jili2/application/admin/controller/ConveyStatistics.php

<?php
namespace app\admin\controller;


use think\Db;
use think\Exception;


class ConveyStatistics extends Auth
{

    public function urgencyOrder(){
        $start = input('start',date('Y-m-d 00:00:00',strtotime('-1 month')));
        $end = input('end',date('Y-m-d 23:59:59'));

        $rolesId = Db::name('roles')
            ->where('parent_id',6)
            ->where('del',0)
            ->where('type',1)
            ->where('enable',1)
            ->where('org_id',$this->orgId)
            ->column('id');
        $user = Db::name('user')
            ->alias('u')
            ->field('u.*')
            ->join('user_roles ur','ur.user_id=u.id')
            ->whereIn('ur.roles_id',$rolesId)
            ->where('u.del',0)
            ->where('u.enable',1)
            ->select();

        $data = [];
        foreach ($user as $k=>$v){
            $data[$k]['user_name'] = $v['real_name'];
            $count = Db::name('todo')
                ->alias('t')
                ->join('order_convey oc','oc.order_id=t.order_id')
                ->where('t.to_user_id',$v['id'])
                ->where('t.org_id',$this->orgId)
                ->where('t.del',0)
                ->where('t.work_type_mode',3)
                ->where('t.todo_mode',3)
                ->where('t.create_time','>=',$start)
                ->where('t.create_time','<=',$end)
                ->where('oc.priority',3)
                ->count();
            $data[$k]['count'] = $count;

            $list = Db::name('todo')
                ->alias('t')
                ->field('t.id,t.order_id,t.done_time,oc.ywc_time')
                ->join('order_convey oc','oc.order_id=t.order_id')
                ->where('t.to_user_id',$v['id'])
                ->where('t.org_id',$this->orgId)
                ->where('t.del',0)
                ->where('t.work_type_mode',3)
                ->where('t.todo_mode',3)
                ->where('t.create_time','>=',$start)
                ->where('t.create_time','<=',$end)
                ->where('oc.priority',3)
                ->select();
            $hg = 0;
            foreach ($list as $kk=>$vv){
                if($vv['done_time'] && $vv['ywc_time'] >= $vv['done_time']){
                    $hg+=1;
                }
            }
            $data[$k]['hg_num'] = $hg;
            $data[$k]['no_hg_num'] = $count -$hg;
            if($count > 0){
                $bl = round($hg/$count*100,2).'%';
            }else{
                $bl = '0%';
            }
            $data[$k]['bl'] = $bl;
        }
        $data = $data?arraySequence($data,'count','SORT_DESC'):[];

        $this->assign('data',$data);


        $this->assign('start',$start);
        $this->assign('end',$end);
        return $this->fetch();
    }


