qyadmin/app/Import/Import.php

<?php


namespace  App\Import;

use App\Service\ImportService;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\ToArray;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Facades\Excel;


class Import implements WithMultipleSheets,ToArray {
    private $msg = '';
    public $crt_id = 0;
    public $config = [];
    public $mergedCells = [];
    public $data = [];

    public function sheets(): array
    {
        return [
            0 => $this, // 指定要导入的 sheet 索引，这里假设是第一个 sheet
        ];
    }

    public function array (array $array){
        $this->handleData($array);
    }

    public function setConfig($config,$mergedCells,$data){
        $this->config = $config;
        $this->mergedCells = $mergedCells;
        $this->data = $data;
    }

    public function getMsg(){
        return $this->msg;
    }

    public function setMsg($msg){
        $this->msg = $msg;
    }

    public function handleData (array $array) {
        //表头处理
        list($status,$msg) = $this->clearHead($array);
        if(! $status) {
            $this->setMsg($msg);
            return ;
        }

        //表头
        $table_head = $msg;

        // 去除表头
        unset($array[0]);
        if(empty($array)) {
            $this->setMsg('导入数据不能为空！');
            return ;
        }

        //数据处理
        list($status,$msg) = $this->checkRule($table_head,$array);
        if(! $status) {
            $this->setMsg($msg);
            return ;
        }

        //写入数据
        $time = time();
        list($status,$msg) = $this->insertAllData($msg, $time);
        if(! $status) {
            $this->setMsg($msg);
            return ;
        }

        //（特殊 需要额外写入的数据） 做对写入数据的更新
        if(! empty($this->config['other_field_func'])){
            $func = $this->config['other_field_func'] ?? "";
            if(empty($func)) return;
            (new ImportService)->$func($time);
        }
    }

    public function clearHead($array){
        $table_head = array_filter($array[0]);
        if(empty($table_head)) return [false, '表头不能为空！'];

        foreach ($table_head as $key => $value){
            $head_tmp = trim($value);
            if(empty($head_tmp)) {
                unset($table_head[$key]);
            }else{
                $table_head[$key] = $head_tmp;
            }
        }
        if(empty($table_head)) return [false, '表头不能为空！'];

        return [true, $table_head];
    }

