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\Model\Depart;
use App\Model\PLeaveOverOrder;
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['data'], $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 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;

        $file_name = "项目工资统计表_" . date("Y-m-d") . "_". rand(1000,9999);
        $filename =  $file_name . '.' . 'xlsx';
        $bool = Excel::store(new ItemSalarySheetExport($projects, $exportData, Depart::where('id', $user['top_depart_id'])->value('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['data']);

        // 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
            ];
        }

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

        Excel::store(
            new ItemSalaryFTMultipleSheetExport($monthsData, Depart::where('id', $user['top_depart_id'])->value('title')),
            "/public/export/{$filename}"
        );

        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;
    }
}