mingde-bd/application/admin/controller/WasteStatistics.php

<?php

namespace app\admin\controller;
use think\App;
use think\Db;

class WasteStatistics extends Auth {
    public function work() {
        $cur = date('Y-m-d');
        $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
        $end = input('end', date('Y-m-d'));
        $start1 = $start . ' 00:00:00';
        $end1 = $end . ' 23:59:59';
        $type = input('type');
        $typeList = Db::name('waste_type')
            ->where('enable', 1)
            ->where('del', 0)
            //->where('org_id', $this->orgId)
            ->select();
        $list = $this->workData($start1, $end1, $type);
        $this->assign('list', $list);
        $this->assign('type', $type);
        $this->assign('typeList', $typeList);
        $this->assign('start', $start);
        $this->assign('end', $end);
        return $this->fetch();
    }
    public function workData($start1, $end1, $type) {
        $map1[] = ['create_time', '>=', $start1];
        $map1[] = ['create_time', '<=', $end1];
        $map1[] = ['org_id', '=', $this->orgId];
        if (!empty($type)) {
            $map1[] = ['cateid', '=', $type];
        }
        $list = Db::name('waste_record')
            ->where($map1)
            ->where('del', 0)
            ->group('user_id')
            ->select();
        foreach ($list as $k => $v) {
            $list[$k]['title'] = Db::name('user')
                ->where('id', $v['user_id'])
                ->value('real_name');
            $c = Db::name('waste_record')
                ->where($map1)
                ->where('del', 0)
                ->where('user_id', $v['user_id'])
                ->count();
            $list[$k]['count'] = $c ? $c : 0;
            $weight = Db::name('waste_record')
                ->where($map1)
                ->where('del', 0)
                ->where('user_id', $v['user_id'])
                ->sum('weight');
            $list[$k]['weight'] = $weight ? round($weight / 1000, 2) : 0;
        }
        return $list;
    }
    public function workExport() {
        $cur = date('Y-m-d');
        $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
        $end = input('end', date('Y-m-d'));
        $start1 = $start . ' 00:00:00';
        $end1 = $end . ' 23:59:59';
        $type = input('type');
        $ret = $this->workData($start1, $end1, $type);
        include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
        //实例化PHPExcel类
        $objPHPExcel = new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '编号')
            ->setCellValue('B1', '姓名')
            ->setCellValue('C1', '单数')
            ->setCellValue('D1', '重量(kg)');
        // 设置表格头水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($ret); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $ret[$i]['user_id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $ret[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $ret[$i]['count']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $ret[$i]['weight']);
        }
        //设置保存的Excel表格名称
        $filename = '医废工作量统计_' . date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle('医废工作量统计');
        //设置浏览器窗口下载表格
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename);
        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        return $objWriter->save('php://output');
    }
    // 近两年医废统计
    public function waste_year() {
        $org_id = $this->orgId;
        $year = date('Y');
        $qyear = $year - 1;
        $arr1 = [$year . '01', $year . '02', $year . '03', $year . '04', $year . '05', $year . '06', $year . '07', $year . '08', $year . '09', $year . '10', $year . '11', $year . '12',];
        $arr2 = [$qyear . '01', $qyear . '02', $qyear . '03', $qyear . '04', $qyear . '05', $qyear . '06', $qyear . '07', $qyear . '08', $qyear . '09', $qyear . '10', $qyear . '11', $qyear . '12',];
        $arrs = array_merge($arr2, $arr1);
        $lists = [];
        foreach ($arrs as $k => $v) {
            $weight = Db::name('waste_record')
                ->where('org_id', $org_id)
                ->where('del', 0)
                ->where('create_yyyymm', $v)
                ->sum('weight');
            $lists[] = [
                'month' => $v,
                'month_text' => intval(date('m', strtotime($v . '01'))) . '月',
                'weight' => $weight && !empty($weight) ? round($weight / 1000, 2) : 0
            ];
        }
        $warr = $warr1 = $warr2 = [];
        $tmonths = [];
        $hb = [];
        // 计算环比
        foreach ($lists as $k => $v) {
            if ($k == 0) {
                $lists[$k]['hb'] = 0;
            }
            else {
                if ($lists[$k - 1]['weight'] > 0) {
                    $lists[$k]['hb'] = round(($v['weight'] - $lists[$k - 1]['weight']) / $lists[$k - 1]['weight'], 3) * 100;
                }
                else {
                    $lists[$k]['hb'] = 0;
                }
            }
            if ($k >= 12) {
                $warr1[] = $v['weight'];
            }
            else {
                $warr2[] = $v['weight'];
            }
            $warr[] = $v['weight'];
            $tmonths[] = date('Y-m', strtotime($v['month'] . '01'));
            $hb[] = $lists[$k]['hb'];
        }
        //计算同比
        $tbarr = [];
        foreach ($arr1 as $k => $v) {
            $v1 = $lists[$k]['weight'];
            $v2 = $lists[$k + 12]['weight'];
            if ($v1 > 0) {
                $tbarr[] = round(($v2 - $v1) / $v1, 3) * 100;
            }
            else {
                $tbarr[] = 0;
            }
        }
        $data = [
            'lists' => $lists,
            'tb' => $tbarr,
            'months' => ['1月', '2月', '3月', '4月', '5月', '6月', '7月', '8月', '9月', '10月', '11月', '12月'],
            'year' => $year,
            'qyear' => $qyear,
            'warr' => $warr,
            'warr1' => $warr1,
            'warr2' => $warr2,
            'tmonths' => $tmonths,
            'hb' => $hb
        ];
        $this->assign('data', $data);
        return $this->fetch();
    }
    //医疗报表统计
    public function view(){

        $month = input('month',date('Y-m'));
        $dep_id = input('dep_id','');
        $addr_id = input('addr_id','');
        $type_id = input('type_id','');
        $address =(new \app\common\model\Address())->getListByType(7,$this->orgId);
        $dep = (new \app\common\model\Dep())->getList($this->orgId);
        $typeList = Db::name('waste_type')
            ->where('enable', 1)
            ->where('del', 0)
           // ->where('org_id', $this->orgId)
            ->select();

        $return = $this->viewData($month,$dep_id,$addr_id,$type_id);
        $this->assign('data',$return);
        $this->assign('address',$address);
        $this->assign('dep',$dep);
        $this->assign('typeList',$typeList);
        $this->assign('month',$month);
        $this->assign('dep_id',$dep_id);
        $this->assign('addr_id',$addr_id);
        $this->assign('type_id',$type_id);
        return $this->fetch();
    }

    public function viewData($month,$dep_id,$addr_id,$type_id){
        $map[] = ['org_id','=',$this->orgId];
        $typeMap = [];
        if($type_id !=''){
            $typeMap[] = ['id','=',$type_id];
        }
        $typeList = Db::name('waste_type')
            ->where('enable', 1)
            ->where('del', 0)
            ->where($typeMap)
           // ->where('org_id', $this->orgId)
            ->select();
        $recordMap = [];
        $depWhere = [];
        if($dep_id !=''){
            $depWhere[] = ['id','=',$dep_id];
            $map[] = ['del','=',0];
            $map[] = ['enable','=',1];
            $map[] = ['dep_id','=',$dep_id];
            $map[]=['','exp',Db::raw("FIND_IN_SET(7,types)")];
            $address = Db::name('address')
                ->where($map)
                ->order('id desc')
                ->field('id,title,remark,sn,x,y')
                ->select();
            if($address){
                $recordMap[] = ['waste_device_id','in',array_column($address,'id')];
            }else{
                $recordMap[] = ['waste_device_id','=',0];
            }
        }

        if($addr_id!=''){
            $recordMap[] = ['waste_device_id','=',$addr_id];
        }
        $name1 = [];
        $name2 = [];
        $name3 = [];
        $data1 = [];
        $data2 = [];
        $data3 = [];
        $data4 = [];
        $day = getDay(strtotime($month));


        foreach ($day as $k1=>$v){
            $name2[] = date('d',strtotime($v));
        }

        $dep = Db::name('dep')
            ->where('del',0)
            ->where('org_id',$this->orgId)
            ->where('enable',1)
            ->where($depWhere)
            ->select();
        $depList = [];
        foreach ($dep as $k=>$v){
            $dep_map[] = ['del','=',0];
            $dep_map[] = ['enable','=',1];
            $dep_map[] = ['dep_id','=',$v['id']];
            $dep_map[]=['','exp',Db::raw("FIND_IN_SET(7,types)")];
            $address = Db::name('address')
                ->where($dep_map)
                ->order('id desc')
                ->field('id,title,remark,sn,x,y')
                ->select();
            if($address){
                $depList[] = [
                    'title'=>$v['title'],
                    'ids'=>array_column($address,'id')
                ];
            }
        }
        foreach ($depList as $k2=>$v2){
            $map = [];
            if($month){
                $map[] =['create_yyyymm','=',date('Ym',strtotime($month))];
            }
            $nums1 = Db::name('waste_record')
                ->where('org_id',$this->orgId)
                ->where('del',0)
                ->where($recordMap)
                ->where($map)
                ->where('waste_device_id','in',$v2['ids'])
                ->sum('weight');
            $depList[$k2]['total'] = $nums1?round($nums1/1000,2):0;
        }
        $depList = $depList?list_sort_by($depList,'total','desc'):[];
        $name3[] = $depList?array_column($depList,'title'):[];



        foreach ($typeList as $k=>$v){
            $map = [];
            if($month){
                $map[] =['create_yyyymm','=',date('Ym',strtotime($month))];
            }
            $num = Db::name('waste_record')
                ->where('org_id',$this->orgId)
                ->where('del',0)
                ->where('cateid',$v['id'])
                ->where($recordMap)
                ->where($map)
                ->sum('weight');
            $total =  $num?round($num/1000,2):0;
            $name1[] = $v['title'];
            $data1[] = $total;

            $data2[] =['value'=>$total,'name'=>$v['title']];
            $numList = [];
            $numList1 = [];
            foreach ($day as $k1=>$v1){
                $nums = Db::name('waste_record')
                    ->where('org_id',$this->orgId)
                    ->where('del',0)
                    ->where('cateid',$v['id'])
                    ->where($recordMap)
                    ->where($map)
                    ->where('create_yyyymmdd',$v1)
                    ->sum('weight');
                $totals =  $nums?round($nums/1000,2):0;
                $numList[] = $totals;

            }
            foreach ($depList as $k2=>$v2){
                if($k2<=9){
                    $nums1 = Db::name('waste_record')
                        ->where('org_id',$this->orgId)
                        ->where('del',0)
                        ->where('cateid',$v['id'])
                        ->where($map)
                        ->where('waste_device_id','in',$v2['ids'])
                        ->sum('weight');
                    $totals1 =  $nums1?round($nums1/1000,2):0;
                    $numList1[] = $totals1;
                }
            }
            $data3[] = [
                'name'=>$v['title'],
                'type'=>'line',
                'stack'=>'stack',
                'data'=>$numList
            ];
            $data4[] = [
                'name'=>$v['title'],
                'type'=>'line',
                'stack'=>'stack',
                'data'=>$numList1
            ];
        }
        $return = [
            'name1'=>$name1,
            'name2'=>$name2,
            'name3'=>$name3,
            'data1'=>$data1,
            'data2'=>$data2,
            'data3'=>$data3,
            'data4'=>$data4,
        ];
        return $return;

    }

    public function viewExportData($month,$dep_id,$addr_id,$type_id){
        $tMap = [];
        $tMap[] = ['enable','=',1];
        $tMap[] = ['del','=',0];
        //$tMap[] = ['org_id','=',$this->orgId];
        if($type_id!=''){
            $tMap[] = ['id','=',$type_id];
        }
        $lists = [];

        $type = Db::name('waste_type')->where($tMap)->select();
        $type = $type?$type:[];
        $first = [
            'name' => '类型',
        ];
        $second = [
            'name' => '重量',
        ];
        $third = [
            'name' => '比例',
        ];
        $fourth = [
            'name' => '',
        ];
        $five = [
            'name' => '按天统计',
        ];
        $total = 0;
        $count = 1;
        foreach ($type as $k=>$v){
            $count++;
            $map = [];
            $map[] = ['del','=',0];
            $map[] = ['org_id','=',$this->orgId];
            $map[] = ['status','in',[0,1,2]];
            if($type_id){
                $map[] = ['cateid','=',$type_id];
            }
            if($addr_id){
                $map[] = ['waste_device_id','=',$addr_id];
            }
            if($dep_id){
                $addrs = Db::name('address')->where('dep_id',$dep_id)->where('enable',1)->where('del',0)->column('id');
                if($addrs){
                    $map[] = ['waste_device_id','in',$addr_id];
                }else{
                    $map[] = ['waste_device_id','=',-1];
                }
            }
            if($month){
                $map[] = ['create_yyyymm','=',str_ireplace('-','',$month)];
            }
            $weight = Db::name('waste_record')->where($map)->sum('weight');
            $total += $weight;

            $first['type'.$v['id']] = $v['title'];
            $second['type'.$v['id']] = round($weight/1000,2);
            $third['type'.$v['id']] = $weight;

            $fourth['type'.$v['id']] = '';
            $five['type'.$v['id']] = '';
        }

        foreach ($type as $k=>$v){
            $third['type'.$v['id']] = $total>0?round($third['type'.$v['id']]/$total,2):0;
        }

        $lists[] = $first;
        $lists[] = $second;
        $lists[] = $third;
        $lists[] = $fourth;
        $lists[] = $five;

        $days = getDay(strtotime($month.'-01'));
        foreach ($days as $kk=>$vv){
            $arr = [
                'name' => date('Y-m-d',strtotime($vv))
            ];
            foreach ($type as $k=>$v){
                $map = [];
                $map[] = ['del','=',0];
                $map[] = ['org_id','=',$this->orgId];
                $map[] = ['status','in',[0,1,2]];
                if($type_id){
                    $map[] = ['cateid','=',$type_id];
                }
                if($addr_id){
                    $map[] = ['waste_device_id','=',$addr_id];
                }
                if($dep_id){
                    $addrs = Db::name('address')->where('dep_id',$dep_id)->where('enable',1)->where('del',0)->column('id');
                    if($addrs){
                        $map[] = ['waste_device_id','in',$addr_id];
                    }else{
                        $map[] = ['waste_device_id','=',-1];
                    }
                }
                $map[] = ['create_yyyymmdd','=',$vv];
                $weight = Db::name('waste_record')->where($map)->sum('weight');

                $arr['type'.$v['id']] = round($weight/1000,2);
            }
            $lists[] = $arr;
        }

        $arr2 = [
            'name' => '',
        ];
        $arr3 = [
            'name' => '按科室统计',
        ];
        foreach ($type as $k=>$v){
            $arr2['type'.$v['id']] = '';
            $arr3['type'.$v['id']] = '';
        }

        $lists[] = $arr2;
        $lists[] = $arr3;

        $m[] = ['org_id','=',$this->orgId];
        $m[] = ['enable','=',1];
        $m[] = ['del','=',0];
        if($dep_id > 0){
            $m[] = ['id','=',$dep_id];
        }
        $deps = Db::name('dep')->where($m)->select();
        $deps = $deps?$deps:[];
        foreach ($deps as $kk=>$vv){
            $arr = [
                'name' => $vv['title'],
            ];
            foreach ($type as $k=>$v){
                $map = [];
                $map[] = ['del','=',0];
                $map[] = ['org_id','=',$this->orgId];
                $map[] = ['status','in',[0,1,2]];
                if($type_id){
                    $map[] = ['cateid','=',$type_id];
                }
                if($addr_id){
                    $map[] = ['waste_device_id','=',$addr_id];
                }
                if($vv['id']){
                    $addrs = Db::name('address')->where('dep_id',$dep_id)->where('enable',1)->where('del',0)->column('id');
                    if($addrs){
                        $map[] = ['waste_device_id','in',$addr_id];
                    }else{
                        $map[] = ['waste_device_id','=',-1];
                    }
                }
                $map[] = ['create_yyyymm','=',$month];
                $weight = Db::name('waste_record')->where($map)->sum('weight');
                $arr['type'.$v['id']] = round($weight/1000,2);
            }
            $lists[] = $arr;
        }


        $address = '地点：全部';
        if($addr_id){
            $address = '地点：'.Db::name('address')->where('id',$addr_id)->value('title');
        }
        $dep = '科室：全部';
        if($dep_id){
            $dep = '科室：'.Db::name('dep')->where('id',$dep_id)->value('title');
        }
        $typestr = '医废类型：全部';
        if($type_id){
            $typestr = '医废类型：'.Db::name('waste_type')->where('id',$type_id)->value('title');
        }
        $monthstr = "月份：".$month;

        $data = [
            'count' => $count,
            'address' => $address,
            'dep' => $dep,
            'type' => $typestr,
            'types' => $type,
            'monthstr' => $monthstr,
            'lists' => $lists
        ];


        return $data;
    }
    public function viewExport(){
        $month = input('month',date('Y-m'));
        $dep_id = input('dep_id','');
        $addr_id = input('addr_id','');
        $type_id = input('type_id','');
        $data = $this->viewExportData($month,$dep_id,$addr_id,$type_id);
        $lists = $data['lists'];
        $types = $data['types'];

        $char = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
        $lins = [];
        // 暂时支持2层
        for( $i=0;$i<$data['count'];$i++ ){
            $r1 = $i%26;
            $r2 = floor($i/26);
            if($r2 == 0){
                $lins[] = $char[$r1];
            }else if($r2 == 1){
                $lins[] = $char[$r2-1].$char[$r1];
            }else if($r2 == 2){
                $lins[] = $char[$r2-1].$char[$r1];
            }else if($r2 == 3){
                $lins[] = $char[$r2-1].$char[$r1];
            }
        }

        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        //设置工作表标题名称
        $worksheet->setTitle('医疗废弃物报表');
        foreach ($lins as $k=>$v){
            $spreadsheet->setActiveSheetIndex(0)->getStyle($v)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $spreadsheet->setActiveSheetIndex(0)->getColumnDimension($v)->setAutoSize(true);
        }

        //表头
        //设置单元格内容
        $worksheet->setCellValueByColumnAndRow(1, 1, $data['address']);
        $worksheet->setCellValueByColumnAndRow(2, 1, $data['dep']);
        $worksheet->setCellValueByColumnAndRow(3, 1, $data['type']);
        $worksheet->setCellValueByColumnAndRow(4, 1, $data['monthstr']);
        foreach ($lists as $k=>$v){
            $worksheet->setCellValueByColumnAndRow(1, $k+2, $v['name']);
            foreach ($types as $kk=>$vv){
                $worksheet->setCellValueByColumnAndRow($kk+2, $k+2, $v['type'.$vv['id']]);
            }
        }

        $filename = '医疗废弃物报表'.$month.'.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

    // 医废日统计
    public function wasteDay() {
        $month = input('start',date('Y-m-d'));
        $addr_id = input('addr_id','');
        $type_id = input('type_id','');

        $addressList = (new \app\common\model\Address())->getListByType(7,$this->orgId);

        $tMap[] = ['del','=',0];
        $tMap[] = ['enable','=',1];
        //$tMap[] = ['org_id','=',$this->orgId];
        $typeList1 = Db::name('waste_type')
            ->where($tMap)
            ->field('id,title')
            ->select();
        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }else{
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }

        $data = $this->wasteDataDay($month,$addr_id,$type_id);
        $this->assign('data',$data);
        $this->assign('typeList',$typeList);
        $this->assign('typeList1',$typeList1);
        $this->assign('addressList',$addressList);
        $this->assign('start',$month);
        $this->assign('addr_id',$addr_id?explode(',',$addr_id):[]);
        $this->assign('type_id',$type_id?explode(',',$type_id):[]);
        return $this->fetch();
    }

    public function wasteDataDay($month,$addr_id,$type_id){

        $org_id = $this->orgId;
        $aMap = [];
        if($addr_id!=''){
            $aMap[] = ['id','in',explode(',',$addr_id)];
        }
        $aMap[]=['','exp',Db::raw("FIND_IN_SET(7,types)")];
        $dep = Db::name('address')
            ->where('org_id',$org_id)
            ->where('enable',1)
            ->where('del',0)
            ->where($aMap)
            ->select();

        $tMap = [];
        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
        }
        $type = Db::name('waste_type')
            ->where('enable',1)
            ->where('del',0)
            //->where('org_id',$this->orgId)
            ->where($tMap)
            ->select();
        $date = date('Ymd',strtotime($month));

        foreach ($dep as $k=>$v){
            foreach ($type as $k1=>$v1){
                $map = [];
                $map[] = ['CREATE_YYYYMMDD','=',$date];
                $row = Db::name('waste_record')
                    ->where('waste_device_id',$v['id'])
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map)
                    ->count();
                $dep[$k]['data'][] = $row;
                $map1 = [];
                if($addr_id!=''){
                    $map1[] = ['waste_device_id','in',explode(',',$addr_id)];
                }
                $dep[$k]['total'][] = Db::name('waste_record')
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map)
                    ->where($map1)
                    ->count();

                $kg = Db::name('waste_record')
                    ->where('waste_device_id',$v['id'])
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map)
                    ->sum('weight');

                $we =  $kg?round($kg/1000,2):0;
                $dep[$k]['data'][] = $we;
                $kg = Db::name('waste_record')
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map)
                    ->where($map1)
                    ->sum('weight');
                $we =  $kg?round($kg/1000,2):0;
                $dep[$k]['total'][] =$we;

            }
        }
        return $dep;
    }

    //医废日统计导出
    public function wasteDayExport()
    {
        set_time_limit(0);
        ini_set("memory_limit", "1024M");
        $month = input('start',date('Y-m-d'));
        $addr_id = input('addr_id','');
        $type_id = input('type_id','');
        $data = $this->wasteDataDay($month,$addr_id,$type_id);
        $tMap[] = ['del','=',0];
        $tMap[] = ['enable','=',1];
        //$tMap[] = ['org_id','=',$this->orgId];
        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
            $type = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }else{
            $type = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }

        $title = '医废日统计';
        if(!empty($month)){
            $title = $month.'医废日统计';

        }
        header("Content-type: application/vnd.ms-excel");
        header("Content-Type: application/force-download");
        header("Content-Disposition: attachment; filename=".$title.".xls");
        header('Expires:0');
        header('Pragma:public');
        $res = '';
        $res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
        $res.='<tr style="background: #ffffff;">
          <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">地点</th>';
        foreach ($type as $k=>$v){
            $res.='  <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" colspan="2">'.$v['title'].'</th>';

        }
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">备注</th>';
        $res.='<tr style="background: #ffffff;">';
        foreach ($type as $v){
            $res.=' <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">件数/包</th>
            <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">重量/kg</th>';
        }
        foreach ($data as $k=>$v){
            $res.='<tr style="background: #ffffff;">';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['title'].'</td>';
            foreach ($v['data'] as $k1=>$v1){
                $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
            }
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>';
            $res.='</tr>';

        }
        $res.='<tr style="background: #ffffff;">
               <td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">共计:</td>';
        if(!empty($data)){
            foreach ($data[0]['total'] as $k1=>$v1){
                $res.=' <td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
            }
        }
        $res.='<td style="border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>
       </tr>';
        echo $res;

    }



    // 医废月统计
    public function wasteMonth() {
        $month = input('start',date('Y-m'));
        $addr_id = input('addr_id','');
        $type_id = input('type_id','');

        $addressList = (new \app\common\model\Address())->getListByType(7,$this->orgId);

        $tMap[] = ['del','=',0];
        $tMap[] = ['enable','=',1];
       // $tMap[] = ['org_id','=',$this->orgId];
        $typeList1 = Db::name('waste_type')
            ->where($tMap)
            ->field('id,title')
            ->select();
        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }else{
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }

        $data = $this->wasteMonthData($month,$addr_id,$type_id);
        $this->assign('data',$data['res']);
        $this->assign('totalData',$data['total']);
        $this->assign('typeList',$typeList);
        $this->assign('typeList1',$typeList1);
        $this->assign('addressList',$addressList);
        $this->assign('start',$month);
        $this->assign('addr_id',$addr_id?explode(',',$addr_id):[]);
        $this->assign('type_id',$type_id?explode(',',$type_id):[]);
        return $this->fetch();
    }

    public function wasteMonthData($month,$addr_id,$type_id){

        $org_id = $this->orgId;
        $dep  = getDay(strtotime($month));

        $tMap = [];
        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
        }
        $type = Db::name('waste_type')
            ->where('enable',1)
            ->where('del',0)
           // ->where('org_id',$this->orgId)
            ->where($tMap)
            ->select();
        $res = [];
        $total = [];
        foreach ($dep as $k=>$v){
            foreach ($type as $k1=>$v1){
                $map = [];
                $map[] = ['CREATE_YYYYMMDD','=',$v];
                $map1 = [];
                if($addr_id!=''){
                    $map1[] = ['waste_device_id','in',explode(',',$addr_id)];
                }
                $row = Db::name('waste_record')
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map)
                    ->where($map1)
                    ->count();
                $res[$k][] = $row;
                $map2 = [];
                $map2[] = ['CREATE_YYYYMM','=',date('Ym',strtotime($v))];

                $row = Db::name('waste_record')
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map1)
                    ->where($map2)
                    ->count();
                $total[$k][] = $row;
                $kg = Db::name('waste_record')
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map)
                    ->where($map1)
                    ->sum('weight');

                $we =  $kg?round($kg/1000,2):0;
                $res[$k][] = $we;
                $kg = Db::name('waste_record')
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map1)
                    ->where($map2)
                    ->sum('weight');
                $we =  $kg?round($kg/1000,2):0;
                $total[$k][] =$we;

            }
        }
        return ['res'=>$res,'total'=>$total];
    }

    public function wasteMonthExport(){
        $month = input('start',date('Y-m'));
        $addr_id = input('addr_id','');
        $type_id = input('type_id','');
        set_time_limit(0);
        ini_set("memory_limit", "1024M");

        $tMap[] = ['del','=',0];
        $tMap[] = ['enable','=',1];
       // $tMap[] = ['org_id','=',$this->orgId];

        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }else{
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }
        $data = $this->wasteMonthData($month,$addr_id,$type_id);
        $title = '医废月统计';
        if(!empty($month)){
            $title = $month.'医废月统计';
        }

        header("Content-type: application/vnd.ms-excel");
        header("Content-Type: application/force-download");
        header("Content-Disposition: attachment; filename=".$title.".xls");
        header('Expires:0');
        header('Pragma:public');
        $res = '';
        $res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
        $res.='<tr style="background: #ffffff;">
          <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">日期</th>';
        foreach ($typeList as $k=>$v){
            $res.='  <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" colspan="2">'.$v['title'].'</th>';

        }
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">备注</th>';
        $res.='<tr style="background: #ffffff;">';
        foreach ($typeList as $v){
            $res.=' <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">件数/包</th>
            <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">重量/kg</th>';
        }
        foreach ($data['res'] as $k=>$v){
            $res.='<tr style="background: #ffffff;">';
            $kk = $k+1;
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$kk.'</td>';
            foreach ($v as $k1=>$v1){
                $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
            }
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>';
            $res.='</tr>';

        }
        $res.='<tr style="background: #ffffff;">
               <td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">共计:</td>';
        if(!empty($data['total'])){
            foreach ($data['total'][0] as $k1=>$v1){
                $res.=' <td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
            }
        }
        $res.='<td style="border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>
       </tr>';
        echo $res;
    }

    // 医废年统计
    public function wasteYears() {
        $month = input('start',date('Y'));
        $addr_id = input('addr_id','');
        $type_id = input('type_id','');

        $addressList = (new \app\common\model\Address())->getListByType(7,$this->orgId);

        $tMap[] = ['del','=',0];
        $tMap[] = ['enable','=',1];
        //$tMap[] = ['org_id','=',$this->orgId];
        $typeList1 = Db::name('waste_type')
            ->where($tMap)
            ->field('id,title')
            ->select();
        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }else{
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }

        $data = $this->wasteYearsData($month,$addr_id,$type_id);
        $this->assign('data',$data);
        $this->assign('typeList',$typeList);
        $this->assign('typeList1',$typeList1);
        $this->assign('addressList',$addressList);
        $this->assign('start',$month);
        $this->assign('addr_id',$addr_id?explode(',',$addr_id):[]);
        $this->assign('type_id',$type_id?explode(',',$type_id):[]);
        return $this->fetch();
    }

    public function wasteYearsData($month,$addr_id,$type_id){

        $org_id = $this->orgId;
        $dep  = get_month($month);

        $tMap = [];
        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
        }
        $type = Db::name('waste_type')
            ->where('enable',1)
            ->where('del',0)
            //->where('org_id',$this->orgId)
            ->where($tMap)
            ->select();
        foreach ($dep as $k=>$v){
            foreach ($type as $k1=>$v1){
                $map = [];
                $map[] = ['create_yyyymm','=',$v['date']];
                $map1 = [];
                if($addr_id!=''){
                    $map1[] = ['waste_device_id','in',explode(',',$addr_id)];
                }
                $row = Db::name('waste_record')
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map)
                    ->where($map1)
                    ->count();
                $dep[$k]['list'][] = $row;
                $map2 = [];
                $map2[] = ['create_yyyy','=',$month];

                $row = Db::name('waste_record')
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map1)
                    ->where($map2)
                    ->count();
                $dep[$k]['total'][] = $row;
                $kg = Db::name('waste_record')
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map)
                    ->where($map1)
                    ->sum('weight');

                $we =  $kg?round($kg/1000,2):0;
                $dep[$k]['list'][] = $we;
                $kg = Db::name('waste_record')
                    ->where('cateid',$v1['id'])
                    ->where('org_id',$org_id)
                    ->where('del',0)
                    ->where($map1)
                    ->where($map2)
                    ->sum('weight');
                $we =  $kg?round($kg/1000,2):0;
                $dep[$k]['total'][] = $we;

            }
        }
        return $dep;
    }


    public function wasteYearsExport() {
        $month = input('start',date('Y'));
        $addr_id = input('addr_id','');
        $type_id = input('type_id','');
        set_time_limit(0);
        ini_set("memory_limit", "1024M");
        $tMap[] = ['del','=',0];
        $tMap[] = ['enable','=',1];
        //$tMap[] = ['org_id','=',$this->orgId];
        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }else{
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }
        $data = $this->wasteYearsData($month,$addr_id,$type_id);
        $title = '医废年统计';
        if(!empty($month)){
            $title = $month.'医废年统计';
        }
        header("Content-type: application/vnd.ms-excel");
        header("Content-Type: application/force-download");
        header("Content-Disposition: attachment; filename=".$title.".xls");
        header('Expires:0');
        header('Pragma:public');
        $res = '';
        $res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
        $res.='<tr style="background: #ffffff;">
          <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">日期</th>';
        foreach ($typeList as $k=>$v){
            $res.='  <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" colspan="2">'.$v['title'].'</th>';

        }
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">备注</th>';
        $res.='</tr>';
        $res.='<tr style="background: #ffffff;">';
        foreach ($typeList as $v){
            $res.=' <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">件数/包</th>
            <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">重量/kg</th>';
        }
        $res.='</tr>';

        foreach ($data as $k=>$v){
            $res.='<tr style="background: #ffffff;">';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['key'].'</td>';
            foreach ($v['list'] as $k1=>$v1){
                $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
            }
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>';
            $res.='</tr>';

        }
        $res.='<tr style="background: #ffffff;">
               <td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">共计:</td>';
        if(!empty($data)){
            foreach ($data[1]['total'] as $k1=>$v1){
                $res.=' <td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
            }
        }
        $res.='<td style="border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>
       </tr>';
        echo $res;
    }

    // 医废暂存统计
    public function wasteZc() {
        $addr_id = input('addr_id','');
        $type_id = input('type_id','');

        $addressList = (new \app\common\model\Address())->getListByType(7,$this->orgId);

        $tMap[] = ['del','=',0];
        $tMap[] = ['enable','=',1];
       // $tMap[] = ['org_id','=',$this->orgId];
        $typeList1 = Db::name('waste_type')
            ->where($tMap)
            ->field('id,title')
            ->select();
        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }else{
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }

        $data = $this->wasteZcData($addr_id,$type_id);

        $this->assign('data',$data);
        $this->assign('typeList',$typeList);
        $this->assign('typeList1',$typeList1);
        $this->assign('addressList',$addressList);
        $this->assign('addr_id',$addr_id?explode(',',$addr_id):[]);
        $this->assign('type_id',$type_id?explode(',',$type_id):[]);
        return $this->fetch();
    }

    public function wasteZcData($addr_id,$type_id){
        $org_id = $this->orgId;
        $tMap = [];
        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
        }
        $type = Db::name('waste_type')
            ->where('enable',1)
            ->where('del',0)
            //->where('org_id',$this->orgId)
            ->where($tMap)
            ->select();
        $res = [];
        foreach ($type as $k1=>$v1){
            $map1 = [];
            if($addr_id!=''){
                $map1[] = ['waste_device_id','in',explode(',',$addr_id)];
            }
            $row = Db::name('waste_record')
                ->where('cateid',$v1['id'])
                ->where('org_id',$org_id)
                ->where('del',0)
                ->where($map1)
                ->where(function ($query)
                {
                    $query->where('status', 1)->whereor('status', 2);
                })
                ->count();
            $res[]= $row;
            $kg = Db::name('waste_record')
                ->where('cateid',$v1['id'])
                ->where('org_id',$org_id)
                ->where('del',0)
                ->where($map1)
                ->where(function ($query)
                {
                    $query->where('status', 1)->whereor('status', 2);
                })
                ->sum('weight');
            $we =  $kg?round($kg/1000,2):0;
            $res[]= $we;

        }
        return $res;
    }

    // 医废暂存统计导出
    public function wasteZcExport() {
        $addr_id = input('addr_id','');
        $type_id = input('type_id','');

        set_time_limit(0);
        ini_set("memory_limit", "1024M");
        $tMap[] = ['del','=',0];
        $tMap[] = ['enable','=',1];
        //$tMap[] = ['org_id','=',$this->orgId];
        if($type_id!=''){
            $tMap[] = ['id','in',explode(',',$type_id)];
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }else{
            $typeList = Db::name('waste_type')
                ->where($tMap)
                ->field('id,title')
                ->select();
        }

        $data = $this->wasteZcData($addr_id,$type_id);
        $title = '医废暂存统计';
        header("Content-type: application/vnd.ms-excel");
        header("Content-Type: application/force-download");
        header("Content-Disposition: attachment; filename=".$title.".xls");
        header('Expires:0');
        header('Pragma:public');
        $res = '';
        $res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
        $res.='<tr style="background: #ffffff;">';
        foreach ($typeList as $k=>$v){
            $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" colspan="2">'.$v['title'].'</th>';
        }
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">备注</th>';
        $res.='</tr>';
        $res.='<tr style="background: #ffffff;">';
        foreach ($typeList as $v){
            $res.=' <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">件数/包</th>
            <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">重量/kg</th>';
        }
        $res.='</tr>';
        $res.='<tr style="background: #ffffff;">';
        foreach ($data as $k=>$v){
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v.'</td>';

        }
        $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>';
        $res.='</tr>';
        echo $res;

    }
}