//using Dapper;
//using Microsoft.AspNetCore.Hosting;
//using Microsoft.AspNetCore.SignalR;
//using Microsoft.Extensions.Logging;
//using Microsoft.Extensions.Options;
//using NPOI.HSSF.UserModel;
//using NPOI.HSSF.Util;
//using NPOI.SS.UserModel;
//using NPOI.SS.Util;
//using NPOI.XSSF.UserModel;
//using Performance.DtoModels;
//using Performance.DtoModels.AppSettings;
//using Performance.EntityModels;
//using Performance.Infrastructure;
//using Performance.Repository;
//using System;
//using System.Collections.Generic;
//using System.IO;
//using System.Linq;
//using System.Text;
//using System.Text.RegularExpressions;

//namespace Performance.Services
//{
//    /// <summary>
//    /// 数据抽取服务
//    /// </summary>
//    public class NewExtractService : IAutoInjection
//    {
//        #region 
//        private readonly ILogger<ExtractService> logger;
//        private readonly IWebHostEnvironment environment;
//        private readonly IEmailService emailService;
//        private readonly PerSheetService perSheetService;
//        private readonly PerforHospitalRepository perforHospitalRepository;
//        private readonly PerforHospitalconfigRepository perforHospitalconfigRepository;
//        private readonly PerforModmoduleRepository perforModmoduleRepository;
//        private readonly PerforModextractRepository perforModextractRepository;
//        private readonly PerforModitemRepository perforModitemRepository;
//        private readonly PerforModspecialRepository perforModspecialRepository;
//        private readonly PerforPerallotRepository perforPerallotRepository;
//        private readonly PerforPersheetRepository perforPersheetRepository;
//        private readonly PerforImemployeeRepository perforImemployeeRepository;
//        private readonly PerforImaccountbasicRepository perforImaccountbasicRepository;
//        private readonly PerforImspecialunitRepository perforImspecialunitRepository;
//        private readonly PerforImdataRepository perforImdataRepository;
//        private readonly LogManageService logManageService;

//        private IWorkbook workbook = null;
//        private ICellStyle style;
//        private int AllotId;

//        public NewExtractService(ILogger<ExtractService> logger,
//            IWebHostEnvironment environment,
//            IEmailService emailService,
//            PerSheetService perSheetService,
//            PerforHospitalRepository perforHospitalRepository,
//            PerforHospitalconfigRepository perforHospitalconfigRepository,
//            PerforModmoduleRepository perforModmoduleRepository,
//            PerforModextractRepository perforModextractRepository,
//            PerforModitemRepository perforModitemRepository,
//            PerforModspecialRepository perforModspecialRepository,
//            PerforPerallotRepository perforPerallotRepository,
//            PerforPersheetRepository perforPersheetRepository,
//            PerforImemployeeRepository perforImemployeeRepository,
//            PerforImaccountbasicRepository perforImaccountbasicRepository,
//            PerforImspecialunitRepository perforImspecialunitRepository,
//            PerforImdataRepository perforImdataRepository,
//            LogManageService logManageService)
//        {
//            this.logger = logger;
//            this.environment = environment;
//            this.emailService = emailService;
//            this.perSheetService = perSheetService;
//            this.perforHospitalRepository = perforHospitalRepository;
//            this.perforHospitalconfigRepository = perforHospitalconfigRepository;
//            this.perforModmoduleRepository = perforModmoduleRepository;
//            this.perforModextractRepository = perforModextractRepository;
//            this.perforModitemRepository = perforModitemRepository;
//            this.perforModspecialRepository = perforModspecialRepository;
//            this.perforPerallotRepository = perforPerallotRepository;
//            this.perforPersheetRepository = perforPersheetRepository;
//            this.perforImemployeeRepository = perforImemployeeRepository;
//            this.perforImaccountbasicRepository = perforImaccountbasicRepository;
//            this.perforImspecialunitRepository = perforImspecialunitRepository;
//            this.perforImdataRepository = perforImdataRepository;
//            this.logManageService = logManageService;
//        }
//        #endregion

//        #region 改版抽取

//        public string ExtractData(int allotId, string email, int hospitalId)
//        {
//            AllotId = allotId;

//            // 获取绩效信息
//            var allot = perforPerallotRepository.GetEntity(t => t.ID == allotId);
//            // 获取医院信息
//            var hospital = perforHospitalRepository.GetEntity(t => t.ID == hospitalId);
//            // 获取医院配置信息
//            var hospitalConfigList = perforHospitalconfigRepository.GetEntities(t => t.HospitalId == hospitalId);

//            var statesArray = new int[] { (int)AllotStates.GenerateSucceed, (int)AllotStates.Archive };
//            var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId && statesArray.Contains(t.States));
//            var allotLast = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).First();
//            // 获取当前医院模版信息
//            var modulesList = perforModmoduleRepository.GetEntities(t => t.HospitalId == hospitalId);
//            // 获取模板项
//            var moduleIdList = modulesList.Select(t => t.Id).ToList();
//            var itemsList = perforModitemRepository.GetEntities(t => t.ModuleId.HasValue && moduleIdList.Contains(t.ModuleId.Value));
//            var extractIdList = itemsList.Select(t => t.ExtractId).Union(modulesList.Select(t => t.ExtractId)).Distinct().ToList();

//            var extractList = perforModextractRepository.GetEntities(t => extractIdList.Contains(t.Id));

//            try
//            {
//                var (tempPath, newPath) = CopyOriginalFile(hospitalId);
//                workbook = new XSSFWorkbook(tempPath);

//                CreateNotExistSheet(modulesList, workbook);

//                style = CellStyle.CreateCellStyle(workbook, StyleType.数据);

