//using Dapper;
//using Microsoft.AspNetCore.Hosting;
//using Microsoft.Extensions.Logging;
//using NPOI.SS.UserModel;
//using NPOI.SS.Util;
//using NPOI.XSSF.UserModel;
//using Performance.DtoModels;
//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
//{
//    public class DFExtractService : IAutoInjection
//    {
//        #region
//        private readonly ILogger<ExtractService> logger;
//        private readonly IHostingEnvironment 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 PerforRepimportconfigRepository repimportconfigRepository;
//        private readonly LogManageService logManageService;

//        private IWorkbook workbook = null;
//        private ICellStyle style;
//        private per_allot Allot;

//        public DFExtractService(ILogger<ExtractService> logger,
//            IHostingEnvironment 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,
//            PerforRepimportconfigRepository repimportconfigRepository,
//            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.repimportconfigRepository = repimportconfigRepository;
//            this.logManageService = logManageService;
//        }
//        #endregion

//        #region 抽取

//        public string ExtractData(int allotId, string email, int hospitalId, string filePath = null)
//        {
//            var hospital = perforHospitalRepository.GetEntity(t => t.ID == hospitalId);
//            try
//            {
//                var allot = perforPerallotRepository.GetEntity(t => t.ID == allotId);
//                Allot = allot ?? throw new PerformanceException("");

//                var configs = perforHospitalconfigRepository.GetEntities(t => t.HospitalId == hospitalId);

//                var statesArray = new int[] { (int)AllotStates.GenerateSucceed, (int)AllotStates.Archive };
//                var allots = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId && statesArray.Contains(t.States));
//                var lastAllot = allots?.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).First();

//                var extractIds = new List<int>();
//                var modules = perforModmoduleRepository.GetEntities(t => t.HospitalId == hospitalId);
//                var items = new List<mod_item>();
//                if (modules != null && modules.Any())
//                {
//                    extractIds.AddRange(modules.Select(t => t.ExtractId ?? 0));
//                    items = perforModitemRepository.GetEntities(t => t.ModuleId.HasValue
//                    && modules.Select(m => m.Id).Contains(t.ModuleId.Value));

//                    extractIds.AddRange(items?.Select(t => t.ExtractId ?? 0) ?? new List<int>());
//                }
//                var specials = perforModspecialRepository.GetEntities(t => t.HospitalId == hospitalId);
//                if (specials != null && specials.Any())
//                    extractIds.AddRange(specials.Select(t => t.ExtractId ?? 0));

//                extractIds = extractIds.Distinct().ToList();
//                var extracts = perforModextractRepository.GetEntities(t => extractIds.Contains(t.Id));

//                return lastAllot == null ? TemplateExecute(email, hospital, configs, modules, items, specials, extracts)
//                    : AlllotExecute(email, hospital, configs, modules, items, specials, extracts, lastAllot, filePath);
//            }
//            catch (Exception ex)
//            {
//                logManageService.WriteMsg("提取数据异常", $"数据写入出现异常", 4, Allot.ID, "ReceiveMessage");
//                logger.LogError($"提取绩效数据异常 数据写入出现异常{ex.ToString()}");
//                //SendEmail(email, "", $"{hospital.HosName}HIS数据提取失败", $"{hospital.HosName}提取数据过程中出现异常情况，我们将尽快解决问题。给您带来的不便我们深感歉意！");
//                throw ex;
//            }
//            finally
//            {
//                Allot.IsExtracting = null;
//                perforPerallotRepository.Update(Allot);
//                if (workbook != null)
//                    workbook.Close();
//                GC.Collect();
//            }
//        }

//        /// <summary>
//        /// 空白模板
//        /// </summary>
//        /// <param name="email"></param>
//        /// <param name="lastAllot"></param>
//        /// <param name="hospital"></param>
//        /// <param name="configs"></param>
//        /// <param name="modules"></param>
//        /// <param name="items"></param>
//        /// <param name="specials"></param>
//        /// <param name="extracts"></param>
//        /// <returns></returns>
//        public string TemplateExecute(string email, sys_hospital hospital, List<sys_hospitalconfig> configs, List<mod_module> modules, List<mod_item> items, List<mod_special> specials, List<mod_extract> extracts)
//        {
//            string originalPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Template", "东方医院绩效模板.xlsx");
//            var (tempPath, newPath) = CopyOriginalFile(hospital.ID, originalPath);
//            workbook = new XSSFWorkbook(tempPath);

//            CreateNotExistSheet(modules, workbook);

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

