using AutoMapper;
using Dapper;
using Dapper.Contrib.Extensions;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using MySql.Data.MySqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
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.Threading.Tasks;

namespace Performance.Services.AllotCompute
{
    /// <summary>
    /// 导入excel数据并保持
    /// </summary>
    public class ImportDataService : IAutoInjection
    {
        private readonly IMapper _mapper;
        private readonly IOptions<AppConnection> _options;
        private readonly PerSheetService _perSheetService;
        private readonly LogManageService _logManageService;

        public ImportDataService(
            IMapper mapper,
            IOptions<AppConnection> options,
            PerSheetService perSheetService,
            LogManageService logManageService)
        {
            _mapper = mapper;
            _options = options;
            _perSheetService = perSheetService;
            _logManageService = logManageService;
        }

        /// <summary>
        /// 读取excel并保存
        /// </summary>
        /// <param name="allot"></param>
        /// <returns></returns>
        public PerExcel ReadDataAndSave(per_allot allot)
        {
            var excel = Import(allot);
            Save(excel, allot.ID);
            var item = excel.PerSheet.FirstOrDefault(w => w.SheetType == SheetType.AccountBasicSpecial);
            if (item != null)
                excel.PerSheet.Remove(item);
            return excel;
        }

        /// <summary>
        /// 导入excel数据
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private PerExcel Import(per_allot allot)
        {
            var path = allot.Path;
            PerExcel excel = new PerExcel
            {
                Path = path,
                FileName = FileHelper.GetFileNameNoExtension(path),
                Version = FileHelper.GetExtension(path) == ".xlsx" ? ExcelVersion.xlsx : ExcelVersion.xls,
                PerSheet = new List<PerSheet>()
            };

            _logManageService.WriteMsg("读取文件", $"文件读取中...当前操作需要一点时间，请耐心等待", 1, allot.ID, "ReceiveMessage", true);

            using (FileStream fs = new FileStream(path, FileMode.Open))
            {
                IWorkbook workbook = (excel.Version == ExcelVersion.xlsx) ? new XSSFWorkbook(fs) : new HSSFWorkbook(fs);
                bool isnew = false;
                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    var sheet = workbook.GetSheetAt(i);
                    var sheetType = _perSheetService.GetSheetType(sheet.SheetName);
                    if (SheetType.Unidentifiable != sheetType)
                        _logManageService.WriteMsg("准备读取文件", $"正在准备读取“{sheet.SheetName}”", 1, allot.ID, "ReceiveMessage", true);
                    //else
                    //_logManageService.WriteMsg("准备读取文件", $"忽略文件“{sheet.SheetName}”", 1, allot.ID, "ReceiveMessage", true);

                    try
                    {
                        // 保留临床科室测算表全部结构，用作展示，不做其他用途
                        if (sheetType == SheetType.AccountBasic)
                        {
                            var st = _perSheetService.SheetAccountBasicSpecial(sheet, isnew);
                            excel.PerSheet.Add(st);
                        }
                        if (SheetType.Unidentifiable != sheetType)
                        {
                            var st = _perSheetService.Sheet(sheet, isnew);
                            excel.PerSheet.Add(st);

                        }
                        else
                        {
                            if (sheet.SheetName.Contains("开单医院"))
                                isnew = true;
                        }

                    }
                    catch (Exception ex)
                    {
                        _logManageService.WriteMsg("读取文件", ex.Message.ToString(), 4, allot.ID, "ReceiveMessage", true);
                        throw;
                    }
                }


                _logManageService.WriteMsg("读取文件", $"EXCEL文件基础数据读取完成!", 1, allot.ID, "ReceiveMessage", true);
                return excel;
            }
        }

