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

package thyyxxk.webserver.dao.his.medicalinsurance;

import org.apache.ibatis.annotations.*;
import thyyxxk.webserver.entity.medicalinsurance.inpatient.ZyPatientInfo;
import thyyxxk.webserver.entity.medicalinsurance.inpatient.*;

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

/**
 * @description: 新医保住院mapper
 * @author: DingJie
 * @create: 2021-05-28 14:50:31
 **/
@Mapper
public interface SiZyDao {

    /**
     * 获取员工姓名
     *
     * @param staffId 员工5位长度唯一编码
     * @return 员工姓名
     */
    @Select("select rtrim(name) from a_employee_mi where code=#{staffId}")
    String selectStaffName(@Param("staffId") String staffId);

    /**
     * 获取入院医保诊断
     *
     * @param patNo 住院号
     * @param times 住院次数
     * @return 入院医保诊断
     */
    @Select("select psnNo=(select top 1 psn_no from t_si_pat_info where pat_no=#{patNo} and times=#{times}), " +
            "psnName=(select top 1 psn_name from t_si_pat_info where pat_no=#{patNo} and times=#{times}), " +
            "diag_type as diagType, " +
            "maindiagFlag=case when diag_no=1 then '1' else '0' end, " +
            "diag_no as diagSrtNo, " +
            "icd_code as diagCode, " +
            "icd_text as diagName, " +
            "diagDept=(select rtrim(name) from zd_unit_code where code=( " +
            "select small_dept from zy_actpatient where zy_actpatient.inpatient_no=zy_in_diag_yb.inpatient_no)), " +
            "op_date as diagTime " +
            "from zy_in_diag_yb where inpatient_no=#{patNo} and admiss_times=#{times}")
    List<AdmDiseinfo> selectAdmDiags(@Param("patNo") String patNo, @Param("times") int times);

    /**
     * 获取入院基本信息
     *
     * @param inpatientNo 住院号
     * @param ledgerSn    账页号
     * @return 入院基本信息
     */
    @Select("select b.psn_no,b.insutype,b.psn_name,isnull(a.yb_register_date,a.admiss_date) as begntime, " +
            "mdtrtCertType='02',b.certno as mdtrtCertNo,certType='1',b.certno,b.med_type, " +
            "rtrim(a.inpatient_no) as iptNo,a.dise_code as diseCodg,a.dise_name, " +
            "atddrNo=(select rtrim(isnull(yb_code,code)) from a_employee_mi where code=isnull(a.refer_physician,a.admiss_physician)), " +
            "chfpdrName=(select rtrim(name) from a_employee_mi where code=isnull(a.refer_physician,a.admiss_physician)), " +
            "rtrim(a.small_dept) as admDeptCodg, " +
            "admDeptName=(select rtrim(name) from zd_unit_code where code=a.small_dept), " +
            "rtrim(a.bed_no) as admBed,a.exp_content " +
            "from zy_actpatient a, t_si_pat_info b " +
            "where a.inpatient_no=#{inpatientNo} and b.pat_no=a.inpatient_no " +
            "and b.times=a.admiss_times and b.ledger_sn=#{ledgerSn}")
    AdmMdtrtinfo selectAdmMdtrtinfo(@Param("inpatientNo") String inpatientNo,
                                    @Param("ledgerSn") int ledgerSn);

    @Select("select endtime from t_si_setlinfo where pat_no=#{patNo} and times=#{times} and ledger_sn=1 and revoked=0")
    Date selectSetlEndTime(@Param("patNo") String patNo,
                           @Param("times") int times);

    /**
     * 获取当前患者最大账页号
     *
     * @param inpatientNo 住院号
     * @param admissTimes 住院次数
     * @return 账页号
     */
    @Select("select isnull(max(ledger_sn), isnull(min(ledger_sn), 1)) from zy_ledger_file where " +
            "inpatient_no=#{inpatientNo} and admiss_times=#{admissTimes}")
    Integer selectMaxLedgerSn(@Param("inpatientNo") String inpatientNo,
                              @Param("admissTimes") int admissTimes);