//            List<AccountUnitEntity> unitList = new List<AccountUnitEntity>();

//            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.OtherIncome:
//                        WriteOtherIncome(sheet, sheetRead, unitList, configs, modules, items, extracts);
//                        break;
//                    case SheetType.Income:
//                        WriteIncome(sheet, sheetRead, unitList, configs, modules, items, extracts);
//                        break;
//                    case SheetType.Expend:
//                        WriteExpend(sheet, sheetRead, unitList, configs, modules, items, extracts);
//                        break;
//                    case SheetType.Workload:
//                        WriteWorkload(sheet, sheetRead, unitList, configs, modules, items, extracts);
//                        break;
//                    case SheetType.SpecialUnit:
//                        WriteSpecialUnit(sheet, sheetRead, configs, specials, extracts);
//                        break;
//                }
//            }
//            using (FileStream file = new FileStream(newPath, FileMode.OpenOrCreate))
//            {
//                workbook.Write(file);
//            }
//            logManageService.WriteMsg("提取绩效数据", $"{hospital.HosName}HIS数据提取成功,文件路径：{newPath}。", 5, Allot.ID, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 {hospital.HosName}HIS数据提取成功,文件路径：{newPath}。");
//            ImportData(Allot, configs);
//            SendEmail(email, newPath, $"{hospital.HosName}HIS数据提取成功", $"{hospital.HosName}在{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}成功提取。");
//            return newPath;
//        }

//        /// <summary>
//        /// 历史绩效为模板
//        /// </summary>
//        /// <param name="email"></param>
//        /// <param name="lastAllot"></param>
//        /// <param name="hospital"></param>
//        /// <param name="configs"></param>
//        /// <param name="modules"></param>
//        /// <param name="items"></param>
//        /// <param name="specials"></param>
//        /// <param name="extracts"></param>
//        /// <returns></returns>
//        public string AlllotExecute(string email, sys_hospital hospital, List<sys_hospitalconfig> configs, List<mod_module> modules, List<mod_item> items, List<mod_special> specials, List<mod_extract> extracts, per_allot lastAllot, string path)
//        {
//            if (string.IsNullOrEmpty(path)) throw new PerformanceException("历史绩效文件不存在!");

//            var (tempPath, newPath) = CopyOriginalFile(hospital.ID, path);
//            workbook = new XSSFWorkbook(tempPath);

//            CreateNotExistSheet(modules, workbook);

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

//            List<AccountUnitEntity> unitList = new List<AccountUnitEntity>();
//            if (lastAllot != null)
//            {
//                unitList = perforImdataRepository.GetAccountUnit(lastAllot.ID).ToList();
//                logger.LogInformation($"lastAllot.ID: {lastAllot.ID}, lastAllot date: {lastAllot.Year}-{lastAllot.Month}, unitList count: {unitList?.Count ?? 0}");
//            }

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

//                var sheetRead = PerSheetDataFactory.GetDataRead(sheetType);
//                switch (sheetType)
//                {
//                    case SheetType.OtherIncome:
//                        ClearData(sheet, 5, 4);
//                        WriteOtherIncome(sheet, sheetRead, unitList, configs, modules, items, extracts, false);
//                        break;
//                    case SheetType.Income:
//                        ClearData(sheet, 5, 4, true);
//                        WriteIncome(sheet, sheetRead, unitList, configs, modules, items, extracts, false);
//                        break;
//                    case SheetType.Expend:
//                        ClearData(sheet, 5, 4);
//                        WriteExpend(sheet, sheetRead, unitList, configs, modules, items, extracts, false);
//                        break;
//                    case SheetType.Workload:
//                        ClearData(sheet, 3, 2);
//                        WriteWorkload(sheet, sheetRead, unitList, configs, modules, items, extracts, false);
//                        break;
//                    case SheetType.SpecialUnit:
//                        ClearData(sheet, 2, 0);
//                        WriteSpecialUnit(sheet, sheetRead, configs, specials, extracts, false, lastAllot);
//                        break;
//                }
//            }
//            using (FileStream file = new FileStream(newPath, FileMode.OpenOrCreate))
//            {
//                workbook.Write(file);
//            }
//            logManageService.WriteMsg("提取绩效数据", $"{hospital.HosName}HIS数据提取成功,文件路径：{newPath}。", 5, Allot.ID, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 {hospital.HosName}HIS数据提取成功,文件路径：{newPath}。");
//            ImportData(Allot, configs);
//            SendEmail(email, newPath, $"{hospital.HosName}HIS数据提取成功", $"{hospital.HosName}在{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}成功提取。");
//            return newPath;
//        }

