workHour/app/Exports/ResearchExpenseSummarySheetExport.php

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class ResearchExpenseSummarySheetExport implements WithEvents, WithTitle
{
    protected $year;
    protected $items;
    protected $taxId;
    protected $companyName;
    protected $dynamicHeaders; // 动态传入的明细科目

    public function __construct($year, array $payload, array $dynamicHeaders = [])
    {
        $this->year = $year;
        $this->items = $payload['items'] ?? [];
        $this->taxId = $payload['tax_id'] ?? '';
        $this->companyName = $payload['company_name'] ?? '';
        // 确保至少包含基础的：人员人工, 折旧
        $this->dynamicHeaders = $dynamicHeaders ?: ['人员人工费用', '折旧费用'];
    }

    public function title(): string { return $this->year . '年汇总表'; }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();

                // 1. 计算关键列的字母索引
                $baseColCount = 4; // A:编号, B:名称, C:完成, D:类型
                $totalDynamic = count($this->dynamicHeaders);

                // 允许加计扣除合计列 (E)
                $col_E = 'E';
                // 明细科目起始列 (F开始)
                $startDetailIdx = 6;
                // 小计列 (6列)
                $col_6 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic);
                // 7.1列 (其他相关费用合计)
                $col_7_1 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 1);
                // 7.2列 (调整后)
                $col_7_2 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 2);
                // 8.1列 (委托境内)
                $col_8_1 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 3);
                // 8.2列 (8.1*80%)
                $col_8_2 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 4);
                // 8.3列 (委托境外)
                $col_8_3 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 5);
                // 8.4列 (调整后境外)
                $col_8_4 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 6);

                $highestColumn = $col_8_4;

                // 2. 设置列宽
                $sheet->getColumnDimension('B')->setWidth(40);
                for ($i = 1; $i <= Coordinate::columnIndexFromString($highestColumn); $i++) {
                    $col = Coordinate::stringFromColumnIndex($i);
                    if ($col != 'B') $sheet->getColumnDimension($col)->setWidth(12);
                }

                // 3. 绘制表头
                $this->drawDynamicHeader($sheet, $col_E, $col_6, $col_7_1, $col_8_1, $highestColumn);

                // 4. 填充明细数据
                $currentRow = 8;
                $capRows = []; $expRows = [];

                foreach ($this->items as $item) {
                    $sheet->setCellValue("A{$currentRow}", $item['no']);
                    $sheet->setCellValue("B{$currentRow}", $item['name']);
                    $sheet->setCellValue("C{$currentRow}", $item['status'] == 3 ? '已完成' : '进行中');
                    $sheet->setCellValue("D{$currentRow}", $item['type']);

                    // 填充动态明细列 (F -> 小计前)
                    foreach ($this->dynamicHeaders as $idx => $header) {
                        $col = Coordinate::stringFromColumnIndex($startDetailIdx + $idx);
                        $sheet->setCellValue("{$col}{$currentRow}", $item['values'][$idx] ?? 0);
                    }

                    // 填充固定逻辑列
                    $sheet->setCellValue("{$col_7_1}{$currentRow}", $item['val7_1'] ?? 0);
                    $sheet->setCellValue("{$col_8_1}{$currentRow}", $item['val8_1'] ?? 0);
                    $sheet->setCellValue("{$col_8_3}{$currentRow}", $item['val8_3'] ?? 0);

                    // 行公式
                    $firstDetailCol = Coordinate::stringFromColumnIndex($startDetailIdx);
                    $lastDetailCol = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic - 1);

                    $sheet->setCellValue("{$col_6}{$currentRow}", "=SUM({$firstDetailCol}{$currentRow}:{$lastDetailCol}{$currentRow})");
                    $sheet->setCellValue("{$col_7_2}{$currentRow}", "={$col_7_1}{$currentRow}");
                    $sheet->setCellValue("{$col_8_2}{$currentRow}", "={$col_8_1}{$currentRow}*0.8");
                    $sheet->setCellValue("{$col_8_4}{$currentRow}", "={$col_8_3}{$currentRow}");
                    // E = 6 + 7.2 + 8.2 + 8.4
                    $sheet->setCellValue("{$col_E}{$currentRow}", "={$col_6}{$currentRow}+{$col_7_2}{$currentRow}+{$col_8_2}{$currentRow}+{$col_8_4}{$currentRow}");

                    if ($item['type'] == '资本化支出' && $item['status'] == 3) $capRows[] = $currentRow;
                    elseif ($item['type'] == '费用化支出') $expRows[] = $currentRow;
                    $currentRow++;
                }

                // 5. 应用合计勾稽逻辑
                $this->applyDynamicSummaryLogic($sheet, $currentRow, $capRows, $expRows, $startDetailIdx, $totalDynamic, $col_E, $col_6, $col_7_1, $col_7_2, $col_8_1, $col_8_2, $col_8_3, $col_8_4);

                // 6. 最终样式
                $lastDataRow = $currentRow + 3;
                $sheet->getStyle("A5:{$highestColumn}{$lastDataRow}")->applyFromArray(['borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]]);
                $sheet->getStyle("E8:{$highestColumn}{$lastDataRow}")->getNumberFormat()->setFormatCode('#,##0.00');
                $sheet->getStyle("A5:{$highestColumn}7")->getAlignment()->setWrapText(true)->setHorizontal('center')->setVertical('center');
            },
        ];
    }

    private function drawDynamicHeader($sheet, $col_E, $col_6, $col_7_1, $col_8_1, $highestColumn)
    {
        // --- 1. 大标题 (第2行) ---
        $sheet->mergeCells("A2:{$highestColumn}2");
        $sheet->setCellValue('A2', '研发支出辅助账汇总表');
        $sheet->getStyle('A2')->applyFromArray([
            'font' => ['size' => 16, 'bold' => true],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
        ]);

        // --- 2. 项目信息行 (第3行) ---
        // 纳税人识别号 (靠左)
        $sheet->setCellValue('A3', '纳税人识别号（统一社会信用代码）：' . $this->taxId);

        // 纳税人名称 (居中偏右，通常定位在 J 列附近，或者根据动态列数灵活调整)
        $nameCol = Coordinate::stringFromColumnIndex(max(10, count($this->dynamicHeaders) + 4));
        $sheet->setCellValue($nameCol . '3', '纳税人名称：' . $this->companyName);

        // 属期 (定位在倒数第 2 或第 3 列)
        $periodCol = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($highestColumn) - 2);
        $sheet->setCellValue($periodCol . '3', "属期：{$this->year}年");

        // 单位 (固定最右侧)
        $sheet->setCellValue($highestColumn . '3', '单位：元');
        $sheet->getStyle($highestColumn . '3')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        // --- 3. 第4行留空 (保持默认即可，不赋值) ---

        // --- 4. 表头 (5-7行) ---
        // A-E 固定列
        $vHeaders = ['A'=>'项目编号', 'B'=>'项目名称', 'C'=>'完成情况', 'D'=>'支出类型', 'E'=>"允许加计\n扣除金额合计"];
        foreach ($vHeaders as $col => $text) {
            $sheet->mergeCells("{$col}5:{$col}7");
            $sheet->setCellValue("{$col}5", $text);
        }

        // 动态明细表头 (F列开始)
        $startIdx = 6;
        foreach ($this->dynamicHeaders as $idx => $text) {
            $col = Coordinate::stringFromColumnIndex($startIdx + $idx);
            $sheet->mergeCells("{$col}5:{$col}6");
            $sheet->setCellValue("{$col}5", $text);
            $sheet->setCellValue("{$col}7", $idx + 1);
        }

        // 小计列
        $sheet->mergeCells("{$col_6}5:{$col_6}6");
        $sheet->setCellValue("{$col_6}5", "前" . count($this->dynamicHeaders) . "项 小计");
        $sheet->setCellValue("{$col_6}7", count($this->dynamicHeaders) + 1);

        // 其他相关费用 (7.1, 7.2)
        $col_7_2 = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($col_7_1) + 1);
        $sheet->mergeCells("{$col_7_1}5:{$col_7_2}5");
        $sheet->setCellValue("{$col_7_1}5", '其他相关费用及限额');
        $sheet->setCellValue("{$col_7_1}6", '其他相关费用合计');
        $sheet->setCellValue("{$col_7_1}7", '7.1');
        $sheet->setCellValue("{$col_7_2}6", "经限额调整后的其他相关费用");
        $sheet->setCellValue("{$col_7_2}7", '7.2');

        // 委托研发 (8.1-8.4)
        $sheet->mergeCells("{$col_8_1}5:{$highestColumn}5");
        $sheet->setCellValue("{$col_8_1}5", '委托研发费用及限额');

        $startColIdx = Coordinate::columnIndexFromString($col_8_1);
        $subHeaderNames = [
            '委托境内机构或个人进行研发活动所发生的费用',
            '允许加计扣除的委托境内机构或个人进行研发活动所发生的费用',
            '委托境外机构进行研发活动所发生的费用',
            '经限额调整后的委托境外机构进行研发活动所发生的费用'
        ];
        $nums = ['8.1','8.2','8.3','8.4'];

        for($i=0; $i<4; $i++) {
            $colLetter = Coordinate::stringFromColumnIndex($startColIdx + $i);
            $sheet->setCellValue($colLetter . '6', $subHeaderNames[$i]);
            $sheet->setCellValue($colLetter . '7', $nums[$i]);
        }

        // 设置第3行行高和垂直居中
        $sheet->getRowDimension('3')->setRowHeight(25);
        $sheet->getStyle("A3:{$highestColumn}3")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
    }

    private function applyDynamicSummaryLogic($sheet, $r, $capRows, $expRows, $startIdx, $dynamicCount, $col_E, $col_6, $col_7_1, $col_7_2, $col_8_1, $col_8_2, $col_8_3, $col_8_4)
    {
        $r_cap = $r; $r_exp = $r + 1; $r_oth = $r + 2; $r_all = $r + 3;
        $labels = ['资本化金额小计', '费用化金额小计', '其中：其他事项', '全额合计'];
        foreach ($labels as $i => $label) {
            $sheet->mergeCells("A" . ($r+$i) . ":D" . ($r+$i));
            $sheet->setCellValue("A" . ($r+$i), $label);
        }

        // 所有需要汇总计算的数值列
        $allValCols = [];
        for($i=0; $i<$dynamicCount; $i++) { $allValCols[] = Coordinate::stringFromColumnIndex($startIdx + $i); }
        $allValCols = array_merge($allValCols, [$col_7_1, $col_8_1, $col_8_3]);

        foreach ($allValCols as $col) {
            $f_cap = !empty($capRows) ? "=SUM(".implode(',', array_map(fn($row)=>"{$col}{$row}", $capRows)).")" : "0";
            $f_exp = !empty($expRows) ? "=SUM(".implode(',', array_map(fn($row)=>"{$col}{$row}", $expRows)).") + {$col}{$r_oth}" : "{$col}{$r_oth}";
            $sheet->setCellValue("{$col}{$r_cap}", $f_cap);
            $sheet->setCellValue("{$col}{$r_exp}", $f_exp);
            $sheet->setCellValue("{$col}{$r_all}", "={$col}{$r_cap}+{$col}{$r_exp}");
        }

        // 核心勾稽公式应用
        $firstDetailCol = Coordinate::stringFromColumnIndex($startIdx);
        $lastDetailCol = Coordinate::stringFromColumnIndex($startIdx + $dynamicCount - 1);

        foreach ([$r_cap, $r_exp, $r_all] as $row) {
            $sheet->setCellValue("{$col_6}{$row}", "=SUM({$firstDetailCol}{$row}:{$lastDetailCol}{$row})");
            $sheet->setCellValue("{$col_8_2}{$row}", "={$col_8_1}{$row}*0.8");

            // 规则六：7.2列 (限额调整)
            if ($row == $r_all) {
                $sheet->setCellValue("{$col_7_2}{$row}", "=MIN({$col_7_1}{$row}, {$col_6}{$row}*0.1/0.9)");
            } else {
                $sheet->setCellValue("{$col_7_2}{$row}", "=IF({$col_7_1}{$r_all}>0, ({$col_7_2}{$r_all}/{$col_7_1}{$r_all})*{$col_7_1}{$row}, 0)");
            }

            // 规则八：8.4列 (境外限额)
            if ($row == $r_all) {
                $sheet->setCellValue("{$col_8_4}{$row}", "=MIN(({$col_6}{$row}+{$col_7_2}{$row}+{$col_8_2}{$row})*2/3, {$col_8_3}{$row}*0.8)");
            } else {
                $sheet->setCellValue("{$col_8_4}{$row}", "=IF({$col_8_3}{$r_all}>0, ({$col_8_4}{$r_all}/{$col_8_3}{$r_all})*{$col_8_3}{$row}, 0)");
            }

            // 最终合计列 E
            $sheet->setCellValue("{$col_E}{$row}", "={$col_6}{$row}+{$col_7_2}{$row}+{$col_8_2}{$row}+{$col_8_4}{$row}");
        }
    }
}