web_server/src/main/java/thyyxxk/webserver/dao/his/casefrontsheet/CaseFrontSheetDao.java

package thyyxxk.webserver.dao.his.casefrontsheet;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;
import thyyxxk.webserver.entity.casefrontsheet.*;
import thyyxxk.webserver.entity.covid.Region;
import thyyxxk.webserver.entity.dictionary.CodeName;

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

/**
 * @author dj
 */
@Mapper
public interface CaseFrontSheetDao extends BaseMapper<CaseFrontsheetMain> {
    @Select("select count(1) from zy_actpatient where inpatient_no=#{bah} and admiss_times=#{times}")
    int selectActCount(@Param("bah") String bah, @Param("times") int times);

    @Select("select " +
            "bedNo=rtrim(bed_no)," +
            "bah=rtrim(inpatient_no)," +
            "times=rtrim(admiss_times)," +
            "name=rtrim(name)," +
            "sex,file_status," +
            "doctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=refer_physician), " +
            "admiss_date " +
            "from zy_actpatient with(nolock) where (small_dept=#{ward} or ward=#{ward}) and " +
            "charindex('$',inpatient_no)=0 " +
            "ORDER BY cast(bed_no AS int)")
    List<SheetOverview> getPatientOverview(@Param("ward") String ward);

    @Select("select " +
            "bedNo=rtrim(bed_no)," +
            "bah=rtrim(inpatient_no)," +
            "times=rtrim(admiss_times)," +
            "name=rtrim(name)," +
            "sex,file_status," +
            "doctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=refer_physician) " +
            "from zy_inactpatient with(nolock) where " +
            "dis_date>=#{start} and dis_date<=#{end} and " +
            "(small_dept=#{ward} or ward=#{ward}) and " +
            "charindex('$',inpatient_no)=0 " +
            "ORDER BY cast(bed_no AS int)")
    List<SheetOverview> getOutPatients(GetOutSheet param);

    @Select("select " +
            "healthCardNo=rtrim(health_card_no), " +
            "bah=rtrim(inpatient_no), " +
            "name=rtrim(name), " +
            "sex=rtrim(sex), " +
            "birthDate=birth_date, " +
            "country=rtrim(country), " +
            "birthPlace=rtrim(birth_place)," +
            "birthPlaceName=(select rtrim(name) from zd_district_code with(nolock) where code=birth_place), " +
            "nation=rtrim(nation_code), " +
            "socialNo=rtrim(social_no), " +
            "occupation=rtrim(occupation_code), " +
            "marriage=rtrim(marry_code), " +
            "livePlace=rtrim(home_street), " +
            "phone=rtrim(home_tel), " +
            "addrZipCode=rtrim(home_zipcode), " +
            "hkZipCode=rtrim(account_zipcode), " +
            "unitName=rtrim(employer_name)," +
            "unitPlace=rtrim(employer_street)," +
            "unitPhone=rtrim(employer_tel), " +
            "unitZipCode=rtrim(employer_zipcode), " +
            "contactName=rtrim(relation_name), " +
            "contactRelation=rtrim(relation_code), " +
            "contactPhone=(rtrim(relation_tel))  " +
            "from a_patient_mi with(nolock) " +
            "where inpatient_no=#{bah}")
    CaseFrontsheetMain getAPatientMi(@Param("bah") String bah);

    @Select("select sum(charge12) surgeryFee,sum(charge1) westMedicineFee,sum(charge2) chineseHerbalMedicineFee, " +
            "sum(charge10) anesthetizeFee,sum(charge28) chinesePatentMedicineFee,sum(charge19) careFee, " +
            "sum(charge25) generalTreatmentOperatingFee, surgicalFee=(sum(charge10) + sum(charge12)) from " +
            "zy_ledger_file with(nolock) where inpatient_no=#{bah} and admiss_times=#{times}")
    CaseFrontsheetMain getZyLedgerFile(@Param("bah") String bah, @Param("times") int times);

    @Select("select top 1 serial_no from t_injury_si_pat_info with(nolock) where pat_no=#{patNo} and times=#{times} order by ledger_sn desc")
    String selectInjurySerialNo(@Param("patNo") String patNo,
                                @Param("times") Integer times);

    @Select("select [dbo].[judge_infant](#{bah}, #{times})")
    int getInfant(@Param("bah") String bah, @Param("times") int times);

    @Select("select start_time from ${table} with(nolock) where inpatient_no=#{bah} " +
            "and admiss_times=#{times} and status_flag > '1' and isnull (group_no,'00')='00' " +
            "and order_code in ('06025','06026','06027','06053','05973')")
    Date getDismissDateFromYzActOrder(@Param("table") String table, @Param("bah") String bah, @Param("times") int times);

