drb/app/Service/ImportService.php

<?php

namespace App\Service;

use App\Exports\TableHeadExport;
use App\Import\Import;
use App\Import\ImportAll;
use App\Model\BasicType;
use App\Model\Customer;
use App\Model\Employee;
use App\Model\Product;
use App\Model\ProductCategory;
use App\Model\ProductPriceDetail;
use App\Model\SalesOrder;
use App\Model\SalesOrderProductInfo;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ImportService extends Service
{

    public static $type = [
        'product', //产品
        'customer', //客户
        'salesOnline', //线上订单
    ];

    //写活的导入------------------- 暂时不好用
    //导入入口
    public function import($data,$user){
        if(empty($data['type'])) return [false,'缺少导入类型，导入失败'];
        if(! in_array($data['type'],self::$type)) return [false,'导入类型不存在，导入失败'];
        if(empty($data['file'])) return [false,'导入文件不能为空'];

        //导入的数据并且校验写入
        list($status,$msg) = $this->importMain($data,$user);
        if(! $status) return [false, $msg];

        return [true, ''];
    }

    //主方法
    public function importMain($data,$user){
        //获取配置文件
        $config = "excel." . $data['type'];
        $config_array = config($config) ?? [];
        if(empty($config_array)) return [false, '配置文件不存在'];

        //(特殊 额外的表头数据)
        $config_array = $this->getTableTitle($config_array,$user,$data);

        //获取合并单元格范围
        $uploadedFile = $_FILES['file']['tmp_name']; // 获取上传文件的临时路径
        $spreadsheet = IOFactory::load($uploadedFile); // 加载上传的 Excel 文件
        $worksheet = $spreadsheet->getActiveSheet(); // 获取第一个工作表
        $mergedCells = $worksheet->getMergeCells(); // 获取单元格合并范围

        // 需要导入的公用数据
        $msg['user_id'] = $user['id'];
        $msg['depart_id'] = $this->getDepart($user);
        $msg['top_depart_id'] = $user['depart_map'][$msg['depart_id']] ?? 0;

        //导入
        $import = new Import();
        $import->setConfig($config_array, $mergedCells,$msg);
        \Maatwebsite\Excel\Facades\Excel::import($import,$data['file']);

        //异常提示报错
        if($import->getMsg()) return [false, $import->getMsg()];

        return [true, ''];
    }

    //表头入口
    public function getTableTitle($config_array,$user,$data){
        if(! empty($config_array['dynamics_field'])){
            $func = $config_array['dynamics_field']['func'];
            return $this->$func($config_array,$user,$data);
        }

        return $config_array;
    }

    //产品导入的额外表头
    public function productTitle($config_array,$user,$data){
        $model = BasicType::TopClear($user,$data);
        $result = $model->whereRaw('type = 22 And del_time = 0')->get()->toArray();
        if(! empty($result)){
            foreach ($result as $value){
                $config_array['field'][$value['title']] = [
                    "key" => $config_array['dynamics_field']['name'],
                    "key_array" => [
                        "basic_type_id" => $value['id'],
                        "price" => 0,
                    ],
                    "rule" => "",
                    "other_rule" => "is_numeric",
                    "multiple" => true,
                    "map" => [
                        $value['title'] => "price",
                    ],
                ];
            }
        }

        return $config_array;
    }

    //产品导入的额外数据
    public function fillInsertProductData($time){
        $last_insert_data = Product::where('crt_time',$time)
            ->where('del_time',0)
            ->select("id","product_category_id")
            ->get()->toArray();
        if(empty($last_insert_data)) return;

        $list = ProductCategory::where('del_time',0)
            ->select('id','parent_id')
            ->get()->toArray();

        foreach ($last_insert_data as $value){
            $parentsId = $this->findParentIds($value['product_category_id'], $list);
            array_unshift($parentsId, $value['product_category_id']);
            $result = array_reverse($parentsId);
            Product::where('id',$value['id'])->update([
                'product_category' => json_encode($result)
            ]);
        }
    }
    //写活的导入------------------- 暂时不好用

    //写死的导入
    public function getTableTitleXls($data,$user){
        if(empty($data['type'])) return [false,'缺少类型'];
        if(! in_array($data['type'],self::$type)) return [false,'类型不存在'];

        //获取配置文件
        $fuc = $data['type'];
        list($status,$msg,$filename) = $this->$fuc($data,$user);
        if(!$status) return [false, $msg];

        $headers = array_column($msg,'value');
        Excel::store(new TableHeadExport([], $headers),"/public/export/{$filename}", null, 'Xlsx', []);
        return [true, ['file' => $filename]];
    }

    private function customer($data,$user){
        //生成下载文件
        $filename =  "客户模板_" . time() . '.' . 'xlsx';

        //获取配置文件
        $config = "excel.customerTable";
        $config_array = config($config) ?? [];
        if(empty($config_array)) return [false, '配置文件不存在',''];
        return [true, $config_array,$filename];
    }

    private function product($data,$user){
        //获取配置文件
        $config = "excel.productTable";
        $config_array = config($config) ?? [];
        if(empty($config_array)) return [false, '配置文件不存在', ''];

        $model = BasicType::TopClear($user,$data);
        $result = $model->whereRaw('type = 22 And del_time = 0')->get()->toArray();

        if(! empty($result)){
            foreach ($result as $value){
                $config_array[] = [
                    'key' => 'table_id.' . $value['id'],
                    'value' => $value['title'],
                ];
            }
        }
        //生成下载文件
        $filename =  "产品模板_" . time() . '.' . 'xlsx';

        return [true, $config_array,$filename];
    }

    private function salesOnline($data,$user){
        //生成下载文件
        $filename =  "线上订单模板_" . time() . '.' . 'xlsx';

        //获取配置文件
        $config = "excel.salesOnlineTable";
        $config_array = config($config) ?? [];
        if(empty($config_array)) return [false, '配置文件不存在',''];
        return [true, $config_array,$filename];
    }

    //导入入口
    public function importAll($data,$user){
        if(empty($data['type'])) return [false,'缺少导入类型，导入失败'];
        if(! in_array($data['type'],self::$type)) return [false,'导入类型不存在，导入失败'];
        if(empty($data['file'])) return [false,'导入文件不能为空'];

        $import = new ImportAll();
        //设置导入人id
        $import->setCrt($user['id']);
        $import->setUser($user);
        $import->setType($data['type']);

        //导入
        \Maatwebsite\Excel\Facades\Excel::import($import,$data['file']);
        if($import->getMsg()) return [false, $import->getMsg()];
        return [true, ''];
    }

    public function customerImport($array, $user){
        $head = $user['head']['id'] ?? 0;

        // 去除表头
        unset($array[0]);
        if(empty($array)) return [false, '导入数据不能为空'];

        //第一次表格数据校验 非空  已经过滤数据
        $array_clean = [];
        foreach ($array as $key => $value){
            $rowData = array_filter($value);
            if (empty($rowData)) {
                unset($array[$key]);
            } elseif(empty($value[0]) || empty($value[1])) {
                return [false, '带*号的字段项必填'];
            }else{
                foreach ($value as $k => $v){
                    $value[$k] = trim($v);
                }
                if(! isset(Customer::dk[$value[0]]))  return [false, '客户模板填写错误'];
                $value[0] = Customer::dk[$value[0]];
                if(in_array($value[1],$array_clean)) return [false, '客户名称不能重复'];
                $array_clean[] = $value[1];
                $array[$key] = $value;
            }
        }unset($array_clean);
        if(empty($array)) return [false, '导入数据不能为空'];

        //客户
        $model = Customer::Clear($user,[]);
        $customer = $model->where('del_time',0)
            ->whereIn('title',array_unique(array_column($array,'1')))
            ->pluck('id','title')
            ->toArray();

        $model = BasicType::TopClear($user,[]);
        $basic = $model->where('del_time',0)
            ->whereIn('type',[1,2,3,5,9,10])
            ->select('id','title','type')
            ->get()
            ->toArray();
        $basic_list = [];
        foreach ($basic as $value){
            if($value['type'] == 1){
                $basic_list[2][$value['title']] = $value['id'];
            }elseif ($value['type'] == 2){
                $basic_list[3][$value['title']] = $value['id'];
            }elseif ($value['type'] == 3){
                $basic_list[4][$value['title']] = $value['id'];
            }elseif ($value['type'] == 5){
                $basic_list[7][$value['title']] = $value['id'];
            }elseif ($value['type'] == 9){
                $basic_list[8][$value['title']] = $value['id'];
            }elseif ($value['type'] == 10){
                $basic_list[9][$value['title']] = $value['id'];
            }
        }

        $model = Product::ProductClear($user,[]);
        $product = $model->where('del_time',0)
            ->whereIn('title',array_unique(array_column($array,'6')))
            ->pluck('id','title')
            ->toArray();

        $emp = Employee::where('del_time',0)
            ->whereIn('emp_name',array_unique(array_merge_recursive(array_column($array,'11'),array_column($array,'12'))))
            ->pluck('id','emp_name')
            ->toArray();

        $time = time();
        $insert = [];
        foreach ($array as $value){
            $tmp = [
                'model_type' => '',
                'title' => '',
                'customer_intention' => '',
                'customer_from' => '',
                'customer_type' => '',
                'car_type' => '',
                'consulting_product' => '',
                'intention_product' => '',
                'progress_stage' => '',
                'state_type' => '',
                'mark' => '',
                'depart_id' => $head,
                'top_depart_id' => $head,
                'crt_id' => $user['id'],
                'crt_time' => $time,
                'upd_time' => $time,
            ];
            $tmp['model_type'] = $value['0'];
            $tmp['consulting_product'] = $value['5'];
            $tmp['mark'] = $value['10'];
            if(! empty($customer[$value['1']])) return [false, '客户:' . $value['1'] . '已存在'];
            $tmp['title'] = $value['1'];
            if($value['2']){
                if(empty($basic_list[2][$value['2']])) return [false, '客户意向度:' . $value['2'] . '不存在'];
                $tmp['customer_intention'] = $basic_list[2][$value['2']];
            }
            if($value['3']){
                if(empty($basic_list[3][$value['3']])) return [false, '客户来源:' . $value['3'] . '不存在'];
                $tmp['customer_from'] = $basic_list[3][$value['3']];
            }
            if($value['4']){
                if(empty($basic_list[4][$value['4']])) return [false, '客户类别:' . $value['4'] . '不存在'];
                $tmp['customer_type'] = $basic_list[4][$value['4']];
            }
            if($value['6']){
                if(empty($product[$value['6']])) return [false, '意向产品:' . $value['6'] . '不存在'];
                $tmp['intention_product'] = $product[$value['6']];
            }
            if($value['7']){
                if(empty($basic_list[7][$value['7']])) return [false, '进展阶段:' . $value['7'] . '不存在'];
                $tmp['progress_stage'] = $basic_list[7][$value['7']];
            }
            if($value['8']){
                if(empty($basic_list[8][$value['8']])) return [false, '状态:' . $value['8'] . '不存在'];
                $tmp['state_type'] = $basic_list[8][$value['8']];
            }
            if($value['9']){
                if(empty($basic_list[9][$value['9']])) return [false, '车型:' . $value['9'] . '不存在'];
                $tmp['car_type'] = $basic_list[9][$value['9']];
            }
//            if($value['11']){
//                if(empty($emp[$value['11']])) return [false, '负责人:' . $value['11'] . '不存在'];
//                $tmp['emp_one'] = $emp[$value['11']];
//            }
//            if($value['12']){
//                if(empty($emp[$value['12']])) return [false, '协同人:' . $value['12'] . '不存在'];
//                $tmp['emp_two'] = $emp[$value['12']];
//            }

            $insert[] = $tmp;
        }

        try{
            DB::beginTransaction();
            if(! empty($insert)) Customer::insert($insert);
            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            return [false, $e->getMessage() . $e->getLine() . $e->getCode()];
        }

        return [true, ''];
    }

    public function productImport($array, $user){
        $head = $user['head']['id'] ?? 0;

        // 去除表头
        $upload = $array[0];
        unset($array[0]);
        if(empty($array)) return [false, '导入数据不能为空'];

        //第一次表格数据校验 非空  已经过滤数据
        $array_clean = [];
        $search = "";
        foreach ($array as $key => $value){
            $rowData = array_filter($value);
            if (empty($rowData)) {
                unset($array[$key]);
            } elseif(empty($value[0]) || empty($value[1]) || empty($value[2])) {
                return [false, '带*号的字段项必填'];
            }else{
                foreach ($value as $k => $v){
                    $value[$k] = trim($v);
                }
                $t = $value[0] . $value[1];
                if(in_array($t,$array_clean)) return [false, '产品名称与编码不能重复'];
                $array_clean[] = $t;
                $array[$key] = $value;
                $search .= "(title = '".$value[0]."' and code ='".$value[1]."') or";
            }
        }unset($array_clean);
        if(empty($array)) return [false, '导入数据不能为空'];
        $search = rtrim($search,' or');
        $product = Product::whereRaw($search)
            ->where('del_time',0)
            ->select('code','title')
            ->get()->toArray();
        $product_array = [];
        foreach ($product as $value){
            $product_array[] = $value['title'] . $value['code'];
        }

        //默认进来 自身顶级公司
        $top_depart_id = $user['depart_top'][0] ?? [];
        $top_depart_id = $top_depart_id['depart_id'] ?? 0;
        $category_list = ProductCategory::where('del_time',0)
            ->where('top_depart_id',$top_depart_id)
            ->get()->toArray();

        $model = BasicType::TopClear($user,[]);
        $basic = $model->where('del_time',0)
            ->where('type',20)
            ->pluck('id','title')
            ->toArray();

        $category = ProductCategory::whereIn('title',array_unique(array_column($array,'2')))
            ->where('del_time',0)
            ->where('top_depart_id',$top_depart_id)
            ->pluck('id','title')
            ->toArray();
        $category_parent = array_unique(array_column($category,'parent'));

        $time = time();

        $table_head = $this->product([],$user);
        $heads = $table_head[1];
        $tmp = array_column($heads,'key');
        $tmp = array_fill_keys($tmp, '');
        $tmp['product_category'] = '';
        $tmp['depart_id'] = $head;
        $tmp['top_depart_id'] = $head;
        $tmp['crt_id'] = $user['id'];
        $tmp['crt_time'] = $time;
        $tmp['upd_time'] = $time;
        $tmp['crt_id'] = $user['id'];

        $upload = array_flip($upload);
        $map = [];
        foreach ($heads as $value){
            if(strpos($value['key'], 'table_id.') !== false && isset($upload[$value['value']])){
                $map[$value['key']] = [
                    'col' => $upload[$value['value']],
                    'name' => $value['value']
                ];
            }
        }

        $array = array_values($array);
        $insert = $insert2 = [];
        foreach ($array as $key => $value){
            $pro_str = $value['0'] . $value['1'];
            if(in_array($pro_str, $product_array)) return [false, '产品名称、编码:' . $value['0']. '、' . $value['1'] . '已存在'];
            $tmp['title'] = $value['0'];
            $tmp['code'] = $value['1'];
            if(empty($category[$value['2']])) return [false,'产品分类:' . $value['2'] . '不存在'];
            $tmp['product_category_id'] = $category[$value['2']];
            if(in_array($tmp['product_category_id'],$category_parent)) return [false,'产品分类:' . $value['2'] . '下存在子分类,请将产品建与最底层分类下'];
            $parentsId = $this->findParentIds($tmp['product_category_id'], $category_list);
            array_unshift($parentsId, $tmp['product_category_id']);
            $result = array_reverse($parentsId);
            $tmp['product_category'] = json_encode($result);
            $tmp['size'] = $value['3'];
            if($value['4']){
                if(empty($basic[$value['4']])) return [false, '单位:' . $value['4'] . '不存在'];
                $tmp['unit'] = $basic[$value['4']];
            }
            $tmp['bar_code'] = $value['5'];
            $tmp['cost'] = $value['6'];
            $tmp['retail_price'] = $value['7'];
            foreach ($map as $m => $v){
                if($value[$v['col']]){
                    if(! is_numeric($value[$v['col']])) return [false,$v['name'] . ': 请输入数字且最多两位小数'];
                    $formattedNumber = number_format($value[$v['col']], 2, '.', '');
                    if($formattedNumber != $value[$v['col']]) return [false,$v['name'] . ': 请输入数字且最多两位小数'];
                }
                $tmp[$m] = $value[$v['col']];
            }

            foreach ($tmp as $k => $v){
                if(strpos($k, 'table_id.') !== false){
                    $tmp2 = [];
                    $k_n = str_replace('table_id.', "", $k);
                    $tmp2['basic_type_id'] = $k_n;
                    $tmp2['price'] = $v;
                    $tmp2['crt_time'] = $time;
                    $tmp2['upd_time'] = $time;
                    $insert2[$key][] = $tmp2;
                    unset($tmp[$k]);
                }
            }
            $insert[] = $tmp;
        }

        try{
            DB::beginTransaction();
            if(! empty($insert)) Product::insert($insert);
            if(! empty($insert2)){
                $insert_detail = [];
                $last_insert_id = Product::where('crt_time',$time)
                    ->select('id')
                    ->get()->toArray();
                $last_insert_id = array_column($last_insert_id,'id');
                foreach ($last_insert_id as $key => $value){
                    if(isset($insert2[$key])) {
                        foreach ($insert2[$key] as $val){
                            $val['product_id'] = $value;
                            $insert_detail[] = $val;
                        }
                    }
                }
                ProductPriceDetail::insert($insert_detail);
            }
            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            return [false, $e->getMessage() . $e->getLine() . $e->getCode()];
        }

        return [true, ''];
    }

    public function salesOnlineImport($array, $user){
        $head = $user['head']['id'] ?? 0;

        // 去除表头
        unset($array[0]);
        if(empty($array)) return [false, '导入数据不能为空'];

        $model = BasicType::TopClear($user,[]);
        $basic_type = $model->where('del_time',0)
            ->where('type',24)
            ->pluck('id','title')
            ->toArray();
        $search = "";
        foreach ($array as $key => $value){
            $rowData = array_filter($value);
            if (empty($rowData)) {
                unset($array[$key]);
            } elseif(empty($value[0]) || empty($value[1]) || empty($value[2]) || empty($value[3]) || empty($value[4]) || empty($value[5]) || empty($value[6])) {
                return [false, '带*号的字段项必填'];
            }else{
                foreach ($value as $k => $v){
                    $value[$k] = trim($v);
                }
                if(! isset($basic_type[$value[1]])) return [false, '店铺（平台类型）:' . $value[1] .'不存在'];
                $value[1] = $basic_type[$value[1]];
                if(! isset(SalesOrder::$order_type[$value[6]])) return [false, '产品类型填写错误'];
                $array[$key] = $value;
                $search .= "(code = '".$value[2]."' and title ='".$value[3]."') or";
            }
        }
        if(empty($array)) return [false, '导入数据不能为空'];
        $search = rtrim($search,' or');
        $model = Product::ProductClear($user,[]);
        $product = $model->whereRaw($search)
            ->where('del_time',0)
            ->select('title','id','code','cost','retail_price')
            ->get()->toArray();
        $product_map = [];
        foreach ($product as $value){
            $product_map[$value['code'] . $value['title']] = [
                'id' => $value['id'],
                'cost' => $value['cost'],
                'retail_price' => $value['retail_price'],
            ];
        }

        $time = time();
        $tmp = [
            'model_type' => SalesOrder::Model_type_four,
            'sales_order_type' => 0,
            'order_number' => '',
            'plat_type' => '',
            'plat_order' => '',
            'sign_time' => $time,
            'product_total' => 0,
            'contract_fee' => 0,
            'rate' => 100,
            'depart_id' => $head,
            'top_depart_id' => $head,
            'crt_id' => $user['id'],
            'crt_time' => $time,
            'upd_time' => $time,
        ];
        $tmp_detail = [
            'sales_order_id' => 0,
            'product_id' => 0,
            'cost' => 0,
            'retail_price' => 0,
            'price' => 0,
            'final_amount' => 0,
            'number' => '',
            'crt_time' => $time,
        ];
        $insert = $insert_detail = [];
        $prefix = SalesOrder::$prefix[salesOrder::Model_type_four];
        foreach ($array as $value){
            $product_str = $value[2] . $value[3];
            if(! isset($product_map[$product_str])) return [false, '产品:' . '[' . $value[2]. ']' . '[' . $value[3]. ']' . '不存在'];
            $product_tmp = $product_map[$product_str] ?? [];

            $tmp['product_total'] = $tmp['contract_fee'] = 0;
            $keys = $value[0] . $value[6];
            if(! isset($insert[$keys])){
                $tmp['order_number'] = OrderNoService::createSalesOrderNumber($prefix);
                $tmp['sales_order_type'] = $value[6];
                $tmp['plat_type'] = $value[1];
                $tmp['plat_order'] = $value[0];
                $tmp['product_total'] += $value[4] * $value[5];
                $tmp['contract_fee'] += $value[4] * $value[5];
                $insert[$keys] = $tmp;
            }else{
                $insert[$keys]['product_total'] += $value[4] * $value[5];
                $insert[$keys]['contract_fee'] += $value[4] * $value[5];
            }

            $tmp_detail['product_id'] = $product_tmp['id'];
            $tmp_detail['cost'] = $product_tmp['cost'];
            $tmp_detail['retail_price'] = $product_tmp['retail_price'];
            $tmp_detail['price'] = $value[5];
            $tmp_detail['final_amount'] = $value[4] * $value[5];
            $tmp_detail['number'] = $value[4];

            $insert_detail[$keys][] = $tmp_detail;
        }

        $insert_detail = array_values($insert_detail);
        try{
            DB::beginTransaction();
            if(! empty($insert)) SalesOrder::insert($insert);
            if(! empty($insert_detail)){
                $insert2 = [];
                $last_insert_id = SalesOrder::where('crt_time',$time)
                    ->select('id')
                    ->get()->toArray();
                $last_insert_id = array_column($last_insert_id,'id');
                foreach ($last_insert_id as $key => $value){
                    if(isset($insert_detail[$key])) {
                        foreach ($insert_detail[$key] as $val){
                            $val['sales_order_id'] = $value;
                            $insert2[] = $val;
                        }
                    }
                }
                SalesOrderProductInfo::insert($insert2);
            }
            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            return [false, $e->getMessage() . $e->getLine() . $e->getCode()];
        }

        return [true, ''];
    }
}