//        #endregion

//        #region Excel

//        private (string TempPath, string NewPath) CopyOriginalFile(int hospitalId, string originalPath)
//        {
//            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 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 ClearData(ISheet sheet, int beginRowNum, int beginCellNum, bool isIncome = false)
//        {
//            if (sheet == null)
//                return;

//            for (int i = beginRowNum; i < sheet.LastRowNum + 1; i++)
//            {
//                var row = sheet.GetRow(i);
//                if (row != null)
//                {
//                    //跳过核算单元和科室
//                    for (int j = beginCellNum; j < row.LastCellNum; j++)
//                    {
//                        var cell = row.GetCell(j);
//                        if (cell != null && (cell.CellType != CellType.Formula || isIncome))
//                        {
//                            cell.RemoveCellComment();
//                            row.RemoveCell(cell);
//                        }
//                    }
//                }
//            }

//            sheet.ForceFormulaRecalculation = true;
//        }

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

//            return row;
//        }

//        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;
//        }

//        #endregion

//        #region SheetData

//        private void WriteOtherIncome(ISheet sheet, IPerSheetDataRead sheetRead, List<AccountUnitEntity> unitList, List<sys_hospitalconfig> configs, List<mod_module> modules, List<mod_item> items, List<mod_extract> extracts, bool isNewTemp = true)
//        {
//            logger.LogInformation($"{sheet.SheetName}开始提取.");
//            var module = modules.FirstOrDefault(t => t.SheetType == (int)SheetType.OtherIncome);
//            if (module == null) return;

//            var itemList = items.Where(t => t.ModuleId == module.Id).ToList();
//            logger.LogInformation($"item有{itemList?.Count ?? 0}个.");
//            if (itemList == null || !itemList.Any()) return;

//            WriteHeaderAndFactor(sheet, sheetRead, itemList, isNewTemp);

//            //查询数据
//            var extractIdList = itemList.Select(t => t.ExtractId).Distinct().ToList();
//            var extractList = extracts.Where(t => extractIdList.Contains(t.Id)).ToList();
//            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, Allot.ID, "ReceiveMessage");
//                    logger.LogInformation($"提取绩效数据 执行SQL脚本获取数据 -- {module.ModuleName}，");

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

//            WriteSheetData(sheet, sheetRead, unitList, allExtract, itemList.Select(t => t.ItemName), isNewTemp);
//            logger.LogInformation($"{sheet.SheetName}提取结束.");
//        }

//        private void WriteIncome(ISheet sheet, IPerSheetDataRead sheetRead, List<AccountUnitEntity> unitList, List<sys_hospitalconfig> configs, List<mod_module> modules, List<mod_item> items, List<mod_extract> extracts, bool isNewTemp = true)
//        {
//            logger.LogInformation($"{sheet.SheetName}开始提取.");
//            var module = modules.FirstOrDefault(t => t.ModuleName.Replace(" ", "") == sheet.SheetName.Replace(" ", ""));
//            if (module == null) return;

//            var itemList = items.Where(t => t.ModuleId == module.Id).ToList();
//            logger.LogInformation($"item有{itemList?.Count ?? 0}个.");
//            if (itemList == null || !itemList.Any()) return;

//            //查询数据
//            var extractList = extracts.Where(t => module.ExtractId == t.Id).ToList();
//            if (extractList == null || extractList.Count == 0) return;

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

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

//            logger.LogInformation($"{sheet.SheetName}合计值为: " + allExtract.Sum(t => t.Value));

//            var category = allExtract.Select(t => t.Category).Distinct().ToList();
//            WriteIncomeHeaderAndFactor(sheet, sheetRead, category, isNewTemp);
//            WriteSheetData(sheet, sheetRead, unitList, allExtract, category, isNewTemp, true);
//            logger.LogInformation($"{sheet.SheetName}提取结束.");
//        }

//        private void WriteExpend(ISheet sheet, IPerSheetDataRead sheetRead, List<AccountUnitEntity> unitList, List<sys_hospitalconfig> configs, List<mod_module> modules, List<mod_item> items, List<mod_extract> extracts, bool isNewTemp = true)
//        {
//            logger.LogInformation($"{sheet.SheetName}开始提取.");
//            var module = modules.FirstOrDefault(t => t.SheetType == (int)SheetType.Expend);
//            if (module == null) return;