    public function urgencyOrderExport(){

        $start = input('start',date('Y-m-d 00:00:00',strtotime('-1 month')));
        $end = input('end',date('Y-m-d 23:59:59'));

        $rolesId = Db::name('roles')
            ->whereIn('parent_id',6)
            ->where('del',0)
            ->where('type',1)
            ->where('enable',1)
            ->where('org_id',$this->orgId)
            ->column('id');
        $user = Db::name('user')
            ->alias('u')
            ->field('u.*')
            ->join('user_roles ur','ur.user_id=u.id')
            ->whereIn('ur.roles_id',$rolesId)
            ->where('u.del',0)
            ->where('u.enable',1)
            ->select();

        $data = [];
        foreach ($user as $k=>$v){
            $data[$k]['user_name'] = $v['real_name'];
            $count = Db::name('todo')
                ->alias('t')
                ->join('order_convey oc','oc.order_id=t.order_id')
                ->where('t.to_user_id',$v['id'])
                ->where('t.org_id',$this->orgId)
                ->where('t.del',0)
                ->where('t.work_type_mode',3)
                ->where('t.todo_mode',3)
                ->where('t.create_time','>=',$start)
                ->where('t.create_time','<=',$end)
                ->where('oc.priority',3)
                ->count();
            $data[$k]['count'] = $count;

            $list = Db::name('todo')
                ->alias('t')
                ->field('t.id,t.order_id,t.done_time,oc.ywc_time')
                ->join('order_convey oc','oc.order_id=t.order_id')
                ->where('t.to_user_id',$v['id'])
                ->where('t.org_id',$this->orgId)
                ->where('t.del',0)
                ->where('t.work_type_mode',3)
                ->where('t.todo_mode',3)
                ->where('t.create_time','>=',$start)
                ->where('t.create_time','<=',$end)
                ->where('oc.priority',3)
                ->select();
            $hg = 0;
            foreach ($list as $kk=>$vv){
                if($vv['done_time'] && $vv['ywc_time'] >= $vv['done_time']){
                    $hg+=1;
                }
            }
            $data[$k]['hg_num'] = $hg;
            $data[$k]['no_hg_num'] = $count -$hg;
            if($count > 0){
                $bl = round($hg/$count*100,2).'%';
            }else{
                $bl = '0%';
            }
            $data[$k]['bl'] = $bl;
        }

        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        //实例化PHPExcel类
        $objPHPExcel =new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '姓名')
            ->setCellValue('B1', '总数')
            ->setCellValue('C1', '合格数')
            ->setCellValue('D1', '不合格数')
            ->setCellValue('E1', '合格率');
        // 设置表格头水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //设置列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(30);


        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($data); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $data[$i]['user_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $data[$i]['count']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $data[$i]['hg_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $data[$i]['no_hg_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $data[$i]['bl']);

        }
        //设置保存的Excel表格名称
        $filename = '运送急查订单统计报表_' . date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle('运送急查订单统计报表');
        //设置浏览器窗口下载表格
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename);
        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        return $objWriter->save('php://output');
    }
    
    public function conveyOrder(){
        $start = input('start',date('Y-m-d',strtotime('-7 day')));
        $end = input('end',date('Y-m-d'));
        $address = input('address');
        if(request()->isAjax()){
            $sTime = $start.' 00:00:00';
            $eTime = $end.' 23:59:59';
            $map = [];
            if(!empty($address)){
                $ids = explode(',',$address);
                $map[] = ['id','in',$ids];
            }
            $list  = Db::name('address')
                ->field('id,title')
                ->where('enable',1)
                ->where('del',0)
                ->where($map)
                ->where('org_id',$this->orgId)
                ->where('find_in_set(2,types)')
                ->select();

            foreach ($list as $k=>$v){
                $list[$k]['nums'] = Db::name('orders')
                    ->alias('a')
                    ->join('order_convey b','a.id=b.order_id')
                    ->where('a.org_id',$this->orgId)
                    ->where('a.del',0)
                    ->where('a.work_type_mode',3)
                    ->where('a.order_mode','in',[5,6])
                    ->where('b.start',$v['id'])
                    ->where('a.create_time','>=',$sTime)
                    ->where('a.create_time','<=',$eTime)
                    ->count('a.id');
            }

            $result['rows'] = $list;
            return json($result);
        }else{
            $addressList  = Db::name('address')
                ->field('id,title')
                ->where('enable',1)
                ->where('del',0)
                ->where('org_id',$this->orgId)
                ->where('find_in_set(2,types)')
                ->select();
            $this->assign('addressList',$addressList);
            $this->assign('start',$start);
            $this->assign('end',$end);
            $this->assign('addressVal',$address?explode(',',$address):[]);
            $this->assign('address',$address);
            return $this->fetch();
        }
    }

    public function conveyOrderExport(){
        $start = input('start',date('Y-m-d',strtotime('-7 day')));
        $end = input('end',date('Y-m-d'));
        $sTime = $start.' 00:00:00';
        $eTime = $end.' 23:59:59';
        $address = input('address');

        $map = [];
        if(!empty($address)){
            $ids = explode(',',$address);
            $map[] = ['id','in',$ids];
        }
        $list  = Db::name('address')
            ->field('id,title')
            ->where('enable',1)
            ->where('del',0)
            ->where($map)
            ->where('org_id',$this->orgId)
            ->where('find_in_set(2,types)')
            ->select();

        foreach ($list as $k=>$v){
            $list[$k]['nums'] = Db::name('orders')
                ->alias('a')
                ->join('order_convey b','a.id=b.order_id')
                ->where('a.org_id',$this->orgId)
                ->where('a.del',0)
                ->where('a.work_type_mode',3)
                ->where('a.order_mode','in',[5,6])
                ->where('b.start',$v['id'])
                ->where('a.create_time','>=',$sTime)
                ->where('a.create_time','<=',$eTime)
                ->count('a.id');
        }

        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        //实例化PHPExcel类
        $objPHPExcel =new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '运送地点')
            ->setCellValue('B1', '单量');

        // 设置表格头水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //设置列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);

        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($list); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $list[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $list[$i]['nums']);

        }
        //设置保存的Excel表格名称
        $filename = '科室运送单量统计_'. date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle('科室运送单量统计_');
        //设置浏览器窗口下载表格
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename);
        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        return $objWriter->save('php://output');
    }

}