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

package thyyxxk.webserver.dao.his.medicalinsurance;

import org.apache.ibatis.annotations.*;
import thyyxxk.webserver.entity.datamodify.GetDropdownBox;
import thyyxxk.webserver.entity.datamodify.TYbSetModifyTime;
import thyyxxk.webserver.entity.datamodify.TYbSetlModifyReq;
import thyyxxk.webserver.entity.medicalinsurance.inpatient.BatjBa4;
import thyyxxk.webserver.entity.medicalinsurance.inpatient.YbZyDisDiag;
import thyyxxk.webserver.entity.medicalinsurance.inpatient.ZyDisDiagYb;
import thyyxxk.webserver.entity.medicalinsurance.inpatient.ZyInactpatient;
import thyyxxk.webserver.entity.medicalinsurance.query.SiPatInfo;
import thyyxxk.webserver.entity.medicalinsurance.setllistupld.*;
import thyyxxk.webserver.entity.querydata.SiSetlinfoTemp;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * <p>
 * 描述： 医保结算单上传
 * </p>
 *
 * @author xc
 * @date 2021-11-25 08:42
 */
@Mapper
public interface UpIdCollectionDao {


    @Select("select a.mdtrt_id,   " + // -- 就诊ID
            "       a.setl_id,    " + // -- 结算ID
            "       a.psn_no as hi_no,  " + //--  医保编号
            "       medcasno=(a.pat_no+'_'+cast(a.times as varchar)),  " + //-- 病案号
            "       dcla_time=getdate(), " + //--  申报时间
            "       a.psn_name,  " + //-- 人员姓名
            "       a.gend, " + //  -- 性别
            "       a.brdy,  " + //-- 出生日期
            "       a.age,  " + // -- 年龄
            "       a.certno, " + //  -- 证件号码
            "       a.psn_cert_type as patnCertType,  " + // -- 患者证件类别
            "       b.insuplc_admdvs as insuplc,  " + //  -- 参保地
            "       psnSelfpay=cast(a.psn_cash_pay as decimal(16,2)) ,  " + // -- 个人自付
            "       psnOwnpay=cast(a.fulamt_ownpay_amt as decimal(16,2)) ,  " + // -- 个人自费
            "       acct_pay=cast(a.acct_pay as decimal(16,2)), " + // -- 个人账户支出
            "       psnCashpay=cast(a.psn_cash_pay as decimal(16,2)) ,  " + // --  个人现金支付
            "       a.clr_optins as hsorg,  " + // -- 医保机构
            "       a.insutype as hi_type," +   // -- 险种类型
            "       a.setl_time as  setl_end_date " +  //结算时间
            "from t_si_setlinfo a, t_si_pat_info b " +
            "where a.pat_no=b.pat_no and a.times=b.times " +
            "and a.ledger_sn = b.ledger_sn  " +
            "and a.pat_no = #{patNo} and a.times = #{times} " +
            "and a.ledger_sn = #{ledgerSn} " +
            "and a.revoked = 0 ")
    SetlinfoUpld setlinfo1(@Param("patNo") String patNo,
                           @Param("times") Integer times,
                           @Param("ledgerSn") Integer ledgerSn);


    @Select("select  rtrim(country) as ntly, " +  // 国籍
            "        rtrim(social_no) as certno,  " + // 证件
            "        rtrim(occupation_code) as prfs,  " + // -- 职业 在 Prfs 中有对应的
            "        rtrim(home_street) as curr_addr ," + // -- 现住址
            "        rtrim(employer_name) as emp_name, " + //-- 单位名称
            "        rtrim(employer_street) as emp_addr,  " + //-- 单位地址
            "        rtrim(employer_tel) as  emp_tel, " +
            "        rtrim(employer_zipcode) as poscode," +
            "        (select rtrim(name) from zd_nation_code where code = nation_code) as naty," +
            "       rtrim(relation_name) as coner_name, " +
            "       (select  isnull(rtrim(yb_code),80) from zy_zd_relative_code where code = relation_code)  as  patn_rlts,  " + // 与患者的关系
            "       rtrim(relation_tel) as  coner_tel " + //  联系人电话
            "from a_patient_mi " +
            "where inpatient_no = #{patNo} or mz_no = #{patNo}")
    SetlinfoUpld setlinfo2(@Param("patNo") String patNo);