//            var itemList = items.Where(t => t.ModuleId == module.Id).ToList();
//            logger.LogInformation($"item有{itemList?.Count ?? 0}个.");
//            if (itemList == null || !itemList.Any()) return;

//            WriteHeaderAndFactor(sheet, sheetRead, itemList, isNewTemp);

//            //查询数据
//            var extractIdList = itemList.Select(t => t.ExtractId).Distinct().ToList();
//            var extractList = extracts.Where(t => extractIdList.Contains(t.Id)).ToList();
//            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, Allot.ID, "ReceiveMessage");
//                    logger.LogInformation($"提取绩效数据 执行SQL脚本获取数据 -- {module.ModuleName}，");

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

//            WriteSheetData(sheet, sheetRead, unitList, allExtract, itemList.Select(t => t.ItemName), isNewTemp);
//            logger.LogInformation($"{sheet.SheetName}提取结束.");
//        }

//        private void WriteWorkload(ISheet sheet, IPerSheetDataRead sheetRead, List<AccountUnitEntity> unitList, List<sys_hospitalconfig> configs, List<mod_module> modules, List<mod_item> items, List<mod_extract> extracts, bool isNewTemp = true)
//        {
//            logger.LogInformation($"{sheet.SheetName}开始提取.");
//            var module = modules.FirstOrDefault(t => t.ModuleName.Replace(" ", "") == sheet.SheetName.Replace(" ", ""));
//            if (module == null) return;

//            var itemList = items.Where(t => t.ModuleId == module.Id).ToList();
//            logger.LogInformation($"item有{itemList?.Count ?? 0}个.");
//            if (itemList == null || !itemList.Any()) return;

//            WriteWorkHeader(sheet, sheetRead, itemList, isNewTemp);

//            //查询数据
//            var extractIdList = itemList.Select(t => t.ExtractId).Distinct().ToList();
//            var extractList = extracts.Where(t => extractIdList.Contains(t.Id)).ToList();
//            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, Allot.ID, "ReceiveMessage");
//                    logger.LogInformation($"提取绩效数据  执行SQL脚本获取数据 -- {module.ModuleName}");

//                    var config = configs.FirstOrDefault(t => t.Id == moditem.ConfigId);
//                    if (config == null) continue;
//                    var result = QueryDatabase(config, item, Allot, moditem.ItemName);
//                    if (result != null)
//                        allExtract.AddRange(result);
//                    logger.LogInformation($"{module.ModuleName}提取{moditem.ItemName}的合计值为: " + allExtract.Sum(t => t.Value));
//                }
//            }

//            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, Allot.ID, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 填充数据 -- {module.ModuleName}");
//            //写入数据
//            WriteWorkData(sheet, sheetRead, unitList, allExtract, specialHead, isNewTemp);
//            logger.LogInformation($"{sheet.SheetName}提取结束.");
//        }

//        #region WriteAccountBasic

//        //private void WriteAccountBasic(ISheet sheet, IPerSheetDataRead sheetRead, int allotLastId)
//        //{
//        //    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.WorkSlopeFactor },
//        //        { "保底绩效参考标准", (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, Allot.ID, "ReceiveMessage");
//        //    logger.Information($"填充数据 -- 临床科室医护绩效测算表", "提取绩效数据");
//        //    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.默认);
//        //        }
//        //    }
//        //}

//        #endregion

//        private void WriteSpecialUnit(ISheet sheet, IPerSheetDataRead sheetRead, List<sys_hospitalconfig> configs, List<mod_special> specials, List<mod_extract> extracts, bool IsWriteHead = true, per_allot lastAllot = null)
//        {
//            logger.LogInformation($"{sheet.SheetName}开始提取.");
//            var dictionary = new Dictionary<string, Func<mod_special, List<im_specialunit>, object>>
//            {
//                { "科室", (special,lastallot) => special.Department },
//                { "人数", (special,lastallot) => lastallot.Where(t=>special.Department == t.Department).Max(t=>t.Number) },
//                { "量化指标", (special,lastallot) => special.Target},
//                { "量化指标绩效分值",(special,lastallot) => special.TargetFactor },
//                { "调节系数", (special,lastallot) => special.AdjustFactor },
//            };

//            var speaialList = specials?.OrderBy(t => t.Department).ToList();
//            logger.LogInformation($"item有{speaialList?.Count ?? 0}个.");
//            if (speaialList == null || !speaialList.Any()) return;

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

