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

<?php

namespace app\api\controller;


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

/**
 * 决策支持
 */

class Decision extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];


    //月度产量统计菜单

    public function OutputSstatisticsMenu()
    {
        if ($this->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['设备运行工时']
                    - $value['保养工时']
                    - $value['打样总工时']
                    - $value['装版总工时']
                    - $value['异常停机工时'];
                $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)) {
//            $this->error('参数错误');
//        }
//
//        // 工序分类
////        $processOrder = [
////            1 => ['name' => '胶印', 'keys' => ['胶印', '上光']],
////            2 => ['name' => '卷凹', 'keys' => ['卷凹']],
////            3 => ['name' => '圆烫', 'keys' => ['圆烫']],
////            4 => ['name' => '圆切', 'keys' => ['圆切']],
////            5 => ['name' => '烫模', 'keys' => ['烫金', '模切', '凹凸']],
////            6 => ['name' => '丝印', 'keys' => ['丝印']],
////            7 => ['name' => '喷码', 'keys' => ['喷码']],
////            8 => ['name' => '单凹', 'keys' => ['单凹']],
////            9 => ['name' => '覆膜', 'keys' => ['覆膜']]
////        ];
//        $sist = ['胶印车间','凹丝印车间','印后车间','检验车间'];
//        $processOrder= \db('设备_基本资料')
//            ->whereNotNull('设备编组')
//            ->whereIn('使用部门',$sist)
//            ->group('设备编组')
//            ->column('rtrim(设备编组) as 设备编组');
//
//
//        // 获取存在数据的月份
//        $monthsWithData = \db('工单_质量考核汇总')
//            ->where('sys_ny', 'like', $param['year'] . '%')
//            ->column('distinct(sys_ny) as month');
//
//        // 初始化结果数组，按工序名称归类
//        $result = [];
//        foreach ($processOrder as $processId => $process) {
//            $result[substr($process, 5, 6)] = [
//                '01月' => '',
//                '02月' => '',
//                '03月' => '',
//                '04月' => '',
//                '05月' => '',
//                '06月' => '',
//                '07月' => '',
//                '08月' => '',
//                '09月' => '',
//                '10月' => '',
//                '11月' => '',
//                '12月' => '',
//                '平均值' => '0.00%'
//            ];
//        }
//
//        // 遍历每个月份
//        foreach ($monthsWithData as $month) {
//            foreach ($processOrder as $processId => $process) {
//                // 查询该月份该工序的合格率数据
//                $query = \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.设备编号')
////                    ->field('a.sys_ny,(1 - ((SUM(a.班组制程废品) + SUM(a.班组质检废品)) / SUM(a.班组产量))) * 100 as 合格率')
//                    ->field('a.sys_ny,SUM(a.班组制程废品)/a.联数 as 制程废品,SUM(a.班组质检废品)/a.联数 as 质检废品,SUM(a.班组产量)/a.联数 as 产量')
//                    ->where('e.设备编组',$process)
//                    ->where('d.jjcp_smb', 'like', '末%')
//                    ->where('a.sys_ny', $month)
//                    ->group('a.sys_ny,a.Gy0_gdbh,a.印件及工序,a.工序');
////                    ->group('a.sys_ny');
//
//                $res = $query->select();
//                $data = ['sys_ny'=>'','制程废品'=>0, '质检废品'=>0, '产量'=>0];
//                foreach ($res as $value) {
//                    $data['sys_ny'] = $value['sys_ny'];
//                    $data['制程废品'] += $value['制程废品'];
//                    $data['质检废品'] += $value['质检废品'];
//                    $data['产量'] += $value['产量'];
//                }
//                $records = [
//                    'sys_ny' => $data['sys_ny'],
//                    '合格率' => (1-(($data['制程废品']+$data['质检废品'])/$data['产量']))* 100
//                ];
////                halt($records);
//                if (!empty($records)) {
//                    $monthName = substr($month, 4, 2) . '月';
//                    $result[substr($process, 5, 6)][$monthName] = sprintf("%.2f%%", $records['合格率']);
//                }
//            }
//        }
//
//        // 计算年度平均值
//        foreach ($result as $processName => &$processData) {
//            $totalAnnual = 0;
//            $monthCount = 0;
//
//            foreach ($processData as $month => $rate) {
//                if ($month !== '平均值' && $rate !== '') {
//                    $totalAnnual += floatval(str_replace('%', '', $rate));
//                    $monthCount++;
//                }
//            }
//
//            if ($monthCount > 0) {
//                $processData['平均值'] = sprintf("%.2f%%", $totalAnnual / $monthCount);
//            }
//        }
//        unset($processData); // 关键修复：解除引用
//
//        // 转换为目标格式
//        $formattedData = [];
//        foreach ($result as $processName => $processData) {
//            // 现在processData是独立值
//            $formattedRow = array_merge(['工序类型' => $processName], $processData);
//            $formattedData[] = $formattedRow;
//        }
//        // 返回JSON响应
//       $this->success('成功',$formattedData);
//    }

    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. 汇总所有年份内数据，一次查出所有“工序-月份”数据，避免嵌套多次SQL
        $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']);
            }
        }

        // 6. 转换为前端需要的格式
        $formattedData = [];
        foreach ($result as $process => $data) {
            $row = array_merge(['工序类型' => $process], $data);
            $formattedData[] = $row;
        }

        // 7. 返回
        $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('插入数据失败');
        }
    }
}