jili2/application/admin/controller/PatrolStatistics.php

<?php

namespace app\admin\controller;
use function PHPSTORM_META\map;
use think\App;
use think\Db;

class PatrolStatistics extends Auth {
    public function patrol() {
        $cur = date('Y-m-d');
        $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
        $end = input('end', date('Y-m-d'));
        $start1 = $start . ' 00:00:00';
        $end1 = $end . ' 23:59:59';
        $type = input('type', 1);
        $list = $this->patrolData($start1, $end1, $type);
        $this->assign('type', $type);
        $this->assign('list', $list);
        $this->assign('start', $start);
        $this->assign('end', $end);
        return $this->fetch();
    }
    public function patrolData($start1, $end1, $type) {
        $map1[] = ['create_time', '>=', $start1];
        $map1[] = ['create_time', '<=', $end1];
        $map1[] = ['org_id', '=', $this->orgId];
        $list = Db::name('patrol_record')
            ->where($map1)
            ->where('patrol_mode', $type)
            ->group('patrol_addr_id')
            ->distinct(true)
            ->select();
        foreach ($list as $k => $v) {
            $list[$k]['title'] = Db::name('address')
                ->where('id', $v['patrol_addr_id'])
                ->value('title');
            $c = Db::name('patrol_record')
                ->where($map1)
                ->where('is_normal', 0)
                ->where('patrol_addr_id', $v['patrol_addr_id'])
                ->where('patrol_mode', $type)
                ->count();
            $list[$k]['count'] = $c ? $c : 0;
            $normal = Db::name('patrol_record')
                ->where($map1)
                ->where('is_normal', 1)
                ->where('patrol_addr_id', $v['patrol_addr_id'])
                ->where('patrol_mode', $type)
                ->count();
            $list[$k]['normal'] = $normal ? $normal : 0;
        }
        return $list;
    }
    public function patrolExport() {
        $cur = date('Y-m-d');
        $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
        $end = input('end', date('Y-m-d'));
        $start1 = $start . ' 00:00:00';
        $end1 = $end . ' 23:59:59';
        $type = input('type', 1);
        $ret = $this->patrolData($start1, $end1, $type);
        include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
        //实例化PHPExcel类
        $objPHPExcel = new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '智慧点')
            ->setCellValue('B1', '总数')
            ->setCellValue('C1', '总数(异常)');
        // 设置表格头水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($ret); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $ret[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $ret[$i]['count']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $ret[$i]['normal']);
        }
        $n = Db::name('patrol_mode')
            ->where('id', $type)
            ->value('name');
        //设置保存的Excel表格名称
        $filename = $n . '工作量统计分析_' . date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle($n . '工作量统计分析');
        //设置浏览器窗口下载表格
        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 work() {
        $cur = date('Y-m-d');
        $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
        $end = input('end', date('Y-m-d'));
        $start1 = $start . ' 00:00:00';
        $end1 = $end . ' 23:59:59';
        $type = input('type', 1);
        $list = $this->workData($start1, $end1, $type);
        $this->assign('type', $type);
        $this->assign('list', $list);
        $this->assign('start', $start);
        $this->assign('end', $end);
        return $this->fetch();
    }
    public function workData($start1, $end1, $type) {
        $map1[] = ['create_time', '>=', $start1];
        $map1[] = ['create_time', '<=', $end1];
        $map1[] = ['org_id', '=', $this->orgId];
        $list = Db::name('patrol_record')
            ->where($map1)
            ->where('patrol_mode', $type)
            ->group('user_id')
            ->distinct(true)
            ->select();
        foreach ($list as $k => $v) {
            $list[$k]['title'] = Db::name('user')
                ->where('id', $v['user_id'])
                ->value('real_name');
            $c = Db::name('patrol_record')
                ->where($map1)
                ->where('user_id', $v['user_id'])
                ->where('patrol_mode', $type)
                ->count();
            $list[$k]['count'] = $c ? $c : 0;

            $c1 = Db::name('patrol_record')
                ->where($map1)
                ->where('user_id', $v['user_id'])
                ->where('patrol_mode', $type)
                ->where('is_normal', 0)
                ->count();
            $list[$k]['count1'] = $c1 ? $c1 : 0;
            $c2 = Db::name('patrol_record')
                ->where($map1)
                ->where('user_id', $v['user_id'])
                ->where('patrol_mode', $type)
                ->where('is_normal', 1)
                ->count();
            $list[$k]['count2'] = $c2 ? $c2 : 0;

            $taskCount = Db::name('patrol_task_user')
                ->alias('a')
                ->join('patrol_task b','a.patrol_task_id=b.id')
                ->where('a.user_id',$v['user_id'])
                ->where('b.org_id',$this->orgId)
                ->where('b.status','<>',6)
                ->where('b.start_time','>=',$start1)
                ->where('b.end_time','<=',$end1)
                ->where('b.patrol_mode','=',$type)
                ->count();
            $ywcCount = Db::name('patrol_task_user')
                ->alias('a')
                ->join('patrol_task b','a.patrol_task_id=b.id')
                ->where('a.user_id',$v['user_id'])
                ->where('b.org_id',$this->orgId)
                ->where('b.status','=',2)
                ->where('b.start_time','>=',$start1)
                ->where('b.end_time','<=',$end1)
                ->where('b.patrol_mode','=',$type)
                ->count();

            $wcl = $taskCount>0?round($ywcCount/$taskCount,2)*100:0;
            $wcl = $wcl.'%';
            $list[$k]['wcl'] = $wcl;
            $list[$k]['count3'] = $ywcCount;
            $list[$k]['count4'] = $taskCount;


        }
        return $list;
    }
    public function workList(){

        $user_id = input('user_id','','trim');
        if($user_id!=''){
            $map[] = ['user_id','=',$user_id];
        }
        $is_normal = input('is_normal','','trim');
        if($is_normal!==''){
            $map[] = ['is_normal','=',$is_normal];
        }
        $type = input('type','','trim');
        if($type != ''){
            $map[] = ['patrol_mode','=',$type];
        }
        $start = input('start',date('Y-m-d', strtotime('' . date('Y-m-d') . ' -1 week')));
        if($start != ''){
            $map[] = ['create_time','>=',$start.' 00:00:00'];
        }
        $end = input('end', date('Y-m-d'));
        if($end != ''){
            $map[] = ['create_time','<=',$end.' 23:59:59'];
        }
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','sort','trim');      //排序列
            $sort = input('sord','asc','trim');        //排序方式
            $order = $sortRow.' '.$sort.' ,id desc';

            $map[] = ['org_id','=',$this->orgId];
            $map= empty($map) ? true: $map;

            //数据查询
            $lists = Db::name('patrol_record')
                ->where($map)
                ->limit($start,$length)
                ->order($order)
                ->select();
            foreach ($lists as $k=>$v){
                $lists[$k]['address_title'] = Db::name('address')
                    ->where('id', $v['patrol_addr_id'])
                    ->value('title');
                $lists[$k]['task_user'] = Db::name('user')
                    ->where('id', $v['user_id'])->value('real_name');
                $lists[$k]['task_title'] = Db::name('patrol_task')
                    ->where('id', $v['patrol_task_id'])
                    ->value('title');

            }
            //数据返回
            $totalCount = Db::name('patrol_record')->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            $type = input('type',1);
            $t = Db::name('patrol_mode')
                ->where('id',$type)
                ->value('name');
            $this->assign('is_normal',$is_normal);
            $this->assign('type',$type);
            $this->assign('start',$start);
            $this->assign('end',$end);
            $this->assign('user_id',$user_id);
            $this->assign('meta_title',$t.'人员工作量统计');
            return $this->fetch();
        }
    }
    public function workList1(){
        $user_id = input('user_id','','trim');
        if($user_id!=''){
            $map[] = ['a.user_id','=',$user_id];
        }
        $is_normal = input('is_normal','','trim');
        if($is_normal!==''){
            if($is_normal==1){//总数
                $map[] = ['b.status','<>',6];
            }
            if($is_normal==2){//已完成
                $map[] = ['b.status','=',2];

            }
        }
        $type = input('type','','trim');
        if($type != ''){
            $map[] = ['b.patrol_mode','=',$type];
        }
        $start = input('start',date('Y-m-d', strtotime('' . date('Y-m-d') . ' -1 week')));
        if($start != ''){
            $map[] = ['b.start_time','>=',$start.' 00:00:00'];
        }
        $end = input('end', date('Y-m-d'));
        if($end != ''){
            $map[] = ['b.end_time','<=',$end.' 23:59:59'];
        }
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置


            $map[] = ['b.org_id','=',$this->orgId];
            $map= empty($map) ? true: $map;

            //数据查询
            $lists =  Db::name('patrol_task_user')
                ->alias('a')
                ->join('patrol_task b','a.patrol_task_id=b.id')
                ->field('b.*,a.user_id')
                ->where($map)
                ->limit($start,$length)
                ->order('b.id','desc')
                ->select();
            foreach ($lists as $k=>$v){

                $lists[$k]['task_user'] = Db::name('user')
                    ->where('id', $v['user_id'])->value('real_name');

            }
            //数据返回
            $totalCount = Db::name('patrol_task_user')
                ->alias('a')
                ->join('patrol_task b','a.patrol_task_id=b.id')->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            $type = input('type',1);
            $t = Db::name('patrol_mode')
                ->where('id',$type)
                ->value('name');
            $this->assign('is_normal',$is_normal);
            $this->assign('type',$type);
            $this->assign('start',$start);
            $this->assign('end',$end);
            $this->assign('user_id',$user_id);
            $this->assign('meta_title',$t.'人员工作量统计');
            return $this->fetch();
        }
    }
    public function workExport() {
        $cur = date('Y-m-d');
        $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
        $end = input('end', date('Y-m-d'));
        $start1 = $start . ' 00:00:00';
        $end1 = $end . ' 23:59:59';
        $type = input('type', 1);
        $ret = $this->workData($start1, $end1, $type);
        include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
        //实例化PHPExcel类
        $objPHPExcel = new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'ID')
            ->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('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);
        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($ret); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $ret[$i]['id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $ret[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $ret[$i]['count']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $ret[$i]['count1']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $ret[$i]['count2']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), $ret[$i]['wcl']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . ($i + 2), $ret[$i]['count4']);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . ($i + 2), $ret[$i]['count3']);
        }
        $n = Db::name('patrol_mode')
            ->where('id', $type)
            ->value('name');
        //设置保存的Excel表格名称
        $filename = $n . '人员工作量统计_' . date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle($n . '人员工作量统计');
        //设置浏览器窗口下载表格
        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 patrolList(){
        $patrol_addr_id = input('patrol_addr_id','','trim');
        if($patrol_addr_id!=''){
            $map[] = ['patrol_addr_id','=',$patrol_addr_id];
        }
        $is_normal = input('is_normal','','trim');
        if($is_normal!==''){
            $map[] = ['is_normal','=',$is_normal];
        }
        $type = input('type','','trim');
        if($type != ''){
            $map[] = ['patrol_mode','=',$type];
        }
        $start = input('start',date('Y-m-d', strtotime('' . date('Y-m-d') . ' -1 week')));
        if($start != ''){
            $map[] = ['create_time','>=',$start.' 00:00:00'];
        }
        $end = input('end', date('Y-m-d'));
        if($end != ''){
            $map[] = ['create_time','<=',$end.' 23:59:59'];
        }
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','sort','trim');      //排序列
            $sort = input('sord','asc','trim');        //排序方式
            $order = $sortRow.' '.$sort.' ,id desc';

            $map[] = ['org_id','=',$this->orgId];
            $map= empty($map) ? true: $map;

            //数据查询
            $lists = Db::name('patrol_record')
                ->where($map)
                ->limit($start,$length)
                ->order($order)
                ->select();
            foreach ($lists as $k=>$v){
                $lists[$k]['address_title'] = Db::name('address')
                    ->where('id', $v['patrol_addr_id'])
                    ->value('title');
                $lists[$k]['task_user'] = Db::name('user')
                    ->where('id', $v['user_id'])->value('real_name');
                $lists[$k]['task_title'] = Db::name('patrol_task')
                    ->where('id', $v['patrol_task_id'])
                    ->value('title');
            }
            //数据返回
            $totalCount = Db::name('patrol_record')->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            $type = input('type',1);
            $t = Db::name('patrol_mode')
                ->where('id',$type)
                ->value('name');
            $this->assign('is_normal',$is_normal);
            $this->assign('type',$type);
            $this->assign('start',$start);
            $this->assign('end',$end);
            $this->assign('patrol_addr_id',$patrol_addr_id);
            $this->assign('meta_title',$t.'检查记录');
            return $this->fetch();
        }
    }

    public function orgList(){

        $sday = input('sday',date('Y-m-d',strtotime('-7 day')));
        $day = input('day',date('Y-m-d'));
        $patrolMode = input('patrolMode',2);
        $title = input('title','','trim');
        $lists = $this->orgListData($sday,$day,$patrolMode,$title);

        $this->assign('lists',$lists);
        $modeList = Db::name('patrol_mode')->select();
        foreach ($modeList as $k=>$v){
            if($v['id'] == 1){
                $modeList[$k]['name'] = '安保巡更';
            }elseif($v['id'] == 2){
                $modeList[$k]['name'] = '保洁巡视';
            }elseif($v['id'] == 3){
                $modeList[$k]['name'] = '总部巡查';
            }elseif($v['id'] == 4){
                $modeList[$k]['name'] = '设备巡检';
            }
        }
        $this->assign('modeList',$modeList);
        $this->assign('patrolMode',$patrolMode);
        $this->assign('title',$title);
        $this->assign('day',$day);
        $this->assign('sday',$sday);

        return $this->fetch();

    }

    public function orgListData($sday,$day,$patrolMode,$title){

        if($title !=''){
            $map[] = ['name','like','%'.$title.'%'];
        }
        $map[] = ['del','=',0];
        $lists = Db::name('org')
            ->where('type',1)
            ->where('enable',1)
            ->where($map)
            ->where('parent_id',1)
            ->field('id,name')
            ->select();

        $data = [];
        foreach ($lists as $k=>$v){
            $orgs = model('org')->getAllNextId($v['id']);

            $orgList = Db::name('org')
                ->where('del',0)
                ->where('enable',1)
                ->where('type',2)
                ->whereIn('id',$orgs)
                ->field('id,name')
                ->select();

            foreach ($orgList as $kk=>$vv){
                $count = Db::name('patrol_task')
                    ->where('org_id',$vv['id'])
                    ->where('create_yyyymmdd','>=',date('Ymd',strtotime($sday)))
                    ->where('create_yyyymmdd','<=',date('Ymd',strtotime($day)))
                    ->where('del',0)
                    ->where('patrol_mode',$patrolMode)
                    ->count();

                $status0 = Db::name('patrol_task')
                    ->where('org_id',$vv['id'])
                    ->where('create_yyyymmdd','>=',date('Ymd',strtotime($sday)))
                    ->where('create_yyyymmdd','<=',date('Ymd',strtotime($day)))
                    ->where('del',0)
                    ->where('patrol_mode',$patrolMode)
                    ->where('status',0)
                    ->count();

                $status1 = Db::name('patrol_task')
                    ->where('org_id',$vv['id'])
                    ->where('create_yyyymmdd','>=',date('Ymd',strtotime($sday)))
                    ->where('create_yyyymmdd','<=',date('Ymd',strtotime($day)))
                    ->where('del',0)
                    ->where('patrol_mode',$patrolMode)
                    ->where('status',1)
                    ->count();

                $status2 = Db::name('patrol_task')
                    ->where('org_id',$vv['id'])
                    ->where('create_yyyymmdd','>=',date('Ymd',strtotime($sday)))
                    ->where('create_yyyymmdd','<=',date('Ymd',strtotime($day)))
                    ->where('del',0)
                    ->where('patrol_mode',$patrolMode)
                    ->where('status',2)
                    ->count();

                $status3 = Db::name('patrol_task')
                    ->where('org_id',$vv['id'])
                    ->where('create_yyyymmdd','>=',date('Ymd',strtotime($sday)))
                    ->where('create_yyyymmdd','<=',date('Ymd',strtotime($day)))
                    ->where('del',0)
                    ->where('patrol_mode',$patrolMode)
                    ->where('status','=',3)
                    ->count();

                $prInfo = Db::name('patrol_record')
                    ->alias('pr')
                    ->join('patrol_task pt','pt.id=pr.patrol_task_id')
                    ->join('user u','u.id=pr.user_id')
                    ->where('pt.org_id',$vv['id'])
                    ->where('pt.create_yyyymmdd','>=',date('Ymd',strtotime($sday)))
                    ->where('pt.create_yyyymmdd','<=',date('Ymd',strtotime($day)))
                    ->where('pt.del',0)
                    ->where('pt.patrol_mode',$patrolMode)
                    ->where('pt.status','=',2)
                    ->field('u.real_name,pr.create_time')
                    ->order('pr.id desc')
                    ->find();

                $endTaskTime = $prInfo?$prInfo['create_time']:'';

                $user = $prInfo?$prInfo['real_name'] :'';
                $data[] = [
                    'area_name'=> $kk==0 ?$v['name']:'',
                    'org_name'=>$vv['name'],
                    'org_num'=>count($orgList),
                    'count'=>$count,
                    'status0'=>$status0,
                    'status1'=>$status1,
                    'status2'=>$status2,
                    'status3'=>$status3,
                    'mode'=>$patrolMode,
                    'sday'=>$sday,
                    'day'=>$day,
                    'end_task_time'=>$endTaskTime,
                    'user_name'=>$user,
                    'org_id'=>$vv['id'],
                ];

            }
        }

        return $data;
    }

    public function orgListExport(){
        $sday = input('sday',date('Y-m-d',strtotime('-7 day')));
        $day = input('day',date('Y-m-d'));
        $patrolMode = input('patrolMode',1);
        $title = input('title','','trim');
        $lists = $this->orgListData($sday,$day,$patrolMode,$title);

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

        $n = Db::name('patrol_mode')
            ->where('id', $patrolMode)
            ->value('name');


        $title = $day.'-'. $n.'各项目统计';
        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>';
        $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 ($lists as $k=>$v){
            $res.='<tr style="background: #ffffff;">';
            if($v['area_name']){
                $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="'.$v['org_num'].'">'.$v['area_name'].'</td>';
            }
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['org_name'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['count'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['status0'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['status1'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['status2'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['status3'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['end_task_time'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['user_name'].'</td>';

            $res.='</tr>';

        }
        echo $res;

    }



    public function orgListCopy(){

        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;

            $day = input('day',date('Y-m-d'));
            $patrolMode = input('patrolMode',1);


            $lists = Db::name('org')
                ->where('type',2)
                ->where('enable',1)
                ->where('del',0)
                ->field('id,name')
                ->limit($start,$length)
                ->order($order)
                ->select();


            foreach ($lists as $k=>$v){
                $lists[$k]['count'] = Db::name('patrol_task')
                    ->where('org_id',$v['id'])
                    ->where('create_yyyymmdd',date('Ymd',strtotime($day)))
                    ->where('del',0)
                    ->where('patrol_mode',$patrolMode)
                    ->count();

                $lists[$k]['status0'] = Db::name('patrol_task')
                    ->where('org_id',$v['id'])
                    ->where('create_yyyymmdd',date('Ymd',strtotime($day)))
                    ->where('del',0)
                    ->where('patrol_mode',$patrolMode)
                    ->where('status',0)
                    ->count();

                $lists[$k]['status1'] = Db::name('patrol_task')
                    ->where('org_id',$v['id'])
                    ->where('create_yyyymmdd',date('Ymd',strtotime($day)))
                    ->where('del',0)
                    ->where('patrol_mode',$patrolMode)
                    ->where('status',1)
                    ->count();

                $lists[$k]['status2'] = Db::name('patrol_task')
                    ->where('org_id',$v['id'])
                    ->where('create_yyyymmdd',date('Ymd',strtotime($day)))
                    ->where('del',0)
                    ->where('patrol_mode',$patrolMode)
                    ->where('status',2)
                    ->count();

                $lists[$k]['status3'] = Db::name('patrol_task')
                    ->where('org_id',$v['id'])
                    ->where('create_yyyymmdd',date('Ymd',strtotime($day)))
                    ->where('del',0)
                    ->where('patrol_mode',$patrolMode)
                    ->where('status','=',3)
                    ->count();
                $lists[$k]['mode'] = $patrolMode;
                $lists[$k]['day'] = $day;
            }

            //数据返回
            $totalCount = Db::name('org')
                ->where('type',2)
                ->where('enable',1)
                ->where('del',0)
                ->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{


            $modeList = Db::name('patrol_mode')->select();
            $this->assign('modeList',$modeList);
            $this->assign('day',input('day',date('Y-m-d')));

            return $this->fetch();
        }

    }

    public function patrolTaskList(){

        $sday = input('sday');
        $day = input('day');

        $mode = input('mode');
        $status = input('status');
        $orgId= input('orgId');

        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','sort','trim');      //排序列
            $sort = input('sord','asc','trim');        //排序方式
            $order = $sortRow.' '.$sort.' ,id desc';

            if($status > -1){
                $map[] = ['status','=',$status];
            }

            $map[] = ['create_yyyymmdd','>=',date('Ymd',strtotime($sday))];
            $map[] = ['create_yyyymmdd','<=',date('Ymd',strtotime($day))];
            $map[] = ['org_id','=',$orgId];
            $map[] = ['patrol_mode','=',$mode];
            $map[] = ['del','=',0];
            $map= empty($map) ? true: $map;
            //数据查询
            $lists = Db::name('patrol_task')
                ->where($map)
                ->limit($start,$length)
                ->order($order)
                ->select();

            foreach ($lists as $k=>$v){
                $lists[$k]['start_time'] = date('Y-m-d H:i',strtotime($v['start_time']));
                $lists[$k]['end_time'] = date('Y-m-d H:i',strtotime($v['end_time']));
                $taskUser=Db::name('patrol_task_user')
                    ->alias('ptu')
                    ->field('u.real_name')
                    ->join('user u','u.id=ptu.user_id')
                    ->where('ptu.patrol_task_id',$v['id'])
                    ->column('real_name');
                $lists[$k]['task_user']=$taskUser?implode(',',$taskUser):'';
            }
            //数据返回
            $totalCount = Db::name('patrol_task')->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{

            $this->assign('day',$day);
            $this->assign('sday',$sday);
            $this->assign('mode',$mode);
            $this->assign('status',$status);
            $this->assign('orgId',$orgId);

            return $this->fetch();
        }

    }

    public function orgOver(){

        $start = input('start', '');
        $end = input('end','');
        $mode = input('mode',2);

        if($start){
            $map[] = ['create_yyyymmdd','>=',date('Ymd',strtotime($start))];
        }

        if($end){
            $map[] = ['create_yyyymmdd','<=',date('Ymd',strtotime($end))];
        }
        $map[] = ['del','=',0];


        $list = Db::name('org')
            ->where('del',0)
            ->where('enable',1)
            ->where('type',2)
            ->select();

        $y = $y1 = $y2 = [];
        foreach ($list as $k=>$v){
            $list[$k]['y'] = Db::name('patrol_task')
                ->where($map)
                ->where('org_id',$v['id'])
                ->where('patrol_mode',$mode)
                ->count();

            $list[$k]['y1'] = Db::name('patrol_task')
                ->where($map)
                ->where('org_id',$v['id'])
                ->where('patrol_mode',$mode)
                ->where('status',2)
                ->count();

            $list[$k]['y2'] = Db::name('patrol_task')
                ->where($map)
                ->where('org_id',$v['id'])
                ->where('patrol_mode',$mode)
                ->where('status',3)
                ->count();
        }

        $list = list_sort_by($list,'y','asc');

        $this->assign('x',array_column($list,'name'));
        $this->assign('y',array_column($list,'y'));
        $this->assign('y1',array_column($list,'y1'));
        $this->assign('y2',array_column($list,'y2'));

        $modeList = Db::name('patrol_mode')->select();
        foreach ($modeList as $k=>$v){
            if($v['id'] == 1){
                $modeList[$k]['name'] = '安保巡更';
            }elseif($v['id'] == 2){
                $modeList[$k]['name'] = '保洁巡视';
            }elseif($v['id'] == 3){
                $modeList[$k]['name'] = '总部巡查';
            }elseif($v['id'] == 4){
                $modeList[$k]['name'] = '设备巡检';
            }
        }

        $this->assign('modeList',$modeList);
        $this->assign('mode', $mode);
        $this->assign('start', $start);
        $this->assign('end', $end);

        return $this->fetch();
    }

    public function areaDay(){

        $map[] = ['del','=',0];
        $map[] = ['create_yyyymmdd','=',date('Ymd')];

        $list = Db::name('org')
            ->field('id,name')
            ->where('del',0)
            ->where('enable',1)
            ->where('type',1)
            ->where('parent_id',1)
            ->select();


        foreach ($list as $k=>$v){
            $orgs = model('org')->getAllNextId($v['id']);

//            $y = Db::name('patrol_task')
//                ->where($map)
//                ->whereIn('org_id',$orgs)
//                ->where('patrol_mode',2)
//                ->count();


            $y1 = Db::name('patrol_task')
                ->where($map)
                ->whereIn('org_id',$orgs)
                ->where('patrol_mode',2)
                ->where('status',2)
                ->count();

            $list[$k]['value'] =  $y1;
        }


        $this->assign('list',$list);

        return $this->fetch();
    }

    public function ztqk(){

        $map[] = ['del','=',0];
        $map[] = ['create_yyyymmdd','=',date('Ymd')];

        $list = Db::name('org')
            ->field('id,name')
            ->where('del',0)
            ->where('enable',1)
            ->where('type',1)
            ->where('parent_id',1)
            ->select();


        foreach ($list as $k=>$v){
            $orgs = model('org')->getAllNextId($v['id']);

//            $y = Db::name('patrol_task')
//                ->where($map)
//                ->whereIn('org_id',$orgs)
//                ->where('patrol_mode',2)
//                ->count();


            $y1 = Db::name('patrol_task')
                ->where($map)
                ->whereIn('org_id',$orgs)
                ->where('patrol_mode',2)
                ->where('status',2)
                ->count();

            $list[$k]['value'] =  $y1;
        }


        $this->assign('list',$list);

        $month = [];
        for ($i=1;$i<=12;$i++){
            $t = $i;
            if($i< 10){
                $t =  '0'.$i;
            }
            $month[] = $t;
        }

        $year = date('Y');
        $y = [];
        foreach ($month as $k=>$v){
            $y[] = Db::name('patrol_task')
                ->where('create_yyyymm',$year.$v)
                ->whereIn('org_id',$this->orgId)
                ->where('patrol_mode',2)
                ->where('status',2)
                ->count();
        }


        $this->assign('x',$month);
        $this->assign('y',$y);
        return $this->fetch();
    }
    public function monthOver(){

        $month = [];
        for ($i=1;$i<=12;$i++){
            $t = $i;
            if($i< 10){
                $t =  '0'.$i;
            }
            $month[] = $t;
        }

        $year = date('Y');
        $y = [];
        foreach ($month as $k=>$v){
            $y[] = Db::name('patrol_task')
                ->where('create_yyyymm',$year.$v)
                ->whereIn('org_id',$this->orgId)
                ->where('patrol_mode',2)
                ->where('status',2)
                ->count();
        }


        $this->assign('x',$month);
        $this->assign('y',$y);

        return $this->fetch();
    }
}