web_server/src/main/java/thyyxxk/webserver/dao/his/yibao/XiangMuLuRuDao.java

package thyyxxk.webserver.dao.his.yibao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;
import thyyxxk.webserver.entity.datamodify.FeiYongLeiXin;
import thyyxxk.webserver.entity.datamodify.GetDropdownBox;
import thyyxxk.webserver.entity.datamodify.ZyDetailCharge;
import thyyxxk.webserver.entity.yibao.WeiGuiFeiYongFenXi;

import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 * 描述： 项目录入
 * </p>
 *
 * @author xc
 * @date 2021-08-02 10:37
 */
@Mapper
public interface XiangMuLuRuDao extends BaseMapper<ZyDetailCharge> {


    @Select("<script>" +
            "select a.admiss_times, a.inpatient_no, a.order_no, a.gen_time, a.ledger_sn, a.detail_sn,  " +
            "                   a.trans_flag_yb,    " +
            "       a.charge_date, a.op_id_code, d.name op_name, a.charge_code, a.charge_fee, a.charge_status ,  " +
            "       bill_item_code= b.bill_item_zy, bill_item_name=(select name from zy_bill_item where code = b.bill_item_zy),  " +
            "       charge_name=b.name, spec=b.charge_unit, a.charge_code_mx, a.serial, price=0.0000, charge_amount=a.charge_amount,  " +
            "       exec_dept=(select name from zd_unit_code where code = a.exec_unit),  " +
            "       dept_code = (select name from zd_unit_code where code = a.ward_code) ,  " +
            "       self_flag=isnull(a.self_flag,0), suppress_flag=a.suppress_flag, group_no='00',  " +
            "       separate_flag=a.separate_flag, ope_flag=a.ope_flag, drugname=c.name, isnull(a.ori_detail_sn,0) ori_detail_sn  " +
            "    FROM  (select top ${pageSize} * from (select ROW_NUMBER() OVER (order by charge_date ${riQiPaiXu} ) as RowNumber,* from zy_detail_charge a " +
            "where  a.charge_code  " +
            "like 'BILL%' and a.ledger_sn = #{ledgerSn}  and trans_flag_yb &lt;&gt; 2 and a.order_no &lt;&gt; 6 and inpatient_no = #{inpatientNo}  " +
            "and admiss_times = #{admissTimes} and a.infant_flag = #{infantFlag} " +
            "<if test=\"chargeCode != null and chargeCode != '' \">" +
            "and charge_code_mx = #{chargeCode} " +
            "</if>" +
            "<if test=\"startTime != null and startTime!= '' \">" +
            "and charge_date &gt;= #{startTime} and charge_date &lt;= #{endTime} " +
            "</if>" +
            "<if test=\"dept != null and dept != '' \"> " +
            "and exec_unit=#{dept}   " +
            "</if>" +
            "<if test=\"orderNo != 2 and orderNo != 1 \">" +
            "and order_no = #{orderNo} " +
            "</if>" +
            "<if test=\"orderNo !=2 and orderNo == 1\">" +
            "and len(order_no) &gt;2 " +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 0  \">" +
            "and ori_detail_sn is null and charge_fee > 0" +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 1  \">" +
            "and (ori_detail_sn &gt; 0 or charge_fee &lt; 0) " +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 2  \">" +
            "and (ori_detail_sn = -1 )" +
            "</if>" +
            ") temp where RowNumber > ${pageSize} * (${currentPage} - 1 )) a  " +
            "        left join zd_charge_item b on (a.charge_code = b.code)  " +
            "        left join  yp_zd_dict c on (a.serial = c.serial and c.code= a.charge_code_mx)  " +
            "        left join a_employee_mi d on (d.code = a.op_id_code)  " +
            "</script>")
    List<ZyDetailCharge> huanZheYaoPinFeiYong(@Param("currentPage") long currentPage,
                                              @Param("pageSize") long pageSize,
                                              @Param("inpatientNo") String inpatientNo,
                                              @Param("admissTimes") Integer admissTimes,
                                              @Param("ledgerSn") Integer ledgerSn,
                                              @Param("chargeCode") String chargeCode,
                                              @Param("infantFlag") int infantFlag,
                                              @Param("startTime") String startTime,
                                              @Param("endTime") String endTime,
                                              @Param("orderNo") BigDecimal orderNo,
                                              @Param("dept") String dept,
                                              @Param("riQiPaiXu") String riQiPaiXu,
                                              @Param("tuiFeiFlag") Integer tuiFeiFlag);

    @Select("<script>" +
            "select count(*) total,sum(charge_fee) charge_fee" +
            "    FROM zy_detail_charge a  " +
            "where   a.charge_code  " +
            "like 'BILL%' and a.ledger_sn = #{ledgerSn}  and trans_flag_yb &lt;&gt; 2 and a.order_no &lt;&gt; 6 and inpatient_no = #{inpatientNo}  " +
            "and admiss_times = #{admissTimes} and a.infant_flag = #{infantFlag} " +
            "<if test=\"chargeCode != null and chargeCode != '' \">" +
            "and charge_code_mx = #{chargeCode} " +
            "</if>" +
            "<if test=\"startTime != null and startTime!= '' \">" +
            "and charge_date &gt;= #{startTime} and charge_date &lt;= #{endTime} " +
            "</if>" +
            "<if test=\"dept != null and dept != '' \"> " +
            "and exec_unit=#{dept}   " +
            "</if>" +
            "<if test=\"orderNo != 2 and orderNo != 1 \">" +
            "and order_no = #{orderNo} " +
            "</if>" +
            "<if test=\"orderNo !=2 and orderNo == 1\">" +
            "and len(order_no) &gt;2 " +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 0  \">" +
            "and ori_detail_sn is null and charge_fee > 0" +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 1  \">" +
            "and (ori_detail_sn &gt; 0 or charge_fee &lt; 0) " +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 2  \">" +
            "and (ori_detail_sn = -1 )" +
            "</if>" +
            "</script>")
    long huanZheYaoPinFeiYongTotal(@Param("inpatientNo") String inpatientNo,
                                   @Param("admissTimes") Integer admissTimes,
                                   @Param("ledgerSn") Integer ledgerSn,
                                   @Param("chargeCode") String chargeCode,
                                   @Param("infantFlag") int infantFlag,
                                   @Param("startTime") String startTime,
                                   @Param("endTime") String endTime,
                                   @Param("orderNo") BigDecimal orderNo,
                                   @Param("dept") String dept,
                                   @Param("tuiFeiFlag") Integer tuiFeiFlag);

