workHour/app/Service/ImportService.php

<?php

namespace App\Service;

use App\Exports\TableHeadExport;
use App\Import\ImportAll;
use App\Model\Device;
use App\Model\Employee;
use App\Model\Item;
use App\Model\ItemDetails;
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 = [
        'device', // 设备
        'item', // 项目
    ];

    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 = $enums = [];
        foreach ($msg as $value){
            if(! empty($value['comments'])) $comments[$value['value']] = $value['comments'];
            if(! empty($value['enums'])) $enums[$value['value']] = $value['enums'];
        }
        Excel::store(new TableHeadExport([], $headers, $comments, $enums),"/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 device($data,$user){
        $config = $this->getTableConfig($data['type']);
        if(empty($config)) return [false, ['导入配置表头文件不存在','']];

        $config_array = $config['array'] ?? [];
        //生成下载文件
        $filename =  $config['name'] . "导入模板_" . time() . '.' . 'xlsx';

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

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

        $config_array = $config['array'] ?? [];
        //生成下载文件
        $filename =  $config['name'] . "导入模板_" . 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 deviceImport($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];

        // 详细校验 (这里 $array 传引用，内部会转换日期)
        list($error, $update_map) = $this->deviceCheck($array, $user, $table_config);
        if(!empty($error)) return [0, $error];

        $time = time();
        $insert = [];
        $update = [];

        foreach ($array as $key => $value){
            $main_tmp = [];
            foreach ($value as $k => $val){
                if(!empty($table_config[$k]['is_main'])){
                    $main_tmp[$table_config[$k]['key']] = $val;
                }
            }

            if(isset($update_map[$key])){
                // 存入待更新数组
                $update[] = array_merge($main_tmp, ['id' => $update_map[$key]]);
            } else {
                $main_tmp['top_depart_id'] = $user['top_depart_id'];
                $main_tmp['crt_id'] = $user['id'];
                $main_tmp['crt_time'] = $time;
                $insert[] = $main_tmp;
            }
        }

        DB::beginTransaction();
        try {
            // 1. 批量新增
            if(!empty($insert)){
                foreach(array_chunk($insert, 500) as $chunkInsert){
                    Device::insert($chunkInsert);
                }
            }

            // 批量更新
            foreach (array_chunk($update, 100) as $chunk) {
                foreach ($chunk as $item) {
                    $id = $item['id'];
                    unset($item['id']);
                    Device::where('id', $id)->update($item);
                }
            }

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

        return [true, ''];
    }

    private function deviceCheck(&$array, $user, $table_config)
    {
        // 动态获取关键列的索引
        $codeIdx = array_search('code', array_column($table_config, 'key'));
        $dateIdx = array_search('in_time', array_column($table_config, 'key'));
        $typeIdx = array_search('type', array_column($table_config, 'key'));
        $type2Idx = array_search('is_use', array_column($table_config, 'key'));

        $code_map = $this->getDeviceList($array, $user, $codeIdx);

        $errors = [];
        $update = [];

        $map_type = array_flip(Device::$type);
        $map_type_2 = array_flip(Device::Use);
        foreach ($array as $rowIndex => $value) {
            $valCode = $value[$codeIdx] ?? '';

            // 记录更新 ID
            if(isset($code_map[$valCode])){
                $update[$rowIndex] = $code_map[$valCode];
            }

            if(empty($map_type[$value[$typeIdx]])){
                $errors[] = "第{$rowIndex}行固定资产类型错误";
            }else{
                $array[$rowIndex][$typeIdx] = $map_type[$value[$typeIdx]];
            }
            if(empty($map_type_2[$value[$type2Idx]])){
                $errors[] = "第{$rowIndex}行是否启用错误";
            }else{
                $array[$rowIndex][$type2Idx] = $map_type_2[$value[$type2Idx]];
            }

            // 日期处理
            if($dateIdx !== false && !empty($value[$dateIdx])){
                list($status, $msg) = $this->convertExcelCellToDate($value[$dateIdx]);
                if(!$status) {
                    $errors[] = "第{$rowIndex}行日期格式错误";
                } else {
                    $array[$rowIndex][$dateIdx] = $msg;
                }
            }
        }

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

        return [$error_string, $update];
    }

    private function getDeviceList($array, $user, $index){
        //查找设备
        $codes = array_unique(array_filter(array_column($array,$index)));

        return Device::where('del_time', 0)
            ->where('top_depart_id', $user['top_depart_id'])
            ->whereIn('code', $codes)
            ->pluck('id','code')
            ->toArray();
    }

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

        // 1. 公共校验 (必填、唯一性等)
        list($array, $error) = $this->checkCommon($array, $table_config);
        if (!empty($error)) return [0, $error];

        // 2. 业务详细校验 (获取更新映射及明细数据)
        list($error, $update_map, $detail_data_map) = $this->itemCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

        $time = time();
        $insert_data = [];
        $update_data = [];
        $all_detail_insert = [];
        $update_main_ids = [];

        // 3. 数据分拣
        foreach ($array as $key => $value) {
            $main_tmp = [];
            foreach ($value as $k => $val) {
                if (!empty($table_config[$k]['is_main'])) {
                    $main_tmp[$table_config[$k]['key']] = $val;
                }
            }

            if (isset($update_map[$key])) {
                // 更新逻辑
                $itemId = $update_map[$key];
                $update_data[] = array_merge($main_tmp, ['id' => $itemId]);
                $update_main_ids[] = $itemId;

                // 收集明细 (后续统一插入)
                if (isset($detail_data_map[$key])) {
                    foreach ($detail_data_map[$key] as $d) {
                        $all_detail_insert[] = array_merge($d, ['item_id' => $itemId, 'crt_time' => $time]);
                    }
                }
            } else {
                // 新增逻辑
                $main_tmp['top_depart_id'] = $user['top_depart_id'];
                $main_tmp['crt_id'] = $user['id'];
                $main_tmp['crt_time'] = $time;
                // 以 code 为键，方便后续回填 ID
                $insert_data[$main_tmp['code']] = $main_tmp;

                if (isset($detail_data_map[$key])) {
                    foreach ($detail_data_map[$key] as $d) {
                        $all_detail_insert[] = array_merge($d, ['_code' => $main_tmp['code'], 'crt_time' => $time]);
                    }
                }
            }
        }

        DB::beginTransaction();
        try {
            // 4. 执行新增主表
            if (!empty($insert_data)) {
                foreach (array_chunk($insert_data, 500) as $chunk) {
                    Item::insert($chunk);
                }
                // 获取新插入数据的 ID 映射
                $new_item_maps = Item::whereIn('code', array_keys($insert_data))
                    ->where('del_time', 0)
                    ->where('top_depart_id', $user['top_depart_id'])
                    ->pluck('id', 'code')->toArray();
            }

            // 5. 执行更新主表 (分批更新)
            if (!empty($update_data)) {
                foreach (array_chunk($update_data, 100) as $chunk) {
                    foreach ($chunk as $uItem) {
                        $id = $uItem['id'];
                        unset($uItem['id']);
                        Item::where('id', $id)->update($uItem);
                    }
                }
            }

            // 6. 处理明细表 (先删后插策略)
            // 删除旧明细 (逻辑删除)
            if (!empty($update_main_ids)) {
                ItemDetails::whereIn('item_id', $update_main_ids)
                    ->where('del_time', 0)
                    ->update(['del_time' => $time]);
            }

            // 回填新增主表的 ID 到明细数组
            foreach ($all_detail_insert as &$di) {
                if (isset($di['_code'])) {
                    $di['item_id'] = $new_item_maps[$di['_code']] ?? 0;
                    unset($di['_code']);
                }
            }
            unset($di);

            // 批量插入所有明细
            if (!empty($all_detail_insert)) {
                foreach (array_chunk($all_detail_insert, 500) as $chunk) {
                    ItemDetails::insert($chunk);
                }
            }

            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return [false, "入库失败：" . $e->getMessage() . " (行号:" . $e->getLine() . ")"];
        }

        return [true, '导入成功'];
    }

    private function getItemList($array, $user, $index){
        //查找设备
        $codes = array_unique(array_filter(array_column($array,$index)));

        return Item::where('del_time', 0)
            ->where('top_depart_id', $user['top_depart_id'])
            ->whereIn('code', $codes)
            ->pluck('id','code')
            ->toArray();
    }

    private function itemCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $stateIdx = array_search('state', $keys);
        $manIdx = array_search('man_list', $keys);
        $deviceIdx = array_search('device_list', $keys);
        // 日期索引
        $dateIdx = array_search('start_time', $keys);
        $date2Idx = array_search('end_time', $keys);

        $code_map = $this->getItemList($array, $user, $codeIdx);
        list($man_map, $device_map) = $this->getDataList($array, $user, $manIdx, $deviceIdx);

        $errors = [];
        $update_mapping = [];
        $detail_storage = [];
        $state_type_map = array_flip(Item::State_Type);

        foreach ($array as $rowIndex => $rowValue) {
            $valCode = $rowValue[$codeIdx] ?? '';

            // 1. 判定更新还是新增
            if (isset($code_map[$valCode])) {
                $update_mapping[$rowIndex] = $code_map[$valCode];
            }

            // 2. 状态校验
            $state_text = $rowValue[$stateIdx] ?? '';
            if (!isset($state_type_map[$state_text])) {
                $errors[] = "第{$rowIndex}行：状态[{$state_text}]无效";
            } else {
                $array[$rowIndex][$stateIdx] = $state_type_map[$state_text];
            }

            // 3. 日期转换
            foreach ([$dateIdx, $date2Idx] as $dIdx) {
                if ($dIdx !== false && !empty($rowValue[$dIdx])) {
                    list($s, $m) = $this->convertExcelCellToDate($rowValue[$dIdx]);
                    if (!$s) $errors[] = "第{$rowIndex}行：日期格式非法";
                    else $array[$rowIndex][$dIdx] = $m;
                }
            }

            // 4. 解析人员 (明细类型1)
            if ($manIdx !== false && !empty($rowValue[$manIdx])) {
                foreach (explode(',', $rowValue[$manIdx]) as $mNum) {
                    $mNum = trim($mNum);
                    if (!isset($man_map[$mNum])) {
                        $errors[] = "第{$rowIndex}行：人员编码[{$mNum}]不存在";
                    } else {
                        $detail_storage[$rowIndex][] = [
                            'type' => ItemDetails::type_one,
                            'data_id' => $man_map[$mNum],
                        ];
                    }
                }
            }

            // 5. 解析设备 (明细类型2)
            if ($deviceIdx !== false && !empty($rowValue[$deviceIdx])) {
                foreach (explode(',', $rowValue[$deviceIdx]) as $dCode) {
                    $dCode = trim($dCode);
                    if (!isset($device_map[$dCode])) {
                        $errors[] = "第{$rowIndex}行：资产编码[{$dCode}]不存在";
                    } else {
                        $detail_storage[$rowIndex][] = [
                            'type' => ItemDetails::type_two,
                            'data_id' => $device_map[$dCode],
                        ];
                    }
                }
            }
        }

        $error_str = !empty($errors) ? implode('|', $errors) : "";
        return [$error_str, $update_mapping, $detail_storage];
    }

    private function getDataList($array, $user, $index1, $index2)
    {
        $manNums = [];
        $devCodes = [];

        // 去重收集
        foreach ($array as $row) {
            if (!empty($row[$index1])) {
                foreach (explode(',', $row[$index1]) as $v) $manNums[trim($v)] = true;
            }
            if (!empty($row[$index2])) {
                foreach (explode(',', $row[$index2]) as $v) $devCodes[trim($v)] = true;
            }
        }

        $manMap = Employee::where('del_time', 0)
            ->where('top_depart_id', $user['top_depart_id'])
            ->whereIn('number', array_keys($manNums))
            ->pluck('id', 'number')->toArray();

        $devMap = Device::where('del_time', 0)
            ->where('top_depart_id', $user['top_depart_id'])
            ->whereIn('code', array_keys($devCodes))
            ->pluck('id', 'code')->toArray();

        return [$manMap, $devMap];
    }



    //公共校验 -----------------------------------------
    private function checkCommon($array, $table_config) {
        $error = [];
        $uniqueCheck = []; // 格式：[$column_index => [$value => $first_line]]

        foreach ($array as $line => $row) {
            $rowData = array_filter($row);
            if (empty($rowData)) {
                unset($array[$line]);
                continue;
            }

            foreach ($row as $colIndex => $value) {
                $value = trim($value);
                $config = $table_config[$colIndex] ?? null;

                // 1. 基础存在性检查
                if (!$config) {
                    $error[] = "第{$line}行第{$colIndex}列配置不存在";
                    continue;
                }

                $fieldName = $config['value'];

                // 2. 必填校验
                if (!empty($config['required']) && ($value === '' || !isset($value))) {
                    $error[] = "第{$line}行：[{$fieldName}] 必填";
                }

                // 3. 默认值填充
                if ($value === '' && isset($config['default'])) {
                    $value = $config['default'];
                }

                // 4. 唯一性校验（重点：一次遍历解决）
                if (!empty($config['unique']) && $value !== '') {
                    if (isset($uniqueCheck[$colIndex][$value])) {
                        $prevLine = $uniqueCheck[$colIndex][$value];
                        $error[] = "第{$line}行：[{$fieldName}] 与第{$prevLine}行重复，重复值：{$value}";
                    } else {
                        // 记录该值第一次出现的位置
                        $uniqueCheck[$colIndex][$value] = $line;
                    }
                }

                $row[$colIndex] = $value;
            }
            $array[$line] = $row;
        }

        $error_string = !empty($error) ? 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)];
    }
}