web_server/src/main/java/thyyxxk/webserver/dao/his/medicalinsurance/SiQueryDao.java

package thyyxxk.webserver.dao.his.medicalinsurance;

import org.apache.ibatis.annotations.*;
import thyyxxk.webserver.entity.dictionary.PureCodeName;
import thyyxxk.webserver.entity.medicalinsurance.query.*;
import thyyxxk.webserver.entity.medicalinsurance.setllist.BrfChrgitm;
import thyyxxk.webserver.entity.medicalinsurance.setllist.InptntSetlmtLst;
import thyyxxk.webserver.entity.medicalinsurance.setllist.OtptntSetlmtLst;
import thyyxxk.webserver.entity.yibao.ZyActpatient;

import java.util.Date;
import java.util.List;

/**
 * @description: 查询服务Mapper
 * @author: DingJie
 * @create: 2021/7/2111:17
 */
@Mapper
public interface SiQueryDao {

    /**
     * 查询门诊患者的身份证和姓名
     *
     * @param patNo 门诊号
     * @return 门诊患者的身份证和姓名
     */
    @Select("select social_no,rtrim(name) as name,times from mz_patient_mi where patient_id=#{patNo}")
    QryPsnBsInfo selectMzSocialAndName(@Param("patNo") String patNo);

    /**
     * 是否已保存过此患者的医保基本信息
     *
     * @param inpatientNo 住院号
     * @param admissTimes 住院次数
     * @param ledgerSn    账页号
     * @return 0-未保存 1-已保存
     */
    @Select("select count(1) from t_si_pat_info where pat_no=#{inpatientNo} " +
            "and times=#{admissTimes} and ledger_sn=#{ledgerSn}")
    int selectSiZyInfoCount(@Param("inpatientNo") String inpatientNo,
                            @Param("admissTimes") int admissTimes,
                            @Param("ledgerSn") int ledgerSn);

    /**
     * 新增患者的医保基本信息（个人编码）
     *
     * @param psnBaseinfo 个人基本信息
     */
    @Insert("insert into t_si_pat_info (pat_no, times, ledger_sn, psn_no, psn_cert_type, " +
            "certno, psn_name, gend, naty, brdy, age, med_type, psn_idet_type, create_datetime) " +
            "values (#{patNo},#{times},#{ledgerSn},#{psnNo},#{psnCertType},#{certno}," +
            "#{psnName},#{gend},#{naty},#{brdy},#{age},#{medType},#{psnIdetType},getdate())")
    void insertSiZyInfoPsnNo(PsnBaseInfo psnBaseinfo);

    /**
     * 更新患者的医保基本信息（个人编码）
     *
     * @param psnBaseinfo 个人基本信息
     */
    @Update("update t_si_pat_info set age=#{age}, med_type=#{medType} where pat_no=#{patNo} " +
            "and times=#{times} and ledger_sn=#{ledgerSn}")
    void updateSiZyInfoPsnNo(PsnBaseInfo psnBaseinfo);

    @Update("update zy_actpatient set med_type=#{medType} where inpatient_no=#{patNo}")
    void updateZyActpatient(PsnBaseInfo psnBaseInfo);

    @Update("update t_si_pat_info set psn_idet_type=#{psnIdetType} where psn_no=#{psnNo} ")
    void updatePsnIdetType(@Param("psnIdetType") String psnIdetType,
                           @Param("psnNo") String psnNo);

    /**
     * 获取患者的医保基本信息
     *
     * @param patNo    住院号
     * @param times    住院次数
     * @param ledgerSn 账页号
     * @return 医保基本信息
     */
    @Select("select * from t_si_pat_info where pat_no=#{patNo} and times=#{times} and ledger_sn=#{ledgerSn}")
    SiPatInfo selectSiPatInfoForZy(@Param("patNo") String patNo,
                                   @Param("times") int times,
                                   @Param("ledgerSn") int ledgerSn);

    /**
     * 获取患者的医保个人编号
     *
     * @param patNo 住院号/门诊ID
     * @return 患者的医保个人编号
     */
    @Select("select top 1 psn_no from t_si_pat_info where pat_no=#{patNo}")
    String selectPsnNo(@Param("patNo") String patNo);

