<?php

namespace app\index\service;

use app\index\model\Bi;
use app\admin\model\exams\ExamsModel;
use app\admin\model\remote\Remote;
// use app\admin\model\report\Report;
use app\admin\model\patient\PatientInfoModel;
use think\Cache;
use think\Db;

class BiService
{

    protected $model = null;
    protected $examsModel = null;

    public function __construct()
    {
        $this->model = new Bi();
        $this->examsModel = new ExamsModel();
        $this->remoteModel = new Remote();
        // $this->reportModel = new Report();
        $this->patientModel = new PatientInfoModel();
    }

    // 获取最新20条检查数据
    public function getExams($id) {
        $info = Cache::get($id.'exams');
        if(empty($info))
        {
            $data = $this->examsModel
                ->field('name, exam_project, exam_class, exam_datetime, createdAt')
                ->where('institution_id', $id)
                ->order('createdAt', 'desc')
                ->limit(20)
                ->select();
            Cache::set($id.'exams',$data,600);
        }else{
            return $info;
        }
        return $data;
    }

    // 获取七日内检查数据
    public function getStudy($id) {
        $info = Cache::get($id.'study');
        if(empty($info))
        {
            $group = $this->examsModel
                ->field('date_format(createdAt, "%Y.%m.%d") as day, count(*) as count')
                ->where('institution_id', $id)
                ->where('createdAt', '>', $this->_getWeekTime())
                // ->where('createdAt', '<', $this->_getYesterdayTime())
                ->group('day')
                ->select();
            Cache::set($id.'study',$group,600);
        }else{
            $group = $info;
        }
        return $this->_formatWeekData($group);
    }

    // 获取七日内远程诊断数据
    public function getRemote($id) {
        $info = Cache::get($id.'remote');
        if(empty($info))
        {
            $group = $this->remoteModel
                ->field('date_format(createdAt, "%Y.%m.%d") as day, count(*) as count')
                ->where('local_institution_id', $id)
                ->where('createdAt', '>', $this->_getWeekTime())
                // ->where('createdAt', '<', $this->_getYesterdayTime())
                ->group('day')
                ->select();
            Cache::set($id.'remote',$group,600);
        }else{
            $group = $info;
        }
        return $this->_formatWeekData($group);
    }

    // 获取七日内阳性数据(直查)
    //  public function getPositive($id) {
    //     $group = $this->examsModel
    //         ->field('date_format(createdAt, "%Y.%m.%d") as day, count(*) as count')
    //         ->where('institution_id', $id)
    //         ->where('report_result', 2)
    //         ->where('createdAt', '>', $this->_getWeekTime())
    //         ->group('day')
    //         ->select();
    //     return $this->_formatWeekData($group);
    // }

    // 获取七日内阳性数据(连表)
    public function getPositive($id) {
        $info = Cache::get($id.'positive');
        if(empty($info))
        {
            $group = $this->examsModel
                ->alias('e')
                ->join(['report'=>'r'],'r.exam_id=e.id')
                ->field('date_format(e.createdAt, "%Y.%m.%d") as day, count(*) as count')
                ->where('e.institution_id', $id)
                ->where('r.report_result', 2)
                ->where('r.type', 1)
                ->where('e.createdAt', '>', $this->_getWeekTime())
                // ->where('e.createdAt', '<', $this->_getYesterdayTime())
                ->group('day')
                ->select();
            Cache::set($id.'positive',$group,600);
        }else{
            $group = $info;
        }
        return $this->_formatWeekData($group);
    }

    // 科室检查统计
    public function getDepartment($id)
    {
        $info = Cache::get($id.'getDepartment');
        if(empty($info))
        {

            $today = $this->_getDepartment($id, $this->_getTodayTime());
            $month = $this->_getDepartment($id, $this->_getMonthTime());
            $all = $this->_getDepartment($id);
            Cache::set($id.'getDepartment',[
                "today" => $today,
                "month" => $month,
                "all" => $all
            ],600);
            return [
                "today" => $today,
                "month" => $month,
                "all" => $all
            ];
        }else{
            return $info;
        }
    }


    // 科室检查统计
    public function getDepartmentToday($id)
    {
        $info = Cache::get($id.'getDepartmentToday');
        if(empty($info))
        {

            $today = $this->_getDepartment($id, $this->_getTodayTime());
            Cache::set($id.'getDepartmentToday',[
                "today" => $today
            ],600);
            return [
                "today" => $today
            ];
        }else{
            return $info;
        }
    }

