//using Dapper;
//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.RegularExpressions;

//namespace Performance.Services
//{
//    public class DFExtractService : IAutoInjection
//    {
//        #region
//        private readonly ILogger<DFExtractService> logger;
//        private readonly IEmailService emailService;
//        private readonly PerSheetService perSheetService;
//        private readonly PerforHospitalRepository perforHospitalRepository;
//        private readonly PerforHospitalconfigRepository perforHospitalconfigRepository;
//        private readonly PerforPerallotRepository perforPerallotRepository;
//        private readonly PerforImspecialunitRepository perforImspecialunitRepository;
//        private readonly PerforImdataRepository perforImdataRepository;
//        private readonly PerforRepimportconfigRepository repimportconfigRepository;
//        private readonly LogManageService logManageService;

//        private readonly PerforExitemRepository perforExitemRepository;
//        private readonly PerforExmoduleRepository perforExmoduleRepository;
//        private readonly PerforExspecialRepository perforExspecialRepository;
//        private readonly PerforExresultRepository perforExresultRepository;
//        private readonly PerforExscriptRepository perforExscriptRepository;

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

//        public DFExtractService(ILogger<DFExtractService> logger,
//            IEmailService emailService,
//            PerSheetService perSheetService,
//            PerforHospitalRepository perforHospitalRepository,
//            PerforHospitalconfigRepository perforHospitalconfigRepository,
//            PerforPerallotRepository perforPerallotRepository,
//            PerforImspecialunitRepository perforImspecialunitRepository,
//            PerforImdataRepository perforImdataRepository,
//            PerforRepimportconfigRepository repimportconfigRepository,
//            LogManageService logManageService,

//            PerforExitemRepository perforExitemRepository,
//            PerforExmoduleRepository perforExmoduleRepository,
//            PerforExspecialRepository perforExspecialRepository,
//            PerforExresultRepository perforExresultRepository,
//            PerforExscriptRepository perforExscriptRepository)
//        {
//            this.logger = logger;
//            this.emailService = emailService;
//            this.perSheetService = perSheetService;
//            this.perforHospitalRepository = perforHospitalRepository;
//            this.perforHospitalconfigRepository = perforHospitalconfigRepository;
//            this.perforPerallotRepository = perforPerallotRepository;
//            this.perforImspecialunitRepository = perforImspecialunitRepository;
//            this.perforImdataRepository = perforImdataRepository;
//            this.repimportconfigRepository = repimportconfigRepository;
//            this.logManageService = logManageService;

//            this.perforExitemRepository = perforExitemRepository;
//            this.perforExmoduleRepository = perforExmoduleRepository;
//            this.perforExspecialRepository = perforExspecialRepository;
//            this.perforExresultRepository = perforExresultRepository;
//            this.perforExscriptRepository = perforExscriptRepository;
//        }
//        #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 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 configs = perforHospitalconfigRepository.GetEntities(t => t.HospitalId == hospitalId);

//                var typeIds = new List<int>();
//                var modules = perforExmoduleRepository.GetEntities(t => t.HospitalId == hospitalId);
//                var items = new List<ex_item>();
//                if (modules != null && modules.Any())
//                {
//                    typeIds.AddRange(modules.Select(t => t.TypeId ?? 0));
//                    items = perforExitemRepository.GetEntities(t => t.ModuleId.HasValue
//                    && modules.Select(m => m.Id).Contains(t.ModuleId.Value));

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

//                typeIds = typeIds.Distinct().ToList();
//                var data = new List<ex_result>();
//                var scripts = perforExscriptRepository.GetEntities(t => typeIds.Contains(t.TypeId));
//                ClearHistData(allot);
//                var otherItems = items.Where(t => modules.Where(m => m.SheetType != (int)SheetType.Income).Select(m => m.Id).Contains(t.ModuleId.Value)).ToList();
//                if (scripts != null && scripts.Any())
//                {
//                    data.AddRange(ExtractIncome(modules, scripts, configs, allot));
//                    data.AddRange(ExtractItems(otherItems, scripts, configs, allot, modules));
//                    data.AddRange(ExtractSpcial(specials, scripts, configs, allot));
//                }

