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