using Microsoft.Extensions.Logging;
using NPOI.SS.UserModel;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using Performance.DtoModels;
using Performance.EntityModels;
using Performance.Infrastructure;
using Performance.Repository;
using Performance.Services.ExtractExcelService;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;

namespace Performance.Services
{
    public class ReportGlobalService : IAutoInjection
    {
        private readonly ILogger logger;
        private readonly ComputeService _computeService;
        private readonly PerforHospitalRepository _hospitalRepository;
        private readonly PerforPerallotRepository perallotRepository;
        private readonly PerforReportglobalRepository reportglobalRepository;
        private readonly PerforHisimportdataRepository hisimportdataRepository;
        private readonly PerforHisimportaccountRepository hisimportaccountRepository;
        private readonly PerforHisimportclinicRepository hisimportclinicRepository;
        private readonly PerforHisimportbaiscnormRepository hisimportbaiscnormRepository;
        private readonly PerforReportperformancetagsRepository reportperformancetagsRepository;
        private readonly PerforReportperformancepersontagsRepository reportperformancepersontagsRepository;
        private readonly PerforPeremployeeRepository perforPeremployeeRepository;
        private readonly PerforUserhospitalRepository perforUserhospitalRepository;
        private readonly PerforUserRepository perforUserRepository;
        private readonly PersonService personService;

        public ReportGlobalService(
            ILogger<ReportGlobalService> logger,
            ComputeService computeService,
            PerforHospitalRepository hospitalRepository,
            PerforPerallotRepository perallotRepository,
            PerforReportglobalRepository reportglobalRepository,
            PerforHisimportdataRepository hisimportdataRepository,
            PerforHisimportaccountRepository hisimportaccountRepository,
            PerforHisimportclinicRepository hisimportclinicRepository,
            PerforHisimportbaiscnormRepository hisimportbaiscnormRepository,
            PerforReportperformancetagsRepository reportperformancetagsRepository,
            PerforReportperformancepersontagsRepository reportperformancepersontagsRepository,
            PerforPeremployeeRepository perforPeremployeeRepository,
            PerforUserhospitalRepository perforUserhospitalRepository,
            PerforUserRepository perforUserRepository,
            PersonService personService
            )
        {
            this.logger = logger;
            _computeService = computeService;
            _hospitalRepository = hospitalRepository;
            this.perallotRepository = perallotRepository;
            this.reportglobalRepository = reportglobalRepository;
            this.hisimportdataRepository = hisimportdataRepository;
            this.hisimportaccountRepository = hisimportaccountRepository;
            this.hisimportclinicRepository = hisimportclinicRepository;
            this.hisimportbaiscnormRepository = hisimportbaiscnormRepository;
            this.reportperformancetagsRepository = reportperformancetagsRepository;
            this.reportperformancepersontagsRepository = reportperformancepersontagsRepository;
            this.perforPeremployeeRepository = perforPeremployeeRepository;
            this.perforUserhospitalRepository = perforUserhospitalRepository;
            this.perforUserRepository = perforUserRepository;
            this.personService = personService;
        }

        #region Report_Global

        public List<report_global> GetReportGlobals(int hospitalId)
        {
            var globals = reportglobalRepository.GetEntities(t => t.HospitalID == hospitalId)
                ?.OrderBy(t => t.Category).ThenBy(t => t.Year).ThenBy(t => t.Month).ToList();
            return globals;
        }

        public bool CreateReportGlobal(report_global global)
        {
            return reportglobalRepository.Add(global);
        }

        public bool UpdateReportGlobal(report_global global)
        {
            var entity = reportglobalRepository.GetEntity(t => t.Id == global.Id);
            entity.Year = global.Year;
            entity.Month = global.Month;
            entity.Category = global.Category;
            entity.Value = global.Value;
            return reportglobalRepository.Update(entity);
        }

        public bool DeleteReportGlobal(int globalId)
        {
            var entity = reportglobalRepository.GetEntity(t => t.Id == globalId);
            return reportglobalRepository.Remove(entity);
        }

        #endregion Report_Global

        #region Copy Previous Report Data