    /**
     * 撤销医保费用上传后，更改上传标志为0
     *
     * @param inpatientNo 住院号
     * @param admissTimes 住院次数
     * @param ledgerSn    账页号
     */
    @Update("update zy_detail_charge set trans_flag_yb=0 where inpatient_no=#{inpatientNo} and " +
            "admiss_times=#{admissTimes} and ledger_sn=#{ledgerSn} and isnull(trans_flag_yb,0)!=2")
    void revokeAllUploadFee(@Param("inpatientNo") String inpatientNo,
                            @Param("admissTimes") int admissTimes,
                            @Param("ledgerSn") int ledgerSn);

    @Update("update zy_ledger_file set charge_yb=0,fund_pay_sumamt=0 where inpatient_no=#{inpatientNo} " +
            "and admiss_times=#{admissTimes} and ledger_sn=#{ledgerSn}")
    void resetFundpayAmt(@Param("inpatientNo") String inpatientNo,
                         @Param("admissTimes") int admissTimes,
                         @Param("ledgerSn") int ledgerSn);

    @Update("<script>" +
            "update zy_detail_charge set trans_flag_yb=0 where inpatient_no=#{inpatientNo} and " +
            "admiss_times=#{admissTimes} and ledger_sn=#{ledgerSn} and isnull(trans_flag_yb,0)!=2 " +
            "and detail_sn in " +
            "<foreach collection='list' item='sn' separator=',' open='(' close=')'>" +
            "#{sn}</foreach>" +
            "</script>")
    void revokePartUploadFee(@Param("inpatientNo") String inpatientNo,
                             @Param("admissTimes") int admissTimes,
                             @Param("ledgerSn") int ledgerSn,
                             @Param("list") List<Integer> list);

    /**
     * 更新患者医保报销金额
     *
     * @param fundPay     医保报销金额
     * @param inpatientNo 住院号
     * @param admissTimes 住院次数
     * @param ledgerSn    账页号
     */
    @Update("update zy_ledger_file set charge_yb=#{fundPay},fund_pay_sumamt=#{fundPay} " +
            "where inpatient_no=#{inpatientNo} and admiss_times=#{admissTimes} and ledger_sn=#{ledgerSn}")
    void updateFundPay(@Param("fundPay") String fundPay,
                       @Param("inpatientNo") String inpatientNo,
                       @Param("admissTimes") int admissTimes,
                       @Param("ledgerSn") int ledgerSn);

    /**
     * 获取预结算参数
     *
     * @param patNo    住院号
     * @param times    住院次数
     * @param ledgerSn 账页号
     * @return 预结算参数
     */
    @Select("select psn_no,certno as mdtrtCertNo,certno,mdtrtCertType='02', " +
            "psn_cert_type,psn_type,psn_name,mdtrt_id,insutype,insuplc_admdvs, " +
            "medfeeSumamt=(select sum(charge_fee) from zy_detail_charge b where b.inpatient_no=a.pat_no " +
            "and b.admiss_times=a.times and b.ledger_sn=a.ledger_sn and isnull(b.infant_flag,0)=0 " +
            "and trans_flag_yb!=2 and charge_date<=#{endtime})," +
            "expContent=(select d.exp_content from zy_actpatient d where d.inpatient_no=#{patNo}) " +
            "from t_si_pat_info a where pat_no=#{patNo} and times=#{times} and ledger_sn=#{ledgerSn}")
    PreSetlmt selectPreSetlmt(@Param("patNo") String patNo,
                              @Param("times") int times,
                              @Param("ledgerSn") int ledgerSn,
                              @Param("endtime") String endtime);

    @Select("select start_time from yz_act_order with(nolock) where inpatient_no=#{patNo} and admiss_times=#{times} " +
            "and status_flag > '1' and isnull(group_no, '00')='00' and order_code in ('06026','06053','05973') and status_flag!='6'  ")
    Date selectActOrderDisDate(@Param("patNo") String patNo, @Param("times") Integer times);

