using Dapper;
using Microsoft.Extensions.Logging;
using Performance.Subsidy.Services.Repository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace Performance.Subsidy.Services
{
    public class SubsidyService
    {
        private readonly ConnectionFactory _factory;
        private readonly ConnectionStringBuilder builder;
        private readonly int _commandTimeout;
        private readonly IDbConnection _dbConnection;
        private readonly ILogger<SubsidyService> logger;

        public SubsidyService(
            ConnectionFactory factory,
            ConnectionStringBuilder builder,
            ILogger<SubsidyService> logger)
        {
            _factory = factory;
            this.builder = builder;
            _commandTimeout = 60 * 5;
            _dbConnection = _factory.CreateDefault();
            this.logger = logger;
        }

        //绩效列表
        public async Task<IEnumerable<view_allot>> GetAllot()
        {
            return await _dbConnection.QueryAsync<view_allot>("SELECT * FROM view_allot;");
        }

        //allot查询
        public view_allot GetAllot(int allotId)
        {
            return _dbConnection.QueryFirst<view_allot>($@"select * from view_allot where AllotID=@allotId;", new { allotId });
        }

        //职称查询
        public IEnumerable<sub_jobtitle> GetJobTitle(int allotId, int hospitalId)
        {
            try
            {
                var jobTitleSql = $@"select * from sub_jobtitle where AllotID=@allotId ";
                var jobTitles = _dbConnection.Query<sub_jobtitle>(jobTitleSql, new { allotId });
                if (jobTitles?.Count() > 0) return jobTitles;

                jobTitles = _dbConnection.Query<sub_jobtitle>($@"SELECT DISTINCT JobTitle FROM db_performance_subsidy.sub_subsidy where AllotID=@allotId ", new { allotId }).Select(t => new sub_jobtitle { JobTitle = t.JobTitle, BasicPerforFee = t.BasicPerforFee ?? 0 });

                var allotOder = _dbConnection.Query<view_allot>($@"SELECT * from view_allot a WHERE a.HospitalId=@HospitalId ORDER BY a.`Year`,a.`Month`;", new { hospitalId }).ToList();
                if (!allotOder.Any()) return jobTitles;

                var allot = allotOder.FirstOrDefault(t => t.AllotId == allotId);
                if (allot == null) throw new Exception("有问题");

                var index = allotOder.IndexOf(allot);
                if (index == 0) return jobTitles;

                var prevAllot = allotOder[index - 1];
                var jobTitle = _dbConnection.Query<sub_jobtitle>(jobTitleSql, new { prevAllot.AllotId });

                return jobTitle.Select(t => new sub_jobtitle { JobTitle = t.JobTitle, BasicPerforFee = t.BasicPerforFee ?? 0 }) ?? jobTitles;
            }
            catch (Exception e)
            {
                logger.LogError(e.Message);
                throw e;
            }

        }

        //重新查询职称
        public void GetHrpJobTitle(int allotId, int hospitalId, bool isRefresh)
        {
            try
            {
                var allot = GetAllot(allotId);
                if (allot == null) throw new Exception("AllotId无效");

                var subsidies = _dbConnection.Query<sub_subsidy>("select * from sub_subsidy where AllotID=@allotId ", new { allotId });
                if (subsidies.Any() && isRefresh == false) return;

                var config = _dbConnection.QueryFirst<ex_config>("select * from ex_config where hospitalId=@hospitalId", new { hospitalId });
                var connectionString = builder.GetConnectionString(config.DataBaseType, config.DbSource, config.DbName, config.DbUser, config.DbPassword);
                var connection = _factory.Create(config.DataBaseType, connectionString);

                var hrp = _dbConnection.QueryFirst<ex_script>("select * from ex_script;");
                var res = connection.Query<sub_subsidy>(hrp?.ExecScript, new { allotId }, commandTimeout: _commandTimeout);
                var subsidy = _dbConnection.Query<sub_jobtitle>("select * from sub_jobtitle where AllotID=@allotId ;", new { allotId }).Select(t => new { t.JobTitle, t.BasicPerforFee }).Distinct();

                //删除:在点击重新加载时删除记录重新插入
                _dbConnection.Execute("delete from sub_subsidy where AllotID=@allotId;delete from sub_jobtitle where AllotID=@allotId;", new { allotId });

                var jobtitle = res.Where(w=>!string.IsNullOrWhiteSpace(w.JobTitle)).Select(t => new
                {
                    allotId,
                    t.JobTitle,
                    BasicPerforFee = subsidy.Where(w => w.JobTitle == t.JobTitle)?.Select(t => t.BasicPerforFee).FirstOrDefault()
                }).Distinct();

                var sql = $@"insert into sub_jobtitle(AllotID,JobTitle,BasicPerforFee) values (@allotId,@JobTitle,@BasicPerforFee);";
                _dbConnection.Execute(sql, jobtitle);

                sql = $@"insert into sub_subsidy (AllotID,Department,PersonnelNumber,PersonnelName,JobTitle,Attendance) values (@allotId,@Department,@PersonnelNumber,@PersonnelName,@JobTitle,@Attendance);";
                var exmper = res.ToList().Select(t => new
                {
                    AllotID = allotId,
                    t.Department,
                    t.PersonnelNumber,
                    t.PersonnelName,
                    t.JobTitle,
                    t.Attendance
                });
                var i = _dbConnection.Execute(sql, exmper);
            }
            catch (Exception e)
            {
                logger.LogError(e.Message);
                throw e;
            }
        }

        //职称标准保存
        public bool SaveJobTitle(int allotId, List<sub_jobtitle> jobtitle)
        {
            try
            {
                var allot = GetAllot(allotId);
                if (allot == null) throw new Exception("AllotId无效");

                var result = jobtitle.Select(t => new { AllotID = allotId, t.BasicPerforFee, t.JobTitle });
                var modify = _dbConnection.Execute($" update `sub_jobtitle` set BasicPerforFee =@BasicPerforFee WHERE AllotID=@allotId and  JobTitle=@JobTitle; ", result);
                _dbConnection.Execute("call proc_performance_subsidy(@allotId) ;", new { allotId });
                return modify > 0;
            }
            catch (Exception e)
            {
                logger.LogError(e.Message);
                throw e;
            }
        }

        //个人职称补贴结果查询
        public List<sub_subsidy> GetJobTitleSubsidy(int allotId)
        {
            try
            {
                var allot = GetAllot(allotId);
                if (allot == null) throw new Exception("AllotId无效");

                IEnumerable<sub_subsidy> _Subsidies = _dbConnection.Query<sub_subsidy>(@"select * from sub_subsidy where AllotID=@allotId ;", new { allotId });
                return _Subsidies?.ToList();
            }
            catch (Exception e)
            {
                logger.LogError(e.Message);
                throw e;
            }
        }

        //个人职称补贴结果保存
        public bool SaveJobTitleSubsidy(int allotId, List<sub_subsidy> subsidys)
        {
            try
            {
                var allot = GetAllot(allotId);
                if (allot == null) throw new Exception("AllotId无效");

                var result = subsidys.Select(t => new { t.RealAmount, AllotID = allotId, t.PersonnelNumber });
                _dbConnection.Execute(@$"update sub_subsidy set GiveAmount = Attendance * BasicPerforFee, RealAmount = @RealAmount where AllotID=@allotId and PersonnelNumber=@PersonnelNumber;", result);

                _dbConnection.Execute($@"call proc_performance_sync(@allotId);", new { allotId });
                return true;
            }
            catch (Exception e)
            {
                logger.LogError(e.Message);
                throw e;
            }
        }

        public bool SaveSubsidy(int allotId, List<sub_subsidy> subsidys)
        {
            try
            {
                var allot = GetAllot(allotId);
                if (allot == null) throw new Exception("AllotId无效");

                var result = subsidys.Select(t => new { t.RealAmount, AllotID = allotId, t.PersonnelNumber });
                _dbConnection.Execute(@$"update sub_subsidy set GiveAmount = Attendance * BasicPerforFee, RealAmount = @RealAmount where AllotID=@allotId and PersonnelNumber=@PersonnelNumber;", result);
                return true;
            }
            catch (Exception e)
            {
                logger.LogError(e.Message);
                throw e;
            }
        }
    }
}