    @Select("select * from t_si_pat_info where mdtrt_id=#{mdtrtId}")
    SiPatInfo selectSiPatInfoByMdtrtId(@Param("mdtrtId") String mdtrtId);

    /**
     * 获取患者的医保个人编号
     *
     * @param patNo   住院号
     * @param medType 医疗类别
     * @return 患者的医保个人编号
     */
    @Select("select top 1 insuplc_admdvs as code, psn_no as name from t_si_pat_info where pat_no=#{patNo} and med_type=#{medType} " +
            "and insuplc_admdvs is not null ")
    PureCodeName selectPsnNoByPatNoAndMedType(@Param("patNo") String patNo,
                                              @Param("medType") String medType);

    /**
     * 获取患者的医保基本信息
     *
     * @param patNo 门诊号
     * @param times 门诊次数
     * @return 医保基本信息
     */
    @Select("select * from t_si_pat_info where pat_no=#{patNo} and times=#{times}")
    SiPatInfo selectSiPatInfoForMz(@Param("patNo") String patNo,
                                   @Param("times") int times);

    /**
     * 取消入院登记后清空就诊id
     *
     * @param inpatientNo 住院号
     * @param admissTimes 住院次数
     * @param ledgerSn    账页号
     */
    @Update("update t_si_pat_info set mdtrt_id=#{nullCtnt}, adm_reg_msgid=#{nullCtnt}, setl_id=#{nullCtnt} " +
            "where pat_no=#{inpatientNo} and times=#{admissTimes} and ledger_sn=#{ledgerSn}")
    void clearMdtrtIdForZy(@Param("inpatientNo") String inpatientNo,
                           @Param("admissTimes") int admissTimes,
                           @Param("ledgerSn") int ledgerSn,
                           @Param("nullCtnt") String nullCtnt);

    @Select("select type=#{type},pat_no,times,ledger_sn,psn_name,setl_id,gend,setl_time,medfee_sumamt,psn_no " +
            "from t_si_setlinfo where pat_no=#{patNo} and times=#{times} and revoked=0")
    List<SetlIndex> selectSetlIndex(QrySetlListPrm q);

