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

namespace Performance.Services
{
    public class ExtractIncomeService : IAutoInjection
    {
        private readonly PerforPerallotRepository perallotRepository;
        private readonly PerforHospitalconfigRepository hospitalconfigRepository;
        private readonly PerforExtypeRepository extypeRepository;
        private readonly PerforExscriptRepository exscriptRepository;
        private readonly ILogger logger;

        public ExtractIncomeService(
            PerforPerallotRepository perallotRepository,
            PerforHospitalconfigRepository hospitalconfigRepository,
            PerforExtypeRepository extypeRepository,
            PerforExscriptRepository exscriptRepository,
            ILogger<ExtractIncomeService> logger
            )
        {
            this.perallotRepository = perallotRepository;
            this.hospitalconfigRepository = hospitalconfigRepository;
            this.extypeRepository = extypeRepository;
            this.exscriptRepository = exscriptRepository;
            this.logger = logger;
        }

        public string Execture(int allotId)
        {
            try
            {
                var allot = perallotRepository.GetEntity(t => t.ID == allotId);
                if (allot == null) throw new PerformanceException("选取的绩效无效!");

                var pairs = GetIncomeData(allot);

                string filepath = CreateExcel(allot.HospitalId, pairs);
                if (string.IsNullOrEmpty(filepath) || !FileHelper.IsExistFile(filepath))
                    throw new PerformanceException("抽取文件错误");

                logger.LogInformation("医生收入文件: " + filepath);
                return filepath;
            }
            catch (PerformanceException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                logger.LogError(ex.ToString());
                throw new PerformanceException("抽取数据过程中发生异常!");
            }
        }

        private Dictionary<string, List<IncomeDataDto>> GetIncomeData(per_allot allot)
        {
            var configs = hospitalconfigRepository.GetEntities(t => t.HospitalId == allot.HospitalId);
            if (configs == null || !configs.Any()) throw new PerformanceException("未添加医院提取配置");

            var types = extypeRepository.GetEntities(t => t.Source == 100);
            if (types == null || !types.Any()) throw new PerformanceException("未配置数据提取内容");

            Dictionary<string, List<IncomeDataDto>> pairs = new Dictionary<string, List<IncomeDataDto>>();
            foreach (var item in types)
            {
                pairs.Add(item.EName, new List<IncomeDataDto>());

                var scripts = exscriptRepository.GetEntities(t => t.TypeId == item.Id);
                if (scripts == null || !scripts.Any()) continue;

                foreach (var script in scripts)
                {
                    var config = configs.FirstOrDefault(t => t.Id == script.ConfigId);
                    if (config == null) continue;

                    var data = QueryData(config, allot, script.ExecScript);
                    if (data != null && data.Any())
                        pairs[item.EName].AddRange(data);
                }
            }
            return pairs;
        }

        private IEnumerable<IncomeDataDto> QueryData(sys_hospitalconfig config, per_allot allot, string execsql)
        {
            try
            {
                var parameters = GetParameters(allot);
                using (var connection = ConnectionBuilder.Create((DatabaseType)config.DataBaseType, config.DbSource, config.DbName, config.DbUser, config.DbPassword))
                {
                    foreach (var item in parameters)
                    {
                        execsql = Regex.Replace(execsql, item.Key, item.Value, RegexOptions.IgnoreCase);
                    }
                    logger.LogInformation("获取医生收入sql: " + execsql + "\n配置:" + JsonHelper.Serialize(config));
                    var result = connection.Query<IncomeDataDto>(execsql, commandTimeout: 20000);
                    return result;
                }
            }
            catch (Exception ex)
            {
                logger.LogError("获取医生收入时发生异常: " + ex.ToString());
            }
            return new List<IncomeDataDto>();
        }