//                List<AccountUnitEntity> unitList = new List<AccountUnitEntity>();
//                if (allotLast != null)
//                    unitList = perforImdataRepository.GetAccountUnit(allotLast.ID).ToList();

//                for (int i = 0; i < workbook.NumberOfSheets; i++)
//                {
//                    var sheet = workbook.GetSheetAt(i);
//                    var sheetType = perSheetService.GetSheetType(sheet.SheetName);
//                    if (sheetType == SheetType.Unidentifiable) continue;

//                    var sheetRead = PerSheetDataFactory.GetDataRead(sheetType);
//                    switch (sheetType)
//                    {
//                        case SheetType.Employee:
//                            if (allotLast != null)
//                                WriteEmployee(sheet, allotLast.ID, sheetRead);
//                            break;
//                        case SheetType.AccountBasic:
//                            if (allotLast != null)
//                                WriteAccountBasic(sheet, allotLast.ID, sheetRead);
//                            break;
//                        case SheetType.SpecialUnit:
//                            WriteSpecialUnit(sheet, hospital.ID, allot, sheetRead, hospitalConfigList);
//                            break;
//                        case SheetType.Income:
//                            WriteIncome(sheet, hospital.ID, unitList, sheetRead, allot, modulesList, hospitalConfigList);
//                            break;
//                        case SheetType.OtherIncome:
//                            WriteOtherIncome(sheet, hospital.ID, unitList, sheetRead, allot, modulesList, hospitalConfigList);
//                            break;
//                        case SheetType.Expend:
//                            WriteExpend(sheet, hospital.ID, sheetRead);
//                            break;
//                        case SheetType.Workload:
//                            WriteWorkload(sheet, hospital.ID, unitList, sheetRead, allot, modulesList, hospitalConfigList);
//                            break;
//                    }
//                }
//                using (FileStream file = new FileStream(newPath, FileMode.OpenOrCreate))
//                {
//                    workbook.Write(file);
//                }
//                logManageService.WriteMsg("提取绩效数据", $"{hospital.HosName}HIS数据提取成功,文件路径：{newPath}。", 5, allotId, "ReceiveMessage");
//                logger.LogInformation($"提取绩效数据 {hospital.HosName}HIS数据提取成功,文件路径：{newPath}。");
//                SendEmail(email, newPath, $"{hospital.HosName}HIS数据提取成功", $"{hospital.HosName}在{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}成功提取。");
//                return newPath;
//            }
//            catch (Exception ex)
//            {
//                logManageService.WriteMsg("提取数据异常", $"数据写入出现异常", 4, allotId, "ReceiveMessage");
//                logger.LogError($"提取绩效数据异常 数据写入出现异常{ex.ToString()}");
//                SendEmail(email, "", $"{hospital.HosName}HIS数据提取失败", $"{hospital.HosName}提取数据过程中出现异常情况，我们将尽快解决问题。给您带来的不便我们深感歉意！");
//                throw ex;
//            }
//            finally
//            {
//                allot.IsExtracting = null;
//                perforPerallotRepository.Update(allot);
//                workbook.Close();
//                GC.Collect();
//            }
//        }

//        private static void CreateNotExistSheet(List<mod_module> modulesList, IWorkbook workbook)
//        {
//            SortedDictionary<string, int> pairs = new SortedDictionary<string, int>();
//            for (int i = 0; i < workbook.NumberOfSheets; i++)
//            {
//                pairs.Add(workbook.GetSheetAt(i).SheetName, i);
//            }

//            int sheetIndex = 0;
//            foreach (var module in modulesList.Where(t => t.SheetType == (int)SheetType.Income)?.OrderBy(t => t.ModuleName))
//            {
//                var sheet = workbook.GetSheet(module.ModuleName);
//                if (sheet == null)
//                {
//                    string[] keyArray = new string[] { "开单", "执行" };
//                    if (keyArray.Any(key => module.ModuleName.Contains(key)))
//                    {
//                        var item = pairs.Where(t => t.Key.StartsWith("1.")).OrderByDescending(t => t.Key).First();
//                        if (sheetIndex == 0)
//                            sheetIndex = item.Value + 1;
//                        var copysheet = workbook.GetSheet(item.Key);
//                        var newSheet = copysheet.CopySheet(item.Key, true);
//                        workbook.SetSheetOrder(newSheet.SheetName, sheetIndex);
//                        workbook.SetSheetName(sheetIndex, module.ModuleName);
//                        sheetIndex++;
//                    }
//                }
//            }
//        }

//        private void WriteOtherIncome(ISheet sheet, int hospitalId, List<AccountUnitEntity> unitList, IPerSheetDataRead sheetRead, per_allot allot, List<mod_module> moduleList, List<sys_hospitalconfig> hospitalConfigList)
//        {
//            var module = perforModmoduleRepository.GetEntity(t => t.SheetType == (int)SheetType.OtherIncome && t.HospitalId == hospitalId);
//            var itemList = perforModitemRepository.GetEntities(t => t.ModuleId == module.Id);
//            if (itemList == null || !itemList.Any()) return;

//            var nurseFactor = sheet.GetRow(sheetRead.Point.AccountingUnit.First(t => t.UnitType == "护理组").FactorRow.Value);
//            var doctorFactor = sheet.GetRow(sheetRead.Point.AccountingUnit.First(t => t.UnitType == "医生组").FactorRow.Value);
//            var technicianFactor = sheet.GetRow(sheetRead.Point.AccountingUnit.First(t => t.UnitType == "医技组").FactorRow.Value);
//            var head = sheet.GetRow(sheetRead.Point.HeaderFirstRowNum.Value);

//            int cellStartIndex = sheetRead.Point.HeaderFirstCellNum.Value + 4;

