using NPOI.SS.UserModel;
using Performance.DtoModels;
using Performance.EntityModels;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Performance.Services.ExtractExcelService
{
    public class WriteDataHelper
    {
        public enum UnitType
        {
            护理组,
            医生组,
            医技组
        }

        public static void WriteSheetHeader(ISheet sheet, PerSheetPoint point, SheetType sheetType, ExcelStyle style, List<ExcelHeader> headers)
        {
            if (headers == null || !headers.Any()) return;

            var columns = headers.Select(t => t).ToList();

            // 收入支出系数
            var nurseFactor = sheet.GetOrCreate(point.AccountingUnit.FirstOrDefault(t => t.UnitType == UnitType.护理组.ToString())?.FactorRow.Value ?? 0);
            var doctorFactor = sheet.GetOrCreate(point.AccountingUnit.FirstOrDefault(t => t.UnitType == UnitType.医生组.ToString())?.FactorRow.Value ?? 0);
            var technicianFactor = sheet.GetOrCreate(point.AccountingUnit.FirstOrDefault(t => t.UnitType == UnitType.医技组.ToString())?.FactorRow.Value ?? 0);
            // 费用类型
            var columnHeader = sheet.GetOrCreate(point.HeaderFirstRowNum.Value);
            // 工作量系数
            var workloadFactor = sheet.GetOrCreate(point.HeaderFirstRowNum.Value + 1);

            var factorStyle = new SheetType[] { SheetType.Workload, SheetType.OtherWorkload }.Contains(sheetType)
                ? style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.系数, CellFormat.数字)
                : style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.系数, CellFormat.百分比);
            var columnStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.列头);

            // 去除excel中已存在的列
            int headerFirstCellNum = point.DataFirstCellNum.Value;
            if (columnHeader.LastCellNum > point.DataFirstCellNum.Value)
            {
                for (int index = point.DataFirstCellNum.Value; index < columnHeader.LastCellNum; index++)
                {
                    var category = columnHeader.GetCell(index).GetDecodeEscapes();
                    if (string.IsNullOrEmpty(category)) continue;

                    if (index > headerFirstCellNum) headerFirstCellNum = index;
                    var column = columns.FirstOrDefault(t => t.ColumnName.NoBlank() == category);
                    if (sheetType == SheetType.Income)
                    {
                        WriteFactor(doctorFactor, index, column?.DoctorFactor, factorStyle);

                        WriteFactor(nurseFactor, index, column?.NurseFactor, factorStyle);

                        WriteFactor(technicianFactor, index, column?.TechnicianFactor, factorStyle);
                    }
                    columns.RemoveAll(t => t.ColumnName.NoBlank() == category);
                }
                if (headerFirstCellNum > point.DataFirstCellNum.Value)
                    headerFirstCellNum += 1;
            }

            if (columns == null || !columns.Any()) return;

            // 补充excel中不存在的列
            foreach (var item in columns)
            {
                var columnCell = columnHeader.GetOrCreate(headerFirstCellNum);
                columnCell.SetCellValue(item.ColumnName);
                columnCell.CellStyle = columnStyle;

                if (new SheetType[] { SheetType.Workload, SheetType.OtherWorkload }.Contains(sheetType))
                {
                    var workloadCell = workloadFactor.GetOrCreate(headerFirstCellNum);
                    workloadCell.SetCellValue<decimal>(item.WorkloadFactor);
                    workloadCell.CellStyle = factorStyle;
                }
                else
                {
                    WriteFactor(doctorFactor, headerFirstCellNum, item?.DoctorFactor, factorStyle);

                    WriteFactor(nurseFactor, headerFirstCellNum, item?.NurseFactor, factorStyle);

                    WriteFactor(technicianFactor, headerFirstCellNum, item?.TechnicianFactor, factorStyle);
                }

                headerFirstCellNum++;
            }
        }

        private static void WriteFactor(IRow row, int cellIndex, decimal? value, ICellStyle style)
        {
            var cell = row.GetCell(cellIndex);
            if (cell != null)
            {
                row.RemoveCell(cell);
            }
            cell = row.CreateCell(cellIndex);

            if (value.HasValue)
            {
                cell.SetCellValue<decimal>(value);
            }
            else
            {
                cell.SetCellValue<string>("#VALUE!");
            }
            cell.CellStyle = style;
        }

        #region ExtractData

        public static void WriteSheetData(ISheet sheet, PerSheetPoint point, SheetType sheetType, ExcelStyle style, List<string> headers, List<ExtractTransDto> data)
        {
            var columnHeader = sheet.GetOrCreate(point.HeaderFirstRowNum.Value);
            int dataFirstRowNum = point.DataFirstRowNum.Value;

            List<IncomeRow> rows = new List<IncomeRow>();

            WriteSheetDataExistent(sheet, columnHeader, point, sheetType, style, headers, data, rows, ref dataFirstRowNum);

            if (sheetType == SheetType.Income && rows != null)
            {
                dataFirstRowNum = point.DataFirstRowNum.Value;
                RemoveIncomeRow(sheet, point);
                WriteSheetIncomeData(sheet, columnHeader, point, style, headers, data, rows, dataFirstRowNum);
                dataFirstRowNum = point.DataFirstRowNum.Value + rows.Count;
            }

            if (data == null || !data.Any()) return;

            WriteSheetDataNonexistent(sheet, columnHeader, point, sheetType, style, headers, data, dataFirstRowNum);
        }

        private static void WriteSheetDataExistent(ISheet sheet, IRow columnHeader, PerSheetPoint point, SheetType sheetType, ExcelStyle style,
            List<string> headers, List<ExtractTransDto> data, List<IncomeRow> incomes, ref int dataFirstRowNum)
        {
            if (sheet.LastRowNum <= dataFirstRowNum) return;

            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);

            headers = headers.Select(t => t.NoBlank()).ToList();

            int dataFirstCellNum = point.DataFirstCellNum.Value;

            for (int rowIndex = point.DataFirstRowNum.Value; rowIndex < sheet.LastRowNum + 1; rowIndex++)
            {
                var row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                string department = row.GetOrCreate(dataFirstCellNum - 1).GetDecodeEscapes();
                if (string.IsNullOrEmpty(department)) continue;

                if (rowIndex > dataFirstRowNum) dataFirstRowNum = rowIndex + 1;

                var deptData = data.Where(t => t.Department == department);
                if (deptData == null || !deptData.Any(t => t.Value.HasValue && t.Value != 0)) continue;

                #region 写入数据

                if (sheetType == SheetType.Income)
                {
                    incomes.Add(GetIncomeRowMessage(row, dataFirstCellNum, department, rowIndex));
                    continue;
                }

                for (int cellIndex = dataFirstCellNum; cellIndex < columnHeader.LastCellNum; cellIndex++)
                {
                    var column = columnHeader.GetOrCreate(cellIndex).GetDecodeEscapes();
                    var cell = row.GetOrCreate(cellIndex);

                    var value = deptData.FirstOrDefault(t => t.Category.NoBlank() == column)?.Value;
                    //数据为空，且单元格值不为空，不写入数据（保留原始值）
                    var notWrite = !value.HasValue && !string.IsNullOrEmpty(cell.ToString());
                    if (/*cell.CellType != CellType.Formula && */!notWrite)
                    {
                        cell.SetCellValue<decimal>(value);
                        if (headers != null && headers.Contains(column))
                        {
                            cell.CellStyle = cellStyle;
                        }
                    }
                }

                #endregion

                data.RemoveAll(t => t.Department == department);
            }

            if (point.DataFirstRowNum.Value < dataFirstRowNum) dataFirstRowNum += 1;
        }

        private static void WriteSheetDataNonexistent(ISheet sheet, IRow columnHeader, PerSheetPoint point, SheetType sheetType, ExcelStyle style,
            List<string> headers, List<ExtractTransDto> data, int dataFirstRowNum)
        {
            var departments = data.Select(s => s.Department ?? "")/*.Where(w => !string.IsNullOrEmpty(w))*/.Distinct().ToList();

            var filed = sheet.SheetName.Contains("住院") ? fieldInpat : fieldOut;
            if (sheet.SheetName.Contains("工作量"))
            {
                filed = sheet.SheetName.Contains("医生") ? fieldDoctor : fieldNurse;
            }

            var deptStyle = style.GetCellStyle();
            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);

            headers = headers.Select(t => t.NoBlank()).ToList();

            foreach (string department in departments)
            {
                var deptData = data.Where(t => (t.Department ?? "") == department);
                if (deptData == null || !deptData.Any()) continue;

                var row = sheet.GetOrCreate(dataFirstRowNum);
                for (int cellIndex = point.HeaderFirstCellNum.Value; cellIndex < columnHeader.LastCellNum; cellIndex++)
                {
                    var column = columnHeader.GetCell(cellIndex).GetDecodeEscapes();
                    var cell = row.CreateCell(cellIndex);

                    if (filed.ContainsKey(column))
                    {
                        cell.SetCellOValue(filed[column]?.Invoke(deptData.First()));
                        cell.CellStyle = deptStyle;
                    }
                    else if (sheetType == SheetType.Income || (headers != null && headers.Contains(column)))
                    {
                        var value = deptData.FirstOrDefault(t => t.Category.NoBlank() == column)?.Value;
                        cell.SetCellValue<decimal>(value);
                        cell.CellStyle = cellStyle;
                    }
                }
                dataFirstRowNum++;
            }
        }

        private static void WriteSheetIncomeData(ISheet sheet, IRow columnHeader, PerSheetPoint point, ExcelStyle style, List<string> headers,
            List<ExtractTransDto> data, List<IncomeRow> incomes, int dataFirstRowNum)
        {
            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);
            var deptStyle = style.GetCellStyle();

            headers = headers.Select(t => t.NoBlank()).ToList();

            int dataFirstCellNum = point.DataFirstCellNum.Value;

            foreach (var item in incomes)
            {
                var row = sheet.GetOrCreate(dataFirstRowNum);

                var deptData = data.Where(t => t.Department == item.Department);
                if (deptData == null || !deptData.Any()) continue;

                var deptContents = new Dictionary<int, string>
                {
                    { 1, item.Department },
                    { 2, item.NurseAccount },
                    { 3, item.DoctorAccount },
                    { 4, item.TechnicAccounting },
                };

                foreach (var content in deptContents)
                {
                    var cell = row.GetOrCreate(dataFirstCellNum - content.Key);
                    cell.SetCellValue(content.Value);
                    cell.CellStyle = deptStyle;
                }

                for (int cellIndex = dataFirstCellNum; cellIndex < columnHeader.LastCellNum; cellIndex++)
                {
                    var column = columnHeader.GetOrCreate(cellIndex).GetDecodeEscapes();
                    var cell = row.GetOrCreate(cellIndex);

                    var value = deptData.FirstOrDefault(t => t.Category.NoBlank() == column)?.Value;
                    if (cell.CellType != CellType.Formula)
                    {
                        cell.SetCellValue<decimal>(value);
                        cell.CellStyle = cellStyle;
                    }
                }

                dataFirstRowNum++;
                data.RemoveAll(t => t.Department == item.Department);
            }
        }

        public static string HasValue(params string[] list)
        {
            if (list == null || !list.Any()) return null;

            return list.FirstOrDefault(t => !string.IsNullOrEmpty(t));
        }

        /// <summary> 住院核算单元 </summary>
        private static readonly Dictionary<string, Func<ExtractTransDto, string>> fieldInpat = new Dictionary<string, Func<ExtractTransDto, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元（医生组）", (dto) =>  dto.InpatDoctorAccounting },
            { "核算单元（护理组）", (dto) =>  dto.InpatNurseAccounting },
            { "核算单元（医技组）", (dto) =>  dto.InpatTechnicAccounting },
        };

        /// <summary> 门诊核算单元 </summary>
        private static readonly Dictionary<string, Func<ExtractTransDto, string>> fieldOut = new Dictionary<string, Func<ExtractTransDto, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元（医生组）", (dto) => dto.OutDoctorAccounting },
            { "核算单元（护理组）", (dto) => dto.OutNurseAccounting },
            { "核算单元（医技组）", (dto) => dto.OutTechnicAccounting },
        };

        /// <summary> 医生工作量 </summary>
        private static readonly Dictionary<string, Func<ExtractTransDto, string>> fieldDoctor = new Dictionary<string, Func<ExtractTransDto, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元", (dto) =>
                          {
                              var obj = new string []{ dto.OutDoctorAccounting, dto.InpatDoctorAccounting, dto.OutTechnicAccounting, dto.InpatTechnicAccounting }
                                .FirstOrDefault(t => !string.IsNullOrEmpty(t));
                              return obj;
                          }
            },
        };

        /// <summary> 护理工作量 </summary>
        private static readonly Dictionary<string, Func<ExtractTransDto, string>> fieldNurse = new Dictionary<string, Func<ExtractTransDto, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元", (dto) => new string []{ dto.OutNurseAccounting, dto.InpatNurseAccounting }.FirstOrDefault(t => !string.IsNullOrEmpty(t)) },
        };

        #endregion ExtractData

        #region CollectData

        public static void WriteCollectData(ISheet sheet, PerSheetPoint point, SheetType sheetType, ExcelStyle style, List<string> headers, List<collect_data> data)
        {
            var columnHeader = sheet.GetOrCreate(point.HeaderFirstRowNum.Value);
            int dataFirstRowNum = point.DataFirstRowNum.Value;

            List<IncomeRow> rows = new List<IncomeRow>();

            WriteCollectDataExistent(sheet, columnHeader, point, sheetType, style, headers, data, rows, ref dataFirstRowNum);

            if (sheetType == SheetType.Income && rows != null)
            {
                dataFirstRowNum = point.DataFirstRowNum.Value;
                RemoveIncomeRow(sheet, point);
                WriteCollectIncomeData(sheet, columnHeader, point, style, headers, data, rows, dataFirstRowNum);
                dataFirstRowNum = point.DataFirstRowNum.Value + rows.Count;
            }

            if (data == null || !data.Any()) return;

            WriteCollectDataNonexistent(sheet, columnHeader, point, sheetType, style, headers, data, dataFirstRowNum);
        }

        private static void WriteCollectDataExistent(ISheet sheet, IRow columnHeader, PerSheetPoint point, SheetType sheetType, ExcelStyle style,
            List<string> headers, List<collect_data> data, List<IncomeRow> incomes, ref int dataFirstRowNum)
        {
            if (sheet.LastRowNum <= dataFirstRowNum) return;

            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);

            headers = headers.Select(t => t.NoBlank()).ToList();

            int dataFirstCellNum = point.DataFirstCellNum.Value;

            for (int rowIndex = point.DataFirstRowNum.Value; rowIndex < sheet.LastRowNum + 1; rowIndex++)
            {
                var row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                string department = row.GetOrCreate(dataFirstCellNum - 1).GetDecodeEscapes();
                if (string.IsNullOrEmpty(department)) continue;

                if (rowIndex > dataFirstRowNum) dataFirstRowNum = rowIndex + 1;

                var deptData = data.Where(t => t.Department == department);
                if (deptData == null || !deptData.Any(t => !string.IsNullOrEmpty(t.CellValue))) continue;

                #region 写入数据 

                if (sheetType == SheetType.Income)
                {
                    incomes.Add(GetIncomeRowMessage(row, dataFirstCellNum, department, rowIndex));
                    continue;
                }

                for (int cellIndex = dataFirstCellNum; cellIndex < columnHeader.LastCellNum; cellIndex++)
                {
                    var column = columnHeader.GetOrCreate(cellIndex).GetDecodeEscapes();
                    var cell = row.GetOrCreate(cellIndex);

                    var value = deptData.FirstOrDefault(t => t.TypeName.NoBlank() == column)?.CellValue;
                    //数据为空，且单元格值不为空，不写入数据（保留原始值）
                    var notWrite = string.IsNullOrEmpty(value) && !string.IsNullOrEmpty(cell.ToString());
                    if (cell.CellType != CellType.Formula && !notWrite)
                    {
                        cell.SetCellValue<decimal>(value);
                        if (headers != null && headers.Contains(column))
                        {
                            cell.CellStyle = cellStyle;
                        }
                    }
                }

                #endregion

                data.RemoveAll(t => t.Department == department);
            }

            if (point.DataFirstRowNum.Value < dataFirstRowNum) dataFirstRowNum += 1;
        }

        private static void WriteCollectDataNonexistent(ISheet sheet, IRow columnHeader, PerSheetPoint point, SheetType sheetType, ExcelStyle style,
            List<string> headers, List<collect_data> data, int dataFirstRowNum)
        {
            var departments = data.Select(s => s.Department ?? "")/*.Where(w => !string.IsNullOrEmpty(w))*/.Distinct().ToList();

            var filed = new SheetType[] { SheetType.Workload, SheetType.OtherWorkload }.Contains(sheetType)
                ? collectWork
                : new SheetType[] { SheetType.OtherIncome, SheetType.Expend }.Contains(sheetType) ? collectIncome : collectDept;

            var deptStyle = style.GetCellStyle();
            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);

            headers = headers.Select(t => t.NoBlank()).ToList();

            foreach (string department in departments)
            {
                var deptData = data.Where(t => (t.Department ?? "") == department);
                if (deptData == null || !deptData.Any()) continue;

                var row = sheet.GetOrCreate(dataFirstRowNum);
                for (int cellIndex = point.HeaderFirstCellNum.Value; cellIndex < columnHeader.LastCellNum; cellIndex++)
                {
                    var column = columnHeader.GetCell(cellIndex).GetDecodeEscapes();
                    var cell = row.CreateCell(cellIndex);

                    if (filed.ContainsKey(column))
                    {
                        cell.SetCellOValue(filed[column]?.Invoke(deptData.First()));
                        cell.CellStyle = deptStyle;
                    }
                    else if (sheetType == SheetType.Income || (headers != null && headers.Contains(column)))
                    {
                        var value = deptData.FirstOrDefault(t => t.TypeName.NoBlank() == column)?.CellValue;
                        cell.SetCellValue<decimal>(value);
                        cell.CellStyle = cellStyle;
                    }
                }
                dataFirstRowNum++;
            }
        }

        private static void WriteCollectIncomeData(ISheet sheet, IRow columnHeader, PerSheetPoint point, ExcelStyle style, List<string> headers,
            List<collect_data> data, List<IncomeRow> incomes, int dataFirstRowNum)
        {
            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);
            var deptStyle = style.GetCellStyle();

            headers = headers.Select(t => t.NoBlank()).ToList();

            int dataFirstCellNum = point.DataFirstCellNum.Value;

            foreach (var item in incomes)
            {
                var row = sheet.GetOrCreate(dataFirstRowNum);

                var deptData = data.Where(t => t.Department == item.Department);
                if (deptData == null || !deptData.Any()) continue;

                var deptContents = new Dictionary<int, string>
                {
                    { 1, item.Department },
                    { 2, item.NurseAccount },
                    { 3, item.DoctorAccount },
                    { 4, item.TechnicAccounting },
                };

                foreach (var content in deptContents)
                {
                    var cell = row.GetOrCreate(dataFirstCellNum - content.Key);
                    cell.SetCellValue(content.Value);
                    cell.CellStyle = deptStyle;
                }

                for (int cellIndex = dataFirstCellNum; cellIndex < columnHeader.LastCellNum; cellIndex++)
                {
                    var column = columnHeader.GetOrCreate(cellIndex).GetDecodeEscapes();
                    var cell = row.GetOrCreate(cellIndex);

                    var value = deptData.FirstOrDefault(t => t.TypeName.NoBlank() == column)?.CellValue;
                    if (cell.CellType != CellType.Formula)
                    {
                        cell.SetCellValue<decimal>(value);
                        cell.CellStyle = cellStyle;
                    }
                }

                dataFirstRowNum++;
                data.RemoveAll(t => t.Department == item.Department);
            }
        }

        /// <summary> 收入固定列 </summary>
        private static readonly Dictionary<string, Func<collect_data, string>> collectIncome = new Dictionary<string, Func<collect_data, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元（医生组）", (dto) => dto.AccountingUnitDoctor },
            { "核算单元（护理组）", (dto) => dto.AccountingUnitNurse },
            { "核算单元（医技组）", (dto) => dto.AccountingUnitTechnician },
        };

        /// <summary> 工作量固定列 </summary>
        private static readonly Dictionary<string, Func<collect_data, string>> collectWork = new Dictionary<string, Func<collect_data, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元", (dto) => dto.AccountingUnitDoctor },
        };

        /// <summary> 科室详情数据固定列 </summary>
        private static readonly Dictionary<string, Func<collect_data, string>> collectDept = new Dictionary<string, Func<collect_data, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元类型", (dto) => dto.UnitType },
        };

        #endregion CollectData

        /// <summary>
        /// 抽取优化，获取有数据的科室信息
        /// </summary>
        /// <param name="row"></param>
        /// <param name="dataFirstCellNum"></param>
        /// <param name="department"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private static IncomeRow GetIncomeRowMessage(IRow row, int dataFirstCellNum, string department, int rowIndex)
        {
            var nurse = row.GetOrCreate(dataFirstCellNum - 2).GetDecodeEscapes();
            var doctor = row.GetOrCreate(dataFirstCellNum - 3).GetDecodeEscapes();
            var technic = row.GetOrCreate(dataFirstCellNum - 4).GetDecodeEscapes();

            for (int j = dataFirstCellNum - 4; j < dataFirstCellNum; j++)
            {
                var cell = row.GetCell(j);
                if (cell != null)
                {
                    cell.RemoveCellComment();
                    row.RemoveCell(cell);
                }
            }

            return new IncomeRow(department, doctor, nurse, technic, rowIndex);
        }

        /// <summary>
        /// 抽取优化，删除收入数据的核算科室信息
        /// </summary>
        /// <param name="row"></param>
        /// <param name="dataFirstCellNum"></param>
        /// <param name="department"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private static void RemoveIncomeRow(ISheet sheet, PerSheetPoint point)
        {
            if (sheet == null)
                return;

            for (int i = point.DataFirstRowNum.Value; i < sheet.LastRowNum + 1; i++)
            {
                var row = sheet.GetRow(i);
                if (row != null)
                {
                    int dataFirstCellNum = point.DataFirstCellNum.Value;
                    //跳过核算单元和科室
                    for (int j = dataFirstCellNum - 4; j < dataFirstCellNum; j++)
                    {
                        var cell = row.GetCell(j);
                        if (cell != null)
                        {
                            cell.RemoveCellComment();
                            row.RemoveCell(cell);
                        }
                    }
                }
            }
        }
    }
}