        private string CreateExcel(int hospitalId, Dictionary<string, List<IncomeDataDto>> dataDict)
        {
            if (dataDict == null || !dataDict.Any(t => t.Value != null && t.Value.Any())) throw new PerformanceException("未查询到数据!");

            IWorkbook workbook = new XSSFWorkbook();
            ICellStyle cellStyle = getCellStyle.Invoke(workbook);

            foreach (var dict in dataDict.Where(t => t.Value != null && t.Value.Any()))
            {
                ISheet sheet = workbook.CreateSheet(dict.Key);

                var columns = memberDict.Keys.ToList();
                var categories = dict.Value.Select(s => s.Category).Distinct().OrderBy(t => t);
                columns.AddRange(categories);

                CreateHeader(sheet, cellStyle, columns);

                int rowIndex = sheet.LastRowNum + 1;
                var groupData = dict.Value.GroupBy(t => new { t.EmpCode, t.EmpName, t.DeptName, t.ExecDeptName });
                foreach (var data in groupData)
                {
                    IRow row = sheet.CreateRow(rowIndex);
                    int index = 0;
                    foreach (var field in columns)
                    {
                        ICell cell = row.CreateCell(index);
                        if (memberDict.ContainsKey(field))
                            OutToExcelCell(cell, memberDict[field]?.Invoke(data.Key));
                        else
                        {
                            var value = data.FirstOrDefault(t => t.Category == field)?.Fee;
                            if (value.HasValue)
                                OutToExcelCell(cell, value.Value);
                        }
                        cell.CellStyle = cellStyle;
                        index++;
                    }
                    rowIndex++;
                }
            }

            var path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Files", $"{hospitalId}", "income");
            if (!FileHelper.IsExistDirectory(path))
                FileHelper.CreateDirectory(path);

            string filepath = Path.Combine(path, $"医生收入{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx");
            using (FileStream fs = new FileStream(filepath, FileMode.Create))
            {
                workbook.Write(fs);
            }

            if (workbook != null) workbook.Close();

            return filepath;
        }

        private void CreateHeader(ISheet sheet, ICellStyle cellStyle, List<string> columns)
        {
            IRow row = sheet.CreateRow(0);
            if (row == null) return;

            int index = 0;
            foreach (var col in columns)
            {
                ICell cell = row.CreateCell(index);
                cell.SetCellValue(col);
                cell.CellStyle = cellStyle;
                index++;
            }

            sheet.CreateFreezePane(memberDict.Count, 1); //首行冻结
        }

        private Dictionary<string, string> GetParameters(per_allot allot)
        {
            DateTime beginTime = new DateTime(allot.Year, allot.Month, 1);

            Dictionary<string, string> pairs = new Dictionary<string, string>
            {
                { "@beginTime", $"'{beginTime.ToString("yyyy-MM-dd")}'" },
                { "@endTime", $"'{beginTime.AddMonths(1).ToString("yyyy-MM-dd")}'"},
            };
            return pairs;
        }

        public void OutToExcelCell(ICell cell, object obj)
        {
            if (obj == null)
            {
                cell.SetCellValue("");
                return;
            }

            string value = obj.ToString();
            try
            {
                var type = obj.GetType();
                switch (type.ToString())
                {
                    case "System.String"://字符串类型   
                        cell.SetCellValue(value);
                        break;
                    case "System.DateTime"://日期类型   
                        DateTime dateV;
                        DateTime.TryParse(value, out dateV);
                        cell.SetCellValue(dateV.ToString("yyyy/M/d"));

                        break;
                    case "System.Boolean"://布尔型   
                        bool boolV = false;
                        bool.TryParse(value, out boolV);
                        cell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型   
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(value, out intV);
                        cell.SetCellValue(intV);
                        break;
                    case "System.Decimal"://浮点型   
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(value, out doubV);
                        cell.SetCellValue(doubV);
                        break;
                    case "System.DBNull"://空值处理   
                        cell.SetCellValue("");
                        break;
                    default:
                        cell.SetCellValue("");
                        break;
                }
            }
            catch
            {
                cell.SetCellValue(value);
            }
        }

        private readonly Dictionary<string, Func<dynamic, object>> memberDict = new Dictionary<string, Func<dynamic, object>>
        {
            { "医生工号", (t) => t.EmpCode },
            { "医生姓名", (t) => t.EmpName },
            { "开单科室", (t) => t.DeptName },
            { "执行科室", (t) => t.ExecDeptName },
            //{ "费用类型", (t) => t.Category },
            //{ "费用", (t) => t.Fee }
        };

        private readonly Func<IWorkbook, ICellStyle> getCellStyle = (workbook) =>
         {
             IFont font = workbook.CreateFont();
             font.FontHeightInPoints = 12;
             font.FontName = "微软雅黑";

             ICellStyle cellStyle = workbook.CreateCellStyle();
             cellStyle.Alignment = HorizontalAlignment.Center;//设置水平居中
             cellStyle.VerticalAlignment = VerticalAlignment.Center;//设置垂直居中
             cellStyle.SetFont(font);

             return cellStyle;
         };
    }

    public class IncomeDataDto
    {
        /// <summary>
        /// 医生工号
        /// </summary>
        public string EmpCode { get; set; }

        /// <summary>
        /// 医生姓名
        /// </summary>
        public string EmpName { get; set; }

        /// <summary>
        /// 开单科室
        /// </summary>
        public string DeptName { get; set; }

        /// <summary>
        /// 执行科室
        /// </summary>
        public string ExecDeptName { get; set; }

        /// <summary>
        /// 费用类型
        /// </summary>
        public string Category { get; set; }

        /// <summary>
        /// 费用
        /// </summary>
        public decimal Fee { get; set; }
    }
}