//            logManageService.WriteMsg("提取绩效数据", $"写入列头信息 -- {module.ModuleName}", 1, AllotId, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 写入列头信息 -- {module.ModuleName}");
//            //写入列头信息
//            foreach (var item in itemList)
//            {
//                var headcell = head.GetCell(cellStartIndex) ?? head.CreateCell(cellStartIndex);
//                headcell.SetCellValue(item.ItemName);
//                headcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.列头);

//                var doctorcell = doctorFactor.GetCell(cellStartIndex) ?? doctorFactor.CreateCell(cellStartIndex);
//                doctorcell.SetCellValue(item.FactorValue1 != null ? (double)item.FactorValue1 : 0);
//                doctorcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);

//                var nursecell = nurseFactor.GetCell(cellStartIndex) ?? nurseFactor.CreateCell(cellStartIndex);
//                nursecell.SetCellValue(item.FactorValue2 != null ? (double)item.FactorValue2 : 0);
//                nursecell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);

//                var techniciancell = technicianFactor.GetCell(cellStartIndex) ?? technicianFactor.CreateCell(cellStartIndex);
//                techniciancell.SetCellValue(item.FactorValue3 != null ? (double)item.FactorValue3 : 0);
//                techniciancell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);
//                cellStartIndex++;
//            }

//            //查询数据
//            var extractIdList = itemList.Select(t => t.ExtractId).Distinct().ToList();
//            var extractList = perforModextractRepository.GetEntities(t => extractIdList.Contains(t.Id));
//            if (extractList == null || extractList.Count == 0) return;

//            List<ExtractDto> allExtract = new List<ExtractDto>();
//            foreach (var item in extractList)
//            {
//                var category = itemList.Where(t => t.ExtractId == item.Id);
//                if (category == null || category.Count() == 0) continue;
//                foreach (var moditem in category)
//                {
//                    logManageService.WriteMsg("提取绩效数据", $"执行SQL脚本获取数据 -- {module.ModuleName}", 1, AllotId, "ReceiveMessage");
//                    logger.LogInformation($"提取绩效数据 执行SQL脚本获取数据 -- {module.ModuleName}");

//                    var config = hospitalConfigList.FirstOrDefault(t => t.Id == moditem.ConfigId);
//                    if (config == null) continue;
//                    var result = QueryDatabase(config, item, allot, moditem.ItemName);
//                    if (result != null)
//                        allExtract.AddRange(result);
//                }
//            }

//            var specialHead = new List<string>();
//            var extractHead = allExtract?.Select(t => t.Category);
//            if (extractHead != null && extractHead.Any())
//            {
//                specialHead = itemList.Select(t => t.ItemName).Intersect(extractHead.Distinct())?.ToList();
//            }

//            logManageService.WriteMsg("提取绩效数据", $"填充数据 -- {module.ModuleName}", 1, AllotId, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据  填充数据 -- {module.ModuleName}");
//            //写入数据
//            var rowIndex = sheetRead.Point.HeaderFirstRowNum.Value + 1;
//            foreach (var department in allExtract.Select(t => t.Department).Distinct())
//            {
//                var row = sheet.CreateRow(rowIndex);
//                for (int i = head.FirstCellNum; i < head.LastCellNum; i++)
//                {
//                    var headName = head.GetCell(i).StringCellValue;
//                    var newCell = row.CreateCell(i);
//                    if (headName.Replace("\n", "") == "核算单元（医生组）")
//                    {
//                        var dept = unitList.FirstOrDefault(t => t.SheetName == sheet.SheetName && t.Department == department && t.UnitType == 1)?.AccountingUnit;
//                        newCell.SetCellValue(dept ?? "");
//                        newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                    }
//                    else if (headName.Replace("\n", "") == "核算单元（护理组）")
//                    {
//                        var dept = unitList.FirstOrDefault(t => t.SheetName == sheet.SheetName && t.Department == department && t.UnitType == 2)?.AccountingUnit;
//                        newCell.SetCellValue(dept ?? "");
//                        newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                    }
//                    else if (headName.Replace("\n", "") == "核算单元（医技组）")
//                    {
//                        var dept = unitList.FirstOrDefault(t => t.SheetName == sheet.SheetName && t.Department == department && t.UnitType == 3)?.AccountingUnit;
//                        newCell.SetCellValue(dept ?? "");
//                        newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                    }
//                    else if (headName == "科室名称")
//                    {
//                        newCell.SetCellValue(department ?? "");
//                        newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                    }
//                    else
//                    {
//                        var value = allExtract.FirstOrDefault(t => t.Department == department && t.Category == headName)?.Value;
//                        value = value == 0 ? null : value;
//                        OutToExcelCell(newCell, value);
//                        //newCell.SetCellValue(value == null || value == 0 ? "" : value.ToString());
//                        newCell.CellStyle = style;
//                    }
//                }
//                rowIndex++;
//            }
//        }

//        private void WriteIncome(ISheet sheet, int hospitalId, List<AccountUnitEntity> unitList, IPerSheetDataRead sheetRead, per_allot allot, List<mod_module> moduleList, List<sys_hospitalconfig> hospitalConfigList)
//        {
//            var module = moduleList.FirstOrDefault(t => t.ModuleName == sheet.SheetName && t.HospitalId == hospitalId);
//            if (module == null) return;

//            var itemList = perforModitemRepository.GetEntities(t => t.ModuleId == module.Id);
//            if (itemList == null || !itemList.Any()) return;

//            var nurseFactor = sheet.GetRow(sheetRead.Point.AccountingUnit.First(t => t.UnitType == "护理组").FactorRow.Value);
//            var doctorFactor = sheet.GetRow(sheetRead.Point.AccountingUnit.First(t => t.UnitType == "医生组").FactorRow.Value);
//            var technicianFactor = sheet.GetRow(sheetRead.Point.AccountingUnit.First(t => t.UnitType == "医技组").FactorRow.Value);
//            var head = GetOrCreate(sheet, sheetRead.Point.HeaderFirstRowNum.Value);

