web_server/src/main/java/thyyxxk/webserver/dao/his/inpatient/PatientDao.java

package thyyxxk.webserver.dao.his.inpatient;

import org.apache.ibatis.annotations.*;
import thyyxxk.webserver.entity.dictionary.CodeName;
import thyyxxk.webserver.entity.inpatient.dismiss.ZyDisYbDiag;
import thyyxxk.webserver.entity.inpatient.patient.*;

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

@Mapper
public interface PatientDao {

    @Select("select " +
            "RTRIM(a.bed_no) bedNo, " +
            "RTRIM(a.inpatient_no) inpatientNo, " +
            "a.admiss_times, " +
            "RTRIM(a.name) as name, " +
            "sex=isnull(a.sex, 9), " +
            "mdtrtId=(select b.mdtrt_id from t_si_pat_info b where b.pat_no=a.inpatient_no " +
            "and b.times=a.admiss_times and b.ledger_sn=a.times_billed), " +
            "injurySerialNo=(select serial_no from t_injury_si_pat_info where pat_no=a.inpatient_no " +
            "and times=a.admiss_times and ledger_sn=a.times_billed), " +
            "medType=a.med_type, " +
            "dismissOrder=(select count(1) from yz_act_order b where b.inpatient_no=a.inpatient_no " +
            "and b.admiss_times=a.admiss_times and status_flag > '1' and isnull(group_no,'00')='00' " +
            "and order_code in (select order_code from yz_zd_order_item_confirm where item_name in (N'出院',N'死亡')))," +
            "orderNoCount = (select count(1) " +
            "                    from yz_act_order c " +
            "                    where a.inpatient_no = c.inpatient_no " +
            "                      and c.admiss_times = a.admiss_times " +
            "                      and c.status_flag = '1' " +
            "                      and c.enter_oper = #{userCode}), " +
            "                   rtrim(refer_physician) refer_physician, " +
            "                   (select rtrim(name) from a_employee_mi where code = refer_physician) as refer_physician_name, " +
            "                   rtrim(consult_physician) consult_physician, " +
            "                   (select rtrim(name) from a_employee_mi where code = consult_physician) as consult_physician_name, " +
            "                   rtrim(dept_director) dept_director, " +
            "                   (select rtrim(name) from a_employee_mi where code = dept_director) as dept_director_name, " +
            "                         total_charge, " +
            "                         balance, " +
            "                         rtrim(admiss_diag_str) as  admiss_diag_str, " +
            "                         rtrim(clinic_diag_str) as  clinic_diag_str, " +
            "   responce_type_name = (select rtrim(name) from zy_zd_responce_type where code = responce_type)," +
            "   admiss_date,zkys," +
            "   zkys_name =(select rtrim(name) from a_employee_mi where code = zkys) " +
            "from zy_actpatient a left join batj_ba2 ba on (a.inpatient_no = ba.zyh and a.admiss_times = ba.zycs) " +
            "where a.bed_status <> '0' and a.ward like #{ward} ${sql} " +
            "ORDER BY cast(a.bed_no AS int)")
    List<Overview> getOverView(@Param("ward") String ward,
                               @Param("userCode") String userCode,
                               @Param("sql") String sql);

