jili2/application/admin/controller/RepairStatistics.php

<?php
namespace app\admin\controller;

use think\App;
use think\Db;

class RepairStatistics extends Auth
{

    public function addrType(){
        $start = input('start',date('Y-m'));
        $end = input('end',date('Y-m'));

        if($start > $end){
            $this->error('开始时间不能大于结束时间');
        }

        $map[] = ['o.create_yyyymm','>=',date('Ym',strtotime($start))];
        $map[] = ['o.create_yyyymm','<=',date('Ym',strtotime($end))];

        $typeIds = input('typeIds');
        if($typeIds !=''){
            $where[] = ['id','in',$typeIds];
        }

        $mode = [
            ['type'=>1,'title'=>'需求数','value'=>[1]],
            ['type'=>2,'title'=>'已派工','value'=>[4]],
            ['type'=>3,'title'=>'已完成','value'=>[5,6]],
            ['type'=>4,'title'=>'已取消','value'=>[2,3]],
        ];

        $addr = Db::name('address')
            ->field('id,title')
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->where('enable',1)
            ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
            ->select();

        $where[] = ['org_id','=',$this->orgId];
        $type = Db::name('order_type')
            ->where($where)
            ->where('parent_id',0)
            ->where('del',0)
            ->where('enable',1)
            ->select();
        $type = $type?$type:[];

        $lists = [];
        foreach ($addr as $k=>$v){
            $a = [
                'title' => $v['title'],
                'total' => '',
                'status' => '',
            ];
            foreach ($type as $key=>$val){
                $a['type'.$val['id']] = '';
            }
            $lists[] = $a;
            foreach ($mode as $kk=>$vv){
                $b = [
                    'title' => '',
                    'status' => $vv['title'],
                ];
                $total = 0;
                foreach ($type as $key=>$val){
                    $subTypeId = Db::name('order_type')
                        ->where('parent_id',$val['id'])
                        ->where('del',0)
                        ->where('enable',1)
                        ->where('org_id',$this->orgId)
                        ->column('id');
                    $count  = Db::name('orders')
                        ->alias('o')
                        ->join('order_repair or','or.order_id=o.id')
                        ->where('o.work_type_mode',1)
                        ->where('o.del',0)
                        ->where($map)
                        ->whereIn('o.order_mode',$vv['value'])
                        ->where('or.address_id',$v['id'])
                        ->whereIn('or.type_id',$subTypeId)
                        ->count();
                    $total += $count;
                    $b['type'.$val['id']] = $count;
                }
                $b['total'] = $total;
                $lists[] = $b;
            }
        }

        $this->assign('lists',$lists);
        $this->assign('addr',$addr);
        $this->assign('type',$type);

        $typeList = Db::name('order_type')
            ->where('org_id',$this->orgId)
            ->where('parent_id',0)
            ->where('del',0)
            ->where('enable',1)
            ->select();
        $this->assign('typeList',$typeList);

        $this->assign('start',$start);
        $this->assign('end',$end);
        $this->assign('typeIds',$typeIds);
        $this->assign('typeIds2',!empty($typeIds)?explode(',',$typeIds):[]);

        return  $this->fetch();
    }


