using Dapper;
using Microsoft.EntityFrameworkCore;
using Performance.DtoModels;
using Performance.EntityModels;
using Performance.EntityModels.Other;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Performance.Repository
{
    public partial class PerforReportRepository : PerforRepository<PerReport>
    {
        private readonly IDbConnection connection;

        public PerforReportRepository(PerformanceDbContext context) : base(context)
        {
            connection = context?.Database.GetDbConnection() ?? throw new ArgumentNullException(nameof(context));
        }

        /// <summary>
        /// 月群体人均绩效
        /// </summary>
        /// <returns></returns>
        public List<PerReport> GetAvgPerfor(int hospitalid)
        {

            string sql = @"select concat(allot.year,'-',lpad(allot.month,2,'0')) x, positionname y,round(avgvalue,2) value
                from res_baiscnorm bc left join per_allot allot on bc.allotid = allot.id where allot.hospitalid = @hospitalid 
                order by str_to_date(concat(allot.month, '/', allot.year),'%m/%Y')";
            return DapperQuery(sql, new { hospitalid }).ToList();
        }

        /// <summary>
        /// 人群绩效比
        /// </summary>
        /// <param name="hospitalid"></param>
        /// <returns></returns>
        public List<PerReport> AvgRatio(int hospitalid)
        {
            string sql = @"select concat(allot.year,'-',lpad(allot.month,2,'0')) x, bc.PositionName y,round(bc.AvgValue / rbn.AvgValue,2) value
                from res_baiscnorm bc left join res_baiscnorm rbn on bc.allotid = rbn.allotid and rbn.positionname = '临床科室主任人均绩效'
                left join per_allot allot on bc.allotid = allot.id where allot.hospitalid = @hospitalid 
                order by str_to_date(concat(allot.month, '/', allot.year),'%m/%Y');";
            return DapperQuery(sql, new { hospitalid }).ToList();
        }

        /// <summary>
        /// 门诊患者均次费用
        /// </summary>
        /// <returns></returns>
        public List<PerReport> OutFeeAvg(int hospitalId, List<string> date)
        {
            string sql = @"select concat(year,'-',lpad(month,2,'0')) y,deptname x,round(sum(fee) / sum(persontime),2) value from hos_personfee where source = '门诊' and concat(year,'-',lpad(month,2,'0')) and hospitalid = @hospitalId in @date group by year,month,deptname order by y asc,value desc;";
            return DapperQuery(sql, new { date, hospitalId }).ToList();
        }

        /// <summary>
        /// 住院患者均次费用
        /// </summary>
        /// <returns></returns>
        public List<PerReport> InpatFeeAvg(int hospitalId, List<string> date)
        {
            string sql = @"select concat(year,'-',lpad(month,2,'0')) y,deptname x,round(sum(fee) / sum(persontime),2) value from hos_personfee where source = '住院' and concat(year,'-',lpad(month,2,'0')) in @date and hospitalid = @hospitalId group by year,month,deptname order by y asc,value desc;";
            return DapperQuery(sql, new { date, hospitalId }).ToList();
        }

        //    /// <summary>
        //    /// 科室药占比
        //    /// </summary>
        //    /// <returns></returns>
        //    public List<PerReport> Medicine(int hospitalId, List<string> date)
        //    {
        //        string sql = @"select accountingunit x,concat(year,'-',lpad(month,2,'0')) y,round((sum(if(cd.id is null,0,cellvalue))  / sum(cellvalue))*100,2) value 
        //            from per_allot aot join per_sheet sht on aot.id=sht.allotid join im_data dt on dt.sheetid=sht.id 
        //left join cof_drugtype cd on cd.allotid=dt.allotid and cd.charge=dt.TypeName and cd.chargetype in ('药费') where unittype=1 and sheettype=3 
        //            and sheetname like '%开单收入' and ifnull(accountingunit,'') not in ('') and concat(year,'-',lpad(month,2,'0')) 
        //            in @date and hospitalid = @hospitalId group by year,month,accountingunit order by y asc,value desc;";
        //        return DapperQuery(sql, new { hospitalId, date }).ToList();
        //    }

        //    /// <summary>
        //    /// 科室有效收入占比
        //    /// </summary>
        //    /// <returns></returns>
        //    public List<PerReport> Income(int hospitalId, List<string> date)
        //    {
        //        string sql = @"select accountingunit x,concat(year,'-',lpad(month,2,'0')) y,round((sum(if(cd.id is null,cellvalue,0)) / sum(cellvalue))*100,2) value
        //            from per_allot aot join per_sheet sht on aot.id=sht.allotid join im_data dt on dt.sheetid=sht.id 
        //left join cof_drugtype cd on cd.allotid=dt.allotid and cd.charge=dt.TypeName and cd.chargetype in ('药费','材料费') 
        //where unittype=1 and sheettype=3 and sheetname like '%开单收入' and ifnull(accountingunit,'') not in ('') and concat(year,'-',lpad(month,2,'0')) 
        //            in @date and hospitalid = @hospitalId group by year,month,accountingunit order by y asc,value desc;";
        //        return DapperQuery(sql, new { hospitalId, date }).ToList();
        //    }

        #region 首页报表
        /// <summary>
        /// 医院收入结构占比
        /// </summary>
        /// <returns></returns>
        public List<PerReport> InHosIncome(int hospitalId, string currentDate)
        {
            string sql = $"select '{currentDate}' x,'住院收入占比' y,round(t.InHos/(t.InHos+t.Outpatient) * 100, 2) value from (select year,month,sum(case SourceType when '住院' then CellValue else 0 end) InHos,sum(case SourceType when '门诊' then CellValue else 0 end) Outpatient from view_report_original_income where hospitalid = @hospitalId and concat(year,'-',lpad(month,2,'0')) = '{currentDate}' group by year,month)t" +
              $" union " +
              $"select '{currentDate}' x,'门诊收入占比' y,round(t.Outpatient/(t.InHos+t.Outpatient) * 100, 2) value from (select year,month,sum(case SourceType when '住院' then CellValue else 0 end) InHos,sum(case SourceType when '门诊' then CellValue else 0 end) Outpatient from view_report_original_income where hospitalid = @hospitalId and concat(year,'-',lpad(month,2,'0')) = '{currentDate}' group by year,month)t;";
            return DapperQuery(sql, new { hospitalId }).ToList();
        }

        /// <summary>
        /// 绩效发放金额
        /// </summary>
        /// <param name="currentDate"></param>
        /// <param name="yoyDate"></param>
        /// <param name="chainDate"></param>
        /// <returns></returns>
        public List<PerReport> PerforPayment(int hospitalId, string currentDate, string yoyDate, string chainDate)
        {
            string sql = $"select '{currentDate}' x,t1.y,ifnull(t2.RealGiveFee, 0.0000) value from (select '{currentDate}' x,'当月发放金额' y union select '{yoyDate}' x,'同期发放金额' y union select '{chainDate}' x,'环比发放金额' y)t1 left join view_report_allot_summary t2 on t1.x = concat(t2.year,'-',lpad(t2.month,2,'0')) and t2.HospitalId = @hospitalId order by y;";
            return DapperQuery(sql, new { hospitalId }).ToList();
        }

        /// <summary>
        /// 绩效发放金额占全院收入占比
        /// </summary>
        /// <param name="currentDate"></param>
        /// <param name="yoyDate"></param>
        /// <param name="chainDate"></param>
        /// <returns></returns>
        public List<PerReport> IndexPerforRatio(int hospitalId, string currentDate, string yoyDate, string chainDate)
        {
            string sql = $"select '{currentDate}' x,t1.y, round(ifnull(t2.RealGiveFee, 0.0000)/t3.income*100, 2)value from(select '{currentDate}' x,'当月发放占比' y union select '{yoyDate}' x,'同期发放占比' y union select '{chainDate}' x,'环比发放占比' y)t1 left join view_report_allot_summary t2 on t1.x = concat(t2.year,'-', lpad(t2.month,2,'0')) and t2.HospitalId = @hospitalId left join (select year, month, sum(CellValue) income from view_report_original_income where HospitalId = @hospitalId group by year, month)t3 on t1.x = concat(t3.year,'-', lpad(t3.month,2,'0')) order by y;";
            return DapperQuery(sql, new { hospitalId }).ToList();
        }

        /// <summary>
        /// 首页药占比（本月、环比、同期）
        /// </summary>
        /// <param name="hospitalId">医院Id</param>
        /// <param name="currentDate">本月日期</param>
        /// <param name="yoyDate">同比日期</param>
        /// <param name="chainDate">环比日期</param>
        /// <returns></returns>
        public List<PerReport> IndexDrugRatio(int hospitalId, string currentDate, string yoyDate, string chainDate)
        {
            string sql = $"select '{currentDate}' x,t1.y,round(t2.CellValue/t3.`value`*100, 2) value from (select '{currentDate}' x,'当月药占比' y union select '{yoyDate}' x,'同期药占比' y union select '{chainDate}' x,'环比药占比' y)t1 left join (select concat(year,'-',lpad(month,2,'0')) date,sum(CellValue) CellValue from view_report_original_income t1 inner join cof_drugtype t2 on t1.TypeName = t2.Charge and t1.AllotID = t2.AllotId and t2.ChargeType = '药费' where HospitalID = @hospitalId group by year,month) t2 on t1.x = t2.date left join (select concat(year,'-',lpad(month,2,'0')) date,sum(CellValue) value from view_report_original_income where hospitalid = @hospitalId group by year,month)t3 on t1.x = t3.date order by y;";
            return DapperQuery(sql, new { hospitalId }).ToList();
        }

        /// <summary>
        /// 首页材料占比（本月、环比、同期）
        /// </summary>
        /// <param name="hospitalId">医院Id</param>
        /// <param name="currentDate">本月日期</param>
        /// <param name="yoyDate">同比日期</param>
        /// <param name="chainDate">环比日期</param>
        /// <returns></returns>
        public List<PerReport> IndexMaterialRatio(int hospitalId, string currentDate, string yoyDate, string chainDate)
        {
            string sql = $"select '{currentDate}' x,t1.y,round(t2.CellValue/t3.`value`*100, 2) value from (select '{currentDate}' x,'当月材料占比' y union select '{yoyDate}' x,'同期材料占比' y union select '{chainDate}' x,'环比材料占比' y)t1 left join (select concat(year,'-',lpad(month,2,'0')) date,sum(CellValue) CellValue from view_report_original_income t1 inner join cof_drugtype t2 on t1.TypeName = t2.Charge and t1.AllotID = t2.AllotId and t2.ChargeType = '材料费' where HospitalID = @hospitalId group by year,month) t2 on t1.x = t2.date left join (select concat(year,'-',lpad(month,2,'0')) date,sum(CellValue) value from view_report_original_income where hospitalid = @hospitalId group by year,month)t3 on t1.x = t3.date order by y;";
            return DapperQuery(sql, new { hospitalId }).ToList();
        }

        /// <summary>
        /// 首页结构占比
        /// </summary>
        /// <returns></returns>
        public List<PerReport> IndexStructRatio(int hospitalId, string currentDate)
        {
            string sql = $"select TypeName y,t1.y x,round(CellValue/t2.`value`*100,2) value from (select '{currentDate}' y,TypeName,sum(CellValue) CellValue from view_report_original_income where HospitalID = @hospitalId and concat(year,'-',lpad(month,2,'0')) = '{currentDate}' group by TypeName) t1 inner join (select '{currentDate}' y,sum(CellValue) value from view_report_original_income where hospitalid = @hospitalId and concat(year,'-',lpad(month,2,'0')) = '{currentDate}')t2 on t1.y = t2.y order by value desc;";
            return DapperQuery(sql, new { hospitalId }).ToList();
        }
        #endregion

        #region 菜单报表
        /// <summary>
        /// 业务总收入
        /// </summary>
        /// <returns></returns>
        public List<PerReport> GeneralIncome(ReportRequest request)
        {
            string where = "";
            if (!string.IsNullOrEmpty(request.Year))
            {
                where += $" and year in ({request.Year}) ";
            }
            if (!string.IsNullOrEmpty(request.Month))
            {
                where += $" and month in ({request.Month}) ";
            }
            string sql = $"select concat(year,'年') x,'业务总收入（年）' y,sum(CellValue) value from view_report_original_income where hospitalid = @hospitalId {where} group by year order by x;";
            if (request.OnlyYear != 1)
                sql = $"select concat(month,'月') x,concat(year,'年') y,sum(CellValue) value from view_report_original_income where hospitalid = @hospitalId {where} group by year,month order by x;";
            return DapperQuery(sql, new { hospitalId = request.HospitalId }).ToList();
        }

        /// <summary>
        /// 门诊、住院业务收入占比
        /// </summary>
        /// <returns></returns>
        public List<PerReport> InHosIncome(ReportRequest request)
        {
            string where = "";
            if (!string.IsNullOrEmpty(request.Year))
            {
                where += $" and year in ({request.Year}) ";
            }
            if (!string.IsNullOrEmpty(request.Month))
            {
                where += $" and month in ({request.Month}) ";
            }
            string sql = $"select * from (select '住院' y,concat(year,'年') x,round(t.InHos/(t.InHos + t.Outpatient) * 100, 2) value from (select year,sum(case SourceType when '住院' then CellValue else 0 end) InHos,sum(case SourceType when '门诊' then CellValue else 0 end) Outpatient from view_report_original_income where hospitalid = @hospitalId {where} group by year)t" +
                $" union " +
                $"select '门诊' y,concat(year,'年') x,round(t.Outpatient/(t.InHos + t.Outpatient) * 100, 2) value from (select year,sum(case SourceType when '住院' then CellValue else 0 end) InHos,sum(case SourceType when '门诊' then CellValue else 0 end) Outpatient from view_report_original_income where hospitalid = @hospitalId {where} group by year)t)t order by t.x;";
            if (request.OnlyYear != 1)
                sql = $"select * from (select '住院' y,concat(month,'月') x,round(t.InHos/(t.InHos+t.Outpatient) * 100, 2) value from (select year,month,sum(case SourceType when '住院' then CellValue else 0 end) InHos,sum(case SourceType when '门诊' then CellValue else 0 end) Outpatient from view_report_original_income where hospitalid = @hospitalId {where} group by month)t" +
                    $" union " +
                    $"select '门诊' y,concat(month,'月') x,round(t.Outpatient/(t.InHos+t.Outpatient) * 100, 2) value from (select year,month,sum(case SourceType when '住院' then CellValue else 0 end) InHos,sum(case SourceType when '门诊' then CellValue else 0 end) Outpatient from view_report_original_income where hospitalid = @hospitalId {where} group by month)t)t order by t.x;";
            return DapperQuery(sql, new { hospitalId = request.HospitalId }).ToList();
        }

        /// <summary>
        /// 业务收入结构占比
        /// </summary>
        /// <returns></returns>
        public List<PerReport> StructRatio(ReportRequest request)
        {
            string where = "";
            if (!string.IsNullOrEmpty(request.Year))
            {
                where += $" and year in ({request.Year}) ";
            }
            if (!string.IsNullOrEmpty(request.Month))
            {
                where += $" and month in ({request.Month}) ";
            }
            string sql = $"select TypeName y,concat(t1.y,'年') x,round(CellValue/t2.`value`*100,2) value from (select year y,TypeName,sum(CellValue) CellValue from view_report_original_income where HospitalID = @hospitalId {where} group by year,TypeName) t1 inner join (select year y,sum(CellValue) value from view_report_original_income where hospitalid = @hospitalId {where} group by year)t2 on t1.y = t2.y order by x asc,value desc;";
            if (request.OnlyYear != 1)
                sql = $"select TypeName y,concat(t1.y,'月') x,round(CellValue/t2.`value`*100,2) value from (select month y,TypeName,sum(CellValue) CellValue from view_report_original_income where HospitalID = @hospitalId {where} group by month,TypeName) t1 inner join (select month y,sum(CellValue) value from view_report_original_income where hospitalid = @hospitalId {where} group by month)t2 on t1.y = t2.y order by x asc,value desc;";
            return DapperQuery(sql, new { hospitalId = request.HospitalId }).ToList();
        }

        /// <summary>
        /// 药占比
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="request.OnlyYear"></param>
        /// <returns></returns>
        public List<PerReport> DrugRatio(ReportRequest request)
        {
            string where = "";
            if (!string.IsNullOrEmpty(request.Year))
            {
                where += $" and year in ({request.Year}) ";
            }
            if (!string.IsNullOrEmpty(request.Month))
            {
                where += $" and month in ({request.Month}) ";
            }
            string sql = $"select '药占比（年）' y,concat(t1.y,'年') x,round(CellValue/t2.`value`*100,2) value from (select year y,sum(CellValue) CellValue from view_report_original_income t1 inner join cof_drugtype t2 on t1.TypeName = t2.Charge and t1.AllotID = t2.AllotId and t2.ChargeType = '药费' where HospitalID = @hospitalId {where} group by year) t1 inner join (select year y,sum(CellValue) value from view_report_original_income where hospitalid = @hospitalId {where} group by year)t2 on t1.y = t2.y order by x asc;";
            if (request.OnlyYear != 1)
                sql = $"select concat(t1.year,'年') y,concat(t1.month,'月') x,round(CellValue/t2.`value`*100,2) value from (select month,year,sum(CellValue) CellValue from view_report_original_income t1 inner join cof_drugtype t2 on t1.TypeName = t2.Charge and t1.AllotID = t2.AllotId and t2.ChargeType = '药费' where HospitalID = @hospitalId {where} group by month,year) t1 inner join (select month,year,sum(CellValue) value from view_report_original_income where hospitalid = @hospitalId {where} group by month,year)t2 on t1.`month`=t2.`month` and t1.year = t2.year order by x asc;";
            return DapperQuery(sql, new { hospitalId = request.HospitalId }).ToList();
        }

        /// <summary>
        /// 材料占比
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="request.OnlyYear"></param>
        /// <returns></returns>
        public List<PerReport> MaterialRatio(ReportRequest request)
        {
            string where = "";
            if (!string.IsNullOrEmpty(request.Year))
            {
                where += $" and year in ({request.Year}) ";
            }
            if (!string.IsNullOrEmpty(request.Month))
            {
                where += $" and month in ({request.Month}) ";
            }
            string sql = $"select '材料占比（年）' y,concat(t1.y,'年') x,round(CellValue/t2.`value`*100,2) value from (select year y,sum(CellValue) CellValue from view_report_original_income t1 inner join cof_drugtype t2 on t1.TypeName = t2.Charge and t1.AllotID = t2.AllotId and t2.ChargeType = '材料费' where HospitalID = @hospitalId {where} group by year) t1 inner join (select year y,sum(CellValue) value from view_report_original_income where hospitalid = @hospitalId {where} group by year)t2 on t1.y = t2.y order by x asc;";
            if (request.OnlyYear != 1)
                sql = $"select concat(t1.year,'年') y,concat(t1.month,'月') x,round(CellValue/t2.`value`*100,2) value from (select month,year,sum(CellValue) CellValue from view_report_original_income t1 inner join cof_drugtype t2 on t1.TypeName = t2.Charge and t1.AllotID = t2.AllotId and t2.ChargeType = '材料费' where HospitalID = @hospitalId {where} group by month,year) t1 inner join (select month,year,sum(CellValue) value from view_report_original_income where hospitalid = @hospitalId {where} group by month,year)t2 on t1.`month`=t2.`month` and t1.year = t2.year order by x asc;";
            return DapperQuery(sql, new { hospitalId = request.HospitalId }).ToList();
        }

        /// <summary>
        /// 绩效发放金额占全院收入占比
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="request.OnlyYear"></param>
        /// <returns></returns>
        public List<PerReport> PerforRatio(ReportRequest request)
        {
            string where = "";
            if (!string.IsNullOrEmpty(request.Year))
            {
                where += $" and year in ({request.Year}) ";
            }
            if (!string.IsNullOrEmpty(request.Month))
            {
                where += $" and month in ({request.Month}) ";
            }
            string sql = $"select concat(t2.x,'年') x,'绩效发放金额占全院收入占比（年）' y,round(t1.RealGiveFee/t2.income * 100, 2) value from (select `year`,sum(realgivefee) realgivefee from view_report_allot_summary where HospitalID = @hospitalId {where} group by year) t1 inner join (select year x,sum(CellValue) income from view_report_original_income where HospitalId = @hospitalId {where} group by year)t2 on t1.`Year` = t2.x order by x asc;";
            if (request.OnlyYear != 1)
                sql = $"select concat(t2.month,'月') x,concat(t2.year,'年') y,round(t1.RealGiveFee/t2.income * 100, 2) value from (select `year`,`month`,sum(realgivefee) realgivefee from view_report_allot_summary where HospitalID = @hospitalId {where} group by year,month) t1 inner join (select `year`,`month`,sum(CellValue) income from view_report_original_income where HospitalId = @hospitalId {where} group by year,month)t2 on t1.month = t2.month and t1.year = t2.year order by x asc;";
            return DapperQuery(sql, new { hospitalId = request.HospitalId }).ToList();
        }

        /// <summary>
        /// 绩效群体收入
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="request.OnlyYear"></param>
        /// <returns></returns>
        public List<PerReport> PerforGroup(ReportRequest request)
        {
            string where = "";
            if (!string.IsNullOrEmpty(request.Year))
            {
                where += $" and year in ({request.Year}) ";
            }
            if (!string.IsNullOrEmpty(request.Month))
            {
                where += $" and month in ({request.Month}) ";
            }
            string sql = $"select PositionName y,concat(year,'年') x,round(sum(avgvalue), 2) value from (select t1.PositionName,year,AvgValue from res_baiscnorm t1 inner join per_allot t2 on t1.AllotID = t2.Id and t2.states in (6,8,10) and t2.HospitalId = @hospitalId {where} and locate('保底', t1.PositionName) = 0)t group by PositionName,year order by x asc,value desc;";
            if (request.OnlyYear != 1)
                sql = $"select PositionName y,concat(month,'月') x,round(sum(avgvalue), 2) value from (select t1.PositionName,month,AvgValue from res_baiscnorm t1 inner join per_allot t2 on t1.AllotID = t2.Id and t2.states in (6,8,10) and t2.HospitalId = @hospitalId {where} and locate('保底', t1.PositionName) = 0)t group by PositionName,month order by x asc,value desc;";
            return DapperQuery(sql, new { hospitalId = request.HospitalId }).ToList();
        }

        /// <summary>
        /// 医生核算单元人均绩效
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="request.OnlyYear"></param>
        /// <returns></returns>
        public List<PerReport> DoctorAvg(ReportRequest request)
        {
            string where = "";
            if (!string.IsNullOrEmpty(request.Year))
            {
                where += $" and t2.year in ({request.Year}) ";
            }
            if (!string.IsNullOrEmpty(request.Month))
            {
                where += $" and t2.month in ({request.Month}) ";
            }
            string sql = $"select concat(`Year`,'年') x,AccountingUnit y,round(sum(avg),2) value from (select t1.AccountingUnit,t1.Avg,t2.`Year`,t2.`Month`,t2.ID from res_account t1 inner join per_allot t2 on t1.AllotID = t2.ID and t2.states in (6,8,10) and t2.HospitalId = @hospitalId and t1.UnitType != 2 where 1=1 {where})t group by year,AccountingUnit order by x asc,value desc;";
            if (request.OnlyYear != 1)
                sql = $"select concat(`Month`,'月') x,AccountingUnit y,round(sum(avg),2) value from (select t1.AccountingUnit,t1.Avg,t2.`Year`,t2.`Month`,t2.ID from res_account t1 inner join per_allot t2 on t1.AllotID = t2.ID and t2.states in (6,8,10) and t2.HospitalId = @hospitalId and t1.UnitType != 2 where 1=1 {where})t group by month,AccountingUnit order by x asc,value desc;";
            return DapperQuery(sql, new { hospitalId = request.HospitalId }).ToList();
        }

        /// <summary>
        /// 护理核算单元人均绩效
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="request.OnlyYear"></param>
        /// <returns></returns>
        public List<PerReport> NurseAvg(ReportRequest request)
        {
            string where = "";
            if (!string.IsNullOrEmpty(request.Year))
            {
                where += $" and t2.year in ({request.Year}) ";
            }
            if (!string.IsNullOrEmpty(request.Month))
            {
                where += $" and t2.month in ({request.Month}) ";
            }
            string sql = $"select concat(`Year`,'年') x,AccountingUnit y,round(sum(avg),2) value from (select t1.AccountingUnit,t1.Avg,t2.`Year`,t2.`Month`,t2.ID from res_account t1 inner join per_allot t2 on t1.AllotID = t2.ID and t2.states in (6,8,10) and t2.HospitalId = @hospitalId and t1.UnitType = 2 where 1=1 {where})t group by year,AccountingUnit order by x asc,value desc;";
            if (request.OnlyYear != 1)
                sql = $"select concat(`Month`,'月') x,AccountingUnit y,round(sum(avg),2) value from (select t1.AccountingUnit,t1.Avg,t2.`Year`,t2.`Month`,t2.ID from res_account t1 inner join per_allot t2 on t1.AllotID = t2.ID and t2.states in (6,8,10) and t2.HospitalId = @hospitalId and t1.UnitType = 2 where 1=1 {where})t group by month,AccountingUnit order by x asc,value desc;";
            return DapperQuery(sql, new { hospitalId = request.HospitalId }).ToList();
        }
        #endregion

        /// <summary>
        /// 获取医院预留绩效
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public List<EmployeeReservedDto> GetEmployeeReserved(int hospitalId, int year)
        {
            string sql = "SELECT * FROM view_allot_result_report WHERE HospitalID=@HospitalID AND Year=@Year";
            return DapperQuery<EmployeeReservedDto>(sql, new { HospitalID = hospitalId, Year = year })?.ToList();
        }

        public List<view_allot_result> GetOwnerPerformance(List<int> hospitalId, string jobNumber)
        {
            string sql = "SELECT * FROM view_allot_result WHERE States = 8 AND HospitalID IN @HospitalID AND JobNumber=@JobNumber";
            return DapperQuery<view_allot_result>(sql, new { HospitalID = hospitalId, JobNumber = jobNumber })?.ToList();
        }

        public List<dynamic> QueryCompute(int allotId, string viewName)
        {
            var sql = $@"SELECT * FROM {viewName} WHERE   AllotId = @AllotId; ";

            return DapperQuery<dynamic>(sql, new { allotId })?.ToList();
        }

        public List<dynamic> QueryComputeByDate(string viewName, BeginEndTime request)
        {
            var sql = $@"SELECT * FROM {viewName} 
where STR_TO_DATE(concat(Year,'-',Month,'-01'),'%Y-%m-%d') >= @beginTime 
    and STR_TO_DATE(concat(Year,'-',Month,'-01'),'%Y-%m-%d') < @endTime and hospitalid = {request.HospitalId}";

            if (request.Search != null && request.Search.Any(w => !string.IsNullOrEmpty(w.Title) && !string.IsNullOrEmpty(w.Value)))
            {
                var where = request.Search.Where(w => !string.IsNullOrEmpty(w.Title) && !string.IsNullOrEmpty(w.Value)).Select(t => $"{t.Title} like '%{t.Value}%'");
                sql += " and " + string.Join(" and ", where);
            }

            if (!string.IsNullOrEmpty(request.SortBy))
                sql += $" order by {request.SortBy} ";
            else
                sql += $" order by hospitalid,code,unittype,accountingunit";

            return DapperQuery<dynamic>(sql, new { beginTime = request.BeginTime, endTime = request.EndTime }).ToList();
        }

        public List<dynamic> QueryComputeByDateAndTotal(string viewName, HospitalGrantSummary request)
        {
            if (!new string[] { "view_allot_sign_dept", "view_allot_sign_emp", "view_allot_sign_emp_finance" }.Contains(viewName)) return new List<dynamic>();

            var sql = $@" SELECT * FROM {viewName} where STR_TO_DATE(concat(Year,'-',Month,'-01'),'%Y-%m-%d') >= @beginTime 
                      and STR_TO_DATE(concat(Year,'-',Month,'-01'),'%Y-%m-%d') < @endTime and hospitalid = {request.HospitalId}";

            Dictionary<string, List<string>> dict = new Dictionary<string, List<string>>
            {
                { "view_allot_sign_emp_group", new List<string>{ "hospitalid", "code", "unittype", "accountingunit", "source", "jobnumber", "employeename", "jobtitle", "bankcard", "batch", "jobcategory", "duty", "titleposition" } },
                { "view_allot_sign_emp_sum", new List<string>{ "perforsumfee", "performanagementfee", "nightworkperfor", "adjustlaterotherfee", "otherperfor", "hideotherperfor", "shouldgivefee", "reservedratiofee", "realgivefee" } },

                { "view_allot_sign_dept_group", new List<string>{ "hospitalid", "code", "unittype", "accountingunit" } },
                { "view_allot_sign_dept_sum", new List<string>{ "perforfee", "workloadfee", "assessbeforeotherfee", "perfortotal", "scoringaverage", "extra", "medicineextra", "materialsextra", "assesslaterotherfee", "assesslaterperfortotal", "adjustfactor", "adjustlaterotherfee", "aprperforamount", "hideaprotherperforamount", "assesslatermanagementfee", "realgivefee" } },

                { "view_allot_sign_emp_finance_group", new List<string>{ "hospitalid", "code", "unittype", "accountingunit","jobnumber", "employeename", } },
                { "view_allot_sign_emp_finance_sum", new List<string>{ "perforsumfee", "performanagementfee", "nightworkperfor", "adjustlaterotherfee", "otherperfor", "hideotherperfor", "shouldgivefee", "reservedratiofee", "realgivefee" } },
            };

            request.GroupBy.Remove("");
            if (request.SumBy == null || !request.SumBy.Any(t => !string.IsNullOrEmpty(t))) request.SumBy = dict[viewName + "_sum"];

            if (request.Search != null && request.Search.Any(w => !string.IsNullOrEmpty(w.Title) && !string.IsNullOrEmpty(w.Value)))
            {
                var where = request.Search.Where(w => !string.IsNullOrEmpty(w.Title) && !string.IsNullOrEmpty(w.Value)).Select(t => $"{t.Title} like '%{t.Value}%'");
                sql += " and " + string.Join(" and ", where);
            }

            if (!string.IsNullOrEmpty(request.SortBy))
                sql += $" order by {request.SortBy} ";
            else
                sql += $" order by hospitalid,code,unittype,accountingunit";
            if (request.GroupBy == null || !request.GroupBy.Any(t => !string.IsNullOrEmpty(t))) /*request.GroupBy = dict[viewName + "_group"];*/
            {
                sql = $"select {string.Join(",", dict[viewName + "_group"])}, {string.Join(",", request.SumBy.Select(t => $"sum({t}) {t}"))} from ({sql}) tab group by {string.Join(",", dict[viewName + "_group"])}";
            }
            else
            {
                sql = $"select {string.Join(",", request.GroupBy)}, {string.Join(",", request.SumBy.Select(t => $"sum({t}) {t}"))} from ({sql}) tab group by {string.Join(",", request.GroupBy)}";
            }
            //sql = $"select {string.Join(",", request.GroupBy)}, {string.Join(",", request.SumBy.Select(t => $"sum({t}) {t}"))} from ({sql}) tab group by {string.Join(",", request.GroupBy)}";

            return DapperQuery<dynamic>(sql, new { beginTime = request.BeginTime, endTime = request.EndTime }).ToList();
        }

        public (List<dynamic> list, int count) QueryComputePageData(string query, object param = null)
        {
            try
            {
                using (var multi = connection.QueryMultiple(query, param, commandTimeout: 1000))
                {
                    var list = multi.Read<dynamic>().ToList();
                    var count = multi.Read<int>().FirstOrDefault();
                    return (list, count);
                }
            }
            catch
            {
                return (new List<dynamic>(), 0);
            }
        }

        /// <summary>
        /// 科室
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="allotId"></param> 
        /// <param name="accountingUnit"></param>
        /// <param name="unitType"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public CustonPagingData QueryCustom(string tableName, int allotId, string accountingUnit, string[] unitType, int pageIndex = 1, int pageSize = 20)
        {
            string dataQuery = $@"SELECT * FROM {tableName} WHERE AllotId = @AllotId and AccountingUnit = @accountingUnit and UnitType in @unitType  order by UnitType,AccountingUnit LIMIT {(pageIndex - 1) * pageSize},{pageSize} ";
            string countQuery = $@"SELECT COUNT(*) FROM {tableName} WHERE AllotId = @AllotId and AccountingUnit = @accountingUnit and UnitType in @unitType  ";

            var result = new CustonPagingData
            {
                DataList = DapperQuery<dynamic>(dataQuery, new { allotId, unitType, accountingUnit })?.ToList(),
                TotalCount = DapperQuery<int>(countQuery, new { allotId, unitType, accountingUnit })?.FirstOrDefault() ?? 0,
            };

            return result;
        }

        /// <summary>
        /// 管理员
        /// </summary>
        /// <param name="request"></param>
        /// <param name="IsHead"></param>
        /// <returns></returns>
        public CustonPagingData QueryCustom(CustomPagingRequest request, bool IsHead)
        {
            var result = new CustonPagingData();
            string sql, Query;

            if (string.IsNullOrEmpty(request.QuerySearch))
                Query = " and 1=1 ";
            else
                Query = $@" and (AccountingUnit like '%{request.QuerySearch}%' or UnitType like '%{request.QuerySearch}%') ";

            if (IsHead)
                sql = $@"SELECT * FROM {request.TableName} WHERE AllotId = @AllotId ";
            else
                sql = $@"SELECT * FROM {request.TableName} WHERE AllotId = @AllotId {Query} order by UnitType,AccountingUnit LIMIT {(request.PageIndex - 1) * request.PageSize},{request.PageSize} ";


            result.DataList = DapperQuery<dynamic>(sql, new { request.AllotId })?.ToList();

            sql = $@"SELECT COUNT(*) FROM {request.TableName} WHERE AllotId = @AllotId {Query} ";
            result.TotalCount = DapperQuery<int>(sql, new { request.AllotId })?.FirstOrDefault() ?? 0;
            return result;
        }

        public bool QueryIsAllotId(string tableName, params string[] columns)
        {
            var database = context.Database.GetDbConnection().Database;
            var sql = $@"SELECT column_name FROM information_schema.COLUMNS s
                    WHERE table_name = @table_name AND TABLE_SCHEMA = @database AND column_name in @columns;";
            var result = DapperQuery<string>(sql, new { database = database, table_name = tableName, columns });
            var isExist = result?.Count() == 3;


            return isExist;

        }

        public List<ColumnEntity> QueryCustomColumn(string tableName)
        {
            var database = context.Database.GetDbConnection().Database;
            var sql = $@"SELECT column_name as Name,ifnull(column_comment,column_name) as comment FROM information_schema.`COLUMNS` WHERE table_schema = @table_schema AND table_name = @table_name AND  COLUMN_KEY <> 'PRI' ";
            var result = DapperQuery<ColumnEntity>(sql, new { table_schema = database, table_name = tableName })?.ToList();

            return result;
        }

        public bool CreatCustom(int AllotId, string tableName, List<dynamic> datas)
        {
            var sql = $@"DELETE FROM {tableName} WHERE allotId=@AllotId ";
            Execute(sql, new { AllotId });
            if (datas == null)
                return true;

            var database = context.Database.GetDbConnection().Database;
            sql = $@"SELECT column_name FROM information_schema.COLUMNS s
                    WHERE table_name = @table_name AND TABLE_SCHEMA = @database AND COLUMN_KEY <> 'PRI';";
            var columns = DapperQuery<string>(sql, new { database = database, table_name = tableName })?.ToList();

            sql = $"INSERT INTO {tableName}({string.Join(",", columns.ToArray())}) VALUES({string.Join(",", columns.Select(t => "@" + t))});";

            var success = Execute(sql, datas);
            if (success > 0)
                return true;
            else
                return false;
        }

        public List<dynamic> QueryType(string tableName)
        {
            var database = context.Database.GetDbConnection().Database;
            var sql = $@"select column_name,data_type from information_schema.columns where table_name=@table_name  and table_schema=@table_schema AND (data_type like '%int%' or  data_type like '%decimal%' or data_type like '%date%') AND COLUMN_KEY <> 'PRI' ";
            var result = DapperQuery<dynamic>(sql, new { table_schema = database, table_name = tableName })?.ToList();

            return result;
        }
    }
}
