drb/app/Service/ExportFileService.php

<?php

namespace App\Service;

use App\Exports\ExportOrder;
use App\Model\Freight;
use App\Model\GiveOut;
use App\Model\Product;
use App\Model\RevenueCost;
use App\Model\RevenueCostTotal;
use App\Model\SalaryEmployee;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;

class ExportFileService extends Service
{
    //导出文件
    const type_one = 1;
    const type_two = 2;
    const type_three = 3;
    const type_four = 4;
    const type_five = 5;
    const type_six = 6;
    const type_seven = 7;
    const type_eight = 8;
    const type_nine = 9;
    const type_ten = 10;
    const type_eve = 11;
    const type_twl = 12;

    //导出文件方法
    protected static $fuc = [
        self::type_one => 'one',
        self::type_two => 'two',
        self::type_three => 'three',
        self::type_four => 'four',
        self::type_five => 'five',
        self::type_six => 'six',
        self::type_seven => 'seven',
        self::type_eight => 'eight',
        self::type_nine => 'nine',
        self::type_ten => 'ten',
        self::type_eve => 'eve',
        self::type_twl => 'twl',
    ];

    protected static $fuc_name = [
        self::type_one => '存货',
        self::type_two => '运费设置',
        self::type_three => '收入成本统计表',
        self::type_four => '销货单统计表',
        self::type_five => '销售发票统计表',
        self::type_six => '回款单统计表',
        self::type_seven => '发放统计表',
        self::type_eight => '利润分配统计表',
        self::type_nine => '业务员工资统计表',
        self::type_ten => '客户',
        self::type_eve => '现存量',
        self::type_twl => '库存台账',
    ];

    public static $filename = "";

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

    public function exportAll($data,$user){
        if(empty($data['type']) || ! isset(self::$fuc[$data['type']])) return [false,'导出文件类型错误或者不存在'];
        self::$filename = self::$fuc_name[$data['type']] ?? "";

        $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,'请选择导出数据'];
        }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条'];
            list($status,$id) = $this->getListForSearch($data, $user);
            if(! $status) return [false, $id];
            $data['id'] = $id;
        }

        $function = self::$fuc[$data['type']];
        $return = $this->$function($data,$user);

        return [true, $return];
    }