    @Select("<script>" +
            "select inpatientNo=rtrim(a.inpatient_no),a.admiss_times,a.med_type,b.social_no,dis_date,consult_physician,dept_director," +
            "consult_physician_name = (select name from a_employee_mi where a_employee_mi.code = consult_physician)," +
            "dept_director_name = (select name from a_employee_mi where a_employee_mi.code = dept_director)," +
            "zkys= (select zkys from batj_ba2 ba where ba.zyh = a.inpatient_no and ba.zycs = a.admiss_times)," +
            "mz_no = (select rtrim(mz_no) from a_patient_mi where a_patient_mi.inpatient_no  = a.inpatient_no)," +
            "zk_ward,zk_ward_name = (select rtrim(name) from zd_unit_code where code=zk_ward), " +
            "name=rtrim(a.name),sex=isnull(a.sex, b.sex),act_ipt_days=(datediff(day, admiss_date, isnull(dis_date,getdate()))), " +
            "birthDate=(convert(varchar(10), birth_date, 21)),home_tel=rtrim(b.home_tel), " +
            "admissPhysician=rtrim(a.admiss_physician),b.psn_cert_type, " +
            "insutype=(select top 1 insutype from t_si_pat_info with(nolock) where pat_no=#{inpatientNo} and " +
            "times=a.admiss_times order by ledger_sn desc), " +
            "psnType=(select max(psn_type) from t_si_pat_info with(nolock) where pat_no=#{inpatientNo} and times=a.admiss_times), " +
            "psnIdetType=(select max(psn_idet_type) from t_si_pat_info with(nolock) where pat_no=#{inpatientNo} and times=a.admiss_times), " +
            "admissPhysicianName=rtrim((select name FROM a_employee_mi WHERE code = a.admiss_physician)), " +
            "referPhysician=rtrim(a.refer_physician), " +
            "referPhysicianName=rtrim((select name FROM a_employee_mi WHERE code = a.refer_physician)), " +
            "admissDiagStr=rtrim(isnull(a.admiss_diag_str,'')), " +
            "clinicDiagStr=rtrim(isnull(a.clinic_diag_str,'')), " +
            "admissWard=rtrim(isnull(a.ward,a.admiss_ward)), " +
            "admissWardName=(select rtrim(name) from zd_unit_code where code=a.ward), " +
            "admissDept=rtrim(a.admiss_dept), a.matn_type,smallDept=rtrim(a.small_dept), " +
            "smallDeptName=rtrim((select name from zd_unit_code where code=small_dept)), " +
            "admiss_date,ybRegisterDate=isnull(yb_register_date, admiss_date), " +
            "responceType=a.med_type,a.dise_code,a.dise_name, " +
            "zkWard=rtrim(a.zk_ward),ward=(a.ward),a.latechb_flag,a.pret_flag,ledgerSn=a.times_billed, " +
            "admissStatus=rtrim(a.admiss_status), " +
            "dismissOrder=(select count(1) from yz_act_order y where y.inpatient_no=b.inpatient_no  " +
            "and y.admiss_times=a.admiss_times and status_flag>'1' and isnull(group_no,'00')='00' " +
            "and order_code in (select order_code from yz_zd_order_item_confirm where item_name in (N'出院',N'死亡'))),  " +
            "disDiagStatus=(select RTRIM(dis_diag_status) from zy_dis_diag_yb where inpatient_no=a.inpatient_no " +
            "and admiss_times=a.admiss_times and dis_diag_no=1), " +
            "operation=rtrim(a.operation),a.zy_serial_no, " +
            "bedNo=rtrim(a.bed_no),a.admdvs,a.exp_content, " +
            "nursingLevel=(select top 1 z.order_name from yz_act_order z where " +
            "z.inpatient_no=a.inpatient_no and z.admiss_times=a.admiss_times and z.order_name like '%级护理' and status_flag in ('3', '4') order by z.start_time desc), " +
            "dutyNurse=(select rtrim(job_nurse) from batj_ba2 t where t.zyh=a.inpatient_no and t.zycs=a.admiss_times), " +
            "rtrim(b.country) as country, rtrim(b.nation_code) as nation," +
            "rtrim(b.relation_name) as contactName, rtrim(relation_code) as contactRelation, " +
            "rtrim(b.relation_street) as contactAddrName, rtrim(relation_tel) as contactPhone, " +
            "mdtrtId=(select mdtrt_id from t_si_pat_info where pat_no=a.inpatient_no and times=a.admiss_times and ledger_sn=a.times_billed), " +
            "injurySerialNo=(select serial_no from t_injury_si_pat_info where pat_no=a.inpatient_no " +
            "and times=a.admiss_times and ledger_sn=a.times_billed), " +
            "chargeYb=(select isnull(charge_yb, '0.00') from zy_ledger_file where inpatient_no=a.inpatient_no " +
            "and admiss_times=a.admiss_times and ledger_sn=a.times_billed), " +
            "totalCharge=(select rtrim(isnull(total_charge, '0.00')) from zy_ledger_file where inpatient_no=a.inpatient_no " +
            "and admiss_times=a.admiss_times and ledger_sn=a.times_billed), " +
            "isnull(final_control,0) as final_control FROM ${table} a with(nolock), " +
            "a_patient_mi b with(nolock) WHERE a.inpatient_no=#{inpatientNo} " +
            "<if test=\"times != null and times != 0 \">" +
            "and a.admiss_times =#{times} " +
            "</if>" +
            " AND b.inpatient_no=a.inpatient_no" +
            "</script>")
    Patient getPatientInfo(@Param("inpatientNo") String inpatientNo, @Param("table") String table, @Param("times") Integer times);

