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

//namespace Performance.Services
//{
//    /// <summary>
//    /// 数据抽取服务
//    /// </summary>
//    public class ExtractService : IAutoInjection
//    {
//        private readonly ILogger<ExtractService> logger;
//        private readonly IHostingEnvironment environment;
//        private readonly IEmailService emailService;
//        private readonly PerSheetService perSheetService;
//        private readonly PerHeaderService perHeaderService;
//        private readonly PerforPersheetRepository perforPersheetRepository;
//        private readonly PerforImheaderRepository perforImheaderRepository;
//        private readonly PerforImemployeeRepository perforImemployeeRepository;
//        private readonly PerforImdataRepository perforImdataRepository;
//        private readonly PerforImaccountbasicRepository perforImaccountbasicRepository;
//        private readonly PerforExtractRepository perforExtractRepository;
//        private readonly PerforPerfirstRepository perforPerfirstRepository;
//        private readonly PerforPerallotRepository perforPerallotRepository;
//        private readonly PerforRepimportconfigRepository repimportconfigRepository;
//        private readonly PerforHospitalconfigRepository perforHospitalconfigRepository;

//        public ExtractService(ILogger<ExtractService> logger,
//            IHostingEnvironment environment,
//            IEmailService emailService,
//            PerSheetService perSheetService,
//            PerHeaderService perHeaderService,
//            PerforPersheetRepository perforPersheetRepository,
//            PerforImheaderRepository perforImheaderRepository,
//            PerforImemployeeRepository perforImemployeeRepository,
//            PerforImdataRepository perforImdataRepository,
//            PerforImaccountbasicRepository perforImaccountbasicRepository,
//            PerforExtractRepository perforExtractRepository,
//            PerforPerfirstRepository perforPerfirstRepository,
//            PerforPerallotRepository perforPerallotRepository,
//            PerforRepimportconfigRepository repimportconfigRepository,
//            PerforHospitalconfigRepository perforHospitalconfigRepository)
//        {
//            this.logger = logger;
//            this.environment = environment;
//            this.emailService = emailService;
//            this.perSheetService = perSheetService;
//            this.perHeaderService = perHeaderService;
//            this.perforPersheetRepository = perforPersheetRepository;
//            this.perforImheaderRepository = perforImheaderRepository;
//            this.perforImemployeeRepository = perforImemployeeRepository;
//            this.perforImdataRepository = perforImdataRepository;
//            this.perforImaccountbasicRepository = perforImaccountbasicRepository;
//            this.perforExtractRepository = perforExtractRepository;
//            this.perforPerfirstRepository = perforPerfirstRepository;
//            this.perforPerallotRepository = perforPerallotRepository;
//            this.repimportconfigRepository = repimportconfigRepository;
//            this.perforHospitalconfigRepository = perforHospitalconfigRepository;
//        }

//        /// <summary>
//        /// 获得首次模板路径
//        /// </summary>
//        /// <param name="hospitalId"></param>
//        /// <param name="type">1、历史绩效文件  2、首次模板文件</param>
//        /// <returns></returns>
//        public string GetFilepath(int hospitalId, out int type)
//        {
//            try
//            {
//                var firstList = perforPerfirstRepository.GetEntities(t => t.HospitalId == hospitalId);
//                var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId);
//                type = 2;
//                if ((allotList == null || !allotList.Any(t => t.States > 0)) && (firstList != null || !firstList.Any()))
//                    return firstList.OrderByDescending(t => t.CreateDate).FirstOrDefault().Path;

//                else
//                {
//                    type = 1;
//                    var allot = allotList.Where(t => t.Path != null && t.Path != "").OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).First();
//                    return allot.Path;
//                }
//            }
//            catch (Exception ex)
//            {
//                logger.LogError("获取提取模板文件路径" + ex.ToString());
//                type = 0;
//                return "";
//            }
//        }

//        public string ExtractData(int allotId, string mail, sys_hospital hospital, string firstPath)
//        {
//            logger.LogInformation($"提取绩效数据开始执行绩效数据提取任务{new { allotId, mail, hospital }}");
//            List<PerSheet> sheetList = new List<PerSheet>();
//            string filepath = "";
//            try
//            {
//                var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospital.ID);
//                var configList = perforHospitalconfigRepository.GetEntities(t => t.HospitalId == hospital.ID);
//                var firstList = perforPerfirstRepository.GetEntities(t => t.HospitalId == hospital.ID);
//                var scriptList = perforExtractRepository.GetEntities(t => t.HospitalId == hospital.ID);