    @Select("select " +
            "admissTimes=#{times}, " +
            "rtrim(zk_ward) as transDeptCode," +
            "transDept=(select rtrim(name) from zd_unit_code with(nolock) where code=zk_ward)," +
            "rtrim(zk_ward) as dismissDeptCode," +
            "dismissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=isnull(zk_ward,small_dept))," +
            "dismissWardCode=ward,admiss_date, " +
            "dismissWard=(select rtrim(name) from zd_unit_code with(nolock) where code=ward)," +
            "medType=(select top 1 med_type from t_si_pat_info with(nolock) where pat_no=#{bah} and times=#{times} order by ledger_sn desc), " +
            "insutype=(select top 1 insutype from t_si_pat_info with(nolock) where pat_no=#{bah} and times=#{times} order by ledger_sn desc), " +
            "dismissDate=(select start_time from ${yzTable} with(nolock) where inpatient_no=#{bah} " +
            "and admiss_times=#{times} and status_flag > '1' and isnull (group_no,'00')='00' " +
            "and order_code in ('06025','06026','06027','06053','05973')), " +
            "rtrim(small_dept) as admissDeptCode, " +
            "admissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=small_dept), " +
            "rtrim(admiss_ward) as admissWardCode, " +
            "admissWard=(select rtrim(name) from zd_unit_code with(nolock) where code=admiss_ward), " +
            "clinicDiagStr=rtrim(clinic_diag_str), " +
            "clinicDiagCode=rtrim(clinic_diag), " +
            "totalCost=total_charge, " +
            "selfPay=(select sum(last_balance) from zy_ledger_file with(nolock) where inpatient_no=#{bah} and admiss_times=#{times})," +
            "autopsy=rtrim(autopsy_mark)," +
            "hasSurgery=rtrim(operation)," +
            "diagConform1=rtrim(diagnose_conform1)," +
            "diagConform2=rtrim(diagnose_conform2)," +
            "diagConform3=rtrim(diagnose_conform3)," +
            "diagConform4=rtrim(diagnose_conform4)," +
            "diagConform5=rtrim(diagnose_conform5)," +
            "pathologicDiagStr=rtrim(pathology_diag_comment)," +
            "pathologicDiagCode=rtrim(pathology_diag)," +
            "lendFlag=isnull(lend_flag, 0), " +
            "fileStatus=isnull(file_status, 0) " +
            "from ${table} with(nolock) where inpatient_no=#{bah} and admiss_times=#{times}")
    CaseFrontsheetMain getZyActPatient(@Param("table") String table, @Param("yzTable") String yzTable,
                                       @Param("bah") String bah, @Param("times") int times);


    @Select("select " +
            "payMethod=rtrim(pay_method)," +
            "ageDays=rtrim(age_days)," +
            "newBornWeight=rtrim(new_born_weight)," +
            "newBornAdmissWeight=rtrim(new_born_admiss_weight)," +
            "zyAdmissWay=rtrim(rytj)," +
            "nativePlace=rtrim(jg)," +
            "nativePlaceName=(select rtrim(name) from zd_district_code with(nolock) where code=jg)," +
            "hkPlace=rtrim(hkdz)," +
            "hkPlaceName=rtrim(hkdz2)," +
            "contactAddr=rtrim(lxdz)," +
            "contactAddrName=rtrim(lxdz2)," +
            "hkZipCode=rtrim(hkyb) " +
            "from batj_ba1 with(nolock) where bah=#{bah} and zycs=#{times}")
    CaseFrontsheetMain getBatjBa1(@Param("bah") String bah, @Param("times") int times);

    @Select("select " +
            "blh=rtrim(a.blzd_qph)," +
            "allergy=rtrim(a.gm_flag)," +
            "allergicMedicine=rtrim(a.gmwy)," +
            "hbsAg=rtrim(a.hbsag)," +
            "hcvAb=rtrim(a.hcv_vb)," +
            "hivAb=rtrim(a.hiv_vb)," +
            "tpAb=rtrim(a.tp_ab)," +
            "bloodType=rtrim(a.xx)," +
            "rh=rtrim(a.xx_rh)," +
            "deptLeader=rtrim(isnull(kzr, b.dept_director))," +
            "deptLeaderName=(select rtrim(name) from a_employee_mi with(nolock) where code=(isnull(kzr, b.dept_director)))," +
            "leaderDoctor=rtrim(isnull(zrys, b.dept_director))," +
            "leaderDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=(isnull(zrys, b.dept_director)))," +
            "mainDoctor=rtrim(isnull(zzys, b.consult_physician))," +
            "mainDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=(isnull(zzys, b.consult_physician)))," +
            "admissDoctor=rtrim(isnull(zyys, b.refer_physician))," +
            "admissDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=isnull(zyys, b.refer_physician))," +
            "dutyNurse=rtrim(a.job_nurse)," +
            "dutyNurseName=(select rtrim(name) from a_employee_mi with(nolock) where code=a.job_nurse)," +
            "studyDoctor=rtrim(a.jxys)," +
            "studyDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=jxys)," +
            "internshipDoctor=rtrim(a.sxys)," +
            "internshipDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=sxys)," +
            "coder=rtrim(a.bmy)," +
            "coderName=(select rtrim(name) from a_employee_mi with(nolock) where code=bmy)," +
            "qualityControlLevel=rtrim(a.bazl)," +
            "qualityControlDoctor=rtrim(a.zkys)," +
            "qualityControlDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=zkys)," +
            "qualityControlNurse=rtrim(a.zkhs)," +
            "qualityControlNurseName=(select rtrim(name) from a_employee_mi with(nolock) where code=zkhs)," +
            "qualityControlDate=a.barq," +
            "zyDismissWay=rtrim(a.lyfs)," +
            "dismissDestination=rtrim(a.jsjg)," +
            "admissAgainInOneMonth=rtrim(a.zzy_flag)," +
            "admissAgainPurpose=rtrim(a.zzy_md)," +
            "comaDaysBeforeAdmiss=rtrim(a.ryq_hmsj_day)," +
            "comaHoursBeforeAdmiss=rtrim(a.ryq_hmsj_hour)," +
            "comaMinutesBeforeAdmiss=rtrim(a.ryq_hmsj_min)," +
            "comaDaysAfterAdmiss=rtrim(a.ryh_hmsj_day)," +
            "comaHoursAfterAdmiss=rtrim(a.ryh_hmsj_hour)," +
            "comaMinutesAfterAdmiss=rtrim(a.ryh_hmsj_min)," +
            "rescueTimes=rtrim(a.qjcs)," +
            "rescueSuccessTimes=rtrim(a.cgcs)," +
            "caseClassification=rtrim(a.blfx)," +
            "hasIcu=rtrim(a.zzjh)," +
            "icuDays=rtrim(a.zzjh_day)," +
            "icuHours=rtrim(a.zzjh_hour)," +
            "monopathyManagement=rtrim(a.dbz)," +
            "clinicalPathwayManagement=rtrim(a.lclj)," +
            "drgsManagement=rtrim(a.drgs)," +
            "useAntibiotic=rtrim(a.kss)," +
            "bacilliculture=rtrim(a.xjpy)," +
            "statutoryEpidemic=rtrim(a.fdcrb)," +
            "hurtReasonName=rtrim(a.hurt_reason_name)," +
            "hurtReasonCode=rtrim(a.hurt_reason_code)," +
            "tumorLevelT=rtrim(a.tumor_level_t)," +
            "tumorLevelN=rtrim(a.tumor_level_n)," +
            "tumorLevelM=rtrim(a.tumor_level_m)," +
            "xxeApgar=rtrim(a.apgar) " +
            "from batj_ba2 a with(nolock), zy_actpatient b with(nolock) where " +
            "b.inpatient_no=a.bah and " +
            "a.bah=#{bah} and a.zycs=#{times}")
    CaseFrontsheetMain getBatjBa2ForInPatient(@Param("bah") String bah, @Param("times") int times);

