t_nine/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\CustomerInfo;
use App\Model\Depart;
use App\Model\Employee;
use App\Model\LastJc;
use App\Model\Product;
use App\Model\ProductCategory;
use App\Model\ProductPriceDetail;
use App\Model\SalesOrder;
use App\Model\SalesOrderInfo;
use App\Model\SalesOrderProductInfo;
use App\Model\Storehouse;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ImportService extends Service
{

    public static $type = [
        'product', //产品
        'customer', //客户
        'salesOnline', //线上订单
        'btOnline', //补贴订单
        'lastJc', //上月结存
    ];

    //写活的导入------------------- 暂时不好用
    //导入入口
    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, '配置文件不存在', ''];

        $result = (new BasicTypeService())->getMyBasicList($user, 22);
        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];
    }

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

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

    private function lastJc($data,$user){
        //生成下载文件
        $filename =  "上月结存数据更新模板_" . time() . '.' . 'xlsx';

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

    //导入入口
    public function importAll($data,$user){
//        //不超时
//        ini_set('max_execution_time', 0);
//        //内存设置
//        ini_set('memory_limit', -1);
//        $reader = IOFactory::createReader('Xlsx');
//        $reader->setReadDataOnly(true); // 只读取有数据的单元格
//        $spreadsheet = $reader->load($data['file']);
//        dd($spreadsheet);
//        // 创建一个Reader对象
//        $reader = IOFactory::createReader('Xlsx'); // 根据你的文件格式选择合适的reader
//
//// 加载Excel文件
//        $spreadsheet = $reader->load($data['file']);
//
//// 获取第一个工作表
//        $worksheet = $spreadsheet->getActiveSheet();
//
//// 获取总行数
//        $totalRows = $worksheet->getHighestRow();dd($totalRows);
        if(empty($data['type'])) return [false,'缺少导入类型，导入失败'];
        if(! in_array($data['type'],self::$type)) return [false,'导入类型不存在，导入失败'];
        if(empty($data['file'])) return [false,'导入文件不能为空'];

        try {
            $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()];
        }catch (\Throwable $exception) {
            return [false, $exception->getMessage() . ' (Code: ' . $exception->getCode() . ', Line: ' . $exception->getLine() . ')'];
        }

        return [true, ''];
    }

    public function customerImport($array, $user){
        $head = $user['depart_top'][0] ?? [];
        $head = $head['depart_id'] ?? 0;
        if(empty($head)) return [false, '导入异常错误，门店信息丢失'];

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

        //第一次表格数据校验 非空  已经过滤数据
        $array_clean = $contact_info = [];
        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;

                if(! empty($value[13])){
                    if(in_array($value[13],$contact_info)) return [false, '联系方式内容不能重复'];
                    $contact_info[] = $value[13];
                }
            }
        }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();

        $basic = (new BasicTypeService())->getMyBasicList($user, [1,2,3,4,5,9,10,30]);
        $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[41][$value['title']] = $value['id'];
            }elseif ($value['type'] == 30){
                $basic_list[42][$value['title']] = $value['id'];
            }elseif ($value['type'] == 4){
                $basic_list[12][$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('number',array_unique(array_column($array,'14')))
            ->pluck('id','number')
            ->toArray();

        $top_depart_id = $user['depart_top'][0] ?? [];
        $top_depart_id = $top_depart_id['depart_id'] ?? 0;
        $contact_info_array = CustomerInfo::from('customer_info as a')
            ->join('customer as b','b.id','a.customer_id')
            ->where('a.del_time',0)
            ->where('b.del_time',0)
            ->where('b.top_depart_id',$top_depart_id)
            ->whereIn('a.contact_info', $contact_info)
            ->select('a.contact_info')->get()->toArray();
        $contact_info_array = array_column($contact_info_array,'contact_info');

        $time = time();
        $insert = [];
        $insert_detail = $insert_detail2 = [];
        foreach ($array as $value){
            $tmp = [
                'model_type' => '',
                'title' => '',
                'customer_intention' => '',
                'customer_from' => '',
                'customer_type' => '',
                'car_type' => '',
                'consulting_product' => '',
                'intention_product' => '',
                'progress_stage' => '',
                'state_type' => '',
                'address2' => '',
                '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'];
            $tmp['address2'] = $value['11'];
            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']){
                $keys = 4 . $value[0];
                $model_title = Customer::dk2[$value[0]] ?? "";
                if(empty($basic_list[$keys][$value['4']])) return [false, '模板:' . $model_title . '下客户类别:' . $value['4'] . '不存在'];
                $tmp['customer_type'] = $basic_list[$keys][$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']];
            }
            $contact_id = 0;
            if($value['12']){
                if(empty($basic_list[12][$value['12']])) return [false, '联系方式类型:' . $value['12'] . '不存在'];
                $contact_id = $basic_list[12][$value['12']];
            }
            if($value['13'] && in_array($value['13'],$contact_info_array)) return [false, '联系方式内容:' . $value['13'] . '已存在'];
            $man = 0;
            if($value['14']){
                if(empty($emp[$value['14']])) return [false, '负责人:' . $value['14'] . '不存在'];
                $man = $emp[$value['14']];
            }
//            if($value['12']){
//                if(empty($emp[$value['12']])) return [false, '协同人:' . $value['12'] . '不存在'];
//                $tmp['emp_two'] = $emp[$value['12']];
//            }

            $insert[] = $tmp;

            $insert_detail[] = [
                'customer_id' => 0,
                'contact_type' => $contact_id,
                'contact_info' => $value['13'],
                'crt_time' => $time,
                'type' => CustomerInfo::type_one
            ];
            $insert_detail2[] = [
                'customer_id' => 0,
                'data_id' => $man,
                'crt_time' => $time,
                'type' => CustomerInfo::type_two
            ];
        }

        try{
            DB::beginTransaction();
            if(! empty($insert)) Customer::insert($insert);

            //获取上一次所有id
            $last_insert_id = Customer::where('crt_time',$time)
                ->where('crt_time',$time)
                ->where('depart_id',$head)
                ->where('top_depart_id',$head)
                ->where('crt_id',$user['id'])
                ->select('id')->get()->toArray();
            $last_insert_id = array_column($last_insert_id,'id');

            //组织数据 写入与主表的关联id
            $insert_detail_1 = [];
            foreach ($insert_detail as $key => $value){
                if(empty($value['contact_type']) && empty($value['contact_info'])) continue;
                $value['customer_id'] = $last_insert_id[$key];
                $insert_detail_1[] = $value;
            }unset($insert_detail);
            $insert_detail_2 = [];
            foreach ($insert_detail2 as $key => $value){
                if(empty($value['data_id'])) continue;
                $value['customer_id'] = $last_insert_id[$key];
                $insert_detail_2[] = $value;
            }unset($insert_detail2);

            if(! empty($insert_detail_1)) CustomerInfo::insert($insert_detail_1);
            if(! empty($insert_detail_2)) CustomerInfo::insert($insert_detail_2);

            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            return [false, $e->getMessage() . $e->getLine() . $e->getCode()];
        }

        return [true, ''];
    }

    public function productImport($array, $user){
        //当前门店
        $depart_id = $this->getDepart($user);
        $top_depart_id = $user['depart_map'][$depart_id] ?? 0;

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

        //第一次表格数据校验 非空  已经过滤数据
        $array_clean = [];
        $search = "";
        $map_attr = array_flip(Product::$product_attribute);
        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[1];
                if(in_array($t,$array_clean)) return [false, '产品编码:'. $value[1] .'在文件中重复出现'];
                $array_clean[] = $t;

                if(! empty($value[8])){
                    if(! isset($map_attr[$value[8]])) return [false, '产品属性不存在' . $value[8]];
                    $value[8] = $map_attr[$value[8]];
                }else{
                    $value[8] = Product::Product_attribute_zero;
                }
                $array[$key] = $value;
                $search .= "(binary code ='".$value[1]."') or";
            }
        }unset($array_clean);
        if(empty($array)) return [false, '导入数据不能为空'];

        $search = rtrim($search,' or');
        $search = "($search)";
        $product = Product::whereRaw($search)
            ->where('del_time',0)
            ->select('code','top_depart_id','id')
            ->get()->toArray();
        $product_array = [];
        foreach ($product as $value){
            $product_array[$value['code']] = [
                'id' => $value['id'],
                'top_depart_id' => $value['top_depart_id']
            ];
        }

        $category_list = ProductCategory::where('del_time',0)
            ->where('top_depart_id',$top_depart_id)
            ->get()->toArray();

        $basic = (new BasicTypeService())->getMyBasicList($user, 20);
        $basic = array_column($basic,'id','title');

        $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['upd_time'] = $time;

        $top_message = Depart::where('parent_id',0)
            ->pluck('title','id')
            ->toArray();

        $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 = $update = $update2 = [];
        foreach ($array as $value){
            if(isset($product_array[$value['1']])){
                $pro_tmp = $product_array[$value['1']] ?? [];
                if($pro_tmp['top_depart_id'] != $top_depart_id){
                    $belong = $top_message[$pro_tmp['top_depart_id']] ?? "";
                    $now = $top_message[$top_depart_id] ?? "";
                    return [false, '产品编码:' . $value['1'] . '属于门店:' . $belong . ',当前门店:' . $now . ',不允许跨门店操作更新产品!'];
                }
            }

            $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'] ?? 0;
            $tmp['retail_price'] = $value['7'] ?? 0;
            $tmp['product_attribute'] = $value['8'] ?? 0;
            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']];
            }

            if(isset($product_array[$value['1']])){
                //更新
                $pro_tmp = $product_array[$value['1']] ?? [];
                $product_id = $pro_tmp['id'];

                //产品价格子表
                foreach ($tmp as $k => $v){
                    if(strpos($k, 'table_id.') !== false){
                        $tmp2 = [];
                        $k_n = str_replace('table_id.', "", $k);
                        $tmp2['product_id'] = $product_id;
                        $tmp2['basic_type_id'] = $k_n;
                        $tmp2['price'] = $v;
                        $tmp2['crt_time'] = $time;
                        $tmp2['upd_time'] = $time;
                        $update2[] = $tmp2;
                        unset($tmp[$k]);
                    }
                }

                //产品主表
                $update[$product_id] = $tmp;
            }else{
                $tmp['depart_id'] = $depart_id;
                $tmp['top_depart_id'] = $top_depart_id;
                $tmp['crt_id'] = $user['id'];
                $tmp['crt_time'] = $time;
                //产品价格子表
                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[$tmp['code']][] = $tmp2;
                        unset($tmp[$k]);
                    }
                }

                //产品主表
                $insert[$tmp['code']] = $tmp;
            }
        }

        try{
            DB::beginTransaction();
            //新增
            if(! empty($insert)){
                Product::insert($insert);
                if(! empty($insert2)){
                    $insert_detail = [];
                    $last_insert_id = Product::where('crt_time',$time)
                        ->pluck('id','code')
                        ->toArray();
                    foreach ($insert2 as $code => $val){
                        foreach ($val as $v2){
                            $v2['product_id'] = $last_insert_id[$code] ?? 0;
                            $insert_detail[] = $v2;
                        }
                    }
                    ProductPriceDetail::insert($insert_detail);
                }
            }

            //编辑
            if(! empty($update)){
                foreach ($update as $p_id => $value){
                    Product::where('id',$p_id)
                        ->update($value);
                }

                if(! empty($update2)){
                    $product_id_array = array_keys($update);
                    ProductPriceDetail::whereIn('product_id',$product_id_array)
                        ->update(['del_time' => $time]);
                    ProductPriceDetail::insert($update2);
                }
            }
            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, '导入数据不能为空'];

        $basic = (new BasicTypeService())->getMyBasicList($user, [18,23,24,29]);
        $basic_list = [];
        foreach ($basic as $value){
            if($value['type'] == 18){
                $basic_list[9][$value['title']] = $value['id'];
            }elseif ($value['type'] == 23){
                $basic_list[10][$value['title']] = $value['id'];
            }elseif ($value['type'] == 24){
                $basic_list[3][$value['title']] = $value['id'];
            }elseif ($value['type'] == 29){
                $basic_list[2][$value['title']] = $value['id'];
            }
        }

        $search = "";
        $customer = [];
        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]) || $value[6] === null) {
                return [false, '带*号的字段项必填'];
            }else{
                foreach ($value as $k => $v){
                    $value[$k] = trim($v);
                }
                if(! isset($basic_list[2][$value[2]])) return [false, '客户简称:' . $value[2] .'不存在'];
                $value[2] = $basic_list[2][$value[2]];
                if(! isset($basic_list[3][$value[3]])) return [false, '店铺（平台类型）:' . $value[3] .'不存在'];
                $value[3] = $basic_list[3][$value[3]];

                if(! is_numeric($value[5])) return [false, '货品数量请填写正确的数值'];
                if(! is_numeric($value[6])) return [false, '产品合同金额请填写正确的数值'];
                if(! empty($value[8]) && ! is_numeric($value[8])) return [false, '订单优惠金额请填写正确的数值'];
                if(! empty($value[9])){
                    if(! isset($basic_list[9][$value[9]])) return [false, '安装方式:' . $value[9] .'不存在'];
                    $value[9] = $basic_list[9][$value[9]];
                }
                if(! empty($value[10])){
                    if(! isset($basic_list[10][$value[10]])) return [false, '安装地点:' . $value[10] .'不存在'];
                    $value[10] = $basic_list[10][$value[10]];
                }

                if(! isset(SalesOrder::$order_type_name[$value[1]])) return [false, '产品类型填写错误'];
                $value[1] = SalesOrder::$order_type_name[$value[1]];

                if(! empty($value[11])) {
                    list($status,$msg) = $this->changeAndReturnDate($value[11]);
                    if(! $status) return [false,"施工日期请填写正确的日期格式，例如2023-05-01"];
                    $value[11] = $msg;
                }

                if(! empty($value[12])) {
                    list($status,$msg) = $this->changeAndReturnDate($value[12]);
                    if(! $status) return [false,"交车日期请填写正确的日期格式，例如2023-05-01"];
                    $value[12] = $msg;
                }

                $array[$key] = $value;
                $search .= "(code = '".$value[4]."') or";

                if(! empty($value[7]) && ! in_array($value[7], $customer)) $customer[] = $value[7];
            }
        }
        if(empty($array)) return [false, '导入数据不能为空'];
        $search = rtrim($search,' or');
        $search = "($search)";
        $model = Product::ProductClear($user,[]);
        $product = $model->whereRaw($search)
            ->where('del_time',0)
            ->select('title','id','code','cost','retail_price')
            ->get()->toArray();