    @Select("select " +
            "       pwcry_bfadm_coma_dura = isnull(ryq_hmsj_day,0)  + '/' + isnull(ryq_hmsj_hour,0) + '/' + isnull(ryq_hmsj_min,0), " + //  -- 颅脑损伤患者入院前昏迷时长
            "       pwcry_afadm_coma_dura = isnull(ryh_hmsj_day,0)  + '/' + isnull(ryh_hmsj_hour,0) + '/' + isnull(ryh_hmsj_min,0),  " + // -- 颅脑损伤患者入院后昏迷时长
            "       lyfs as dscg_way,  " + // -- 离院方式
            "       jsjg as acp_medins_name,  " + // -- 拟接收机构名称
            "       acp_optins_code = '',  " + // -- 拟接收机构代码 (占时为空)
            "       zzy_flag as days_rinp_flag_31,  " + // -- 出院31天内再住院计划标志
            "       rtrim(zzy_md) as days_rinp_pup_31,  " + // -- 出院31天内再住院目的
            "       chfpdr_name = (select rtrim(name) from a_employee_mi where code = isnull(zzys,b.consult_physician)),  " + // -- 主诊医师姓名
            "       (select yb_code from a_employee_mi where code = isnull(zzys,b.consult_physician))  as chfpdr_code,  " + //-- 主诊医师代码
            "       b.admiss_date as adm_time,  " + //-- 入院时间
            "       adm_caty = (select rtrim(si_caty) from zd_unit_code where code = b.small_dept),  " + // -- 入院科别
            "       refldept_dept = (select rtrim(si_caty) from zd_unit_code where code = b.zk_ward),  " + //--转科科别
            "        b.dis_date as dscg_time,  " + // -- 出院时间
            "       dscg_caty = (select rtrim(si_caty) from zd_unit_code where code=isnull(zk_ward,small_dept)), " + // -- 出院科别
            "       datediff(day ,b.admiss_date ,b.dis_date) as  act_ipt_days,  " +  // 实际住院天数
            "       zy_serial_no as biz_sn " +// 业务流水号
            "from batj_ba2 a,zy_inactpatient b " +
            "where a.bah = b.inpatient_no and a.zycs = b.admiss_times " +
            "and a.bah = #{patNo} and a.zycs = #{times}")
    SetlinfoUpld setlinfo3(@Param("patNo") String patNo,
                           @Param("times") Integer times);


    @Select("select  case age_days when  '-' then 0 else age_days end as nwb_age, " +
            "       rtrim(lxdz2) as  coner_addr, " +
            "       rtrim(rytj) as  adm_way, " +
            "        case new_born_weight when  '-' then 0 else age_days end as nwb_bir_wt, " +
            "        case new_born_admiss_weight when  '-' then 0 else age_days end as nwb_bir_wt" +
            "       from batj_ba1 where  zyh = #{patNo} and zycs = #{times} ")
    SetlinfoUpld setlinfo4(@Param("patNo") String patNo,
                           @Param("times") Integer times);

    @Select("select top(1) rtrim(name) name,dept_name = (select rtrim(name) from zd_unit_code where code = dept_code) " +
            "from a_employee_mi where code = #{code}")
    GetDropdownBox userInfo(String code);

    @Select("select isnull(sum(charge_amount),0) from zy_detail_charge where inpatient_no = #{patNo} and admiss_times = #{times} and ledger_sn = #{ledgerSn} and infant_flag = #{infantFlag}" +
            " and charge_code_mx in ('F01464','017051')")
    int teJiHuLi(@Param("patNo") String patNo,
                 @Param("times") Integer times,
                 @Param("ledgerSn") Integer ledgerSn,
                 @Param("infantFlag") Integer infantFlag);

    @Select("select isnull(sum(charge_amount),0) from zy_detail_charge where inpatient_no = #{patNo} and admiss_times = #{times} and ledger_sn = #{ledgerSn} and infant_flag = #{infantFlag}" +
            " and charge_code_mx in ('F01465','017052')")
    int yiJiHuLi(@Param("patNo") String patNo,
                 @Param("times") Integer times,
                 @Param("ledgerSn") Integer ledgerSn,
                 @Param("infantFlag") Integer infantFlag);

    @Select("select isnull(sum(charge_amount),0) from zy_detail_charge where inpatient_no = #{patNo} and admiss_times = #{times} and ledger_sn = #{ledgerSn} and infant_flag = #{infantFlag}" +
            " and charge_code_mx in ('F01466','017053')")
    int erJiHuLi(@Param("patNo") String patNo,
                 @Param("times") Integer times,
                 @Param("ledgerSn") Integer ledgerSn,
                 @Param("infantFlag") Integer infantFlag);

