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

<?php

namespace app\api\controller;
use app\common\controller\Api;
use Monolog\Handler\IFTTTHandler;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use think\Db;
use think\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;
class  Achievementatestatistics extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /**
     * 1.1达成率统计->左侧菜单
     */
    public function Leftmenu(){
        // 获取所有部门（排除特定）
        $departments = Db::name('设备_基本资料')
            ->field('使用部门')
            ->whereNotIn('使用部门', ['打样室', '智能车间'])
            ->group('使用部门')
            ->select();
        // 部门名数组
        $deptList = array_map(function($item) {
            return trim($item['使用部门']);
        }, $departments);
        // 获取产量表日期
        $rawData = Db::name('设备_产量计酬')
            ->field("DATE(sys_rq) as rq")
            ->whereRaw("YEAR(sys_rq) >= 2000")
            ->order("rq desc")
            ->select();
        $workStats = [];
        $addedMonths = []; // 记录已处理年月
        foreach ($rawData as $item) {
            $rq = $item['rq'];
            $year = date('Y', strtotime($rq));
            $yearMonth = date('Ym', strtotime($rq));

            // 如果该年月还没加过，才添加
            if (!isset($addedMonths[$year][$yearMonth])) {
                $workStats[$year][$yearMonth] = $deptList;
                $addedMonths[$year][$yearMonth] = true;
            }
        }
        $result = [
            '排产计划达成率统计' => $departments,
            '设备工时达成率统计' => $workStats
        ];
        $this->success('成功',$result);
    }


    public function Machine_List()
    {
        if (!$this->request->isGet()) {
            $this->error('请求方式错误');
        }

        $param = $this->request->param();
        if (empty($param['Machine'])) {
            $this->error('缺少参数 Machine');
        }

        // 日期映射：近 7 天
        $dateMap = [];
        $recentDates = [];
        for ($i = 0; $i < 7; $i++) {
            $date = date('Y-m-d', strtotime("-{$i} days"));
            $label = date('m月d日', strtotime($date));
            $dateMap[$date] = $label;
            $recentDates[] = $date;
        }

        $devices = Db::name('设备_基本资料')
            ->field('设备编号, 设备名称')
            ->whereLike('使用部门', '%' . $param['Machine'] . '%')
            ->select();

        $results = [];

        foreach ($devices as $device) {
            $jtbh = $device['设备编号'];
            $jtname = trim($device['设备名称']);

            // 获取原始产量数据
            $records = Db::name('设备_产量计酬')
                ->field([
                    'sczl_bzdh as 班组',
                    'sczl_cl',
                    'sczl_Pgcl',
                    'sczl_rq as 日期',
                    'sczl_gdbh',
                    'sczl_yjno',
                    'sczl_gxh'
                ])
                ->where('sczl_jtbh', $jtbh)
                ->where('sczl_rq', '>=', date('Y-m-d 00:00:00', strtotime('-6 days')))
                ->where('sczl_rq', '<=', date('Y-m-d 23:59:59'))
                ->select();

            $stat = [];
            $summary = [];

            foreach ($records as $row) {
                $bz = $row['班组'] ?: '未分组';
                $rq = date('Y-m-d', strtotime($row['日期']));
                $cl = floatval($row['sczl_cl']);
                $pgcl = floatval($row['sczl_Pgcl']);
                $actual = ($cl == 0) ? 0 : (($pgcl > 0) ? $cl * $pgcl : $cl);

                // 查询排产产量
                $plan = Db::name('工单_工艺资料')
                    ->where('Gy0_gdbh', $row['sczl_gdbh'])
                    ->where('Gy0_yjno', $row['sczl_yjno'])
                    ->where('Gy0_gxh', $row['sczl_gxh'])
                    ->value('Gy0_计划接货数');
                $plan = floatval($plan);

                // 班组维度统计
                if (!isset($stat[$bz][$rq])) {
                    $stat[$bz][$rq] = ['实际产量' => 0, '排产产量' => 0];
                }
                $stat[$bz][$rq]['实际产量'] += $actual;
                $stat[$bz][$rq]['排产产量'] += $plan;

                // 合计维度统计
                if (!isset($summary[$rq])) {
                    $summary[$rq] = ['实际产量' => 0, '排产产量' => 0];
                }
                $summary[$rq]['实际产量'] += $actual;
                $summary[$rq]['排产产量'] += $plan;
            }

            // 输出每个班组
            foreach ($stat as $bz => $dateList) {
                $item = [
                    '机台编号' => $jtbh,
                    '机台名称' => $jtname,
                    '班组' => $bz,
                    '实际总产量' => 0,
                    '排产总产量' => 0,
                    '近7天综合达成率' => '0%'
                ];

                $i = 1;
                foreach ($dateMap as $date => $label) {
                    $actual = isset($dateList[$date]) ? round($dateList[$date]['实际产量'], 2) : 0;
                    $plan = isset($dateList[$date]) ? round($dateList[$date]['排产产量'], 2) : 0;

                    $rate = ($plan > 0) ? round($actual / $plan * 100, 2) . '%' : '0%';

                    $item[$label] = $rate;
                    $item["实际产量{$i}"] = $actual;
                    $item["排产产量{$i}"] = $plan;

                    $item['实际总产量'] += $actual;
                    $item['排产总产量'] += $plan;
                    $i++;
                }

                $item['近7天综合达成率'] = ($item['排产总产量'] > 0)
                    ? round($item['实际总产量'] / $item['排产总产量'] * 100, 2) . '%'
                    : '0%';

                $results[] = $item;
            }

            // 合计行
            $item = [
                '机台编号' => $jtbh,
                '机台名称' => '合计',
                '班组' => '',
                '实际总产量' => 0,
                '排产总产量' => 0,
                '近7天综合达成率' => '0%'
            ];

            $i = 1;
            foreach ($dateMap as $date => $label) {
                $actual = isset($summary[$date]) ? round($summary[$date]['实际产量'], 2) : 0;
                $plan = isset($summary[$date]) ? round($summary[$date]['排产产量'], 2) : 0;

                // 重新计算达成率，不使用之前累加的百分比
                $rate = ($plan > 0) ? round($actual / $plan * 100, 2) . '%' : '0%';

                $item[$label] = $rate;
                $item["实际产量{$i}"] = $actual;
                $item["排产产量{$i}"] = $plan;

                $item['实际总产量'] += $actual;
                $item['排产总产量'] += $plan;
                $i++;
            }

// 修正“近7天综合达成率”的计算
            $item['近7天综合达成率'] = ($item['排产总产量'] > 0)
                ? round($item['实际总产量'] / $item['排产总产量'] * 100, 2) . '%'
                : '0%';

            $results[] = $item;

        }

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

    /**
     * 1.3达成率统计->机台生产详情
     */
    public function Machine_Detail()
    {
        if (!$this->request->isGet()) {
            $this->error('请求方式错误');
        }
        $param = $this->request->param();
        if (empty($param['jtbh']) || empty($param['bz'])) {
//            $this->error('缺少参数：jtbh（机台编号）或 bz（班组）');
        }
        $jtbh = $param['jtbh'];
        $bz = $param['bz'];

        // 查询近 7 天产量记录
        $records = Db::name('设备_产量计酬')->alias('a')
            ->field('
            a.sczl_rq as 日期, 
            a.sczl_jtbh as 机台编号,
            a.sczl_bzdh as 班组,
            a.sczl_gdbh as 工单编号, 
            a.sczl_yjno as 印件号, 
            b.yj_yjmc as 印件名称, 
            a.sczl_gxh as 工序号, 
            a.sczl_gxmc as 工序名称, 
            a.sczl_cl as 实际产量,
            c.排单小时定额 as 排产标准产能,
            d.Gy0_计划接货数 as 排产产量,
            a.sczl_设备运行工时 as 上报运行工时,
            a.sczl_装版总工时 as 装版实际工时,
            a.sczl_保养工时 as 保养工时,
            a.sczl_打样总工时 as 打样工时,
            d.版距 as 版距,
            c.设备名称 as 机台名称
            ')
            ->join('工单_印件资料 b','a.sczl_gdbh = b.Yj_Gdbh')
            ->join('设备_基本资料 c','a.sczl_jtbh = c.设备编号')
            ->join('工单_工艺资料 d','a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh')
            ->where('a.sczl_jtbh', $jtbh)
            ->where('a.sczl_bzdh', $bz)
            ->where('a.sczl_rq', '>=', date('Y-m-d 00:00:00', strtotime('-6 days')))
            ->where('a.sczl_rq', '<=', date('Y-m-d 23:59:59'))
            ->order('a.sczl_rq desc')
            ->select();
        $details = [];
        foreach ($records as $row) {
            $rq = date('Y-m-d', strtotime($row['日期']));
            $details[] = [
                '日期' => $rq,
                '机台编号' => $row['机台编号'],
                '班组' => $row['班组'],
                '工单编号' => $row['工单编号'],
                '印件号' => $row['印件号'],
                '印件名称' => $row['印件名称'],
                '工序号' => $row['工序号'],
                '工序名称' => $row['工序名称'],
                '实际产量' => floatval($row['实际产量']),
                '排产标准产能' => $row['排产标准产能'],
                '排产产量' => floatval($row['排产产量']),
                '上报运行工时' => $row['上报运行工时'],
                '装版实际工时' => $row['装版实际工时'],
                '保养工时' => $row['保养工时'],
                '打样工时' => $row['打样工时'],
                '版距' => $row['版距'],
                '机台名称' => $row['机台名称'],
            ];
        }
        $this->success('明细获取成功', $details);
    }

    /**
     * 1.4达成率统计 -> 按月份汇总机台产量（按班组分组）
     */
    public function Machine_production_list()
    {
        if (!$this->request->isGet()) {
            $this->error('请求方式错误');
        }

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

        $machine = $param['Machine'];
        $rq = $param['rq'];
        $ym = substr($rq, 0, 4) . '-' . substr($rq, 4, 2);

        // 查询设备编号
        $devices = Db::name('设备_基本资料')
            ->where('使用部门', $machine)
            ->column('设备编号');
        $devices = array_map('trim', $devices);

        // 查询产量记录
        $records = Db::name('设备_产量计酬')
            ->alias('a')
            ->field([
                'a.sczl_jtbh as 机台编号',
                'a.sczl_bzdh as 班组',
                'a.sczl_rq',
                'a.sczl_cl',
                'a.sczl_ls',
                'a.sczl_rq',
                'a.sczl_装版总工时 as 装板实际工时',
                'a.sczl_装版工时 as 装板补产工时',
                'a.sczl_保养工时 as 保养工时',
                'a.sczl_打样总工时 as 打样总工时',
                'a.sczl_打样工时 as 打样补产工时',
                'a.sczl_异常停机工时 as 异常总工时',
                'a.sczl_异常工时1 as 异常补时',
                'a.sczl_设备运行工时 as 运行工时',
                'c.排单小时定额 as 小时产能',
                'c.平均车速'
            ])
            ->join('设备_基本资料 c', 'a.sczl_jtbh = c.设备编号')
            ->whereIn('a.sczl_jtbh', $devices)
            ->where('a.sczl_rq', 'like', $ym . '%')
            ->select();
        // 分组汇总：按 机台编号 + 班组
        $resultList = [];
        foreach ($records as $row) {
            $jtbh = $row['机台编号'];
            $bz = $row['班组'] ?: '未分组';
            $key = $jtbh . '|' . $bz;
            if ($row['运行工时'] >= 8) {
                $row['运行工时'] = $row['运行工时'] - 1;
            }elseif ($row['运行工时'] < 8 && $row['运行工时'] > 4) {
                $row['运行工时'] = $row['运行工时'] - 0.5;
            }
            if (strpos($row['机台编号'],'YJY') !== false) {
                $plate_hours = $row['装板补产工时'] * 1.7;
                $plate_mounting_hours = $row['装板实际工时'] * 1.5;
                if ($plate_hours < $plate_mounting_hours) {
                    $row['装板补产工时'] = $plate_hours;
                }elseif ($plate_hours > $plate_mounting_hours) {
                    $row['装板补产工时'] = $plate_mounting_hours;
                }elseif ($row['装板补产工时'] > $plate_mounting_hours) {
                    $row['装板补产工时'] = $row['装板补产工时'];
                }else{
                    $row['装板补产工时'] = $plate_hours;
                }
            }
            if (!isset($resultList[$key])) {
                $resultList[$key] = [
                    '机台编号' => $jtbh,
                    '班组' => $bz,
                    '实际产量' => 0,
                    '装板实际工时' => 0,
                    '装板补产工时' => 0,
                    '保养工时' => 0,
                    '打样总工时' => 0,
                    '打样补产工时' => 0,
                    '异常总工时' => 0,
                    '异常补时' => 0,
                    '运行工时' => 0,
                    'sczl_rq' => date('Ym', strtotime($row['sczl_rq'])),
                    // 新增字段
                    '目标产量' => 0,
                    '负荷产量' => 0,
                    '小时产能' => $row['小时产能'],
                    '平均车速' => $row['平均车速']
                ];
            }

            // 累加字段
            $resultList[$key]['实际产量'] += floatval($row['sczl_cl']);
            $resultList[$key]['装板实际工时'] += floatval($row['装板实际工时']);
            $resultList[$key]['装板补产工时'] += floatval($row['装板补产工时']);
            $resultList[$key]['保养工时'] += floatval($row['保养工时']);
            $resultList[$key]['打样总工时'] += floatval($row['打样总工时']);
            $resultList[$key]['打样补产工时'] += floatval($row['打样补产工时']);
            $resultList[$key]['异常总工时'] += floatval($row['异常总工时']);
            $resultList[$key]['异常补时'] += floatval($row['异常补时']);
            $resultList[$key]['运行工时'] += floatval($row['运行工时']);
        }
        // 汇总并计算目标/负荷产量及达成率
        $grouped = []; // [机台编号 => [班组数据...]]
        foreach ($resultList as $item) {
            $jtbh = $item['机台编号'];
            $grouped[$jtbh][] = $item;
        }

        $finalList = [];
        foreach ($grouped as $jtbh => $rows) {
            $sum = [
                '机台编号' => $jtbh,
                '班组' => '合计',
                '实际产量' => 0,
                '装板实际工时' => 0,
                '装板补产工时' => 0,
                '保养工时' => 0,
                '打样总工时' => 0,
                '打样补产工时' => 0,
                '异常总工时' => 0,
                '异常补时' => 0,
                '运行工时' => 0,
                '目标产量' => 0,
                '负荷产量' => 0,
                'sczl_rq' => $rows[0]['sczl_rq'] ?? '',
            ];
            foreach ($rows as &$row) {
                // 计算目标 & 负荷产量
                $有效工时 = max(0, $row['运行工时'] - $row['保养工时'] - $row['装板补产工时'] - $row['异常总工时'] - $row['打样补产工时']);
                $row['目标产量'] = round($有效工时 * $row['小时产能'], 2);
                $row['负荷产量'] = round($有效工时 * $row['平均车速'], 2);

                // 计算达成率 & 利用率
                $row['目标达成'] = ($row['目标产量'] > 0)
                    ? round($row['实际产量'] / $row['目标产量'] * 100, 2) . '%'
                    : '0%';

                $row['综合利用率'] = ($row['负荷产量'] > 0)
                    ? round($row['实际产量'] / $row['负荷产量'] * 100, 2) . '%'
                    : '0%';

                $finalList[] = $row;

                // 合计累加
                $sum['实际产量'] += $row['实际产量'];
                $sum['装板实际工时'] += $row['装板实际工时'];
                $sum['装板补产工时'] += $row['装板补产工时'];
                $sum['保养工时'] += $row['保养工时'];
                $sum['打样总工时'] += $row['打样总工时'];
                $sum['打样补产工时'] += $row['打样补产工时'];
                $sum['异常总工时'] += $row['异常总工时'];
                $sum['异常补时'] += $row['异常补时'];
                $sum['运行工时'] += $row['运行工时'];
                $sum['目标产量'] += $row['目标产量'];
                $sum['负荷产量'] += $row['负荷产量'];
            }

            // 合计达成率
            $sum['目标达成'] = ($sum['目标产量'] > 0)
                ? round($sum['实际产量'] / $sum['目标产量'] * 100, 2) . '%'
                : '0%';

            $sum['综合利用率'] = ($sum['负荷产量'] > 0)
                ? round($sum['实际产量'] / $sum['负荷产量'] * 100, 2) . '%'
                : '0%';

            $finalList[] = $sum;
        }

        $this->success('汇总成功', $finalList);
    }

    /**
     * 1.5达成率统计 -> 按日期+工单统计生产明细
     */
    public function Machine_production_details()
    {
        if (!$this->request->isGet()) {
            $this->error('请求方式错误');
        }

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

        if (empty($param['jtbh']) || empty($param['rq'])) {
            $this->error('缺少必要参数：jtbh（机台编号）、rq（年月）');
        }

        $jtbh = $param['jtbh'];
        $rq = $param['rq'];
        $ym = substr($rq, 0, 4) . '-' . substr($rq, 4, 2);

        // 查询该月份内该机台的每日工单产量明细
        $records = Db::name('设备_产量计酬')->alias('a')
            ->field([
                'a.sczl_jtbh as 机台编号',
                'a.sczl_rq as 日期',
                'a.sczl_bzdh as 班组',
                'a.sczl_gdbh as 工单编号',
                'b.yj_yjmc as 印件名称',
                'a.sczl_yjno as 印件号',
                'a.sczl_gxh as 工序号',
                'a.sczl_gxmc as 工序名称',
                'a.sczl_ls',
                'SUM(a.sczl_cl) as 实际产量',
                'SUM(a.sczl_装版总工时) as 装版实际工时',
                'SUM(a.sczl_装版工时) as 装版补产工时',
                'SUM(a.sczl_保养工时) as 保养工时',
                'SUM(a.sczl_打样总工时) as 打样总工时',
                'SUM(a.sczl_打样工时) as 打样补产工时',
                'SUM(a.sczl_异常停机工时) as 异常总工时',
                'SUM(a.sczl_异常工时1) as 异常补时',
                'SUM(a.sczl_设备运行工时) as 运行工时',
                'c.排单小时定额 as 小时产能',
                'c.平均车速',
                'd.工价系数 as 难度系数'
            ])
            ->join('工单_印件资料 b', 'a.sczl_gdbh = b.Yj_Gdbh AND a.sczl_yjno = b.Yj_YjNo', 'LEFT') // 添加印件号关联条件
            ->join('设备_基本资料 c', 'a.sczl_jtbh = c.设备编号')
            ->join('工单_工艺资料 d', 'a.sczl_gdbh = d.Gy0_gdbh AND a.sczl_yjno = d.Gy0_yjno AND a.sczl_gxh = d.Gy0_gxh', 'LEFT')
            ->where('a.sczl_jtbh', $jtbh)
            ->whereLike('a.sczl_rq', $ym . '%')
            ->group('a.sczl_rq, a.sczl_gdbh, a.sczl_yjno, a.sczl_gxh, a.sczl_jtbh ,a.sczl_bzdh') // 确保唯一性分组
            ->order('a.sczl_rq desc, a.sczl_bzdh')
            ->select();
        // 查询每个班组的运行工时
        $Operating_hours = \db('设备_产量计酬')
            ->field('sczl_rq as 日期,sczl_bzdh as 班组,sum(sczl_设备运行工时) as 设备运行工时')
            ->where('sczl_jtbh', $jtbh)
            ->whereLike('sczl_rq', $ym . '%')
            ->group('sczl_rq, sczl_bzdh')
            ->order('sczl_rq desc, sczl_bzdh')
            ->select();
        // 设备运行达成率设备运行工时计算规则：
        // 1.上报通电工时>8小时，设备运行工时=上报通电工时-1小时
        // 2.上报通电工时<8小时且上报通电工时>4小时，设备运行工时= 上报通电工时-0.5小时
        // 3.上报通电工时<4小时，设备运行工时 = 上报通电工时
        foreach ($Operating_hours as $k => $v) {
            if ($v['设备运行工时'] >= 8) {
                $Operating_hours[$k]['设备运行工时'] = $v['设备运行工时'] - 1;
            }elseif ($v['设备运行工时'] < 8 && $v['设备运行工时'] > 4) {
                $Operating_hours[$k]['设备运行工时'] = $v['设备运行工时'] - 0.5;
            }else{
                $Operating_hours[$k]['设备运行工时'] = $v['设备运行工时'];
            }
        }
        // 数据格式化与目标产量计算
        foreach ($records as $key => $row) {
            $records[$key]['日期'] = date('Y-m-d', strtotime($row['日期']));
            $records[$key]['印件工序'] = $row['印件号'] . '-' . $row['工序名称'];

            // 胶印设备换型工时（装版补产工时）计算规则：
            //1.按班次工单分开计算装版补产工时，基础计算规则：装版补产工时*1.7
            //2.当装版补产工时*1.7<实际装版工时*1.5时，按照装版补产工时*1.7计算
            //3.当装版补产工时*1.7>实际装版工时*1.5，按照实际装版工时*1.5计算
            //4.当装版补产工时>实际装版工时*1.5，按照装版补产工时计算
            if (strpos($row['机台编号'],'YJY') !== false) {
                $plate_hours = $row['装版补产工时'] * 1.7;
                $plate_mounting_hours = $row['装版实际工时'] * 1.5;
                if ($plate_hours < $plate_mounting_hours) {
                    $records[$key]['装版补产工时'] = $plate_hours;
                }elseif ($plate_hours > $plate_mounting_hours) {
                    $records[$key]['装版补产工时'] = $plate_mounting_hours;
                }elseif ($row['装版补产工时'] > $plate_mounting_hours) {
                    $records[$key]['装版补产工时'] = $row['装版补产工时'];
                }else{
                    $records[$key]['装版补产工时'] = $plate_hours;
                }
            }
            // 计算目标产量
            foreach ($Operating_hours as $k => $v) {
                if ($v['日期'] === $row['日期'] && $v['班组'] === $row['班组']) {
                    //重新赋值运行工时
                    $records[$key]['运行工时'] = $v['设备运行工时'];
                    //计算目标产量
                    $records[$key]['目标产量'] = round(
                        max(0, $v['设备运行工时'] - $row['保养工时'] - $row['装版补产工时'] - $row['异常补时'] - $row['打样补产工时']) * $row['小时产能'],
                        2
                    );

                    // 计算负荷产量
                    $records[$key]['负荷产量'] = round(
                        max(0, $v['设备运行工时'] - $row['保养工时'] - $row['异常补时'] - $row['打样补产工时'] ) * $row['平均车速'],
                        2
                    );

                    // 计算目标达成率
                    $records[$key]['目标达成'] = $records[$key]['目标产量'] > 0
                        ? round($row['实际产量'] / $records[$key]['目标产量'] * 100, 2) . '%'
                        : '0%';

                    // 计算综合利用率
                    $records[$key]['综合利用率'] = $records[$key]['负荷产量'] > 0
                        ? round($row['实际产量'] / $records[$key]['负荷产量'] * 100, 2) . '%'
                        : '0%';
                }
            }

        }

        $this->success('明细获取成功', $records);
    }
    /**
     * 1.6达成率统计->按时段导出Excel
     */
    public function Machine_date_excel()
    {
        if (!$this->request->isGet()) {
            $this->error('请求方式错误');
        }

        $param = $this->request->param();
        if (empty($param['start_rq']) || empty($param['end_rq']) || empty($param['bm'])) {
            $this->error('缺少参数:start_rq(开始日期)或 end_rq(结束日期)或 bm(部门)');
        }

        $start_rq = $param['start_rq'];
        $end_rq = $param['end_rq'];
        $bm = $param['bm'];

        // 1. 先查询设备_基本资料表，获取该部门下的所有设备编号（去重）
        $deviceIds = Db::name('设备_基本资料')
            ->where('使用部门', $bm)
            ->group('设备编号') // 去重
            ->column('设备编号');

        if (empty($deviceIds)) {
            $this->error('该部门下没有设备数据');
        }

        // 2. 查询设备_产量计酬表，筛选符合条件的记录
        $records = Db::name('设备_产量计酬')->alias('a')
            ->field([
                'a.sczl_jtbh as 机台编号',
                'a.sczl_rq as 日期',
                'a.sczl_bzdh as 班组',
                'a.sczl_gdbh as 工单编号',
                'b.yj_yjmc as 印件名称',
                'a.sczl_yjno as 印件号',
                'a.sczl_gxh as 工序号',
                'a.sczl_gxmc as 工序名称',
                'a.sczl_ls',
                'SUM(a.sczl_cl) as 实际产量',
                'sczl_装版总工时 as 装板实际工时',
                'sczl_装版工时 as 装板补产工时',
                'sczl_保养工时 as 保养工时',
                'sczl_打样总工时 as 打样总工时',
                'sczl_打样工时 as 打样补产工时',
                'sczl_异常停机工时 as 异常总工时',
                'sczl_异常工时1 as 异常补时',
                'sczl_设备运行工时 as 运行工时'
            ])
            ->join('工单_印件资料 b', 'a.sczl_gdbh = b.Yj_Gdbh')
            ->whereIn('a.sczl_jtbh', $deviceIds) // 使用设备编号列表筛选
            ->whereBetween('a.sczl_rq', [
                $start_rq . ' 00:00:00',
                $end_rq . ' 23:59:59'
            ])
            ->group('a.sczl_jtbh, a.sczl_rq, a.sczl_bzdh, a.sczl_gdbh') // 按设备、日期、班组、工单分组
            ->order('a.sczl_rq desc')
            ->select();

        // 格式化数据
        foreach ($records as &$row) {
            $row['日期'] = date('Y-m-d', strtotime($row['日期']));
            $row['印件工序'] = $row['印件号'] . '-' . $row['工序名称'];
        }

        $this->success('明细获取成功', $records);
    }




    /**
     * 01.产品年度投入产出率统计->左侧菜单
     */
//    public function left_Productyear() {
//        $list = \db('产品_基本资料')
//            ->field('客户编号,客户名称,Mod_rq')
//            ->group('客户编号,客户名称,Mod_rq')
//            ->where('客户编号','<>','')
//            ->order('客户编号')
//            ->select();
//
//
//        $data['翌星工单'] = [];
//        $data['MN工单'] = [];
//
//        foreach ($list as $key => $value) {
//            $value['客户编号'] = rtrim($value['客户编号']);
//            $value['客户名称'] = rtrim($value['客户名称']);
//            if (empty($value['客户名称'])) {
//                continue;
//            }
//
//            $name = $value['客户编号'] . '【' . $value['客户名称'] . '】';
//            $year = date('Y', strtotime($value['Mod_rq']));
//            $uniqueKey = $value['客户编号'] . '_' . $value['客户名称'] . '_' . $year;
//
//            if (isset($seenEntries[$uniqueKey])) {
//                continue; // 如果已经存在，跳过重复项
//            }
//            $seenEntries[$uniqueKey] = true;
//
//            if (strpos($value['客户编号'], 'J') !== false || strpos($value['客户编号'], 'Y') !== false) {
//                if (!isset($data['翌星工单'][$year])) {
//                    $data['翌星工单'][$year] = [];
//                }
//                array_push($data['翌星工单'][$year], $name);
//            } else {
//                if (!isset($data['MN工单'][$year])) {
//                    $data['MN工单'][$year] = [];
//                }
//                array_push($data['MN工单'][$year], $name);
//            }
//        }
//
//        // 按年份排序（从最新到最旧）
//        krsort($data['翌星工单']);
//        krsort($data['MN工单']);
//
//        // 对每个年份内的数据进行排序（可选）
//        foreach ($data['翌星工单'] as $year => &$items) {
//            sort($items);
//        }
//        foreach ($data['MN工单'] as $year => &$items) {
//            sort($items);
//        }
//        $this->success('成功', $data);
//    }
    public function left_Productyear() {
        $list = \db('产品_基本资料')
            ->field('客户编号,客户名称')
            ->group('客户编号,客户名称')
            ->where('客户编号','<>','')
            ->order('客户编号')
            ->select();

        $data['翌星工单'] = [];
        $data['MN工单'] = [];
        $seenEntries = []; // 添加这个变量来跟踪已处理的条目

        foreach ($list as $key => $value) {
            $value['客户编号'] = rtrim($value['客户编号']);
            $value['客户名称'] = rtrim($value['客户名称']);
            if (empty($value['客户名称'])) {
                continue;
            }

            // 获取工单信息，包含Sys_rq字段
            $productIng = \db('工单_基本资料')
                ->field('Sys_rq')
                ->where('Gd_cpdh|成品代号', 'LIKE', rtrim($value['客户编号']) . '%')
                ->order('Sys_rq DESC')
                ->find();

            // 如果没有找到相关工单，跳过
            if (!$productIng || empty($productIng['Sys_rq'])) {
                continue;
            }

            $name = $value['客户编号'] . '【' . $value['客户名称'] . '】';
            $year = date('Y', strtotime($productIng['Sys_rq']));
            $uniqueKey = $value['客户编号'] . '_' . $value['客户名称'] . '_' . $year;

            if (isset($seenEntries[$uniqueKey])) {
                continue; // 如果已经存在，跳过重复项
            }
            $seenEntries[$uniqueKey] = true;

            if (strpos($value['客户编号'], 'J') !== false || strpos($value['客户编号'], 'Y') !== false) {
                if (!isset($data['翌星工单'][$year])) {
                    $data['翌星工单'][$year] = [];
                }
                array_push($data['翌星工单'][$year], $name);
            } else {
                if (!isset($data['MN工单'][$year])) {
                    $data['MN工单'][$year] = [];
                }
                array_push($data['MN工单'][$year], $name);
            }
        }

        // 按年份排序（从最新到最旧）
        krsort($data['翌星工单']);
        krsort($data['MN工单']);

        // 对每个年份内的数据进行排序（可选）
        foreach ($data['翌星工单'] as $year => &$items) {
            sort($items);
        }
        foreach ($data['MN工单'] as $year => &$items) {
            sort($items);
        }

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

    /**
     * 01.产品年度投入产出率统计->数据汇总表
     */
    public function list_Productyear() {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        $rq = $param['rq'];

        $where = [];
        if (!empty($param['search'])){
            $where['产品代号|产品名称'] = ['like','%'.$param['search'].'%'];
        }
        if (!empty($param['khdh'])){
            $where['产品代号'] = ['like',$param['khdh'].'%'];
        }

        $qualityData = \db('工单_质量考核汇总')
            ->field('Gy0_gdbh, 客户代号, 入仓日期 as sys_rq, 实际投料, 入仓数量, 产品代号, 产品名称, 销售订单号, 订单数量,计量单位')
            ->where('YEAR(Sys_rq)', $rq)
            ->where($where)
            ->select();
        if (empty($qualityData)) {
            $this->success('未查询到数据', []);
        }

        // 第一步：按工单去重，每个工单只取一条记录
        $uniqueByGongdan = [];
        foreach ($qualityData as $data) {
            $gdbh = $data['Gy0_gdbh'];
            if (!isset($uniqueByGongdan[$gdbh])) {
                $uniqueByGongdan[$gdbh] = $data;
            }
        }

        // 第二步：按产品代号和月份分组并计算总和
        $productMap = [];
        foreach ($uniqueByGongdan as $gdbh => $data) {
            $productCode = $data['产品代号'];
            $sysRq = $data['sys_rq'];

            // 提取月份1-12 的月份数字
            $month = date('n', strtotime($sysRq));

            if (!isset($productMap[$productCode])) {
                $productMap[$productCode] = [
                    'sys_rq' => substr($data['sys_rq'], 0, 4),
                    '计量单位' => $data['计量单位'],
                    '成品编码' => $data['产品代号'],
                    '成品名称' => $data['产品名称'],
                    'months' => [],
                    '实际投料' => 0,
                    '入仓数量' => 0,
                    '工单列表' => [] // 记录包含的工单编号
                ];
            }

            if (!isset($productMap[$productCode]['months'][$month])) {
                $productMap[$productCode]['months'][$month] = [
                    '实际投料' => 0,
                    '入仓数量' => 0
                ];
            }

            //实际投料先乘以10000再累加
            $adjustedActual = round($data['实际投料'] * 10000);

            // 累加月度数据
            $productMap[$productCode]['months'][$month]['实际投料'] += $adjustedActual;
            $productMap[$productCode]['months'][$month]['入仓数量'] += $data['入仓数量'];

            // 累加年度总量（实际投料已经乘以10000）
            $productMap[$productCode]['实际投料'] += $adjustedActual;
            $productMap[$productCode]['入仓数量'] += $data['入仓数量'];

            // 记录工单编号
            $productMap[$productCode]['工单列表'][] = $data['Gy0_gdbh'];
        }

        //月份
        $monthNames = [
            1 => '1月', 2 => '2月', 3 => '3月', 4 => '4月',
            5 => '5月', 6 => '6月', 7 => '7月', 8 => '8月',
            9 => '9月', 10 => '10月', 11 => '11月', 12 => '12月'
        ];

        //合并数据并计算合格率
        $result = [];
        foreach ($productMap as $productCode => $productData) {
            $resultItem = [
                'sys_rq' => $productData['sys_rq'],
                '成品编码' => $productData['成品编码'],
                '成品名称' => $productData['成品名称'],
                '实际投料' => $productData['实际投料'],
                '入仓数量' => $productData['入仓数量'],
                '计量单位' => $productData['计量单位'],
                '工单数量' => count($productData['工单列表']),
                '工单列表' => implode(', ', $productData['工单列表'])
            ];

            // 初始化各月份数据
            foreach ($monthNames as $monthNum => $monthName) {
                $resultItem[$monthName] = '-';
            }

            // 处理该产品的月度数据
            if (isset($productData['months'])) {
                foreach ($productData['months'] as $month => $monthData) {
                    $adjustedActual = $monthData['实际投料'];
                    $delivery = $monthData['入仓数量'];

                    // 计算月度合格率
                    if ($adjustedActual > 0) {
                        $monthlyRate = round(($delivery / $adjustedActual) * 100, 2);
                        $resultItem[$monthNames[$month]] = $monthlyRate . '%';
                    } else if ($delivery > 0) {
                        $resultItem[$monthNames[$month]] = '100%';
                    } else {
                        $resultItem[$monthNames[$month]] = '0%';
                    }
                }
            }

            // 计算年度综合合格率
            if ($resultItem['实际投料'] > 0) {
                $overallRate = round(($resultItem['入仓数量'] / $resultItem['实际投料']) * 100, 2);
                $resultItem['综合合格率'] = $overallRate . '%';
            } else if ($resultItem['入仓数量'] > 0) {
                $resultItem['综合合格率'] = '100%';
            } else {
                $resultItem['综合合格率'] = '0%';
            }

            $result[] = $resultItem;
        }

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

    /**
     * 01.产品年度投入产出率统计->数据明细表
     */
    public function list_Productmonth() {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }

        $param = $this->request->param();
        $productCode = $param['product_code'] ?? '';
        $year = $param['year'] ?? date('Y');

        if (empty($productCode)) {
            $this->error('请提供成品编码');
        }

        // 1. 查询对应的工单数据
        $qualityData = \db('工单_质量考核汇总')
            ->field('Gy0_gdbh as 工单编号, 订单数量, 入仓日期, 实际投料, 入仓数量, 客户代号, 客户名称, 产品代号, 产品名称, 销售订单号')
            ->where('产品代号', $productCode)
            ->where('YEAR(入仓日期)', $year)
            ->order('入仓日期','desc')
            ->select();

        if (empty($qualityData)) {
            $this->success('该产品无质量考核数据', []);
        }

        // 2. 先按工单去重，每个工单只取一条记录
        $uniqueByGongdan = [];
        foreach ($qualityData as $data) {
            $gdbh = $data['工单编号'];
            if (!isset($uniqueByGongdan[$gdbh])) {
                $uniqueByGongdan[$gdbh] = $data;
            }
        }

        // 3. 按月份分组累计数据
        $monthlyData = [];
//        $monthNames = [
//            1 => '1月', 2 => '2月', 3 => '3月', 4 => '4月',
//            5 => '5月', 6 => '6月', 7 => '7月', 8 => '8月',
//            9 => '9月', 10 => '10月', 11 => '11月', 12 => '12月'
//        ];

        foreach ($uniqueByGongdan as $gdbh => $data) {
            $sysRq = $data['入仓日期'];
            $month = date('n', strtotime($sysRq)); // 1-12 的月份数字

            // 初始化月份数据
            if (!isset($monthlyData[$data['工单编号']])) {
                $monthlyData[$data['工单编号']] = [
                    '月份' => $month,
                    '订单数量' => 0,
                    '实际投料' => 0,
                    '入仓数量' => 0,
                    '入仓日期' => $data['入仓日期'],
                    '客户代号' => $data['客户代号'],
                    '客户名称' => $data['客户名称'],
                    '产品代号' => $data['产品代号'],
                    '产品名称' => $data['产品名称'],
                    '销售订单号' => $data['销售订单号']
                ];
            }

            $monthlyData[$data['工单编号']]['订单数量'] += round($data['订单数量'] * 10000);
            $monthlyData[$data['工单编号']]['实际投料'] += round($data['实际投料'] * 10000);
            $monthlyData[$data['工单编号']]['入仓数量'] += round($data['入仓数量']);

            // 记录工单编号
            $monthlyData[$data['工单编号']]['工单编号'] = $data['工单编号'];
        }
        // 4. 计算每个月的实际合格率并整理结果
        $detailList = [];
        foreach ($monthlyData as $month => $monthData) {
            // 计算实际合格率
            $actualRate = '-';
            if ($monthData['实际投料'] > 0) {
                $actualRate = round(($monthData['入仓数量'] / $monthData['实际投料']) * 100, 2) . '%';
            } else if ($monthData['入仓数量'] > 0) {
                $actualRate = '100%';
            } else {
                $actualRate = '0%';
            }

            $detailList[$month] = [
                '日期' => $monthData['月份'],
                '入仓日期' => substr($monthData['入仓日期'], 0, 10),
                '订单数量' => $monthData['订单数量'],
                '实际投料' => $monthData['实际投料'],
                '入仓数量' => $monthData['入仓数量'],
                '实际合格率' => $actualRate,
                '客户代号' => $monthData['客户代号'],
                '客户名称' => $monthData['客户名称'],
                '产品代号' => $monthData['产品代号'],
                '产品名称' => $monthData['产品名称'],
                '销售订单号' => $monthData['销售订单号'],
                '工单编号' => $monthData['工单编号']
            ];
        }

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

}