workHour/app/Exports/ResearchProjectDetailSheetExport.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 ResearchProjectDetailSheetExport implements WithEvents, WithTitle
{
    protected $project;
    protected $year;
    protected $taxInfo;

    public function __construct($year, array $project, array $taxInfo = []) {
        $this->year = $year;
        $this->project = $project;
        $this->taxInfo = $taxInfo;
    }

    public function title(): string {
        return $this->project['project_no'] ?? '项目明细';
    }

    public function registerEvents(): array {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                $lastCol = 'AO';
                $dataCount = count($this->project['details']);
                $lastRow = 7 + $dataCount;

                // --- 1. 第一行：基础信息行 ---
                $sheet->mergeCells('A1:D1');
                $sheet->setCellValue('A1', "加计扣除年度：{$this->year}年");
                $sheet->mergeCells('E1:L1');
                $sheet->setCellValue('E1', "纳税人识别号：" . ($this->taxInfo['tax_id'] ?? ''));
                $sheet->mergeCells('M1:AD1');
                $sheet->setCellValue('M1', "纳税人名称(盖章)：" . ($this->taxInfo['tax_name'] ?? ''));
                $sheet->mergeCells('AE1:AO1');
                $sheet->setCellValue('AE1', "金额单位：元（列至角分）");

                // --- 2. 绘制全量表头 (第2-7行) ---
                $this->drawCorrectHeaders($sheet);

                // --- 3. 填充数据 (从第8行开始) ---
                $currentRow = 8;
                foreach ($this->project['details'] as $row) {
                    $sheet->fromArray($this->mapRowData($row), null, "A{$currentRow}");
                    $currentRow++;
                }

                // --- 4. 样式全局精修 ---
                $fullRange = "A1:{$lastCol}{$lastRow}";
                $sheet->getStyle($fullRange)->getFont()->setBold(false);
                $sheet->getStyle($fullRange)->getAlignment()->applyFromArray([
                    'vertical' => Alignment::VERTICAL_CENTER,
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                    'wrapText' => true,
                ]);

                // 信息行特殊处理：靠左
                $sheet->getStyle('A1:AO1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

                // --- 5. A列数据区特殊处理：合并单元格并纵向排列文字 ---
                if ($dataCount > 0) {
                    $dataStartRow = 8;
                    $sheet->mergeCells("A{$dataStartRow}:A{$lastRow}");
                    $sheet->setCellValue("A{$dataStartRow}", "项目明细\n（填写项目贷方发生额）");
                    // 设置文字竖排/换行对齐
                    $sheet->getStyle("A{$dataStartRow}")->getAlignment()->setWrapText(true);
                }

                // --- 6. 设置行高与列宽 ---
                $sheet->getRowDimension(1)->setRowHeight(30);
                for ($i = 2; $i <= 6; $i++) {
                    $sheet->getRowDimension($i)->setRowHeight(40);
                }
                $sheet->getRowDimension(7)->setRowHeight(30); // 编号行

                $wideCols = ['A', 'H', 'R', 'S', 'U', 'V', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO'];
                foreach (range('A', 'AO') as $col) {
                    $width = in_array($col, $wideCols) ? 25 : 12;
                    $sheet->getColumnDimension($col)->setWidth($width);
                }

                // --- 7. 全表框线 ---
                $sheet->getStyle("A2:{$lastCol}{$lastRow}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
            },
        ];
    }

    private function drawCorrectHeaders($sheet) {
        // A列特殊：2-6行合并为“项目”，7行填“行次、序号”
        $sheet->mergeCells("A2:A6");
        $sheet->setCellValue("A2", "项目");
        $sheet->setCellValue("A7", "行次、序号");

        // B-K: 其他基础项目 (垂直合并 2-7行)
        $headers = [
            'B'=>'序号','C'=>'项目名称','D'=>'项目编号','E'=>'研发形式',
            'F'=>'资本化、费用化支出选项','G'=>'项目实施状态选项',
            'H'=>'委托方与受托方是否存在关联关系选项','I'=>'是否委托境外选项','J'=>'研发成果','K'=>'研发成果证书号'
        ];
        foreach ($headers as $col => $title) {
            $sheet->mergeCells("{$col}2:{$col}7");
            $sheet->setCellValue("{$col}2", $title);
        }

        // 一、人员人工
        $sheet->mergeCells('L2:N3'); $sheet->setCellValue('L2', '一、人员人工费用');
        $sheet->mergeCells('L4:M4'); $sheet->setCellValue('L4', '直接从事研发活动人员');
        $sheet->mergeCells('L5:L6'); $sheet->setCellValue('L5', '工资薪金'); $sheet->setCellValue('L7', '1.1');
        $sheet->mergeCells('M5:M6'); $sheet->setCellValue('M5', '五险一金'); $sheet->setCellValue('M7', '1.2');
        $sheet->mergeCells('N4:N6'); $sheet->setCellValue('N4', "外聘研发人员的\n劳务费用"); $sheet->setCellValue('N7', '1.3');

        // 二、直接投入
        $sheet->mergeCells('O2:V3'); $sheet->setCellValue('O2', '二、直接投入费用');
        $sheet->mergeCells('O4:Q4'); $sheet->setCellValue('O4', '研发活动直接消耗');
        $sheet->mergeCells('O5:O6'); $sheet->setCellValue('O5', '材料'); $sheet->setCellValue('O7', '2.1');
        $sheet->mergeCells('P5:P6'); $sheet->setCellValue('P5', '燃料'); $sheet->setCellValue('P7', '2.2');
        $sheet->mergeCells('Q5:Q6'); $sheet->setCellValue('Q5', '动力费用'); $sheet->setCellValue('Q7', '2.3');
        $sheet->mergeCells('R4:R6'); $sheet->setCellValue('R4', "用于中间试验和产品试制的模具、工艺装备开发及制造费"); $sheet->setCellValue('R7', '2.4');
        $sheet->mergeCells('S4:S6'); $sheet->setCellValue('S4', "用于不构成固定资产的样品、样机及一般测试手段购置费"); $sheet->setCellValue('S7', '2.5');
        $sheet->mergeCells('T4:T6'); $sheet->setCellValue('T4', '用于试制产品的检验验收费'); $sheet->setCellValue('T7', '2.6');
        $sheet->mergeCells('U4:U6'); $sheet->setCellValue('U4', "用于研发活动的仪器、设备的运行维护、调整、检验、维修等费用"); $sheet->setCellValue('U7', '2.7');
        $sheet->mergeCells('V4:V6'); $sheet->setCellValue('V4', "通过经营租赁方式租入的用于研发活动的仪器、设备租赁费"); $sheet->setCellValue('V7', '2.8');

        // 三、折旧
        $sheet->mergeCells('W2:X4'); $sheet->setCellValue('W2', '三、折旧费用');
        $sheet->mergeCells('W5:W6'); $sheet->setCellValue('W5', '用于研发活动的仪器、设备的折旧费'); $sheet->setCellValue('W7', '3.1');
        $sheet->mergeCells('X5:X6'); $sheet->setCellValue('X5', '用于研发活动的建筑物的折旧费'); $sheet->setCellValue('X7', '3.2');

        // 四、无形资产摊销
        $sheet->mergeCells('Y2:AA4'); $sheet->setCellValue('Y2', '四、无形资产摊销');
        $sheet->mergeCells('Y5:Y6'); $sheet->setCellValue('Y5', '用于研发活动的软件的摊销费用'); $sheet->setCellValue('Y7', '4.1');
        $sheet->mergeCells('Z5:Z6'); $sheet->setCellValue('Z5', '用于研发活动的专利权的摊销费用'); $sheet->setCellValue('Z7', '4.2');
        $sheet->mergeCells('AA5:AA6'); $sheet->setCellValue('AA5', "用于研发活动的非专利技术（包括许可证、专有技术、设计和计算方法等）的摊销费用"); $sheet->setCellValue('AA7', '4.3');

        // 五、新产品设计费
        $sheet->mergeCells('AB2:AE4'); $sheet->setCellValue('AB2', '五、新产品设计费等');
        $sheet->mergeCells('AB5:AB6'); $sheet->setCellValue('AB5', '新产品设计费'); $sheet->setCellValue('AB7', '5.1');
        $sheet->mergeCells('AC5:AC6'); $sheet->setCellValue('AC5', '新工艺规程制定费'); $sheet->setCellValue('AC7', '5.2');
        $sheet->mergeCells('AD5:AD6'); $sheet->setCellValue('AD5', '新药研制的临床试验费'); $sheet->setCellValue('AD7', '5.3');
        $sheet->mergeCells('AE5:AE6'); $sheet->setCellValue('AE5', '勘探开发技术的现场试验费'); $sheet->setCellValue('AE7', '5.4');

        // 六、其他相关费用
        $sheet->mergeCells('AF2:AJ4'); $sheet->setCellValue('AF2', '六、其他相关费用');
        $sheet->mergeCells('AF5:AF6'); $sheet->setCellValue('AF5', '技术图书资料费、资料翻译费等'); $sheet->setCellValue('AF7', '6.1');
        $sheet->mergeCells('AG5:AG6'); $sheet->setCellValue('AG5', '研发成果的检索、分析、评议等'); $sheet->setCellValue('AG7', '6.2');
        $sheet->mergeCells('AH5:AH6'); $sheet->setCellValue('AH5', '知识产权的申请费、注册费等'); $sheet->setCellValue('AH7', '6.3');
        $sheet->mergeCells('AI5:AI6'); $sheet->setCellValue('AI5', '职工福利费、补充养老保险费等'); $sheet->setCellValue('AI7', '6.4');
        $sheet->mergeCells('AJ5:AJ6'); $sheet->setCellValue('AJ5', '差旅费、会议费'); $sheet->setCellValue('AJ7', '6.5');

        // 七、委托外部
        $sheet->mergeCells('AK2:AK6'); $sheet->setCellValue('AK2', "七、委托外部机构或个人进行研发活动所发生的费用"); $sheet->setCellValue('AK7', '7');
        $sheet->mergeCells('AL2:AL6'); $sheet->setCellValue('AL2', "其中：委托境外进行研发活动所发生的费用（包括存在关联关系的委托研发"); $sheet->setCellValue('AL7', '7.1');

        // 八、九 汇总
        $sheet->mergeCells('AM2:AM6'); $sheet->setCellValue('AM2', "八、允许加计扣除的研发费用中的第1至5类费用合计"); $sheet->setCellValue('AM7', '8');
        $sheet->mergeCells('AN2:AN6'); $sheet->setCellValue('AN2', "其他相关费用限额=序号8×10%/(1-10%)"); $sheet->setCellValue('AN7', '8.1');
        $sheet->mergeCells('AO2:AO6'); $sheet->setCellValue('AO2', "九、当期费用化支出可加计扣除总额"); $sheet->setCellValue('AO7', '9');
    }

    private function mapRowData($row) {
        // A列在 registerEvents 中通过 mergeCells 统一填充了，所以这里返回时第一个位置留空
        return [
            '', $row['id']??'', $row['rd_name']??'', $row['rd_code']??'', $row['form']??'',
            $row['opt']??'', $row['status']??'', $row['rel']??'', $row['os']??'', $row['res']??'', $row['res_no']??'',
            $row['v1_1']??0, $row['v1_2']??0, $row['v1_3']??0,
            $row['v2_1']??0, $row['v2_2']??0, $row['v2_3']??0, $row['v2_4']??0, $row['v2_5']??0, $row['v2_6']??0, $row['v2_7']??0, $row['v2_8']??0,
            $row['v3_1']??0, $row['v3_2']??0,
            $row['v4_1']??0, $row['v4_2']??0, $row['v4_3']??0,
            $row['v5_1']??0, $row['v5_2']??0, $row['v5_3']??0, $row['v5_4']??0,
            $row['v6_1']??0, $row['v6_2']??0, $row['v6_3']??0, $row['v6_4']??0, $row['v6_5']??0,
            $row['v7']??0, $row['v7_1']??0,
            $row['v8']??0, $row['v8_1']??0, $row['v9']??0
        ];
    }
}