    @Select("select psn_no,certno as mdtrtCertNo,certno,mdtrtCertType='02', " +
            "psn_cert_type,psn_type,mdtrt_id,insutype,insuplc_admdvs, " +
            "medfeeSumamt=(select sum(charge_fee) from zy_detail_charge b where b.inpatient_no=a.pat_no " +
            "and b.admiss_times=a.times and b.ledger_sn=a.ledger_sn and isnull(b.infant_flag,0)=0 " +
            "and trans_flag_yb!=2 and charge_date>=#{begntime} and charge_date<=#{endtime}) " +
            "from t_si_pat_info a where pat_no=#{patNo} and times=#{times} and ledger_sn=#{ledgerSn}")
    PreSetlmt selectPreSetlmtForMidSetl(@Param("patNo") String patNo,
                                        @Param("times") int times,
                                        @Param("ledgerSn") int ledgerSn,
                                        @Param("begntime") Date begntime,
                                        @Param("endtime") Date endtime);

    @Select("select a.mdtrt_id,a.psn_no,a.insutype,b.start_time as endtime, " +
            "dscgDeptCodg=(select si_caty from zd_unit_code where code=c.zk_ward)," +
            "dscgDeptName=(select rtrim(name) from zd_unit_code where code=c.zk_ward) " +
            "from t_si_pat_info a, yz_act_order b,zy_actpatient c " +
            "where a.pat_no=#{patNo} and a.times=#{times} and a.ledger_sn=#{ledgerSn} " +
            "and a.pat_no=b.inpatient_no and a.times=b.admiss_times and a.pat_no=c.inpatient_no " +
            "and b.status_flag>'1' and isnull(b.group_no, '00')='00' and b.order_code in ('06026','06053','05973')")
    Dscginfo selectDscginfo(@Param("patNo") String patNo,
                            @Param("times") int times,
                            @Param("ledgerSn") int ledgerSn);

    @Select("select a.mdtrt_id,a.psn_no,a.insutype, " +
            "dscgDeptCodg=(select si_caty from zd_unit_code where code=b.zk_ward), " +
            "dscgDeptName=(select rtrim(name) from zd_unit_code where code=b.zk_ward) " +
            "from t_si_pat_info a, zy_actpatient b " +
            "where a.pat_no=#{patNo} and a.times=#{times} and a.ledger_sn=#{ledgerSn} " +
            "and a.pat_no=b.inpatient_no and a.times=b.admiss_times ")
    Dscginfo selectDscginfoForMidSetl(@Param("patNo") String patNo,
                                      @Param("times") int times,
                                      @Param("ledgerSn") int ledgerSn);

    @Select("select insuplc_admdvs from t_si_pat_info where pat_no=#{patNo} and times=#{times} and ledger_sn=#{ledgerSn}")
    String selectAdmdvs(@Param("patNo") String patNo,
                        @Param("times") int times,
                        @Param("ledgerSn") int ledgerSn);

    /**
     * 获取患者出院医保诊断
     *
     * @param patNo    住院号
     * @param times    住院次数
     * @param ledgerSn 账页号
     * @return 患者出院医保诊断
     */
    @Select("select b.mdtrt_id,b.psn_no, " +
            "a.si_diag_type as diagType, " +
            "maindiagFlag=case when a.dis_diag_no=1 then 1 else 0 end, " +
            "a.dis_diag_no as diagSrtNo, " +
            "rtrim(a.dis_diag) as diagCode, " +
            "rtrim(a.dis_diag_comment) as diagName, " +
            "diagDept=(select rtrim(dept_code) from a_employee_mi where code=a.op_id_code), " +
            "diseDorNo=(select rtrim(isnull(yb_code,code)) from a_employee_mi where code=op_id_code), " +
            "diseDorName=(select rtrim(name) from a_employee_mi where code=a.op_id_code), " +
            "a.op_diag_date as diagTime " +
            "from zy_dis_diag_yb a, t_si_pat_info b " +
            "where b.pat_no=#{patNo} and b.times=#{times} and b.ledger_sn=#{ledgerSn} " +
            "and a.inpatient_no=b.pat_no and a.admiss_times=b.times")
    List<SetlDiseinfo> selectSetlDises(@Param("patNo") String patNo,
                                       @Param("times") int times,
                                       @Param("ledgerSn") int ledgerSn);

