guoyao-jiankang/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;
//        }
        $addrIds = array_unique(array_column($list, 'patrol_addr_id'));
        $addrIds = is_array($addrIds) ? array_filter($addrIds) : [];

        $addressTitles = [];
        if (!empty($addrIds)) {
            $addressTitles = Db::name('address')
                ->whereIn('id', $addrIds)
                ->column('title', 'id');
        }

        $stats = [];
        if (!empty($addrIds)) {
            $query = Db::name('patrol_record')
                ->field([
                    'patrol_addr_id',
                    'SUM(CASE WHEN is_normal = 0 THEN 1 ELSE 0 END) as abnormal_count',
                    'SUM(CASE WHEN is_normal = 1 THEN 1 ELSE 0 END) as normal_count'
                ])
                ->whereIn('patrol_addr_id', $addrIds)  // 确保 $addrIds 是数组
                ->where('patrol_mode', $type)
                ->where($map1);

            $results = $query->group('patrol_addr_id')->select();

            foreach ($results as $row) {
                $stats[$row['patrol_addr_id']] = [
                    'abnormal_count' => (int)$row['abnormal_count'],
                    'normal_count' => (int)$row['normal_count']
                ];
            }

            foreach ($addrIds as $id) {
                if (!isset($stats[$id])) {
                    $stats[$id] = ['abnormal_count' => 0, 'normal_count' => 0];
                }
            }
        }

        foreach ($list as $k => $v) {
            $addrId = $v['patrol_addr_id'];

            $list[$k]['title']   = $addressTitles[$addrId] ?? '';
            $list[$k]['count']   = $stats[$addrId]['abnormal_count'] ?? 0;
            $list[$k]['normal']  = $stats[$addrId]['normal_count'] ?? 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();
        }
    }
}