workHour/app/Service/ExportFileService.php

<?php

namespace App\Service;

use App\Exports\ExportOrder;
use App\Exports\ItemSalaryFTMultipleSheetExport;
use App\Exports\ItemSalarySheetExport;
use App\Exports\ManMonthlyWorkHourMultipleSheetExport;
use App\Exports\MultiSheetExport;
use App\Exports\ProjectDepreciationMultipleSheetExport;
use App\Exports\ProjectStaffExport;
use App\Exports\ResearchExpenseMultipleSheetExport;
use App\Model\Depart;
use App\Model\Employee;
use App\Model\Fee;
use App\Model\PLeaveOverOrder;
use Illuminate\Support\Carbon;
use Maatwebsite\Excel\Facades\Excel;

class ExportFileService extends Service
{
    public static $filename = "";

    //导出的方式 0 选择导出的数据 1 查询后 导出指定的数据（最多每次一千条）
    public static $export_type = [
        0,
        1
    ];

    public function exportAll($data,$user){
        if(empty($data['menu_id'])) return [false, 'menu_id不能为空'];
        if(empty($data['type'])) return [false, 'type不能为空'];
        $function = $data['type'];
        $name = $this->fillE($data['type'], $user);
        if (! method_exists(self::class, $function)) return [false, "导出方法不存在,请联系开发"];
        self::$filename = $name;

        $export_type = $data['export_type'] ?? 0;
        if(! isset(self::$export_type[$export_type])) return [false,'导出文件方式错误或者不存在'];
        if(empty($export_type)){
            if(empty($data['id'])) return [false,'请选择导出数据'];
            $search = $data;
        }else{
            if(empty($data['order_search'])) return [false,'搜索条件不能为空'];
            $search = $data['order_search'];
            if(empty($search['page_index'])) return [false,'请选择导出数据的开始页码'];
            if(empty($search['page_size'])) return [false,'请选择导出数据的条数'];
            if($search['page_size'] > 5000) return [false,'请选择导出数据的条数每次最多5000条'];
            $data['order_search']['menu_id'] = $data['menu_id'];
            $search = $data['order_search'];
        }

        list($status, $return) = $this->$function($search,$user);
        if(! $status) return [false, $return];

        return [true, $return];
    }

    public function fillE($type, &$user){
        $header = config("excel." . $type) ?? [];
        $funcName = $header['name'] ?? "";

//        $header_f = "extra_" . $menu_id;
//        $service = new TableHeadService();
//        if(method_exists($service,$header_f)) $service->$header_f($header_default);

        $user['e_header_default'] = $header['array'] ?? [];

        return $funcName;
    }

    private function fillData($data, $column, &$return)
    {
        // 预先创建包含默认值的键数组
        $default = array_fill_keys($column, '');

        foreach ($data as $value) {
            // 提取交集，并用默认值补充缺失的键
            $return[] = array_merge($default, array_intersect_key($value, $default));
        }
    }

    private function fillTotalData($data, $column, &$return){
        $tmp = [];
        foreach ($column as $value){
            $key = $value['key'];
            if(! empty($value['sum']) && isset($data[$value['key']])){
                $decimals = $col['decimals'] ?? 2;
//                $tmp[$value['key']] = $data[$value['key']];

                // 用 number_format 格式化输出（保持字符串形式，避免科学计数）
                $tmp[$key] = number_format((float)$data[$key], $decimals, '.', '');
            }else{
                $tmp[$value['key']] = "";
            }
        }

        $return[] = $tmp;
    }

