jili2/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'));
        $title = input('title','');
        $start1 = $start . ' 00:00:00';
        $end1 = $end . ' 23:59:59';
        $list = $this->orgOrderData($start1, $end1,$title);
        $this->assign('list', $list['list']);
        $this->assign('totalList', $list['dt']);
        $this->assign('start', $start);
        $this->assign('end', $end);
        $this->assign('title', $title);
        return $this->fetch();
    }
    public function orgOrderData($start1, $end1,$title) {


        $map[] = ['del','=',0];

        if($title !=''){
            $map[] = ['name','like','%'.$title.'%'];
        }

        $list = Db::name('org')
            ->where('type',1)
            ->where('enable',1)
            ->where($map)
            ->where('parent_id',1)
            ->field('id,name')
            ->select();

        $map1[] = ['create_time', '>=', $start1];
        $map1[] = ['create_time', '<=', $end1];

        $alist = [];
        foreach ($list as $k1 => $v1) {
            $orgs = model('org')->getAllNextId($v1['id']);

            $orgList = Db::name('org')
                ->where('del',0)
                ->where('enable',1)
                ->where('type',2)
                ->whereIn('id',$orgs)
                ->field('id,name')
                ->select();

            foreach ($orgList as $kk=>$vv){
                $count = Db::name('orders')
                    ->where('del', 0)
                    ->where('org_id', $vv['id'])
                    ->where($map1)
                    ->count();

                $bx = Db::name('orders')
                    ->where('del', 0)
                    ->where('org_id', $vv['id'])
                    ->where('work_type_mode', 1)
                    ->where($map1)
                    ->count();

                $bj = Db::name('orders')
                    ->where('del', 0)
                    ->where('org_id', $vv['id'])
                    ->where('work_type_mode', 2)
                    ->where($map1)
                    ->count();

                $ys = Db::name('orders')
                    ->where('del', 0)
                    ->where('org_id', $vv['id'])
                    ->where('work_type_mode', 3)
                    ->where($map1)
                    ->count();

                $yh = Db::name('orders')
                    ->where('del', 0)
                    ->where('org_id', $vv['id'])
                    ->where('work_type_mode', 4)
                    ->where($map1)
                    ->count();


                $alist[] = [
                    'area_name'=> $kk==0 ?$v1['name']:'',
                    'org_num'=>count($orgList),
                    'name'=>$vv['name'],
                    'count'=>$count,
                    'bx'=>$bx,
                    'bj'=>$bj,
                    'ys'=>$ys,
                    'yh'=>$yh,
                ];

            }

        }

        $totalList =[
            array_sum(array_column($alist,'count')),
            array_sum(array_column($alist,'bx')),
            array_sum(array_column($alist,'bj')),
            array_sum(array_column($alist,'ys')),
            array_sum(array_column($alist,'yh')),
        ];


        return ['list'=>$alist,'dt'=>$totalList];
    }
    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';
        $title = input('title','');
        $list = $this->orgOrderData($start1, $end1,$title);

        $ret = $list['list'];
        $totalList = $list['dt'];


        set_time_limit(0);
        ini_set("memory_limit", "1024M");



        $title = '各项目订单统计';
        header("Content-type: application/vnd.ms-excel");
        header("Content-Type: application/force-download");
        header("Content-Disposition: attachment; filename=".$title.".xls");
        header('Expires:0');
        header('Pragma:public');
        $res = '';
        $res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
        $res.='<tr style="background: #ffffff;">';

        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">区域名称</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">项目名称</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">总数</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">报修</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">保洁</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">运送</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">应急</th>';
        $res.='</tr>';

        foreach ($ret as $k=>$v){
            $res.='<tr style="background: #ffffff;">';
            if($v['area_name']){
                $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="'.$v['org_num'].'">'.$v['area_name'].'</td>';
            }
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['name'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['count'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['bx'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['bj'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['ys'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['yh'].'</td>';
            $res.='</tr>';
        }
        $res.='<tr style="background: #ffffff;">';
        $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" colspan="2">合计：</td>';
        foreach ($totalList as $k=>$v){
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" >'.$v.'</td>';
        }
        $res.='</tr>';

        echo $res;


    }
    public function org_user() {
        $title = input('title','');

        $list = $this->orgUserData($title);
        $this->assign('list', $list['list']);
        $this->assign('totalList', $list['totalList']);
        $this->assign('title',$title);
        return $this->fetch();
    }
    public function orgUserData($title) {


        $map[] = ['del','=',0];

        if($title !=''){
            $map[] = ['name','like','%'.$title.'%'];
        }

        $lists = Db::name('org')
            ->where('type',1)
            ->where('enable',1)
            ->where($map)
            ->where('parent_id',1)
            ->field('id,name')
            ->select();


        $alist = [];
        foreach ($lists as $k1 => $v1) {
            $orgs = model('org')->getAllNextId($v1['id']);

            $orgList = Db::name('org')
                ->where('del',0)
                ->where('enable',1)
                ->where('type',2)
                ->whereIn('id',$orgs)
                ->field('id,name')
                ->select();

            foreach ($orgList as $kk=>$vv){
                $res = Db::name('user')
                    ->alias('a')
                    ->field('a.id,d.parent_id')
                    ->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', 'in',[3,4,5,6,7,8,9,10])
                    ->where('c.org_id', $vv['id'])
                    ->select();
                $a1 = $a2 = $a3= $a4= $a5= $a6= $a7 = $a8 =0;
                foreach ($res as $kkk=>$vvv){

                    if($vvv['parent_id'] == 3){
                        $a1 +=1;
                    }
                    if($vvv['parent_id'] == 4){
                        $a2 +=1;
                    }
                    if($vvv['parent_id'] == 5){
                        $a3 +=1;
                    }
                    if($vvv['parent_id'] == 6){
                        $a4 +=1;
                    }
                    if($vvv['parent_id'] == 7){
                        $a5 +=1;
                    }
                    if($vvv['parent_id'] == 8){
                        $a6 +=1;
                    }
                    if($vvv['parent_id'] == 9){
                        $a7 +=1;
                    }
                    if($vvv['parent_id'] == 10){
                        $a8 +=1;
                    }
                }

                $alist[] = [
                    'area_name'=> $kk==0 ?$v1['name']:'',
                    'org_num'=>count($orgList),
                    'name'=>$vv['name'],
                    'count'=>count($res),
                    'a1'=>$a1,
                    'a2'=>$a2,
                    'a3'=>$a3,
                    'a4'=>$a4,
                    'a5'=>$a5,
                    'a6'=>$a6,
                    'a7'=>$a7,
                    'a8'=>$a8,
                ];
            }
        }

        $totalList =[
            array_sum(array_column($alist,'count')),
            array_sum(array_column($alist,'a1')),
            array_sum(array_column($alist,'a2')),
            array_sum(array_column($alist,'a3')),
            array_sum(array_column($alist,'a4')),
            array_sum(array_column($alist,'a5')),
            array_sum(array_column($alist,'a6')),
            array_sum(array_column($alist,'a7')),
            array_sum(array_column($alist,'a8')),
        ];


        return ['list'=>$alist,'totalList'=>$totalList];
    }
    public function orgUserExport() {
        $title = input('title','');
        $list = $this->orgUserData($title);
        $ret = $list['list'];
        $totalList = $list['totalList'];


        set_time_limit(0);
        ini_set("memory_limit", "1024M");



        $title = '各项目人员统计';
        header("Content-type: application/vnd.ms-excel");
        header("Content-Type: application/force-download");
        header("Content-Disposition: attachment; filename=".$title.".xls");
        header('Expires:0');
        header('Pragma:public');
        $res = '';
        $res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
        $res.='<tr style="background: #ffffff;">';

        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">区域名称</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">项目名称</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">总数</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">客户</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">综合</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">保安</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">运送</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">维修</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">保洁</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">调度</th>';
        $res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">管理层</th>';
        $res.='</tr>';

        foreach ($ret as $k=>$v){
            $res.='<tr style="background: #ffffff;">';
            if($v['area_name']){
                $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="'.$v['org_num'].'">'.$v['area_name'].'</td>';
            }
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['name'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['count'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['a1'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['a2'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['a3'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['a4'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['a5'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['a6'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['a7'].'</td>';
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['a8'].'</td>';
            $res.='</tr>';
        }
        $res.='<tr style="background: #ffffff;">';
        $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" colspan="2">合计：</td>';
        foreach ($totalList as $k=>$v){
            $res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" >'.$v.'</td>';
        }
        $res.='</tr>';

        echo $res;
    }
}