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

<?php

namespace app\api\controller;

use app\common\controller\Api;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * 车间生产信息汇总
 */
class ProductionSummary extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /** @var ProductionInformationStatistics */
    protected $statisticsService;

    public function _initialize()
    {
        parent::_initialize();
        $this->statisticsService = new ProductionInformationStatistics();
    }

    /**
     * 导出车间生产信息汇总Excel
     * 按月份天数生成 sheet：全月汇总 + 1~N日，样式相同
     * @ApiMethod (GET)
     * @param string month 月份，格式 Y-m，如 2026-07；不传则取当前月
     */
    public function export()
    {
        $month = $this->request->param('month', date('Y-m'));
        $timestamp = strtotime($month . '-01');
        if ($timestamp === false) {
            $this->error('月份格式错误，请传 Y-m，如 2026-07');
        }

        $daysInMonth = (int)date('t', $timestamp);
        $monthStr = date('Y-m', $timestamp);
        $spreadsheet = new Spreadsheet();

        // 第一个 sheet：全月汇总
        $summarySheet = $spreadsheet->getActiveSheet();
        $summarySheet->setTitle('全月汇总');
        $this->fillSheetTemplate($summarySheet, $monthStr);

        // 按日生成 sheet：1 ~ 当月天数
        for ($day = 1; $day <= $daysInMonth; $day++) {
            $daySheet = $spreadsheet->createSheet();
            $daySheet->setTitle((string)$day);
            $dayDate = sprintf('%s-%02d', $monthStr, $day);
            $this->fillSheetTemplate($daySheet, $dayDate);
        }

        $spreadsheet->setActiveSheetIndex(0);

        $fileName = '车间生产信息汇总_' . date('Ym', $timestamp) . '_' . date('His');
        $folderPath = 'uploads/folder';
        if (!is_dir($folderPath)) {
            mkdir($folderPath, 0777, true);
        }
        $tempPath = $folderPath . '/' . $fileName . '.xlsx';

        $writer = new Xlsx($spreadsheet);
        $writer->save($tempPath);

        $protocol = isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? 'https' : 'http';
        $host = $_SERVER['HTTP_HOST'];
        $siteUrl = $protocol . '://' . $host . '/' . $tempPath;

        $this->success('成功', [
            'url'   => $tempPath,
            'month' => $monthStr,
            'days'  => $daysInMonth,
            'sheets'=> $daysInMonth + 1,
        ]);
    }

    /**
     * 填充单个 sheet 的统一样式模板及数据
     */
    protected function fillSheetTemplate($sheet, $date)
    {
        $this->buildHeader($sheet);
        $templateRows = $this->getTemplateRows();
        $lastRow = $this->buildLeftFixedData($sheet, $templateRows);

        $dataList = $this->statisticsService->getExportData($date);
        $startRow = 5;
        foreach ($templateRows as $i => $templateRow) {
            $matched = $this->matchExportRow($templateRow, $dataList);
            if ($matched) {
                $this->fillExportRowValues($sheet, $startRow + $i, $matched);
            }
        }

        $this->applyCommonStyle($sheet, $lastRow);
        $this->setColumnWidths($sheet);
    }

    /**
     * 将计算后的数据写入 Excel 行（H-AA列）
     */
    protected function fillExportRowValues($sheet, $rowNum, array $data)
    {
        $values = [
            'H'  => $data['理论速度'],
            'I'  => $data['计划生产时间'],
            'J'  => $data['计划保养时间'],
            'K'  => $data['计划早会交接班时间'],
            'L'  => $data['设备故障时间'],
            'M'  => $data['换型时间'],
            'N'  => $data['打样时间'],
            'O'  => $data['异常时间'],
            'P'  => $data['计划完成产量'],
            'Q'  => $data['实际产量'],
            'R'  => $data['不良品数量'],
            'S'  => $data['负荷时间'],
            'T'  => $data['实际运行时间'],
            'U'  => $data['生产计划达成率'],
            'V'  => $data['设备综合效率'],
            'W'  => $data['时间利用率'],
            'X'  => $data['速度利用率'],
            'Y'  => $data['质量合格率'],
            'AA' => $data['标准产量'],
        ];

        foreach ($values as $col => $value) {
            if ($value === '' || $value === null) {
                continue;
            }
            $sheet->setCellValue($col . $rowNum, $value);
        }
    }

    /**
     * 模板行与统计数据匹配
     */
    protected function matchExportRow(array $templateRow, array $dataList)
    {
        list($workshop, $group, $machineName,) = $templateRow;

        // 1. 机台名称精确匹配
        foreach ($dataList as $row) {
            if ($row['机台名称'] === $machineName) {
                return $row;
            }
        }

        // 2. 去「汇总」后缀匹配
        $normName = $this->normalizeExportName($machineName);
        foreach ($dataList as $row) {
            if ($this->normalizeExportName($row['机台名称']) === $normName) {
                return $row;
            }
        }

        // 3. 车间合计行：机台名称含「全部工序合计」且车间匹配
        if (strpos($machineName, '全部工序合计') !== false) {
            foreach ($dataList as $row) {
                if ($row['row_type'] !== 'workshop') {
                    continue;
                }
                if ($this->workshopMatches($workshop, $row['车间']) &&
                    strpos($row['机台名称'], '全部工序合计') !== false) {
                    return $row;
                }
            }
        }

        // 4. 机组汇总行
        if (strpos($machineName, '汇总') !== false && $group !== '全部机组' && $group !== '全公司') {
            $shortName = str_replace('汇总', '', $machineName);
            $shortName = str_replace('工序', '', $shortName);
            foreach ($dataList as $row) {
                if ($row['row_type'] !== 'group') {
                    continue;
                }
                if ($this->groupMatches($group, $row['机组']) &&
                    strpos($row['机台名称'], $shortName) !== false) {
                    return $row;
                }
            }
        }

        // 5. 班组汇总行（A班/B班/白班/夜班）
        foreach ($dataList as $row) {
            if ($row['row_type'] !== 'group_shift') {
                continue;
            }
            if (!$this->groupMatches($group, $row['机组'])) {
                continue;
            }
            if ($this->normalizeExportName($row['机台名称']) === $normName) {
                return $row;
            }
            if (strpos($machineName, $row['班组']) !== false &&
                $this->groupMatches($group, $row['机组'])) {
                return $row;
            }
        }

        return null;
    }

    protected function normalizeExportName($name)
    {
        $name = trim((string)$name);
        return preg_replace('/汇总$/u', '', $name);
    }

    protected function workshopMatches($templateWorkshop, $dataWorkshop)
    {
        $templateWorkshop = trim($templateWorkshop);
        $dataWorkshop = trim($dataWorkshop);
        if ($templateWorkshop === $dataWorkshop) {
            return true;
        }
        return strpos($dataWorkshop, $templateWorkshop) !== false
            || strpos($templateWorkshop, $dataWorkshop) !== false;
    }

    protected function groupMatches($templateGroup, $dataGroup)
    {
        $templateGroup = trim($templateGroup);
        $dataGroup = trim($dataGroup);
        if ($templateGroup === $dataGroup) {
            return true;
        }
        $templateKey = str_replace('机组', '', $templateGroup);
        return strpos($dataGroup, $templateKey) !== false;
    }

    /**
     * 表头：第1-4行，A-AA列
     */
    protected function buildHeader($sheet)
    {
        $fixedHeaders = [
            'A' => '车间',
            'B' => '机组',
            'C' => '机台名称',
            'D' => '班次',
            'E' => '机长',
            'F' => '产品名称',
            'G' => '工序名称',
        ];
        foreach ($fixedHeaders as $col => $title) {
            $sheet->setCellValue($col . '1', $title);
            $sheet->mergeCells($col . '1:' . $col . '3');
        }

        $sheet->setCellValue('H1', '理论速度');
        $sheet->mergeCells('H1:H2');
        $sheet->setCellValue('I1', '计划生产时间');
        $sheet->mergeCells('I1:I2');

        $sheet->setCellValue('J1', '计划内固定停机时间');
        $sheet->mergeCells('J1:K1');
        $sheet->setCellValue('J2', '标准计划保养时间');
        $sheet->setCellValue('K2', "标准早会、交接班时间\n（须按机时填写）");

        $sheet->setCellValue('L1', '非计划停机时间');
        $sheet->mergeCells('L1:O1');
        $sheet->setCellValue('L2', '设备故障时间');
        $sheet->setCellValue('M2', '实际换型时间');
        $sheet->setCellValue('N2', '实际打样时间');
        $sheet->setCellValue('O2', '其他异常时间');

        $mainHeaders = [
            'P' => '计划完成产量',
            'Q' => '实际产量',
            'R' => '不良品数量',
            'S' => '负荷时间',
            'T' => '实际运行时间',
            'U' => '生产计划达成率',
            'V' => "设备综合效率\n(OEE)",
            'W' => '时间利用率',
            'X' => '速度利用率',
            'Y' => '质量合格率',
            'Z' => '备注',
            'AA' => "标准产量\n（辅助计算列）",
        ];
        foreach ($mainHeaders as $col => $title) {
            $sheet->setCellValue($col . '1', $title);
            $sheet->mergeCells($col . '1:' . $col . '2');
        }

        $units = [
            'H' => "米/小时\n个/小时", 'I' => '小时', 'J' => '小时', 'K' => '小时',
            'L' => '小时', 'M' => '小时', 'N' => '小时', 'O' => '小时',
            'P' => "大米\n个", 'Q' => "大米\n个", 'R' => "大米\n个",
            'S' => '小时', 'T' => '小时', 'U' => '%', 'V' => '%', 'W' => '%',
            'X' => '%', 'Y' => '%', 'Z' => '', 'AA' => "大米\n个",
        ];
        foreach ($units as $col => $unit) {
            $sheet->setCellValue($col . '3', $unit);
        }

        for ($i = 1; $i <= 7; $i++) {
            $sheet->setCellValueByColumnAndRow($i, 4, '');
        }
        $numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 18, 15, 16, 17, '', 18];
        foreach ($numbers as $idx => $num) {
            $sheet->setCellValueByColumnAndRow($idx + 8, 4, $num);
        }
    }

    /**
     * 左侧固定层级数据（从第5行起）
     */
    protected function getTemplateRows()
    {
        return [
            ['全公司', '全公司', '印刷+印后全部工序合计', '/', 'company'],
            ['全公司', '全公司', '检验+糊盒全部工序合计', '/', 'company'],
            ['印刷车间', '全部机组', '印刷车间全部工序合计', '/', 'workshop'],
            ['印刷车间', '卷凹机组', '卷凹汇总', '/', 'group'],
            ['印刷车间', '卷凹机组', '卷凹A班', '/', 'shift'],
            ['印刷车间', '卷凹机组', '卷凹B班', '/', 'shift'],
            ['印刷车间', '胶印机组', '胶印汇总', '/', 'group'],
            ['印刷车间', '胶印机组', '胶印A班', '/', 'shift'],
            ['印刷车间', '胶印机组', '胶印B班', '/', 'shift'],
            ['印刷车间', '单凹机组', '单凹汇总', '/', 'group'],
            ['印刷车间', '单凹机组', '单凹A班', '/', 'shift'],
            ['印刷车间', '单凹机组', '单凹B班', '/', 'shift'],
            ['印刷车间', '覆膜机组', '覆膜汇总', '/', 'group'],
            ['印刷车间', '覆膜机组', '覆膜A班', '/', 'shift'],
            ['印刷车间', '覆膜机组', '覆膜B班', '/', 'shift'],
            ['印刷车间', '丝印机组', '丝印汇总', '/', 'group'],
            ['印刷车间', '丝印机组', '丝印A班', '/', 'shift'],
            ['印刷车间', '丝印机组', '丝印B班', '/', 'shift'],
            ['印刷车间', '喷码机组', '喷码汇总', '/', 'group'],
            ['印刷车间', '喷码机组', '喷码A班', '/', 'shift'],
            ['印刷车间', '喷码机组', '喷码B班', '/', 'shift'],
            ['印后车间（印刷）', '全部机组', '印后车间（印刷）全部工序合计', '/', 'workshop'],
            ['印后车间（印刷）', '烫金机组', '烫金工序汇总', '/', 'group'],
            ['印后车间（印刷）', '烫金机组', '烫金A班汇总', '/', 'shift'],
            ['印后车间（印刷）', '烫金机组', '烫金B班汇总', '/', 'shift'],
            ['印后车间（印刷）', '模切机组', '模切工序汇总', '/', 'group'],
            ['印后车间（印刷）', '模切机组', '模切工序A班汇总', '/', 'shift'],
            ['印后车间（印刷）', '模切机组', '模切工序B班汇总', '/', 'shift'],
            ['检验车间', '全部机组', '检验车间全部工序合计', '/', 'workshop'],
            ['检验车间', '手检', '手检工序汇总', '/', 'group'],
            ['检验车间', '机检', '机检工序汇总', '/', 'group'],
            ['检验车间', '机检', '机检A班', '/', 'shift'],
            ['检验车间', '机检', '机检B班', '/', 'shift'],
            ['印后车间（糊盒）', '全部机组', '印后车间（糊盒）全部工序合计', '/', 'workshop'],
            ['印后车间（糊盒）', '印后（糊盒）', '印后（糊盒）白班汇总', '/', 'shift'],
            ['印后车间（糊盒）', '印后（糊盒）', '印后（糊盒）夜班汇总', '/', 'shift'],
            ['糊盒车间', '全部机组', '糊盒车间全部工序合计', '/', 'workshop'],
            ['糊盒车间', '手工', '手工工序汇总', '/', 'group'],
            ['糊盒车间', '条盒糊盒', '条盒糊盒工序汇总', '/', 'group'],
            ['糊盒车间', '条盒糊盒', '条盒糊盒白班汇总', '/', 'shift'],
            ['糊盒车间', '条盒糊盒', '条盒糊盒夜班汇总', '/', 'shift'],
            ['糊盒车间', '皮壳组', '皮壳组工序汇总', '/', 'group'],
            ['糊盒车间', '皮壳组', '皮壳组白班汇总', '/', 'shift'],
            ['糊盒车间', '皮壳组', '皮壳组夜班汇总', '/', 'shift'],
            ['糊盒车间', '小盒糊盒', '小盒糊盒工序汇总', '/', 'group'],
            ['糊盒车间', '小盒糊盒', '小盒糊盒白班汇总', '/', 'shift'],
            ['糊盒车间', '小盒糊盒', '小盒糊盒夜班汇总', '/', 'shift'],
            ['糊盒车间', '小盒切割', '小盒切割工序汇总', '/', 'group'],
            ['糊盒车间', '小盒切割', '小盒切割白班汇总', '/', 'shift'],
            ['糊盒车间', '小盒切割', '小盒切割夜班汇总', '/', 'shift'],
            ['糊盒车间', '贴硫酸纸', '贴硫酸纸工序汇总', '/', 'group'],
            ['糊盒车间', '贴硫酸纸', '贴硫酸纸白班汇总', '/', 'shift'],
            ['糊盒车间', '贴硫酸纸', '贴硫酸纸夜班汇总', '/', 'shift'],
        ];
    }

    protected function buildLeftFixedData($sheet, array $rows)
    {
        $startRow = 5;
        foreach ($rows as $i => $row) {
            $r = $startRow + $i;
            $sheet->setCellValue('A' . $r, $row[0]);
            $sheet->setCellValue('B' . $r, $row[1]);
            $sheet->setCellValue('C' . $r, $row[2]);
            $sheet->setCellValue('D' . $r, $row[3]);
            $sheet->setCellValue('E' . $r, '/');
            $sheet->setCellValue('F' . $r, '/');
            $sheet->setCellValue('G' . $r, '/');
        }

        $lastRow = $startRow + count($rows) - 1;
        $this->mergeVerticalByValue($sheet, 'A', 7, $lastRow);
        $this->mergeVerticalByValue($sheet, 'B', 7, $lastRow);

        return $lastRow;
    }

    protected function mergeVerticalByValue($sheet, $col, $startRow, $endRow)
    {
        $mergeStart = $startRow;
        $prev = (string)$sheet->getCell($col . $startRow)->getValue();

        for ($r = $startRow + 1; $r <= $endRow + 1; $r++) {
            $curr = $r <= $endRow ? (string)$sheet->getCell($col . $r)->getValue() : null;
            if ($curr !== $prev) {
                if ($r - 1 > $mergeStart) {
                    $sheet->mergeCells($col . $mergeStart . ':' . $col . ($r - 1));
                }
                $mergeStart = $r;
                $prev = $curr;
            }
        }
    }

    protected function applyCommonStyle($sheet, $lastRow)
    {
        $range = 'A1:AA' . $lastRow;
        $sheet->getStyle($range)->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical'   => Alignment::VERTICAL_CENTER,
                'wrapText'   => true,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color'       => ['rgb' => '000000'],
                ],
            ],
            'font' => [
                'name' => '宋体',
                'size' => 10,
            ],
        ]);

        $sheet->getStyle('A1:AA4')->getFont()->setBold(true);
        $sheet->getRowDimension(1)->setRowHeight(22);
        $sheet->getRowDimension(2)->setRowHeight(36);
        $sheet->getRowDimension(3)->setRowHeight(30);
        $sheet->getRowDimension(4)->setRowHeight(18);
        for ($r = 5; $r <= $lastRow; $r++) {
            $sheet->getRowDimension($r)->setRowHeight(20);
        }
    }

    protected function setColumnWidths($sheet)
    {
        $widths = [
            'A' => 16, 'B' => 14, 'C' => 28, 'D' => 10, 'E' => 10,
            'F' => 12, 'G' => 12, 'H' => 10, 'I' => 10, 'J' => 10,
            'K' => 12, 'L' => 10, 'M' => 10, 'N' => 10, 'O' => 10,
            'P' => 10, 'Q' => 10, 'R' => 10, 'S' => 10, 'T' => 10,
            'U' => 10, 'V' => 12, 'W' => 10, 'X' => 10, 'Y' => 10,
            'Z' => 10, 'AA' => 12,
        ];
        foreach ($widths as $col => $w) {
            $sheet->getColumnDimension($col)->setWidth($w);
        }
    }
}