    public function addrTypeExport(){
        $start = input('start',date('Y-m'));
        $end = input('end',date('Y-m'));

        if($start > $end){
            $this->error('开始时间不能大于结束时间');
        }

        $map[] = ['o.create_yyyymm','>=',date('Ym',strtotime($start))];
        $map[] = ['o.create_yyyymm','<=',date('Ym',strtotime($end))];

        $typeIds = input('typeIds','','trim');
        if($typeIds !=''){
            $where[] = ['id','in',$typeIds];
        }

        $mode = [
            ['type'=>1,'title'=>'需求数','value'=>[1]],
            ['type'=>2,'title'=>'已派工','value'=>[4]],
            ['type'=>3,'title'=>'已完成','value'=>[5,6]],
            ['type'=>4,'title'=>'已取消','value'=>[2,3]],
        ];

        $addr = Db::name('address')
            ->field('id,title')
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->where('enable',1)
            ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
            ->select();

        $where[] = ['org_id','=',$this->orgId];
        $type = Db::name('order_type')
            ->where($where)
            ->where('parent_id',0)
            ->where('del',0)
            ->where('enable',1)
            ->select();
        $type = $type?$type:[];

        $lists = [];
        foreach ($addr as $k=>$v){
            $a = [
                'title' => $v['title'],
                'total' => '',
            ];
            foreach ($type as $key=>$val){
                $a['type'.$val['id']] = '';
            }
            $lists[] = $a;
            foreach ($mode as $kk=>$vv){
                $b = [
                    'title' => $vv['title'],
                ];
                $total = 0;
                foreach ($type as $key=>$val){
                    $subTypeId = Db::name('order_type')
                        ->where('parent_id',$val['id'])
                        ->where('del',0)
                        ->where('enable',1)
                        ->where('org_id',$this->orgId)
                        ->column('id');
                    $count  = Db::name('orders')
                        ->alias('o')
                        ->join('order_repair or','or.order_id=o.id')
                        ->where('o.work_type_mode',1)
                        ->where('o.del',0)
                        ->where($map)
                        ->whereIn('o.order_mode',$vv['value'])
                        ->where('or.address_id',$v['id'])
                        ->whereIn('or.type_id',$subTypeId)
                        ->count();
                    $total += $count;
                    $b['type'.$val['id']] = $count;
                }
                $b['total'] = $total;
                $lists[] = $b;
            }
        }

        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        //实例化PHPExcel类
        $objPHPExcel =new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T');
        foreach ($arr as $k=>$v) {
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
        }
        $sheet->setCellValueByColumnAndRow(0, 1, '地点');
        if($type){
            foreach ($type as $k=>$v){
                $sheet->setCellValueByColumnAndRow($k+1, 1, $v['title']);
            }
        }
        $sheet->setCellValueByColumnAndRow(count($type)+1, 1, '总数');

        foreach ($lists as $k=>$v) {
            $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['title']);
            foreach ($type as $kk=>$vv){
                $sheet->setCellValueByColumnAndRow($kk+1, $k + 2, $v['type'.$vv['id']]);
            }
            $sheet->setCellValueByColumnAndRow(count($type)+1, $k + 2, $v['total']);
        }

        //设置保存的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 todoXyTime(){
        $list = Db::name('todo')
            ->where('work_type_mode',1)
            ->where('del',0)
            ->where('todo_mode','in',[2,3])
            ->where('org_id',$this->orgId)
            ->order('id desc')
            ->select();
        $count = Db::name('todo')
            ->where('work_type_mode',1)
            ->where('del',0)
            ->where('todo_mode','in',[2,3])
            ->where('org_id',$this->orgId)
            ->order('id desc')
            ->sum('xy_time');
        foreach ($list as $k=>$v){
            $subType  = Db::name('order_type')
                ->alias('ot')
                ->join('order_repair or','or.type_id=ot.id')
                ->where('or.order_id',$v['order_id'])
                ->value('type_id');
            $parentId = Db::name('order_type')->where('id',$subType)->value('parent_id');
            $list[$k]['type_title'] = Db::name('order_type')->where('id',$parentId)->value('title');
            $list[$k]['content'] = Db::name('orders')->where('id',$v['order_id'])->value('content');
            $list[$k]['dep'] = Db::name('orders')->alias('o')->join('dep d','d.id=o.dep_id')->where('o.id',$v['order_id'])->value('d.title');
            $list[$k]['user_name'] = Db::name('user')->where('id',$v['to_user_id'])->value('real_name');
            $list[$k]['xy_time'] = round($v['xy_time']/60,1);
        }

        $this->assign('count',round($count/60,1));
        $this->assign('list',$list);

