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

package thyyxxk.webserver.dao.his.medicalinsurance;

import org.apache.ibatis.annotations.*;
import thyyxxk.webserver.entity.dictionary.CodeName;
import thyyxxk.webserver.entity.markmtfees.*;
import thyyxxk.webserver.entity.medicalinsurance.inpatient.AdmDiseinfo;
import thyyxxk.webserver.entity.medicalinsurance.inpatient.AdmMdtrtinfo;
import thyyxxk.webserver.entity.medicalinsurance.inpatient.Dscginfo;
import thyyxxk.webserver.entity.medicalinsurance.inpatient.PreSetlmt;
import thyyxxk.webserver.entity.medicalinsurance.outpatient.*;
import thyyxxk.webserver.entity.medicalinsurance.query.PsnBaseInfo;
import thyyxxk.webserver.entity.outpatient.medicalinfo.MzBlRecord;

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

/**
 * @description: 新医保门诊mapper
 * @author: DingJie
 * @create: 2021-06-28 14:35:25
 **/
@Mapper
public interface SiMzDao {

    @Select("select times from mz_patient_mi where patient_id=#{patientId} ")
    int selectMaxTimes(String patientId);

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

    @Select("select * from t_si_pat_info where pat_no=#{patNo} and times=#{times}")
    PsnBaseInfo selectPsnBaseinfo(String patNo, int times);

    @Select("SELECT rtrim(a.patient_id) as patientId,a.times,a.visit_dept_code,b.adress,rtrim(b.phone_no) as phoneNo, " +
            "rtrim(b.name) as name,b.age,sex=case when b.sex='1' then '男' when b.sex='2' then '女' else '未知' end, " +
            "visitDeptName=(select rtrim(name) from zd_unit_code where code=a.visit_dept_code), " +
            "a.doctor_code,a.visit_date,a.receipt_no,a.order_no,a.op_day,b.social_no, " +
            "doctorName=(select rtrim(name) from a_employee_mi where code=a.doctor_code), " +
            "doctorYbCode=(select rtrim(yb_code) from a_employee_mi where code=a.doctor_code), " +
            "icdText=case when nullif(a.icd_code_new,'') is null then rtrim(a.icd_text) else rtrim(a.icd_text_new) end, " +
            "admdvs=(select insuplc_admdvs from t_si_pat_info where pat_no=#{patientId} and times=#{times} and ledger_sn=0) " +
            "FROM mz_visit_table a, mz_patient_mi b " +
            "WHERE a.patient_id=#{patientId} AND a.times=#{times} and a.patient_id=b.patient_id")
    MzVisit selectMzVisit(String patientId, int times);

    @Select("select count(1) from t_si_setlinfo where pat_no=#{patNo} and times=#{times} and revoked=0")
    int selectSiCount(String patNo, int times);

    @Select("select count(1) from powersi_mip_setlinfo where med_org_ord=#{hisOrdNum} and ord_state='SETTLED'")
    int selectMipayCount(String hisOrdNum);

    @Select("select count(1) from t_injury_setlinfo where pat_no=#{patNo} and times=#{times} and revoked=0")
    int selectInjuryCount(String patNo, int times);

    @Select("select count(1) from t_mt_receipt where patient_id=#{patientId} and times=#{times} and " +
            "receipt_no=#{receiptNo} and order_no=#{orderNo}")
    int selectFeeCount(String patientId, int times, int receiptNo, int orderNo);

    @Select("select rtrim(name) from a_employee_mi where code=#{code}")
    String selectDoctorName(String code);

    @Select("select rtrim(specification) from yp_zd_dict where code=#{code} and serial=#{serial}")
    String selectSpecification(String code, String serial);

    @Select("select rtrim(supply_name) from mz_zd_supply_type where supply_code=#{code}")
    String selectSupplyName(String code);

    @Select("select rtrim(charge_unit) from zd_charge_item where code=#{code}")
    String selectXmChargeUnit(String code);

