web_server/src/main/java/thyyxxk/webserver/dao/his/ybkf/YbStatDao.java

package thyyxxk.webserver.dao.his.ybkf;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import thyyxxk.webserver.entity.medicalinsurance.setlinfo.SiSetlinfo;
import thyyxxk.webserver.entity.ybkf.YbStatResult;

import java.util.List;
import java.util.Map;

/**
 *@Author hsh
 *@Description 医保统计查询
 *@Date 2022/6/17 9:12
 *@Param
 *@Return
 **/
@Mapper
public interface YbStatDao {

    @Select("<script>" +
            "select psnCount=count(distinct a.setl_id), " +
            "inDays=sum(datediff(day, zy.admiss_date, zy.dis_date)), " +
            "avgInDays=cast(round(sum(datediff(day, zy.admiss_date, zy.dis_date))/cast(count(distinct a.setl_id) as decimal(10,2)),2) as decimal(10,2)), " +
            "totalFee=cast(round(sum(a.medfee_sumamt),2) as decimal(10,2)), " +
            "fundPooling=cast(round(sum(a.hifp_pay),2) as decimal(10,2)), " +
            "bigIllFundPooling=cast(round(sum(a.hifmi_pay),2) as decimal(10,2)), " +
            "civilServiceFund=cast(round(sum(a.cvlserv_pay),2) as decimal(10,2)), " +
            "otherFunds=cast(round(sum(isnull(a.fund_pay_sumamt,0)-isnull(a.hifp_pay,0)-isnull(a.cvlserv_pay,0)-isnull(a.hifmi_pay,0)),2) as decimal(10,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(10,2)), " +
            "drugFee=cast(round(sum(isnull(a.charge_western_medicine,0) + isnull(a.charge_patent_medicine,0) + isnull(a.charge_herbal,0)),2) as decimal(10,2)), " +
            "drugFeeRatio=cast(round(sum(isnull(a.charge_western_medicine,0) + isnull(a.charge_patent_medicine,0) + isnull(a.charge_herbal,0))/sum(a.medfee_sumamt)*100,2) as decimal(10,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(10,2)), " +
            "matFeeRatio=cast(round(sum(a.charge_sanitary_material)/sum(a.medfee_sumamt)*100,2) as decimal(10,2)) " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time&gt;=#{beginTime} and a.setl_time&lt;=#{endTime} " +
            "<if test=\"insuplcAdmdvs != null and insuplcAdmdvs.size > 0 \">" +
            " and a.insuplc_admdvs in  " +
            " <foreach collection='insuplcAdmdvs' item='item' index='index' open='(' close=')' separator=','>  " +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"insurtype != null and insurtype.size > 0 \">" +
            " and a.insutype in  " +
            " <foreach collection='insurtype' item='item' index='index' open='(' close=')' separator=','>  " +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"med_type != null and med_type.size > 0 \">" +
            " and a.med_type in  " +
            " <foreach collection='med_type' item='item' index='index' open='(' close=')' separator=','>  " +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "</script>")
    YbStatResult selectYbStatInfo(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType,
        @Param("insuplcAdmdvs") List<String> insuplcAdmdvs, @Param("insurtype") List<String> insurtype,
        @Param("med_type") List<String> med_type);

    @Select("<script>" +
            "select psnCount=count(distinct a.setl_id), " +
            "inDays=sum(datediff(day, zy.admiss_date, zy.dis_date)), " +
            "avgInDays=cast(round(sum(datediff(day, zy.admiss_date, zy.dis_date))/cast(count(distinct a.setl_id) as decimal(10,2)),2) as decimal(10,2)), " +
            "totalFee=cast(round(sum(a.medfee_sumamt),2) as decimal(10,2)), " +
            "fundPooling=cast(round(sum(a.hifp_pay),2) as decimal(10,2)), " +
            "bigIllFundPooling=cast(round(sum(a.hifmi_pay),2) as decimal(10,2)), " +
            "civilServiceFund=cast(round(sum(a.cvlserv_pay),2) as decimal(10,2)), " +
            "otherFunds=cast(round(sum(isnull(a.fund_pay_sumamt,0)-isnull(a.hifp_pay,0)-isnull(a.cvlserv_pay,0)-isnull(a.hifmi_pay,0)),2) as decimal(10,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(10,2)), " +
            "drugFee=cast(round(sum(isnull(a.charge_western_medicine,0) + isnull(a.charge_patent_medicine,0) + isnull(a.charge_herbal,0)),2) as decimal(10,2)), " +
            "drugFeeRatio=cast(round(sum(isnull(a.charge_western_medicine,0) + isnull(a.charge_patent_medicine,0) + isnull(a.charge_herbal,0))/sum(a.medfee_sumamt)*100,2) as decimal(10,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(10,2)), " +
            "matFeeRatio=cast(round(sum(a.charge_sanitary_material)/sum(a.medfee_sumamt)*100,2) as decimal(10,2)) " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time&gt;=#{beginTime} and a.setl_time&lt;=#{endTime} " +
            "and a.insuplc_admdvs != '439900' and a.insuplc_admdvs like '43%' and a.insuplc_admdvs not like '4301%' and a.med_type != '2102' " +
            "</script>")
    YbStatResult selectYbStatInfoInProvinceOtherCities(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType);

