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. 计算列索引
                $totalDynamic = count($this->dynamicHeaders);
                $startDetailIdx = 6; // F列

                $col_E = 'E';
                $col_6 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic);
                $col_7_1 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 1);
                $col_7_2 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 2);
                $col_8_1 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 3);
                $col_8_2 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 4);
                $col_8_3 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 5);
                $col_8_4 = Coordinate::stringFromColumnIndex($startDetailIdx + $totalDynamic + 6);

                $highestColumn = $col_8_4;

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

                // 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']);

                    // 动态明细
                    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}*0.8");
                    $sheet->setCellValue("{$col_E}{$currentRow}", "={$col_6}{$currentRow}+{$col_7_2}{$currentRow}+{$col_8_2}{$currentRow}+{$col_8_4}{$currentRow}");

                    // 严格清洗类型字符串进行归集
                    $cleanType = preg_replace('/[\s\v\t\r\n]+/u', '', (string)$item['type']);
                    if ($cleanType === '资本化支出' && $item['status'] == 3) {
                        $capRows[] = $currentRow;
                    } elseif ($cleanType === '费用化支出') {
                        $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)
    {
        $sheet->mergeCells("A2:{$highestColumn}2");
        $sheet->setCellValue('A2', '研发支出辅助账汇总表');
        $sheet->getStyle('A2')->applyFromArray(['font' => ['size' => 16, 'bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]]);

        // 第3行布局修复
        $totalCols = Coordinate::columnIndexFromString($highestColumn);
        $sheet->setCellValue('A3', '纳税人识别号（统一社会信用代码）：' . $this->taxId);

        $nameCol = Coordinate::stringFromColumnIndex(max(7, floor($totalCols * 0.55)));
        $sheet->setCellValue($nameCol . '3', '纳税人名称：' . $this->companyName);

        $periodCol = Coordinate::stringFromColumnIndex($totalCols - 3);
        $sheet->setCellValue($periodCol . '3', "属期：{$this->year}年");

        $sheet->setCellValue($highestColumn . '3', '单位：元');
        $sheet->getStyle($highestColumn . '3')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

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

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

        $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');

        $sheet->mergeCells("{$col_8_1}5:{$highestColumn}5");
        $sheet->setCellValue("{$col_8_1}5", '委托研发费用及限额');
        $sIdx = Coordinate::columnIndexFromString($col_8_1);
        $subs = ['委托境内机构或个人进行研发活动所发生的费用', '允许加计扣除的委托境内机构或个人进行研发活动所发生的费用', '委托境外机构进行研发活动所发生的费用', '经限额调整后的委托境外机构进行研发活动所发生的费用'];
        $nums = ['8.1','8.2','8.3','8.4'];
        for($i=0;$i<4;$i++) {
            $c = Coordinate::stringFromColumnIndex($sIdx + $i);
            $sheet->setCellValue($c . '6', $subs[$i]);
            $sheet->setCellValue($c . '7', $nums[$i]);
        }
    }

    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_base = !empty($expRows) ? "SUM(".implode(',', array_map(fn($row)=>"{$col}{$row}", $expRows)).")" : "0";
            $sheet->setCellValue("{$col}{$r_cap}", $f_cap);
            $sheet->setCellValue("{$col}{$r_exp}", "={$f_exp_base}+{$col}{$r_oth}");
            $sheet->setCellValue("{$col}{$r_all}", "={$col}{$r_cap}+{$col}{$r_exp}");
        }

        $fCol = Coordinate::stringFromColumnIndex($startIdx);
        $lCol = Coordinate::stringFromColumnIndex($startIdx + $dynamicCount - 1);

        foreach ([$r_cap, $r_exp, $r_all] as $row) {
            $sheet->setCellValue("{$col_6}{$row}", "=SUM({$fCol}{$row}:{$lCol}{$row})");
            $sheet->setCellValue("{$col_8_2}{$row}", "={$col_8_1}{$row}*0.8");
            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)");
            }
            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)");
            }
            $sheet->setCellValue("{$col_E}{$row}", "={$col_6}{$row}+{$col_7_2}{$row}+{$col_8_2}{$row}+{$col_8_4}{$row}");
        }
    }
}