        private bool Save(PerExcel excel, int allotId)
        {
            var tasks = new List<Task>();
            using (IDbConnection connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
            {
                foreach (var sheet in excel.PerSheet)
                {
                    _logManageService.WriteMsg("准备保存数据", $"正在准备保存数据“{sheet.SheetName}”", 1, allotId, "ReceiveMessage", true);

                    var allot = connection.Get<per_allot>(allotId);
                    var allotList = connection.Query<per_allot>("select * from per_allot where HospitalId = @HospitalId", new { allot.HospitalId }).ToList();
                    var sheets = connection.Query<per_sheet>("select * from per_sheet where AllotID = @allotId", new { allotId }).ToList();

                    var task = Task.Factory.StartNew(() =>
                    {
                        if (sheet.SheetType == SheetType.Employee)
                        {
                            var imsheetid = SaveEmployee(sheet, allot, allotList);
                            SaveHeader(sheet, allot, imsheetid);
                        }
                        else if (sheet.SheetType == SheetType.LogisticsEmployee)
                        {
                            var imsheetid = SaveLogisticsEmployee(sheet, allot, allotList);
                            SaveHeader(sheet, allot, imsheetid);
                        }
                        else if (sheet.SheetType == SheetType.ClinicEmployee)
                        {
                            var imsheetid = SaveClinicEmployee(sheet, allot, allotList);
                            SaveHeader(sheet, allot, imsheetid);
                        }
                        else if (sheet.SheetType == SheetType.AccountBasic)
                        {
                            var imsheetid = SaveAccountBasic(sheet, allot, sheets);
                            SaveHeader(sheet, allot, imsheetid);
                        }
                        else if (sheet.SheetType == SheetType.SpecialUnit)
                        {
                            var imsheetid = SaveSpecialUnit(sheet, allot);
                            SaveHeader(sheet, allot, imsheetid);
                        }
                        else
                        {
                            var imsheetid = SaveCommon(sheet, allot, sheets);
                            SaveHeader(sheet, allot, imsheetid);
                        }
                    });
                    tasks.Add(task);
                }
            }
            _logManageService.WriteMsg("保存基础数据", $"基础数据保存中...当前操作需要一点时间，请耐心等待", 1, allotId, "ReceiveMessage", true);

            Task.WaitAll(tasks.ToArray());

            _logManageService.WriteMsg("保存基础数据", $"基础数据保存完成!", 1, allotId, "ReceiveMessage", true);
            return true;
        }

        /// <summary>
        /// 保存医院人员
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public int SaveEmployee(PerSheet sheet, per_allot allot, List<per_allot> allotList)
        {
            using (IDbConnection connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
            {
                var imsheet = new per_sheet { AllotID = allot.ID, SheetName = sheet.SheetName, Source = 1, SheetType = (int)sheet.SheetType };
                imsheet.ID = (int)connection.Insert(imsheet);

                var dataList = sheet.PerData.Select(t => (PerDataEmployee)t);
                //新上传名单中无人员名单，取到最后日期的数据
                if (dataList == null || dataList.Count() <= 0)
                {
                    foreach (var item in allotList)
                    {
                        var employeeList = connection.Query<im_employee>("select * from im_employee where AllotID = @allotid", new { AllotID = allot.ID });
                        if (employeeList != null && employeeList.Count() > 0)
                        {
                            dataList = _mapper.Map<List<PerDataEmployee>>(employeeList);
                            break;
                        }
                    }
                }
                List<im_employee> addList = new List<im_employee>();
                foreach (var data in dataList)
                {
                    var imdata = _mapper.Map<im_employee>(data);
                    imdata.SheetID = imsheet.ID;
                    imdata.AllotID = allot.ID;
                    //imdata.OtherPerfor = data.OthePerfor;
                    addList.Add(imdata);
                }
                connection.Insert(addList.ToArray());
                return imsheet.ID;
            }

            //var imsheet = new per_sheet {  AllotID =allot.ID, SheetName = sheet.SheetName, Source = 1, SheetType = (int)sheet.SheetType };
            //perforPerSheetRepository.Add(imsheet);
            //var dataList = sheet.PerData.Select(t => (PerDataEmployee)t);
            ////新上传名单中无人员名单，取到最后日期的数据
            //if (dataList == null || dataList.Count() <= 0)
            //{
            //    var allot = perforPerallotRepository.GetEntity(t => t.ID == allotId);
            //    var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == allot.HospitalId)
            //                    .OrderByDescending(t => t.Year).ThenByDescending(t => t.Month);
            //    foreach (var item in allotList)
            //    {
            //        var employeeList = perforImEmployeeRepository.GetEntities(t => t.AllotID == item.ID);
            //        if (employeeList != null && employeeList.Count > 0)
            //        {
            //            dataList = _mapper.Map<List<PerDataEmployee>>(employeeList);
            //            break;
            //        }
            //    }
            //}
            //List<im_employee> addList = new List<im_employee>();
            //foreach (var data in dataList)
            //{
            //    var imdata = _mapper.Map<im_employee>(data);
            //    imdata.SheetID = imsheet.ID;
            //    imdata. AllotID =allot.ID;
            //    //imdata.OtherPerfor = data.OthePerfor;
            //    addList.Add(imdata);
            //}
            //perforImEmployeeRepository.BulkInsert(addList.ToArray());
            //return imsheet.ID;
        }

        /// <summary>
        /// 保存临床人员
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public int SaveClinicEmployee(PerSheet sheet, per_allot allot, List<per_allot> allotList)
        {
            using (IDbConnection connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
            {
                var imsheet = new per_sheet { AllotID = allot.ID, SheetName = sheet.SheetName, Source = 1, SheetType = (int)sheet.SheetType };
                imsheet.ID = (int)connection.Insert(imsheet);

                var dataList = sheet.PerData.Select(t => (PerDataClinicEmployee)t);
                //新上传名单中无人员名单，取到最后日期的数据
                if (dataList == null || dataList.Count() <= 0)
                {
                    foreach (var item in allotList)
                    {
                        var employeeList = connection.Query<im_employee_clinic>("select * from im_employee_clinic where AllotID = @allotid", new { AllotID = allot.ID });
                        if (employeeList != null && employeeList.Count() > 0)
                        {
                            dataList = _mapper.Map<List<PerDataClinicEmployee>>(employeeList);
                            break;
                        }
                    }
                }
                List<im_employee_clinic> addList = new List<im_employee_clinic>();
                foreach (var data in dataList)
                {
                    var imdata = _mapper.Map<im_employee_clinic>(data);
                    imdata.SheetID = imsheet.ID;
                    imdata.AllotID = allot.ID;
                    imdata.OtherPerfor = data.OthePerfor;
                    addList.Add(imdata);
                }
                connection.Insert(addList);
                return imsheet.ID;
            }
        }

        /// <summary>
        /// 保存临床人员
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public int SaveLogisticsEmployee(PerSheet sheet, per_allot allot, List<per_allot> allotList)
        {
            using (IDbConnection connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
            {
                var imsheet = new per_sheet { AllotID = allot.ID, SheetName = sheet.SheetName, Source = 1, SheetType = (int)sheet.SheetType };
                imsheet.ID = (int)connection.Insert(imsheet);

                var dataList = sheet.PerData.Select(t => (PerDataLogisticsEmployee)t);
                //新上传名单中无人员名单，取到最后日期的数据
                if (dataList == null || dataList.Count() <= 0)
                {
                    foreach (var item in allotList?.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month))
                    {
                        var employeeList = connection.Query<im_employee_logistics>("select * from im_employee_logistics where AllotID = @allotid", new { AllotID = allot.ID });
                        if (employeeList != null && employeeList.Count() > 0)
                        {
                            dataList = _mapper.Map<List<PerDataLogisticsEmployee>>(employeeList);
                            break;
                        }
                    }
                }
                List<im_employee_logistics> addList = new List<im_employee_logistics>();
                foreach (var data in dataList)
                {
                    var imdata = _mapper.Map<im_employee_logistics>(data);
                    imdata.SheetID = imsheet.ID;
                    imdata.AllotID = allot.ID;
                    imdata.OtherPerfor = data.OthePerfor;
                    addList.Add(imdata);
                }
                connection.Insert(addList);
                return imsheet.ID;
            }
        }

        /// <summary>
        /// 保存科室绩效基础表
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public int SaveAccountBasic(PerSheet sheet, per_allot allot, List<per_sheet> sheets)
        {
            using (IDbConnection connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
            {
                //var imsheet = new per_sheet {  AllotID =allot.ID, SheetName = sheet.SheetName, Source = 1, SheetType = (int)sheet.SheetType };
                //perforPerSheetRepository.Add(imsheet);
                List<int> types = new List<int> { (int)SheetType.AccountBasicSpecial, (int)SheetType.AccountBasic };
                var imsheet = sheets?.FirstOrDefault(w => w.AllotID == allot.ID && w.SheetType.HasValue && types.Contains(w.SheetType.Value));
                if (imsheet != null)
                {
                    imsheet.SheetType = (int)SheetType.AccountBasic;
                    connection.Update(imsheet);
                }
                else
                {
                    imsheet = new per_sheet { AllotID = allot.ID, SheetName = sheet.SheetName, Source = 1, SheetType = (int)sheet.SheetType };
                    imsheet.ID = (int)connection.Insert(imsheet);
                }

                var dataList = sheet.PerData.Select(t => (PerDataAccountBaisc)t);
                List<im_accountbasic> addList = new List<im_accountbasic>();
                foreach (var data in dataList)
                {
                    var imdata = _mapper.Map<im_accountbasic>(data);
                    imdata.SheetID = imsheet.ID;
                    imdata.AllotID = allot.ID;
                    addList.Add(imdata);
                }
                connection.Insert(addList.ToArray());
                return imsheet.ID;
            }
        }

        /// <summary>
        /// 保存特殊科室
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public int SaveSpecialUnit(PerSheet sheet, per_allot allot)
        {
            using (IDbConnection connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
            {
                var imsheet = new per_sheet { AllotID = allot.ID, SheetName = sheet.SheetName, Source = 1, SheetType = (int)sheet.SheetType };
                imsheet.ID = (int)connection.Insert(imsheet);

                var dataList = sheet.PerData.Select(t => (PerDataSpecialUnit)t);
                List<im_specialunit> addList = new List<im_specialunit>();
                foreach (var data in dataList)
                {
                    var imdata = _mapper.Map<im_specialunit>(data);
                    imdata.SheetID = imsheet.ID;
                    imdata.AllotID = allot.ID;
                    addList.Add(imdata);
                }
                connection.Insert(addList.ToArray());
                return imsheet.ID;
            }
        }

        /// <summary>
        /// 保存通用格式
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public int SaveCommon(PerSheet sheet, per_allot allot, List<per_sheet> sheets)
        {
            using (IDbConnection connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
            {
                per_sheet imsheet = null;
                List<int> types = new List<int> { (int)SheetType.AccountBasicSpecial, (int)SheetType.AccountBasic };
                if (types.Any(type => type == (int)sheet.SheetType))
                {
                    imsheet = sheets?.FirstOrDefault(w => w.AllotID == allot.ID && w.SheetType.HasValue && types.Contains(w.SheetType.Value));
                    if (imsheet != null)
                    {
                        imsheet.SheetType = (int)SheetType.AccountBasic;
                        connection.Update(imsheet);
                    }
                }
                if (imsheet == null)
                {
                    imsheet = new per_sheet { AllotID = allot.ID, SheetName = sheet.SheetName, Source = 1, SheetType = (int)sheet.SheetType };
                    imsheet.ID = (int)connection.Insert(imsheet);
                }


                List<im_data> addDataList = new List<im_data>();
                var dataList = sheet.PerData.Select(t => (PerData)t);
                foreach (var data in dataList)
                {
                    var imdata = _mapper.Map<im_data>(data);
                    imdata.SheetID = imsheet.ID;
                    imdata.AllotID = allot.ID;
                    addDataList.Add(imdata);
                }
                if (addDataList != null && addDataList.Any())
                {
                    int rows = 500;
                    for (int i = 0; i < Math.Ceiling((double)addDataList.Count / rows); i++)
                    {
                        connection.Insert(addDataList.Skip(rows * i).Take(rows).ToArray());
                    }
                }
                return imsheet.ID;
            }
        }

        public void SaveHeader(PerSheet sheet, per_allot allot, int imsheetid)
        {
            using (IDbConnection connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
            {
                List<im_header> addHeadList = new List<im_header>();
                foreach (var header in sheet.PerHeader)
                {
                    var imheader = _mapper.Map<im_header>(header);
                    imheader.SheetID = imsheetid;
                    imheader.AllotID = allot.ID;
                    imheader.ID = (int)connection.Insert(imheader);

                    if (header.IsHasChildren)
                    {
                        foreach (var child in header.Children)
                        {
                            var imheaderChild = _mapper.Map<im_header>(child);
                            imheaderChild.SheetID = imsheetid;
                            imheaderChild.ParentID = imheader.ID;
                            imheaderChild.AllotID = allot.ID;
                            addHeadList.Add(imheaderChild);
                        }
                    }
                }
                if (addHeadList != null && addHeadList.Any())
                    connection.Insert(addHeadList);
            }
        }

    }
}