    @Select("select " +
            "lateFlag=(select late_flag from t_case_frontsheet_main t where t.bah=#{bah} and t.admiss_times=#{times}), " +
            "fileStatus=(select file_status from t_case_frontsheet_main t where t.bah=#{bah} and t.admiss_times=#{times}), " +
            "signDate=(select sign_date from t_case_frontsheet_main t where t.bah=#{bah} and t.admiss_times=#{times}), " +
            "blh=rtrim(a.blzd_qph)," +
            "allergy=rtrim(a.gm_flag)," +
            "allergicMedicine=rtrim(a.gmwy)," +
            "hbsAg=rtrim(a.hbsag)," +
            "hcvAb=rtrim(a.hcv_vb)," +
            "hivAb=rtrim(a.hiv_vb)," +
            "tpAb=rtrim(a.tp_ab)," +
            "bloodType=rtrim(a.xx)," +
            "rh=rtrim(a.xx_rh)," +
            "deptLeader=rtrim(isnull(kzr, b.dept_director))," +
            "deptLeaderName=(select rtrim(name) from a_employee_mi with(nolock) where code=(isnull(kzr, b.dept_director)))," +
            "leaderDoctor=rtrim(isnull(zrys, b.dept_director))," +
            "leaderDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=(isnull(zrys, b.dept_director)))," +
            "mainDoctor=rtrim(isnull(zzys, b.consult_physician))," +
            "mainDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=(isnull(zzys, b.consult_physician)))," +
            "admissDoctor=rtrim(isnull(zyys, b.refer_physician))," +
            "admissDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=isnull(zyys, b.refer_physician))," +
            "dutyNurse=rtrim(a.job_nurse)," +
            "dutyNurseName=(select rtrim(name) from a_employee_mi with(nolock) where code=a.job_nurse)," +
            "studyDoctor=rtrim(a.jxys)," +
            "studyDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=jxys)," +
            "internshipDoctor=rtrim(a.sxys)," +
            "internshipDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=sxys)," +
            "coder=rtrim(a.bmy)," +
            "coderName=(select rtrim(name) from a_employee_mi with(nolock) where code=bmy)," +
            "qualityControlLevel=rtrim(a.bazl)," +
            "qualityControlDoctor=rtrim(a.zkys)," +
            "qualityControlDoctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=zkys)," +
            "qualityControlNurse=rtrim(a.zkhs)," +
            "qualityControlNurseName=(select rtrim(name) from a_employee_mi with(nolock) where code=zkhs)," +
            "qualityControlDate=a.barq," +
            "zyDismissWay=rtrim(a.lyfs)," +
            "dismissDestination=rtrim(a.jsjg)," +
            "admissAgainInOneMonth=rtrim(a.zzy_flag)," +
            "admissAgainPurpose=rtrim(a.zzy_md)," +
            "comaDaysBeforeAdmiss=rtrim(a.ryq_hmsj_day)," +
            "comaHoursBeforeAdmiss=rtrim(a.ryq_hmsj_hour)," +
            "comaMinutesBeforeAdmiss=rtrim(a.ryq_hmsj_min)," +
            "comaDaysAfterAdmiss=rtrim(a.ryh_hmsj_day)," +
            "comaHoursAfterAdmiss=rtrim(a.ryh_hmsj_hour)," +
            "comaMinutesAfterAdmiss=rtrim(a.ryh_hmsj_min)," +
            "rescueTimes=rtrim(a.qjcs)," +
            "rescueSuccessTimes=rtrim(a.cgcs)," +
            "caseClassification=rtrim(a.blfx)," +
            "hasIcu=rtrim(a.zzjh)," +
            "icuDays=rtrim(a.zzjh_day)," +
            "icuHours=rtrim(a.zzjh_hour)," +
            "monopathyManagement=rtrim(a.dbz)," +
            "clinicalPathwayManagement=rtrim(a.lclj)," +
            "drgsManagement=rtrim(a.drgs)," +
            "useAntibiotic=rtrim(a.kss)," +
            "bacilliculture=rtrim(a.xjpy)," +
            "statutoryEpidemic=rtrim(a.fdcrb)," +
            "hurtReasonName=rtrim(a.hurt_reason_name)," +
            "hurtReasonCode=rtrim(a.hurt_reason_code)," +
            "tumorLevelT=rtrim(a.tumor_level_t)," +
            "tumorLevelN=rtrim(a.tumor_level_n)," +
            "tumorLevelM=rtrim(a.tumor_level_m)," +
            "xxeApgar=rtrim(a.apgar) " +
            "from batj_ba2 a with(nolock), zy_inactpatient b with(nolock) where " +
            "b.inpatient_no=a.bah and b.admiss_times=a.zycs and " +
            "a.bah=#{bah} and a.zycs=#{times}")
    CaseFrontsheetMain getBatjBa2ForOutPatient(@Param("bah") String bah, @Param("times") int times);

