using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Reflection;
using Tiobon.Core.Common.Helper;

namespace Tiobon.Core.Common.Extensions;

public static class IQueryableExtensions
{
    static int EXPORT_FILE_PAGESIZE = 10000;
    /// <summary>
    /// IQueryable数据写入文件(Excel)
    /// </summary>
    /// <param name="db"></param>
    /// <param name="fname"></param>
    /// <param name="splitstr"></param>
    /// <param name="ExportFields"></param>
    /// <param name="exportFieldsWidth"></param>
    /// <param name="headText"></param>
    /// <param name="totalText"></param>
    /// <param name="isNeedItemNo"></param>
    public static void IntoFileFromLinqExcel<T>(this IQueryable<T> db, string fname, string splitstr, List<string> exportFields, Dictionary<string, string> exportDicFields = null, List<int> exportFieldsWidth = null, string headText = "", string totalText = "", bool isNeedItemNo = false) where T : class
    {
        if (db.IsNull())
            return;
        //查询文件状态
        if (File.Exists(fname))
        {
            LoggerHelper.SendLog("文件已生成 " + fname);
            return;
        }
        LoggerHelper.SendLog("正在生成文件 " + fname);
        DateTime dt_start = DateTime.Now;
        if (!File.Exists(fname))
            File.Create(fname).Close();
        //获取需要导出的字段
        Dictionary<string, string> fieldDescs = GetFieldDesc<T>();

        fieldDescs = exportDicFields;
        int dbCount = db.Count();
        ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.Commercial;
        //列名排序，返回有序列表
        var (fields, colunms) = Sort(fieldDescs, exportFields);
        using (FileStream stream = File.Create(fname))
        {
            using (ExcelPackage package = new ExcelPackage(stream))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");


                var colunmsCount = colunms.Count;

                var startCol = 0;
                var startRow = 1;   // 初始行数
                if (isNeedItemNo)
                    startCol = 1;


                if (!string.IsNullOrEmpty(headText))
                {
                    worksheet.Cells[startRow, 1].Value = headText;
                    worksheet.Cells[startRow, 1, 1, colunmsCount + startCol].Merge = true;
                    worksheet.Cells[startRow, 1, 1, colunmsCount + startCol].Style.Font.Bold = true;
                    worksheet.Cells[startRow, 1, 1, colunmsCount + startCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Alignment is center
                    worksheet.Cells[startRow, 1, 1, colunmsCount + startCol].Style.Font.Size = 14;
                    startRow++;
                }

                if (isNeedItemNo)
                {
                    worksheet.Cells[startRow, 1].Value = "序号";
                    worksheet.Cells[startRow, 1, startRow, 1].Style.Font.Bold = true;
                    worksheet.Cells[startRow, 1, startRow, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    worksheet.Cells[startRow, 1, startRow, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                }

                //写入列名
                for (int i = 0; i < fields.Count; ++i)
                {
                    worksheet.Cells[startRow, i + 1 + startCol].Value = fields[i];
                    worksheet.Cells[startRow, i + 1 + startCol, startRow, i + 1 + startCol].Style.Font.Bold = true;
                    worksheet.Cells[startRow, i + 1 + startCol, startRow, i + 1 + startCol].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    worksheet.Cells[startRow, i + 1 + startCol, startRow, i + 1 + startCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                }

                startRow++;

                //查看数据是否过大，需要分页
                int totalPageNum = (dbCount + EXPORT_FILE_PAGESIZE - 1) / EXPORT_FILE_PAGESIZE;
                //遍历每一页
                for (int pageNum = 0; pageNum < totalPageNum; ++pageNum)
                {
                    var list = db.Skip(pageNum * EXPORT_FILE_PAGESIZE).Take(EXPORT_FILE_PAGESIZE).ToList();
                    //遍历每行
                    for (int row = 0; row < list.Count; ++row)
                    {
                        if (isNeedItemNo)
                        {
                            worksheet.Cells[row + startRow, 1].Value = row + 1;
                            worksheet.Cells[row + startRow, 1, row + startRow, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                            worksheet.Cells[row + startRow, 1, row + startRow, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        }
                        //遍历需要写入文件的字段
                        for (int i = 0; i < colunms.Count; ++i)
                        {
                            worksheet.Cells[row + startRow, i + 1 + startCol].Value = ConvertData2String(list[row], colunms[i]);
                            worksheet.Cells[row + startRow, i + 1 + startCol, row + startRow, i + 1 + startCol].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                            worksheet.Cells[row + startRow, i + 1 + startCol, row + startRow, i + 1 + startCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        }
                    }
                }

                if (!string.IsNullOrEmpty(totalText))
                {
                    worksheet.Cells[dbCount + startRow, 1].Value = "总计:";
                    worksheet.Cells[dbCount + startRow, 1, dbCount + startRow, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    worksheet.Cells[dbCount + startRow, 1, dbCount + startRow, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    worksheet.Cells[dbCount + startRow, 2, dbCount + startRow, colunms.Count + startCol].Value = totalText;
                    worksheet.Cells[dbCount + startRow, 2, dbCount + startRow, colunms.Count + startCol].Merge = true;
                    startRow++;
                }

                using (ExcelRange r = worksheet.Cells[1, 1, dbCount + startRow - 1, fields.Count + startCol])
                {
                    r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

                    r.Style.Border.Top.Color.SetColor(Color.Black);
                    r.Style.Border.Left.Color.SetColor(Color.Black);
                    r.Style.Border.Right.Color.SetColor(Color.Black);
                    r.Style.Border.Bottom.Color.SetColor(Color.Black);
                }
                if (isNeedItemNo)
                {
                    worksheet.Column(1).Width = 20;//设置列宽为默认值
                }
                //设置列宽
                if (exportFieldsWidth == null || exportFieldsWidth.Count < fields.Count)
                {
                    for (int i = 0; i < fields.Count; ++i)
                    {
                        worksheet.Column(i + 1 + startCol).Width = 20;//设置列宽为默认值
                    }
                }
                else
                {
                    for (int i = 0; i < fields.Count; ++i)
                    {
                        worksheet.Column(i + 1 + startCol).Width = exportFieldsWidth[i];//设置列宽为前段配置的值
                    }
                }

                package.Workbook.Properties.Title = "数据导出";
                package.Workbook.Properties.Author = "SUZHOUEU";
                package.Workbook.Properties.Comments = "苏州一优信息技术有限公司提供技术支持！";

                package.Workbook.Properties.Company = "苏州一优信息技术有限公司";

                package.Workbook.Properties.SetCustomPropertyValue("Checked by", "SimonHsiao");
                package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "SUZHOUEU");

                package.Save();

            }
        }
        LoggerHelper.SendLog("生成文件 " + fname + " 完毕 " + dbCount + " 耗时 " + (int)(DateTime.Now - dt_start).TotalSeconds);
    }
    public static void IntoFileFromLinqExcel(this DataTable db, string fname, string splitstr, Dictionary<string, string> fieldDescs, List<int> exportFieldsWidth, string headText = "", string totalText = "", bool isNeedItemNo = false)
    {
        if (db.IsNull())
            return;
        //查询文件状态
        if (File.Exists(fname))
        {
            LoggerHelper.SendLog("文件已生成 " + fname);
            return;
        }
        LoggerHelper.SendLog("正在生成文件 " + fname);
        DateTime dt_start = DateTime.Now;
        if (!File.Exists(fname))
            File.Create(fname).Close();
        //获取需要导出的字段
        int dbCount = db.Rows.Count;

        //列名排序，返回有序列表
        var (fields, colunms) = Sort(fieldDescs, null);
        using (FileStream stream = File.Create(fname))
        {
            using (ExcelPackage package = new ExcelPackage(stream))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");


                var colunmsCount = colunms.Count;

                var startCol = 0;
                var startRow = 1;   // 初始行数
                if (isNeedItemNo)
                    startCol = 1;


                if (!string.IsNullOrEmpty(headText))
                {
                    worksheet.Cells[startRow, 1].Value = headText;
                    worksheet.Cells[startRow, 1, 1, colunmsCount + startCol].Merge = true;
                    worksheet.Cells[startRow, 1, 1, colunmsCount + startCol].Style.Font.Bold = true;
                    worksheet.Cells[startRow, 1, 1, colunmsCount + startCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Alignment is center
                    worksheet.Cells[startRow, 1, 1, colunmsCount + startCol].Style.Font.Size = 14;
                    startRow++;
                }

                if (isNeedItemNo)
                {
                    worksheet.Cells[startRow, 1].Value = "序号";
                    worksheet.Cells[startRow, 1, startRow, 1].Style.Font.Bold = true;
                    worksheet.Cells[startRow, 1, startRow, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    worksheet.Cells[startRow, 1, startRow, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                }

                //写入列名
                for (int i = 0; i < fields.Count; ++i)
                {
                    worksheet.Cells[startRow, i + 1 + startCol].Value = fields[i];
                    worksheet.Cells[startRow, i + 1 + startCol, startRow, i + 1 + startCol].Style.Font.Bold = true;
                    worksheet.Cells[startRow, i + 1 + startCol, startRow, i + 1 + startCol].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    worksheet.Cells[startRow, i + 1 + startCol, startRow, i + 1 + startCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                }
                startRow++;

                for (int row = 0; row < dbCount; ++row)
                {
                    if (isNeedItemNo)
                    {
                        worksheet.Cells[row + startRow, 1].Value = row + 1;
                        worksheet.Cells[row + startRow, 1, row + startRow, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                        worksheet.Cells[row + startRow, 1, row + startRow, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    }
                    //遍历需要写入文件的字段
                    for (int i = 0; i < colunms.Count; ++i)
                    {
                        worksheet.Cells[row + startRow, i + 1 + startCol].Value = db.Rows[row][colunms[i]].ToString();
                        worksheet.Cells[row + startRow, i + 1 + startCol, row + startRow, i + 1 + startCol].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                        worksheet.Cells[row + startRow, i + 1 + startCol, row + startRow, i + 1 + startCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    }
                }

                if (!string.IsNullOrEmpty(totalText))
                {
                    worksheet.Cells[dbCount + startRow, 1].Value = "总计:";
                    worksheet.Cells[dbCount + startRow, 1, dbCount + startRow, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    worksheet.Cells[dbCount + startRow, 1, dbCount + startRow, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    worksheet.Cells[dbCount + startRow, 2, dbCount + startRow, colunms.Count + startCol].Value = totalText;
                    worksheet.Cells[dbCount + startRow, 2, dbCount + startRow, colunms.Count + startCol].Merge = true;
                    startRow++;
                }

                using (ExcelRange r = worksheet.Cells[1, 1, dbCount + startRow - 1, fields.Count + startCol])
                {
                    r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

                    r.Style.Border.Top.Color.SetColor(Color.Black);
                    r.Style.Border.Left.Color.SetColor(Color.Black);
                    r.Style.Border.Right.Color.SetColor(Color.Black);
                    r.Style.Border.Bottom.Color.SetColor(Color.Black);
                }
                if (isNeedItemNo)
                {
                    worksheet.Column(1).Width = 20;//设置列宽为默认值
                }
                //设置列宽
                if (exportFieldsWidth == null || exportFieldsWidth.Count < fields.Count)
                {
                    for (int i = 0; i < fields.Count; ++i)
                    {
                        worksheet.Column(i + 1 + startCol).Width = 20;//设置列宽为默认值
                    }
                }
                else
                {
                    for (int i = 0; i < fields.Count; ++i)
                    {
                        worksheet.Column(i + 1 + startCol).Width = exportFieldsWidth[i];//设置列宽为前段配置的值
                    }
                }

                package.Workbook.Properties.Title = "数据导出";
                package.Workbook.Properties.Author = "SUZHOUEU";
                package.Workbook.Properties.Comments = "苏州一优信息技术有限公司提供技术支持！";

                package.Workbook.Properties.Company = "苏州一优信息技术有限公司";

                package.Workbook.Properties.SetCustomPropertyValue("Checked by", "SimonHsiao");
                package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "SUZHOUEU");

                package.Save();

            }
        }
        LoggerHelper.SendLog("生成文件 " + fname + " 完毕 " + dbCount + " 耗时 " + (int)(DateTime.Now - dt_start).TotalSeconds);
    }
    private static string ConvertData2String<T>(T t, string field) where T : class
    {
        if (t.FieldTypeIsDateTime(field))
        {
            DateTime? time = t.GetDateTimeValueFromField(field);
            if (time != null)
            {
                if (time.Value.ToString("HH:mm:ss") == "00:00:00")
                {
                    return time.Value.ToString("yyyy-MM-dd");
                }
                else
                {
                    return time.Value.ToString("yyyy-MM-dd HH:mm:ss");
                }
            }
            else
            {
                return string.Empty;
            }
        }
        else
        {
            return t.GetStringValueFromField(field);
        }
    }
    /// <summary>
    /// 列名按照前端显示顺序导出
    /// </summary>
    /// <param name="columns"></param>
    /// <param name="ExportFields"></param>
    /// <returns></returns>
    public static (List<string>, List<string>) Sort(Dictionary<string, string> columns, List<string> ExportFields)
    {
        List<string> fields = new List<string>();
        List<string> colunms = new List<string>();
        if (ExportFields == null || ExportFields.Count == 0)
        {
            return (columns.Keys.ToList(), columns.Values.ToList());
        }

        foreach (var field in ExportFields)
        {
            foreach (var item in columns)
            {
                if (item.Key == field)
                {
                    fields.Add(item.Key);
                    colunms.Add(item.Value);
                }
            }
        }

        return (fields, colunms);
    }


    #region 获取字段描述
    /// <summary>
    /// 对象字段描述
    /// </summary>
    private static Dictionary<string, Dictionary<string, string>> m_FieldDesc = new Dictionary<string, Dictionary<string, string>>();

    /// <summary>
    /// 获取字段的描述(描述 - 列名)
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public static Dictionary<string, string> GetFieldDesc<T>()
    {
        var type = typeof(T).ToString();
        lock (m_FieldDesc)
        {
            if (m_FieldDesc.ContainsKey(type))
                return m_FieldDesc[type];
        }
        Dictionary<string, string> dic = new Dictionary<string, string>();
        try
        {
            PropertyInfo[] peroperties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            if (peroperties != null)
            {
                foreach (PropertyInfo property in peroperties)
                {
                    object[] objs = property.GetCustomAttributes(typeof(DescriptionAttribute), true);
                    if (objs.Length > 0)
                    {
                        var desc = ((DescriptionAttribute)objs[0]).Description.Trim();
                        if (!dic.ContainsKey(desc))
                        {
                            dic.Add(desc, property.Name);
                        }
                        else
                        {
                            dic[desc] = property.Name;
                        }
                    }
                }
            }
        }
        catch //(Exception ex)
        {
        }
        lock (m_FieldDesc)
        {
            if (!m_FieldDesc.ContainsKey(type))
                m_FieldDesc.Add(type, dic);
        }
        return dic;
    }
    #endregion

}