    @Select("select rtrim(name) from yp_zd_unit where code=#{code}")
    String selectDrugUnit(String code);

    @Select("select receipt_no from t_mt_receipt where patient_id=#{patNo} and times=#{times} group by receipt_no")
    List<Integer> selectGeneratedReceiptNos(String patNo, int times);

    @Select("select count(1) from mz_charge_detail where patient_id=#{patNo} and times=#{times} " +
            "and receipt_no=#{receiptNo} and pay_mark=0 and confirm_flag!=4")
    int isReceiptPayed(String patNo, int times, int receiptNo);

    @Delete("delete from t_mt_receipt where patient_id=#{patNo} and times=#{times} and receipt_no=#{receiptNo}")
    void deleteCertainReceipt(String patNo, int times, int receiptNo);

    @Insert("<script>" +
            "insert into t_mt_receipt (patient_id,times,receipt_no,order_no,item_no, " +
            "his_item_name,his_item_code,fee_date,input_date,medi_item_type,charge_fee, " +
            "price,quantity,drug_win,input_staff,input_man,serial_no,yb_trans_flag,charge_type, " +
            "bill_item_code,drug_unit,hosp_appr_flag) values " +
            "<foreach collection='list' item='item' separator=','>" +
            "(#{item.patientId},#{item.times},#{item.receiptNo},#{item.orderNo},#{item.itemNo}," +
            "#{item.drugName},#{item.chargeItemCode},#{item.priceTime},#{item.chargeDate},#{item.mediItemType}," +
            "#{item.chargeFee},#{item.price},#{item.quantity},#{item.drugWin},#{item.doctorCode}," +
            "#{item.doctorName},#{item.serialNo},0,#{item.groupNo},#{item.billItemCode},#{item.drugUnit}," +
            "#{item.hospApprFlag})" +
            "</foreach>" +
            "</script>")
    void insertMtReceipts(List<MzReceipt> list);

    @Delete("delete from t_mt_receipt where patient_id=#{patientId} and times=#{times} and " +
            "receipt_no=#{receiptNo} and order_no=#{orderNo}")
    void deleteMzReceipt(OrderNo param);

    @Delete("delete from t_mt_receipt where patient_id=#{patNo} and times=#{times}")
    void deleteAllReceipts(String patNo, int times);

    @Select("select rtrim(patient_id) as patNo,times,charge_date,receipt_no, " +
            "chargeFee=(select cast(sum(isnull(unit_price,0)*isnull(drug_win,1)*isnull(quantity,1)) as decimal(16,2))), " +
            "status=(select count(1) from t_mt_receipt d where d.patient_id=a.patient_id and d.times=a.times and d.receipt_no=a.receipt_no) " +
            "from mz_charge_detail a where a.patient_id=#{patNo} and a.charge_date>=#{start} and a.charge_date<=#{end} " +
            "and pay_mark=0 and confirm_flag!=4 and isnull(cash_id,'')!='99998' group by patient_id, times, charge_date,receipt_no order by times")
    List<MzDepositFile> selectMzDepositFiles(String patNo, String start, String end);

