mes-dc-server-api/application/api/controller/StaffSalary.php

<?php
namespace app\api\controller;

use app\common\controller\Api;
use \think\Request;
use think\Db;
/**
 * 员工工资查询
*/
class StaffSalary extends Api
{

    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];


    /**
     * 查询报工数据月份（年-年月-日期-工序-小组五级树）
     * @ApiMethod (GET)
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function GetReportingWorkMonth()
    {
        if (!$this->request->isGet()) {
            $this->error('请求方法错误');
        }

        $rows = db('设备_工分计酬')
            ->where('del_rq', null)
            ->where('sys_rq', 'not null')
            ->field('LEFT(sys_rq, 10) as date, IF(majorprocess IS NULL OR majorprocess = "", "其他", majorprocess) as process, rtrim(sys_id) as sys_id')
            ->group('LEFT(sys_rq, 10), process, sys_id')
            ->order('date desc, process asc, sys_id asc')
            ->select();

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

        $treeMap = [];
        foreach ($rows as $row) {
            $date = $row['date'];
            if (empty($date)) {
                continue;
            }
            // 拆分年、年月、日期
            $year = substr($date, 0, 4);
            $yearMonth = substr($date, 0, 7);
            $process = $row['process'];
            $sysId = $row['sys_id'] !== '' ? $row['sys_id'] : '未知';

            // 构建五级结构
            if (!isset($treeMap[$year])) {
                $treeMap[$year] = [];
            }
            if (!isset($treeMap[$year][$yearMonth])) {
                $treeMap[$year][$yearMonth] = [];
            }
            if (!isset($treeMap[$year][$yearMonth][$date])) {
                $treeMap[$year][$yearMonth][$date] = [
                    'processes' => [],
                ];
            }
            if (!isset($treeMap[$year][$yearMonth][$date]['processes'][$process])) {
                $treeMap[$year][$yearMonth][$date]['processes'][$process] = [];
            }
            if (!in_array($sysId, $treeMap[$year][$yearMonth][$date]['processes'][$process], true)) {
                $treeMap[$year][$yearMonth][$date]['processes'][$process][] = $sysId;
            }
        }

        // 工序固定排序
        $processOrder = ['裁剪', '车缝', '手工', '大烫', '总检', '包装', '其他'];
        $result = [];
        krsort($treeMap);

        foreach ($treeMap as $year => $yearMonths) {
            $yearNode = [
                'label' => (string)$year,
                'value' => (string)$year,
                'children' => [],
            ];

            krsort($yearMonths);
            foreach ($yearMonths as $yearMonth => $dates) {
                $yearMonthNode = [
                    'label' => $yearMonth,
                    'value' => $yearMonth,
                    'children' => [],
                ];

                krsort($dates);
                foreach ($dates as $dateKey => $dateData) {
                    $processChildren = [];
                    $sortedProcesses = $this->sortProcessList(array_keys($dateData['processes']), $processOrder);
                    foreach ($sortedProcesses as $process) {
                        $groups = $dateData['processes'][$process];
                        sort($groups);
                        $groupChildren = [];
                        foreach ($groups as $group) {
                            $groupChildren[] = [
                                'label' => $group,
                                'value' => $group,
                            ];
                        }
                        $processChildren[] = [
                            'label' => $process,
                            'value' => $process,
                            'children' => $groupChildren,
                        ];
                    }

                    $dateNode = [
                        'label' => $dateKey,
                        'value' => $dateKey,
                        'children' => $processChildren,
                    ];
                    $yearMonthNode['children'][] = $dateNode;
                }

                $yearNode['children'][] = $yearMonthNode;
            }

            $result[] = $yearNode;
        }

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

    /**
     * 按指定顺序排序工序
     * @param array $processList
     * @param array $order
     * @return array
     */
    private function sortProcessList(array $processList, array $order): array
    {
        $sorted = [];
        foreach ($order as $item) {
            if (in_array($item, $processList)) {
                $sorted[] = $item;
                unset($processList[array_search($item, $processList)]);
            }
        }
        return array_merge($sorted, $processList);
    }


    /**
     * 查询员工工资列表
     * @ApiMethod (GET)
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function GetStaffSalaryList()
    {
        if (!$this->request->isGet()) {
            $this->error('请求方法错误');
        }
        $param = $this->request->param();

        if (empty($param['sys_rq'])) {
            $this->error('请选择日期（sys_rq）');
        }

        $sysRq = $param['sys_rq'];
        $bigProcess = !empty($param['big_process']) ? $param['big_process'] : '';
        $group = !empty($param['group']) ? $param['group'] : '';

        // ========== 第一步：连表查询员工列表（无exp、无whereRaw） ==========
        $staffWhere = [];
        $staffWhere['a.status'] = 1;

        // 日期条件：纯LIKE匹配，兼容带时分秒的sys_rq
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $sysRq)) {
            $staffWhere['b.sys_rq'] = ['like', $sysRq . '%'];
        } elseif (preg_match('/^\d{4}-\d{2}$/', $sysRq)) {
            $staffWhere['b.sys_rq'] = ['like', $sysRq . '%'];
        } elseif (preg_match('/^\d{4}$/', $sysRq)) {
            $staffWhere['b.sys_rq'] = ['like', $sysRq . '%'];
        } else {
            $this->error('sys_rq 格式错误，请使用 2026 / 2026-06 / 2026-06-08 格式');
        }

        // 工序筛选（可选）
        if (!empty($bigProcess)) {
            $staffWhere['a.big_process'] = $bigProcess;
        }

        // 小组筛选（可选）
        if (!empty($group)) {
            $staffWhere['b.sys_id'] = $group;
        }

        // 关键：用whereNull处理del_rq IS NULL，彻底解决EXP报错
        $staff = db('人员_基本资料')
            ->alias('a')
            ->join('设备_工分计酬 b','a.staff_no = b.staff_no','left')
            ->where($staffWhere)
            ->whereNull('b.del_rq')
            ->field('a.staff_no,a.staff_name,sum(b.salary) as salary')
            ->group('a.staff_no,a.staff_name')
            ->select();

        if(empty($staff)){
            $this->error('当前条件下暂无工资数据');
        }

        $staffMap = [];
        $staffNos = [];
        foreach ($staff as $item) {
            $staffMap[$item['staff_no']] = $item['staff_name'];
            $staffNos[] = $item['staff_no'];
        }

        // ========== 第二步：查询明细数据（无exp、无whereRaw） ==========
        $detailWhere = [];
        $detailWhere['staff_no'] = ['in', $staffNos];

        // 日期条件：和上面保持一致
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $sysRq)) {
            $detailWhere['sys_rq'] = ['like', $sysRq . '%'];
        } elseif (preg_match('/^\d{4}-\d{2}$/', $sysRq)) {
            $detailWhere['sys_rq'] = ['like', $sysRq . '%'];
        } elseif (preg_match('/^\d{4}$/', $sysRq)) {
            $detailWhere['sys_rq'] = ['like', $sysRq . '%'];
        }

        // 工序筛选（可选）
        if (!empty($bigProcess)) {
            $detailWhere['majorprocess'] = $bigProcess;
        }

        // 小组筛选（可选）
        if (!empty($group)) {
            $detailWhere['sys_id'] = $group;
        }

        // 关键：用whereNull处理del_rq IS NULL
        $salaryRows = db('设备_工分计酬')
            ->where($detailWhere)
            ->whereNull('del_rq')
            // 新增sys_id字段，并在group里加上，确保不同小组的数据不被合并
            ->field('staff_no,staff_name,DATE_FORMAT(sys_rq, "%Y-%m-%d") as date,sum(salary) as salary,sys_id')
            ->group('staff_no,DATE_FORMAT(sys_rq, "%Y-%m-%d"),sys_id')
            ->order('staff_no asc,sys_rq asc')
            ->select();

        $grouped = [];
        foreach ($salaryRows as $row) {
            $staffNo = $row['staff_no'];
            if (!isset($grouped[$staffNo])) {
                $name = !empty($row['staff_name']) ? $row['staff_name'] : (isset($staffMap[$staffNo]) ? $staffMap[$staffNo] : '');
                $grouped[$staffNo] = [
                    'staff' => $staffNo . '-' . $name,
                    'total_salary' => 0,
                    'children' => [],
                ];
            }
            $grouped[$staffNo]['children'][] = [
                '员工编号' => $staffNo,
                '员工姓名' => !empty($row['staff_name']) ? $row['staff_name'] : (isset($staffMap[$staffNo]) ? $staffMap[$staffNo] : ''),
                '日期' => $row['date'],
                '工资' => $row['salary'],
                // 新增小组字段
                '小组' => $row['sys_id'],
            ];
            $grouped[$staffNo]['total_salary'] += $row['salary'];
        }

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

//    /**
//     * 查询员工工资列表
//     * @ApiMethod (GET)
//     * @return array
//     * @throws \think\db\exception\DataNotFoundException
//     * @throws \think\db\exception\ModelNotFoundException
//     * @throws \think\exception\DbException
//     */
//    public function GetStaffSalaryList()
//    {
//        if (!$this->request->isGet()) {
//            $this->error('请求方法错误');
//        }
//        $param = $this->request->param();
//        if (empty($param['month'])) {
//            $this->error('请选择月份');
//        }
//        if (empty($param['big_process'])) {
//            $this->error('请选择部门');
//        }
//        //获取大工序员工列表
//        $staff = db('人员_基本资料')
//            ->alias('a')
//            ->join('设备_工分计酬 b','a.staff_no = b.staff_no','left')
//            ->where('a.big_process',$param['big_process'])
//            ->where('a.status',1)
//            ->where('b.date', 'like', $param['month'] . '%')
//            ->field('a.staff_no,a.staff_name,sum(b.salary) as salary')
//            ->group('a.staff_no,a.staff_name')
//            ->select();
//        if(empty($staff)){
//            $this->error('该工序没有报工数据');
//        }
//
//        $staffMap = [];
//        $staffNos = [];
//        foreach ($staff as $item) {
//            $staffMap[$item['staff_no']] = $item['staff_name'];
//            $staffNos[] = $item['staff_no'];
//        }
//
//        $salaryRows = db('设备_工分计酬')
//            ->where('del_rq', null)
//            ->where('date', 'like', $param['month'] . '%')
//            ->where('staff_no', 'in', $staffNos)
//            ->field('staff_no,staff_name,DATE_FORMAT(date, "%Y-%m-%d") as date,sum(salary) as salary,sys_id')
//            ->group('staff_no,DATE_FORMAT(date, "%Y-%m-%d")')
//            ->order('staff_no asc,date asc')
//            ->select();
//
//        $grouped = [];
//        foreach ($salaryRows as $row) {
//            $staffNo = $row['staff_no'];
//            if (!isset($grouped[$staffNo])) {
//                $name = !empty($row['staff_name']) ? $row['staff_name'] : (isset($staffMap[$staffNo]) ? $staffMap[$staffNo] : '');
//                $grouped[$staffNo] = [
//                    'staff' => $staffNo . '-' . $name,
//                    'total_salary' => 0,
//                    'children' => [],
//                ];
//            }
//            $grouped[$staffNo]['children'][] = [
//                '员工编号' => $staffNo,
//                '员工姓名' => !empty($row['staff_name']) ? $row['staff_name'] : (isset($staffMap[$staffNo]) ? $staffMap[$staffNo] : ''),
//                '日期' => $row['date'],
//                '工资' => $row['salary'],
//            ];
//            $grouped[$staffNo]['total_salary'] += $row['salary'];
//        }
//
//        $this->success('成功', array_values($grouped));
//
//    }


    /**
     * 查询员工工资详情
     *
     */
    public function GetStaffSalaryDetail()
    {
        if (!$this->request->isGet()){
            $this->error('请求方法错误');
        }
        $param = $this->request->param();
        if (empty($param['staff_no'])) {
            $this->error('请选择员工');
        }
        if(empty($param['date'])){
            $this->error('请选择日期');
        }
        $where = [
            'a.staff_no' => $param['staff_no'],
            'a.date' => ['like', $param['date'] . '%'],
            'a.del_rq' => null,
        ];
        $list = db('设备_工分计酬')
            ->alias('a')
            ->join('工单_部件资料 b', 'a.work_order = b.work_order and a.part_code = b.part_code', 'left')
            ->join('工单_基本资料 c', 'a.work_order = c.订单编号', 'left')
            ->field('a.work_order as 订单编号,DATE_FORMAT(a.date, "%Y-%m-%d") as 日期,b.part_name as 部件名称,
            a.part_code as 部件编号,a.salary as 工资,a.number as 数量,a.production_hour as 生产工时,a.production_score as 生产分数,
            a.machine as 设备名称,a.process_code as 工序编号,a.process_name as 工序名称,a.standard_hour as 标准工时,a.standard_score as 标准分数,
            a.coefficient as 系数,a.sys_id as 设备编号,c.生产款号,c.款式')
            ->where($where)
            ->order('a.date asc,a.process_code asc')
            ->select();
        if(empty($list)){
            $this->error('没有数据');
        }
        $this->success('成功', $list);
    }


    /**
     * 查询月份员工工资数据
     * @ApiMethod (GET)
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function GetStaffSalaryMonth()
    {
        if (!$this->request->isGet()){
            $this->error('请求方法错误');
        }
        $param = $this->request->param();
        if (empty($param['month'])) {
            $this->error('请选择月份');
        }
        $where = [
            'date' => ['like', $param['month'] . '%'],
            'del_rq' => null,
        ];
        if (!empty($param['search'])) {
            $where['staff_no|staff_name'] = ['like', '%' . $param['search'] . '%'];
        }
        $list = db('设备_工分计酬')
            ->where($where)
            ->field('staff_no,staff_name,sum(salary) as salary')
            ->group('staff_no,staff_name')
            ->field('staff_no,staff_name,DATE_FORMAT(date, "%Y-%m-%d") as date,sum(salary) as salary')
            ->group('staff_no,DATE_FORMAT(date, "%Y-%m-%d")')
            ->order('staff_no asc,date asc')
            ->select();
        if(empty($list)){
            $this->error('没有数据');
        }
        $this->success('成功', $list);
    }

}