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 org.apache.ibatis.annotations.Update;
import thyyxxk.webserver.entity.datamodify.GetDropdownBox;
import thyyxxk.webserver.entity.medicalinsurance.query.SiPatInfo;
import thyyxxk.webserver.entity.medicalinsurance.setllistupload.*;

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

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


    @Select("select a.mdtrt_id,   " + // -- 就诊ID
            "       a.setl_id,    " + // -- 结算ID
            "       a.psn_no as hi_no,  " + //--  医保编号
            "       medcasno=(a.pat_no+'_'+cast(a.times as varchar)),  " + //-- 病案号
            "       dcla_time=getdate(), " + //--  申报时间
            "       a.psn_name,  " + //-- 人员姓名
            "       a.gend, " + //  -- 性别
            "       a.brdy,  " + //-- 出生日期
            "       a.age,  " + // -- 年龄
            "       a.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
            "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} " +
            "and a.revoked = 0 ")
    SetlinfoUpld setlinfo1(@Param("patNo") String patNo,
                           @Param("times") Integer times,
                           @Param("ledgerSn") Integer ledgerSn);


    @Select("select  rtrim(country) as ntly, " +  // 国籍
            "        rtrim(social_no) as certno,  " + // 证件
            "        rtrim(occupation_code) as prfs,  " + // -- 职业 在 Prfs 中有对应的
            "        rtrim(home_street) as curr_addr ," + // -- 现住址
            "        rtrim(employer_name) as emp_name, " + //-- 单位名称
            "        rtrim(employer_street) as emp_addr,  " + //-- 单位地址
            "        rtrim(employer_tel) as  emp_tel, " +
            "        rtrim(employer_zipcode) as poscode, " +
            "       rtrim(relation_name) as coner_name, " +
            "       relation_code as  patn_rlts,  " + // TODO: 2021/11/26  这里可能要转成医保的类型
            "       rtrim(relation_tel) as  coner_tel " + //  联系人电话
            "from a_patient_mi " +
            "where inpatient_no = #{patNo} or mz_no = #{patNo}")
    SetlinfoUpld setlinfo2(@Param("patNo") String patNo);


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


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

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

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

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

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

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

    // 这里是 住院诊断消息
    @Select("select si_diag_type as diag_type,  " +
            "        rtrim(dis_diag) as diag_code,  " +
            "       rtrim(dis_diag_comment) as diag_name,  " +
            "       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);


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


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

    @Select("<script>" +
            "select a.pat_no,a.times,a.ledger_sn 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 revoked = 0 and setl_type = 21 and " +
            "b.insuplc_admdvs like '4301%' and b.insuplc_admdvs != '430182' and setl_list_id is null " +
            "and a.setl_time &gt;= #{startTime} and a.setl_time &lt;= #{endTime} " +
            "<if test=\"insutype!=null and insutype!=''\">" +
            "and a.insutype=#{insutype}" +
            "</if>" +
            "</script>")
    List<SiPatInfo> getHuanZheXinXi(@Param("startTime") String startTime,
                                    @Param("endTime") String endTime,
                                    @Param("insutype") String insutype);
}