MES / IOT · Files

using Hh.Mes.Pojo.System;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.WebEntity.equipment;
using HH.Data.Excel.ColumnMapConfig;
using NPOI.HPSF;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HH.Data.Excel
{
    public partial class MainEventHandlers
    {
        /// <summary>
        /// 上传文件
        /// </summary>
        /// <param name="fileLen">最少选择的文件个数</param>
        /// <param name="msg">消息提示</param>
        /// <param name="multiselect">是否多选</param>
        /// <param name="isJudgeFileName">是否需要指定判断默认上传的ip地址表文件名</param>
        /// <returns></returns>
        private OpenFileDialog? OpenFileDialogExcel(int fileLen, string msg, bool multiselect = false, bool isJudgeFileName = true)
        {
            using OpenFileDialog openFileDialog = new();
            openFileDialog.Filter = "Excel Files|*.xls;*.xlsx";
            openFileDialog.Multiselect = multiselect;
            if (openFileDialog.ShowDialog() != DialogResult.OK) return null;
            //是否需要指定判断默认上传的ip地址表文件名
            if (isJudgeFileName)
            {
                var hasIpFile = openFileDialog.FileNames.Any(x => x.IndexOf(_main._sysIpFileName) > -1);
                if ((!hasIpFile))
                {
                    Program.SysMsgShow(msg);
                    return null;
                }
            }
            //多选 
            if (multiselect && openFileDialog.FileNames.Length < fileLen)
            {
                Program.SysMsgShow(msg + "-当前文件个数:" + openFileDialog.FileNames.Length);
                return null;
            }
            return openFileDialog;
        }

        /// <summary>
        /// 输送线 上传文件后 前置判断 相关业务逻辑判断 
        /// </summary>
        /// <returns></returns>
        private bool CheckRequiredFilesCC()
        {
            //_sysIpFileName：IP地址表.xlsx 是否为空并且 表格有数据内容
            var ipDataDt = _sysSource[_main._sysIpFileName].FirstOrDefault().Value.dtData;
            if (ipDataDt == null || ipDataDt.Rows.Count == 0)
            {
                Program.SysMsgShow($"【{_main._sysIpFileName}】文件不存数据！");
                return false;
            }

            // 统一检查列是否存在 顺序不能改
            string[] reqCols = { "类型", "IP地址", "文件名称", "设备名称" };
            foreach (string column in reqCols)
            {
                if (!ipDataDt.Columns.Contains(column))
                {
                    Program.SysMsgShow($"【{_main._sysIpFileName}】文件不存列【{column}】！");
                    return false;
                }
            }

            // 从 IP 地址表中提取文件名称
            var seenValuesExcelName = new HashSet<string>();
            var seenValuesEqName = new HashSet<string>();
            var seenValuesIp = new HashSet<string>();
            for (int i = 0; i < ipDataDt.Rows.Count; i++)
            {
                //类型
                var typesCol = reqCols[0];
                string cellNameTypeValue = ipDataDt.Rows[i][typesCol]?.ToString() ?? string.Empty;
                if (string.IsNullOrEmpty(cellNameTypeValue))
                {
                    Program.SysMsgShow($"【{_main._sysIpFileName}】文件,第【{i + 2}】行，列【{typesCol}】的内容行数据为空！");
                    return false;
                }
                //类型列的值,必须都是 PLC系统报警 
                if (cellNameTypeValue.Trim() != ccTypeName)
                {
                    Program.SysMsgShow($"【{_main._sysIpFileName}】文件,第【{i + 2}】行，列的值{typesCol}【{cellNameTypeValue}】不等于{ccTypeName}");
                    return false;
                }

                // IP地址
                string currentIpValue = ipDataDt.Rows[i][reqCols[1]]?.ToString() ?? string.Empty;
                if (string.IsNullOrEmpty(currentIpValue))
                {
                    Program.SysMsgShow($"【{_main._sysIpFileName}】文件,第【{i + 2}】行,列 [{reqCols[1]}]值为空!");
                    return false;
                }
                if (seenValuesIp.Contains(currentIpValue))
                {
                    Program.SysMsgShow($"【{_main._sysIpFileName}】文件,第【{i + 2}】行,列 [{reqCols[1]}]发现重复值: {currentIpValue}");
                    return false;
                }
                seenValuesIp.Add(currentIpValue);

                //文件名称
                string fileNameValue = ipDataDt.Rows[i][reqCols[2]]?.ToString() ?? string.Empty;
                if (string.IsNullOrEmpty(fileNameValue))
                {
                    Program.SysMsgShow($"【{_main._sysIpFileName}】文件,第【{i + 2}】行,列 [{reqCols[2]}]值为空!");
                    return false;
                }
                if (seenValuesExcelName.Contains(fileNameValue))
                {
                    Program.SysMsgShow($"【{_main._sysIpFileName}】文件,第【{i + 2}】行,列 [{reqCols[2]}]发现重复值: {fileNameValue}");
                    return false;
                }
                seenValuesExcelName.Add(fileNameValue);

                //设备名称
                string currentEqNameValue = ipDataDt.Rows[i][reqCols[3]]?.ToString() ?? string.Empty;
                if (string.IsNullOrEmpty(currentEqNameValue))
                {
                    Program.SysMsgShow($"【{_main._sysIpFileName}】文件,第【{i + 2}】行,列 [{reqCols[3]}] 值为空!");
                    return false;
                }
                if (seenValuesEqName.Contains(currentEqNameValue))
                {
                    Program.SysMsgShow($"【{_main._sysIpFileName}】文件,第【{i + 2}】行,列 [{reqCols[3]}] 发现重复值: {currentEqNameValue}");
                    return false;
                }
                seenValuesEqName.Add(currentEqNameValue);
            }

            //ip地址表文件里面的文件名称数据是否都包含在上传附件中
            // 获取 _sysSource 中排除特定文件名后的集合
            var filteredSysSourceKeys = _sysSource.Keys.Where(key => key != _main._sysIpFileName).ToHashSet(); // 转换为 HashSet 提高查询效率
            // 检查两个集合是否完全一致
            if (!filteredSysSourceKeys.SetEquals(seenValuesExcelName))
            {
                // 找出缺失的文件（在 seenValuesExcelName 但不在 _sysSource 中）
                var missingInSysSource = seenValuesExcelName.Except(filteredSysSourceKeys).ToList();
                if (missingInSysSource.Any())
                {
                    Program.SysMsgShow($"上传的 Excel 附件中不存在以下文件：【{string.Join("、", missingInSysSource)}】");
                    return false;
                }

                // 找出多余的文件（在 _sysSource 但不在 seenValuesExcelName 中）
                var extraInSysSource = filteredSysSourceKeys.Except(seenValuesExcelName).ToList();
                if (extraInSysSource.Any())
                {
                    Program.SysMsgShow($"上传的 Excel 附件中不存在以下文件：【{string.Join("、", extraInSysSource)}】");
                    return false;
                }
            }

            //sheets 页签的名称是否都包含 防止上传的附件页签名称不对
            foreach (var item in _sysSource.Keys)
            {
                if (item == _main._sysIpFileName)
                {
                    if (_sysSource[item].Count != 1)
                    {
                        Program.SysMsgShow($"Excel附件{_main._sysIpFileName}的页签只能允许有一个");
                        return false;
                    }
                    continue;
                }
                var allSheet = _sysSource[item];
                if (allSheet.Count != 4)
                {
                    Program.SysMsgShow($"Excel附件{_main._sysIpFileName}的页签数量必须是4个");
                    return false;
                }
                //页签名称是否匹配
                foreach (var sheet in allSheet)
                {
                    var currentValue = sheet.Key;
                    bool isContained = excelAllSheets.Any(s => currentValue.Contains(s));
                    if (!isContained)
                    {
                        Program.SysMsgShow($"Excel附件【{_main._sysIpFileName}】当前页签的值【{currentValue}】需要包含在【{string.Join(",", excelAllSheets)}】页签范围内！");
                        return false;
                    }
                    //数据是否为空
                    if (sheet.Value.dtData == null || sheet.Value.dtData.Rows.Count == 0)
                    {
                        Program.SysMsgShow($"Excel附件【{_main._sysIpFileName}】当前页签的值【{currentValue}】的数据是空!");
                        return false;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 获取CheckedListBox的值 只支持可以和value数据源绑定方式
        /// 默认返回key
        /// </summary>
        /// <param name="checkedListBox"></param>
        /// <param name="returnValue">默认返回key，true是value</param>
        /// <param name="returnBoth">同时返回key和value</param>
        public List<string> GetCheckedItems(CheckedListBox checkedListBox, bool returnValue = false, bool returnBoth = false)
        {
            var selectedItems = new List<string>();

            foreach (var item in checkedListBox.CheckedItems)
            {
                // 假设每个项是一个 KeyValuePair
                var kvp = (KeyValuePair<int, string>)item;

                if (returnBoth)
                {
                    selectedItems.Add($"{kvp.Key}: {kvp.Value}"); // 返回键和值
                }
                else if (returnValue)
                {
                    selectedItems.Add(kvp.Value); // 返回值
                }
                else
                {
                    selectedItems.Add(kvp.Key.ToString()); // 默认返回键
                }
            }
            return selectedItems;
        }

        /// <summary>
        /// dt的某一列值 是否都在selectValues中
        /// </summary>
        public Tuple<int, string, string> AllValuesExist(DataTable dataTable, string columnName, List<string> selectValues)
        {
            // 1. 判断列是否存在 [2](@ref)
            if (!dataTable.Columns.Contains(columnName))
                return new Tuple<int, string, string>(-1, "", "");

            // 2. 提取列值的唯一集合（处理空值并转为字符串）
            var columnValues = new HashSet<string>(
                dataTable.AsEnumerable()
                         .Select(row => row[columnName]?.ToString() ?? "")
                         .Where(s => !string.IsNullOrEmpty(s)), // 可选：是否需要过滤空字符串
                StringComparer.OrdinalIgnoreCase // 控制大小写敏感性
            );
            var columnValuesStr = string.Join(",", columnValues);
            var selectValuesStr = string.Join(",", selectValues);

            var filteredSysSourceKeys = selectValues.ToHashSet(); // 转换为 HashSet 提高查询效率
            // 检查两个集合是否完全一致
            if (!filteredSysSourceKeys.SetEquals(columnValues))
            {
                // 找出缺失的文件（在 columnValues 但不在 filteredSysSourceKeys 中）
                var missingInSysSource = columnValues.Except(filteredSysSourceKeys).ToList();
                if (missingInSysSource.Any())
                {
                    return new Tuple<int, string, string>(0, columnValuesStr, selectValuesStr);
                }

                // 找出多余的文件（在 filteredSysSourceKeys 但不在 columnValues 中）
                var extraInSysSource = filteredSysSourceKeys.Except(columnValues).ToList();
                if (extraInSysSource.Any())
                if (extraInSysSource.Any())
                {
                    return new Tuple<int, string, string>(0, columnValuesStr, selectValuesStr);
                }
            }

            return new Tuple<int, string, string>(1, "", ""); ;
        }

        #region Conveyor_Fault_ALRM、System_Fault_ALRM
        /// <summary>
        /// 设备堆垛机设备属性  dt自动填充到实体 
        /// </summary>
        public IEnumerable<daq_equipment> ProcessDataDafault(DataTable dt, Dictionary<string, string> ColumnMapIpExcel, string fileName)
        {
            // 新增验证步骤：检查所有映射列是否存在于DataTable
            var missingColumns = ColumnMapIpExcel.Keys.Where(excelCol => !dt.Columns.Contains(excelCol)).ToList();

            if (missingColumns.Any())
            {
                throw new ArgumentException(
                    $"Excel附件【{fileName}】缺少映射配置中的列: {string.Join(", ", missingColumns)}\n" +
                    $"Excel附件【{fileName}】缺少映射配置中的列: {string.Join(", ", missingColumns)}\n" +
                    $"目前现有的列: {string.Join(", ", dt.Columns.Cast<DataColumn>().Select(c => c.ColumnName))}"
                );
            }

            foreach (DataRow row in dt.Rows)
            {
                var daqEq = new daq_equipment();

                // 步骤1：遍历映射规则
                foreach (var mapping in ColumnMapIpExcel)
                {
                    var (excelCol, dbField) = (mapping.Key, mapping.Value);

                    // 步骤2：获取原始值
                    var rawValue = row[excelCol];

                    // 步骤3：应用转换规则
                    if (ColumnMapping.ValueConverters.TryGetValue(dbField, out var converter))
                    {
                        try
                        {
                            var rowValue = converter(rawValue);
                            daqEq.GetType().GetProperty(dbField)?.SetValue(daqEq, rowValue);
                        }
                        catch (Exception ex)
                        {
                            var msg = ex.Message + $"\n---Excel文件列[{excelCol}]转换失败: {excelCol}={rawValue} -> {dbField}\n" +
                                                  $"---Excel文件行号: {dt.Rows.IndexOf(row) + 1}";
                            throw new ArgumentException(msg);
                        }
                    }
                    // 步骤4：无转换规则时直接赋值
                    else
                    {
                        try
                        {
                            daqEq.GetType().GetProperty(dbField)?.SetValue(daqEq, rawValue);
                        }
                        catch (Exception ex)
                        {
                            var msg = ex.Message + $"\n---Excel文件列[{excelCol}]转换失败: {excelCol}={rawValue} -> {dbField}\n" +
                                                   $"---Excel文件行号: {dt.Rows.IndexOf(row) + 1}";
                            throw new ArgumentException(msg);
                        }
                    }
                }
                yield return daqEq;
            }
        }


        /// <summary>
        /// 设备CC 属性 dt自动填充到实体 
        /// </summary>
        public IEnumerable<daq_equipment_prop> ProcessDataDafaultCC(DataTable dt, Dictionary<string, string> columnMap, string fileName)
        {
            // 新增验证步骤：检查所有映射列是否存在于DataTable
            var missingColumns = columnMap.Keys.Where(excelCol => !dt.Columns.Contains(excelCol) && excelCol != "Created" && excelCol != "CreatedBy").ToList();

            if (missingColumns.Any())
            {
                throw new ArgumentException(
                    $"Excel附件【{fileName}】缺少映射配置中的列: {string.Join(", ", missingColumns)}\n" +
                    $"目前现有的列: {string.Join(", ", dt.Columns.Cast<DataColumn>().Select(c => c.ColumnName))}"
                );
            }

            foreach (DataRow row in dt.Rows)
            {
                var daqEq = new daq_equipment_prop();

                // 步骤1：遍历映射规则
                foreach (var mapping in columnMap)
                {
                    if (mapping.Key == "CreatedBy")
                    {
                        daqEq.GetType().GetProperty("CreatedBy")?.SetValue(daqEq, SystemVariable.DefaultCreatedExcel);
                        continue;
                    }
                    else if (mapping.Key == "Created")
                    {
                        daqEq.GetType().GetProperty("Created")?.SetValue(daqEq, DateTime.Now);
                        continue;
                    }
                    var (excelCol, dbField) = (mapping.Key, mapping.Value);

                    // 步骤2：获取原始值
                    var rawValue = row[excelCol];

                    // 步骤3：应用转换规则
                    if (ColumnMapping.ValueConverters.TryGetValue(dbField, out var converter))
                    {
                        try
                        {
                            var rowValue = converter(rawValue);
                            daqEq.GetType().GetProperty(dbField)?.SetValue(daqEq, rowValue);
                        }
                        catch (Exception ex)
                        {
                            var msg = ex.Message + $"\n---Excel文件列[{excelCol}]转换失败: {excelCol}={rawValue} -> {dbField}\n" +
                                                  $"---Excel文件行号: {dt.Rows.IndexOf(row) + 1}";
                            throw new ArgumentException(msg);
                        }
                    }
                    // 步骤4：无转换规则时直接赋值
                    else
                    {
                        try
                        {
                            daqEq.GetType().GetProperty(dbField)?.SetValue(daqEq, rawValue);
                        }
                        catch (Exception ex)
                        {
                            var msg = ex.Message + $"\n---Excel文件列[{excelCol}]转换失败: {excelCol}={rawValue} -> {dbField}\n" +
                                                   $"---Excel文件行号: {dt.Rows.IndexOf(row) + 1}";
                            throw new ArgumentException(msg);
                        }
                    }
                }
                yield return daqEq;
            }
        }

        /// <summary>
        /// 输送线 Fault_ALRM、系统报警 页签 数据源转换业务
        /// </summary>
        public DataTable TransformDataTableCC(DataTable originalTable, string fileName)
        {
            string varNameCell = "变量名称";
            if (!originalTable.Columns.Contains(varNameCell)) throw new ArgumentException($"【{fileName}】文件不存列【{varNameCell}】！");
            const string dataTypeCell = "数据类型";
            if (!originalTable.Columns.Contains(dataTypeCell)) throw new ArgumentException($"【{fileName}】文件不存列【{dataTypeCell}】！");
            const string dbBlockCell = "DB块编号";
            if (!originalTable.Columns.Contains(dbBlockCell)) throw new ArgumentException($"【{fileName}】文件不存列【{dbBlockCell}】！");
            const string offsetCell = "偏移量";
            if (!originalTable.Columns.Contains(offsetCell)) throw new ArgumentException($"【{fileName}】文件不存列【{offsetCell}】！");

            const string commentCell = "注释";
            if (!originalTable.Columns.Contains(commentCell)) throw new ArgumentException($"【{fileName}】文件不存列【{commentCell}】！");

            DataTable transformedTable = new DataTable();
            transformedTable.Columns.Add(varNameCell, typeof(string));
            transformedTable.Columns.Add(dataTypeCell, typeof(string));
            transformedTable.Columns.Add(dbBlockCell, typeof(string));
            transformedTable.Columns.Add(commentCell, typeof(string));
            transformedTable.Columns.Add("Name", typeof(string));
            transformedTable.Columns.Add("PropType", typeof(string));
            transformedTable.Columns.Add("MonitorCompareValue", typeof(string));


            string lastVarName = string.Empty;
            var propType = "PLCMonitorAddress";
            for (int i = 0; i < originalTable.Rows.Count; i++)
            {
                DataRow row = originalTable.Rows[i];

                string varName = row[varNameCell]?.ToString() ?? string.Empty;
                string dataType = row[dataTypeCell]?.ToString() ?? string.Empty;
                string db = row[dbBlockCell]?.ToString() ?? string.Empty;
                string offset = row[dbBlockCell]?.ToString() ?? string.Empty;
                string comment = row[commentCell]?.ToString() ?? string.Empty;
                // 更新lastVarName，如果注释为空
                if (string.IsNullOrWhiteSpace(comment))
                {
                    lastVarName = varName; // 更新lastVarName
                    continue; // 跳过此行
                }
                else
                {
                    // 合并变量名称
                    varName = lastVarName + "_" + varName;
                }
                string dbOffset = db + (row["偏移量"].ToString() != "" ? "." + row["偏移量"].ToString() : "");

                // 添加转换后的行
                transformedTable.Rows.Add(varName, dataType.ToUpper(), dbOffset, comment, comment, propType, "'False");
            }

            // 检查是否没有找到有效数据
            if (string.IsNullOrWhiteSpace(lastVarName))
            {
                throw new InvalidOperationException($"Excel附件【{fileName}】数据源存在错误：所有行的注释列均为空。");
            }
            return transformedTable;
        }
        #endregion

        #region 输送线WCS_Display和输送线状态地址
        /// <summary>
        /// 输送线WCS_Display 和输送线状态地址 数据校验
        /// </summary>
        private void ValidateSourcesCC(DataTable source1, DataTable source2, string fileName)
        {
            // 获取source1中的所有基准代码
            var source1Codes = source1.AsEnumerable()
                                      .Select(row => row.Field<string>("Code").Replace("_StationError", ""))
                                      .Distinct().ToList();

            // 获取source2中所有有效状态字段
            var source2Codes = source2.AsEnumerable()
                                      .Select(row => row.Field<string>("状态字段"))
                                      .Where(code => !string.IsNullOrWhiteSpace(code))
                                      .Distinct().ToList();

            // 验证一一对应关系
            //foreach (var code in source1Codes)
            //{
            //    if (!source2Codes.Contains(code))
            //    {
            //        throw new ArgumentException($"页签:WCS_Display的列状态字段编码值【{code}】不存在于页签:输送线状态地址数据中，请仔细检查数据,文件{fileName}");
            //    }
            //}

            // 验证双向包含关系
            if (!source1Codes.All(s2 => source2Codes.Contains(s2))
                //|| !source2Codes.All(s1 => source1Codes.Contains(s1))
                )
            {
                // 获取差异项
                var missingInSource2 = source1Codes.Except(source2Codes);
                var redundantInSource2 = source2Codes.Except(source1Codes);

                throw new ArgumentException($"Excel附件【{fileName}】页签【WCS_Display,输送线状态地址】的列【状态字段】编码值数据不一致：\n" +
                    $"页签【输送线状态地址】缺失项：{string.Join(",", missingInSource2)}\n" +
                    $"页签【输送线状态地址】冗余项：{string.Join(",", redundantInSource2)}");
            }
        }

        /// <summary>
        /// 输送线状态地址 数据追加到输送线WCS_Display  合并数据源
        /// </summary>
        private void AddSourceCCLineData(DataTable result, DataTable source2, string fileName)
        {
            foreach (DataRow row in source2.Rows)
            {
                if (string.IsNullOrWhiteSpace(row["状态字段"]?.ToString())) continue;

                // 构建新行数据
                DataRow newRow = result.NewRow();
                newRow["Code"] = $"{row["状态字段"]}_StationMonitorAutomation";
                newRow["Name"] = "站台监控-自动状态";
                newRow["Address"] = $"{row["DB块编号"]}.{row["偏移量"]}";
                newRow["PropType"] = "PLCDataAddress";
                newRow["DataType"] = "INT";

                result.Rows.Add(newRow);
            }
        }

        /// <summary>
        /// 输送线状态地址 数据追加到输送线WCS_Display  合并数据源
        /// </summary>
        /// <param name="source1">输送线WCS_Display</param>
        /// <param name="source2">输送线状态地址</param>
        /// <returns></returns>
        public DataTable MergeDataDisplayAndLineCC(DataTable source1, DataTable source2, string fileName)
        {
            // 验证数据完整性
            ValidateSourcesCC(source1, source2, fileName);

            // 创建结果表（直接克隆source1结构）
            DataTable result = source1.Clone();

            // 添加source1原有数据
            foreach (DataRow row in source1.Rows)
            {
                result.ImportRow(row);
            }

            // 添加source2新数据
            AddSourceCCLineData(result, source2, fileName);

            return result;
        }

        /// <summary>
        /// 输送线 WCS_Display 数据源转换业务
        /// </summary>
        public DataTable TransformDataTableConvertDisplayCC(DataTable source, string fileName)
        {
            DataTable result = new DataTable();
            result.Columns.AddRange(new[] {
                new DataColumn("Code", typeof(string)),
                new DataColumn("Name", typeof(string)),
                new DataColumn("Address", typeof(string)),
                new DataColumn("PropType", typeof(string)),
                new DataColumn("DataType", typeof(string))
            });

            const string statusCell = "状态字段";
            if (!source.Columns.Contains(statusCell)) throw new ArgumentException($"【{fileName}】文件不存列【{statusCell}】！");
            const string dataTypeCell = "数据类型";
            if (!source.Columns.Contains(dataTypeCell)) throw new ArgumentException($"【{fileName}】文件不存列【{dataTypeCell}】！");
            const string dbBlockCell = "DB块编号";
            if (!source.Columns.Contains(dbBlockCell)) throw new ArgumentException($"【{fileName}】文件不存列【{dbBlockCell}】！");
            const string offsetCell = "偏移量";
            if (!source.Columns.Contains(offsetCell)) throw new ArgumentException($"【{fileName}】文件不存列【{offsetCell}】！");

            string currentPCode = null;
            string currentDB = null;
            var pendingIsErr = new Dictionary<string, (string db, string offset)>();

            foreach (DataRow row in source.Rows)
            {
                string statusField = row[statusCell].ToString()?.ToString() ?? string.Empty; 
                string dataType = row[dataTypeCell].ToString()?.ToString() ?? string.Empty;
                string dbBlock = row[dbBlockCell].ToString()?.ToString() ?? string.Empty; 
                string offset = row[offsetCell].ToString()?.ToString() ?? string.Empty; 

                // 检测新的 P 编号区块
                if (dataType.IndexOf("Display") > -1)
                {
                    currentPCode = statusField;
                    currentDB = dbBlock;
                    pendingIsErr[currentPCode] = (dbBlock, null);
                    continue;
                }

                // 在区块内查找 IsErr
                if (currentPCode != null && statusField == "IsErr")
                {
                    pendingIsErr[currentPCode] = (currentDB, offset);
                    currentPCode = null; // 重置当前区块
                }
            }

            // 生成结果
            foreach (var kvp in pendingIsErr)
            {
                if (!string.IsNullOrEmpty(kvp.Value.offset))
                {
                    result.Rows.Add($"{kvp.Key}_StationError", "站台监控-是否报错", $"{kvp.Value.db}.{kvp.Value.offset}", "PLCDataAddress", "INT");
                }
            }
            // 检查是否没有找到有效数据
            if (result.Rows.Count == 0)
            {
                throw new InvalidOperationException($"Excel附件【{fileName}】页签【WCS_Display】数据源转换业务失败结果为空!");
            }
            return result;
        }

        /// <summary>
        /// 输送线 WCS_Display和 输送线状态地址 整合数据源转换业务
        /// </summary>
        public DataTable TransformDataTableConvertLineCC(DataTable source, string fileName)
        {
            DataTable result = new DataTable();
            result.Columns.AddRange(new[] {
                new DataColumn("Code", typeof(string)),
                new DataColumn("Name", typeof(string)),
                new DataColumn("Address", typeof(string)),
                new DataColumn("PropType", typeof(string)),
                new DataColumn("DataType", typeof(string))
            });

            const string statusCell = "状态字段";
            if (!source.Columns.Contains(statusCell)) throw new ArgumentException($"【{fileName}】文件不存列【{statusCell}】！");
            const string dataTypeCell = "数据类型";
            if (!source.Columns.Contains(dataTypeCell)) throw new ArgumentException($"【{fileName}】文件不存列【{dataTypeCell}】！");
            const string dbBlockCell = "DB块编号";
            if (!source.Columns.Contains(dbBlockCell)) throw new ArgumentException($"【{fileName}】文件不存列【{dbBlockCell}】！");
            const string offsetCell = "偏移量";
            if (!source.Columns.Contains(offsetCell)) throw new ArgumentException($"【{fileName}】文件不存列【{offsetCell}】！");

            string currentPCode = null;
            string currentDB = null;
            var pendingIsErr = new Dictionary<string, (string db, string offset)>();

            foreach (DataRow row in source.Rows)
            {
                string statusField = row[statusCell].ToString()?.ToString() ?? string.Empty; ;
                string dataType = row[dataTypeCell].ToString()?.ToString() ?? string.Empty; ;
                string dbBlock = row[dbBlockCell].ToString()?.ToString() ?? string.Empty; ;
                string offset = row[offsetCell].ToString()?.ToString() ?? string.Empty; ;

                // 检测新的 P 编号区块
                if (dataType.IndexOf("Display") > -1)
                {
                    currentPCode = statusField;
                    currentDB = dbBlock;
                    pendingIsErr[currentPCode] = (dbBlock, null);
                    continue;
                }

                // 在区块内查找 IsErr
                if (currentPCode != null && statusField == "IsErr")
                {
                    pendingIsErr[currentPCode] = (currentDB, offset);
                    currentPCode = null; // 重置当前区块
                }
            }

            // 生成结果
            foreach (var kvp in pendingIsErr)
            {
                if (!string.IsNullOrEmpty(kvp.Value.offset))
                {
                    result.Rows.Add($"{kvp.Key}_StationError", "站台监控-是否报错", $"{kvp.Value.db}.{kvp.Value.offset}", "PLCDataAddress", "INT");
                }
            }
            // 检查是否没有找到有效数据
            if (result.Rows.Count == 0)
            {
                throw new InvalidOperationException($"Excel附件【{fileName}】页签【WCS_Display,输送线状态地址】整合数据源转换业务转换失败结果为空!");
            }
            return result;
        }
        #endregion

        /// <summary>
        /// 生成随机数
        /// </summary>
        public string[] GenerateUniqueCode(int count)
        {
            if (count < 1)
                throw new ArgumentException("设备编码 生成数量必须大于0", nameof(count));

            char[] baseChars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz".ToCharArray();
            Random random = new Random();
            string[] codes = new string[count];

            for (int n = 0; n < count; n++)
            {
                // 每次生成独立洗牌的字符池
                char[] shuffledChars = (char[])baseChars.Clone();
                // Fisher-Yates 洗牌算法（打乱前6位）
                for (int i = 0; i < 6; i++)
                {
                    int j = random.Next(i, shuffledChars.Length);
                    (shuffledChars[i], shuffledChars[j]) = (shuffledChars[j], shuffledChars[i]);
                }
                // 取前6位作为随机码
                codes[n] = new string(shuffledChars.Take(6).ToArray());
            }

            return codes;
        }


        /// <summary>
        /// 将目标文件"IP地址表.xlsx"强制置顶排序，其他文件保持原有相对顺序
        /// </summary>
        /// <param name="filePaths">文件路径数组（必须包含目标文件）</param>
        /// <returns>目标文件为首位的新数组</returns>
        public static string[] OrderWithTargetFileFirst(string desFile, string[] filePaths)
        {
            if (filePaths == null)
                throw new ArgumentNullException(nameof(filePaths));

            // 文件名精准匹配逻辑（局部函数）
            bool IsTargetFile(string path) => Path.GetFileName(path).Equals(desFile, StringComparison.OrdinalIgnoreCase);

            // 存在性强制验证（根据需求可注释）
            if (!filePaths.Any(IsTargetFile))
                throw new InvalidOperationException(desFile);

            // 动态排序逻辑
            return filePaths .OrderByDescending(IsTargetFile)  // 目标文件置顶
                             .ThenBy(x => System.Array.IndexOf(filePaths, x)) // 保持其他文件原始顺序
                             .ToArray();
        }
    }
}