using PCHMI;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Net.Mime.MediaTypeNames;

namespace CowainHmi.Alarm
{
    public class AlarmExcelHelper
    {
        public static DataTable ReadReleaseExcel<T>(string fileName, BindingList<T> bindingList)
        {
            OleDbConnection oleDbConnection = null;
            try
            {
                string text = new FILE().GET_RELEASE_DIR() + $"\\DATA\\{fileName}.xls";
                //string text = Environment.CurrentDirectory + $"\\DATA\\{fileName}.xls";
                if (!File.Exists(text))
                {
                    SaveBindingListToExcel(bindingList, fileName);
                }
                if (File.Exists(text))
                {
                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
                    oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
                    try
                    {
                        oleDbConnection.Open();
                        OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", oleDbConnection);
                        dataAdapter.SelectCommand = selectCommand;
                        DataTable dt = new DataTable();
                        dataAdapter.Fill(dt);
                        return dt;
                    }
                    catch (Exception ex)
                    {
                        new PClass().WErrTxt("读取报警表格异常:" + ex.ToString());
                    }
                    finally
                    {
                        oleDbConnection.Close();

                    }
                }
            }
            finally
            {
                oleDbConnection?.Close();
            }
            return null;
        }

        public static DataTable ReadErrExcel(string fileName)
        {
            OleDbConnection oleDbConnection = null;
            try
            {
                string text = System.Windows.Forms.Application.StartupPath + $"\\DATA\\{fileName}.xls";
                if (File.Exists(text))
                {
                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
                    oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
                    try
                    {
                        oleDbConnection.Open();
                        OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", oleDbConnection);
                        dataAdapter.SelectCommand = selectCommand;
                        DataTable dt = new DataTable();
                        dataAdapter.Fill(dt);
                        return dt;
                    }
                    catch (Exception ex)
                    {
                        new PClass().WErrTxt("读取报警表格异常:" + ex.ToString());
                    }
                    finally
                    {
                        oleDbConnection.Close();

                    }
                }
            }
            finally
            {
                oleDbConnection?.Close();
            }
            return null;
        }