    @Select("select rtrim(patient_id) as patient_id,times,receipt_no,order_no,item_no,charge_item_code, " +
            "charge_bill_code,quantity,unit_price,serial,serial_no,frequency,drug_quan,charge_date, " +
            "supplyCode=(select top 1 rtrim(d.supply_name) from mz_zd_supply_type d where d.supply_code=a.supply_code)," +
            "doctorName=(select rtrim(d.name) from a_employee_mi d where d.code=a.doctor_code)," +
            "group_no,confirm_flag,instruction_text,drug_win,price_time,tc_no, " +
            "bill_item_code,doctor_code,tc_name,hosp_appr_flag," +
            "drugName=(case when group_no='00' then " +
            "(select d.name from zd_charge_item d where d.code=a.charge_item_code) else " +
            "(select top 1 d.name from yp_zd_dict d where d.code=a.charge_item_code) end)," +
            "drugUnit=(case when group_no='00' then " +
            "(select rtrim(d.charge_unit) from zd_charge_item d where d.code=a.charge_item_code) else " +
            "(select rtrim(d.name) from yp_zd_unit d where d.code=a.drug_unit) end)," +
            "specification=(case when group_no='00' then '' else " +
            "(select rtrim(d.specification) from yp_zd_dict d where d.code=a.charge_item_code and d.serial=a.serial) end)," +
            "ybComment=(case when group_no='00' then null else " +
            "(select max(d.yb_comment_new) from yp_zd_dict d where d.code=a.charge_item_code) end)," +
            "nationalCode=(case when group_no='00' then " +
            "(select isnull(d.national_code_27,d.national_code) from zd_charge_item d where d.code=a.charge_item_code) else " +
            "(select max(d.national_code) from yp_zd_dict d where d.code=a.charge_item_code) end) " +
            "from mz_charge_detail a where patient_id=#{patNo} and times=#{times} and " +
            "receipt_no=#{receipt} and pay_mark=0 and confirm_flag!=4 and bill_item_code!='TC' " +
            "and charge_item_code!='BILL99' and isnull(cash_id,'')!='99998'")
    List<MzReceipt> selectMzCharge(String patNo, int times, int receipt);

    @Select("select top 1 national_code from yp_zd_dict where code=#{code}")
    String selectYpNationalCode(String code);

    @Select("select isnull(national_code_27,national_code) from zd_charge_item where code=#{code}")
    String selectXmNationalCode(String code);

    @Select("select max(yb_comment_new) from yp_zd_dict where code=#{code} ")
    String selectYbComment(String code);

    @Delete("delete from t_si_mz_diag where pat_no=#{patNo} and times=#{times}")
    void deleteMzDiags(String patNo, int times);

    @Insert("insert into t_si_mz_diag (pat_no, times, diag_srt_no, diag_type, diag_code, diag_name, diag_dept, " +
            "dise_dor_no, dise_dor_name, diag_time, vali_flag, real_opter) " +
            "values (#{patNo}, #{times}, #{diagSrtNo}, #{diagType}, #{diagCode}, #{diagName}, #{diagDept}, " +
            "#{diseDorNo}, #{diseDorName}, #{diagTime}, #{valiFlag}, #{realOpter})")
    void insertNewMzDiag(SiMzDiag diag);

    @Update("update t_si_pat_info set insutype=#{insutype},balc=#{balc},insuplc_admdvs=#{admdvs}, " +
            "insuplc_admdvs_name=#{insuplcAdmdvsName} where pat_no=#{patNo} and times=#{times}")
    void updateSiZyInfoPsnNo(String patNo, int times, String insutype, String balc, String admdvs, String insuplcAdmdvsName);

    @Select("select count(1) from t_si_pat_info where pat_no=#{patNo} and times=#{times} ")
    int selectSiMzInfoCount(String patNo, int times);

    @Select("select top 1 * from t_si_pat_info where pat_no=#{patNo} order by times desc")
    PsnBaseInfo selectLatestPsnInfo(String patNo);

    @Insert("insert into t_si_pat_info (pat_no,times,ledger_sn,psn_no,psn_cert_type," +
            "insutype,insuplc_admdvs,insuplc_admdvs_name,certno,psn_name,gend,naty,brdy,age," +
            "med_type,psn_idet_type,psn_type,emp_name,create_datetime,balc) " +
            "values (#{patNo},#{times},#{ledgerSn},#{psnNo},#{psnCertType},#{insutype}," +
            "#{insuplc},#{insuplcAdmdvsName},#{certno},#{psnName},#{gend},#{naty},#{brdy},#{age}," +
            "#{medType},#{psnIdetType},#{psnType},#{empName},getdate(),#{balc})")
    void insertSiMzInfoPsnNo(PsnBaseInfo psnBaseinfo);