    @Select("<script>" +
            "select psnCount=count(distinct a.setl_id), " +
            "inDays=sum(datediff(day, zy.admiss_date, zy.dis_date)), " +
            "avgInDays=cast(round(sum(datediff(day, zy.admiss_date, zy.dis_date))/cast(count(distinct a.setl_id) as decimal(10,2)),2) as decimal(10,2)), " +
            "totalFee=cast(round(sum(a.medfee_sumamt),2) as decimal(10,2)), " +
            "fundPooling=cast(round(sum(a.hifp_pay),2) as decimal(10,2)), " +
            "bigIllFundPooling=cast(round(sum(a.hifmi_pay),2) as decimal(10,2)), " +
            "civilServiceFund=cast(round(sum(a.cvlserv_pay),2) as decimal(10,2)), " +
            "otherFunds=cast(round(sum(isnull(a.fund_pay_sumamt,0)-isnull(a.hifp_pay,0)-isnull(a.cvlserv_pay,0)-isnull(a.hifmi_pay,0)),2) as decimal(10,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(10,2)), " +
            "drugFee=cast(round(sum(isnull(a.charge_western_medicine,0) + isnull(a.charge_patent_medicine,0) + isnull(a.charge_herbal,0)),2) as decimal(10,2)), " +
            "drugFeeRatio=cast(round(sum(isnull(a.charge_western_medicine,0) + isnull(a.charge_patent_medicine,0) + isnull(a.charge_herbal,0))/sum(a.medfee_sumamt)*100,2) as decimal(10,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(10,2)), " +
            "matFeeRatio=cast(round(sum(a.charge_sanitary_material)/sum(a.medfee_sumamt)*100,2) as decimal(10,2)) " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time&gt;=#{beginTime} and a.setl_time&lt;=#{endTime} " +
            "and a.insuplc_admdvs != '439900' and a.insuplc_admdvs not like '43%' " +
            "</script>")
    YbStatResult selectYbStatInfoInOutProvinceCities(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType);

    @Select("<script>" +
            "select psnCount=count(distinct a.setl_id), " +
            "inDays=sum(datediff(day, zy.admiss_date, zy.dis_date)), " +
            "avgInDays=cast(round(sum(datediff(day, zy.admiss_date, zy.dis_date))/cast(count(distinct a.setl_id) as decimal(10,2)),2) as decimal(10,2)), " +
            "totalFee=cast(round(sum(a.medfee_sumamt),2) as decimal(10,2)), " +
            "fundPooling=cast(round(sum(a.hifp_pay),2) as decimal(10,2)), " +
            "bigIllFundPooling=cast(round(sum(a.hifmi_pay),2) as decimal(10,2)), " +
            "civilServiceFund=cast(round(sum(a.cvlserv_pay),2) as decimal(10,2)), " +
            "otherFunds=cast(round(sum(isnull(a.fund_pay_sumamt,0)-isnull(a.hifp_pay,0)-isnull(a.cvlserv_pay,0)-isnull(a.hifmi_pay,0)),2) as decimal(10,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(10,2)), " +
            "drugFee=cast(round(sum(isnull(a.charge_western_medicine,0) + isnull(a.charge_patent_medicine,0) + isnull(a.charge_herbal,0)),2) as decimal(10,2)), " +
            "drugFeeRatio=cast(round(sum(isnull(a.charge_western_medicine,0) + isnull(a.charge_patent_medicine,0) + isnull(a.charge_herbal,0))/sum(a.medfee_sumamt)*100,2) as decimal(10,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(10,2)), " +
            "matFeeRatio=cast(round(sum(a.charge_sanitary_material)/sum(a.medfee_sumamt)*100,2) as decimal(10,2)) " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time&gt;=#{beginTime} and a.setl_time&lt;=#{endTime} " +
            "and a.insuplc_admdvs != '439900' and a.insuplc_admdvs like '43%' and a.insuplc_admdvs not like '4301%' and a.med_type = '2102' " +
            "</script>")
    YbStatResult selectYbStatInfoInProvinceOtherCitiesSingleDisease(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType);

