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(){
        return $this->fetch();
	}

    public function datas(){
        $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[] = ' s.createdAt > \''.$params['upload1'].'\' ';
                    break;
                case 'upload2':
                    if($params['upload2'] == null){
                        continue;
                    }
                    $where[] = ' s.createdAt < \''.$params['upload2'].'\' ';
                    break;
                case 'exam1':
                    if($params['exam1'] == null){
                        continue;
                    }
                    $exam1 = str_replace('-', '', $params['exam1']);
                    $where[] = ' s.studydate > \''.$exam1.'\' ';
                    break;
                case 'exam2':
                    if($params['exam2'] == null){
                        continue;
                    }
                    $exam2 = str_replace('-', '', $params['exam2']);
                    $where[] = ' s.studydate < \''.$exam2.'\' ';
                    break;
                case 'exam_class':
                    if($params['exam_class'] == null){
                        continue;
                    }
                    $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;
        }
        $start = $pagesize*($page-1);
        if($page != 1){
            $start = $start+1;
        }
        $finish = $pagesize*$page;
        $limit = "limit $start,$finish";
        /*$count = DB::table('studies')
            ->alias('s')
            ->join(['patient_infos'=>'p'],'p.id=s.patient_id')
            ->join(['user_bind'=>'u'],'u.patient_id=s.patient_id','left')
            ->where('s.institution_id='.$institution_id)
            ->where($search)
            ->count();
        $list = DB::table('studies')
            ->alias('s')
            ->join(['patient_infos'=>'p'],'p.id=s.patient_id')
            ->join(['user_bind'=>'u'],'u.patient_id=s.patient_id','left')
            ->where('s.institution_id='.$institution_id)
            ->where($search)
            ->page($page, $pagesize)
            ->field('p.name,p.age,p.sex,s.studydate,s.createdAt,s.modality,u.exam_id')
            ->order('s.createdAt desc')
            ->select();*/
        $count = "SELECT count(*) FROM `studies` `s` INNER JOIN `patient_infos` `p` ON `p`.`id`=`s`.`patient_id` LEFT JOIN ( SELECT `patient_id`, max(`createAt`) FROM `user_bind` group by `patient_id`) AS u ON `u`.`patient_id`=`s`.`patient_id` WHERE  (  s.institution_id=$institution_id ) ORDER BY `s`.`createdAt`";
        $list = "SELECT `p`.`name`,`p`.`age`,`p`.`sex`,`s`.`studydate`,`s`.`createdAt`,`s`.`modality`,`u`.`patient_id` FROM `studies` `s` INNER JOIN `patient_infos` `p` ON `p`.`id`=`s`.`patient_id` LEFT JOIN ( SELECT `patient_id`, max(`createAt`) FROM `user_bind` group by `patient_id`) AS u ON `u`.`patient_id`=`s`.`patient_id` WHERE  (  s.institution_id=$institution_id ) ORDER BY `s`.`createdAt` $limit";
        $data["total"] = $count;
        $data["rows"] = $list;
        echo json_encode($data);

    }

    public function out() {
        $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[] = ' s.createdAt > \''.$params['upload1'].'\' ';
                    break;
                case 'upload2':
                    if($params['upload2'] == null){
                        continue;
                    }
                    $where[] = ' s.createdAt < \''.$params['upload2'].'\' ';
                    break;
                case 'exam1':
                    if($params['exam1'] == null){
                        continue;
                    }
                    $exam1 = str_replace('-', '', $params['exam1']);
                    $where[] = ' s.studydate > \''.$exam1.'\' ';
                    break;
                case 'exam2':
                    if($params['exam2'] == null){
                        continue;
                    }
                    $exam2 = str_replace('-', '', $params['exam2']);
                    $where[] = ' s.studydate < \''.$exam2.'\' ';
                    break;
                case 'exam_class':
                    if($params['exam_class'] == null){
                        continue;
                    }
                    $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')
            ->join(['patient_infos'=>'p'],'p.id=s.patient_id')
            ->join(['user_bind'=>'u'],'u.patient_id=s.patient_id','left')
            ->where('s.institution_id='.$institution_id)
            ->where($search)
            ->field('p.name,p.age,p.sex,s.studydate,s.createdAt,s.modality,u.exam_id')
            ->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;
    }
}