        return  $this->fetch();
    }

    public function xyTimeEcharts(){
        $curTime = date('Y-m-d H:i:s');
        $time = [
            ['title'=>'24小时内','sTime'=>date('Y-m-d H:i:s',strtotime($curTime)-3600*24),'eTime'=>$curTime,'type'=>1],
            ['title'=>'24~48小时','sTime'=>date('Y-m-d H:i:s',strtotime($curTime)-3600*48),'eTime'=>date('Y-m-d H:i:s',strtotime($curTime)-3600*24),'type'=>1],
            ['title'=>'超过72小时','sTime'=>'','eTime'=>date('Y-m-d H:i:s',strtotime($curTime)-3600*72),'type'=>2],
            ['title'=>'未响应','sTime'=>'','eTime'=>'','type'=>3],
            ['title'=>'总数','sTime'=>'','eTime'=>'','type'=>4],
        ];
        $list = [];
        foreach ($time as $k=>$v){
            if($v['type'] == 1){
                $count = Db::name('todo')
                    ->where('org_id',$this->orgId)
                    ->where('work_type_mode',1)
                    ->where('del',0)
                    ->where('create_time','>',$v['sTime'])
                    ->where('create_time','<=',$v['eTime'])
                    ->where('todo_mode','in',[2,3])
                    ->count();
            }elseif ($v['type'] == 2){
                $count = Db::name('todo')
                    ->where('org_id',$this->orgId)
                    ->where('work_type_mode',1)
                    ->where('del',0)
                    ->where('create_time','<',$v['eTime'])
                    ->where('todo_mode','in',[2,3])
                    ->count();
            }elseif ($v['type'] == 3){
                $count = Db::name('todo')
                    ->where('org_id',$this->orgId)
                    ->where('work_type_mode',1)
                    ->where('del',0)
                    ->where('todo_mode',1)
                    ->count();
            }elseif($v['type'] == 4){
                $count = Db::name('todo')
                    ->where('org_id',$this->orgId)
                    ->where('work_type_mode',1)
                    ->where('del',0)
                    ->count();
            }

            $list[$k]['title'] = $v['title'];
            $list[$k]['value'] = $count;
        }

        $data = [
            'title'=>array_column($time,'title'),
            'list'=>$list,
        ];

        $this->success('','',$data);

    }
    public function xyTimeExport(){

        $list = Db::name('todo')
            ->where('work_type_mode',1)
            ->where('del',0)
            ->where('todo_mode','in',[2,3])
            ->where('org_id',$this->orgId)
            ->order('id desc')
            ->select();
        $count = Db::name('todo')
            ->where('work_type_mode',1)
            ->where('del',0)
            ->where('todo_mode','in',[2,3])
            ->where('org_id',$this->orgId)
            ->order('id desc')
            ->sum('xy_time');
        foreach ($list as $k=>$v){
            $subType  = Db::name('order_type')
                ->alias('ot')
                ->join('order_repair or','or.type_id=ot.id')
                ->where('or.order_id',$v['order_id'])
                ->value('type_id');
            $parentId = Db::name('order_type')->where('id',$subType)->value('parent_id');
            $list[$k]['type_title'] = Db::name('order_type')->where('id',$parentId)->value('title');
            $list[$k]['content'] = Db::name('orders')->where('id',$v['order_id'])->value('content');
            $list[$k]['dep'] = Db::name('orders')->alias('o')->join('dep d','d.id=o.dep_id')->where('o.id',$v['order_id'])->value('d.title');
            $list[$k]['user_name'] = Db::name('user')->where('id',$v['to_user_id'])->value('real_name');
            $list[$k]['xy_time'] = round($v['xy_time']/60,1);
        }

        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        //实例化PHPExcel类
        $objPHPExcel =new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N');
        foreach ($arr as $k=>$v) {
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
        }
        $sheet->setCellValueByColumnAndRow(0, 1, '类型');
        $sheet->setCellValueByColumnAndRow(1, 1, '部门');
        $sheet->setCellValueByColumnAndRow(2, 1, '员工');
        $sheet->setCellValueByColumnAndRow(3, 1, '需求描述');
        $sheet->setCellValueByColumnAndRow(4, 1, '状态');
        $sheet->setCellValueByColumnAndRow(5, 1, '响应时间');
        $sheet->setCellValueByColumnAndRow(6, 1, '合计');

        foreach ($list as $k=>$v) {
            $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['type_title']);
            $sheet->setCellValueByColumnAndRow(1, $k + 2, $v['dep']);
            $sheet->setCellValueByColumnAndRow(2, $k + 2, $v['user_name']);
            $sheet->setCellValueByColumnAndRow(3, $k + 2, $v['content']);
            if($v['todo_mode'] == 2){
                $sheet->setCellValueByColumnAndRow(4, $k + 2, '进行中');
            }
            $sheet->setCellValueByColumnAndRow(5, $k + 2, $v['xy_time']);
        }
        $sheet->setCellValueByColumnAndRow(6, $k + 2, round($count/60,1));
        //设置保存的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 addrWorkHour(){
        $start = input('start',date('Y-m'));
        $end = input('end',date('Y-m'));

        if($start > $end){
            $this->error('开始时间不能大于结束时间');
        }

        $map[] = ['t.create_yyyymm','>=',date('Ym',strtotime($start))];
        $map[] = ['t.create_yyyymm','<=',date('Ym',strtotime($end))];

        $addrIds= input('addrIds','','trim');
        if($addrIds !=''){
            $where[] = ['id','in',$addrIds];
        }
        $where[] = ['org_id','=',$this->orgId];
        $addr = Db::name('address')
            ->field('id,title')
            ->where($where)
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->where('enable',1)
            ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
            ->select();
        $addr = $addr?$addr:[];
        $list = [];
        $total = $total2 = 0;
        foreach ($addr as $k=>$v){
            $count = Db::name('todo')
                ->alias('t')
                ->join('order_repair or','or.order_id=t.order_id')
                ->where('or.address_id',$v['id'])
                ->where($map)
                ->where('t.del',0)
                ->where('t.work_type_mode',1)
                ->where('t.org_id',$this->orgId)
                ->where('t.todo_mode',3)
                ->count();
            $total +=$count;
            $count2 = Db::name('todo')
                ->alias('t')
                ->join('order_repair or','or.order_id=t.order_id')
                ->where('or.address_id',$v['id'])
                ->where($map)
                ->where('t.del',0)
                ->where('t.work_type_mode',1)
                ->where('t.org_id',$this->orgId)
                ->where('t.todo_mode',3)
                ->sum('wc_time');
            $total2 +=$count2;
            $list[$k]['title'] = $v['title'];
            $list[$k]['count'] = $count;
            $list[$k]['count2'] = round($count2/60,1);
        }

        $this->assign('list',$list);
        $this->assign('total',$total);
        $this->assign('total2',round($total2/60,1));

        $addrList = Db::name('address')
            ->field('id,title')
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->where('enable',1)
            ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
            ->select();
        $this->assign('addrList',$addrList);

        $this->assign('start',$start);
        $this->assign('end',$end);
        $this->assign('addrIds',$addrIds);
        $this->assign('addrIds2',!empty($addrIds)?explode(',',$addrIds):[]);

        return  $this->fetch();
    }

    public function addrWorkHourExport(){
        $start = input('start',date('Y-m'));
        $end = input('end',date('Y-m'));

        if($start > $end){
            $this->error('开始时间不能大于结束时间');
        }

        $map[] = ['t.create_yyyymm','>=',date('Ym',strtotime($start))];
        $map[] = ['t.create_yyyymm','<=',date('Ym',strtotime($end))];

        $addrIds= input('addrIds','','trim');
        if($addrIds !=''){
            $where[] = ['id','in',$addrIds];
        }
        $where[] = ['org_id','=',$this->orgId];
        $addr = Db::name('address')
            ->field('id,title')
            ->where($where)
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->where('enable',1)
            ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
            ->select();
        $addr = $addr?$addr:[];
        $list = [];
        $total = $total2 = 0;
        foreach ($addr as $k=>$v){
            $count = Db::name('todo')
                ->alias('t')
                ->join('order_repair or','or.order_id=t.order_id')
                ->where('or.address_id',$v['id'])
                ->where($map)
                ->where('t.del',0)
                ->where('t.work_type_mode',1)
                ->where('t.org_id',$this->orgId)
                ->where('t.todo_mode',3)
                ->count();
            $total +=$count;
            $count2 = Db::name('todo')
                ->alias('t')
                ->join('order_repair or','or.order_id=t.order_id')
                ->where('or.address_id',$v['id'])
                ->where($map)
                ->where('t.del',0)
                ->where('t.work_type_mode',1)
                ->where('t.org_id',$this->orgId)
                ->where('t.todo_mode',3)
                ->sum('wc_time');
            $total2 +=$count2;
            $list[$k]['title'] = $v['title'];
            $list[$k]['count'] = $count;
            $list[$k]['count2'] = round($count2/60,1);
        }


        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        //实例化PHPExcel类
        $objPHPExcel =new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N');
        foreach ($arr as $k=>$v) {
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
        }
        $sheet->setCellValueByColumnAndRow(0, 1, '地点');
        $sheet->setCellValueByColumnAndRow(1, 1, '工单数');
        $sheet->setCellValueByColumnAndRow(2, 1, '所需工时');


        foreach ($list as $k=>$v) {
            $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['title']);
            $sheet->setCellValueByColumnAndRow(1, $k + 2, $v['count']);
            $sheet->setCellValueByColumnAndRow(2, $k + 2, $v['count2']);
        }
        $sheet->setCellValueByColumnAndRow(0, count($list)+3, '合计');
        $sheet->setCellValueByColumnAndRow(1, count($list)+3, '总工单数');
        $sheet->setCellValueByColumnAndRow(2, count($list)+3, $total);

        $sheet->setCellValueByColumnAndRow(1, count($list)+4, '总工时');
        $sheet->setCellValueByColumnAndRow(2, count($list)+4, round($total2/60,1));

        $sheet->setCellValueByColumnAndRow(1, count($list)+5, '平均工时');
        if($total > 0){
            $sheet->setCellValueByColumnAndRow(2, count($list)+5, round($total2/60/$total,1));
        }else{
            $sheet->setCellValueByColumnAndRow(2, count($list)+5, 0);
        }

        //设置保存的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 typeWorkHour(){
        $start = input('start',date('Y-m'));
        $end = input('end',date('Y-m'));

        if($start > $end){
            $this->error('开始时间不能大于结束时间');
        }

        $map[] = ['t.create_yyyymm','>=',date('Ym',strtotime($start))];
        $map[] = ['t.create_yyyymm','<=',date('Ym',strtotime($end))];

        $typeIds = input('typeIds');
        if($typeIds !=''){
            $where[] = ['id','in',$typeIds];
        }


        $addr = Db::name('address')
            ->field('id,title')
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->where('enable',1)
            ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
            ->select();

        $where[] = ['org_id','=',$this->orgId];
        $type = Db::name('order_type')
            ->where($where)
            ->where('parent_id',0)
            ->where('del',0)
            ->where('enable',1)
            ->select();
        $type = $type?$type:[];
        $newType = [];

        foreach ($type as $k=>$v){
            $a = [
                'title'=>$v['title'],
                'id'=>$v['id'],
                'type'=>1
            ];
            $newType[]= $a;
            $b= [
                'title'=>$v['title'].'工时',
                'id'=>$v['id'],
                'type'=>2
            ];
            $newType[]= $b;
        }
        $this->assign('newType',$newType);

        $lists = [];

        $total = $total2 = 0 ;
        foreach ($addr as $k=>$v){
            $a = [
                'title' => $v['title'],
            ];
            foreach ($newType as $key=>$val){

                $subTypeId = Db::name('order_type')
                    ->where('parent_id',$val['id'])
                    ->where('del',0)
                    ->where('enable',1)
                    ->where('org_id',$this->orgId)
                    ->column('id');
                if($val['type']==1){
                    $count  = Db::name('todo')
                        ->alias('t')
                        ->join('order_repair or','or.order_id=t.order_id')
                        ->where('t.work_type_mode',1)
                        ->where('t.del',0)
                        ->where($map)
                        ->where('t.todo_mode',3)
                        ->where('or.address_id',$v['id'])
                        ->whereIn('or.type_id',$subTypeId)
                        ->count();

                    $total +=$count;
                }elseif ($val['type']==2){
                    $wx_time  = Db::name('todo')
                        ->alias('t')
                        ->join('order_repair or','or.order_id=t.order_id')
                        ->where('t.work_type_mode',1)
                        ->where('t.del',0)
                        ->where($map)
                        ->where('t.todo_mode',3)
                        ->where('or.address_id',$v['id'])
                        ->whereIn('or.type_id',$subTypeId)
                        ->sum('wc_time');
                    $count = round($wx_time/60,1);
                    $total2 +=$count;
                }
                $a['type'.$key] = $count;
            }
            $lists[] = $a;
        }

        $this->assign('lists',$lists);
        $this->assign('total',$total);
        $this->assign('total2',$total2);
        $this->assign('addr',$addr);
        $this->assign('type',$type);

        $typeList = Db::name('order_type')
            ->where('org_id',$this->orgId)
            ->where('parent_id',0)
            ->where('del',0)
            ->where('enable',1)
            ->select();
        $this->assign('typeList',$typeList);

        $this->assign('start',$start);
        $this->assign('end',$end);
        $this->assign('typeIds',$typeIds);
        $this->assign('typeIds2',!empty($typeIds)?explode(',',$typeIds):[]);

        return  $this->fetch();
    }

    public function typeWorkHourExport(){
        $start = input('start',date('Y-m'));
        $end = input('end',date('Y-m'));

        if($start > $end){
            $this->error('开始时间不能大于结束时间');
        }

        $map[] = ['t.create_yyyymm','>=',date('Ym',strtotime($start))];
        $map[] = ['t.create_yyyymm','<=',date('Ym',strtotime($end))];

        $typeIds = input('typeIds');
        if($typeIds !=''){
            $where[] = ['id','in',$typeIds];
        }


        $addr = Db::name('address')
            ->field('id,title')
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->where('enable',1)
            ->where('','exp',Db::raw("FIND_IN_SET(1,types)"))
            ->select();

        $where[] = ['org_id','=',$this->orgId];
        $type = Db::name('order_type')
            ->where($where)
            ->where('parent_id',0)
            ->where('del',0)
            ->where('enable',1)
            ->select();
        $type = $type?$type:[];
        $newType = [];

        foreach ($type as $k=>$v){
            $a = [
                'title'=>$v['title'],
                'id'=>$v['id'],
                'type'=>1
            ];
            $newType[]= $a;
            $b= [
                'title'=>$v['title'].'工时',
                'id'=>$v['id'],
                'type'=>2
            ];
            $newType[]= $b;
        }
        $this->assign('newType',$newType);

        $lists = [];

        $total = $total2 = 0 ;
        foreach ($addr as $k=>$v){
            $a = [
                'title' => $v['title'],
            ];
            foreach ($newType as $key=>$val){

                $subTypeId = Db::name('order_type')
                    ->where('parent_id',$val['id'])
                    ->where('del',0)
                    ->where('enable',1)
                    ->where('org_id',$this->orgId)
                    ->column('id');
                if($val['type']==1){
                    $count  = Db::name('todo')
                        ->alias('t')
                        ->join('order_repair or','or.order_id=t.order_id')
                        ->where('t.work_type_mode',1)
                        ->where('t.del',0)
                        ->where($map)
                        ->where('t.todo_mode',3)
                        ->where('or.address_id',$v['id'])
                        ->whereIn('or.type_id',$subTypeId)
                        ->count();

                    $total +=$count;
                }elseif ($val['type']==2){
                    $wx_time  = Db::name('todo')
                        ->alias('t')
                        ->join('order_repair or','or.order_id=t.order_id')
                        ->where('t.work_type_mode',1)
                        ->where('t.del',0)
                        ->where($map)
                        ->where('t.todo_mode',3)
                        ->where('or.address_id',$v['id'])
                        ->whereIn('or.type_id',$subTypeId)
                        ->sum('wc_time');
                    $count = round($wx_time/60,1);
                    $total2 +=$count;
                }
                $a['type'.$key] = $count;
            }
            $lists[] = $a;
        }


        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        //实例化PHPExcel类
        $objPHPExcel =new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T');
        foreach ($arr as $k=>$v) {
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
        }
        $sheet->setCellValueByColumnAndRow(0, 1, '地点');
        foreach ($newType as $k=>$v){
            $sheet->setCellValueByColumnAndRow($k+1, 1, $v['title']);
        }

        foreach ($lists as $k=>$v) {
            $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['title']);
            foreach ($newType as $kk=>$vv){
                $sheet->setCellValueByColumnAndRow($kk+1, $k + 2, $v['type'.$kk]);
            }

        }
        $sheet->setCellValueByColumnAndRow(0, count($lists)+3, '合计');
        $sheet->setCellValueByColumnAndRow(1, count($lists)+3, '总工单数');
        $sheet->setCellValueByColumnAndRow(2, count($lists)+3, $total);

        $sheet->setCellValueByColumnAndRow(1, count($lists)+4, '总工时');
        $sheet->setCellValueByColumnAndRow(2, count($lists)+4, $total2);

        $sheet->setCellValueByColumnAndRow(1, count($lists)+5, '平均工时');
        if($total > 0){
            $sheet->setCellValueByColumnAndRow(2, count($lists)+5, $total2/$total);
        }else{
            $sheet->setCellValueByColumnAndRow(2, count($lists)+5, 0);
        }

        //设置保存的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 consumables(){
        $month = input('month',date('Y-m'));