//            ////查询数据
//            //var extractIdList = speaialList.Select(t => t.ExtractId).Distinct().ToList();
//            //var extractList = extracts.Where(t => extractIdList.Contains(t.Id)).ToList();

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

//            //        var config = configs.FirstOrDefault(t => t.Id == specialitem.ConfigId);
//            //        if (config == null) continue;
//            //        var result = QueryDatabase(config, item, Allot, specialitem.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 = speaialList.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, Allot.ID, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 填充数据 -- 特殊核算单元绩效测算表");
//            for (int i = 0; i < speaialList.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(speaialList[i], allotDataList) ?? "";

//                        if (item.Key == "科室" && rowIndex == mergedBegin)
//                        {
//                            var count = modDataGroup.First(t => t.Department.ToString() == value.ToString()).Count;
//                            mergedEnd = mergedBegin + count - 1;
//                        }
//                    }
//                    if (cell.StringCellValue == "数量" && speaialList[i]?.ExtractId > 0)
//                    {
//                        logManageService.WriteMsg("提取绩效数据", $"执行SQL脚本获取数据 -- 特殊核算单元绩效测算表", 1, Allot.ID, "ReceiveMessage");
//                        logger.LogInformation($"提取绩效数据 执行SQL脚本获取数据 -- 特殊核算单元绩效测算表 ");

//                        var config = configs.FirstOrDefault(t => t.Id == speaialList[i].ConfigId);
//                        if (config == null) continue;
//                        var ext = extracts.FirstOrDefault(w => w.Id == speaialList[i]?.ExtractId);

//                        var result = QueryDatabase(config, ext, Allot);
//                        value = result?.FirstOrDefault()?.Value;
//                    }

//                    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) || (cell.StringCellValue == "数量" && !string.IsNullOrEmpty(value?.ToString())))
//                        newCell.CellStyle = style;

//                    cellIndex++;
//                }
//                mergedBegin = mergedEnd + 1;
//            }
//            logger.LogInformation($"{sheet.SheetName}提取结束.");
//        }

//        #region 写入数据
//        /// <summary>
//        /// 写入列头
//        /// </summary>
//        /// <param name="sheet"></param>
//        /// <param name="sheetRead"></param>
//        /// <param name="items">列头数据(列名、系数)</param>
//        /// <param name="isNewTemp">是否为空白模板</param>
//        private void WriteHeaderAndFactor(ISheet sheet, IPerSheetDataRead sheetRead, List<mod_item> items, bool isNewTemp)
//        {
//            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("提取绩效数据", $"写入列头信息 -- {sheet.SheetName}", 1, Allot.ID, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 提取绩效数据 写入列头信息 -- {sheet.SheetName}");

//            var cellItems = new List<mod_item>();
//            cellItems.AddRange(items);
//            if (!isNewTemp)
//            {
//                List<string> original = new List<string>();
//                #region 过滤历史模板中已有的列头
//                //写入列头信息
//                int cellStartIndex = sheetRead.Point.HeaderFirstCellNum.Value + 4;
//                for (int i = cellStartIndex; i < head.LastCellNum; i++)
//                {
//                    var cellvalue = head.GetCell(i)?.ToString();
//                    if (string.IsNullOrEmpty(cellvalue)) continue;
//                    cellItems.RemoveAll(t => t.ItemName == cellvalue);
//                }
//                #endregion
//            }

//            if (cellItems == null || !cellItems.Any()) return;

//            #region 新增模板中不存在的列头
//            var lastcellIndex = isNewTemp ? sheetRead.Point.HeaderFirstCellNum.Value + 4 : head.LastCellNum;
//            foreach (var item in cellItems)
//            {
//                var headcell = GetOrCreate(head, lastcellIndex);
//                headcell.SetCellValue(item.ItemName);
//                headcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.列头);

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

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

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


//        /// <summary>
//        /// 写入列头
//        /// </summary>
//        /// <param name="sheet"></param>
//        /// <param name="sheetRead"></param>
//        /// <param name="items">列头数据(列名、系数)</param>
//        /// <param name="isNewTemp">是否为空白模板</param>
//        private void WriteIncomeHeaderAndFactor(ISheet sheet, IPerSheetDataRead sheetRead, List<string> items, bool isNewTemp)
//        {
//            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("提取绩效数据", $"写入列头信息 -- {sheet.SheetName}", 1, Allot.ID, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 提取绩效数据 写入列头信息 -- {sheet.SheetName}");

//            logger.LogInformation($"{sheet.SheetName}查询出的列头有:" + string.Join(", ", items));