//                if (configList == null || !configList.Any())
//                {
//                    logger.LogError("提取绩效数据 暂不支持自动提取绩效数据", "");
//                    throw new PerformanceException($"暂不支持自动提取绩效数据");
//                }

//                if (scriptList == null || !scriptList.Any())
//                {
//                    logger.LogError($"提取绩效数据 暂无该医院的配置信息和执行sql，无法进行数据提取", "");
//                    throw new PerformanceException($"暂无该医院的配置信息和执行sql，无法进行数据提取");
//                }

//                var hospitalConfig = configList.First();
//                string originalPath = "";
//                //判断是否是首次
//                if ((allotList == null || !allotList.Any(t => t.States > 0)) && (firstList != null || !firstList.Any()))
//                {
//                    logger.LogInformation($"提取绩效数据 当前绩效为首次提取,从EXCEL中获取信息", "");
//                    //var first = firstList.OrderByDescending(t => t.CreateDate).FirstOrDefault();
//                    //首次 从excel中获取人员信息，SHEET页信息，列头信息
//                    sheetList = GetFileData(firstPath);
//                    originalPath = firstPath;
//                }
//                else
//                {
//                    logger.LogInformation($"提取绩效数据 当前绩效为非首次提取,从数据库中获取信息", "");
//                    //非首次 从数据库中获取人员信息，SHEET页信息，列头信息
//                    if (allotList.Any(t => t.Path != null && t.Path != ""))
//                    {
//                        var allot = allotList.Where(t => t.Path != null && t.Path != "").OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).First();
//                        sheetList = GetRepositoryData(allot.ID);
//                        originalPath = firstPath;
//                    }
//                    else
//                    {
//                        logger.LogError($"提取绩效数据 历史绩效未上传文件", "");
//                        throw new PerformanceException($"历史绩效未上传文件");
//                    }
//                }
//                logger.LogInformation($"提取绩效数据 基础数据提取完成，", "");
//                var dpath = Path.Combine(environment.ContentRootPath, "Files", $"{hospital.ID}", "autoextract");
//                FileHelper.CreateDirectory(dpath);
//                string path = Path.Combine(dpath, $"绩效数据{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx");
//                //根据SHEET页信息，列头信息，创建EXCEL文件
//                if (WriteExcel(path, originalPath, sheetList, hospitalConfig, hospital.ID, allotList.First(t => t.ID == allotId), out filepath))
//                {
//                    logger.LogInformation($"提取绩效数据 基础数据提取完成，文件保存成功{filepath}", "");
//                    var allot = allotList.First(t => t.ID == allotId);
//                    //allot.ExtractPath = filepath;
//                    //if (!string.IsNullOrEmpty(filepath))
//                    //    perforPerallotRepository.Update(allot);
//                    ImportData(allot, configList);
//                    SendEmail(mail, filepath, $"{hospital.HosName}HIS数据提取成功", $"{hospital.HosName}在{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}成功提取。");
//                }
//            }
//            catch (PerformanceException ex)
//            {
//                logger.LogError("提取绩效数据" + ex.ToString());
//                SendEmail(mail, "", $"{hospital.HosName}HIS数据提取失败", $"{hospital.HosName}提取数据失败，{ex.Message}！");
//            }
//            catch (Exception ex)
//            {
//                logger.LogError("提取绩效数据" + ex.ToString());
//                SendEmail(mail, "", $"{hospital.HosName}HIS数据提取失败", $"{hospital.HosName}提取数据过程中出现异常情况，我们将尽快解决问题。给您带来的不便我们深感歉意！");
//            }
//            finally
//            {
//                var allot = perforPerallotRepository.GetEntity(t => t.ID == allotId);
//                if (allot != null)
//                {
//                    allot.IsExtracting = null;
//                    perforPerallotRepository.Update(allot);
//                }
//            }
//            return filepath;
//        }

//        /// <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 },
//            };
//            var imports = repimportconfigRepository.GetEntities(w => w.ScriptType == 1);
//            foreach (var import in imports)
//            {
//                var conf = configs.FirstOrDefault(w => w.HospitalId == allot.HospitalId && w.Id == import.ConfigId);
//                if (conf != null)
//                {
//                    if (import.TimeRange == "1")
//                    {
//                        pairs.Add("@year", allot.Year);
//                        pairs.Add("@month", allot.Month);
//                    }
//                    else if (import.TimeRange == "2")
//                    {
//                        pairs.Add("@year", allot.Year - 1);
//                        pairs.Add("@month", allot.Month);
//                    }
//                    else if (import.TimeRange == "3")
//                    {
//                        pairs.Add("@year", allot.Year);
//                        pairs.Add("@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());
//                    }
//                }
//            }
//        }

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

