yuanchengpasc/application/manage/controller/Statistics.php

<?php

namespace app\manage\controller;

use think\Controller;
use think\Db;
use think\Session;
use think\Config;
use think\Cookie;
use think\Request;
use app\common\library\SysLogs;
use app\common\library\UUIDs;

class Statistics extends Base {

    public function index() {
        return $this->fetch('index');
    }


    public function datas(){
        $admin = Session::get('session_manager');
        $institution_id = $admin['institution_id'];
        $doctor = DB::table('doctors')->where('institution_id',$institution_id)->field('realname,id')->select();
        $request = Request::instance();
        $params = $request->param();
        $where = array();
        foreach ($params as $k=>$v) {
            switch($k){
                case 'upload1':
                    if($params['upload1'] == null){
                        continue;
                    }
                    $where[] = ' report_datetime > \''.$params['upload1'].' 00:00:00\' ';
                    break;
                case 'upload2':
                    if($params['upload2'] == null){
                        continue;
                    }
                    $where[] = ' report_datetime < \''.$params['upload2'].' 23:59:59\' ';
                    break;
            }
        }
        if(count($where) <= 1){
            $where1  =  implode('',$where);
            $search = $where1;
        }else{
            $where1 = implode(' and ',$where);
            $search = $where1;
        }
        if($search == ''){
            $time1 = date('Y-m-d 00:00:00',time());
            $time2 = date('Y-m-d 23:59:59',time());
            $search = 'report_datetime >\''.$time1.'\' and report_datetime < \''.$time2.'\'';
        }
        $report_num = 0;
        $review_num = 0;
        $list = [];
        foreach($doctor as $k=>$v){
            $report = DB::table('report')
                ->where('report_doctor_id',$v['id'])
                ->where('remote_application_id','not null')
                ->where('review_doctor_id','not null')
                ->where($search)
                ->count();
            $review = DB::table('report')
                ->where('review_doctor_id',$v['id'])
                ->where('remote_application_id','not null')
                ->where($search)
                ->count();
            $list[$k]['name'] = $v['realname'];
            $list[$k]['report'] = $report;
            $list[$k]['review'] = $review;
            $report_num = $report_num + $report;
            $review_num = $review_num + $review;
        }
        $num = count($doctor);
        $list[$num]['name'] = '统计';
        $list[$num]['report'] = $report_num;
        $list[$num]['review']  = $review_num;
        echo json_encode($list);

    }


    public function infos()
    {
        $admin = Session::get('session_manager');
        $institution_id = $admin['institution_id'];
        $request = Request::instance();
        $params = $request->param();
        $where = array();
        foreach ($params as $k=>$v) {
            switch($k){
                case 'upload1':
                    if($params['upload1'] == null){
                        continue;
                    }
                    $where[] = ' report_datetime > \''.$params['upload1'].' 00:00:00\' ';
                    break;
                case 'upload2':
                    if($params['upload2'] == null){
                        continue;
                    }
                    $where[] = ' report_datetime < \''.$params['upload2'].' 23:59:59\' ';
                    break;
            }
        }
        if(count($where) <= 1){
            $where1  =  implode('',$where);
            $search = $where1;
        }else{
            $where1 = implode(' and ',$where);
            $search = $where1;
        }
        if($search == ''){
            $time1 = date('Y-m-d 00:00:00',time());
            $time2 = date('Y-m-d 23:59:59',time());
            $search = 'r.report_datetime >\''.$time1.'\' and r.report_datetime < \''.$time2.'\'';
        }
        $list = DB::table('remote_application')
            ->alias('ra')
            ->join(['report'=>'r'],'ra.id=r.remote_application_id')
            ->join(['exams'=>'e'],'ra.exam_id=e.id')
            ->join(["(select id,realname from doctors )"=>'reportd'],'reportd.id=r.report_doctor_id','left')
            ->join(["(select id,realname from doctors )"=>'reviewd'],'reviewd.id=r.review_doctor_id','left')
            ->join(["(select id,name from institution )"=>'ins'],'ins.id=ra.local_institution_id','left')
            ->join(["(select id,realname from doctors )"=>'req'],'req.id=ra.req_doctor_id','left')
            ->where('ra.remote_institution_id='.$institution_id)
            ->where('ra.report_status>7')
            ->where($search)
            ->field('ins.name as insname,req.realname as reqname,e.patient_num,e.exam_class,ra.req_date_time,r.report_datetime,reportd.realname as rpname,reviewd.realname as rvname,r.review_datetime')
            ->select();
        echo json_encode($list);

    }