    @Select("select no=rtrim(dis_diag_no), name=rtrim(dis_diag_comment), " +
            "code=rtrim(dis_diag), admissStatus=rtrim(admiss_status), " +
            "dismissStatus=rtrim(dis_diag_status) from ba_first_page1 with(nolock) " +
            "where inpatient_no=#{bah} and admiss_times=#{times}")
    List<CaseFrontsheetDisdiag> getDisdiags(@Param("bah") String bah, @Param("times") int times);

    @Select("select no=rtrim(ssxh)," +
            "name=rtrim(ssmc)," +
            "date=ssrq, " +
            "op_end_date,anst_start_date,anst_end_date, " +
            "level=rtrim(ssjb)," +
            "code=rtrim(ssbm)," +
            "operator=rtrim(ssys)," +
            "operatorName=(select rtrim(name) from a_employee_mi with(nolock) where code=ssys)," +
            "assistantOne=rtrim(sszs1)," +
            "assistantOneName=(select rtrim(name) from a_employee_mi with(nolock) where code=sszs1)," +
            "assistantTwo=rtrim(sszs2)," +
            "assistantTwoName=(select rtrim(name) from a_employee_mi with(nolock) where code=sszs2)," +
            "cut=rtrim(qkjb), " +
            "heal=rtrim(yhqk), " +
            "anaesthesia=rtrim(mzff)," +
            "anaesthesiaor=rtrim(mzys)," +
            "anaesthesiaorName=(select rtrim(name) from a_employee_mi with(nolock) where code=mzys) " +
            "from batj_ba4 with(nolock) " +
            "where bah=#{bah} and zycs=#{times}")
    List<CaseFrontsheetSurgery> getSurgeries(@Param("bah") String bah, @Param("times") int times);

    @Select("select (select count(1) from yz_act_order with(nolock) where order_code in ('06026','06053','06027') " +
            "and inpatient_no=#{bah} and admiss_times=#{times}) + " +
            "(select count(1) from yz_inact_order with(nolock) where order_code in ('06026','06053','06027') and " +
            "inpatient_no=#{bah} and admiss_times=#{times})")
    int getDismissOrder(@Param("bah") String bah, @Param("times") int times);

    @Select("select (select count(1) from yz_act_order with(nolock) where order_code='05973' and inpatient_no=#{bah} " +
            "and admiss_times=#{times}) + (select count(1) from yz_inact_order with(nolock) where order_code='05973' " +
            "and inpatient_no=#{bah} and admiss_times=#{times})")
    int getDeathOrder(@Param("bah") String bah, @Param("times") int times);

    @Update("update a_patient_mi set name=#{name}, " +
            "sex=#{sex}, " +
            "health_card_no=#{healthCardNo}," +
            "birth_date=#{birthDate}, " +
            "country=#{country}, " +
            "birth_place=#{birthPlace}, " +
            "nation_code=#{nation}, " +
            "social_no=#{socialNo}, " +
            "occupation_code=#{occupation}, " +
            "marry_code=#{marriage}, " +
            "home_street=#{livePlace}, " + // max 32 char
            "home_tel=#{phone}, " +
            "home_zipcode=#{addrZipCode}, " +
            "account_street=#{hkPlaceName}, " + // max 20 char
            "account_zipcode=#{hkZipCode}, " +
            "employer_name=#{unitName}, " +
            "employer_street=#{unitPlace}, " + // max 32 char
            "employer_tel=#{unitPhone}, " +
            "employer_zipcode=#{unitZipCode}, " +
            "relation_name=#{contactName}, " +
            "relation_code=#{contactRelation}, " +
            "relation_street=#{contactAddrName}, " + // max 32 char
            "relation_tel=#{contactPhone} " +
            "where inpatient_no=#{bah}")
    void updateAPatientMi(CaseFrontsheetMain sheet);

