drb/app/Service/ImportService.php

<?php

namespace App\Service;

use App\Exports\TableHeadExport;
use App\Import\ImportAll;
use App\Model\Freight;
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 = [
        'product', //存货
        'freight', //运费设置
    ];

    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');
        Excel::store(new TableHeadExport([], $headers),"/public/export/{$filename}", null, 'Xlsx', []);
        return [true, ['file' => $filename]];
    }

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

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

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

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

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

    private function freight($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 productImport($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, '导入数据不能为空'];

        //查找产品
        $product_map = Product::whereIn("code", array_column($array,0))
            ->where('del_time',0)
            ->pluck('id','code')
            ->toArray();

        $time = time();
        $insert = $update = [];
        foreach ($array as $value){
            $tmp = [];
            foreach ($value as $k => $val){
                $field = $table_config[$k]['key'];
                $tmp[$field] = $val;
            }

            if(isset($product_map[$tmp['code']])){
                $product_id = $product_map[$tmp['code']];
                //产品主表
                $update[$product_id] = $tmp;
            }else{
                $tmp['crt_id'] = $user['id'];
                $tmp['crt_time'] = $time;

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

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

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

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

        return [true, ''];
    }

    public function freightImport($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, '导入数据不能为空'];

        $time = time();
        $insert = [];
        foreach ($array as $value){
            $tmp = [];
            foreach ($value as $k => $val){
                $field = $table_config[$k]['key'];
                $tmp[$field] = $val;
            }

            $tmp['crt_id'] = $user['id'];
            $tmp['crt_time'] = $time;

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

        try{
            Freight::where('del_time',0)
                ->update(['del_time' => $time]);
            //新增
            if(! empty($insert)) Freight::insert($insert);

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

        return [true, ''];
    }

    //公共校验
    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];
    }
}