youfeng/app/Service/ExportFileService.php

<?php

namespace App\Service;

use App\Exports\ExportOrder;
use App\Exports\ExportOrder2;
use App\Exports\MultiSheetExport;
use App\Model\ItemReportRoad;
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 => 'eve',
        1 => 'twl',
    ];

    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 ProductService();
        $model = $service->productCommon($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){
            $data = $data->toArray();
            $this->fillData($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 FreightService();
        $model = $service->freightCommon($ergs, $user);
        $model->chunk(500,function ($data) use(&$return,$column){
            $data = $data->toArray();
            $this->fillData($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 StatisticsService();
        list($status, $model) = $service->statisticsRevenueCostCommon($ergs, $user);
        if(! $status) return [false, $model];
        $model->chunk(500,function ($data) use(&$return, $service, $column){
            $data = $data->toArray();
            $list['data'] = $data;

            //订单数据
            $list = $service->statisticsRevenueCostFillData($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 four($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');

        $ergs['order_type'] = RevenueCost::ORDER_ONE;
        $service = new StatisticsService();
        list($status, $model) = $service->statisticsRevenueCostOneAndTwoCommon($ergs, $user);
        if(! $status) return [false, $model];

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

            //订单数据
            $list = $service->statisticsRevenueCostOneAndTwoFillData($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){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');

        $ergs['order_type'] = RevenueCost::ORDER_TWO;
        $service = new StatisticsService();
        list($status, $model) = $service->statisticsRevenueCostOneAndTwoCommon($ergs, $user);
        if(! $status) return [false, $model];

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

            //订单数据
            $list = $service->statisticsRevenueCostOneAndTwoFillData($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 six($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');

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

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

            //订单数据
            $list = $service->statisticsRevenueCostThreeFillData($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 seven($ergs,$user){
        // 导出数据
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');
        $service = new GiveOutService();
        $model = $service->giveOutCommon($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 eight($ergs,$user){
        //导出
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default, 'key');
        $header = array_column($header_default, 'value');

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

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

            //订单数据
            $list = $service->statisticsProfitFillData($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 nine($ergs,$user){
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default, 'key');
        $header = array_column($header_default, 'value');

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

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

            //订单数据
            $list = $service->statisticsEmployeeSalaryFillData($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 ten11($ergs,$user){
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default, 'key');
        $header = array_column($header_default, 'value');

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

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

            //订单数据
            $list = $service->statisticsFreightFeeFillData($list, $user,$ergs);

            $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 ten($ergs,$user){
        $return = [];
        $header_default = $user['e_header_default'];
        $column = array_column($header_default, 'key');
        $header = array_column($header_default, 'value');

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

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

            $order_time = array_column($data,'order_time');
            $ergs = array_merge($ergs, ['order_time' => $order_time]);

            $list = $service->statisticsFreightFeeDetailsForExport($ergs, $user);
            $return = array_merge_recursive($return, $list);
        });

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

    //        $data = [];
//        for ($i = 0; $i < 22; $i++) {
//            $row = [];
//            foreach ($title as $value) {
//                $row[] = rand(1000, 5000);   // 当月
//                $row[] = rand(10000, 20000); // 累计
//            }
//            $data[] = $row;
//        }dd($data);
//
//        dd($data, $title);
    public function eve($ergs,$user){
        $service = new StatisticsService();
        $result = $service->getChannelReport($ergs);
        if(empty($result)) return [false, '暂无数据'];

        $title = array_column($result,'channel');
        if(empty($title)) return [false, '暂无数据'];

        // 2. 字段顺序（22 个）
        $fieldOrder = [
            'receipt_amount',
            'cost',
            'profit',
            'settle_amount',
            'gl_amount',
            'wl_amount',
            'ht_amount',
            'zk_amount',
            'cx_amount',
            'tg_amount',
            'cl_amount',
            'kq_amount',
            'zp_amount',
            'gg_amount',
            'kd_amount',
            'xsqt_amount',
            'ry_amount',
            'sb_amount',
            'amount1',
            'sj_amount',
            'amount2',
            'total'
        ];

        // 3. 构建 channel => data 的映射，方便查找
        $channelDataMap = [];
        foreach ($result as $item) {
            $channelDataMap[$item['channel']] = [
                'monthly' => $item['monthly'],
                'cumulative' => $item['cumulative']
            ];
        }

        // 4. 按字段逐行构建 $data
        $data = [];
        foreach ($fieldOrder as $field) {
            $row = [];
            $m_fin = $c_fin = 0;
            foreach ($title as $channel) {
                $m = $channelDataMap[$channel]['monthly'][$field] ?? '0.00000';
                $c = $channelDataMap[$channel]['cumulative'][$field] ?? '0.00000';
                $row[] = $m;
                $row[] = $c;
                $m_fin = bcadd($m_fin, $m,2);
                $c_fin = bcadd($c_fin, $c,2);
            }
            $row[] = $m_fin;
            $row[] = $c_fin;
            $data[] = $row;
        }
        $title[] = '合计';

        $export = new ExportOrder2($data,'jc',[]);
        $export->mergeStart = 'D';

        $numChannels = count($title);
        $totalColumns = $numChannels * 2;
        $endColumnIndex = 3 + $totalColumns - 1; // D is index 3
        $export->mergeEnd = $this->numberToExcelColumn($endColumnIndex);
//        $export->mergeEnd = 'AC';
        $export->mergeTitles = $title;

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

        return [true, $filename];
    }

    public function twl($ergs,$user){
        $service = new StatisticsService();
        $result = $service->getChannelReportRoad($ergs);
        if(empty($result)) return [false, '暂无数据'];

        $title_type = array_column($result,'channel');
        if(empty($title_type)) return [false, '暂无数据'];
        $title = [];
        foreach ($title_type as $value){
            $title[] = ItemReportRoad::$order_type[$value];
        }

        // 2. 字段顺序（22 个）
        $fieldOrder = [
            'receipt_amount',
            'cost',
            'profit',
            'settle_amount',
            'gl_amount',
            'wl_amount',
            'ht_amount',
            'zk_amount',
            'cx_amount',
            'tg_amount',
            'cl_amount',
            'kq_amount',
            'zp_amount',
            'gg_amount',
            'kd_amount',
            'xsqt_amount',
            'ry_amount',
            'sb_amount',
            'amount1',
            'sj_amount',
            'amount2',
            'total'
        ];

        // 3. 构建 channel => data 的映射，方便查找
        $channelDataMap = [];
        foreach ($result as $item) {
            $channelDataMap[$item['channel']] = [
                'monthly' => $item['monthly'],
                'cumulative' => $item['cumulative']
            ];
        }

        // 4. 按字段逐行构建 $data
        $data = [];
        foreach ($fieldOrder as $field) {
            $row = [];
            $m_fin = $c_fin = 0;
            foreach ($title_type as $channel) {
                $m = $channelDataMap[$channel]['monthly'][$field] ?? '0.00000';
                $c = $channelDataMap[$channel]['cumulative'][$field] ?? '0.00000';
                $row[] = $m;
                $row[] = $c;
                $m_fin = bcadd($m_fin, $m,2);
                $c_fin = bcadd($c_fin, $c,2);
            }
            $row[] = $m_fin;
            $row[] = $c_fin;
            $data[] = $row;
        }
        $title[] = '合计';

        $export = new ExportOrder2($data,'jc2',[]);
        $export->mergeStart = 'D';
        $numChannels = count($title);
        $totalColumns = $numChannels * 2;
        $endColumnIndex = 3 + $totalColumns - 1; // D is index 3
        $export->mergeEnd = $this->numberToExcelColumn($endColumnIndex);
        $export->mergeTitles = $title;

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

        return [true, $filename];
    }

    // 辅助方法（可放在同一个类中）
    private function numberToExcelColumn(int $num): string
    {
        $column = '';
        while ($num >= 0) {
            $remainder = $num % 26;
            $column = chr(65 + $remainder) . $column;
            $num = floor($num / 26) - 1;
        }
        return $column;
    }

    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,$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),"/public/export/{$filename}", null, 'Xlsx', []);

        return $filename;
    }
}