//        /// <summary>
//        /// 向EXCEL中写入数据
//        /// 人员信息由EXCEL中提供，或从上次绩效中提供
//        /// SHEET页在SQL提取中出现时，执行SQL脚本获得结果向EXCEL中填充
//        /// 向EXCEL中填充数据，填充时值与列头必须匹配
//        /// </summary>
//        /// <param name="newpath"></param>
//        /// <param name="originalPath"></param>
//        /// <param name="sheetList"></param>
//        /// <param name="hospitalConfig"></param>
//        /// <param name="hospitalId"></param>
//        private bool WriteExcel(string newpath, string originalPath, List<PerSheet> sheetList, sys_hospitalconfig hospitalConfig, int hospitalId, per_allot allot, out string filepath)
//        {
//            logger.LogInformation($"提取绩效数据 开始向EXCEL中写入数据，", "");
//            if (string.IsNullOrEmpty(originalPath))
//            {
//                logger.LogError($"提取绩效数据{originalPath}文件路径无效", "");
//                throw new PerformanceException($"{originalPath}文件路径无效");
//            }

//            var scriptList = perforExtractRepository.GetEntities(t => t.HospitalId == hospitalId && t.IsEnable == 1);

//            //根据SHEET页信息，列头信息，创建EXCEL文件
//            IWorkbook workbook = null;
//            FileStream file = null;
//            try
//            {
//                workbook = new XSSFWorkbook(originalPath);
//                foreach (var sheet in sheetList)
//                {
//                    var importSheet = workbook.GetSheet(sheet.SheetName);
//                    if (importSheet == null) continue;
//                    if (sheet.PerHeader == null) continue;

//                    logger.LogInformation($"提取绩效数据列头信息写入，", "");
//                    var maxHeaderRowNumber = sheet.PerHeader.Max(t => t.PointRow);
//                    sheet.PerHeader?.ForEach(t =>
//                    {
//                        if (t.IsHasChildren)
//                        {
//                            var maxnum = t.Children.Max(c => c.PointRow);
//                            maxHeaderRowNumber = maxHeaderRowNumber > maxnum ? maxHeaderRowNumber : maxnum;
//                        }
//                    });
//                    if (sheet.SheetType == SheetType.Workload)
//                        maxHeaderRowNumber += 1;
//                    //清空数据行
//                    for (int i = maxHeaderRowNumber + 1; i < importSheet.LastRowNum + 1; i++)
//                    {
//                        var importRow = importSheet.GetRow(i);
//                        if (importRow != null)
//                            importSheet.RemoveRow(importRow);
//                    }
//                    //取消合并单元格
//                    int mergedCount = importSheet.NumMergedRegions;
//                    for (int i = mergedCount - 1; i >= 0; i--)
//                    {
//                        var temp = importSheet.GetMergedRegion(i);
//                        if (temp.FirstRow > maxHeaderRowNumber)
//                            importSheet.RemoveMergedRegion(i);
//                    }

//                    //填充人员信息
//                    if (SheetType.Employee == sheet.SheetType && sheet.PerData != null && sheet.PerData.Any())
//                    {
//                        logger.LogInformation($"提取绩效数据填充人员信息，", "");
//                        var dataList = sheet.PerData.ConvertAll(new Converter<IPerData, PerDataEmployee>(t => (PerDataEmployee)t));
//                        for (int i = 0; i < dataList.Count; i++)
//                        {
//                            var importRow = importSheet.CreateRow(maxHeaderRowNumber + i + 1);
//                            Dictionary<string, Func<PerDataEmployee, object>> keyValues = new Dictionary<string, Func<PerDataEmployee, object>>
//                            {
//                                { "核算单元", (t) => t.AccountingUnit },
//                                { "医生姓名", (t) => t.DoctorName },
//                                { "职称", (t) => t.JobTitle },
//                                { "绩效基数核算参考对象", (t) => t.FitPeople },
//                                { "绩效基数核算系数", (t) => t.FitPeopleRatio },
//                                { "核算单元分类", (t) => t.AccountType },
//                                { "岗位系数", (t) => t.PostCoefficient },
//                                { "参加工作时间", (t) => t.WorkTime },
//                                { "考核得分率", (t) => t.ScoreAverageRate },
//                                { "出勤率", (t) => t.Attendance },
//                                //{ "核算单元医生数", (t) => t.PeopleNumber },
//                                //{ "工作量绩效", (t) => t.Workload },
//                                { "其他绩效", (t) => t.OthePerfor },
//                                { "医院奖罚", (t) => t.Punishment },
//                                { "调节系数", (t) => t.Adjust },
//                                //{ "发放系数", (t) => t.Grant },
//                            };
//                            foreach (var item in keyValues.Keys)
//                            {
//                                var headInfo = sheet.PerHeader.FirstOrDefault(t => t.CellValue == item);
//                                if (headInfo != null)
//                                {
//                                    var value = (keyValues[item].Invoke(dataList[i]) ?? "").ToString();
//                                    importRow.CreateCell(headInfo.PointCell).SetCellValue(Verify(value));
//                                }
//                            }
//                        }
//                    }

