weibao/application/company/controller/Yuejian.php

<?php

namespace app\company\controller;

use app\common\controller\Apic;
use think\Db;
use app\common\library\ExcelCsv;
/**
 * 月检进度表
 */
class Yuejian extends Apic
{
    protected $noNeedLogin = [];
    protected $noNeedRight = [];

    protected $table = 'jiance';

    public function index(){
        $search = [];

        //搜师傅
        $worker_id = input('worker_id',0);
        if(!empty($worker_id)){
            $search['jiance.worker_id'] = $worker_id;
        }

        //搜年份
        $year = input('year',date('Y'));
        $starttime = strtotime($year.'-01-01');
        $endtime   = strtotime($year + 1 .'-01-01');

        //查出本公司，该师傅，本年所有的服务项目
        $jiance_list = Db::name('jiance')
            ->where('jiance.deletetime',NULL)
            ->where('jiance.company_id',$this->auth->company_id)
            ->where('jiance.worker_id',$worker_id)
            ->where('jiance.tongjitime','BETWEEN',[$starttime,$endtime])
            ->order('jiance.tongjitime','asc')
//            ->select(false);echo $jiance_list;exit;
            ->select();

//        dump($jiance_list);
        //公司名
        $companyname = Db::name('company')->where('id',$this->auth->company_id)->value('companyname');

        //各个项目铺满，并去重
        $project_ids  = array_column($jiance_list,'uc_id');
        $project_list = Db::name('user_company')->where('id','IN',$project_ids)->field('id,projectname,endtime')->select();
//        dump($project_list);

        //双循环，得到各项目数据
        $bottom_list = [];
        foreach($project_list as $project){
            $i = 1;

            //每个project填充
            $newline = [
//                'uc_id'       => $project['id'],
                'num'         => $i,
                'companyname' => $companyname,
                'projectname' => $project['projectname'],
                'endtime'     => $project['endtime'],
                'month_01'     => 'moren',
                'month_02'     => 'moren',
                'month_03'     => 'moren',
                'month_04'     => 'moren',
                'month_05'     => 'moren',
                'month_06'     => 'moren',
                'month_07'     => 'moren',
                'month_08'     => 'moren',
                'month_09'     => 'moren',
                'month_10'     => 'moren',
                'month_11'     => 'moren',
                'month_12'     => 'moren',
            ];

            //修正每个月的数据
            foreach($jiance_list as $key => $jiance){
                if($project['id'] == $jiance['uc_id']){
                    //找到对应月份
                    $month_key = 'month_'.date('m',$jiance['tongjitime']);
                    //赋值完成情况
                    $newline[$month_key] = $jiance['status'];
                }
            }

            $bottom_list[] = $newline;
            $i++;
        }
        unset($i);
        //dump($bottom_list);

        //循环每月数据，得到顶部统计数据
        $mk = ['01','02','03','04','05','06','07','08','09','10','11','12'];
        $month_data = [];
        foreach($mk as $monnum){
            $key = 'month_'.$monnum;

            $$key = array_count_values(array_column($bottom_list,$key));

//            $$key['count'] = count($bottom_list);
//            $$key['moren'] = isset($$key['moren']) ? $$key['moren'] : 0;
            $$key['status0'] = isset($$key[0]) ? $$key[0] : 0;
            $$key['status1'] = isset($$key[1]) ? $$key[1] : 0;
            $$key['total']   = $$key['status0'] + $$key['status1'];
            $$key['bili'] = ( $$key['total'] == 0 ? 0 : bcmul(bcdiv($$key['status1'],$$key['total'],4),100,2) ) . '%';

            $month_data[$key] = $$key;
        }
        //顶部数据
        //dump($month_data);

        //赋值三行顶部数据
        $top1 = [
            'num'         => '',
            'companyname' => '比例',
            'projectname' => '',
            'endtime'     => '',
            'month_01'     => $month_data['month_01']['bili'],
            'month_02'     => $month_data['month_02']['bili'],
            'month_03'     => $month_data['month_03']['bili'],
            'month_04'     => $month_data['month_04']['bili'],
            'month_05'     => $month_data['month_05']['bili'],
            'month_06'     => $month_data['month_06']['bili'],
            'month_07'     => $month_data['month_07']['bili'],
            'month_08'     => $month_data['month_08']['bili'],
            'month_09'     => $month_data['month_09']['bili'],
            'month_10'     => $month_data['month_10']['bili'],
            'month_11'     => $month_data['month_11']['bili'],
            'month_12'     => $month_data['month_12']['bili'],
        ];
        $top2 = [
            'num'         => '',
            'companyname' => '实际完成数',
            'projectname' => '',
            'endtime'     => '',
            'month_01'     => $month_data['month_01']['status1'],
            'month_02'     => $month_data['month_02']['status1'],
            'month_03'     => $month_data['month_03']['status1'],
            'month_04'     => $month_data['month_04']['status1'],
            'month_05'     => $month_data['month_05']['status1'],
            'month_06'     => $month_data['month_06']['status1'],
            'month_07'     => $month_data['month_07']['status1'],
            'month_08'     => $month_data['month_08']['status1'],
            'month_09'     => $month_data['month_09']['status1'],
            'month_10'     => $month_data['month_10']['status1'],
            'month_11'     => $month_data['month_11']['status1'],
            'month_12'     => $month_data['month_12']['status1'],
        ];
        $top3 = [
            'num'         => '',
            'companyname' => '应该完成数',
            'projectname' => '',
            'endtime'     => '',
            'month_01'     => $month_data['month_01']['total'],
            'month_02'     => $month_data['month_02']['total'],
            'month_03'     => $month_data['month_03']['total'],
            'month_04'     => $month_data['month_04']['total'],
            'month_05'     => $month_data['month_05']['total'],
            'month_06'     => $month_data['month_06']['total'],
            'month_07'     => $month_data['month_07']['total'],
            'month_08'     => $month_data['month_08']['total'],
            'month_09'     => $month_data['month_09']['total'],
            'month_10'     => $month_data['month_10']['total'],
            'month_11'     => $month_data['month_11']['total'],
            'month_12'     => $month_data['month_12']['total'],
        ];

        $bottom_list_export = $bottom_list;

        //重整结果集，替换，对号，错号，空白
        foreach($bottom_list as $bk => $bottom){
            foreach($bottom as $key => $val){

                if(strpos($key,'month_') !== false){
                    //这里严格用===是因为，php7.4里： '一切字符串'==0 为true
                    if($val === 'moren'){$val = '';}
                    elseif($val === 1){$val = '✔';}
                    elseif($val === 0){$val = '✖';}
                }

                $bottom[$key] = $val;
            }

            $bottom['endtime'] = date('Y-m-d',$bottom['endtime']);
            $bottom_list[$bk] = $bottom;
        }

        //重整结果集，替换，对号，错号，空白
        foreach($bottom_list_export as $bk => $bottom){
            foreach($bottom as $key => $val){

                if(strpos($key,'month_') !== false){
                    //这里严格用===是因为，php7.4里： '一切字符串'==0 为true
                    if($val === 'moren'){$val = '';}
                    elseif($val === 1){$val = 'V';}
                    elseif($val === 0){$val = 'X';}
                }

                $bottom[$key] = $val;
            }

            $bottom['endtime'] = date('Y-m-d',$bottom['endtime']);
            $bottom_list_export[$bk] = $bottom;
        }


        //组合结果
        $result[] = $top1;
        $result[] = $top2;
        $result[] = $top3;
        $result   = array_merge($result,$bottom_list);


        //导出excel
        $action = input('action','');
        if($action == 'export'){

            //表头
            $excel_header = [
                'num'         => '序号',
                'companyname' => '合同单位',
                'projectname' => '项目名称',
                'endtime'     => '终止日期',
                'month_01'     => '1月',
                'month_02'     => '2月',
                'month_03'     => '3月',
                'month_04'     => '4月',
                'month_05'     => '5月',
                'month_06'     => '6月',
                'month_07'     => '7月',
                'month_08'     => '8月',
                'month_09'     => '9月',
                'month_10'     => '10月',
                'month_11'     => '11月',
                'month_12'     => '12月',
            ];

            //内容
            $excel_result[] = $top1;
            $excel_result[] = $top2;
            $excel_result[] = $top3;
            $excel_result   = array_merge($excel_result,$bottom_list_export);

            //文件输出
            $worker_info = Db::name('worker')->where('id',$worker_id)->value('truename');

            $fileName = $year.'年'.$worker_info.'月检-' . date('Y-m-d-H-i-s');
            $ExcelCsv = new ExcelCsv($excel_header, $excel_result);
            $data = $ExcelCsv->collection();
            $ExcelCsv->download($fileName, $excel_header, $data);

        }else{
            $this->success(1,$result);
        }


    }


}