//-----------------------------------------------------------------------
// <copyright file=" sys_allot.cs">
// * FileName: sys_allot.cs
// * history : Created by T4 2019-03-06 16:43:31
// </copyright>
//-----------------------------------------------------------------------
using Dapper;
using Microsoft.EntityFrameworkCore;
using Performance.EntityModels;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Performance.Repository
{
    /// <summary>
    /// sys_allot Repository
    /// </summary>
    public partial class PerforPerallotRepository : PerforRepository<per_allot>
    {
        public bool UpdateAllotStates(int allotId, int states, string remark, int generate)
        {
            var allot = GetEntity(t => t.ID == allotId);
            allot.States = states;
            allot.Remark = remark;
            allot.Generate = generate;
            return Update(allot);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="execsql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public bool ExecProc(string execsql, object param)
        {
            using (var connection = context.Database.GetDbConnection())
            {
                Execute(execsql, param);
            }
            return true;
        }

        /// <summary>
        /// 只支持EXCEL抽取报表数据
        /// </summary>
        /// <param name="import"></param>
        /// <param name="pairs"></param>
        /// <returns></returns>
        public bool ImportData(rep_importconfig import, Dictionary<string, object> pairs)
        {
            using (var connection = context.Database.GetDbConnection())
            {
                var data = connection.Query(import.ImportScript, new DynamicParameters(pairs), commandTimeout: 60 * 60);
                ImportData(import, pairs, data);
            }
            return true;
        }

        /// <summary>
        /// 指定数据源插入MYSQL
        /// </summary>
        /// <param name="import"></param>
        /// <param name="pairs"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public bool ImportData(rep_importconfig import, Dictionary<string, object> pairs, IEnumerable<dynamic> data)
        {
            if (data != null && data.Any())
            {
                using (var connection = context.Database.GetDbConnection())
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    using (var transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            connection.Execute(import.DeleteScript, new DynamicParameters(pairs), commandTimeout: 60 * 60, transaction: transaction);

                            List<string> columns = new List<string>();
                            foreach (KeyValuePair<string, object> col in data.First())
                            {
                                columns.Add(col.Key);
                            }
                            var c1 = string.Join(",", columns.Select(w => $"`{w}`"));
                            var c2 = string.Join(",", columns.Select(w => $"@{w}"));
                            string insert = $"insert into {import.TableName}({c1}) values({c2})";
                            connection.Execute(insert, data, commandTimeout: 60 * 60, transaction: transaction);

                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw ex;
                        }
                    }
                }
            }
            return true;
        }

        public bool UpdateData(rep_importconfig import, Dictionary<string, object> pairs)
        {
            using (var connection = context.Database.GetDbConnection())
            {
                var flag = connection.Execute(import.ImportScript, new DynamicParameters(pairs), commandTimeout: 60 * 60);
                return flag > 0;
            }
        }

        /// <summary>
        /// 清除历史抽取数据
        /// </summary>
        /// <param name="allotid"></param>
        public void ClearResultData(int allotid)
        {
            using (var connection = context.Database.GetDbConnection())
            {
                if (connection.State != ConnectionState.Open) connection.Open();
                try
                {
                    string update = "update ex_result set isdelete = 1 where allotid =  @allotid ";
                    connection.Execute(update, new { allotid }, commandTimeout: 60 * 60);

                    string clear = "delete from ex_result where allotid = @allotid and isdelete = 1 and createtime < (select min(createtime) from (select distinct createtime from ex_result where allotid = @allotid and isdelete = 1 order by createtime desc limit 4) t);";
                    connection.Execute(clear, new { allotid }, commandTimeout: 60 * 60);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 保存工作量数据
        /// </summary>
        /// <param name="allot"></param>
        /// <param name="parameters"></param>
        public void ImportWorkloadData(per_allot allot, object parameters)
        {
            using (var connection = context.Database.GetDbConnection())
            {
                if (connection.State != ConnectionState.Open) connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        string clear = "delete from report_original_workload where allotid = @allotid and hospitalid = @hospitalid;";
                        connection.Execute(clear, new { allotid = allot.ID, hospitalid = allot.HospitalId }, commandTimeout: 60 * 60, transaction: transaction);

                        string insert = "insert into report_original_workload(allotid,year,month,hospitalid,sourcetype,accountingunit,department,doctorname,personnelnumber,category,fee) values(@allotid,@year,@month,@hospitalid,@sourcetype,@accountingunit,@department,@doctorname,@personnelnumber,@category,@fee);";
                        connection.Execute(insert, parameters, commandTimeout: 60 * 60, transaction: transaction);

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }
                }
            }
        }

        /// <summary>
        /// 查询工作量数据
        /// </summary>
        /// <param name="allotid"></param>
        public IEnumerable<report_original_workload> QueryWorkloadData(int allotid, string accountingunit, string[] unittypes, int hospitalid)
        {
            using (var connection = context.Database.GetDbConnection())
            {
                if (connection.State != ConnectionState.Open) connection.Open();
                try
                {
                    string clear = @"
SELECT DISTINCT AccountingUnit as Department,if(ifnull(DoctorName,'')='', '未知',DoctorName) DoctorName,PersonnelNumber,Category,ROUND(SUM(Fee),2) Fee 
FROM view_second_report_workload
WHERE AllotId = @allotid AND UnitType in @unittypes AND AccountingUnit = @accountingunit
GROUP BY AccountingUnit,DoctorName,PersonnelNumber,Category
ORDER BY doctorname,Category;";
                    return connection.Query<report_original_workload>(clear, new { allotid, accountingunit, unittypes = unittypes.Union(new string[] { "通用工作量" }), hospitalid }, commandTimeout: 60 * 60);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 查询门诊收入数据
        /// </summary>
        /// <param name="allotid"></param>
        public IEnumerable<ex_result> QueryIncomeData(int allotid, string source, string accountingunit, string[] unittypes, int hospitalid)
        {
            using (var connection = context.Database.GetDbConnection())
            {
                if (connection.State != ConnectionState.Open) connection.Open();
                try
                {
                    var tableName = (source == "门诊") ? "view_second_report_income_out" : "view_second_report_income_inpat";


                    string clear = $@"
SELECT DISTINCT AccountingUnit as Department,if(ifnull(DoctorName,'')='', '未知',DoctorName) DoctorName,PersonnelNumber,Category,ROUND(SUM(Fee),2) Fee 
FROM {tableName}
WHERE AllotId = @allotid AND UnitType in @unittypes AND AccountingUnit = @accountingunit
GROUP BY AccountingUnit,DoctorName,PersonnelNumber,Category
ORDER BY doctorname,Category;";

                    return connection.Query<ex_result>(clear, new { allotid, accountingunit, unittypes, hospitalid }, commandTimeout: 60 * 60);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 二次分配按工号带出带出工作量
        /// </summary>
        /// <param name="allotid"></param>
        /// <param name="unittype"></param>
        /// <param name="accountingunit"></param>
        /// <returns></returns>
        public IEnumerable<view_second_workload_result> GetSecondWorkload(int allotid, string unittype, string accountingunit)
        {
            using (var connection = context.Database.GetDbConnection())
            {
                if (connection.State != ConnectionState.Open) connection.Open();
                try
                {
                    string query = $@"
SELECT TAB1.HospitalId,AllotId,TAB1.UnitType,AccountingUnit,HISDeptName,ItemId,ItemName,FactorValue,DoctorName,PersonnelNumber,Category,SUM(Fee) Value 
FROM view_second_workload TAB1
 JOIN view_second_workload_his TAB2 ON TAB1.HospitalId = TAB2.HospitalId AND (TAB1.UnitType = TAB2.UnitType ) AND TAB1.HISDeptName = TAB2.Department AND TAB1.SourceCategory = TAB2.Category
WHERE AllotId = @allotid AND TAB1.UnitType = @unittype AND AccountingUnit = @accountingunit
GROUP BY TAB1.HospitalId,AllotId,TAB1.UnitType,AccountingUnit,HISDeptName,ItemId,ItemName,FactorValue,DoctorName,PersonnelNumber,Category";
                    return connection.Query<view_second_workload_result>(query, new { allotid, unittype, accountingunit }, commandTimeout: 60 * 60);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 查询HIS提取数据，工作量字典
        /// </summary>
        /// <param name="allotid"></param>
        /// <param name="unittype"></param>
        /// <param name="accountingunit"></param>
        /// <returns></returns>
        public IEnumerable<string> GetSecondWorkloadMaps(int hospitalId)
        {
            using (var connection = context.Database.GetDbConnection())
            {
                if (connection.State != ConnectionState.Open) connection.Open();
                try
                {
                    string query = $@"SELECT DISTINCT Category FROM (
	SELECT IF(Source = 102,EName,ItemName) Category FROM ex_type t1
	LEFT JOIN ex_item t2 ON t1.Id = t2.TypeId 
	WHERE Source IN (102,7) AND HospitalId = @hospitalId
)TAB
WHERE IFNULL(Category,'')<>''";
                    return connection.Query<string>(query, new { hospitalId }, commandTimeout: 60 * 60);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
    }
}