<?php

namespace App\Service;

use App\Exports\TableHeadExport;
use App\Import\ImportAll;
use App\Model\DailyDwOrder;
use App\Model\DailyPwOrder;
use App\Model\Depart;
use App\Model\Device;
use App\Model\Employee;
use App\Model\EmployeeDepartPermission;
use App\Model\ExpenseClaimsDetails;
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 App\Model\PLeaveOverOrder;
use App\Model\Tag;
use App\Model\RuleSet;
use App\Model\RuleSetDetails;
use App\Model\Team;
use App\Model\TeamDetails;
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 = [
        'depart', // 部门
        'employee', // 用户
        'device', // 设备
        'item', // 项目
        'fee', // 费用
        'monthPwOrder', // 人员月度研发工时单
        'monthDwOrder', // 设备月度研发工时单
        'monthPsOrder', // 人员月度工资单
        'monthDdOrder', // 设备月度折旧单
        'ruleSet', // 规则配置单
        'dailyPwOrder', // 人员日工时单
        'dailyDwOrder', // 设备日工时单
        'leaveOrder', // 请假单
        'overtimeOrder', // 加班单
        'feeOrder', // 费用项目单
        'RDOrder', // 研发支出辅助帐
        'tag', // 标签管理
        'team', // 团队
    ];

    public function getTableTitleXls($data,$user){
        if(empty($data['type'])) return [false,'缺少类型'];

        //获取配置文件
//        $fuc = $data['type'];
//        if (! method_exists(self::class, $fuc)) return [false, "导入文件方法获取不存在,请联系开发"];
        list($status,$return) = $this->title($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 title($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 employeeImport($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->employeeCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

        $time = time();
        $insert_data = [];
        $update_data = [];
        $all_detail_insert = []; // 用于聚合所有部门权限
        $update_main_ids = [];

        // 获取工号在配置中的索引
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('number', $keys);

        // 3. 数据分拣与聚合
        foreach ($array as $key => $value) {
            $empNumber = trim((string)$value[$codeIdx]);
            if ($empNumber === '') continue;

            $main_tmp = [];
            foreach ($value as $k => $val) {
                // 只要 is_main 为 true，就记录到主表。
                // 配置单中 depart_code 为 false，会自动被此处跳过
                if (!empty($table_config[$k]['is_main'])) {
                    $main_tmp[$table_config[$k]['key']] = $val;
                }
            }

            if (isset($update_map[$key])) {
                // 更新逻辑：以 ID 为键去重
                $empId = $update_map[$key];
                $update_main_ids[$empId] = $empId;
                $update_data[$empId] = array_merge($main_tmp, ['id' => $empId]);

                // 收集部门明细
                if (isset($detail_data_map[$key])) {
                    foreach ($detail_data_map[$key] as $d) {
                        // 使用 工号_部门ID 确保明细不重复
                        $all_detail_insert[$empNumber . '_' . $d['depart_id']] = [
                            'employee_id' => $empId,
                            'depart_id' => $d['depart_id'],
                            'top_depart_id' => $user['top_depart_id']
                        ];
                    }
                }
            } else {
                // 新增逻辑：以工号为键去重
                if (!isset($insert_data[$empNumber])) {
                    $main_tmp['account'] = $user['top_depart_code'] . "_" . $empNumber;
                    $main_tmp['top_depart_id'] = $user['top_depart_id'];
                    $main_tmp['crt_id'] = $user['id'];
                    $main_tmp['crt_time'] = $time;
                    $insert_data[$empNumber] = $main_tmp;
                }

                // 收集部门明细 (待回填主表 ID)
                if (isset($detail_data_map[$key])) {
                    foreach ($detail_data_map[$key] as $d) {
                        $all_detail_insert[$empNumber . '_' . $d['depart_id']] = [
                            '_number' => $empNumber, // 临时标记
                            'depart_id' => $d['depart_id'],
                            'top_depart_id' => $user['top_depart_id']
                        ];
                    }
                }
            }
        }

        DB::beginTransaction();
        try {
            // 4. 执行新增主表
            $new_item_maps = [];
            if (!empty($insert_data)) {
                // 使用 array_values 将关联数组转为索引数组
                foreach (array_chunk(array_values($insert_data), 500) as $chunk) {
                    Employee::insert($chunk);
                }
                // 获取新插入数据的 ID 映射
                $new_item_maps = Employee::whereIn('number', array_keys($insert_data))
                    ->where('del_time', 0)
                    ->where('top_depart_id', $user['top_depart_id'])
                    ->pluck('id', 'number')->toArray();
            }

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

            // 6. 处理明细表 (先删后插策略)
            // 注意：分行模式下，更新人员的所有旧权限都要先清空
            if (!empty($update_main_ids)) {
                EmployeeDepartPermission::whereIn('employee_id', array_values($update_main_ids))->delete();
            }

            // 组装最终插入的明细数组并回填 ID
            $final_detail_insert = [];
            foreach ($all_detail_insert as $di) {
                if (isset($di['_number'])) {
                    $di['employee_id'] = $new_item_maps[$di['_number']] ?? 0;
                    unset($di['_number']);
                }
                if ($di['employee_id'] > 0) {
                    $final_detail_insert[] = $di;
                }
            }

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

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

        return [true, ''];
    }

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

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

    private function employeeCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('number', $keys);
        $sexIdx = array_search('sex', $keys);
        $eductionIdx = array_search('education', $keys);
        $stateIdx = array_search('state', $keys);
        $manIdx = array_search('man_type', $keys);
        $entrustIdx = array_search('entrust_type', $keys);
        $depIdx = array_search('depart_code', $keys);
        $employeeTypeIdx = array_search('employee_type', $keys);

        $code_map = $this->getEmployeeList($array, $user, $codeIdx);
        $dep_map = $this->getEDataList($array, $user, $depIdx);

        $errors = [];
        $update_mapping = [];
        $detail_storage = [];
        $mainDataConsistency = []; // 用于存放工号对应的主表数据备份

        $sex_map = array_flip(Employee::SEX_TYPE);
        $e_map = array_flip(Employee::Education);
        $state_map = array_flip(Employee::State_Type);
        $man_map = array_flip(Employee::Man_Type);
        $wt_map = array_flip(Employee::WT_Type);
        $employeeT_map = array_flip(Employee::E_State_Type);

        // 获取所有标记为 is_main 的列索引，用于一致性对比
        $mainColIndices = [];
        foreach ($table_config as $index => $conf) {
            if (!empty($conf['is_main'])) $mainColIndices[$index] = $conf['value'];
        }

        foreach ($array as $rowIndex => $rowValue) {
            $displayLine = $rowIndex + 1;
            $valCode = trim($rowValue[$codeIdx] ?? '');
            if ($valCode === '') continue;

            // --- 核心：一致性校验 ---
            if (!isset($mainDataConsistency[$valCode])) {
                // 第一次遇见该工号，记录其所有主表字段的值
                foreach ($mainColIndices as $idx => $label) {
                    $mainDataConsistency[$valCode][$idx] = trim($rowValue[$idx] ?? '');
                }
            } else {
                // 再次遇见该工号，对比主表字段是否一致
                foreach ($mainColIndices as $idx => $label) {
                    $currentVal = trim($rowValue[$idx] ?? '');
                    if ($currentVal !== $mainDataConsistency[$valCode][$idx]) {
                        $errors[] = "第{$displayLine}行：工号[{$valCode}]的主表数据[{$label}]与前文不一致";
                    }
                }
            }

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

            // 2. 校验（性别、学历、状态映射转换）
            // 性别
            $sex_text = trim($rowValue[$sexIdx] ?? '');
            if (!empty($sex_text)) {
                if (!isset($sex_map[$sex_text])) {
                    $errors[] = "第{$displayLine}行：性别[{$sex_text}]无效";
                } else {
                    $array[$rowIndex][$sexIdx] = $sex_map[$sex_text];
                }
            }
            // 学历
            $e_text = trim($rowValue[$eductionIdx] ?? '');
            if (!empty($e_text)) {
                if (!isset($e_map[$e_text])) {
                    $errors[] = "第{$displayLine}行：学历[{$e_text}]无效";
                } else {
                    $array[$rowIndex][$eductionIdx] = $e_map[$e_text];
                }
            }
            // 状态
            $state_text = trim($rowValue[$stateIdx] ?? '');
            if (!isset($state_map[$state_text])) {
                $errors[] = "第{$displayLine}行：状态[{$state_text}]无效";
            } else {
                $array[$rowIndex][$stateIdx] = $state_map[$state_text];
            }
            // 外聘类型
            $e_text = trim($rowValue[$employeeTypeIdx] ?? '');
            if (!isset($employeeT_map[$e_text])) {
                $errors[] = "第{$displayLine}行：外聘类型[{$e_text}]无效";
            } else {
                $array[$rowIndex][$employeeTypeIdx] = $employeeT_map[$e_text];
            }

            //人员类别
            $man_text = trim($rowValue[$manIdx] ?? '');
            if (!isset($man_map[$man_text])) {
                $errors[] = "第{$displayLine}行：人员类别[{$man_text}]无效";
            } else {
                $array[$rowIndex][$manIdx] = $man_map[$man_text];
            }

            //委托类型
            $wt_text = trim($rowValue[$entrustIdx] ?? '');
            if (!isset($wt_map[$wt_text])) {
                $errors[] = "第{$displayLine}行：委托方式[{$wt_text}]无效";
            } else {
                $array[$rowIndex][$entrustIdx] = $wt_map[$wt_text];
            }

            // 4. 解析部门 (分行模式)
            if ($depIdx !== false && !empty($rowValue[$depIdx])) {
                $mNum = trim($rowValue[$depIdx]);
                if (!isset($dep_map[$mNum])) {
                    $errors[] = "第{$displayLine}行：部门编码[{$mNum}]不存在";
                } else {
                    $detail_storage[$rowIndex][] = ['depart_id' => $dep_map[$mNum]];
                }
            }
        }

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

    private function getEDataList($array, $user, $index1)
    {
        $depNums = [];

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

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

    // 部门 ----------------------------------
    public function departImport($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->departCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

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

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

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

            $all_codes[] = $cCode;
            if (isset($parent_code_map[$key])) {
                $all_parent_codes[] = $parent_code_map[$key];
            }

            $main_tmp = [];
            foreach ($value as $k => $val){
                if(!empty($table_config[$k]['is_main'])){
                    if (!in_array($table_config[$k]['key'], $ignoreKeys)) {
                        $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;
            }
        }

        $searchCodes = array_unique(array_merge($all_codes, $all_parent_codes));

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

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

            // --- 修正点 2: 核心回填逻辑 ---
            $newCodeToIdMap = Depart::where('del_time', 0)
                ->where('top_depart_id', $user['top_depart_id'])
                ->whereIn('code', $searchCodes)
                ->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;
                if($pCode == $user['top_depart_code']) $parentId = $user['top_depart_id'];

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

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

    private function departCheck(&$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->getDepartList($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 ?: $user['top_depart_code'] ;
        }

        // 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->findDLoopInAncestors($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;
            }else{
                $parent_code_map[$rowIndex] = $user['top_depart_code'];
            }
        }

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

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

        $allFees = Depart::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 findDLoopInAncestors($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 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'));
        $deviceTypeIdx = array_search('device_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);
        $map_type_3 = array_flip(Device::$device_type);
        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_3[$value[$deviceTypeIdx]])){
                $errors[] = "第{$displayLine}行设备标签错误";
            }else{
                $array[$rowIndex][$typeIdx] = $map_type_3[$value[$deviceTypeIdx]];
            }
            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();
    }

    // 项目 -----------------------------------
    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();
    }

    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 = [];

        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);

        // 3. 数据分拣（聚合平铺数据）
        foreach ($array as $key => $value) {
            $itemCode = trim($value[$codeIdx]);
            $main_tmp = [];
            foreach ($value as $k => $val) {
                if (!empty($table_config[$k]['is_main'])) {

                    if ($table_config[$k]['key'] == 'employee_title') continue;

                    $main_tmp[$table_config[$k]['key']] = $val;
                }
            }

            if (isset($update_map[$key])) {
                // 更新逻辑：以 ID 为键去重
                $itemId = $update_map[$key];
                $update_main_ids[$itemId] = $itemId;
                $update_data[$itemId] = array_merge($main_tmp, ['id' => $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 {
                // 新增逻辑：以项目编码为键去重
                if (!isset($insert_data[$itemCode])) {
                    $main_tmp['top_depart_id'] = $user['top_depart_id'];
                    $main_tmp['crt_id'] = $user['id'];
                    $main_tmp['crt_time'] = $time;
                    $insert_data[$itemCode] = $main_tmp;
                }

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

        DB::beginTransaction();
        try {
            // 4. 执行新增主表
            $new_item_maps = [];
            if (!empty($insert_data)) {
                foreach (array_chunk(array_values($insert_data), 500) as $chunk) {
                    Item::insert($chunk);
                }
                $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 ($update_data as $id => $uItem) {
                    unset($uItem['id']);
                    Item::where('id', $id)->update($uItem);
                }
            }

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

            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 itemCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $stateIdx = array_search('state', $keys);
        $attrIdx = array_search('item_attribute', $keys);
        $budgetIdx = array_search('budget', $keys);
        $typeIdx = array_search('type', $keys);     // 明细类型列
        $code2Idx = array_search('code_2', $keys);  // 资产/工号列
        $dateIdx = array_search('start_time', $keys);
        $date2Idx = array_search('end_time', $keys);
        $empIdx = array_search('charge_id', $keys);

        $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();

        $code_map = $this->getItemList($array, $user, $codeIdx);
        // 这里的 getDataList 需要适配平铺后的 code_2 列
        list($man_map, $device_map) = $this->getFlatDataList($array, $user, $typeIdx, $code2Idx);

        $errors = [];
        $update_mapping = [];
        $detail_storage = [];
        $mainDataConsistency = []; // 一致性校验

        $state_type_map = array_flip(Item::State_Type);
        $attr_map = array_flip(Item::Item_Attribute);
        $detail_type_map = array_flip(ItemDetails::$type_name);

        // 识别主表字段列索引
        $mainColIndices = [];
        foreach ($table_config as $index => $conf) {
            if (!empty($conf['is_main'])) $mainColIndices[$index] = $conf['value'];
        }

        foreach ($array as $rowIndex => $rowValue) {
            $displayLine = $rowIndex + 1;
            $valCode = trim($rowValue[$codeIdx] ?? '');
            $valEmp = trim($rowValue[$empIdx] ?? '');
            if ($valCode === '') continue;

            // --- A. 主表一致性校验 ---
            if (!isset($mainDataConsistency[$valCode])) {
                $mainDataConsistency[$valCode] = array_intersect_key($rowValue, $mainColIndices);
            } else {
                foreach ($mainColIndices as $idx => $label) {
                    if (trim($rowValue[$idx] ?? '') != trim($mainDataConsistency[$valCode][$idx] ?? '')) {
                        $errors[] = "第{$displayLine}行：项目[{$valCode}]的主表信息[{$label}]与前文不一致";
                    }
                }
            }

            // --- B. 基础校验 ---
            if (isset($code_map[$valCode])) {
                $update_mapping[$rowIndex] = $code_map[$valCode];
            }

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

            $attr_text = $rowValue[$attrIdx] ?? '';
            if (!isset($attr_map[$attr_text])) {
                $errors[] = "第{$displayLine}行：项目属性[{$attr_text}]无效";
            } else {
                $array[$rowIndex][$attrIdx] = $attr_map[$attr_text];
            }

            $budget_text = $rowValue[$budgetIdx] ?? 0;
            if(! empty($budget_text)){
                $res = $this->checkNumber($budget_text, 2, 'non-negative');
                if (!$res['valid']) $errors[] = "第{$displayLine}行预算：" . $res['error'];
            }

            // D. 人员校验
            if(! empty($valEmp)){
                if (!isset($dbEmps[$valEmp])) {
                    $errors[] = "第{$displayLine}行：人员工号[{$valEmp}]不存在";
                } else {
                    $array[$rowIndex][$empIdx] = $dbEmps[$valEmp];
                }
            }

            // 日期转换
            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;
                }
            }

            // --- C. 解析明细 (一行一个明细) ---
            $typeName = trim($rowValue[$typeIdx] ?? '');
            $subCode  = trim($rowValue[$code2Idx] ?? '');

            if ($typeName !== '' && $subCode !== '') {
                $typeVal = $detail_type_map[$typeName] ?? 0;
                if (!$typeVal) {
                    $errors[] = "第{$displayLine}行：明细类型[{$typeName}]无效";
                } else {
                    if ($typeVal == ItemDetails::type_one) {
                        if (!isset($man_map[$subCode])) {
                            $errors[] = "第{$displayLine}行：人员工号[{$subCode}]不存在";
                        } else {
                            $detail_storage[$rowIndex][] = ['type' => $typeVal, 'data_id' => $man_map[$subCode]];
                        }
                    } else {
                        if (!isset($device_map[$subCode])) {
                            $errors[] = "第{$displayLine}行：设备编码[{$subCode}]不存在";
                        } else {
                            $detail_storage[$rowIndex][] = ['type' => $typeVal, 'data_id' => $device_map[$subCode]];
                        }
                    }
                }
            }
        }

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

    private function getFlatDataList($array, $user, $typeIdx, $code2Idx)
    {
        $manNums = [];
        $devCodes = [];
        $detail_type_map = array_flip(ItemDetails::$type_name);

        foreach ($array as $row) {
            $typeName = trim($row[$typeIdx] ?? '');
            $val = trim($row[$code2Idx] ?? '');
            if ($val === '') continue;

            $typeVal = $detail_type_map[$typeName] ?? 0;
            if ($typeVal == ItemDetails::type_one) {
                $manNums[$val] = true;
            } elseif ($typeVal == ItemDetails::type_two) {
                $devCodes[$val] = 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);
        // ------------------------------------

        $ignoreKeys = ['parent_id', 'parent_title'];
        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 (!in_array($table_config[$k]['key'], $ignoreKeys)) {
                        $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;
            }
        }

        $all_parent_codes = array_values($parent_code_map);
        $searchCodes = array_unique(array_merge($all_codes, $all_parent_codes));

        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', $searchCodes)
                ->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);
        $isOtherIdx = array_search('is_other', $keys);

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

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

        $is_other_map = array_flip(Fee::IS_OTHER);

        // 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'];
            }

            // 其他费用
            $is_other_text = $value[$isOtherIdx] ?? '';
            if (!isset($is_other_map[$is_other_text])) {
                $errors[] = "第{$displayLine}行：是否其他费用[{$is_other_text}]无效";
            } else {
                $array[$rowIndex][$isOtherIdx] = $is_other_map[$is_other_text];
            }

            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'];

                    if ($fieldKey == 'employee_title') continue;

                    $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);
        $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);

        $topDepartId = $user['top_depart_id'];
        $errors = [];

        // --- 1. 预处理月份与工号 ---
        $allEmpNumbers = [];
        $allMonthsTs = [];
        foreach ($array as $rowIndex => $row) {
            $valMonthRaw = trim($row[$monthIdx] ?? '');
            list($mStatus, $valMonthTs) = $this->convertExcelCellToDate($valMonthRaw);
            if (!$mStatus) {
                $errors[] = "第" . ($rowIndex + 1) . "行：月份格式错误";
                continue;
            }
            $valMonthTs = strtotime(date('Y-m-01', $valMonthTs));
            $array[$rowIndex][$monthIdx] = $valMonthTs;
            $allMonthsTs[] = $valMonthTs;
            if (!empty($row[$empIdx])) $allEmpNumbers[] = trim($row[$empIdx]);
        }
        if (!empty($errors)) return [implode('|', $errors), [], []];

        $allMonthsTs = array_unique($allMonthsTs);
        $allEmpNumbers = array_unique($allEmpNumbers);

        //归档
        list($status, $msg) = ArchiveService::isArchive($allMonthsTs, $user);
        if(! $status)  return [$msg, [], []];

        // --- 2. 批量预加载 ---
        // A. 人员档案
        $empModels = Employee::where('del_time', 0)->whereIn('number', $allEmpNumbers)
            ->where('top_depart_id', $topDepartId)->get(['id', 'number', 'title'])->keyBy('number');
        $dbEmps = $empModels->pluck('id', 'number')->toArray();

        // B. 系统考勤基准 (按月获取)
        $systemStatsMap = [];
        $service = new EmployeeService();
        foreach ($allMonthsTs as $ts) {
            list($sStatus, $stats) = $service->getEmployeesMonthStats(array_values($dbEmps), $ts, $user);
            if (!$sStatus) {
                $errors[] = "月份[" . date('Y-m', $ts) . "]：" . $stats;
                continue;
            }
            $systemStatsMap[$ts] = $stats;
        }
        if (!empty($errors)) return [implode('|', $errors), [], []];

        // C. 核心修正：单据查重（建立 月份 -> 单号 的映射，避免循环内查询）
        $allCodes = array_filter(array_unique(array_column($array, $codeIdx)));

        // 1) 所有的 Excel 涉及月份在数据库中已有的单据映射
        $existingMonthsMap = DB::table('monthly_pw_order')
            ->where('top_depart_id', $topDepartId)
            ->where('del_time', 0)
            ->whereIn('month', $allMonthsTs)
            ->pluck('code', 'month') // 注意：这里直接查出 month => code
            ->toArray();

        // 2) 用户填写的单据号对应的数据库记录
        $dbOrdersByCode = DB::table('monthly_pw_order')
            ->whereIn('code', $allCodes)
            ->where('top_depart_id', $topDepartId)
            ->where('del_time', 0)
            ->get()
            ->keyBy('code');

        // --- 3. 业务逻辑全量检查 ---
        $excelAggregator = [];
        $update_map = [];

        foreach ($array as $rowIndex => $row) {
            $line = $rowIndex + 1;
            $valCode = trim($row[$codeIdx] ?? '');
            $valMonthTs = $row[$monthIdx];
            $valEmpNum = trim($row[$empIdx] ?? '');
            $monthStr = date('Y-m', $valMonthTs);

            if (!isset($empModels[$valEmpNum])) {
                $errors[] = "第{$line}行：工号[{$valEmpNum}]不存在";
                continue;
            }
            $empId = $empModels[$valEmpNum]->id;
            $empTitle = "[{$valEmpNum}]" . $empModels[$valEmpNum]->title;

            // --- 核心修正：单据单号提示逻辑 ---
            if ($valCode !== '') {
                if (isset($dbOrdersByCode[$valCode])) {
                    if ($dbOrdersByCode[$valCode]->month != $valMonthTs) {
                        $errors[] = "第{$line}行：单据[{$valCode}]月份应为" . date('Y-m', $dbOrdersByCode[$valCode]->month) . "，请核实";
                    }
                    $update_map[$rowIndex] = $dbOrdersByCode[$valCode]->id;
                } else {
                    $errors[] = "第{$line}行：填写的单据编号[{$valCode}]在系统中不存在";
                }
            } else {
                // 如果没填单号，但该月已存在单据，从预加载的 existingMonthsMap 中获取正确单号
                if (isset($existingMonthsMap[$valMonthTs])) {
                    $correctCode = $existingMonthsMap[$valMonthTs];
                    $errors[] = "第{$line}行：月份[{$monthStr}]已存在研发单，单号为：[{$correctCode}]，请填写该单号进行编辑";
                }
            }

            // --- 考勤上限校验 ---
            $sysEmpData = $systemStatsMap[$valMonthTs][$empId] ?? null;
            if ($sysEmpData) {
                $inTotalDays = (float)($row[$numIdx] ?? 0);
                $inRdDays    = (float)($row[$num2Idx] ?? 0);
                $inTotalHours= (float)($row[$num3Idx] ?? 0);
                $inRdHours   = (float)($row[$num4Idx] ?? 0);

                // 使用误差范围比较，避免浮点数精度问题
                if (abs($inTotalDays - $sysEmpData['attendance_days']) > 0.01) {
                    $errors[] = "第{$line}行：{$empTitle}出勤总天数({$inTotalDays})与核算({$sysEmpData['attendance_days']})不符";
                }
                if (abs($inTotalHours - $sysEmpData['final_work_hour']) > 0.01) {
                    $errors[] = "第{$line}行：{$empTitle}出勤总工时({$inTotalHours})与核算({$sysEmpData['final_work_hour']})不符";
                }
                if ($inRdDays > $inTotalDays + 0.01) $errors[] = "第{$line}行：{$empTitle}研发天数超过总天数";
                if ($inRdHours > $inTotalHours + 0.01) $errors[] = "第{$line}行：{$empTitle}研发工时超过总工时";
            }

            if (isset($excelAggregator[$valCode ?: $valMonthTs]['emps'][$valEmpNum])) {
                $errors[] = "第{$line}行：人员在同单据内重复";
            }
            $excelAggregator[$valCode ?: $valMonthTs]['emps'][$valEmpNum] = true;
        }

        return [!empty($errors) ? implode('|', $errors) : "", $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'];

                    if ($fieldKey == 'device_title') continue;

                    $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);
        $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);

        $topDepartId = $user['top_depart_id'];
        $errors = [];

        // --- 1. 提取月份和资产编码 ---
        $allDevCodes = [];
        $allMonthsTs = [];
        foreach ($array as $rowIndex => $row) {
            list($mStatus, $valMonthTs) = $this->convertExcelCellToDate($row[$monthIdx] ?? '');
            if (!$mStatus) {
                $errors[] = "第" . ($rowIndex + 1) . "行：月份格式错误";
                continue;
            }
            $valMonthTs = strtotime(date('Y-m-01', $valMonthTs));
            $array[$rowIndex][$monthIdx] = $valMonthTs;
            $allMonthsTs[] = $valMonthTs;
            if (!empty($row[$devIdx])) $allDevCodes[] = trim($row[$devIdx]);
        }
        if (!empty($errors)) return [implode('|', $errors), [], []];

        $allMonthsTs = array_unique($allMonthsTs);

        //归档
        list($status, $msg) = ArchiveService::isArchive($allMonthsTs, $user);
        if(! $status)  return [$msg, [], []];

        // --- 2. 预加载档案与基准 ---
        $devModels = Device::where('del_time', 0)
            ->whereIn('code', $allDevCodes)
            ->where('top_depart_id', $topDepartId)
            ->get(['id', 'code', 'title'])
            ->keyBy('code');
        $dbDevs = $devModels->pluck('id', 'code')->toArray();

        $systemStatsMap = [];
        $allDeviceIds = array_values($dbDevs);
        $service = new DeviceService();
        foreach ($allMonthsTs as $ts) {
            list($sStatus, $stats) = $service->getDevicesMonthStats($allDeviceIds, $ts, $user);
            if (!$sStatus) {
                $errors[] = "月份[" . date('Y-m', $ts) . "]解析失败：{$stats}";
                $systemStatsMap[$ts] = null;
            } else {
                $systemStatsMap[$ts] = $stats;
            }
        }
        // 如果基准获取有误（如日历未设），直接返回错误，不执行后续逻辑
        if (!empty($errors)) return [implode('|', $errors), [], []];

        // --- 3. 核心修正：单据查重与单号映射 (避免循环内查询) ---
        $allCodes = array_filter(array_unique(array_column($array, $codeIdx)));

        // 获取该租户相关月份已存在的单据映射 (Month => Code)
        $existingMonthsMap = DB::table('monthly_dw_order')
            ->where('top_depart_id', $topDepartId)
            ->where('del_time', 0)
            ->whereIn('month', $allMonthsTs)
            ->pluck('code', 'month')
            ->toArray();

        // 获取 Excel 中填写的单号对应的数据库记录 (Code => Object)
        $dbOrdersByCode = DB::table('monthly_dw_order')
            ->whereIn('code', $allCodes)
            ->where('top_depart_id', $topDepartId)
            ->where('del_time', 0)
            ->get()
            ->keyBy('code');

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

        // --- 4. 循环校验 ---
        foreach ($array as $rowIndex => $row) {
            $line = $rowIndex + 1;
            $valMonthTs = $row[$monthIdx];
            $monthStr = date('Y-m', $valMonthTs);
            $valDevCode = trim($row[$devIdx] ?? '');
            $valCode = trim($row[$codeIdx] ?? '');

            // A. 设备档案检查
            if (!isset($devModels[$valDevCode])) {
                $errors[] = "第{$line}行：设备编码[{$valDevCode}]不存在";
                continue;
            }
            $deviceId = $devModels[$valDevCode]->id;
            $devTitle = "[{$valDevCode}]" . $devModels[$valDevCode]->title;

            // B. 单据号存在性与意图校验
            if ($valCode !== '') {
                // 意图：编辑已存在的单据
                if (isset($dbOrdersByCode[$valCode])) {
                    $dbOrder = $dbOrdersByCode[$valCode];
                    if ($dbOrder->month != $valMonthTs) {
                        $errors[] = "第{$line}行：单据[{$valCode}]所属月份为" . date('Y-m', $dbOrder->month) . "，与当前月份不符";
                    }
                    $update_map[$rowIndex] = $dbOrder->id;
                } else {
                    // 填了单号但库里没有
                    $errors[] = "第{$line}行：单据编号[{$valCode}]无效或不存在";
                }
            } else {
                // 意图：新增。校验是否该月已有单据
                if (isset($existingMonthsMap[$valMonthTs])) {
                    $correctCode = $existingMonthsMap[$valMonthTs];
                    $errors[] = "第{$line}行：月份[{$monthStr}]已存在研发单，单号为：[{$correctCode}]，请填写该单号进行更新";
                }
            }

            // C. 考勤基准对比
            $sysData = $systemStatsMap[$valMonthTs][$deviceId] ?? null;
            if ($sysData) {
                $inTotalDays = (float)($row[$numIdx] ?? 0);
                $inRdDays    = (float)($row[$num2Idx] ?? 0);
                $inTotalHours= (float)($row[$num3Idx] ?? 0);
                $inRdHours   = (float)($row[$num4Idx] ?? 0);

                // 1. 内部逻辑：研发 <= 总额
                if ($inRdDays > $inTotalDays + 0.01) $errors[] = "第{$line}行：{$devTitle}研发天数大于总天数";
                if ($inRdHours > $inTotalHours + 0.01) $errors[] = "第{$line}行：{$devTitle}研发工时大于总工时";

                // 2. 外部逻辑：总额 == 系统核算
                if (abs($inTotalDays - $sysData['attendance_days']) > 0.01) {
                    $errors[] = "第{$line}行：{$devTitle}总天数({$inTotalDays})不等于系统核算({$sysData['attendance_days']})";
                }
                if (abs($inTotalHours - $sysData['final_work_hour']) > 0.01) {
                    $errors[] = "第{$line}行：{$devTitle}总工时({$inTotalHours})不等于系统核算({$sysData['final_work_hour']})";
                }
            }

            // D. 组内查重 (单号或月份作为聚合维度)
            $aggKey = $valCode ?: "NEW_" . $valMonthTs;
            if (isset($excelAggregator[$aggKey]['devs'][$valDevCode])) {
                $errors[] = "第{$line}行：资产编码[{$valDevCode}]在同单据中重复";
            }
            $excelAggregator[$aggKey]['devs'][$valDevCode] = true;
        }

        $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'];

                    if ($fieldKey == 'employee_title') continue;

                    $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('base_salary', $keys);
        $num2Idx = array_search('social_insurance', $keys);
        $num3Idx = array_search('public_housing_fund', $keys);
        $num4Idx = array_search('performance_salary', $keys);
        $num5Idx = array_search('bonus', $keys);
        $num6Idx = array_search('other', $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'])
            ->where('del_time', 0) // 必须确保没删除
            ->get()->keyBy('code');

        $errors = [];
        $uniqueMonths = [];

        // --- 步骤 1：月份标准化预处理 ---
        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;
        }
        if (!empty($errors)) return [implode('|', $errors), [], []];

        $allMonthsTs = array_unique($uniqueMonths);

        //归档
        list($status, $msg) = ArchiveService::isArchive($allMonthsTs, $user);
        if(! $status)  return [$msg, [], []];

        // 查询数据库中已存在的月份单据
        $existingMonths = DB::table('monthly_ps_order')
            ->where('top_depart_id', $user['top_depart_id'])
            ->where('del_time', 0)
            ->whereIn('month', $allMonthsTs)
            ->pluck('month')
            ->toArray();
        $existingMonthsMap = array_fill_keys($existingMonths, true);

        $excelAggregator = [];
        $update_map = [];
        $monthToKeyMap = []; // 校验 Excel 内部月份是否冲突

        // --- 步骤 2：全量业务校验 ---
        foreach ($array as $rowIndex => $row) {
            $displayLine = $rowIndex + 1;

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

            if (!is_numeric($valMonthTs)) continue;

            // 确定单据组 AggKey
            $aggKey = $valCode ?: "NEW_ORDER_" . $valMonthTs;

            // A. 校验单据存在性 & 匹配 ID
            if ($valCode) {
                if (isset($dbOrders[$valCode])) {
                    $dbOrder = $dbOrders[$valCode];
                    if ($dbOrder->month != $valMonthTs) {
                        $errors[] = "第{$displayLine}行：单据编码[{$valCode}]对应月份为[" . date('Y-m', $dbOrder->month) . "]，与导入月份不符";
                    }
                    $update_map[$rowIndex] = $dbOrder->id;
                }
                // 乱填编码的情况，不会进 update_map，将由下方的查重逻辑拦截
            }

            // B. 【核心修正】月份唯一单据校验
            // 如果系统最终认为这行是“新增”，则必须检查该月份是否已有数据
            if (!isset($update_map[$rowIndex])) {
                if (isset($existingMonthsMap[$valMonthTs])) {
                    $errors[] = "第{$displayLine}行：月份[" . date('Y-m', $valMonthTs) . "]已存在单据，请填写正确的单据编码进行编辑";
                }
            }

            // C. Excel 内部逻辑一致性
            if (!isset($monthToKeyMap[$valMonthTs])) {
                $monthToKeyMap[$valMonthTs] = $aggKey;
            } elseif ($monthToKeyMap[$valMonthTs] !== $aggKey) {
                $errors[] = "第{$displayLine}行：同一个月份[" . date('Y-m', $valMonthTs) . "]在 Excel 中指向了不同的单据组";
            }

            // D. 人员校验与单据内查重
            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. 数字格式校验
            $numChecks = [
                $numIdx => '基本工资',
                $num2Idx => '社保',
                $num3Idx => '公积金',
                $num4Idx => '绩效工资',
                $num5Idx => '奖金',
                $num6Idx => '其他',
            ];
            foreach ($numChecks as $idx => $label) {
                $res = $this->checkNumber($row[$idx] ?? 0, 2, 'non-negative');
                if (!$res['valid']) $errors[] = "第{$displayLine}行{$label}：" . $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'];
                    if ($fieldKey == 'device_title') continue;

                    $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();

            $id = [];
            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(! in_array($mainId, $id)) $id[] = $mainId;

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

            DB::commit();

            //触发折旧计算
            (new DeviceDepreciationService())->triggerJob($id);

        } 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. 预加载基础数据
        $allDevNumbers = array_filter(array_unique(array_column($array, $devIdx)));
        $dbDevs = Device::where('del_time', 0)
            ->whereIn('code', $allDevNumbers)
            ->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'])
            ->where('del_time', 0)
            ->get()->keyBy('code');

        $errors = [];
        $uniqueMonths = [];

        // --- 步骤 1：月份标准化 ---
        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;
        }
        if (!empty($errors)) return [implode('|', $errors), [], []];

        $allMonthsTs = array_unique($uniqueMonths);

        //归档
        list($status, $msg) = ArchiveService::isArchive($allMonthsTs, $user);
        if(! $status)  return [$msg, [], []];

        // 预查数据库已存在的月份
        $existingMonths = DB::table('monthly_dd_order')
            ->where('top_depart_id', $user['top_depart_id'])
            ->where('del_time', 0)
            ->whereIn('month', $allMonthsTs)
            ->pluck('month')
            ->toArray();
        $existingMonthsMap = array_fill_keys($existingMonths, true);

        $excelAggregator = [];
        $update_map = [];
        $monthToKeyMap = []; // 校验同一月份在 Excel 内部是否产生了多个单据组

        // --- 步骤 2：全量校验 ---
        foreach ($array as $rowIndex => $row) {
            $displayLine = $rowIndex + 1;

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

            if (!is_numeric($valMonthTs)) continue;

            // 确定单据聚合 Key
            $aggKey = $valCode ?: "NEW_ORDER_" . $valMonthTs;

            // A. 匹配更新 ID
            if ($valCode) {
                if (isset($dbOrders[$valCode])) {
                    $dbOrder = $dbOrders[$valCode];
                    if ($dbOrder->month != $valMonthTs) {
                        $errors[] = "第{$displayLine}行：单据[{$valCode}]对应月份为[" . date('Y-m', $dbOrder->month) . "]，与导入不符";
                    }
                    $update_map[$rowIndex] = $dbOrder->id;
                }
                // 乱填编码的情况不进 update_map，将被视为“新增”由下方逻辑拦截
            }

            // B. 【核心修正】月份唯一性拦截
            // 只要不是更新已有单据，且月份已存在，就报错
            if (!isset($update_map[$rowIndex])) {
                if (isset($existingMonthsMap[$valMonthTs])) {
                    $errors[] = "第{$displayLine}行：月份[" . date('Y-m', $valMonthTs) . "]已存在折旧单，请填正确编码编辑";
                }
            }

            // C. Excel 内部逻辑一致性
            if (!isset($monthToKeyMap[$valMonthTs])) {
                $monthToKeyMap[$valMonthTs] = $aggKey;
            } elseif ($monthToKeyMap[$valMonthTs] !== $aggKey) {
                $errors[] = "第{$displayLine}行：同月份[" . date('Y-m', $valMonthTs) . "]存在多组单据定义";
            }

            // D. 设备校验
            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. 数字校验
            $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];
    }

    // 规则配置单
    public function ruleSetImport($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, $dbDevs) = $this->ruleSetCheck($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);
        $rateIdx = array_search('rate', $keys);

        $groups = [];
        foreach ($array as $rowIndex => $row) {
            $mCode = trim($row[$codeIdx] ?? '');
            $month = $row[$monthIdx];
            $aggKey = $mCode ?: "NEW_" . $month;

            if (!isset($groups[$aggKey])) {
                $groups[$aggKey] = [
                    'id' => $update_map[$rowIndex] ?? 0,
                    'month' => $month,
                    'details' => []
                ];
            }
            $groups[$aggKey]['details'][] = [
                'item_id' => $row['parsed_item_id'],
                'data_id' => $row['parsed_data_id'],
                'type'    => $row['parsed_type'],
                'rate'    => $row[$rateIdx],
            ];
        }

        $time = time();
        DB::beginTransaction();
        try {
            foreach ($groups as $group) {
                $mainId = $group['id'];
                if ($mainId) {
                    DB::table('rule_set_details')
                        ->where('del_time',0)
                        ->where('main_id', $mainId)->update(['del_time' => $time]);
                } else {
                    $newCode = $this->generateBillNo([
                        'top_depart_id' => $user['top_depart_id'],
                        'type' => RuleSet::Order_type,
                        'period' => date("Ym", $group['month'])
                    ]);
                    $mainId = DB::table('rule_set')->insertGetId([
                        'code' => $newCode,
                        'month' => $group['month'],
                        'top_depart_id' => $user['top_depart_id'],
                        'crt_id' => $user['id'],
                        'crt_time' => $time
                    ]);
                }

                foreach ($group['details'] as &$d) {
                    $d['main_id'] = $mainId;
                    $d['top_depart_id'] = $user['top_depart_id'];
                    $d['crt_time'] = $time;
                }
                DB::table('rule_set_details')->insert($group['details']);
            }
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return [false, "失败: " . $e->getMessage()];
        }

        return [true, ''];
    }

    private function ruleSetCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');

        // 1. 索引提取
        $mCodeIdx = array_search('code', $keys);
        $monthIdx = array_search('month', $keys);
        $typeIdx  = array_search('type', $keys);
        $dCodeIdx = array_search('code_2', $keys);
        $itemIdIdx = array_search('item_id', $keys);
        $rateIdx   = array_search('rate', $keys);

        $uniqueMonths = [];
        $empCodes = [];
        $devCodes = [];
        $itemCodes = [];
        $type_map = array_flip(RuleSetDetails::$type_name);

        // --- 步骤 1: 预处理扫描 ---
        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;

            $type = $type_map[$row[$typeIdx] ?? ''] ?? 0;
            $dCode = trim($row[$dCodeIdx] ?? '');
            $iCode = trim($row[$itemIdIdx] ?? '');

            if ($type == RuleSetDetails::type_one && $dCode !== '') $empCodes[] = $dCode;
            if ($type == RuleSetDetails::type_two && $dCode !== '') $devCodes[] = $dCode;
            if ($iCode !== '') $itemCodes[] = $iCode;
        }

        if (!empty($errors)) return [implode('|', $errors), [], []];

        $allMonthsTs = array_unique($uniqueMonths);

        //归档
        list($status, $msg) = ArchiveService::isArchive($allMonthsTs, $user);
        if(! $status)  return [$msg, [], []];

        // --- 步骤 2: 批量预加载 ---
        $dbEmps = Employee::where('del_time', 0)->where('top_depart_id', $user['top_depart_id'])
            ->whereIn('number', array_unique($empCodes))->pluck('id', 'number')->toArray();
        $dbDevs = Device::where('del_time', 0)->where('top_depart_id', $user['top_depart_id'])
            ->whereIn('code', array_unique($devCodes))->pluck('id', 'code')->toArray();
        $dbItems = Item::where('del_time', 0)->where('top_depart_id', $user['top_depart_id'])
            ->whereIn('code', array_unique($itemCodes))->pluck('id', 'code')->toArray();

        $allMCodes = array_filter(array_unique(array_column($array, $mCodeIdx)));
        $dbOrders = DB::table('rule_set')->where('top_depart_id', $user['top_depart_id'])
            ->where('del_time', 0)->whereIn('code', $allMCodes)->get()->keyBy('code');

        $existingMonthsMap = DB::table('rule_set')->where('top_depart_id', $user['top_depart_id'])
            ->where('del_time', 0)->whereIn('month', array_unique($uniqueMonths))->pluck('id', 'month')->toArray();

        $errors = [];
        $update_map = [];
        $aggregator = [];
        $uniqueCheck = [];
        $monthToKeyMap = []; // 校验 Excel 内部月份与单号组的唯一性

        // --- 步骤 3: 详细校验循环 ---
        foreach ($array as $rowIndex => $row) {
            $displayLine = $rowIndex + 1;
            $valMCode = trim($row[$mCodeIdx] ?? '');
            $valMonthTs = $row[$monthIdx] ?? '';
            $typeTitle = trim($row[$typeIdx] ?? '');
            $type = $type_map[$typeTitle] ?? 0;
            $valDCode = trim($row[$dCodeIdx] ?? '');
            $valICode = trim($row[$itemIdIdx] ?? '');
            $valRate = $row[$rateIdx] ?? 0;

            // A. 编辑/新增逻辑判定 (核心修正)
            if ($valMCode !== '' && isset($dbOrders[$valMCode])) {
                $dbOrder = $dbOrders[$valMCode];
                if ($dbOrder->month != $valMonthTs) {
                    $errors[] = "第{$displayLine}行：单号[{$valMCode}]对应月份为[" . date('Y-m', $dbOrder->month) . "]，不允许修改";
                }
                $update_map[$rowIndex] = $dbOrder->id;
            }

            // 月份唯一性拦截：只要没进入 update_map (无论是乱填单号还是没填)，就查重
            if (!isset($update_map[$rowIndex])) {
                if (isset($existingMonthsMap[$valMonthTs])) {
                    $errors[] = "第{$displayLine}行：月份[" . date('Y-m', $valMonthTs) . "]已存在单据，请填写正确的单号编辑";
                }
            }

            // B. Excel 内部一致性拦截
            $aggKey = $valMCode ?: "NEW_ORDER_" . $valMonthTs;
            if (!isset($monthToKeyMap[$valMonthTs])) {
                $monthToKeyMap[$valMonthTs] = $aggKey;
            } elseif ($monthToKeyMap[$valMonthTs] !== $aggKey) {
                $errors[] = "第{$displayLine}行：月份[" . date('Y-m', $valMonthTs) . "]在表格中对应了不同的单据编号，请确保同一月份的单号一致";
            }

            // C. 人员/设备/项目存在性校验
            $dataId = 0;
            if ($type == RuleSetDetails::type_one) {
                $dataId = $dbEmps[$valDCode] ?? 0;
                if (!$dataId) $errors[] = "第{$displayLine}行：工号[{$valDCode}]不存在";
            } elseif ($type == RuleSetDetails::type_two) {
                $dataId = $dbDevs[$valDCode] ?? 0;
                if (!$dataId) $errors[] = "第{$displayLine}行：资产编码[{$valDCode}]不存在";
            } else {
                $errors[] = "第{$displayLine}行：明细类型[{$typeTitle}]非法";
            }

            $itemId = $dbItems[$valICode] ?? 0;
            if (!$itemId) $errors[] = "第{$displayLine}行：项目编码[{$valICode}]不存在";

            // D. 比例与唯一性聚合
            if ($dataId && $itemId) {
                // 同一单据+项目内 人/设备唯一
                $rowUniqueKey = $aggKey . '_' . $itemId . '_' . $type . '_' . $dataId;
                if (isset($uniqueCheck[$rowUniqueKey])) {
                    $errors[] = "第{$displayLine}行：项目[{$valICode}]下[{$valDCode}]重复录入";
                }
                $uniqueCheck[$rowUniqueKey] = true;

                // 比例和校验聚合
                $totalRateKey = $aggKey . '_' . $type . '_' . $dataId;
                $aggregator[$totalRateKey]['total_rate'] = ($aggregator[$totalRateKey]['total_rate'] ?? 0) + $valRate;
                $aggregator[$totalRateKey]['title'] = $valDCode;
                $aggregator[$totalRateKey]['type_name'] = $typeTitle;
                $aggregator[$totalRateKey]['month_str'] = date('Y-m', $valMonthTs);
            }

            $array[$rowIndex]['parsed_data_id'] = $dataId;
            $array[$rowIndex]['parsed_item_id'] = $itemId;
            $array[$rowIndex]['parsed_type'] = $type;
        }

        // --- 步骤 4: 比例汇总校验 ---
        foreach ($aggregator as $key => $info) {
            if (abs($info['total_rate'] - 100) > 0.0001) {
                $errors[] = "汇总错误：月份[{$info['month_str']}]下，{$info['type_name']}[{$info['title']}]的总分摊比例为 {$info['total_rate']}%，不等于 100%";
            }
        }

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

    // 人员日工时单 ------------------------------
    public function dailyPwOrderImport($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. 详细业务校验 (返回 update_map, 人员映射, 项目映射)
        list($error, $update_map, $dbEmps, $dbItems) = $this->dailyPwOrderCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $timeIdx = array_search('order_time', $keys);
        $itemIdx = array_search('item_id', $keys);
        $empIdx  = array_search('employee_id', $keys);
        $startIdx = array_search('start_time', $keys);
        $endIdx   = array_search('end_time', $keys);

        // 3. 数据聚合分组
        $groups = [];
        foreach ($array as $rowIndex => $row) {
            $cCode = trim($row[$codeIdx] ?? '');
            $cTimeTs = $row[$timeIdx];
            $cItemCode = trim($row[$itemIdx] ?? '');
            $cItemId = $dbItems[$cItemCode] ?? 0;

            // 聚合 Key：如果是更新则按主表ID聚类；如果是新增则按 日期+项目 聚类
            $aggKey = ($update_map[$rowIndex] ?? 0) > 0
                ? "UPDATE_" . $update_map[$rowIndex]
                : "NEW_" . $cTimeTs . "_" . $cItemId;

            if (!isset($groups[$aggKey])) {
                $groups[$aggKey] = [
                    'main_id'    => $update_map[$rowIndex] ?? 0,
                    'code'       => $cCode,
                    'order_time' => $cTimeTs,
                    'item_id'    => $cItemId,
                    'details'    => []
                ];
            }

            // 解析时间
            list($sH, $sM) = $this->parseTimeHourMin($row[$startIdx]);
            list($eH, $eM) = $this->parseTimeHourMin($row[$endIdx]);

            $groups[$aggKey]['details'][] = [
                'employee_id'     => $dbEmps[trim($row[$empIdx])] ?? 0,
                'start_time_hour' => $sH,
                'start_time_min'  => $sM,
                'end_time_hour'   => $eH,
                'end_time_min'    => $eM,
                'total_work_min'  => ($eH * 60 + $eM) - ($sH * 60 + $sM), // 计算分钟差
            ];
        }

        // 4. 执行写入
        DB::beginTransaction();
        try {
            $time = time();
            foreach ($groups as $group) {
                $mainId = $group['main_id'];

                if ($mainId > 0) {
                    // 编辑：更新主表（允许改项目）
                    DB::table('daily_pw_order')->where('id', $mainId)->update([
                        'item_id'  => $group['item_id'],
                        'upd_time' => $time
                    ]);
                    // 逻辑删除旧明细
                    DB::table('daily_pw_order_details')->where('main_id', $mainId)
                        ->where('del_time', 0)
                        ->update(['del_time' => $time]);
                } else {
                    // 新增：自动生成单号
                    $newCode = $this->generateBillNo([
                        'top_depart_id' => $user['top_depart_id'],
                        'type'          => DailyPwOrder::Order_type,
                        'period'        => date("Ym", $group['order_time'])
                    ]);

                    $mainId = DB::table('daily_pw_order')->insertGetId([
                        'code'          => $newCode,
                        'order_time'    => $group['order_time'],
                        'item_id'       => $group['item_id'],
                        'top_depart_id' => $user['top_depart_id'],
                        'crt_id'        => $user['id'],
                        'crt_time'      => $time,
                    ]);
                }

                // 批量插入明细
                $insertDetails = [];
                foreach ($group['details'] as $detail) {
                    $detail['main_id']       = $mainId;
                    $detail['top_depart_id'] = $user['top_depart_id'];
                    $detail['crt_time']      = $time;
                    $detail['crt_id']      = $user['id'];
                    $insertDetails[] = $detail;
                }
                if (!empty($insertDetails)) {
                    DB::table('daily_pw_order_details')->insert($insertDetails);
                }
            }
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return [false, "失败：" . $e->getMessage()];
        }

        return [true, ''];
    }

    private function dailyPwOrderCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $timeIdx = array_search('order_time', $keys);
        $itemIdx = array_search('item_id', $keys);
        $empIdx  = array_search('employee_id', $keys);
        $startIdx = array_search('start_time', $keys);
        $endIdx   = array_search('end_time', $keys);

        $topDepartId = $user['top_depart_id'];

        // 1. 批量预加载基础档案
        $empCodes = array_filter(array_unique(array_column($array, $empIdx)));
        $empModels = Employee::where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('number', $empCodes)->get(['id', 'number', 'title']);

        $dbEmps = $empModels->pluck('id', 'number')->toArray();
        $empDisplayMap = $empModels->mapWithKeys(function($item){
            return [$item->id => "[{$item->number}]{$item->title}"];
        })->toArray();

        $itemCodes = array_filter(array_unique(array_column($array, $itemIdx)));
        $dbItems = Item::where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('code', $itemCodes)->pluck('id', 'code')->toArray();

        // 2. 预加载已有单据
        $allCodes = array_filter(array_unique(array_column($array, $codeIdx)));
        $dbOrders = DB::table('daily_pw_order')->where('top_depart_id', $topDepartId)
            ->whereIn('code', $allCodes)->where('del_time', 0)->get()->keyBy('code');

        // --- 跨单据预查：提前解析日期并检索库内已存在的工时区间 ---
        $allDates = [];
        foreach ($array as $rowIndex => $row) {
            $valMonthRaw = $row[$timeIdx] ?? '';
            list($status, $ts) = $this->convertExcelCellToDate($valMonthRaw);
            if(! $status) {
                $errors[] = "第" . ($rowIndex + 1) . "行：单据日期格式错误({$valMonthRaw})";
                continue;
            }
            $d = strtotime(date('Y-m-d', $ts));
            $array[$rowIndex][$timeIdx] = $d;
            $allDates[] = $d;
        }
        if (!empty($errors)) return [implode('|', $errors), [], [], []];

        $allDates = array_unique($allDates);

        //归档
        list($status, $msg) = ArchiveService::isArchive($allDates, $user);
        if(! $status)  return [$msg, [], [], []];

        // 查询数据库：同公司、同日期、同项目、同人员的已有工时 (排除本次 Excel 涉及到的单号)
        $dbExistingWork = DB::table('daily_pw_order_details as d')
            ->join('daily_pw_order as m', 'd.main_id', '=', 'm.id')
            ->where('m.top_depart_id', $topDepartId) // 核心：公司数据隔离
            ->whereIn('m.order_time', $allDates)
            ->whereIn('m.item_id', array_values($dbItems))
            ->whereIn('d.employee_id', array_values($dbEmps))
            ->where('m.del_time', 0)
            ->where('d.del_time', 0)
            ->whereNotIn('m.code', $allCodes) // 排除掉当前正在编辑的单据本身，避免自冲突
            ->select('m.order_time', 'm.item_id', 'd.employee_id', 'd.start_time_hour', 'd.start_time_min', 'd.end_time_hour', 'd.end_time_min')
            ->get()
            ->groupBy(function($item) {
                return $item->order_time . '_' . $item->item_id . '_' . $item->employee_id;
            });

        $errors = [];
        $update_map = [];
        $excelBillMap = [];
        $timeOverlapMap = []; // 内部记录器：$date_$item_$emp => [ ['s'=>min, 'e'=>min], ... ]

        // 3. 全量循环检查
        foreach ($array as $rowIndex => $row) {
            $displayLine = $rowIndex + 1;

            $ts = $row[$timeIdx];

            $valCode = trim($row[$codeIdx] ?? '');
            $valItemCode = trim($row[$itemIdx] ?? '');
            $itemId = $dbItems[$valItemCode] ?? 0;
            $valEmpCode = trim($row[$empIdx] ?? '');
            $empId = $dbEmps[$valEmpCode] ?? 0;
            $empName = $empDisplayMap[$empId] ?? "工号:{$valEmpCode}";

            // B. 基础档案存在性校验
            if (!$itemId) $errors[] = "第{$displayLine}行：项目编号[{$valItemCode}]不存在";
            if (!$empId) $errors[] = "第{$displayLine}行：人员工号[{$valEmpCode}]不存在";
            if (!$itemId || !$empId) continue;

            // C. 单号与编辑逻辑判定
            if ($valCode !== '') {
                if (isset($dbOrders[$valCode])) {
                    $dbOrder = $dbOrders[$valCode];
                    if ($dbOrder->order_time != $ts) {
                        $errors[] = "第{$displayLine}行：单据[{$valCode}]原日期为[" . date('Y-m-d', $dbOrder->order_time) . "]，不允许跨日期编辑";
                    }
                    $update_map[$rowIndex] = $dbOrder->id;
                } else {
                    $errors[] = "第{$displayLine}行：单据编号[{$valCode}]不存在，若要新增请清空该列";
                }
            }

            // D. 时间合法性校验
            list($sH, $sM) = $this->parseTimeHourMin($row[$startIdx] ?? '');
            list($eH, $eM) = $this->parseTimeHourMin($row[$endIdx] ?? '');

            if ($sH === null || $sM === null || $eH === null || $eM === null) {
                $errors[] = "第{$displayLine}行：时间格式非法或超出00:00-23:59范围";
                continue;
            }

            $currentStart = $sH * 60 + $sM;
            $currentEnd   = $eH * 60 + $eM;

            if ($currentStart >= $currentEnd) {
                $errors[] = "第{$displayLine}行：人员{$empName}的开始时间需早于结束时间";
                continue;
            }

            // --- E. 三位一体冲突校验 ---
            $checkKey = $ts . "_" . $itemId . "_" . $empId;

            // 1. 校验：数据库已有记录 (跨单据)
            if (isset($dbExistingWork[$checkKey])) {
                foreach ($dbExistingWork[$checkKey] as $p) {
                    $exStart = $p->start_time_hour * 60 + $p->start_time_min;
                    $exEnd   = $p->end_time_hour * 60 + $p->end_time_min;
                    // 重叠判定公式: (StartA < EndB) && (StartB < EndA)
                    if ($currentStart < $exEnd && $exStart < $currentEnd) {
                        $errors[] = "第{$displayLine}行：人员{$empName}与已存在该时间重叠的工时单";
                        break;
                    }
                }
            }

            // 2. 校验：Excel内上方行记录 (内部重叠)
            if (isset($timeOverlapMap[$checkKey])) {
                foreach ($timeOverlapMap[$checkKey] as $period) {
                    if ($currentStart < $period['e'] && $period['s'] < $currentEnd) {
                        $errors[] = "第{$displayLine}行：人员{$empName}与表格内其他行时间重叠";
                        break;
                    }
                }
            }

            // 3. 校验通过，记录到内部记录器
            $timeOverlapMap[$checkKey][] = ['s' => $currentStart, 'e' => $currentEnd];
        }

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

    // 设备日工时单 ------------------------------
    public function dailyDwOrderImport($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, $dbDevices, $dbItems) = $this->dailyDwOrderCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $timeIdx = array_search('order_time', $keys);
        $itemIdx = array_search('item_id', $keys);
        $devIdx  = array_search('device_id', $keys); // 对应配置中的 device_id
        $startIdx = array_search('start_time', $keys);
        $endIdx   = array_search('end_time', $keys);

        // 3. 数据聚合分组
        $groups = [];
        foreach ($array as $rowIndex => $row) {
            $cCode = trim($row[$codeIdx] ?? '');
            $cTimeTs = $row[$timeIdx];
            $cItemCode = trim($row[$itemIdx] ?? '');
            $cItemId = $dbItems[$cItemCode] ?? 0;

            $aggKey = ($update_map[$rowIndex] ?? 0) > 0
                ? "UPDATE_" . $update_map[$rowIndex]
                : "NEW_" . $cTimeTs . "_" . $cItemId;

            if (!isset($groups[$aggKey])) {
                $groups[$aggKey] = [
                    'main_id'    => $update_map[$rowIndex] ?? 0,
                    'code'       => $cCode,
                    'order_time' => $cTimeTs,
                    'item_id'    => $cItemId,
                    'details'    => []
                ];
            }

            list($sH, $sM) = $this->parseTimeHourMin($row[$startIdx]);
            list($eH, $eM) = $this->parseTimeHourMin($row[$endIdx]);

            $groups[$aggKey]['details'][] = [
                'device_id'       => $dbDevices[trim($row[$devIdx])] ?? 0, // 改为设备ID
                'start_time_hour' => $sH,
                'start_time_min'  => $sM,
                'end_time_hour'   => $eH,
                'end_time_min'    => $eM,
                'total_work_min'  => ($eH * 60 + $eM) - ($sH * 60 + $sM),
            ];
        }

        // 4. 执行写入
        DB::beginTransaction();
        try {
            $time = time();
            foreach ($groups as $group) {
                $mainId = $group['main_id'];

                if ($mainId > 0) {
                    // 编辑：更新设备工时主表
                    DB::table('daily_dw_order')->where('id', $mainId)->update([
                        'item_id'  => $group['item_id'],
                        'upd_time' => $time
                    ]);
                    DB::table('daily_dw_order_details')->where('main_id', $mainId)
                        ->where('del_time', 0)
                        ->update(['del_time' => $time]);
                } else {
                    // 新增
                    $newCode = $this->generateBillNo([
                        'top_depart_id' => $user['top_depart_id'],
                        'type'          => DailyDwOrder::Order_type,
                        'period'        => date("Ym", $group['order_time'])
                    ]);

                    $mainId = DB::table('daily_dw_order')->insertGetId([
                        'code'          => $newCode,
                        'order_time'    => $group['order_time'],
                        'item_id'       => $group['item_id'],
                        'top_depart_id' => $user['top_depart_id'],
                        'crt_id'        => $user['id'],
                        'crt_time'      => $time,
                    ]);
                }

                $insertDetails = [];
                foreach ($group['details'] as $detail) {
                    $detail['main_id']       = $mainId;
                    $detail['top_depart_id'] = $user['top_depart_id'];
                    $detail['crt_time']      = $time;
                    $detail['crt_id']      = $user['id'];
                    $insertDetails[] = $detail;
                }
                if (!empty($insertDetails)) {
                    DB::table('daily_dw_order_details')->insert($insertDetails);
                }
            }
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return [false, "失败：" . $e->getMessage()];
        }

        return [true, ''];
    }

    private function dailyDwOrderCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $timeIdx = array_search('order_time', $keys);
        $itemIdx = array_search('item_id', $keys);
        $devIdx  = array_search('device_id', $keys); // 设备编码索引
        $startIdx = array_search('start_time', $keys);
        $endIdx   = array_search('end_time', $keys);

        $topDepartId = $user['top_depart_id'];

        // 1. 批量预加载设备档案 (替换原本的人员逻辑)
        $devCodes = array_filter(array_unique(array_column($array, $devIdx)));
        $devModels = Device::where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('code', $devCodes)->get(['id', 'code', 'title']);

        $dbDevices = $devModels->pluck('id', 'code')->toArray(); // 以编号为Key查找ID
        $devDisplayMap = $devModels->mapWithKeys(function($item){
            return [$item->id => "[{$item->code}]{$item->title}"];
        })->toArray();

        $itemCodes = array_filter(array_unique(array_column($array, $itemIdx)));
        $dbItems = Item::where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('code', $itemCodes)->pluck('id', 'code')->toArray();

        // 2. 预加载设备已有单据
        $allCodes = array_filter(array_unique(array_column($array, $codeIdx)));
        $dbOrders = DB::table('daily_dw_order')->where('top_depart_id', $topDepartId)
            ->whereIn('code', $allCodes)->where('del_time', 0)->get()->keyBy('code');

        // 3. 跨单据预查
        $allDates = [];
        foreach ($array as $rowIndex => $row) {
            $valMonthRaw = $row[$timeIdx] ?? '';
            list($status, $ts) = $this->convertExcelCellToDate($valMonthRaw);
            if(! $status) {
                $errors[] = "第" . ($rowIndex + 1) . "行：单据日期格式错误({$valMonthRaw})";
                continue;
            }
            $d = strtotime(date('Y-m-d', $ts));
            $array[$rowIndex][$timeIdx] = $d;
            $allDates[] = $d;
        }
        if (!empty($errors)) return [implode('|', $errors), [], [], []];

        $allDates = array_unique($allDates);

        //归档
        list($status, $msg) = ArchiveService::isArchive($allDates, $user);
        if(! $status)  return [$msg, [], [], []];

        $dbExistingWork = DB::table('daily_dw_order_details as d')
            ->join('daily_dw_order as m', 'd.main_id', '=', 'm.id')
            ->where('m.top_depart_id', $topDepartId)
            ->whereIn('m.order_time', $allDates)
            ->whereIn('m.item_id', array_values($dbItems))
            ->whereIn('d.device_id', array_values($dbDevices)) // 改为 device_id
            ->where('m.del_time', 0)
            ->where('d.del_time', 0)
            ->whereNotIn('m.code', $allCodes)
            ->select('m.order_time', 'm.item_id', 'd.device_id', 'd.start_time_hour', 'd.start_time_min', 'd.end_time_hour', 'd.end_time_min')
            ->get()
            ->groupBy(function($item) {
                return $item->order_time . '_' . $item->item_id . '_' . $item->device_id;
            });

        $errors = [];
        $update_map = [];
        $timeOverlapMap = [];

        // 4. 全量循环检查
        foreach ($array as $rowIndex => $row) {
            $displayLine = $rowIndex + 1;

            $ts = $row[$timeIdx];

            $valCode = trim($row[$codeIdx] ?? '');
            $valItemCode = trim($row[$itemIdx] ?? '');
            $itemId = $dbItems[$valItemCode] ?? 0;
            $valDevCode = trim($row[$devIdx] ?? '');
            $devId = $dbDevices[$valDevCode] ?? 0;
            $devName = $devDisplayMap[$devId] ?? "设备编号:{$valDevCode}";

            if (!$itemId) $errors[] = "第{$displayLine}行：项目编号[{$valItemCode}]不存在";
            if (!$devId) $errors[] = "第{$displayLine}行：设备编码[{$valDevCode}]不存在";
            if (!$itemId || !$devId) continue;

            if ($valCode !== '') {
                if (isset($dbOrders[$valCode])) {
                    $dbOrder = $dbOrders[$valCode];
                    if ($dbOrder->order_time != $ts) {
                        $errors[] = "第{$displayLine}行：单据[{$valCode}]原日期不符，编辑模式不允许修改日期";
                    }
                    $update_map[$rowIndex] = $dbOrder->id;
                } else {
                    $errors[] = "第{$displayLine}行：单据编号[{$valCode}]不存在";
                }
            }

            list($sH, $sM) = $this->parseTimeHourMin($row[$startIdx] ?? '');
            list($eH, $eM) = $this->parseTimeHourMin($row[$endIdx] ?? '');

            if ($sH === null || $sM === null || $eH === null || $eM === null) {
                $errors[] = "第{$displayLine}行：时间段格式非法";
                continue;
            }

            $currentStart = $sH * 60 + $sM;
            $currentEnd   = $eH * 60 + $eM;

            if ($currentStart >= $currentEnd) {
                $errors[] = "第{$displayLine}行：设备{$devName}开始时间需早于结束时间";
                continue;
            }

            $checkKey = $ts . "_" . $itemId . "_" . $devId;

            // 跨单据检查
            if (isset($dbExistingWork[$checkKey])) {
                foreach ($dbExistingWork[$checkKey] as $p) {
                    $exStart = $p->start_time_hour * 60 + $p->start_time_min;
                    $exEnd   = $p->end_time_hour * 60 + $p->end_time_min;
                    if ($currentStart < $exEnd && $exStart < $currentEnd) {
                        $errors[] = "第{$displayLine}行：设备{$devName}在该项目当日已有记录重叠";
                        break;
                    }
                }
            }

            // Excel内部检查
            if (isset($timeOverlapMap[$checkKey])) {
                foreach ($timeOverlapMap[$checkKey] as $period) {
                    if ($currentStart < $period['e'] && $period['s'] < $currentEnd) {
                        $errors[] = "第{$displayLine}行：设备{$devName}与表格内其他行重叠";
                        break;
                    }
                }
            }

            $timeOverlapMap[$checkKey][] = ['s' => $currentStart, 'e' => $currentEnd];
        }

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

    // 人员请假单 --------------------------------
    public function leaveOrderImport($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, $dbEmps) = $this->leaveOrderCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $timeIdx = array_search('order_time', $keys);
        $empIdx  = array_search('employee_id', $keys);
        $startIdx = array_search('start_time', $keys);
        $endIdx   = array_search('end_time', $keys);

        // 3. 数据聚合分组 (按日期聚合)
        $groups = [];
        foreach ($array as $rowIndex => $row) {
            $cTimeTs = $row[$timeIdx];
            $mainId = $update_map[$rowIndex] ?? 0;

            // 聚合Key：如果是更新则按主表ID；如果是新增则按日期
            $aggKey = $mainId > 0 ? "ID_" . $mainId : "DATE_" . $cTimeTs;

            if (!isset($groups[$aggKey])) {
                $groups[$aggKey] = [
                    'main_id'    => $mainId,
                    'order_time' => $cTimeTs,
                    'details'    => []
                ];
            }

            list($sH, $sM) = $this->parseTimeHourMin($row[$startIdx]);
            list($eH, $eM) = $this->parseTimeHourMin($row[$endIdx]);

            $groups[$aggKey]['details'][] = [
                'employee_id'     => $dbEmps[trim($row[$empIdx])] ?? 0,
                'start_time_hour' => $sH,
                'start_time_min'  => $sM,
                'end_time_hour'   => $eH,
                'end_time_min'    => $eM,
                'total_min'       => ($eH * 60 + $eM) - ($sH * 60 + $sM),
                'type'            => PLeaveOverOrder::TYPE_ONE, // 固定请假类型
            ];
        }

        // 4. 执行写入
        DB::beginTransaction();
        try {
            $time = time();
            foreach ($groups as $group) {
                if ($group['main_id'] > 0) {
                    $mainId = $group['main_id'];
                    // 删除旧明细准备覆盖
                    DB::table('p_leave_over_order_details')->where('main_id', $mainId)->update(['del_time' => $time]);
                } else {
                    // 生成请假单号
                    $newCode = $this->generateBillNo([
                        'top_depart_id' => $user['top_depart_id'],
                        'type'          => PLeaveOverOrder::Order_type,
                        'period'        => date("Ym", $group['order_time'])
                    ]);
                    $mainId = DB::table('p_leave_over_order')->insertGetId([
                        'code'          => $newCode,
                        'order_time'    => $group['order_time'],
                        'type'          => PLeaveOverOrder::TYPE_ONE,
                        'top_depart_id' => $user['top_depart_id'],
                        'crt_id'        => $user['id'],
                        'crt_time'      => $time,
                    ]);
                }

                foreach ($group['details'] as &$d) {
                    $d['main_id'] = $mainId;
                    $d['top_depart_id'] = $user['top_depart_id'];
                    $d['crt_time'] = $time;
                }
                DB::table('p_leave_over_order_details')->insert($group['details']);
            }
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return [false, "失败：" . $e->getMessage()];
        }
        return [true, ''];
    }

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

        $topDepartId = $user['top_depart_id'];
        $errors = [];
        $update_map = [];

        // --- 1. 基础档案预加载 (代码省略，保持原样) ---
        $empCodes = array_filter(array_unique(array_column($array, $empIdx)));
        $empModels = Employee::where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('number', $empCodes)->get(['id', 'number', 'title']);
        $dbEmps = $empModels->pluck('id', 'number')->toArray();
        $empDisplayMap = $empModels->mapWithKeys(fn($item) => [$item->id => "[{$item->number}]{$item->title}"])->toArray();

        // --- 2. 日历 & 主表预查 (代码省略，保持原样) ---
        $allDateTs = [];
        foreach ($array as $rowIndex => $row) {
            $valMonthRaw = $row[$timeIdx] ?? '';
            list($status, $ts) = $this->convertExcelCellToDate($valMonthRaw);
            if(! $status) {
                $errors[] = "第" . ($rowIndex + 1) . "行：单据日期格式错误({$valMonthRaw})";
                continue;
            }
            $d = strtotime(date('Y-m-d', $ts));
            $array[$rowIndex][$timeIdx] = $d;
            $allDateTs[] = $d;
        }
        if (!empty($errors)) return [implode('|', $errors), [], []];

        $allDateTs = array_unique($allDateTs);

        //归档
        list($status, $msg) = ArchiveService::isArchive($allDateTs, $user);
        if(! $status)  return [$msg, [], []];

        $calendarMap = DB::table('calendar_details')->where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('time', $allDateTs)->pluck('is_work', 'time')->toArray();

        $existMainOrders = DB::table('p_leave_over_order')
            ->where('top_depart_id', $topDepartId)
            ->whereIn('order_time', $allDateTs)
            ->where('type', PLeaveOverOrder::TYPE_ONE)
            ->where('del_time', 0)->get()->keyBy('order_time');

        // --- 3. 工时设置 (代码省略，保持原样) ---
        $specialWorkMap = DB::table('employee_work_range')->whereIn('employee_id', array_values($dbEmps))
            ->where('top_depart_id', $topDepartId)->get()->groupBy('employee_id');
        $commonWorkRanges = DB::table('work_range_details')->where('del_time', 0)->where('top_depart_id', $topDepartId)->get();
        $commonPeriods = [];
        foreach ($commonWorkRanges as $wr) {
            $commonPeriods[] = ['s' => $wr->start_time_hour * 60 + $wr->start_time_min, 'e' => $wr->end_time_hour * 60 + $wr->end_time_min];
        }

        // --- 4. 库内明细校验数据 (代码省略，保持原样) ---
        $allCodes = array_filter(array_unique(array_column($array, $codeIdx)));
        $dbExistingWork = DB::table('p_leave_over_order_details as d')
            ->join('p_leave_over_order as m', 'd.main_id', '=', 'm.id')
            ->where('m.top_depart_id', $topDepartId)
            ->whereIn('m.order_time', $allDateTs)
            ->where('m.type', PLeaveOverOrder::TYPE_ONE)
            ->where('m.del_time', 0)->where('d.del_time', 0)
            ->whereNotIn('m.code', $allCodes)
            ->select('m.order_time', 'd.employee_id', 'd.start_time_hour', 'd.start_time_min', 'd.end_time_hour', 'd.end_time_min')
            ->get()->groupBy(fn($item) => $item->order_time . '_' . $item->employee_id);

        $dbOrders = DB::table('p_leave_over_order')->where('top_depart_id', $topDepartId)
            ->whereIn('code', $allCodes)->where('del_time', 0)->get()->keyBy('code');

        $internalOverlapMap = [];

        // --- 5. 循环校验 (全量提示版) ---
        foreach ($array as $rowIndex => $row) {
            $line = $rowIndex + 1;
            $rowErrors = []; // 临时收集本行的所有错误

            $ts = $row[$timeIdx];

            // 1. 单据与编辑逻辑检查
            $valCode = trim($row[$codeIdx] ?? '');
            if ($valCode === '') {
                if (isset($existMainOrders[$ts])) {
                    $rowErrors[] = date('Y-m-d', $ts) . "已存在请假单[{$existMainOrders[$ts]->code}]，请填写单号进行编辑";
                }
            } else {
                if (isset($dbOrders[$valCode])) {
                    if ($dbOrders[$valCode]->order_time != $ts) {
                        $rowErrors[] = "单据[{$valCode}]原日期不符，不允许跨日期编辑";
                    }
                    $update_map[$rowIndex] = $dbOrders[$valCode]->id;
                } else {
                    $rowErrors[] = "单据号[{$valCode}]不存在";
                }
            }

            // 2. 日历检查
            if (!isset($calendarMap[$ts]) || (int)$calendarMap[$ts] !== 1) {
                $rowErrors[] = "该日期非工作日，无需请假";
            }

            // 3. 人员检查
            $empId = $dbEmps[trim($row[$empIdx])] ?? 0;
            $empName = $empDisplayMap[$empId] ?? "工号:".$row[$empIdx];
            if (!$empId) {
                $rowErrors[] = "人员不存在";
                // 如果人都不存在，后面的时间校验没意义，报错合并
                $errors[] = "第{$line}行：" . implode('；', $rowErrors);
                continue;
            }

            // 4. 时间段合法性检查
            list($sH, $sM) = $this->parseTimeHourMin($row[$startIdx] ?? '');
            list($eH, $eM) = $this->parseTimeHourMin($row[$endIdx] ?? '');
            $curS = $sH * 60 + $sM; $curE = $eH * 60 + $eM;

            if ($curS >= $curE) {
                $rowErrors[] = "结束时间需晚于开始时间";
            } else {
                // 只有在时间段本身合法的情况下，才检查范围和重叠

                // A. 工作范围检查
                $empPeriods = isset($specialWorkMap[$empId]) ? [] : $commonPeriods;
                if (isset($specialWorkMap[$empId])) {
                    foreach ($specialWorkMap[$empId] as $sw) {
                        $empPeriods[] = ['s' => $sw->start_time_hour * 60 + $sw->start_time_min, 'e' => $sw->end_time_hour * 60 + $sw->end_time_min];
                    }
                }
                $inRange = false;
                foreach ($empPeriods as $p) { if ($curS >= $p['s'] && $curE <= $p['e']) { $inRange = true; break; } }
                if (!$inRange) {
                    $rowErrors[] = "请假需在规定的工作时间内";
                }

                // B. 重叠检查 (不再 continue，两个都查)
                $checkKey = $ts . "_" . $empId;
                // 表内
                if (isset($internalOverlapMap[$checkKey])) {
                    foreach ($internalOverlapMap[$checkKey] as $p) {
                        if ($curS < $p['e'] && $p['s'] < $curE) {
                            $rowErrors[] = "在表内存在时间重叠";
                            break; // 同一行的“表内重叠”只提示一次
                        }
                    }
                }
                // 库内
                if (isset($dbExistingWork[$checkKey])) {
                    foreach ($dbExistingWork[$checkKey] as $p) {
                        $exS = $p->start_time_hour * 60 + $p->start_time_min;
                        $exE = $p->end_time_hour * 60 + $p->end_time_min;
                        if ($curS < $exE && $exS < $curE) {
                            $rowErrors[] = "与已有的请假单时间重叠";
                            break; // 同一行的“库内重叠”只提示一次
                        }
                    }
                }
            }

            // 收集汇总错误
            if (!empty($rowErrors)) {
                $errors[] = "第{$line}行：{$empName}" . implode('，', $rowErrors);
            } else {
                // 只有完全没错误的数据，才放入 internalOverlapMap 供后续行比对
                // 这样可以防止第2行报错了，第3行又提示跟第2行重叠，造成提示混乱
                $checkKey = $ts . "_" . $empId;
                $internalOverlapMap[$checkKey][] = ['s' => $curS, 'e' => $curE];
            }
        }

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

    // 人员加班单
    public function overtimeOrderImport($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, $dbEmps) = $this->overtimeOrderCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $timeIdx = array_search('order_time', $keys);
        $empIdx  = array_search('employee_id', $keys);
        $startIdx = array_search('start_time', $keys);
        $endIdx   = array_search('end_time', $keys);

        // 3. 数据聚合分组 (按日期聚合)
        $groups = [];
        foreach ($array as $rowIndex => $row) {
            $cTimeTs = $row[$timeIdx];
            $mainId = $update_map[$rowIndex] ?? 0;
            $aggKey = $mainId > 0 ? "ID_" . $mainId : "DATE_" . $cTimeTs;

            if (!isset($groups[$aggKey])) {
                $groups[$aggKey] = [
                    'main_id'    => $mainId,
                    'order_time' => $cTimeTs,
                    'details'    => []
                ];
            }

            list($sH, $sM) = $this->parseTimeHourMin($row[$startIdx]);
            list($eH, $eM) = $this->parseTimeHourMin($row[$endIdx]);

            $groups[$aggKey]['details'][] = [
                'employee_id'     => $dbEmps[trim($row[$empIdx])] ?? 0,
                'start_time_hour' => $sH,
                'start_time_min'  => $sM,
                'end_time_hour'   => $eH,
                'end_time_min'    => $eM,
                'total_min'       => ($eH * 60 + $eM) - ($sH * 60 + $sM),
                'type'            => PLeaveOverOrder::TYPE_TWO, // 加班类型
            ];
        }

        // 4. 执行写入
        DB::beginTransaction();
        try {
            $time = time();
            foreach ($groups as $group) {
                if ($group['main_id'] > 0) {
                    $mainId = $group['main_id'];
                    DB::table('p_leave_over_order_details')->where('main_id', $mainId)->update(['del_time' => $time]);
                } else {
                    $newCode = $this->generateBillNo([
                        'top_depart_id' => $user['top_depart_id'],
                        'type'          => PLeaveOverOrder::Order_type,
                        'period'        => date("Ym", $group['order_time'])
                    ]);
                    $mainId = DB::table('p_leave_over_order')->insertGetId([
                        'code'          => $newCode,
                        'order_time'    => $group['order_time'],
                        'type'          => PLeaveOverOrder::TYPE_TWO,
                        'top_depart_id' => $user['top_depart_id'],
                        'crt_id'        => $user['id'],
                        'crt_time'      => $time,
                    ]);
                }

                foreach ($group['details'] as &$d) {
                    $d['main_id'] = $mainId;
                    $d['top_depart_id'] = $user['top_depart_id'];
                    $d['crt_time'] = $time;
                }
                DB::table('p_leave_over_order_details')->insert($group['details']);
            }
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return [false, "失败：" . $e->getMessage()];
        }

        return [true, ''];
    }

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

        $topDepartId = $user['top_depart_id'];
        $errors = [];
        $update_map = [];

        // 1. 档案预加载
        $empCodes = array_filter(array_unique(array_column($array, $empIdx)));
        $empModels = Employee::where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('number', $empCodes)->get(['id', 'number', 'title']);
        $dbEmps = $empModels->pluck('id', 'number')->toArray();
        $empDisplayMap = $empModels->mapWithKeys(fn($item) => [$item->id => "[{$item->number}]{$item->title}"])->toArray();

        // 2. 日期、日历、主表预查
        $allDateTs = [];
        foreach ($array as $rowIndex => $row) {
            $valMonthRaw = $row[$timeIdx] ?? '';
            list($status, $ts) = $this->convertExcelCellToDate($valMonthRaw);
            if(! $status) {
                $errors[] = "第" . ($rowIndex + 1) . "行：单据日期格式错误({$valMonthRaw})";
                continue;
            }
            $d = strtotime(date('Y-m-d', $ts));
            $array[$rowIndex][$timeIdx] = $d;
            $allDateTs[] = $d;
        }

        if (!empty($errors)) return [implode('|', $errors), [], []];

        $allDateTs = array_unique($allDateTs);

        //归档
        list($status, $msg) = ArchiveService::isArchive($allDateTs, $user);
        if(! $status)  return [$msg, [], []];

        $calendarMap = DB::table('calendar_details')->where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('time', $allDateTs)->pluck('is_work', 'time')->toArray();

        $existMainOrders = DB::table('p_leave_over_order')
            ->where('top_depart_id', $topDepartId)
            ->whereIn('order_time', $allDateTs)
            ->where('type', PLeaveOverOrder::TYPE_TWO)
            ->where('del_time', 0)->get()->keyBy('order_time');

        // 3. 工时设置预加载
        $specialWorkMap = DB::table('employee_work_range')->whereIn('employee_id', array_values($dbEmps))
            ->where('top_depart_id', $topDepartId)->get()->groupBy('employee_id');
        $commonWorkRanges = DB::table('work_range_details')->where('del_time', 0)->where('top_depart_id', $topDepartId)->get();
        $commonPeriods = [];
        foreach ($commonWorkRanges as $wr) {
            $commonPeriods[] = ['s' => $wr->start_time_hour * 60 + $wr->start_time_min, 'e' => $wr->end_time_hour * 60 + $wr->end_time_min];
        }

        // 4. 库内明细预查 (跨单重叠校验)
        $allCodes = array_filter(array_unique(array_column($array, $codeIdx)));
        $dbExistingWork = DB::table('p_leave_over_order_details as d')
            ->join('p_leave_over_order as m', 'd.main_id', '=', 'm.id')
            ->where('m.top_depart_id', $topDepartId)
            ->whereIn('m.order_time', $allDateTs)
            ->where('m.type', PLeaveOverOrder::TYPE_TWO)
            ->where('m.del_time', 0)->where('d.del_time', 0)
            ->whereNotIn('m.code', $allCodes)
            ->select('m.order_time', 'd.employee_id', 'd.start_time_hour', 'd.start_time_min', 'd.end_time_hour', 'd.end_time_min')
            ->get()->groupBy(fn($item) => $item->order_time . '_' . $item->employee_id);

        $dbOrders = DB::table('p_leave_over_order')->where('top_depart_id', $topDepartId)
            ->whereIn('code', $allCodes)->where('del_time', 0)->get()->keyBy('code');

        $internalOverlapMap = [];

        // 5. 全量循环检查
        foreach ($array as $rowIndex => $row) {
            $line = $rowIndex + 1;
            $rowErrors = [];

            $ts = $row[$timeIdx];

            // A. 单据存在性逻辑
            $valCode = trim($row[$codeIdx] ?? '');
            if ($valCode === '') {
                if (isset($existMainOrders[$ts])) {
                    $rowErrors[] = date('Y-m-d', $ts) . "已存在加班单[{$existMainOrders[$ts]->code}]，请填写单号进行编辑";
                }
            } else {
                if (isset($dbOrders[$valCode])) {
                    if ($dbOrders[$valCode]->order_time != $ts) $rowErrors[] = "单据[{$valCode}]原日期不符";
                    $update_map[$rowIndex] = $dbOrders[$valCode]->id;
                } else { $rowErrors[] = "单据号[{$valCode}]不存在"; }
            }

            // B. 人员校验
            $empId = $dbEmps[trim($row[$empIdx])] ?? 0;
            $empName = $empDisplayMap[$empId] ?? "工号:".$row[$empIdx];
            if (!$empId) {
                $errors[] = "第{$line}行：人员不存在"; continue;
            }

            // C. 时间格式校验
            list($sH, $sM) = $this->parseTimeHourMin($row[$startIdx] ?? '');
            list($eH, $eM) = $this->parseTimeHourMin($row[$endIdx] ?? '');
            $curS = $sH * 60 + $sM; $curE = $eH * 60 + $eM;

            if ($curS >= $curE) {
                $rowErrors[] = "结束时间需晚于开始时间";
            } else {
                // D. 加班规则校验 (核心逻辑)
                $isWorkDay = (int)($calendarMap[$ts] ?? 0) === 1;

                if ($isWorkDay) {
                    // 如果是工作日，加班不能在工作时间内
                    $empPeriods = isset($specialWorkMap[$empId]) ? [] : $commonPeriods;
                    if (isset($specialWorkMap[$empId])) {
                        foreach ($specialWorkMap[$empId] as $sw) {
                            $empPeriods[] = ['s' => $sw->start_time_hour * 60 + $sw->start_time_min, 'e' => $sw->end_time_hour * 60 + $sw->end_time_min];
                        }
                    }
                    foreach ($empPeriods as $p) {
                        if ($curS < $p['e'] && $p['s'] < $curE) {
                            $rowErrors[] = "加班时间不能与正常工作时间重叠";
                            break;
                        }
                    }
                }

                // E. 重叠校验
                $checkKey = $ts . "_" . $empId;
                // 表内重叠
                if (isset($internalOverlapMap[$checkKey])) {
                    foreach ($internalOverlapMap[$checkKey] as $p) {
                        if ($curS < $p['e'] && $p['s'] < $curE) { $rowErrors[] = "在表内存在时间重叠"; break; }
                    }
                }
                // 库内重叠
                if (isset($dbExistingWork[$checkKey])) {
                    foreach ($dbExistingWork[$checkKey] as $p) {
                        $exS = $p->start_time_hour * 60 + $p->start_time_min; $exE = $p->end_time_hour * 60 + $p->end_time_min;
                        if ($curS < $exE && $exS < $curE) { $rowErrors[] = "与已有的加班单时间重叠"; break; }
                    }
                }
            }

            if (!empty($rowErrors)) {
                $errors[] = "第{$line}行：{$empName} " . implode('，', $rowErrors);
            } else {
                $internalOverlapMap[$ts . "_" . $empId][] = ['s' => $curS, 'e' => $curE];
            }
        }

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

    // 费用项目单月度导入 ------------------------------
    public function feeOrderImport($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. 业务详细校验 (获取 update_map 和 基础档案映射)
        // 此时 claim_date 会在 check 方法内部被转换为时间戳并校验月份归属
        list($error, $update_map, $maps) = $this->feeOrderCheck($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);

        // --- 步骤 1: 数据聚合分组 ---
        $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'];
                    // 跳过仅用于展示的名称字段
                    if (in_array($fieldKey, ['employee_title', 'item_title', 'fee_title'])) continue;

                    $fieldVal = $row[$k];

                    // 转换编号为 ID
                    if ($fieldKey == 'employee_id') $fieldVal = $maps['emps'][$fieldVal] ?? 0;
                    if ($fieldKey == 'item_id') $fieldVal = $maps['items'][$fieldVal] ?? 0;
                    if ($fieldKey == 'fee_id') $fieldVal = $maps['fees'][$fieldVal] ?? 0;

                    // 转换枚举值（委托方式、是否定位到人）
//                    if ($fieldKey == 'entrust_type') {
//                        $fieldVal = array_search($fieldVal, \App\Model\ExpenseClaimsDetails::State_Type) ?: 0;
//                    }
//                    if ($fieldKey == 'expense_type') {
//                        $fieldVal = array_search($fieldVal, \App\Model\ExpenseClaimsDetails::State_Type_2) ?: 0;
//                    }

                    // 注意：claim_date 已经在 check 方法里转成了时间戳，这里直接赋值即可
                    $detailTmp[$fieldKey] = $fieldVal;
                }
            }
            $groups[$aggKey]['details'][] = $detailTmp;
        }

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

                if ($mainId > 0) {
                    // 编辑：逻辑删除旧明细
                    DB::table('expense_claims_details')->where('del_time', 0)
                        ->where('expense_claims_id', $mainId)
                        ->update(['del_time' => $time]);

                    // 更新主表更新时间
                    DB::table('expense_claims')->where('id', $mainId)->update(['upd_time' => $time]);
                } else {
                    // 新增：生成单号
                    $newCode = $this->generateBillNo([
                        'top_depart_id' => $user['top_depart_id'],
                        'type'          => \App\Model\ExpenseClaims::Order_type,
                        'period'        => date("Ym", $group['month'])
                    ]);

                    $mainId = DB::table('expense_claims')->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
                    ]);
                }

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

                if (!empty($insertDetails)) {
                    DB::table('expense_claims_details')->insert($insertDetails);
                }
            }
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return [false, "写入失败：" . $e->getMessage() . " (行号:" . $e->getLine() . ")"];
        }

        return [true, ''];
    }

    private function feeOrderCheck(&$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);
        $itemIdx = array_search('item_id', $keys);
        $feeIdx = array_search('fee_id', $keys);
        $claimDateIdx = array_search('claim_date', $keys);
        $amountIdx = array_search('amount', $keys);
        $eAmountIdx = array_search('entrust1_amount', $keys);
        $e2AmountIdx = array_search('entrust2_amount', $keys);
        $eTypeIdx = array_search('entrust_type', $keys);
        $exTypeIdx = array_search('expense_type', $keys);

        $state_map = array_flip(ExpenseClaimsDetails::State_Type);
        $ex_map = array_flip(ExpenseClaimsDetails::State_Type_2);

        $topDepartId = $user['top_depart_id'];
        $errors = [];

        $allEmpNos = []; $allItemCodes = []; $allFeeCodes = []; $allMonthsTs = []; $allCodes = [];

        // --- 1. 预处理：日期转换与基础编号提取 ---
        foreach ($array as $rowIndex => $row) {
            $line = $rowIndex + 1;

            // A. 报销主月份处理
            $valMonthRaw = trim($row[$monthIdx] ?? '');
            list($mStatus, $valMonthTs) = $this->convertExcelCellToDate($valMonthRaw);
            if (!$mStatus) {
                $errors[] = "第{$line}行：月份格式错误";
                continue;
            }
            $valMonthTs = strtotime(date('Y-m-01', $valMonthTs));
            $array[$rowIndex][$monthIdx] = $valMonthTs; // 回写转换后的时间戳
            $allMonthsTs[] = $valMonthTs;

            // B. 费用产生日期校验 (claim_date)
            $valClaimDateRaw = trim($row[$claimDateIdx] ?? '');
            if (empty($valClaimDateRaw)) {
                $errors[] = "第{$line}行：费用产生日期不能为空";
            } else {
                list($dStatus, $dTs) = $this->convertExcelCellToDate($valClaimDateRaw);
                if (!$dStatus) {
                    $errors[] = "第{$line}行：费用产生日期格式错误";
                } else {
                    // 核心逻辑：判断产生日期是否在主月份内
                    $monthStr = date('Y-m', $valMonthTs);
                    $claimMonthStr = date('Y-m', $dTs);
                    if ($monthStr !== $claimMonthStr) {
                        $errors[] = "第{$line}行：产生日期[{$claimMonthStr}]与报销月份[{$monthStr}]不符";
                    }
                    $array[$rowIndex][$claimDateIdx] = $dTs; // 回写转换后的时间戳
                }
            }

            // C. 提取其他编号
            if (!empty($row[$empIdx]))  $allEmpNos[] = trim($row[$empIdx]);
            if (!empty($row[$itemIdx])) $allItemCodes[] = trim($row[$itemIdx]);
            if (!empty($row[$feeIdx]))  $allFeeCodes[] = trim($row[$feeIdx]);
            if (!empty($row[$codeIdx])) $allCodes[] = trim($row[$codeIdx]);
        }

        if (!empty($errors)) return [implode('|', $errors), [], []];

        $allMonthsTs = array_unique($allMonthsTs);

        //归档
        list($status, $msg) = ArchiveService::isArchive($allMonthsTs, $user);
        if(! $status)  return [$msg, [], []];

        // --- 2. 批量预加载档案映射 ---
        $dbEmps = DB::table('employee')->where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('number', array_unique($allEmpNos))->pluck('id', 'number')->toArray();

        $dbItems = DB::table('item')->where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('code', array_unique($allItemCodes))->pluck('id', 'code')->toArray();

        $dbFees = DB::table('fee')->where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('code', array_unique($allFeeCodes))->pluck('id', 'code')->toArray();

        $existingMonthsMap = DB::table('expense_claims')->where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('month', $allMonthsTs)->pluck('code', 'month')->toArray();

        $dbOrdersByCode = DB::table('expense_claims')->where('del_time', 0)->where('top_depart_id', $topDepartId)
            ->whereIn('code', array_unique($allCodes))->get()->keyBy('code');

        // --- 3. 逐行校验档案存在性与单据逻辑 ---
        $update_map = [];
        foreach ($array as $rowIndex => $row) {
            $line = $rowIndex + 1;
            $valCode = trim($row[$codeIdx] ?? '');
            $valMonthTs = $row[$monthIdx];

            if (!empty($row[$empIdx]) && !isset($dbEmps[trim($row[$empIdx])])) {
                $errors[] = "第{$line}行：工号[{$row[$empIdx]}]不存在";
            }
            if (!isset($dbItems[trim($row[$itemIdx])])) {
                $errors[] = "第{$line}行：项目编号[{$row[$itemIdx]}]不存在";
            }
            if (!isset($dbFees[trim($row[$feeIdx])])) {
                $errors[] = "第{$line}行：费用类型编号[{$row[$feeIdx]}]不存在";
            }

            if ($valCode !== '') {
                if (isset($dbOrdersByCode[$valCode])) {
                    if ($dbOrdersByCode[$valCode]->month != $valMonthTs) {
                        $errors[] = "第{$line}行：单据[{$valCode}]对应月份为" . date('Y-m', $dbOrdersByCode[$valCode]->month) . "，Excel中月份不符";
                    }
                    $update_map[$rowIndex] = $dbOrdersByCode[$valCode]->id;
                } else {
                    $errors[] = "第{$line}行：填写的单据编码[{$valCode}]系统中不存在";
                }
            } else {
                if (isset($existingMonthsMap[$valMonthTs])) {
                    $errors[] = "第{$line}行：月份[" . date('Y-m', $valMonthTs) . "]已存在单据[{$existingMonthsMap[$valMonthTs]}]，请填写该单号进行编辑";
                }
            }

            // 数字格式校验
            $amount = $row[$amountIdx];
            $res = $this->checkNumber($amount, 2, 'non-negative');
            if (!$res['valid']) $errors[] = "第{$line}行费用金额：" . $res['error'];

            // 委托方式
            $e_text = $row[$eTypeIdx] ?? '';
            if (!isset($state_map[$e_text])) {
                $errors[] = "第{$line}行：委托方式[{$e_text}]无效";
            } else {
                $v_tmp = $state_map[$e_text];
                $array[$rowIndex][$eTypeIdx] = $v_tmp;
            }

            $e1_text = $row[$eAmountIdx];
            $res = $this->checkNumber($e1_text, 2, 'non-negative');
            if (!$res['valid']) {
                $errors[] = "第{$line}行：境内委托金额" . $res['error'];
            }
            $e2_text = $row[$e2AmountIdx];
            $res = $this->checkNumber($e2_text, 2, 'non-negative');
            if (!$res['valid']) {
                $errors[] = "第{$line}行：境外委托金额" . $res['error'];
            }

            $tmp = bcadd($e1_text, $e2_text,2);
            if(floatval($tmp) > $amount) return [false, "第{$line}行委托金额总和不能超过费用金额"];

            //是否定位到人
            $ex_text = $row[$exTypeIdx] ?? '';
            if (!isset($ex_map[$ex_text])) {
                $errors[] = "第{$line}行：是否定位到人[{$ex_text}]无效";
            } else {
                $v_tmp = $ex_map[$ex_text];
                $array[$rowIndex][$exTypeIdx] = $v_tmp;
            }
        }

        $maps = ['emps' => $dbEmps, 'items' => $dbItems, 'fees' => $dbFees];
        return [!empty($errors) ? implode('|', $errors) : "", $update_map, $maps];
    }

    // 标签管理
    public function tagImport($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) = $this->tagCheck($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_time'] = $time;
                $insert[] = $main_tmp;
            }
        }

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

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

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

    private function tagCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $sortIdx = array_search('sort', $keys);
        $typeIdx = array_search('type', $keys);
        $isUseIdx = array_search('is_use', $keys);

        $type_map = array_flip(Tag::TYPE_TITLE);
        $isUse_map = array_flip(Tag::IS_USE);

        // 获取当前企业下已存在的标签，用于判断是新增还是更新
        $dbMap = Tag::where('del_time', 0)
            ->where('top_depart_id', $user['top_depart_id'])
            ->get()
            ->keyBy('code')
            ->toArray();

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

        foreach ($array as $rowIndex => $value) {
            $displayLine = $rowIndex + 1;
            $valCode = trim($value[$codeIdx] ?? '');

            if ($valCode === '') {
                $errors[] = "第{$displayLine}行：编码不能为空";
                continue;
            }

            // 校验 Excel 内部是否重复
            if (isset($excelCodes[$valCode])) {
                $errors[] = "第{$displayLine}行：编码[{$valCode}]在文件中重复出现";
                continue;
            }
            $excelCodes[$valCode] = true;

            // 类型
            $e_text = $value[$typeIdx] ?? '';
            if (!isset($type_map[$e_text])) {
                $errors[] = "第{$displayLine}行：类型[{$e_text}]无效";
            } else {
                $array[$rowIndex][$typeIdx] = $type_map[$e_text];
            }

            // 是否启用
            $e_text = $value[$isUseIdx] ?? '';
            if (!isset($isUse_map[$e_text])) {
                $errors[] = "第{$displayLine}行：是否启用[{$e_text}]无效";
            } else {
                $array[$rowIndex][$isUseIdx] = $isUse_map[$e_text];
            }

            // 数字格式校验
            $sort = $value[$sortIdx];
            if (filter_var($sort, FILTER_VALIDATE_INT) === false) return [false, "第{$displayLine}行排序字段排序字段必须是整数且不能含有小数点"];

            // 如果数据库已存在，记录为更新操作
            if (isset($dbMap[$valCode])) {
                $update[$rowIndex] = $dbMap[$valCode]['id'];
            }
        }

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

    // 团队 -----------------------------------
    private function getTeamList($array, $user, $index){
        $codes = array_unique(array_filter(array_column($array,$index)));

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

    public function teamImport($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->teamCheck($array, $user, $table_config);
        if (!empty($error)) return [0, $error];

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

        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);

        // 3. 数据分拣（聚合平铺数据）
        foreach ($array as $key => $value) {
            $itemCode = trim($value[$codeIdx]);
            $main_tmp = [];
            foreach ($value as $k => $val) {
                if (!empty($table_config[$k]['is_main'])) {

                    if ($table_config[$k]['key'] == 'employee_title') continue;

                    $main_tmp[$table_config[$k]['key']] = $val;
                }
            }

            if (isset($update_map[$key])) {
                // 更新逻辑：以 ID 为键去重
                $itemId = $update_map[$key];
                $update_main_ids[$itemId] = $itemId;
                $update_data[$itemId] = array_merge($main_tmp, ['id' => $itemId]);

                if (isset($detail_data_map[$key])) {
                    foreach ($detail_data_map[$key] as $d) {
                        $all_detail_insert[] = array_merge($d, [
                            'team_id' => $itemId,
                            'crt_time' => $time,
                            'top_depart_id' => $user['top_depart_id']
                        ]);
                    }
                }
            } else {
                // 新增逻辑：以项目编码为键去重
                if (!isset($insert_data[$itemCode])) {
                    $main_tmp['top_depart_id'] = $user['top_depart_id'];
                    $main_tmp['crt_id'] = $user['id'];
                    $main_tmp['crt_time'] = $time;
                    $insert_data[$itemCode] = $main_tmp;
                }

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

        DB::beginTransaction();
        try {
            // 4. 执行新增主表
            $new_item_maps = [];
            if (!empty($insert_data)) {
                foreach (array_chunk(array_values($insert_data), 500) as $chunk) {
                    Team::insert($chunk);
                }
                $new_item_maps = Team::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 ($update_data as $id => $uItem) {
                    unset($uItem['id']);
                    Team::where('id', $id)->update($uItem);
                }
            }

            // 6. 处理明细表 (先全删后插)
            if (!empty($update_main_ids)) {
                TeamDetails::whereIn('team_id', array_values($update_main_ids))
                    ->where('del_time', 0)
                    ->update(['del_time' => $time]);
            }

            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) {
                    TeamDetails::insert($chunk);
                }
            }

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

        return [true, ''];
    }

    private function teamCheck(&$array, $user, $table_config)
    {
        $keys = array_column($table_config, 'key');
        $codeIdx = array_search('code', $keys);
        $stateIdx = array_search('state', $keys);
        $code2Idx = array_search('code_2', $keys);  // 工号列
        $empIdx = array_search('charge_id', $keys);

        // 假设 $array 是你的数据集
        $allEmpNumbers = array_values(array_filter(array_unique([
            array_column($array, $empIdx),
            array_column($array, $code2Idx)
        ])));

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

        $code_map = $this->getTeamList($array, $user, $codeIdx);
        $man_map = $this->getFlatDataList2($array, $user, $code2Idx);

        $errors = [];
        $update_mapping = [];
        $detail_storage = [];
        $mainDataConsistency = []; // 一致性校验

        $state_type_map = array_flip(Team::State_Type);

        // 识别主表字段列索引
        $mainColIndices = [];
        foreach ($table_config as $index => $conf) {
            if (!empty($conf['is_main'])) $mainColIndices[$index] = $conf['value'];
        }

        foreach ($array as $rowIndex => $rowValue) {
            $displayLine = $rowIndex + 1;
            $valCode = trim($rowValue[$codeIdx] ?? '');
            $valEmp = trim($rowValue[$empIdx] ?? '');
            if ($valCode === '') continue;

            // --- A. 主表一致性校验 ---
            if (!isset($mainDataConsistency[$valCode])) {
                $mainDataConsistency[$valCode] = array_intersect_key($rowValue, $mainColIndices);
            } else {
                foreach ($mainColIndices as $idx => $label) {
                    if (trim($rowValue[$idx] ?? '') != trim($mainDataConsistency[$valCode][$idx] ?? '')) {
                        $errors[] = "第{$displayLine}行：团队[{$valCode}]的主表信息[{$label}]与前文不一致";
                    }
                }
            }

            // --- B. 基础校验 ---
            if (isset($code_map[$valCode])) {
                $update_mapping[$rowIndex] = $code_map[$valCode];
            }

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

            // D. 人员校验
            if(! empty($valEmp)){
                if (!isset($dbEmps[$valEmp])) {
                    $errors[] = "第{$displayLine}行：负责人工号[{$valEmp}]不存在";
                } else {
                    $array[$rowIndex][$empIdx] = $dbEmps[$valEmp];
                }
            }

            $subCode  = trim($rowValue[$code2Idx] ?? '');

            if ($subCode !== '') {
                if (!isset($man_map[$subCode])) {
                    $errors[] = "第{$displayLine}行：人员工号[{$subCode}]不存在";
                } else {
                    $detail_storage[$rowIndex][] = ['data_id' => $man_map[$subCode]];
                }
            }
        }

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

    private function getFlatDataList2($array, $user, $code2Idx)
    {
        $codes = array_unique(array_filter(array_column($array, $code2Idx)));
        return Employee::where('del_time', 0)
            ->where('top_depart_id', $user['top_depart_id'])
            ->whereIn('number', $codes)
            ->pluck('id', 'number')
            ->toArray();
    }

    /**
     * 解析并校验时间
     */
    private function parseTimeHourMin($val)
    {
        $h = -1; $m = -1;
        if (strpos($val, ':') !== false) {
            $parts = explode(':', $val);
            $h = isset($parts[0]) ? (int)$parts[0] : -1;
            $m = isset($parts[1]) ? (int)$parts[1] : -1;
        } elseif (is_numeric($val)) {
            $totalMinutes = (int)round($val * 1440);
            $h = (int)floor($totalMinutes / 60);
            $m = (int)($totalMinutes % 60);
        }

        if ($h < 0 || $h > 23 || $m < 0 || $m > 59) {
            return [null, null];
        }
        return [$h, $m];
    }

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

        foreach ($array as $line => $row) {
            // 如果整行都是空的（包括 null），直接剔除
            if (empty(array_filter($row))) {
                unset($array[$line]);
                continue;
            }

            // --- 核心修正点：遍历配置，而不是遍历原始行数据 ---
            // 这样即便 Excel 有 100 列，我们也只校验配置里的那 4 列
            $newRow = [];
            foreach ($table_config as $colIndex => $config) {
                $value = isset($row[$colIndex]) ? trim($row[$colIndex]) : '';
                $fieldName = $config['value'];

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

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

                // 3. 唯一性校验
                if (!empty($config['unique']) && $value !== '') {
                    if (isset($uniqueCheck[$colIndex][$value])) {
                        $prevLine = $uniqueCheck[$colIndex][$value];
                        $error[] = "第{$line}行：[{$fieldName}] 与第{$prevLine}行重复，重复值：{$value}";
                    } else {
                        $uniqueCheck[$colIndex][$value] = $line;
                    }
                }

                $newRow[$colIndex] = $value;
            }

            // 只保留校验过的数据列，多余的列（第4列之后）被自动丢弃
            $array[$line] = $newRow;
        }

        $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)];
    }
}