        /// <summary>
        /// 更新实时报警表格
        /// </summary>
        public static void UpdateAlarmRT(string pid, string type, string group, string id, string error, bool rw, string fileName)
        {
            OleDbConnection oleDbConnection = null;
            try
            {
                if (PClass.SystemRun)
                {
                    string text = System.Windows.Forms.Application.StartupPath + $"\\UDTA\\{fileName}.xls";
                    if (!File.Exists(text))
                    {
                        new JUDGE_DIR().CreateExcelFile(text, "AlarmLog", new string[]
                            {
                        "PLC",
                        "DTIM",
                        "报警类别",
                        "报警组",
                        "ID",
                        "ErrText",
                        "TP",
                            });
                    }
                    oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
                    oleDbConnection.Open();
                    DataTable dt = GetAlarmRT(fileName);
                    var dr = dt.Select($"报警类别='{type}' and 报警组='{group}' and ID='{id}'").FirstOrDefault();
                    string sql = String.Empty;
                    if (dr == null)
                    {
                        if (rw)
                        {
                            sql =
                   $"insert into [AlarmLog$] (PLC,报警类别,报警组,ID,DTIM,TP,ErrText) values('{pid}','{type}','{group}','{id}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffff")}','1','{error}')";
                        }
                    }
                    else
                    {
                        if (rw)
                        {
                            //更新记录
                            sql =
                        $"UPDATE [AlarmLog$] SET DTIM='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffff")}',TP='1' WHERE 报警类别='{type}' and 报警组='{group}' and ID='{id}' ";

                        }
                        else
                        {
                            //删除记录
                            sql =
                        $"UPDATE [AlarmLog$] SET DTIM='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffff")}',TP='0' WHERE 报警类别='{type}' and 报警组='{group}' and ID='{id}' ";
                        }

                    }
                    if (!string.IsNullOrEmpty(sql))
                    {
                        new OleDbCommand(sql, oleDbConnection).ExecuteNonQuery();
                    }
                }
            }
            finally
            {
                oleDbConnection?.Close();
            }
        }
       
        
        
        
        
        public static DataTable GetAlarmRT(string fileName)
        {
            OleDbConnection oleDbConnection = null;
            try
            {
                string text = System.Windows.Forms.Application.StartupPath + $"\\UDTA\\{fileName}.xls";
                if (File.Exists(text))
                {

                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
                    oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
                    try
                    {
                        oleDbConnection.Open();
                        OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [AlarmLog$]", oleDbConnection);
                        dataAdapter.SelectCommand = selectCommand;
                        DataTable dt = new DataTable();
                        dataAdapter.Fill(dt);
                        return dt;
                    }
                    catch (Exception ex)
                    {
                        new PClass().WErrTxt("读取实时报警记录错误:" + ex.ToString());
                    }
                    finally
                    {
                        oleDbConnection.Close();

                    }

                }
            }
            finally
            {
                oleDbConnection?.Close();
            }
            return null;
        }
        public static void SaveAlarmLog(string pid, string type, string group, string id, string tp, string error)
        {
            OleDbConnection oleDbConnection = null;
            try
            {
                if (PClass.SystemRun)
                {
                    string text = System.Windows.Forms.Application.StartupPath + "\\UDTA\\AlarmDirectory";
                    text = text + "\\" + DateTime.Now.ToLocalTime().ToString("yyMMdd") + ".xls";
                    if (!File.Exists(text))
                    {
                        new JUDGE_DIR().CreateExcelFile(text, "AlarmLog", new string[]
                            {
                        "PLC",
                        "报警类别",
                        "报警组",
                        "ID",
                        "DTIM",
                        "TP",
                        "ErrText"
                            });
                    }
                    oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
                    oleDbConnection.Open();
                    string sql =
                    $"insert into [AlarmLog$] (PLC,报警类别,报警组,ID,DTIM,TP,ErrText) values('{pid}','{type}','{group}','{id}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffff")}','{tp}','{error}')";
                    new OleDbCommand(sql, oleDbConnection).ExecuteNonQuery();
                }
            }
            finally
            {
                oleDbConnection?.Close();
            }
        }

        public static DataTable GetAlarmLog(string fileName)
        {
            OleDbConnection oleDbConnection = null;

            string text = System.Windows.Forms.Application.StartupPath + "\\UDTA\\AlarmDirectory";//Application.StartupPath为文件执行路径
            text = text + "\\" + fileName + ".xls";
            if (File.Exists(text))//判断文件是否存在
            {
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
                oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
                try
                {
                    oleDbConnection.Open();
                    OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [AlarmLog$]", oleDbConnection);
                    dataAdapter.SelectCommand = selectCommand;
                    DataTable dt = new DataTable();
                    dataAdapter.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    new PClass().WErrTxt("读取历史报警记录错误:" + ex.ToString());
                }
                finally
                {
                    oleDbConnection.Close();
                }

            }
            return null;
        }


        public static BindingList<T> GetDataList<T>(DataTable dt)
        {
            if (dt == null)
            {
                return null;
            }
            var list = new BindingList<T>();
            var plist = new List<PropertyInfo>(typeof(T).GetProperties());
            foreach (DataRow item in dt.Rows)
            {
                T s = Activator.CreateInstance<T>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
                    if (info != null)
                    {
                        try
                        {
                            if (!Convert.IsDBNull(item[i]))
                            {
                                object v = null;
                                if (info.PropertyType.ToString().Contains("System.Nullable"))
                                {
                                    v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
                                }
                                else
                                {
                                    v = Convert.ChangeType(item[i], info.PropertyType);
                                }
                                info.SetValue(s, v, null);
                            }
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }
                list.Add(s);
            }
            return list;
        }

        public class EnumItemModel<T>
        {
            public string EnumName { get; set; }
            public int EnumValue { get; set; }
            public string Description { get; set; }
            public T EnumItem { get; set; }
        }

        public static List<EnumItemModel<T>> GetEnumItems<T>() where T : Enum
        {
            // 获取枚举的类型信息  
            Type enumType = typeof(T);

            // 获取枚举的名称数组和值数组  
            string[] enumNames = Enum.GetNames(enumType);
            Array enumValues = Enum.GetValues(enumType);

            // 创建一个列表来存储枚举项模型对象  
            List<EnumItemModel<T>> enumItems = new List<EnumItemModel<T>>();

            // 遍历枚举的名称和值数组  
            for (int i = 0; i < enumNames.Length; i++)
            {
                string name = enumNames[i];
                //int value = 0;
                int value = Convert.ToInt32(enumValues.GetValue(i));
                string description = GetEnumDescription(enumType, name);

                // 创建一个枚举项模型对象，并将其添加到列表中  
                EnumItemModel<T> enumItem = new EnumItemModel<T>
                {
                    EnumName = name,
                    EnumValue = value,
                    Description = description,
                    EnumItem = (T)Enum.Parse(typeof(T), name)
                };
                enumItems.Add(enumItem);
            }

            // 返回枚举项列表  
            return enumItems;
        }
        public static string GetEnumDescription(Type enumType, string enumName)
        {
            // 使用反射获取枚举项的描述属性  
            FieldInfo fieldInfo = enumType.GetField(enumName);
            DescriptionAttribute[] attributes = (DescriptionAttribute[])fieldInfo.GetCustomAttributes(typeof(DescriptionAttribute), false);

            // 如果存在描述属性，则返回描述值，否则返回空字符串  
            return (attributes.Length > 0) ? attributes[0].Description : string.Empty;
        }

        public static BindingList<T> DeepCopyList<T>(BindingList<T> other)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                BinaryFormatter formatter = new BinaryFormatter();
                formatter.Serialize(ms, other);
                ms.Position = 0;
                return (BindingList<T>)formatter.Deserialize(ms);
            }
        }

        public static void SaveBindingListToExcel<T>(BindingList<T> bindingList, string fileName)
        {
            string filePath = new FILE().GET_RELEASE_DIR() + $"\\DATA\\{fileName}.xls";
            //string filePath = Environment.CurrentDirectory + $"\\DATA\\{fileName}.xls";
            File.Delete(filePath);
            // 创建数据表  
            DataTable dataTable = new DataTable(typeof(T).Name);

            // 添加列  
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            foreach (PropertyDescriptor prop in properties)
            {
                dataTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }

            // 添加行  
            foreach (T item in bindingList)
            {
                DataRow row = dataTable.NewRow();
                foreach (PropertyDescriptor prop in properties)
                {
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                }
                dataTable.Rows.Add(row);
            }
            // 使用OleDb写入Excel  
            using (OleDbConnection conn = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + filePath + ";Extended Properties=Excel 8.0;"))
            {
                conn.Open();
                string sheetName = "Sheet1";
                using (OleDbCommand cmd = new OleDbCommand($"CREATE TABLE [{sheetName}] ({string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(c => $"[{c.ColumnName}] {GetOleDbType(c.DataType)}"))})", conn))
                {
                    cmd.ExecuteNonQuery();
                }

                using (OleDbDataAdapter adapter = new OleDbDataAdapter($"SELECT * FROM [{sheetName}]", conn))
                {
                    using (OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter))
                    {
                        adapter.Update(dataTable);
                    }
                }
            }
        }

        private static string GetOleDbType(Type dataType)
        {
            if (dataType == typeof(string))
                return "Text";
            else if (dataType == typeof(int))
                return "Integer";
            else if (dataType == typeof(double))
                return "Double";
            else if (dataType == typeof(DateTime))
                return "DateTime";
            else
                return "Variant";
        }
    }
}