//                    //临床科室医护绩效测算表
//                    else if (SheetType.AccountBasic == sheet.SheetType && sheet.PerData != null && sheet.PerData.Any())
//                    {
//                        logger.LogInformation($"提取绩效数据 填充临床科室医护绩效测算表，", "");
//                        var dataList = sheet.PerData.ConvertAll(new Converter<IPerData, PerDataAccountBaisc>(t => (PerDataAccountBaisc)t));
//                        var children = new List<PerHeader>();
//                        foreach (var item in sheet.PerHeader)
//                        {
//                            var childItem = item.Children;
//                            if (childItem != null)
//                            {
//                                childItem.ForEach(t => t.CellValue = item.CellValue + t.CellValue);
//                                children = children.Union(childItem).ToList();
//                            }
//                        }
//                        for (int i = 0; i < dataList.Count; i++)
//                        {
//                            var importRow = importSheet.CreateRow(maxHeaderRowNumber + i + 1);
//                            Dictionary<string, Func<PerDataAccountBaisc, object>> keyValues = new Dictionary<string, Func<PerDataAccountBaisc, object>>
//                            {
//                                { "核算单元类型", (t) => t.UnitType },
//                                { "核算单元", (t) => t.AccountingUnit },
//                                { "科室名称", (t) => t.Department },
//                                { "医生组核算单元医生数量", (t) => t.Number },
//                                { "医生组基础系数", (t) => t.BasicFactor },
//                                { "医生组倾斜系数", (t) => t.SlopeFactor },
//                                { "医生组其他绩效1", (t) => t.OtherPerfor1 },
//                                { "医生组考核得分率", (t) => t.ScoringAverage },
//                                { "医生组医院奖罚", (t) => t.Extra },
//                                { "医生组其他绩效2", (t) => t.OtherPerfor2 },
//                                { "医生组调节系数", (t) => t.AdjustFactor },
//                            };
//                            foreach (var item in keyValues.Keys)
//                            {
//                                var headInfo = sheet.PerHeader.FirstOrDefault(t => t.CellValue == item)
//                                    ?? children?.FirstOrDefault(t => t.CellValue == item);
//                                if (headInfo != null)
//                                {
//                                    var value = (keyValues[item].Invoke(dataList[i]) ?? "").ToString();
//                                    if (Regex.Match(value.Trim(), @"(0*.0*)|0").ToString() != value.Trim())
//                                        importRow.CreateCell(headInfo.PointCell).SetCellValue(Verify(value));
//                                }
//                            }
//                        }
//                    }

//                    //SHEET页在SQL提取中出现时，执行SQL脚本获得结果向EXCEL中填充
//                    else if (scriptList.Any(t => sheet.SheetName.Contains(t.SheetName)))
//                    {
//                        logger.LogInformation($"提取绩效数据 执行SQL脚本获取数据，", "");
//                        var script = scriptList.First(t => sheet.SheetName.Contains(t.SheetName));
//                        if (!string.IsNullOrEmpty(script.ExecuteScript))
//                        {
//                            script.ExecuteScript = ReplaceParameter(script.ExecuteScript, allot);
//                            logger.LogInformation($"提取绩效数据 SQL脚本{script.ExecuteScript}，", "");
//                            var children = new List<PerHeader>();
//                            foreach (var item in sheet.PerHeader?.Select(t => t.Children))
//                            {
//                                if (item != null)
//                                    children = children.Union(item).ToList();
//                            }

