hqyt/app/Service/ExportFileService.php

<?php

namespace App\Service;

use App\Exports\ExportOrder;
use App\Exports\MultiSheetExport;
use App\Model\CalendarDetails;
use App\Model\Device;
use App\Model\Employee;
use App\Model\EmployeeRole;
use App\Model\Item;
use App\Model\ItemDetails;
use App\Model\RevenueCost;
use Maatwebsite\Excel\Facades\Excel;

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

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

    public $special_func = [
        0 => 'five',
        1 => 'six',
    ];

    public function exportAll($data,$user){
        if(empty($data['menu_id'])) return [false, '菜单ID不能为空'];
        list($function, $name) = EmployeeService::fillMenu2($data['menu_id'], $user);
        if (empty($function) || ! method_exists(self::class, $function)) return [false, "导出方法不存在,请联系开发"];
        self::$filename = $name;

        //特殊的导出
        if(in_array($function, $this->special_func)){
            list($status, $return) = $this->$function($data,$user);
            if(! $status) return [false, $return];

            return [true, $return];
        }

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

    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 one($ergs, $user){
        $service = new EmployeeService();
        $model = $service->employeeCommon($ergs, $user);

        // 导出数据
        $return = [];

        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');
        $model->chunk(500,function ($data) use(&$return,$column,$service){
            $data = $data->toArray();
            $list['data'] = $data;
            $list = $service->organizationEmployeeData($list);

            $this->fillData($list['data'], $column, $return);
        });

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

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

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

        $service = new ItemService();
        $model = $service->itemCommon($ergs, $user);
        $model->chunk(500,function ($data) use(&$return,$column,$service){
            $data = $data->toArray();

            $list['data'] = $data;
            $list = $service->fillData($list);

            $this->fillData($list['data'], $column, $return);
        });

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

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

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

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

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

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

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

        $service = new RDService();
        $model = $service->rdCommon($ergs, $user);

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

            //订单数据
            $list = $service->fillData($list);

            $this->fillData($list['data'], $column, $return);
        });

        //合计
        $total = $this->countTotal($return, $header_default);
        //填充合计
        $this->fillTotalData($total, $header_default, $return);

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

    public function five($ergs, $user) {
        $header_default = $user['e_header_default'];
        $column = array_column($header_default, 'key'); // 对应数据的键名
        $header = array_column($header_default, 'value'); // Excel 展现的中文表头

        array_unshift($column, 'index_no'); // 内部识别用的 key
        array_unshift($header, '序号');     // Excel 显示的表头

        $service = new StatisticsService();
        list($status, $res) = $service->statisticsEmployeeExportCommon($ergs, $user);
        if (!$status) return [false, $res];
        list($model, $timeRange, $totalWorkDays) = $res;

        $rawData = $model->get()->toArray();

        // 获取员工名单（避免在循环里重复查询）
        $employee_id = EmployeeRole::where('del_time', 0)
            ->where('role_id', 87)
            ->pluck('employee_id')
            ->toArray();
        $employee = Employee::where('del_time', 0)
            ->whereIn('id', $employee_id)
            ->where('id', '<>', Employee::SPECIAL_ADMIN)
            ->select('id', 'emp_name as name')
            ->get()->toArray();
        $item_total = Item::where('del_time', 0)->select('code', 'id','start_time','end_time')->get()->toArray();

        // 生成起止时间行
        $timeRow = $this->getItemTimeRow($column, $item_total);

        // 最终交给 MultiSheetExport 的数组
        $finalExportData = [];

        $month = date("Y.m", $timeRange[0]);
        // --- 1. 处理全月汇总 Sheet ---
        $summaryData = $service->statisticsEmployeeFillExportData($rawData, $timeRange, $totalWorkDays, $employee,$item_total);
        // 格式化数据：只取 header 中定义的列，并按顺序排列
        $finalExportData[$month . '合计'] = $this->formatSheetData($header, $column, $summaryData);

        // --- 2. 处理每日明细 Sheets ---
        $dataByDay = [];
        foreach ($rawData as $row) {
            $dateKey = date('Y-m-d', $row['order_time']);
            $dataByDay[$dateKey][] = $row;
        }

        foreach ($dataByDay as $date => $dayRows) {
            $dayTimestamp = strtotime($date);
            // 这里调用你的填充逻辑
            $dayResult = $service->statisticsEmployeeFillExportData($dayRows, [$dayTimestamp], 1, $employee, $item_total);
            // 格式化并存入结果集
            $finalExportData[date('Y.m.d',$dayTimestamp)] = $this->formatSheetData($header, $column, $dayResult);
        }

        // 3. 调用保存方法
        return [true, $this->saveExportData2($finalExportData,2,$column,$timeRow)];
    }

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

        array_unshift($column, 'index_no'); // 内部识别用的 key
        array_unshift($header, '序号');     // Excel 显示的表头

        $service = new StatisticsService();
        list($status, $result) = $service->statisticsDeviceExportCommon($ergs, $user);
        if(! $status) return [false, $result];
        list($model, $timeRange) = $result;

        $rawData = $model->get()->toArray();

        $device = Device::where('del_time',0)
            ->where('is_use',Device::type_one)
            ->select('id','title as name','code','type','type_2','power','in_time','number')
            ->get()->toArray();

        $item_total = Item::where('del_time', 0)->where('is_use', 1)->select('code', 'id','start_time','end_time')->get()->toArray();

        $time_array = ItemDetails::where('del_time',0)
            ->pluck('total_hours', 'device_id')
            ->all();
        // 生成起止时间行
        $timeRow = $this->getItemTimeRow($column, $item_total);

        // 最终交给 MultiSheetExport 的数组
        $finalExportData = [];

        $month = date("Y.m", $timeRange[0]);
        // --- 1. 处理全月汇总 Sheet ---
        $summaryData = $service->statisticsDeviceFillExportData($rawData, $timeRange, $device, $item_total,true,$time_array);
        // 格式化数据：只取 header 中定义的列，并按顺序排列
        $finalExportData[$month . '合计'] = $this->formatSheetData($header, $column, $summaryData);

        // --- 2. 处理每日明细 Sheets ---
        $dataByDay = [];
        foreach ($rawData as $row) {
            $dateKey = date('Y-m-d', $row['order_time']);
            $dataByDay[$dateKey][] = $row;
        }

        foreach ($dataByDay as $date => $dayRows) {
            $dayTimestamp = strtotime($date);
            // 这里调用你的填充逻辑
            $dayResult = $service->statisticsDeviceFillExportData($dayRows, [$dayTimestamp], $device, $item_total,false, $time_array);
            // 格式化并存入结果集
            $finalExportData[date('Y.m.d',$dayTimestamp)] = $this->formatSheetData($header, $column, $dayResult);
        }

        // 3. 调用保存方法
        return [true, $this->saveExportData2($finalExportData, 2,$column,$timeRow)];
    }

    private function getItemTimeRow($column, $item_total) {
        $item_time_map = [];
        foreach ($item_total as $it) {
            $start = !empty($it['start_time']) ? date('Y.m', (int)$it['start_time']) : '';
            $end = !empty($it['end_time']) ? date('Y.m', (int)$it['end_time']) : '';
            $item_time_map[$it['id']] = ($start || $end) ? "{$start}-{$end}" : '';
        }

        $timeRow = [];
        foreach ($column as $key) {
            if (strpos($key, 'extra_') === 0) {
                $itemId = str_replace('extra_', '', $key);
                $timeRow[$key] = $item_time_map[$itemId] ?? '';
            } else {
                $timeRow[$key] = ''; // 其他字段（序号、设备名等）全为空
            }
        }
        return $timeRow;
    }

    /**
     * 辅助方法：将数据按表头顺序对齐，并把表头插入到第一行
     */
    private function formatSheetData($header, $column, $dataRows) {
        $sheet = [];
        $sheet[] = $header; // 第一行放入表头文字

        $count = 1; // 初始化序号计数器
        foreach ($dataRows as $row) {
            $temp = [];
            foreach ($column as $key) {
                if ($key === 'index_no') {
                    $temp[] = $count; // 填充序号
                } else {
                    $temp[] = $row[$key] ?? '';
                }
            }
            $sheet[] = $temp;
            $count++; // 序号自增
        }
        return $sheet;
    }

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