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.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
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.log.SiLog;
import thyyxxk.webserver.entity.medicalinsurance.setllistupld.*;
import thyyxxk.webserver.entity.querydata.SiSetlinfoTemp;
import thyyxxk.webserver.entity.querydata.TAutoUploadBill;

import java.math.BigDecimal;
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 extends BaseMapper<SetlinfoUpld> {

    @Select("select count(1) from t_case_frontsheet_main where bah = #{patNo} " + "  and admiss_times = #{times} and file_status =1")
    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)) ,  " + // -- 个人自付 TODO
            "       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-hosp_part_amt) 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 = a.country)        as ntly /*国籍*/,\n" +
            "                     a.social_no                                  as certno /*证件*/,\n" +
            "                     a.occupation                                 as prfs /*职业 在 Prfs 中有对应的*/,\n" +
            "                     a.live_place_combo                           as curr_addr/*现住址*/,\n" +
            "                     a.unit_name                                  as emp_name /*单位名称*/,\n" +
            "                     a.unit_place                                 as emp_addr /*单位地址*/,\n" +
            "                     a.unit_phone                                 as emp_tel /*工作单位手机*/,\n" +
            "                     a.addr_zip_code                              as poscode /* 邮编*/,\n" +
            "                     (select wjw_code from zd_nation_code where code = a.nation)          as naty/*民族*/,\n" +
            "                     a.contact_name                               as coner_name/*联系人姓名*/,\n" +
            "a.birth_date,a.age,"+
            "                     (select rtrim(yb_code)\n" +
            "                      from zy_zd_relative_code\n" +
            "                      where code = a.contact_relation)            as patn_rlts /*与患者的关系*/,\n" +
            "                     a.contact_phone                              as coner_tel /*联系人电话*/,\n" +
            "                     a.coma_days_before_admiss + '/' + a.coma_hours_before_admiss + '/' +\n" +
            "                     a.coma_minutes_before_admiss                 as pwcry_bfadm_coma_dura/*颅脑损伤患者入院前昏迷时长*/,\n" +
            "                     a.coma_days_after_admiss + '/' + a.coma_hours_after_admiss + '/' +\n" +
            "                     a.coma_minutes_after_admiss                  as pwcry_afadm_coma_dura/*颅脑损伤患者入院后昏迷时长*/,\n" +
            "                     a.zy_dismiss_way                             as dscg_way /*离院方式*/,\n" +
            "                     a.dismiss_destination                        as acp_medins_name/*拟接收机构名称*/,\n" +
            "                     a.admiss_again_in_one_month                  as days_rinp_flag_31 /*出院31天内再住院计划标志*/,\n" +
            "                     a.admiss_again_purpose                       as days_rinp_pup_31/*出院31天内再住院目的*/,\n" +
            "                     rtrim(isnull(a.main_doctor_name,a.admiss_doctor_name))                           as chfpdr_name/*主诊医师姓名*/,\n" +
            "                     (select rtrim(yb_code)\n" +
            "                      from a_employee_mi\n" +
            "                      where code = isnull(a.main_doctor,a.admiss_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 = isnull(a.trans_dept_code,b.zk_ward))                   as refldept_dept /*转科科别*/,\n" +
            "                     (select rtrim(si_caty)\n" +
            "                      from zd_unit_code\n" +
            "                      where code = isnull(a.dismiss_dept_code,b.zk_ward)) as dscg_caty /*出院科别*/,\n" +
            "                     b.zy_serial_no                               as biz_sn /*业务流水号*/,\n" +
            "                     case a.age_days\n" +
            "                         when '-' then 0\n" +
            "                         else a.age_days\n" +
            "                         end                                    as nwb_age/*新生儿年龄*/,\n" +
            "                     a.contact_addr_name                          as coner_addr /*联系人地址*/,\n" +
            "                     case a.new_born_weight\n" +
            "                         when '-' then 0\n" +
            "                         else a.new_born_weight\n" +
            "                         end                                    as nwb_bir_wt/*新生儿体重*/,\n" +
            "    a.birth_date brdy , a.age , a.sex gend,"+
            "    resp_nurs_code = (select rtrim(yb_code)\n" +
            "                      from a_employee_mi\n" +
            "                      where a_employee_mi.code = isnull(a.duty_nurse,b.job_nurse)) /*责任护士*/,\n" +
            "   case when a.duty_nurse is not null then a.duty_nurse_name else  (select name\n" +
            "                      from a_employee_mi\n" +
            "                      where a_employee_mi.code = b.job_nurse) end as resp_nurs_name\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 rtrim(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_code as wmDiseCode, clinic_diag_str otpWmDise " +
            "from t_case_frontsheet_main " +
            "where bah = #{patNo} " +
            "  and admiss_times = #{times}" +
            "  and nullif(clinic_diag_code, '') is not null ")
    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, " +
            "       admiss_date                            as setl_begn_date, " +
            "       admiss_date                            as adm_time," +
            "       case " + "           when datediff(day, admiss_date, dis_date) <= 0 then 1 " +
            "           else datediff(day, admiss_date, 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 sum(charge_amount) 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')")
    Integer teJiHuLi(@Param("patNo") String patNo, @Param("times") Integer times, @Param("ledgerSn") Integer ledgerSn, @Param("infantFlag") Integer infantFlag);

    @Select("select sum(charge_amount) 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')")
    Integer yiJiHuLi(@Param("patNo") String patNo, @Param("times") Integer times, @Param("ledgerSn") Integer ledgerSn, @Param("infantFlag") Integer infantFlag);

    @Select("select sum(charge_amount) 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')")
    Integer 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')")
    Integer 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 when yb_code is null then rtrim(dis_diag) else rtrim(yb_code) end as diag_code," +
            "case when yb_name is null then rtrim(dis_diag_comment) else rtrim(yb_name) end as 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(yb_code) 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 rtrim(receipt_no) 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" +
            " and obsolete_dcount_no = 0 ")
    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 - fulamt_ownpay_amt)  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(fulamt_ownpay_amt) as fulamtOwnpayAmt\n" +
            "FROM t_si_setl_fee_detl " +
            "where setl_id = #{setlId}  " +
            "group by med_chrgitm_type")
    List<IteminfoUpld> patientPays(@Param("setlId") String setlId);

    @Select("SELECT sum(DATEDIFF(second, start_time, end_time))\n" +
            "from yz_inact_order\n" +
            "where order_code in ('05181', '06563')\n" +
            "  and inpatient_no = #{patNo}\n" +
            "  and admiss_times = #{times}")
    Integer ventilatorUseTime(@Param("patNo") String patNo, @Param("times") Integer times);

    /**
     * 重症监护信息
     *
     * @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.age,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, b.dis_date, a.hi_paymtd," +
            "       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," +
            "       insuplc_admdvs, " +
            "       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("  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 and " +
            " ${ew.sqlSegment} ")
    long huoQuJieSuanRenYuanTotal(@Param(Constants.WRAPPER) QueryWrapper<?> queryWrapper);


    @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," +
            "dept_code," +
            "(select name from zd_unit_code where code = dept_code) as dept_code_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<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) rtrim(yb_code) from a_employee_mi where code = ssys) as oper_dr_code, " +
            "(select top(1) rtrim(yb_code) from a_employee_mi where code = mzys) as anst_dr_code," +
            "dept_code," +
            "(select name from zd_unit_code where code = dept_code) as dept_code_name " +
            "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,rtrim(small_dept) small_dept, " + "       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 when yb_code is null then rtrim(dis_diag) else rtrim(yb_code) end as dis_diag," +
            "case when yb_name is null then rtrim(dis_diag_comment) else rtrim(yb_name) end as 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 del_flag=0 and (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 del_flag=0 and (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);
    @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}; ")
    void delDiseAndOprt(@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("insert into t_yb_setl_modify_req (pat_no, times, req_op_id, req_op_name, req_op_date, req_remark, audit_staff, audit_name, audit_date, audit_remark, audit_flag) " +
            "  values (#{patNo}, #{times}, #{reqOpId}, #{reqOpName}, #{reqOpDate}, #{reqRemark}, #{auditStaff}, #{auditName}, #{auditDate}, #{auditRemark}, #{auditFlag})")
    void insertSetlModifyReq(TYbSetlModifyReq tYbSetlModifyReq);

    @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,dept_code) " +
            "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},#{param.deptCode})" +
            "</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,dept_code) " +
            "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}," +
            "#{param.deptCode,jdbcType = VARCHAR})" +
            "</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} " +
            "and nullif(dise_code, '') is not null")
    List<OpspdiseinfoUpld> diagnosisOfMenterSDisease(@Param("patNo") String patNo,
                                                     @Param("times") Integer times,
                                                     @Param("ledgerSn") Integer ledgerSn);

    @Select("select icd_code_new as diag_code " +
            "from mz_visit_table " +
            "where nullif(icd_code_new, '') is not null " +
            "  and patient_id = #{patNo} and times = #{times}")
    OpspdiseinfoUpld outpatientDiagnosis1(@Param("patNo") String patNo,
                                          @Param("times") Integer times);

    @Select("select top 1 yb_code  as diag_code,yb_name as diag_name " +
            "from zd_icd_code_new where yb_code = #{ybCode} ")
    OpspdiseinfoUpld outpatientSlowDiagnosis(String ybCode);

    @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> outpatientDiagnosis2(@Param("patNo") String patNo,
                                                @Param("times") Integer times);


    @Select("select top 1 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, " +
            "       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" +
            "       '1'                                                 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," +
            "(select doctor_code\n" +
            "from mz_visit_table\n" +
            "where patient_id = #{patNo}\n" +
            "  and times = #{times}) as chfpdr_code " +
            "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 opsp_diag_caty = (select si_caty from zd_unit_code where code = visit_dept_code),\n" +
            "                        visit_date as opsp_mdtrt_date\n" +
            "from mz_visit_table\n" +
            "where patient_id = #{patNo} and times = #{times}")
    SetlinfoUpld mzPatInfo1(@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);

    @Select("select pat_no, times, ledger_sn,setl_time " +
            "from t_si_setlinfo " +
            "where nullif(setl_list_id,'') is null " +
            "  and revoked = 0 " +
            "  and insuplc_admdvs like '43%' " +
            "  and DATEDIFF(day,setl_time,GETDATE()) >=6" +
            "and setl_time >= '${firstDate}'"
            )
    List<SiSetlinfoTemp> getBillingPatientsToday(String firstDate);


    /**
     * 自动上传的信息
     *
     * @param patNo    住院号
     * @param times    住院次数
     * @param ledgerSn 账页号
     * @param logText  日志
     */
    @Select("insert into t_auto_upload_bill (pat_no, times, ledger_sn,log_text,type_flag,flag,setl_time, upload_date) " +
            "values (#{patNo},#{times},#{ledgerSn},#{logText},#{typeFlag},#{flag},#{setlTime},'${uploadDate}')")
    void insertAutoLongPassError(@Param("patNo") String patNo,
                                 @Param("times") Integer times,
                                 @Param("ledgerSn") Integer ledgerSn,
                                 @Param("logText") String logText,
                                 @Param("typeFlag") Integer typeFlag,
                                 @Param("flag") Integer flag,
                                 @Param("uploadDate") String uploadDate,
                                 @Param("setlTime") Date setlTime
                                 );

    @Select("select * " +
            "from t_auto_upload_bill " +
            "where upload_date >= '${startTime}' " +
            "  and upload_date <= '${endTime}'")
    List<TAutoUploadBill> exportAutoUploadInfo(@Param("startTime") String startTime,
                                               @Param("endTime") String endTime);


    @Select("select count(1)\n" +
            "from t_si_setlinfo a,\n" +
            "     t_si_log b\n" +
            "where (b.infno = '4101' and a.pat_no = b.pat_no and a.times = b.times and a.ledger_sn = b.ledger_sn)")
    Integer 获取已经上传的数据总数();

    @Select("select top ${pageSize} *\n" +
            "from (select row_number() over (order by create_datetime) row_index, create_datetime, body, result\n" +
            "      from t_si_setlinfo a,\n" +
            "           t_si_log b\n" +
            "      where (b.infno = '4101' and a.pat_no = b.pat_no and a.times = b.times and a.ledger_sn = b.ledger_sn)) temp\n" +
            "where row_index > (${currentPage} - 1) * ${pageSize}\n" +
            "order by row_index")
    List<SiLog> 获取上传的文件(int currentPage, int pageSize);

    /**
     * 是否有上传结算单信息
     *
     * @param setlId
     * @return
     */
    @Select("select max(setl_id) from t_settlement_setlinfo where setl_id = #{setlId}")
    Integer whetherThereIsUploadInfo(String setlId);

    /**
     * 根据setld 删除
     *
     * @param id
     */
    @Delete("delete t_settlement_diseinfo where setl_id = #{setlId};" +
            "delete t_settlement_opspdiseinfo where setl_id = #{setlId};" +
            "delete t_settlement_oprninfo where setl_id = #{setlId}; " +
            "delete t_settlement_setlinfo where setl_id = #{setlId};" +
            "delete t_settlement_icuinfo where setl_id = #{setlId};" +
            "delete t_settlement_bldinfo where setl_id = #{setlId};" +
            "delete t_settlement_payinfo where setl_id = #{setlId};" +
            "delete t_settlement_iteminfo where setl_id = #{setlId};")
    void deleteAccordingToSetlId(String id);

    /**
     * 插入结算单诊断信息
     *
     * @param setlId id
     * @param list   诊断数据
     */
    @Insert("<script>" +
            "insert into t_settlement_diseinfo " +
            "(setl_id, subscript, diag_type, diag_code, diag_name, adm_cond_type, maindiag_flag) " +
            "values " +
            "<foreach collection='list' item='item' index='index' separator=','>" +
            "(#{setlId}, ${index} + 1, #{item.diagType}, #{item.diagCode}, #{item.diagName}, #{item.admCondType}, " +
            "#{item.maindiagFlag})" +
            "</foreach>" +
            "</script>")
    void insertStatementDiagnosisInfo(String setlId, List<DiseinfoUpld> list);


    /**
     * 插入结算单基金支付信息
     *
     * @param setlId id
     * @param list   基金支付信息
     */
    @Insert("<script>" +
            "insert into t_settlement_payinfo (setl_id, subscript, fund_pay_type, fund_payamt) " +
            " values " +
            "<foreach collection='list' item='item' index='index' separator=','>" +
            " (#{setlId},${index} + 1,#{item.fundPayType},#{item.fundPayamt}) " +
            "</foreach>" +
            "</script>")
    void insertFundPaymentInfoOfStatement(String setlId, List<PayinfoUpld> list);


    /**
     * 插入收费项目信息
     *
     * @param setlId id
     * @param list   收费项目
     */
    @Insert("<script>" +
            "insert into t_settlement_iteminfo (setl_id, subscript, med_chrgitm, amt, claa_sumfee, clab_amt, fulamt_ownpay_amt," +
            " oth_amt)" +
            "values " +
            "<foreach collection='list' item='item' index='index' separator=','>" +
            "(#{setlId},${index} + 1,#{item.medChrgitm},#{item.amt},#{item.claaSumfee},#{item.clabAmt},#{item.fulamtOwnpayAmt}," +
            "#{item.othAmt})" +
            "</foreach>" +
            "</script>")
    void insertChargingItemInfo(String setlId, List<IteminfoUpld> list);

    /**
     * 插入门诊慢特病
     *
     * @param setlId id
     * @param list   数据
     */
    @Insert("<script>" +
            "insert into t_settlement_opspdiseinfo (setl_id, subscript, diag_name, diag_code, oprn_oprt_name, oprn_oprt_code," +
            "maindiag_flag) " +
            " values " +
            "<foreach collection='list' item='item' index='index' separator=','>" +
            "(#{setlId}, ${index} + 1, #{item.diagName}, #{item.diagCode}, #{item.oprnOprtName}, #{item.oprnOprtCode}," +
            "#{item.maindiagFlag})" +
            "</foreach>" +
            "</script>")
    void insertionOfOutpatientChronicDiseases(String setlId, List<OpspdiseinfoUpld> list);

    /**
     * 插入手术操作信息
     *
     * @param setlId id
     * @param list   数据
     */
    @Insert("<script>" +
            "insert into t_settlement_oprninfo(setl_id, subscript, oprn_oprt_type, oprn_oprt_name, oprn_oprt_code," +
            "oprn_oprt_date, " +
            "anst_way, oper_dr_name, oper_dr_code, anst_dr_name, anst_dr_code, oprn_oprt_begntime,\n" +
            "oprn_oprt_endtime, anst_begntime, anst_endtime)\n" +
            "values " +
            "<foreach collection='list' item='item' index='index' separator=','>" +
            "(#{setlId},${index} + 1,#{item.oprnOprtType},#{item.oprnOprtName},#{item.oprnOprtCode}," +
            "#{item.oprnOprtDate},#{item.anstWay},#{item.operDrName},#{item.operDrCode},#{item.anstDrName},#{item.anstDrCode}," +
            "#{item.oprnOprtBegntime},#{item.oprnOprtEndtime},#{item.anstBegntime},#{item.anstEndtime})" +
            "</foreach>" +
            "</script>")
    void insertSurgicalOperationInfo(String setlId, List<OprninfoUpld> list);

    /**
     * 插入重症监护信息
     *
     * @param setlId id
     * @param list   数据
     */
    @Insert("<script>" +
            "insert into t_settlement_icuinfo (setl_id, subscript, scs_cutd_ward_type, scs_cutd_inpool_time, " +
            "scs_cutd_exit_time,scs_cutd_sum_dura) " +
            "values " +
            "<foreach collection='list' item='item' index='index' separator=','>" +
            "(#{setlId},${index} + 1,#{item.scsCutdWardType},#{item.scsCutdInpoolTime}," +
            "#{item.scsCutdExitTime},#{item.scsCutdSumDura})" +
            "</foreach>" +
            "</script>")
    void insertIntensiveCareInfor(String setlId, List<IcuinfoUpld> list);

    /**
     * 插入 输血信息
     *
     * @param setlId id
     * @param list   数据
     */
    @Insert("<script>" +
            "insert into t_settlement_bldinfo (setl_id, subscript, bld_cat, bld_amt, bld_unt) " +
            " values " +
            "<foreach collection='list' item='item' index='index' separator=','>" +
            " (#{setlId},${index} + 1,#{item.bldCat},#{item.bldAmt},#{item.bldUnt})" +
            "</foreach>" +
            "</script>")
    void insertTransfusionInfo(String setlId, List<Bldinfo> list);
    /**
     * @description:插入结算单审核日志表
     * @author: lihong
     * @date: 2022/12/12 16:50
     * @param: setlAuditLog
     **/
    @Insert("insert into  t_setl_audit_log(create_name, create_code, remark, audit_flag, audit_type, pat_no, times) " +
            "    values (#{createName},#{createCode},#{remark},#{auditFlag},#{auditType},#{patNo},#{times})")
    void insertSetlAuditLog(SetlAuditLog setlAuditLog);
    /**
     * @description:查询审核日志
     * @author: lihong
     * @date: 2022/12/13 9:25
     * @param: patNo
     * @param: times
     **/
    @Select("select create_name," +
            "       create_code," +
            "       create_time," +
            "       remark," +
            "       audit_flag," +
            "       audit_type," +
            "        id"+
            "        from  t_setl_audit_log where pat_no=#{patNo} and times=#{times}  order by create_time")
    List<SetlAuditLog> listSetlAuditLog(String patNo, Integer times);
    /**
     * @description:删除审核申请记录
     * @author: lihong
     * @date: 2022/12/13 10:17
     * @param: patNo
     * @param: times
     **/
    @Delete("delete  from  t_yb_setl_modify_req where pat_no=#{patNo} and times=#{times} ")
    void deleteModifReq(String patNo, Integer times);
    @Select("select audit_flag  from  t_yb_setl_modify_req where pat_no=#{patNo} and times=#{times}")
    Integer getAuditFlag(String patNo, Integer times);
    @Select(" <script> " +
            " select b.audit_flag from (" +
            "                  select pat_no + cast(times as varchar)  as visit_id,pat_no,times  from  t_yb_setl_modify_req\n" +
            "                  ) a join t_yb_setl_modify_req b on a.pat_no=b.pat_no and a.times = b.times" +
            " where a.visit_id in"+
                    "<foreach collection='visitIds' item='patNo' open='(' close=')' separator=','>"+
            " #{patNo}"+
            " </foreach>"+
                        " </script>")
    List<Integer> listSetlModifyReq(List<String> visitIds);
    @Select("<script>" +
            " select rtrim(code) code ,rtrim(yb_code) yb_code,rtrim(yb_name) yb_name from  ${tableName} where code in" +
            " <foreach collection='glCodes' item='item' open='(' close=')' separator=','>" +
            " #{item}" +
            " </foreach>"+
            " </script>")
    List<Map<String,String>> listYbDiseOrOprt(@Param("glCodes") List<String> glCodes,@Param("tableName") String tableName);
    @Select("<script>" +
            " select rtrim(yb_code) yb_code,rtrim(code) code from a_employee_mi  where code in" +
            " <foreach collection='ssysCode' item='item' open='(' close=')' separator=','>" +
            " #{item}" +
            " </foreach>"+
            " </script>")
    List<Map<String,String>> listYbSsys(List<String> ssysCode);
    @Select("<script>" +
            " select rtrim(code) code,rtrim(yb_code) yb_code,rtrim(name) yb_name  from  zd_anaesthesia where  code in " +
            " <foreach collection='mzfsCodes' item='item' open='(' close=')' separator=','>" +
            " #{item}" +
            " </foreach>"+
            " </script>")
    List<Map<String,String>> listZdAnaesthesia(List<String> mzfsCodes);
    /**
     * @description:查询字典值
     * @author: lihong
     * @date: 2022/12/26 9:47
     * @param: s
     * @param: s1
     **/
    @Select("select dict_value from   t_dict_data where dict_type=#{dictType} and dict_name=#{dictName} and status='0'"
            )
    String getDictValueByDictName(@Param("dictType") String dictType, @Param("dictName")String dictName);
    /**
     * @description:查询审核状态
     * @author: lihong
     * @date: 2022/12/26 11:02
     * @param: patNo
     * @param: times
     **/
    @Select("select audit_flag from t_yb_setl_modify_req where pat_no=#{patNo} and times=#{times} ")
    Integer qualityPass(@Param("patNo") String patNo, @Param("times") Integer times);
    @Select(" select count(*) from  zy_inactpatient where inpatient_no=#{patNo} and  admiss_times=#{times} ")
    Integer isInHospital(@Param("patNo")String patNo, @Param("times") Integer times);
    @Select("     select       rtrim(a.pat_no) pat_no,a.times,ledger_sn,psn_name,gend,brdy,insutype,psn_type,mdtrt_id,setl_id,setl_time,  \n" +
            "                   a.med_type,medfee_sumamt,fund_pay_sumamt,  \n" +
            "                   acct_pay,psn_cash_pay,clr_optins,clr_optins_name = (select name from t_region where code=clr_optins),  \n" +
            "                   outDeptName=(select rtrim(name) from zd_unit_code where code=isnull(zk_ward,small_dept)), \n" +
            "                   rtrim(isnull(zk_ward,small_dept)) as out_dept, \n" +
            "                   insuplc_admdvs,  \n" +
            "                   setl_list_id,datediff(day ,b.admiss_date ,b.dis_date) as act_ipt_days,b.operation,b.dec_type, \n" +
            "                   id,req_op_id,rtrim(req_op_name) req_op_name,req_op_date,req_remark, \n" +
            "            audit_staff,audit_name,audit_date,audit_remark,audit_flag, \n" +
            "            refer_physician_name = (select top 1 rtrim(name) from a_employee_mi where code = b.refer_physician),b.refer_physician,a.ipt_med_type  \n" +
            "            from t_si_setlinfo a left join  zy_inactpatient b on (a.pat_no = b.inpatient_no and a.times = b.admiss_times)  \n" +
            "            left join t_yb_setl_modify_req c on (a.pat_no = c.pat_no and a.times = c.times)  \n" +
            "            where revoked = 0\n" +
            "                and a.pat_no=#{patNo} and a.times=#{times} and a.ledger_sn=#{ledgerSn}")
    SiSetlinfoTemp getJieSuanDetail(String patNo, Integer times, Integer ledgerSn);

    @Select("select top 1 *  from  t_yb_setl_modify_req where pat_no=#{patNo} and times=#{times} ")
    TYbSetlModifyReq getSetlModifyReq(@Param("patNo") String patNo, @Param("times") Integer times);
    /**
     * @description:删除最近的历史记录
     * @author: lihong
     * @date: 2022/12/28 15:19
     * @param: patNo
     * @param: times
     **/
    @Delete("  delete" +
            "  from t_setl_audit_log" +
            "  where id in (" +
            "    select b.id" +
            "    from (" +
            "             select max(create_time) create_time, pat_no, times" +
            "             from t_setl_audit_log" +
            "             where pat_no = #{patNo}" +
            "               and times = #{times}" +
            "             group by pat_no, times" +
            "         ) a" +
            "             join t_setl_audit_log b on a.create_time = b.create_time and a.pat_no = b.pat_no and a.times = b.times" +
            ")")
    void deletelatestSetlAuditLog(@Param("patNo") String patNo, @Param("times") Integer times);
    @Update("update t_yb_setl_modify_req set audit_staff = '' ,audit_name='',audit_remark='',audit_flag=0 where pat_no=#{patNo} and times=#{times}")
    void updateSetlModifyReq(@Param("patNo") String patNo, @Param("times") Integer times);
    @Select(" select b.pat_no,b.times,b.ledger_sn,b.setl_time from t_yb_setl_modify_req a join t_si_setlinfo b on a.pat_no=b.pat_no and a.times=b.times" +
            " where a.audit_flag =1" +
            "  and b.revoked = 0" +
            "  and nullif(b.setl_list_id,'') is null" +
            "  and b.setl_time >= '${firstDate}'"
            )
    List<SiSetlinfoTemp> getQualityPass(String firstDate);

    @Select(" select  count(1) from    t_case_frontsheet_main where bah=#{inpatientNo} and admiss_times = #{admissTimes}")
    Integer queryCountTCaseFrontsheetMain(@Param("inpatientNo") String inpatientNo, @Param("admissTimes") Integer admissTimes);
    @Select(" select medfee_sumamt from t_si_setlinfo  where   pat_no = #{patNo} and  times =#{times} and ledger_sn = #{ledgerSn}")
    BigDecimal getsetlTotalFee(@Param("patNo") String patNo, @Param("times") Integer times,@Param("ledgerSn")Integer ledgerSn);
}