    @Update("update a_patient_mi set province_code=#{province},city_code=#{city},district_code=#{district} " +
            "where inpatient_no=#{bah}")
    void updateAddressCascader(@Param("bah") String bah,
                               @Param("province") Integer province,
                               @Param("city") Integer city,
                               @Param("district") Integer district);

    @Update("update zy_actpatient set autopsy_mark=#{autopsy},clinic_diag_str=#{clinicDiagStr}, " +
            "diagnose_conform1=#{dc1},diagnose_conform2=#{dc2},diagnose_conform3=#{dc3}," +
            "diagnose_conform4=#{dc4}, diagnose_conform5=#{dc5}, operation=#{hasSurgery}," +
            "pathology_diag_comment=#{pathologicDiagStr},pathology_diag=#{pathologicDiagCode}, " +
            "clinic_diag=#{clinicDiagCode} where inpatient_no=#{bah} and admiss_times=#{times}")
    void updateZyActPatient(@Param("bah") String bah, @Param("times") int times,
                            @Param("autopsy") String autopsy,
                            @Param("dc1") String dc1, @Param("dc2") String dc2,
                            @Param("dc3") String dc3, @Param("dc4") String dc4,
                            @Param("dc5") String dc5, @Param("hasSurgery") String hasSurgery,
                            @Param("pathologicDiagStr") String pathologicDiagStr,
                            @Param("pathologicDiagCode") String pathologicDiagCode,
                            @Param("clinicDiagCode") String clinicDiagCode,
                            @Param("clinicDiagStr") String clinicDiagStr);

    @Update("update zy_inactpatient set autopsy_mark=#{autopsy},clinic_diag_str=#{clinicDiagStr}, " +
            "diagnose_conform1=#{dc1},diagnose_conform2=#{dc2},diagnose_conform3=#{dc3}," +
            "diagnose_conform4=#{dc4}, diagnose_conform5=#{dc5}, operation=#{hasSurgery}," +
            "pathology_diag_comment=#{pathologicDiagStr},pathology_diag=#{pathologicDiagCode}, " +
            "clinic_diag=#{clinicDiagCode} where inpatient_no=#{bah} and admiss_times=#{times}")
    void updateZyInActPatient(@Param("bah") String bah, @Param("times") int times,
                              @Param("autopsy") String autopsy,
                              @Param("dc1") String dc1, @Param("dc2") String dc2,
                              @Param("dc3") String dc3, @Param("dc4") String dc4,
                              @Param("dc5") String dc5, @Param("hasSurgery") String hasSurgery,
                              @Param("pathologicDiagStr") String pathologicDiagStr,
                              @Param("pathologicDiagCode") String pathologicDiagCode,
                              @Param("clinicDiagCode") String clinicDiagCode,
                              @Param("clinicDiagStr") String clinicDiagStr);

    @Select("select count(1) from batj_ba1 with(nolock) where bah=#{bah} and zycs=#{times}")
    int isBatjBa1Exist(@Param("bah") String bah, @Param("times") int times);

    @Select("select count(1) from batj_ba2 with(nolock) where bah=#{bah} and zycs=#{times}")
    int isBatjBa2Exist(@Param("bah") String bah, @Param("times") int times);

    @Insert("insert into batj_ba1 (bah, zyh, zycs, xm, ryrq, ryks, rybf) values " +
            "(#{bah}, #{bah}, #{times}, #{name}, #{ryrq}, " +
            "(select code from zd_unit_code with(nolock) where name=#{dept}), " +
            "(select code from zd_unit_code with(nolock) where name=#{ward}))")
    void createBatjBa1(@Param("bah") String bah, @Param("times") int times,
                       @Param("name") String name, @Param("ryrq") Date ryrq,
                       @Param("dept") String dept, @Param("ward") String ward);

    @Insert("insert into batj_ba2 (bah, zyh, zycs) values (#{bah}, #{bah}, #{times})")
    void createBatjBa2(@Param("bah") String bah, @Param("times") int times);

    @Update("update batj_ba1 set " +
            "pay_method=#{payMethod}," +
            "age_days=#{ageDays}," +
            "jg=#{nativePlace}," +
            "new_born_weight=#{newBornWeight}," +
            "new_born_admiss_weight=#{newBornAdmissWeight}," +
            "xm=#{name}, " +
            "xb=#{sex}, " +
            "csrq=#{birthDate}, " +
            "hyzk=#{marriage}, " +
            "zy=#{occupation}, " +
            "csd=#{birthPlace}, " +
            "mz=#{nation}, " +
            "gj=#{country}, " +
            "sfzh=#{socialNo}, " +
            "gzdw=#{unitName}, " +
            "gzdh=#{unitPhone}, " +
            "gzyb=#{unitZipCode}, " +
            "hkdz=#{hkPlace}, " +
            "hkdz2=#{hkPlaceName}," +
            "hkyb=#{hkZipCode}, " +
            "lxr=#{contactName}, " +
            "lxrgx=#{contactRelation}, " +
            "lxdh=#{contactPhone}, " +
            "lxdz=#{contactAddr}, " +
            "lxdz2=#{contactAddrName}," +
            "rytj=#{zyAdmissWay}, " +
            "xzz=#{livePlace}, " +
            "xdh=#{phone} " +
            "where bah=#{bah} and zycs=#{admissTimes}")
    void updateBatjBa1(CaseFrontsheetMain info);

