jili2/application/admin/controller/OrderStatistics.php

<?php
namespace app\admin\controller;


use think\Db;
use think\Exception;


class OrderStatistics extends Auth
{
    public function repairItems(){

        $start = input('start',date('Y-m-d',strtotime('-7 day')));
        $end = input('end',date('Y-m-d'));
        if(request()->isAjax()){
            $sTime = $start.' 00:00:00';
            $eTime = $end.' 23:59:59';
            $list = Db::name('order_type')
                ->field('id,title')
                ->where('enable',1)
                ->where('del',0)
                ->where('org_id',$this->orgId)
                ->where('parent_id','>',0)
                ->select();

            foreach ($list as $k=>$v){
                $list[$k]['nums'] =  Db::name('orders')
                    ->alias('o')
                    ->join('order_repair or','or.order_id=o.id')
                    ->where('or.type_id',$v['id'])
                    ->where('o.del',0)
                    ->where('o.create_time','>=',$sTime)
                    ->where('o.create_time','<=',$eTime)
                    ->count();
            }
            $data = list_sort_by($list,'nums','desc');
            $result['rows'] = $data;

            return json($result);
        }else{
            $this->assign('start',$start);
            $this->assign('end',$end);
            return $this->fetch();
        }
    }
    public function repairItemsExport(){
        $start = input('start',date('Y-m-d',strtotime('-7 day')));
        $end = input('end',date('Y-m-d'));
        $sTime = $start.' 00:00:00';
        $eTime = $end.' 23:59:59';

        $list = Db::name('order_type')
            ->field('id,title')
            ->where('enable',1)
            ->where('del',0)
            ->where('org_id',$this->orgId)
            ->where('parent_id','>',0)
            ->select();

        foreach ($list as $k=>$v){
            $list[$k]['nums']  =  Db::name('orders')
                ->alias('o')
                ->join('order_repair or','or.order_id=o.id')
                ->where('or.type_id',$v['id'])
                ->where('o.del',0)
                ->where('o.create_time','>=',$sTime)
                ->where('o.create_time','<=',$eTime)
                ->count();
        }
        $data = list_sort_by($list,'nums','desc');


        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', '总数');
        // 设置表格头水平居中
        $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(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);


        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($data); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $data[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $data[$i]['nums']);

        }
        //设置保存的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 repairCate(){
        $start = input('start',date('Y-m-d',strtotime('-7 day')));
        $end = input('end',date('Y-m-d'));
        if(request()->isAjax()){
            $sTime = $start.' 00:00:00';
            $eTime = $end.' 23:59:59';
            $list = Db::name('order_type')
                ->field('id,title,parent_id')
                ->where('enable',1)
                ->where('del',0)
                ->where('org_id',$this->orgId)
                ->where('parent_id','=',0)
                ->select();
            foreach ($list as $k=>$v){
                $type = Db::name('order_type')
                    ->field('id')
                    ->where('enable',1)
                    ->where('del',0)
                    ->where('org_id',$this->orgId)
                    ->where('parent_id',$v['id'])
                    ->select();
                $ids = [];
                foreach ($type as $kk=>$vv){
                    $ids[$kk] = $vv['id'];
                }
                $list[$k]['ids'] = $ids;
            }

            foreach ($list as $k=>$v){
                $list[$k]['nums'] =  Db::name('orders')
                    ->alias('o')
                    ->join('order_repair or','or.order_id=o.id')
                    ->whereIn('or.type_id',$v['ids'])
                    ->where('o.del',0)
                    ->where('o.org_id',$this->orgId)
                    ->where('o.create_time','>=',$sTime)
                    ->where('o.create_time','<=',$eTime)
                    ->count();
            }
            $data = list_sort_by($list,'nums','desc');
            $result['rows'] = $data;
            return json($result);
        }else{
            $this->assign('start',$start);
            $this->assign('end',$end);
            return $this->fetch();
        }
    }
    
    public function repairCateExport(){
        $start = input('start',date('Y-m-d',strtotime('-7 day')));
        $end = input('end',date('Y-m-d'));
        $sTime = $start.' 00:00:00';
        $eTime = $end.' 23:59:59';

        $list = Db::name('order_type')
                ->field('id,title,parent_id')
                ->where('enable',1)
                ->where('del',0)
                ->where('org_id',$this->orgId)
                ->where('parent_id','=',0)
                ->select();
            foreach ($list as $k=>$v){
                $type = Db::name('order_type')
                    ->field('id')
                    ->where('enable',1)
                    ->where('del',0)
                    ->where('org_id',$this->orgId)
                    ->where('parent_id',$v['id'])
                    ->select();
                $ids = [];
                foreach ($type as $kk=>$vv){
                    $ids[$kk] = $vv['id'];
                }
                $list[$k]['ids'] = $ids;
            }

            foreach ($list as $k=>$v){
                $list[$k]['nums'] =  Db::name('orders')
                    ->alias('o')
                    ->join('order_repair or','or.order_id=o.id')
                    ->whereIn('or.type_id',$v['ids'])
                    ->where('o.del',0)
                    ->where('o.org_id',$this->orgId)
                    ->where('o.create_time','>=',$sTime)
                    ->where('o.create_time','<=',$eTime)
                    ->count();
            }
            $data = list_sort_by($list,'nums','desc');
           

        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', '总数');
        // 设置表格头水平居中
        $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(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);


        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($data); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $data[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $data[$i]['nums']);

        }
        //设置保存的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 repairWorkload($mode){
        $start = input('start',date('Y-m-d 00:00:00',strtotime('-7 day')));
        $end = input('end',date('Y-m-d 23:59:59'));
        $mode = input('mode');

        $lists = $this->repairWorkloadData($start,$end,$mode);

        $this->assign('list',$lists['list']);
        $this->assign('totalList',$lists['totalList']);

        if($mode == 1){
            $title = '报修工作量统计报表';
        }elseif ($mode == 2){
            $title = '保洁工作量统计报表';
        }elseif ($mode == 3){
            $title = '运送工作量统计报表';
        }elseif ($mode == 4){
            $title = '应急工作量统计报表';
        }elseif ($mode == 0){
            $title = '一键呼叫工作量统计报表';
        }
        $this->assign('mode',$mode);
        $this->assign('title',$title);
        $this->assign('start',$start);
        $this->assign('end',$end);
        return $this->fetch();
    }

    public function repairWorkloadData($start,$end,$mode){

        $sTime = $start;
        $eTime = $end;

        $list = Db::name('todo')
            ->field('id,to_user_id')
            ->where('del',0)
            ->where('org_id',$this->orgId)
            ->where('create_time','>=',$sTime)
            ->where('create_time','<=',$eTime)
            ->where('work_type_mode',$mode)
            ->group('to_user_id')
            ->select();

        $userIds = array_unique(array_column($list, 'to_user_id'));
        $userNames = Db::name('user')->where('id', 'in', $userIds)->column('real_name', 'id');
        $todoData = Db::name('todo')
            ->alias('a')
            ->join('order_convey b', 'a.order_id=b.order_id')
            ->field('a.to_user_id, COUNT(*) as total,
             SUM(CASE WHEN a.todo_mode = 1 THEN 1 ELSE 0 END) as nums1, 
             SUM(CASE WHEN a.todo_mode = 2 THEN 1 ELSE 0 END) as nums2, 
             SUM(CASE WHEN a.todo_mode = 3 THEN 1 ELSE 0 END) as nums3, 
             SUM(CASE WHEN a.todo_mode = 4 THEN 1 ELSE 0 END) as nums4, 
             SUM(CASE WHEN a.todo_mode = 5 THEN 1 ELSE 0 END) as nums5, 
             SUM(CASE WHEN a.todo_mode = 6 THEN 1 ELSE 0 END) as nums6,
             SUM(CASE WHEN a.todo_mode = 3 THEN b.score ELSE 0 END) as score')
            ->where('a.del', 0)
            ->where('a.org_id', $this->orgId)
            ->where('a.create_time', '>=', $sTime)
            ->where('a.create_time', '<=', $eTime)
            ->where('a.work_type_mode', $mode)
            ->group('a.to_user_id')
            ->select();
        foreach ($list as $k => $v) {
            $list[$k]['user_name'] = $userNames[$v['to_user_id']] ?? '';

            $todoInfo = $todoData ? array_filter($todoData, function ($item) use ($v) { return $item['to_user_id'] == $v['to_user_id']; }) : [];
            $todoInfo = array_values($todoInfo)[0] ?? [];

            $list[$k] = array_merge($list[$k], [
                'score' => $mode == 3 ? $todoInfo['score']: 0,
                'nums' => $todoInfo['total'] ?? 0,
                'nums1' => $todoInfo['nums1'] ?? 0,
                'nums2' => $todoInfo['nums2'] ?? 0,
                'nums3' => $todoInfo['nums3'] ?? 0,
                'nums4' => $todoInfo['nums4'] ?? 0,
                'nums5' => $todoInfo['nums5'] ?? 0,
                'nums6' => $todoInfo['nums6'] ?? 0,
            ]);
        }

        $list =  $list ? list_sort_by($list,'nums','desc'):[];


        $totalList = [
            array_sum(array_column($list,'score')),
            array_sum(array_column($list,'nums')),
            array_sum(array_column($list,'nums1')),
            array_sum(array_column($list,'nums2')),
            array_sum(array_column($list,'nums3')),
            array_sum(array_column($list,'nums4')),
            array_sum(array_column($list,'nums5')),
            array_sum(array_column($list,'nums6')),
        ];
        if($mode!=3){
            unset($totalList[0]);
        }


        return ['list'=>$list,'totalList'=>$totalList];
    }

    //查询时间巨长！！！
    public function repairWorkloadDataOld($start,$end,$mode){

//        $sTime = $start.' 00:00:00';
//        $eTime = $end.' 23:59:59';
        $sTime = $start;
        $eTime = $end;

        $list = Db::name('todo')
            ->field('id,to_user_id')
            ->where('del',0)
            ->where('org_id',$this->orgId)
            ->where('create_time','>=',$sTime)
            ->where('create_time','<=',$eTime)
            ->where('work_type_mode',$mode)
            ->group('to_user_id')
            ->select();

        foreach ($list as $k=>$v){
            $list[$k]['user_name'] = Db::name('user')->where('id',$v['to_user_id'])->value('real_name');

            $list[$k]['score'] = 0;
            if($mode==3){
                $list[$k]['score'] =  Db::name('todo')
                    ->alias('a')
                    ->join('order_convey b','a.order_id=b.order_id')
                    ->where('a.del',0)
                    ->where('a.to_user_id',$v['to_user_id'])
                    ->where('a.org_id',$this->orgId)
                    ->where('a.create_time','>=',$sTime)
                    ->where('a.create_time','<=',$eTime)
                    ->where('a.work_type_mode',$mode)
                    ->where('a.todo_mode',3)
                    ->sum('b.score');
            }
            $list[$k]['nums'] =Db::name('todo')
                ->where('del',0)
                ->where('to_user_id',$v['to_user_id'])
                ->where('org_id',$this->orgId)
                ->where('create_time','>=',$sTime)
                ->where('create_time','<=',$eTime)
                ->where('work_type_mode',$mode)
                ->count();
            $list[$k]['nums1'] =Db::name('todo')
                ->where('del',0)
                ->where('to_user_id',$v['to_user_id'])
                ->where('org_id',$this->orgId)
                ->where('create_time','>=',$sTime)
                ->where('create_time','<=',$eTime)
                ->where('work_type_mode',$mode)
                ->where('todo_mode',1)
                ->count();
            $list[$k]['nums2'] =Db::name('todo')
                ->where('del',0)
                ->where('to_user_id',$v['to_user_id'])
                ->where('org_id',$this->orgId)
                ->where('create_time','>=',$sTime)
                ->where('create_time','<=',$eTime)
                ->where('work_type_mode',$mode)
                ->where('todo_mode',2)
                ->count();
            $list[$k]['nums3'] =Db::name('todo')
                ->where('del',0)
                ->where('to_user_id',$v['to_user_id'])
                ->where('org_id',$this->orgId)
                ->where('create_time','>=',$sTime)
                ->where('create_time','<=',$eTime)
                ->where('work_type_mode',$mode)
                ->where('todo_mode',3)
                ->count();
            $list[$k]['nums4'] =Db::name('todo')
                ->where('del',0)
                ->where('to_user_id',$v['to_user_id'])
                ->where('org_id',$this->orgId)
                ->where('create_time','>=',$sTime)
                ->where('create_time','<=',$eTime)
                ->where('work_type_mode',$mode)
                ->where('todo_mode',4)
                ->count();
            $list[$k]['nums5'] =Db::name('todo')
                ->where('del',0)
                ->where('to_user_id',$v['to_user_id'])
                ->where('org_id',$this->orgId)
                ->where('create_time','>=',$sTime)
                ->where('create_time','<=',$eTime)
                ->where('work_type_mode',$mode)
                ->where('todo_mode',5)
                ->count();
            $list[$k]['nums6'] =Db::name('todo')
                ->where('del',0)
                ->where('to_user_id',$v['to_user_id'])
                ->where('org_id',$this->orgId)
                ->where('create_time','>=',$sTime)
                ->where('create_time','<=',$eTime)
                ->where('work_type_mode',$mode)
                ->where('todo_mode',6)
                ->count();
        }

        $list =  $list ? list_sort_by($list,'nums','desc'):[];


        $totalList = [
            array_sum(array_column($list,'score')),
            array_sum(array_column($list,'nums')),
            array_sum(array_column($list,'nums1')),
            array_sum(array_column($list,'nums2')),
            array_sum(array_column($list,'nums3')),
            array_sum(array_column($list,'nums4')),
            array_sum(array_column($list,'nums5')),
            array_sum(array_column($list,'nums6')),
        ];
        if($mode!=3){
            unset($totalList[0]);
        }


        return ['list'=>$list,'totalList'=>$totalList];
    }

    public function repairWorkloadExport(){
        $start = input('start',date('Y-m-d 00:00:00',strtotime('-7 day')));
        $end = input('end',date('Y-m-d 23:59:59'));
        $mode = input('mode');

        $ret = $this->repairWorkloadData($start,$end,$mode);

        $list = $ret['list'];
        $totalList = $ret['totalList'];

        $title = '';
        if($mode == 1){
            $title = '报修工作量统计报表';
        }elseif ($mode == 2){
            $title = '保洁工作量统计报表';
        }elseif ($mode == 3){
            $title = '运送工作量统计报表';
        }elseif ($mode == 4){
            $title = '应急工作量统计报表';
        }elseif ($mode == 0){
            $title = '一键呼叫工作量统计报表';
        }



        set_time_limit(0);
        ini_set("memory_limit", "1024M");



        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;">';

        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">姓名</th>';
        if($mode==3){
            $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">累计运送积分</th>';
        }
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">总数</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">待接单</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">已接单</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">已完成</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">被员工驳回</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">客观原因无法完成</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">已取消</th>';
        $res.='</tr>';

        foreach ($list 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['user_name'].'</td>';
            if($mode==3){
                $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['score'].'</td>';

            }
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['nums'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['nums1'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['nums2'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['nums3'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['nums4'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['nums5'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['nums6'].'</td>';
            $res.='</tr>';
        }
        $res.='<tr style="background: #ffffff;">';
        $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" >合计：</td>';
        foreach ($totalList as $k=>$v){
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" >'.$v.'</td>';
        }
        $res.='</tr>';

        echo $res;

    }

    public function conveyCateWorkload(){
        $start = input('start',date('Y-m-d 00:00:00',strtotime('-7 day')));
        $end = input('end',date('Y-m-d 23:59:59'));


        $lists = $this->conveyCateWorkloadData($start,$end);


        $this->assign('list',$lists);
        $this->assign('start',$start);
        $this->assign('end',$end);
        return $this->fetch();
    }


    public function conveyCateWorkloadData($start,$end){
//        $sTime = $start.' 00:00:00';
//        $eTime = $end.' 23:59:59';
        $sTime = $start;
        $eTime = $end;
        $list  = Db::name('address')
            ->field('id,title')
            ->where('enable',1)
            ->where('del',0)
            ->where('org_id',$this->orgId)
            ->where('find_in_set(2,types)')
            ->select();


        foreach ($list as $k=>$v){
            $list[$k]['nums'] = Db::name('order_convey_lis')
                ->where('org_id',$this->orgId)
                ->where('addr_id',$v['id'])
                ->where('create_time','>=',$sTime)
                ->where('create_time','<=',$eTime)
                ->count();
        }

        return $list;
    }

    public function conveyCateWorkloadExport(){
        $start = input('start',date('Y-m-d 00:00:00',strtotime('-7 day')));
        $end = input('end',date('Y-m-d 23:59:59'));

        $list = $this->conveyCateWorkloadData($start,$end);

        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', '标本');

        // 设置表格头水平居中
        $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(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);

        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($list); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $list[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $list[$i]['nums']);

        }
        //设置保存的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 conveyCateTodo(){
        $start = input('start',date('Y-m-d 00:00:00',strtotime('-7 day')));
        $end = input('end',date('Y-m-d 23:59:59'));

        $ret = $this->conveyCateTodoData($start,$end);

        $this->assign('list',$ret);
        $this->assign('start',$start);
        $this->assign('end',$end);
        return $this->fetch();
    }


    public function conveyCateTodoData($start,$end){

//        $sTime = $start.' 00:00:00';
//        $eTime = $end.' 23:59:59';
        $sTime = $start;
        $eTime = $end;
        $list  = Db::name('convey_cate')
            ->field('id,title')
            ->where('enable',1)
            ->where('del',0)
            ->where('org_id',$this->orgId)

            ->select();
        foreach ($list as $k=>$v){
            $todo = Db::name('todo')
                ->alias('t')
                ->field('t.wc_time')
                ->join('order_convey oc','oc.order_id=t.order_id')
                ->where('oc.type',$v['id'])
                ->where('t.del',0)
                ->where('t.create_time','>=',$sTime)
                ->where('t.create_time','<=',$eTime)
                ->where('t.todo_mode',3)
                ->select();
            $todoCount = Db::name('todo')
                ->alias('t')
                ->field('t.wc_time')
                ->join('order_convey oc','oc.order_id=t.order_id')
                ->where('oc.type',$v['id'])
                ->where('t.del',0)
                ->where('t.create_time','>=',$sTime)
                ->where('t.create_time','<=',$eTime)
                ->where('t.todo_mode',3)
                ->count();

            $totalTime = 0;
            foreach ($todo as $key=>$val){
                $totalTime +=$val['wc_time'];
            }
            $aveTime = $totalTime>0?round($totalTime/$todoCount/60,2):0;

            $list[$k]['count'] = isset($todoCount)?$todoCount:0;
            $list[$k]['total_time'] = isset($totalTime)?round($totalTime/60,2):0;
            $list[$k]['ave_time'] = isset($aveTime)&&$aveTime>0?$aveTime:0;

        }
        $list = $list?arraySequence($list,'count','SORT_DESC'):[];
        return $list;
    }

    public function conveyCateTodoExport(){
        $start = input('start',date('Y-m-d 00:00:00',strtotime('-7 day')));
        $end = input('end',date('Y-m-d 23:59:59'));


        $list = $this->conveyCateTodoData($start,$end);

        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', '平均时长(分)');

        // 设置表格头水平居中
        $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('D1')->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)->getStyle('D')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(30);

        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($list); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $list[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $list[$i]['count']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $list[$i]['total_time']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $list[$i]['ave_time']);

        }
        //设置保存的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');

    }


}