//            logManageService.WriteMsg("提取绩效数据", $"写入列头信息 -- {module.ModuleName}", 1, AllotId, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 写入列头信息 -- {module.ModuleName}");
//            //写入列头信息
//            int cellStartIndex = sheetRead.Point.HeaderFirstCellNum.Value + 4;
//            foreach (var item in itemList)
//            {
//                var headcell = GetOrCreate(head, cellStartIndex);
//                headcell.SetCellValue(item.ItemName);
//                headcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.列头);

//                var doctorcell = GetOrCreate(doctorFactor, cellStartIndex);
//                doctorcell.SetCellValue(item.FactorValue1 != null ? (double)item.FactorValue1 : 0);
//                doctorcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);

//                var nursecell = GetOrCreate(nurseFactor, cellStartIndex);
//                nursecell.SetCellValue(item.FactorValue2 != null ? (double)item.FactorValue2 : 0);
//                nursecell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);

//                var techniciancell = GetOrCreate(technicianFactor, cellStartIndex);
//                techniciancell.SetCellValue(item.FactorValue3 != null ? (double)item.FactorValue3 : 0);
//                techniciancell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);
//                cellStartIndex++;
//            }
//            //查询数据
//            var extractList = perforModextractRepository.GetEntities(t => module.ExtractId == t.Id);
//            if (extractList == null || extractList.Count == 0) return;

//            List<ExtractDto> allExtract = new List<ExtractDto>();
//            foreach (var item in extractList)
//            {
//                logManageService.WriteMsg("提取绩效数据", $"执行SQL脚本获取数据 -- {module.ModuleName}", 1, AllotId, "ReceiveMessage");
//                logger.LogInformation($"提取绩效数据 执行SQL脚本获取数据 -- {module.ModuleName}");

//                var config = hospitalConfigList.FirstOrDefault(t => t.Id == module.ConfigId);
//                if (config == null) continue;
//                var result = QueryDatabase(config, item, allot);
//                if (result != null)
//                    allExtract.AddRange(result);
//            }

//            logManageService.WriteMsg("提取绩效数据", $"填充数据 -- {module.ModuleName}", 1, AllotId, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 填充数据 -- {module.ModuleName}");
//            //写入数据
//            var rowIndex = sheetRead.Point.HeaderFirstRowNum.Value + 1;
//            foreach (var department in allExtract.Select(t => t.Department).Distinct())
//            {
//                var row = sheet.CreateRow(rowIndex);
//                for (int i = head.FirstCellNum; i < head.LastCellNum; i++)
//                {
//                    var headName = head.GetCell(i).StringCellValue;
//                    var newCell = row.CreateCell(i);
//                    if (headName.Replace("\n", "") == "核算单元（医生组）")
//                    {
//                        var dept = unitList.FirstOrDefault(t => t.SheetName == sheet.SheetName && t.Department == department && t.UnitType == 1)?.AccountingUnit;
//                        newCell.SetCellValue(dept ?? "");
//                        newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                    }
//                    else if (headName.Replace("\n", "") == "核算单元（护理组）")
//                    {
//                        var dept = unitList.FirstOrDefault(t => t.SheetName == sheet.SheetName && t.Department == department && t.UnitType == 2)?.AccountingUnit;
//                        newCell.SetCellValue(dept ?? "");
//                        newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                    }
//                    else if (headName.Replace("\n", "") == "核算单元（医技组）")
//                    {
//                        var dept = unitList.FirstOrDefault(t => t.SheetName == sheet.SheetName && t.Department == department && t.UnitType == 3)?.AccountingUnit;
//                        newCell.SetCellValue(dept ?? "");
//                        newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                    }
//                    else if (headName == "科室名称")
//                    {
//                        newCell.SetCellValue(department ?? "");
//                        newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                    }
//                    else
//                    {
//                        var value = allExtract.FirstOrDefault(t => t.Department == department && t.Category == headName)?.Value;
//                        value = value == 0 ? null : value;
//                        OutToExcelCell(newCell, value);
//                        //newCell.SetCellValue(value == null || value == 0 ? "" : value.ToString());
//                        newCell.CellStyle = style;
//                    }
//                }
//                rowIndex++;
//            }
//        }

//        private ICell GetOrCreate(IRow row, int index)
//        {
//            var cell = row.GetCell(index);
//            if (cell == null)
//                cell = row.CreateCell(index);

//            //cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Orange.Index;
//            return cell;
//        }

//        private IRow GetOrCreate(ISheet sheet, int index)
//        {
//            var row = sheet.GetRow(index);
//            if (row == null)
//                row = sheet.CreateRow(index);

//            return row;
//        }

//        private Dictionary<string, string> GetParameters(per_allot allot)
//        {
//            DateTime beginTime = new DateTime(allot.Year, allot.Month, 1);

//            Dictionary<string, string> pairs = new Dictionary<string, string>
//            {
//                { "@beginTime", $"'{beginTime.ToString("yyyy-MM-dd")}'" },
//                { "@endTime", $"'{beginTime.AddMonths(1).ToString("yyyy-MM-dd")}'"},
//            };
//            return pairs;
//        }

//        private void WriteWorkload(ISheet sheet, int hospitalId, List<AccountUnitEntity> unitList, IPerSheetDataRead sheetRead, per_allot allot, List<mod_module> moduleList, List<sys_hospitalconfig> hospitalConfigList)
//        {
//            var module = moduleList.FirstOrDefault(t => t.ModuleName == sheet.SheetName && t.HospitalId == hospitalId);
//            if (module == null) return;

