mingde-bd/application/admin/controller/UserStatistics.php

<?php

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

class UserStatistics extends Auth {
    public function user() {
        $roles = Db::name('roles')
            ->where('type', 1)
            ->where('parent_id', 0)
            ->select();
        $all = 0;
        foreach ($roles as $k => $v) {
            $res = Db::name('user')
                ->alias('a')
                ->join('user_roles b', 'a.id=b.user_id')
                ->join('user_org c', 'a.id=c.user_id')
                ->join('roles d', 'b.roles_id=d.id')
                ->where('a.del', 0)
                ->where('a.enable', 1)
                ->where('d.parent_id', $v['id'])
                ->where('c.org_id', $this->orgId)
                ->count();
            $roles[$k]['value'] = $res ? $res : 0;
            $all += $res;
        }
        $this->assign('list', $roles);
        $this->assign('allCount', $all);
        return $this->fetch();
    }
    public function org_order() {
        $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->orgOrderData($start1, $end1);
        $this->assign('list', $list);
        $this->assign('start', $start);
        $this->assign('end', $end);
        return $this->fetch();
    }
    public function orgOrderData($start1, $end1) {
        $map1[] = ['create_time', '>=', $start1];
        $map1[] = ['create_time', '<=', $end1];
        $list = Db::name('org')
            ->where('type', 2)
            ->where('del', 0)
            ->where('enable', 1)
            ->select();
        foreach ($list as $k => $v) {
            $count = Db::name('orders')
                ->where('del', 0)
                ->where('org_id', $v['id'])
                ->where($map1)
                ->count();
            $list[$k]['count'] = $count ? $count : 0;
            $bx = Db::name('orders')
                ->where('del', 0)
                ->where('org_id', $v['id'])
                ->where('work_type_mode', 1)
                ->where($map1)
                ->count();
            $list[$k]['bx'] = $bx ? $bx : 0;
            $bj = Db::name('orders')
                ->where('del', 0)
                ->where('org_id', $v['id'])
                ->where('work_type_mode', 2)
                ->where($map1)
                ->count();
            $list[$k]['bj'] = $bj ? $bj : 0;
            $ys = Db::name('orders')
                ->where('del', 0)
                ->where('org_id', $v['id'])
                ->where('work_type_mode', 3)
                ->where($map1)
                ->count();
            $list[$k]['ys'] = $ys ? $ys : 0;
            $yh = Db::name('orders')
                ->where('del', 0)
                ->where('org_id', $v['id'])
                ->where('work_type_mode', 4)
                ->where($map1)
                ->count();
            $list[$k]['yh'] = $yh ? $yh : 0;
        }
        return $list;
    }
    public function orgOrderExport() {
        $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 = $list = $this->orgOrderData($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', '总数')
            ->setCellValue('C1', '报修')
            ->setCellValue('D1', '保洁')
            ->setCellValue('E1', '运送')
            ->setCellValue('F1', '应急');
        // 设置表格头水平居中
        $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(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($ret); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $ret[$i]['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $ret[$i]['count']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $ret[$i]['bx']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $ret[$i]['bj']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $ret[$i]['ys']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), $ret[$i]['yh']);
        }
        //设置保存的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 org_user() {
        $list = $this->orgUserData();
        $this->assign('list', $list);
        return $this->fetch();
    }
    public function orgUserData() {
        $list = Db::name('org')
            ->where('type', 2)
            ->where('del', 0)
            ->where('enable', 1)
            ->select();
        foreach ($list as $k1 => $v1) {
            $roles = Db::name('roles')
                ->where('type', 1)
                ->where('parent_id', 0)
                ->select();
            $all = 0;
            foreach ($roles as $k => $v) {
                $res = Db::name('user')
                    ->alias('a')
                    ->join('user_roles b', 'a.id=b.user_id')
                    ->join('user_org c', 'a.id=c.user_id')
                    ->join('roles d', 'b.roles_id=d.id')
                    ->where('a.del', 0)
                    ->where('a.enable', 1)
                    ->where('d.parent_id', $v['id'])
                    ->where('c.org_id', $v1['id'])
                    ->count();
                $roles[$k]['value'] = $res ? $res : 0;
                $all += $res;
            }
            $list[$k1]['user'] = $roles;
            $list[$k1]['count'] = $all;
        }
        return $list;
    }
    public function orgUserExport() {
        $ret = $list = $this->orgUserData();;
        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', '保安')
            ->setCellValue('F1', '运送')
            ->setCellValue('G1', '维修')
            ->setCellValue('H1', '保洁')
            ->setCellValue('I1', '调度')
            ->setCellValue('J1', '管理层');
        // 设置表格头水平居中
        $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(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($ret); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $ret[$i]['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $ret[$i]['count']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), isset($ret[$i]['user'][0]) ? $ret[$i]['user'][0]['value'] : 0);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), isset($ret[$i]['user'][1]) ? $ret[$i]['user'][1]['value'] : 1);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), isset($ret[$i]['user'][2]) ? $ret[$i]['user'][2]['value'] : 2);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), isset($ret[$i]['user'][3]) ? $ret[$i]['user'][3]['value'] : 3);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . ($i + 2), isset($ret[$i]['user'][4]) ? $ret[$i]['user'][4]['value'] : 4);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . ($i + 2), isset($ret[$i]['user'][5]) ? $ret[$i]['user'][5]['value'] : 5);
            $objPHPExcel->getActiveSheet()->setCellValue('I' . ($i + 2), isset($ret[$i]['user'][6]) ? $ret[$i]['user'][6]['value'] : 6);
            $objPHPExcel->getActiveSheet()->setCellValue('J' . ($i + 2), isset($ret[$i]['user'][7]) ? $ret[$i]['user'][7]['value'] : 7);
        }
        //设置保存的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');
    }
}