    @Select("select max(insuplc_admdvs) from t_si_pat_info where pat_no=#{patNo} " +
            "and times=#{times} and ledger_sn=#{ledgerSn}")
    Integer getPatAdmdvs(String patNo, int times, int ledgerSn);

    @Select("select parent_code from t_region where code=#{code}")
    Integer selectParentRegion(Integer code);

    @Select("select dis_diag_no, " +
            "rtrim(dis_diag) disDiag, " +
            "rtrim(dis_diag_comment) disDiagComment, " +
            "dis_diag_status, " +
            "opName=(select rtrim(name) from a_employee_mi where code=op_id_code) " +
            "from zy_dis_diag_yb where inpatient_no=#{inpatientNo} and admiss_times=#{admissTimes}")
    List<ZyDisYbDiag> getYibaoDisDiags(@Param("inpatientNo") String inpatientNo, @Param("admissTimes") Integer admissTimes);

    @Select("select * from zy_dis_diag_yb where inpatient_no=#{patNo} and admiss_times=#{times}")
    List<ZyDisYbDiag> getZyDisYbDiags(@Param("patNo") String patNo, @Param("times") Integer times);

    @Select("SELECT dis_diag_no, " +
            "rtrim(dis_diag) disDiag, " +
            "rtrim(dis_diag_comment) disDiagComment, " +
            "disDiagStatusName=(select rtrim(name) from zd_dis_diag_status WHERE code=dis_diag_status), " +
            "opName=(select rtrim(name) from a_employee_mi where code=op_id_code) " +
            "from ba_first_page1 where inpatient_no=#{inpatientNo} and admiss_times=#{admissTimes}")
    List<ZyDisYbDiag> getBingliDisDiags(@Param("inpatientNo") String inpatientNo, @Param("admissTimes") Integer admissTimes);

    @Select("select rtrim(inpatient_no) from a_patient_mi where social_no=#{idCard}")
    List<String> getInpatientNo(@Param("idCard") String idCard);

    @Select("SELECT item_no, inpatient_no, admiss_times, agency, expirestart, file_sfz " +
            "FROM zy_xnh_record_sfz WHERE inpatient_no=#{inpatientNo} AND admiss_times=#{admissTimes}")
    List<IdCard> getIdCard(@Param("inpatientNo") String inpatientNo, @Param("admissTimes") Integer admissTimes);

    @Select("select agency, expirestart, sfz_image as fileSfz " +
            "from patient_mi_sfz where social_no=#{socialNo}")
    List<IdCard> selectIdCard(@Param("socialNo") String socialNo);

    @Select("SELECT item_no,inpatient_no,admiss_times,file_xnh AS file_sfz FROM zy_xnh_record " +
            "WHERE inpatient_no=#{inpatientNo} AND admiss_times=#{admissTimes}")
    List<IdCard> getScanFile(@Param("inpatientNo") String inpatientNo, @Param("admissTimes") Integer admissTimes);

    @Select("select settle_type from zy_ledger_file where inpatient_no=#{inpatientNo} and admiss_times=#{admissTimes} " +
            "and ledger_sn=(select isnull(max ( ledger_sn ), min ( ledger_sn )) from zy_ledger_file where " +
            "inpatient_no=#{inpatientNo} and admiss_times=#{admissTimes})")
    Integer getSettleType(@Param("inpatientNo") String inpatientNo, @Param("admissTimes") Integer admissTimes);

