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.TreeNodesResult;
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(12,2)),2) as decimal(12,2)), " +
            "totalFee=cast(round(sum(a.medfee_sumamt),2) as decimal(12,2)), " +
            "fundPooling=cast(round(sum(a.hifp_pay),2) as decimal(12,2)), " +
            "acctPay=cast(round(sum(a.acct_pay),2) as decimal(12,2)), " +
            "bigIllFundPooling=cast(round(sum(a.hifmi_pay),2) as decimal(12,2)), " +
            "civilServiceFund=cast(round(sum(a.cvlserv_pay),2) as decimal(12,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)-isnull(a.maf_pay,0)-isnull(a.hifob_pay,0)-isnull(a.hifes_pay,0)),2) as decimal(12,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(12,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(12,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(12,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(12,2)), " +
            "matFeeRatio=cast(round(sum(a.charge_sanitary_material)/sum(a.medfee_sumamt)*100,2) as decimal(12,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(12,2)),2) as decimal(12,2)), " +
            "totalFee=cast(round(sum(a.medfee_sumamt),2) as decimal(12,2)), " +
            "acctPay=cast(round(sum(a.acct_pay),2) as decimal(12,2)), " +
            "fundPooling=cast(round(sum(a.hifp_pay),2) as decimal(12,2)), " +
            "bigIllFundPooling=cast(round(sum(a.hifmi_pay),2) as decimal(12,2)), " +
            "civilServiceFund=cast(round(sum(a.cvlserv_pay),2) as decimal(12,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)-isnull(a.maf_pay,0)-isnull(a.hifob_pay,0)-isnull(a.hifes_pay,0)),2) as decimal(12,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(12,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(12,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(12,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(12,2)), " +
            "matFeeRatio=cast(round(sum(a.charge_sanitary_material)/sum(a.medfee_sumamt)*100,2) as decimal(12,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(12,2)),2) as decimal(12,2)), " +
            "totalFee=cast(round(sum(a.medfee_sumamt),2) as decimal(12,2)), " +
            "acctPay=cast(round(sum(a.acct_pay),2) as decimal(12,2)), " +
            "fundPooling=cast(round(sum(a.hifp_pay),2) as decimal(12,2)), " +
            "bigIllFundPooling=cast(round(sum(a.hifmi_pay),2) as decimal(12,2)), " +
            "civilServiceFund=cast(round(sum(a.cvlserv_pay),2) as decimal(12,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)-isnull(a.maf_pay,0)-isnull(a.hifob_pay,0)-isnull(a.hifes_pay,0)),2) as decimal(12,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(12,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(12,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(12,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(12,2)), " +
            "matFeeRatio=cast(round(sum(a.charge_sanitary_material)/sum(a.medfee_sumamt)*100,2) as decimal(12,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(12,2)),2) as decimal(12,2)), " +
            "totalFee=cast(round(sum(a.medfee_sumamt),2) as decimal(12,2)), " +
            "acctPay=cast(round(sum(a.acct_pay),2) as decimal(12,2)), " +
            "fundPooling=cast(round(sum(a.hifp_pay),2) as decimal(12,2)), " +
            "bigIllFundPooling=cast(round(sum(a.hifmi_pay),2) as decimal(12,2)), " +
            "civilServiceFund=cast(round(sum(a.cvlserv_pay),2) as decimal(12,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)-isnull(a.maf_pay,0)-isnull(a.hifob_pay,0)-isnull(a.hifes_pay,0)),2) as decimal(12,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(12,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(12,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(12,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(12,2)), " +
            "matFeeRatio=cast(round(sum(a.charge_sanitary_material)/sum(a.medfee_sumamt)*100,2) as decimal(12,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(12,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(12,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(12,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(12,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(12,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(12,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(12,2)), " +
            "matFee=cast(round(sum(a.charge_sanitary_material),2) as decimal(12,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.*, rtrim(zy.small_dept) as deptId, rtrim(z.name) as psnCertType," +
            "rtrim(isnull(zd2.dis_diag_comment, zd1.dis_diag_comment)) as mainDiagnosis, rtrim(isnull(ss2.ssmc, ss1.ssmc)) as mainOperation " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "left join zd_unit_code z on zy.small_dept = z.code " +
            "left join zy_dis_diag_yb zd1 on (zd1.inpatient_no = a.pat_no and zd1.admiss_times = a.times and zd1.dis_diag_no = '1') " +
            "left join zy_dis_diag_yb_modify zd2 on (zd2.inpatient_no = a.pat_no and zd2.admiss_times = a.times and zd2.dis_diag_no = '1') " +
            "left join batj_ba4 ss1 on (ss1.zyh = a.pat_no and ss1.zycs = a.times and ss1.ssxh = '1') " +
            "left join batj_ba4_modify ss2 on (ss2.zyh = a.pat_no and ss2.zycs = a.times and ss2.ssxh = '1') " +
            "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.*, rtrim(zy.small_dept) as deptId, rtrim(z.name) as psnCertType, " +
            "rtrim(isnull(zd2.dis_diag_comment, zd1.dis_diag_comment)) as mainDiagnosis, rtrim(isnull(ss2.ssmc, ss1.ssmc)) as mainOperation " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "left join zd_unit_code z on zy.small_dept = z.code " +
            "left join zy_dis_diag_yb zd1 on (zd1.inpatient_no = a.pat_no and zd1.admiss_times = a.times and zd1.dis_diag_no = '1') " +
            "left join zy_dis_diag_yb_modify zd2 on (zd2.inpatient_no = a.pat_no and zd2.admiss_times = a.times and zd2.dis_diag_no = '1') " +
            "left join batj_ba4 ss1 on (ss1.zyh = a.pat_no and ss1.zycs = a.times and ss1.ssxh = '1') " +
            "left join batj_ba4_modify ss2 on (ss2.zyh = a.pat_no and ss2.zycs = a.times and ss2.ssxh = '1') " +
            "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.*, rtrim(zy.small_dept) as deptId, rtrim(z.name) as psnCertType, " +
            "rtrim(isnull(zd2.dis_diag_comment, zd1.dis_diag_comment)) as mainDiagnosis, rtrim(isnull(ss2.ssmc, ss1.ssmc)) as mainOperation " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "left join zd_unit_code z on zy.small_dept = z.code " +
            "left join zy_dis_diag_yb zd1 on (zd1.inpatient_no = a.pat_no and zd1.admiss_times = a.times and zd1.dis_diag_no = '1') " +
            "left join zy_dis_diag_yb_modify zd2 on (zd2.inpatient_no = a.pat_no and zd2.admiss_times = a.times and zd2.dis_diag_no = '1') " +
            "left join batj_ba4 ss1 on (ss1.zyh = a.pat_no and ss1.zycs = a.times and ss1.ssxh = '1') " +
            "left join batj_ba4_modify ss2 on (ss2.zyh = a.pat_no and ss2.zycs = a.times and ss2.ssxh = '1') " +
            "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.*, rtrim(zy.small_dept) as deptId, rtrim(z.name) as psnCertType, " +
            "rtrim(isnull(zd2.dis_diag_comment, zd1.dis_diag_comment)) as mainDiagnosis, rtrim(isnull(ss2.ssmc, ss1.ssmc)) as mainOperation " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "left join zd_unit_code z on zy.small_dept = z.code " +
            "left join zy_dis_diag_yb zd1 on (zd1.inpatient_no = a.pat_no and zd1.admiss_times = a.times and zd1.dis_diag_no = '1') " +
            "left join zy_dis_diag_yb_modify zd2 on (zd2.inpatient_no = a.pat_no and zd2.admiss_times = a.times and zd2.dis_diag_no = '1') " +
            "left join batj_ba4 ss1 on (ss1.zyh = a.pat_no and ss1.zycs = a.times and ss1.ssxh = '1') " +
            "left join batj_ba4_modify ss2 on (ss2.zyh = a.pat_no and ss2.zycs = a.times and ss2.ssxh = '1') " +
            "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(12,2)), " +
            "fundPooling=cast(round(sum(a.hifp_pay),2) as decimal(12,2)), " +
            "bigIllFundPooling=cast(round(sum(a.hifmi_pay),2) as decimal(12,2)), " +
            "civilServiceFund=cast(round(sum(a.cvlserv_pay),2) as decimal(12,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)-isnull(a.maf_pay,0)-isnull(a.hifob_pay,0)-isnull(a.hifes_pay,0)),2) as decimal(12,2)), " +
            "allFunds=cast(round(sum(a.fund_pay_sumamt),2) as decimal(12,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(12,2)), " +
            "matFee=cast(round(isnull(sum(a.charge_sanitary_material),0),2) as decimal(12,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);

    @Select("select count(a.setl_id) value, 0 id, '医保病人数' name " +
            "from t_si_setlinfo a " +
            "where a.revoked=0 and a.setl_type=#{setlType} and a.setl_time>=#{beginTime} and a.setl_time<=#{endTime} ")
    TreeNodesResult getTreeRootResult(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType);

    @Select("<script> " +
            "select count(t.setl_id) value, 0 pid, rtrim(t.id) as id, rtrim(isnull(d.name, '其他')) name from " +
            "(select a.setl_id, isnull(zy.small_dept, zya.small_dept) id " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "left join zy_actpatient zya on (zya.inpatient_no = a.pat_no and zya.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} " +
            " ) t " +
            "left join zd_unit_code d on d.code = t.id " +
            "group by t.id, d.name " +
            "order by count(t.setl_id) desc " +
            "</script>")
    List<TreeNodesResult> selectTreeSecondData(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType, @Param("insurType") String insurType);

    @Select("<script> " +
            "select count(t.setl_id) as value, rtrim(t.pid) as pid, rtrim(t.id) as id, rtrim(isnull(e.name, '其他')) as name from " +
            "(select a.setl_id, isnull(zy.small_dept, zya.small_dept) pid, isnull(isnull(zy.refer_physician, zya.refer_physician), 9999) id " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "left join zy_actpatient zya on (zya.inpatient_no = a.pat_no and zya.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} " +
            " ) t " +
            "left join a_employee_mi e on e.code = t.id " +
            "group by t.pid, t.id, e.name " +
            "order by count(t.setl_id) desc " +
            "</script>")
    List<TreeNodesResult> selectTreeThirdData(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType, @Param("insurType") String insurType);

    @Select("<script> " +
            "select count(t.setl_id) as value, rtrim(t.uid) as uid, rtrim(t.pid) as pid, rtrim(isnull(t.medins_type, '其他')) as name  from " +
            "(select a.setl_id, a.medins_type, isnull(isnull(zy.refer_physician, zya.refer_physician), 9999) pid, " +
            "isnull(isnull(zy.small_dept, zya.small_dept), 999) uid " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "left join zy_actpatient zya on (zya.inpatient_no = a.pat_no and zya.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} " +
            " ) t " +
            "group by t.uid, t.pid, t.medins_type " +
            "order by count(t.setl_id) desc " +
            "</script>")
    List<TreeNodesResult> selectTreeFourthData(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType, @Param("insurType") String insurType);

    @Select("<script> " +
            "select count(t.setl_id) as rs, rtrim(isnull(t.medins_type, '其他')) as insurName, rtrim(isnull(e.name, '其他')) as doctorName, rtrim(isnull(d.name, '其他')) as deptName  from " +
            "(select a.setl_id, a.medins_type, isnull(zy.small_dept, zya.small_dept) pid, isnull(isnull(zy.refer_physician, zya.refer_physician), 9999) id " +
            "from t_si_setlinfo a " +
            "left join zy_inactpatient zy on (zy.inpatient_no = a.pat_no and zy.admiss_times = a.times) " +
            "left join zy_actpatient zya on (zya.inpatient_no = a.pat_no and zya.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=\"insurType != null and insurType != '' \"> " +
            " and a.medins_type=#{insurType}  " +
            "</if> " +
            ") t " +
            "left join zd_unit_code d on d.code = t.pid " +
            "left join a_employee_mi e on e.code = t.id " +
            "where 1=1 " +
            "<if test=\"dept != null and dept != '' \"> " +
            " and rtrim(d.name)=#{dept} " +
            "</if> " +
            "<if test=\"doctor != null and doctor != '' \"> " +
            " and rtrim(e.name)=#{doctor} " +
            "</if> " +
            "group by t.pid, t.id, t.medins_type, d.name, e.name " +
            "order by d.name, e.name, count(t.setl_id) desc " +
            "</script>")
    List<Map<String, Object>> selectTreeAllData(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("setlType") String setlType,
                                              @Param("dept") String dept, @Param("doctor") String doctor, @Param("insurType") String insurType);

}