    // 科室检查统计
    public function getModality($id)
    {
        $info = Cache::get($id.'getModality');
        if(empty($info))
        {

            $today = $this->_getModality($id, $this->_getTodayTime());
            $month = $this->_getModality($id, $this->_getMonthTime());
            $all = $this->_getModality($id);
            Cache::set($id.'getModality',[
                "today" => $today,
                "month" => $month,
                "all" => $all
            ],600);
            return [
                "today" => $today,
                "month" => $month,
                "all" => $all
            ];
        }else{
            return $info;
        }
    }

    // 科室检查统计
    public function getModalityToday($id)
    {
        $info = Cache::get($id.'getModalityToday');
        if(empty($info))
        {

            $today = $this->_getModality($id, $this->_getTodayTime());
            Cache::set($id.'getModalityToday',[
                "today" => $today
            ],60);
            return [
                "today" => $today
            ];
        }else{
            return $info;
        }
    }

    
    // 年龄统计
    public function getAge($id)
    {
        $info = Cache::get($id.'getAge');
        if(empty($info))
        {
            $group = $this->examsModel
                ->field('CASE WHEN age + 0 > 60 THEN "60岁以上" WHEN age + 0 > 40 THEN "40-60岁" WHEN age + 0 > 20 THEN "20-40岁" ELSE "0-20岁" END as age_range, count(*) as count')
                ->where('institution_id', $id)
                ->group('age_range')
                ->select();
            Cache::set($id.'getAge',$group,600);
        }else{
            $group = $info;
        }
        return $group;
    }

     // 年龄统计 
    //  todo
     public function getAgeToday($id)
     {
         $info = Cache::get($id.'getAgeToday');
         if(empty($info))
         {
             $group = $this->examsModel
                 ->field('CASE WHEN age + 0 > 60 THEN "60岁以上" WHEN age + 0 > 40 THEN "40-60岁" WHEN age + 0 > 20 THEN "20-40岁" ELSE "0-20岁" END as age_range, count(*) as count')
                 ->where('institution_id', $id)
                 ->group('age_range')
                 ->select();
             Cache::set($id.'getAgeToday',$group,60);
         }else{
             $group = $info;
         }
         return $group;
     }

    // 主页数据
    public function getMain($id)
    {
        $info = Cache::get($id.'getMain');
        if(empty($info)) {
            $allPositive = $this->_getPositiveCount($id);
            $allRemote = $this->_getRemoteCount($id);
            $allStudy = $this->_getStudyCount($id);
            $todayPositive = $this->_getPositiveCount($id, $this->_getTodayTime());
            $todayRemote = $this->_getRemoteCount($id, $this->_getTodayTime());
            $todayStudy = $this->_getStudyCount($id, $this->_getTodayTime());
            $allPositiveRate = $allPositive == 0 ? 0: $allPositive * 100 / $allStudy;
            $todayPositiveRate = $todayPositive == 0 ? 0: ($todayPositive * 100 / $todayStudy) ."%";
            Cache::set($id.'getMain',[
                "allPositive" => $allPositive,
                "allRemote" => $allRemote,
                "allStudy" => $allStudy,
                "todayPositive" => $todayPositive,
                "todayRemote" => $todayRemote,
                "todayStudy" => $todayStudy,
                "allPositiveRate" => $allPositiveRate,
                "todayPositiveRate" => $todayPositiveRate
            ],600);
            return [
                "allPositive" => $allPositive,
                "allRemote" => $allRemote,
                "allStudy" => $allStudy,
                "todayPositive" => $todayPositive,
                "todayRemote" => $todayRemote,
                "todayStudy" => $todayStudy,
                "allPositiveRate" => $allPositiveRate,
                "todayPositiveRate" => $todayPositiveRate
            ];
        }else{
            return $info;
        }

    }