    @Update("update batj_ba2 set " +
            "blzd_qph=#{blh}," +
            "gm_flag=#{allergy}," +
            "gmwy=#{allergicMedicine}," +
            "hbsag=#{hbsAg}," +
            "hcv_vb=#{hcvAb}," +
            "hiv_vb=#{hivAb}," +
            "tp_ab=#{tpAb}," +
            "xx=#{bloodType}," +
            "xx_rh=#{rh}," +
            "kzr=#{deptLeader}," +
            "zrys=#{leaderDoctor}," +
            "zzys=#{mainDoctor}," +
            "zyys=#{admissDoctor}," +
            "job_nurse=#{dutyNurse}," +
            "jxys=#{studyDoctor}," +
            "sxys=#{internshipDoctor}," +
            "bmy=#{coder}," +
            "bazl=#{qualityControlLevel}," +
            "zkys=#{qualityControlDoctor}," +
            "zkhs=#{qualityControlNurse}," +
            "barq=#{qualityControlDate}," +
            "lyfs=#{zyDismissWay}," +
            "jsjg=#{dismissDestination}," +
            "zzy_flag=#{admissAgainInOneMonth}," +
            "zzy_md=#{admissAgainPurpose}," +
            "ryq_hmsj=#{comaDaysBeforeAdmiss}," +
            "ryh_hmsj=#{comaDaysAfterAdmiss}," +
            "qjcs=#{rescueTimes}," +
            "cgcs=#{rescueSuccessTimes}," +
            "blfx=#{caseClassification}," +
            "zzjh=#{hasIcu}," +
            "zzjh_day=#{icuDays}," +
            "zzjh_hour=#{icuHours}," +
            "dbz=#{monopathyManagement}," +
            "lclj=#{clinicalPathwayManagement}," +
            "drgs=#{drgsManagement}," +
            "kss=#{useAntibiotic}," +
            "xjpy=#{bacilliculture}," +
            "fdcrb=#{statutoryEpidemic}," +
            "hurt_reason_name=#{hurtReasonName}," +
            "hurt_reason_code=#{hurtReasonCode}," +
            "tumor_level_t=#{tumorLevelT}," +
            "tumor_level_n=#{tumorLevelN}," +
            "tumor_level_m=#{tumorLevelM}," +
            "apgar=#{xxeApgar} " +
            "where bah=#{bah} and zycs=#{admissTimes}")
    void updateBatjBa2(CaseFrontsheetMain info);

    @Delete("delete from ba_first_page1 where inpatient_no=#{bah} and admiss_times=#{times}")
    void deleteOldDisdiag(@Param("bah") String bah, @Param("times") int times);

    @Delete("delete from batj_ba4 where bah=#{bah} and zycs=#{times}")
    void deleteOldSurgeryRecord(@Param("bah") String bah, @Param("times") int times);

    @Insert("insert into ba_first_page1 (inpatient_no, admiss_times, dis_diag_no," +
            "dis_diag_comment, dis_diag, admiss_status, dis_diag_status, op_id_code) " +
            "values (#{bah}, #{times}, #{no}, #{name}, #{code}, #{admissStatus}, " +
            "#{dismissStatus}, #{opIdCode})")
    void writeNewDisdiag(CaseFrontsheetDisdiag disDiag);

    @Insert("insert into batj_ba4 (bah,zyh,zycs,ssxh,ssmc,ssrq,ssjb,ssbm,ssys,sszs1,sszs2,qkjb,yhqk," +
            "mzff,mzys,op_start_date,op_end_date,anst_start_date,anst_end_date) " +
            "values (#{bah},#{bah},#{times},#{no},#{name},#{date},#{level},#{code},#{operator},#{assistantOne}," +
            "#{assistantTwo},#{cut},#{heal},#{anaesthesia},#{anaesthesiaor},#{date},#{opEndDate},#{anstStartDate},#{anstEndDate})")
    void writeNewZySurgeryRecord(CaseFrontsheetSurgery surgery);

    @Select("select status from t_frontsheet_sign_apply where bah=#{bah} and times=#{times} ")
    Integer getSignApplyStatus(@Param("bah") String bah, @Param("times") int times);

    @Insert("insert into t_frontsheet_sign_apply (bah, times, name, gender, apply_staff) " +
            "values (#{bah}, #{times}, #{name}, #{gender}, #{staff})")
    void insertNewSignApply(@Param("bah") String bah, @Param("times") int times,
                            @Param("name") String name, @Param("gender") String gender,
                            @Param("staff") String staff);

    @Update("update t_frontsheet_sign_apply set status=1, sign_staff=#{staff}, sign_datetime=getdate() " +
            "where bah=#{bah} and times=#{times}")
    void updateSignApply(@Param("bah") String bah, @Param("times") int times, @Param("staff") String staff);