//                            var connection = ConnectionBuilder.Create(DatabaseType.SqlServer, hospitalConfig.DbSource, hospitalConfig.DbName, hospitalConfig.DbUser, hospitalConfig.DbPassword);
//                            var dataList = perforExtractRepository.ExecuteScript(connection, script.ExecuteScript, null);
//                            logger.LogInformation($"提取绩效数据 写入SQL脚本执行结果", "");

//                            //用于查询核算单元
//                            var deptList = dataList.Where(t => t.ColumnName == "科室名称").Select(t => t.Value.ToString()).Distinct().ToList();
//                            var imdata = perforImdataRepository.GetAccountingUnit(hospitalId, (int)sheet.SheetType, deptList);

//                            //创建数据行
//                            foreach (var pointRow in dataList.Select(t => t.RowNumber).Distinct().OrderBy(t => t))
//                            {
//                                var importRow = importSheet.CreateRow(maxHeaderRowNumber + pointRow + 1);
//                                //写入单元格
//                                foreach (var data in dataList.Where(t => t.RowNumber == pointRow))
//                                {
//                                    var headInfo = sheet.PerHeader.FirstOrDefault(t => t.CellValue == data.ColumnName)
//                                        ?? children?.FirstOrDefault(t => t.CellValue == data.ColumnName);
//                                    if (headInfo != null)
//                                    {
//                                        importRow.CreateCell(headInfo.PointCell).SetCellValue(Verify(data.Value.ToString()));
//                                    }
//                                    if (data.ColumnName == "科室名称")
//                                    {
//                                        //补充核算单元
//                                        if (sheet.SheetType == SheetType.Income)
//                                        {
//                                            var doctor = imdata.FirstOrDefault(t => t.UnitType == 1 && t.Department == data.Value.ToString());
//                                            if (doctor != null)
//                                                importRow.CreateCell(0).SetCellValue(doctor.AccountingUnit);
//                                            var nurse = imdata.FirstOrDefault(t => t.UnitType == 2 && t.Department == data.Value.ToString());
//                                            if (nurse != null)
//                                                importRow.CreateCell(1).SetCellValue(nurse.AccountingUnit);
//                                        }
//                                        else if (sheet.SheetType == SheetType.Workload)
//                                        {
//                                            if (sheet.SheetName.Contains("医生"))
//                                            {
//                                                var doctor = imdata.FirstOrDefault(t => t.UnitType == 1 && t.Department == data.Value.ToString());
//                                                if (doctor != null)
//                                                    importRow.CreateCell(0).SetCellValue(doctor.AccountingUnit);
//                                            }
//                                            else
//                                            {
//                                                var nurse = imdata.FirstOrDefault(t => t.UnitType == 2 && t.Department == data.Value.ToString());
//                                                if (nurse != null)
//                                                    importRow.CreateCell(0).SetCellValue(nurse.AccountingUnit);
//                                            }
//                                        }
//                                    }
//                                }
//                            }
//                        }
//                    }
//                }

//                logger.LogInformation($"提取绩效数据 数据写入完成，开始保存文件{newpath}", "");
//                file = new FileStream(newpath, FileMode.Create);
//                workbook.Write(file);
//                filepath = newpath;
//                return true;
//            }
//            catch (Exception ex)
//            {
//                logger.LogError($"提取绩效数据 数据写入出现异常{ex.ToString()}");
//                throw ex;
//            }
//            finally
//            {
//                workbook.Close();
//                file.Close();
//                file.Dispose();
//                GC.Collect();
//            }
//        }

//        /// <summary>
//        /// 替换SQL参数
//        /// </summary>
//        /// <param name="executeScript"></param>
//        /// <param name="allot"></param>
//        /// <returns></returns>
//        private string ReplaceParameter(string executeScript, per_allot allot)
//        {
//            var basicTime = new DateTime(allot.Year, allot.Month, 1);
//            string beginTime = basicTime.ToString("yyyy-MM-dd HH:mm:ss");
//            string endTime = basicTime.AddMonths(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss");
//            executeScript = Regex.Replace(executeScript, "@beginTime", $"'{beginTime}'", RegexOptions.IgnoreCase);
//            executeScript = Regex.Replace(executeScript, "@endTime", $"'{endTime}'", RegexOptions.IgnoreCase);
//            return executeScript;
//        }