    // 主页数据
    public function getMainToday($id)
    {
        $info = Cache::get($id.'getMainToday');
        if(empty($info)) {
            $todayPositive = $this->_getPositiveCount($id, $this->_getTodayTime());
            $todayRemote = $this->_getRemoteCount($id, $this->_getTodayTime());
            $todayStudy = $this->_getStudyCount($id, $this->_getTodayTime());
            $todayPositiveRate = $todayPositive == 0 ? 0: ($todayPositive * 100 / $todayStudy) ."%";
            Cache::set($id.'getMainToday',[
                "todayPositive" => $todayPositive,
                "todayRemote" => $todayRemote,
                "todayStudy" => $todayStudy,
                "todayPositiveRate" => $todayPositiveRate
            ],60);
            return [
                "todayPositive" => $todayPositive,
                "todayRemote" => $todayRemote,
                "todayStudy" => $todayStudy,
                "todayPositiveRate" => $todayPositiveRate
            ];
        }else{
            return $info;
        }

    }

    // 运营数据
    public function getOperate($id)
    {
        $info = Cache::get($id.'getOperate');
        if(empty($info))
        {

            $today = $this->_getOperate($id, $this->_getTodayTime());
            $month = $this->_getOperate($id, $this->_getMonthTime());
            $all = $this->_getOperate($id);
            Cache::set($id.'getOperate',[
                "today" => $today,
                "month" => $month,
                "all" => $all
            ],600);
            return [
                "today" => $today,
                "month" => $month,
                "all" => $all
            ];
        }else{
            return $info;
        }
    }


    // 运营数据
    public function getOperateToday($id)
    {
        $info = Cache::get($id.'getOperateToday');
        if(empty($info))
        {

            $today = $this->_getOperate($id, $this->_getTodayTime());
            Cache::set($id.'getOperateToday',[
                "today" => $today
            ],60);
            return [
                "today" => $today
            ];
        }else{
            return $info;
        }
    }


    // 部位数据
    public function getArea($id)
    {
        $info = Cache::get($id.'getArea');
        if(empty($info))
        {
            $today = $this->_getArea($id, $this->_getTodayTime());
            $month = $this->_getArea($id, $this->_getMonthTime());
            $all = $this->_getArea($id);
            Cache::set($id.'getArea',[
                "today" => $today,
                "month" => $month,
                "all" => $all
            ],600);
            return [
                "today" => $today,
                "month" => $month,
                "all" => $all
            ];
        }else{
            return $info;
        }
    }

     // 部位数据
     public function getAreaToday($id)
     {
         $info = Cache::get($id.'getAreaToday');
         if(empty($info))
         {
             $today = $this->_getArea($id, $this->_getTodayTime());
             Cache::set($id.'getAreaToday',[
                 "today" => $today
             ],60);
             return [
                 "today" => $today
             ];
         }else{
             return $info;
         }
     }

    // 患者来源
    public function getSource($id)
    {
        $info = Cache::get($id.'getSource');
        if(empty($info))
        {
            $today = $this->_getSource($id, $this->_getTodayTime());
            $month = $this->_getSource($id, $this->_getMonthTime());
            $all = $this->_getSource($id);
            Cache::set($id.'getSource',[
                "today" => $today,
                "month" => $month,
                "all" => $all
            ],600);
            return [
                "today" => $today,
                "month" => $month,
                "all" => $all
            ];
        }else{
            return $info;
        }
    }

    // 患者来源
    public function getSourceToday($id)
    {
        $info = Cache::get($id.'getSourceToday');
        if(empty($info))
        {
            $today = $this->_getSource($id, $this->_getTodayTime());
            Cache::set($id.'getSourceToday',[
                "today" => $today
            ],60);
            return [
                "today" => $today
            ];
        }else{
            return $info;
        }
    }

    
    // TODO 科室数据 
    public function getDoctor($id)
    {
        // TODO 新建表数据
        return [
            ["level" => "执业医师", "count" => 501],
            ["level" => "执业助理医师", "count" => 121],
            ["level" => "注册护士", "count" => 1524],
            ["level" => "其他", "count" => 326]
        ];
    }

    // TODO 设备数据 
    public function getDevice($id)
    {
        // TODO 新建表数据

        $info = Cache::get($id.'getDevice');
        if(empty($info))
        {
            $today = $this->_getDevice($id, $this->_getTodayTime());
            $month = $this->_getDevice($id, $this->_getMonthTime());
            $all = $this->_getDevice($id);
            $data = [
                "today" => $today,
                "month" => $month,
                "all" => $all
            ];
            Cache::set($id.'getDevice',$data,600);
            return $data;
        }
        return $info;
    }
    // TODO 设备数据 
    public function getDeviceList($id)
    {
        // TODO 新建表数据

        $info = Cache::get($id.'getDeviceList');
        if(empty($info))
        {
            $data = $this->_getDeviceList($id, $this->_getTodayTime());
            Cache::set($id.'getDeviceList',$data,600);
            return $data;
        }
        return $info;
    }

    
    public function getDeviceToday($id)
    {
        // TODO 新建表数据

        $info = Cache::get($id.'getDeviceToday');
        if(empty($info))
        {
            $today = $this->_getDevice($id, $this->_getTodayTime());
            $data = [
                "today" => $today
            ];
            Cache::set($id.'getDeviceToday',$data,60);
            return $data;
        }
        return $info;
    }

