request->isGet() === false){ $this->error('请求错误'); } $mouth = \db('设备_产量计酬') ->distinct(true) ->field('DATE_FORMAT(sczl_rq, "%Y-%m") AS month') ->order('month desc') ->select(); $sist = \db('设备_基本资料') ->whereNotNull('设备编组') ->group('设备编组') ->column('rtrim(设备编组) as 设备编组'); $data = []; foreach ($mouth as $key=>$value) { $arr = [ 'date'=>date('Ym',strtotime($value['month'])), 'sbbh'=>$sist, ]; array_push($data,$arr); } $this->success('成功',$data); } /** * 月度产量统计上方机台生产数据 * @return void * @throws \think\db\exception\DataNotFoundException * @throws \think\db\exception\ModelNotFoundException * @throws \think\exception\DbException */ public function MachineProduction() { // 验证请求方式 if ($this->request->isGet() === false) { $this->error('请求错误'); } // 获取请求参数 $param = $this->request->param(); if (empty($param['mouth'])) { $this->error('参数错误'); } // 初始化查询条件 $where = []; if (!empty($param['sist'])) { $where['b.设备编组'] = $param['sist']; } // 转换日期格式 $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m'); // 使用单个查询获取所有需要的数据 $results = \db('设备_产量计酬') ->alias('a') ->join('设备_基本资料 b', 'a.sczl_jtbh = b.设备编号') ->join('工单_印件资料 c', 'a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno', 'LEFT') ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh', 'LEFT') ->field([ 'a.sczl_jtbh', 'a.sczl_bzdh', 'DATE_FORMAT(a.sczl_rq, "%Y-%m-%d") as day', 'SUM(a.sczl_cl) AS total_cl', 'c.yj_yjmc AS 印件名称', 'CONCAT(d.Gy0_gxmc,"(",d.Add_gxmc,")") AS 工序名称', 'RTRIM(d.印刷方式) AS 印刷方式', 'RTRIM(d.版距) AS 版距' ]) ->where('a.sczl_rq', 'like', $mouth . '%') ->where('b.sys_sbID', '<>', '') ->where($where) ->group('a.sczl_jtbh, a.sczl_bzdh, day, c.yj_yjmc, d.Gy0_gxmc') ->order('a.sczl_jtbh, a.sczl_bzdh, day') ->select(); // 数据处理 $data = []; // 获取唯一日期并排序 $data['head'] = array_unique(array_column($results, 'day')); // 将日期转为时间戳以排序 usort($data['head'], function($a, $b) { return strtotime($a) - strtotime($b); }); // 准备按设备和班组整理的数据 $total_by_machine = []; foreach ($results as $item) { $machine_id = $item['sczl_jtbh']; $group_id = $item['sczl_bzdh']; $day = $item['day']; $printed_type = $item['印刷方式']; // 初始化设备和班组 if (!isset($total_by_machine[$machine_id])) { $total_by_machine[$machine_id] = []; } if (!isset($total_by_machine[$machine_id][$group_id])) { $total_by_machine[$machine_id][$group_id] = [ '机台编号' => $machine_id, '班组编号' => $group_id, '印件名称' => $item['印件名称'], '工序名称' => $item['工序名称'], 'total_cl' => 0, 'day_total' => [] ]; } // 更新累计数量 if ($printed_type === '卷对卷') { $total_by_machine[$machine_id][$group_id]['total_cl'] += round($item['total_cl'] / $item['版距'] * 1000); } else { $total_by_machine[$machine_id][$group_id]['total_cl'] += $item['total_cl']; } // 更新每日总计 if (!isset($total_by_machine[$machine_id][$group_id]['day_total'][$day])) { $total_by_machine[$machine_id][$group_id]['day_total'][$day] = 0; } if ($printed_type === '卷对卷') { $total_by_machine[$machine_id][$group_id]['day_total'][$day] += round($item['total_cl'] / $item['版距'] * 1000); } else { $total_by_machine[$machine_id][$group_id]['day_total'][$day] += $item['total_cl']; } } // 格式化输出数据 $data['total'] = []; foreach ($total_by_machine as $machine_group) { foreach ($machine_group as $group_data) { $data['total'][] = $group_data; } } $this->success('成功', $data); } /** * 机台班次生产工单明细 * @return void * @throws \think\db\exception\DataNotFoundException * @throws \think\db\exception\ModelNotFoundException * @throws \think\exception\DbException */ public function MachineProductDetail() { if (!$this->request->isGet()) { $this->error('请求错误'); } $param = $this->request->param(); // 检查必需的参数 if (empty($param['mouth'])) { $this->error('参数错误'); } // Initializing where conditions $where = []; // 添加可选的查询条件 if (!empty($param['machine'])) { $where['a.sczl_jtbh'] = $param['machine']; } if (!empty($param['team'])) { $where['a.sczl_bzdh'] = ['like', substr($param['team'], 0, 1) . '%']; } // 将参数转换成标准日期格式 $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m'); $where['a.sczl_rq'] = ['like', $mouth . '%']; // 分页配置 $page = !empty($param['page']) ? (int)$param['page'] : 1; $limit = !empty($param['limit']) ? (int)$param['limit'] : 9999; // 默认查询所有 // 查询数据 $list = \db('设备_产量计酬') ->alias('a') ->join('工单_印件资料 c', 'a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno') ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh') ->join('dic_lzde e', 'a.sczl_dedh = e.sys_bh') ->field([ 'a.sczl_gdbh' => '工单编号', 'a.sczl_yjno' => '印件号', 'a.sczl_gxh' => '工序号', 'c.yj_yjmc' => '印件名称', 'CONCAT(d.Gy0_gxmc,"(",d.Add_gxmc,")")' => '工序名称', 'DATE(a.sczl_rq)' => '工作日期', 'a.sczl_jtbh' => '机台编号', 'a.sczl_bzdh' => '班组编号', 'SUM(a.sczl_cl)' => '产量', 'a.sczl_ms' => '墨色数', 'rtrim(d.印刷方式)' => '印刷方式', 'rtrim(d.版距)' => '版距', 'rtrim(e.补产标准)' => '补产标准', 'rtrim(d.工价系数)' => '工价系数' ]) ->where($where) ->group('a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_rq, a.sczl_jtbh, a.sczl_bzdh') ->order('工作日期') ->page($page, $limit) // 使用 page 和 limit 实现分页 ->select(); // 数据处理 if (!empty($list)) { foreach ($list as $key => $value) { $list[$key]['印件名称'] = $value['印件号'] . '-' . $value['印件名称']; $list[$key]['工序名称'] = $value['工序号'] . '-' . $value['工序名称']; unset($list[$key]['印件号'], $list[$key]['工序号']); // 产量计算 if ($value['印刷方式'] === '卷对卷') { $list[$key]['产量'] = round($value['产量'] / $value['版距'] * 1000); } // 墨色数修正 if ($value['墨色数'] === '0.00') { $list[$key]['墨色数'] = '1.00'; } } } $this->success('成功', $list); } /** * 月度机台运行工时汇总 * @return void * @throws \think\db\exception\DataNotFoundException * @throws \think\db\exception\ModelNotFoundException * @throws \think\exception\DbException */ public function MachineOperation() { // 确保请求是GET if ($this->request->isGet() === false) { $this->error('请求错误'); } // 获取请求参数 $param = $this->request->param(); if (empty($param['mouth'])) { $this->error('参数错误'); } // 构建查询条件 $where = []; if (!empty($param['sist'])) { $where['a.设备编组'] = $param['sist']; } // 将参数转换成标准日期格式 $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m'); // 单次查询,计算产量和各项工时 $list = \db('设备_基本资料') ->alias('a') ->join('设备_产量计酬 b', 'a.设备编号 = b.sczl_jtbh') ->join('工单_印件资料 c', 'b.sczl_gdbh = c.Yj_Gdbh AND b.sczl_yjno = c.yj_Yjno') ->join('工单_工艺资料 d', 'b.sczl_gdbh = d.Gy0_gdbh AND b.sczl_yjno = d.Gy0_yjno AND b.sczl_gxh = d.Gy0_gxh') ->field([ 'a.设备编号' => '设备编号', 'rtrim(a.设备名称)' => '设备名称', 'SUM(CASE WHEN rtrim(d.版距) = "卷对卷" THEN b.sczl_cl / NULLIF(d.版距, 0) * 1000 ELSE b.sczl_cl END) AS 产量', 'SUM(b.sczl_设备运行工时)' => '设备运行工时', 'SUM(b.sczl_保养工时)' => '保养工时', 'SUM(b.sczl_打样总工时)' => '打样总工时', 'SUM(b.sczl_打样工时)' => '打样补产工时', 'SUM(b.sczl_装版总工时)' => '装版总工时', 'SUM(b.sczl_装版工时)' => '装版补产工时', 'SUM(b.sczl_异常停机工时)' => '异常停机工时', 'rtrim(d.印刷方式)' => '印刷方式', 'rtrim(d.版距)' => '版距' ]) ->where($where) ->where('b.sczl_rq', 'like', $mouth . '%') ->group('a.设备编号') ->order('a.设备编号') ->select(); // 处理总工时的计算 $total = [ '设备运行工时' => 0, '保养工时' => 0, '打样总工时' => 0, '打样补产工时' => 0, '装版总工时' => 0, '装版补产工时' => 0, '异常停机工时' => 0 ]; // 计算各项总工时 foreach ($list as $item) { $total['设备运行工时'] += $item['设备运行工时']; $total['保养工时'] += $item['保养工时']; $total['打样总工时'] += $item['打样总工时']; $total['打样补产工时'] += $item['打样补产工时']; $total['装版总工时'] += $item['装版总工时']; $total['装版补产工时'] += $item['装版补产工时']; $total['异常停机工时'] += $item['异常停机工时']; } // 将总工时添加到列表中 $list['total'] = $total; // 返回成功响应 $this->success('成功', $list); } /** * 设备运行工时机台生产工单数据详情 * @return void * @throws \think\db\exception\DataNotFoundException * @throws \think\db\exception\ModelNotFoundException * @throws \think\exception\DbException */ public function MachineOperationProductDetail() { if (!$this->request->isGet()) { $this->error('请求错误'); } $param = $this->request->param(); if (empty($param['mouth'])) { $this->error('参数错误'); } // Initialize where conditions $where = []; if (!empty($param['machine'])) { $where['a.sczl_jtbh'] = $param['machine']; } // 将参数转换成标准日期格式 $mouth = date_create_from_format('Ym', $param['mouth'])->format('Y-m'); $where['a.sczl_rq'] = ['like', $mouth . '%']; // 分页配置 $page = !empty($param['page']) ? (int)$param['page'] : 1; $limit = !empty($param['limit']) ? (int)$param['limit'] : 9999; // 默认查询所有 // 查询数据 $list = \db('设备_产量计酬') ->alias('a') ->join('设备_基本资料 b', 'a.sczl_jtbh = b.设备编号') ->join('工单_印件资料 c', 'a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno') ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh') ->join('dic_lzde e', 'a.sczl_dedh = e.sys_bh') ->field([ 'a.sczl_jtbh' => '设备编号', 'rtrim(b.设备名称)' => '设备名称', 'DATE(a.sczl_rq)' => '日期', 'a.sczl_gdbh' => '工单编号', 'a.sczl_yjno' => '印件号', 'a.sczl_gxh' => '工序号', 'c.yj_yjmc' => '印件名称', 'CONCAT(d.Gy0_gxmc, "(", d.Add_gxmc, ")")' => '工序名称', 'SUM(a.sczl_cl)' => '产量', 'SUM(a.sczl_设备运行工时)' => '设备运行工时', 'SUM(a.sczl_保养工时)' => '保养工时', 'SUM(a.sczl_打样总工时)' => '打样总工时', 'SUM(a.sczl_打样工时)' => '打样补产工时', 'SUM(a.sczl_装版总工时)' => '装板总工时', 'SUM(a.sczl_装版工时)' => '装板补产工时', 'SUM(a.sczl_异常停机工时)' => '异常停机工时', 'a.sczl_ms' => '墨色数', 'rtrim(d.印刷方式)' => '印刷方式', 'rtrim(d.版距)' => '版距', 'rtrim(e.补产标准)' => '补产标准', 'rtrim(d.工价系数)' => '工价系数' ]) ->where($where) ->group('a.sczl_rq, a.sczl_gdbh, a.sczl_gxh, a.sczl_jtbh') ->order('a.sczl_rq') ->page($page, $limit) // 使用 page 和 limit 技术实现分页 ->select(); // 数据处理 if (!empty($list)) { foreach ($list as $key => $value) { $list[$key]['工序名称'] = $value['印件号'] . '-' . $value['工序号'] . '-' . $value['工序名称']; // 移除不需要的字段 unset($list[$key]['印件号'], $list[$key]['工序号']); // 产量计算 if ($value['印刷方式'] === '卷对卷') { $list[$key]['产量'] = round($value['产量'] / $value['版距'] * 1000); } // 墨色数修正 if ($value['墨色数'] === '0.00') { $list[$key]['墨色数'] = '1.00'; } // 计算实际每小时车头产量and实际每小时计件产量与补产标准差额 $output = $list[$key]['产量']; $runningHours = $value['设备运行工时'] ?: 0.0001; $standardOutput = $value['补产标准'] ?: 0; $priceCoefficient = $value['工价系数'] ?: 0; //修正前公式 // 实际每小时车头产量 = 产量 / 设备运行工时 // $list[$key]['实际每小时车头产量'] = $runningHours > 0 ? round($output / $runningHours, 2) : 0; //修正后公式 //实际每小时车头产量=产量/(设备运行工时-保养工时-打样总工时-装版总工时-异常停机工时) $totalHours = $value['设备运行工时'] ?: 0 - $value['保养工时'] ?: 0 - $value['打样总工时'] ?: 0 - $value['装版总工时'] ?: 0 - $value['异常停机工时'] ?: 0; $totalHours = max($totalHours, 0.0001); // 防止除数为0 $list[$key]['实际每小时车头产量'] = round($output / $totalHours, 2); // 实际每小时计件产量与补产标准差额 = 实际每小时车头产量 * 计件系数 - 补产标准 $list[$key]['实际每小时计件产量与补产标准差额'] = round( $list[$key]['实际每小时车头产量'] * $priceCoefficient - $standardOutput, 2); } } $this->success('成功', $list); } /** * 工序产出率月度统计报表 * @return void * @throws \think\db\exception\DataNotFoundException * @throws \think\db\exception\ModelNotFoundException * @throws \think\exception\DbException */ public function ProcessOutputRate() { if (!$this->request->isGet()) { $this->error('请求错误'); } $param = $this->request->param(); if (empty($param) || empty($param['year'])) { $this->error('参数错误'); } // 1. 获取设备分组,作为工序依据 $sist = ['胶印车间','凹丝印车间','印后车间','检验车间']; $processOrder = \db('设备_基本资料') ->whereNotNull('设备编组') ->whereIn('使用部门', $sist) ->group('设备编组') ->column('RTRIM(设备编组) as 设备编组'); if (empty($processOrder)) { $this->success('成功', []); } // 2. 初始化结果数组(按工序) $result = []; foreach ($processOrder as $process) { $key = substr($process, 5, 6); $result[$key] = []; for ($i = 1; $i <= 12; $i++) { $result[$key][str_pad($i, 2, '0', STR_PAD_LEFT) . '月'] = ''; } $result[$key]['平均值'] = '0.00%'; } // 3. 汇总所有年份内数据,一次查出所有“工序-月份”数据 $dataList = \db('工单_质量考核汇总') ->alias('a') ->join('工单_工艺资料 b', 'a.Gy0_gdbh = b.Gy0_gdbh AND a.印件及工序 = b.Gy0_yjno AND a.工序 = b.Gy0_gxh') ->join('工单_印件资料 c', 'a.Gy0_gdbh = c.Yj_Gdbh AND a.印件及工序 = c.yj_Yjno') ->join('成品入仓 d', 'a.Gy0_gdbh = d.jjcp_gdbh AND a.印件及工序 = d.jjcp_yjno') ->join('设备_基本资料 e', 'a.sczl_jtbh = e.设备编号') ->whereIn('e.设备编组', $processOrder) ->where('d.jjcp_smb', 'like', '末%') ->where('a.sys_ny', 'like', $param['year'] . '%') ->field([ 'e.设备编组', 'a.sys_ny', 'SUM(a.班组制程废品/ a.联数) AS 制程废品', 'SUM(a.班组质检废品/ a.联数) AS 质检废品', 'SUM(a.班组产量/ a.联数) AS 产量' ]) ->group('e.设备编组, a.sys_ny') ->select(); //修正前计算方式 // // 4. 处理统计数据,填充到$result // $annual = []; // 年均合格率累加用 // foreach ($dataList as $item) { // $processKey = substr($item['设备编组'], 5, 6); // $monthKey = substr($item['sys_ny'], 4, 2) . '月'; // // $output = floatval($item['产量']); // $waste = floatval($item['制程废品']) + floatval($item['质检废品']); // $rate = $output > 0 ? (1 - $waste / $output) * 100 : 0; // $result[$processKey][$monthKey] = sprintf('%.2f%%', $rate); // // if (!isset($annual[$processKey])) { // $annual[$processKey] = ['sum' => 0, 'count' => 0]; // } // $annual[$processKey]['sum'] += $rate; // $annual[$processKey]['count']++; // } // // 5. 补充“平均值” // foreach ($annual as $processKey => $v) { // if ($v['count'] > 0) { // $result[$processKey]['平均值'] = sprintf('%.2f%%', $v['sum']/$v['count']); // } // } //修正后计算方式 // 【用于算平均值】 $yearTotal = []; // 4. 处理每月数据 foreach ($dataList as $item) { $processKey = substr($item['设备编组'], 5, 6); $monthKey = substr($item['sys_ny'], 4, 2) . '月'; $output = floatval($item['产量']); $waste = floatval($item['制程废品']) + floatval($item['质检废品']); $rate = $output > 0 ? (1 - $waste / $output) * 100 : 0; $result[$processKey][$monthKey] = sprintf('%.2f%%', $rate); // 【累计:总产量、总废品】 if (!isset($yearTotal[$processKey])) { $yearTotal[$processKey] = [ 'totalOutput' => 0, 'totalWaste' => 0 ]; } $yearTotal[$processKey]['totalOutput'] += $output; $yearTotal[$processKey]['totalWaste'] += $waste; } //打印总计 // echo "
";
//        print_r($yearTotal);
//        echo "
";

        // 【平均值:用全年总和计算】
        foreach ($yearTotal as $processKey => $val) {
            $totalOutput = $val['totalOutput'];
            $totalWaste = $val['totalWaste'];
            if ($totalOutput > 0) {
                $avgRate = (1 - $totalWaste / $totalOutput) * 100;
                $result[$processKey]['平均值'] = sprintf('%.2f%%', $avgRate);
            } else {
                $result[$processKey]['平均值'] = '0.00%';
            }
        }

        // 6. 转前端格式
        $formattedData = [];
        foreach ($result as $process => $data) {
            $row = array_merge(['工序类型' => $process], $data);
            $formattedData[] = $row;
        }
        $this->success('成功', $formattedData);
    }
    /**
     * 获取年分数据
     * @return void
     */
    public function GetYear()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $data = \db('工单_质量考核汇总')
                ->group('year')
                ->column('YEAR(STR_TO_DATE(sys_ny, "%Y%m")) as year');
        $this->success('成功',$data);
    }

    /**
     * 数据透视表
     * @return null
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function PoductData()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        if (empty($param['year'])) {
            $this->error('参数错误');
        }

        // 获取设备编组列表(已按编号排序)
        $processGroups = \db('设备_基本资料')
            ->whereNotNull('设备编组')
            ->group('设备编组')
            ->column('rtrim(设备编组) as 设备编组');

        // 构建基础查询
        $query = \db('工单_质量考核汇总')
            ->alias('a')
            ->field('
            a.sczl_jtbh AS 机台编号,
            a.Gy0_gdbh AS 工单编号,
            a.印件及工序 AS 工序号,
            a.产品名称 AS 印件名称,
            a.工序 AS 工序,
            a.工序名称,
            b.Gy0_gxmc,
            a.联数,
            a.班组产量,
            a.班组制程废品,
            a.班组质检废品,
            a.sczl_bzdh AS 班组编号,
            b.印刷方式,
            b.版距,
            DATE_FORMAT(a.入仓日期, \'%Y%m\') AS 完工年月,
            d.设备编组
        ')
            ->join('工单_工艺资料 b', 'a.Gy0_gdbh = b.Gy0_gdbh AND a.印件及工序 = b.Gy0_yjno AND a.工序 = b.Gy0_gxh')
            ->join('成品入仓 c', 'a.Gy0_gdbh = c.jjcp_gdbh AND a.印件及工序 = c.jjcp_yjno')
            ->join('设备_基本资料 d', 'a.sczl_jtbh = d.设备编号')
            ->where('a.sys_ny', 'like', $param['year'] . '%')
            ->where('a.工序名称', 'not like', '%机检%')
            ->where('c.jjcp_smb', 'like', '末%')
            ->where('a.工序名称', 'not like', '%拆片%');

        // 获取总数
        $total = clone $query;
        $total = $total->count();

        // 执行查询
        $rawList = isset($param['page'], $param['limit'])
            ? $query->page($param['page'])->limit($param['limit'])->select()
            : $query->select();

        // 构建分类映射表
        $groupMap = [];
        foreach ($processGroups as $index => $group) {
            // 提取机组名称核心词(去除编号和"机组"字样)
            $groupName = preg_replace('/^\d+、/', '', $group); // 移除开头编号
            $coreName = str_replace('机组', '', $groupName);   // 移除尾部的"机组"
            $groupMap[] = [
                'id' => $index + 1,
                'name' => $groupName,
                'core' => $coreName
            ];
        }
        // 处理分类标记
        $processedList = [];
        foreach ($rawList as $item) {
            foreach ($groupMap as $group) {
                // 使用核心词进行模糊匹配
                if (strpos($item['设备编组'], $group['core']) !== false) {
                    $item['type_id'] = $group['id'];
                    $item['type_name'] = $group['name'];
                    $processedList[] = $item;
                    continue 2;
                }
            }
        }
        // 按分类ID排序
        usort($processedList, function($a, $b) {
            return $a['type_id'] <=> $b['type_id'];
        });

        // 返回结构
        $data['data'] = $processedList;
        $data['total'] = $total;
        $data['filtered_total'] = count($processedList);

        return count($processedList) > 0
            ? $this->success('成功', $data)
            : $this->error('未找到数据');
    }


    /**
     * 月度色度数导出
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function ChromaticityNumber()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param['month'])) {
            $this->error('参数错误');
        }

        $month = date_create_from_format('Ym', $param['month'])->format('Y-m');
        $where = ['a.sczl_rq' => ['like', $month . '%']];

        $list = \db('设备_产量计酬')
            ->alias('a')
            ->join('工单_印件资料 c', 'a.sczl_gdbh = c.Yj_Gdbh AND a.sczl_yjno = c.yj_Yjno')
            ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh')
            ->join('设备_基本资料 e', 'a.sczl_jtbh = e.设备编号')
            ->field([
                'a.sczl_gdbh' => '工单编号',
                'a.sczl_yjno' => '印件号',
                'a.sczl_gxh' => '工序号',
                'd.Gy0_gxmc' => '工序名称',
                'SUM(a.sczl_cl)' => '产量',
                'a.sczl_ms' => '墨色数',
                'rtrim(d.印刷方式)' => '印刷方式',
                'rtrim(d.版距)' => '版距',
                'rtrim(d.Gy0_SITE)' => '车间名称',
                'rtrim(e.设备编组)' => '设备编组',
                'a.sczl_jtbh'
            ])
            ->where($where)
            ->group('a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_rq, a.sczl_jtbh, a.sczl_bzdh')
            ->select();
        $data = [];
        if (!empty($list)) {
            foreach ($list as $key => $value) {
                // Convert ALL outputs to integers
                $value['产量'] = (int)round($value['产量']);

                if ($value['印刷方式'] === '卷对卷' && $value['版距'] != 0) {
                    $value['产量'] = (int)round($value['产量'] / $value['版距'] * 1000);
                }
                if($value['墨色数'] == 0 && $value['设备编组'] !== '11、检品机组'){
                    $value['墨色数'] = 1;
                }
                if($value['设备编组'] === '11、检品机组'){
                    $value['墨色数'] = 0;
                }
                $data[$key] = [
                    'gdbh' => $value['工单编号'],
                    'ms'  => $value['墨色数'],
                    '产量' => $value['产量'],
                    '墨色' => $value['产量'] * $value['墨色数'],
                    '设备编组' => $value['设备编组']
                ];
            }
        }

        $grouped = [];
        foreach ($data as $item) {
            $key = substr($item['设备编组'], 5, 6);
            if (!isset($grouped[$key])) {
                $grouped[$key] = [
                    '产量合计' => 0,
                    '工序名称' => $key,
                    '色度数' => 0
                ];
            }


            $grouped[$key]['产量合计'] += (int)$item['产量'];
            $grouped[$key]['色度数'] += (int)$item['墨色'];
        }


        foreach ($grouped as &$group) {
            $group['产量合计'] = (int)$group['产量合计'];
            $group['色度数'] = (int)$group['色度数'];
        }
        $this->success('成功', array_values($grouped));
    }


    /**
     * 年度质检废品率统计左侧菜单
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function QualityInspectionGetTab()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }
        $monthStats = \db('db_qczl')
            ->group('month')
            ->order('month desc')
            ->column("DATE_FORMAT(qczl_rq, '%Y%m') AS month");

        // 按年月层次结构归类(返回树形结构)
        $treeData = \db('db_qczl')
            ->field([
                "DATE_FORMAT(qczl_rq, '%Y') AS year",
                "DATE_FORMAT(qczl_rq, '%m') AS month",
                "COUNT(*) AS total"
            ])
            ->order('year desc,month desc')
            ->group('year, month')
            ->select();
        // 构建树形结构
        $result = [];
        foreach ($treeData as $item) {
            $year = $item['year'];
            $month = $year . $item['month'];

            if (!isset($result[$year])) {
                $result[$year] = [
                    'year' => $year,
                    'children' => []
                ];
            }

            $result[$year]['children'][] = [
                'month' => $month,
                'total' => $item['total']
            ];
        }

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


    /**
     * 年度质检废品统计右侧上方列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function QualityInspectionList()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        $where = ['年月' => ['like', $param['month'] . '%']];

        // 查询工单列表
        $workList = \db('rec_月度废品汇总')
            ->where($where)
            ->field("CONCAT(Gd_gdbh,'-',印件号) as 工单,实际投料")
            ->group('工单')
            ->select();
        $work = [];
        foreach ($workList as $item) {
            $work[$item['工单']] = $item['实际投料'];
        }

        // 查询工单对应的废品类别首字母(按工单和首字母分组)
        $WasteWorkList = \db('rec_月度废品汇总')
            ->where($where)
            ->field("CONCAT(Gd_gdbh,'-',印件号) as 工单, left(废品类别,1) as 废品类别首字母")
            ->group('工单, 废品类别首字母')
            ->order('废品类别首字母 asc')
            ->select();
        $waste = [];
        foreach ($WasteWorkList as $item) {
            if (!isset($waste[$item['废品类别首字母']])) {
                $waste[$item['废品类别首字母']] = [];
            }
            $waste[$item['废品类别首字母']][] = $item['工单'];
        }

        // 计算每个首字母的实际投料总和
        $res = [];
        foreach ($waste as $key => $item) {
            $res[$key] = array_sum(array_intersect_key($work, array_flip($item)));
        }

        // 查询废品类别对应的废品数量
        $wasteList = \db('rec_月度废品汇总')
            ->where($where)
            ->group('废品类别')
            ->order('废品类别 asc')
            ->column("sum(废品数量) as 废品数量", "废品类别");

        // 从废品类别数据计算每个首字母的废品数量总和
        $classification = [];
        foreach ($wasteList as $category => $amount) {
            $firstChar = substr($category, 0, 1);
            if (!isset($classification[$firstChar])) {
                $classification[$firstChar] = 0;
            }
            $classification[$firstChar] += $amount;
        }

        // 构建数据数组
        $data = [];
        foreach ($wasteList as $category => $amount) {
            $firstChar = substr($category, 0, 1);
            $number = $res[$firstChar] ?? 0;
            $data[] = [
                '废品类别' => $category,
                '实际投料' => $number,
                '废品数量' => $amount,
                '质检废品率' => (round($amount/$number, 7) * 100) . '%',
            ];
        }

        // 添加首字母合计行
        foreach ($classification as $firstChar => $amount) {
            $number = $res[$firstChar] ?? 0;
            $data[] = [
                '废品类别' => $firstChar . '-合计',
                '实际投料' => $number,
                '废品数量' => $amount,
                '质检废品率' => (round($amount/$number, 7) * 100) . '%',
            ];
        }

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

    //判断字符串首位是否为英文字母
    function isFirstCharEnglish($str) {
        if (mb_strlen($str) == 0) return false;

        $firstChar = mb_substr($str, 0, 1);
        return preg_match('/^[A-Za-z]$/u', $firstChar);
    }


    /**
     * 年度质检废品率统计下方列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function QualityInspectionDetailList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        $where = [
            '年月' => ['like',$param['month'].'%'],
            '废品类别' => $param['class']
        ];
        $list = \db('rec_月度废品汇总')
            ->where($where)
            ->field('年月,客户编号,客户名称,产品名称,Gd_gdbh as 工单编号,印件号,实际投料,废品类别,sum(废品数量) as 废品数量,质检完工时间')
            ->group('工单编号,印件号,废品类别')
            ->select();
        foreach ($list as $key => $item) {
            $list[$key]['质检废品率'] = (round($item['废品数量']/$item['实际投料'], 7)*100).'%';
            $list[$key]['年周数'] = substr($list[$key]['质检完工时间'],0,4).'年第'.date('W',strtotime($item['质检完工时间'])).'周';
        }
        $this->success('成功', $list);

    }


    /**
     * 创建月度废品率系数
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function QualityInspectionAdd()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

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

        // 1. 修复WHERE条件(原来的第二个WHERE会覆盖第一个)
        $where = ['jjcp_sj' => ['between', [$param['startMonth'], $param['endMonth']]]];
        $where = ['jjcp_smb' => ['like', '末%']];

        // 2. 优化字段选择,只选择必要的字段
        $field = [
            'a.qczl_gdbh', 'a.qczl_yjno', 'a.qczl_rq',
            'c.成品编码', 'c.成品名称', 'd.规格',
            'b.实际投料', 'e.客户编号', 'e.客户名称',
            "DATE_FORMAT(c.jjcp_sj, '%Y%m') AS ym",
            'c.jjcp_cpdh', 'c.jjcp_cpmc'
        ];

        // 3. 添加fp_lb和fp_sl字段
        for ($i = 1; $i <= 13; $i++) {
            $field[] = "a.fp_lb{$i}";
            $field[] = "a.fp_sl{$i}";
        }

        // 4. 执行查询
        $list = \db('db_qczl')
            ->alias('a')
            ->join('工单_基本资料 b', 'a.qczl_gdbh = b.Gd_gdbh and a.qczl_yjno = b.行号', 'LEFT')
            ->join('成品入仓 c', 'a.qczl_gdbh = c.jjcp_gdbh and a.qczl_yjno = c.jjcp_yjno', 'LEFT')
            ->join('物料_存货编码 d', 'c.jjcp_cpdh = d.物料代码', 'LEFT')
            ->join('产品_基本资料 e', 'c.成品编码 = e.产品编号', 'LEFT')
            ->field($field)
            ->where($where)
            ->select();

        if (empty($list)) {
            $this->error('没有找到符合条件的数据');
        }

        $data = [];
        $currentTime = date('Y-m-d H:i:s');
        // 5. 优化循环处理
        foreach ($list as $item) {
            // 预先处理公共数据
            $commonData = [
                '年月' => $item['ym'],
                '客户编号' => $item['客户编号'],
                '客户名称' => $item['客户名称'],
                '产品类别' => $item['规格'],
                '产品编号' => $item['成品编码'],
                '产品名称' => $item['成品名称'],
                'Gd_gdbh' => $item['qczl_gdbh'],
                '印件号' => $item['qczl_yjno'],
                'Gd_cpdh' => $item['jjcp_cpdh'] ?? '',
                'Gd_cpmc' => $item['jjcp_cpmc'] ?? '',
                '实际投料' => ($item['实际投料'] ?? 0) * 10000,
                '质检完工时间' => $item['qczl_rq'],
                'sys_id' => $param['sys_id'],
                'Sys_rq' => $currentTime
            ];

            // 处理13个废品类别
            for ($i = 1; $i <= 13; $i++) {
                $lbField = "fp_lb{$i}";
                $slField = "fp_sl{$i}";

                if (!empty($item[$lbField]) && $this->isFirstCharEnglish($item[$lbField]) && $item[$slField] != 0) {
                    $data[] = array_merge($commonData, [
                        '废品类别' => $item[$lbField],
                        '废品数量' => $item[$slField] ?? 0
                    ]);
                }
            }
        }

        if (empty($data)) {
            $this->error('没有符合条件的数据需要插入');
        }

        // 6. 分批插入避免单次数据量过大
        $chunks = array_chunk($data, 100); // 每批100条
        $successCount = 0;

        foreach ($chunks as $chunk) {
            $result = \db('rec_月度废品汇总')->insertAll($chunk);
            if ($result !== false) {
                $successCount += $result;
            }
        }

        if ($successCount > 0) {
            $this->success("成功插入 {$successCount} 条数据");
        } else {
            $this->error('插入数据失败');
        }
    }

    /**
     * 成品入仓印件名称分类
     * 含“小盒” -> 小盒;含“面彩/围条/内贴/上盖/下盖” -> 其他;其余 -> 条盒
     */
    private function CprcCategory($cpmc)
    {
        $cpmc = trim((string)$cpmc);
        if ($cpmc === '') {
            return '条盒';
        }
        if (strpos($cpmc, '小盒') !== false) {
            return '小盒';
        }
        foreach (['面彩', '围条', '内贴', '上盖', '下盖'] as $kw) {
            if (strpos($cpmc, $kw) !== false) {
                return '其他';
            }
        }
        return '条盒';
    }

    /**
     * 取成品入仓指定账期(年月)的入仓记录
     * @param string $ny 6位年月(YYYYMM) 或 4位年份(YYYY)
     */
    private function CprcMonthRows($ny)
    {
        $ny = (string)$ny;
        if (strlen($ny) === 4) {
            $year = (int)$ny;
            $start = $year . '-01-01 00:00:00';
            $end = ($year + 1) . '-01-01 00:00:00';
        } else {
            $year = (int)substr($ny, 0, 4);
            $month = (int)substr($ny, 4, 2);
            $start = date('Y-m-01 00:00:00', mktime(0, 0, 0, $month, 1, $year));
            $end = date('Y-m-01 00:00:00', mktime(0, 0, 0, $month + 1, 1, $year));
        }

        return \db('成品入仓')
            ->field('jjcp_gdbh, jjcp_yjno, jjcp_cpdh, jjcp_cpmc, jjcp_sl, jjcp_dw, jjcp_sj')
            ->where('jjcp_sj', '>=', $start)
            ->where('jjcp_sj', '<', $end)
            ->order('jjcp_sj desc')
            ->select();
    }

    /**
     * 成品入仓统计左侧菜单(年 -> 年月,值为入仓数量合计/10000)
     */
    public function CprctjLeft()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $rows = \db('成品入仓')
            ->field('jjcp_sj, jjcp_cpmc, jjcp_sl')
            ->whereNotNull('jjcp_sj')
            ->select();

        // 先按月聚合
        $months = [];
        foreach ($rows as $row) {
            $sj = trim((string)$row['jjcp_sj']);
            if ($sj === '') {
                continue;
            }
            $ny = substr(str_replace('-', '', substr($sj, 0, 10)), 0, 6);
            if (!preg_match('/^\d{6}$/', $ny)) {
                continue;
            }

            $cat = $this->CprcCategory($row['jjcp_cpmc']);
            if ($cat === '其他') {
                continue;
            }

            if (!isset($months[$ny])) {
                $months[$ny] = ['小盒' => 0, '条盒' => 0];
            }
            $months[$ny][$cat] += (float)$row['jjcp_sl'];
        }
        krsort($months);

        // 再按年聚合
        $years = [];
        foreach ($months as $ny => $item) {
            $year = substr((string)$ny, 0, 4);
            if (!isset($years[$year])) {
                $years[$year] = ['小盒' => 0, '条盒' => 0, '月份' => []];
            }
            $years[$year]['小盒'] += $item['小盒'];
            $years[$year]['条盒'] += $item['条盒'];
            $years[$year]['月份'][] = [
                '年月' => (string)$ny,
                '小盒' => round($item['小盒'] / 10000, 3),
                '条盒' => round($item['条盒'] / 10000, 3),
            ];
        }
        krsort($years);

        $data = [];
        foreach ($years as $year => $item) {
            $data[] = [
                '年份' => (string)$year,
                '小盒' => round($item['小盒'] / 10000, 2),
                '条盒' => round($item['条盒'] / 10000, 2),
                '月份' => $item['月份'],
            ];
        }

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

    /**
     * 成品入仓统计上方表格(按日期汇总)
     */
    public function CprctjList_hz()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        $ny = trim((string)(isset($param['ym']) ? $param['ym'] : ''));
        if (!preg_match('/^\d{4}(\d{2})?$/', $ny)) {
            $this->error('参数错误');
        }

        $rows = $this->CprcMonthRows($ny);

        $days = [];
        foreach ($rows as $row) {
            $day = substr(trim((string)$row['jjcp_sj']), 0, 10);
            if (!isset($days[$day])) {
                $days[$day] = [
                    '日期' => $day,
                    '小盒入仓数' => 0,
                    '条盒入仓数' => 0,
                    '其他类' => 0,
                    '入仓数量' => 0,
                ];
            }

            $cat = $this->CprcCategory($row['jjcp_cpmc']);
            $sl = (float)$row['jjcp_sl'];

            if ($cat === '小盒') {
                $days[$day]['小盒入仓数'] += $sl;
            } elseif ($cat === '其他') {
                $days[$day]['其他类'] += $sl;
            } else {
                $days[$day]['条盒入仓数'] += $sl;
            }
            $days[$day]['入仓数量'] += $sl;
        }
        krsort($days);

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

    /**
     * 成品入仓统计下方表格(工单 + 印件明细)
     * 客户信息成品入仓表中没有,取自 工单_基本资料
     */
    public function CprctjList_mx()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        $ny = trim((string)(isset($param['ym']) ? $param['ym'] : ''));
        if (!preg_match('/^\d{4}(\d{2})?$/', $ny)) {
            $this->error('参数错误');
        }
        $date = trim((string)(isset($param['date']) ? $param['date'] : ''));

        $rows = $this->CprcMonthRows($ny);

        $list = [];
        $gdbhList = [];
        foreach ($rows as $row) {
            $day = substr(trim((string)$row['jjcp_sj']), 0, 10);
            if ($date !== '' && $day !== $date) {
                continue;
            }

            $gdbh = trim((string)$row['jjcp_gdbh']);
            $key = $gdbh . '|' . trim((string)$row['jjcp_cpdh']) . '|' . trim((string)$row['jjcp_cpmc']) . '|' . $day;

            if (!isset($list[$key])) {
                $list[$key] = [
                    '工单编号' => $gdbh,
                    '印件代号' => trim((string)$row['jjcp_cpdh']),
                    '印件名称' => trim((string)$row['jjcp_cpmc']),
                    '日期' => $day,
                    '小盒数' => 0,
                    '条盒数' => 0,
                    '其他类' => 0,
                    '入仓数量' => 0,
                    '单位' => trim((string)$row['jjcp_dw']),
                    '客户编号' => '',
                    '客户名称' => ''
                ];
                if ($gdbh !== '') {
                    $gdbhList[$gdbh] = 1;
                }
            }

            $cat = $this->CprcCategory($row['jjcp_cpmc']);
            $sl = (float)$row['jjcp_sl'];

            if ($cat === '小盒') {
                $list[$key]['小盒数'] += $sl;
            } elseif ($cat === '其他') {
                $list[$key]['其他类'] += $sl;
            } else {
                $list[$key]['条盒数'] += $sl;
            }
            $list[$key]['入仓数量'] += $sl;
        }

        // 客户信息:按工单编号取最新一条工单_基本资料
        if (!empty($gdbhList)) {
            $orders = \db('工单_基本资料')
                ->field('Gd_gdbh, Gd_客户代号, Gd_客户名称')
                ->whereIn('Gd_gdbh', array_keys($gdbhList))
                ->order('Sys_rq desc')
                ->select();

            $customerMap = [];
            foreach ($orders as $order) {
                $gdbh = trim((string)$order['Gd_gdbh']);
                if (!isset($customerMap[$gdbh])) {
                    $customerMap[$gdbh] = [
                        '编号' => trim((string)$order['Gd_客户代号']),
                        '名称' => trim((string)$order['Gd_客户名称'])
                    ];
                }
            }

            foreach ($list as $key => $item) {
                if (isset($customerMap[$item['工单编号']])) {
                    $list[$key]['客户编号'] = $customerMap[$item['工单编号']]['编号'];
                    $list[$key]['客户名称'] = $customerMap[$item['工单编号']]['名称'];
                }
            }
        }

        $data = array_values($list);
        usort($data, function ($a, $b) {
            if ($a['日期'] !== $b['日期']) {
                return strcmp($b['日期'], $a['日期']);
            }
            return strcmp($a['工单编号'], $b['工单编号']);
        });

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

    /**
     * 成品编码对应的客户信息(产品_基本资料)
     */
    private function CprcProductCustomer($codes)
    {
        $map = [];
        $codes = array_values(array_unique(array_filter($codes, function ($code) {
            return trim((string)$code) !== '';
        })));
        if (empty($codes)) {
            return $map;
        }

        $rows = \db('产品_基本资料')
            ->field('产品编号, 客户编号, 客户名称')
            ->whereIn('产品编号', $codes)
            ->select();

        foreach ($rows as $row) {
            $code = trim((string)$row['产品编号']);
            if ($code === '' || isset($map[$code])) {
                continue;
            }
            $map[$code] = [
                '客户编号' => trim((string)$row['客户编号']),
                '客户名称' => trim((string)$row['客户名称']),
            ];
        }

        return $map;
    }

    /**
     * 年度成品入仓统计左侧菜单(年 -> 客户)
     */
    public function YearcprctjLeft()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $rows = \db('成品入仓')
            ->field([
                "DATE_FORMAT(jjcp_sj, '%Y') AS 年份",
                '成品编码',
            ])
            ->where('jjcp_sj', '>=', '2000-01-01')
            ->group('年份, 成品编码')
            ->select();

        $customerMap = $this->CprcProductCustomer(array_column($rows, '成品编码'));

        $years = [];
        foreach ($rows as $row) {
            $year = trim((string)$row['年份']);
            $code = trim((string)$row['成品编码']);
            if ($year === '' || $year === '0' || !isset($customerMap[$code])) {
                continue;
            }
            $customer = $customerMap[$code];
            if ($customer['客户名称'] === '') {
                continue;
            }
            if (!isset($years[$year])) {
                $years[$year] = ['年份' => $year, '客户' => []];
            }
            $customerKey = $customer['客户编号'] !== '' ? $customer['客户编号'] : $customer['客户名称'];
            $years[$year]['客户'][$customerKey] = [
                '客户编号' => $customer['客户编号'],
                '客户名称' => $customer['客户名称'],
            ];
        }
        krsort($years);

        $data = [];
        foreach ($years as $year) {
            ksort($year['客户']);
            $year['客户'] = array_values($year['客户']);
            $data[] = $year;
        }

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

    /**
     * 年度成品入仓统计右侧表格
     * 产品取自成品入仓(成品编码/成品名称),大箱数取自 db_包装计件 的 sczl_cl1-6,按 sczl_rq 月份汇总
     */
    public function YearcprctjList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        $year = trim((string)(isset($param['year']) ? $param['year'] : ''));
        if (!preg_match('/^\d{4}$/', $year)) {
            $this->error('参数错误');
        }
        $customer = trim((string)(isset($param['customer']) ? $param['customer'] : ''));
        $search = trim((string)(isset($param['search']) ? $param['search'] : ''));

        $start = $year . '-01-01 00:00:00';
        $end = ((int)$year + 1) . '-01-01 00:00:00';

        // 1、成品入仓:本年度有入仓记录的产品
        $warehouseRows = \db('成品入仓')
            ->field('jjcp_gdbh, 成品编码, 成品名称')
            ->where('jjcp_sj', '>=', $start)
            ->where('jjcp_sj', '<', $end)
            ->where('jjcp_gdbh', '<>', '')
            ->select();

        $products = [];      // 产品编号 => 行数据
        $orderProduct = [];  // 工单编号(大写) => 产品编号
        foreach ($warehouseRows as $row) {
            $code = trim((string)$row['成品编码']);
            if ($code === '') {
                continue;
            }
            if (!isset($products[$code])) {
                $name = trim((string)$row['成品名称']);
                $products[$code] = [
                    '产品编号' => $code,
                    '产品名称' => $name,
                    '产品类别' => strpos($name, '小盒') !== false ? '小盒' : '条盒',
                    '客户编号' => '',
                    '客户名称' => '',
                    'monthly' => array_fill(1, 12, 0),
                ];
            }

            $order = strtoupper(trim((string)$row['jjcp_gdbh']));
            if ($order !== '' && !isset($orderProduct[$order])) {
                $orderProduct[$order] = $code;
            }
        }

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

        // 2、包装计件:按 sczl_rq 的月份汇总对应工单的 sczl_cl1-6
        $packRows = \db('db_包装计件')
            ->field('sczl_rq, sczl_gdbh1, sczl_gdbh2, sczl_gdbh3, sczl_gdbh4, sczl_gdbh5, sczl_gdbh6, sczl_cl1, sczl_cl2, sczl_cl3, sczl_cl4, sczl_cl5, sczl_cl6')
            ->where('sczl_rq', '>=', $start)
            ->where('sczl_rq', '<', $end)
            ->select();

        foreach ($packRows as $row) {
            $rq = str_replace('/', '-', trim((string)$row['sczl_rq']));
            $month = (int)substr($rq, 5, 2);
            if ($month < 1 || $month > 12) {
                continue;
            }
            for ($i = 1; $i <= 6; $i++) {
                $order = strtoupper(trim((string)$row['sczl_gdbh' . $i]));
                if ($order === '' || !isset($orderProduct[$order])) {
                    continue;
                }
                $products[$orderProduct[$order]]['monthly'][$month] += (float)$row['sczl_cl' . $i];
            }
        }

        // 3、客户信息:产品_基本资料(按成品编码)
        $customerMap = $this->CprcProductCustomer(array_keys($products));
        foreach ($products as $code => $product) {
            if (isset($customerMap[$code])) {
                $products[$code]['客户编号'] = $customerMap[$code]['客户编号'];
                $products[$code]['客户名称'] = $customerMap[$code]['客户名称'];
            }
        }

        // 4、组装数据:无数据的月份留空
        $data = [];
        foreach ($products as $product) {
            if ($customer !== '' && $product['客户编号'] !== $customer) {
                continue;
            }
            if ($search !== ''
                && strpos($product['产品编号'], $search) === false
                && strpos($product['产品名称'], $search) === false) {
                continue;
            }

            $total = 0;
            $months = [];
            for ($m = 1; $m <= 12; $m++) {
                $value = $product['monthly'][$m];
                $total += $value;
                $months[str_pad($m, 2, '0', STR_PAD_LEFT) . '月'] = $value > 0 ? round($value, 2) : '';
            }

            $data[] = array_merge([
                '产品编号' => $product['产品编号'],
                '产品名称' => $product['产品名称'],
                '产品类别' => $product['产品类别'],
                '每月合计' => round($total, 2),
            ], $months, [
                '客户编号' => $product['客户编号'],
                '客户名称' => $product['客户名称'],
            ]);
        }

        usort($data, function ($a, $b) {
            return strcmp($a['产品编号'], $b['产品编号']);
        });

        // 5、底部合计行:小盒合计、条盒合计
        $totalRows = [];
        foreach (['小盒', '条盒'] as $category) {
            $exists = false;
            $total = 0;
            $sums = array_fill(1, 12, 0);
            foreach ($data as $row) {
                if ($row['产品类别'] !== $category) {
                    continue;
                }
                $exists = true;
                $total += (float)$row['每月合计'];
                for ($m = 1; $m <= 12; $m++) {
                    $monthKey = str_pad($m, 2, '0', STR_PAD_LEFT) . '月';
                    $sums[$m] += $row[$monthKey] === '' ? 0 : (float)$row[$monthKey];
                }
            }
            if (!$exists) {
                continue;
            }

            $totalRow = [
                '产品编号' => '',
                '产品名称' => $category . '合计(大箱)',
                '产品类别' => '',
                '每月合计' => round($total, 2),
                '合计类型' => $category,
            ];
            for ($m = 1; $m <= 12; $m++) {
                $monthKey = str_pad($m, 2, '0', STR_PAD_LEFT) . '月';
                $totalRow[$monthKey] = $sums[$m] > 0 ? round($sums[$m], 2) : '';
            }
            $totalRow['客户编号'] = '';
            $totalRow['客户名称'] = '';
            $totalRows[] = $totalRow;
        }
        $data = array_merge($data, $totalRows);

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

    /**
     * 客诉质量缺陷统计A 左侧菜单(年 -> 缺陷关键字)
     */
    public function KhtjLeftA()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $rows = \db('db_客诉记录')
            ->field("DATE_FORMAT(客诉日期, '%Y') AS 年份, rtrim(缺陷关键字) AS 缺陷关键字, count(1) AS num")
            ->group("DATE_FORMAT(客诉日期, '%Y'), rtrim(缺陷关键字)")
            ->select();

        $years = [];
        foreach ($rows as $row) {
            $year = trim((string)$row['年份']);
            if ($year === '' || $year === '0') {
                continue;
            }
            if (!isset($years[$year])) {
                $years[$year] = ['总数' => 0, '关键字' => []];
            }
            $num = (int)$row['num'];
            $years[$year]['总数'] += $num;
            $keyword = trim((string)$row['缺陷关键字']);
            if ($keyword !== '') {
                $years[$year]['关键字'][$keyword] = $num;
            }
        }
        krsort($years);

        $data = [];
        foreach ($years as $year => $info) {
            arsort($info['关键字']);
            $children = [];
            foreach ($info['关键字'] as $keyword => $num) {
                $children[] = ['关键字' => $keyword, '次数' => $num];
            }
            $data[] = [
                '年份' => $year,
                '次数' => $info['总数'],
                'children' => $children,
            ];
        }

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

    /**
     * 客诉质量缺陷统计A 明细(年 + 缺陷关键字筛选)
     */
    public function KhtjListA()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        $year = trim((string)(isset($param['year']) ? $param['year'] : ''));
        if (!preg_match('/^\d{4}$/', $year)) {
            $this->error('参数错误');
        }
        $keyword = trim((string)(isset($param['keyword']) ? $param['keyword'] : ''));
        $search = trim((string)(isset($param['search']) ? $param['search'] : ''));

        $data = $this->KhtjDetailRows(
            $year . '-01-01 00:00:00',
            ((int)$year + 1) . '-01-01 00:00:00',
            '',
            $keyword,
            $search
        );

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

    /**
     * 客诉质量缺陷统计B 左侧菜单(年 -> 客户)
     */
    public function KhtjLeftB()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $rows = \db('db_客诉记录')
            ->field("DATE_FORMAT(客诉日期, '%Y') AS 年份, rtrim(客户编号) AS 客户编号, rtrim(客户名称) AS 客户名称")
            ->group("DATE_FORMAT(客诉日期, '%Y'), rtrim(客户编号), rtrim(客户名称)")
            ->select();

        $years = [];
        foreach ($rows as $row) {
            $year = trim((string)$row['年份']);
            $code = trim((string)$row['客户编号']);
            $name = trim((string)$row['客户名称']);
            if ($year === '' || $year === '0' || $code === '') {
                continue;
            }
            if (!isset($years[$year])) {
                $years[$year] = [];
            }
            if (!isset($years[$year][$code]) && $name !== '') {
                $years[$year][$code] = $name;
            }
        }
        krsort($years);

        $data = [];
        foreach ($years as $year => $customers) {
            ksort($customers);
            $children = [];
            foreach ($customers as $code => $name) {
                $children[] = ['客户编号' => $code, '客户名称' => $name];
            }
            $data[] = ['年份' => $year, '客户' => $children];
        }

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

    /**
     * 客诉质量缺陷统计B 月度汇总 + 明细
     */
    public function KhtjListB()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        $year = trim((string)(isset($param['year']) ? $param['year'] : ''));
        if (!preg_match('/^\d{4}$/', $year)) {
            $this->error('参数错误');
        }
        $customer = trim((string)(isset($param['customer']) ? $param['customer'] : ''));
        $search = trim((string)(isset($param['search']) ? $param['search'] : ''));

        $start = $year . '-01-01 00:00:00';
        $end = ((int)$year + 1) . '-01-01 00:00:00';

        // 1、月度汇总:按客户统计全年及每月客诉次数(按客诉日期所在月份)
        $summary = $this->KhtjSummaryRows($start, $end, $customer, $search);

        // 2、客诉明细
        $list = $this->KhtjDetailRows($start, $end, $customer, '', $search);

        $this->success('成功', ['summary' => $summary, 'list' => $list]);
    }

    /**
     * 客诉月度汇总(按客户统计全年及每月客诉次数)
     */
    private function KhtjSummaryRows($start, $end, $customer = '', $search = '')
    {
        $rows = \db('db_客诉记录')
            ->field("rtrim(客户编号) AS 客户编号, rtrim(客户名称) AS 客户名称, DATE_FORMAT(客诉日期, '%m') AS 月")
            ->where('客诉日期', '>=', $start)
            ->where('客诉日期', '<', $end)
            ->select();

        $customers = [];
        foreach ($rows as $row) {
            $code = trim((string)$row['客户编号']);
            if ($code === '') {
                continue;
            }
            $month = (int)$row['月'];
            if ($month < 1 || $month > 12) {
                continue;
            }
            if (!isset($customers[$code])) {
                $customers[$code] = [
                    '客户名称' => trim((string)$row['客户名称']),
                    '总数' => 0,
                    'monthly' => array_fill(1, 12, 0),
                ];
            }
            $customers[$code]['总数']++;
            $customers[$code]['monthly'][$month]++;
        }

        $year = substr($start, 0, 4);
        $summary = [];
        foreach ($customers as $code => $info) {
            if ($customer !== '' && $code !== $customer) {
                continue;
            }
            if ($search !== '' && mb_stripos($code . ' ' . $info['客户名称'], $search) === false) {
                continue;
            }
            $summaryRow = [
                '年月' => $year,
                '客户编号' => $code,
                '客户名称' => $info['客户名称'],
                '年度投诉次数' => $info['总数'],
            ];
            for ($m = 1; $m <= 12; $m++) {
                $summaryRow['月份' . str_pad($m, 2, '0', STR_PAD_LEFT)] = $info['monthly'][$m] > 0 ? $info['monthly'][$m] : '';
            }
            $summary[] = $summaryRow;
        }
        usort($summary, function ($a, $b) {
            return strcmp($a['客户编号'], $b['客户编号']);
        });

        return $summary;
    }

    /**
     * 客诉明细查询(统计A/B共用)
     */
    private function KhtjDetailRows($start, $end, $customer = '', $keyword = '', $search = '')
    {
        $field = 'rtrim(客诉单号) AS 客诉单号, 客诉日期, rtrim(客诉方式) AS 客诉方式, rtrim(订单编号) AS 订单编号, '
            . 'rtrim(产品编号) AS 产品编号, rtrim(产品名称) AS 产品名称, rtrim(客户编号) AS 客户编号, rtrim(客户名称) AS 客户名称, '
            . 'rtrim(客诉描述) AS 客诉描述, rtrim(缺陷关键字) AS 缺陷关键字, rtrim(客诉性质) AS 客诉性质, 扣分, '
            . 'rtrim(主要责任部门) AS 主要责任部门, rtrim(次要责任部门) AS 次要责任部门, rtrim(Sys_id) AS 创建用户, '
            . 'Sys_rq AS 创建时间, 关闭时间 AS 结案时间, UniqID';

        $query = \db('db_客诉记录')
            ->field($field)
            ->where('客诉日期', '>=', $start)
            ->where('客诉日期', '<', $end);
        if ($customer !== '') {
            $query->where('客户编号', $customer);
        }
        if ($keyword !== '') {
            $query->where('缺陷关键字', $keyword);
        }
        if ($search !== '') {
            $query->where('客诉单号|订单编号|产品编号|产品名称|客户编号|客户名称|客诉描述|缺陷关键字', 'like', '%' . $search . '%');
        }

        return $query->order('客诉日期 desc')->select();
    }

    /**
     * 年度打样效率统计 左侧菜单(年 -> 年月)
     */
    public function DyxlLeft()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $list = \db('工单_基本资料')
            ->field([
                "DATE_FORMAT(工单完工日期, '%Y') AS 年份",
                "DATE_FORMAT(工单完工日期, '%Y%m') AS 年月",
            ])
            ->where('gd_statu', '1-已完工')
            ->whereRaw($this->DyxlOrderWhere())
            ->whereRaw('工单完工日期 IS NOT NULL')
            ->group("DATE_FORMAT(工单完工日期, '%Y'), DATE_FORMAT(工单完工日期, '%Y%m')")
            ->orderRaw("DATE_FORMAT(工单完工日期, '%Y%m') DESC")
            ->select();

        $years = [];
        foreach ($list as $row) {
            $year = trim((string)$row['年份']);
            $ym = trim((string)$row['年月']);
            if ($year === '' || $ym === '') {
                continue;
            }
            if (!isset($years[$year])) {
                $years[$year] = [];
            }
            if (!in_array($ym, $years[$year], true)) {
                $years[$year][] = $ym;
            }
        }

        $data = [];
        foreach ($years as $year => $months) {
            rsort($months);
            $data[] = ['年份' => (string)$year, '月份' => $months];
        }
        usort($data, function ($a, $b) {
            return strcmp($b['年份'], $a['年份']);
        });

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

    /**
     * 年度打样效率统计 汇总 + 明细
     */
    public function DyxlList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        $date = trim((string)(isset($param['date']) ? $param['date'] : ''));
        if (!preg_match('/^(\d{4})(\d{2})?$/', $date, $match)) {
            $this->error('参数错误');
        }

        $year = (int)$match[1];
        if (isset($match[2]) && $match[2] !== '') {
            $month = (int)$match[2];
            if ($month < 1 || $month > 12) {
                $this->error('参数错误');
            }
            $start = sprintf('%04d-%02d-01', $year, $month);
            if ($month === 12) {
                $end = sprintf('%04d-01-01', $year + 1);
            } else {
                $end = sprintf('%04d-%02d-01', $year, $month + 1);
            }
        } else {
            $start = sprintf('%04d-01-01', $year);
            $end = sprintf('%04d-01-01', $year + 1);
        }

        // 1、打样工单(按工单编号去重)
        $orders = \db('工单_基本资料')
            ->field([
                "RTRIM(Gd_gdbh) AS 工单编号",
                "DATE_FORMAT(工单完工日期, '%Y%m') AS 年月",
                "RTRIM(Gd_客户代号) AS 客户编号",
                "RTRIM(Gd_客户名称) AS 客户名称",
                "RTRIM(成品名称) AS 成品名称",
            ])
            ->where('gd_statu', '1-已完工')
            ->where('工单完工日期', '>=', $start)
            ->where('工单完工日期', '<', $end)
            ->whereRaw($this->DyxlOrderWhere())
            ->order('工单编号')
            ->select();

        $orderMap = [];
        foreach ($orders as $row) {
            $gdbh = trim((string)$row['工单编号']);
            if ($gdbh === '') {
                continue;
            }
            if (!isset($orderMap[$gdbh])) {
                $orderMap[$gdbh] = [
                    '年月' => trim((string)$row['年月']),
                    '客户编号' => trim((string)$row['客户编号']),
                    '客户名称' => trim((string)$row['客户名称']),
                    '工单编号' => $gdbh,
                    '成品名称' => trim((string)$row['成品名称']),
                ];
            } else {
                if ($orderMap[$gdbh]['客户编号'] === '') {
                    $orderMap[$gdbh]['客户编号'] = trim((string)$row['客户编号']);
                }
                if ($orderMap[$gdbh]['客户名称'] === '') {
                    $orderMap[$gdbh]['客户名称'] = trim((string)$row['客户名称']);
                }
                if ($orderMap[$gdbh]['成品名称'] === '') {
                    $orderMap[$gdbh]['成品名称'] = trim((string)$row['成品名称']);
                }
            }
        }

        if (empty($orderMap)) {
            $this->success('成功', ['summary' => [], 'detail' => []]);
        }

        $gdbhs = array_keys($orderMap);
        $categories = ['胶印', '凹印', '单凹', '喷码', '丝印', '烫金', '模切'];

        // 2、工艺资料:工单 + 印件 + 工序
        $crafts = \db('工单_工艺资料')
            ->field([
                "RTRIM(Gy0_gdbh) AS 工单编号",
                "FLOOR(Gy0_yjno) AS 印件号",
                "FLOOR(Gy0_gxh) AS 工序号",
                "RTRIM(Gy0_gxmc) AS 工序名称",
                "RTRIM(Gy0_sbbh) AS 设备编号",
                "Gy0_计划接货数 AS 计划接货数",
                "Gy0_ks AS 开数",
                "Gy0_ls AS 印数",
                "Gy0_小时产能 AS 小时产能",
            ])
            ->whereIn('Gy0_gdbh', $gdbhs)
            ->order('工单编号, 印件号, 工序号')
            ->select();

        // 3、实际工时:设备_产量计酬 中同工单+印件+工序、设备运行工时不为0的记录
        $actualRows = \db('设备_产量计酬')
            ->field([
                "RTRIM(sczl_gdbh) AS 工单编号",
                "FLOOR(sczl_yjno) AS 印件号",
                "FLOOR(sczl_gxh) AS 工序号",
                "MAX(sczl_设备运行工时) AS 实际工时",
            ])
            ->whereIn('sczl_gdbh', $gdbhs)
            ->where('sczl_设备运行工时', '<>', 0)
            ->group('RTRIM(sczl_gdbh), FLOOR(sczl_yjno), FLOOR(sczl_gxh)')
            ->select();

        $actualMap = [];
        foreach ($actualRows as $row) {
            $actualMap[$this->DyxlKey($row['工单编号'], $row['印件号'], $row['工序号'])] = (float)$row['实际工时'];
        }

        $sumPlan = [];
        $sumActual = [];
        $detail = [];

        foreach ($crafts as $craft) {
            $gdbh = trim((string)$craft['工单编号']);
            if (!isset($orderMap[$gdbh])) {
                continue;
            }
            $category = $this->DyxlProcessCategory($craft['设备编号'], $craft['工序名称']);
            if ($category === '') {
                continue;
            }

            $yjno = (int)$craft['印件号'];
            $gxh = (int)$craft['工序号'];
            $plan = $this->DyxlPlanHours($craft);
            $key = $this->DyxlKey($gdbh, $yjno, $gxh);
            $actual = isset($actualMap[$key]) ? $actualMap[$key] : null;

            if ($plan !== null) {
                if (!isset($sumPlan[$gdbh][$category])) {
                    $sumPlan[$gdbh][$category] = 0;
                }
                $sumPlan[$gdbh][$category] += $plan;
            }
            if ($actual !== null) {
                if (!isset($sumActual[$gdbh][$category])) {
                    $sumActual[$gdbh][$category] = 0;
                }
                $sumActual[$gdbh][$category] += $actual;
            }

            $detailRow = $orderMap[$gdbh];
            $detailRow['印件及工序'] = str_pad($gxh, 2, '0', STR_PAD_LEFT) . '-' . trim((string)$craft['工序名称']);
            foreach ($categories as $cat) {
                $detailRow[$cat . '计划工时'] = '';
                $detailRow[$cat . '实际工时'] = '';
            }
            if ($plan !== null) {
                $detailRow[$category . '计划工时'] = $this->DyxlNumber($plan);
            }
            if ($actual !== null) {
                $detailRow[$category . '实际工时'] = $this->DyxlNumber($actual);
            }
            $detail[] = $detailRow;
        }

        // 4、按工单汇总
        $summary = [];
        $totalPlan = array_fill_keys($categories, 0);
        $totalActual = array_fill_keys($categories, 0);
        $hasPlan = [];
        $hasActual = [];

        foreach ($orderMap as $gdbh => $info) {
            $row = $info;
            foreach ($categories as $cat) {
                $plan = isset($sumPlan[$gdbh][$cat]) ? $sumPlan[$gdbh][$cat] : null;
                $actual = isset($sumActual[$gdbh][$cat]) ? $sumActual[$gdbh][$cat] : null;
                $row[$cat . '计划工时'] = $plan === null ? '' : $this->DyxlNumber($plan);
                $row[$cat . '实际工时'] = $actual === null ? '' : $this->DyxlNumber($actual);
                if ($plan !== null) {
                    $totalPlan[$cat] += $plan;
                    $hasPlan[$cat] = true;
                }
                if ($actual !== null) {
                    $totalActual[$cat] += $actual;
                    $hasActual[$cat] = true;
                }
            }
            $summary[] = $row;
        }

        $totalRow = ['年月' => '合计:', '客户编号' => '', '客户名称' => '', '工单编号' => '', '成品名称' => ''];
        foreach ($categories as $cat) {
            $totalRow[$cat . '计划工时'] = !empty($hasPlan[$cat]) ? $this->DyxlNumber($totalPlan[$cat]) : '';
            $totalRow[$cat . '实际工时'] = !empty($hasActual[$cat]) ? $this->DyxlNumber($totalActual[$cat]) : '';
        }
        $totalRow['isTotal'] = true;
        if (!empty($summary)) {
            $summary[] = $totalRow;
        }

        $this->success('成功', ['summary' => $summary, 'detail' => $detail]);
    }

    /**
     * 打样工单条件:工单编号第3位(带Y前缀则第4位)为8
     */
    private function DyxlOrderWhere()
    {
        return "SUBSTRING(RTRIM(Gd_gdbh), IF(LEFT(RTRIM(Gd_gdbh), 1) = 'Y', 4, 3), 1) = '8'";
    }

    /**
     * 按设备编号前缀判定工序类别
     */
    private function DyxlProcessCategory($sbbh, $gxmc)
    {
        $map = ['YJY' => '胶印', 'YWY' => '凹印', 'YDW' => '单凹', 'YPM' => '喷码', 'YSY' => '丝印', 'YTJ' => '烫金', 'YMQ' => '模切'];
        $prefix = strtoupper(substr(trim((string)$sbbh), 0, 3));
        if (isset($map[$prefix])) {
            return $map[$prefix];
        }

        $name = trim((string)$gxmc);
        foreach (['单凹' => '单凹', '凹印' => '凹印', '卷凹' => '凹印', '胶印' => '胶印', '喷码' => '喷码', '丝印' => '丝印', '烫金' => '烫金', '模切' => '模切'] as $keyword => $category) {
            if (strpos($name, $keyword) !== false) {
                return $category;
            }
        }
        return '';
    }

    /**
     * 计划工时 = 计划接货数 * 开数 * 印数 / 小时产能
     */
    private function DyxlPlanHours($craft)
    {
        $qty = (float)$craft['计划接货数'];
        $ks = (float)$craft['开数'];
        $ls = (float)$craft['印数'];
        $capacity = (float)$craft['小时产能'];
        if ($qty <= 0 || $ks <= 0 || $ls <= 0 || $capacity <= 0) {
            return null;
        }
        return $qty * $ks * $ls / $capacity;
    }

    private function DyxlKey($gdbh, $yjno, $gxh)
    {
        return trim((string)$gdbh) . '|' . (int)$yjno . '|' . (int)$gxh;
    }

    private function DyxlNumber($value)
    {
        return number_format(round((float)$value, 2), 2, '.', '');
    }

    /**
     * 年度手工检验统计 左侧菜单(年 -> 客户)
     * 两阶段:先查手工检验取工单号 → 再工单_基本资料去重取客户
     */
    public function YearSgyjLeft()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $inspectRows = \db('db_手工检验')
            ->field([
                "DATE_FORMAT(sczl_rq, '%Y') AS 年份",
                'UPPER(RTRIM(sczl_gdbh)) AS 工单编号',
            ])
            ->where('sczl_rq', '>=', '2000-01-01')
            ->group('年份, 工单编号')
            ->select();

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

        // 提取工单编号,查工单_基本资料并按 Gd_gdbh 去重
        $gdbhs = array_values(array_unique(array_column($inspectRows, '工单编号')));
        $orderMap = $this->YearSgyjOrderMap($gdbhs);

        $years = [];
        foreach ($inspectRows as $row) {
            $year = trim((string)$row['年份']);
            $gdbh = $row['工单编号'];
            if ($year === '' || !isset($orderMap[$gdbh])) {
                continue;
            }
            $info = $orderMap[$gdbh];
            $code = $info['客户编号'];
            $name = $info['客户名称'];
            if ($code === '' && $name === '') {
                continue;
            }
            if (!isset($years[$year])) {
                $years[$year] = ['年份' => $year, '客户' => []];
            }
            $customerKey = $code !== '' ? $code : $name;
            if (!isset($years[$year]['客户'][$customerKey])) {
                $years[$year]['客户'][$customerKey] = [
                    '客户编号' => $code,
                    '客户名称' => $name,
                ];
            }
        }
        krsort($years);

        $data = [];
        foreach ($years as $year) {
            ksort($year['客户']);
            $year['客户'] = array_values($year['客户']);
            $data[] = $year;
        }

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

    /**
     * 年度手工检验统计 右侧数据
     * 两阶段:先查手工检验 → 工单_基本资料按 Gd_gdbh 去重后 PHP 关联
     */
    public function YearSgyjList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        $year = trim((string)(isset($param['year']) ? $param['year'] : ''));
        if (!preg_match('/^\d{4}$/', $year)) {
            $this->error('参数错误');
        }
        $customer = trim((string)(isset($param['customer']) ? $param['customer'] : ''));
        $search = trim((string)(isset($param['search']) ? $param['search'] : ''));

        $start = $year . '-01-01 00:00:00';
        $end = ((int)$year + 1) . '-01-01 00:00:00';

        // 1、手工检验数据(不 join)
        $inspectRows = \db('db_手工检验')
            ->field([
                "DATE_FORMAT(sczl_rq, '%Y%m') AS 年月",
                'UPPER(RTRIM(sczl_gdbh)) AS 工单编号',
                'DATE_FORMAT(sczl_rq, \'%m\') AS 月份',
                '(sczl_cl0+sczl_cl1+sczl_cl2+sczl_cl3+sczl_cl4+sczl_cl5+sczl_cl6+sczl_cl7+sczl_cl8+sczl_cl9+sczl_cl10+sczl_cl11+sczl_cl12) AS 总产量',
            ])
            ->where('sczl_rq', '>=', $start)
            ->where('sczl_rq', '<', $end)
            ->select();

        if (empty($inspectRows)) {
            $this->success('成功', ['summary' => [], 'detail' => []]);
        }

        // 2、工单编号 → 工单信息(去重)
        $gdbhs = array_values(array_unique(array_filter(array_column($inspectRows, '工单编号'))));
        $orderMap = $this->YearSgyjOrderMap($gdbhs);

        // 3、组装完整行:合并工单信息到手工检验
        $mergedRows = [];
        foreach ($inspectRows as $row) {
            $gdbh = $row['工单编号'];
            if (!isset($orderMap[$gdbh])) {
                continue;
            }
            $info = $orderMap[$gdbh];
            $mergedRows[] = [
                '年月' => trim((string)$row['年月']),
                '工单编号' => $gdbh,
                '月份' => (int)$row['月份'],
                '总产量' => (float)$row['总产量'],
                '产品编号' => $info['产品编号'],
                '产品名称' => $info['产品名称'],
                '客户编号' => $info['客户编号'],
                '客户名称' => $info['客户名称'],
                '产品类别' => $info['产品类别'],
            ];
        }

        // 4、客户过滤 + 搜索过滤
        if ($customer !== '') {
            $mergedRows = array_values(array_filter($mergedRows, function ($r) use ($customer) {
                return $r['客户编号'] === $customer;
            }));
        }
        if ($search !== '') {
            $mergedRows = array_values(array_filter($mergedRows, function ($r) use ($search) {
                return strpos($r['产品编号'], $search) !== false || strpos($r['产品名称'], $search) !== false;
            }));
        }

        if (empty($mergedRows)) {
            $this->success('成功', ['summary' => [], 'detail' => []]);
        }

        // 5、summary:按 产品编号 聚合(产品编号为空时用产品名称兜底),按月份分月汇总
        $products = [];
        foreach ($mergedRows as $row) {
            $code = $row['产品编号'];
            if ($code === '') {
                continue;
            }
            $m = $row['月份'];
            if ($m < 1 || $m > 12) {
                continue;
            }
            if (!isset($products[$code])) {
                $products[$code] = [
                    '产品编号' => $code,
                    '产品名称' => $row['产品名称'],
                    '产品类别' => $row['产品类别'],
                    '客户编号' => $row['客户编号'],
                    '客户名称' => $row['客户名称'],
                    'monthly' => array_fill(1, 12, 0),
                ];
            }
            $products[$code]['monthly'][$m] += $row['总产量'];
        }

        // 6、组装 summary 数组
        $summary = [];
        foreach ($products as $product) {
            $totalAll = 0;
            $months = [];
            for ($m = 1; $m <= 12; $m++) {
                $value = $product['monthly'][$m];
                $totalAll += $value;
                $months[str_pad($m, 2, '0', STR_PAD_LEFT) . '月'] = $value > 0 ? round($value, 2) : '';
            }
            $summary[] = array_merge([
                '产品编号' => $product['产品编号'],
                '产品名称' => $product['产品名称'],
                '产品类别' => $product['产品类别'],
                '每月合计' => round($totalAll, 2),
            ], $months, [
                '客户编号' => $product['客户编号'],
                '客户名称' => $product['客户名称'],
            ]);
        }
        usort($summary, function ($a, $b) {
            return strcmp($a['产品编号'], $b['产品编号']);
        });

        // 7、summary 合计行:小盒合计、条盒合计
        $totalRows = [];
        foreach (['小盒', '条盒'] as $category) {
            $exists = false;
            $total = 0;
            $sums = array_fill(1, 12, 0);
            foreach ($summary as $row) {
                if ($row['产品类别'] !== $category) {
                    continue;
                }
                $exists = true;
                $total += (float)$row['每月合计'];
                for ($m = 1; $m <= 12; $m++) {
                    $mk = str_pad($m, 2, '0', STR_PAD_LEFT) . '月';
                    $sums[$m] += $row[$mk] === '' ? 0 : (float)$row[$mk];
                }
            }
            if (!$exists) continue;
            $totalRow = [
                '产品编号' => $category . '合计',
                '产品名称' => '',
                '产品类别' => '',
                '每月合计' => round($total, 2),
                '合计类型' => true,
                '客户编号' => '',
                '客户名称' => '',
            ];
            for ($m = 1; $m <= 12; $m++) {
                $totalRow[str_pad($m, 2, '0', STR_PAD_LEFT) . '月'] = round($sums[$m], 2);
            }
            $totalRows[] = $totalRow;
        }
        $summary = array_merge($summary, $totalRows);

        // 8、detail:按 工单编号+年月 聚合
        $detailMap = [];
        foreach ($mergedRows as $row) {
            $dKey = $row['工单编号'] . '|' . $row['年月'];
            if (!isset($detailMap[$dKey])) {
                $detailMap[$dKey] = [
                    '年月' => $row['年月'],
                    '工单编号' => $row['工单编号'],
                    '产品编号' => $row['产品编号'],
                    '产品名称' => $row['产品名称'],
                    '产品类别' => $row['产品类别'],
                    '总产量' => 0,
                    '客户编号' => $row['客户编号'],
                    '客户名称' => $row['客户名称'],
                ];
            }
            $detailMap[$dKey]['总产量'] += $row['总产量'];
        }
        $detail = array_values($detailMap);
        usort($detail, function ($a, $b) {
            $c = strcmp($a['年月'], $b['年月']);
            if ($c !== 0) return $c;
            return strcmp($a['工单编号'], $b['工单编号']);
        });
        foreach ($detail as &$d) {
            $d['总产量'] = round($d['总产量'], 2);
        }
        unset($d);

        $this->success('成功', ['summary' => $summary, 'detail' => $detail]);
    }

    /**
     * 工单_基本资料 按 Gd_gdbh 去重取一条
     * 取第一个有客户代号且有成品编号的行;都没有时取第一条非空行
     */
    private function YearSgyjOrderMap(array $gdbhs)
    {
        if (empty($gdbhs)) {
            return [];
        }
        $rows = \db('工单_基本资料')
            ->field([
                'UPPER(RTRIM(Gd_gdbh)) AS 工单编号',
                'RTRIM(Gd_cpdh) AS 产品编号',
                'RTRIM(成品名称) AS 产品名称',
                'RTRIM(Gd_客户代号) AS 客户编号',
                'RTRIM(Gd_客户名称) AS 客户名称',
            ])
            ->whereIn('Gd_gdbh', $gdbhs)
            ->select();

        $map = [];
        foreach ($rows as $row) {
            $gdbh = trim((string)$row['工单编号']);
            if ($gdbh === '') continue;
            $code = trim((string)$row['产品编号']);
            $name = trim((string)$row['产品名称']);
            $khdh = trim((string)$row['客户编号']);
            $khmc = trim((string)$row['客户名称']);
            $category = $name !== '' ? (strpos($name, '小盒') !== false ? '小盒' : '条盒') : '';

            if (!isset($map[$gdbh])) {
                // 第一次见到:如果有客户代号且有成品编号,直接用;否则先占坑
                $map[$gdbh] = [
                    '产品编号' => $code,
                    '产品名称' => $name,
                    '客户编号' => $khdh,
                    '客户名称' => $khmc,
                    '产品类别' => $category,
                ];
            } else {
                // 已存在:如果现有记录缺客户,新记录有客户则覆盖;同理产品编号
                if ($map[$gdbh]['客户编号'] === '' && $khdh !== '') {
                    $map[$gdbh]['客户编号'] = $khdh;
                    $map[$gdbh]['客户名称'] = $khmc;
                }
                if ($map[$gdbh]['产品编号'] === '' && $code !== '') {
                    $map[$gdbh]['产品编号'] = $code;
                    $map[$gdbh]['产品名称'] = $name;
                    $map[$gdbh]['产品类别'] = $category;
                }
            }
        }
        return $map;
    }

    /**
     * 设备周数据统计 左侧菜单(车间 -> 机台)
     * 参考 facility/getTab,但去掉日期子节点
     */
    public function EqwdtLeft()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $department = \db('设备_基本资料')
            ->distinct(true)
            ->where('使用部门', '<>', '研发中心')
            ->where('设备编组', '<>', '')
            ->where('sys_sbID', '<>', '')
            ->order('设备编组')
            ->column('rtrim(使用部门) as 使用部门');

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

        $data = [];
        foreach ($department as $dept) {
            $machines = \db('设备_基本资料')
                ->where('使用部门', $dept)
                ->where('sys_sbID', '<>', '')
                ->field('rtrim(设备编号) as 设备编号, rtrim(设备名称) as 设备名称')
                ->order('设备编号')
                ->select();

            $machineNodes = [];
            foreach ($machines as $m) {
                $code = trim((string)$m['设备编号']);
                if ($code === '') continue;
                $machineNodes[] = [
                    'label' => $code . '-->' . trim((string)$m['设备名称']),
                    'key' => 'M' . $code,
                    'machine' => $code,
                ];
            }
            $data[] = [
                'label' => $dept,
                'key' => 'D' . $dept,
                'children' => $machineNodes,
            ];
        }

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

    /**
     * 设备周数据统计 右侧汇总 + 明细
     * 固定取数据库中最新日期往前 6 天(共 7 天)
     * 参数: machine (可选机台编号)
     */
    public function EqwdtList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        $machine = trim((string)(isset($param['machine']) ? $param['machine'] : ''));

        // 以数据库最新日期为基准,取最近 7 天
        $maxDate = \db('设备_产量计酬')->max('sczl_rq');
        if (!$maxDate) {
            $this->success('成功', ['summary' => [], 'detail' => [], 'range' => ['start' => '', 'end' => '']]);
        }
        $maxTs = strtotime($maxDate);
        $end = date('Y-m-d', $maxTs);
        $start = date('Y-m-d', $maxTs - 6 * 86400);
        $startTime = $start . ' 00:00:00';
        $endTime = date('Y-m-d', $maxTs + 86400) . ' 00:00:00';

        // 1、汇总:按机台编号聚合
        $summaryQuery = \db('设备_产量计酬')
            ->alias('s')
            ->join('设备_基本资料 b', 'RTRIM(b.设备编号) = RTRIM(s.sczl_jtbh)', 'LEFT')
            ->field([
                'RTRIM(s.sczl_jtbh) AS 机台编号',
                'RTRIM(b.设备名称) AS 设备名称',
                'RTRIM(b.使用部门) AS 使用部门',
                'RTRIM(b.生产工序) AS 生产工序',
                'RTRIM(b.设备编组) AS 设备编组',
                'SUM(s.sczl_cl) AS 总产量',
                'SUM(s.sczl_设备运行工时) AS 设备运行工时',
                'SUM(s.sczl_装版工时) AS 装版工时',
                'SUM(s.sczl_装版总工时) AS 装版总工时',
                'SUM(s.sczl_保养工时) AS 保养工时',
                'SUM(s.sczl_打样工时) AS 打样工时',
                'SUM(s.sczl_打样总工时) AS 打样总工时',
                'SUM(s.sczl_异常停机工时) AS 异常停机工时',
                'SUM(s.sczl_zcfp) AS 制程废品',
                'SUM(s.sczl_zccp) AS 制程次品',
                'SUM(s.sczl_前工序废) AS 前工序废',
                'SUM(s.sczl_来料少数) AS 来料少数',
                'SUM(s.sczl_异常工时1) AS 异常补时',
            ])
            ->where('s.sczl_rq', '>=', $startTime)
            ->where('s.sczl_rq', '<', $endTime);

        if ($machine !== '') {
            $summaryQuery->where('s.sczl_jtbh', $machine);
        }

        $summary = $summaryQuery
            ->group('机台编号, 设备名称, 使用部门, 生产工序, 设备编组')
            ->order('机台编号')
            ->select();

        // 2、明细:工单级(按 机台编号 + 工单编号 + 日期 聚合)
        $detailQuery = \db('设备_产量计酬')
            ->alias('s')
            ->join('设备_基本资料 b', 'RTRIM(b.设备编号) = RTRIM(s.sczl_jtbh)', 'LEFT')
            ->join('工单_基本资料 g', 'RTRIM(g.Gd_gdbh) = RTRIM(s.sczl_gdbh)', 'LEFT')
            ->field([
                'DATE_FORMAT(s.sczl_rq, \'%Y-%m-%d\') AS 日期',
                'RTRIM(s.sczl_jtbh) AS 机台编号',
                'RTRIM(b.设备名称) AS 设备名称',
                'RTRIM(s.sczl_gdbh) AS 工单编号',
                'CONCAT(FLOOR(s.sczl_yjno), \'-\', RTRIM(s.sczl_gxmc)) AS 印件及工序',
                'RTRIM(g.Gd_cpdh) AS 产品编号',
                'RTRIM(g.成品名称) AS 成品名称',
                'RTRIM(g.Gd_客户名称) AS 客户名称',
                'SUM(s.sczl_cl) AS 产量',
                'SUM(s.sczl_设备运行工时) AS 设备运行工时',
                'SUM(s.sczl_装版工时) AS 装版工时',
                'SUM(s.sczl_保养工时) AS 保养工时',
                'SUM(s.sczl_打样工时) AS 打样工时',
                'SUM(s.sczl_异常停机工时) AS 异常停机工时',
                'SUM(s.sczl_zcfp) AS 制程废品',
                'SUM(s.sczl_zccp) AS 制程次品',
            ])
            ->where('s.sczl_rq', '>=', $startTime)
            ->where('s.sczl_rq', '<', $endTime);

        if ($machine !== '') {
            $detailQuery->where('s.sczl_jtbh', $machine);
        }

        $detail = $detailQuery
            ->group('日期, 机台编号, 设备名称, 工单编号, 印件及工序, 产品编号, 成品名称, 客户名称')
            ->order('日期, 机台编号, 工单编号')
            ->select();

        // 数值格式化(0 显示为空字符串)
        foreach ($summary as &$row) {
            foreach (['总产量', '设备运行工时', '装版工时', '装版总工时', '保养工时', '打样工时', '打样总工时', '异常停机工时', '制程废品', '制程次品', '前工序废', '来料少数', '异常补时'] as $k) {
                $v = (float)$row[$k];
                $row[$k] = $v === 0 ? '' : round($v, 2);
            }
        }
        unset($row);

        foreach ($detail as &$row) {
            foreach (['产量', '设备运行工时', '装版工时', '保养工时', '打样工时', '异常停机工时', '制程废品', '制程次品'] as $k) {
                $v = (float)$row[$k];
                $row[$k] = $v === 0 ? '' : round($v, 2);
            }
        }
        unset($row);

        $this->success('成功', [
            'summary' => $summary,
            'detail' => $detail,
            'range' => ['start' => $start, 'end' => $end],
        ]);
    }

    /**
     * 完工工单生产周期统计 左侧菜单(年月 → 客户)
     * 参考 order_super_loss/getTab
     */
    public function CopsTab()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $sql = "SELECT
            DATE_FORMAT(a.接单日期, '%Y%m') AS date,
            COUNT(DISTINCT a.Gd_gdbh) AS cnt,
            RTRIM(a.Gd_客户代号) AS code,
            RTRIM(a.Gd_客户名称) AS name
        FROM `工单_基本资料` a
        WHERE a.接单日期 >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
          AND a.接单日期 IS NOT NULL
          AND (a.工单完工日期 IS NOT NULL OR EXISTS (SELECT 1 FROM `成品入仓` c WHERE c.jjcp_gdbh = a.Gd_gdbh))
        GROUP BY date, code, name
        ORDER BY date DESC, code ASC";
        $rows = Db::query($sql);

        // 组装为 { date-total: [{code, name, total}] } 结构
        $organized = [];
        foreach ($rows as $r) {
            $date = $r['date'];
            if (!isset($organized[$date])) {
                $organized[$date] = ['total_count' => 0, 'items' => []];
            }
            $organized[$date]['items'][] = [
                'code' => $r['code'],
                'name' => $r['name'],
                'total' => intval($r['cnt']),
            ];
            $organized[$date]['total_count'] += intval($r['cnt']);
        }

        $data = [];
        foreach ($organized as $date => $info) {
            $key = $date . '-' . $info['total_count'];
            foreach ($info['items'] as $it) {
                $data[$key][] = $it;
            }
        }

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

    /**
     * 完工工单生产周期统计 右侧列表(纯 SQL 避免 TP5 Query 方法缺失)
     * 参数: date (年月 YYYYMM), code (客户代号), search (关键字搜索),
     *       page (页码, 默认1), limit (每页条数, 默认50), export (1=导出全量, 不分页)
     */
    public function CopsList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        $date = trim((string)(isset($param['date']) ? $param['date'] : ''));
        $code = trim((string)(isset($param['code']) ? $param['code'] : ''));
        $search = trim((string)(isset($param['search']) ? $param['search'] : ''));

        $page = empty($param['page']) ? 1 : max(1, intval($param['page']));
        $limit = empty($param['limit']) ? 50 : max(1, intval($param['limit']));
        $isExport = !empty($param['export']);
        // 导出全量时限制上限,避免一次性拉爆内存
        if ($isExport) {
            $page = 1;
            $limit = 50000;
        }
        $offset = ($page - 1) * $limit;

        $where = [];
        $where[] = "a.接单日期 >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)";
        $where[] = "a.接单日期 IS NOT NULL";
        $where[] = "(a.工单完工日期 IS NOT NULL AND a.工单完工日期 <> '1900-01-01 00:00:00') OR EXISTS (SELECT 1 FROM `成品入仓` c2 WHERE c2.jjcp_gdbh = a.Gd_gdbh)";

        if ($date !== '') {
            $where[] = "DATE_FORMAT(a.接单日期, '%Y%m') = " . intval($date);
        }
        if ($code !== '') {
            $escaped = str_replace("'", "''", $code);
            $where[] = "UPPER(RTRIM(a.Gd_客户代号)) = UPPER(RTRIM('{$escaped}'))";
        }
        if ($search !== '') {
            $escaped = str_replace("'", "''", $search);
            $where[] = "(a.Gd_gdbh LIKE '%{$escaped}%' OR a.成品名称 LIKE '%{$escaped}%' OR a.Gd_cpdh LIKE '%{$escaped}%')";
        }

        $whereSql = implode(' AND ', $where);

        // 公共 FROM 段(与 a 表 1:1 关联,可用于统计总数)
        $fromSql = "FROM `工单_基本资料` a
        LEFT JOIN `工单_印件资料` b
            ON a.Gd_gdbh = b.Yj_Gdbh AND CAST(a.行号 AS UNSIGNED) = b.yj_Yjno
        LEFT JOIN (
            SELECT jjcp_gdbh, jjcp_yjno,
                SUM(jjcp_sl) AS 入仓数量,
                MIN(jjcp_sj) AS 检验日期,
                MAX(jjcp_sj) AS 入仓完工日期
            FROM `成品入仓`
            GROUP BY jjcp_gdbh, jjcp_yjno
        ) c
            ON a.Gd_gdbh = c.jjcp_gdbh AND CAST(a.行号 AS UNSIGNED) = c.jjcp_yjno
        WHERE {$whereSql}";

        // 总数
        $totalRow = Db::query("SELECT COUNT(*) AS total {$fromSql}");
        $total = isset($totalRow[0]['total']) ? (int)$totalRow[0]['total'] : 0;

        $sql = "SELECT
            RTRIM(a.Gd_生产分类) AS 生产分类,
            RTRIM(a.Gd_gdbh) AS 工单编号,
            RTRIM(b.yj_Yjdh) AS 印件代号,
            RTRIM(a.Gd_cpdh) AS 产品代号,
            RTRIM(a.成品名称) AS 产品名称,
            a.订单数量,
            a.实际投料,
            RTRIM(a.计量单位) AS 计量单位,
            IFNULL(c.入仓数量, 0) AS 入仓数量,
            DATEDIFF(COALESCE(DATE(c.检验日期), DATE(a.工单完工日期)), DATE(a.接单日期)) AS 工单周期,
            DATE(COALESCE(c.检验日期, a.工单完工日期)) AS 检验日期,
            TIME(COALESCE(c.检验日期, a.工单完工日期)) AS 检验时间,
            DATE(a.接单日期) AS 接单日期,
            DATE(COALESCE(c.入仓完工日期, NULLIF(a.入仓日期, '1900-01-01 00:00:00'), a.工单完工日期)) AS 入仓完工日期,
            RTRIM(a.Gd_客户代号) AS 客户代号,
            RTRIM(a.Gd_客户名称) AS 客户名称,
            RTRIM(a.销售订单号) AS 销售订单号
        {$fromSql}
        ORDER BY COALESCE(c.检验日期, a.工单完工日期) DESC, a.Gd_gdbh DESC
        LIMIT {$offset}, {$limit}";

        $list = Db::query($sql);

        foreach ($list as &$row) {
            $row['生产公分'] = '';
            $row['工单公休'] = '';
            $row['计划开工日期'] = '';
            $row['实际开工日期'] = '';
            $row['目标合格率'] = '';

            foreach (['订单数量', '实际投料'] as $k) {
                $v = (float)$row[$k];
                $row[$k] = $v === 0 ? '' : number_format($v, 2);
            }
            $row['入仓数量'] = (int)$row['入仓数量'] === 0 ? '' : (int)$row['入仓数量'];
            $row['工单周期'] = $row['工单周期'] === null ? '' : (int)$row['工单周期'];

            $sl = (int)$row['入仓数量'];
            $tl = (float)$row['实际投料'];
            if ($sl > 0 && $tl > 0) {
                $row['实际合格率'] = number_format($sl / ($tl * 10000) * 100, 2) . '%';
            } else {
                $row['实际合格率'] = '';
            }

            foreach (['检验日期', '检验时间', '接单日期', '入仓完工日期'] as $k) {
                if ($row[$k] === null || $row[$k] === '1900-01-01' || $row[$k] === '1900-01-01 00:00:00') {
                    $row[$k] = '';
                }
            }
        }
        unset($row);

        $this->success('成功', [
            'list' => $list,
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
        ]);
    }
}