jili2/application/admin/controller/DeviceStatistics.php

<?php

namespace app\admin\controller;
use think\App;
use think\Db;

class DeviceStatistics extends Auth {

    public function device(){
        $cur = date('Y-m-d');
        $start = input('start',date('Y-m-d', strtotime(''.$cur.' -1 week')));
        $end = input('end',date('Y-m-d'));
        $start1 = $start.' 00:00:00';
        $end1 = $end.' 23:59:59';
        $list = $this->deviceData($start1,$end1);
        $this->assign('list',$list);
        $this->assign('start',$start);
        $this->assign('end',$end);
        return $this->fetch();
    }
    public function deviceData($start1,$end1){
        $map1[] = ['create_time','>=',$start1];
        $map1[] = ['create_time','<=',$end1];
        $map1[] = ['org_id','<=',$this->orgId];
        $list  =Db::name('device_record')
            ->where('org_id',$this->orgId)
            ->group('device_id')
            ->select();
        foreach ($list as $k=>$v){
            $list[$k]['title'] = Db::name('device')
                ->where('id',$v['device_id'])
                ->value('title');
            $c = Db::name('device_record')
                ->where($map1)
                ->where('device_id',$v['device_id'])
                ->count();
            $list[$k]['count'] = $c?$c:0;
        }
        return $list;
    }
    public function deviceExport(){
        $cur = date('Y-m-d');
        $start = input('start',date('Y-m-d', strtotime(''.$cur.' -1 week')));
        $end = input('end',date('Y-m-d'));
        $start1 = $start.' 00:00:00';
        $end1 = $end.' 23:59:59';
        $ret = $this->deviceData($start1,$end1);
        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('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


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

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

        //设置保存的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');
    }
}