mes-server-api/application/api/controller/WasteStatistics.php

<?php

namespace app\api\controller;


use app\common\controller\Api;
use DateTime;
use \think\db;


/**
 * 决策支持->大废品统计
 */
class WasteStatistics extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];


    /**
     * 左侧菜单栏
     * @return void
     * @throws \think\exception\DbException
     * @throws db\exception\DataNotFoundException
     * @throws db\exception\ModelNotFoundException
     */
    public function Getlist()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $list = \db('db_大废品')
            ->field('DATE_FORMAT(sys_rq, "%Y-%m") AS month')
            ->group('month')
            ->order('month desc')
            ->select();
        $data = [];
        foreach ($list as $key => $value){
            $week = \db('db_大废品')
                ->field('WEEK(sys_rq,1) as week')
                ->where('sys_rq','like',$value['month'].'%')
                ->group('week')
                ->order('week desc')
                ->select();
            foreach ($week as $k=>$v){
                $week[$k]['周数'] = '第'.$v['week'].'周';
            }
            $data[str_replace('-', '', $value['month'])] = $week;
        }
        $this->success('成功',$data);
    }

    /**
     * 右侧数据
     * @return void
     * @throws \think\exception\DbException
     * @throws db\exception\DataNotFoundException
     * @throws db\exception\ModelNotFoundException
     */
    public function ReghtList()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (isset($param['mouth']) === false){
            $this->error('参数错误');
        }
        //判断查询时间范围
        $dateTime = new DateTime();
        $date = $dateTime->createFromFormat('Ym', $param['mouth']);
        $mouth = $date->format('Y-m');
        $year = $date->format('Y');
        $where = [
            'a.sczl_rq' => ['like',$mouth.'%']
        ];
        if (isset($param['week']) && !empty($param['week'])){
            $week = $dateTime->setISODate($year,$param['week']);
            $startTime = $week->format('Y-m-d 00:00:00');
            $weekLast = $week->modify('+7 days');
            $endTime = $weekLast->format('Y-m-d 00:00:00');
            $where['a.sczl_rq'] = ['between',[$startTime,$endTime]];
        }
        $query = \db('db_大废品')
            ->alias('a')
            ->field('a.sczl_cl,a.JL_bh1,a.JL_bh2,a.JL_bh3,a.JL_bh4,a.JL_bh5,a.JL_bh6,a.JL_bh7,a.JL_bh8,
            a.JL_bh9,a.JL_bh10,a.JL_Je1,JL_Je2,JL_Je3,JL_Je4,JL_Je5,JL_Je6,JL_Je7,JL_Je8,JL_Je9,
            JL_Je10,a.sczl_bh1,a.sczl_bh2,a.sczl_bh3,a.sczl_bh4,a.sczl_bh5,a.sczl_bh6,a.sczl_bh7,
            a.sczl_bh8,a.sczl_bh9,a.sczl_bh10,a.sczl_je1,a.sczl_je2,a.sczl_je3,a.sczl_je4,a.sczl_je5,
            a.sczl_je6,a.sczl_je7,a.sczl_je8,a.sczl_je9,a.sczl_je10,b1.员工姓名 as 奖励人员1,
            b2.员工姓名 as 奖励人员2,b3.员工姓名 as 奖励人员3,b4.员工姓名 as 奖励人员4,b5.员工姓名 as 奖励人员5,
            b6.员工姓名 as 奖励人员6,b7.员工姓名 as 奖励人员7,b8.员工姓名 as 奖励人员8,b9.员工姓名 as 奖励人员9,
            b10.员工姓名 as 奖励人员10,b1.所在部门 as 奖励部门1,b2.所在部门 as 奖励部门2,b3.所在部门 as 奖励部门3,
            b4.所在部门 as 奖励部门4,b5.所在部门 as 奖励部门5,b6.所在部门 as 奖励部门6,b7.所在部门 as 奖励部门7,
            b8.所在部门 as 奖励部门8,b9.所在部门 as 奖励部门9,b10.所在部门 as 奖励部门10,c1.员工姓名 as 惩罚人员1,
            c2.员工姓名 as 惩罚人员2,c3.员工姓名 as 惩罚人员3,c4.员工姓名 as 惩罚人员4,c5.员工姓名 as 惩罚人员5,
            c6.员工姓名 as 惩罚人员6,c7.员工姓名 as 惩罚人员7,c8.员工姓名 as 惩罚人员8,c9.员工姓名 as 惩罚人员9,
            c10.员工姓名 as 惩罚人员10,c1.所在部门 as 惩罚部门1,c2.所在部门 as 惩罚部门2,c3.所在部门 as 惩罚部门3,
            c4.所在部门 as 惩罚部门4,c5.所在部门 as 惩罚部门5,c6.所在部门 as 惩罚部门6,c7.所在部门 as 惩罚部门7,
            c8.所在部门 as 惩罚部门8,c9.所在部门 as 惩罚部门9,c10.所在部门 as 惩罚部门10,WEEK(a.sys_rq,1) as week');
        for($i = 1;$i<=10;$i++){
            $jl_bh = 'a.JL_bh' . $i;
            $jl_alias = 'b' . $i;
            $query->join("人事_基本资料 $jl_alias", "$jl_bh = {$jl_alias}.员工编号 AND {$jl_bh} IS NOT NULL", 'LEFT');
            $sczl_bh = 'a.sczl_bh' . $i;
            $sczl_alias = 'c' . $i;
            $query->join("人事_基本资料 $sczl_alias", "$sczl_bh = {$sczl_alias}.员工编号 AND {$sczl_bh} IS NOT NULL", 'LEFT');
        }
        $list = $query->where($where)->select();
        $data = [];
        foreach ($list as $key=>$value){
            for ($i = 1;$i <= 10; $i++){
                $jl_bh = $value['JL_bh'.$i];
                $jl_je = $value['JL_Je'.$i];
                $jl_xm = $value['奖励人员'.$i];
                $jl_bm = $value['奖励部门'.$i];
                $sczl_bh = $value['sczl_bh'.$i];
                $sczl_je = $value['sczl_je'.$i];
                $sczl_xm = $value['惩罚人员'.$i];
                $sczl_bm = $value['惩罚部门'.$i];
                //奖励人员数据
                if (!empty($jl_bh) && $jl_bh !== ''){
                    if (isset($data[$jl_bh])){
                        $data[$jl_bh]['奖励金额'] += $jl_je;
                        $data[$jl_bh]['发现次数'] += 1;
                        $data[$jl_bh]['发现张数'] += $value['sczl_cl'];
                    }else{
                        $data[$jl_bh] = [
                            '所在部门' => $jl_bm,
                            '员工编号' => $jl_bh,
                            '员工姓名' => $jl_xm,
                            '发现次数' => 1,
                            '发现张数' => $value['sczl_cl'],
                            '奖励金额' => $jl_je,
                            '遗漏次数' => 0,
                            '遗漏张数' => 0,
                            '处罚金额' => 0,
                            '年月' => $param['mouth'],
                            '周数' => $value['week']
                        ];
                    }
                }
                //惩罚人员数据
                if (!empty($sczl_bh) && $sczl_bh !== ''){
                    if (isset($data[$sczl_bh])){
                        $data[$sczl_bh]['处罚金额'] += $sczl_je;
                        $data[$sczl_bh]['遗漏次数'] += 1;
                        $data[$sczl_bh]['遗漏张数'] += $value['sczl_cl'];
                    }else{
                        $data[$sczl_bh] = [
                            '所在部门' => $sczl_bm,
                            '员工编号' => $sczl_bh,
                            '员工姓名' => $sczl_xm,
                            '发现次数' => 0,
                            '发现张数' => 0,
                            '奖励金额' => 0,
                            '遗漏次数' => 1,
                            '遗漏张数' => $value['sczl_cl'],
                            '处罚金额' => $sczl_je,
                            '年月' => $param['mouth'],
                            '周数' => $value['week']
                        ];
                    }
                }
            }
        }
        $data = array_values($data);
        $this->success('成功',$data);
    }
}