        public void CopyPreviousGlobalData(per_allot allot)
        {
            var globals = reportglobalRepository.GetEntities(t => t.HospitalID == allot.HospitalId);
            if (globals == null || !globals.Any()) return;

            var onlyYears = globals.Where(t => t.Year.HasValue && !t.Month.HasValue);
            if (onlyYears != null && onlyYears.Any())
            {
                var year = onlyYears.Where(t => t.Year < allot.Year)?.Max(t => t.Year);
                if (year.HasValue)
                {
                    var current = onlyYears.Where(t => t.Year == allot.Year)?.ToList() ?? new List<report_global>();
                    var previous = onlyYears.Where(t => t.Year == year)?.ToList() ?? new List<report_global>();

                    var newdata = previous.Where(t => !current.Select(s => s.Category).Contains(t.Category))?.ToList();
                    if (newdata != null && newdata.Any())
                    {
                        newdata = newdata.Select(t => new report_global
                        {
                            HospitalID = allot.HospitalId,
                            Year = allot.Year,
                            Category = t.Category,
                            Value = t.Value
                        }).ToList();
                        reportglobalRepository.AddRange(newdata.ToArray());
                    }
                }
            }

            var yearAndMonth = globals.Where(t => t.Year.HasValue && t.Month.HasValue);
            if (yearAndMonth != null && yearAndMonth.Any())
            {
                var month = yearAndMonth.Where(t => t.Year == allot.Year && t.Month < allot.Month)?.Max(t => t.Month);
                if (allot.Month == 1 || !month.HasValue)
                {
                    var year = yearAndMonth.Where(t => t.Year < allot.Year)?.Max(t => t.Year);
                    month = yearAndMonth.Where(t => t.Year == year)?.Max(t => t.Month);
                    if (year.HasValue && month.HasValue)
                    {
                        var current = yearAndMonth.Where(t => t.Year == allot.Year && t.Month == allot.Month)?.ToList() ?? new List<report_global>();
                        var previous = yearAndMonth.Where(t => t.Year == year && t.Month == month)?.ToList() ?? new List<report_global>();

                        var newdata = previous.Where(t => !current.Select(s => s.Category).Contains(t.Category))?.ToList();
                        if (newdata != null && newdata.Any())
                        {
                            newdata = newdata.Select(t => new report_global
                            {
                                HospitalID = allot.HospitalId,
                                Year = allot.Year,
                                Month = allot.Month,
                                Category = t.Category,
                                Value = t.Value
                            }).ToList();
                            reportglobalRepository.AddRange(newdata.ToArray());
                        }
                    }
                }
                else
                {
                    if (month.HasValue)
                    {
                        var current = yearAndMonth.Where(t => t.Year == allot.Year && t.Month == allot.Month)?.ToList() ?? new List<report_global>();
                        var previous = yearAndMonth.Where(t => t.Year == allot.Year && t.Month == month)?.ToList() ?? new List<report_global>();

                        var newdata = previous.Where(t => !current.Select(s => s.Category).Contains(t.Category))?.ToList();
                        if (newdata != null && newdata.Any())
                        {
                            newdata = newdata.Select(t => new report_global
                            {
                                HospitalID = allot.HospitalId,
                                Year = allot.Year,
                                Month = allot.Month,
                                Category = t.Category,
                                Value = t.Value
                            }).ToList();
                            reportglobalRepository.AddRange(newdata.ToArray());
                        }
                    }
                }
            }
        }

        #endregion Copy Previous Report Data

        #region ImportFile && SaveData

        public void ImportAllotData(int hospitalId, string filePath)
        {
            IWorkbook workbook = null;
            try
            {
                workbook = ExcelHelper.GetWorkbook(filePath);
                if (workbook == null) throw new PerformanceException("文件读取失败");

                var allots = perallotRepository.GetEntities(t => t.HospitalId == hospitalId);

                var basicnormData = new List<his_import_baiscnorm>();

                for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
                {
                    var sheet = workbook.GetSheetAt(sheetIndex);

                    if (sheet == null) continue;

                    string sheetName = sheet.SheetName.NoBlank();
                    logger.LogInformation($"正在读取{sheetName}的数据");

                    var columns = GetColumns(sheet, sheetName, out string sourceType);
                    if (columns == null || !columns.Any(t => !string.IsNullOrEmpty(t.NoBlank()))) continue;

                    switch (sheetName)
                    {
                        case "科室绩效":
                            ImporAccountData(sheet, columns, hospitalId, allots, basicnormData);
                            break;

                        case "科室标签":
                            ImporAccountTag(sheet, columns, hospitalId);
                            break;

                        case "科主任护士长绩效":
                            ImporClinicData(sheet, columns, hospitalId, allots, basicnormData);
                            break;

                        case "科主任护士长标签":
                            ImporClinicTag(sheet, columns, hospitalId);
                            break;

                        default:
                            ImportBasicData(sheet, columns, hospitalId, allots, sheetName, sourceType);
                            break;
                    }
                    logger.LogInformation($"{sheetName}读取已结束");
                }

                #region 人均绩效

                if (basicnormData != null && basicnormData.Any())
                {
                    foreach (var item in basicnormData.Select(t => new { t.Year, t.Month }).Distinct())
                    {
                        hisimportbaiscnormRepository.DeleteFromQuery(t => t.HospitalId == hospitalId && t.Year == item.Year && t.Month == item.Month);
                    }
                    hisimportbaiscnormRepository.InsertExecute(basicnormData);
                }

                #endregion 科室总绩效 人均绩效
            }
            catch (Exception ex)
            {
                logger.LogError(ex.ToString());
            }
        }