//            if (!isNewTemp)
//            {
//                List<string> original = new List<string>();
//                #region 过滤历史模板中已有的列头
//                //写入列头信息
//                int cellStartIndex = sheetRead.Point.HeaderFirstCellNum.Value + 4;
//                for (int i = cellStartIndex; i < head.LastCellNum; i++)
//                {
//                    var cellvalue = head.GetCell(i)?.ToString();
//                    if (string.IsNullOrEmpty(cellvalue)) continue;
//                    items.Remove(cellvalue);
//                }
//                #endregion
//            }

//            if (items == null || !items.Any()) return;

//            logger.LogInformation($"{sheet.SheetName}需要新增的列头有:" + string.Join(", ", items));

//            #region 新增模板中不存在的列头
//            var lastcellIndex = isNewTemp ? sheetRead.Point.HeaderFirstCellNum.Value + 4 : head.LastCellNum;
//            foreach (var item in items)
//            {
//                var headcell = GetOrCreate(head, lastcellIndex);
//                headcell.SetCellValue(item);
//                headcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.列头);

//                var doctorcell = GetOrCreate(doctorFactor, lastcellIndex);
//                doctorcell.SetCellValue(0);
//                doctorcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);

//                var nursecell = GetOrCreate(nurseFactor, lastcellIndex);
//                nursecell.SetCellValue(0);
//                nursecell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);

//                var techniciancell = GetOrCreate(technicianFactor, lastcellIndex);
//                techniciancell.SetCellValue(0);
//                techniciancell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.系数, CellFormat.百分比);
//                lastcellIndex++;
//            }
//            #endregion
//        }

//        /// <summary>
//        /// 写入数据
//        /// </summary>
//        /// <param name="sheet"></param>
//        /// <param name="sheetRead"></param>
//        /// <param name="unitList">核算单元</param>
//        /// <param name="allExtract">抽取的数据(科室、列头、数据)</param>
//        /// <param name="header">设定抽取的列头</param>
//        /// <param name="isNewTemp">是否为空白模板</param>
//        /// <param name="isIncom">是否是开单、执行收入</param>
//        private void WriteSheetData(ISheet sheet, IPerSheetDataRead sheetRead, List<AccountUnitEntity> unitList, List<ExtractDto> allExtract, IEnumerable<string> header, bool isNewTemp, bool isIncom = false)
//        {
//            logManageService.WriteMsg("提取绩效数据", $"填充数据 -- {sheet.SheetName}", 1, Allot.ID, "ReceiveMessage");
//            logger.LogInformation($"提取绩效数据 填充数据 -- {sheet.SheetName}");
//            //写入数据
//            var head = GetOrCreate(sheet, sheetRead.Point.HeaderFirstRowNum.Value);
//            var rowIndex = sheetRead.Point.HeaderFirstRowNum.Value + 1;

//            if (!isNewTemp)
//            {
//                #region 给历史模板已有科室补充数据
//                for (int i = rowIndex; i < sheet.LastRowNum + 1; i++)
//                {
//                    var row = sheet.GetRow(i);
//                    if (row != null)
//                    {
//                        var department = row.GetCell(3)?.ToString();    // 科室名称
//                        if (string.IsNullOrEmpty(department)) continue;

//                        var deptData = allExtract.Where(t => t.Department == department);
//                        if (deptData == null || !deptData.Any()) continue;

//                        for (int j = 4; j < head.LastCellNum; j++)
//                        {
//                            var headName = head.GetCell(j).StringCellValue;
//                            var newCell = GetOrCreate(row, j);
//                            if (newCell == null) continue;

//                            var value = deptData.FirstOrDefault(t => t.Category == headName)?.Value;
//                            if (isIncom)
//                            {
//                                value = value == 0 ? null : value;
//                                OutToExcelCell(newCell, value);
//                                newCell.CellStyle = style;
//                            }
//                            else if (newCell.CellType != CellType.Formula)
//                            {
//                                value = value == 0 ? null : value;
//                                OutToExcelCell(newCell, value);
//                                if (header != null && header.Contains(headName))
//                                    newCell.CellStyle = style;
//                            }
//                        }
//                        allExtract.RemoveAll(t => t.Department == department);
//                    }
//                }
//                #endregion
//            }

//            if (allExtract == null || !allExtract.Any()) return;