    @Select("<script>" +
            "            SELECT a.admiss_times, " +
            "                   a.inpatient_no, " +
            "                   a.order_no,    " +
            "                   a.gen_time,      " +
            "                   a.ledger_sn," +
            "                   a.trans_flag_yb,    " +
            "                   a.detail_sn,    " +
            "                   a.charge_date,    " +
            "                   a.op_id_code,    " +
            "                   c.name op_name,    " +
            "                   a.charge_code,    " +
            "                   a.charge_fee,    " +
            "                   a.charge_status  ,    " +
            "                   bill_item_code = b.bill_item_zy,    " +
            "                   bill_item_name=(select name from zy_bill_item where code = b.bill_item_zy),    " +
            "                   charge_name=b.name,    " +
            "                   spec=b.charge_unit,    " +
            "                   a.charge_code_mx,    " +
            "                   a.serial,    " +
            "                   price=0.0000,    " +
            "                   charge_amount=a.charge_amount,    " +
            "                   exec_dept=(select name from zd_unit_code where code = a.exec_unit),    " +
            "                   dept_code = (select name from zd_unit_code  where code = a.ward_code) ,    " +
            "                   self_flag=a.self_flag,    " +
            "                   suppress_flag=a.suppress_flag,    " +
            "                   group_no='00',    " +
            "                   separate_flag=a.separate_flag,    " +
            "                   ope_flag=a.ope_flag,    " +
            "                   drugname=b.name,    " +
            "                   isnull(a.ori_detail_sn,0) ori_detail_sn    " +
            "                   FROM  (select top ${pageSize} * from (select ROW_NUMBER() OVER (order by charge_date ${riQiPaiXu} ) as RowNumber,*  from zy_detail_charge where inpatient_no= #{inpatientNo} and " +
            "admiss_times= #{admissTimes}  and ledger_sn = #{ledgerSn} and infant_flag = #{infantFlag} and   trans_flag_yb &lt;&gt; 2 AND charge_code not like 'BILL%' and  order_no &lt;&gt; 6" +
            "<if test=\"chargeCode != null and chargeCode != '' \">" +
            "and charge_code_mx = #{chargeCode} " +
            "</if>" +
            "<if test=\"startTime != null and startTime!= '' \">" +
            "and charge_date &gt;= #{startTime} and charge_date &lt;= #{endTime} " +
            "</if>" +
            "<if test=\"dept != null and dept != '' \"> " +
            "and exec_unit = #{dept}   " +
            "</if>" +
            "<if test=\"orderNo != 2 and orderNo != 1 \">" +
            "and order_no = #{orderNo} " +
            "</if>" +
            "<if test=\"orderNo !=2 and orderNo == 1\">" +
            "and len(order_no) &gt;2 " +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 0  \">" +
            "and ori_detail_sn is null and charge_fee > 0" +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 1  \">" +
            "and (ori_detail_sn &gt; 0 or charge_fee &lt; 0) " +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 2  \">" +
            "and (ori_detail_sn = -1 )" +
            "</if>" +
            ") temp where RowNumber > ${pageSize} * (${currentPage}-1) )  a " +
            "  left join  zd_charge_item b on (a.charge_code=b.code) " +
            "  left join a_employee_mi c on (c.code = a.op_id_code)" +
            "</script>")
    List<ZyDetailCharge> huanZheXiangMuFeiYong(@Param("currentPage") long currentPage,
                                               @Param("pageSize") long pageSize,
                                               @Param("inpatientNo") String inpatientNo,
                                               @Param("admissTimes") Integer admissTimes,
                                               @Param("ledgerSn") Integer ledgerSn,
                                               @Param("chargeCode") String chargeCode,
                                               @Param("infantFlag") int infantFlag,
                                               @Param("startTime") String startTime,
                                               @Param("endTime") String endTime,
                                               @Param("orderNo") BigDecimal orderNo,
                                               @Param("dept") String dept,
                                               @Param("riQiPaiXu") String riQiPaiXu,
                                               @Param("tuiFeiFlag") Integer tuiFeiFlag);


    @Select("<script>" +
            " select count(*) total,sum(charge_fee) charge_fee from zy_detail_charge where inpatient_no= #{inpatientNo} and " +
            "admiss_times= #{admissTimes}  and ledger_sn = #{ledgerSn} and infant_flag = #{infantFlag} and trans_flag_yb &lt;&gt; 2 AND charge_code not like 'BILL%' and  order_no &lt;&gt; 6" +
            "<if test=\"chargeCode != null and chargeCode != '' \">" +
            "and charge_code_mx = #{chargeCode} " +
            "</if>" +
            "<if test=\"startTime != null and startTime!= '' \">" +
            "and charge_date &gt;= #{startTime} and charge_date &lt;= #{endTime} " +
            "</if>" +
            "<if test=\"dept != null and dept != '' \"> " +
            "and exec_unit = #{dept}   " +
            "</if>" +
            "<if test=\"orderNo != 2 and orderNo != 1 \">" +
            "and order_no = #{orderNo} " +
            "</if>" +
            "<if test=\"orderNo !=2 and orderNo == 1\">" +
            "and len(order_no) &gt;2 " +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 0  \">" +
            "and ori_detail_sn is null and charge_fee > 0" +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 1  \">" +
            "and (ori_detail_sn &gt; 0 or charge_fee &lt; 0) " +
            "</if>" +
            "<if test=\"tuiFeiFlag != null and tuiFeiFlag == 2  \">" +
            "and (ori_detail_sn = -1 )" +
            "</if>" +
            "</script>")
    long huanZheFeiYongToatal(@Param("inpatientNo") String inpatientNo,
                              @Param("admissTimes") Integer admissTimes,
                              @Param("ledgerSn") Integer ledgerSn,
                              @Param("chargeCode") String chargeCode,
                              @Param("infantFlag") int infantFlag,
                              @Param("startTime") String startTime,
                              @Param("endTime") String endTime,
                              @Param("orderNo") BigDecimal orderNo,
                              @Param("dept") String dept,
                              @Param("tuiFeiFlag") Integer tuiFeiFlag);


    @Select("select detail_sn,ori_detail_sn from zy_detail_charge where inpatient_no=#{inpatientNo} and admiss_times = #{admissTimes} " +
            "and charge_fee <= 0 and ori_detail_sn is not null and infant_flag = #{infantFlag}")
    List<ZyDetailCharge> tuiFeiList(@Param("inpatientNo") String inpatientNo,
                                    @Param("admissTimes") Integer admissTimes,
                                    @Param("infantFlag") int infantFlag);

    /**
     * @param pyCode 拼音编码
     * @return 获取项目的名称和code
     */
    @Select("select distinct code,name from  zd_charge_item where (py_code like #{pyCode} or code like #{pyCode} or name like #{pyCode})" +
            "union all " +
            "select distinct code,name from  yp_zd_dict where (py_code like #{pyCode} or code like #{pyCode} or name like #{pyCode})")
    List<GetDropdownBox> getChargeCode(String pyCode);