     // TODO 设备数据 
     public function getExamProject($device, $id)
     {
         // TODO 新建表数据
 
         $info = Cache::get($id.'getExamProject'.$device);
         if(empty($info))
         {
             $today = $this->_getExamProject($device, $id, $this->_getTodayTime());
             $month = $this->_getExamProject($device, $id, $this->_getMonthTime());
             $all = $this->_getExamProject($device, $id);
             $data = [
                 "today" => $today,
                 "month" => $month,
                 "all" => $all
             ];
             Cache::set($id.'getExamProject'.$device,$data,600);
             return $data;
         }
         return $info;
     }

      // TODO 设备数据 
      public function getExamProjectToday($device, $id)
      {
          // TODO 新建表数据
  
          $info = Cache::get($id.'getExamProjectToday'.$device);
          if(empty($info))
          {
              $today = $this->_getExamProject($device, $id, $this->_getTodayTime());
              $data = [
                  "today" => $today
              ];
              Cache::set($id.'getExamProjectToday'.$device,$data,60);
              return $data;
          }
          return $info;
      }

    
    // 检查设备
    public function _getDevice($id, $time = false)
    {
        $model = $this->examsModel;
        if($time) {
            $model->where('createdAt', '>', $time);
        }
        $group = $model->field('device_name as name, count(*) as count')
        ->where('institution_id', $id)
        ->whereNotNull('device_name')
        ->group('device_name')
        ->order('count', 'desc')
        ->limit(5)
        ->select();
        return $group;
    }

    // 检查设备
    public function _getDeviceList($id, $time = false)
    {
        $model = $this->examsModel;
        $group = $model->field('device_name as name')
        ->where('institution_id', $id)
        ->whereNotNull('device_name')
        ->group('device_name')
        ->select();
        return $group;
    }

    // 检查方法
    public function _getExamProject($device, $id, $time = false)
    {
        $model = $this->examsModel;
        if($time) {
            $model->where('createdAt', '>', $time);
        }
        if($device !== 'all') {
            $model->where('device_name', $device);
        }
        $group = $model->field('exam_project as name, count(*) as count')
        ->where('institution_id', $id)
        ->whereNotNull('exam_project')
        ->group('exam_project')
        ->order('count', 'desc')
        ->limit(5)
        ->select();
        return $group;
    }

    // 检查部位
    public function _getArea($id, $time = false)
    {
        $model = $this->examsModel;
        if($time) {
            $model->where('createdAt', '>', $time);
        }
        $group = $model->field('body_part as name, count(*) as count')
        ->where('institution_id', $id)
        ->whereNotNull('body_part')
        ->group('body_part')
        ->order('count', 'desc')
        ->limit(5)
        ->select();
        return $group;
    }

    // 运营数据
    public function _getOperate($id, $time = false) {
        $patient = $this->_getPatientCount($id, $time);
        $study = $this->_getStudyCount($id, $time);
        $remote = $this->_getRemoteCount($id, $time);
        $positive = $this->_getPositiveCount($id, $time);
        return [
            "patient" => $patient,
            "study" => $study,
            "remote" => $remote,
            "positive" => $positive
        ];
    }


     // 患者来源
     public function _getSource($id, $time = false)
     {
        $model = $this->examsModel;
        if($time) {
            $model->where('createdAt', '>', $time);
        }
        $group = $model->field('CASE WHEN patient_source = 1 THEN "急诊" WHEN patient_source = 2 THEN "住院" WHEN patient_source = 3 THEN "门诊" WHEN patient_source = 4 THEN "体检" ELSE "其他" END as source, count(*) as count')
        ->where('institution_id', $id)
        ->group('source')
        ->select();
        return $group;
     }


    // 患者数量
    public function _getPatientCount($id, $time = false)
    {
        $model = $this->patientModel;
        if($time) {
            $model->where('createdAt', '>', $time);
        }
        $count = $model->where('institution_id', $id)->count();
        return $count;
    }