//            var itemList = perforModitemRepository.GetEntities(t => t.ModuleId == module.Id);
//            if (itemList == null || !itemList.Any()) return;

//            var head = GetOrCreate(sheet, sheetRead.Point.HeaderFirstRowNum.Value + 0);
//            var factor = GetOrCreate(sheet, sheetRead.Point.HeaderFirstRowNum.Value + 1);

//            logManageService.WriteMsg("提取绩效数据", $"写入列头信息 -- {module.ModuleName}", 1, AllotId, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 写入列头信息 -- {module.ModuleName}");
//            //写入列头信息
//            int cellStartIndex = sheetRead.Point.HeaderFirstCellNum.Value + 2;
//            foreach (var item in itemList)
//            {
//                var headcell = GetOrCreate(head, cellStartIndex);
//                headcell.SetCellValue(item.ItemName);
//                headcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.列头);

//                var doctorcell = GetOrCreate(factor, cellStartIndex);
//                doctorcell.SetCellValue(item.FactorValue1 != null ? (double)item.FactorValue1 : 0);
//                doctorcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.列头, CellFormat.数字2);
//                cellStartIndex++;
//            }
//            //查询数据
//            var extractIdList = itemList.Select(t => t.ExtractId).Distinct().ToList();
//            var extractList = perforModextractRepository.GetEntities(t => extractIdList.Contains(t.Id));
//            if (extractList == null || extractList.Count == 0) return;

//            List<ExtractDto> allExtract = new List<ExtractDto>();
//            foreach (var item in extractList)
//            {
//                var category = itemList.Where(t => t.ExtractId == item.Id);
//                if (category == null || category.Count() == 0) continue;
//                foreach (var moditem in category)
//                {
//                    logManageService.WriteMsg("提取绩效数据", $"执行SQL脚本获取数据 -- {module.ModuleName}", 1, AllotId, "ReceiveMessage");
//                    logger.LogInformation($"提取绩效数据 执行SQL脚本获取数据 -- {module.ModuleName}，");

//                    var config = hospitalConfigList.FirstOrDefault(t => t.Id == moditem.ConfigId);
//                    if (config == null) continue;
//                    var result = QueryDatabase(config, item, allot, moditem.ItemName);
//                    if (result != null)
//                        allExtract.AddRange(result);
//                }
//            }

//            var specialHead = new List<string>();
//            var extractHead = allExtract?.Select(t => t.Category);
//            if (extractHead != null && extractHead.Any())
//            {
//                specialHead = itemList.Select(t => t.ItemName).Intersect(extractHead.Distinct())?.ToList();
//            }

//            logManageService.WriteMsg("提取绩效数据", $"填充数据 -- {module.ModuleName}", 1, AllotId, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 填充数据 -- {module.ModuleName}");
//            //写入数据
//            var rowIndex = sheetRead.Point.HeaderFirstRowNum.Value + 2;
//            foreach (var department in allExtract.Select(t => t.Department).Distinct())
//            {
//                var row = sheet.CreateRow(rowIndex);
//                for (int i = head.FirstCellNum; i < head.LastCellNum; i++)
//                {
//                    var headName = head.GetCell(i).StringCellValue;
//                    var newCell = row.CreateCell(i);
//                    if (headName == "核算单元")
//                    {
//                        var dept = unitList.FirstOrDefault(t => t.SheetName == sheet.SheetName && t.Department == department)?.AccountingUnit;
//                        newCell.SetCellValue(dept ?? "");
//                        newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                    }
//                    else if (headName == "科室名称")
//                    {
//                        newCell.SetCellValue(department ?? "");
//                        newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                    }
//                    else
//                    {
//                        var extract = allExtract.FirstOrDefault(t => t.Department == department && t.Category == headName);
//                        var value = extract?.Value == 0 ? null : extract?.Value;
//                        OutToExcelCell(newCell, value);
//                        if (specialHead != null && specialHead.Contains(headName))
//                            newCell.CellStyle = style;
//                    }
//                }
//                rowIndex++;
//            }
//        }

//        private List<ExtractDto> QueryDatabase(sys_hospitalconfig config, mod_extract extract, per_allot allot, string category = null)
//        {
//            //var config = hospitalConfigList.FirstOrDefault(t => t.Id == extract.ConfigId);
//            //if (config == null) return null;
//            string executeScript = extract.ExecuteScript;
//            var parameters = GetParameters(allot);
//            using (var connection = ConnectionBuilder.Create((DatabaseType)config.DataBaseType, config.DbSource, config.DbName, config.DbUser, config.DbPassword))
//            {
//                foreach (var item in parameters)
//                {
//                    executeScript = Regex.Replace(executeScript, item.Key, item.Value, RegexOptions.IgnoreCase);
//                }
//                //logManageService.WriteMsg("提取绩效数据", $"SQL脚本:{executeScript}", 1, AllotId, "ReceiveMessage");
//                logger.LogInformation($"提取绩效数据 SQL脚本{executeScript}，");
//                var result = connection.Query<ExtractDto>(executeScript, commandTimeout: 20000);
//                if (result != null && result.Count() > 0)
//                {
//                    if (extract.ExecuteType == 2)
//                    {
//                        foreach (var item in result)
//                        {
//                            item.Category = category;
//                        }
//                    }
//                    return result.ToList();
//                }
//            }
//            return null;
//        }

//        private void WriteExpend(ISheet sheet, int hospitalId, IPerSheetDataRead sheetRead)
//        {
//            var module = perforModmoduleRepository.GetEntity(t => t.SheetType == (int)SheetType.Expend && t.HospitalId == hospitalId);
//            var itemList = perforModitemRepository.GetEntities(t => t.ModuleId == module.Id);
//            if (itemList == null || !itemList.Any()) return;