    @Insert("insert into ba_op_log (op_type, op_id, op_time, inpatient_no, admiss_times) values " +
            "(#{opType}, #{staffId}, getDate(), #{bah}, #{admissTimes})")
    void writeBaOpLog(@Param("opType") int opType,
                      @Param("staffId") String staffId,
                      @Param("bah") String bah,
                      @Param("admissTimes") int admissTimes);

    @Select("select rtrim(dis_diag_no) no, rtrim(dis_diag) code, " +
            "rtrim(dis_diag_comment) name, rtrim(dis_diag_status) cyzg, " +
            "rtrim(op_id_code) operId, convert(varchar(19), op_diag_date, 21) opDate, " +
            "diagType='13',si_diag_type,bzfx='普通', admiss_cond " +
            "from zy_dis_diag_yb with(nolock) where inpatient_no=#{bah} and admiss_times=#{times}")
    List<YiBaoDisdiag> getYbDiags(@Param("bah") String bah, @Param("times") int times);

    @Delete("delete from zy_dis_diag_yb where inpatient_no=#{bah} and admiss_times=#{times}")
    void deleteOldYbDiag(@Param("bah") String bah, @Param("times") int times);

    @Insert("insert into zy_dis_diag_yb (inpatient_no, admiss_times, dis_diag_no, dis_diag_type, dis_diag, " +
            "dis_diag_comment, op_id_code, op_diag_date, dis_diag_bzfx, dis_diag_status, si_diag_type, admiss_cond) " +
            "values (#{bah},#{times},#{no},13,#{code},#{name},#{operId},#{opDate},'普通',#{cyzg},#{siDiagType},#{admissCond})    ")
    void insertNewYbDiag(YiBaoDisdiag diag);

    @Update("update zy_actpatient set lend_flag=1 where inpatient_no=#{bah} " +
            "and admiss_times=#{times}")
    void lendZyOut(@Param("bah") String bah, @Param("times") int times);

    @Update("update zy_inactpatient set lend_flag=1 where inpatient_no=#{bah} " +
            "and admiss_times=#{times}")
    void lendZyInOut(@Param("bah") String bah, @Param("times") int times);

    @Insert("insert into ba_op_log (op_type, op_id, op_time, inpatient_no, " +
            "admiss_times, borrow_id, borrow_date) values (5, #{staffId}, getDate(), #{bah}, " +
            "#{admissTimes}, #{borrowId}, #{borrowDate})")
    void writeLendOutLog(@Param("staffId") String staffId,
                         @Param("bah") String bah,
                         @Param("admissTimes") int admissTimes,
                         @Param("borrowId") String borrowId,
                         @Param("borrowDate") Date borrowDate);

    @Update("update zy_actpatient set lend_flag=2 where inpatient_no=#{bah} " +
            "and admiss_times=#{times}")
    void lendZyBack(@Param("bah") String bah, @Param("times") int times);

    @Update("update zy_inactpatient set lend_flag=2 where inpatient_no=#{bah} " +
            "and admiss_times=#{times}")
    void lendZyInBack(@Param("bah") String bah, @Param("times") int times);

    @Insert("insert into ba_op_log (op_type, op_id, op_time, inpatient_no, " +
            "admiss_times, borrow_id, borrow_date) values (6, #{staffId}, getDate(), #{bah}, " +
            "#{admissTimes}, #{borrowId}, #{borrowDate})")
    void writeLendBackLog(@Param("staffId") String staffId,
                          @Param("bah") String bah,
                          @Param("admissTimes") int admissTimes,
                          @Param("borrowId") String borrowId,
                          @Param("borrowDate") Date borrowDate);

    @Select("select top 1 borrow_id, " +
            "borrow_name=(select rtrim(name) from a_employee_mi with(nolock) where code=borrow_id), " +
            "borrow_date from ba_op_log with(nolock) where op_type=5 and inpatient_no=#{bah} and admiss_times=#{times} " +
            "order by borrow_date desc")
    BorrowBase getBorrowInfo(@Param("bah") String bah, @Param("times") int times);

    @Select("select count(1) from zy_inactpatient a with(nolock), batj_ba2 b with(nolock) " +
            "where (dis_dept=#{ward} or dis_ward=#{ward}) and " +
            "b.bah=a.inpatient_no and " +
            "b.zycs=a.admiss_times and " +
            "b.blfx>2")
    Integer getCDBlfxCount(@Param("ward") String ward);

    @Select("select count(1) from zy_inactpatient with(nolock) where dis_dept=#{ward} or dis_ward=#{ward}")
    Integer getAllDisPatientCount(@Param("ward") String ward);

