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 java.util.Date;
import java.util.List;

/**
 * @author dj
 */
@Mapper
public interface CaseFrontSheetDao extends BaseMapper<CaseFrontsheetMain> {
    @Select("select " +
            "bedNo=rtrim(bed_no)," +
            "bah=rtrim(inpatient_no)," +
            "times=rtrim(admiss_times)," +
            "name=rtrim(name)," +
            "sex," +
            "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)," +
            "doctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=refer_physician), " +
            "sex " +
            "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 " +
            "admissTimes=#{times}, " +
            "transDept=(select rtrim(name) from zd_unit_code with(nolock) where code=zk_ward)," +
            "dismissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=isnull(isnull(zk_dept,zk_ward),small_dept))," +
            "dismissWard=(select rtrim(name) from zd_unit_code with(nolock) where code=isnull(zk_ward,ward))," +
            "admiss_date, med_type=(select top 1 med_type from t_si_pat_info with(nolock) where pat_no=#{bah} and times=#{times} order by ledger_sn desc)," +
            "dismissDate=(select start_time from yz_act_order 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')), " +
            "admissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=small_dept), " +
            "zk_ward zkWardCode, " +
            "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(total_charge) - sum(isnull(charge_yb,0)) 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)," +
            "fileStatus=isnull(file_status, 0), " +
            "lendFlag=isnull(lend_flag, 0), " +
            "balance=(select balance+charge_yb from zy_ledger_file with(nolock) where inpatient_no=#{bah} " +
            "and admiss_times=#{times} and ledger_sn=(select max(ledger_sn) from zy_ledger_file with(nolock) " +
            "where inpatient_no=#{bah} and admiss_times=#{times})) " +
            "from zy_actpatient with(nolock) where inpatient_no=#{bah}")
    CaseFrontsheetMain getZyActPatient(@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 yz_inact_order 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 getDismissDateFromYzInActOrder(@Param("bah") String bah, @Param("times") int times);

    @Select("select " +
            "admissTimes=#{times}, " +
            "transDept=(select rtrim(name) from zd_unit_code with(nolock) where code=zk_ward)," +
            "dismissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=isnull(isnull(zk_dept,zk_ward),small_dept))," +
            "dismissWard=(select rtrim(name) from zd_unit_code with(nolock) where code=isnull(zk_ward,ward))," +
            "admiss_date, med_type=(select top 1 med_type from t_si_pat_info with(nolock) where pat_no=#{bah} and times=#{times} order by ledger_sn desc), " +
            "dismissDate=(select start_time from yz_inact_order 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')), " +
            "admissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=small_dept), " +
            "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 zy_inactpatient with(nolock) where inpatient_no=#{bah} and admiss_times=#{times}")
    CaseFrontsheetMain getZyInActPatient(@Param("bah") String bah, @Param("times") int times);

    @Select("select start_time from yz_act_order 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("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 " +
            "late_flag lateFlag, " +
            "signDate=(convert(varchar(19), sign_date, 21)), " +
            "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," +
            "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(*) from yz_act_order with(nolock) where order_code in ('06026','06053','06027') " +
            "and inpatient_no=#{bah} and admiss_times=#{times}) + " +
            "(select count(*) 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(*) from yz_act_order with(nolock) where order_code='05973' and inpatient_no=#{bah} " +
            "and admiss_times=#{times}) + (select count(*) 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 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(*) from batj_ba1 with(nolock) where bah=#{bah} and zycs=#{times}")
    int isBatjBa1Exist(@Param("bah") String bah, @Param("times") int times);

    @Select("select count(*) 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) " +
            "values (#{bah}, #{bah}, #{times}, #{no}, #{name}, #{date}, #{level}," +
            " #{code}, #{operator}, #{assistantOne}, #{assistantTwo}, #{cut}, #{heal}, " +
            "#{anaesthesia}, #{anaesthesiaor})")
    void writeNewZySurgeryRecord(CaseFrontsheetSurgery surgery);

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

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

    @Update("update batj_ba2 set late_flag=#{flag}, sign_date=getdate() where zyh=#{bah} and zycs=#{times}")
    void updateLateSubmitFlag(@Param("flag") int flag, @Param("bah") String bah, @Param("times") int times);

    @Update("update batj_ba2 set sign_date=null where zyh=#{bah} and zycs=#{times}")
    void clearSignDate(@Param("bah") String bah, @Param("times") int times);

    @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', 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(*) 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(*) from zy_inactpatient with(nolock) where dis_dept=#{ward} or dis_ward=#{ward}")
    Integer getAllDisPatientCount(@Param("ward") String ward);

    @Select("select " +
            "rtrim(a.inpatient_no) bah, " +
            "a.admiss_times times, " +
            "rtrim(a.name) name, " +
            "a.sex, " +
            "admissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=a.admiss_dept), " +
            "dismissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=a.dis_dept), " +
            "doctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=b.zyys), " +
            "signDate=(convert(varchar(10), b.sign_date, 21)), " +
            "rtrim(b.blfx) blfx, " +
            "a.dis_date," +
            "inDays=(select datediff(day, a.admiss_date, a.dis_date)), " +
            "a.total_charge totalCost " +
            "from zy_inactpatient a with(nolock), batj_ba2 b with(nolock) " +
            "where " +
            "b.zyh=a.inpatient_no and " +
            "b.zycs=a.admiss_times and " +
            "a.dis_date>=#{start} and " +
            "a.dis_date<=#{end} and " +
            "(a.refer_physician like #{doctorCode} or b.zyys like #{doctorCode}) and " +
            "a.sex like #{sex}")
    List<SheetOverview> pureAdvanceSearch(AdvanceSearchParam param);

    @Select("select " +
            "rtrim(a.inpatient_no) bah, " +
            "a.admiss_times times, " +
            "rtrim(a.name) name, " +
            "a.sex, " +
            "doctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=b.zyys), " +
            "signDate=(convert(varchar(10), b.sign_date, 21)), " +
            "a.dis_date," +
            "admissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=a.admiss_dept), " +
            "dismissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=a.dis_dept), " +
            "rtrim(b.blfx) blfx, " +
            "inDays=(select datediff(day, a.admiss_date, a.dis_date)), " +
            "a.total_charge totalCost " +
            "from zy_inactpatient a with(nolock), batj_ba2 b with(nolock), ba_first_page1 c with(nolock), batj_ba4 d with(nolock) " +
            "where " +
            "b.zyh=a.inpatient_no and " +
            "b.zycs=a.admiss_times and " +
            "a.dis_date>=#{start} and " +
            "a.dis_date<=#{end} and " +
            "(a.refer_physician like #{doctorCode} or b.zyys like #{doctorCode}) and " +
            "a.sex like #{sex} and " +
            "c.inpatient_no=a.inpatient_no and " +
            "c.admiss_times=a.admiss_times and " +
            "c.dis_diag like #{icdCode} and " +
            "d.zyh=a.inpatient_no and " +
            "d.zycs=a.admiss_times and " +
            "d.ssbm like #{surgeryCode}")
    List<SheetOverview> maxAdvanceSearch(AdvanceSearchParam param);

    @Select("select " +
            "rtrim(a.inpatient_no) bah, " +
            "a.admiss_times times, " +
            "rtrim(a.name) name, " +
            "a.sex, " +
            "doctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=b.zyys), " +
            "signDate=(convert(varchar(10), b.sign_date, 21)), " +
            "a.dis_date," +
            "admissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=a.admiss_dept), " +
            "dismissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=a.dis_dept), " +
            "rtrim(b.blfx) blfx, " +
            "inDays=(select datediff(day, a.admiss_date, a.dis_date)), " +
            "a.total_charge totalCost " +
            "from zy_inactpatient a with(nolock), batj_ba2 b with(nolock), ba_first_page1 c with(nolock) " +
            "where " +
            "b.zyh=a.inpatient_no and " +
            "b.zycs=a.admiss_times and " +
            "a.dis_date>=#{start} and " +
            "a.dis_date<=#{end} and " +
            "(a.refer_physician like #{doctorCode} or b.zyys like #{doctorCode}) and " +
            "a.sex like #{sex} and " +
            "c.inpatient_no=a.inpatient_no and " +
            "c.admiss_times=a.admiss_times and " +
            "c.dis_diag like #{icdCode}")
    List<SheetOverview> advanceSearchWithIcd(AdvanceSearchParam param);

    @Select("select " +
            "rtrim(a.inpatient_no) bah, " +
            "a.admiss_times times, " +
            "rtrim(a.name) name, " +
            "a.dis_date," +
            "a.sex, " +
            "doctorName=(select rtrim(name) from a_employee_mi with(nolock) where code=b.zyys), " +
            "signDate=(convert(varchar(10), b.sign_date, 21)), " +
            "admissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=a.admiss_dept), " +
            "dismissDept=(select rtrim(name) from zd_unit_code with(nolock) where code=a.dis_dept), " +
            "rtrim(b.blfx) blfx, " +
            "inDays=(select datediff(day, a.admiss_date, a.dis_date)), " +
            "a.total_charge totalCost " +
            "from zy_inactpatient a with(nolock), batj_ba2 b with(nolock), batj_ba4 d with(nolock) " +
            "where " +
            "b.zyh=a.inpatient_no and " +
            "b.zycs=a.admiss_times and " +
            "a.dis_date>=#{start} and " +
            "a.dis_date<=#{end} and " +
            "(a.refer_physician like #{doctorCode} or b.zyys like #{doctorCode}) and " +
            "a.sex like #{sex} and " +
            "d.zyh=a.inpatient_no and " +
            "d.zycs=a.admiss_times and " +
            "d.ssbm like #{surgeryCode}")
    List<SheetOverview> advanceSearchWithSurgery(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);

    @Update("update t_case_frontsheet_main set file_status=#{status} where bah=#{bah} and admiss_times=#{admissTimes}")
    void updateFileStatus(@Param("bah") String bah,
                          @Param("admissTimes") Integer admissTimes,
                          @Param("status") int status);

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

    @Select("select ssbm from batj_ba4 with(nolock) where zyh=#{bah} and zycs=#{times} and isnull(ssbm,'')!='' ")
    List<String> selectPatientSurgeryCodes(@Param("bah") String bah, @Param("times") Integer times);

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

    @Select("select rtrim(zk_ward) as deptCode,isnull(file_status,0) as fileStatus, count(1) as dismissCount " +
            "from zy_inactpatient with(nolock) where dis_date>=#{start} and dis_date<=#{end} group by zk_ward, file_status")
    List<TempDismissCount> selectDismissCount(@Param("start") String start,
                                          @Param("end") String end);

    @Select("select rtrim(a.inpatient_no) as patNo,a.admiss_times as times, " +
            "rtrim(a.name) as name,gender=(case when a.sex='1' then '男' else '女' end), " +
            "age=datediff(year,b.birth_date,getdate()),a.admiss_date,a.dis_date, " +
            "inHospDays=datediff(day, a.admiss_date, a.dis_date),a.total_charge, " +
            "rtrim(a.zk_ward) as disDept, " +
            "disDiag=(select dis_diag_comment from ba_first_page1 t where t.inpatient_no=a.inpatient_no " +
            "and t.admiss_times=a.admiss_times and t.dis_diag_no=1), " +
            "blfx=isnull(c.blfx,''),rtrim(a.small_dept) as admissDept,rtrim(a.zk_ward) as zkWard, " +
            "c.kzr,c.zzys,c.zyys,fdcrb=isnull(c.fdcrb,'')," +
            "deadOrder=(select count(1) from yz_inact_order t where t.inpatient_no=a.inpatient_no and " +
            "t.admiss_times=a.admiss_times and t.order_code='05973') " +
            "from zy_inactpatient a, a_patient_mi b, batj_ba2 c where " +
            "a.inpatient_no not like 'S%' and " +
            "a.inpatient_no not like 'JT%' and " +
            "charindex('$',a.inpatient_no)=0 and " +
            "a.dis_date>=#{begntime} and a.dis_date<=#{endtime} and " +
            "a.inpatient_no=b.inpatient_no and a.inpatient_no=c.zyh and a.admiss_times=c.zycs " +
            "order by a.zk_ward")
    List<DismissCount> selectDismissDetailCount(@Param("begntime") String begntime,
                                                @Param("endtime") String endtime);

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