    @Select("select count(id) from zy_retract_yb where inpatientNo=#{inpatientNo} " +
            "and admissTimes=#{admissTimes} and requestType=#{requestType}")
    Integer getRequestCount(@Param("inpatientNo") String inpatientNo,
                            @Param("admissTimes") Integer admissTimes,
                            @Param("requestType") Integer requestType);

    @Select("select * from zy_retract_yb where inpatientNo=#{inpatientNo} and " +
            "admissTimes=#{admissTimes} and requestType=#{requestType}")
    List<ToYbkRequest> getRetractRequests(@Param("inpatientNo") String inpatientNo,
                                          @Param("admissTimes") Integer admissTimes,
                                          @Param("requestType") Integer requestType);

    @Insert("insert into zy_retract_yb (inpatientNo, admissTimes, name, sex, responceType, ybType, treatType, " +
            "remark, inputId, inputName, inputTimes, statusFlag, requestType) values (#{inpatientNo}, #{admissTimes}, " +
            "#{name}, #{sex}, #{responceType}, #{ybType}, #{treatType}, #{remark}, #{inputId}, #{inputName}," +
            "#{inputTimes}, 1, #{requestType})")
    void submitNewRequest(ToYbkRequest indata);

    @Select("select isnull(max ( ledger_sn ), isnull(min ( ledger_sn ), 1)) from zy_ledger_file where " +
            "inpatient_no=#{inpatientNo} and admiss_times=#{admissTimes}")
    Integer getLedgerSn(@Param("inpatientNo") String inpatientNo, @Param("admissTimes") Integer admissTimes);

    @Update("update zy_actpatient set yb_register_date=#{ybRegisterDate},med_type=#{medType},responce_type=#{responceType}, " +
            "admdvs=#{admdvs},refer_physician=#{referPhysician},matn_type=#{matnType},latechb_flag=#{latechbFlag}," +
            "pret_flag=#{pretFlag},dise_code=#{diseCode},dise_name=#{diseName},exp_content=#{expContent},name=#{name}, " +
            "sex=#{sex} where inpatient_no=#{inpatientNo} and admiss_times=#{admissTimes}")
    void updateZyActpatient(Patient param);