        private void ImportBasicData(ISheet sheet, List<string> columns, int hospitalId, List<per_allot> allots, string sheetName, string sourceType)
        {
            if (string.IsNullOrEmpty(sourceType)) return;

            var data = new List<his_importdata>();
            for (int rowIndex = 1; rowIndex < sheet.LastRowNum + 1; rowIndex++)
            {
                var row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                his_importdata importdata = new his_importdata
                {
                    Year = GetCellValue<int>(row, columns, "年份"),
                    Month = GetCellValue<int>(row, columns, "月份"),
                    AccountingUnit = GetCellValue<string>(row, columns, "核算单元"),
                    Department = GetCellValue<string>(row, columns, "科室"),
                    PersonnelNumber = GetCellValue<string>(row, columns, "工号"),
                    PersonnelName = GetCellValue<string>(row, columns, "医生姓名"),
                    Original = GetCellValue<string>(row, columns, "类别"),
                    Value = GetCellValue<decimal>(row, columns, "值"),
                };
                data.Add(importdata);
            }
            logger.LogInformation($"{sheet.SheetName}共有{sheet.LastRowNum}行数据，读取到{data.Count}条记录");
            if (data == null || !data.Any(t => t.Year != 0 && t.Month != 0)) return;

            var dateTime = DateTime.Now;
            data.ForEach(t =>
            {
                t.HospitalId = hospitalId;
                t.AllotId = allots?.FirstOrDefault(w => w.Year == t.Year && t.Month == w.Month)?.ID;
                t.SourceType = sourceType;
                t.Category = sheetName;
                t.CreateTime = dateTime;
            });

            foreach (var item in data.Select(t => new { t.Year, t.Month }).Distinct())
            {
                hisimportdataRepository.DeleteFromQuery(t => t.HospitalId == hospitalId && t.Category == sheetName && t.Year == item.Year && t.Month == item.Month);
            }
            hisimportdataRepository.InsertExecute(data.Where(t => t.Year != 0 && t.Month != 0));
        }

        private void ImporAccountData(ISheet sheet, List<string> columns, int hospitalId, List<per_allot> allots, List<his_import_baiscnorm> basicnormData)
        {
            var data = new List<his_import_account>();
            for (int rowIndex = 1; rowIndex < sheet.LastRowNum + 1; rowIndex++)
            {
                var row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                his_import_account importdata = new his_import_account
                {
                    Year = GetCellValue<int>(row, columns, "年份"),
                    Month = GetCellValue<int>(row, columns, "月份"),
                    UnitType = GetCellValue<string>(row, columns, "核算单元类型"),
                    AccountingUnit = GetCellValue<string>(row, columns, "核算单元"),
                    Number = GetCellValue<decimal>(row, columns, "核算单元人员数量"),
                    RealGiveFee = GetCellValue<decimal>(row, columns, "实发绩效"),
                };
                data.Add(importdata);
            }
            logger.LogInformation($"{sheet.SheetName}共有{sheet.LastRowNum}行数据，读取到{data.Count}条记录");
            if (data == null || !data.Any(t => t.Year != 0 && t.Month != 0)) return;

            var dateTime = DateTime.Now;
            data.ForEach(t =>
            {
                t.HospitalId = hospitalId;
                t.AllotId = allots?.FirstOrDefault(w => w.Year == t.Year && t.Month == w.Month)?.ID ?? 0;
                t.CreateTime = dateTime;
            });

            #region his_import_baiscnorm 人均绩效计算
            logger.LogInformation($"人均绩效计算");
            Dictionary<string, string> basicDict = new Dictionary<string, string>
            {
                { "行政工勤人均绩效", UnitType.行政工勤.ToString() },
                { "临床医生人均绩效", UnitType.医生组.ToString() },
                { "医技医生人均绩效", UnitType.医技组.ToString() },
                { "护士人均绩效", UnitType.护理组.ToString() },
            };
            List<his_import_baiscnorm> baiscnorms = new List<his_import_baiscnorm>();
            foreach (var item in basicDict)
            {
                basicnormData.AddRange(data.Where(t => t.UnitType == item.Value).GroupBy(t => new { t.AllotId, t.Year, t.Month }).Select(t => new his_import_baiscnorm
                {
                    HospitalId = hospitalId,
                    AllotId = t.Key.AllotId ?? 0,
                    Year = t.Key.Year,
                    Month = t.Key.Month,
                    PositionName = item.Key,
                    TotelValue = t.Sum(s => s.RealGiveFee),
                    TotelNumber = t.Sum(s => s.Number),
                    AvgValue = t.Sum(s => s.Number) == 0 ? 0 : t.Sum(s => s.RealGiveFee) / t.Sum(s => s.Number),
                    CreateTime = dateTime
                }));
            }
            #endregion his_import_baiscnorm

            #region his_import_account 临床科室绩效
            logger.LogInformation($"临床科室绩效");
            foreach (var item in data.Select(t => new { t.Year, t.Month }).Distinct())
            {
                hisimportaccountRepository.DeleteFromQuery(t => t.HospitalId == hospitalId && t.Year == item.Year && t.Month == item.Month);
            }
            hisimportaccountRepository.InsertExecute(data.Where(t => t.Year != 0 && t.Month != 0));
            #endregion his_import_account
        }