    @Select("select isnull(sum(charge_amount),0) from zy_detail_charge where inpatient_no = #{patNo} and admiss_times = #{times} and ledger_sn = #{ledgerSn} and infant_flag = #{infantFlag}" +
            " and charge_code_mx in ('F01467','017054')")
    int sanJiHuLi(@Param("patNo") String patNo,
                  @Param("times") Integer times,
                  @Param("ledgerSn") Integer ledgerSn,
                  @Param("infantFlag") Integer infantFlag);

    // 这里是 住院诊断消息
    @Select("select si_diag_type as diag_type,  " +
            "       rtrim(dis_diag) as diag_code,  " +
            "       rtrim(dis_diag_comment) as diag_name " +
            "from ${tableName}  " +
            "where inpatient_no = #{patNo} and admiss_times = #{times} " +
            "order by dis_diag_no ")
    List<DiseinfoUpld> diseinfo(@Param("patNo") String patNo,
                                @Param("times") Integer times,
                                @Param("tableName") String tableName);

    // 这里是手术操作信息
    @Select("select " +
            "       rtrim(ssmc) as oprn_oprt_name, " +
            "       rtrim(ssbm) as oprn_oprt_code, " +
            "       ssrq as oprn_oprt_date, " +
            "       (select rtrim(isnull(yb_code,9)) from zd_anaesthesia where code = mzff ) as anst_way, " +
            "       (select top(1) rtrim(name) from a_employee_mi where code = ssys) as oper_dr_name, " +
            "       (select rtrim(yb_code) from a_employee_mi where code = ssys) as oper_dr_code, " +
            "       (select rtrim(name) from a_employee_mi where code = mzys) as  anst_dr_name, " +
            "       (select rtrim(yb_code) from a_employee_mi where code = mzys)  as anst_dr_code " +
            "       from ${tableName} " +
            "where zyh = #{patNo} and zycs = #{times} " +
            "order by ssxh")
    List<OprninfoUpld> oprninfoUplds(@Param("patNo") String patNo,
                                     @Param("times") Integer times,
                                     @Param("tableName") String tableName);

    @Select("select insuplc_admdvs,insutype,med_type from t_si_pat_info " +
            "where pat_no = #{patNo} and times = #{times} and ledger_sn = #{ledgerSn}")
    SiPatInfo huoQuZhiFuFangShi(@Param("patNo") String patNo,
                                @Param("times") Integer times,
                                @Param("ledgerSn") Integer ledgerSn);

    @Update("update t_si_setlinfo set hi_paymtd = #{hiPaymtd} where pat_no = #{patNo} and times = #{times} and ledger_sn = #{ledgerSn}")
    void genXingYiBaoZhiFuFangShi(@Param("patNo") String patNo,
                                  @Param("times") Integer times,
                                  @Param("ledgerSn") Integer ledgerSn,
                                  @Param("hiPaymtd") Integer hiPaymtd);

    @Select("select receipt_no from zy_receipt where inpatient_no=#{patNo} and admiss_times=#{times} and ledger_sn=#{ledgerSn} and receipt_no<>'0000000'" +
            "and receipt_sn = 1")
    String billNo(@Param("patNo") String patNo,
                  @Param("times") Integer times,
                  @Param("ledgerSn") Integer ledgerSn);

    @Select("select isnull(audit_flag,0) from t_yb_setl_modify_req where pat_no = #{patNo} " +
            "and times = #{times}")
    Integer setlModifyReqAuditFlag(@Param("patNo") String patNo,
                                   @Param("times") Integer times);

    // 基金支付信息
    @Select("select fund_pay_type,fund_payamt=cast(sum(fund_payamt) as decimal(16,2)) from t_si_setldetail " +
            "where pat_no = #{patNo} and times = #{times} and ledger_sn = #{ledgerSn} " +
            "group by fund_pay_type ")
    List<PayinfoUpld> payinfoUpld(@Param("patNo") String patNo,
                                  @Param("times") Integer times,
                                  @Param("ledgerSn") Integer ledgerSn);