    // 检查数量
    public function _getStudyCount($id, $time = false)
    {
        $model = $this->examsModel;
        if($time) {
            $model->where('createdAt', '>', $time);
        }
        $count = $model->where('institution_id', $id)->count();
        return $count;
    }

    // 远程诊断数量
    public function _getRemoteCount($id, $time = false)
    {
        $model = $this->remoteModel;
        if($time) {
            $model->where('createdAt', '>', $time);
        }
        $count = $model->where('local_institution_id', $id)->count();
        return $count;
    }

    // 阳性数量
    public function _getPositiveCount($id, $time = false)
    {
        $model = $this->examsModel
        ->alias('e')
        ->join(['report'=>'r'],'r.exam_id=e.id')
        ->field('date_format(e.createdAt, "%Y.%m.%d") as day, count(*) as count')
        ->where('e.institution_id', $id)
        ->where('r.report_result', 2)
        ->where('r.type', 1);
        if($time) {
            $model->where('e.createdAt', '>', $time);
        }
        $count = $model->count();
        return $count;
    }

    // 科室检查统计
    public function _getDepartment($id, $time = false)
    {
        $model = $this->examsModel;
        if($time) {
            $model->where('createdAt', '>', $time);
        }
        $group = $model->field('application_department as name, count(*) as count')
        ->where('institution_id', $id)
        ->whereNotNull('application_department')
        // ->where('createdAt', '>', $time)
        ->group('application_department')
        ->order('count', 'desc')
        ->limit(5)
        ->select();
        return $group;
    }

      // 科室检查统计
      public function _getModality($id, $time = false)
      {
          $model = $this->examsModel;
          if($time) {
              $model->where('createdAt', '>', $time);
          }
          $group = $model->field('exam_class as name, count(*) as count')
          ->where('institution_id', $id)
          ->whereNotNull('exam_class')
          // ->where('createdAt', '>', $time)
          ->group('exam_class')
          ->order('count', 'desc')
          ->limit(5)
          ->select();
          return $group;
      }


    // 今天
    public function _getTodayTime()
    {
        return date('Y-m-d 00:00:00');
    }

     // 七天
     public function _getYesterdayTime()
     {
         $time = strtotime('-1 days');
         return date('Y-m-d 00:00:00',$time);
     }

    // 七天
    public function _getWeekTime()
    {
        $time = strtotime('-6 days');
        return date('Y-m-d 00:00:00',$time);
    }

    // 本月1日
    public function _getMonthTime()
    {
        return date('Y-m-01 00:00:00');
    }

    // 获取时间字符串key值
    public function _getKeyFromToday($day)
    {
        $time = strtotime($day.' days');
        return date('Y.m.d', $time);
    }

    // 格式化7天数据 
    // 7天数据量较少，没有封装生成数据方法，直接嵌套循环，如果数据量较大，考虑减少嵌套
    public function _formatWeekData($data)
    {
       $res = [
        ["day" => $this->_getKeyFromToday(-6), "count" => 0],
        ["day" => $this->_getKeyFromToday(-5), "count" => 0],
        ["day" => $this->_getKeyFromToday(-4), "count" => 0],
        ["day" => $this->_getKeyFromToday(-3), "count" => 0],
        ["day" => $this->_getKeyFromToday(-2), "count" => 0],
        ["day" => $this->_getKeyFromToday(-1), "count" => 0],
        ["day" => $this->_getKeyFromToday(-0), "count" => 0],
       ];

       foreach($res as $key => $val1 ) {
            foreach($data as $val2) {
                if($val1['day'] == $val2['day']) {
                    $res[$key]['count'] = $val2['count'];
                }
            }
       }
       return $res;
    }