        private void ImporAccountTag(ISheet sheet, List<string> columns, int hospitalId)
        {
            var data = new List<report_performance_tags>();
            for (int rowIndex = 1; rowIndex < sheet.LastRowNum + 1; rowIndex++)
            {
                var row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                report_performance_tags importdata = new report_performance_tags
                {
                    UnitType = GetCellValue<string>(row, columns, "核算单元类型"),
                    AccountingUnit = GetCellValue<string>(row, columns, "核算单元"),
                    Tag1 = GetCellValue<string>(row, columns, "Tag1"),
                    Tag2 = GetCellValue<string>(row, columns, "Tag2"),
                    Tag3 = GetCellValue<string>(row, columns, "Tag3"),
                    Tag4 = GetCellValue<string>(row, columns, "Tag4"),
                    Tag5 = GetCellValue<string>(row, columns, "Tag5"),
                };
                data.Add(importdata);
            }
            logger.LogInformation($"{sheet.SheetName}共有{sheet.LastRowNum}行数据，读取到{data.Count}条记录");
            if (data == null || !data.Any(t => !string.IsNullOrEmpty(t.UnitType) && !string.IsNullOrEmpty(t.AccountingUnit))) return;

            var dateTime = DateTime.Now;
            data.ForEach(t =>
            {
                t.HospitalId = hospitalId;
                t.CreateTime = dateTime;
            });

            reportperformancetagsRepository.DeleteFromQuery(t => t.HospitalId == hospitalId);
            reportperformancetagsRepository.InsertExecute(data.Where(t => !string.IsNullOrEmpty(t.UnitType) && !string.IsNullOrEmpty(t.AccountingUnit)));
        }

        private void ImporClinicData(ISheet sheet, List<string> columns, int hospitalId, List<per_allot> allots, List<his_import_baiscnorm> basicnormData)
        {
            var data = new List<his_import_clinic>();
            for (int rowIndex = 1; rowIndex < sheet.LastRowNum + 1; rowIndex++)
            {
                var row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                his_import_clinic importdata = new his_import_clinic
                {
                    Year = GetCellValue<int>(row, columns, "年份"),
                    Month = GetCellValue<int>(row, columns, "月份"),
                    UnitType = GetCellValue<string>(row, columns, "核算单元类型"),
                    AccountingUnit = GetCellValue<string>(row, columns, "核算单元"),
                    AccountType = GetCellValue<string>(row, columns, "职称"),
                    JobNumber = GetCellValue<string>(row, columns, "工号"),
                    EmployeeName = GetCellValue<string>(row, columns, "姓名"),
                    Basics = GetCellValue<decimal>(row, columns, "基础绩效系数"),
                    RealGiveFee = GetCellValue<decimal>(row, columns, "实发绩效"),
                };
                data.Add(importdata);
            }
            logger.LogInformation($"{sheet.SheetName}共有{sheet.LastRowNum}行数据，读取到{data.Count}条记录");
            if (data == null || !data.Any(t => t.Year != 0 && t.Month != 0)) return;

            var dateTime = DateTime.Now;
            data.ForEach(t =>
            {
                t.HospitalId = hospitalId;
                t.AllotId = allots?.FirstOrDefault(w => w.Year == t.Year && t.Month == w.Month)?.ID ?? 0;
                t.CreateTime = dateTime;
            });

            #region his_import_baiscnorm 人均绩效计算
            logger.LogInformation($"人均绩效计算");
            Dictionary<string, string> basicDict = new Dictionary<string, string>
            {
                { "临床科室主任人均绩效", UnitType.医生组.ToString() },
                { "医技科室主任人均绩效", UnitType.医技组.ToString() },
                { "护士长人均绩效", UnitType.护理组.ToString() },
            };
            List<his_import_baiscnorm> baiscnorms = new List<his_import_baiscnorm>();
            foreach (var item in basicDict)
            {
                basicnormData.AddRange(data.Where(t => t.UnitType == item.Value).GroupBy(t => new { t.AllotId, t.Year, t.Month }).Select(t => new his_import_baiscnorm
                {
                    HospitalId = hospitalId,
                    AllotId = t.Key.AllotId ?? 0,
                    Year = t.Key.Year,
                    Month = t.Key.Month,
                    PositionName = item.Key,
                    TotelValue = t.Sum(s => s.RealGiveFee),
                    TotelNumber = t.Sum(s => s.Basics),
                    AvgValue = t.Sum(s => s.Basics) == 0 ? 0 : t.Sum(s => s.RealGiveFee) / t.Sum(s => s.Basics),
                    CreateTime = dateTime
                }));
            }
            #endregion his_import_baiscnorm

            #region his_import_clinic 科主任护士长绩效
            logger.LogInformation($"科主任护士长绩效");
            foreach (var item in data.Select(t => new { t.Year, t.Month }).Distinct())
            {
                hisimportclinicRepository.DeleteFromQuery(t => t.HospitalId == hospitalId && t.Year == item.Year && t.Month == item.Month);
            }
            hisimportclinicRepository.InsertExecute(data.Where(t => t.Year != 0 && t.Month != 0));
            #endregion his_import_clinic
        }