    @Select("<script>" +
            "select distinct " +
            "a.bah,a.admiss_times as times, " +
            "a.name,a.sex,fileStatus=1, " +
            "a.admiss_doctor_name as doctorName, " +
            "a.sign_date, " +
            "a.dismiss_date," +
            "a.admiss_dept, " +
            "a.dismiss_dept, " +
            "a.case_classification as blfx " +
            "from t_case_frontsheet_main a with(nolock), " +
            "t_case_frontsheet_disdiag c with(nolock), " +
            "t_case_frontsheet_surgery d with(nolock) " +
            "where " +
            "a.dismiss_date&gt;=#{start} and " +
            "a.dismiss_date&lt;=#{end} and " +
            "<if test=\"blfxChkbox!=null and blfxChkbox.size>0 \">" +
            "a.case_classification in " +
            "<foreach collection='blfxChkbox' item='item' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach> and " +
            "</if>" +
            "<if test=\"medType!=null and medType!='' \">" +
            "a.med_type=#{medType} and " +
            "</if>" +
            "<if test=\"doctorCode!=null and doctorCode!='' \">" +
            "a.admiss_doctor=#{doctorCode} and " +
            "</if>" +
            "<if test=\"sex!=null and sex!='' \">" +
            "a.sex=#{sex} and " +
            "</if>" +
            "<if test=\"icdCode!=null and icdCode!='' \">" +
            "c.code=#{icdCode} and " +
            "</if>" +
            "<if test=\"surgeryCode!=null and surgeryCode!='' \">" +
            "d.code=#{surgeryCode} and " +
            "</if>" +
            "c.bah=a.bah and " +
            "c.times=a.admiss_times and " +
            "d.bah=a.bah and " +
            "d.times=a.admiss_times " +
            "</script>")
    List<SheetOverview> advanceSearch(AdvanceSearchParam param);

    @Select("select file_status from t_case_frontsheet_main with(nolock) where bah=#{bah} and admiss_times=#{admissTimes}")
    Integer selectFileStatus(@Param("bah") String bah,
                             @Param("admissTimes") Integer admissTimes);

    @Select("select count(1) from t_yb_setl_modify_req where pat_no=#{bah} and times=#{times}")
    Integer selectModified(@Param("bah") String bah, @Param("times") Integer times);

    @Select("select top 1 * from t_si_disefamily_diagnose with(nolock) where dise_code=#{code} ")
    DisefamilyGrade selectDisefamilyGrade(@Param("code") String code);

    @Select("select yb_code from zd_icd9_cm3 where code in " +
            "(select ssbm from ${table} with(nolock) where zyh=#{bah} and zycs=#{times} and isnull(ssbm,'')!='') " +
            "union select ssbm from ${table} with(nolock) where zyh=#{bah} and zycs=#{times} and isnull(ssbm,'')!=''")
    List<String> selectPatientSurgeryCodes(@Param("bah") String bah, @Param("times") Integer times, @Param("table") String table);

    @Select("select * from t_si_operation_disefamily with(nolock) where disefamily_code=#{code}")
    List<OprnDisefamilyGrade> selectOperationDisefamilies(@Param("code") String code);

    @Update("update zd_icd_code set weight=(weight+1) where code=#{code}")
    void increaseDiagWeight(@Param("code") String code);

    @Select("select * from t_case_frontsheet_main where bah=#{bah} and admiss_times=#{times}")
    CaseFrontsheetMain selectSignedSheetMain(@Param("bah") String bah, @Param("times") Integer times);

    @Select("select * from t_case_frontsheet_disdiag where bah=#{bah} and times=#{times} ")
    List<CaseFrontsheetDisdiag> selectSignedSheetDiags(@Param("bah") String bah, @Param("times") Integer times);

    @Select("select * from t_case_frontsheet_surgery where bah=#{bah} and times=#{times} ")
    List<CaseFrontsheetSurgery> selectSignedSheetSurgeries(@Param("bah") String bah, @Param("times") Integer times);

    @Update("update t_case_frontsheet_main set age_days=#{agedays} where bah=#{bah} and admiss_times=#{times}")
    void updateNewBornAge(@Param("bah") String bah, @Param("times") Integer times, @Param("agedays") Integer agedays);

    @Select("select rtrim(yb_code) as code, rtrim(yb_name) as name from zd_icd_code_new where code=#{code}")
    CodeName selectSiDiagByBaDiag(@Param("code") String code);

    @Select("select province_code,city_code,district_code," +
            "provinceName=(select name from t_region where code=province_code), " +
            "cityName=(select name from t_region where code=city_code), " +
            "districtName=(select name from t_region where code=district_code) " +
            "from a_patient_mi where inpatient_no=#{bah}")
    StandardAddressMember selectStandardAddressMember(@Param("bah") String bah);

    @Select("select rtrim(social_no) from a_patient_mi where inpatient_no=#{bah}")
    String selectSocialNo(@Param("bah") String bah);

    @Select("select parent_code as code, name=(select t.name from t_region t where t.code=a.parent_code) " +
            "from t_region a where code=#{region}")
    Region selectParentRegion(@Param("region") Integer region);

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

    @Select("select med_type from t_si_pat_info where pat_no=#{patNo} and times=#{times} and ledger_sn=" +
            "(select max(t.ledger_sn) from zy_ledger_file t where t.inpatient_no=#{patNo} and t.admiss_times=#{times})")
    String selectMedType(@Param("patNo") String patNo,
                         @Param("times") int times);

    @Select("select provinceName=(select name from t_region where code=#{province}), " +
            "cityName=(select name from t_region where code=#{city}), " +
            "districtName=(select name from t_region where code=#{district}) ")
    StandardAddressMember selectStandardAddressMember2(@Param("province") int province,
                                                       @Param("city") int city,
                                                       @Param("district") int district);
}