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\Fee;
use App\Model\Item;
use App\Model\ItemDetails;
use App\Model\MonthlyDdOrder;
use App\Model\MonthlyPsOrder;
use App\Model\MonthlyPwOrder;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\Calculation\Statistical\Distributions\F;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class ImportService extends Service
{
    public static $type = [
        'device', // 设备
        'item', // 项目
        'fee', // 费用
        'monthPwOrder', // 人员月度研发工时单
        'monthDwOrder', // 设备月度研发工时单
        'monthPsOrder', // 人员月度工资单
        'monthDdOrder', // 设备月度折旧单
    ];

    public function getTableTitleXls($data,$user){
        if(empty($data['type'])) return [false,'缺少类型'];
        if(! in_array($data['type'],self::$type)) return [false,'类型不存在'];

        //获取配置文件
        $fuc = $data['type'];
        if (! method_exists(self::class, $fuc)) return [false, "导入文件获取不存在,请联系开发"];
        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]];
    }

    private function fee($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 monthPwOrder($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 monthDwOrder($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 monthPsOrder($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 monthDdOrder($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) {
            $displayLine = $rowIndex + 1;

            $valCode = $value[$codeIdx] ?? '';

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

            if(empty($map_type[$value[$typeIdx]])){
                $errors[] = "第{$displayLine}行固定资产类型错误";
            }else{
                $array[$rowIndex][$typeIdx] = $map_type[$value[$typeIdx]];
            }
            if(empty($map_type_2[$value[$type2Idx]])){
                $errors[] = "第{$displayLine}行是否启用错误";
            }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[] = "第{$displayLine}行日期格式错误";
                } 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, 'top_depart_id' => $user['top_depart_id']]);
                    }
                }
            } 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, 'top_depart_id' => $main_tmp['top_depart_id']]);
                    }
                }
            }
        }

        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) {
            $displayLine = $rowIndex + 1;

            $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[] = "第{$displayLine}行：状态[{$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[] = "第{$displayLine}行：日期格式非法";
                    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[] = "第{$displayLine}行：人员工号[{$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[] = "第{$displayLine}行：设备的资产编码[{$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];
    }

    // 费用 ----------------------------------
    public function feeImport($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];

        // 2. 详细校验
        list($error, $update_map, $parent_code_map) = $this->feeCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

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

        // --- 修正点 1: 必须确保索引提取准确 ---
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        // ------------------------------------

        foreach ($array as $key => $value) {
            $cCode = trim($value[$codeIdx] ?? '');
            if($cCode === '') continue;

            $all_codes[] = $cCode;
            $main_tmp = [];
            foreach ($value as $k => $val){
                if(!empty($table_config[$k]['is_main'])){
                    if ($table_config[$k]['key'] !== 'parent_id') {
                        $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_time'] = $time;
                $main_tmp['parent_id'] = 0;
                $insert[] = $main_tmp;
            }
        }

        DB::beginTransaction();
        try {
            if (!empty($insert)) {
                foreach (array_chunk($insert, 500) as $chunk) {
                    Fee::insert($chunk);
                }
            }

            if (!empty($update)) {
                foreach ($update as $item) {
                    $uId = $item['id']; unset($item['id']);
                    Fee::where('id', $uId)->update($item);
                }
            }

            // --- 修正点 2: 核心回填逻辑 ---
            $newCodeToIdMap = Fee::where('del_time', 0)
                ->where('top_depart_id', $user['top_depart_id'])
                ->whereIn('code', array_unique($all_codes))
                ->pluck('id', 'code')
                ->toArray();

            foreach ($parent_code_map as $rowIndex => $pCode) {
                // 这里必须通过 $rowIndex 从原始 $array 中精准获取当前行的编码
                $currentCode = isset($array[$rowIndex][$codeIdx]) ? trim($array[$rowIndex][$codeIdx]) : '';
                $pCode = trim($pCode);

                if ($currentCode === '' || $pCode === '') continue;

                $currentId = $newCodeToIdMap[$currentCode] ?? null;
                $parentId = $newCodeToIdMap[$pCode] ?? null;

                // 严谨判断：只有当前记录存在，且上级记录也存在，且两者不是同一个 ID 时才更新
                if ($currentId && $parentId && $currentId != $parentId) {
                    Fee::where('id', $currentId)->update(['parent_id' => $parentId]);
                }
            }

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

    private function feeCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $parentIdx = array_search('parent_id', $keys);

        // 1. 获取基础数据
        list($dbFeeMap, $excelCodesMap) = $this->getFeeList($array, $user, $codeIdx);

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

        // 2. 建立 Excel 内部父子关系映射（用于环路追溯）
        $currentExcelMap = [];
        foreach ($array as $row) {
            $c = trim($row[$codeIdx] ?? '');
            $p = trim($row[$parentIdx] ?? '');
            if ($c !== '') $currentExcelMap[$c] = $p;
        }

        // 3. 逐行校验
        foreach ($array as $rowIndex => $value) {
            $displayLine = $rowIndex + 1;

            $valCode = trim($value[$codeIdx] ?? '');
            $valParentCode = trim($value[$parentIdx] ?? '');

            if ($valCode === '') continue;

            // 更新状态记录
            if (isset($dbFeeMap[$valCode])) {
                $update[$rowIndex] = $dbFeeMap[$valCode]['id'];
            }

            if ($valParentCode !== '') {
                // --- A. 存在性校验 ---
                // 这里会检查 006 是否在数据库，或者是否在本次 Excel 的其他行中
                if (!isset($dbFeeMap[$valParentCode]) && !isset($excelCodesMap[$valParentCode])) {
                    $errors[] = "第{$displayLine}行：上级编码[{$valParentCode}]在系统和文件中均不存在";
                    continue;
                }

                // --- B. 自引用校验 ---
                if ($valCode === $valParentCode) {
                    $errors[] = "第{$displayLine}行：上级编码不能是自身";
                    continue;
                }

                // --- C. 环路追溯 ---
                $visited = [];
                $res = $this->findLoopInAncestors($valParentCode, $valCode, $currentExcelMap, $dbFeeMap, $visited);
                if ($res !== false) {
                    if ($res['type'] === 'LOOP_SELF') {
                        $errors[] = "第{$displayLine}行：编码[{$valCode}]与上级[{$valParentCode}]存在循环引用";
                    } else {
                        $errors[] = "第{$displayLine}行：上级[{$valParentCode}]的溯源链条已成环";
                    }
                    continue;
                }

                // 记录有效的父级关系
                $parent_code_map[$rowIndex] = $valParentCode;
            }
        }

        $error_string = !empty($errors) ? implode('|', $errors) : "";
        return [$error_string, $update, $parent_code_map];
    }

    private function getFeeList($array, $user, $index)
    {
        // 关键：一定要把 Excel 里所有的 code 这一列全部拿出来，并去除空值
        $codesInExcel = [];
        foreach ($array as $row) {
            $c = trim($row[$index] ?? '');
            if ($c !== '') {
                $codesInExcel[$c] = true;
            }
        }

        $allFees = Fee::where('del_time', 0)
            ->where('top_depart_id', $user['top_depart_id'])
            ->select('id', 'code', 'parent_id')
            ->get();

        $dbFeeMap = [];
        foreach ($allFees as $fee) {
            $dbFeeMap[$fee->code] = [
                'id' => $fee->id,
                'code' => $fee->code,
                'parent_id' => $fee->parent_id
            ];
        }

        // 返回数据库映射和 Excel 编码映射
        return [$dbFeeMap, $codesInExcel];
    }

    private function findLoopInAncestors($startParentCode, $targetCode, $excelMap, $dbFeeMap, &$visited)
    {
        $current = $startParentCode;
        while ($current !== '' && $current !== 0) {
            if ($current === $targetCode) {
                return ['type' => 'LOOP_SELF', 'code' => $current];
            }
            if (isset($visited[$current])) {
                return ['type' => 'EXISTING_LOOP', 'code' => $current];
            }
            $visited[$current] = true;

            if (isset($excelMap[$current]) && $excelMap[$current] !== '') {
                $current = $excelMap[$current];
            } elseif (isset($dbFeeMap[$current])) {
                $parentId = $dbFeeMap[$current]['parent_id'];
                $parentCode = '';
                foreach ($dbFeeMap as $item) {
                    if ($item['id'] == $parentId) {
                        $parentCode = $item['code'];
                        break;
                    }
                }
                $current = $parentCode;
            } else {
                break;
            }
        }
        return false;
    }

    // 人员月度工时单 ------------------------------
    public function monthPwOrderImport($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];

        // 2. 详细校验 (这里会返回 update_map 和 dbEmps 映射)
        list($error, $update_map, $dbEmps) = $this->monthPwOrderCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $monthIdx = array_search('month', $keys);
        $empIdx = array_search('employee_id', $keys);

        // --- 步骤 1: 数据聚合分组 ---
        // 将 Excel 数据按“单据”维度归类
        $groups = [];
        foreach ($array as $rowIndex => $row) {
            $cCode = trim($row[$codeIdx] ?? '');
            $cMonthTs = $row[$monthIdx]; // 校验阶段已转为时间戳

            // 聚合 Key：有编码用编码，没编码用月份标记
            $aggKey = $cCode ?: "MONTH_" . $cMonthTs;

            if (!isset($groups[$aggKey])) {
                $groups[$aggKey] = [
                    'main_id'  => $update_map[$rowIndex] ?? 0, // 如果存在则是编辑
                    'code'     => $cCode,
                    'month'    => $cMonthTs,
                    'details'  => []
                ];
            }

            // 准备详情行数据
            $detailTmp = [];
            foreach ($table_config as $k => $conf) {
                if (!$conf['is_main']) {
                    $fieldKey = $conf['key'];
                    $fieldVal = $row[$k];
                    // 如果是人员，转换为 ID
                    if ($fieldKey == 'employee_id') {
                        $fieldVal = $dbEmps[$fieldVal] ?? 0;
                    }
                    $detailTmp[$fieldKey] = $fieldVal;
                }
            }
            $groups[$aggKey]['details'][] = $detailTmp;
        }

        // --- 步骤 2: 开启事务写入 ---
        DB::beginTransaction();
        try {
            $time = time();
            foreach ($groups as $aggKey => $group) {
                $mainId = $group['main_id'];

                if ($mainId > 0) {
                    // 删除旧详情
                    DB::table('monthly_pw_order_details')->where('del_time',0)
                        ->where('main_id', $mainId)
                        ->update(['del_time' => $time]);
                } else {
                    // B. 新增逻辑
                    $newCode = $this->generateBillNo([
                        'top_depart_id' => $user['top_depart_id'],
                        'type'          => MonthlyPwOrder::Order_type,
                        'period'        => date("Ym", $group['month'])
                    ]);

                    $mainId = DB::table('monthly_pw_order')->insertGetId([
                        'code'          => $newCode,
                        'month'         => $group['month'],
                        'top_depart_id' => $user['top_depart_id'],
                        'crt_id'        => $user['id'],
                        'crt_time'      => $time,
                        'upd_time'      => $time,
                        'del_time'      => 0
                    ]);
                }

                // C. 批量插入详情
                $insertDetails = [];
                foreach ($group['details'] as $detail) {
                    $detail['main_id']      = $mainId;
                    $detail['top_depart_id']= $user['top_depart_id'];
                    $detail['crt_time']     = $time;
                    $detail['del_time']     = 0;
                    $insertDetails[] = $detail;
                }

                if (!empty($insertDetails)) {
                    DB::table('monthly_pw_order_details')->insert($insertDetails);
                }
            }

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

        return [true, ''];
    }

    private function monthPwOrderCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $monthIdx = array_search('month', $keys);
        $empIdx = array_search('employee_id', $keys);
        $startIdx = array_search('start_time', $keys);
        $endIdx = array_search('end_time', $keys);

        $numIdx = array_search('total_days', $keys);
        $num2Idx = array_search('rd_total_days', $keys);
        $num3Idx = array_search('total_hours', $keys);
        $num4Idx = array_search('rd_total_hours', $keys);

        // 1. 预加载基础数据
        $allEmpNumbers = array_filter(array_unique(array_column($array, $empIdx)));
        $dbEmps = Employee::where('del_time', 0)
            ->whereIn('number', $allEmpNumbers)
            ->where('top_depart_id', $user['top_depart_id'])
            ->pluck('id', 'number')->toArray();

        $allCodes = array_filter(array_unique(array_column($array, $codeIdx)));
        $dbOrders = DB::table('monthly_pw_order')
            ->whereIn('code', $allCodes)
            ->where('top_depart_id', $user['top_depart_id'])
            ->get()->keyBy('code');

        $errors = [];

        //时间转换
        foreach ($array as $rowIndex => $row) {
            $valMonthRaw = trim($row[$monthIdx] ?? '');
            if ($valMonthRaw === '') continue;

            list($mStatus, $valMonthTs) = $this->convertExcelCellToDate($valMonthRaw);
            if (!$mStatus) {
                $errors[] = "第" . ($rowIndex + 1) . "行：月份格式错误({$valMonthRaw})";
                continue;
            }

            // 标准化并存回数组，这样后面的循环直接拿到的就是时间戳
            $valMonthTs = strtotime(date('Y-m-01', $valMonthTs));
            $array[$rowIndex][$monthIdx] = $valMonthTs;
            $uniqueMonths[] = $valMonthTs;
        }

        $existingMonthsMap = [];
        if (!empty($uniqueMonths)) {
            $existingMonths = DB::table('monthly_pw_order')
                ->where('top_depart_id', $user['top_depart_id'])
                ->where('del_time', 0)
                ->whereIn('month', array_unique($uniqueMonths))
                ->pluck('month')
                ->toArray();
            $existingMonthsMap = array_fill_keys($existingMonths, true);
        }

        $excelAggregator = [];
        $update_map = [];

        foreach ($array as $rowIndex => $row) {
            // 用户看到的行号（假设数据从第2行开始）
            $displayLine = $rowIndex + 1;

            $valCode = trim($row[$codeIdx] ?? '');
            $valMonthTs = trim($row[$monthIdx] ?? '');
            $valEmp = trim($row[$empIdx] ?? '');
            $valStartRaw = $row[$startIdx] ?? '';
            $valEndRaw = $row[$endIdx] ?? '';

            $valNumRaw = $row[$numIdx] ?? 0;
            $valNum2Raw = $row[$num2Idx] ?? 0;
            $valNum3Raw = $row[$num3Idx] ?? 0;
            $valNum4Raw = $row[$num4Idx] ?? 0;

            if (!is_numeric($valMonthTs)) continue;

            // 转换开始日期
            list($sStatus, $startTime) = $this->convertExcelCellToDate($valStartRaw);
            if (!$sStatus) {
                $errors[] = "第{$displayLine}行：开始日期格式错误";
                continue;
            }

            // 转换结束日期
            list($eStatus, $endTime) = $this->convertExcelCellToDate($valEndRaw);
            if (!$eStatus) {
                $errors[] = "第{$displayLine}行：结束日期格式错误";
                continue;
            }

            $aggKey = $valCode ?: "NEW_ORDER_" . $valMonthTs;

            // --- B. 校验单据与月份的一致性 ---
            if ($valCode && isset($dbOrders[$valCode])) {
                $dbOrder = $dbOrders[$valCode];
                if ($dbOrder->month != $valMonthTs) {
                    $errors[] = "第{$displayLine}行：单据编码[{$valCode}]对应月份为[" . date('Y-m', $dbOrder->month) . "]，与导入月份不符";
                }
                $update_map[$rowIndex] = $dbOrder->id;
            }

            // --- C. 校验人员存在性与单据内唯一性 ---
            if (!isset($dbEmps[$valEmp])) {
                $errors[] = "第{$displayLine}行：人员工号[{$valEmp}]不存在";
            } else {
                if (isset($excelAggregator[$aggKey]['emps'][$valEmp])) {
                    $errors[] = "第{$displayLine}行：人员[{$valEmp}]在同一单据中重复";
                }
                $excelAggregator[$aggKey]['emps'][$valEmp] = true;

                if (isset($excelAggregator[$aggKey]['month_ts']) && $excelAggregator[$aggKey]['month_ts'] !== $valMonthTs) {
                    $errors[] = "第{$displayLine}行：同组数据的月份不统一";
                }
                $excelAggregator[$aggKey]['month_ts'] = $valMonthTs;
            }

            // --- D. 逻辑校验：日期范围 ---
            if ($startTime > $endTime) {
                $errors[] = "第{$displayLine}行：开始日期大于结束日期";
            } else {
                $monthEndTs = strtotime("+1 month", $valMonthTs) - 1;
                if ($startTime < $valMonthTs || $endTime > $monthEndTs) {
                    $errors[] = "第{$displayLine}行：日期超出[ " . date('Y-m', $valMonthTs) . " ]范围";
                }
            }

            // --- E. 月份唯一单据校验 (新增) ---
            if (!$valCode) {
                if (isset($existingMonthsMap[$valMonthTs])) {
                    $errors[] = "第{$displayLine}行：月份[ " . date('Y-m', $valMonthTs) . " ]已存在单据，请填编码编辑";
                }
            }

            // --- F. 数字校验
            $res = $this->checkNumber($valNumRaw,0,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行出勤总天数：" . $res['error'];
            $res = $this->checkNumber($valNum2Raw,0,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行研发出勤总天数：" . $res['error'];
            $res = $this->checkNumber($valNum3Raw,2,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行出勤总工时：" . $res['error'];
            $res = $this->checkNumber($valNum4Raw,2,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行研发总工时：" . $res['error'];

            // 将转换后的时间戳写回原数组，方便后续写入数据库时直接使用
            $array[$rowIndex][$startIdx] = $startTime;
            $array[$rowIndex][$endIdx] = $endTime;
        }

        $error_string = !empty($errors) ? implode('|', $errors) : "";
        return [$error_string, $update_map, $dbEmps];
    }

    // 设备月度工时单
    public function monthDwOrderImport($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];

        // 2. 详细校验 (这里会返回 update_map 和 dbDevs 映射)
        list($error, $update_map, $dbDevs) = $this->monthDwOrderCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $monthIdx = array_search('month', $keys);
        $empIdx = array_search('device_id', $keys);

        // --- 步骤 1: 数据聚合分组 ---
        // 将 Excel 数据按“单据”维度归类
        $groups = [];
        foreach ($array as $rowIndex => $row) {
            $cCode = trim($row[$codeIdx] ?? '');
            $cMonthTs = $row[$monthIdx]; // 校验阶段已转为时间戳

            // 聚合 Key：有编码用编码，没编码用月份标记
            $aggKey = $cCode ?: "MONTH_" . $cMonthTs;

            if (!isset($groups[$aggKey])) {
                $groups[$aggKey] = [
                    'main_id'  => $update_map[$rowIndex] ?? 0, // 如果存在则是编辑
                    'code'     => $cCode,
                    'month'    => $cMonthTs,
                    'details'  => []
                ];
            }

            // 准备详情行数据
            $detailTmp = [];
            foreach ($table_config as $k => $conf) {
                if (!$conf['is_main']) {
                    $fieldKey = $conf['key'];
                    $fieldVal = $row[$k];
                    // 如果是人员，转换为 ID
                    if ($fieldKey == 'device_id') {
                        $fieldVal = $dbDevs[$fieldVal] ?? 0;
                    }
                    $detailTmp[$fieldKey] = $fieldVal;
                }
            }
            $groups[$aggKey]['details'][] = $detailTmp;
        }

        // --- 步骤 2: 开启事务写入 ---
        DB::beginTransaction();
        try {
            $time = time();
            foreach ($groups as $aggKey => $group) {
                $mainId = $group['main_id'];

                if ($mainId > 0) {
                    // 删除旧详情
                    DB::table('monthly_dw_order_details')->where('del_time',0)
                        ->where('main_id', $mainId)
                        ->update(['del_time' => $time]);
                } else {
                    // B. 新增逻辑
                    $newCode = $this->generateBillNo([
                        'top_depart_id' => $user['top_depart_id'],
                        'type'          => MonthlyPwOrder::Order_type,
                        'period'        => date("Ym", $group['month'])
                    ]);

                    $mainId = DB::table('monthly_dw_order')->insertGetId([
                        'code'          => $newCode,
                        'month'         => $group['month'],
                        'top_depart_id' => $user['top_depart_id'],
                        'crt_id'        => $user['id'],
                        'crt_time'      => $time,
                        'upd_time'      => $time,
                        'del_time'      => 0
                    ]);
                }

                // C. 批量插入详情
                $insertDetails = [];
                foreach ($group['details'] as $detail) {
                    $detail['main_id']      = $mainId;
                    $detail['top_depart_id']= $user['top_depart_id'];
                    $detail['crt_time']     = $time;
                    $detail['del_time']     = 0;
                    $insertDetails[] = $detail;
                }

                if (!empty($insertDetails)) {
                    DB::table('monthly_dw_order_details')->insert($insertDetails);
                }
            }

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

        return [true, ''];
    }

    private function monthDwOrderCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $monthIdx = array_search('month', $keys);
        $devIdx = array_search('device_id', $keys);
        $startIdx = array_search('start_time', $keys);
        $endIdx = array_search('end_time', $keys);

        $numIdx = array_search('total_days', $keys);
        $num2Idx = array_search('rd_total_days', $keys);
        $num3Idx = array_search('total_hours', $keys);
        $num4Idx = array_search('rd_total_hours', $keys);

        // 1. 预加载基础数据
        $allEmpNumbers = array_filter(array_unique(array_column($array, $devIdx)));
        $dbDevs = Device::where('del_time', 0)
            ->whereIn('code', $allEmpNumbers)
            ->where('top_depart_id', $user['top_depart_id'])
            ->pluck('id', 'code')->toArray();

        $allCodes = array_filter(array_unique(array_column($array, $codeIdx)));
        $dbOrders = DB::table('monthly_dw_order')
            ->whereIn('code', $allCodes)
            ->where('top_depart_id', $user['top_depart_id'])
            ->get()->keyBy('code');

        $errors = [];

        //时间转换
        foreach ($array as $rowIndex => $row) {
            $valMonthRaw = trim($row[$monthIdx] ?? '');
            if ($valMonthRaw === '') continue;

            list($mStatus, $valMonthTs) = $this->convertExcelCellToDate($valMonthRaw);
            if (!$mStatus) {
                $errors[] = "第" . ($rowIndex + 1) . "行：月份格式错误({$valMonthRaw})";
                continue;
            }

            // 标准化并存回数组，这样后面的循环直接拿到的就是时间戳
            $valMonthTs = strtotime(date('Y-m-01', $valMonthTs));
            $array[$rowIndex][$monthIdx] = $valMonthTs;
            $uniqueMonths[] = $valMonthTs;
        }

        $existingMonthsMap = [];
        if (!empty($uniqueMonths)) {
            $existingMonths = DB::table('monthly_dw_order')
                ->where('top_depart_id', $user['top_depart_id'])
                ->where('del_time', 0)
                ->whereIn('month', array_unique($uniqueMonths))
                ->pluck('month')
                ->toArray();
            $existingMonthsMap = array_fill_keys($existingMonths, true);
        }

        $excelAggregator = [];
        $update_map = [];

        foreach ($array as $rowIndex => $row) {
            // 用户看到的行号（假设数据从第2行开始）
            $displayLine = $rowIndex + 1;

            $valCode = trim($row[$codeIdx] ?? '');
            $valMonthTs = trim($row[$monthIdx] ?? '');
            $valDev = trim($row[$devIdx] ?? '');
            $valStartRaw = $row[$startIdx] ?? '';
            $valEndRaw = $row[$endIdx] ?? '';

            $valNumRaw = $row[$numIdx] ?? 0;
            $valNum2Raw = $row[$num2Idx] ?? 0;
            $valNum3Raw = $row[$num3Idx] ?? 0;
            $valNum4Raw = $row[$num4Idx] ?? 0;

            if (!is_numeric($valMonthTs)) continue;

            // 转换开始日期
            list($sStatus, $startTime) = $this->convertExcelCellToDate($valStartRaw);
            if (!$sStatus) {
                $errors[] = "第{$displayLine}行：开始日期格式错误";
                continue;
            }

            // 转换结束日期
            list($eStatus, $endTime) = $this->convertExcelCellToDate($valEndRaw);
            if (!$eStatus) {
                $errors[] = "第{$displayLine}行：结束日期格式错误";
                continue;
            }

            $aggKey = $valCode ?: "NEW_ORDER_" . $valMonthTs;

            // --- B. 校验单据与月份的一致性 ---
            if ($valCode && isset($dbOrders[$valCode])) {
                $dbOrder = $dbOrders[$valCode];
                if ($dbOrder->month != $valMonthTs) {
                    $errors[] = "第{$displayLine}行：单据编码[{$valCode}]对应月份为[" . date('Y-m', $dbOrder->month) . "]，与导入月份不符";
                }
                $update_map[$rowIndex] = $dbOrder->id;
            }

            // --- C. 校验设备存在性与单据内唯一性 ---
            if (!isset($dbDevs[$valDev])) {
                $errors[] = "第{$displayLine}行：设备的资产编码[{$valDev}]不存在";
            } else {
                if (isset($excelAggregator[$aggKey]['devs'][$valDev])) {
                    $errors[] = "第{$displayLine}行：设备的资产编码[{$valDev}]在同一单据中重复";
                }
                $excelAggregator[$aggKey]['devs'][$valDev] = true;

                if (isset($excelAggregator[$aggKey]['month_ts']) && $excelAggregator[$aggKey]['month_ts'] !== $valMonthTs) {
                    $errors[] = "第{$displayLine}行：同组数据的月份不统一";
                }
                $excelAggregator[$aggKey]['month_ts'] = $valMonthTs;
            }

            // --- D. 逻辑校验：日期范围 ---
            if ($startTime > $endTime) {
                $errors[] = "第{$displayLine}行：开始日期大于结束日期";
            } else {
                $monthEndTs = strtotime("+1 month", $valMonthTs) - 1;
                if ($startTime < $valMonthTs || $endTime > $monthEndTs) {
                    $errors[] = "第{$displayLine}行：日期超出[ " . date('Y-m', $valMonthTs) . " ]范围";
                }
            }

            // --- E. 月份唯一单据校验 (新增) ---
            if (!$valCode) {
                if (isset($existingMonthsMap[$valMonthTs])) {
                    $errors[] = "第{$displayLine}行：月份[ " . date('Y-m', $valMonthTs) . " ]已存在单据，请填编码编辑";
                }
            }

            // --- F. 数字校验
            $res = $this->checkNumber($valNumRaw,0,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行出勤总天数：" . $res['error'];
            $res = $this->checkNumber($valNum2Raw,0,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行研发出勤总天数：" . $res['error'];
            $res = $this->checkNumber($valNum3Raw,2,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行出勤总工时：" . $res['error'];
            $res = $this->checkNumber($valNum4Raw,2,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行研发总工时：" . $res['error'];

            // 将转换后的时间戳写回原数组，方便后续写入数据库时直接使用
            $array[$rowIndex][$startIdx] = $startTime;
            $array[$rowIndex][$endIdx] = $endTime;
        }

        $error_string = !empty($errors) ? implode('|', $errors) : "";
        return [$error_string, $update_map, $dbDevs];
    }

    // 人员月度工资单
    public function monthPsOrderImport($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];

        // 2. 详细校验 (这里会返回 update_map 和 dbEmps 映射)
        list($error, $update_map, $dbEmps) = $this->monthPsOrderCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $monthIdx = array_search('month', $keys);
        $empIdx = array_search('employee_id', $keys);

        // --- 步骤 1: 数据聚合分组 ---
        // 将 Excel 数据按“单据”维度归类
        $groups = [];
        foreach ($array as $rowIndex => $row) {
            $cCode = trim($row[$codeIdx] ?? '');
            $cMonthTs = $row[$monthIdx]; // 校验阶段已转为时间戳

            // 聚合 Key：有编码用编码，没编码用月份标记
            $aggKey = $cCode ?: "MONTH_" . $cMonthTs;

            if (!isset($groups[$aggKey])) {
                $groups[$aggKey] = [
                    'main_id'  => $update_map[$rowIndex] ?? 0, // 如果存在则是编辑
                    'code'     => $cCode,
                    'month'    => $cMonthTs,
                    'details'  => []
                ];
            }

            // 准备详情行数据
            $detailTmp = [];
            foreach ($table_config as $k => $conf) {
                if (!$conf['is_main']) {
                    $fieldKey = $conf['key'];
                    $fieldVal = $row[$k];
                    // 如果是人员，转换为 ID
                    if ($fieldKey == 'employee_id') {
                        $fieldVal = $dbEmps[$fieldVal] ?? 0;
                    }
                    $detailTmp[$fieldKey] = $fieldVal;
                }
            }
            $groups[$aggKey]['details'][] = $detailTmp;
        }

        // --- 步骤 2: 开启事务写入 ---
        DB::beginTransaction();
        try {
            $time = time();
            foreach ($groups as $aggKey => $group) {
                $mainId = $group['main_id'];

                if ($mainId > 0) {
                    // 删除旧详情
                    DB::table('monthly_ps_order_details')->where('del_time',0)
                        ->where('main_id', $mainId)
                        ->update(['del_time' => $time]);
                } else {
                    // B. 新增逻辑
                    $newCode = $this->generateBillNo([
                        'top_depart_id' => $user['top_depart_id'],
                        'type'          => MonthlyPsOrder::Order_type,
                        'period'        => date("Ym", $group['month'])
                    ]);

                    $mainId = DB::table('monthly_ps_order')->insertGetId([
                        'code'          => $newCode,
                        'month'         => $group['month'],
                        'top_depart_id' => $user['top_depart_id'],
                        'crt_id'        => $user['id'],
                        'crt_time'      => $time,
                        'upd_time'      => $time,
                        'del_time'      => 0
                    ]);
                }

                // C. 批量插入详情
                $insertDetails = [];
                foreach ($group['details'] as $detail) {
                    $detail['main_id']      = $mainId;
                    $detail['top_depart_id']= $user['top_depart_id'];
                    $detail['crt_time']     = $time;
                    $detail['del_time']     = 0;
                    $insertDetails[] = $detail;
                }

                if (!empty($insertDetails)) {
                    DB::table('monthly_ps_order_details')->insert($insertDetails);
                }
            }

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

        return [true, ''];
    }

    private function monthPsOrderCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $monthIdx = array_search('month', $keys);
        $empIdx = array_search('employee_id', $keys);

        $numIdx = array_search('salary', $keys);
        $num2Idx = array_search('social_insurance', $keys);
        $num3Idx = array_search('public_housing_fund', $keys);

        // 1. 预加载基础数据
        $allEmpNumbers = array_filter(array_unique(array_column($array, $empIdx)));
        $dbEmps = Employee::where('del_time', 0)
            ->whereIn('number', $allEmpNumbers)
            ->where('top_depart_id', $user['top_depart_id'])
            ->pluck('id', 'number')->toArray();

        $allCodes = array_filter(array_unique(array_column($array, $codeIdx)));
        $dbOrders = DB::table('monthly_ps_order')
            ->whereIn('code', $allCodes)
            ->where('top_depart_id', $user['top_depart_id'])
            ->get()->keyBy('code');

        $errors = [];

        //时间转换
        foreach ($array as $rowIndex => $row) {
            $valMonthRaw = trim($row[$monthIdx] ?? '');
            if ($valMonthRaw === '') continue;

            list($mStatus, $valMonthTs) = $this->convertExcelCellToDate($valMonthRaw);
            if (!$mStatus) {
                $errors[] = "第" . ($rowIndex + 1) . "行：月份格式错误({$valMonthRaw})";
                continue;
            }

            // 标准化并存回数组，这样后面的循环直接拿到的就是时间戳
            $valMonthTs = strtotime(date('Y-m-01', $valMonthTs));
            $array[$rowIndex][$monthIdx] = $valMonthTs;
            $uniqueMonths[] = $valMonthTs;
        }

        $existingMonthsMap = [];
        if (!empty($uniqueMonths)) {
            $existingMonths = DB::table('monthly_ps_order')
                ->where('top_depart_id', $user['top_depart_id'])
                ->where('del_time', 0)
                ->whereIn('month', array_unique($uniqueMonths))
                ->pluck('month')
                ->toArray();
            $existingMonthsMap = array_fill_keys($existingMonths, true);
        }

        $excelAggregator = [];
        $update_map = [];

        foreach ($array as $rowIndex => $row) {
            // 用户看到的行号（假设数据从第2行开始）
            $displayLine = $rowIndex + 1;

            $valCode = trim($row[$codeIdx] ?? '');
            $valMonthTs = trim($row[$monthIdx] ?? '');
            $valEmp = trim($row[$empIdx] ?? '');

            $valNumRaw = $row[$numIdx] ?? 0;
            $valNum2Raw = $row[$num2Idx] ?? 0;
            $valNum3Raw = $row[$num3Idx] ?? 0;

            if (!is_numeric($valMonthTs)) continue;

            $aggKey = $valCode ?: "NEW_ORDER_" . $valMonthTs;

            // --- B. 校验单据与月份的一致性 ---
            if ($valCode && isset($dbOrders[$valCode])) {
                $dbOrder = $dbOrders[$valCode];
                if ($dbOrder->month != $valMonthTs) {
                    $errors[] = "第{$displayLine}行：单据编码[{$valCode}]对应月份为[" . date('Y-m', $dbOrder->month) . "]，与导入月份不符";
                }
                $update_map[$rowIndex] = $dbOrder->id;
            }

            // --- C. 校验人员存在性与单据内唯一性 ---
            if (!isset($dbEmps[$valEmp])) {
                $errors[] = "第{$displayLine}行：人员工号[{$valEmp}]不存在";
            } else {
                if (isset($excelAggregator[$aggKey]['emps'][$valEmp])) {
                    $errors[] = "第{$displayLine}行：人员[{$valEmp}]在同一单据中重复";
                }
                $excelAggregator[$aggKey]['emps'][$valEmp] = true;

                if (isset($excelAggregator[$aggKey]['month_ts']) && $excelAggregator[$aggKey]['month_ts'] !== $valMonthTs) {
                    $errors[] = "第{$displayLine}行：同组数据的月份不统一";
                }
                $excelAggregator[$aggKey]['month_ts'] = $valMonthTs;
            }

            // --- E. 月份唯一单据校验 (新增) ---
            if (!$valCode) {
                if (isset($existingMonthsMap[$valMonthTs])) {
                    $errors[] = "第{$displayLine}行：月份[ " . date('Y-m', $valMonthTs) . " ]已存在单据，请填编码编辑";
                }
            }

            // --- F. 数字校验
            $res = $this->checkNumber($valNumRaw,0,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行工资总额：" . $res['error'];
            $res = $this->checkNumber($valNum2Raw,0,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行社保：" . $res['error'];
            $res = $this->checkNumber($valNum3Raw,2,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行公积金：" . $res['error'];
        }

        $error_string = !empty($errors) ? implode('|', $errors) : "";
        return [$error_string, $update_map, $dbEmps];
    }

    // 设备月度折旧单
    public function monthDdOrderImport($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];

        // 2. 详细校验 (这里会返回 update_map 和 dbDevs 映射)
        list($error, $update_map, $dbDevs) = $this->monthDdOrderCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $monthIdx = array_search('month', $keys);
        $empIdx = array_search('device_id', $keys);

        // --- 步骤 1: 数据聚合分组 ---
        // 将 Excel 数据按“单据”维度归类
        $groups = [];
        foreach ($array as $rowIndex => $row) {
            $cCode = trim($row[$codeIdx] ?? '');
            $cMonthTs = $row[$monthIdx]; // 校验阶段已转为时间戳

            // 聚合 Key：有编码用编码，没编码用月份标记
            $aggKey = $cCode ?: "MONTH_" . $cMonthTs;

            if (!isset($groups[$aggKey])) {
                $groups[$aggKey] = [
                    'main_id'  => $update_map[$rowIndex] ?? 0, // 如果存在则是编辑
                    'code'     => $cCode,
                    'month'    => $cMonthTs,
                    'details'  => []
                ];
            }

            // 准备详情行数据
            $detailTmp = [];
            foreach ($table_config as $k => $conf) {
                if (!$conf['is_main']) {
                    $fieldKey = $conf['key'];
                    $fieldVal = $row[$k];
                    // 如果是人员，转换为 ID
                    if ($fieldKey == 'device_id') {
                        $fieldVal = $dbDevs[$fieldVal] ?? 0;
                    }
                    $detailTmp[$fieldKey] = $fieldVal;
                }
            }
            $groups[$aggKey]['details'][] = $detailTmp;
        }

        // --- 步骤 2: 开启事务写入 ---
        DB::beginTransaction();
        try {
            $time = time();
            foreach ($groups as $aggKey => $group) {
                $mainId = $group['main_id'];

                if ($mainId > 0) {
                    // 删除旧详情
                    DB::table('monthly_dd_order_details')->where('del_time',0)
                        ->where('main_id', $mainId)
                        ->update(['del_time' => $time]);
                } else {
                    // B. 新增逻辑
                    $newCode = $this->generateBillNo([
                        'top_depart_id' => $user['top_depart_id'],
                        'type'          => MonthlyDdOrder::Order_type,
                        'period'        => date("Ym", $group['month'])
                    ]);

                    $mainId = DB::table('monthly_dd_order')->insertGetId([
                        'code'          => $newCode,
                        'month'         => $group['month'],
                        'top_depart_id' => $user['top_depart_id'],
                        'crt_id'        => $user['id'],
                        'crt_time'      => $time,
                        'upd_time'      => $time,
                        'del_time'      => 0
                    ]);
                }

                // C. 批量插入详情
                $insertDetails = [];
                foreach ($group['details'] as $detail) {
                    $detail['main_id']      = $mainId;
                    $detail['top_depart_id']= $user['top_depart_id'];
                    $detail['crt_time']     = $time;
                    $detail['del_time']     = 0;
                    $insertDetails[] = $detail;
                }

                if (!empty($insertDetails)) {
                    DB::table('monthly_dd_order_details')->insert($insertDetails);
                }
            }

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

        return [true, ''];
    }

    private function monthDdOrderCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $monthIdx = array_search('month', $keys);
        $devIdx = array_search('device_id', $keys);

        $numIdx = array_search('depreciation_amount', $keys);

        // 1. 预加载基础数据
        $allEmpNumbers = array_filter(array_unique(array_column($array, $devIdx)));
        $dbDevs = Device::where('del_time', 0)
            ->whereIn('code', $allEmpNumbers)
            ->where('top_depart_id', $user['top_depart_id'])
            ->pluck('id', 'code')->toArray();

        $allCodes = array_filter(array_unique(array_column($array, $codeIdx)));
        $dbOrders = DB::table('monthly_dd_order')
            ->whereIn('code', $allCodes)
            ->where('top_depart_id', $user['top_depart_id'])
            ->get()->keyBy('code');

        $errors = [];

        //时间转换
        foreach ($array as $rowIndex => $row) {
            $valMonthRaw = trim($row[$monthIdx] ?? '');
            if ($valMonthRaw === '') continue;

            list($mStatus, $valMonthTs) = $this->convertExcelCellToDate($valMonthRaw);
            if (!$mStatus) {
                $errors[] = "第" . ($rowIndex + 1) . "行：月份格式错误({$valMonthRaw})";
                continue;
            }

            // 标准化并存回数组，这样后面的循环直接拿到的就是时间戳
            $valMonthTs = strtotime(date('Y-m-01', $valMonthTs));
            $array[$rowIndex][$monthIdx] = $valMonthTs;
            $uniqueMonths[] = $valMonthTs;
        }

        $existingMonthsMap = [];
        if (!empty($uniqueMonths)) {
            $existingMonths = DB::table('monthly_dd_order')
                ->where('top_depart_id', $user['top_depart_id'])
                ->where('del_time', 0)
                ->whereIn('month', array_unique($uniqueMonths))
                ->pluck('month')
                ->toArray();
            $existingMonthsMap = array_fill_keys($existingMonths, true);
        }

        $excelAggregator = [];
        $update_map = [];

        foreach ($array as $rowIndex => $row) {
            // 用户看到的行号（假设数据从第2行开始）
            $displayLine = $rowIndex + 1;

            $valCode = trim($row[$codeIdx] ?? '');
            $valMonthTs = trim($row[$monthIdx] ?? '');
            $valDev = trim($row[$devIdx] ?? '');
            $valNumRaw = $row[$numIdx] ?? 0;

            if (!is_numeric($valMonthTs)) continue;

            $aggKey = $valCode ?: "NEW_ORDER_" . $valMonthTs;

            // --- B. 校验单据与月份的一致性 ---
            if ($valCode && isset($dbOrders[$valCode])) {
                $dbOrder = $dbOrders[$valCode];
                if ($dbOrder->month != $valMonthTs) {
                    $errors[] = "第{$displayLine}行：单据编码[{$valCode}]对应月份为[" . date('Y-m', $dbOrder->month) . "]，与导入月份不符";
                }
                $update_map[$rowIndex] = $dbOrder->id;
            }

            // --- C. 校验设备存在性与单据内唯一性 ---
            if (!isset($dbDevs[$valDev])) {
                $errors[] = "第{$displayLine}行：设备的资产编码[{$valDev}]不存在";
            } else {
                if (isset($excelAggregator[$aggKey]['devs'][$valDev])) {
                    $errors[] = "第{$displayLine}行：设备的资产编码[{$valDev}]在同一单据中重复";
                }
                $excelAggregator[$aggKey]['devs'][$valDev] = true;

                if (isset($excelAggregator[$aggKey]['month_ts']) && $excelAggregator[$aggKey]['month_ts'] !== $valMonthTs) {
                    $errors[] = "第{$displayLine}行：同组数据的月份不统一";
                }
                $excelAggregator[$aggKey]['month_ts'] = $valMonthTs;
            }

            // --- E. 月份唯一单据校验 (新增) ---
            if (!$valCode) {
                if (isset($existingMonthsMap[$valMonthTs])) {
                    $errors[] = "第{$displayLine}行：月份[ " . date('Y-m', $valMonthTs) . " ]已存在单据，请填编码编辑";
                }
            }

            // --- F. 数字校验
            $res = $this->checkNumber($valNumRaw,2,'non-negative');
            if(! $res['valid']) $errors[] = "第{$displayLine}行月折旧额：" . $res['error'];
        }

        $error_string = !empty($errors) ? implode('|', $errors) : "";
        return [$error_string, $update_map, $dbDevs];
    }





    //公共校验 -----------------------------------------
    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 = $this->getTableConfig($param['type']);
        $config_array = $config['array'];
        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)];
    }
}