        private void ImporClinicTag(ISheet sheet, List<string> columns, int hospitalId)
        {
            var data = new List<report_performance_person_tags>();
            for (int rowIndex = 1; rowIndex < sheet.LastRowNum + 1; rowIndex++)
            {
                var row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                report_performance_person_tags importdata = new report_performance_person_tags
                {
                    UnitType = GetCellValue<string>(row, columns, "核算单元类型"),
                    AccountingUnit = GetCellValue<string>(row, columns, "核算单元"),
                    PersonnelName = GetCellValue<string>(row, columns, "姓名"),
                    PersonnelNumber = GetCellValue<string>(row, columns, "工号"),
                    Tag1 = GetCellValue<string>(row, columns, "Tag1"),
                    Tag2 = GetCellValue<string>(row, columns, "Tag2"),
                    Tag3 = GetCellValue<string>(row, columns, "Tag3"),
                    Tag4 = GetCellValue<string>(row, columns, "Tag4"),
                    Tag5 = GetCellValue<string>(row, columns, "Tag5"),
                };
                data.Add(importdata);
            }
            logger.LogInformation($"{sheet.SheetName}共有{sheet.LastRowNum}行数据，读取到{data.Count}条记录");
            if (data == null || !data.Any(t => !string.IsNullOrEmpty(t.UnitType) && !string.IsNullOrEmpty(t.AccountingUnit))) return;

            var dateTime = DateTime.Now;
            data.ForEach(t =>
            {
                t.HospitalId = hospitalId;
                t.CreateTime = dateTime;
            });

            reportperformancepersontagsRepository.DeleteFromQuery(t => t.HospitalId == hospitalId);
            reportperformancepersontagsRepository.InsertExecute(data.Where(t => !string.IsNullOrEmpty(t.PersonnelName) && !string.IsNullOrEmpty(t.PersonnelNumber)));
        }

        private List<string> GetColumns(ISheet sheet, string sheetName, out string sourceType)
        {
            sourceType = "";
            foreach (var item in sourceTypes)
            {
                if (item.Value.Contains(sheetName))
                {
                    sourceType = item.Key;
                    break;
                }
            }

            //if (string.IsNullOrEmpty(sourceType)) return new List<string>();

            var columnRow = sheet.GetOrCreate(0);
            return columnRow.GetCellValues();
        }

        private T GetCellValue<T>(IRow row, List<string> columns, string key)
        {
            if (columns.Contains(key) && row.GetCell(columns.IndexOf(key)) != null)
                return row.GetCell(columns.IndexOf(key)).GetCellValue<T>();

            return default;
        }

        private readonly Dictionary<string, string[]> sourceTypes = new Dictionary<string, string[]>
        {
            { "收入", new string[] { "门诊收入", "住院收入" } },
            { "医生工作量", new string[] { "门诊人次", "出院人次", "手术概况", "住院天数", "实际开放总床日数" } },
            { "医技工作量", new string[] { "医技工作量" } },
            { "支出", new string[] { "支出" } },
        };

        #endregion ImportFile && SaveData

        #region 人员、科室标签配置