    @Select("select top 1 * from t_si_pat_info where pat_no=#{patNo} and times=#{times} order by ledger_sn desc")
    SiPatInfo selectSiPatInfo(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select top 1 insuplc_admdvs from t_si_pat_info where pat_no=#{patNo} and times=#{times} " +
            "and insuplc_admdvs is not null order by ledger_sn desc")
    String selectAdmdvs(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select top 1 insuplc_admdvs from t_si_pat_info where pat_no=#{patNo} and insuplc_admdvs is not null order by times desc")
    String selectAdmdvsByPatNo(@Param("patNo") String patNo);

    @Select("select top 1 insuplc_admdvs from t_si_pat_info where psn_no=#{psnNo} and insuplc_admdvs is not null order by times desc")
    String selectAdmdvsByPsnNo(@Param("psnNo") String psnNo);

    @Select("select top 1 insuplc_admdvs from t_si_pat_info where pat_no=#{patNo} and psn_no=#{psnNo} " +
            "and insuplc_admdvs is not null order by times desc")
    String selectAdmdvsByPatNoAndPsnNo(@Param("patNo") String patNo, @Param("psnNo") String psnNo);

    @Select("select a.mdtrt_id,a.psn_name,a.gend,a.age,a.psn_no,b.emp_name," +
            "a.psn_type,a.cvlserv_flag,a.pat_no,a.setl_id,b.insuplc_admdvs, " +
            "tel=(select rtrim(home_tel) from a_patient_mi where a_patient_mi.inpatient_no=a.pat_no), " +
            "admDeptName=(select rtrim(name) from zd_unit_code where code=isnull(c.zk_ward,c.small_dept)), " +
            "c.bed_no as admBed,c.admiss_date as begntime, c.dis_date as endtime, " +
            "a.certno,a.mdtrt_cert_type, b.psn_idet_type, " +
            "dscgMaindiagName=(select rtrim(dis_diag_comment) from zy_dis_diag_yb where zy_dis_diag_yb.inpatient_no=a.pat_no " +
            "and zy_dis_diag_yb.admiss_times=a.times and zy_dis_diag_yb.dis_diag_no=1), " +
            "a.med_type,a.setl_time,a.hifes_pay, " +
            "chfpdrName=(select rtrim(name) from a_employee_mi where code=c.refer_physician), " +
            "staffName=(select rtrim(name) from a_employee_mi where a_employee_mi.code=a.staff_id), " +
            "a.balc,a.medfee_sumamt,a.hifp_pay,a.acct_pay,a.cvlserv_pay,a.psn_cash_pay, " +
            "a.hifob_pay,a.oth_pay,a.hifmi_pay,a.maf_pay " +
            "from t_si_setlinfo a, t_si_pat_info b, ${table} c where a.setl_id=#{setlId} " +
            "and a.pat_no=b.pat_no and a.times=b.times and a.ledger_sn=b.ledger_sn and a.pat_no=c.inpatient_no " +
            "and a.times=c.admiss_times")
    InptntSetlmtLst selectZySetlifo(@Param("setlId") String setlId, @Param("table") String table);

    @Select("select account_date from zy_ledger_file where inpatient_no=#{patNo} and admiss_times=#{times} and ledger_sn=#{sn}")
    Date selectAccountDate(@Param("patNo") String patNo,
                           @Param("times") int times,
                           @Param("sn") int sn);

    @Select("select a.mdtrt_id,a.psn_name,a.gend,a.age,a.cvlserv_flag,a.psn_type, " +
            "b.emp_name,a.certno,a.med_type,b.dise_code,a.medfee_sumamt,a.psn_no, " +
            "a.hifp_pay,a.acct_pay,a.cvlserv_pay,a.psn_cash_pay,b.insuplc_admdvs, " +
            "a.hifmi_pay,a.maf_pay,a.hifob_pay,a.oth_pay,a.staff_id,a.hifes_pay, " +
            "staffName=(select rtrim(name) from a_employee_mi where code=staff_id) " +
            "from t_si_setlinfo a, t_si_pat_info b where a.pat_no=#{patNo} " +
            "and a.times=#{times} and isnull(a.revoked,0)!=1 " +
            "and a.pat_no=b.pat_no and a.times=b.times")
    OtptntSetlmtLst selectMzSetlifo(@Param("patNo") String patNo,
                                    @Param("times") int times);

    @Select("select charge_fee,med_chrgitm_type,chrgitm_lv from t_mt_receipt where patient_id=#{patNo} " +
            "and times=#{times} and yb_trans_flag=1")
    List<BrfChrgitm> selectMzBrfChrgitms(@Param("patNo") String patNo,
                                         @Param("times") int times);

    @Select("select name from t_region where code=#{code}")
    String selectRegionName(@Param("code") String code);

    @Select("select insuplc_admdvs from t_si_pat_info where mdtrt_id=#{mdtrtId}")
    String selectInsuplcAdmdvsByMdtrtId(@Param("mdtrtId") String mdtrtId);

    @Select("select times from t_si_setlinfo where pat_no=#{patNo} and revoked=0")
    List<Integer> fetchSiTimesList(@Param("patNo") String patNo);

    @Select("select * from t_si_setl_fee_detl where mdtrt_id=#{mdtrtId}")
    List<SiSetlFeeDetl> selectAllSetlFeeDtle(@Param("mdtrtId") String mdtrtId);

    @Select("select a.feedetl_sn,a.fee_ocur_time,a.cnt,cast(a.pric as decimal(16,2)) as pric," +
            "cast(a.det_item_fee_sumamt as decimal(16,2)) as det_item_fee_sumamt, " +
            "a.selfpay_prop,cast(a.fulamt_ownpay_amt as decimal(16,2)) as fulamt_ownpay_amt," +
            "cast(a.overlmt_amt as decimal(16,2)) as overlmt_amt,cast(a.preselfpay_amt as decimal(16,2)) as preselfpay_amt, " +
            "cast(a.inscp_scp_amt as decimal(16,2)) as inscp_scp_amt,a.chrgitm_lv,a.medins_list_name,a.med_list_codg, " +
            "a.med_chrgitm_type,a.bilg_dept_codg,a.bilg_dept_name,a.bilg_dr_codg, " +
            "a.bilg_dr_name,a.selfpay_prop_percent,a.chrgitm_lv_name, " +
            "rtrim(b.charge_code_mx) as medins_list_codg from t_si_setl_fee_detl a, " +
            "zy_detail_charge b where mdtrt_id=#{mdtrtId} and b.inpatient_no=#{patNo}  " +
            "and b.admiss_times=#{times} and b.ledger_sn=#{sn} and b.detail_sn=a.feedetl_sn")
    List<SiSetlFeeDetl> selectFeeDetlList(@Param("mdtrtId") String mdtrtId,
                                          @Param("patNo") String patNo,
                                          @Param("times") int times,
                                          @Param("sn") int sn);

    @Select("select a.feedetl_sn,b.charge_date as fee_ocur_time,a.cnt," +
            "cast(a.pric as decimal(16,2)) as pric, cast(a.det_item_fee_sumamt as decimal(16,2)) as det_item_fee_sumamt, " +
            "a.selfpay_prop,cast(a.fulamt_ownpay_amt as decimal(16,2)) as fulamt_ownpay_amt, " +
            "cast(a.overlmt_amt as decimal(16,2)) as overlmt_amt,cast(a.preselfpay_amt as decimal(16,2)) as preselfpay_amt, " +
            "cast(a.inscp_scp_amt as decimal(16,2)) as inscp_scp_amt,a.chrgitm_lv, " +
            "med_list_codg=case when b.serial in ('','00') then (select max(national_code) from zd_charge_item where code=b.charge_code_mx) " +
            "else (select max(national_code) from yp_zd_dict where code=b.charge_code_mx) end, " +
            "medins_list_name=case when b.serial in ('','00') then (select max(name) from zd_charge_item where code=b.charge_code_mx) " +
            "else (select max(name) from yp_zd_dict where code=b.charge_code_mx) end, " +
            "a.med_chrgitm_type,rtrim(b.ward_code) as bilg_dept_codg, " +
            "bilg_dept_name=(select rtrim(name) from zd_unit_code where code=ward_code), " +
            "bilg_dr_codg=isnull(isnull(b.doctor_code,b.refer_physician), b.op_id_code), " +
            "bilg_dr_name=isnull(isnull((select rtrim(name) from a_employee_mi where code=doctor_code), " +
            "(select rtrim(name) from a_employee_mi where code=refer_physician)), " +
            "(select rtrim(name) from a_employee_mi where code=op_id_code)), " +
            "rtrim(b.charge_code_mx) as medins_list_codg from t_si_charge_temp a, " +
            "zy_detail_charge b where a.pat_no=#{patNo} and a.times=#{times} and a.ledger_sn=#{sn} " +
            "and a.pat_no=b.inpatient_no and a.times=b.admiss_times and a.ledger_sn=b.ledger_sn " +
            "and a.feedetl_sn=b.detail_sn order by a.feedetl_sn")
    List<SiSetlFeeDetl> selectTempCharge(@Param("patNo") String patNo,
                                         @Param("times") int times,
                                         @Param("sn") int sn);

    @Insert("<script>" +
            "insert into t_si_setl_fee_detl (mdtrt_id, setl_id, feedetl_sn, fee_ocur_time, cnt, pric, " +
            "det_item_fee_sumamt, selfpay_prop, fulamt_ownpay_amt, overlmt_amt, preselfpay_amt, inscp_scp_amt, " +
            "chrgitm_lv, hilist_code, hilist_name, list_type, med_list_codg, medins_list_codg, medins_list_name, " +
            "med_chrgitm_type, bilg_dept_codg, bilg_dept_name, bilg_dr_codg, bilg_dr_name, lmt_used_flag, " +
            "hosp_prep_flag, hosp_appr_flag, tcmdrug_used_way, prodplac_type, bas_medn_flag, hi_nego_drug_flag, " +
            "chld_medc_flag, etip_flag, etip_hosp_code, dscg_tkdrug_flag, list_sp_item_flag, matn_fee_flag, " +
            "drt_reim_flag, memo, opter_id, opter_name, opt_time, selfpay_prop_percent, chrgitm_lv_name) " +
            "values " +
            "<foreach collection='list' item='fee' open='' separator=',' close=''>" +
            "(#{fee.mdtrtId}, #{fee.setlId}, #{fee.feedetlSn}, #{fee.feeOcurTime}, #{fee.cnt}, #{fee.pric}, " +
            "#{fee.detItemFeeSumamt}, #{fee.selfpayProp}, #{fee.fulamtOwnpayAmt}, #{fee.overlmtAmt}, " +
            "#{fee.preselfpayAmt}, #{fee.inscpScpAmt}, #{fee.chrgitmLv}, #{fee.hilistCode}, #{fee.hilistName}, " +
            "#{fee.listType}, #{fee.medListCodg}, #{fee.medinsListCodg}, #{fee.medinsListName}, " +
            "#{fee.medChrgitmType}, #{fee.bilgDeptCodg}, #{fee.bilgDeptName}, #{fee.bilgDrCodg}, #{fee.bilgDrName}, " +
            "#{fee.lmtUsedFlag}, #{fee.hospPrepFlag}, #{fee.hospApprFlag}, #{fee.tcmdrugUsedWay}, #{fee.prodplacType}, " +
            "#{fee.basMednFlag}, #{fee.hiNegoDrugFlag}, #{fee.chldMedcFlag}, #{fee.etipFlag}, #{fee.etipHospCode}, " +
            "#{fee.dscgTkdrugFlag}, #{fee.listSpItemFlag}, #{fee.matnFeeFlag}, #{fee.drtReimFlag}, #{fee.memo}, " +
            "#{fee.opterId}, #{fee.opterName}, #{fee.optTime}, #{fee.selfpayPropPercent}, #{fee.chrgitmLvName})" +
            "</foreach>" +
            "</script>")
    void insertSetlFeeDetlBatch(List<SiSetlFeeDetl> list);

    @Select("select * from t_si_mz_matn_dise")
    List<SiMzMatnDise> selectAllMzMatnDises();

    @Update("update t_si_pat_info set mdtrt_id=#{mdtrtId},insutype=#{insutype} where pat_no=#{patNo} " +
            "and times=#{times} and ledger_sn=#{sn}")
    void updateInhospInfo(@Param("patNo") String patNo,
                          @Param("times") int times,
                          @Param("sn") int sn,
                          @Param("mdtrtId") String mdtrtId,
                          @Param("insutype") String insutype);

    @Select("select pat_no,times,ledger_sn,insuplc_admdvs,medfee_sumamt,insutype,hifmi_pay,hifp_pay,cvlserv_pay,setl_list_id,psn_name, " +
            "psn_cash_pay,setl_time, " +
            "fundPaySumamt=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn),a.insutype, " +
            "hospPay=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn and (b.fund_pay_type='999996' or b.setl_proc_info='999996')) " +
            "from t_si_setlinfo a where revoked=0 and setl_type=#{setlType} and insutype like #{insutype} and setl_time>=#{begntime} " +
            "and setl_time<=#{endtime} and insuplc_admdvs='439900' ")
    List<BaseSetlStatistics> selectBaseSetlStatisticsInProvinceLevel(@Param("begntime") String begntime,
                                                                     @Param("endtime") String endtime,
                                                                     @Param("insutype") String insutype,
                                                                     @Param("setlType") String setlType);

    @Select("select pat_no,times,ledger_sn,insuplc_admdvs,medfee_sumamt,insutype,hifmi_pay,hifp_pay,cvlserv_pay,setl_list_id,psn_name, " +
            "psn_cash_pay,setl_time, " +
            "fundPaySumamt=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn),a.insutype, " +
            "hospPay=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn and (b.fund_pay_type='999996' or b.setl_proc_info='999996')) " +
            "from t_si_setlinfo a where revoked=0 and setl_type=#{setlType} and insutype like #{insutype} and setl_time>=#{begntime} " +
            "and setl_time<=#{endtime} and insuplc_admdvs not in ('439900','430121','430181') and insuplc_admdvs like '4301%' ")
    List<BaseSetlStatistics> selectBaseSetlStatisticsInChangshaCity(@Param("begntime") String begntime,
                                                                    @Param("endtime") String endtime,
                                                                    @Param("insutype") String insutype,
                                                                    @Param("setlType") String setlType);

    @Select("select pat_no,times,ledger_sn,insuplc_admdvs,medfee_sumamt,insutype,hifmi_pay,hifp_pay,cvlserv_pay,setl_list_id,psn_name, " +
            "psn_cash_pay,setl_time, " +
            "fundPaySumamt=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn),a.insutype, " +
            "hospPay=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn and (b.fund_pay_type='999996' or b.setl_proc_info='999996')) " +
            "from t_si_setlinfo a where revoked=0 and setl_type=#{setlType} and insutype like #{insutype} and setl_time>=#{begntime} " +
            "and setl_time<=#{endtime} and insuplc_admdvs='430121' ")
    List<BaseSetlStatistics> selectBaseSetlStatisticsInChangshaCounty(@Param("begntime") String begntime,
                                                                    @Param("endtime") String endtime,
                                                                    @Param("insutype") String insutype,
                                                                    @Param("setlType") String setlType);

    @Select("select pat_no,times,ledger_sn,insuplc_admdvs,medfee_sumamt,insutype,hifmi_pay,hifp_pay,cvlserv_pay,setl_list_id,psn_name, " +
            "psn_cash_pay,setl_time, " +
            "fundPaySumamt=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn),a.insutype, " +
            "hospPay=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn and (b.fund_pay_type='999996' or b.setl_proc_info='999996')) " +
            "from t_si_setlinfo a where revoked=0 and setl_type=#{setlType} and insutype like #{insutype} and setl_time>=#{begntime} " +
            "and setl_time<=#{endtime} and insuplc_admdvs='430181' ")
    List<BaseSetlStatistics> selectBaseSetlStatisticsInLiuYangCity(@Param("begntime") String begntime,
                                                                      @Param("endtime") String endtime,
                                                                      @Param("insutype") String insutype,
                                                                      @Param("setlType") String setlType);

    @Select("select pat_no,times,ledger_sn,insuplc_admdvs,medfee_sumamt,insutype,hifmi_pay,hifp_pay,cvlserv_pay,setl_list_id,psn_name, " +
            "psn_cash_pay,setl_time, " +
            "fundPaySumamt=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn),a.insutype, " +
            "hospPay=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn and (b.fund_pay_type='999996' or b.setl_proc_info='999996')) " +
            "from t_si_setlinfo a where revoked=0 and setl_type=#{setlType} and insutype like #{insutype} and setl_time>=#{begntime} " +
            "and setl_time<=#{endtime} and insuplc_admdvs!='439900' and insuplc_admdvs like '43%' and insuplc_admdvs not like '4301%' ")
    List<BaseSetlStatistics> selectBaseSetlStatisticsInProvinceOtherCities(@Param("begntime") String begntime,
                                                                           @Param("endtime") String endtime,
                                                                           @Param("insutype") String insutype,
                                                                           @Param("setlType") String setlType);

    @Select("select pat_no,times,ledger_sn,insuplc_admdvs,medfee_sumamt,insutype,hifmi_pay,hifp_pay,cvlserv_pay,setl_list_id,psn_name, " +
            "psn_cash_pay,setl_time, " +
            "fundPaySumamt=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn),a.insutype, " +
            "hospPay=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn and (b.fund_pay_type='999996' or b.setl_proc_info='999996')) " +
            "from t_si_setlinfo a where revoked=0 and setl_type=#{setlType} and insutype like #{insutype} and setl_time>=#{begntime} " +
            "and setl_time<=#{endtime} and insuplc_admdvs!='439900' and insuplc_admdvs not like '43%' ")
    List<BaseSetlStatistics> selectBaseSetlStatisticsOutProvince(@Param("begntime") String begntime,
                                                                 @Param("endtime") String endtime,
                                                                 @Param("insutype") String insutype,
                                                                 @Param("setlType") String setlType);

    @Select("select pat_no,times,ledger_sn,insuplc_admdvs,medfee_sumamt,insutype,hifmi_pay,hifp_pay,cvlserv_pay,setl_list_id,psn_name, " +
            "psn_cash_pay,setl_time, " +
            "fundPaySumamt=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn),a.insutype, " +
            "hospPay=(select sum(b.fund_payamt) from t_si_setldetail b where b.pat_no=a.pat_no " +
            "and b.times=a.times and b.ledger_sn=a.ledger_sn and (b.fund_pay_type='999996' or b.setl_proc_info='999996')) " +
            "from t_si_setlinfo a where revoked=0 and setl_type=#{setlType} and insutype like #{insutype} and setl_time>=#{begntime} " +
            "and setl_time<=#{endtime} ")
    List<BaseSetlStatistics> selectBaseSetlStatisticsInAllPlaces(@Param("begntime") String begntime,
                                                                 @Param("endtime") String endtime,
                                                                 @Param("insutype") String insutype,
                                                                 @Param("setlType") String setlType);

    @Select("select code=(select b.name from t_region b where b.code=a.parent_code),a.name from t_region a where code=#{code}")
    PureCodeName selectAdmdvsNameAndParentName(@Param("code") String code);

    @Select("select name,code='' from t_si_admdvs where code=#{code}")
    PureCodeName selectAdmdvsName(@Param("code") String code);


    @Select("<script>" +
            "select                     rtrim(a.inpatient_no)     inpatient_no," +
            "                           a.admiss_times," +
            "                           rtrim(name)               name," +
            "    refer_physician_name = (select rtrim(name) from a_employee_mi where code = refer_physician)," +
            "    small_dept_name      = (select rtrim(name) from zd_unit_code where code = small_dept)," +
            "    responce_type_name   = (select rtrim(name) from zy_zd_responce_type where code = responce_type)," +
            "                           total_charge," +
            "                           sex," +
            "                           rtrim(c.dis_diag)         dis_diag," +
            "                           rtrim(c.dis_diag_comment) dis_diag_comment," +
            "                           certno," +
            "                           insuplc_admdvs, " +
            "    insuplc_admdvs_name  = isnull((select name from t_region where code = insuplc_admdvs), " +
            "                                  (select name from t_si_admdvs where code = insuplc_admdvs))" +
            "from zy_actpatient a " +
            "         left join t_si_pat_info b on (a.inpatient_no = b.pat_no and a.admiss_times = b.times) " +
            "         left join zy_dis_diag_yb c on (a.inpatient_no = c.inpatient_no and a.admiss_times = c.admiss_times and c.dis_diag_no = 1) " +
            "where  " +
            "setl_id is null and b.mdtrt_id is not null " +
            "<if test=\"startTime !=null and startTime != '' \">" +
            "  and admiss_date &gt;= #{startTime} and admiss_date &lt;= #{endTime}  " +
            "</if>" +
            "<if test=\"patNo != null and patNo != '' \">" +
            "  and a.inpatient_no = #{patNo} /* 住院号 */ " +
            "</if>" +
            "<if test=\"name != null and name != ''\">" +
            "  and name like #{name} /*姓名*/ " +
            "</if>" +
            "<if test=\"certno !=null and certno != '' \">" +
            "  and certno = '' /*身份证号码*/" +
            "</if>" +
            "</script>")
    List<ZyActpatient> zaiYuanHuanZheXinXi(@Param("patNo") String patNo,
                                           @Param("startTime") String startTime,
                                           @Param("endTime") String endTime,
                                           @Param("name") String name,
                                           @Param("certno") String certno);

}