    @Select("<script>" +
            "select yf=convert(varchar(7), a.setl_time, 120), " +
            "totalFee=cast(round(sum(a.medfee_sumamt),2) as decimal(10,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(10,2)), " +
            "drugFee=cast(round(sum(isnull(a.charge_western_medicine,0) + isnull(a.charge_patent_medicine,0) + isnull(a.charge_herbal,0)),2) as decimal(10,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(10,2)) " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time&gt;=#{beginTime} and a.setl_time&lt;=#{endTime} " +
            "group by convert(varchar(7), a.setl_time, 120) " +
            "</script>")
    List<YbStatResult> selectYbStatRatio(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType);

    @Select("<script>" +
            "select totalFee=cast(round(sum(a.medfee_sumamt),2) as decimal(10,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(10,2)), " +
            "drugFee=cast(round(sum(isnull(a.charge_western_medicine,0) + isnull(a.charge_patent_medicine,0) + isnull(a.charge_herbal,0)),2) as decimal(10,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(10,2)) " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time&gt;=#{beginTime} and a.setl_time&lt;=#{endTime} " +
            "</script>")
    YbStatResult selectYbStatRatioTotal(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType);

    @Select("<script>" +
            "select a.* " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time&gt;=#{beginTime} and a.setl_time&lt;=#{endTime} " +
            "<if test=\"insuplcAdmdvs != null and insuplcAdmdvs.size > 0 \">" +
            " and a.insuplc_admdvs in  " +
            " <foreach collection='insuplcAdmdvs' item='item' index='index' open='(' close=')' separator=','>  " +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"insurtype != null and insurtype.size > 0 \">" +
            " and a.insutype in  " +
            " <foreach collection='insurtype' item='item' index='index' open='(' close=')' separator=','>  " +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"med_type != null and med_type.size > 0 \">" +
            " and a.med_type in  " +
            " <foreach collection='med_type' item='item' index='index' open='(' close=')' separator=','>  " +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "</script>")
    List<SiSetlinfo> selectSiSetlInfoList(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType,
                                          @Param("insuplcAdmdvs") List<String> insuplcAdmdvs, @Param("insurtype") List<String> insurtype,
                                          @Param("med_type") List<String> med_type);

    @Select("<script>" +
            "select a.* " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time&gt;=#{beginTime} and a.setl_time&lt;=#{endTime} " +
            "and a.insuplc_admdvs != '439900' and a.insuplc_admdvs like '43%' and a.insuplc_admdvs not like '4301%' and a.med_type != '2102' " +
            "</script>")
    List<SiSetlinfo> selectSiSetlInfoListInProvinceOtherCities(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType);


    @Select("<script>" +
            "select a.* " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time&gt;=#{beginTime} and a.setl_time&lt;=#{endTime} " +
            "and a.insuplc_admdvs != '439900' and a.insuplc_admdvs not like '43%' " +
            "</script>")
    List<SiSetlinfo> selectSiSetlInfoListInOutProvinceCities(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType);


    @Select("<script>" +
            "select a.* " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time&gt;=#{beginTime} and a.setl_time&lt;=#{endTime} " +
            "and a.insuplc_admdvs != '439900' and a.insuplc_admdvs like '43%' and a.insuplc_admdvs not like '4301%' and a.med_type = '2102' " +
            "</script>")
    List<SiSetlinfo> selectSiSetlInfoListInProvinceOtherCitiesSingleDisease(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType);

    @Select("<script>" +
            "select yf=convert(varchar(7), a.setl_time, 120), a.medins_type, " +
            "psnCount=count(distinct a.setl_id), " +
            "inDays=sum(datediff(day, zy.admiss_date, zy.dis_date)), " +
            "totalFee=cast(round(sum(a.medfee_sumamt),2) as decimal(10,2)), " +
            "fundPooling=cast(round(sum(a.hifp_pay),2) as decimal(10,2)), " +
            "bigIllFundPooling=cast(round(sum(a.hifmi_pay),2) as decimal(10,2)), " +
            "civilServiceFund=cast(round(sum(a.cvlserv_pay),2) as decimal(10,2)), " +
            "otherFunds=cast(round(sum(isnull(a.fund_pay_sumamt,0)-isnull(a.hifp_pay,0)-isnull(a.cvlserv_pay,0)-isnull(a.hifmi_pay,0)),2) as decimal(10,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(10,2)), " +
            "drugFee=cast(round(sum(isnull(a.charge_western_medicine,0) + isnull(a.charge_patent_medicine,0) + isnull(a.charge_herbal,0)),2) as decimal(10,2)), " +
            "matFee=cast(round(isnull(sum(a.charge_sanitary_material),0),2) as decimal(10,2)) " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time&gt;=#{beginTime} and a.setl_time&lt;=#{endTime} " +
            "group by convert(varchar(7), a.setl_time, 120), a.medins_type order by convert(varchar(7), a.setl_time, 120) " +
            "</script>")
    List<Map<String, Object>> selectBarChangeData(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType);

}