hqyt/app/Service/ImportService.php

<?php

namespace App\Service;

use App\Exports\TableHeadExport;
use App\Import\ImportAll;
use App\Model\CustomerSupply;
use App\Model\Employee;
use App\Model\RD;
use App\Model\RdDetails;
use App\Model\Item;
use App\Model\GiveOut;
use App\Model\Product;
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 = [
        'order', //订单
    ];

    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,$return) = $this->$fuc($data,$user);
        list($msg,$filename) = $return;
        if(! $status) return [false, $msg];

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

    private function getTableConfig($type = ""){
        if(empty($type)) return [];

        //获取配置文件
        $config = "excel." . $type;
        return config($config) ?? [];
    }

    private function order($data,$user){
        $config_array = $this->getTableConfig($data['type']);
        if(empty($config_array)) return [false, ['导入配置表头文件不存在','']];

        //生成下载文件
        $filename =  "订单导入模板_" . time() . '.' . 'xlsx';

        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 orderImport($array, $user, $other_param){
        $upload = $array[0];
        list($status, $msg) = $this->compareTableAndReturn($upload, $other_param);
        if(! $status) return [false, $msg];
        $table_config = $msg;

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

        list($array, $error) = $this->checkCommon($array, $table_config);
        if(! empty($error)) return [0, $error];
        if(empty($array)) return [false, '导入数据不能为空'];

        list($error, $map, $total_map) = $this->orderCheck($array, $user);
        if(! empty($error)) return [0, $error];
        $order_map = $this->getOrder($array, $user);

        $time = time();
        $insert = $update = $detail = $order_number = [];
        foreach ($array as $key => $value){
            $main_tmp = $detail_tmp = [];
            foreach ($value as $k => $val){
                $t = $table_config[$k];
                $field = $t['key'];
                if(! empty($t['is_main'])){
                    $main_tmp[$field] = $val;
                }
                if(! empty($t['is_detail'])){
                    $detail_tmp[$field] = $val;
                }
            }

            $main_tmp['customer_id'] = $map[$key]['customer_id'] ?? 0;
            $main_tmp['supply_id'] = $map[$key]['supply_id'] ?? 0;
            $main_tmp['quantity'] = $total_map[$main_tmp['order_number']] ?? 0;
            if(isset($order_map[$main_tmp['order_number']])){
                $order_id = $order_map[$main_tmp['order_number']];
                //更新
                $update[$order_id] = $main_tmp;
            }else{
                $main_tmp['crt_id'] = $user['id'];
                $main_tmp['crt_time'] = $time;

                //新增
                $insert[$main_tmp['order_number']] = $main_tmp;
            }

            $detail_tmp['order_number'] = $main_tmp['order_number'];
            $detail_tmp['crt_time'] = $time;
            $detail[] = $detail_tmp;

            $order_number[] = $main_tmp['order_number'];
        }

        try{
            //新增
            if(! empty($insert)){
                RD::insert($insert);
            }

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

            if(! empty($detail)){
                $maps = RD::where('del_time',0)
                    ->whereIn('order_number',$order_number)
                    ->pluck('id','order_number')
                    ->toArray();
                foreach ($detail as $key => $value){
                    $detail[$key]['order_id'] = $maps[$value['order_number']] ?? 0;
                    unset($detail[$key]['order_number']);
                }
                RdDetails::where('del_time',0)
                    ->where('order_id',array_values($maps))
                    ->update(['del_time' => $time]);

                RdDetails::insert($detail);
            }

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

        return [true, ''];
    }

    private function getCustomerSupply($array, $user){
        //查找客户 供应商
        $customer_codes = array_unique(array_filter(array_column($array,1)));
        $customer_titles = array_unique(array_filter(array_column($array,2)));
        $supply_codes = array_unique(array_filter(array_column($array,3)));
        $supply_titles = array_unique(array_filter(array_column($array,4)));
        $records = CustomerSupply::where('del_time', 0)
            ->where('crt_id', $user['id'])
            ->where(function($query) use ($customer_codes, $customer_titles, $supply_codes, $supply_titles) {
                $query->where(function($q) use ($customer_codes, $customer_titles) {
                    $q->where('type', CustomerSupply::type_one)
                        ->where(function($sub) use ($customer_codes, $customer_titles) {
                            $sub->whereIn('code', $customer_codes)
                                ->orWhereIn('title', $customer_titles);
                        });
                })->orWhere(function($q) use ($supply_codes, $supply_titles) {
                    $q->where('type', CustomerSupply::type_two)
                        ->where(function($sub) use ($supply_codes, $supply_titles) {
                            $sub->whereIn('code', $supply_codes)
                                ->orWhereIn('title', $supply_titles);
                        });
                });
            })
            ->select('id','title','code','type')
            ->get()
            ->toArray();

        return $records;
    }

    private function getOrder($array, $user){
        $order = array_unique(array_filter(array_column($array,0)));
        $records = RD::where('del_time', 0)
            ->where('crt_id', $user['id'])
            ->whereIn('order_number', $order)
            ->pluck('id','order_number')
            ->toArray();

        return $records;
    }

    private function orderCheck(&$array, $user)
    {
        // 查询所有客户 / 供应商档案
        $customer_supply = $this->getCustomerSupply($array, $user);

        // 建立映射表（code、title 都可以匹配到记录）
        $map_by_code  = array_column($customer_supply, null, 'code');
        $map_by_title = [];
        foreach ($customer_supply as $value){
            $map_by_title[$value['title']][] = $value;
        }

        $errors = []; // 存放错误信息
        $success = []; // 存放校验通过的行

        $order_material_map = [];
        $order_total_map = [];    // 统计每个订单号的总数量
        foreach ($array as $rowIndex => $value) {
            $rowNum = $rowIndex; // 行号

            if(! empty($value[5])){
                list($status, $msg) = $this->convertExcelCellToDate($value[5]);
                if(! $status){
                    $error_2[] = "第" . $rowNum ."行" . "的订单日期错误";
                }else{
                    $array[$rowIndex][5] = $msg;
                }
            }

            // -------- 客户校验 --------
            $customer_id = 0;
            if (!empty($value[1]) || !empty($value[2])) {
                $customer_id = $this->checkData($value, $rowNum,'客户',CustomerSupply::type_one, $errors,$map_by_code,$map_by_title);
            }

            // -------- 供应商校验 --------
            $supply_id = 0;
            if (!empty($value[3]) || !empty($value[4])) {
                $supply_id = $this->checkData($value, $rowNum,'供应商',CustomerSupply::type_two, $errors,$map_by_code,$map_by_title);
            }

            $success[$rowNum] = [
                'customer_id' => $customer_id,
                'supply_id'   => $supply_id,
            ];

            // -------- 新增：校验订单号 + 物料编码 不重复 --------
            $order_no = trim($value[0] ?? '');
            $material_code = trim($value[6] ?? '');

            if ($order_no !== '' && $material_code !== '') {
                // 组合唯一 key
                $unique_key = $order_no . '|' . $material_code;

                if (isset($order_material_map[$unique_key])) {
                    // 已经存在重复
                    $firstRow = $order_material_map[$unique_key];
                    $errors[] = "第{$rowNum}行：订单号 {$order_no} 下的物料编码 {$material_code} 重复（首次出现在第 {$firstRow} 行）";
                } else {
                    // 记录首次出现的行号
                    $order_material_map[$unique_key] = $rowNum;
                }
            }

            $qty = $value[10];
            if ($order_no !== '') {
                if (isset($order_total_map[$order_no])) {
                    $tmp_num = bcadd($qty, $order_total_map[$order_no],2);
                    $order_total_map[$order_no] = $tmp_num;
                }else{
                    $order_total_map[$order_no] = $qty;
                }

            }
        }

        $error_string = "";
        if(! empty($errors)) $error_string = implode('|', $errors);

        return [
            $error_string ,
            $success,
            $order_total_map
        ];
    }

    private function checkData($value, $rowNum, $main_title, $type,&$errors,$map_by_code,$map_by_title){
        $customer_id_1 = $customer_id_2 = 0;
        if($type == CustomerSupply::type_one){
            $code  = $value[1] ?? '';
            $title = $value[2] ?? '';
        }else{
            $code  = $value[3] ?? '';
            $title = $value[4] ?? '';
        }

        $match_code = $map_by_code[$code] ?? null;
        $match_title = $map_by_title[$title] ?? null;
        if($code){
            if(empty($match_code) || $match_code['type'] != $type){
                $errors[] = "第{$rowNum}行：{$main_title}编码({$code}) 未在档案中找到";
            }else {
                $customer_id_1 = $match_code['id'];
            }
        }
        if($title){
            if(empty($match_title)){
                $errors[] = "第{$rowNum}行：{$main_title}名称({$title}) 未在档案中找到";
            }elseif (count($match_title) > 1){
                if(empty($code)){
                    $errors[] = "第{$rowNum}行：{$main_title}名称({$title}) 在档案中找到多条数据，请填写客户编码";
                }else{
                    $tmp = array_column($match_title,null,'code');
                    if(isset($tmp[$code])){
                        $t = $tmp[$code];
                        if($t['type'] != $type) {
                            $errors[] = "第{$rowNum}行：{$main_title}名称({$title}) 未在档案中找到";
                        }else{
                            $customer_id_2 = $t['id'];
                        }
                    }else{
                        $errors[] = "第{$rowNum}行：{$main_title}编码和名称对应数据未在档案中找到";
                    }
                }
            }else {
                $tt = $match_title[0] ?? [];
                $customer_id_2 = $tt['id'] ?? 0;
            }
        }
        if($customer_id_1 && $customer_id_2 && $customer_id_1 != $customer_id_2) $errors[] = "第{$rowNum}行：{$main_title}编码和名称对应数据未在档案中找到";

        $customer_id = $customer_id_1 ? $customer_id_1 : $customer_id_2;

        return $customer_id;
    }

    //公共校验
    private function checkCommon($array, $table_config){
        $error = [];

        $uniqueValues = []; // 用于存储需要唯一性检查的值
        // 第一次遍历：收集所有唯一字段的值
        foreach ($array as $key => $value) {
            foreach ($value as $k => $v) {
                if (isset($table_config[$k]) && ! empty($table_config[$k]['unique'])) {
                    $uniqueValues[$k][] = [
                        'value' => trim($v),
                        'line' => $key
                    ];
                }
            }
        }
        // 检查唯一性
        foreach ($uniqueValues as $column => $values) {
            $valueCount = [];
            foreach ($values as $item) {
                $valueCount[$item['value']][] = $item['line'];
            }
            foreach ($valueCount as $value => $lines) {
                if (count($lines) > 1) {
                    $lineNumbers = implode(',', $lines);
                    $error[] = $table_config[$column]['value'] . "重复，在第{$lineNumbers}行出现重复值：{$value}";
                }
            }
        }
        
        // 第二次遍历：进行其他验证
        foreach ($array as $key => $value){
            $line = '第' . $key . '行';
            $rowData = array_filter($value);
            if (empty($rowData)) {
                unset($array[$key]);
            } else{
                foreach ($value as $k => $v){
                    $row = '第' . $k . '列';
                    $tmp_v = trim($v);
                    if(! isset($table_config[$k])){
                        $error[] = $line . $row . "数据不存在";
                    }else{
                        $table_tmp = $table_config[$k] ?? [];
                        if(! empty($table_tmp['required'])){
                            if ($tmp_v === '' || ! isset($tmp_v)) {
                                $error[] = $line . $table_tmp['value'] . "必填";
                            }
                        }
                    }

                    if(empty($tmp_v) && isset($table_tmp['default'])) $tmp_v = $table_tmp['default'];
                    $value[$k] = $tmp_v;
                }

                $array[$key] = $value;
            }
        }

        $error_string = "";
        if(! empty($error)) $error_string = implode('|', $error);

        return [$array, $error_string];
    }

    //模板校验
    private function compareTableAndReturn($upload, $param){
        if(empty($upload)) return [false, '表头不能为空'];
        $config_array = $this->getTableConfig($param['type']);
        if(empty($config_array)) return [false, '导入配置表头文件不存在'];

        foreach ($config_array as $key => $value){
            $key_position = $key + 1;
            if(! isset($upload[$key])) return [false, "第" . $key_position . "列表头缺失"];
            $tmp_v = trim($upload[$key]);
            if($tmp_v != $value['value']) return [false, "第" . $key_position . "列表头与模板不符合,请重新下载模板"];
        }

        return [true, $config_array];
    }

    //转换日期
    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');
                if(! strtotime($formattedDate)) return [false, ''];

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

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

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