    public function insertAllData($data, $time){
        $model_array = $this->config['table'];

        //执行写入
        try{
            DB::beginTransaction();

            foreach ($model_array as $model_name => $model_field){
                if(! empty($model_field['need_param'])) {
                    $model = "\App\Model\\" . $model_field['db_name'];
                    $last_insert_id = $model::where('crt_time',$time)
                        ->select($model_field['db_key'])
                        ->get()->toArray();
                    $last_insert_id = array_column($last_insert_id,$model_field['db_key']);
                }
                $tmp = [];$insert = [];
                if(! empty($model_field['field'])){
                    foreach ($model_field['field'] as $m){
                        $tmp[$m] = "";
                        if(strstr($m,'time')) $tmp[$m] = $time;
                        if($m == "depart_id") $tmp[$m] = $this->data['depart_id'] ?? 0;
                        if($m == "top_depart_id") $tmp[$m] = $this->data['top_depart_id'] ?? 0;
                        if($m == "crt_id") $tmp[$m] = $this->data['user_id'] ?? 0;
                    }
                    foreach ($data as $value){
                        $insert[] = array_intersect_key(array_merge($tmp, $value),$tmp);
                    }
                }elseif (! empty($model_field['field_array'])){
                    foreach ($model_field['field_array'] as $km => $m){
                        foreach ($m as $v){
                            $tmp[$v] = "";
                            if(strstr($v,'time')) $tmp[$v] = $time;
                        }
                        foreach ($data as $key => $value){
                            foreach ($value[$km] as $vv){
                                $vv[$model_field['need_param']] = $last_insert_id[$key];
                                $insert[] = array_intersect_key(array_merge($tmp, $vv),$tmp);
                            }
                        }
                    }
                }

                $model = "\App\Model\\" . $model_name;
                $model::insert($insert);
            }

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

        return [true, ''];
    }

    public function checkRule($head, $data){
        //模板里的表头
        foreach ($this->config['field'] as $key => $value){
            if(! in_array($key, $head)) return [false, "缺少表头：" . $key];
        }

        //处理 =》 存在合并单元格数据
        if(! empty($this->mergedCells)) $data = $this->clearMergeData($data);

        //数据校验
        list($status,$msg) = $this->checkData($head,$data);
        return [$status,$msg];
    }

    function checkData($head,$data){
        $return_data = [];

        //数据处理
        foreach ($this->config['field'] as $key => $value){
            //找到表头在xlsx中的下标位置
            $key2 = array_search($key, $head);
            if ($key2 === false) return [false, '未找到表头在xlsx中的位置！'];
            //获取表头对应列数据
            $tmp_data = array_column($data,$key2);
            //去除数据中两侧的空白字符或其他预定义字符
            foreach ($tmp_data as $v_key => $v_data){
                $tmp_data[$v_key] = trim($v_data);
            }

            //------ 根据规则 查找数据 =>做 替换和校验--------------//
            //校验数据唯一
            if (strpos($value['other_rule'], 'unique') !== false) {
                $uniqueValue = $this->getOtherRuleModelName($value,"unique");
                $model = "\App\Model\\" . $uniqueValue;
                $newStr = "";
                if( ! empty($value['search_raw'])){
                    $str = $value['search_raw'];
                    foreach ($value['search_field'] as $value_field){
                        if(isset($this->data[$value_field])){
                            $v = $this->data[$value_field];
                            $newStr = str_replace("?", $v, $str);
                        }
                    }
                    if( ! empty($newStr)) $model::whereRaw($newStr);
                }
                $bool = $model::whereIn($value['key'], array_unique($tmp_data))
                    ->where('del_time',0)
                    ->exists();
                if($bool) return [false, $key . "在已存在"];
            }

            //替换数据
            if(! empty($value['db_search'])){
                $db = $value['db_search'];
                $model = "\App\Model\\" . $db['db_name'];
                $map = $model::whereIn($db['key'], array_unique($tmp_data))
                    ->pluck($db['value'], $db['key'])
                    ->toArray();
            }

            //常量字典
            if(strpos($value['other_rule'], 'dk') !== false){
                $uniqueValue = $this->getOtherRuleModelName($value,"dk");
                $model = "\App\Model\\" . $uniqueValue;
                $constantValue = constant($model . '::' . $value['dk_name']);
            }
            //------ 根据规则 查找数据 =>做 替换和校验--------------//


            //校验&&组织
            $tmp_clean = [];
            foreach ($tmp_data as $v_key => $v_data){
                //常量字典
                if(strpos($value['other_rule'], 'dk') !== false){
                    if(! isset($constantValue[$v_data])) return [false, $key . '在系统中不存在！'];
                    $v_data = $constantValue[$v_data];
                }

                if (strpos($value['other_rule'], 'require') !== false) {
                    if(empty($v_data)) return [false, $key . '不能为空！'];
                }
                if (strpos($value['other_rule'], 'unique') !== false) {
                    if(in_array($v_data,$tmp_clean)) return [false, $key . '不能重复！'];
                    $tmp_clean[] = $v_data;
                }
                if (strpos($value['other_rule'], 'is_numeric') !== false && ! empty($v_data)) {
                    if(! is_numeric($v_data))  return [false, $key . '请输入数字！'];
                    $formattedNumber = number_format($v_data, 2, '.', '');
                    if($formattedNumber != $v_data) return [false, $key . '请输入数字（请输入不超过两位小数）！'];
                }
                if(! empty($value['db_search'])) {
                    if(! empty($v_data)){
                        if(empty($map[$v_data])) return [false, $key . '下所输入的内容在系统中不存在！'];
                        $v_data = $map[$v_data];
                    }
                }

                if(! empty($value['multiple'])) {
                    $field = $value['map'][$key] ?? "";
                    $value['key_array'][$field] = $v_data;

                    //子表数据 一对多
                    $return_data[$v_key][$value['key']][] = $value['key_array'];
                }else{
                    //数据
                    $return_data[$v_key][$value['key']] = $v_data;
                }
            }
        }
        unset($tmp_clean);dd($return_data);//TODO

        return [true, $return_data];
    }

    function getOtherRuleModelName($value,$needle){
        $needle = $needle . ":";
        $uniqueIndex = strpos($value['other_rule'], $needle);
        $start = $uniqueIndex + strlen($needle);
        $end = strpos($value['other_rule'], '|', $start);
        if ($end !== false) {
            $uniqueValue = substr($value['other_rule'], $start, $end - $start);
        } else {
            $uniqueValue = substr($value['other_rule'], $start);
        }
        return $uniqueValue;
    }

    // 处理合并单元格数据
    function clearMergeData($data){
        //获取行列
        $rows = count($data);
        $cols = count($data[1]);

        //非合并单元格并且为null的数据 替换成空字符串
        for ($row = 1; $row < $rows; $row++) {
            // 遍历每一列
            for ($col = 0; $col < $cols; $col++) {
                // 如果当前值为 null，并且不是合并单元格
                if ($data[$row][$col] === null && ! $this->isMergedCell($this->mergedCells, $row, $col)) {
                    $data[$row][$col] = '';
                } else {
                    $data[$row][$col] = $data[$row][$col];
                }
            }
        }

        //合并单元格数据替换为原来的数据
        for ($row = 1; $row <= $rows; $row++) {
            // 遍历每一列
            for ($col = 0; $col < $cols; $col++) {
                // 如果当前值为 null
                if ($data[$row][$col] === null) {
                    // 查找左方第一个非 null 值
                    for ($i = $col - 1; $i >= 0; $i--) {
                        if ($data[$row][$i] !== null) {
                            $data[$row][$col] = $data[$row][$i];
                            break;
                        }
                    }
                    // 如果左方没有非 null 值，则查找上方第一个非 null 值
                    if (!isset($newArray[$row][$col])) {
                        for ($i = $row - 1; $i >= 1; $i--) {
                            if ($data[$i][$col] !== null) {
                                $data[$row][$col] = $data[$i][$col];
                                break;
                            }
                        }
                    }
                } else {
                    $data[$row][$col] = $data[$row][$col];
                }
            }
        }

        return $data;
    }

    // 判断一个单元格是否是合并单元格
    function isMergedCell($mergeCells, $row, $col) {
        foreach ($mergeCells as $mergeCell) {
            [$start, $end] = explode(':', $mergeCell);
            [$startCol, $startRow] = $this->coordinateToIndex($start);
            [$endCol, $endRow] = $this->coordinateToIndex($end);

            if ($col >= $startCol && $col <= $endCol && $row >= $startRow && $row <= $endRow) {
                return true;
            }
        }

        return false;
    }

    // 将 Excel 单元格坐标转换为索引
    function coordinateToIndex($coordinate) {
        preg_match('/([A-Z]+)(\d+)/', $coordinate, $matches);
        [, $col, $row] = $matches;

        $colIndex = 0;
        $length = strlen($col);
        for ($i = 0; $i < $length; $i++) {
            $colIndex += (ord($col[$i]) - 65 + 1) * pow(26, $length - $i - 1);
        }

        return [$colIndex - 1, $row - 1];
    }
}