guochanhua/zskk_new_manage/application/admin/controller/statistics/Exams.php

<?php

namespace app\admin\controller\statistics;

use app\admin\model\exams\ExamsModel;
use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Db;
use think\Debug;

/**
 *
 *
 * @icon fa fa-circle-o
 */
class Exams extends Backend
{

    /**
     * Remote模型对象
     * @var \app\admin\model\remote\Remote
     */
    protected $model = null;

    protected $noNeedRight = ['getPositive','getExcel'];


    public function _initialize()
    {
        parent::_initialize();
        $this->model = new ExamsModel();

    }

    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            if(!(strlen($this->request->request('filter')) > 2))
            {
                return json(['code'=>200,'msg'=>'请输入搜索条件进行查询']);
            }
            //如果发送的来源是Selectpage，则转发到Selectpage
            list($where, $sort, $order, $offset, $limit) = $this->buildparams($this->searchFields, true);
            $field = [
//                'exam.*', 'i.name as institution_name','province','pb.id as pb_id'
                'exam.*', 'i.name as institution_name','ris_province as province','ris_city as city'
            ];
            $status = ['exam.status' => '1'];
            // 过滤机构
            $childInsIds = $this->auth->getMyInsId();
            if($childInsIds == false){
                $more = false;
            } else {
	            $more['exam.institution_id'] = ['in',$this->auth->getMyInsId()];
            }
            $bind_where = false;
            $param = $this->request->param();
            if(isset($param['is_bind'])){
                $filter = $this->request->get("filter", '');
                $filter = json_decode($filter, TRUE);
                $ins = $filter['institution_id'] ?? '';
                if(empty($ins))
                {
                    $this->error('请选择医院进行查询患者绑定信息');
                }
//                $bind_arr = Db::table('patient_search')
//                    ->where('institution_id',$ins)
//                    ->where('search_result', '1')
//                    ->column('exam_id');
//                $bind_arr = array_filter($bind_arr);
//                $bind_arr = array_keys(array_flip($bind_arr));
                $cond = $param['is_bind'] === '1' ? 'in' : 'not in';
//                $bind_where['exam.id'] = [
//                    $cond, $bind_arr
//                ];
                $total = $this->model->alias('exam')
                    ->join(['institution'=>'i'],'exam.institution_id = i.id','left')
                    ->where('exam.id',$cond,function ($query)use($filter){
                        $query->table('patient_search')
                            ->where('institution_id',$filter['institution_id'])
                            ->where('search_result', '1')
                            ->field('exam_id');
                    })
                    ->where($where)
                    ->where($bind_where)
                    ->where($more)
                    ->where($status)
                    ->count();
                if($offset === 0 && $limit === 00 && $total > 10000){
                    $this->error('超出10000条记录，请分批导出!');
                }
                $sql = $this->model->alias('exam')
                    ->join(['institution'=>'i'],'exam.institution_id = i.id','left')
                    ->where('exam.id',$cond,function ($query)use($filter){
                        $query->table('patient_search')
                            ->where('institution_id',$filter['institution_id'] ?? '')
                            ->where('search_result', '1')
                            ->field('exam_id');
                    })
                    ->field($field)
                    ->order($sort, $order)
                    ->limit($offset, $limit)
                    ->where($where)
                    ->where($bind_where)
                    ->where($more)
                    ->where($status)
                    ->buildSql();
                $list = Db::table($sql.'t')
                    ->join(['patient_search'=>'pb'],'pb.exam_id=t.id and pb.search_result=1','left')
                    ->field('t.*, count(pb.id) as c')
                    ->group('t.id')
                    ->select();
                foreach ($list as $k=>$v){
                    if($v['c'] == 0){
                        $list[$k]['is_bind'] = 0;
                    }else{
                        $list[$k]['is_bind'] = 1;
                    }
                }
            }else{
                $total = $this->model->alias('exam')
                    ->join(['institution'=>'i'],'exam.institution_id = i.id','left')
                    ->where($where)
                    ->where($bind_where)
                    ->where($more)
                    ->where($status)
                    ->count();
                if($offset === 0 && $limit === 00 && $total > 10000){
                    $this->error('超出10000条记录，请分批导出!');
                }
                $sql = $this->model->alias('exam')
                    ->join(['institution'=>'i'],'exam.institution_id = i.id','left')
                    ->field($field)
                    ->order($sort, $order)
                    ->limit($offset, $limit)
                    ->where($where)
                    ->where($bind_where)
                    ->where($more)
                    ->where($status)
                    ->buildSql();
                $list = Db::table($sql.'t')
                    ->join(['patient_search'=>'pb'],'pb.exam_id=t.id and pb.search_result=1','left')
                    ->field('t.*, count(pb.id) as c')
                    ->group('t.id')
                    ->select();
                foreach ($list as $k=>$v){
                    if($v['c'] == 0){
                        $list[$k]['is_bind'] = 0;
                    }else{
                        $list[$k]['is_bind'] = 1;
                    }
                }
            }
	        // 计算阳性率
            $list = collection($list)->toArray();
            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }

    public function getPositive()
    {
        list($where) = $this->buildparams($this->searchFields, true);
        $more = ['exam.status' => '1',];
        $record = $this->model->alias('exam')
            ->join([
                ['institution i', 'exam.institution_id = i.id','LEFT'],
                ['report r', 'exam.id = r.exam_id','LEFT'],
            ])
            ->where($where)
            ->where($more)
            ->where(function ($query){
                $query->where('r.report_result',"1")
                    ->whereOr('r.report_result',"2");
            })
            ->column('r.report_result');
        $num = 0;
        $positive = 0;
        foreach ($record as $val){
            $positive += $val === '2' ? 1 : 0;
        }
        if($positive != 0){
            $num =  round(($positive / count($record)) * 100, 2);
        }
        $this->success('','',[
            'centage' => $num,
            'positive' => $positive
        ]);
    }

    public function getFilm()
    {
        list($where) = $this->buildparams($this->searchFields, true);
        $more = ['exam.status' => '1',];
        $record = $this->model->alias('exam')
            ->join([
                ['report r', 'exam.id = r.exam_id and r.type=1'],
                ['quality_control q', 'q.report_id = r.id'],
            ])
            ->where($where)
            ->where($more)
            ->column('q.pic_quality');
        $you = 0;
        $liang = 0;
        $hege = 0;
        $cha = 0;
        foreach ($record as $val){
            switch ($val)
            {
                case 1:
                    $you++;
                    break;
                case 2:
                    $liang++;
                    break;
                case 3:
                    $hege++;
                    break;
                case 4:
                    $cha++;
                    break;
            }
        }
        $you =  round(($you / count($record)) * 100, 2);
        $liang =  round(($liang / count($record)) * 100, 2);
        $hege =  round(($hege / count($record)) * 100, 2);
        $cha =  round(($cha / count($record)) * 100, 2);
        $this->success('','',[
            'you' => $you,
            'liang' => $liang,
            'hege' => $hege,
            'cha' => $cha
        ]);
    }

    public function getExcel()
    {
        $filter = $this->request->get("filter", '');
        $filter = json_decode($filter, TRUE);
        if(empty($filter['createdAt']) && empty($filter['exam_datetime']))
        {
            $this->error('创建时间不能为空');
        }
        if(empty($filter['exam_class']))
        {
            $this->error('检查类型不能为空');
        }
        if(empty($filter['institution_id']))
        {
            $this->error('请选择医院');
        }
        if($filter['institution_id'] == '06300010')
        {
            $this->excel2($filter);
            exit;
        }
        if(empty($filter['createdAt']))
        {
            $createTime = explode(',',$filter['exam_datetime']);
            $key = 'exam_datetime';
        }else{
            $createTime = explode(' - ',$filter['createdAt']);
            $key = 'createdAt';
        }
        $examClass = $filter['exam_class'];
        list($where) = $this->buildparams($this->searchFields, true);
        $more = ['e.status' => '1'];
        $exam = $this->model->alias('e')
            ->where($where)
            ->where($more)
            ->field('id,exam_class')
            ->buildSql();
        $allCount = [];
        foreach ($examClass as $v)
        {
            $num = Db::table('exams')->where('exam_class',$v)->where('institution_id',$filter['institution_id'])->where("$key between '$createTime[0]' and '$createTime[1]'")
//                ->where("patient_num is not null or patient_num != ''")
                ->field('patient_num,exam_datetime,count(*)')->group('patient_num,exam_datetime')->select();
//            $num = Db::table('exams')->where('exam_class',$v)->where('institution_id',$filter['institution_id'])->where("$key between '$createTime[0]' and '$createTime[1]'")->where("accession_num is not null and accession_num != ''")->distinct(true)->field('accession_num')->select();
//            $num1 = Db::table('exams')->where('exam_class',$v)->where('institution_id',$filter['institution_id'])->where("$key between '$createTime[0]' and '$createTime[1]'")->where("accession_num is  null or accession_num = ''")->select();
//            $allCount[$v] = count($num)+count($num1);
            $allCount[$v] = count($num);
        }
        $d = Db::table($exam.'t')
            ->join(['wechat_visit'=>'wv'],'wv.exam_id=t.id','left')
            ->field('t.id,t.exam_class, count(wv.id) AS c')
            ->group('t.id')
            ->select();
        if(empty($d))
        {
            $this->error('没有数据');
        }
//        $class = [];
//        foreach ($examClass as $type) {
//            $all = 0;
//            $bind = 0;
//            foreach ($d as $k => $v) {
//                if ($v['exam_class'] == $type) {
//                    $all++;
//                    if ($v['c'] != '0') {
//                        // 已绑定
//                        $bind++;
//                    }
//                }
//            }
//            $class[$type] = ['all'=>$all,'bind'=>$bind];
//        }
        $fileName = '检查统计'.date('YmdHis').'.xlsx';
        import('phpexcel.PHPExcel', EXTEND_PATH);
        $path = ROOT_PATH.'public/excel.xlsx';
        $reader = IOFactory::createReader('Xlsx');
        $excal= $reader->load($path);
        $sheet = $excal->getActiveSheet();


        $b7Class = $sheet->getCell('B7')->getValue();
        $b8Class = $sheet->getCell('B8')->getValue();
        $ins = Db::table('institution')->where('id',$filter['institution_id'])->value('name');
        $price = Db::table('institution')->where('id',$filter['institution_id'])->value('film_price');
        $ins_name = $ins.'云胶片收费单';
        $sheet->setCellValue ('A1', $ins_name);
        $sheet->setCellValue ('D2', $createTime[0]);
        $sheet->setCellValue ('F2', $createTime[1]);
        $sheet->setCellValue ('F3', $price/100);
        $sheet->setCellValue ('C7', $allCount[$b7Class] ?? 0);
        $sheet->setCellValue ('C8', $allCount[$b8Class] ?? 0);

        //获取打印人次
        $time1 = date('Y-m-d 00:00:00',strtotime($createTime[0]));
        $time2 = date('Y-m-d 23:59:59',strtotime($createTime[1]));
//        $all = Db::table('print_sync')
//            ->where('institution_id',$filter['institution_id'])
//            ->where("print_time between '$time1' and '$time2'")
//            ->field("COUNT(*) as times,code,name,DATE_FORMAT(print_time,'%Y-%m-%d') as c,institution_id")
//            ->group("code,name,DATE_FORMAT(print_time,'%Y-%m-%d'),institution_id")
//            ->select();
//        $d7_times = count($all);
//        $sheet->setCellValue ('D7', $d7_times);
//        $sheet->setCellValue ('F7', ($class[$b7Class]['bind'] ?? 0)+($class[$b8Class]['bind'] ?? 0));
        //获取打印胶片量
        $h7_times = Db::table('print_sync')->where("print_time between '$time1' and '$time2'")->where('institution_id',$filter['institution_id'])->sum('times');
        $sheet->setCellValue ('H7', $h7_times);

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($excal, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    public function excel2($filter)
    {
        if(empty($filter['createdAt']))
        {
            $createTime = explode(',',$filter['exam_datetime']);
            $key = 'exam_datetime';
        }else{
            $createTime = explode(' - ',$filter['createdAt']);
            $key = 'createdAt';
        }
        $examClass = $filter['exam_class'];
        list($where) = $this->buildparams($this->searchFields, true);
        $more = ['e.status' => '1'];
        $exam = $this->model->alias('e')
            ->where($where)
            ->where($more)
            ->field('id,exam_class')
            ->buildSql();
//        $allCount = [];
        $noDisCount = [];
        foreach ($examClass as $v)
        {
//            $num = Db::table('exams')->where('exam_class',$v)->where('institution_id',$filter['institution_id'])->where("$key between '$createTime[0]' and '$createTime[1]'")
//                ->field('patient_num,exam_datetime,count(*)')->group('patient_num,exam_datetime')->select();
//            $allCount[$v] = count($num);
            $noDisCount[$v] =  Db::table('exams')->where('exam_class',$v)->where('institution_id',$filter['institution_id'])->where("$key between '$createTime[0]' and '$createTime[1]'")->count();
        }
        $d = Db::table($exam.'t')
            ->join(['wechat_visit'=>'wv'],'wv.exam_id=t.id','left')
            ->field('t.id,t.exam_class, count(wv.id) AS c')
            ->group('t.id')
            ->select();
        if(empty($d))
        {
            $this->error('没有数据');
        }
        $class = [];
        foreach ($examClass as $type) {
            $all = 0;
            $bind = 0;
            foreach ($d as $k => $v) {
                if ($v['exam_class'] == $type) {
                    $all++;
                    if ($v['c'] != '0') {
                        // 已绑定
                        $bind++;
                    }
                }
            }
            $class[$type] = ['all'=>$all,'bind'=>$bind];
        }
        $fileName = '检查统计'.date('YmdHis').'.xlsx';
        import('phpexcel.PHPExcel', EXTEND_PATH);
        $path = ROOT_PATH.'public/excel2.xlsx';
        $reader = IOFactory::createReader('Xlsx');
        $excal= $reader->load($path);
        $sheet = $excal->getActiveSheet();


        $b7Class = $sheet->getCell('B7')->getValue();
        $b8Class = $sheet->getCell('B8')->getValue();
        $b9Class = $sheet->getCell('B9')->getValue();
        if($b9Class == 'DR')
        {
            $b9Class = 'DX';
        }
        $ins = Db::table('institution')->where('id',$filter['institution_id'])->value('name');
        $price = Db::table('institution')->where('id',$filter['institution_id'])->value('film_price');
        $ins_name = $ins.'云胶片收费单';
        $sheet->setCellValue ('A1', $ins_name);
        $sheet->setCellValue ('D2', $createTime[0]);
        $sheet->setCellValue ('F2', $createTime[1]);
        $sheet->setCellValue ('F3', $price/100);
        $sheet->setCellValue ('C7', $noDisCount[$b7Class] ?? 0);
        $sheet->setCellValue ('C8', $noDisCount[$b8Class] ?? 0);
        $sheet->setCellValue ('C9', ($noDisCount[$b9Class] ?? 0)+($noDisCount['CR'] ?? 0));
//        $sheet->setCellValue ('G7', ($allCount[$b7Class] ?? 0)+($allCount[$b8Class] ?? 0));
        $sheet->setCellValue ('G7', ($noDisCount[$b7Class] ?? 0)+($noDisCount[$b8Class] ?? 0));

        //获取打印人次
        $time1 = date('Y-m-d 00:00:00',strtotime($createTime[0]));
        $time2 = date('Y-m-d 23:59:59',strtotime($createTime[1]));
        $all = Db::table('print_sync')
            ->where('institution_id',$filter['institution_id'])
            ->where("print_time between '$time1' and '$time2'")
            ->field("COUNT(*) as times,code,name,DATE_FORMAT(print_time,'%Y-%m-%d') as c,institution_id")
            ->group("code,name,DATE_FORMAT(print_time,'%Y-%m-%d'),institution_id")
            ->select();
        $d7_times = count($all);
        $sheet->setCellValue ('D7', $d7_times);

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($excal, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

}