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

<?php

namespace app\api\controller;


use app\common\controller\Api;
use think\Db;
use think\Request;

/**
 * 糊盒工资查询
 */
class GluingSalary extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];


    /**
     * 左侧菜单
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getTab()
    {
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $res=db('糊盒工资汇总')
            ->field('DATE_FORMAT(`sczl_rq`, "%Y%m") as sys_ny,bh')
            ->group('DATE_FORMAT(`sczl_rq`, "%Y%m"),bh')
            ->whereTime('sczl_rq', '>=', strtotime('-14 months'))
            ->where('bh','<>','000000')
            ->order('sczl_rq desc')
            ->select();
        foreach($res as $v){
            $arr[$v['sys_ny'].'（'][]=$v['bh'];
        }
        $rs = db('人事_基本资料')->column('员工编号,所在部门');
        foreach($arr as $k=>$v){
            foreach($v as $value){
                if(array_key_exists($value,$rs)){
                    $data[$k.count($v).'人）'][rtrim($rs[$value])][]=rtrim($rs[$value]);
                }            }
            $j=0;
            foreach($data[$k.count($v).'人）'] as $keys=>$values){
                $data[$k.count($v).'人）']['bm'][$j]=$keys;
                $data[$k.count($v).'人）'][$keys]=count($values);
                $j++;
            }
            usort($data[$k.count($v).'人）']['bm'], function($a, $b) {
                $order = array(
                    '胶印车间',
                    '凹印车间',
                    '丝印车间',
                    '模切车间',
                    '检验车间',
                    '精品试验车间',
                    '品保部',
                    '人力资源部',
                    '生产部',
                    '营销部',
                    '数字化车间',
                    '精品车间'
                );
                $a_index = array_search($a, $order);
                $b_index = array_search($b, $order);
                return $a_index - $b_index;
            });
            foreach($data[$k.count($v).'人）']['bm'] as &$va){
                $va=$va.'（'.$data[$k.count($v).'人）'][$va].'人）';
            }
            $data[$k.count($v).'人）']=$data[$k.count($v).'人）']['bm'];
        }
        $i=0;
        foreach($data as $k=>$v){
            $datas[$i]['label']=$k;
            $gdbh=substr($k,0,strpos($k,'（'));
            foreach($v as $key=>$value){
                $datas[$i]['children'][$key]['label']=$value;
                $datas[$i]['children'][$key]['gdbh']=$gdbh;
            }
            $i++;
        }
        $this->success('成功',$datas);

    }


    /**
     * 上方列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getList()
    {
        // 1. 请求验证
        if (!$this->request->isGet()) {
            $this->error('请求方式错误');
        }

        $req = $this->request->param();

        // 2. 获取查询条件
        $where = $this->buildWhereConditions($req);

        // 3. 执行查询
        $res = $this->executeQuery($req, $where);

        if (empty($res)) {
            $this->success('成功', []);
        }

        // 4. 处理数据
        $finalData = $this->processResultData($res);

        $this->success('成功', $finalData);
    }

    /**
     * 构建查询条件
     */
    private function buildWhereConditions(array $req): array
    {
        $where = [];

        // 确保日期条件始终存在
        if (!empty($req['date'])) {
            $where['sczl_rq'] = ['like', $req['date'] . '%'];
        }

        // 如果有搜索条件，添加员工编号/姓名的模糊查询
        if (!empty($req['search'])) {
            $where['bh|name'] = ['like', $req['search'] . '%'];
        }

        return $where;
    }

    /**
     * 执行数据库查询
     */
    private function executeQuery(array $req, array $where)
    {
        $query = db('糊盒工资汇总');

        // 判断是否需要联表查询
        if (empty($req['search']) && !empty($req['department'])) {
            return $this->executeJoinedQuery($query, $req, $where);
        }

        return $this->executeSimpleQuery($query, $where);
    }

    /**
     * 执行简单查询（不需要联表）
     */
    private function executeSimpleQuery($query, array $where)
    {
        return $query
            ->where($where)
            ->group('bh, sczl_rq')
            ->field([
                'bh as 员工编号',
                'DATE_FORMAT(sczl_rq, "%Y.%m.%d") as 日期',
                'name as 姓名',
                'sum(salary) as 计件工资',
                'sum(time_salary) as 计时工资',
            ])
            ->select();
    }

    /**
     * 执行联表查询（需要部门筛选）
     */
    private function executeJoinedQuery($query, array $req, array $where)
    {
        // 移除原始的sczl_rq条件，改用别名
        if (isset($where['sczl_rq'])) {
            unset($where['sczl_rq']);
            $where['a.sczl_rq'] = ['like', $req['date'] . '%'];
        }

        // 添加部门条件
        $where['b.所在部门'] = ['like', $req['department'] . '%'];

        return $query
            ->alias('a')
            ->join('人事_基本资料 b', 'b.员工编号 = a.bh')
            ->where($where)
            ->group('a.bh, a.sczl_rq')
            ->field([
                'a.bh as 员工编号',
                'DATE_FORMAT(a.sczl_rq, "%Y.%m.%d") as 日期',
                'a.name as 姓名',
                'sum(a.salary) as 计件工资',
                'sum(a.time_salary) as 计时工资',
            ])
            ->select();
    }

    /**
     * 处理查询结果数据
     */
    private function processResultData(array $results): array
    {
        $processedData = [];

        foreach ($results as $item) {
            $empNo = $item['员工编号'];

            if (!isset($processedData[$empNo])) {
                $processedData[$empNo] = $this->initEmployeeData($item);
            }

            $processedData[$empNo] = $this->updateEmployeeData(
                $processedData[$empNo],
                $item
            );
        }

        // 对每个员工的数据进行排序并格式化最终数据
        $processedData = $this->formatFinalData($processedData);

        return array_values($processedData);
    }

    /**
     * 初始化员工数据结构
     */
    private function initEmployeeData(array $item): array
    {
        return [
            '员工编号' => $item['员工编号'],
            '姓名' => $item['姓名'],
            '月工资总和' => 0.00,
            '月计件工资' => 0.00,
            '月计时工资' => 0.00,
            '每日明细' => []
        ];
    }

    /**
     * 更新员工数据
     */
    private function updateEmployeeData(array $empData, array $item): array
    {
        $pieceSalary = (float)($item['计件工资'] ?? 0);
        $timeSalary = (float)($item['计时工资'] ?? 0);
        $dailyTotal = $pieceSalary + $timeSalary;

        $empData['月工资总和'] += $dailyTotal;
        $empData['月计件工资'] += $pieceSalary;
        $empData['月计时工资'] += $timeSalary;

        $empData['每日明细'][] = [
            '日期' => $item['日期'],
            '计件工资' => number_format($pieceSalary, 2, '.', ''),
            '计时工资' => number_format($timeSalary, 2, '.', ''),
            '日工资总和' => number_format($dailyTotal, 2, '.', ''),
        ];

        return $empData;
    }

    /**
     * 对每日明细按日期排序并格式化最终数据
     */
    private function formatFinalData(array $processedData): array
    {
        foreach ($processedData as &$empData) {
            $empData['每日明细'] = $this->sortDailyDetails($empData['每日明细']);

            $empData['月工资总和'] = number_format($empData['月工资总和'], 2, '.', '');
            $empData['月计件工资'] = number_format($empData['月计件工资'], 2, '.', '');
            $empData['月计时工资'] = number_format($empData['月计时工资'], 2, '.', '');
        }
        unset($empData);

        return $processedData;
    }

    /**
     * 对每日明细按日期排序
     */
    private function sortDailyDetails(array $dailyDetails): array
    {
        usort($dailyDetails, function($a, $b) {
            // 使用更简单的日期转换方式
            $timeA = strtotime(str_replace('.', '-', $a['日期']));
            $timeB = strtotime(str_replace('.', '-', $b['日期']));

            return $timeA <=> $timeB;
        });

        return $dailyDetails;
    }

    /**
     *下方详情
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getDetail()
    {
        if($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $req = $this->request->param();
        if (!isset($req['date'])){
            $this->error('参数错误');
        }
        $list = \db('糊盒工资汇总')
            ->field('sczl_gdbh,sczl_gxmc,DATE_FORMAT(sczl_rq, "%Y.%m.%d") as 日期,sczl_jtbh,cpdh,cpmc,保养工时,装版工时,
            异常工时,设备运行工时,rate as 分配比例,sczl_cl,price as 计件单价,salary as 计件工资,
            time_salary as 计时工资,time_duration as 计时时长,time_price as 计时单价,remark as 计时备注')
            ->where([
                'sczl_rq' => ['like', $req['date'] . '%'],
                'bh' => $req['code']
            ])
            ->order('sczl_rq')
            ->select();
        if (empty($list)) {
            $this->error('失败');
        }
        $this->success('成功', $list);
    }
}