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.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 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,
            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.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)
            {
                LogHelper.Error(ex.ToString(), "获取提取模板文件路径", "异常");
                type = 0;
                return "";
            }
        }

        public string ExtractData(int allotId, string mail, sys_hospital hospital, string firstPath)
        {
            LogHelper.Information($"开始执行绩效数据提取任务{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())
                {
                    LogHelper.Error("暂不支持自动提取绩效数据", "提取绩效数据");
                    throw new PerformanceException($"暂不支持自动提取绩效数据");
                }

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

                var hospitalConfig = configList.First();
                string originalPath = "";
                //判断是否是首次
                if ((allotList == null || !allotList.Any(t => t.States > 0)) && (firstList != null || !firstList.Any()))
                {
                    LogHelper.Information($"当前绩效为首次提取,从EXCEL中获取信息", "提取绩效数据");
                    //var first = firstList.OrderByDescending(t => t.CreateDate).FirstOrDefault();
                    //首次 从excel中获取人员信息，SHEET页信息，列头信息
                    sheetList = GetFileData(firstPath);
                    originalPath = firstPath;
                }
                else
                {
                    LogHelper.Information($"当前绩效为非首次提取,从数据库中获取信息", "提取绩效数据");
                    //非首次 从数据库中获取人员信息，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
                    {
                        LogHelper.Error($"历史绩效未上传文件", "提取绩效数据");
                        throw new PerformanceException($"历史绩效未上传文件");
                    }
                }
                LogHelper.Information($"基础数据提取完成，", "提取绩效数据");
                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))
                {
                    LogHelper.Information($"基础数据提取完成，文件保存成功{filepath}", "提取绩效数据");
                    //var allot = allotList.First(t => t.ID == allotId);
                    //allot.ExtractPath = filepath;
                    //if (!string.IsNullOrEmpty(filepath))
                    //    perforPerallotRepository.Update(allot);

                    SendEmail(mail, filepath, $"{hospital.HosName}HIS数据提取成功", $"{hospital.HosName}在{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}成功提取。");
                }
            }
            catch (PerformanceException ex)
            {
                logger.LogError(ex.ToString());
                LogHelper.Error(ex.ToString(), "提取绩效数据", "异常");
                SendEmail(mail, "", $"{hospital.HosName}HIS数据提取失败", $"{hospital.HosName}提取数据失败，{ex.Message}！");
            }
            catch (Exception ex)
            {
                logger.LogError(ex.ToString());
                LogHelper.Error(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>
        /// 发送邮件
        /// </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)
        {
            LogHelper.Information($"开始向EXCEL中写入数据，", "提取绩效数据");
            if (string.IsNullOrEmpty(originalPath))
            {
                LogHelper.Error($"{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;

                    LogHelper.Information($"列头信息写入，", "提取绩效数据");
                    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())
                    {
                        LogHelper.Information($"填充人员信息，", "提取绩效数据");
                        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())
                    {
                        LogHelper.Information($"填充临床科室医护绩效测算表，", "提取绩效数据");
                        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)))
                    {
                        LogHelper.Information($"执行SQL脚本获取数据，", "提取绩效数据");
                        var script = scriptList.First(t => sheet.SheetName.Contains(t.SheetName));
                        if (!string.IsNullOrEmpty(script.ExecuteScript))
                        {
                            script.ExecuteScript = ReplaceParameter(script.ExecuteScript, allot);
                            LogHelper.Information($"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);
                            LogHelper.Information($"写入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);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                LogHelper.Information($"数据写入完成，开始保存文件{newpath}", "提取绩效数据");
                file = new FileStream(newpath, FileMode.Create);
                workbook.Write(file);
                filepath = newpath;
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.Error($"数据写入出现异常{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
    }
}