//            var nurseFactor = sheet.GetRow(sheetRead.Point.AccountingUnit.First(t => t.UnitType == "护理组").FactorRow.Value);
//            var doctorFactor = sheet.GetRow(sheetRead.Point.AccountingUnit.First(t => t.UnitType == "医生组").FactorRow.Value);
//            var technicianFactor = sheet.GetRow(sheetRead.Point.AccountingUnit.First(t => t.UnitType == "医技组").FactorRow.Value);
//            var head = sheet.GetRow(sheetRead.Point.HeaderFirstRowNum.Value);

//            int cellStartIndex = sheetRead.Point.HeaderFirstCellNum.Value + 4;

//            logManageService.WriteMsg("提取绩效数据", $"写入列头信息 -- {module.ModuleName}", 1, AllotId, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 写入列头信息 -- {module.ModuleName}");
//            //写入列头信息
//            foreach (var item in itemList)
//            {
//                var headcell = GetOrCreate(head, cellStartIndex);
//                headcell.SetCellValue(item.ItemName);
//                headcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.列头);

//                var doctorcell = GetOrCreate(doctorFactor, cellStartIndex);
//                doctorcell.SetCellValue(item.FactorValue1 != null ? (double)item.FactorValue1 : 0);
//                doctorcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);

//                var nursecell = GetOrCreate(nurseFactor, cellStartIndex);
//                nursecell.SetCellValue(item.FactorValue2 != null ? (double)item.FactorValue2 : 0);
//                nursecell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);

//                var techniciancell = GetOrCreate(technicianFactor, cellStartIndex);
//                techniciancell.SetCellValue(item.FactorValue3 != null ? (double)item.FactorValue3 : 0);
//                techniciancell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);
//                cellStartIndex++;
//            }
//        }

//        private (string TempPath, string NewPath) CopyOriginalFile(int hospitalId)
//        {
//            string originalPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Template", "医院绩效模板.xlsx");
//            var dpath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Files", $"{hospitalId}", "autoextract");
//            FileHelper.CreateDirectory(dpath);
//            string tempPath = Path.Combine(dpath, $"Template{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx");
//            FileHelper.Copy(originalPath, tempPath);
//            string newPath = Path.Combine(dpath, $"绩效提取数据{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx");

//            return (tempPath, newPath);
//        }

//        private void WriteEmployee(ISheet sheet, int allotLastId, IPerSheetDataRead sheetRead)
//        {
//            var dictionary = new Dictionary<string, Func<im_employee, object>>
//            {
//                { "核算单元", (t) => t.AccountingUnit },
//                { "医生姓名", (t) => t.DoctorName },
//                { "职称", (t) => t.JobTitle },
//                { "绩效基数核算参考对象", (t) => t.FitPeople },
//                { "绩效基数核算系数", (t) => t.FitPeopleRatio },
//                { "人员分类", (t) => t.AccountType },
//                { "岗位系数", (t) => t.PostCoefficient },
//                { "参加工作时间", (t) => t.WorkTime },
//                { "考核得分率", (t) => t.ScoreAverageRate },
//                { "出勤率", (t) => t.Attendance },
//                { "其他绩效", (t) => t.OtherPerfor },
//                { "医院奖罚", (t) => t.Punishment },
//                { "调节系数", (t) => t.Adjust },
//            };

//            logManageService.WriteMsg("提取绩效数据", $"填充数据 -- 人员信息", 1, AllotId, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 填充数据 -- 人员信息");
//            var dataList = perforImemployeeRepository.GetEntities(t => t.AllotID == allotLastId)?.OrderBy(t => t.AccountType).ToList();
//            for (int i = 0; i < dataList.Count; i++)
//            {
//                var headIndex = sheetRead.Point.HeaderFirstRowNum;
//                var cellList = sheet.GetRow(headIndex.Value).Cells;

//                var rowIndex = sheetRead.Point.DataFirstRowNum.Value + i;
//                var importRow = sheet.CreateRow(rowIndex);

//                foreach (var cell in cellList)
//                {
//                    var item = dictionary.FirstOrDefault(t => t.Key == cell.StringCellValue);
//                    var value = item.Value.Invoke(dataList[i]) ?? "";
//                    var newCell = importRow.CreateCell(cell.ColumnIndex);
//                    OutToExcelCell(newCell, value);
//                    newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                }
//            }
//        }

//        private void WriteSpecialUnit(ISheet sheet, int hospitalId, per_allot allot, IPerSheetDataRead sheetRead, List<sys_hospitalconfig> hospitalConfigList)
//        {
//            var dictionary = new Dictionary<string, Func<mod_special, List<ExtractDto>, object>>
//            {
//                { "科室", (special,lastAllot) => special.Department },
//                { "人数", (special,lastAllot) => lastAllot.Where(t=>special.Department == t.Department).Sum(t=>t.Value) },
//                { "量化指标", (special,lastAllot) => special.Target},
//                { "量化指标绩效分值",(special,lastAllot) => special.TargetFactor },
//                { "调节系数", (special,lastAllot) => special.AdjustFactor },
//            };

//            var modDataList = perforModspecialRepository.GetEntities(t => t.HospitalId == hospitalId)?.OrderBy(t => t.Department).ToList();
//            if (modDataList == null || modDataList.Count == 0) return;

//            //List<im_specialunit> allotDataList = new List<im_specialunit>();
//            //if (allotLast != null)
//            //    allotDataList = perforImspecialunitRepository.GetEntities(t => t.AllotID == allotLast.ID);

//            //查询数据
//            var extractIdList = modDataList.Select(t => t.ExtractId).Distinct().ToList();
//            var extractList = perforModextractRepository.GetEntities(t => extractIdList.Contains(t.Id));
//            if (extractList == null || extractList.Count == 0) return;

