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;

class ResearchExpenseSummarySheetExport implements WithEvents, WithTitle
{
    protected $year;
    protected $items;
    protected $taxId;
    protected $companyName;

    public function __construct($year, array $payload)
    {
        $this->year = $year;
        $this->items = $payload['items'] ?? [];
        $this->taxId = $payload['tax_id'] ?? '';
        $this->companyName = $payload['company_name'] ?? '';
    }

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

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

                // 1. 设置列宽 (A-Q)
                $widths = ['A'=>12, 'B'=>40, 'C'=>10, 'D'=>12, 'E'=>18, 'F'=>12, 'G'=>12, 'H'=>12, 'I'=>12, 'J'=>12, 'K'=>15, 'L'=>12, 'M'=>12, 'N'=>12, 'O'=>12, 'P'=>12, 'Q'=>12];
                foreach ($widths as $col => $w) { $sheet->getColumnDimension($col)->setWidth($w); }

                // 2. 绘制表头 (去大标题样式)
                $this->drawCorrectHeader($sheet);

                // 3. 填充数据
                $currentRow = 8;
                $capRows = []; // 仅存 资本化且已完成(status=3) 的行
                $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']);

                    // 原始数据
                    $sheet->setCellValue("F{$currentRow}", $item['val1']); // 1.人工
                    $sheet->setCellValue("G{$currentRow}", $item['val2']); // 2.投入
                    $sheet->setCellValue("H{$currentRow}", $item['val3']); // 3.折旧
                    $sheet->setCellValue("I{$currentRow}", $item['val4']); // 4.摊销
                    $sheet->setCellValue("J{$currentRow}", $item['val5']); // 5.新产品
                    $sheet->setCellValue("L{$currentRow}", $item['val7_1']); // 7.1
                    $sheet->setCellValue("N{$currentRow}", $item['val8_1']); // 8.1
                    $sheet->setCellValue("P{$currentRow}", $item['val8_3'] ?? 0); // 8.3

                    // 【逻辑修正】明细行公式
                    $sheet->setCellValue("K{$currentRow}", "=SUM(F{$currentRow}:J{$currentRow})"); // 6列
                    $sheet->setCellValue("O{$currentRow}", "=N{$currentRow}*0.8"); // 8.2 = 8.1 * 80%
                    // 明细行不限制 7.2 和 8.4，通常直接取 7.1 和 8.3
                    $sheet->setCellValue("M{$currentRow}", "=L{$currentRow}"); // 7.2
                    $sheet->setCellValue("Q{$currentRow}", "=P{$currentRow}"); // 8.4
                    // E列 = 6 + 7.2 + 8.2 + 8.4
                    $sheet->setCellValue("E{$currentRow}", "=K{$currentRow}+M{$currentRow}+O{$currentRow}+Q{$currentRow}");

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

                // 4. 合计行勾稽计算
                $this->applySummaryLogic($sheet, $currentRow, $capRows, $expRows);

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

                // 签字行
                $sheet->setCellValue("A" . ($lastDataRow + 1), "法定代表人（签章）：");
            },
        ];
    }

    private function drawCorrectHeader($sheet)
    {
        $sheet->mergeCells('A2:Q2');
        $sheet->setCellValue('A2', '研发支出辅助账汇总表');
        $sheet->getStyle('A2')->getAlignment()->setHorizontal('center');
        $sheet->getStyle('A2')->getFont()->setSize(16)->setBold(true);

        $sheet->setCellValue('A3', '纳税人识别号：' . $this->taxId);
        $sheet->setCellValue('J3', '纳税人名称：' . $this->companyName);
        $sheet->setCellValue('O3', "属期：{$this->year}年");
        $sheet->setCellValue('Q3', '单位：元');

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

        $subHeaders = ['F'=>'人员人工费用', 'G'=>'直接投入费用', 'H'=>'折旧费用', 'I'=>'无形资产摊销', 'J'=>'新产品设计费等', 'K'=>'前五项 小计'];
        foreach ($subHeaders as $col => $text) {
            $sheet->mergeCells("{$col}5:{$col}6");
            $sheet->setCellValue("{$col}5", $text);
        }

        $sheet->mergeCells('L5:M5'); $sheet->setCellValue('L5', '其他相关费用及限额');
        $sheet->setCellValue('L6', '其他相关费用合计'); $sheet->setCellValue('M6', "经限额调整后的\n其他相关费用");

        $sheet->mergeCells('N5:Q5'); $sheet->setCellValue('N5', '委托研发费用及限额');
        $sheet->setCellValue('N6', "委托境内机构或\n个人研发费用"); $sheet->setCellValue('O6', "允许加计扣除的\n委托境内研发费");
        $sheet->setCellValue('P6', "委托境外机构\n进行研发活动费"); $sheet->setCellValue('Q6', "经限额调整后的\n委托境外研发费");

        $nums = ['F'=>'1','G'=>'2','H'=>'3','I'=>'4','J'=>'5','K'=>'6','L'=>'7.1','M'=>'7.2','N'=>'8.1','O'=>'8.2','P'=>'8.3','Q'=>'8.4'];
        foreach ($nums as $col => $n) { $sheet->setCellValue("{$col}7", $n); }
    }

    private function applySummaryLogic($sheet, $r, $capRows, $expRows)
    {
        $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);
        }

        // 基础汇总列 (F,G,H,I,J,L,N,P)
        $cols = ['F', 'G', 'H', 'I', 'J', 'L', 'N', 'P'];
        foreach ($cols 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}");
        }

        // --- 核心勾稽逻辑修正 ---
        foreach ([$r_cap, $r_exp, $r_all] as $row) {
            $sheet->setCellValue("K{$row}", "=SUM(F{$row}:J{$row})"); // 6列
            $sheet->setCellValue("O{$row}", "=N{$row}*0.8");           // 8.2列

            // 修正：7.2列 (规则六)
            if ($row == $r_all) {
                $sheet->setCellValue("M{$row}", "=MIN(L{$row}, K{$row}*0.1/0.9)");
            } else {
                $sheet->setCellValue("M{$row}", "=IF(L{$r_all}>0, (M{$r_all}/L{$r_all})*L{$row}, 0)");
            }

            // 修正：8.4列 (规则八：金额合计行计算公式)
            if ($row == $r_all) {
                // 1. 金额合计 = MIN((6列+7.2列+8.2列)*2/3, 8.3列*80%)
                $sheet->setCellValue("Q{$row}", "=MIN((K{$row}+M{$row}+O{$row})*2/3, P{$row}*0.8)");
            } else {
                // 分摊逻辑
                $sheet->setCellValue("Q{$row}", "=IF(P{$r_all}>0, (Q{$r_all}/P{$r_all})*P{$row}, 0)");
            }

            // 修正：E列 (规则四：E = 6+7.2+8.2+8.4)
            $sheet->setCellValue("E{$row}", "=K{$row}+M{$row}+O{$row}+Q{$row}");
        }
    }
}