using Performance.EntityModels;
using System;
using System.Collections.Generic;
using System.Linq;

namespace Performance.Repository
{
    public partial class PerforReportRepository : PerforRepository<PerReport>
    {
        public PerforReportRepository(PerformanceDbContext context) : base(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();
        }
    }
}
