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

package thyyxxk.webserver.dao.his.medicalinsurance;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import thyyxxk.webserver.entity.medicalinsurance.manage.InstSetlLdgChkBrf;
import thyyxxk.webserver.entity.medicalinsurance.manage.detailanalyse.FsiDiagnoseDtos;
import thyyxxk.webserver.entity.medicalinsurance.manage.detailanalyse.FsiEncounterDtos;
import thyyxxk.webserver.entity.medicalinsurance.manage.detailanalyse.FsiOrderDtos;
import thyyxxk.webserver.entity.medicalinsurance.manage.detailanalyse.PatientDtos;
import thyyxxk.webserver.entity.medicalinsurance.manage.frontsheet.Baseinfo;
import thyyxxk.webserver.entity.medicalinsurance.manage.frontsheet.Diseinfo;
import thyyxxk.webserver.entity.medicalinsurance.manage.orderinfo.ActOrder;

import java.util.List;

/**
 * @author: DingJie
 * @create: 2021/8/99:45
 */
@Mapper
public interface SiManageDao {

    @Update("<script>" +
            "update ${table} set uploaded_flag=#{flag} where code in " +
            "<foreach collection='codes' item='code' separator=',' open='(' close=')'>" +
            "#{code}</foreach>" +
            "</script>")
    void updateUploadedFlag(@Param("table") String table,
                            @Param("flag") int flag,
                            @Param("codes") List<String> codes);

    @Update("update ${table} set uploaded_flag=0 where code=#{code}")
    void updateUploadedFlag2(@Param("table") String table,
                             @Param("code") String codes);

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

    @Select("select pat_no,times,ledger_sn,medfee_sumamt,acct_pay,fund_pay_sumamt, " +
            "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 insutype=#{insutype} and setl_type=#{setlType} and revoked=0 " +
            "and clr_optins=#{clrOptins} and setl_time>=#{start} and setl_time<=#{end} and fund_pay_sumamt!=0 " +
            "and (insuplc_admdvs='439900' or insuplc_admdvs like '4301%')")
    List<InstSetlLdgChkBrf> selectSetlChkBrfsWithInsutype(@Param("setlType") String setlType,
                                              @Param("insutype") String insutype,
                                              @Param("clrOptins") String clrOptins,
                                              @Param("start") String start,
                                              @Param("end") String end);

    @Select("select pat_no,times,ledger_sn,setl_id,mdtrt_id,psn_no,medfee_sumamt,acct_pay,fund_pay_sumamt,psn_cash_pay, " +
            "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 setl_type=#{setlType} and revoked=0 and clr_optins=#{clrOptins} and fund_pay_sumamt!=0 " +
            "and setl_time>=#{start} and setl_time<=#{end} and (insuplc_admdvs='439900' or insuplc_admdvs like '4301%')")
    List<InstSetlLdgChkBrf> selectSetlChkBrfs(@Param("setlType") String setlType,
                                              @Param("clrOptins") String clrOptins,
                                              @Param("start") String start,
                                              @Param("end") String end);

    @Select("select count(1) from dj_user_role where user_code=#{code} and role_id=41")
    Integer recoveryTradePermission(@Param("code") String code);