//                return lastAllot == null ? TemplateExecute(email, hospital, configs, modules, items, specials, data)
//                      : AlllotExecute(email, hospital, configs, modules, items, specials, data, 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>
//        /// <typeparam name="TEntity">ex_module</typeparam>
//        /// <param name="entities"></param>
//        /// <param name="scripts">抽取配置项</param>
//        /// <param name="configs">数据连接项</param>
//        /// <param name="allot">绩效</param>
//        /// <returns></returns>
//        private List<ex_result> ExtractIncome<TEntity>(List<TEntity> entities, List<ex_script> scripts, List<sys_hospitalconfig> configs, per_allot allot)
//        {
//            var data = new List<ex_result>();
//            var modules = entities as List<ex_module>;
//            if (modules == null || !modules.Any(t => t.SheetType == (int)SheetType.Income && t.TypeId > 0))
//                return data;

//            foreach (var module in modules.Where(t => t.TypeId > 0))
//            {
//                foreach (var config in configs)
//                {
//                    var item = scripts.FirstOrDefault(t => t.TypeId == module.TypeId && t.DatabaseType == config.DataBaseType);
//                    if (item == null) continue;
//                    data.AddRange(QueryData(config, allot, item.ExecScript, module.ModuleName));
//                }
//            }
//            if (data != null && data.Any())
//                perforExresultRepository.AddRange(data.ToArray());

//            return data;
//        }

//        /// <summary>
//        /// 抽取支出、工作量等费用
//        /// </summary>
//        /// <typeparam name="TEntity"></typeparam>
//        /// <param name="entities"></param>
//        /// <param name="scripts"></param>
//        /// <param name="configs"></param>
//        /// <param name="allot"></param>
//        /// <param name="modules"></param>
//        /// <returns></returns>
//        private List<ex_result> ExtractItems<TEntity>(List<TEntity> entities, List<ex_script> scripts, List<sys_hospitalconfig> configs, per_allot allot, List<ex_module> modules)
//        {
//            var data = new List<ex_result>();
//            var items = entities as List<ex_item>;
//            if (items == null || !items.Any(t => t.TypeId > 0))
//                return data;

//            foreach (var exitem in items.Where(t => t.TypeId > 0))
//            {
//                var module = modules.FirstOrDefault(t => t.Id == exitem.ModuleId);
//                foreach (var config in configs)
//                {
//                    var item = scripts.FirstOrDefault(t => t.TypeId == exitem.TypeId && t.DatabaseType == config.DataBaseType);
//                    if (item == null) continue;

//                    var result = QueryData(config, allot, item.ExecScript, module.ModuleName);
//                    result.ForEach(t => t.Category = exitem.ItemName);
//                    data.AddRange(result);
//                }
//            }
//            if (data != null && data.Any())
//                perforExresultRepository.AddRange(data.ToArray());

//            return data;
//        }

//        /// <summary>
//        /// 抽取特殊科室数据
//        /// </summary>
//        /// <typeparam name="TEntity"></typeparam>
//        /// <param name="entities"></param>
//        /// <param name="scripts"></param>
//        /// <param name="configs"></param>
//        /// <param name="allot"></param>
//        /// <returns></returns>
//        private List<ex_result> ExtractSpcial<TEntity>(List<TEntity> entities, List<ex_script> scripts, List<sys_hospitalconfig> configs, per_allot allot)
//        {
//            var data = new List<ex_result>();
//            var specials = entities as List<ex_special>;
//            if (specials == null || !specials.Any(t => t.TypeId > 0))
//                return data;

//            foreach (var special in specials.Where(t => t.TypeId > 0))
//            {
//                foreach (var config in configs)
//                {
//                    var item = scripts.FirstOrDefault(t => t.TypeId == special.TypeId && t.DatabaseType == config.DataBaseType);
//                    if (item == null) continue;

