//-----------------------------------------------------------------------
// <copyright file=" cof_director.cs">
// * FileName: cof_director.cs
// * history : Created by T4 2019-03-22 15:48:55
// </copyright>
//-----------------------------------------------------------------------
using Dapper;
using Microsoft.EntityFrameworkCore;
using Performance.EntityModels;
using System;
using System.Collections.Generic;

namespace Performance.Repository
{
    /// <summary>
    /// cof_director Repository
    /// </summary>
    public partial class PerforCofdirectorRepository : PerforRepository<cof_director>
    {
        public int DeleteData(int allotId)
        {
            List<string> tableArray = new List<string>
            {
                "im_accountbasic",
                "im_data",
                "im_employee",
                "im_employee_clinic",
                "im_employee_logistics",
                "im_header",
                "im_specialunit",
                "log_check",
                "per_sheet",
                "res_account",
                "res_accountdoctor",
                "res_accountnurse",
                "res_baiscnorm",
                "res_compute",
                "res_specialunit",
                "report_allot_crowd",
                "report_allot_summary",
                "report_original_income",
                "report_original_persontime",
                "report_original_stays",
                "report_original_surgery",
                "report_original_workload",
            };
            string sql = "";
            tableArray.ForEach(t => sql += $"delete from {t} where allotid=@allotId;");
            sql += "delete from log_dbug where allotid=@allotId and type=1;";

            return Execute(sql, new { allotId });
        }

        public int DeleteAllotData(int allotId)
        {
            List<string> tableArray = new List<string>
            {
                "ag_secondallot",
                "cof_accounting",
                "cof_again",
                "cof_check",
                "cof_cmi",
                "cof_depttype",
                "cof_director",
                "cof_drugprop",
                "cof_drugtype",
                "cof_guarantee",
                "cof_income",
                "cof_singlefactor",
                "cof_workitem",
                "cof_workyear",
                "im_accountbasic",
                "im_data",
                "im_employee",
                "im_employee_clinic",
                "im_employee_logistics",
                "im_header",
                "im_specialunit",
                "log_check",
                "per_employee",
                "per_sheet",
                "report_allot_crowd",
                "report_allot_summary",
                "report_original_income",
                "report_original_persontime",
                "report_original_stays",
                "report_original_surgery",
                "report_original_workload",
                "res_account",
                "res_accountdoctor",
                "res_accountnurse",
                "res_baiscnorm",
                "res_compute",
                "res_specialunit",
            };
            string sql = "";
            tableArray.ForEach(t => sql += $"delete from {t} where allotid=@allotId;");
            sql += "delete from log_dbug where allotid=@allotId and type=1;";

            return Execute(sql, new { allotId });
        }

        public int DeleteResData(int allotId)
        {
            List<string> tableArray = new List<string>
            {
                "log_dbug",
                "report_allot_crowd",
                "report_allot_summary",
                "report_original_income",
                "report_original_persontime",
                "report_original_stays",
                "report_original_surgery",
                "report_original_workload",
                "res_account",
                "res_accountdoctor",
                "res_accountnurse",
                "res_baiscnorm",
                "res_compute",
                "res_specialunit",
            };
            string sql = "";
            tableArray.ForEach(t => sql += $"delete from {t} where allotid=@allotId;");
            sql += "delete from per_sheet where allotid=@allotId and source=2;";

            return Execute(sql, new { allotId });
        }

        public int DelAgain(int againid)
        {
            List<string> tableArray = new List<string>
            {
                "ag_data",
                "ag_employee",
                "ag_header",
                "ag_againsituation"
            };
            string sql = "";
            tableArray.ForEach(t => sql += $"delete from {t} where againallotid=@againid;");

            return Execute(sql, new { againid });
        }

        /// <summary>
        /// 补充费用占比类型
        /// </summary>
        /// <param name="allotid"></param>
        /// <returns></returns>
        public int SupplementaryData(int allotid)
        {
            using (var connection = context.Database.GetDbConnection())
            {
                #region MyRegion

                //                var sql = @"insert into cof_drugtype(allotid,charge) select
                //distinct @allotid allotid,typename charge
                //from per_sheet sheet
                //	inner join im_data im on sheet.id = im.sheetid
                //	left join cof_drugtype dtype on sheet.allotid = dtype.allotid
                //		and im.typename != dtype.charge
                //where sheet.allotid = @allotid and sheet.sheettype = 3 and im.typename not in
                //	(select charge from cof_drugtype where allotid = @allotid);

                //insert into cof_depttype(allotid,charge) select
                //distinct @allotid allotid,department charge
                //from per_sheet sheet
                //	inner join im_data im on sheet.id = im.sheetid
                //	left join cof_depttype dtype on sheet.allotid = dtype.allotid
                //		and im.department != dtype.charge
                //where sheet.allotid = @allotid and sheet.sheettype = 9 and im.department not in
                //	(select charge from cof_depttype where allotid = @allotid);";

                #endregion MyRegion

                List<string> sqls = new List<string>
                {
                    @"insert into cof_drugtype(allotid,charge) select
distinct @allotid allotid,typename charge
from per_sheet sheet
	inner join im_data im on sheet.id = im.sheetid
	left join cof_drugtype dtype on sheet.allotid = dtype.allotid
		and im.typename != dtype.charge
where sheet.allotid = @allotid and sheet.sheettype = 3 and im.typename not in
	(select charge from cof_drugtype where allotid = @allotid);",

                    @"insert into cof_depttype(allotid,charge) select
distinct @allotid allotid,department charge
from per_sheet sheet
	inner join im_data im on sheet.id = im.sheetid
	left join cof_depttype dtype on sheet.allotid = dtype.allotid
		and im.department != dtype.charge
where sheet.allotid = @allotid and sheet.sheettype = 9 and im.department not in
	(select charge from cof_depttype where allotid = @allotid);",

                   /* @"insert into per_apr_amount(AllotId, PersonnelNumber, DoctorName)
select distinct @allotid AllotId, t.PersonnelNumber, t.DoctorName from
(
	select AllotId,PeopleNumber PersonnelNumber,DoctorName from im_employee where allotid = @allotid
	union all
	select AllotId,PersonnelNumber,DoctorName from im_employee_clinic where allotid = @allotid
) t where DoctorName not in (select DoctorName from per_apr_amount where allotid = @allotid);",*/

                    @"insert into cof_cmi(AllotId, UnitType, AccountingUnit)
select AllotId,UnitType,DoctorAccountingUnit from im_accountbasic where allotid = @allotid
and concat(UnitType,DoctorAccountingUnit) not in (select concat(UnitType,AccountingUnit) from cof_cmi where allotid = @allotid)"
                };

                int flag = 0;
                foreach (var sql in sqls)
                {
                    try
                    {
                        flag = connection.Execute(sql, new { allotid }, commandTimeout: 60 * 60);
                    }
                    catch (Exception)
                    {
                    }
                }
                return flag;
            }
        }

        public IEnumerable<view_dic_category_factor> QueryCategoryFactor(int hospitalId, int allotId)
        {

            return DapperQuery<view_dic_category_factor>("select * from view_dic_category_factor where hospitalid = @hospitalid and allotid = @allotid;", new { hospitalId, allotId });
        }
    }
}