    @Update("update t_si_setlinfo set fund_pay_sumamt=#{fundPay},acct_pay=#{acctPay} " +
            "where pat_no=#{patientId} and times=#{times} and revoked=0")
    int modifyFundAmt(ModifyFundAmt amt);

    @Select("select count(1) from t_si_presetlinfo where pat_no=#{patNo} and times=#{times}")
    int selectPresettleCount(String patNo, int times);

    @Select("select staff_id from t_si_setlinfo where pat_no=#{patNo} and times=#{times} and revoked=0")
    String selectSetlStaff(String patNo, int times);

    @Select("select emr_chief_complaint,emr_hpi,emr_ps,emr_pe,emr_fzjc " +
            "from mz_bl_record where patient_id=#{patNo} and times=#{times} ")
    MzBlRecord selectMzBlRecord(String patNo, int times);

    @Select("select rtrim(max(receipt_bill)) from mz_receipt_serial " +
            "where patient_id=#{patNo} and times=#{times}")
    String selectReceiptBill(String patNo, int times);

    @Select("select result from t_si_log where msgid=#{msgid}")
    String getLogContent(String msgid);

    @Select("select b.psn_no,b.insutype,b.psn_name,a.visit_date as begntime, " +
            "mdtrtCertType=b.psn_cert_type,b.certno as mdtrtCertNo,certType=b.psn_cert_type,b.certno,b.med_type, " +
            "rtrim(a.patient_id) as iptNo, rtrim(a.visit_dept_code) as admDeptCodg, " +
            "atddrNo=(select rtrim(isnull(yb_code,code)) from a_employee_mi where code=a.doctor_code), " +
            "chfpdrName=(select rtrim(name) from a_employee_mi where code=a.doctor_code), " +
            "admDeptName=(select rtrim(name) from zd_unit_code where code=a.visit_dept_code), " +
            "admBed='JZ-1',exp_content='{}' from mz_visit_table a, t_si_pat_info b " +
            "where a.patient_id=#{patNo} and a.times=#{times} and b.pat_no=a.patient_id " +
            "and b.times=a.times and b.ledger_sn=0")
    AdmMdtrtinfo selectAdmMdtrtinfo(String patNo, int times);

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

    @Select("select icd_code_new as code,icd_text_new as name from mz_visit_table " +
            "where patient_id=#{patNo} and times=#{times}")
    CodeName getMzIcd(String patNo, int times);

    @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,diag_srt_no,diag_code,diag_name,diag_dept,diag_time, " +
            "maindiagFlag=case when diag_srt_no=1 then '1' else '0' end " +
            "from t_si_mz_diag where pat_no=#{patNo} and times=#{times}")
    List<AdmDiseinfo> getAdmDiseinfo(String patNo, int times);

    @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 t_mt_receipt b where b.patient_id=a.pat_no " +
            "and b.times=a.times),expContent='{}'" +
            "from t_si_pat_info a where pat_no=#{patNo} and times=#{times} ")
    PreSetlmt getPreSetlmt(String patNo, int times);

    @Select("select a.mdtrt_id,a.psn_no,a.insutype,b.visit_date as endtime, " +
            "dscgDeptCodg=(select si_caty from zd_unit_code where code=b.visit_dept_code), " +
            "dscgDeptName=(select rtrim(name) from zd_unit_code where code=b.visit_dept_code) " +
            "from t_si_pat_info a, mz_visit_table b " +
            "where a.pat_no=#{patNo} and a.times=#{times} and a.ledger_sn=0 " +
            "and a.pat_no=b.patient_id and a.times=b.times")
    Dscginfo getDscginfo(String patNo, int times);

    @Select("select mdtrt_id from t_si_pat_info where pat_no=#{patNo} and times=#{times}")
    String getMdtrtId(String patNo, int times);
}