    /**
     * 入院登记成功后更新患者医保信息
     *
     * @param p 患者基本信息
     */
    @Update("update t_si_pat_info set insuplc_admdvs=#{insuplcAdmdvs},psn_type=#{psnType},emp_name=#{empName},balc=#{balc}," +
            "mdtrt_id=#{mdtrtId},insutype=#{insutype},visit_datetime=#{ybRegisterDate},adm_reg_msgid=#{admRegMsgid} " +
            "where pat_no=#{inpatientNo} and times=#{admissTimes} and ledger_sn=#{ledgerSn}")
    void afterAdmissRegister(ZyPatientInfo p);

    @Update("update zy_actpatient set responce_type=#{restype},med_type=#{medtype} where inpatient_no=#{patNo} ;" +
            "update zy_ledger_file set responce_type=#{restype} where inpatient_no=#{patNo} " +
            "and admiss_times=#{times} and ledger_sn=#{sn}")
    void updateResponceType(@Param("restype") String restype,
                            @Param("medtype") String medtype,
                            @Param("patNo") String patNo,
                            @Param("times") int times,
                            @Param("sn") int sn);

    @Update("update zy_detail_charge set trans_flag_yb='2' where inpatient_no=#{patNo} " +
            "and admiss_times=#{times} and ledger_sn in (#{sn1}, #{sn2})")
    void updateTransFlag(@Param("patNo") String patNo,
                         @Param("times") int times,
                         @Param("sn1") int sn1,
                         @Param("sn2") int sn2);

    @Select("select code from zy_zd_responce_type where med_type=#{medtype} ")
    String selectResponceType(@Param("medtype") String medtype);

    @Update("update t_si_setlinfo set revoked=1 where pat_no=#{patNo} and times=#{times} and ledger_sn=#{ledgerSn}")
    void deleteSetlInfo(@Param("patNo") String patNo, @Param("times") int times, @Param("ledgerSn") int ledgerSn);

    @Update("delete from t_si_setldetail where pat_no=#{patNo} and times=#{times} and ledger_sn=#{ledgerSn}")
    void deleteSetlDetail(@Param("patNo") String patNo, @Param("times") int times, @Param("ledgerSn") int ledgerSn);

    @Update("update t_si_pat_info set rvk_setl_msgid=#{rvkSetlMsgid} where " +
            "pat_no=#{patNo} and times=#{times} and ledger_sn=#{ledgerSn}")
    void updateRvkSetlMsgid(@Param("patNo") String patNo,
                            @Param("times") int times,
                            @Param("ledgerSn") int ledgerSn,
                            @Param("rvkSetlMsgid") String rvkSetlMsgid);

    @Delete("delete from t_si_setl_fee_detl where mdtrt_id=#{mdtrtId}")
    void deleteSiSetlFeeDetl(@Param("mdtrtId") String mdtrtId);

    @Delete("delete from zy_ledger_file_yb where inpatient_no=#{patNo} and admiss_times=#{times} and ledger_sn=#{ledgerSn}")
    void deleteZyLedgerFileYb(@Param("patNo") String patNo,
                              @Param("times") int times,
                              @Param("ledgerSn") int ledgerSn);

    @Delete("delete from zy_receipt where inpatient_no=#{patNo} and admiss_times=#{times} " +
            "and ledger_sn=#{ledgerSn} and receipt_no='00000000'")
    void deleteZyReceipt(@Param("patNo") String patNo,
                         @Param("times") int times,
                         @Param("ledgerSn") int ledgerSn);

    @Update("update zy_ledger_file set settle_type=0 where inpatient_no=#{patNo} and admiss_times=#{times} and ledger_sn=#{ledgerSn}")
    void updateSettleType(@Param("patNo") String patNo,
                          @Param("times") int times,
                          @Param("ledgerSn") int ledgerSn);

    @Update("update zy_detail_charge set charge_status=2 where charge_status=3 and " +
            "inpatient_no=#{patNo} and admiss_times=#{times} and ledger_sn=#{ledgerSn}")
    void updateChargeStatus(@Param("patNo") String patNo,
                            @Param("times") int times,
                            @Param("ledgerSn") int ledgerSn);
}