using Dapper;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using NPOI.SS.UserModel;
using Performance.DtoModels;
using Performance.EntityModels;
using Performance.Infrastructure;
using Performance.Repository;
using Performance.Services.ExtractExcelService.SheetDataWrite;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace Performance.Services.ExtractExcelService
{
    public class ExtractService : IAutoInjection
    {
        private readonly ILogger logger;
        private readonly IConfiguration _configuration;
        private readonly DapperService service;
        private readonly LogManageService logService;
        private readonly QueryService queryService;
        private readonly PersonService personService;
        private readonly PerSheetService perSheetService;
        private readonly CostTransferDataWrite costTransfer;
        private readonly DictionaryService dictionaryService;
        private readonly EmployeeService employeeService;
        private readonly CustomDataWrite customDataWrite;
        private readonly PerforPerallotRepository perallotRepository;
        private readonly PerforCollectdataRepository collectdataRepository;
        private readonly PerforExtypeRepository extypeRepository;
        private readonly PerforPeremployeeRepository peremployeeRepository;
        private readonly PerforPerdeptdicRepository perdeptdicRepository;
        private readonly PerforCofdrugtypefactorRepository drugtypefactorRepository;
        private readonly PerforExresultRepository exresultRepository;
        private readonly DapperService dapperService;

        public ExtractService(
            ILogger<ExtractService> logger,
            IConfiguration configuration,
            DapperService service,
            LogManageService logService,
            QueryService queryService,
            PersonService personService,
            PerSheetService perSheetService,
            CostTransferDataWrite costTransfer,
            DictionaryService dictionaryService,
            EmployeeService employeeService,
            CustomDataWrite customDataWrite,
            PerforPerallotRepository perallotRepository,
            PerforCollectdataRepository collectdataRepository,
            PerforExtypeRepository extypeRepository,
            PerforPeremployeeRepository peremployeeRepository,
            PerforPerdeptdicRepository perdeptdicRepository,
            PerforCofdrugtypefactorRepository drugtypefactorRepository,
            PerforExresultRepository exresultRepository,
            DapperService dapperService
            )
        {
            this.logger = logger;
            _configuration = configuration;
            this.service = service;
            this.logService = logService;
            this.queryService = queryService;
            this.personService = personService;
            this.perSheetService = perSheetService;
            this.costTransfer = costTransfer;
            this.dictionaryService = dictionaryService;
            this.employeeService = employeeService;
            this.customDataWrite = customDataWrite;
            this.perallotRepository = perallotRepository;
            this.collectdataRepository = collectdataRepository;
            this.extypeRepository = extypeRepository;
            this.peremployeeRepository = peremployeeRepository;
            this.perdeptdicRepository = perdeptdicRepository;
            this.drugtypefactorRepository = drugtypefactorRepository;
            this.exresultRepository = exresultRepository;
            this.dapperService = dapperService;
        }

        /// <summary>
        /// 抽取绩效文件
        /// </summary>
        /// <param name="allotId">抽取绩效Id</param>
        /// <param name="hospitalId">医院Id</param>
        /// <param name="email">邮箱地址</param>
        /// <param name="groupName">即时日志分组名称</param>
        /// <param name="filePath">历史提交文件地址</param>
        /// <param name="isSingle">抽取是否在同一项目</param>
        public string Main(int allotId, int hospitalId, string email, string groupName, string filePath = null, bool isSingle = false)
        {
            groupName = allotId.ToString();
            string extractFilePath = "";
            per_allot allot = null;
            IWorkbook workbook = null;
            IDbConnection connection = peremployeeRepository.GetDbConnection();
            try
            {
                logService.ClearExtractLog(allotId);

                logService.ReturnTheLog(allotId, groupName, 2, "等待提取", $"确认配置信息是否可完成数据提取...", 1, isSingle);

                var allots = perallotRepository.GetEntities(t => t.HospitalId == hospitalId);
                if (allots == null || !allots.Any(t => t.ID == allotId)) throw new Exception("绩效不存在");

                allot = allots.First(t => t.ID == allotId);
                var dict = new Dictionary<ExDataDict, object>();
                var scripts = new List<ex_script>();

                logService.ReturnTheLog(allotId, groupName, 3, "", 5, 1, isSingle);

                queryService.ClearConnectionPools();
                queryService.ClearHistoryData(allot.ID, groupName, isSingle);

                service.SyncDataToResult(allotId);

                var data = exresultRepository.GetEntities(t => t.AllotId == allotId);

                data.AddRange(queryService.Handler(hospitalId, allot, groupName, isSingle, ref dict, ref scripts));
                var standData = StandDataFormat(hospitalId, allotId, scripts, data);

                dictionaryService.Handler(hospitalId, allot, groupName, isSingle);

                var statesArray = new int[] { (int)AllotStates.绩效下发, (int)AllotStates.归档 };

                var templateFilePath = ExtractHelper.GetExtractFile(hospitalId, allot, ref extractFilePath, filePath);
                logService.ReturnTheLog(allotId, groupName, 2, "创建文件", $"模板文件: {FileHelper.GetFileName(templateFilePath)}", 1, isSingle);

                if (!FileHelper.IsExistFile(templateFilePath)) throw new Exception("抽取文件创建失败");

                workbook = ExcelHelper.GetWorkbook(templateFilePath);
                if (workbook == null) throw new Exception("文件读取失败");

                WriteDataToFile(workbook, allot, dict, standData, groupName, isSingle);

                allot.IsExtracting = isSingle ? 2 : 0;
                allot.ExtractPath = extractFilePath;

                logService.ReturnTheLog(allotId, groupName, 2, "写入数据", $"写入数据至Excel文件", 1, isSingle);

                workbook.ForceFormulaRecalculation();
                using (FileStream file = new FileStream(extractFilePath, FileMode.OpenOrCreate))
                {
                    workbook.Write(file);
                }
                workbook.Close();
            }
            catch (Exception ex)
            {
                allot.IsExtracting = 3;
                logService.ReturnTheLog(allotId, groupName, 2, "提取完成", $"绩效数据提取失败", 4, isSingle);
                logger.LogError("提取数据中发生异常: " + ex.ToString());
            }
            finally
            {
                logService.ReturnTheLog(allotId, groupName, 3, "", 100, 5, isSingle);
                if (allot.IsExtracting != 3)
                    logService.ReturnTheLog(allotId, groupName, 2, "提取完成", $"绩效数据提取成功", 5, isSingle);
                UpdateAllot(connection, allot);
            }
            return extractFilePath;
        }

        /// <summary>
        /// 数据写入
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="allot"></param>
        /// <param name="exdict"></param>
        /// <param name="extractDto"></param>
        /// <param name="groupName"></param>
        /// <param name="isSingle"></param>
        private void WriteDataToFile(IWorkbook workbook, per_allot allot, Dictionary<ExDataDict, object> exdict, List<ExtractTransDto> extractDto, string groupName, bool isSingle)
        {
            ExcelStyle style = new ExcelStyle(workbook);

            var models = exdict[ExDataDict.ExModule] as List<ex_module>;
            ExtractHelper.CreateNotExistSheet(models, workbook);

            var employeeDict = peremployeeRepository.GetEntities(t => t.AllotId == allot.ID);
            var collectData = collectdataRepository.GetEntities(t => t.AllotID == allot.ID && t.Status == 3);

            var drugfactorData = drugtypefactorRepository.GetEntities(t => t.AllotID == allot.ID) ?? new List<cof_drugtype_factor>();
            if (!exdict.ContainsKey(ExDataDict.IncomeFactor))
                exdict.Add(ExDataDict.IncomeFactor, drugfactorData);

            logger.LogInformation($"allotId: {allot.ID}: 总金额 - {extractDto?.Sum(s => s.Value ?? 0)}");

            WriteDataFactory factory = new WriteDataFactory();
            var types = new List<SheetType> { SheetType.OtherIncome, SheetType.Income, SheetType.Expend, SheetType.Workload, SheetType.OtherWorkload/*, SheetType.AccountBasic*/ };
            types.AddRange(OtherConfigSheet);
            decimal ratio = 60m;

            //string accountBasicSheetName = "";
            //for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
            //{
            //    var sheetName = workbook.GetSheetAt(sheetIndex).SheetName;
            //    if (!sheetName.StartsWith("4.1"))
            //        continue;

            //    accountBasicSheetName = sheetName;
            //    break;
            //}

            //var accountBasicSheet = workbook.GetSheet(accountBasicSheetName);
            //HandleSheet(workbook, allot, exdict, extractDto, groupName, isSingle, accountBasicSheet, ratio, types, factory, style, employeeDict, collectData);

            for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
            {
                var sheet = workbook.GetSheetAt(sheetIndex);

                HandleSheet(workbook, allot, exdict, extractDto, groupName, isSingle, sheet, ratio, types, factory, style, employeeDict, collectData);
            }

            customDataWrite.WriteDataToCustom(workbook, allot, style, allot.ID, groupName, isSingle);
        }

        private void HandleSheet(IWorkbook workbook, per_allot allot, Dictionary<ExDataDict, object> exdict, List<ExtractTransDto> extractDto, string groupName, bool isSingle,
            ISheet sheet, decimal ratio, List<SheetType> types, WriteDataFactory factory, ExcelStyle style, List<per_employee> employeeDict, List<collect_data> collectData)
        {
            try
            {
                if (sheet == null) return;

                string sheetName = sheet.SheetName.NoBlank();

                ratio += 40m / workbook.NumberOfSheets;
                var sheetType = perSheetService.GetSheetType(sheet.SheetName);
                if (sheetType == SheetType.Unidentifiable) return;

                logService.ReturnTheLog(allot.ID, groupName, 3, "", ratio > 99 ? 99 : ratio, 1, isSingle);
                logService.ReturnTheLog(allot.ID, groupName, 2, "写入数据", $"sheet“{sheet.SheetName}”开始写入数据", 1, isSingle);

                var point = PerSheetDataFactory.GetDataRead(sheetType)?.Point;
                if (sheetType == SheetType.OtherWorkload) point = PerSheetDataFactory.GetDataRead(SheetType.Workload)?.Point;

                if (types.Contains(sheetType) && point != null && point.DataFirstCellNum.HasValue)
                {
                    logger.LogInformation($"{sheetName}开始清除历史数据。point：{JsonHelper.Serialize(point)}");
                    // 何明清舟山医院，忽略收入表清楚历史数据  默认关闭
                    var ignoreSheetTypeIncomeClear = _configuration.GetValue<bool>("Application:IgnoreSheetTypeIncomeClear", false);
                    ExtractHelper.ClearSheetPartialData(sheet, point, sheetType, ignoreSheetTypeIncomeClear);
                }

                var customer = factory.GetWriteData(sheetType, logger);
                if (customer != null)
                {
                    var collects = collectData?.Where(t => t.SheetName.StartsWith(sheetName.GetNo())).ToList();
                    customer.WriteCollectData(sheet, point, sheetType, style, collects, exdict);

                    var exdata = extractDto.Where(t => t.SheetName.StartsWith(sheetName.GetNo()))?.ToList();
                    if (exdata != null)
                    {
                        logger.LogInformation($"{sheetName}: 总金额 - {exdata.Sum(s => s.Value ?? 0)}; 科室 - {string.Join(",", exdata.Select(s => s.Department).Distinct())}");
                    }
                    var data = GetDataBySheetType(allot.HospitalId, sheetType, exdata, employeeDict);
                    customer.WriteSheetData(sheet, point, sheetType, style, data, exdict);
                }

                if (sheetName.StartsWith("1.0.1"))
                    costTransfer.WriteSheetData(sheet, point, style, allot.ID, allot.HospitalId);

                logService.ReturnTheLog(allot.ID, groupName, 2, "写入数据", $"sheet“{sheet.SheetName}”已完成数据写入", 1, isSingle);
            }
            catch (Exception ex)
            {
                logger.LogError("写入数据时发生异常：" + ex);
            }
        }

        private object GetDataBySheetType(int hospitalId, SheetType sheetType, List<ExtractTransDto> extractDto, List<per_employee> employeeDict)
        {
            switch (sheetType)
            {
                case SheetType.Employee:
                case SheetType.ClinicEmployee:
                    return employeeDict;

                case SheetType.AccountBasic:
                    return dapperService.GetAccountBasicAccountingUnit(hospitalId);

                default:
                    return extractDto;
            }
        }

        /// <summary>
        /// 标准数据格式, 匹配科室字典
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="hospitalId"></param>
        /// <param name="results"></param>
        /// <returns></returns>
        private List<ExtractTransDto> StandDataFormat(int hospitalId, int allotId, List<ex_script> scripts, List<ex_result> results)
        {
            if (results == null || !results.Any()) return new List<ExtractTransDto>();

            results.ForEach(t =>
            {
                t.Category = string.IsNullOrEmpty(t.Category) ? "（空白）" : t.Category;
                t.Department = string.IsNullOrEmpty(t.Department) ? "（空白）" : t.Department;
            });

            var types = extypeRepository.GetEntities(w => w.HospitalId == hospitalId) ?? new List<ex_type>();

            var dict = dapperService.GetDepartments(allotId)?.ToList();
            if (dict == null || !dict.Any())
            {
                return results.GroupBy(t => new { t.Department, t.Category, t.Source }).Select(t => new ExtractTransDto
                {
                    SheetName = t.Key.Source,
                    Department = t.Key.Department,
                    Category = t.Key.Category,
                    Value = t.Sum(group => group.Fee) == 0 ? null : t.Sum(group => group.Fee),
                    EName = types.FirstOrDefault(w => w.Id == t.FirstOrDefault().TypeId)?.EName
                }).ToList();
            }

            dict.ForEach(t =>
            {
                t.Department = WriteDataHelper.HasValue(t.Department, t.HISDeptName);
            });

            var data = results
                .GroupJoin(dict, outer => new { Department = outer.Department }, inner => new { Department = inner.HISDeptName }, (outer, inner) => new { outer, inner })
                .Select(t =>
                {
                    Func<DeptdicResponse, bool> exp =
                    t.outer.Source.StartsWith("3.1")
                    ? (e) => !string.IsNullOrEmpty(e.InpatDoctorAccounting.AccountingUnit)
                          || !string.IsNullOrEmpty(e.OutDoctorAccounting.AccountingUnit)
                          || !string.IsNullOrEmpty(e.InpatTechnicAccounting.AccountingUnit)
                          || !string.IsNullOrEmpty(e.OutTechnicAccounting.AccountingUnit)
                    : (
                        exp = t.outer.Source.StartsWith("3.2")
                        ? (e) => !string.IsNullOrEmpty(e.InpatNurseAccounting.AccountingUnit) || !string.IsNullOrEmpty(e.OutNurseAccounting.AccountingUnit)
                        : (e) => true
                      );

                    #region 根据sheet区分医生或者护理（暂时不做处理）

                    //Func<DeptdicResponse, bool> exp =
                    //                  exp = (t.outer.Source.Contains("护士") || t.outer.Source.Contains("护理"))
                    //                  ? (e) => !string.IsNullOrEmpty(e.InpatNurseAccounting.AccountingUnit) || !string.IsNullOrEmpty(e.OutNurseAccounting.AccountingUnit)
                    //                  : (e) => !string.IsNullOrEmpty(e.InpatDoctorAccounting.AccountingUnit)
                    //                    || !string.IsNullOrEmpty(e.OutDoctorAccounting.AccountingUnit)
                    //                    || !string.IsNullOrEmpty(e.InpatTechnicAccounting.AccountingUnit)
                    //                    || !string.IsNullOrEmpty(e.OutTechnicAccounting.AccountingUnit)
                    //                    || !string.IsNullOrEmpty(e.SpecialAccounting.AccountingUnit);

                    #endregion

                    var dept = !string.IsNullOrEmpty(t.inner.FirstOrDefault(exp)?.Department) ? t.inner.FirstOrDefault(exp)?.Department : t.outer.Department;

                    return new ExtractTransDto
                    {
                        SheetName = t.outer.Source,
                        Department = scripts.Any(w => w.TypeId == t.outer.TypeId && w.IsOrgDepartment == 1) ? t.outer.Department : dept,
                        Category = t.outer.Category,
                        DoctorName = t.outer.DoctorName,
                        PersonnelNumber = t.outer.PersonnelNumber,
                        Value = t.outer.Fee ?? 0,
                        OutDoctorAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.OutDoctorAccounting?.AccountingUnit,
                        OutNurseAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.OutNurseAccounting?.AccountingUnit,
                        OutTechnicAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.OutTechnicAccounting?.AccountingUnit,
                        InpatDoctorAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.InpatDoctorAccounting?.AccountingUnit,
                        InpatNurseAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.InpatNurseAccounting?.AccountingUnit,
                        InpatTechnicAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.InpatTechnicAccounting?.AccountingUnit,
                        SpecialAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.SpecialAccounting?.AccountingUnit ?? dept,
                        EName = types.FirstOrDefault(w => w.Id == t.outer.TypeId)?.EName,
                    };
                });

            var groupdata = data.GroupBy(t => new { t.Department, t.Category, t.SheetName }).Select(t => new ExtractTransDto
            {
                SheetName = t.Key.SheetName,
                Department = t.Key.Department,
                Category = t.Key.Category,
                Value = t.Sum(group => group.Value) == 0 ? null : t.Sum(group => group.Value),
                OutDoctorAccounting = t.FirstOrDefault()?.OutDoctorAccounting,
                OutNurseAccounting = t.FirstOrDefault()?.OutNurseAccounting,
                OutTechnicAccounting = t.FirstOrDefault()?.OutTechnicAccounting,
                InpatDoctorAccounting = t.FirstOrDefault()?.InpatDoctorAccounting,
                InpatNurseAccounting = t.FirstOrDefault()?.InpatNurseAccounting,
                InpatTechnicAccounting = t.FirstOrDefault()?.InpatTechnicAccounting,
                SpecialAccounting = t.FirstOrDefault()?.SpecialAccounting,
                EName = t.FirstOrDefault(w => !string.IsNullOrEmpty(w.EName))?.EName
            });

            return groupdata.Where(w => w.Value.HasValue && w.Value != 0).ToList();
        }

        private void UpdateAllot(IDbConnection connection, per_allot allot)
        {
            try
            {
                string sql = "update per_allot set isextracting = @isextracting, extractpath = @extractpath where id = @id";

                if (connection == null) return;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                connection.Execute(sql, allot);
            }
            catch (Exception ex)
            {
                logger.LogError(ex.Message);
            }
        }

        private readonly List<SheetType> OtherConfigSheet = new List<SheetType>
        {
            SheetType.AccountExtra,
            SheetType.PersonExtra,
            SheetType.AccountDrugAssess,
            SheetType.AccountMaterialsAssess,
            SheetType.AccountScoreAverage,
            SheetType.AccountAdjustLaterOtherFee,
            SheetType.PersonAdjustLaterOtherFee,
            SheetType.BudgetRatio,
            SheetType.AssessBeforeOtherFee,
            SheetType.PersonOtherManagePerforFee,
            SheetType.PersonAdjustLaterOtherManagePerforFee,
            SheetType.PersonPostCoefficient,
            SheetType.WorkloadMedicineProp,
            SheetType.WorkloadCMI,
            SheetType.WorkloadIncline,
        };
    }
}