    public function employee($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new EmployeeService();
        $model = $service->employeeCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column, $user){
            $service->fillDataForExport($data, $column, $user, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function depart($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new EmployeeService();
        $model = $service->departCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column, $user){
            $data = $data->toArray();
            $list['data'] = $data;

            //订单数据
            $list = $service->fillDepartList($list, $user, true);
            //返回数据
            $this->fillData($list, $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function device($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new DeviceService();
        $model = $service->deviceCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column,$ergs, $user){
            $data = $data->toArray();
            $list['data'] = $data;

            //订单数据
            $list = $service->fillData($list, $ergs, $user);
            //返回数据
            $this->fillData($list['data'], $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function item($ergs,$user){
        // 导出数据
        $return = [];

        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new ItemService();
        $model = $service->itemCommon($ergs, $user);
        $model->chunk(500,function ($data) use(&$return,$column,$service, $user){
            // 调用 Service 层的填充方法
            $service->fillDataForExport($data, $column, $user, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function fee($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new FeeService();
        $model = $service->feeCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column, $user){
            $data = $data->toArray();
            $list['data'] = $data;

            //订单数据
            $list = $service->fillFeeList($list, $user, true);
            //返回数据
            $this->fillData($list['data'], $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function monthPwOrder($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new PersonWorkService();
        $model = $service->monthlyPwOrderCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column){
            // 直接处理这一批主表数据，将其与详情合并平铺
            $service->fillDataForExport($data->toArray(), $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function monthDwOrder($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new DeviceWorkService();
        $model = $service->monthlyDwOrderCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column){
            // 直接处理这一批主表数据，将其与详情合并平铺
            $service->fillDataForExport($data->toArray(), $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function monthPsOrder($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new PersonSalaryService();
        $model = $service->monthlyPsOrderCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column){
            // 直接处理这一批主表数据，将其与详情合并平铺
            $service->fillDataForExport($data->toArray(), $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function monthDdOrder($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new DeviceDepreciationService();
        $model = $service->monthlyDdOrderCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column){
            // 直接处理这一批主表数据，将其与详情合并平铺
            $service->fillDataForExport($data->toArray(), $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function ruleSet($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new RuleSetService();
        $model = $service->ruleSetCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column){
            // 直接处理这一批主表数据，将其与详情合并平铺
            $service->fillDataForExport($data->toArray(), $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function dailyPwOrder($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new PersonWorkService();
        $model = $service->dailyPwOrderCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column){
            // 直接处理这一批主表数据，将其与详情合并平铺
            $service->fillDataForExportDaily($data->toArray(), $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function dailyDwOrder($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new DeviceWorkService();
        $model = $service->dailyDwOrderCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column){
            // 直接处理这一批主表数据，将其与详情合并平铺
            $service->fillDataForExportDaily($data->toArray(), $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function leaveOrder($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new PLeaveOverService();
        $ergs['type'] = PLeaveOverOrder::TYPE_ONE;
        $model = $service->pLeaveOverCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column){
            // 直接处理这一批主表数据，将其与详情合并平铺
            $service->fillDataForExportDaily($data->toArray(), $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function overtimeOrder($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'export');
        $header = array_column($header_default,'value');

        $service = new PLeaveOverService();
        $ergs['type'] = PLeaveOverOrder::TYPE_TWO;
        $model = $service->pLeaveOverCommon($ergs, $user);

        $model->chunk(500,function ($data) use(&$return, $service, $column){
            // 直接处理这一批主表数据，将其与详情合并平铺
            $service->fillDataForExportDaily($data->toArray(), $column, $return);
        });

        return [true, $this->saveExportData($return,$header)];
    }

    public function feeOrder($ergs, $user)
    {
        // 导出配置
        $return = [];
        $header_default = $user['e_header_default']; // 假设前端已下发报销单的模板配置
        $column = array_column($header_default, 'export');
        $header = array_column($header_default, 'value');

        $service = new ExpenseClaimsService();
        // 使用你提到的 common 方法获取 Query Builder
        $model = $service->expenseClaimsSetCommon($ergs, $user);

        $model->chunk(500, function ($data) use (&$return, $service, $column) {
            // 直接处理这一批主表数据，将其与详情合并平铺
            $service->fillDataForExport($data->toArray(), $column, $return);
        });

        return [true, $this->saveExportData($return, $header)];
    }

    public function RDOrder($ergs, $user)
    {
        // 导出配置
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default, 'export');
        $header = array_column($header_default, 'value');

        $service = new AuxiliaryAccountService();
        $model = $service->setCommon($ergs, $user);

        $model->chunk(500, function ($data) use (&$return, $service, $column) {
            // 直接处理这一批主表数据，将其与详情合并平铺
            $service->fillDataForExport($data->toArray(), $column, $return);
        });

        return [true, $this->saveExportData($return, $header)];
    }

    //优先级
    public function priority($ergs, $user)
    {
        // 导出配置
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default, 'export');
        $header = array_column($header_default, 'value');

        $service = new PriorityService();
        $model = $service->priorityCommon($ergs, $user);

        $model->chunk(500, function ($data) use (&$return, $service, $column, $user) {
            $data = $data->toArray();
            $list['data'] = $data;

            //订单数据
            $list = $service->fillPriorityList($list, $user, true);
            //返回数据
            $this->fillData($list['data'], $column, $return);
        });

        return [true, $this->saveExportData($return, $header)];
    }

    // 项目工资统计表
    public function exportEmployeeSalary($data, $user)
    {
        $service = new StatisticService();
        // 1. 调用你现有的查询方法获取基础数据
        list($status, $itemMonthList) = $service->employeeMonthSalaryStatistic($data, $user);
        if (!$status) return $itemMonthList; // 返回错误信息

        // 2. 提取所有涉及到的唯一项目 (按 Code 排序，保证列顺序固定)
        $projects = collect($itemMonthList)->pluck('item_code')->unique()->sort()->values()->toArray();

        // 3. 按月份对数据进行分组
        $groupedByMonth = collect($itemMonthList)->groupBy('month')->sortKeys();

        $exportData = [];
        $columnTotals = array_fill(0, count($projects) * 2, 0); // 用于存储每列的合计
        $grandTotalSalary = 0; // 总计金额

        // 4. 循环每个月，构造行数据
        foreach ($groupedByMonth as $month => $items) {
            $row = [$month]; // A列：月份
            $monthTotalSalary = 0;

            // 创建该月项目的映射，方便快速查找
            $monthItemsMap = $items->keyBy('item_code');

            foreach ($projects as $index => $code) {
                $itemDetail = $monthItemsMap->get($code);
                $days = $itemDetail['days'] ?? 0;
                $salary = $itemDetail['allocated_salary'] ?? 0;

                $row[] = $days > 0 ? $days : '';     // 天数列
                $row[] = $salary > 0 ? $salary : ''; // 工资列

                // 累加合计行（列合计）
                $columnTotals[$index * 2] += $days;
                $columnTotals[$index * 2 + 1] += $salary;
                $monthTotalSalary += $salary;
            }

            $row[] = $monthTotalSalary; // 最后一列：该月合计工资
            $grandTotalSalary += $monthTotalSalary;
            $exportData[] = $row;
        }

        // 5. 构造最后的“合计”行
        $totalRow = ['合计'];
        foreach ($columnTotals as $val) {
            $totalRow[] = $val > 0 ? $val : 0;
        }
        $totalRow[] = $grandTotalSalary;
        $exportData[] = $totalRow;

        //获取公司基本信息
        $company = EmployeeService::getCompanyDetail($user);

        $file_name = "项目工资统计表_" . date("Y-m-d") . "_". rand(1000,9999);
        $filename =  $file_name . '.' . 'xlsx';
        $bool = Excel::store(new ItemSalarySheetExport($projects, $exportData, $company['title'] ?? ''),"/public/export/{$filename}", null, 'Xlsx', []);

        return [true, $filename];
    }

    // 人员月工时统计表
    public function exportManMonthlyWorkHour($data, $user)
    {
        // 1. 获取报表基础数据
        $service = new StatisticService();
        list($status, $result) = $service->employeeDayHourStatistic($data, $user);
        if (!$status) return $result;

        $sourceData = collect($result);

        // 2. 按月份分组，准备多 Sheet 数据
        // 结构：[ '2024-04' => [ 'days' => 30, 'data' => [...] ], ... ]
        $monthsData = [];

        // 按月分组数据
        $groupedByMonth = $sourceData->groupBy(function ($item) {
            return date('Y年m月', strtotime($item['order_date']));
        });

        foreach ($groupedByMonth as $monthName => $monthItems) {
            // A. 计算该月总天数
            // 转换 '2024年04月' 为 '2024-04' 获取天数
            $formatMonth = str_replace(['年', '月'], ['-', ''], $monthName);
            $daysInMonth = date('t', strtotime($formatMonth . '-01'));

            // B. 进一步按 项目+人员 分组，因为一行显示一个项目一个人的全月工时
            $groupedByUserItem = $monthItems->groupBy(function ($item) {
                return $item['item_code'] . '_' . $item['employee_id'];
            });

            $sheetRows = [];
            foreach ($groupedByUserItem as $key => $records) {
                $first = $records->first();
                // 初始化行：前两列是 项目编号 和 姓名
                $row = [
                    $first['item_code'],
                    $first['employee_name']
                ];

                // C. 循环 1 号到该月最后一天，填充工时
                // 将该员工该项目在这个月的记录转为 日期 => 工时 的映射
                $dayMap = $records->keyBy(function($r){
                    return (int)date('d', strtotime($r['order_date']));
                });

                for ($d = 1; $d <= $daysInMonth; $d++) {
                    $workHour = $dayMap->get($d)['total_work_hours'] ?? '';
                    // 如果工时为 0 或空，按你要求的格式传空字符串
                    $row[] = ($workHour > 0) ?(float) $workHour : '';
                }

                $sheetRows[] = $row;
            }

            $monthsData[$monthName] = [
                'days' => (int)$daysInMonth,
                'data' => $sheetRows
            ];
        }

        $file_name = "人员月工时统计表_" . date("Y-m-d") . "_". rand(1000,9999);
        $filename =  $file_name . '.' . 'xlsx';

        $bool =  Excel::store(new ManMonthlyWorkHourMultipleSheetExport($monthsData), "/public/export/{$filename}", null, 'Xlsx', []);
        return [true, $filename];
    }

    // 项目设备折旧
    public function exportDeviceZj(array $data, $user)
    {
        $service = new StatisticService();
        list($status, $result) = $service->itemDeviceMonthStatistic($data, $user);
        if (!$status) return $result;

        // 2. 将数据按 [项目编号][月份] 进行分组
        // 预期结构：$monthsData['RD01']['months']['2025-01'] = [设备1, 设备2...]
        $groupedData = collect($result)->groupBy('item_code');

        $finalExportData = [];

        foreach ($groupedData as $itemCode => $itemRecords) {
            $projectName = $itemRecords->first()['item_title'] ?? '';

            // 2. 获取年份（从 order_month 字段提取，如 "2026-01" 取前4位）
            $firstMonth = $firstRecord['month'] ?? date('Y-m');
            $year = substr($firstMonth, 0, 4);

            // 3. 构造新的 Key：年-项目 (例如: 2026-53code)
            $newSheetKey = $year . '年度项目' . $itemCode;

            // 按月份进一步分组
            $monthGroups = $itemRecords->groupBy('month');

            $monthsPayload = [];
            foreach ($monthGroups as $month => $devices) {
                $monthData = [];
                foreach ($devices as $dev) {
                    $monthData[] = [
                        'device_name'         => $dev['device_title'],
                        'original_value'      => $dev['device_original'],    // 设备原值
                        'total_depreciation'  => $dev['total_depreciatio'], // 当月总折旧
                        'project_hours'       => $dev['hours'],              // 本项目工时
                        'total_hours'         => $dev['total_hours'],        // 当月总工时
                        'ratio'               => bcmul($dev['ratio'], 100,2),              // 研发工时占比
                        'allocated_depreciation' => $dev['allocated_depreciatio'], // 本项目分摊折旧
                    ];
                }
                $monthsPayload[$month] = $monthData;
            }

            // 构造 Sheet 所需结构
            $finalExportData[$newSheetKey] = [
                'project_name' => $projectName,
                'months'       => $monthsPayload
            ];
        }

        $file_name = "项目设备折旧费用统计表_" . date("Y-m-d") . "_". rand(1000,9999);
        $filename =  $file_name . '.' . 'xlsx';

        $bool =  Excel::store(new ProjectDepreciationMultipleSheetExport($finalExportData), "/public/export/{$filename}", null, 'Xlsx', []);
        return [true, $filename];
    }

    // 项目工资分摊
    public function exportItemSalaryFT(array $data, $user)
    {
        $service = new StatisticService();
        list($status, $result) = $service->itemDaySalaryStatistic($data, $user);
        if (!$status) return $result;

        $sourceData = collect($result);
        $groupedByMonth = $sourceData->groupBy('month');
        $monthsData = [];

        foreach ($groupedByMonth as $month => $monthRecords) {
            // A. 获取本月参与的所有唯一项目编码
            $monthProjects = $monthRecords->pluck('item_code')->unique()->sort()->values()->all();

            // B. 按人员分组组织数据
            $groupedByEmployee = $monthRecords->groupBy('employee_id');
            $sheetRows = [];
            $index = 1;

            // 初始化列合计
            $colTotals = [
                'total_salary' => 0,
                'total_min_hours' => 0, // 月总工时合计
                'project_days' => array_fill_keys($monthProjects, 0),
                'project_salary' => array_fill_keys($monthProjects, 0),
                'total_attendance_days' => 0, // 合计工时列的合计
            ];

            foreach ($groupedByEmployee as $employeeId => $records) {
                $first = $records->first();
                $empMonthSalary = (float)$first['total_salary'];
                $empTotalHours = round((float)$first['total_min'] / 60, 2); // 月总工时(小时)

                // 1-4列：序号、姓名、工资、月总工时
                $row = [$index++, $first['employee_title'], $empMonthSalary, $empTotalHours];

                // 5. 动态项目工时列
                $empMap = $records->keyBy('item_code');
                $rowProjectDaysSum = 0;
                foreach ($monthProjects as $code) {
                    $days = $empMap->has($code) ? (float)$empMap->get($code)['days'] : 0;
                    $row[] = $days > 0 ? $days : '';
                    $rowProjectDaysSum += $days;
                    $colTotals['project_days'][$code] += $days;
                }

                // 6. 合计工时列
                $row[] = $rowProjectDaysSum;
                $colTotals['total_attendance_days'] += $rowProjectDaysSum;

                // 7. 动态项目金额列
                foreach ($monthProjects as $code) {
                    $salary = $empMap->has($code) ? (float)$empMap->get($code)['allocated_salary'] : 0;
                    $row[] = $salary > 0 ? $salary : '';
                    $colTotals['project_salary'][$code] += $salary;
                }

                // 8. 总计工资列
                $row[] = $empMonthSalary;

                // 累加合计
                $colTotals['total_salary'] += $empMonthSalary;
                $colTotals['total_min_hours'] += $empTotalHours;

                $sheetRows[] = $row;
            }

            // C. 构造合计行
            $totalRow = ['合计', '', $colTotals['total_salary'], $colTotals['total_min_hours']];
            foreach ($monthProjects as $code) { $totalRow[] = $colTotals['project_days'][$code]; }
            $totalRow[] = $colTotals['total_attendance_days'];
            foreach ($monthProjects as $code) { $totalRow[] = $colTotals['project_salary'][$code]; }
            $totalRow[] = $colTotals['total_salary'];

            $sheetRows[] = $totalRow;

            $monthsData[$month] = [
                'projects' => $monthProjects,
                'data'     => $sheetRows
            ];
        }

        //获取公司基本信息
        $company = EmployeeService::getCompanyDetail($user);

        $file_name = "项目工资分摊统计表_" . date("Y-m-d") . "_". rand(1000,9999);
        $filename =  $file_name . '.xlsx';

        Excel::store(
            new ItemSalaryFTMultipleSheetExport($monthsData, $company['title'] ?? ''),
            "/public/export/{$filename}"
        );

        return [true, $filename];
    }

    // 年度研发支出辅助账汇总表
    public function exportResearchExpense(array $data, $user)
    {
        $service = new StatisticService();
        list($status, $result) = $service->auxiliaryStatistic($data, $user);
        if (!$status) return [false, $result];

        $fee_type_list = $result['fee_type_list'];
        $raw_list = $result['list'];
        if(empty($raw_list)) return [false, '暂无导出数据'];

        // 预读年份和动态表头长度
        $year = date("Y", strtotime($raw_list[0]['voucher_date']));
        $company = Depart::where('id', $user['top_depart_id'])->value('title');
        $dynamicHeaderTitles = array_column($fee_type_list, 'title');
        $dynamicCount = count($dynamicHeaderTitles);
        $totalColCount = 8 + $dynamicCount + 2; // 基础6 + 固定2 + 动态N + 委托2

        $groupedData = [];

        foreach ($raw_list as $row) {
            $sheetKey = $year . $row['code'];

            if (!isset($groupedData[$sheetKey])) {
                $groupedData[$sheetKey] = [
                    'project' => [
                        'code' => $row['code'],
                        'name' => $row['title'],
                    ],
                    'dynamic_headers' => $dynamicHeaderTitles,
                    'data' => [],
                    'year' => $company . $year,
                    'totals' => array_fill(0, $totalColCount, 0)
                ];
                $groupedData[$sheetKey]['totals'][0] = '合计'; // 第一列标识
            }

            // 组织明细行数据 (逻辑不变)
            $excelRow = [
                $row['voucher_date'], $row['voucher_type'], $row['voucher_no'], $row['voucher_remark'],
                (float)$row['voucher_amount'],
                (float)$row['aggregation_amount'],
                ($row['type'] == 1 ? (float)$row['total_amount'] : 0), // 人员
                ($row['type'] == 2 ? (float)$row['total_amount'] : 0)  // 折旧
            ];

            // 动态列填充
            foreach ($fee_type_list as $feeId => $feeItem) {
                $excelRow[] = ($row['type'] == 3 && $row['fee_id'] == $feeId) ? (float)$row['total_amount'] : 0;
            }

            // 委托列
            $excelRow[] = (float)($row['entrust1_amount'] ?? 0);
            $excelRow[] = (float)($row['entrust2_amount'] ?? 0);

            // 【性能优化】：同步累加金额 (从索引 4 开始是金额列)
            for ($i = 4; $i < $totalColCount; $i++) {
                $groupedData[$sheetKey]['totals'][$i] += $excelRow[$i];
            }

            $groupedData[$sheetKey]['data'][] = $excelRow;
        }

        foreach ($groupedData as &$group) {
            $group['data'][] = $group['totals'];
            unset($group['totals']); // 释放内存
        }

        // 8. 导出逻辑保持不变
        $file_name = "年度研发支出辅助账汇总统计表_" . date("Y-m-d") . "_". rand(1000,9999);
        $filename =  $file_name . '.xlsx';

        Excel::store(
            new ResearchExpenseMultipleSheetExport($groupedData),
            "public/export/{$filename}"
        );

        return [true, $filename];
    }

    // 研发支出辅助帐汇总表
    public function exportFormalSummary(array $data, $user)
    {
        $service = new StatisticService();
        // 1. 调用业务逻辑获取原始数据
        list($status, $result) = $service->employeeAttendanceMonthStatistic($data, $user);
        if (!$status) return [false, $result];

        $rawList = $result['list'] ?? [];
        $feeTypes = $result['fee_type_list'] ?? [];

        // 2. 构造动态表头名称
        $dynamicHeaderTitles = array_column($feeTypes, 'title');
        $dynamicHeaders = array_merge(['人员人工费用', '折旧费用'], $dynamicHeaderTitles);

        $items = [];
        foreach ($rawList as $v) {
            $rowValues = [];
            // A. 填充基础固定两项：人工和折旧
            $rowValues[] = (float)($v['employee_salary'] ?? 0);
            $rowValues[] = (float)($v['device_depreciation'] ?? 0);

            // B. 初始化累加变量
            $val7_1 = 0; // 其他相关费用合计 (所有 total_amount 之和)
            $val8_1 = 0; // 委托境内合计 (所有 entrust1_amount 之和)
            $val8_3 = 0; // 委托境外合计 (所有 entrust2_amount 之和)

            // 将当前项目的 fee_list 转为集合以便按 ID 快速查找
            $currentProjectFees = collect($v['fee_list'] ?? [])->keyBy('id');

            // 遍历所有可能的费用类型，确保 values 数组长度与表头一致
            foreach ($feeTypes as $type) {
                $feeData = $currentProjectFees->get($type['id']);
                $amount = (float)($feeData['total_amount'] ?? 0);

                // 填充到动态科目列
                $rowValues[] = $amount;

                // 1. 累加其他相关费用合计 (7.1)
                if(! empty($feeData['is_other']) && $feeData['is_other'] == Fee::IS_OTHER_ONE) $val7_1 += $amount;

                // 2. 累加委托费用 (8.1 和 8.3)
                $val8_1 += (float)($feeData['entrust1_amount'] ?? 0);
                $val8_3 += (float)($feeData['entrust2_amount'] ?? 0);
            }

            $items[] = [
                'no'     => $v['code'] ?? '',
                'name'   => $v['title'] ?? '',
                'status' => (($v['state'] ?? '') == "完结" ? 3 : 2),
                'type'   => $v['expense_type'] ?? '费用化支出',
                'values' => $rowValues,
                'val7_1' => $val7_1,
                'val8_1' => $val8_1,
                'val8_3' => $val8_3,
            ];
        }

        // 年份获取逻辑
        $year = Carbon::parse($data['year'])
            ->setTimezone(config('app.timezone')) // 转换为 Laravel 配置的时区
            ->format('Y');

        //获取公司基本信息
        $company = EmployeeService::getCompanyDetail($user);

        // 3. 组织多 Sheet 格式数据
        $monthsData = [
            $year => [
                'tax_id'          => $company['code'] ?? '',
                'company_name'    => $company['title'] ?? '',
                'items'           => $items,
                'dynamic_headers' => $dynamicHeaders
            ]
        ];

        // 4. 执行 Excel 存储
        $file_name = "研发支出辅助账汇总表_" . date("YmdHis") . "_" . rand(1000, 9999);
        $filename = $file_name . '.xlsx';

        Excel::store(
            new \App\Exports\ResearchExpenseSummaryMultipleSheetExport($monthsData),
            "public/export/{$filename}"
        );

        return [true, $filename];
    }

    // 人员活动考勤占比
    public function exportActivityTimeCard(array $data, $user)
    {
        $service = new StatisticService();
        // 1. 获取统计数据
        list($status, $result) = $service->itemEmployeeSalaryStatistic($data, $user);
        if (!$status) return [false, $result];

        $rawList = collect($result ?? []);

        // 2. 按 项目名称 和 年度 联合分组
        $groupedData = $rawList->groupBy(function ($item) {
            $year = substr($item['month'] ?? date('Y'), 0, 4);
            return $year . "-" . $item['item_title'];
        });

        $allProjectsData = [];

        foreach ($groupedData as $groupKey => $records) {
            list($year, $projectTitle) = explode('-', $groupKey);

            $projectRows = [];
            $groupedByMonth = $records->groupBy('month');

            foreach ($groupedByMonth as $monthStr => $monthRecords) {
                $monthNum = substr($monthStr, 5, 2);
                $monthSubTotal = array_fill(3, 15, 0); // 索引3-17的累加器

                foreach ($monthRecords as $v) {
                    $radioVal = (float)($v['radio'] ?? 0);
                    $row = [
                        (int)$monthNum . '月',         // 0. 月份
                        '技术人员',            // 1. 类别
                        $v['employee_title'] ?? '',   // 2. 姓名
                        (float)$v['total_min'],       // 3. 应出勤
                        (float)$v['work_minutes'],    // 4. 研发出勤
                        ($radioVal * 100) . '%',      // 5. 占比 (字符串)
                        (float)$v['salary'],          // 6. 归集工资
                        (float)$v['social_insurance'],// 7. 归集社保
                        (float)$v['public_housing_fund'], // 8. 归集公积金
                        (float)$v['work_salary'],     // 9. 确定工资
                        (float)$v['work_social_insurance'], // 10. 确定社保
                        (float)$v['work_public_housing_fund'], // 11. 确定公积金
                        (float)$v['work_salary'],     // 12. 研发确定总工资
                        (float)$v['work_social_insurance'], // 13. 研发确定总社保
                        (float)$v['work_public_housing_fund'], // 14. 研发确定总公积金
                        0, 0, 0                       // 15, 16, 17. 调整金额
                    ];
                    $projectRows[] = $row;

                    // 累加月份小计 (跳过索引5的百分比字符串)
                    for ($i = 3; $i <= 14; $i++) {
                        if ($i == 5) continue;
                        $monthSubTotal[$i] += (float)$row[$i];
                    }
                }

                // 构造月份小计行 (重新计算占比)
                $mRadio = $monthSubTotal[3] > 0 ? round($monthSubTotal[4] / $monthSubTotal[3] * 100, 2) . '%' : '0%';
                $projectRows[] = [
                    '小计:', '', '', $monthSubTotal[3], $monthSubTotal[4], $mRadio,
                    $monthSubTotal[6], $monthSubTotal[7], $monthSubTotal[8],
                    $monthSubTotal[9], $monthSubTotal[10], $monthSubTotal[11],
                    $monthSubTotal[12], $monthSubTotal[13], $monthSubTotal[14],
                    0, 0, 0
                ];
            }

            // 计算整年合计 (避开小计行，避开非数字)
            $yearTotal = array_fill(3, 15, 0);
            foreach ($projectRows as $row) {
                if ($row[0] !== '小计:' && $row[0] !== '合计') {
                    for ($i = 3; $i <= 14; $i++) {
                        if ($i == 5) continue;
                        $yearTotal[$i] += (float)$row[$i];
                    }
                }
            }

            $yRadio = $yearTotal[3] > 0 ? round($yearTotal[4] / $yearTotal[3] * 100, 2) . '%' : '0%';
            $projectRows[] = [
                '合计', '', '', $yearTotal[3], $yearTotal[4], $yRadio,
                $yearTotal[6], $yearTotal[7], $yearTotal[8],
                $yearTotal[9], $yearTotal[10], $yearTotal[11],
                $yearTotal[12], $yearTotal[13], $yearTotal[14],
                0, 0, 0
            ];

            $allProjectsData[$groupKey] = [
                'project' => $projectTitle,
                'year'    => $year,
                'data'    => $projectRows
            ];
        }

        $filename = "人员活动考勤占比统计表_" . date("YmdHis") . '.xlsx';
        \Maatwebsite\Excel\Facades\Excel::store(
            new \App\Exports\ManActivityTimeCardMultipleSheetExport($allProjectsData),
            "public/export/{$filename}"
        );

        return [true, $filename];
    }

    //业研究开发活动汇总表
    public function exportEnterpriseRdStatistic(array $data, $user)
    {
        $service = new StatisticService();
        list($status, $result) = $service->enterpriseRdStatistic($data, $user);
        if (!$status) return [false, '获取统计数据失败'];

        $exportData = [];
        $totalBudget = 0;   // 预算总计累加器
        $totalActual = 0;   // 支出总计累加器
        foreach ($result as $row) {
            $budget = (float)($row['budget'] ?? 0);
            $actual = (float)($row['actual_expenditure'] ?? 0);

            $totalBudget = bcadd($totalBudget, $budget, 2);
            $totalActual = bcadd($totalActual, $actual, 2);

            $exportData[] = [
                'activity_name'   => $row['title'] ?? '',           // 研发活动名称
                'time_range'      => $row['time_range'] ?? '',      // 起止时间
                'from'      => $row['from'] ?? '自有技术',      // 起止时间
                'budget'          => $budget,  // 研发预算
                'actual_spending' => $actual, // 实际支出
                'tech_area'       => $row['field'] ?? '',      // 技术领域
                'remark'          => $row['mark'] ?? '',          // 备注
            ];
        }
        $exportData[] = [
            'activity_name'   => '',
            'time_range'      => '',
            'from'      => '',
            'budget'          => $totalBudget,
            'actual_spending' => $totalActual,
            'tech_area'       => '',
            'remark'          => '',
        ];

        $file_name = "企业研究开发活动汇总表_" . date("YmdHis");
        $filename = $file_name . '.xlsx';
        $relative_path = "public/export/{$filename}";

        \Maatwebsite\Excel\Facades\Excel::store(
            new \App\Exports\CompanyRdActivityExport($exportData),
            $relative_path
        );

        return [true, $filename];
    }

    //企业研究开发科技人员情况表
    public function exportEnterpriseRdManStatistic(array $data, $user)
    {
        $service = new StatisticService();
        list($status, $result) = $service->enterpriseRdManStatistic($data, $user);

        if (!$status) return [false, '获取人员统计数据失败'];

        // 2. 映射数据到导出类需要的字段格式
        $exportData = [];
        foreach ($result as $row) {
            $exportData[] = [
                'name'            => $row['title'] ?? '',          // 姓名 (对应模型里的 title 字段)
                'id_card'         => $row['id_card'] ?? '',        // 身份证号码
                'education'       => Employee::Education[$row['education']] ?? "", // 学历
                'major'           => $row['major'] ?? '',          // 专业
                'title_level'     => $row['p_title'] ?? '',    // 职称/职业资格
                'department_job'  => $row['position_new'] ?? '',       // 部门/岗位
                'employment_type' => $row['employee_type_title'] ?? "", // 聘用类型
            ];
        }

        // 3. 定义文件名和路径
        $file_name = "企业研究开发科技人员情况表_" . date("YmdHis");
        $filename = $file_name . '.xlsx';
        $relative_path = "public/export/{$filename}";

        \Maatwebsite\Excel\Facades\Excel::store(
            new \App\Exports\TechnicalStaffExport($exportData),
            $relative_path
        );

        return [true, $filename];
    }

    //项目研发活动人员情况表
    public function exportEnterpriseRdItemStatistic(array $data, $user)
    {
        $service = new StatisticService();
        list($status, $result) = $service->enterpriseRdItemStatistic($data, $user);

        if (!$status) return [false, '获取统计数据失败'];

        // 2. 映射数据到导出类需要的字段格式
        $exportData = [];
        foreach ($result as $row) {
            $exportData[] = [
                'title'            => $row['title'] ?? '',          // 姓名 (对应模型里的 title 字段)
                'education'       => Employee::Education[$row['education']] ?? "", // 学历
                'major'           => $row['major'] ?? '',          // 专业
                'p_title'     => $row['p_title'] ?? '',    // 职称/职业资格
                'item_title'  => $row['item_title'] ?? '',       // 项目
                'item_role'  => $row['item_role'] ?? '',       // 项目角色
                'depart_title'  => $row['depart_title'] ?? '',       // 部门
                'duty' => $row['duty'] ?? "", // 职责
            ];
        }

        // 3. 定义文件名和路径
        $file_name = "项目研发活动人员情况表_" . date("YmdHis");
        $filename = $file_name . '.xlsx';
        $relative_path = "public/export/{$filename}";

        \Maatwebsite\Excel\Facades\Excel::store(
            new \App\Exports\ProjectStaffExport($exportData),
            $relative_path
        );

        return [true, $filename];
    }

    public function saveExportData($data, $headers, $type = 'default',$file_name = ''){
        if(empty($file_name)) $file_name = self::$filename . "_". date("Y-m-d") . "_". rand(1000,9999);
        $filename =  $file_name . '.' . 'xlsx';
        $bool = Excel::store(new ExportOrder($data,$type,$headers),"/public/export/{$filename}", null, 'Xlsx', []);
        return $filename;
    }

    public function saveExportData2($data,$type = 1,$column,$timeRow, $file_name = ''){
        if(empty($file_name)) $file_name = self::$filename . "_". date("Y-m-d") . "_". rand(1000,9999);
        $filename =  $file_name . '.' . 'xlsx';
        \Maatwebsite\Excel\Facades\Excel::store(new MultiSheetExport($data, $type,$column,$timeRow),"/public/export/{$filename}", null, 'Xlsx', []);

        return $filename;
    }
}