//            List<ExtractDto> allExtract = new List<ExtractDto>();
//            foreach (var item in extractList)
//            {
//                var category = modDataList.Where(t => t.ExtractId == item.Id);
//                if (category == null || category.Count() == 0) continue;
//                foreach (var moditem in category)
//                {
//                    logManageService.WriteMsg("提取绩效数据", $"执行SQL脚本获取数据 -- 特殊核算单元绩效测算表", 1, AllotId, "ReceiveMessage");
//                    logger.LogInformation($"提取绩效数据 执行SQL脚本获取数据 -- 特殊核算单元绩效测算表，");

//                    var config = hospitalConfigList.FirstOrDefault(t => t.Id == moditem.ConfigId);
//                    if (config == null) continue;
//                    var result = QueryDatabase(config, item, allot, moditem.Target);
//                    if (result != null)
//                        allExtract.AddRange(result);
//                }
//            }

//            //取消合并单元格
//            int mergedCount = sheet.NumMergedRegions;
//            for (int i = mergedCount - 1; i >= 0; i--)
//            {
//                var temp = sheet.GetMergedRegion(i);
//                if (temp.FirstRow > sheetRead.Point.HeaderFirstRowNum)
//                    sheet.RemoveMergedRegion(i);
//            }

//            var modDataGroup = modDataList.GroupBy(t => new { t.Department }).Select(group => new
//            {
//                Department = group.Key.Department,
//                Count = group.Count()
//            })?.OrderBy(t => t.Department);

//            int mergedBegin = sheetRead.Point.DataFirstRowNum.Value;
//            int mergedEnd = sheetRead.Point.DataFirstRowNum.Value;

//            logManageService.WriteMsg("提取绩效数据", $"填充数据 -- 特殊核算单元绩效测算表", 1, AllotId, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 填充数据 -- 特殊核算单元绩效测算表");
//            for (int i = 0; i < modDataList.Count; i++)
//            {
//                var headIndex = sheetRead.Point.HeaderFirstRowNum;
//                var cellList = sheet.GetRow(headIndex.Value).Cells;

//                var rowIndex = sheetRead.Point.DataFirstRowNum.Value + i;
//                var importRow = sheet.CreateRow(rowIndex);

//                int cellIndex = 0;

//                foreach (var cell in cellList)
//                {
//                    object value = null;
//                    if (dictionary.ContainsKey(cell.StringCellValue))
//                    {
//                        var item = dictionary.First(t => t.Key == cell.StringCellValue);
//                        value = item.Value.Invoke(modDataList[i], allExtract) ?? "";

//                        if (item.Key == "科室" && rowIndex == mergedBegin)
//                        {
//                            var count = modDataGroup.First(t => t.Department.ToString() == value.ToString()).Count;
//                            mergedEnd = mergedBegin + count - 1;
//                        }
//                    }

//                    if (!new List<string> { "量化指标", "数量", "量化指标绩效分值" }.Contains(cell.StringCellValue) && rowIndex == mergedBegin)
//                    {
//                        CellRangeAddress region = new CellRangeAddress(mergedBegin, mergedEnd, cellIndex, cellIndex);
//                        sheet.AddMergedRegion(region);     //合并单元格
//                    }

//                    var newCell = importRow.CreateCell(cellIndex);
//                    //newCell.SetCellValue(Verify(value));
//                    OutToExcelCell(newCell, value);
//                    if (dictionary.ContainsKey(cell.StringCellValue))
//                        newCell.CellStyle = style;

//                    cellIndex++;
//                }
//                mergedBegin = mergedEnd + 1;
//            }
//        }

//        private void WriteAccountBasic(ISheet sheet, int allotLastId, IPerSheetDataRead sheetRead)
//        {
//            var dictionary = new Dictionary<string, Func<im_accountbasic, object>>
//            {
//                { "核算单元类型", (t) => t.UnitType },
//                { "核算单元", (t) => t.DoctorAccountingUnit },
//                { "科室名称", (t) => t.Department },
//                { "科主任/护士长人数", (t) => t.DoctorDirectorNumber },
//                { "核算单元人员数量", (t) => t.DoctorNumber },
//                { "预算比例", (t) => t.DoctorBasicFactor },
//                { "倾斜系数", (t) => t.DoctorSlopeFactor },
//                { "效率绩效系数", (t) => t.DoctorEffic },
//                { "规模绩效系数", (t) => t.DoctorScale },
//                { "管理绩效发放系数", (t) => t.DoctorGrant },
//                { "保底绩效参考标准", (t) => t.MinimumReference },
//                { "保底绩效系数", (t) => t.MinimumFactor },
//                { "其他绩效1", (t) => t.DoctorOtherPerfor1 },
//                { "考核得分率", (t) => t.DoctorScoringAverage },
//                { "医院奖罚", (t) => t.DoctorExtra },
//                { "其他绩效2", (t) => t.DoctorOtherPerfor2 },
//                { "调节系数", (t) => t.DoctorAdjustFactor },
//            };

//            logManageService.WriteMsg("提取绩效数据", $"填充数据 -- 临床科室医护绩效测算表", 1, AllotId, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 填充数据 -- 临床科室医护绩效测算表");
//            var dataList = perforImaccountbasicRepository.GetEntities(t => t.AllotID == allotLastId)?.OrderBy(t => t.UnitType).ThenBy(t => t.DoctorAccountingUnit).ToList();
//            for (int i = 0; i < dataList.Count; i++)
//            {
//                var headIndex = sheetRead.Point.HeaderFirstRowNum;
//                var cellList = sheet.GetRow(headIndex.Value).Cells;

//                var rowIndex = sheetRead.Point.DataFirstRowNum.Value + i;
//                var importRow = sheet.CreateRow(rowIndex);