//        /// <summary>
//        /// 从数据库中获取sheet及列头
//        /// </summary>
//        /// <param name="allotId"></param>
//        /// <returns></returns>
//        private List<PerSheet> GetRepositoryData(int allotId)
//        {
//            List<PerSheet> sheetList = new List<PerSheet>();
//            //获取最近一次绩效
//            var perSheetList = perforPersheetRepository.GetEntities(t => t.AllotID == allotId);
//            if (perSheetList == null || perSheetList.Count == 0)
//                return sheetList;


//            foreach (var sheet in perSheetList)
//            {
//                PerSheet perSheet = new PerSheet()
//                {
//                    SheetName = sheet.SheetName,
//                    SheetType = (SheetType)sheet.SheetType,
//                    ModuleName = EnumHelper.GetDescription((SheetType)sheet.SheetType),
//                };
//                var perHeadList = perforImheaderRepository.GetEntities(t => t.SheetID == sheet.ID);
//                perSheet.PerHeader = AutoMapper.Mapper.Map<List<PerHeader>>(perHeadList);
//                if (SheetType.Employee == (SheetType)sheet.SheetType)
//                {
//                    perSheet.PerHeader = GetHeader((SheetType)sheet.SheetType);
//                    var employeeList = perforImemployeeRepository.GetEntities(t => t.AllotID == sheet.AllotID);
//                    var perEmployeeList = AutoMapper.Mapper.Map<List<PerDataEmployee>>(employeeList);
//                    perSheet.PerData = perEmployeeList?.ConvertAll(new Converter<PerDataEmployee, IPerData>(m => m));
//                }
//                else if (SheetType.SpecialUnit == (SheetType)sheet.SheetType)
//                {
//                    perSheet.PerHeader = GetHeader((SheetType)sheet.SheetType);
//                }
//                else if (SheetType.AccountBasic == (SheetType)sheet.SheetType)
//                {
//                    perSheet.PerHeader = GetHeader((SheetType)sheet.SheetType);
//                    var basicList = perforImaccountbasicRepository.GetEntities(t => t.AllotID == sheet.AllotID);
//                    var perBasicList = AutoMapper.Mapper.Map<List<PerDataAccountBaisc>>(basicList);
//                    perSheet.PerData = perBasicList?.ConvertAll(new Converter<PerDataAccountBaisc, IPerData>(m => m));
//                }
//                sheetList.Add(perSheet);
//            }
//            return sheetList;
//        }

//        /// <summary>
//        /// 获取sheet及列头
//        /// </summary>
//        /// <param name="path"></param>
//        /// <returns></returns>
//        private List<PerSheet> GetFileData(string path)
//        {
//            if (!FileHelper.IsExistFile(path))
//                throw new PerformanceException($"{path}文件不存在");
//            List<PerSheet> sheetList = new List<PerSheet>();
//            FileStream fs = null;
//            IWorkbook workbook = null;
//            try
//            {
//                fs = new FileStream(path, FileMode.Open, FileAccess.Read);
//                var version = FileHelper.GetExtension(path) == ".xlsx" ? ExcelVersion.xlsx : ExcelVersion.xls;
//                workbook = (version == ExcelVersion.xlsx) ? (IWorkbook)(new XSSFWorkbook(fs)) : (IWorkbook)(new HSSFWorkbook(fs));
//                for (int i = 0; i < workbook.NumberOfSheets; i++)
//                {
//                    var sheet = workbook.GetSheetAt(i);
//                    var sheetType = perSheetService.GetSheetType(sheet.SheetName);

//                    if (SheetType.Unidentifiable != sheetType)
//                    {
//                        var sheetRead = PerSheetDataFactory.GetDataRead(sheetType);
//                        var perHeader = perHeaderService.GetPerHeader(sheet, sheetRead.Point);

//                        PerSheet perSheet = new PerSheet()
//                        {
//                            SheetName = sheet.SheetName,
//                            SheetType = sheetType,
//                            ModuleName = EnumHelper.GetDescription(sheetType),
//                            PerHeader = perHeader
//                        };
//                        if (SheetType.Employee == sheetType)
//                        {
//                            var headerReverse = perHeaderService.GetPerHeaderReverse(perHeader);
//                            perSheet.PerData = sheetRead.ReadData(sheet, headerReverse);
//                        }
//                        if (SheetType.AccountBasic == sheetType)
//                        {
//                            var headerReverse = perHeaderService.GetPerHeaderReverse(perHeader);
//                            perSheet.PerData = sheetRead.ReadData(sheet, headerReverse);
//                        }
//                        sheetList.Add(perSheet);
//                    }
//                }
//            }
//            catch (Exception ex)
//            {
//                throw ex;
//            }
//            finally
//            {
//                workbook.Close();
//                fs.Close();
//                fs.Dispose();
//            }