//            #region 补充新的科室及数据
//            var lastrowIndex = isNewTemp ? rowIndex : sheet.LastRowNum + 1;
//            foreach (var department in allExtract.Select(t => t.Department).Where(t => !string.IsNullOrEmpty(t)).Distinct())
//            {
//                var row = sheet.CreateRow(lastrowIndex);
//                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 ?? 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 ?? 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 ?? 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;
//                        if (isIncom)
//                        {
//                            value = value == 0 ? null : value;
//                            OutToExcelCell(newCell, value);
//                            newCell.CellStyle = style;
//                        }
//                        else if (header != null && header.Contains(headName))
//                        {
//                            value = value == 0 ? null : value;
//                            OutToExcelCell(newCell, value);
//                            newCell.CellStyle = style;
//                        }
//                    }
//                }
//                lastrowIndex++;
//            }
//            #endregion
//        }

//        /// <summary>
//        /// 写入工作量列头
//        /// </summary>
//        /// <param name="sheet"></param>
//        /// <param name="sheetRead"></param>
//        /// <param name="items"></param>
//        /// <param name="isNewTemp"></param>
//        private void WriteWorkHeader(ISheet sheet, IPerSheetDataRead sheetRead, List<mod_item> items, bool isNewTemp)
//        {
//            var head = GetOrCreate(sheet, sheetRead.Point.HeaderFirstRowNum.Value + 0);
//            var factor = GetOrCreate(sheet, sheetRead.Point.HeaderFirstRowNum.Value + 1);

//            var cellItems = new List<mod_item>();
//            cellItems.AddRange(items);
//            if (!isNewTemp)
//            {
//                #region 过滤历史模板中已有的列头
//                //写入列头信息
//                int cellStartIndex = sheetRead.Point.HeaderFirstCellNum.Value + 2;
//                for (int i = cellStartIndex; i < head.LastCellNum; i++)
//                {
//                    var cellvalue = head.GetCell(i)?.ToString();
//                    if (string.IsNullOrEmpty(cellvalue)) continue;
//                    cellItems.RemoveAll(t => t.ItemName == cellvalue);
//                }
//                #endregion
//            }

//            if (cellItems == null || !cellItems.Any()) return;

//            #region 新增模板中不存在的列头
//            var lastcellIndex = isNewTemp ? sheetRead.Point.HeaderFirstCellNum.Value + 2 : head.LastCellNum;
//            foreach (var item in cellItems)
//            {
//                var headcell = GetOrCreate(head, lastcellIndex);
//                headcell.SetCellValue(item.ItemName);
//                headcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.列头);

//                var doctorcell = GetOrCreate(factor, lastcellIndex);
//                doctorcell.SetCellValue(item.FactorValue1 != null ? (double)item.FactorValue1 : 0);
//                doctorcell.CellStyle = CellStyle.CreateCellStyle(workbook, StyleType.列头, CellFormat.数字2);
//                lastcellIndex++;
//            }
//            #endregion

//        }

//        /// <summary>
//        /// 写入工作量数据
//        /// </summary>
//        /// <param name="sheet"></param>
//        /// <param name="sheetRead"></param>
//        /// <param name="unitList"></param>
//        /// <param name="allExtract"></param>
//        /// <param name="header"></param>
//        /// <param name="isNewTemp"></param>
//        private void WriteWorkData(ISheet sheet, IPerSheetDataRead sheetRead, List<AccountUnitEntity> unitList, List<ExtractDto> allExtract, IEnumerable<string> header, bool isNewTemp)
//        {
//            var head = GetOrCreate(sheet, sheetRead.Point.HeaderFirstRowNum.Value + 0);
//            var rowIndex = sheetRead.Point.HeaderFirstRowNum.Value + 2;
//            if (!isNewTemp)
//            {
//                #region 给历史模板已有科室补充数据
//                for (int i = rowIndex; i < sheet.LastRowNum + 1; i++)
//                {
//                    var row = sheet.GetRow(i);
//                    if (row != null)
//                    {
//                        var department = row.GetCell(1)?.ToString();    // 科室名称
//                        if (string.IsNullOrEmpty(department)) continue;

//                        var deptData = allExtract.Where(t => t.Department == department);
//                        if (deptData == null || !deptData.Any()) continue;

//                        for (int j = 2; j < head.LastCellNum; j++)
//                        {
//                            var headName = head.GetCell(j).StringCellValue;
//                            var newCell = GetOrCreate(row, j);
//                            if (newCell == null) continue;

