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\BasicTypeAllUse;
use App\Model\Customer;
use App\Model\CustomerInfo;
use App\Model\CustomerPerFormance;
use App\Model\CustomerRemain;
use App\Model\CustomerRepeat;
use App\Model\Depart;
use App\Model\Employee;
use App\Model\Inventory;
use App\Model\InventorySub;
use App\Model\LastJc;
use App\Model\Product;
use App\Model\ProductAdjustment;
use App\Model\ProductAdjustmentSub;
use App\Model\ProductCategory;
use App\Model\ProductInventorySet;
use App\Model\ProductItemCodeMessage;
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;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class ImportService extends Service
{

    public static $type = [
        'product', //产品
        'customer', //客户
        'salesOnline', //线上订单
        'btOnline', //补贴订单
        'lastJc', //上月结存
        'inventory', // 盘点
        'productAdjustment', // 产品金额调整单
        'customerPerformance', // 客资金额
    ];

    //写活的导入------------------- 暂时不好用
    //导入入口
    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];
    }

    private function inventory($data,$user){
        //生成下载文件
        $filename =  "盘点单导入模板_" . time() . '.' . 'xlsx';

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

    private function productAdjustment($data,$user){
        //生成下载文件
        $filename =  "产品金额调整单导入模板_" . time() . '.' . 'xlsx';

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

    private function customerPerformance($data,$user){
        //生成下载文件
        $filename =  "客资业绩导入模板_" . time() . '.' . 'xlsx';

        //获取配置文件
        $config = "excel.customerPerformanceTable";
        $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']);
            $other = $data;
            unset($other['file']);
            $import->setOtherParam($other);

            //导入
            \Maatwebsite\Excel\Facades\Excel::import($import,$data['file']);
            if($import->getMsg()) {
                $bool = $import->getIsLongText();
                if($bool) {
                    return [0, $import->getMsg()];
                }else{
                    return [false, $import->getMsg()];
                }
            }
        }catch (\Throwable $exception) {
            return [false, $exception->getMessage() . ' (Code: ' . $exception->getCode() . ', Line: ' . $exception->getLine() . ')'];
        }

        return [true, ''];
    }

    public function customerImport($array, $user){
        $time = time();

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

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

        //第一次表格数据校验 非空  已经过滤数据
        list($status, $msg) = $this->customerFilterFirst($array, $time);
        if(! $status) return [0, $msg];
        list($contact_info, $fz, $fp) = $msg;

        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();

        $basic_use = BasicTypeAllUse::where('del_time',0)
            ->where('type', BasicTypeAllUse::type_one)
            ->whereIn('title',array_unique(array_column($array,'5')))
            ->pluck('id','title')
            ->toArray();

        $empList = Employee::where('del_time',0)
            ->when(! empty($fz), function($query) use($fz) {
                return $query->where(function ($q) use ($fz) {
                    $q->whereIn('emp_name', $fz)
                        ->orWhereIn("number", $fz);
                });
            })
            ->select('id','number','emp_name as name')
            ->get()->toArray();
        $emp_map = $emp_map1 = [];
        foreach ($empList as $value){
            $emp_map[$value['name']][] = $value['id'];
            $emp_map1[$value['number']] = $value['id'];
        }

        $departList = Depart::where('del_time',0)
            ->where('parent_id', 0)
            ->whereIn('title', $fp)
            ->select('title','id','notify_id')
            ->get()->toArray();
        $depart = array_column($departList,null,'title');

        $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');

        list($status, $msg) = $this->customerFilterSecond($array,$time,$head,$user,$contact_info_array,$customer,$basic_list,$emp_map,$emp_map1,$basic_use,$depart);
        if(! $status) return [0, $msg];
        list($insert, $insert_detail, $insert_detail2, $insert_detail3) = $msg;

        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);
            $insert_detail_3 = [];
            foreach ($insert_detail3 as $key => $value){
                $value['customer_id'] = $last_insert_id[$key];
                $insert_detail_3[] = $value;
            }unset($insert_detail3);

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

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

        return [true, ''];
    }

    function customerFilterFirst(&$array, $time){
        $array_clean = $contact_info = $fz = $fp =[];

        $error = [];
        foreach ($array as $key => $value){
            $line = '第' . $key . '行';
            $rowData = array_filter($value);
            if (empty($rowData)) {
                unset($array[$key]);
            } else{
                foreach ($value as $k => $v){
                    $value[$k] = trim($v);
                }

                if(empty($value[0])){
                    $error[] = $line . '客户模板字段项必填';
                }else{
                    if(! isset(Customer::dk[$value[0]])) {
                        $error[] = $line . '客户模板填写错误';
                    }else{
                        $value[0] = Customer::dk[$value[0]];
                    }
                }
                if(empty($value[1])){
                    $error[] = $line . '客户名称字段项必填';
                }else{
                    if(in_array($value[1],$array_clean)) {
                        $error[] = $line . '客户名称' . $value[1] .'在导入文件中重复出现';
                    }else{
                        $array_clean[] = $value[1];
                    }
                }

                if(! empty($value[12])){
                    if(in_array($value[12],$contact_info)) {
                        $error[] = $line . '联系方式内容'. $value[12] .'在导入文件内重复出现';
                    }else{
                        $contact_info[] = $value[12];
                    }
                }

                if(! empty($value[14])){
                    list($status, $msg) = $this->convertExcelCellToDate($value[14]);
                    if(! $status) {
                        $error[] = $line . $msg;
                    }else{
                        $value[14] = $msg;
                    }
                } else{
                    $value[14] = $time;
                }

                if(empty($value[15])) {
                    if(empty($value[13])) {
                        $error[] = $line . '(分配门店为空时)负责人名或负责人工号必须填写';
                    }else{
                        if(! in_array($value[13], $fz)) $fz[] = $value[13];
                    }
                }else{
                    if(! in_array($value[15], $fp)) $fp[] = $value[15];
                }

                $array[$key] = $value;
            }
        }unset($array_clean);

        if(! empty($error)){
            $error = implode(',', $error);
            return [false, $error];
        }

        return [true, [$contact_info, $fz, $fp]];
    }

    function customerFilterSecond($array, $time, $head, $user, $contact_info_array,$customer,$basic_list,$emp_name,$emp_number,$basic_use,$depart){
        $error = $insert = $insert_detail = $insert_detail2 = $insert_detail3 = $repeat_array = [];
        foreach ($array as $key => $value){
            $line = '第' . $key . '行';
            $tmp = [
                'model_type' => '',
                'title' => '',
                'customer_intention' => '',
                'customer_from' => '',
                'customer_type' => '',
                'car_type' => '',
                'consulting_product_new' => '',
//                '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,
                'enter_time' => $value['14'],
            ];
            $tmp['model_type'] = $value['0'];
            $tmp['mark'] = $value['9'];
            $tmp['address2'] = $value['10'];
            $repeat = 0;//是否重复

            if(! empty($customer[$value['1']])) {
//                $error[] = $line . '客户名称:' . $value['1'] . '已存在';
//                $repeat = 1;
            }else{
                $tmp['title'] = $value['1'];
            }
            if($value['2']){
                if(empty($basic_list[2][$value['2']])) {
                    $error[] = $line . '客户意向度:' . $value['2'] . '不存在';
                }else{
                    $tmp['customer_intention'] = $basic_list[2][$value['2']];
                }
            }
            if($value['3']){
                if(empty($basic_list[3][$value['3']])) {
                    $error[] = $line . '客户来源:' . $value['3'] . '不存在';
                }else{
                    $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']])) {
                    $error[] = $line . '模板:' . $model_title . '下客户类别:' . $value['4'] . '不存在';
                }else{
                    $tmp['customer_type'] = $basic_list[$keys][$value['4']];
                }
            }
            if($value['5']){
                if(empty($basic_use[$value['5']])) {
                    $error[] = $line . '咨询产品:' . $value['5'] . '不存在';
                }else{
                    $tmp['consulting_product_new'] = $basic_use[$value['5']];
                }
            }
//            if($value['6']){
//                if(empty($product[$value['6']])) {
//                    $error[] = $line . '意向产品:' . $value['6'] . '不存在';
//                }else{
//                    $tmp['intention_product'] = $product[$value['6']];
//                }
//            }
            if($value['6']){
                if(empty($basic_list[7][$value['6']])) {
                    $error[] = $line . '进展阶段:' . $value['6'] . '不存在';
                }else{
                    $tmp['progress_stage'] = $basic_list[7][$value['6']];
                }
            }
            if($value['7']){
                if(empty($basic_list[8][$value['7']])) {
                    $error[] = $line . '状态:' . $value['7'] . '不存在';
                }else{
                    $tmp['state_type'] = $basic_list[8][$value['7']];
                }
            }
            if($value['8']){
                if(empty($basic_list[9][$value['8']])) {
                    $error[] = $line . '车型:' . $value['8'] . '不存在';
                }else{
                    $tmp['car_type'] = $basic_list[9][$value['8']];
                }
            }
            $contact_id = 0;
            if($value['11']){
                if(empty($basic_list[12][$value['11']])) {
                    $error[] = $line . '联系方式类型:' . $value['11'] . '不存在';
                }else{
                    $contact_id = $basic_list[12][$value['11']];
                }
            }
            if($value['12'] && in_array($value['12'],$contact_info_array)){
                $repeat = 1;
                $error[] = $line . '联系方式内容:' . $value['12'] . '已存在';
            }

            $fp = $man =  0;
            if(empty($value['15'])){
                //分配门店为空 负责人必填
                list($return_man, $return_error) = $this->returnMan($emp_name, $emp_number, $value['13'], $line);
                if(! empty($return_error)) $error[] = $return_error;
                if(! empty($return_man)) $man = $return_man;
            }else{
                if(empty($depart[$value['15']])) {
                    $error[] = $line . '分配门店:' . $value['15'] . '不存在';
                }else{
                    $d_t = $depart[$value['15']];
                    $fp = $d_t['id']; //门店id
                    $fp_man = $d_t['notify_id']; //门店负责人
                    if(empty($fp_man)) {
                        $error[] = $line . '分配门店:' . $value['15'] . '门店负责人暂未设置';
                    }else{
                        if(! empty($value['13'])){
                            list($return_man, $return_error) = $this->returnMan($emp_name, $emp_number, $value['13'], $line);
                            if(! empty($return_error)) $error[] = $return_error;
                            if(! empty($return_man)) $man = $return_man;
                            if($fp_man != $man) $error[] = $line . '分配门店:' . $value['15'] . '门店负责人与用户填写的负责人不一致';
                        }
                    }
                    $man = $fp_man;
                }
            }

            $insert[] = $tmp;
            $insert_detail[] = [
                'customer_id' => 0,
                'contact_type' => $contact_id,
                'contact_info' => $value['12'],
                'crt_time' => $time,
                'type' => CustomerInfo::type_one
            ];
            $insert_detail2[] = [
                'customer_id' => 0,
                'data_id' => $man,
                'crt_time' => $time,
                'type' => CustomerInfo::type_two
            ];
            $insert_detail3[] = [
                'customer_id' => 0,
                'fp' => $fp,
                'fz' => $man,
                'crt_time' => $time,
            ];
            if($repeat){
                $tmp['contact_type'] = $contact_id;
                $tmp['contact'] = $value['12'];
                $tmp['fz_man'] = $man;
                $repeat_array[] = $tmp;
            }
        }

        //重复录入数据记录
        if($repeat_array) CustomerRepeat::insert($repeat_array);

        if(! empty($error)){
            $error = implode(',', $error);
            return [false, $error];
        }

        return [true, [$insert, $insert_detail, $insert_detail2, $insert_detail3]];
    }

    function returnMan($emp_name, $emp_number, $value, $line){
        $return = [];
        $type = 0;
        if(! empty($emp_name[$value])){
            $return = $emp_name[$value];
            $type = 1;
        }else{
            if(! empty($emp_number[$value])) $return = $emp_number[$value];
        }

        $man = 0;$error = "";
        if($type){
            if(count($return) > 1) {
                $error = $line . '负责人名:' . $value . '在系统中多次出现,请填写工号精准定位负责人';
            }else{
                $man = $return[0];
            }
        }else{
            if(! empty($return)){
                $man = $return;
            }else{
                $error = $line . '负责人工号:' . $value . '不存在';
            }
        }

        return [$man, $error];
    }

    function convertExcelCellToDate($cellValue) {
        // 尝试将单元格值转换为浮点数（Excel 日期序列号）
        $excelTimestamp = filter_var($cellValue, FILTER_VALIDATE_FLOAT);

        if ($excelTimestamp !== false && $excelTimestamp > 0) {
            // 如果成功转换并且值大于0，则认为是Excel日期序列号
            try {
                $dateTimeObject = Date::excelToDateTimeObject($cellValue);

                if ($dateTimeObject->format('H:i:s') === '00:00:00') {
                    // 如果是，则将时间设置为 '23:59:00'
                    $dateTimeObject->setTime(23, 59);
                }

                // 现在你可以格式化这个日期了
                $formattedDate = $dateTimeObject->format('Y-m-d H:i:s');
                if(! strtotime($formattedDate)) return [false, '录入日期请填写正确的日期'];

                return [true,  strtotime($formattedDate)];
            } catch (\Exception $e) {
                // 处理转换失败的情况
                return [false, '单元格日期格式转换时间戳失败'];
            }
        }

        // 如果不是有效的浮点数，则尝试按照多种日期格式解析
        if(! strtotime($cellValue)) return [false, '单元格文本格式转换时间戳失败'];

        return [true, strtotime($cellValue)];
    }

    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)
            ->select('id','title','parent_id')
            ->where('top_depart_id',$top_depart_id)
            ->get()->toArray();

        // 创建一个以 id 为键的数据映射
        $idMap = [];
        foreach ($category_list as $item) {
            $idMap[$item['id']] = $item;
        }
        // 创建最终的结果数组
        $category_parent = [];
        foreach ($idMap as $id => $item) {
            // 如果没有子项，则处理
            if (! $this->hasChildren($id, $idMap)) {
                $category_parent[$id] = $this->getAncestors($id, $idMap);
            }
        }
        $category_map = array_column($category_list,'id','title');

        //基础类型
        $basic = (new BasicTypeService())->getMyBasicList($user, 20);
        $basic = array_column($basic,'id','title');

        $time = time();
        //表头
        $table_head = $this->product([],$user);
        $heads = $table_head[1];
        $head_value = array_column($heads,'value');
        if($head_value !== $upload) return [false, '产品模板错误，请重新下载模板导入'];

        //写入数据库的字段
        $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 = $insert_detail = $update = $update2 = $update_detail = [];
        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_map[$value['2']])) return [false,'产品分类:' . $value['2'] . '不存在'];
            $tmp['product_category_id'] = $category_map[$value['2']];
            if(! isset($category_parent[$tmp['product_category_id']])) return [false,'产品分类:' . $value['2'] . '下存在子分类,请将产品建与最底层分类下'];
            $parentsId = $category_parent[$tmp['product_category_id']];
            $tmp['product_category'] = json_encode($parentsId);
            $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;
            $tmp['build_fee'] = $value['9'] ?? 0;
            $tmp['item_code'] = $value['10'] ?? "";
            $tmp['warranty_time'] = $value['11'] ?? 0;
            $tmp['product_item_code_title'] = $value['12'] ?? "";
            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]);
                    }
                }

                //规格
                if($tmp['size'] == "") unset($tmp['size']);
                //单位
                if($tmp['unit'] == "") unset($tmp['unit']);
                //条码
                if($tmp['bar_code'] == "") unset($tmp['bar_code']);
                //成本
                if($tmp['cost'] == "") unset($tmp['cost']);
                //零售
                if($tmp['retail_price'] == "") unset($tmp['retail_price']);
                //产品属性
                if($tmp['product_attribute'] == "") unset($tmp['product_attribute']);
                //安装费
                if($tmp['build_fee'] == "") unset($tmp['build_fee']);
                //商品编码
                if($tmp['item_code'] == "") unset($tmp['item_code']);

                if(! empty($tmp['item_code']) && $tmp['product_item_code_title'] != "" && ! isset($update_detail[$tmp['item_code']])) $update_detail[$tmp['item_code']] = [
                    'item_code' => $tmp['item_code'],
                    'title' => $tmp['product_item_code_title']
                ];
                unset($tmp['product_item_code_title']);

                //质保年月
                if($tmp['warranty_time'] == "") unset($tmp['warranty_time']);

                //产品主表
                $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]);
                    }
                }

                if(! empty($tmp['item_code']) && $tmp['product_item_code_title'] != "" && ! isset($insert_detail[$tmp['item_code']])) $insert_detail[$tmp['item_code']] = [
                    'item_code' => $tmp['item_code'],
                    'title' => $tmp['product_item_code_title']
                ];
                unset($tmp['product_item_code_title']);

                //产品主表
                $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($insert_detail)) ProductItemCodeMessage::insert(array_values($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)
                        ->where('del_time',0)
                        ->update(['del_time' => $time]);
                    ProductPriceDetail::insert($update2);
                }

                if(! empty($update_detail)) {
                    $item_code = array_keys($update_detail);
                    ProductItemCodeMessage::where('del_time',0)
                        ->whereIn('item_code',$item_code)
                        ->update(['del_time' => $time]);
                    ProductItemCodeMessage::insert(array_values($update_detail));
                }
            }
            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            return [false, $e->getMessage() . $e->getLine() . $e->getCode()];
        }

        return [true, ''];
    }

    function convertHyphens($string) {
        // 定义要被替换的字符
        $search = array('－', '-', '—');  // 全角连字符、半角连字符 和 破折号
        $replace = '-';             // 替换成半角连字符

        // 执行替换
        $res = str_replace($search, $replace, $string);
        return $res;
    }

    function generateTitleToIdMap($tree, $prefix = '') {
        $result = [];

        foreach ($tree as $node) {
            $currentTitle = $prefix . $node['title'];
            $result[$currentTitle] = $node['id'];

            if (!empty($node['children'])) {
                $result = array_merge(
                    $result,
                    $this->generateTitleToIdMap($node['children'], $currentTitle . '-')
                );
            }
        }

        return $result;
    }

    function getAncestors($id, &$idMap) {
        if (!isset($idMap[$id])) return [];

        $ancestors = [$id];
        $parentId = $idMap[$id]['parent_id'];

        if ($parentId !== null) {
            // 递归调用自己来添加父级 ID
            $ancestors = array_merge($this->getAncestors($parentId, $idMap), $ancestors);
        }

        return $ancestors;
    }

    // 检查是否拥有子项
    function hasChildren($id, &$idMap) {
        foreach ($idMap as $item) {
            if ($item['parent_id'] == $id) {
                return true;
            }
        }
        return false;
    }

    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, ''];
    }

    public function inventoryImport($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])) {
                if($key != 1){
                    if(empty($value[1])) return [false, '带*号的字段项必填'];
                } else{
                    return [false, '带*号的字段项必填'];
                }
            }else{
                foreach ($value as $k => $v){
                    $value[$k] = trim($v);
                }
                if($value[0] && ! in_array($value[0],$shop_name)) $shop_name[] = $value[0];
                if(! in_array($value[1],$product_code)) {
                    $product_code[] = $value[1];
                }else{
                    return [false, '产品编号:' . $value[1] .'已存在'];
                }
                if(! is_numeric($value[2])) return [false, '* 盘点数量请填写正确的数值'];

                if(! empty($value[3])){
                    $bool = $this->checkNumber($value[3]);
                    if(! $bool) return [false, '盘点单价请输入大于等于0的两位小数的数字'];
                }
            }
        }
        if(count($shop_name) != 1) return [false, '只允许单门店盘点'];

        //门店信息
        $depart = Depart::whereIn('title',$shop_name)
            ->where('del_time',0)
            ->where('parent_id',0)
            ->select('id','title','basic_type_id')
            ->first();
        if(empty($depart)) return [false, "门店:" .$shop_name . "不存在或已被删除"];
        $depart = $depart->toArray();
        $top_depart_id = $depart['id'];
        $basic_type_id = $depart['basic_type_id'];

        //门店的仓库
        $storehouse = Storehouse::where('top_depart_id', $top_depart_id)
            ->where('del_time',0)
            ->select('id')
            ->first();
        if(empty($storehouse)) return [false, "门店:" .$shop_name . "的仓库不存在或已被删除"];
        $storehouse = $storehouse->toArray();
        $storehouse_id = $storehouse['id'];
        if(empty($storehouse_id)) return [false, "门店:" .$shop_name . "的仓库不存在或已被删除"];

        //产品
        $model = Product::ProductClear($user,[]);
        $product = $model->whereIn('code',$product_code)
            ->where('del_time',0)
            ->select('id','code','retail_price')
            ->get()->toArray();
        $product_map = array_column($product,'id','code');
        $product_map_2 = array_column($product,'retail_price','id');

        //产品使用价格
        $product_use = [];
        if(! empty($basic_type_id)){
            $product_id = array_column($product,'id');
            $service = new ProductService();
            $detail_map = $service->getProductPrice($product_id, 1, $basic_type_id);
            foreach ($detail_map as $key => $value){
                if(isset($value[$basic_type_id]) && ! empty($value[$basic_type_id]['price'])) {
                    $product_use[$key] = $value[$basic_type_id]['price'];
                }else{
                    $product_use[$key] = $product_map_2[$key] ?? 0;
                }
            }
        }else{
            $product_use = $product_map_2;
        }

        $time = time();
        $product_submit = $product_id = $sub = [];
        foreach ($array as $value){
            $product_tmp = $product_map[$value[1]] ?? 0;
            if($product_tmp <= 0) return [false, "产品编码:" . $value[1] . "不存在或已被删除"];

            if(isset($value[3])){
                $bool = $this->checkNumber($value[3]);
                if(! $bool) return [false, '盘点单价不合法,需是大于等于0的两位数值'];
                $price = $value[3];
            }else{
                if(! isset($product_use[$product_tmp]) || floatval($product_use[$product_tmp]) < 0) return [false, "产品编码:" . $value[1] . "获取单价所得小于0,请检查门店所设置价格以及产品零售价"];
                $price = $product_use[$product_tmp];
            }

            $key = $product_tmp . ',' . $storehouse_id;
            if(! isset($product_submit[$key])) $product_submit[$key] = 0;

            //总的产品id
            $product_id[] = $product_tmp;

            $sub[] = [
                'product_id' => $product_tmp,
                'storehouse_id' => $storehouse_id,
                'order_number' => "",
                'counted_num' => $value[2],
                'book_num' => 0,
                'final_num' => 0,
                'inventory_id' => 0,
                'price' => $price,
            ];
        }

        //库存校验
        $set = ProductInventorySet::where('top_depart_id',$top_depart_id)->where('del_time',0)->first();
        if(empty($set)) return [false, '门店是否校验库存设置缺失'];
        $set = $set->toArray();
        $is_check_stock = $set['param_one'] <= 0 ? ProductInventorySet::type_one : $set['param_one'];

        //获取库存
        $inventory_map = [];
        $inventory = ProductInventoryService::getRealStock($product_id, $storehouse_id);
        foreach ($inventory as $value){
            $key = $value['product_id'] . ',' . $value['storehouse_id'];
            $inventory_map[$key] = $value['real_number'];
        }

        $order_number = (new OrderNoService())->createOrderNumber(Inventory::prefix);
        $product_submit_reduce = $product_reduce_id = [];
        //盘点详情
        foreach ($sub as $key => $value){
            $sub[$key]['order_number'] = $order_number;
            $keys = $value['product_id'] . ',' . $value['storehouse_id'];
            if(isset($inventory_map[$keys])){
                $sub[$key]['book_num'] = $inventory_map[$keys];
                $num = bcsub($value['counted_num'], $inventory_map[$keys],4);
                $sub[$key]['final_num'] = $num;
            }else{
                $sub[$key]['final_num'] = $value['counted_num'];
            }

            $final_amount = bcmul($sub[$key]['final_num'], $value['price'],2);
            $sub[$key]['final_amount'] = $final_amount;

            if($sub[$key]['final_num'] < 0) {
                $product_reduce_id[] = $value['product_id'];
                $product_submit_reduce[$keys] = $sub[$key]['final_num'];
            }
        }

        //校验是否存在产品盘点
        list($status, $msg) = (new InventoryService())->issetProduct(['top_depart_id' => $top_depart_id], $product_submit);
        if(! $status) return [false, $msg];

        if(! empty($product_submit_reduce)){
            //校验库存
            list($status,$msg) = (new ProductInventoryService())->compareStock($user,$product_reduce_id, $product_submit_reduce);
            if(! $status) return [false, $msg];
        }

        if(empty($sub)) return [false, '暂无需要写入的盘点数据'];

        try{
            DB::beginTransaction();

            $inventory_model = new Inventory();
            $inventory_model->order_number = $order_number;
            $inventory_model->storehouse_id = $storehouse_id;
            $inventory_model->counted_id = $user['id'];
            $inventory_model->counted_time = $time;
            $inventory_model->depart_id = $top_depart_id;
            $inventory_model->top_depart_id = $top_depart_id;
            $inventory_model->crt_id = $user['id'];
            $inventory_model->save();

            foreach ($sub as $key => $value){
                $sub[$key]['inventory_id'] = $inventory_model->id;
            }

            //写入数据
            InventorySub::insert($sub);

            //单据创建时是否校验库存
            (new CheckService())->orderInventoryInsert(['order_number' => $order_number, 'is_check_stock' => $is_check_stock]);

            DB::commit();
        }catch (\Exception $exception){
            DB::rollBack();
            if (str_contains($exception->getMessage(), '1062') || str_contains($exception->getMessage(), 'Duplicate entry')) {
                return [false, '网络波动，请重新操作！'];
            }
            return [false, $exception->getMessage() . $exception->getLine() . $exception->getCode()];
        }

        return [true, ''];
    }

    public function productAdjustmentImport($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]) || empty($value[2])) {
                if($key != 1){
                    if(empty($value[1])) return [false, '带*号的字段项必填'];
                } else{
                    return [false, '带*号的字段项必填'];
                }
            }else{
                foreach ($value as $k => $v){
                    $value[$k] = trim($v);
                }
                if($value[0] && ! in_array($value[0],$shop_name)) $shop_name[] = $value[0];
                if(! in_array($value[1],$product_code)) {
                    $product_code[] = $value[1];
                }else{
                    return [false, '产品编号:' . $value[1] .'已存在'];
                }
                if(! is_numeric($value[2])) return [false, '调整金额请填写正确的数值'];
            }
        }
        if(count($shop_name) != 1) return [false, '只允许单门店创建调整金额'];

        //门店信息
        $depart = Depart::whereIn('title',$shop_name)
            ->where('del_time',0)
            ->where('parent_id',0)
            ->select('id','title')
            ->first();
        if(empty($depart)) return [false, "门店:" .$shop_name . "不存在或已被删除"];
        $depart = $depart->toArray();
        $top_depart_id = $depart['id'];

        //门店的仓库
        $storehouse = Storehouse::where('top_depart_id', $top_depart_id)
            ->where('del_time',0)
            ->select('id')
            ->first();
        if(empty($storehouse)) return [false, "门店:" .$shop_name . "的仓库不存在或已被删除"];
        $storehouse = $storehouse->toArray();
        $storehouse_id = $storehouse['id'];
        if(empty($storehouse_id)) return [false, "门店:" .$shop_name . "的仓库不存在或已被删除"];

        //产品
        $model = Product::ProductClear($user,[]);
        $product = $model->whereIn('code',$product_code)
            ->where('del_time',0)
            ->select('id','code')
            ->get()->toArray();
        $product_map = array_column($product,'id','code');

        $product_submit = [];
        foreach ($product as $value){
            $key = $value['id'] . ',' . $storehouse_id;
            if(! isset($product_submit[$key])) $product_submit[$key] = 0;
        }
        //校验是否存在产品调整
        list($status, $msg) = (new ProductAdjustService())->issetProduct(['top_depart_id' => $top_depart_id], $product_submit);
        if(! $status) return [false, $msg];

        $time = time();
        $sub = [];
        foreach ($array as $value){
            $product_tmp = $product_map[$value[1]] ?? 0;
            if($product_tmp <= 0) return [false, "产品编码:" . $value[1] . "不存在或已被删除"];

            $sub[] = [
                'product_id' => $product_tmp,
                'storehouse_id' => $storehouse_id,
                'order_number' => "",
                'product_adjustment_id' => 0,
                'final_amount' => $value[2],
            ];
        }
        if(empty($sub)) return [false, '暂无需要写入的产品调整单数据'];

        try{
            $order_number = (new OrderNoService())->createOrderNumber(ProductAdjustment::prefix);

            DB::beginTransaction();

            $inventory_model = new ProductAdjustment();
            $inventory_model->order_number = $order_number;
            $inventory_model->storehouse_id = $storehouse_id;
            $inventory_model->counted_id = $user['id'];
            $inventory_model->counted_time = $time;
            $inventory_model->depart_id = $top_depart_id;
            $inventory_model->top_depart_id = $top_depart_id;
            $inventory_model->crt_id = $user['id'];
            $inventory_model->save();

            foreach ($sub as $key => $value){
                $sub[$key]['product_adjustment_id'] = $inventory_model->id;
                $sub[$key]['order_number'] = $order_number;
            }

            //写入数据
            ProductAdjustmentSub::insert($sub);

            DB::commit();
        }catch (\Exception $exception){
            DB::rollBack();
            if (str_contains($exception->getMessage(), '1062') || str_contains($exception->getMessage(), 'Duplicate entry')) {
                return [false, '网络波动，请重新操作！'];
            }
            return [false, $exception->getMessage() . $exception->getLine() . $exception->getCode()];
        }

        return [true, ''];
    }

    public function customerPerformanceImport($array, $user, $other_param){
        if(empty($other_param["ym"])) return [false,'请选择导入客资业绩年月'];

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

        $insert = [];
        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(! empty($value[2]) && ! is_numeric($value[2])) return [false, '当月打款金额请填写正确的数值'];
            }

            $insert[] = [
                'area' => $value['0'],
                'title' => $value['1'],
                'current_month_payment' => $value['2'] ?? 0,
                'crt_time' => $other_param['ym']
            ];
        }

        $start_time = strtotime(date("Y-m-01 00:00:00", $other_param['ym']));
        $end_time = strtotime(date("Y-m-t 23:59:59", $other_param['ym']));
        CustomerPerFormance::where('del_time',0)
            ->where('crt_time','>=', $start_time)
            ->where('crt_time','<=', $end_time)
            ->update(['del_time' => time()]);

        if(! empty($insert)) CustomerPerFormance::insert($insert);

        return [true, ''];
    }
}