    public function out() {
        $admin = Session::get('session_manager');
        $institution_id = $admin['institution_id'];
        $doctor = DB::table('doctors')->where('institution_id',$institution_id)->field('realname,id')->select();
        $request = Request::instance();
        $params = $request->param();
        $where = array();
        $begin = '';
        $finish = '';
        foreach ($params as $k=>$v) {
            switch($k){
                case 'upload1':
                    if($params['upload1'] == null){
                        continue;
                    }
                    $where[] = ' report_datetime > \''.$params['upload1'].' 00:00:00\' ';
                    $begin = $params['upload1'];
                    break;
                case 'upload2':
                    if($params['upload2'] == null){
                        continue;
                    }
                    $where[] = ' report_datetime < \''.$params['upload2'].' 23:59:59\' ';
                    $finish = $params['upload2'];
                    break;
            }
        }
        if(count($where) <= 1){
            $where1  =  implode('',$where);
            $search = $where1;
        }else{
            $where1 = implode(' and ',$where);
            $search = $where1;
        }
        if($search == ''){
            $time1 = date('Y-m-d 00:00:00',time());
            $time2 = date('Y-m-d 23:59:59',time());
            $search = 'report_datetime >\''.$time1.'\' and report_datetime < \''.$time2.'\'';
            $begin = date('Y-m-d',time());
            $finish = date('Y-m-d',time());
        }
        $report_num = 0;
        $review_num = 0;
        $list = [];
        foreach($doctor as $k=>$v){
            $report = DB::table('report')->where('report_doctor_id',$v['id'])->where($search)->count();
            $review = DB::table('report')->where('review_doctor_id',$v['id'])->where($search)->count();
            $list[$k]['name'] = $v['realname'];
            $list[$k]['report'] = $report;
            $list[$k]['review'] = $review;
            $report_num = $report_num + $report;
            $review_num = $review_num + $review;
        }
        $num = count($doctor);
        $list[$num]['name'] = '统计';
        $list[$num]['report'] = $report_num;
        $list[$num]['review']  = $review_num;
        if($begin == $finish){
            $filename = $begin."数据统计表";
        }else{
            $filename = $begin.'至'.$finish."数据统计表";
        }
        $headArr = array("医生姓名","已书写报告数量", "已审核报告数量");
        $this->getExcel($filename, $headArr, $list);
    }

    public function outs() {$admin = Session::get('session_manager');
        $institution_id = $admin['institution_id'];
        $request = Request::instance();
        $params = $request->param();
        $where = array();
        $begin = '';
        $finish = '';
        foreach ($params as $k=>$v) {
            switch($k){
                case 'upload1':
                    if($params['upload1'] == null){
                        continue;
                    }
                    $where[] = ' report_datetime > \''.$params['upload1'].' 00:00:00\' ';
                    $begin = $params['upload1'];
                    break;
                case 'upload2':
                    if($params['upload2'] == null){
                        continue;
                    }
                    $where[] = ' report_datetime < \''.$params['upload2'].' 23:59:59\' ';
                    $finish = $params['upload2'];
                    break;
            }
        }
        if(count($where) <= 1){
            $where1  =  implode('',$where);
            $search = $where1;
        }else{
            $where1 = implode(' and ',$where);
            $search = $where1;
        }
        if($search == ''){
            $time1 = date('Y-m-d 00:00:00',time());
            $time2 = date('Y-m-d 23:59:59',time());
            $search = 'r.report_datetime >\''.$time1.'\' and r.report_datetime < \''.$time2.'\'';
        }
        $list = DB::table('remote_application')
            ->alias('ra')
            ->join(['report'=>'r'],'ra.id=r.remote_application_id')
            ->join(['exams'=>'e'],'ra.exam_id=e.id')
            ->join(["(select id,realname from doctors )"=>'reportd'],'reportd.id=r.report_doctor_id','left')
            ->join(["(select id,realname from doctors )"=>'reviewd'],'reviewd.id=r.review_doctor_id','left')
            ->join(["(select id,name from institution )"=>'ins'],'ins.id=ra.local_institution_id','left')
            ->join(["(select id,realname from doctors )"=>'req'],'req.id=ra.req_doctor_id','left')
            ->where('ra.remote_institution_id='.$institution_id)
            ->where('ra.report_status>7')
            ->where($search)
            ->field('ins.name as insname,req.realname as reqname,e.patient_num,e.exam_class,ra.req_date_time,r.report_datetime,reportd.realname as rpname,reviewd.realname as rvname,r.review_datetime')
            ->select();
        if($begin == $finish){
            $filename = $begin."数据详情表";
        }else{
            $filename = $begin.'至'.$finish."数据详情表";
        }
        $headArr = array("申请医院","申请医生","病历号","检查类型","申请时间", "报告时间","报告医生","审核医生","审核时间");
        $this->getExcels($filename, $headArr, $list);
    }

    public function getExcels($fileName, $headArr, $data) {
        //对数据进行检验
        if (empty($data) || !is_array($data)) {
            die("data must be a array");
        }
        //检查文件名
        if (empty($fileName)) {
            exit;
        }

        $date = date("Y_m_d", time());
        $fileName .= ".xls";

        //创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(25);
        //设置表头
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
            $key += 1;
        }

        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();
        foreach ($data as $key => $rows) { //行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) {// 列写入
                $j = chr($span);
                $objActSheet->setCellValue($j . $column, $value);
                $span++;
            }
            $column++;
        }

        $fileName = iconv("utf-8", "gb2312", $fileName);
        //重命名表
        // $objPHPExcel->getActiveSheet()->setTitle('test');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); //文件通过浏览器下载
        exit;
    }
    public function getExcel($fileName, $headArr, $data) {
        //对数据进行检验
        if (empty($data) || !is_array($data)) {
            die("data must be a array");
        }
        //检查文件名
        if (empty($fileName)) {
            exit;
        }

        $date = date("Y_m_d", time());
        $fileName .= ".xls";

        //创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
        //设置表头
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
            $key += 1;
        }

        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();
        foreach ($data as $key => $rows) { //行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) {// 列写入
                $j = chr($span);
                $objActSheet->setCellValue($j . $column, $value);
                $span++;
            }
            $column++;
        }

        $fileName = iconv("utf-8", "gb2312", $fileName);
        //重命名表
        // $objPHPExcel->getActiveSheet()->setTitle('test');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); //文件通过浏览器下载
        exit;
    }

    public function list() {
        return $this->fetch('list');
    }
}