yuanchengpasc/application/manage/controller/Counts.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 Counts extends Base {
    public function index(){
        $manage = Session::get('session_manager');
        $ins = explode(',',$manage['institution_id']);
        $institution = DB::table('institution')->where('id','in',$ins)->field('id,name')->select();
        $this->assign("institution", $institution);
        return $this->fetch();
    }

    public function datas(){
        $admin = Session::get('session_manager');
        $institution_id = $admin['institution_id'];
        $institution = explode(',',$institution_id);
        $request = Request::instance();
        $params = $request->param();
        $where = [];
        $where[] = ' e.film_type=2';
        $datetime = 1;
        if(isset($params['upload1']) && ($params['upload1'] == $params['upload2']) && !empty($params['upload1'])){
            $where[] = ' s.createdAt > \''.$params['upload1'].' 00:00:00\' ';
            $where[] = ' s.createdAt < \''.$params['upload2'].' 23:59:59\' ';
            $datetimme = 2;
        }
        $examtime = 1;
        if(isset($params['exam1']) && ($params['exam1'] == $params['exam2']) && !empty($params['exam1'])){
            $exam1 = str_replace('-', '', $params['exam1']);
            $where[] = ' s.studydate = \''.$exam1.'\' ';
            $examtime = 2;
        }
        foreach ($params as $k=>$v) {
            if(empty($v)){
                continue;
            }
            switch($k){
                case 'upload1':
                    if($datetime == 2){
                        break;
                    }
                    $where[] = ' s.createdAt > \''.$params['upload1'].'\' ';
                    break;
                case 'upload2':
                    if($datetime == 2){
                        break;
                    }
                    $where[] = ' s.createdAt < \''.$params['upload2'].'\' ';
                    break;
                case 'exam1':
                    if($examtime == 2){
                        break;
                    }
                    $exam1 = str_replace('-', '', $params['exam1']);
                    $where[] = ' s.studydate > \''.$exam1.'\' ';
                    break;
                case 'exam2':
                    if($examtime == 2){
                        break;
                    }
                    $exam2 = str_replace('-', '', $params['exam2']);
                    $where[] = ' s.studydate < \''.$exam2.'\' ';
                    break;
                case 'exam_class':
                    $where[] = 's.modality=\''.$params['exam_class'].'\' ';
                    break;
            }
        }
        if(count($where) <= 1){
            $where1  =  implode('',$where);
            $search = $where1;
        }else{
            $where1 = implode(' and ',$where);
            $search = $where1;
        }
        $page = empty($_GET["page"]) ? 1 : $_GET["page"];
        $pagesize = empty($_GET["rows"]) ? 1 : $_GET["rows"];
        if (empty($page) || $page < 1) {
            $page = 1;
        }
        if (empty($pagesize) || $pagesize < 1) {
            $pagesize = 30;
        }
        if(isset($params['institution']) && $params['institution'] != null){
            $institution_where['s.institution_id'] = $params['institution'];
        }else{
            $institution_where['s.institution_id'] = ['in',$institution];
        }
        $count = DB::table('studies')
            ->alias('s')
            ->join(['patient_infos'=>'p'],'p.id=s.patient_id')
            ->join(['exams'=>'e'],'e.study_id=s.id')
            ->join(['user_bind'=>'u'],'u.patient_id=s.patient_id','left')
            ->join(['institution'=>'ins'],'ins.id=e.institution_id','left')
            ->where($institution_where)
            ->where($search)
            ->count('DISTINCT e.id');
        $list = DB::table('studies')
            ->alias('s')
            ->distinct(true)
            ->join(['patient_infos'=>'p'],'p.id=s.patient_id')
            ->join(['exams'=>'e'],'e.study_id=s.id')
            ->join(['user_bind'=>'u'],'u.patient_id=s.patient_id','left')
            ->join(['institution'=>'ins'],'ins.id=e.institution_id','left')
            ->where($institution_where)
            ->where($search)
            ->page($page, $pagesize)
            ->field('p.name,p.age,p.sex,s.studydate,s.createdAt,s.modality,u.exam_id,ins.name as institution_name,e.accession_num,e.register,e.del_time,e.del_doctor_name')
            ->order('s.createdAt desc')
            ->select();
        $data["total"] = $count;
        $data["rows"] = $list;
        echo json_encode($data);

    }

    public function out() {
        $admin = Session::get('session_manager');
        $institution_id = $admin['institution_id'];
        $institution = explode(',',$institution_id);
        $request = Request::instance();
        $params = $request->param();
        if(isset($params['institution']) && $params['institution'] != null){
            $institution_where['s.institution_id'] = $params['institution'];
        }else{
            $institution_where['s.institution_id'] = ['in',$institution];
        }
        $where[] = ' e.film_type=2';
        $datetime = 1;
        if(($params['upload_datetime1'] == $params['upload_datetime2']) && !empty($params['upload_datetime1'])){
            $where[] = ' s.createdAt > \''.$params['upload_datetime1'].' 00:00:00\' ';
            $where[] = ' s.createdAt < \''.$params['upload_datetime2'].' 23:59:59\' ';
            $datetime = 2;
        }
        $examtime = 1;
        if(($params['exam_datetime1'] == $params['exam_datetime1']) && !empty($params['exam_datetime1'])){
            $exam1 = str_replace('-', '', $params['exam_datetime1']);
            $where[] = ' s.studydate = \''.$exam1.'\' ';
            $examtime = 2;
        }
        foreach ($params as $k=>$v) {
            if(empty($v)){
                continue;
            }
            switch($k){
                case 'upload_datetime1':
                    if($datetime == 2){
                        break;
                    }
                    $where[] = ' s.createdAt > \''.$params['upload_datetime1'].'\' ';
                    break;
                case 'upload_datetime2':
                    if($datetime == 2){
                        break;
                    }
                    $where[] = ' s.createdAt < \''.$params['upload_datetime2'].'\' ';
                    break;
                case 'exam_datetime1':
                    if($examtime == 2){
                        break;
                    }
                    $exam1 = str_replace('-', '', $params['exam_datetime1']);
                    $where[] = ' s.studydate > \''.$exam1.'\' ';
                    break;
                case 'exam_datetime2':
                    if($examtime == 2){
                        break;
                    }
                    $exam2 = str_replace('-', '', $params['exam_datetime2']);
                    $where[] = ' s.studydate < \''.$exam2.'\' ';
                    break;
                case 'exam_class':
                    $where[] = 's.modality=\''.$params['exam_class'].'\' ';
                    break;
            }
        }
        if(count($where) <= 1){
            $where1  =  implode('',$where);
            $search = $where1;
        }else{
            $where1 = implode(' and ',$where);
            $search = $where1;
        }
        $data = DB::table('studies')
            ->alias('s')
            ->distinct(true)
            ->join(['patient_infos'=>'p'],'p.id=s.patient_id')
            ->join(['exams'=>'e'],'e.study_id=s.id')
            ->join(['user_bind'=>'u'],'u.patient_id=s.patient_id','left')
            ->join(['institution'=>'ins'],'ins.id=e.institution_id','left')
            ->where($institution_where)
            ->where($search)
            ->field('p.name,p.age,p.sex,s.studydate,s.createdAt,s.modality,u.exam_id,ins.name as institution_name,e.accession_num')
            ->order('s.createdAt desc')
            ->select();
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        /*import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");*/
        foreach($data as $k=>$v){
            if($v['sex'] == 'F'){
                $data[$k]['sex'] = '女';
            }elseif($v['sex'] == 'M'){
                $data[$k]['sex'] = '男';
            }elseif($v['sex'] == '男'){
                $data[$k]['sex'] = '男';
            }elseif($v['sex'] == '女'){
                $data[$k]['sex'] = '女';
            }else{
                $data[$k]['sex'] = '未知';
            }
            if($v['exam_id'] == null){
                $data[$k]['exam_id'] = '未绑定';
            }else{
                $data[$k]['exam_id'] = '已绑定';
            }
        }
        $filename = time()."数据统计表";
        $headArr = array("患者姓名","患者年龄", "患者性别","检查时间", "上传时间", "患者类型",  "是否绑定微信","科室检查号");
        $this->getExcel($filename, $headArr, $data);
    }
    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 .= "_{$date}.xls";

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

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);

        //设置表头
        $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;
    }
}