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.clinicinfo.ClinicDiseinfo;
import thyyxxk.webserver.entity.medicalinsurance.manage.clinicinfo.ClinicRegistration;
import thyyxxk.webserver.entity.medicalinsurance.manage.clinicinfo.ClinicRxinfo;
import thyyxxk.webserver.entity.medicalinsurance.manage.clinicinfo.EmergencyOprninfo;
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;
import java.util.Map;

/**
 * @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 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 clr_type=#{clrType} and revoked=0 " +
            "and clr_optins=#{clrOptins} and setl_time>=#{start} and setl_time<=#{end} and fund_pay_sumamt!=0 " +
            "and insuplc_admdvs like '43%' ")
    List<InstSetlLdgChkBrf> selectSetlChkBrfsWithInsutype(@Param("clrType") String clrType,
                                                          @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 clr_type=#{clrType} and revoked=0 and clr_optins=#{clrOptins} and fund_pay_sumamt!=0 " +
            "and setl_time>=#{start} and setl_time<=#{end} and insuplc_admdvs like '43%' ")
    List<InstSetlLdgChkBrf> selectSetlChkBrfs(@Param("clrType") String clrType,
                                              @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," +
            "deptdrtCode=(select top 1 rtrim(yb_code) from a_employee_mi t where t.code=dept_leader)," +
            "chfdrCode=(select top 1 rtrim(yb_code) from a_employee_mi t where t.code=leader_doctor)," +
            "atddrCode=(select top 1 rtrim(yb_code) from a_employee_mi t where t.code=main_doctor)," +
            "iptDrCode=(select top 1 rtrim(yb_code) from a_employee_mi t where t.code=admiss_doctor)," +
            "respNursCode=(select top 1 rtrim(yb_code) from a_employee_mi t where t.code=duty_nurse)," +
            "qltctrlDrCode=(select top 1 rtrim(yb_code) from a_employee_mi t where t.code=quality_control_doctor)," +
            "qltctrlNursCode=(select top 1 rtrim(yb_code) from a_employee_mi t where t.code=quality_control_nurse) " +
            "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)+'_'+cast(dis_diag_no 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)+'_'+cast(no 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(order_no as decimal) as varchar)+'_'+charge_code_mx,\n" +
            "    rxno=cast(cast(order_no as decimal) as varchar),\n" +
            "    longDrordFlag=case when (select frequ_code from yz_inact_order where act_order_no=order_no)='ONCE' then 0 else 1 end,\n" +
            "    b.list_type,\n" +
            "    chrgType=b.med_chrgitm_type,\n" +
            "    drordBhvr=case when (select frequ_code from yz_inact_order where act_order_no=order_no)='ONCE' then 1 else 2 end,\n" +
            "    b.hilist_code,\n" +
            "    b.hilist_name,\n" +
            "    hilist_lv=b.chrgitm_lv,\n" +
            "    b.pric,\n" +
            "    a.charge_code_mx as hosplist_code,a.charge_amount as cnt,\n" +
            "    hosplistName=b.medins_list_name,\n" +
            "    a.charge_fee as sumamt,\n" +
            "    ownpayAmt=b.fulamt_ownpay_amt,\n" +
            "    selfpayAmt=b.preselfpay_amt,\n" +
            "    drord_begn_date=(select start_time from yz_inact_order where act_order_no=order_no),\n" +
            "    drord_stop_date=(select end_time from yz_inact_order where act_order_no=order_no),\n" +
            "    drord_dept_codg=b.bilg_dept_codg,\n" +
            "    drord_dept_name=b.bilg_dept_name,\n" +
            "    drord_dr_codg=b.bilg_dr_codg,\n" +
            "    drord_dr_name=b.bilg_dr_name,\n" +
            "    drord_dr_profttl=(select rtrim(emp_tit_code) from a_employee_mi where code=b.bilg_dr_codg),\n" +
            "    curr_drord_flag='1'\n" +
            "from zy_detail_charge a, t_si_setl_fee_detl b where a.order_no>100 and a.inpatient_no=#{patNo} and a.admiss_times=#{times}\n" +
            "and a.ledger_sn>0 and b.setl_id=#{setlId} and b.feedetl_sn=a.detail_sn")
    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);

    @Select("select mdtrtSn=pat_no+'_'+cast(times as varchar),valiFlag='1',mdtrt_id,psn_no,begntime as rgstRetnrTime " +
            "from t_si_setlinfo where pat_no=#{patNo} and times=#{times}")
    ClinicRegistration selectClinicRegistration(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select maindiagFlag=case when diag_srt_no=1 then '1' else '0' end,diag_code,diag_name,vali_flag " +
            "from t_si_mz_diag where pat_no=#{patNo} and times=#{times} ")
    List<ClinicDiseinfo> selectClinicDiseinfo(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select rxno=patient_id+'_'+cast(times as varchar)+'_'+cast(receipt_no as varchar)+'_'+cast(order_no as varchar)," +
            "fee_date as rxPrscTime,his_item_code as rxDetlId,his_item_name as rxDetlName,valiFlag='1' " +
            "from t_mt_receipt where patient_id=#{patNo} and times=#{times}")
    List<ClinicRxinfo> selectClinicRxinfo(@Param("patNo") String patNo, @Param("times") int times);

    @Select("select mdtrtSn=pat_no+'_'+cast(times as varchar),medrcdno=pat_no+'_'+cast(times as varchar) " +
            "from t_si_setlinfo where pat_no=#{patNo} and times=#{times}")
    List<EmergencyOprninfo> selectEmergencyOprninfos(@Param("patNo") String patNo, @Param("times") int times);

    @Select(" select mdtrt_id as mdtrtId, setl_id as setlId, hilist_name as hilistName, hilist_code as hilistCode, " +
            " fee_ocur_time as feeOcurTime, det_item_fee_sumamt as totalFee, pric, cnt, " +
            " bilg_dept_name as bilgDeptName, bilg_dr_name as bilgDrName, opter_name as opterName, " +
            " opt_time as optTime, chrgitm_lv_name as chrgitmLvName " +
            " from t_si_setl_fee_detl where mdtrt_id = #{mdtrtId} ")
    List<Map<String, Object>> selectSetlPrescription(@Param("mdtrtId") String mdtrtId);

    @Select(" select patient_id as patientId, times, emr_no as emrNo, visit_date as visitDate, " +
            " emr_chief_complaint as emrCC, emr_hpi as emrHpi, emr_ps as emrPs, " +
            " emr_pe as emrPe, emr_fzjc as emrFzjc, emr_bzfx as emrBzfx, emr_zf as emrZf, " +
            " emr_process as emrProcess, personal_history as personalHistory, family_history as familyHistory, " +
            " obsterical_history as obstericalHistory, weight, temperature, sphygmus, breathe," +
            " pressure_high as pressureHigh, pressure_floor as pressureFloor, " +
            " pressure_high_left as pressureHighLeft, pressure_floor_left as pressureFloorLeft, " +
            " tentative_diagnosis as tentativeDiagnosis, " +
            " rtrim(isnull(e.name, '其他')) as doctorName, rtrim(isnull(z.name, '其他')) as deptName " +
            " from mz_bl_record " +
            " left join zd_unit_code z on dept_code = z.code" +
            " left join a_employee_mi e on doctor_code = e.code " +
            " where patient_id = #{patNo} and times = #{times} ")
    Map<String, Object> selectMzBl(@Param("patNo") String patNo, @Param("times") int times);

}