//            return sheetList;
//        }

//        #region 校验数据格式
//        /// <summary>
//        /// 校验数据格式，并转换
//        /// </summary>
//        /// <param name="obj"></param>
//        /// <returns></returns>
//        public dynamic Verify(string obj)
//        {
//            try
//            {
//                //判断值是否为double类型
//                if (!string.IsNullOrEmpty(obj) && Regex.Match(obj.Trim(), @"([1-9]\d*\.?\d*)|(0\.\d*[1-9]?\d*)|0").ToString() == obj.Trim())
//                    return ConvertHelper.To<double>(obj);
//                //判断值是否为日期格式
//                else if (!string.IsNullOrEmpty(obj) && Regex.Match(obj.Trim(), @"(19|20)\d{2}(-|/)[01]?\d(-|/)[0123]?\d( [012]?\d\:\d{2}\:\d{2})?").ToString() == obj.Trim())
//                    return ConvertHelper.To<DateTime>(obj).ToString("yyyy/M/d");
//                else
//                    return obj;
//            }
//            catch
//            {
//                return obj;
//            }
//        }
//        #endregion

//        #region 获取列头
//        /// <summary>
//        /// 获取列头
//        /// </summary>
//        /// <param name="sheetType">sheet页类型</param>
//        /// <returns></returns>
//        public List<PerHeader> GetHeader(SheetType sheetType)
//        {
//            List<PerHeader> header = new List<PerHeader>();
//            if (SheetType.Employee == sheetType)
//            {
//                header = new List<PerHeader>
//                {
//                    new PerHeader { CellValue = "核算单元分类",   //列名
//                        MergeCell = 1,  //单元格占据的列数
//                        MergeRow = 1,   //单元格占据的行数
//                        PointCell = 0,  //单元格列
//                        PointRow = 1    //单元格行
//                    },
//                    new PerHeader { CellValue = "核算单元", MergeCell = 1, MergeRow = 1, PointCell = 1, PointRow = 1 },
//                    new PerHeader { CellValue = "绩效基数核算参考对象", MergeCell = 1, MergeRow = 1, PointCell = 2, PointRow = 1 },
//                    new PerHeader { CellValue = "绩效基数核算系数", MergeCell = 1, MergeRow = 1, PointCell = 3, PointRow = 1 },
//                    new PerHeader { CellValue = "医生姓名", MergeCell = 1, MergeRow = 1, PointCell = 4, PointRow = 1 },
//                    new PerHeader { CellValue = "职称", MergeCell = 1, MergeRow = 1, PointCell = 5, PointRow = 1 },
//                    new PerHeader { CellValue = "岗位系数", MergeCell = 1, MergeRow = 1, PointCell = 6, PointRow = 1 },
//                    new PerHeader { CellValue = "参加工作时间", MergeCell = 1, MergeRow = 1, PointCell = 7, PointRow = 1 },
//                    new PerHeader { CellValue = "考核得分率", MergeCell = 1, MergeRow = 1, PointCell = 8, PointRow = 1 },
//                    new PerHeader { CellValue = "出勤率", MergeCell = 1, MergeRow = 1, PointCell = 9, PointRow = 1 },
//                    new PerHeader { CellValue = "核算单元医生数", MergeCell = 1, MergeRow = 1, PointCell = 10, PointRow = 1 },
//                    new PerHeader { CellValue = "工作量绩效", MergeCell = 1, MergeRow = 1, PointCell = 11, PointRow = 1 },
//                    new PerHeader { CellValue = "其他绩效", MergeCell = 1, MergeRow = 1, PointCell = 12, PointRow = 1 },
//                    new PerHeader { CellValue = "医院奖罚", MergeCell = 1, MergeRow = 1, PointCell = 13, PointRow = 1 },
//                    new PerHeader { CellValue = "调节系数", MergeCell = 1, MergeRow = 1, PointCell = 14, PointRow = 1 },
//                    new PerHeader { CellValue = "发放系数", MergeCell = 1, MergeRow = 1, PointCell = 15, PointRow = 1 },
//                };
//            }
//            else if (SheetType.AccountBasic == sheetType)
//            {
//                header = new List<PerHeader>
//                {
//                    new PerHeader { CellValue = "核算单元（医生组）", MergeCell = 1, MergeRow = 2, PointCell = 0, PointRow = 1 },
//                    new PerHeader { CellValue = "核算单元（护理组）", MergeCell = 1, MergeRow = 2, PointCell = 1, PointRow = 1 },
//                    new PerHeader { CellValue = "医生组", MergeCell = 8, MergeRow = 1, PointCell = 2, PointRow = 1,
//                        Children = new List<PerHeader>{
//                            new PerHeader { CellValue = "核算单元医生数量", MergeCell = 1, MergeRow = 1, PointCell = 2, PointRow = 2 },
//                            new PerHeader { CellValue = "基础系数", MergeCell = 1, MergeRow = 1, PointCell = 3, PointRow = 2 },
//                            new PerHeader { CellValue = "倾斜系数", MergeCell = 1, MergeRow = 1, PointCell = 4, PointRow = 2 },
//                            new PerHeader { CellValue = "其他绩效1", MergeCell = 1, MergeRow = 1, PointCell = 5, PointRow = 2 },
//                            new PerHeader { CellValue = "考核得分率", MergeCell = 1, MergeRow = 1, PointCell = 6, PointRow = 2 },
//                            new PerHeader { CellValue = "医院奖罚", MergeCell = 1, MergeRow = 1, PointCell = 7, PointRow = 2 },
//                            new PerHeader { CellValue = "其他绩效2", MergeCell = 1, MergeRow = 1, PointCell = 8, PointRow = 2 },
//                            new PerHeader { CellValue = "调节系数", MergeCell = 1, MergeRow = 1, PointCell = 9, PointRow = 2 },
//                        }
//                    },
//                    new PerHeader { CellValue = "护理组", MergeCell = 8, MergeRow = 1, PointCell = 3, PointRow = 1,
//                        Children = new List<PerHeader>{
//                            new PerHeader { CellValue = "核算单元护士数量", MergeCell = 1, MergeRow = 1, PointCell = 10, PointRow = 2 },
//                            new PerHeader { CellValue = "基础系数", MergeCell = 1, MergeRow = 1, PointCell = 11, PointRow = 2 },
//                            new PerHeader { CellValue = "倾斜系数", MergeCell = 1, MergeRow = 1, PointCell = 12, PointRow = 2 },
//                            new PerHeader { CellValue = "其他绩效1", MergeCell = 1, MergeRow = 1, PointCell = 13, PointRow = 2 },
//                            new PerHeader { CellValue = "考核得分率", MergeCell = 1, MergeRow = 1, PointCell = 14, PointRow = 2 },
//                            new PerHeader { CellValue = "医院奖罚", MergeCell = 1, MergeRow = 1, PointCell = 15, PointRow = 2 },
//                            new PerHeader { CellValue = "其他绩效2", MergeCell = 1, MergeRow = 1, PointCell = 16, PointRow = 2 },
//                            new PerHeader { CellValue = "调节系数", MergeCell = 1, MergeRow = 1, PointCell = 17, PointRow = 2 },
//                        }
//                    },
//                };
//            }
//            else if (SheetType.SpecialUnit == sheetType)
//            {
//                header = new List<PerHeader>
//                {
//                    new PerHeader { CellValue = "科室", MergeCell = 1, MergeRow = 1, PointCell = 0, PointRow = 1 },
//                    new PerHeader { CellValue = "人数", MergeCell = 1, MergeRow = 1, PointCell = 1, PointRow = 1 },
//                    new PerHeader { CellValue = "量化指标", MergeCell = 1, MergeRow = 1, PointCell = 2, PointRow = 1 },
//                    new PerHeader { CellValue = "数量", MergeCell = 1, MergeRow = 1, PointCell = 3, PointRow = 1 },
//                    new PerHeader { CellValue = "量化指标绩效分值", MergeCell = 1, MergeRow = 1, PointCell = 4, PointRow = 1 },
//                    new PerHeader { CellValue = "考核得分率", MergeCell = 1, MergeRow = 1, PointCell = 5, PointRow = 1 },
//                    new PerHeader { CellValue = "医院奖罚", MergeCell = 1, MergeRow = 1, PointCell = 6, PointRow = 1 },
//                    new PerHeader { CellValue = "考核得分率", MergeCell = 1, MergeRow = 1, PointCell = 7, PointRow = 1 },
//                    new PerHeader { CellValue = "调节系数", MergeCell = 1, MergeRow = 1, PointCell = 8, PointRow = 1 },
//                };
//            }
//            return header;
//        }
//        #endregion
//    }
//}