    /*                                              收费项目信息                                              */
    // 总金额 和 全自费 和 医疗收费项目
    @Select("select med_chrgitm_type as med_chrgitm," +
            "amt=cast(sum(det_item_fee_sumamt) as decimal(16,2)), " +
            "fulamt_ownpay_amt=cast(sum(fulamt_ownpay_amt) as decimal(16,2)), " +
            "claa_sumfee = 0,clab_amt = 0,oth_amt = 0 from t_si_setl_fee_detl " +
            "where setl_id = #{setlId} group by med_chrgitm_type")
    List<IteminfoUpld> amtAndOthAmt(@Param("setlId") String setlId);

    // -- 01 甲类
    @Select("select med_chrgitm_type as med_chrgitm," +
            "claa_sumfee=cast(sum(det_item_fee_sumamt) as decimal(16,2)) " +
            "from t_si_setl_fee_detl " +
            "where setl_id = #{setlId} and chrgitm_lv = '01' " +
            "group by med_chrgitm_type")
    List<IteminfoUpld> jiaLeiFeiYong(@Param("setlId") String setlId);

    // -- 02 乙类
    @Select("select med_chrgitm_type as med_chrgitm," +
            "clab_amt=cast(sum(det_item_fee_sumamt) as decimal(16,2)) " +
            "from t_si_setl_fee_detl " +
            "where setl_id = #{setlId} and chrgitm_lv = '02' " +
            "group by med_chrgitm_type")
    List<IteminfoUpld> yiLeiFeiYong(@Param("setlId") String setlId);


    //   重症监护信息
    @Select("select op_time from zy_zk_list where inpatient_no=#{patNo} and admiss_times=#{times} and " +
            "(dept_code in ('1160000','8000160') or f_dept_code in ('1160000','8000160')) " +
            "order by zk_times")
    List<Date> zhuanKeShiJian(@Param("patNo") String patNo,
                              @Param("times") Integer times);


    // 保存返回信息
    @Update("update t_si_setlinfo set setl_list_id  = #{setlListId} where setl_id = #{setlId} ")
    void fanHuiLiuShuiHao(@Param("setlListId") String setlListId,
                          @Param("setlId") String setlId);

    @Select("<script>" +
            "select top ${pageSize} * from (" +
            "select " +
            "row_number() over (order by setl_time) as rowNumber," +
            "rtrim(a.pat_no) pat_no,a.times,ledger_sn,psn_name,gend,brdy,insutype,psn_type,mdtrt_id,setl_id,setl_time, " +
            "       a.med_type,medfee_sumamt,fund_pay_sumamt, " +
            "       acct_pay,psn_cash_pay,clr_optins,clr_optins_name = (select name from t_region where code=clr_optins), " +
            "       outDeptName=(select rtrim(name) from zd_unit_code where code=isnull(zk_ward,small_dept))," +
            "       rtrim(isnull(zk_ward,small_dept)) as out_dept, " +
            "       setl_list_id,datediff(day ,b.admiss_date ,b.dis_date) as act_ipt_days,b.operation,b.dec_type," +
            "       id,req_op_id,rtrim(req_op_name) req_op_name,req_op_date,req_remark," +
            "audit_staff,audit_name,audit_date,audit_remark,audit_flag," +
            "refer_physician_name = (select top 1 rtrim(name) from a_employee_mi where code = b.refer_physician),b.refer_physician " +
            "from t_si_setlinfo a left join  zy_inactpatient b on (a.pat_no = b.inpatient_no and a.times = b.admiss_times) " +
            "left join t_yb_setl_modify_req c on (a.pat_no = c.pat_no and a.times = c.times) " +
            "where revoked = 0 " +
            "<if test=\"startTime !=null and startTime != '' \">" +
            " and a.setl_time &gt;= #{startTime} and a.setl_time &lt;= #{endTime} " +
            "</if>" +
            "<if test=\"setlType !=null and setlType != '' \">" +
            "  and a.setl_type = #{setlType} " +
            "</if>" +
            "<if test=\"patNo !=null and patNo != '' \">" +
            "  and a.pat_no = #{patNo} " +
            "</if>" +
            "<if test=\"insutypes.size > 0 \">" +
            "  and insutype in " +
            "<foreach collection='insutypes' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"outDepts.size > 0 and outDepts != null \">" +
            "and isnull(zk_ward,small_dept) in " +
            "<foreach collection='outDepts' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item} " +
            "</foreach>" +
            "</if>" +
            "<if test=\"medTypes.size > 0 \">" +
            "  and a.med_type in " +
            "<foreach collection='medTypes' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"psnTypes.size > 0 \">" +
            "  and psn_type in " +
            "<foreach collection='psnTypes' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"decTypes.size > 0 \">" +
            "  and b.dec_type in " +
            "<foreach collection='decTypes' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"clrOptins.size > 0 \">" +
            "  and clr_optins in " +
            "<foreach collection='clrOptins' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"flag == 1 \">" +
            " and a.setl_list_id is null " +
            "</if>" +
            "<if test=\"flag == 2 \">" +
            " and a.setl_list_id is not null " +
            "</if>" +
            "<if test=\"auditFlag != 4 \">" +
            " and c.audit_flag = #{auditFlag} " +
            "</if>" +
            "<if test=\"hiPaymtd != null and hiPaymtd != 0 \">" +
            " and a.hi_paymtd = #{hiPaymtd} " +
            "</if>" +
            "<if test=\"referPhysician != null and  referPhysician != '' \">" +
            " and b.refer_physician = #{referPhysician} " +
            "</if>" +
            " ) temp where rowNumber > ${pageSize} * (${currentPage} - 1)" +
            "</script>")
    List<SiSetlinfoTemp> huoQuJieSuanRenYuan(@Param("startTime") String startTime,
                                             @Param("endTime") String endTime,
                                             @Param("setlType") String setlTyle,
                                             @Param("patNo") String patNo,
                                             @Param("insutypes") List<String> insutypes,
                                             @Param("outDepts") List<String> outDepts,
                                             @Param("medTypes") List<String> medTypes,
                                             @Param("psnTypes") List<String> psnTypes,
                                             @Param("clrOptins") List<String> clrOptins,
                                             @Param("decTypes") List<Integer> decTypes,
                                             @Param("flag") Integer flag,
                                             @Param("referPhysician") String referPhysician,
                                             @Param("auditFlag") Integer auditFlag,
                                             @Param("hiPaymtd") Integer hiPaymtd,
                                             @Param("currentPage") long currentPage,
                                             @Param("pageSize") long pageSize);