//        $pro = (new ProductService())->productList(['product_id' => array_column($product,'id'), 'type' => 2],$user);

        $product_map = [];
        foreach ($product as $value){
            $product_map[$value['code']] = [
                'id' => $value['id'],
                'cost' => $value['cost'],
                'retail_price' => $value['retail_price'],
            ];
        }

        $time = time();

        $model = Customer::Clear($user,[]);
        $customer_map = $model->where('del_time',0)
            ->whereIn('title',$customer)
            ->pluck('id','title')
            ->toArray();

        $customer_info = CustomerInfo::where('del_time',0)
            ->whereIn('customer_id',array_values($customer_map))
            ->whereIn('type',[CustomerInfo::type_one,CustomerInfo::type_two])
            ->select('customer_id','type','contact_info','data_id')
            ->orderBy('id','asc')
            ->get()->toArray();
        $customer_contact = $customer_man = [];
        foreach ($customer_info as $value){
            if($value['type'] == CustomerInfo::type_one && ! isset($customer_contact[$value['customer_id']])){
                $customer_contact[$value['customer_id']] = $value['contact_info'];
            }elseif ($value['type'] == CustomerInfo::type_two){
                $customer_man[$value['customer_id']][] = [
                    'type' => SalesOrderInfo::type_two,
                    'data_id' => $value['data_id'],
                    'crt_time' => $time,
                ];
            }
        }

        $tmp = [
            'model_type' => SalesOrder::Model_type_four,
            'sales_order_type' => 0,
            'order_number' => '',
            'customer_short_name' => '',
            'plat_type' => '',
            'plat_order' => '',
            'sign_time' => $time,
            'product_total' => 0,
            'contract_fee' => 0,
            'discount_fee' => 0,
            'cdefine29' => '',//分社施工
            'cdefine32' => '',//达人昵称
            'cdefine30' => '',//业务员
            '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,
            'basic_type_id' => 0,
            'price' => 0,
            'final_amount' => 0,
            'number' => '',
            'crt_time' => $time,
        ];
        $insert = $insert_detail = $insert_detail_man =  [];
        $prefix = SalesOrder::$prefix[salesOrder::Model_type_four];
        foreach ($array as $value){
            $product_str = $value[4];
            if(! isset($product_map[$product_str])) return [false, '产品:' . '[' . $value[4]. ']' . '不存在'];
            $product_tmp = $product_map[$product_str] ?? [];

            $customer_tmp = 0;
            $customer_contact_tmp = "";
            $customer_man_tmp = [];
            if(! empty($value[7])){
                $customer_tmp = $customer_map[$value[7]] ?? 0;
                $customer_contact_tmp = $customer_contact[$customer_tmp] ?? "";
                $customer_man_tmp = $customer_man[$customer_tmp] ?? [];
            }
            $customer_man_tmp[] = [
                'type' => SalesOrderInfo::type_one,
                'data_id' => $user['id'],
                'crt_time' => $time,
            ];

            $tmp['product_total'] = $tmp['contract_fee'] = 0;
            $keys = $value[0] . $value[1];
            $insert_detail_man[$keys] = $customer_man_tmp;
            if(! isset($insert[$keys])){
                $tmp['order_number'] = OrderNoService::createSalesOrderNumberImport($prefix);
                $tmp['sales_order_type'] = $value[1];
                $tmp['customer_short_name'] = $value[2];
                $tmp['customer_id'] = $customer_tmp;
                $tmp['customer_contact'] = $customer_contact_tmp;
                $tmp['plat_type'] = $value[3];
                $tmp['plat_order'] = $value[0];
                $tmp['discount_fee'] = $value[8] ?: 0;
                $tmp['cdefine29'] = $value[13] ?? "";//分社施工
                $tmp['cdefine32'] = $value[14] ?? "";//达人昵称
                $tmp['cdefine30'] = $value[15] ?? "";//业务员
                $tmp['product_total'] += $value[6];
                $tmp['contract_fee'] += $value[6] - $tmp['discount_fee'];
                $tmp['construction_time'] = $value[11] ?? 0;
                $tmp['handover_time'] = $value[12] ?? 0;
                $insert[$keys] = $tmp;
            }else{
                $insert[$keys]['product_total'] += $value[6];
                $insert[$keys]['contract_fee'] += $value[6];
            }

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

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

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

        return [true, ''];
    }

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

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

        $basic = (new BasicTypeService())->getMyBasicList($user, [18,23,24,29]);
        $basic_list = [];
        foreach ($basic as $value){
            if($value['type'] == 18){
                $basic_list[9][$value['title']] = $value['id'];
            }elseif ($value['type'] == 23){
                $basic_list[10][$value['title']] = $value['id'];
            }elseif ($value['type'] == 24){
                $basic_list[3][$value['title']] = $value['id'];
            }elseif ($value['type'] == 29){
                $basic_list[2][$value['title']] = $value['id'];
            }
        }

        $search = "";
        $customer = [];
        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]) || $value[6] === null) {
                return [false, '带*号的字段项必填'];
            }else{
                foreach ($value as $k => $v){
                    $value[$k] = trim($v);
                }
                if(! isset($basic_list[2][$value[2]])) return [false, '客户简称:' . $value[2] .'不存在'];
                $value[2] = $basic_list[2][$value[2]];
                if(! isset($basic_list[3][$value[3]])) return [false, '店铺（平台类型）:' . $value[3] .'不存在'];
                $value[3] = $basic_list[3][$value[3]];

                if(! is_numeric($value[5])) return [false, '货品数量请填写正确的数值'];
                if(! is_numeric($value[6])) return [false, '产品合同金额请填写正确的数值'];
                if(! empty($value[8]) && ! is_numeric($value[8])) return [false, '订单优惠金额请填写正确的数值'];
                if(! empty($value[9])){
                    if(! isset($basic_list[9][$value[9]])) return [false, '安装方式:' . $value[9] .'不存在'];
                    $value[9] = $basic_list[9][$value[9]];
                }
                if(! empty($value[10])){
                    if(! isset($basic_list[10][$value[10]])) return [false, '安装地点:' . $value[10] .'不存在'];
                    $value[10] = $basic_list[10][$value[10]];
                }

                if(! isset(SalesOrder::$order_type_name[$value[1]])) return [false, '产品类型填写错误'];
                $value[1] = SalesOrder::$order_type_name[$value[1]];

                if(! empty($value[11])) {
                    list($status,$msg) = $this->changeAndReturnDate($value[11]);
                    if(! $status) return [false,"施工日期请填写正确的日期格式，例如2023-05-01"];
                    $value[11] = $msg;
                }

                if(! empty($value[12])) {
                    list($status,$msg) = $this->changeAndReturnDate($value[12]);
                    if(! $status) return [false,"交车日期请填写正确的日期格式，例如2023-05-01"];
                    $value[12] = $msg;
                }

                $array[$key] = $value;
                $search .= "(code = '".$value[4]."') or";

                if(! empty($value[7]) && ! in_array($value[7], $customer)) $customer[] = $value[7];
            }
        }
        if(empty($array)) return [false, '导入数据不能为空'];
        $search = rtrim($search,' or');
        $search = "($search)";
        $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']] = [
                'id' => $value['id'],
                'cost' => $value['cost'],
                'retail_price' => $value['retail_price'],
            ];
        }

        $time = time();

        $model = Customer::Clear($user,[]);
        $customer_map = $model->where('del_time',0)
            ->whereIn('title',$customer)
            ->pluck('id','title')
            ->toArray();

        $customer_info = CustomerInfo::where('del_time',0)
            ->whereIn('customer_id',array_values($customer_map))
            ->whereIn('type',[CustomerInfo::type_one,CustomerInfo::type_two])
            ->select('customer_id','type','contact_info','data_id')
            ->orderBy('id','asc')
            ->get()->toArray();
        $customer_contact = $customer_man = [];
        foreach ($customer_info as $value){
            if($value['type'] == CustomerInfo::type_one && ! isset($customer_contact[$value['customer_id']])){
                $customer_contact[$value['customer_id']] = $value['contact_info'];
            }elseif ($value['type'] == CustomerInfo::type_two){
                $customer_man[$value['customer_id']][] = [
                    'type' => SalesOrderInfo::type_two,
                    'data_id' => $value['data_id'],
                    'crt_time' => $time,
                ];
            }
        }

        $tmp = [
            'model_type' => SalesOrder::Model_type_seven,
            'sales_order_type' => 0,
            'order_number' => '',
            'customer_short_name' => '',
            'plat_type' => '',
            'plat_order' => '',
            'sign_time' => $time,
            'product_total' => 0,
            'contract_fee' => 0,
            'discount_fee' => 0,
            'cdefine29' => '',//分社施工
            'cdefine32' => '',//达人昵称
            'cdefine30' => '',//业务员
            '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,
            'basic_type_id' => 0,
            'price' => 0,
            'final_amount' => 0,
            'number' => '',
            'crt_time' => $time,
        ];
        $insert = $insert_detail = $insert_detail_man =  [];
        $prefix = SalesOrder::$prefix[salesOrder::Model_type_four];
        foreach ($array as $value){
            $product_str = $value[4];
            if(! isset($product_map[$product_str])) return [false, '产品:' . '[' . $value[4]. ']' . '不存在'];
            $product_tmp = $product_map[$product_str] ?? [];

            $customer_tmp = 0;
            $customer_contact_tmp = "";
            $customer_man_tmp = [];
            if(! empty($value[7])){
                $customer_tmp = $customer_map[$value[7]] ?? 0;
                $customer_contact_tmp = $customer_contact[$customer_tmp] ?? "";
                $customer_man_tmp = $customer_man[$customer_tmp] ?? [];
            }
            $customer_man_tmp[] = [
                'type' => SalesOrderInfo::type_one,
                'data_id' => $user['id'],
                'crt_time' => $time,
            ];

            $tmp['product_total'] = $tmp['contract_fee'] = 0;
            $keys = $value[0] . $value[1];
            $insert_detail_man[$keys] = $customer_man_tmp;
            if(! isset($insert[$keys])){
                $tmp['order_number'] = OrderNoService::createSalesOrderNumberImport($prefix);
                $tmp['sales_order_type'] = $value[1];
                $tmp['customer_short_name'] = $value[2];
                $tmp['customer_id'] = $customer_tmp;
                $tmp['customer_contact'] = $customer_contact_tmp;
                $tmp['plat_type'] = $value[3];
                $tmp['plat_order'] = $value[0];
                $tmp['discount_fee'] = $value[8] ?: 0;
                $tmp['cdefine29'] = $value[13] ?? "";//分社施工
                $tmp['cdefine32'] = $value[14] ?? "";//达人昵称
                $tmp['cdefine30'] = $value[15] ?? "";//业务员
                $tmp['product_total'] += $value[6];
                $tmp['contract_fee'] += $value[6] - $tmp['discount_fee'];
                $tmp['construction_time'] = $value[11] ?? 0;
                $tmp['handover_time'] = $value[12] ?? 0;
                $insert[$keys] = $tmp;
            }else{
                $insert[$keys]['product_total'] += $value[6];
                $insert[$keys]['contract_fee'] += $value[6];
            }

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

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

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

        return [true, ''];
    }

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

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

        $shop_name = $product_code = [];
        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(! is_numeric($value[2])) return [false, '* 上月结存数量请填写正确的数值'];
                if(! is_numeric($value[3])) return [false, '* 上月结存单价请填写正确的数值'];
                if(! is_numeric($value[4])) return [false, '* 上月结存金额请填写正确的数值'];
                if(! in_array($value[0],$shop_name)) $shop_name[] = $value[0];
                if(! in_array($value[1],$product_code)) $product_code[] = $value[1];
            }
        }
        $model = Product::ProductClear($user,[]);
        $product_map = $model->whereIn('code',$product_code)
            ->where('del_time',0)
            ->pluck('id','code')
            ->toArray();

        $depart_map = Depart::whereIn('title',$shop_name)
            ->where('del_time',0)
            ->pluck('id','title')
            ->toArray();
        $storehouse_map = Storehouse::whereIn('top_depart_id',array_values($depart_map))
            ->where('del_time',0)
            ->pluck('id','top_depart_id')
            ->toArray();
        $time = time();
        $insert = [];

        $last_month_stamp = strtotime(date('Y-m-t 23:59:59', strtotime('last month')));
        foreach ($array as $value){
            $depart_tmp = $depart_map[$value[0]] ?? 0;
            if($depart_tmp <= 0) return [false, "门店:" . $value[0] . "不存在或已被删除"];
            $product_tmp = $product_map[$value[1]] ?? 0;
            if($product_tmp <= 0) return [false, "产品编码:" . $value[1] . "不存在或已被删除"];
            $storehouse_tmp = $storehouse_map[$depart_tmp] ?? 0;
            $insert[] = [
                'top_depart_id' => $depart_tmp,
                'product_id' => $product_tmp,
                'storehouse_id' => $storehouse_tmp,
                'number' => $value[2],
                'price' => $value[3],
                'total' => $value[4],
                'crt_time' => $time,
                'time' => $last_month_stamp
            ];
        }
        if(empty($insert)) return [false, '暂无更新的数据'];

        try{
            DB::beginTransaction();

            //更新数据为删除
            foreach ($insert as $value){
                LastJc::where('del_time',0)
                    ->where('time', $last_month_stamp)
                    ->where('top_depart_id',$value['top_depart_id'])
                    ->where('storehouse_id',$value['storehouse_id'])
                    ->where('product_id',$value['product_id'])
                    ->update(['del_time' => $time]);
            }
            //写入数据
            LastJc::insert($insert);
            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            return [false, $e->getMessage() . $e->getLine() . $e->getCode()];
        }

        return [true, ''];
    }
}