        public (HandsonTable handsonTable, List<string> colHeaders) GetReportPersonTag(int hospitalId, int allotId)
        {
            var hos = _hospitalRepository.GetEntity(t => t.ID == hospitalId);
            var columnHeaders = _computeService.CustomColumnHeaders(hos, "/result/all_employee");
            var type = (int)SheetType.Unidentifiable;
            var cols = columnHeaders.Where(w => w.States == 1).Select(t => t.Alias).ToArray();
            var permissions = columnHeaders.Where(w => w.States == 1).Select(t => new collect_permission { HeadName = t.Alias, Visible = 1 }).ToList();
            var result = new HandsonTable(type, cols, permissions);

            var pdata = perforPeremployeeRepository.GetEntities(t => t.HospitalId == hospitalId && t.AllotId == allotId);
            var tdata = reportperformancepersontagsRepository.GetEntities(t => t.HospitalId == hospitalId)?.OrderBy(t => ConvertHelper.To<long>(t.PersonnelNumber));

            var data = (from t1 in pdata
                        join t2 in tdata
                        on t1.PersonnelNumber equals t2.PersonnelNumber into temp
                        from t in temp.DefaultIfEmpty()
                        orderby t1.Id descending
                        select new
                        {
                            PersonnelNumber = t1.PersonnelNumber,
                            DoctorName = t1.DoctorName,
                            AccountingUnit = t1.AccountingUnit,
                            UnitType = t1.UnitType,
                            JobCategory = t1.JobCategory,
                            Duty = t1.Duty,
                            JobTitle = t1.JobTitle,
                            JobNumber = t1.JobNumber,
                            AttendanceDay = t1.AttendanceDay,
                            ReservedRatio = t1.ReservedRatio,
                            BankCard = t1.BankCard,
                            PermanentStaff = t1.PermanentStaff,
                            Remark = t1.Remark,
                            Reserve01 = t1.Reserve01,
                            Reserve02 = t1.Reserve02,
                            Reserve03 = t1.Reserve03,
                            Reserve04 = t1.Reserve04,
                            Reserve05 = t1.Reserve05,
                            Reserve06 = t1.Reserve06,
                            Reserve07 = t1.Reserve07,
                            Reserve08 = t1.Reserve08,
                            Reserve09 = t1.Reserve09,
                            Reserve10 = t1.Reserve10,
                            Tag1 = t?.Tag1,
                            Tag2 = t?.Tag2,
                            Tag3 = t?.Tag3,
                            Tag4 = t?.Tag4,
                            Tag5 = t?.Tag5,
                        }).Distinct()?.ToList();

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

            var users = new List<sys_user>();
            if (hos?.IsOwnerQuery == 1)
            {
                var userhospitals = perforUserhospitalRepository.GetEntities(w => w.HospitalID == hospitalId);
                if (userhospitals != null && userhospitals.Any())
                {
                    users = perforUserRepository.GetEntities(w => userhospitals.Select(w => w.UserID).Contains(w.ID) && w.IsDelete == 1) ?? new List<sys_user>();
                }
            }
            List<HandsonRowData> rowDatas = new List<HandsonRowData>();
            int i = 0;
            foreach (var item in data)
            {
                var json = JsonHelper.Serialize(item);
                var firstDic = JsonHelper.Deserialize<Dictionary<string, string>>(json);

                var cells = (from conf in columnHeaders
                             join fst in firstDic on conf.Name.ToUpper() equals fst.Key.ToUpper()
                             select new HandsonCellData(conf.Alias, fst.Value)).ToList();
                if (hos?.IsOwnerQuery == 1)
                {
                    //var password = users.FirstOrDefault(w => w.Login == item.PersonnelNumber && w.Department == item.AccountingUnit)?.Password ?? "";
                    var password = "";
                    cells.Add(new HandsonCellData("密码", password));
                }

                rowDatas.Add(new HandsonRowData(i, cells));
                i++;
            }

            result.SetRowData(rowDatas, rowDatas != null);

            var columns = new string[] { "员工工号", "姓名", "核算单元" };
            result.Columns?.ForEach(t =>
            {
                t.Type = "text";
                if (columns.Contains(t.Data))
                    t.ReadOnly = true;
            });
            return (result, result.ColHeaders.ToList());
        }

        public string ReportPersonTagDownload(List<Dictionary<string, object>> rows, List<string> colHeaders, string title)
        {
            var data = new List<Dictionary<string, object>>();
            foreach (var obj in rows)
            {
                Dictionary<string, object> nobj = new Dictionary<string, object>();

                foreach (var item in obj)
                {
                    nobj[item.Key.ToLower()] = item.Value;
                }
                data.Add(nobj);
            }

            var dpath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Files");
            if (!Directory.Exists(dpath)) Directory.CreateDirectory(dpath);

            string filepath = Path.Combine(dpath, $"{title}{DateTime.Now:yyyy年MM月dd日}");
            if (File.Exists(filepath)) File.Delete(filepath);

            using (FileStream fs = new FileStream(filepath, FileMode.OpenOrCreate))
            using (ExcelPackage package = new ExcelPackage(fs))
            {
                var worksheet = package.Workbook.Worksheets.Add(title);

                if (rows != null && rows.Count() > 0)
                {
                    worksheet.SetValue(1, 1, title);

                    for (int col = 0; col < colHeaders.Count; col++)
                    {
                        worksheet.SetValue(2, col + 1, colHeaders[col]);
                    }
                    for (int col = 0; col < colHeaders.Count; col++)
                    {
                        for (int row = 0; row < data.Count(); row++)
                        {
                            var temp = data.ElementAt(row);
                            var value = temp[colHeaders[col]] != null ? temp[colHeaders[col]].ToString() : temp[colHeaders[col]];

                            worksheet.Cells[row + 3, col + 1].Value = value;
                        }
                    }

                    #region 样式设置
                    for (int row = worksheet.Dimension.Start.Row; row <= worksheet.Dimension.End.Row; row++)
                    {
                        worksheet.Row(row).Height = 20;
                        for (int col = worksheet.Dimension.Start.Column; col <= worksheet.Dimension.End.Column; col++)
                        {
                            worksheet.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                            worksheet.Cells[row, col].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                            worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        }
                    }
                    worksheet.Cells[1, 1, 1, colHeaders.Count].Merge = true;
                    worksheet.Cells[1, 1, 1, colHeaders.Count].Style.Font.Bold = true;
                    worksheet.Cells[1, 1, 1, colHeaders.Count].Style.Font.Size = 16;
                    worksheet.Cells[2, 1, 2, colHeaders.Count].Style.Font.Bold = true;
                    worksheet.Row(1).Height = 24;
                    worksheet.View.FreezePanes(3, 1);
                    worksheet.Cells.AutoFitColumns();
                    for (int col = worksheet.Dimension.Start.Column; col <= worksheet.Dimension.End.Column; col++)
                    {
                        worksheet.Column(col).Width = worksheet.Column(col).Width > 20 ? 20 : worksheet.Column(col).Width;
                    }
                    #endregion
                }
                package.Save();
            }
            return filepath;
        }