    @Select("<script>" +
            "select count(*) " +
            "from t_si_setlinfo a left join  zy_inactpatient b on (a.pat_no = b.inpatient_no and a.times = b.admiss_times) " +
            "left join t_yb_setl_modify_req c on (a.pat_no = c.pat_no and a.times = c.times) " +
            "where revoked = 0 " +
            "<if test=\"startTime !=null and startTime != '' \">" +
            " and setl_time &gt;= #{startTime} and setl_time &lt;= #{endTime} " +
            "</if>" +
            "<if test=\"setlType !=null and setlType != '' \">" +
            "  and setl_type = #{setlType} " +
            "</if>" +
            "<if test=\"patNo !=null and patNo != '' \">" +
            "  and a.pat_no = #{patNo} " +
            "</if>" +
            "<if test=\"insutypes.size > 0 \">" +
            "  and insutype in " +
            "<foreach collection='insutypes' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"outDepts.size > 0 \">" +
            "and isnull(zk_ward,small_dept) in " +
            "<foreach collection='outDepts' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item} " +
            "</foreach>" +
            "</if>" +
            "<if test=\"medTypes.size > 0 \">" +
            "  and a.med_type in " +
            "<foreach collection='medTypes' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"psnTypes.size > 0 \">" +
            "  and psn_type in " +
            "<foreach collection='psnTypes' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"decTypes.size > 0 \">" +
            "  and b.dec_type in " +
            "<foreach collection='decTypes' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"clrOptins.size > 0 \">" +
            "  and clr_optins in " +
            "<foreach collection='clrOptins' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test=\"flag == 1 \">" +
            " and a.setl_list_id is null " +
            "</if>" +
            "<if test=\"flag == 2 \">" +
            " and a.setl_list_id is not null " +
            "</if>" +
            "<if test=\"auditFlag != 4 \">" +
            " and c.audit_flag = #{auditFlag} " +
            "</if>" +
            "<if test=\"referPhysician != null and referPhysician != '' \">" +
            " and b.refer_physician = #{referPhysician} " +
            "</if>" +
            "<if test=\"hiPaymtd != null and hiPaymtd != 0 \">" +
            " and a.hi_paymtd = #{hiPaymtd} " +
            "</if>" +
            "</script>")
    long huoQuJieSuanRenYuanTotal(@Param("startTime") String startTime,
                                  @Param("endTime") String endTime,
                                  @Param("setlType") String setlTyle,
                                  @Param("patNo") String patNo,
                                  @Param("insutypes") List<String> insutypes,
                                  @Param("outDepts") List<String> outDepts,
                                  @Param("medTypes") List<String> medTypes,
                                  @Param("psnTypes") List<String> psnTypes,
                                  @Param("clrOptins") List<String> clrOptins,
                                  @Param("decTypes") List<Integer> decTypes,
                                  @Param("flag") Integer flag,
                                  @Param("referPhysician") String referPhysician,
                                  @Param("auditFlag") Integer auditFlag,
                                  @Param("hiPaymtd") Integer hiPaymtd);


