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);
    }


    /**
     * 1.2达成率统计->机台列表
     */
    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 as 实际产量, 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();

            // 数据结构：班组 → 日期 → 实际产量 + 工单明细
            $bzData = [];

            foreach ($records as $row) {
                $bz = $row['班组'] ?: '未分组';
                $rq = date('Y-m-d', strtotime($row['日期']));
                if (!isset($bzData[$bz][$rq])) {
                    $bzData[$bz][$rq] = [
                        '实际产量' => 0,
                        '排产产量' => 0,
                        '工单明细' => []
                    ];
                }

                $bzData[$bz][$rq]['实际产量'] += floatval($row['实际产量']);

                // 收集唯一组合键用于查询计划数
                $key = $row['sczl_gdbh'] . '_' . $row['sczl_yjno'] . '_' . $row['sczl_gxh'];
                if (!in_array($key, $bzData[$bz][$rq]['工单明细'])) {
                    $bzData[$bz][$rq]['工单明细'][] = $key;
                }
            }

            // 查询排产产量（计划接货数）
            foreach ($bzData as $bz => &$dateList) {
                foreach ($dateList as $rq => &$info) {
                    $totalPlan = 0;
                    foreach ($info['工单明细'] as $key) {
                        list($gdbh, $yjno, $gxh) = explode('_', $key);
                        $plan = Db::name('工单_工艺资料')
                            ->where('Gy0_gdbh', $gdbh)
                            ->where('Gy0_yjno', $yjno)
                            ->where('Gy0_gxh', $gxh)
                            ->value('Gy0_计划接货数');

                        $totalPlan += floatval($plan);
                    }
                    $info['排产产量'] = $totalPlan;
                }
            }

            // 输出结构：每个机台 + 班组一条
            foreach ($bzData as $bz => $dateList) {
                $item = [
                    '机台编号' => $jtbh,
                    '机台名称' => $jtname,
                    '班组' => $bz,
                    '实际总产量' => 0,
                    '排产总产量' => 0
                ];

                $i = 1;
                foreach ($dateMap as $date => $label) {
                    $sl = isset($dateList[$date]) ? $dateList[$date]['实际产量'] : 0;
                    $jh = isset($dateList[$date]) ? $dateList[$date]['排产产量'] : 0;
                    $rate = $jh > 0 ? round($sl / $jh * 100, 2) . '%' : '0%';

                    $item[$label] = $rate;
                    $item["实际产量{$i}"] = $sl;
                    $item["排产产量{$i}"] = $jh;
                    $item['实际总产量'] += $sl;
                    $item['排产总产量'] += $jh;
                    $i++;
                }

                $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_装版总工时 as 装板实际工时',
                'sczl_装版工时 as 装板补产工时',
                'sczl_保养工时 as 保养工时',
                'sczl_打样总工时 as 打样总工时',
                'sczl_打样工时 as 打样补产工时',
                'sczl_异常停机工时 as 异常总工时',
                'sczl_异常工时1 as 异常补时'
            ])
            ->whereIn('sczl_jtbh', $devices)
            ->where('sczl_rq', 'like', $ym . '%')
            ->select();

        // 分组汇总：按“机台编号 + 班组”
        $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
                ];
            }
            //汇总逻辑
            $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['异常补时']);
        }
        $this->success('汇总成功', array_values($resultList));
    }

    /**
     * 1.5达成率统计->按日期机台生产详情
     */
    public function Machine_production_details()
    {
        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_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')
            ->where('a.sczl_jtbh', $jtbh)
            ->where('a.sczl_bzdh', $bz)
            ->whereBetween('a.sczl_rq', [
                date('Y-m-d 00:00:00', strtotime('-6 days')),
                date('Y-m-d 23:59:59')
            ])
            ->order('a.sczl_rq desc')
            ->select();

        foreach ($records as &$row) {
            $row['日期'] = date('Y-m-d', strtotime($row['日期']));
            $row['印件工序'] = $row['印件号'].'-'.$row['工序名称'];
//            $row['目标产量'] =  ($row['运行工时'] - $row['保养工时'] - $row['装板补产工时'] - $row['异常总工时']) * $row['小时产能'];
        }
        $this->success('明细获取成功', $records);
    }
}