        public ApiResponse SaveReportPersonTag(int hospitalId, int allotId, int userId, SaveCollectData request)
        {
            var hos = _hospitalRepository.GetEntity(t => t.ID == hospitalId);
            var employees = perforPeremployeeRepository.GetEntities(t => t.AllotId == allotId);
            if (employees == null || !employees.Any()) throw new PerformanceException("人员字典中未保存数据");

            var alias = _computeService.CustomColumnHeaders(hos, "/result/all_employee");
            var dicData = CreateDataRow(hospitalId, request, alias);
            var tags = reportperformancepersontagsRepository.GetEntities(t => t.HospitalId == hospitalId);

            var upEmployees = new List<per_employee>();
            var newTags = new List<report_performance_person_tags>();
            var upTags = new List<report_performance_person_tags>();

            List<Dictionary<string, string>> error = new List<Dictionary<string, string>>();
            int index = 0;
            foreach (var item in dicData)
            {
                index++;

                if (item.TryGetValue("PersonnelNumber", out string personnelNumber) && string.IsNullOrEmpty(personnelNumber))
                {
                    if (item.Values.Any(w => !string.IsNullOrEmpty(w)))
                    {
                        error.Add(new Dictionary<string, string>
                        {
                            { "行号", $"第{index}行" },
                            { "人员工号", item["PersonnelNumber"] },
                            { "姓名", item["DoctorName"] },
                            { "错误原因", "“人员工号”不存在请删除" },
                        });
                    }
                    continue;
                }
                var employee = employees.FirstOrDefault(t => t.PersonnelNumber == item["PersonnelNumber"]);
                if (employee == null)
                {
                    error.Add(new Dictionary<string, string>
                    {
                        { "行号", $"第{index}行" },
                        { "人员工号", item["PersonnelNumber"] },
                        { "姓名", item["DoctorName"] },
                        { "错误原因", "“人员工号”不存在请删除" },
                    });
                    continue;
                }
                else if (item["DoctorName"] != employee.DoctorName)
                {
                    error.Add(new Dictionary<string, string>
                    {
                        { "行号", $"第{index}行" },
                        { "人员工号", item["PersonnelNumber"] },
                        { "姓名", item["DoctorName"] },
                        { "错误原因", "“姓名”字典不一致请修改" },
                    });
                    continue;
                }

                if (!item.ContainsKey(nameof(per_employee.AccountingUnit)))
                    item.Add(nameof(per_employee.AccountingUnit), employee.AccountingUnit);
                else
                    item[nameof(per_employee.AccountingUnit)] = employee.AccountingUnit;

                var json = JsonHelper.Serialize(item);

                #region 人员字典表
                var pdata = JsonHelper.Deserialize<per_employee>(json);
                //employee.PersonnelNumber = pdata.PersonnelNumber;
                //employee.DoctorName = pdata.DoctorName;
                employee.JobNumber = pdata.JobNumber;
                employee.JobCategory = pdata.JobCategory;
                employee.Duty = pdata.Duty;
                employee.JobTitle = pdata.JobTitle;
                employee.AttendanceDay = pdata.AttendanceDay;
                employee.ReservedRatio = pdata.ReservedRatio;
                employee.BankCard = pdata.BankCard;
                employee.PermanentStaff = pdata.PermanentStaff;
                employee.Remark = pdata.Remark;
                employee.Reserve01 = pdata.Reserve01;
                employee.Reserve02 = pdata.Reserve02;
                employee.Reserve03 = pdata.Reserve03;
                employee.Reserve04 = pdata.Reserve04;
                employee.Reserve05 = pdata.Reserve05;
                employee.Reserve06 = pdata.Reserve06;
                employee.Reserve07 = pdata.Reserve07;
                employee.Reserve08 = pdata.Reserve08;
                employee.Reserve09 = pdata.Reserve09;
                employee.Reserve10 = pdata.Reserve10;
                upEmployees.Add(employee);
                #endregion

                #region 人员标签表
                var tdata = JsonHelper.Deserialize<report_performance_person_tags>(json);
                tdata.PersonnelName = item["DoctorName"]?.ToString();
                tdata.CreateTime = DateTime.Now;
                tdata.AccountingUnit = employee.AccountingUnit;
                tdata.UnitType = employee.UnitType;
                tdata.UnitType = employee.UnitType;

                var tag = tags.FirstOrDefault(t => t.PersonnelNumber == item["PersonnelNumber"]);
                if (tag != null)
                {
                    tag.AccountingUnit = employee.AccountingUnit;
                    tag.AccountingUnit = employee.AccountingUnit;
                    tag.UnitType = employee.UnitType;
                    tag.PersonnelName = tdata.PersonnelName;
                    tag.PersonnelNumber = tdata.PersonnelNumber;
                    tag.Tag1 = tdata.Tag1;
                    tag.Tag2 = tdata.Tag2;
                    tag.Tag3 = tdata.Tag3;
                    tag.Tag4 = tdata.Tag4;
                    tag.Tag5 = tdata.Tag5;
                    upTags.Add(tag);
                }
                else
                    newTags.Add(tdata);
                #endregion
            }
            if (error.Count > 0)
                return new ApiResponse(ResponseType.WarningTable, "验证不通过,当前操作已拒绝", error);

            if (upEmployees != null && upEmployees.Any())
                perforPeremployeeRepository.UpdateRange(upEmployees.ToArray());
            if (newTags != null && newTags.Any())
                reportperformancepersontagsRepository.AddRange(newTags.ToArray());
            if (upTags != null && upTags.Any())
                reportperformancepersontagsRepository.UpdateRange(upTags.ToArray());
            //个人绩效查询用户添加
            personService.SaveQueryRole(hospitalId, userId, dicData);

            var allot = perallotRepository.GetEntity(t => t.ID == allotId);
            allot.IsModifyConfig = 1;
            perallotRepository.Update(allot);
            return new ApiResponse(ResponseType.OK);
        }