    @Select("select opName = (select top(1) rtrim(name) from a_employee_mi where code = op_id_code)," +
            "rtrim(inpatient_no) inpatient_no, admiss_times, dis_diag_no, dis_diag_type,rtrim(dis_diag) dis_diag," +
            "rtrim(dis_diag_comment) dis_diag_comment," +
            "op_id_code, op_diag_date, dis_diag_bzfx, dis_diag_status, operation, si_diag_type,admiss_cond  " +
            "from ${tableName} where inpatient_no = #{patNo} and admiss_times = #{times} " +
            "order by  dis_diag_no ")
    List<YbZyDisDiag> zhenDuanXinXi(@Param("patNo") String patNo,
                                    @Param("times") Integer times,
                                    @Param("tableName") String tableName);


    @Select("select ssys_name = (select top(1) rtrim(name) from a_employee_mi where code = ssys), " +
            "       sszsName1 = (select top(1) rtrim(name) from a_employee_mi where code = sszs1), " +
            "       sszsName2 = (select top(1) rtrim(name) from a_employee_mi where code = sszs2), " +
            "       mzys_name = (select top(1) rtrim(name) from a_employee_mi where code = mzys)," +
            "       bah, zyh, zycs, ssxh, ssrq, sstj, " +
            "       ssys , sszs1, sszs2, mzys ," +
            " ssbm, ssmc, rtrim(mzff) mzff, rtrim(qkjb) qkjb," +
            "rtrim(yhqk) yhqk, ssbfz, fhqk, ssjb, cut_heal," +
            "(select top(1) yb_code from a_employee_mi where code = ssys) as oper_dr_code, " +
            "(select top(1) yb_code from a_employee_mi where code = mzys) as anst_dr_code " +
            "from ${tableName} where zyh = #{patNo} and zycs = #{times} " +
            "order by ssxh")
    List<BatjBa4> shouShuXinXi(@Param("patNo") String patNo,
                               @Param("times") Integer times,
                               @Param("tableName") String tableName);


    @Select("select rtrim(inpatient_no) inpatient_no,admiss_times,rtrim(name) name,admiss_date,dis_date,responce_type,operation, " +
            "       responce_type_name = (select rtrim(name) from zy_zd_responce_type where code = responce_type),sex,rtrim(refer_physician) refer_physician " +
            "from zy_inactpatient where inpatient_no = #{patNo} and admiss_times = #{times}")
    ZyInactpatient huanZheXinXi(@Param("patNo") String patNo,
                                @Param("times") Integer times);

    @Select("select top(1) * from t_yb_set_modify_time")
    TYbSetModifyTime keXiuGaiShiJian();

    @Select("<script>" +
            "select rtrim(inpatient_no) inpatient_no,admiss_times,dis_diag,dis_diag_comment from ${tableName} where inpatient_no in " +
            "<foreach collection='patNos' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            " and dis_diag_no = 1" +
            "</script>")
    List<ZyDisDiagYb> zhuZhenDuan(@Param("patNos") Set<String> patNos,
                                  @Param("tableName") String tableName);


    @Select("<script>" +
            "select ward_code,ward_code_name = (select rtrim(name) from zd_unit_code where code = ward_code), " +
            "       rtrim(inpatient_no) patNo,admiss_times times " +
            "from zy_detail_charge where inpatient_no in " +
            "<foreach collection='patNos' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            " order by times,charge_date" +
            "</script>")
    List<SiSetlinfoTemp> zhuanKeKeShi(@Param("patNos") Set<String> patNos);


    @Select("select top 10 surgical_operation_code code,operation_operation_name name from " +
            "(SELECT ROW_NUMBER() OVER(ORDER BY code) AS Number,  * " +
            "from t_si_dl_oprtn where (surgical_operation_code like #{content} or operation_operation_name like #{content} or py_code like #{content})) a " +
            "where Number > (${page}-1) * 10 ")
    List<GetDropdownBox> searchSurgeryByAlpha(@Param("content") String content, @Param("page") long page);


