//-----------------------------------------------------------------------
// <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.Data.SqlClient;
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 unittype = unittypes.Any(t => !string.IsNullOrEmpty(t) && t.Contains("医生"))
                        ? "医生组"
                        : unittypes.Any(t => !string.IsNullOrEmpty(t) && t.Contains("护理")) ? "护理组" : "其他组";
                    string clear = @"SELECT DISTINCT t3.AccountingUnit as Department,ifnull(t1.DoctorName, '未知') DoctorName,t1.PersonnelNumber,t1.Category,SUM(t1.Fee) Fee FROM ex_result t1
	JOIN (select distinct AccountingUnit,HISDeptName,unittype from per_dept_dic where HospitalId = @hospitalid) t3 ON t1.Department = t3.HISDeptName
WHERE t1.allotid = @allotid
	AND t3.unittype in @unittypes
	AND t3.accountingunit = @accountingunit
	AND t1.Source LIKE CONCAT('%',@unittype,'工作量%')
	AND T1.IsDelete = 0
GROUP BY ifnull(t1.DoctorName, '未知'),t1.PersonnelNumber,t1.Category
ORDER BY t1.doctorname,t1.Category;";
                    return connection.Query<report_original_workload>(clear, new { allotid, accountingunit, unittypes, unittype, 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[] unittype, int hospitalid)
        {
            using (var connection = context.Database.GetDbConnection())
            {
                if (connection.State != ConnectionState.Open) connection.Open();
                try
                {
                    string clear = $@"SELECT DISTINCT t3.AccountingUnit as Department,ifnull(t1.DoctorName, '未知') DoctorName,t1.PersonnelNumber,t1.Category,SUM(t1.Fee) Fee FROM ex_result t1
	JOIN (select distinct AccountingUnit,HISDeptName,unittype from per_dept_dic where HospitalId = @hospitalid) t3 ON t1.Department = t3.HISDeptName
WHERE t1.allotid = @allotid
	AND t3.unittype in @unittype
	AND t3.accountingunit = @accountingunit
	AND (t1.Source like '%{source}开单%' OR t1.Source like '%{source}就诊%')
	AND T1.IsDelete = 0
GROUP BY ifnull(t1.DoctorName, '未知'),t1.PersonnelNumber,t1.Category
ORDER BY t1.doctorname,t1.Category;";
                    return connection.Query<ex_result>(clear, new { allotid, accountingunit, unittype, hospitalid }, commandTimeout: 60 * 60);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
    }
}