//                    var result = QueryData(config, allot, item.ExecScript, "4.2 特殊核算单元绩效测算表");
//                    result.ForEach(t =>
//                    {
//                        t.Category = special.Target;
//                        t.Department = special.Department;
//                    });
//                    data.AddRange(result);
//                }
//            }
//            if (data != null && data.Any())
//                perforExresultRepository.AddRange(data.ToArray());

//            return data;
//        }

//        /// <summary>
//        /// 清除历史抽取数据
//        /// </summary>
//        /// <param name="allot"></param>
//        private void ClearHistData(per_allot allot)
//        {
//            logger.LogInformation($"开始清除历史提取数据");
//            var data = perforExresultRepository.GetEntities(t => t.AllotId == allot.ID);
//            if (data == null || !data.Any(t => t.Id > 0))
//                return;

//            perforExresultRepository.RemoveRange(data.ToArray());
//        }

//        /// <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<ex_module> modules, List<ex_item> items, List<ex_special> specials, List<ex_result> data)
//        {
//            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, data);
//                        break;
//                    case SheetType.Income:
//                        WriteIncome(sheet, sheetRead, unitList, configs, modules, items, data);
//                        break;
//                    case SheetType.Expend:
//                        WriteExpend(sheet, sheetRead, unitList, configs, modules, items, data);
//                        break;
//                    case SheetType.Workload:
//                        WriteWorkload(sheet, sheetRead, unitList, configs, modules, items, data);
//                        break;
//                    case SheetType.SpecialUnit:
//                        WriteSpecialUnit(sheet, sheetRead, configs, specials, data);
//                        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<ex_module> modules, List<ex_item> items, List<ex_special> specials, List<ex_result> 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, 7);
//                        WriteOtherIncome(sheet, sheetRead, unitList, configs, modules, items, extracts, false);
//                        break;
//                    case SheetType.Income:
//                        ClearData(sheet, 5, 7, true);
//                        WriteIncome(sheet, sheetRead, unitList, configs, modules, items, extracts, false);
//                        break;
//                    case SheetType.Expend:
//                        ClearData(sheet, 5, 7);
//                        WriteExpend(sheet, sheetRead, unitList, configs, modules, items, extracts, false);
//                        break;
//                    case SheetType.Workload:
//                        ClearData(sheet, 3, 3);
//                        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 QueryData

//        /// <summary>
//        /// 查询数据
//        /// </summary>
//        /// <param name="config"></param>
//        /// <param name="allot"></param>
//        /// <param name="execsql"></param>
//        /// <param name="source"></param>
//        /// <param name="category"></param>
//        /// <returns></returns>
//        private List<ex_result> QueryData(sys_hospitalconfig config, per_allot allot, string execsql, string source, string category = "")
//        {
//            var data = new List<ex_result>();
//            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)
//                {
//                    execsql = Regex.Replace(execsql, item.Key, item.Value, RegexOptions.IgnoreCase);
//                }
//                logger.LogInformation($"提取绩效数据 {category ?? ""}SQL脚本{execsql}");
//                var result = connection.Query<ExtractDto>(execsql, commandTimeout: 20000);
//                logger.LogInformation($"提取绩效数据 {category ?? ""} 执行脚本获取数据{result?.Count() ?? 0}条记录");
//                if (result != null && result.Count() > 0)
//                {
//                    data = result.Select(t => new ex_result
//                    {
//                        Department = t.Department,
//                        Category = t.Category?.Trim(),
//                        Fee = t.Value,
//                        Source = source,
//                        DatabaseType = config.DataBaseType,
//                        ConfigId = config.Id,
//                        AllotId = allot.ID,
//                    }).ToList();
//                }
//                return data;
//            }
//        }

//        /// <summary>
//        /// 获取参数
//        /// </summary>
//        /// <param name="allot"></param>
//        /// <returns></returns>
//        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 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<ex_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<ex_module> modules, List<ex_item> items, List<ex_result> data, 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 extractdata = data.Where(t => t.AllotId == Allot.ID && t.Source == module.ModuleName);
//            if (extractdata == null || !extractdata.Any())
//                return;