//                            if (newCell.CellType != CellType.Formula)
//                            {
//                                var extract = deptData.FirstOrDefault(t => t.Category == headName);
//                                var value = extract?.Value == 0 ? null : extract?.Value;
//                                OutToExcelCell(newCell, value);
//                                if (header != null && header.Contains(headName))
//                                    newCell.CellStyle = style;
//                            }
//                        }
//                        allExtract.RemoveAll(t => t.Department == department);
//                    }
//                }
//                #endregion
//            }

//            if (allExtract == null || !allExtract.Any()) return;

//            #region 补充新的科室及数据
//            var lastrowIndex = isNewTemp ? rowIndex : sheet.LastRowNum + 1;
//            foreach (var department in allExtract.Select(t => t.Department).Where(t => !string.IsNullOrEmpty(t)).Distinct())
//            {
//                var row = sheet.CreateRow(lastrowIndex);
//                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 ?? 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 (header != null && header.Contains(headName))
//                            newCell.CellStyle = style;
//                    }
//                }
//                lastrowIndex++;
//            }
//            #endregion
//        }
//        #endregion

//        #endregion

//        #region QueryData

//        private List<ExtractDto> QueryDatabase(sys_hospitalconfig config, mod_extract extract, per_allot allot, string category = null)
//        {
//            //var config = configs.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($"提取绩效数据 {category ?? ""}SQL脚本{executeScript}");
//                var result = connection.Query<ExtractDto>(executeScript, commandTimeout: 20000);
//                logger.LogInformation($"提取绩效数据 {category ?? ""} 执行脚本获取数据{result?.Count() ?? 0}条记录");
//                if (result != null && result.Count() > 0)
//                {
//                    if (extract.ExecuteType == 2)
//                    {
//                        foreach (var item in result)
//                        {
//                            item.Category = category;
//                        }
//                    }
//                    return result.Where(t => !string.IsNullOrEmpty(t.Category)).ToList();
//                }
//            }
//            return null;
//        }

//        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;
//        }

//        /// <summary>
//        /// 从HIS抽取报表数据
//        /// </summary>
//        /// <param name="allot"></param>
//        /// <param name="configs"></param>
//        public void ImportData(per_allot allot, List<sys_hospitalconfig> configs)
//        {
//            Dictionary<string, object> pairs = new Dictionary<string, object>
//            {
//                { "@allotid", allot.ID },
//                { "@hospitalid", allot.HospitalId },
//                { "@year", allot.Year },
//                { "@month", allot.Month },
//            };
//            var imports = repimportconfigRepository.GetEntities(w => w.ScriptType == 1);
//            if (imports == null || !imports.Any()) return;

//            foreach (var import in imports)
//            {
//                var conf = configs.FirstOrDefault(w => w.HospitalId == allot.HospitalId && w.Id == import.ConfigId);
//                if (conf != null)
//                {
//                    var timeRanges = import.TimeRange.SplitRemoveEmpty(",");
//                    if (timeRanges == null || !timeRanges.Any()) continue;

//                    foreach (var item in timeRanges)
//                    {
//                        if (item == "1")
//                        {
//                            pairs["@year"] = allot.Year;
//                            pairs["@month"] = allot.Month;
//                        }
//                        else if (item == "2")
//                        {
//                            pairs["@year"] = allot.Year - 1;
//                            pairs["@month"] = allot.Month;
//                        }
//                        else if (item == "3")
//                        {
//                            pairs["@year"] = allot.Year;
//                            pairs["@month"] = allot.Month - 1;
//                        }
//                        try
//                        {
//                            DatabaseType type = (DatabaseType)conf.DataBaseType;
//                            var connection = ConnectionBuilder.Create(type, conf.DbSource, conf.DbName, conf.DbUser, conf.DbPassword);
//                            var data = connection.Query(import.ImportScript, new DynamicParameters(pairs), commandTimeout: 60 * 60);
//                            perforPerallotRepository.ImportData(import, pairs, data);
//                        }
//                        catch (Exception ex)
//                        {
//                            logger.LogError(ex.ToString());
//                        }
//                    }
//                }
//            }
//        }

//        #endregion

//        #region Common

//        /// <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)
//        {
//            if (string.IsNullOrEmpty(mail)) return;

//            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);
//        }

//        /// <summary>
//        /// 校验数据格式，并转换
//        /// </summary>
//        /// <param name="obj"></param>
//        /// <returns></returns>
//        public void OutToExcelCell(ICell cell, object obj)
//        {
//            string value = obj?.ToString() ?? "";
//            try
//            {
//                var type = obj?.GetType() ?? typeof(string);
//                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
//    }
//}
