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

package thyyxxk.webserver.dao.his.medicalinsurance;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
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 count(1) from t_case_frontsheet_main where bah = #{patNo} " + "  and admiss_times = #{times}")
    Integer shiFouQianShou(@Param("patNo") String patNo, @Param("times") Integer times);


    @Select("select a.mdtrt_id,   " + // -- 就诊ID
            "       a.setl_id,    " + // -- 结算ID
            "       a.psn_no as hi_no,  " + //--  医保编号
            "       a.psn_no as psn_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 - a.fulamt_ownpay_amt 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," + // -- 险种类型
            "       (select rytj from batj_ba1 where zyh = #{patNo} and zycs = #{times})  as admWay,  " +  // 入院途径
            "       a.setl_time as  setl_end_date, " +  //结算时间
            "       a.ipt_med_type, a.hi_paymtd, " +
            "       cast(fund_pay_sumamt as decimal(16,2)) as  fund_pay_sumamt," + //
            "       cast(maf_pay as decimal(16,2)) as maf_pay," +
            "       psn_idet_type as  sp_psn_type " +
            "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               (select si_code from zd_country_code where code = country)        as ntly /*国籍*/,\n" +
            "                     social_no                                  as certno /*证件*/,\n" +
            "                     occupation                                 as prfs /*职业 在 Prfs 中有对应的*/,\n" +
            "                     live_place                                 as curr_addr/*现住址*/,\n" +
            "                     unit_name                                  as emp_name /*单位名称*/,\n" +
            "                     unit_place                                 as emp_addr /*单位地址*/,\n" +
            "                     unit_phone                                 as emp_tel /*工作单位手机*/,\n" +
            "                     addr_zip_code                              as poscode /* 邮编*/,\n" +
            "                     (select wjw_code from zd_nation_code where code = nation)          as naty/*民族*/,\n" +
            "                     contact_name                               as coner_name/*联系人姓名*/,\n" +
            "                     (select rtrim(yb_code)\n" +
            "                      from zy_zd_relative_code\n" +
            "                      where code = contact_relation)            as patn_rlts /*与患者的关系*/,\n" +
            "                     contact_phone                              as coner_tel /*联系人电话*/,\n" +
            "                     coma_days_before_admiss + '/' + coma_hours_before_admiss + '/' +\n" +
            "                     coma_minutes_before_admiss                 as pwcry_bfadm_coma_dur/*颅脑损伤患者入院前昏迷时长*/,\n" +
            "                     coma_days_after_admiss + '/' + coma_hours_after_admiss + '/' +\n" +
            "                     coma_minutes_after_admiss                  as pwcry_afadm_coma_dura/*颅脑损伤患者入院后昏迷时长*/,\n" +
            "                     zy_dismiss_way                             as dscg_way /*离院方式*/,\n" +
            "                     dismiss_destination                        as acp_medins_name/*拟接收机构名称*/,\n" +
            "                     admiss_again_in_one_month                  as days_rinp_flag_31 /*出院31天内再住院计划标志*/,\n" +
            "                     admiss_again_purpose                       as days_rinp_pup_31/*出院31天内再住院目的*/,\n" +
            "                     main_doctor_name                           as chfpdr_name/*主诊医师姓名*/,\n" +
            "                     (select yb_code\n" +
            "                      from a_employee_mi\n" +
            "                      where code = main_doctor)                 as chfpdr_code/*主诊医师代码*/,\n" +
            "                     (select rtrim(si_caty)\n" +
            "                      from zd_unit_code\n" +
            "                      where code = b.small_dept)                as adm_caty/*入院科别*/,\n" +
            "                     (select rtrim(si_caty)\n" +
            "                      from zd_unit_code\n" +
            "                      where code = b.zk_ward)                   as refldept_dept /*转科科别*/,\n" +
            "                     (select rtrim(si_caty)\n" +
            "                      from zd_unit_code\n" +
            "                      where code = isnull(zk_ward, small_dept)) as dscg_caty /*出院科别*/,\n" +
            "    /*实际住院天数*/\n" +
            "                     zy_serial_no                               as biz_sn /*业务流水号*/,\n" +
            "                     case age_days\n" +
            "                         when '-' then 0\n" +
            "                         else age_days\n" +
            "                         end                                    as nwb_age/*新生儿年龄*/,\n" +
            "                     contact_addr_name                          as coner_addr /*联系人地址*/,\n" +
            "                     case new_born_weight\n" +
            "                         when '-' then 0\n" +
            "                         else new_born_weight\n" +
            "                         end                                    as nwb_bir_wt/*新生儿体重*/,\n" +
//            "                     case new_born_admiss_weight\n" +
//            "                         when '-' then 0\n" +
//            "                         else new_born_admiss_weight\n" +
//            "                         end                                    as nwb_adm_wt/*新生儿入院体重*/,\n" +
            "    resp_nurs_code = (select yb_code\n" +
            "                      from a_employee_mi\n" +
            "                      where a_employee_mi.code = job_nurse) /*责任护士*/,\n" +
            "    resp_nurs_name = (select name\n" +
            "                      from a_employee_mi\n" +
            "                      where a_employee_mi.code = job_nurse)\n" +
            "from zy_inactpatient b\n" +
            "         left join\n" +
            "     t_case_frontsheet_main a\n" +
            "     on (\n" +
            "                 a.bah = b.inpatient_no\n" +
            "             and a.admiss_times = b.admiss_times\n" +
            "         )\n" +
            "where b.inpatient_no = #{patNo}\n" +
            "  and b.admiss_times = #{times};")
    SetlinfoUpld setlinfo2(@Param("patNo") String patNo, @Param("times") Integer times);

    @Select("select begntime as adm_time,\n" +
            "       begntime as setl_begn_date,\n" +
            "       endtime          as dscg_time,\n" +
            "       begntime,\n" +
            "       case\n" +
            "           when datediff(day,\n" +
            "                         begntime,\n" +
            "                         endtime) <= 0 then 1\n" +
            "           else datediff(day,\n" +
            "                         begntime,\n" +
            "                         endtime)\n" +
            "           end          as act_ipt_days\n " +
            "from t_si_setlinfo,\n" +
            "     zy_inactpatient\n" +
            "where pat_no = #{patNo}\n" +
            "  and times = #{times}\n" +
            "  and ledger_sn = #{ledgerSn}\n" +
            "  and revoked = 0\n" +
            "  and inpatient_no = pat_no\n" +
            "  and admiss_times = times")
    SetlinfoUpld setlinfo5(@Param("patNo") String patNo,
                           @Param("times") Integer times,
                           @Param("ledgerSn") Integer ledgerSn);


    @Select("select home_street as currAddr," +
            "(select wjw_code from zd_nation_code where code = nation_code)          as naty/*民族*/,\n" +
            "       relation_name                                                        as coner_name,\n" +
            "       (select yb_code from zy_zd_relative_code where code = relation_code) as patn_rlts,\n" +
            "       relation_street                                                      as conerAddr," +
            "       relation_tel                                                         as conerTel  " +
            "from a_patient_mi " +
            "where ${patNo}")
    SetlinfoUpld setlinfo3(@Param("patNo") String patNo);


    @Select("select clinic_diag as wmDiseCode, clinic_diag_str otpWmDise " +
            "from zy_inactpatient " +
            "where inpatient_no = #{patNo} " +
            "  and admiss_times = #{times}")
    SetlinfoUpld setlinfo4(@Param("patNo") String patNo, @Param("times") Integer times);


    @Select("select (select si_code from zd_country_code where code = country)                    as ntly, " +
            "       dis_date                               as setl_end_date, " +
            "       begntime                            as setl_begn_date, " +
            "       begntime                            as adm_time," +
            "       case " + "           when datediff(day, begntime, dis_date) <= 0 then 1 " +
            "           else datediff(day, begntime, dis_date) end  as act_ipt_days,/*实际住院天数*/ " +
            "       dis_date as dscg_time, " +
            "       isnull(nullif(occupation_code, ''), 90) as prfs," +
            "       adm_way = '2' " +
            "from a_patient_mi a, " +
            "     zy_inactpatient b " +
            "where a.inpatient_no = #{patNo} " +
            "  and b.inpatient_no = #{patNo} " +
            "  and b.admiss_times = #{times}")
    SetlinfoUpld singleDiseasePatientInformation(@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);

    /**
     * 这里是 住院诊断消息
     *
     * @param patNo     住院号
     * @param times     住院次数
     * @param tableName 表格名
     * @return 数据
     */
    @Select("select si_diag_type as diag_type,  " +
            "case\n" +
            "                    when yb_code is null then rtrim(dis_diag)\n" +
            "                    else rtrim(yb_code)\n" +
            "                    end             diag_code," +
            "case\n" +
            "                    when yb_name is null then rtrim(dis_diag_comment)\n" +
            "                    else rtrim(yb_name)\n" +
            "                    end             diag_name " +
            " from ${tableName} left join " +
            "     zd_icd_code_new on (dis_diag = code) " +
            "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 " +
            "       case when yb_code is null then rtrim(ssbm) else rtrim(yb_code) end  oprn_oprt_code ," +
            "       case when yb_name is null then rtrim(ssmc) else rtrim(yb_name) end  oprn_oprt_name," +
            "       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," +
            "       op_start_date as oprn_oprt_begntime," +
            "       op_end_date as  oprn_oprt_endtime," +
            "       anst_start_date as anst_begntime," +
            "       anst_end_date as anst_endtime" +
            "       from ${tableName} left join zd_icd9_cm3 on (ssbm = zd_icd9_cm3.code) " +
            "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 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  receipt_bill " +
            "from mz_receipt_serial " +
            "where patient_id = #{patNo} " +
            "and times = #{times} " +
            " and nullif(receipt_bill, '') is not null ")
    String mzBillNo(@Param("patNo") String patNo, @Param("times") Integer times);


    @Select("select med_type " +
            "from t_si_setlinfo " +
            "where pat_no = #{patNo} " +
            "  and times =  #{times} " +
            "  and ledger_sn = #{ledgerSn} and revoked = 0")
    String getMedicalCategory(@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(det_item_fee_sumamt * (selfpay_prop)) 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);

    @Select("select med_chrgitm_type as med_chrgitm," +
            " sum(det_item_fee_sumamt) as amt\n" +
            "from t_si_setl_fee_detl\n" +
            "where setl_id = #{setlId}\n" +
            "group by med_chrgitm_type")
    List<IteminfoUpld> totalCost(@Param("setlId") String setlId);

    @Select("select  med_chrgitm_type    as med_chrgitm,\n" +
            "        sum(det_item_fee_sumamt)  as ${feeName} " +
            "from t_si_setl_fee_detl " +
            "where setl_id = #{setlId} " +
            "  and chrgitm_lv = #{chrgitmLv} " +
            "group by med_chrgitm_type")
    List<IteminfoUpld> aAndBFees(@Param("setlId") String setlId,
                                 @Param("chrgitmLv") String chrgitmLv,
                                 @Param("feeName") String feeName);

    @Select("select med_chrgitm_type as med_chrgitm,\n" +
            "       sum(case\n" +
            "               when chrgitm_lv = '03' then det_item_fee_sumamt\n" +
            "               else det_item_fee_sumamt * selfpay_prop end) as fulamtOwnpayAmt\n" +
            "from t_si_setl_fee_detl\n" +
            "where setl_id = #{setlId}\n" +
            "group by med_chrgitm_type")
    List<IteminfoUpld> fulamtOwnpayAmt(@Param("setlId") String setlId);

    // -- 01 甲类
    @Select("select med_chrgitm_type as med_chrgitm," +
            "claa_sumfee=cast(sum(det_item_fee_sumamt * (1- selfpay_prop)) 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 * (1- selfpay_prop)) 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);


    /**
     * 重症监护信息
     *
     * @param patNo 住院号
     * @param times 住院次数
     * @return 返回转科信息
     */
    @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);


    /**
     * 保存结算单上传返回的 流水号
     *
     * @param setlListId 结算id
     * @param setlId     返回的数据
     */
    @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("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,a.ipt_med_type " +
            "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 and " +
            " ${ew.sqlSegment} " +
            ") temp where rowNumber > ${pageSize} * (${currentPage} - 1)")
    List<SiSetlinfoTemp> huoQuJieSuanRenYuan(@Param(Constants.WRAPPER) QueryWrapper<?> queryWrapper,
                                             @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," + "case  when yb_code is null then rtrim(dis_diag) else rtrim(yb_code) end dis_diag," + "case  when yb_name is null then rtrim(dis_diag_comment) else rtrim(yb_name) end dis_diag_comment," + "op_id_code, op_diag_date, dis_diag_bzfx, dis_diag_status, operation, si_diag_type,admiss_cond  " + "from ${tableName} left join zd_icd_code_new on (dis_diag = code) " + "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 *,bldCatName = (select name from bld_cat_code where bld_cat_code.code = bld_cat) " + "from patient_bld_info " + "where pat_no = #{patNo} " + "  and times = #{times}")
    List<PatientBldInfo> shuXueXinXi(@Param("patNo") String patNo, @Param("times") Integer times);


    @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 ,op_start_date, op_end_date, anst_start_date, anst_end_date," + "       op_start_date as oprn_oprt_begntime," + "       op_end_date as  oprn_oprt_endtime," + "       anst_start_date as anst_begntime," + "       anst_end_date as anst_endtime, " + " case when yb_code is null then rtrim(ssbm) else rtrim(yb_code) end  ssbm ," + " case when yb_name is null then rtrim(ssmc) else rtrim(yb_name) end  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} left join zd_icd9_cm3 on (ssbm = zd_icd9_cm3.code)  " + "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," +
            "case\n" +
            "                    when yb_code is null then rtrim(dis_diag)\n" +
            "                    else rtrim(yb_code)\n" +
            "                    end             dis_diag," +
            "case\n" +
            "                    when yb_name is null then rtrim(dis_diag_comment)\n" +
            "                    else rtrim(yb_name)\n" +
            "                    end             dis_diag_comment " +
            " from ${tableName} left join " +
            "     zd_icd_code_new on (dis_diag = code) " +
            "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 rtrim(yb_code) as code,rtrim(name) as name," + "rtrim(yb_code) as yb_code" + " from " + "(SELECT ROW_NUMBER() OVER(ORDER BY code) AS Number,  * " + "from zd_icd9_cm3 where (code like #{content} or name like #{content} or yb_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 zd_icd9_cm3 where (code like #{content} or name" + " like #{content} or py_code like #{content} or yb_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," +
            "op_start_date,op_end_date,anst_start_date,anst_end_date) " +
            "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,#{param.opStartDate, jdbcType = TIMESTAMP},#{param.opEndDate, jdbcType = TIMESTAMP}," +
            "#{param.anstStartDate, jdbcType = TIMESTAMP},#{param.anstEndDate, jdbcType = TIMESTAMP})" +
            "</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);

    @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 si_code = #{ntly}")
    String getNtlyName(String ntly);

    @Select("select rtrim(name) from zd_nation_code where wjw_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 yb_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 dise_code as diag_code," +
            " dise_name as diag_name, " +
            " oprn_oprt_name = '00000'," +
            " oprn_oprt_code = '无手术'  " +
            " from t_si_pat_info " +
            "where pat_no = #{patNo} and times = #{times} " +
            "and ledger_sn = #{ledgerSn}")
    List<OpspdiseinfoUpld> diagnosisOfMenterSDisease(@Param("patNo") String patNo,
                                                     @Param("times") Integer times,
                                                     @Param("ledgerSn") Integer ledgerSn);

    @Select("select diag_code as diag_code, " +
            "       diag_name as diag_name, " +
            "       '00000'   as oprn_oprt_name, " +
            "       '无手术'  as oprn_oprt_code " +
            "from t_si_mz_diag\n" +
            "where pat_no = #{patNo} and times = #{times} ")
    List<OpspdiseinfoUpld> outpatientDiagnosis(@Param("patNo") String patNo,
                                               @Param("times") Integer times);

    @Select("select rtrim(a.name)                                        as psnName, " +
            "       isnull(nullif(rtrim(b.occupation_code), " +
            "                     ''), " +
            "              90)                                           as prfs, " +
            "       (select si_code from zd_country_code where code = b.country)   as ntly, " +
            "       a.sex                                                as gend, " +
            "       a.birth_day                                          as brdy, " +
            "       a.age                                                as age, " +
            "       a.social_no                                          as certno, " +
            "       a.response_type, " +
            "       a.phone_no                                           as coner_tel, " +
            "       a.adress                                             as currAddr, " +
            "       a.certificate_type                                   as patnCertType, " +
            "       d.setl_time                                          as setl_begn_date, " +
            "       d.setl_time                                          as setl_end_date, " +
            "       c.psn_no                                             as psn_no," +
            "       c.psn_no                                             as hi_no,    " +
            "       rtrim(a.patient_id) + '_' + cast(a.times as varchar) as bizSn," +
            "       dclaTime = getdate()," +
            "       c.insutype as hi_type," +
            "       isnull(work_unit,b.employer_name)                    as emp_name, " +
            "       isnull(work_address,employer_district)               as emp_addr, " +
            "       isnull(work_tel,employer_tel)                        as emp_tel, " +
            "       employer_zipcode                                     as poscode, " +
            "       relation_name                                        as coner_name, " +
            "       (select rtrim(yb_code) from zy_zd_relative_code where code = relation_code) " +
            "                                                            as patn_rlts, " +
            "       relation_district                                    as coner_addr, " +
            "       relation_tel                                         as coner_tel, " +
            "       c.insuplc_admdvs                                     as insuplc," +
            " (select wjw_code from zd_nation_code where code =  b.nation_code)   as naty/*民族*/,\n" +
            "       '01'                                                 as patnRlts," +
            "       a.adress                                             as conerAddr," +
            "       '1'                                                  as hiPaymtd, " +
            "       cast(fund_pay_sumamt as decimal(16,2)) as  fund_pay_sumamt," +
            "       cast(maf_pay as decimal(16,2)) as maf_pay,  " +
            "psn_idet_type as  sp_psn_type " +
            "from mz_patient_mi a " +
            "         left join " +
            "     a_patient_mi b on (a.patient_id = b.mz_no) " +
            "         left join t_si_pat_info c on (a.patient_id = c.pat_no and c.times = #{times}) " +
            "         left join t_si_setlinfo d on (a.patient_id = d.pat_no and d.times = #{times} and revoked = 0 ) " +
            "where a.patient_id = #{patNo}")
    SetlinfoUpld mzPatInfo(@Param("patNo") String patNo,
                           @Param("times") Integer times);

    @Select("select setl_id,mdtrt_id,insuplc_admdvs " +
            "from t_si_setlinfo " +
            "where pat_no =  #{patNo} " +
            "  and times = #{times} and revoked = 0 ")
    SetlinfoUpld mzJieSuanXinXi(@Param("patNo") String patNo, @Param("times") Integer times);

    @Update("update t_si_setlinfo set ipt_med_type = #{iptMenType} where setl_id = #{setlId} and revoked = 0")
    void modifyMedicalCategory(@Param("iptMenType") String iptMenType, @Param("setlId") String setlId);

    @Insert("insert into patient_bld_info (pat_no, times, bld_cat, bld_amt, bld_unit, bld_count) " + "values (#{patNo}, #{times}, #{bldCat}, cast(#{bldAmt} as decimal(6,1)), #{bldUnit}, (select isnull(max(bld_count), 0) from patient_bld_info where pat_no = ''))")
    void addBloodTransfusionInformation(PatientBldInfo param);

    @Update("update patient_bld_info set bld_cat = #{bldCat} ,bld_amt = cast(#{bldAmt} as decimal(6,1)) ,bld_unit = #{bldUnit} " + "where id = #{id}")
    void updateBloodTransfusionVarieties(PatientBldInfo param);

    @Delete("delete patient_bld_info where id = #{id}")
    void removeBloodTransfusion(Integer id);

    @Select("select setl_list_id from t_si_setlinfo where " +
            "pat_no = #{patNo} and times = #{times} and ledger_sn = #{ledgerSn} and revoked = 0")
    String billingListSerialNumber(@Param("patNo") String patNo,
                                   @Param("times") Integer times,
                                   @Param("ledgerSn") Integer ledgerSn);

}