//        $curYear = date('Y',strtotime($month));
//        $curMonth = date('m',strtotime($month));

        $map[] = ['create_yyyymm','=',date('Ym',strtotime($month))];

        $goodsIds = input('goodsIds');
        if($goodsIds !=''){
            $where[] = ['id','in',$goodsIds];
        }
        $where[] = ['org_id','=',$this->orgId];
        $where[] = ['del','=',0];
        $where[] = ['enable','=',1];
        $mate = Db::name('mate_goods')
            ->field('id,title')
            ->where($where)
            ->select();

        $this->assign('mate',$mate);
        //获取某年某个月的总天数
//        $t =  cal_days_in_month(CAL_GREGORIAN,$curMonth,$curYear);
        $t = date('t',strtotime($month));

        $sDay = strtotime($month.'-01');

        $arr = [];
        for ($i = 0;$i<$t;$i++){
            $arr[$i]['date'] = date('m-d',$sDay+$i*86400);
            $arr[$i]['ymd'] = date('Y-m-d',$sDay+$i*86400);
        }

        $lists = [];
        foreach ($arr as $k=>$v){
            $a = [
                'title' => $v['date'],
            ];
            foreach ($mate as $key=>$val){
                $count = Db::name('todo_mate_item')
                    ->where('items_id',$val['id'])
                    ->where('create_time','>=',$v['ymd'].' 00:00:00')
                    ->where('create_time','<=',$v['ymd'].' 23:59:59')
                    ->sum('total');
                $a['type'.$val['id']] = $count;
            }
            $lists[] = $a;
        }
        $subtotal = []; $total = 0 ;
        foreach ($mate as $k=>$v){
            $c = Db::name('todo_mate_item')
                ->where('items_id',$v['id'])
                ->where('create_time','>=',$month.'-01 00:00:00')
                ->where('create_time','<=',$month.'-31 23:59:59')
                ->sum('total');
            $total +=$c;
            $subtotal[$k] = $c;
        }

        $this->assign('lists',$lists);
        $this->assign('subtotal',$subtotal);
        $this->assign('total',$total);


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

        $this->assign('mateGoods',$mateGoods);
        $this->assign('month',$month);
        $this->assign('goodsIds',$goodsIds);
        $this->assign('goodsIds2',!empty($goodsIds)?explode(',',$goodsIds):[]);
        return  $this->fetch();
    }

    public function consumablesExport(){
        $month = input('month',date('Y-m'));

//        $curYear = date('Y',strtotime($month));
//        $curMonth = date('m',strtotime($month));

        $map[] = ['create_yyyymm','=',date('Ym',strtotime($month))];

        $goodsIds = input('goodsIds');
        if($goodsIds !=''){
            $where[] = ['id','in',$goodsIds];
        }
        $where[] = ['org_id','=',$this->orgId];
        $where[] = ['del','=',0];
        $where[] = ['enable','=',1];
        $mate = Db::name('mate_goods')
            ->field('id,title')
            ->where($where)
            ->select();
        // $this->assign('mate',$mate);
        //获取某年某个月的总天数
//        $t =  cal_days_in_month(CAL_GREGORIAN,$curMonth,$curYear);
        $t = date('t',strtotime($month));
        $sDay = strtotime($month.'-01');

        $arr = [];
        for ($i = 0;$i<$t;$i++){
            $arr[$i]['date'] = date('m-d',$sDay+$i*86400);
            $arr[$i]['ymd'] = date('Y-m-d',$sDay+$i*86400);
        }

        $lists = [];
        foreach ($arr as $k=>$v){
            $a = [
                'title' => $v['date'],
            ];
            foreach ($mate as $key=>$val){
                $count = Db::name('todo_mate_item')
                    ->where('items_id',$val['id'])
                    ->where('create_time','>=',$v['ymd'].' 00:00:00')
                    ->where('create_time','<=',$v['ymd'].' 23:59:59')
                    ->sum('total');
                $a['type'.$val['id']] = $count;
            }
            $lists[] = $a;
        }
        $subtotal = []; $total = 0 ;
        foreach ($mate as $k=>$v){
            $c = Db::name('todo_mate_item')
                ->where('items_id',$v['id'])
                ->where('create_time','>=',$month.'-01 00:00:00')
                ->where('create_time','<=',$month.'-31 23:59:59')
                ->sum('total');
            $total +=$c;
            $subtotal[$k] = $c;
        }
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        //实例化PHPExcel类
        $objPHPExcel =new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        $arr2 = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T');
        foreach ($arr2 as $k=>$v) {
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
        }
        $sheet->setCellValueByColumnAndRow(0, 1, '日期');
        foreach ($mate as $k=>$v){
            $sheet->setCellValueByColumnAndRow($k+1, 1, $v['title']);
        }

        foreach ($lists as $k=>$v) {
            $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['title']);
            foreach ($mate as $kk=>$vv){
                $sheet->setCellValueByColumnAndRow($kk+1, $k + 2, $v['type'.$vv['id']]);
                    
            }

        }
        $sheet->setCellValueByColumnAndRow(0, count($lists)+3, '小计');
        foreach ($subtotal as $k=>$v){
            $sheet->setCellValueByColumnAndRow($k+1, count($lists)+3, $v);
        }


        $sheet->setCellValueByColumnAndRow(0, count($lists)+4, '总计');
        $sheet->setCellValueByColumnAndRow(1, count($lists)+4, $total);


        //设置保存的Excel表格名称
        $filename = '报修耗材报表_' . date('YmdHis', time()) . '.xlsx';
        //设置当前激活的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, 'Excel2007');
        //下载文件在浏览器窗口
        return $objWriter->save('php://output');

    }

}