//            //查询数据
//            List<ExtractDto> allExtract = extractdata.Select(t => new ExtractDto
//            {
//                Department = t.Department,
//                Category = t.Category,
//                Value = t.Fee ?? 0
//            }).ToList();

//            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<ex_module> modules, List<ex_item> items, List<ex_result> data, 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 extractdata = data.Where(t => t.AllotId == Allot.ID && t.Source == module.ModuleName);
//            if (extractdata == null || !extractdata.Any())
//                return;

//            //查询数据
//            List<ExtractDto> allExtract = extractdata.Select(t => new ExtractDto
//            {
//                Department = t.Department,
//                Category = t.Category,
//                Value = t.Fee ?? 0
//            }).ToList();

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

//            var category = allExtract.Select(t => t.Category?.Trim()).Distinct().ToList();
//            var existHead = category.Except(itemList.Select(i => i.ItemName?.Trim()));
//            if (existHead != null && existHead.Any())
//                itemList.AddRange(existHead.Select(t => new ex_item
//                {
//                    ItemName = t,
//                }));

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

//        private void WriteExpend(ISheet sheet, IPerSheetDataRead sheetRead, List<AccountUnitEntity> unitList, List<sys_hospitalconfig> configs, List<ex_module> modules, List<ex_item> items, List<ex_result> data, 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 extractdata = data.Where(t => t.AllotId == Allot.ID && t.Source == module.ModuleName);
//            if (extractdata == null || !extractdata.Any())
//                return;

//            //查询数据
//            List<ExtractDto> allExtract = extractdata.Select(t => new ExtractDto
//            {
//                Department = t.Department,
//                Category = t.Category,
//                Value = t.Fee ?? 0
//            }).ToList();

//            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<ex_module> modules, List<ex_item> items, List<ex_result> data, 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 extractdata = data.Where(t => t.AllotId == Allot.ID && t.Source == module.ModuleName);
//            if (extractdata == null || !extractdata.Any())
//                return;

//            //查询数据
//            List<ExtractDto> allExtract = extractdata.Select(t => new ExtractDto
//            {
//                Department = t.Department,
//                Category = t.Category,
//                Value = t.Fee ?? 0
//            }).ToList();

//            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<ex_special> specials, List<ex_result> data, bool IsWriteHead = true, per_allot lastAllot = null)
//        {
//            logger.LogInformation($"{sheet.SheetName}开始提取.");
//            var dictionary = new Dictionary<string, Func<ex_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);

//            //取消合并单元格
//            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;

//            var extractdata = data.Where(t => t.AllotId == Allot.ID && t.Source == "4.2 特殊核算单元绩效测算表");

//            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]?.TypeId > 0)
//                    {
//                        value = extractdata?.FirstOrDefault(t => t.Category == speaialList[i].Target)?.Fee;
//                    }

//                    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<ex_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<ex_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()?.Trim();
//                    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(6)?.ToString();    // 科室名称
//                        if (string.IsNullOrEmpty(department)) continue;

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

//                        for (int j = 7; 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<ex_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<ex_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(2)?.ToString();    // 科室名称
//                        if (string.IsNullOrEmpty(department)) continue;

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

//                        for (int j = 3; 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 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

//        #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 = perforExmoduleRepository.GetEntities(t => t.HospitalId == hospitalId);
//                if (modulesList == null || modulesList.Count == 0)
//                    throw new PerformanceException("当前医院还未配置模版");
//                // 获取模板项
//                var moduleIdList = modulesList.Select(t => t.Id).ToList();
//                var itemsList = perforExitemRepository.GetEntities(t => t.ModuleId.HasValue && moduleIdList.Contains(t.ModuleId.Value));
//                if (itemsList == null || itemsList.Count == 0)
//                    throw new PerformanceException("当前医院还未配置模版项");
//                // 获取当前模板所有相关抽取SQL语句
//                var extypeIds = itemsList.Select(t => t.TypeId).Union(modulesList.Select(t => t.TypeId)).Distinct().ToList();
//                var extractList = perforExscriptRepository.GetEntities(t => extypeIds.Contains(t.TypeId));
//                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
//    }
//}
