using NPOI.SS.UserModel;
using OfficeOpenXml;
using Performance.DtoModels;
using Performance.EntityModels;
using Performance.Infrastructure;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace Performance.Services.ExtractExcelService
{
    public class ExtractHelper
    {
        public static string GetExtractFile(int hospitalId, per_allot allot, ref string newFilePath, string allotFilePath = "")
        {
            string originalPath = string.IsNullOrEmpty(allotFilePath)
                ? Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Template", "医院绩效模板.xlsx")
                : allotFilePath;
            CloseAutoFilter(originalPath);
            var (tempPath, filePath) = CopyOriginalFile(hospitalId, originalPath, allot);
            newFilePath = filePath;
            return tempPath;
        }

        public static string GetExtractFile(int hospitalId, string prefix = "绩效提取数据")
        {
            var dpath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Files", $"{hospitalId}", "autoextract");
            FileHelper.CreateDirectory(dpath);
            return Path.Combine(dpath, $"{prefix}{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx");
        }

        private static (string TempPath, string FilePath) CopyOriginalFile(int hospitalId, string originalPath, per_allot allot)
        {
            var ext = FileHelper.GetExtension(originalPath);
            var dpath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Files", $"{hospitalId}", "autoextract");
            FileHelper.CreateDirectory(dpath);
            string tempPath = Path.Combine(dpath, $"Template{DateTime.Now.ToString("yyyyMMddHHmmssfff")}{ext}");
            FileHelper.Copy(originalPath, tempPath);
            string filePath = Path.Combine(dpath, $"{allot.Year}{allot.Month.ToString().PadLeft(2, '0')}绩效提取数据{DateTime.Now.ToString("yyyyMMddHHmmssfff")}{ext}");
            return (tempPath, filePath);
        }

        public static void CreateNotExistSheet(List<ex_module> modulesList, IWorkbook workbook)
        {
            SortedDictionary<string, int> pairs = new SortedDictionary<string, int>();
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                var sheetname = workbook.GetSheetAt(i).SheetName;
                if (!pairs.Keys.Contains(sheetname))
                    pairs.Add(workbook.GetSheetAt(i).SheetName, i);
            }

            try
            {

                var sheetNames = pairs.Keys;

                #region 收入、额外收入

                foreach (var module in modulesList.Where(t => new int[] { (int)SheetType.Income, (int)SheetType.OtherIncome }.Contains(t.SheetType.Value))?.OrderBy(t => t.ModuleName))
                {
                    var no = module.ModuleName.GetNo();
                    var name = sheetNames.FirstOrDefault(name => name.StartsWith(no)) ?? module.ModuleName;
                    var sheet = workbook.GetSheet(name) ?? workbook.GetSheet(module.ModuleName);

                    if (sheet == null)
                    {
                        ExcelStyle style = new ExcelStyle(workbook);
                        var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.列头, CellFormat.默认);

                        var newSheet = workbook.CreateSheet(module.ModuleName);
                        newSheet.GetOrCreate(1).GetOrCreate(6).SetCellValue("护理组分割比例：");
                        newSheet.GetOrCreate(1).GetOrCreate(6).CellStyle = cellStyle;
                        newSheet.GetOrCreate(2).GetOrCreate(6).SetCellValue("医生组分割比例：");
                        newSheet.GetOrCreate(2).GetOrCreate(6).CellStyle = cellStyle;
                        newSheet.GetOrCreate(3).GetOrCreate(6).SetCellValue("医技组分割比例：");
                        newSheet.GetOrCreate(3).GetOrCreate(6).CellStyle = cellStyle;
                        var r5 = newSheet.GetOrCreate(4);
                        r5.GetOrCreate(0).SetCellValue("核算收入（医技组）");
                        r5.GetOrCreate(1).SetCellValue("核算收入（医生组）");
                        r5.GetOrCreate(2).SetCellValue("核算收入（护理组）");
                        r5.GetOrCreate(3).SetCellValue("核算单元（医技组）");
                        r5.GetOrCreate(4).SetCellValue("核算单元（医生组）");
                        r5.GetOrCreate(5).SetCellValue("核算单元（护理组）");
                        r5.GetOrCreate(6).SetCellValue("科室名称");
                        r5.GetOrCreate(0).CellStyle = cellStyle;
                        r5.GetOrCreate(1).CellStyle = cellStyle;
                        r5.GetOrCreate(2).CellStyle = cellStyle;
                        r5.GetOrCreate(3).CellStyle = cellStyle;
                        r5.GetOrCreate(4).CellStyle = cellStyle;
                        r5.GetOrCreate(5).CellStyle = cellStyle;
                        r5.GetOrCreate(6).CellStyle = cellStyle;
                        workbook.SetSheetOrder(newSheet.SheetName, workbook.NumberOfSheets - 1);
                    }

                }
                #endregion

                #region 其他工作量、考核

                foreach (var module in modulesList.Where(t => new int[] { (int)SheetType.OtherWorkload, (int)SheetType.Assess }.Contains(t.SheetType.Value))?.OrderBy(t => t.ModuleName))
                {
                    var no = module.ModuleName.GetNo();
                    var name = sheetNames.FirstOrDefault(name => name.StartsWith(no)) ?? module.ModuleName;
                    var sheet = workbook.GetSheet(name) ?? workbook.GetSheet(module.ModuleName);
                    if (sheet == null)
                    {
                        var item = pairs.Where(t => t.Key.ToString().NoBlank().StartsWith("3.")).OrderByDescending(t => t.Key).FirstOrDefault();
                        var copysheet = workbook.GetSheet(item.Key ?? "");
                        if (copysheet == null) break;

                        var newSheet = copysheet.CopySheet(module.ModuleName, true);
                        workbook.SetSheetOrder(newSheet.SheetName, workbook.NumberOfSheets - 1);

                        var point = PerSheetDataFactory.GetDataRead(SheetType.Workload)?.Point;
                        ClearSheetTemplate(newSheet, point, (SheetType)module.SheetType);
                    }
                }

                #endregion
            }
            catch (Exception ex)
            {
            }
        }

        /// <summary>
        /// 清理工作表历史数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="point"></param>
        /// <param name="sheetType"></param>
        /// <param name="ignore">何明清舟山医院，忽略收入表清楚历史数据  默认关闭</param>
        public static void ClearSheetPartialData(ISheet sheet, PerSheetPoint point, SheetType sheetType, bool ignore = false)
        {
            if (sheet == null)
                return;

            for (int i = point.DataFirstRowNum.Value; i < sheet.LastRowNum + 1; i++)
            {
                var row = sheet.GetRow(i);
                if (row != null)
                {
                    row.ZeroHeight = false; //取消隐藏行
                    int dataFirstCellRowNum = point.DataFirstCellNum.Value;
                    //跳过核算单元和科室
                    for (int j = dataFirstCellRowNum; j < row.LastCellNum; j++)
                    {
                        var cell = row.GetCell(j);
                        if (cell == null) continue;
                        if (ignore && sheetType == SheetType.Income) continue;

                        if (cell.CellType != CellType.Formula || sheetType == SheetType.Income)
                        {
                            cell.RemoveCellComment();
                            row.RemoveCell(cell);
                        }
                    }
                }
            }
        }
        public static void ClearSheetTemplate(ISheet sheet, PerSheetPoint point, SheetType sheetType)
        {
            if (sheet == null)
                return;

            for (int i = point.HeaderFirstRowNum.Value; i < sheet.LastRowNum + 1; i++)
            {
                var row = sheet.GetRow(i);
                if (row != null)
                {
                    row.ZeroHeight = false; //取消隐藏行
                    int dataFirstCellRowNum = point.DataFirstCellNum.Value;
                    //跳过核算单元和科室
                    for (int j = dataFirstCellRowNum; j < row.LastCellNum; j++)
                    {
                        var cell = row.GetCell(j);
                        if (cell != null)
                        {
                            cell.RemoveCellComment(); //删除批注
                            row.RemoveCell(cell);
                        }
                    }
                }
            }

            if (!new SheetType[] { SheetType.Workload, SheetType.OtherWorkload }.Contains(sheetType))
            {
                if (sheet.NumMergedRegions > 0)
                {
                    for (int mergedIndex = sheet.NumMergedRegions - 1; mergedIndex >= 0; mergedIndex--)
                    {
                        sheet.RemoveMergedRegion(mergedIndex);
                    }
                }

                var row = sheet.GetRow(0);
                if (row != null) sheet.RemoveRow(row);
                sheet.ShiftRows(point.DataFirstRowNum.Value, sheet.LastRowNum + 1, -1);
            }
        }

        public static void CloseAutoFilter(string path)
        {
            try
            {
                var fileInfo = new FileInfo(path);
                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {
                    ExcelWorkbook workbook = package.Workbook;

                    if (workbook == null) return;
                    foreach (var sheet in workbook.Worksheets)
                    {
                        if (sheet.AutoFilterAddress != null)
                        {
                            sheet.Cells[sheet.AutoFilterAddress.Address].AutoFilter = false;
                        }
                    }
                    package.Save();
                }
            }
            catch (Exception)
            {
            }
        }

        /// <summary>
        /// 判断文件是否是xlsx文件
        /// </summary>
        /// <param name="filename">文件名称、文件扩展名</param>
        /// <returns></returns>
        public static bool IsXlsxFile(string filename)
        {
            if (string.IsNullOrEmpty(filename)) return false;

            string ext = filename;
            if (ext.Contains("."))
            {
                int start = filename.LastIndexOf('.') + 1;
                ext = filename.Substring(start, filename.Length - start);
            }
            return ext.ToLower() == ExcelVersion.xlsx.ToString().ToLower();
        }

    }
}