    @Select("select count(1) from t_si_dl_oprtn where (surgical_operation_code like #{content} or operation_operation_name" +
            " like #{content} or py_code like #{content})")
    long searchSurgeryTotal(@Param("content") String content);


    @Delete("delete zy_dis_diag_yb_modify where inpatient_no = #{patNo} and admiss_times = #{times}; " +
            "delete batj_ba4_modify where zyh = #{patNo} and zycs = #{times}; " +
            "delete t_yb_setl_modify_req where pat_no = #{patNo} and times = #{times};")
    void delReq(@Param("patNo") String patNo, @Param("times") Integer times);


    @Insert("insert into t_yb_setl_modify_req (pat_no, times, req_op_id, req_op_name, req_remark) " +
            "values (#{patNo},#{times},#{opIdCode},#{opIdName},#{reqRemark})")
    void setlModifyReq(@Param("patNo") String patNo,
                       @Param("times") Integer times,
                       @Param("opIdCode") String opIdCode,
                       @Param("opIdName") String opIdName,
                       @Param("reqRemark") String reqRemark);

    @Insert("<script>" +
            "insert into ${tableName} (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, operation, si_diag_type,admiss_cond) " +
            "values " +
            "<foreach collection='list' item='param' separator=','>" +
            "(#{param.inpatientNo},#{param.admissTimes},#{param.disDiagNo},'13',#{param.disDiag},#{param.disDiagComment}, " +
            "        #{param.opIdCode},#{param.opDiagDate},'001',#{param.disDiagStatus},null,#{param.siDiagType},#{param.admissCond})" +
            "</foreach>" +
            "</script>")
    void setlModDis(@Param("list") List<YbZyDisDiag> param,
                    @Param("tableName") String tableName);

    @Insert("<script>" +
            "insert into ${tableName} (bah, zyh, zycs, ssxh, ssrq, sstj, ssys, sszs1, sszs2, " +
            "mzys, ssbm, ssmc, mzff, qkjb, yhqk, ssbfz, fhqk, ssjb, cut_heal) " +
            "values " +
            "<foreach collection='list' item='param' separator=','>" +
            "(#{param.zyh},#{param.zyh},#{param.zycs},#{param.ssxh},#{param.ssrq},null,#{param.ssys},#{param.sszs1}," +
            "#{param.sszs2},#{param.mzys},#{param.ssbm},#{param.ssmc},#{param.mzff},#{param.qkjb},#{param.yhqk},null,null,#{param.ssjb},null)" +
            "</foreach>" +
            "</script>")
    void setlModBat(@Param("list") List<BatjBa4> batjBa4s,
                    @Param("tableName") String tableName);


    @Select("update zy_inactpatient set operation = #{operation} /*治疗方式*/ , dec_type = #{decType} /*申报类型*/ " +
            "where  inpatient_no = #{patNo} and admiss_times = #{times}")
    void shenBao(@Param("operation") String operation,
                 @Param("decType") Integer decType,
                 @Param("patNo") String patNo,
                 @Param("times") Integer times);

    @Select("select pat_name = (select top 1 rtrim(name) from zy_inactpatient where inpatient_no = pat_no),* from t_yb_setl_modify_req where id = #{id}")
    TYbSetlModifyReq shenHeXinXi(Integer id);

    @Select("select rtrim(code_rs) from a_employee_mi where code = #{code}")
    String yuanGongGongHao(String code);

    @Update("update t_yb_setl_modify_req set audit_flag = #{auditFlag} where id = #{id}")
    void upAuditFlag(@Param("id") Integer id,
                     @Param("auditFlag") Integer auditFlag);

    @Delete("delete zy_dis_diag_yb where inpatient_no = #{patNo} and admiss_times = #{times};" +
            "delete batj_ba4 where zyh = #{patNo} and zycs = #{times}")
    void delYuanShuJu(@Param("patNo") String patNo,
                      @Param("times") Integer 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, " +
            "                            operation, si_diag_type) " +
            "select 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, " +
            "       operation, si_diag_type from zy_dis_diag_yb_modify " +
            "where inpatient_no = #{patNo} and admiss_times = #{times}; " +
            "insert into batj_ba4 (bah, zyh, zycs, ssxh, ssrq, sstj, ssys, sszs1, sszs2, mzys, ssbm, ssmc, mzff, qkjb, " +
            "                      yhqk, ssbfz, fhqk, ssjb, cut_heal) " +
            "select bah, zyh, zycs, ssxh, ssrq, sstj, ssys, sszs1, sszs2, mzys, ssbm, ssmc, mzff, qkjb, yhqk, ssbfz, fhqk, ssjb, cut_heal " +
            "from batj_ba4_modify where zyh = #{patNo} and zycs = #{times};")
    void auditTongGuo(@Param("patNo") String patNo,
                      @Param("times") Integer times);


