guoyao-jiankang/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'));

        $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');

        $this->assign('data',$data);
        $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',strtotime('-7 day')));
        $end = input('end',date('Y-m-d'));
        $mode = input('mode');
        if(request()->isAjax()){
            $sTime = $start.' 00:00:00';
            $eTime = $end.' 23:59:59';

            $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]['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();
            }

            $result['rows'] = $list;

            return json($result);
        }else{
            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 repairWorkloadExport(){
        $start = input('start',date('Y-m-d',strtotime('-7 day')));
        $end = input('end',date('Y-m-d'));
        $mode = input('mode');
        $sTime = $start.' 00:00:00';
        $eTime = $end.' 23:59:59';

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

        $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]['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();
        }

        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', '已接单')
            ->setCellValue('E1', '已完成')
            ->setCellValue('F1', '被员工驳回')
            ->setCellValue('G1', '客观原因无法完成')
            ->setCellValue('H1', '已取消');
        // 设置表格头水平居中
        $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('E1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->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)->getStyle('E')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->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);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(30);

        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($list); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $list[$i]['user_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $list[$i]['nums']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $list[$i]['nums1']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $list[$i]['nums2']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $list[$i]['nums3']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), $list[$i]['nums4']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . ($i + 2), $list[$i]['nums5']);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . ($i + 2), $list[$i]['nums6']);

        }
        //设置保存的Excel表格名称
        $filename = $title.'_' . date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle($title);
        //设置浏览器窗口下载表格
        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 conveyCateWorkload(){
        $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('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();
            }

            $result['rows'] = $list;
            return json($result);
        }else{

            $this->assign('start',$start);
            $this->assign('end',$end);
            return $this->fetch();
        }
    }
    public function conveyCateWorkloadExport(){
        $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('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();
        }

        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',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('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;

            }

            $result['rows'] = $list;
            return json($result);
        }else{
            $this->assign('start',$start);
            $this->assign('end',$end);
            return $this->fetch();
        }
    }

    public function conveyCateTodoExport(){
        $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('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;
        }
        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('B' . ($i + 2), $list[$i]['total_time']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($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');

    }


}