//                foreach (var cell in cellList)
//                {
//                    var item = dictionary.FirstOrDefault(t => t.Key == cell.StringCellValue);
//                    var value = item.Value.Invoke(dataList[i]) ?? "";
//                    if (cell.StringCellValue == "核算单元类型")
//                    {
//                        value = value.ToString() == "1" ? "医生组" : value.ToString() == "2" ? "护理组" : "医技组";
//                    }
//                    var newCell = importRow.CreateCell(cell.ColumnIndex);
//                    OutToExcelCell(newCell, value);
//                    newCell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.默认);
//                }
//            }
//        }

//        #region 校验数据格式
//        /// <summary>
//        /// 校验数据格式，并转换
//        /// </summary>
//        /// <param name="obj"></param>
//        /// <returns></returns>
//        public void OutToExcelCell(ICell cell, object obj)
//        {
//            string value = obj?.ToString() ?? "";
//            try
//            {
//                var type = obj.GetType();
//                switch (type.ToString())
//                {
//                    case "System.String"://字符串类型   
//                        cell.SetCellValue(value);
//                        break;
//                    case "System.DateTime"://日期类型   
//                        DateTime dateV;
//                        DateTime.TryParse(value, out dateV);
//                        cell.SetCellValue(dateV.ToString("yyyy/M/d"));

//                        break;
//                    case "System.Boolean"://布尔型   
//                        bool boolV = false;
//                        bool.TryParse(value, out boolV);
//                        cell.SetCellValue(boolV);
//                        break;
//                    case "System.Int16"://整型   
//                    case "System.Int32":
//                    case "System.Int64":
//                    case "System.Byte":
//                        int intV = 0;
//                        int.TryParse(value, out intV);
//                        cell.SetCellValue(intV);
//                        break;
//                    case "System.Decimal"://浮点型   
//                    case "System.Double":
//                        double doubV = 0;
//                        double.TryParse(value, out doubV);
//                        cell.SetCellValue(doubV);
//                        break;
//                    case "System.DBNull"://空值处理   
//                        cell.SetCellValue("");
//                        break;
//                    default:
//                        cell.SetCellValue("");
//                        break;
//                }
//            }
//            catch
//            {
//                cell.SetCellValue(value);
//            }
//        }
//        #endregion
//        #endregion

//        #region 配置校验
//        /// <summary>
//        /// 配置校验
//        /// </summary>
//        /// <param name="allotId"></param>
//        /// <param name="hospitalId"></param>
//        /// <param name="useTemplate"></param>
//        public string Judge(int allotId, int hospitalId, int useTemplate, out string filePath)
//        {
//            string result = null;
//            filePath = "";
//            try
//            {
//                // 获取绩效信息
//                var allot = perforPerallotRepository.GetEntity(t => t.ID == allotId);
//                if (allot == null)
//                    throw new PerformanceException("AllotID错误");
//                // 获取医院信息
//                var hospital = perforHospitalRepository.GetEntity(t => t.ID == hospitalId);
//                if (hospital == null)
//                    throw new PerformanceException("医院ID错误");
//                // 获取医院配置信息
//                var hospitalConfigList = perforHospitalconfigRepository.GetEntities(t => t.HospitalId == hospitalId);
//                if (hospitalConfigList == null || hospitalConfigList.Count == 0)
//                    throw new PerformanceException("当前医院暂不支持HIS数据抽取");
//                // 获取最近一次绩效
//                var statesArray = new int[] { (int)AllotStates.GenerateSucceed, (int)AllotStates.Archive };
//                var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId && statesArray.Contains(t.States));
//                var allotLast = allotList?.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).First();
//                if (allotLast != null)
//                    filePath = allotLast.Path;
//                // 获取当前医院模版信息
//                var modulesList = perforModmoduleRepository.GetEntities(t => t.HospitalId == hospitalId);
//                if (modulesList == null || modulesList.Count == 0)
//                    throw new PerformanceException("当前医院还未配置模版");
//                // 获取模板项
//                var moduleIdList = modulesList.Select(t => t.Id).ToList();
//                var itemsList = perforModitemRepository.GetEntities(t => t.ModuleId.HasValue && moduleIdList.Contains(t.ModuleId.Value));
//                if (itemsList == null || itemsList.Count == 0)
//                    throw new PerformanceException("当前医院还未配置模版项");
//                // 获取当前模板所有相关抽取SQL语句
//                var extractIdList = itemsList.Select(t => t.ExtractId).Union(modulesList.Select(t => t.ExtractId)).Distinct().ToList();
//                var extractList = perforModextractRepository.GetEntities(t => extractIdList.Contains(t.Id));
//                if (extractList == null || extractList.Count == 0)
//                    throw new PerformanceException("当前医院配置模板无需抽取");
//            }
//            catch (PerformanceException ex)
//            {
//                logger.LogError($"提取绩效数据异常 {ex.ToString()}");
//                result = ex.Message;
//            }
//            catch (Exception ex)
//            {
//                logger.LogError($"提取绩效数据异常 {ex.ToString()}");
//                throw new Exception(ex.Message);
//            }
//            return result;
//        }
//        #endregion

//        #region 发送邮件
//        /// <summary>
//        /// 发送邮件
//        /// </summary>
//        /// <param name="path"></param>
//        /// <param name="subject"></param>
//        /// <param name="body"></param>
//        private void SendEmail(string mail, string path, string subject, string body)
//        {
//            var message = new EmailMessage
//            {
//                To = new List<string> { mail },
//                DisplayName = "溯直健康",
//                Subject = subject,
//                Body = body
//            };
//            if (!string.IsNullOrEmpty(path))
//                message.Attachments = new List<string> { path };
//            emailService.Send(message);
//        }
//        #endregion
//    }
//}
