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('设备_产量计酬')
            ->field([
                'sczl_jtbh as 机台编号',
                'sczl_bzdh as 班组',
                'sczl_rq',
                'sczl_cl',
                'sczl_ls',
                'sczl_rq',
                'sczl_装版总工时 as 装板实际工时',
                'sczl_装版工时 as 装板补产工时',
                'sczl_保养工时 as 保养工时',
                'sczl_打样总工时 as 打样总工时',
                'sczl_打样工时 as 打样补产工时',
                'sczl_异常停机工时 as 异常总工时',
                'sczl_异常工时1 as 异常补时',
                'sczl_设备运行工时 as 运行工时'
            ])
            ->whereIn('sczl_jtbh', $devices)
            ->where('sczl_rq', 'like', $ym . '%')
            ->select();

        $小时产能 = 50000;

        // 分组汇总：按 机台编号 + 班组
        $resultList = [];
        foreach ($records as $row) {
            $jtbh = $row['机台编号'];
            $bz = $row['班组'] ?: '未分组';
            $key = $jtbh . '|' . $bz;

            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,
                ];
            }

            // 累加字段
            $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['目标产量'] = round($有效工时 * $小时产能, 2);
                $row['负荷产量'] = $row['目标产量'];

                // 计算达成率 & 利用率
                $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.平均车速'
            ])
            ->join('工单_印件资料 b', 'a.sczl_gdbh = b.Yj_Gdbh AND a.sczl_yjno = b.Yj_YjNo', 'LEFT') // 添加印件号关联条件
            ->join('设备_基本资料 c', 'a.sczl_jtbh = c.设备编号')
            ->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);
    }

}