    @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_ba2 (bah, zyh, zycs, job_nurse) values (#{bah}, #{bah}, #{times}, #{dutyNurse})")
    void createBatjBa2(@Param("bah") String bah, @Param("times") int times, @Param("dutyNurse") String dutyNurse);

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

    @Update("update a_patient_mi set psn_cert_type=#{psnCertType},social_no=#{socialNo}," +
            "country=#{country},nation_code=#{nation},relation_name=#{contactName}," +
            "relation_code=#{contactRelation},relation_street=#{contactAddrName},sex=#{sex}, " +
            "relation_tel=#{contactPhone},home_tel=#{homeTel} where inpatient_no=#{inpatientNo}")
    void updateAPatientMi(Patient patient);

    @Select("select * from zy_in_diag_yb where inpatient_no=#{inpatientNo} and admiss_times=#{times} order by diag_no")
    List<ZyInYbDiag> getZyInYbDiag(@Param("inpatientNo") String inpatientNo, @Param("times") Integer times);

    @Delete("delete from zy_in_diag_yb where inpatient_no=#{inpatientNo} and admiss_times=#{times}")
    void deleteOldZyInYbDiag(@Param("inpatientNo") String inpatientNo, @Param("times") Integer times);

    @Insert("<script>" +
            "insert into zy_in_diag_yb (inpatient_no, admiss_times, diag_no, " +
            "icd_code, icd_text, oper_id, op_date, diag_type) values " +
            "<foreach collection='list' item='diag' separator=','>" +
            "(#{diag.inpatientNo},#{diag.admissTimes},#{diag.diagNo}," +
            "#{diag.icdCode},#{diag.icdText},#{operId},#{diag.opDate},#{diag.diagType})" +
            "</foreach>" +
            "</script>")
    void insertNewZyInYbDiag(@Param("operId") String operId, @Param("list") List<ZyInYbDiag> list);

    @Insert("<script>" +
            "insert into zy_in_diag_yb (inpatient_no, admiss_times, diag_no, " +
            "icd_code, icd_text, oper_id, op_date, diag_type) values " +
            "<foreach collection='diags' item='diag' separator=','>" +
            "(#{inpatientNo},#{admissTimes},#{diag.diagNo},#{diag.icdCode}," +
            "#{diag.icdText},#{staffId},getdate(),#{diag.diagType})" +
            "</foreach>" +
            "</script>")
    void insertSiZyInYbDiag(SaveSiZyDiags param);


    @Select("select convert (varchar(12),dateadd (day,0,start_time),111) from yz_act_order where " +
            "inpatient_no=#{inpatientNo} and admiss_times=#{admissTimes} and status_flag > '1' " +
            "and isnull(group_no,'00')='00' and order_code in " +
            "(select order_code from yz_zd_order_item_confirm where item_name in (N'出院',N'死亡')) ")
    String getActOrderDisDate(@Param("inpatientNo") String inpatientNo,
                              @Param("admissTimes") Integer admissTimes);

    @Select("select act_order_no from yz_sequence")
    float getActOrderNo();

    @Update("update yz_sequence set act_order_no=#{no}")
    void updateActorOrderNo(float no);

    @Insert("insert into yz_act_order " +
            "(act_order_no, inpatient_no, admiss_times, order_code, order_time, frequ_code, enter_time, " +
            "enter_oper, start_time, end_time, infant_flag, drug_flag, performance_time, " +
            "physician, confirm_time, signer, status_flag, status_time,drug_specification,order_name,discription, " +
            "ward_code,dept_code,exec_unit) " +
            "values (#{actOrderNo}, #{inpatientNo}, #{admissTimes}, " +
            "(select top 1 order_code from yz_zd_order_item_confirm where item_name=N'出院'), " +
            "#{dismissOrderDate}, 'ONCE', #{dismissOrderDate},#{staffId}, #{dismissOrderDate}, #{dismissOrderDate}, " +
            "0, 0, #{dismissOrderDate}, #{staffId},#{dismissOrderDate}, #{staffId}, 5, #{dismissOrderDate}," +
            "'诊疗项目','今日结账出院','0.00 本项目不包含费用'," +
            "#{admissWard},#{admissWard},#{admissWard})")
    void insertNewDismissActOrder(@Param("actOrderNo") float actOrderNo,
                                  @Param("inpatientNo") String inpatientNo,
                                  @Param("admissTimes") Integer admissTimes,
                                  @Param("dismissOrderDate") Date dismissOrderDate,
                                  @Param("staffId") String staffId,
                                  @Param("admissWard") String admissWard);

    @Select("select * from zy_dis_diag_yb where inpatient_no=#{inpatientNo} and admiss_times=#{admissTimes}")
    List<ZyDisYbDiag> getDisDiags(@Param("inpatientNo") String inpatientNo,
                                  @Param("admissTimes") Integer admissTimes);

    @Select("select rtrim(admiss_diag) code,rtrim(admiss_diag_str) name from zy_actpatient where " +
            "inpatient_no=#{inpatientNo} and admiss_times=#{admissTimes}")
    CodeName getAdmissDiag(@Param("inpatientNo") String inpatientNo,
                           @Param("admissTimes") Integer admissTimes);

    @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) " +
            "values (#{inpatientNo}, #{admissTimes}, 1, 13, #{code}, #{name}, #{staffId}, getDate(), '普通', 0, 1)")
    void insertDisDiag(@Param("inpatientNo") String inpatientNo,
                       @Param("admissTimes") Integer admissTimes,
                       @Param("code") String code,
                       @Param("name") String name,
                       @Param("staffId") String staffId);

    @Insert("<script>" +
            "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) values " +
            "<foreach collection='list' item='diag' separator=','>" +
            "(#{diag.inpatientNo},#{diag.admissTimes},#{diag.diagNo}, 13, " +
            "#{diag.icdCode},#{diag.icdText},#{staffId},getdate(), '普通', 0, 1)" +
            "</foreach>" +
            "</script>")
    void insertDisDiags(@Param("staffId") String staffId, @Param("list") List<ZyInYbDiag> list);

    @Update("update zy_detail_charge set charge_date=#{newDate} where inpatient_no=#{zyh} " +
            "and admiss_times=#{times} and CONVERT(varchar(20), charge_date, 20) <= #{admdate}")
    void correctFeeChargeTimeBeforeAdmiss(@Param("zyh") String zyh,
                                          @Param("times") int times,
                                          @Param("admdate") Date admdate,
                                          @Param("newDate") Date newDate);

    @Select("select start_time from yz_act_order where inpatient_no=#{patNo} and admiss_times=#{times} " +
            "and status_flag > '1' and isnull(group_no, '00')='00' and order_code in " +
            "(select order_code from yz_zd_order_item_confirm where item_name in (N'出院',N'死亡'))")
    Date selectActOrderDisDate(@Param("patNo") String patNo, @Param("times") Integer times);

    @Update("update zy_detail_charge set charge_date=#{disdate} where inpatient_no=#{zyh} " +
            "and admiss_times=#{times} and charge_date>#{disdate}")
    void correctFeeChargeTimeAfterDismiss(@Param("zyh") String zyh,
                                          @Param("times") int times,
                                          @Param("disdate") Date disdate);

    @Update("update zy_ledger_file set deposit=(select isnull(sum(depo_amount),0) from zy_deposit_file f with(nolock) " +
            "where f.inpatient_no=#{zyh} and f.admiss_times=#{times} and f.ledger_sn=#{ledger} and f.status in ('1','2') ) " +
            "where inpatient_no=#{zyh} and admiss_times=#{times} and ledger_sn=#{ledger} and account_date is null")
    void recountDeposit(@Param("zyh") String zyh,
                        @Param("times") int times,
                        @Param("ledger") int ledger);

    @Update("execute zy_receive_drug #{zyh},#{times},#{zyh},#{infant} ")
    void zyReceiveDrug(@Param("zyh") String zyh,
                       @Param("times") int times,
                       @Param("infant") int infant);

    @Update("execute zy_receive_one #{zyh},#{times},#{zyh},#{infant} ")
    void zyReceiveOne(@Param("zyh") String zyh,
                      @Param("times") int times,
                      @Param("infant") int infant);

    @Update("execute dbo.zy_cngl_fyjs_list_yz #{zyh},#{times} ")
    void zyCnglFyjsListYz(@Param("zyh") String zyh,
                          @Param("times") int times);

    @Update("update zy_detail_charge set charge_status='2' where " +
            "inpatient_no=#{zyh} and admiss_times=#{times} and ledger_sn=#{ledger} ")
    void updateZyDetailCharge(@Param("zyh") String zyh,
                              @Param("times") int times,
                              @Param("ledger") int ledger);

    @Update("execute zy_calc_detail_again_new #{zyh},#{times},#{ledger},1,1")
    void zyCalcDetailAgainNew(@Param("zyh") String zyh,
                              @Param("times") int times,
                              @Param("ledger") int ledger);

    @Update("update zy_ledger_file set balance=(deposit+isnull(charge_yb,0)-total_charge) where " +
            "inpatient_no=#{zyh} and admiss_times=#{times} and ledger_sn=#{ledger}")
    void updateBalance(@Param("zyh") String zyh,
                       @Param("times") int times,
                       @Param("ledger") int ledger);

    @Select("select balance from zy_ledger_file where inpatient_no=#{zyh} and admiss_times=#{times} and ledger_sn=#{ledger}")
    String selectLedgerBalance(@Param("zyh") String zyh,
                               @Param("times") int times,
                               @Param("ledger") int ledger);

    @Update("update zy_actpatient set balance=#{balance} where inpatient_no=#{zyh}")
    void updateZyActPatientBalance(@Param("zyh") String zyh, @Param("balance") String balance);


    @Select("select chronic_disease_type " +
            "from crm_patient_mi " +
            "where social_no = #{socialNo} ")
    String getCrmBySocialNo(String socialNo);


    @Select("select name " +
            "from crm_zd_visit_type " +
            "where code in (${typeList})")
    List<String> getCrmTypeName(String typeList);

}