//    private function fillData($data, $column, &$return){
//        foreach ($data as $value) {
//            $tmp = [];
//            foreach ($column as $c_v){
//                $tmp[$c_v] = $value[$c_v] ?? "";
//            }
//            $return[] = $tmp;
//        }
//    }

    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){
        $id = $ergs['id'];

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

        $header_default = config("header.62") ?? [];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');
        DB::table('product')
            ->where('del_time',0)
            ->whereIn('id', $id)
            ->select(Product::$field)
            ->orderBy('id','desc')
            ->chunkById(500,function ($data) use(&$return,$column){
                $data = Collect($data)->map(function ($object) {
                    return (array)$object;
                })->toArray();

                $this->fillData($data, $column, $return);
            });

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

    public function two($ergs,$user){
        $id = $ergs['id'];

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

        $header_default = config("header.66") ?? [];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');
        DB::table('freight')
            ->where('del_time',0)
            ->whereIn('id', $id)
            ->select(Freight::$field)
            ->orderBy('id','desc')
            ->chunkById(500,function ($data) use(&$return,$column){
                $data = Collect($data)->map(function ($object) {
                    return (array)$object;
                })->toArray();

                $this->fillData($data, $column, $return);
            });

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

    public function three($ergs,$user){
        $id = $ergs['id'];

        // 导出数据
        $return = [];
        $service = new StatisticsService();
        $header_default = config("header.68") ?? [];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');
        DB::table('revenue_cost_total')
            ->where('del_time',0)
            ->whereIn('id', $id)
            ->select(RevenueCostTotal::$field)
            ->orderBy('id','desc')
            ->chunkById(500,function ($data) use(&$return, $service, $column){
                $data = Collect($data)->map(function ($object) {
                    return (array)$object;
                })->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 $this->saveExportData($return,$header);
    }

    public function four($ergs,$user){
        $id = $ergs['id'];

        // 导出数据
        $return = [];
        $service = new StatisticsService();
        $header_default = config("header.69") ?? [];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');
        DB::table('revenue_cost')
            ->where('del_time',0)
            ->whereIn('id', $id)
            ->select(RevenueCost::$field_xhd)
            ->orderBy('id','desc')
            ->chunkById(500,function ($data) use(&$return, $column, $service){
                $data = Collect($data)->map(function ($object) {
                    return (array)$object;
                })->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 $this->saveExportData($return,$header);
    }

    public function five($ergs,$user){
        $id = $ergs['id'];

        // 导出数据
        $return = [];
        $service = new StatisticsService();
        $header_default = config("header.70") ?? [];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');
        DB::table('revenue_cost')
            ->where('del_time',0)
            ->whereIn('id', $id)
            ->select(RevenueCost::$field_xsfp)
            ->orderBy('id','desc')
            ->chunkById(500,function ($data) use(&$return, $column, $service){
                $data = Collect($data)->map(function ($object) {
                    return (array)$object;
                })->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 $this->saveExportData($return,$header);
    }

    public function six($ergs,$user){
        $id = $ergs['id'];

        $field = RevenueCost::$field_hkd_main;
        $field[] = DB::raw('sum(payment_amount) as payment_amount');
        $field[] = DB::raw('sum(price_4_total) as price_4_total');
        $field[] = DB::raw('sum(profit) as profit');

        // 导出数据
        $return = [];
        $service = new StatisticsService();
        $header_default = config("header.71") ?? [];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');
        DB::table('revenue_cost')
            ->where('del_time',0)
            ->whereIn('order_id', $id)
            ->where('order_type',RevenueCost::ORDER_THREE)
            ->select($field)
            ->groupby('order_id')
            ->orderBy('id','desc')
            ->chunkById(500,function ($data) use(&$return, $column, $service){
                $data = Collect($data)->map(function ($object) {
                    return (array)$object;
                })->toArray();
                $list['data'] = $data;

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

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

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

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

    public function seven($ergs,$user){
        $id = $ergs['id'];

        // 导出数据
        $return = [];
        $header_default = config("header.72") ?? [];
        $column = array_column($header_default,'key');
        $header = array_column($header_default,'value');
        DB::table('give_out')
            ->where('del_time',0)
            ->whereIn('id', $id)
            ->select(GiveOut::$field)
            ->orderBy('id','desc')
            ->chunkById(500,function ($data) use(&$return, $column){
                $data = Collect($data)->map(function ($object) {
                    return (array)$object;
                })->toArray();
                $list['data'] = $data;

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

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

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

    public function eight($ergs,$user){
        $id = $ergs['id'];

        $field = RevenueCost::$field_hkd_profit_main;
        $field[] = DB::raw('COALESCE(SUM(rc.payment_amount), 0) AS payment_amount');
        $type = RevenueCost::ORDER_THREE;

        $return = [];
        $header_default = config("header.74") ?? [];
        $column = array_column($header_default, 'key');
        $header = array_column($header_default, 'value');
        DB::table('employee_index')
            ->where('employee_index.del_time', 0)
            ->whereIn('employee_index.id', $id)
            ->leftJoin(DB::raw('revenue_cost as rc'), function ($join) use ($type) {
                $join->on('rc.employee_id_2', '=', 'employee_index.employee_id')
                    ->where('rc.del_time', 0)
                    ->where('rc.order_type', $type)
                    ->whereRaw('rc.order_time >= employee_index.start_time')
                    ->whereRaw('rc.order_time <= employee_index.end_time');
            })
            ->select($field)
            ->groupBy('employee_index.employee_id', 'employee_index.start_time', 'employee_index.end_time')
            ->orderBy('employee_index.end_time','desc')
            ->chunkById(500, function ($data) use (&$return, $column) {
                $data = Collect($data)->map(function ($object) {
                    return (array)$object;
                })->toArray();
                $list['data'] = $data;

                //订单数据
                $service = new StatisticsService();
                $list = $service->statisticsProfitFillData($list);

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

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

    public function nine($ergs,$user){
        $id = $ergs['id'];

        $field = SalaryEmployee::$field;

        $return = [];
        $header_default = config("header.73") ?? [];
        $column = array_column($header_default, 'key');
        $header = array_column($header_default, 'value');
        DB::table('salary_employee')
            ->where('del_time', 0)
            ->whereIn('id', $id)
            ->select($field)
            ->orderBy('order_time','desc')
            ->chunkById(500, function ($data) use (&$return, $column) {
                $data = Collect($data)->map(function ($object) {
                    return (array)$object;
                })->toArray();
                $list['data'] = $data;

                //订单数据
                $service = new StatisticsService();
                $list = $service->statisticsEmployeeSalaryFillData($list);

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

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

    public function getListForSearch($ergs, $user){
        $data = $ergs['order_search'];
        $id = [];
        if($ergs['type'] == self::type_one){
            $service = new ProductService();
            $model = $service->productCommon($data, $user, ['id']);
            $return = $this->limitData($model,'',$data);
            $id = array_column($return,'id');
        }elseif ($ergs['type'] == self::type_two){
            $service = new FreightService();
            $model = $service->freightCommon($data, $user, ['id']);
            $return = $this->limitData($model,'',$data);
            $id = array_column($return,'id');
        }elseif ($ergs['type'] == self::type_three){
            $service = new StatisticsService();
            list($status, $model) = $service->statisticsRevenueCostCommon($data, $user, ['id']);
            if(! $status) return [false, $model];
            $return = $this->limitData($model,'',$data);
            $id = array_column($return,'id');
        }elseif ($ergs['type'] == self::type_four || $ergs['type'] == self::type_five){
            if(empty($data['order_type'])){
                if($ergs['type'] == self::type_four){
                    $data['order_type'] = RevenueCost::ORDER_ONE;
                }else{
                    $data['order_type'] = RevenueCost::ORDER_TWO;
                }
            }
            $service = new StatisticsService();
            list($status, $model) = $service->statisticsRevenueCostOneAndTwoCommon($data, $user, ['id']);
            if(! $status) return [false, $model];
            $return = $this->limitData($model,'',$data);
            $id = array_column($return,'id');
        }elseif ($ergs['type'] == self::type_six){
            $service = new StatisticsService();
            list($status, $model) = $service->statisticsRevenueCostThreeCommon($data, $user, ['order_id']);
            if(! $status) return [false, $model];
            $return = $this->limitData($model,'',$data);
            $id = array_column($return,'order_id');
        }elseif ($ergs['type'] == self::type_seven){
            $service = new GiveOutService();
            $model = $service->giveOutCommon($data, $user, ['id']);
            $return = $this->limitData($model,'',$data);
            $id = array_column($return,'id');
        }elseif ($ergs['type'] == self::type_eight){
            $service = new StatisticsService();
            list($status,$model) = $service->statisticsProfitCommon($data, $user, ['employee_index.id']);
            if(! $status) return [false, $model];
            $return = $this->limitData($model,'',$data);
            $id = array_column($return,'id');
        }

        return [true, $id];
    }

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