    @Select("select mdtrtSn='H43010500370'+bah+'_'+cast(admiss_times as varchar), " +
            "mdtrtId=(select top 1 mdtrt_id from t_si_setlinfo where pat_no=bah and times=admiss_times and revoked=0), " +
            "psnNo=(select top 1 psn_no from t_si_setlinfo where pat_no=bah and times=admiss_times and revoked=0), " +
            "admiss_times as patnIptCnt,bah as iptNo,medcasno=bah+'_'+cast(admiss_times as varchar), " +
            "name as psnName,sex as gend,birth_date as brdy, " +
            "ntly=(select si_code from zd_country_code where code=country), " +
            "ntlyName=(select rtrim(name) from zd_country_code where code=country), " +
            "nwbBirWt=case when new_born_weight='-' then '' else new_born_weight end, " +
            "nwbAdmWt=case when new_born_admiss_weight='-' then '' else new_born_admiss_weight end, " +
            "birth_place_name as birplc,native_place_name as napl, " +
            "naty=(select wjw_code from zd_nation_code where code=nation), " +
            "natyName=(select rtrim(name) from zd_nation_code where code=nation), " +
            "social_no as certno,occupation as prfs, " +
            "mrgStas=(select si_code from zd_marital_status where code=marriage), " +
            "live_place as currAddr,phone as psnTel,hk_place_name as resdAddr, " +
            "unit_phone as emprTel,unit_place as emprAddr,contact_phone as conerTel, " +
            "contact_name as conerName,contact_addr_name as conerAddr,zy_admiss_way as admWayCode, " +
            "admWayName=(select name from zy_zd_admiss_way where code=zy_admiss_way), " +
            "admCaty=(select si_caty from zd_unit_code where code=admiss_dept_code), " +
            "admiss_ward as admWard,admiss_date as admDate,dismiss_date as dscgDate, " +
            "dscgCaty=(select si_caty from zd_unit_code where code=dismiss_dept_code), " +
            "dismiss_ward as dscgWard,admiss_days as iptDays,allergy as drugDicmFlag, " +
            "allergic_medicine as dicmDrugName,autopsy as dieAutpFlag,blood_type as aboCode, " +
            "aboName=(select rtrim(name) from zd_blood_type where code=blood_type), " +
            "rhCode=(select wjw_code from zd_hbsag where code=rh), " +
            "rhName=(select rtrim(name) from zd_hbsag where code=rh), " +
            "dept_leader_name as deptdrtName,leader_doctor_name as chfdrName, " +
            "main_doctor_name as atddrName,admiss_doctor_name as iptDrName, " +
            "duty_nurse_name as respNursName,study_doctor_name as trainDrName, " +
            "internship_doctor_name as intnDrName,coder_name as codrName, " +
            "quality_control_doctor_name as qltctrlDrName, " +
            "quality_control_nurse_name as qltctrlNursName, " +
            "quality_control_level as medcasQltCode, " +
            "quality_control_date as qltctrlDate, " +
            "dscgWay=(select wjw_code from zd_zy_dismiss_way where code=zy_dismiss_way), " +
            "dscgWayName=(select name from zd_zy_dismiss_way where code=zy_dismiss_way), " +
            "acpMedinsName=case when dismiss_destination='-' then '' else dismiss_destination end, " +
            "admiss_again_in_one_month as dscg31DaysRinpFlag, " +
            "dscg31DaysRinpPup=case when admiss_again_purpose='-' then '' else admiss_again_purpose end, " +
            "damgIntxExtRea=case when hurt_reason_name='-' then '' else hurt_reason_name end, " +
            "damgIntxExtReaDisecode=case when hurt_reason_code='-' then '' else hurt_reason_code end, " +
            "brn_damg_bfadm_coma_dura=coma_days_before_admiss+'/'+coma_hours_before_admiss+'/'+coma_minutes_before_admiss, " +
            "brn_damg_afadm_coma_dura=coma_days_after_admiss+'/'+coma_hours_after_admiss+'/'+coma_minutes_after_admiss, " +
            "fixmedinsCode='H43010500370',age,hbs_ag as hbsag,hcv_ab,hiv_ab, " +
            "rescue_times as rescCnt,rescue_success_times as rescSuccCnt,total_cost as medfeeSumamt, " +
            "valiFlag='1',case_classification as ctd,dept_leader as deptdrtCode,leader_doctor as chfdrCode, " +
            "main_doctor as atddrCode,admiss_doctor as iptDrCode,duty_nurse as respNursCode, " +
            "quality_control_doctor as qltctrlDrCode,quality_control_nurse as qltctrlNursCode " +
            "from t_case_frontsheet_main where bah=#{patNo} and admiss_times=#{times}")
    Baseinfo selectFrontSheetBaseInfo(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select maindiagFlag=case when dis_diag_no=1 then '1' else '0' end, " +
            "rtrim(dis_diag) as diagCode,rtrim(dis_diag) as inhospDiagCode, " +
            "rtrim(dis_diag_comment) as diagName,rtrim(dis_diag_comment) as inhospDiagName, " +
            "valiFlag='1',iptMedcasHmpgSn=rtrim(inpatient_no)+'_'+cast(admiss_times as varchar), " +
            "mdtrtSn='H43010500370'+rtrim(inpatient_no)+'_'+cast(admiss_times as varchar), " +
            "fixmedinsCode='H43010500370' " +
            "from zy_dis_diag_yb where inpatient_no=#{patNo} and admiss_times=#{times}")
    List<Diseinfo> selectYbDisdiags(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select maindiagFlag=case when no=1 then '1' else '0' end, " +
            "code as diagCode,code as inhospDiagCode, " +
            "name as diagName,name as inhospDiagName, " +
            "valiFlag='1',iptMedcasHmpgSn=bah+'_'+cast(times as varchar), " +
            "mdtrtSn='H43010500370'+bah+'_'+cast(times as varchar), " +
            "fixmedinsCode='H43010500370' " +
            "from t_case_frontsheet_disdiag where bah=#{patNo} and times=#{times}")
    List<Diseinfo> selectFrontSheetDiseInfo(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select mdtrtSn='H43010500370'+rtrim(a.inpatient_no)+'_'+cast(a.admiss_times as varchar), " +
            "mdtrtId=(select top 1 mdtrt_id from t_si_pat_info where " +
            "pat_no=a.inpatient_no and times=a.admiss_times and mdtrt_id is not null and med_type!='42' ), " +
            "psnNo=(select top 1 psn_no from t_si_pat_info where " +
            "pat_no=a.inpatient_no and times=a.admiss_times and mdtrt_id is not null and med_type!='42' ), " +
            "iptBedNo=rtrim(b.bed_no),drordNo=cast(cast(act_order_no as decimal) as varchar), " +
            "isuDeptCode=(select si_caty from zd_unit_code t where t.code=(select dept_code from a_employee_mi e where e.code=a.enter_oper)), " +
            "order_time as drordIsuNo,exeDeptCode=(select si_caty from zd_unit_code where code=exec_unit), " +
            "exeDeptCode=(select si_caty from zd_unit_code t where t.code=(select top 1 exec_unit from zy_detail_charge e where e.order_no=a.act_order_no)), " +
            "exedeptName=(select t.name from zd_unit_code t where t.code=(select top 1 exec_unit from zy_detail_charge e where e.order_no=a.act_order_no)), " +
            "drordPtrName=(select t.name from a_employee_mi t where t.code=a.executer1), " +
            "order_code as drordDetlCode,order_name as drordDetlName,valiFlag='1', " +
            "iptDeptCode=(select si_caty from zd_unit_code t where t.code=b.small_dept), " +
            "medcasDrordDetlId='H43010500370'+cast(cast(act_order_no as decimal) as varchar) " +
            "from yz_act_order a, zy_actpatient b where a.inpatient_no=#{patNo} and a.admiss_times=#{times} " +
            "and a.inpatient_no=b.inpatient_no and a.admiss_times=b.admiss_times")
    List<ActOrder> selectActOrders(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select psn_no as patnId, psn_name as patnName, gend, brdy, insuplc_admdvs as poolarea, mdtrt_id as currMdtrtId " +
            "from t_si_pat_info where pat_no=#{patNo} and times=#{times}")
    PatientDtos selectPatientDto(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select mdtrt_id,medinsAdmdvs='430105',medinsLv='03',begntime as adm_date,endtime as dscg_date,\n" +
            "       dscgMainDiseCodg=(select rtrim(dis_diag) from zy_dis_diag_yb where inpatient_no=a.pat_no and\n" +
            "                        admiss_times=a.times and dis_diag_no=1),\n" +
            "       dscgMainDiseName=(select rtrim(dis_diag_comment) from zy_dis_diag_yb where inpatient_no=a.pat_no and\n" +
            "                        admiss_times=a.times and dis_diag_no=1),\n" +
            "       drCodg=(select rtrim(op_id_code) from zy_dis_diag_yb where inpatient_no=a.pat_no and\n" +
            "               admiss_times=a.times and dis_diag_no=1),\n" +
            "       admDeptCodg=(select rtrim(small_dept) from zy_inactpatient where inpatient_no=a.pat_no and admiss_times=a.times),\n" +
            "       medMdtrtType=case when setl_type='11' then 1 else 2 end,med_type,medfee_sumamt,fulamt_ownpay_amt,\n" +
            "       psn_cash_pay,insutype\n" +
            "from t_si_setlinfo a where a.pat_no=#{patNo} and a.times=#{times} and a.revoked=0")
    FsiEncounterDtos selectFsiEncounterDto(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select dise_id=rtrim(inpatient_no)+'_'+cast(admiss_times as varchar)+'_'+cast(dis_diag_no as varchar),\n" +
            "       inout_dise_type='2',maindise_flag=case when dis_diag_no=1 then 1 else 0 end,\n" +
            "       dis_diag_no as dias_srt_no,rtrim(dis_diag) as dise_codg,rtrim(dis_diag_comment) as dise_name,\n" +
            "       op_diag_date as dise_date\n" +
            "from zy_dis_diag_yb where inpatient_no=#{patNo} and admiss_times=#{times}")
    List<FsiDiagnoseDtos> selectFsiDiagnoseDtos(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select\n" +
            "    rxId=cast(cast(act_order_no as decimal) as varchar)+'_'+charge_code_mx,\n" +
            "    rxno=cast(cast(act_order_no as decimal) as varchar),\n" +
            "    longDrordFlag=case when frequ_code='ONCE' then 0 else 1 end,\n" +
            "    listType=(select list_type from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    chrgType=(select med_chrgitm_type from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    drordBhvr=case when frequ_code='ONCE' then 1 else 2 end,\n" +
            "    hilist_code=(select hilist_code from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    hilist_name=(select hilist_name from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    hilist_lv=(select chrgitm_lv from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    pric=(select pric from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    a.charge_code_mx as hosplist_code,a.charge_amount as cnt,\n" +
            "    hosplistName=(select medins_list_name from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    a.charge_fee as sumamt,\n" +
            "    ownpayAmt=(select fulamt_ownpay_amt from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    selfpayAmt=(select preselfpay_amt from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    b.start_time as drord_begn_date,b.end_time as drord_stop_date,\n" +
            "    drord_dept_codg=(select bilg_dept_codg from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    drord_dept_name=(select bilg_dept_name from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    drord_dr_codg=(select bilg_dr_codg from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    drord_dr_name=(select bilg_dr_name from t_si_setl_fee_detl where setl_id=#{setlId} and feedetl_sn=a.detail_sn),\n" +
            "    drord_dr_profttl=(select rtrim(emp_tit_code) from a_employee_mi where code=b.enter_oper),\n" +
            "    curr_drord_flag='1'\n" +
            "from zy_detail_charge a, yz_inact_order b where order_no>100 and a.inpatient_no=#{patNo} and a.admiss_times=#{times}\n" +
            "and a.ledger_sn>0 and a.inpatient_no=b.inpatient_no and a.admiss_times=b.admiss_times")
    List<FsiOrderDtos> selectFsiOrderDtos(@Param("patNo") String patNo, @Param("times") int times, @Param("setlId") String setlId);

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