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
    {

        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;

            var hasIpFile = openFileDialog.FileNames.Any(x => x.IndexOf(_main._sysIpFileName) > -1);
            if ( (!hasIpFile && isJudgeFileName) || openFileDialog.FileNames.Length < fileLen)
            {
                Program.SysMsgShow(msg);
                return null;
            }
            return openFileDialog;
        }

        /// <summary>
        /// 输送线 上传文件后 前置判断 相关业务逻辑判断 
        /// </summary>
        /// <returns></returns>
        private bool CheckRequiredFilesCC()
        {
            // 从 IP 地址表中提取文件名称
            var excelName = new List<string>();
            var ipDataDt = _sysSource[_main._sysIpFileName].FirstOrDefault().Value.dtData;
            //_sysIpFileName：IP地址表.xlsx 是否为空并且 表格有数据内容
            if (ipDataDt == null || ipDataDt.Rows.Count == 0)
            {
                Program.SysMsgShow($"【{_main._sysIpFileName}】文件不存数据！");
                return false;
            }
            //ipDataDt 是否存在【文件名称】列
            const string cellName = "文件名称";
            if (!ipDataDt.Columns.Contains(cellName))
            {
                Program.SysMsgShow($"【{_main._sysIpFileName}】文件不存列【{cellName}】！");
                return false;
            }
            //ipDataDt 是否存在【类型】列
            const string cellNameType = "类型";
            if (!ipDataDt.Columns.Contains(cellNameType))
            {
                Program.SysMsgShow($"【{_main._sysIpFileName}】文件不存列【{cellNameType}】！");
                return false;
            }

            for (int i = 0; i < ipDataDt.Rows.Count; i++)
            {
                string cellNameRowData = ipDataDt.Rows[i][cellName]?.ToString() ?? string.Empty;
                if (string.IsNullOrEmpty(cellNameRowData))
                {
                    Program.SysMsgShow($"【{_main._sysIpFileName}】文件,第【{i + 2}】行，列【{cellName}】的内容行数据为空！");
                    return false;
                }
                //ip Excel文件类型列的值,必须都是 PLC系统报警 
                string cellNameTypeValue = ipDataDt.Rows[i][cellNameType]?.ToString() ?? string.Empty;
                if (string.IsNullOrEmpty(cellNameRowData) || cellNameTypeValue.Trim() != ccTypeCode)
                {
                    Program.SysMsgShow($"【{_main._sysIpFileName}】文件,第【{i + 2}】行，{cellNameType}列的值【{cellNameTypeValue}】不等于{ccTypeCode}");
                    return false;
                }
                excelName.Add(cellNameRowData);
            }
            //ip地址表文件里面的文件名称数据是否都包含在上传附件中
            foreach (var fileName in excelName)
            {
                if (!_sysSource.Keys.Where(key => key != _main._sysIpFileName).Contains(fileName))
                {
                    Program.SysMsgShow($"上传的Excel附件中不存在【{fileName}】文件！");
                    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, "", "");

            var selectValuesStr = string.Join(",", selectValues);

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

            // 3. 遍历检查每个值是否存在（遇到第一个不存在即终止）
            foreach (var value in selectValues)
            {
                if (!columnValues.Contains(value))
                    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(
                    $"{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(
                    $"{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, dbOffset, comment,comment, propType, "'False");
            }

            // 检查是否没有找到有效数据
            if (string.IsNullOrWhiteSpace(lastVarName))
            {
                throw new InvalidOperationException("数据源存在错误：所有行的注释列均为空。");
            }
            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}");
                }
            }
        }

        /// <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($"输送线页签WCS_Display数据源转换失败结果为空,文件{fileName}");
            }
            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($"输送线页签WCS_Display数据源转换失败结果为空,文件{fileName}");
            }
            return result;
        }
        #endregion
    }
}