        public HandsonTableBase GetReportTag(int hospitalId)
        {
            HandsonTableBase result = new HandsonTableBase()
            {
                Columns = ReportTag.Select(t => new HandsonColumn(t.Value)).ToList(),
                ColHeaders = ReportTag.Select(t => t.Value).ToList(),
            };

            var data = reportperformancetagsRepository.GetEntities(t => t.HospitalId == hospitalId)?.OrderBy(t => t.UnitType);
            if (data == null) return result;

            List<HandsonRowData> rowDatas = new List<HandsonRowData>();
            int i = 0;
            foreach (var item in data)
            {
                var json = JsonHelper.Serialize(item);
                var firstDic = JsonHelper.Deserialize<Dictionary<string, string>>(json);

                var cells = (from conf in ReportTag join fst in firstDic on conf.Key.ToUpper() equals fst.Key.ToUpper() select new HandsonCellData(conf.Value, fst.Value)).ToList();

                rowDatas.Add(new HandsonRowData(i, cells));
                i++;
            }
            result.SetRowData(rowDatas);
            return result;
        }

        public void SaveReportTag(int hospitalId, SaveCollectData request)
        {
            var hos = _hospitalRepository.GetEntity(t => t.ID == hospitalId);
            var alias = ComputeConfig.GetAllPersonnelTags(hos?.IsOwnerQuery == 1);
            var dicData = CreateDataRow(hospitalId, request, alias);

            List<report_performance_tags> report = new List<report_performance_tags>();
            foreach (var item in dicData)
            {
                var json = JsonHelper.Serialize(item);
                var data = JsonHelper.Deserialize<report_performance_tags>(json);
                if (!string.IsNullOrEmpty(data.UnitType) && !string.IsNullOrEmpty(data.AccountingUnit) && !string.IsNullOrEmpty(data.Tag1))
                {
                    data.CreateTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    report.Add(data);
                }
            }

            reportperformancetagsRepository.Execute("delete from report_performance_tags where HospitalId=@hospitalId", new { hospitalId });
            reportperformancetagsRepository.AddRange(report.ToArray());
        }


        public static Dictionary<string, string> ReportTag { get; } = new Dictionary<string, string>
        {
            {nameof(report_performance_tags.UnitType), "核算组别"},
            {nameof(report_performance_tags.AccountingUnit), "核算单元"},
            {nameof(report_performance_tags.Tag1), "绩效发放情况"},
            {nameof(report_performance_tags.Tag2), "当月绩效权重"},
            {nameof(report_performance_tags.Tag3), "重点群体对比1"},
            {nameof(report_performance_tags.Tag4), "重点群体对比2"},
            {nameof(report_performance_tags.Tag5), "重点群体对比5"},
        };

        private List<Dictionary<string, string>> CreateDataRow(int hospitalId, SaveCollectData request, List<cof_alias> alias)
        {
            List<Dictionary<string, string>> allData = new List<Dictionary<string, string>>();

            for (int r = 0; r < request.Data.Length; r++)
            {
                // 创建固定数据列
                Dictionary<string, string> baseData = CreateBaseData(request, alias, r);
                baseData.Add(nameof(cof_hrp_department.HospitalId), hospitalId.ToString());
                allData.Add(baseData);

            }
            return allData;
        }

        private Dictionary<string, string> CreateBaseData(SaveCollectData request, List<cof_alias> alias, int rownumber)
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            for (int c = 0; c < request.ColHeaders.Length; c++)
            {
                var header = request.ColHeaders[c];
                var first = alias.FirstOrDefault(w => w.Alias.ToLower() == header.ToLower());
                if (first!=null && !default(KeyValuePair<string, string>).Equals(first)
                    && !result.ContainsKey(header)
                    && request.Data[rownumber].Length > c)
                {
                    result.Add(first.Name, request.Data[rownumber][c]);
                }
            }

            return result;
        }
        #endregion
    }
}
