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');
    }

}