    public function downExcel($institution)
    {
        set_time_limit(0);
        import('phpexcel.PHPExcel', EXTEND_PATH);
        $objPHPExcel = new \PHPExcel();
        $allData = Db::table('exams')->where('institution_id','43600001')->group('device_name')->cache('43600001',3600)->column('device_name,count(*) as c');
        $device = array_keys($allData);
        $month = date('Y-m-01 00:00:00');
        $monthData = Db::table('exams')->where('institution_id','43600001')->where("createdAt > '$month'")->group('device_name')->cache('43600001_month',3600)->column('device_name,count(*) as c');
        $today = date('Y-m-d 00:00:00');
        $todayData = Db::table('exams')->where('institution_id','43600001')->where("createdAt > '$today'")->group('device_name')->cache('43600001_today',3600)->column('device_name,count(*) as c');
        $objPHPExcel->getActiveSheet()->setTitle('设备检查人次统计');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', '设备名称');
        $objPHPExcel->getActiveSheet()->setCellValue('B1', '检查人次(今日)');
        $objPHPExcel->getActiveSheet()->setCellValue('C1', '检查人次(本月)');
        $objPHPExcel->getActiveSheet()->setCellValue('D1', '检查人次(总计)');
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
        $i = 2;
        foreach ($allData as $k=>$v)
        {
            if(empty($k))
            {
                continue;
            }
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $k);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $todayData[$k] ?? 0);
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $monthData[$k] ?? 0);
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $v);
            $i++;
        }

        $allBodyData = Db::table('exams')->where('institution_id','43600001')->group('exam_project')->cache('43600001_body',3600)->column('exam_project,count(*) as c');
        $monthBodyData = Db::table('exams')->where('institution_id','43600001')->where("createdAt > '$month'")->group('exam_project')->cache('43600001_month_body',3600)->column('exam_project,count(*) as c');
        $todayBodyData = Db::table('exams')->where('institution_id','43600001')->where("createdAt > '$today'")->group('exam_project')->cache('43600001_today_body',3600)->column('exam_project,count(*) as c');
        // 添加Worksheet2
        $sheet = 1;
        $objPHPExcel->createSheet();
        $objPHPExcel->setActiveSheetIndex($sheet);
        $objPHPExcel->getActiveSheet()->setTitle('检查部位统计');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', '设备名称');
        $objPHPExcel->getActiveSheet()->setCellValue('B1', '今日');
        $objPHPExcel->getActiveSheet()->setCellValue('C1', '本月');
        $objPHPExcel->getActiveSheet()->setCellValue('D1', '总计');
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(80);
        $i = 2;
        foreach ($allBodyData as $k=>$v)
        {
            if(empty($k))
            {
                continue;
            }
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $k);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $todayBodyData[$k] ?? 0);
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $monthBodyData[$k] ?? 0);
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $v);
            $i++;
        }
        foreach ($device as $v)
        {
            if(empty($v))
            {
                continue;
            }
            $allDeviceData = Db::table('exams')->where('institution_id','43600001')->where('device_name',$v)->group('exam_project')->where('exam_project != ""')->cache('43600001_device_'.$v,3600)->column('exam_project,count(*) as c');
            if(empty($allDeviceData))
            {
                continue;
            }
            $monthDeviceData = Db::table('exams')->where('institution_id','43600001')->where('device_name',$v)->where("createdAt > '$month'")->group('exam_project')->cache('43600001_month_device_'.$v,3600)->column('exam_project,count(*) as c');
            $todayDeviceData = Db::table('exams')->where('institution_id','43600001')->where('device_name',$v)->where("createdAt > '$today'")->group('exam_project')->cache('43600001_today_device_'.$v,3600)->column('exam_project,count(*) as c');
            $objPHPExcel->createSheet();
            $sheet++;
            $objPHPExcel->setActiveSheetIndex($sheet);
            try {
                $objPHPExcel->getActiveSheet()->setTitle($v.'检查统计');
            }catch (\PHPExcel_Exception $e)
            {
                preg_match_all("/[\x{4e00}-\x{9fa5}a-zA-Z0-9]+/u","$v",$arr);
                $title = (join('',$arr[0]));
                $objPHPExcel->getActiveSheet()->setTitle($title);
            }
            $objPHPExcel->getActiveSheet()->setCellValue('A1', '设备名称');
            $objPHPExcel->getActiveSheet()->setCellValue('B1', '今日');
            $objPHPExcel->getActiveSheet()->setCellValue('C1', '本月');
            $objPHPExcel->getActiveSheet()->setCellValue('D1', '总计');
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(80);
            $i = 2;
            foreach ($allDeviceData as $k=>$value)
            {
                if(empty($k))
                {
                    continue;
                }
                $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $k);
                $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $todayDeviceData[$k] ?? 0);
                $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $monthDeviceData[$k] ?? 0);
                $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $value);
                $i++;
            }
        }
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . time() . '.xls');
        header('Cache-Control: max-age=1');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
    }
}