    @Update("update t_yb_setl_modify_req set audit_staff = #{auditStaff},audit_remark = #{remark},audit_name = #{auditName}," +
            "audit_date = getdate() " +
            "where id = #{id}")
    void updateReq(@Param("id") Integer id,
                   @Param("remark") String remark,
                   @Param("auditStaff") String auditStaff,
                   @Param("auditName") String auditName);

    @Select("select top 1 *,op_id_name = (select top 1 rtrim(name) from a_employee_mi where op_id = code) from t_yb_set_modify_time")
    TYbSetModifyTime xianZhiShiJian();

    @Select("update t_yb_set_modify_time set start_time = #{startTime} ,end_time = #{endTime}, op_id = #{opId} ,op_date = getdate()")
    void sheZhiShiJian(@Param("startTime") String startTime,
                       @Param("endTime") String endTime,
                       @Param("opId") String opId);

    @Select("select ward_code as execUnit,execUnitName = (select rtrim(name) from zd_unit_code where code = ward_code), " +
            "       sum(charge_amount) chargeAmount,sum(charge_fee) chargeFee " +
            "from zy_detail_charge where inpatient_no = #{patNo} and admiss_times = #{times} " +
            "group by ward_code order by chargeFee desc")
    List<Map<String, String>> getHuanZheFeiYong(@Param("patNo") String patNo,
                                                @Param("times") Integer times);

    @Update("update  zy_inactpatient set refer_physician = #{code} where inpatient_no = #{patNo} and admiss_times = #{times}")
    void xiuGaiGuanChaungYiShen(@Param("patNo") String patNo,
                                @Param("times") Integer times,
                                @Param("code") String code);

    @Select("select rtrim(inpatient_no) inpatient_no,admiss_times,rtrim(dis_diag) dis_diag,case admiss_status when '' then null else admiss_status end admiss_cond " +
            "from ba_first_page1 " +
            "where isnull(dis_diag,'') <> '' and inpatient_no = #{patNo} and admiss_times = #{times} ")
    List<YbZyDisDiag> huanZheBingAnShouYeZhenDuanRuYuanBingQing(String patNo, Integer times);

    @Update("<script>" +
            "<foreach collection='list' item='item' separator=';'>" +
            "update zy_dis_diag_yb set admiss_cond = #{item.admissCond} where inpatient_no = #{item.inpatientNo} and admiss_times = #{item.admissTimes} and dis_diag_no = #{item.disDiagNo}" +
            "</foreach>" +
            "</script>")
    void genXingLaoZhenDuan(@Param("list") List<YbZyDisDiag> ybZyDisDiagList);

    @Select("select rtrim(name) from zd_country_code where code = #{ntly}")
    String getNtlyName(String ntly);

    @Select("select rtrim(name) from zd_nation_code where code = #{naty}")
    String getNatyName(String naty);

    @Select("select rtrim(name) from t_zd_psn_cert_type where code = #{cert}")
    String getPatnCertTypeName(String cert);

    @Select("select rtrim(name) name from zy_occupation_code where code = #{prfs}")
    String getPrfsName(String prfs);

    @Select("select  rtrim(name) name from zy_zd_relative_code where code = #{code}")
    String getPatnRltsName(String code);

    @Select("select rtrim(name) from t_si_admdvs where code = #{code} ")
    String getInsuplcName(String code);

    @Select("select rtrim(name) from t_yb_dept where code  = #{code}")
    String getYbDeptName(String code);


    @Select("select (select yb_code from a_employee_mi where code = job_nurse) as code, " +
            "name = (select rtrim(name) from a_employee_mi where code = job_nurse ) from batj_ba2 a,a_employee_mi b " +
            "where job_nurse = b.code and  zyh = #{patNo} and zycs = #{times} ")
    GetDropdownBox zeRenHuShi(@Param("patNo") String patNo,
                              @Param("times") Integer times);


    @Select("select pat_no,times,ledger_sn from t_si_setlinfo where hi_paymtd is null")
    List<Map<String, Object>> huoQuQuanBuJieSuanRenYuan();

}