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

package thyyxxk.webserver.dao.his.medicalinsurance;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import thyyxxk.webserver.entity.medicalinsurance.setllistupload.*;

import java.util.List;

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

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

    @Select("select a.mdtrt_id,   " + // -- 就诊ID
            "       a.setl_id,    " + // -- 结算ID
            "       a.psn_no as hi_no,  " + //--  医保编号
            "       medcasno=(a.pat_no+'_'+cast(a.times as varchar)),  " + //-- 病案号
            "       dcla_time=getdate(), " + //--  申报时间
            "       a.psn_name,  " + //-- 人员姓名
            "       a.gend, " + //  -- 性别
            "       a.brdy,  " + //-- 出生日期
            "       a.age,  " + // -- 年龄
            "       a.naty,  " + // -- 名族
            "       a.certno, " + //  -- 证件号码
            "       a.psn_cert_type as patnCertType,  " + // -- 患者证件类别
            "       b.insuplc_admdvs as insuplc,  " + //  -- 参保地
            "       a.psn_cash_pay as psnSelfpay,  " + // -- 个人自付
            "       a.fulamt_ownpay_amt as psnOwnpay,  " + // -- 个人自费c
            "       a.acct_pay, " + // -- 个人账户支出
            "       a.psn_cash_pay as psnCashpay,  " + // --  个人现金支付
            "       hiPaymtd='3',  " + // --  医保支付方式
            "       a.clr_optins as hsorg,  " + // -- 医保机构
            "       a.insutype as hi_type,  " + // -- 枚举 MdcsType
            "        a.med_type as trt_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.pat_no = #{patNo} and a.times = #{times}")
    SetlinfoUpld setlinfo2(@Param("patNo") String patNo,
                           @Param("times") Integer times);

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

    // 这里是 住院诊断消息
    @Select("select si_diag_type as diag_type,  " +
            "        rtrim(dis_diag) as diag_code,  " +
            "       rtrim(dis_diag_comment) as diag_name,  " +
            "       case when dis_diag_no = 1 then 1 else 0 end as maindiag_flag  " +
            "from zy_dis_diag_yb  " +
            "where inpatient_no = #{patNo} and admiss_times = #{times}")
    List<DiseinfoUpld> diseinfo(@Param("patNo") String patNo,
                                @Param("times") Integer times);

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


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

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

    // -- 01 甲类
    @Select("select med_chrgitm_type as med_chrgitm,sum(det_item_fee_sumamt) as claa_sumfee 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,sum(det_item_fee_sumamt) as clab_amt 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);

}