    /**
     * @param inpatientNo 住院次数
     * @param admissTimes 住院号
     * @param list        需要退费的流水号
     * @return 获取患者的全部详细信息 因为要生成一条一样的数据 只是 费用和数量不一致 以及录入人 和 录入时间
     */
    @Select("<script>" +
            "select " +
            "inpatient_no, " +
            "       admiss_times, " +
            "       ledger_sn, " +
            "       detail_sn, " +
            "       charge_date, " +
            "       op_id_code, " +
            "       charge_code, " +
            "       bill_item_code = (select  bill_item_zy from zd_charge_item where code = charge_code), " +
            "       infant_flag, " +
            "       charge_status, " +
            "       retprice, " +
            "       charge_amount, " +
            "       charge_fee, " +
            "       self_flag, " +
            "       separate_flag, " +
            "       suppress_flag, " +
            "       ward_code, " +
            "       dept_code, " +
            "       order_no, " +
            "       ope_flag, " +
            "       exec_unit, " +
            "       gen_time, " +
            "       pay_percent, " +
            "       zj_flag, " +
            "       zfl, " +
            "       charge_code_mx, " +
            "       serial, " +
            "       zy_serial_no, " +
            "       doctor_code, " +
            "       yb_self_flag, " +
            "       orig_charge_fee, " +
            "       confirm_id, " +
            "       trans_flag_yb, " +
            "       ss_code, " +
            "       gs_flag, " +
            "       refer_physician, " +
            "       consult_physician, " +
            "       ori_detail_sn" +
            " from zy_detail_charge where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledgerSn} and detail_sn in " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item.detailSn}" +
            "</foreach>" +
            "</script>")
    List<ZyDetailCharge> huoQuJuTiFeiYong(@Param("inpatientNo") String inpatientNo,
                                          @Param("admissTimes") Integer admissTimes,
                                          @Param("ledgerSn") Integer ledgerSn,
                                          @Param("list") List<ZyDetailCharge> list);

    /**
     * 查询出 有哪些数据已经退过了 费用
     *
     * @param inpatientNo 住院号
     * @param admissTimes 住院次数
     * @param list        流水号
     * @return 返回 已经退过费用的数据
     */
    @Select("<script>" +
            "select " +
            "inpatient_no, " +
            "       admiss_times, " +
            "       ledger_sn, " +
            "       detail_sn, " +
            "       charge_date, " +
            "       op_id_code, " +
            "       charge_code, " +
            "       bill_item_code = (select  bill_item_zy from zd_charge_item where code = charge_code), " +
            "       infant_flag, " +
            "       charge_status, " +
            "       retprice, " +
            "       charge_amount, " +
            "       charge_fee, " +
            "       self_flag, " +
            "       separate_flag, " +
            "       suppress_flag, " +
            "       ward_code, " +
            "       dept_code, " +
            "       order_no, " +
            "       ope_flag, " +
            "       exec_unit, " +
            "       gen_time, " +
            "       pay_percent, " +
            "       zj_flag, " +
            "       zfl, " +
            "       charge_code_mx, " +
            "       serial, " +
            "       zy_serial_no, " +
            "       doctor_code, " +
            "       yb_self_flag, " +
            "       orig_charge_fee, " +
            "       confirm_id, " +
            "       trans_flag_yb, " +
            "       ss_code, " +
            "       gs_flag, " +
            "       refer_physician, " +
            "       consult_physician, " +
            "       ori_detail_sn" +
            " from zy_detail_charge where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledgerSn}  and ori_detail_sn in " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item.detailSn}" +
            "</foreach>" +
            "</script>")
    List<ZyDetailCharge> chaKanSFYiJingTuiFeiLe(@Param("inpatientNo") String inpatientNo,
                                                @Param("admissTimes") Integer admissTimes,
                                                @Param("ledgerSn") Integer ledgerSn,
                                                @Param("list") List<ZyDetailCharge> list);

    /**
     * 插入数据
     *
     * @param list 插入退费的数据 生成一条一样的数据 只是 费用和数量不一致 以及录入人 和 录入时间 ori_detail_sn 保存退费的原流水号
     */
    @Insert("<script>" +
            "insert into zy_detail_charge(inpatient_no, admiss_times, ledger_sn, detail_sn, charge_date, op_id_code, charge_code, infant_flag, " +
            "charge_status, retprice, charge_amount, charge_fee, self_flag, separate_flag, suppress_flag, ward_code, dept_code, order_no, ope_flag, exec_unit, gen_time, " +
            "pay_percent, zj_flag, zfl, charge_code_mx, serial, zy_serial_no, doctor_code, yb_self_flag, orig_charge_fee, confirm_id, trans_flag_yb, ss_code, gs_flag, " +
            "refer_physician, consult_physician, ori_detail_sn)   " +
            "values " +
            "<foreach collection='list' item='item' separator=','>" +
            "(#{item.inpatientNo},#{item.admissTimes},#{item.ledgerSn},#{item.detailSn},getdate(),#{item.opIdCode},#{item.chargeCode},#{item.infantFlag}," +
            "#{item.chargeStatus},#{item.retprice},cast(#{item.chargeAmount,jdbcType=DECIMAL} as decimal(14,5)),cast(#{item.chargeFee,jdbcType=DECIMAL} as decimal(14,5))," +
            "#{item.selfFlag},#{item.separateFlag},#{item.suppressFlag},#{item.wardCode},#{item.deptCode},#{item.orderNo}," +
            "#{item.opeFlag},#{item.execUnit},#{item.oldGenTime}," +
            "#{item.payPercent},#{item.zjFlag},#{item.zfl},#{item.chargeCodeMx},#{item.serial},#{item.zySerialNo},#{item.doctorCode}," +
            "#{item.ybSelfFlag},cast(#{item.origChargeFee,jdbcType=DECIMAL} as decimal(14,5)),#{item.confirmId},0,#{item.ssCode},#{item.gsFlag}," +
            "#{item.referPhysician},#{item.consultPhysician},#{item.oriDetailSn})" +
            "</foreach>" +
            "</script>")
    void xiangMuTuiFei(@Param("list") List<ZyDetailCharge> list);

    /**
     * 更新对应 退费的正数 流水
     *
     * @param inpatientNo 住院号
     * @param admissTimes 住院次数
     * @param ledgerSn    账页号
     * @param list        正流水
     */
    @Update("<script>" +
            "update zy_detail_charge set ori_detail_sn = -1 where  inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} " +
            "and ledger_sn = #{ledgerSn} " +
            "and charge_fee &gt; 0 and charge_amount &gt; 0  and detail_sn in " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</script>")
    void genXinZhenShuTuiFeiLiuShui(@Param("inpatientNo") String inpatientNo,
                                    @Param("admissTimes") Integer admissTimes,
                                    @Param("ledgerSn") Integer ledgerSn,
                                    @Param("list") List<Integer> list);

    /**
     * 获取模板
     *
     * @param deptCode 根据科室来搜索
     * @return 返回模板信息
     */
    @Select("<script>" +
            "SELECT rtrim(a.pattern_name) name,  " +
            "       group_no=max(a.group_no),  " +
            "       op_id_code=max(op_id_code),  " +
            "       rtrim(b.name) op_name,  " +
            "       a.op_dept  " +
            "FROM zy_charge_pattern a,  " +
            "     a_employee_mi b  " +
            "WHERE  " +
            "    ( a.op_id_code = b.code )  and  " +
            "        a.pattern_name &lt;&gt; ''   " +
            "<if test=\"deptCode != '3100000' \">" +
            "   and  op_dept=#{deptCode}  " +
            "</if>" +
            "<if test=\"defaultNo == 0\">" +
            "and default_no &gt; 0 " +
            "</if>" +
            "group by a.pattern_name,a.op_dept, b.name" +
            "</script>")
    List<ZyDetailCharge> getMuBan(@Param("deptCode") String deptCode,
                                  @Param("defaultNo") Integer defaultNo);

    /**
     * 这个是获取模板下面的那些项目具体信息
     *
     * @param patternName 模板名称
     * @param opIdCode    录入人id
     * @return 返回模板 下面的详细信息
     */
    @Select("SELECT charge_name=RTRIM(b.name),   " +
            "       pattern_name=zy_charge_pattern.pattern_name,   " +
            "       charge_code_mx=b.code,   " +
            "       spec=charge_unit,   " +
            "       group_no='00',   " +
            "       serial='00',   " +
            "       amount=zy_charge_pattern.amount,   " +
            "       charge_amount=b.charge_amount  ,   " +
            "       self    =b.self_flag,   " +
            "       separate_flag=b.separate_flag,   " +
            "       suprice_flag=b.suppress_flag,   " +
            "       infant='0',   " +
            "       per1=zy_charge_pattern.per1,   " +
            "       per2=zy_charge_pattern.per2,   " +
            "       per3=zy_charge_pattern.per3,   " +
            "       per4=zy_charge_pattern.per4,   " +
            "       per5=zy_charge_pattern.per5,   " +
            "       exec_unit_b=b.exec_unit,   " +
            "       item_no=zy_charge_pattern.item_no,   " +
            "       exec_unit_name=c.name,   " +
            "       exec_unit=zy_charge_pattern.exec_unit,   " +
            "       op_id_code=zy_charge_pattern.op_id_code,   " +
            "       zy_charge_pattern.py_code,   " +
            "       zy_charge_pattern.d_code,   " +
            "       price_b=b.charge_amount,   " +
            "       bill_item_code=b.bill_item_zy ," +
            "       bill_item_name=(select name from zy_bill_item where code = b.bill_item_zy)  " +
            "FROM zy_charge_pattern ,zd_charge_item b ,zd_unit_code c   " +
            "WHERE ( zy_charge_pattern.pattern_name = #{patternName})    and   " +
            "      zy_charge_pattern.op_id_code=#{opIdCode} and   " +
            "      zy_charge_pattern.charge_code=b.code and   " +
            "      (isnull(b.del_flag,'0') <> '1' ) and  " +
            "      zy_charge_pattern.exec_unit*=c.code   " +
            "union all   " +
            "SELECT charge_name=RTRIM(d.name),   " +
            "       pattern_name=zy_charge_pattern.pattern_name,   " +
            "       charge_code_mx=b.charge_code,   " +
            "       spec=d.specification,   " +
            "       group_no=b.group_no,   " +
            "       serial=isnull(b.serial,'01'),   " +
            "       amount=zy_charge_pattern.amount,   " +
            "       charge_amount=d.pack_retprice,   " +
            "       self    =d.self_flag,   " +
            "       separate_flag=d.separate_flag,   " +
            "       suprice=d.suprice_flag,   " +
            "       infant='0',   " +
            "       per1=zy_charge_pattern.per1,   " +
            "       per2=zy_charge_pattern.per2,   " +
            "       per3=zy_charge_pattern.per3,   " +
            "       per4=zy_charge_pattern.per4,   " +
            "       per5=zy_charge_pattern.per5,   " +
            "       exec_unit_b=''   ,   " +
            "       item_no=zy_charge_pattern.item_no,   " +
            "       exec_unit_name=c.name,   " +
            "       exec_unit=zy_charge_pattern.exec_unit,   " +
            "       op_id_code=zy_charge_pattern.op_id_code,   " +
            "       zy_charge_pattern.py_code,   " +
            "       zy_charge_pattern.d_code,   " +
            "       price_b=d.pack_retprice,   " +
            "       bill_item_code=d.bill_item_zy ," +
            "       bill_item_name=(select name from zy_bill_item where code = d.bill_item_zy)  " +
            "FROM zy_charge_pattern ,zd_unit_code c,yp_base_yf b,yp_zd_dict d   " +
            "WHERE ( zy_charge_pattern.pattern_name = #{patternName}) and   " +
            "      zy_charge_pattern.op_id_code=#{opIdCode} and   " +
            "      zy_charge_pattern.group_no=b.group_no and   " +
            "      zy_charge_pattern.charge_code=b.charge_code and   " +
            "      isnull(zy_charge_pattern.serial,'01')=b.serial and   " +
            "      b.charge_code=d.code and   " +
            "      b.serial=d.serial and   " +
            "      isnull(d.del_flag,'0') <> '1' and " +
            "      isnull(b.visible_flag_zy,'0') <> '1' and   " +
            "      zy_charge_pattern.exec_unit*=c.code")
    List<ZyDetailCharge> getMuBanXinXi(@Param("patternName") String patternName, @Param("opIdCode") String opIdCode);

    /**
     * 搜索项目信息
     *
     * @param pyCode 拼音码
     * @return 返回模板信息
     */
    @Select("SELECT a.code charge_code," +
            "       a.code charge_code_mx, " +
            "       a.exec_unit dept_code," +
            "       rtrim(a.yb_comment) yb_comment, " +
            "       RTRIM(a.name) charge_name, " +
            "       a.charge_amount," +
            "       amount = 1, " +
            "       bill_item_code=a.bill_item_zy, " +
            "       bill_item_name=(select name from zy_bill_item where code = a.bill_item_zy)," +
            "       a.py_code, " +
            "       a.d_code, " +
            "       a.suppress_flag, " +
            "       a.bill_item_out, " +
            "       a.n_code, " +
            "       self_flag =isnull(a.self_flag,'0'), " +
            "       a.separate_flag , " +
            "       spec=charge_unit, " +
            "       a.yb_comment, " +
            "       exec_unit_name=b.name, " +
            "       serial='00', " +
            "       group_no='00' " +
            "FROM zd_charge_item a,zd_unit_code b " +
            "where    isnull(a.del_flag,'') <> '1' and " +
            "         isnull(a.in_out_flag,'') <> '1' and " +
            "         a.exec_unit*=b.code and " +
            "         (a.py_code like #{pyCode} or a.code like #{pyCode} or a.name like #{pyCode})")
    List<ZyDetailCharge> queryXiangMu(String pyCode);


    @Select("SELECT distinct a.code charge_code_mx, " +
            "                charge_code = (select charge_code from zy_bill_item where a.bill_item_zy = zy_bill_item.code), " +
            "                a.serial, " +
            "                RTRIM(a.name) charge_name, " +
            "                a.pack_size amount, " +
            "                charge_amount=a.pack_retprice, " +
            "                spec=a.specification, " +
            "                a.drug_flag  , " +
            "                a.fix_price, " +
            "                c.stock_amount, " +
            "                self_flag=isnull(a.self_flag,'0'), " +
            "                suppress_flag=a.suprice_flag, " +
            "                group_no=c.group_no, " +
            "                bill_item_code=a.bill_item_zy, " +
            "                bill_item_name=(select name from zy_bill_item where code = a.bill_item_zy), " +
            "                ybbz=a.yb_comment_new, " +
            "                yblx=(select name from yp_zd_yb_type where code=a.yb_flag_new), " +
            "                xnh=a.xnh_flag " +
            "FROM yp_zd_dict a ,yp_print_name b  , " +
            "     yp_base_yf c " +
            "where " +
            "(a.py_code like #{pyCode} or a.name like #{pyCode} or a.code like #{pyCode}) and" +
            "        a.code=b.charge_code and " +
            "        a.code=c.charge_code and " +
            "        a.serial=c.serial    and " +
            "        c.visible_flag_mz <> '1' and " +
            "        c.visible_flag_zy <> '1' and" +
            "        isnull(a.del_flag,0) = 0 and  " +
            "        c.group_no='73'")
    List<ZyDetailCharge> queryYaoPin(String pyCode);

    /**
     * 插入项目录入费用
     *
     * @param zyDetailCharge 插入的信息
     * @param list           费用详情
     */
    @Insert("<script>" +
            "INSERT INTO zy_detail_charge (inpatient_no, admiss_times, ledger_sn, detail_sn, charge_date  " +
            "  , op_id_code, charge_code, infant_flag, charge_status, charge_fee  " +
            "  , self_flag, separate_flag, suppress_flag, ward_code, dept_code  " +
            "  , order_no, ope_flag, exec_unit, charge_amount, gen_time  " +
            "  , zy_serial_no, charge_code_mx, serial,ss_code,yb_self_flag,refer_physician,doctor_code)  " +
            "VALUES " +
            "<foreach collection='list' item='item' separator=','>" +
            "(#{zyDetailCharge.inpatientNo}, #{zyDetailCharge.admissTimes}, #{ledgerSn}, #{item.detailSn}, getdate()  " +
            "  , #{zyDetailCharge.opIdCode}, #{item.chargeCode}, #{infantFlag}, '2', cast(#{item.chargeAmount,jdbcType=DECIMAL} as decimal(14,5))  " +
            "  , '0', '0', NULL, #{zyDetailCharge.ward}, #{zyDetailCharge.dept}  " +
            "  , #{item.orderNo}, '0', #{item.deptCode}, cast(#{item.amount,jdbcType=DECIMAL} as decimal(14,5)), getdate()  " +
            "  , #{zyDetailCharge.zySerialNo}, #{item.chargeCodeMx}, #{item.serial},#{zyDetailCharge.ssCode}," +
            // 是否医保报销 标志 默认为空值
            "<if test=\"item.ybSelfFlag == null\">" +
            "0" +
            "</if>" +
            "<if test=\"item.ybSelfFlag != null\">" +
            "#{item.ybSelfFlag}" +
            "</if>" +
            ",#{zyDetailCharge.referPhysician}," +
            "#{item.doctorCode})" +
            "</foreach>" +
            "</script>")
    void chaRuFeiYong(@Param("zyDetailCharge") ZyDetailCharge zyDetailCharge,
                      @Param("list") List<ZyDetailCharge> list,
                      @Param("infantFlag") int infantFlag,
                      @Param("ledgerSn") Integer ledgerSn);

    @Select("<script>" +
            "SELECT charge_code charge_code_mx,group_no,serial FROM yp_base_yf WHERE charge_code in " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item.chargeCodeMx}" +
            "</foreach>" +
            "</script>")
    List<ZyDetailCharge> yaoFang(List<ZyDetailCharge> yaoPingCode);


    @Insert("<script>" +
            "insert into zy_drug ( inpatient_no , admiss_times , ledger_sn , detail_sn , charge_date , op_id_code , charge_code , infant_flag , " +
            "charge_fee , self_flag , separate_flag , supress_flag , ward_code , dept_code , ope_flag , exec_unit , retprice , amount , zy_serial_no , " +
            "serial , group_no , occ_cate ) values " +
            "<foreach collection='list' item='item' separator=','>" +
            "( #{zyDetailCharge.inpatientNo} , #{zyDetailCharge.admissTimes} , #{ledgerSn} , #{item.detailSn} , getdate() , #{opIdCode} , " +
            "#{item.chargeCodeMx} ,  #{infantFlag} , " +
            // 金额
            "cast(#{item.chargeFee,jdbcType=DECIMAL} as decimal(14,5))," +
            " '0' , '0' , null , #{zyDetailCharge.ward} , #{zyDetailCharge.dept} , '0' , #{item.deptCode} ," +
            // 单价
            "  cast(#{item.chargeFee,jdbcType=DECIMAL} / #{item.chargeAmount,jdbcType=DECIMAL} as decimal(14,5)) , " +
            // 数量
            "cast(#{item.chargeAmount,jdbcType=DECIMAL} as decimal(14,5)) " +
            ", #{zyDetailCharge.zySerialNo} , #{item.serial} , 73 ,'1' )" +
            "</foreach>" +
            "</script>")
    void shenQingYaoPing(@Param("zyDetailCharge") ZyDetailCharge zyDetailCharge,
                         @Param("list") List<ZyDetailCharge> list,
                         @Param("infantFlag") int infantFlag,
                         @Param("ledgerSn") Integer ledgerSn,
                         @Param("opIdCode") String opIdCode);

    /**
     * 需要更新患者的总费用
     *
     * @param fy          费用
     * @param inpatientNo 住院号
     * @param admissTimes 住院次数
     */
    @Update("<script>" +
            "update ${tableName} " +
            "<trim prefix='set' suffixOverrides=','> " +
            "total_charge = total_charge + #{fy.totalCharge} , " +
            "balance = balance - #{fy.totalCharge}," +
            "<if test=\"fy.charge1 !=null \">" +
            "charge1 =charge1 + #{fy.charge1} ," +
            "</if>" +
            "<if test=\"fy.charge2 !=null \">" +
            "charge2 =charge2 + #{fy.charge2} ," +
            "</if>" +
            "<if test=\"fy.charge3 !=null \">" +
            "charge3 =charge3 + #{fy.charge3} ," +
            "</if>" +
            "<if test=\"fy.charge4 !=null \">" +
            "charge4 =charge4 + #{fy.charge4} ," +
            "</if>" +
            "<if test=\"fy.charge5 !=null \">" +
            "charge5 =charge5 + #{fy.charge5} ," +
            "</if>" +
            "<if test=\"fy.charge6 !=null \">" +
            "charge6 =charge6 + #{fy.charge6} ," +
            "</if>" +
            "<if test=\"fy.charge7 !=null \">" +
            "charge7 =charge7 + #{fy.charge7} ," +
            "</if>" +
            "<if test=\"fy.charge8 !=null \">" +
            "charge8 =charge8 + #{fy.charge8} ," +
            "</if>" +
            "<if test=\"fy.charge9 !=null \">" +
            "charge9 =charge9 + #{fy.charge9} ," +
            "</if>" +
            "<if test=\"fy.charge10 !=null \">" +
            "charge10 =charge10 + #{fy.charge10} ," +
            "</if>" +
            "<if test=\"fy.charge11 !=null \">" +
            "charge11 =charge11 + #{fy.charge11} ," +
            "</if>" +
            "<if test=\"fy.charge12 !=null \">" +
            "charge12 =charge12 + #{fy.charge12} ," +
            "</if>" +
            "<if test=\"fy.charge13 !=null \">" +
            "charge13 =charge13 + #{fy.charge13} ," +
            "</if>" +
            "<if test=\"fy.charge14 !=null \">" +
            "charge14 =charge14 + #{fy.charge14} ," +
            "</if>" +
            "<if test=\"fy.charge15 !=null \">" +
            "charge15 =charge15 + #{fy.charge15} ," +
            "</if>" +
            "<if test=\"fy.charge16 !=null \">" +
            "charge16 =charge16 + #{fy.charge16} ," +
            "</if>" +
            "<if test=\"fy.charge17 !=null \">" +
            "charge17 =charge17 + #{fy.charge17} ," +
            "</if>" +
            "<if test=\"fy.charge18 !=null \">" +
            "charge18 =charge18 + #{fy.charge18} ," +
            "</if>" +
            "<if test=\"fy.charge19 !=null \">" +
            "charge19 =charge19 + #{fy.charge19} ," +
            "</if>" +
            "<if test=\"fy.charge20 !=null \">" +
            "charge20 =charge20 + #{fy.charge20} ," +
            "</if>" +
            "<if test=\"fy.charge21 !=null \">" +
            "charge21 =charge21 + #{fy.charge21} ," +
            "</if>" +
            "<if test=\"fy.charge22 !=null \">" +
            "charge22 =charge22 + #{fy.charge22} ," +
            "</if>" +
            "<if test=\"fy.charge23 !=null \">" +
            "charge23 =charge23 + #{fy.charge23} ," +
            "</if>" +
            "<if test=\"fy.charge24 !=null \">" +
            "charge24 =charge24 + #{fy.charge24} ," +
            "</if>" +
            "<if test=\"fy.charge25 !=null \">" +
            "charge25 =charge25 + #{fy.charge25} ," +
            "</if>" +
            "<if test=\"fy.charge26 !=null \">" +
            "charge26 =charge26 + #{fy.charge26} ," +
            "</if>" +
            "<if test=\"fy.charge27 !=null \">" +
            "charge27 =charge27 + #{fy.charge27} ," +
            "</if>" +
            "<if test=\"fy.charge28 !=null \">" +
            "charge28 =charge28 + #{fy.charge28} ," +
            "</if>" +
            "<if test=\"fy.charge29 !=null \">" +
            "charge29 =charge29 + #{fy.charge29} ," +
            "</if>" +
            "<if test=\"fy.charge30 !=null \">" +
            "charge30 =charge30 + #{fy.charge30} ," +
            "</if>" +
            "</trim>" +
            "WHERE inpatient_no = #{inpatientNo} and admiss_times=#{admissTimes}" +
            "</script>")
    void huanZheZongFeiYong(@Param("fy") FeiYongLeiXin fy,
                            @Param("inpatientNo") String inpatientNo,
                            @Param("admissTimes") Integer admissTimes,
                            @Param("tableName") String tableName);


    /**
     * 查看模板名称是否存在
     *
     * @param patternName 模板名字
     * @param opIdCode    创建模板的人 code
     * @return 返回条数
     */
    @Select("select count(*) from zy_charge_pattern where pattern_name =  #{patternName} and op_id_code <> #{opIdCode} ")
    Integer chaKanMuBanMingChengSFcunZi(@Param("patternName") String patternName,
                                        @Param("opIdCode") String opIdCode);

    /**
     * 删除模板
     *
     * @param patternName 模板名字
     * @param opIdCode    创建模板的人 code
     */
    @Select("delete zy_charge_pattern where pattern_name = #{patternName} and op_id_code = #{opIdCode} ")
    Integer delMuBan(@Param("patternName") String patternName,
                     @Param("opIdCode") String opIdCode);

    /**
     * 保存模板
     *
     * @param param 保存的数据
     */
    @Insert("<script>" +
            "INSERT INTO zy_charge_pattern ( charge_name, " +
            "                               pattern_name, " +
            "                               charge_code,   " +
            "                               group_no," +
            "                               serial,  " +
            "                               amount, " +
            "                               self,  " +
            "                               separate," +
            "                               suppress, " +
            "                               infant,  " +
            "                               per1," +
            "                               per2," +
            "                               per3," +
            "                               per4," +
            "                               per5," +
            "                               item_no,  " +
            "                               op_id_code, " +
            "                               py_code, " +
            "                               d_code, " +
            "                               charge_fee, " +
            "                               op_dept, " +
            "                               default_no " +
            "                               )" +
            "                               VALUES " +
            "<foreach collection='param.list' item='item' index='index' separator=','>" +
            "( substring(#{item.chargeName},0,15) , #{param.name} , #{item.chargeCodeMx} , #{item.groupNo}, #{item.serial}, cast(#{item.amount,jdbcType=DECIMAL} as decimal(14,5)) , " +
            "'0', '0', '0', '0', 1, 1, 1, 1, 1, #{index} + 1 , #{param.opIdCode} ,  #{param.pyCode} , #{param.dCode} , cast(#{item.chargeAmount,jdbcType=DECIMAL} as decimal(14,5)) ," +
            " #{param.deptCode} , " +
            "#{param.defaultNo} )" +
            "</foreach>" +
            "</script>")
    void baoCunMuBan(@Param("param") ZyDetailCharge param);

    /**
     * 获取医嘱的费用
     *
     * @param inpatientNo 住院号
     * @param admissTimes 住院次数
     * @param ZF          正的费用还是负的费用
     * @return 返回list 费用集合
     */
    @Select("<script>" +
            "select charge_date,charge_fee,charge_amount,charge_code_mx,detail_sn,ori_detail_sn,ABS(order_no),order_no,ledger_sn,exec_unit " +
            "from zy_detail_charge where  inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and  ledger_sn = #{ledgerSn}  " +
            "and ori_detail_sn is null and charge_amount ${ZF} 0 and charge_fee ${ZF} 0 and infant_flag = #{infantFlag} and trans_flag_yb &lt;&gt; 2 " +
            "<if test=\"list.size >0 \">" +
            "and exec_unit in " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "</script>")
    List<ZyDetailCharge> getYiZhuFeiYong(@Param("inpatientNo") String inpatientNo,
                                         @Param("admissTimes") Integer admissTimes,
                                         @Param("ledgerSn") Integer ledgerSn,
                                         @Param("ZF") String ZF,
                                         @Param("infantFlag") int infantFlag,
                                         @Param("list") List<String> list);

    /**
     * @param inpatientNo 住院号
     * @param admissTimes 住院次数
     * @param yiZhuPiPei  需要匹配的流水信息
     */
    @Update("<script>" +
            "<foreach collection='list' item='item' index='index' open='' close='' separator=';'>" +
            "update zy_detail_charge set ori_detail_sn = #{item.oriDetailSn} where inpatient_no= #{inpatientNo} and admiss_times = #{admissTimes} " +
            "and detail_sn = #{item.detailSn} and infant_flag = #{infantFlag} and ledger_sn = #{item.ledgerSn} " +
            "</foreach>" +
            "</script>")
    void yiZhuTuiFeiPiPei(@Param("inpatientNo") String inpatientNo,
                          @Param("admissTimes") Integer admissTimes,
                          @Param("list") List<ZyDetailCharge> yiZhuPiPei,
                          @Param("infantFlag") int infantFlag);

    @Select("SELECT small_dept FROM zd_dept_all where dept = #{dept}")
    List<String> chaXunZhiZXinKeShi(@Param("dept") String dept);


    @Select("select role_id from dj_user_role where  user_code = #{code}")
    List<Integer> huoQuJueSe(@Param("code") String code);


    @Select("select code,rtrim(name) name from zd_unit_code where code not like '8%'")
    List<GetDropdownBox> getDpet();

    @Select("select code,rtrim(name) name from zd_unit_code where code like '8%'")
    List<GetDropdownBox> getWard();


    @Select("SELECT small_dept code,name=(select name from zd_unit_code where code = small_dept) FROM zd_dept_all where dept = #{dept}")
    List<GetDropdownBox> getBingQuDuiYingKeShi(@Param("dept") String dept);


    @Select("select count(*) from zy_actpatient where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes}")
    Integer getHuanZheSFZaiYuan(@Param("inpatientNo") String inpatientNo,
                                @Param("admissTimes") Integer admissTimes);

    @Select("select settle_type from zy_ledger_file where " +
            "inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} " +
            "and ledger_sn = (select max(ledger_sn)ledger_sn from zy_ledger_file where  zy_ledger_file.inpatient_no = #{inpatientNo} " +
            "and zy_ledger_file.admiss_times = #{admissTimes}) ")
    Integer getHuanZheSFJieSuan(@Param("inpatientNo") String inpatientNo,
                                @Param("admissTimes") Integer admissTimes);


    @Select("select detail_sn,ori_detail_sn,trans_flag_yb from zy_detail_charge where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledger} " +
            "and detail_sn = #{detailSn} and order_no <> 6 and trans_flag_yb <> 2 ")
    ZyDetailCharge beiTuiFeiYongXinXi(@Param("inpatientNo") String inpatientNo,
                                      @Param("admissTimes") Integer admissTimes,
                                      @Param("ledger") Integer ledger,
                                      @Param("detailSn") Integer detailSn);

    @Select("<script>" +
            "select code from yp_zd_dict where code in " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            " #{item}" +
            "</foreach> " +
            " and serial = '01' and isnull(del_flag,0)  = 1 " +
            "union all " +
            "select code from zd_charge_item where code in " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            " #{item}" +
            "</foreach> " +
            " and isnull(del_flag,0)  = 1" +
            "</script>")
    List<String> chaXunFeiYongShiFouTingYong(List<String> bianMa);


    @Select("<script>" +
            "select charge_code from yp_base_yf where stock_amount &lt;= 0 and serial = '01' and charge_code in " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            " #{item}" +
            "</foreach> " +
            "</script>")
    List<String> kuCun(List<String> bianMa);


    @Delete("delete zy_detail_charge where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledger} " +
            "and detail_sn = #{detailSn} and order_no <> 6 and trans_flag_yb <> 2")
    void cheXiaoFeiYong(@Param("inpatientNo") String inpatientNo,
                        @Param("admissTimes") Integer admissTimes,
                        @Param("ledger") Integer ledger,
                        @Param("detailSn") Integer detailSn);

    @Update("update zy_detail_charge set ori_detail_sn = null where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledger} " +
            "and detail_sn = #{detailSn} and order_no <> 6 and trans_flag_yb <> 2")
    void cheXiaoHouHuanYuanZhenShuJu(@Param("inpatientNo") String inpatientNo,
                                     @Param("admissTimes") Integer admissTimes,
                                     @Param("ledger") Integer ledger,
                                     @Param("detailSn") Integer detailSn);


    @Select("select act_order_no as order_no,exec_unit from yz_erase_order where act_order_no in  " +
            "(select order_no from zy_detail_charge where  inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledger}   " +
            "and exec_unit is null AND charge_fee < 0 and ori_detail_sn is null)")
    List<ZyDetailCharge> huoQuYaoPingZhongKeShiWeiKongDe(@Param("inpatientNo") String inpatientNo,
                                                         @Param("admissTimes") Integer admissTimes,
                                                         @Param("ledger") Integer ledger);

    @Update("<script>" +
            " <foreach collection='list' item='item' separator=';' >" +
            "update zy_detail_charge set exec_unit = #{item.execUnit}  where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledger} " +
            "and order_no = #{item.orderNo} and exec_unit is null  and charge_fee &lt; 0" +
            "</foreach> " +
            "</script> ")
    void yiZhuTuiFeiGenXingZhiXingKeShi(@Param("inpatientNo") String inpatientNo,
                                        @Param("admissTimes") Integer admissTimes,
                                        @Param("ledger") Integer ledger,
                                        @Param("list") List<ZyDetailCharge> genXingDeZhiXinKeShi);


    @Select("select b.code from zy_detail_charge a, yp_zd_dict b " +
            "where order_no <> 6 and trans_flag_yb not in (1,2)  and charge_code like 'BILL%' " +
            "  and  isnull(b.national_code,'') = '' and inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledger} " +
            "and a.infant_flag = #{infantFlag} " +
            "  and a.charge_code_mx = b.code " +
            "group by  b.code ")
    List<String> weiGuiYaoPinInt(@Param("inpatientNo") String inpatientNo,
                                 @Param("admissTimes") Integer admissTimes,
                                 @Param("ledger") Integer ledger,
                                 @Param("infantFlag") Integer infantFlag);

    @Update("<script>" +
            "<foreach collection='list' item='item' separator=';'>" +
            "update yp_zd_dict set national_code = (select top(1) national_code from yp_zd_dict where  code = #{item} and national_code is not null ) " +
            "where code = #{item} and national_code is null" +
            "</foreach>" +
            "</script>")
    void genXinYaoPingPiPeiXinXi(@Param("list") List<String> list);


    @Select("select a.charge_code_mx,case when (select top(1) rtrim(name) from zd_charge_item where code = a.charge_code_mx) != '' then " +
            "(select top(1) rtrim(name) from zd_charge_item where code = a.charge_code_mx) else " +
            "(select top(1) rtrim(name) from yp_zd_dict where code = a.charge_code_mx) end as name " +
            "from zy_detail_charge a left join zd_charge_item_hnsgs b " +
            "on (a.charge_code_mx = b.hosp_code) where " +
            "inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledger} and infant_flag = #{infantFlag} and isnull(b.hnsyb_item_code,'') = '' " +
            "group by a.charge_code_mx ")
    List<WeiGuiFeiYongFenXi> gongShangWeiPiPei(@Param("inpatientNo") String inpatientNo,
                                               @Param("admissTimes") Integer admissTimes,
                                               @Param("ledger") Integer ledger,
                                               @Param("infantFlag") Integer infantFlag);

    /**
     * 违规药品 信息 没有匹配的
     *
     * @param inpatientNo 住院号
     * @param admissTimes 住院次数
     * @param ledger      账页号
     * @return 返回违规药品 没有匹配的
     */
    @Select("select charge_code_mx,b.name from zy_detail_charge a, yp_zd_dict b " +
            "where  trans_flag_yb not in (1,2)  and charge_code like 'BILL%' " +
            "  and  isnull(b.national_code,'') = '' and inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledger} " +
            "and a.infant_flag = #{infantFlag}  " +
            "  and a.charge_code_mx = b.code " +
            "group by  charge_code_mx, b.name")
    List<WeiGuiFeiYongFenXi> weiGuiYaoPin(@Param("inpatientNo") String inpatientNo,
                                          @Param("admissTimes") Integer admissTimes,
                                          @Param("ledger") Integer ledger,
                                          @Param("infantFlag") Integer infantFlag);


    @Select("select charge_code_mx,b.name from zy_detail_charge a, zd_charge_item b  " +
            "where  trans_flag_yb not in (1,2)  and charge_code not like 'BILL%'  " +
            "  and  isnull(b.national_code,'') = '' and inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledger}  " +
            " and a.infant_flag = #{infantFlag}  " +
            "  and a.charge_code_mx = b.code  " +
            "group by  charge_code_mx, b.name")
    List<WeiGuiFeiYongFenXi> weiGuiXiangMu(@Param("inpatientNo") String inpatientNo,
                                           @Param("admissTimes") Integer admissTimes,
                                           @Param("ledger") Integer ledger,
                                           @Param("infantFlag") Integer infantFlag);


    @Select("<script>" +
            "select rtrim(inpatient_no) inpatient_no,admiss_times,ledger_sn,charge_code_mx,detail_sn,ori_detail_sn,order_no,charge_fee,charge_amount, " +
            "       exec_unit_name = (select rtrim(name) name from zd_unit_code where exec_unit = code),charge_date,trans_flag_yb, exec_unit, " +
            "       op_id_code = (select rtrim(name) name from a_employee_mi where code  = op_id_code)," +
            "       charge_code_name = (select rtrim(name) name from zd_charge_item where code = charge_code_mx),charge_code = 'xm' from zy_detail_charge " +
            "where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledger} and infant_flag = #{infantFlag} " +
            "  and charge_fee &lt; 0 and charge_code not like 'BILL%' " +
            "  and  trans_flag_yb not in (1,2) " +
            "  and ori_detail_sn is null " +
            "<if test=\"chuYuanYiZhu == 0\">" +
            "  and CONVERT(varchar(100), charge_date, 23) &lt;= CONVERT(varchar(100), GETDATE(), 23)" +
            "</if>" +
            "<if test=\"list.size >0 \">" +
            " and exec_unit in " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "</script>")
    List<ZyDetailCharge> weiXieDaiYuanLiuShuiXiangMu(@Param("inpatientNo") String inpatientNo,
                                                     @Param("admissTimes") Integer admissTimes,
                                                     @Param("ledger") Integer ledger,
                                                     @Param("infantFlag") Integer infantFlag,
                                                     @Param("list") List<String> execUnit,
                                                     @Param("chuYuanYiZhu") Integer chuYuanYiZhu);

    @Select("<script>" +
            "select rtrim(inpatient_no) inpatient_no,admiss_times,ledger_sn,charge_code_mx,detail_sn,order_no,ori_detail_sn,charge_fee,charge_amount, " +
            "       exec_unit_name = (select rtrim(name) name from zd_unit_code where exec_unit = code),charge_date,trans_flag_yb,exec_unit, " +
            "       op_id_code = (select rtrim(name) name from a_employee_mi where code  = op_id_code)," +
            "       charge_code_name = (select top(1) rtrim(name) name from yp_zd_dict where code = charge_code_mx),charge_code = 'yp' from zy_detail_charge " +
            "where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledger} and infant_flag = #{infantFlag} " +
            "  and charge_fee &lt; 0 and charge_code  like 'BILL%' " +
            "  and  trans_flag_yb not in (1,2) " +
            "  and ori_detail_sn is null" +
            "<if test=\"chuYuanYiZhu == 0\">" +
            "  and CONVERT(varchar(100), charge_date, 23) &lt;= CONVERT(varchar(100), GETDATE(), 23)" +
            "</if>" +
            "<if test=\"list.size > 0 \">" +
            " and exec_unit in " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "</script>")
    List<ZyDetailCharge> weiXieDaiYuanLiuShuiYaoPing(@Param("inpatientNo") String inpatientNo,
                                                     @Param("admissTimes") Integer admissTimes,
                                                     @Param("ledger") Integer ledger,
                                                     @Param("infantFlag") Integer infantFlag,
                                                     @Param("list") List<String> execUnit,
                                                     @Param("chuYuanYiZhu") Integer chuYuanYiZhu);

    @Select("<script>" +
            "select inpatient_no,admiss_times,ledger_sn,detail_sn,charge_amount,charge_fee,order_no,charge_code_mx,charge_date,ori_detail_sn " +
            "from zy_detail_charge where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledgerSn} and charge_code_mx = #{chargeCodeMx} " +
            "<choose>" +
            "<when test=\"execUnit != null and execUnit != ''\">" +
            "and exec_unit = #{execUnit} " +
            "</when>" +
            "<otherwise>" +
            "and order_no = #{orderNo}" +
            "</otherwise>" +
            "</choose>" +
            "and trans_flag_yb &lt;&gt; 2 and order_no &lt;&gt; 6 and charge_fee &gt; 0 and  charge_amount &gt; 0 and ori_detail_sn is null " +
            "and infant_flag = #{infantFlag}  " +
            "<if test=\"startTime != null and startTime != '' \">" +
            "and charge_date &gt;= #{startTime} and charge_date &lt;= #{endTime} " +
            "</if>" +
            " order by charge_date ${riQiPaiXu}" +
            "</script>")
    List<ZyDetailCharge> weiGuiZhenShuPiPei(@Param("inpatientNo") String inpatientNo,
                                            @Param("admissTimes") Integer admissTimes,
                                            @Param("ledgerSn") Integer ledgerSn,
                                            @Param("infantFlag") Integer infantFlag,
                                            @Param("chargeCodeMx") String chargeCodeMx,
                                            @Param("execUnit") String execUnit,
                                            @Param("orderNo") String orderNo,
                                            @Param("startTime") String startTime,
                                            @Param("endTime") String endTime,
                                            @Param("riQiPaiXu") String riQiPaiXu);


    @Select("select inpatient_no,admiss_times,ledger_sn,detail_sn,charge_code_mx,sum(charge_fee) yi_tui_fei from (  " +
            "select inpatient_no,admiss_times,ledger_sn,detail_sn,charge_code_mx,charge_fee  " +
            "from zy_detail_charge where " +
            "inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledgerSn} and charge_code_mx = #{chargeCodeMx} " +
            "and exec_unit = #{execUnit} and trans_flag_yb <> 2 and order_no <> 6 and" +
            "    detail_sn in(select ori_detail_sn from zy_detail_charge where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and " +
            "ledger_sn = #{ledgerSn} and charge_code_mx = #{chargeCodeMx}" +
            " and charge_fee <0 and ori_detail_sn is not null)  " +
            "union all  " +
            "select inpatient_no,admiss_times,ledger_sn,ori_detail_sn,charge_code_mx,charge_fee  " +
            "from zy_detail_charge where " +
            "inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledgerSn} and charge_code_mx = #{chargeCodeMx} " +
            "and exec_unit = #{execUnit} and trans_flag_yb <> 2 and order_no <> 6 and " +
            "ori_detail_sn in(select ori_detail_sn from zy_detail_charge where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} " +
            "and ledger_sn = #{ledgerSn} and charge_code_mx = #{chargeCodeMx} and charge_fee <0 and ori_detail_sn is not null)  " +
            ") temp  " +
            "group by inpatient_no, admiss_times, ledger_sn, detail_sn, charge_code_mx")
    List<ZyDetailCharge> tuiFeiYuEr1(@Param("inpatientNo") String inpatientNo,
                                     @Param("admissTimes") Integer admissTimes,
                                     @Param("ledgerSn") Integer ledgerSn,
                                     @Param("chargeCodeMx") String chargeCodeMx,
                                     @Param("execUnit") String execUnit);


    @Select("<script>" +
            "select detail_sn,ori_detail_sn,order_no,charge_fee,charge_amount,charge_code_mx,charge_date, " +
            "       exec_unit_name = (select rtrim(name) from zd_unit_code where code =exec_unit), " +
            "       ward_code = (select rtrim(name) from zd_unit_code where code = ward_code) " +
            "       from zy_detail_charge where " +
            "       inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledgerSn}  " +
            "and infant_flag = #{infantFlag} " +
            "and charge_code_mx = #{chargeCodeMx} and trans_flag_yb &lt;&gt; 2 and order_no &lt;&gt; 6 " +
            "and charge_amount &gt; 0 " +
            "<if test=\"startTime != null and startTime != '' \">" +
            "and charge_date &gt;= #{startTime} and charge_date &lt;= #{endTime} " +
            "</if>" +
            "order by charge_date ${riQiPaiXu}" +
            "</script>")
    List<ZyDetailCharge> genJuBianMaChaZhaoZhenShuFeiYong(@Param("inpatientNo") String inpatientNo,
                                                          @Param("admissTimes") Integer admissTimes,
                                                          @Param("ledgerSn") Integer ledgerSn,
                                                          @Param("infantFlag") Integer infantFlag,
                                                          @Param("chargeCodeMx") String chargeCodeMx,
                                                          @Param("startTime") String startTime,
                                                          @Param("endTime") String endTime,
                                                          @Param("riQiPaiXu") String riQiPaiXu);

    @Select("<script>" +
            "select * from zy_detail_charge " +
            "where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledgerSn} and infant_flag = #{infantFlag} and detail_sn in " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item}" +
            "</foreach>" +
            " and charge_fee &gt; 0 and charge_amount &gt; 0 " +
            "</script>")
    List<ZyDetailCharge> piPeiXinXi(@Param("inpatientNo") String inpatientNo,
                                    @Param("admissTimes") Integer admissTimes,
                                    @Param("ledgerSn") Integer ledgerSn,
                                    @Param("infantFlag") Integer infantFlag,
                                    @Param("list") int[] detailSnList);


    @Select("<script>" +
            "select inpatient_no,admiss_times,ledger_sn,ori_detail_sn,sum(charge_fee) charge_fee from zy_detail_charge  " +
            "              where  inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledgerSn} and infant_flag = #{infantFlag} and ori_detail_sn in  " +
            "<foreach collection='list' item='item' index='index' open='(' close=')' separator=','>" +
            "#{item} " +
            "</foreach>" +
            "and charge_fee &lt; 0 and charge_amount &lt; 0  " +
            "group by inpatient_no, admiss_times, ledger_sn, ori_detail_sn  " +
            "</script>")
    List<ZyDetailCharge> tuiFeiYuEr2(@Param("inpatientNo") String inpatientNo,
                                     @Param("admissTimes") Integer admissTimes,
                                     @Param("ledgerSn") Integer ledgerSn,
                                     @Param("infantFlag") Integer infantFlag,
                                     @Param("list") int[] detailSnList);


    @Select("select inpatient_no,admiss_times,ledger_sn,detail_sn,charge_fee,charge_amount,ori_detail_sn,trans_flag_yb,exec_unit from zy_detail_charge " +
            "where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledgerSn} and detail_sn = #{detailSn} " +
            "and infant_flag = #{infantFlag} and charge_fee < 0 and charge_amount < 0 ")
    ZyDetailCharge piPeiXinXiFuShu(@Param("inpatientNo") String inpatientNo,
                                   @Param("admissTimes") Integer admissTimes,
                                   @Param("ledgerSn") Integer ledgerSn,
                                   @Param("infantFlag") Integer infantFlag,
                                   @Param("detailSn") Integer detailSn);

    @Update("update zy_detail_charge set ori_detail_sn = #{oriDetailSn} where inpatient_no = #{inpatientNo} and admiss_times = #{admissTimes} and ledger_sn = #{ledgerSn} " +
            "and detail_sn = #{detailSn} ")
    void zhiYouYiGeJiuGenXingFuShuLiuShui(@Param("inpatientNo") String inpatientNo,
                                          @Param("admissTimes") Integer admissTimes,
                                          @Param("ledgerSn") Integer ledgerSn,
                                          @Param("detailSn") Integer detailSn,
                                          @Param("oriDetailSn") Integer oriDetailSn);

}