thmz/src/main/java/cn/hnthyy/thmz/mapper/his/yp/YpMzFytjMapper.java

package cn.hnthyy.thmz.mapper.his.yp;

import cn.hnthyy.thmz.entity.his.yp.YpMzFytj;
import cn.hnthyy.thmz.vo.ChargeFeeParamsVo;
import org.apache.ibatis.annotations.*;

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

public interface YpMzFytjMapper {

    /**
     * 查询当前病人本次已经确认发药了的发药统计记录
     *
     * @return
     */
    @Select("select rtrim(patient_id) patient_id,times,receipt_no,order_no,item_no,rtrim(charge_item_code) charge_item_code,rtrim(bill_item_code) bill_item_code," +
            "quantity,unit_price,rtrim(pay_mark) pay_mark,rtrim(serial) serial,rtrim(charge_item_type) charge_item_type,rtrim(country_flag) country_flag,rtrim(drug_flag) drug_flag," +
            "serial_no,rtrim(name) name,charge_date,rtrim(group_no) group_no,confirm_time,rtrim(input_id) input_id,input_time,drugname,specification,confirm_name,confirm_id," +
            "rtrim(doctor_id) doctor_id,doctor_name,confirm_flag,cy_fy,rtrim(warn_dept) warn_dept,rtrim(windows_no) windows_no,rtrim(win_no) win_no,input_date," +
            "rtrim(group_no_out) group_no_out,rtrim(response_type) response_type,rtrim(self_flag) self_flag,real_no,manu_no from yp_mz_fytj WITH(NOLOCK) where patient_id = #{patientId} and times = #{times} and serial_no > 0 and  confirm_flag = '1' ")
    List<YpMzFytj> selectConfirdYpMzFytj(@Param(value = "patientId") String patientId, @Param("times") Integer times);


    /**
     * 根据缴费流水号和药房号查询所有的药品记录
     *
     * @return
     */
    @Select("select rtrim(patient_id) patient_id,times,receipt_no,order_no,item_no,rtrim(charge_item_code) charge_item_code,rtrim(bill_item_code) bill_item_code," +
            "quantity,unit_price,rtrim(pay_mark) pay_mark,rtrim(serial) serial,rtrim(charge_item_type) charge_item_type,rtrim(country_flag) country_flag,rtrim(drug_flag) drug_flag," +
            "serial_no,rtrim(name) name,charge_date,rtrim(group_no) group_no,confirm_time,rtrim(input_id) input_id,input_time,drugname,specification,confirm_name,confirm_id," +
            "rtrim(doctor_id) doctor_id,doctor_name,confirm_flag,cy_fy,rtrim(warn_dept) warn_dept,rtrim(windows_no) windows_no,rtrim(win_no) win_no,input_date," +
            "rtrim(group_no_out) group_no_out,rtrim(response_type) response_type,rtrim(self_flag) self_flag,real_no,manu_no from yp_mz_fytj WITH(NOLOCK) where serial_no = #{serialNo} and group_no = #{groupNoOut} and order_no = #{orderNo}")
    List<YpMzFytj> selectConfirdYpMzFytjBySerialNoAndGroupNo(@Param("serialNo") String serialNo, @Param("groupNoOut") String groupNoOut,@Param("orderNo") String orderNo);



    /**
     * 查询当前病人本次已经退药的西药发药统计记录
     *
     * @return
     */
    @Select("select rtrim(patient_id) patient_id,times,receipt_no,order_no,item_no,rtrim(charge_item_code) charge_item_code,rtrim(bill_item_code) bill_item_code," +
            "quantity,unit_price,rtrim(pay_mark) pay_mark,rtrim(serial) serial,rtrim(charge_item_type) charge_item_type,rtrim(country_flag) country_flag,rtrim(drug_flag) drug_flag," +
            "serial_no,rtrim(name) name,charge_date,rtrim(group_no) group_no,confirm_time,rtrim(input_id) input_id,input_time,drugname,specification,confirm_name,confirm_id," +
            "rtrim(doctor_id) doctor_id,doctor_name,confirm_flag,cy_fy,rtrim(warn_dept) warn_dept,rtrim(windows_no) windows_no,rtrim(win_no) win_no,input_date," +
            "rtrim(group_no_out) group_no_out,rtrim(response_type) response_type,rtrim(self_flag) self_flag,real_no,manu_no from yp_mz_fytj WITH(NOLOCK) " +
            " where patient_id = #{patientId} and times = #{times} and confirm_flag =3 and bill_item_code in('010','011') ")
    List<YpMzFytj> selectXyTyYpMzFytj(@Param(value = "patientId") String patientId, @Param("times") Integer times);




    /**
     * 新增发药统计
     *
     * @param ypMzFytj
     * @return
     */
    @Insert("INSERT INTO yp_mz_fytj(patient_id,times,receipt_no,order_no,item_no,charge_item_code,bill_item_code,quantity,unit_price,pay_mark,serial,charge_item_type,country_flag," +
            "drug_flag,serial_no,name,charge_date,group_no,confirm_time,input_id,input_time,drugname,specification,confirm_name,confirm_id,doctor_id,doctor_name,confirm_flag,cy_fy," +
            "warn_dept,windows_no,win_no,input_date,group_no_out,response_type,self_flag,real_no) " +
            "VALUES(#{patientId,jdbcType=CHAR},#{times,jdbcType=INTEGER},#{receiptNo,jdbcType=SMALLINT},#{orderNo,jdbcType=TINYINT},#{itemNo,jdbcType=TINYINT}," +
            "#{chargeItemCode,jdbcType=CHAR},#{billItemCode,jdbcType=CHAR},#{quantity,jdbcType=DOUBLE},#{unitPrice,jdbcType=DECIMAL},#{payMark,jdbcType=CHAR}," +
            "#{serial,jdbcType=CHAR},#{chargeItemType,jdbcType=CHAR},#{countryFlag,jdbcType=CHAR},#{drugFlag,jdbcType=CHAR},#{serialNo,jdbcType=INTEGER},#{name,jdbcType=CHAR}," +
            "#{chargeDate,jdbcType=TIMESTAMP},#{groupNo,jdbcType=CHAR},#{confirmTime,jdbcType=TIMESTAMP},#{inputId,jdbcType=CHAR},#{inputTime,jdbcType=TIMESTAMP}," +
            "#{drugname,jdbcType=VARCHAR},#{specification,jdbcType=VARCHAR},#{confirmName,jdbcType=VARCHAR},#{confirmId,jdbcType=VARCHAR},#{doctorId,jdbcType=CHAR}," +
            "#{doctorName,jdbcType=VARCHAR},#{confirmFlag,jdbcType=INTEGER},#{cyFy,jdbcType=INTEGER},#{warnDept,jdbcType=CHAR},#{windowsNo,jdbcType=CHAR},#{winNo,jdbcType=CHAR}," +
            "#{inputDate,jdbcType=TIMESTAMP},#{groupNoOut,jdbcType=CHAR},#{responseType,jdbcType=CHAR},#{selfFlag,jdbcType=CHAR},#{realNo,jdbcType=INTEGER})")
    int insertYpMzFytj(YpMzFytj ypMzFytj);


    /**
     * 将已经发药的记录改为负数记录
     *
     * @param ypMzFytj
     * @return
     */
    @Update("update yp_mz_fytj set real_no=#{realNo},serial_no=#{serialNo} where patient_id = #{patientId} and times = #{times} and charge_item_code=#{chargeItemCode} and item_no=#{itemNo}")
    int updateYpMzFytj(YpMzFytj ypMzFytj);


    /**
     * 通用查询发药统计
     *
     * @return
     */
    @Select({"<script>",
            "select DISTINCT rtrim(patient_id) patient_id,times,receipt_no,order_no,rtrim(name) name,charge_date,rtrim(group_no) group_no,confirm_time,rtrim(doctor_id) doctor_id,rtrim(doctor_name) doctor_name,real_no,manu_no from yp_mz_fytj WITH(NOLOCK) where serial_no <![CDATA[>]]> 0 ",
            "<when test='name!=null'>",
            " and name like #{name}",
            "</when>",
            "<when test='patientId!=null'>",
            " and patient_id = #{patientId}",
            "</when>",
            "<when test='opId!=null'>",
            " and doctor_id = #{opId}",
            "</when>",
            "<when test='serialNo!=null'>",
            " and serial_no = #{serialNo}",
            "</when>",
            "<when test='groupNoOut!=null'>",
            " and group_no_out = #{groupNoOut}",
            "</when>",
            "<when test='confirmFlag!=null'>",
            " and confirm_flag = #{confirmFlag}",
            "</when>",
            "<when test='beginDate!=null'>",
            " and charge_date <![CDATA[>=]]> #{beginDate}",
            "</when>",
            "<when test='endDate!=null'>",
            " and charge_date <![CDATA[<=]]> #{endDate}",
            "</when>",
            "</script>"})
    List<YpMzFytj> selectYpMzFytj(ChargeFeeParamsVo chargeFeeParamsVo);


    /**
     * 查询已经确认的药品记录明细
     * @param chargeFeeParamsVo
     * @return
     */
    @Select({"<script>" +
            "SELECT DISTINCT yp_mz_fytj.serial_no,   " +
            "         yp_mz_fytj.order_no,   " +
            "         yp_mz_fytj.patient_id,   " +
            "         yp_mz_fytj.times,   " +
            "         yp_mz_fytj.receipt_no,   " +
            "         drugname=c.name+'('+isnull(d.abbr_name,isnull(d.name,''))+')',   " +
            "         specification=c.specification,   " +
            "         quantity= case when yp_mz_fytj.cy_fy is not null and yp_mz_fytj.cy_fy >0 then yp_mz_fytj.quantity * cy_fy else yp_mz_fytj.quantity  end,   " +
            "         yp_mz_fytj.unit_price,   " +
            "         yp_mz_fytj.item_no,   " +
            "         yp_mz_fytj.drug_flag,   " +
            "         yp_mz_fytj.charge_item_code,   " +
            "         yp_mz_fytj.serial,   " +
            "         yp_mz_fytj.confirm_time,   " +
            "         yp_mz_fytj.group_no,   " +
            "         yp_mz_fytj.bill_item_code,   " +
            "         yp_mz_fytj.pay_mark,   " +
            "         yp_mz_fytj.charge_item_type,   " +
            "         yp_mz_fytj.name,   " +
            "         yp_mz_fytj.charge_date,   " +
            "         '',   " +
            "         yp_mz_fytj.cy_fy,   " +
            "         yp_mz_fytj.specification,   " +
            "         yp_mz_fytj.drugname,   " +
            "         yp_mz_fytj.doctor_name,   " +
            "         yp_mz_fytj.doctor_id,   " +
            "         dec_amount=case when yp_mz_fytj.cy_fy is not null and yp_mz_fytj.cy_fy >0 then yp_mz_fytj.quantity * cy_fy else yp_mz_fytj.quantity  end,       " +
            "         warn_dept=yp_mz_fytj.warn_dept,   " +
            "         yp_mz_fytj.country_flag,   " +
            "         yp_mz_fytj.confirm_flag,   " +
            "         yp_mz_fytj.self_flag,   " +
            "         yp_mz_fytj.response_type,   " +
            "         yp_mz_fytj.warn_dept  ," +
            "         real_no=yp_mz_fytj.real_no," +
            "         win_no = yp_mz_fytj.win_no, " +
            "         yp_mz_fytj.manu_no " +
            "    FROM yp_mz_fytj WITH(NOLOCK) , " +
            "         yp_zd_dict c WITH(NOLOCK), " +
            "         yp_zd_manufactory d WITH(NOLOCK) " +
            "   WHERE  yp_mz_fytj.patient_id = #{patientId}  AND  " +
            "          yp_mz_fytj.times = #{times}  AND  " +
            "          yp_mz_fytj.receipt_no = #{receiptNo}  AND  " +
            "          yp_mz_fytj.order_no = #{orderNo}  AND  " +
            "         yp_mz_fytj.group_no_out = #{groupNoOut} AND  " +
            "          yp_mz_fytj.charge_item_code = c.code  and  " +
            "          yp_mz_fytj.serial = c.serial  and  " +
            "         yp_mz_fytj.confirm_flag = '1'   and " +
            "         yp_mz_fytj.serial_no > 0 and " +
            "         c.manu_code *= d.code " +
            "ORDER BY yp_mz_fytj.item_no ASC ",
            "</script>"})
    List<Map<String,Object>> selectYpMx(ChargeFeeParamsVo chargeFeeParamsVo);


    /**
     * 通用查询退药申请
     *
     * @return
     */
    @Select({"<script>",
            "select DISTINCT rtrim(patient_id) patient_id,times,receipt_no,order_no,rtrim(name) name,input_time,rtrim(group_no) group_no,confirm_time,rtrim(doctor_id) doctor_id,rtrim(doctor_name) doctor_name,real_no,manu_no from yp_mz_fytj WITH(NOLOCK) where receipt_no <![CDATA[<]]> 0 and confirm_flag =2 ",
            "<when test='name!=null'>",
            " and name like #{name}",
            "</when>",
            "<when test='patientId!=null'>",
            " and patient_id = #{patientId}",
            "</when>",
            "<when test='opId!=null'>",
            " and doctor_id = #{opId}",
            "</when>",
            "<when test='serialNo!=null'>",
            " and serial_no = -#{serialNo}",
            "</when>",
            "<when test='groupNoOut!=null'>",
            " and group_no_out = #{groupNoOut}",
            "</when>",
            "<when test='beginDate!=null'>",
            " and input_time <![CDATA[>=]]> #{beginDate}",
            "</when>",
            "<when test='endDate!=null'>",
            " and input_time <![CDATA[<=]]> #{endDate}",
            "</when>",
            "</script>"})
    List<YpMzFytj> selectRepealRefund(ChargeFeeParamsVo chargeFeeParamsVo);


    /**
     * 查询已经申请退药的记录明细
     * @param chargeFeeParamsVo
     * @return
     */
    @Select({"<script>" +
            "SELECT DISTINCT yp_mz_fytj.serial_no,   " +
            "         yp_mz_fytj.order_no,   " +
            "         yp_mz_fytj.patient_id,   " +
            "         yp_mz_fytj.times,   " +
            "         yp_mz_fytj.receipt_no,   " +
            "         drugname=c.name+'('+isnull(d.abbr_name,isnull(d.name,''))+')',   " +
            "         specification=c.specification,   " +
            "         quantity= case when yp_mz_fytj.cy_fy is not null and yp_mz_fytj.cy_fy >0   then yp_mz_fytj.quantity * cy_fy else yp_mz_fytj.quantity end,   " +
            "         yp_mz_fytj.unit_price,   " +
            "         yp_mz_fytj.item_no,   " +
            "         yp_mz_fytj.drug_flag,   " +
            "         yp_mz_fytj.charge_item_code,   " +
            "         yp_mz_fytj.serial,   " +
            "         yp_mz_fytj.confirm_time,   " +
            "         yp_mz_fytj.group_no,   " +
            "         yp_mz_fytj.bill_item_code,   " +
            "         yp_mz_fytj.pay_mark,   " +
            "         yp_mz_fytj.charge_item_type,   " +
            "         yp_mz_fytj.name,   " +
            "         yp_mz_fytj.charge_date,   " +
            "         '',   " +
            "         yp_mz_fytj.cy_fy,   " +
            "         yp_mz_fytj.specification,   " +
            "         yp_mz_fytj.drugname,   " +
            "         yp_mz_fytj.doctor_name,   " +
            "         yp_mz_fytj.doctor_id,   " +
            "         dec_amount=case when yp_mz_fytj.cy_fy is not null and yp_mz_fytj.cy_fy >0   then yp_mz_fytj.quantity * cy_fy else yp_mz_fytj.quantity end  ,       " +
            "         warn_dept=yp_mz_fytj.warn_dept,   " +
            "         yp_mz_fytj.country_flag,   " +
            "         yp_mz_fytj.confirm_flag,   " +
            "         yp_mz_fytj.self_flag,   " +
            "         yp_mz_fytj.response_type,   " +
            "         yp_mz_fytj.warn_dept  ," +
            "         real_no=yp_mz_fytj.real_no," +
            "         win_no = yp_mz_fytj.win_no, " +
            "         yp_mz_fytj.manu_no " +
            "    FROM  (select * from yp_mz_fytj WITH(NOLOCK) where patient_id = #{patientId} and times = #{times} and receipt_no = #{receiptNo} and quantity  <![CDATA[>]]> 0 ) yp_mz_fytj , " +
            "         yp_zd_dict c WITH(NOLOCK), " +
            "         yp_zd_manufactory d WITH(NOLOCK) " +
            "   WHERE  yp_mz_fytj.order_no = #{orderNo}  AND  " +
            "         yp_mz_fytj.group_no_out = #{groupNoOut} AND  " +
            "          yp_mz_fytj.charge_item_code = c.code  and  " +
            "          yp_mz_fytj.serial = c.serial  and  " +
            "         yp_mz_fytj.confirm_flag = '2'   and " +
            "         yp_mz_fytj.serial_no <![CDATA[<]]> 0 and " +
            "         c.manu_code *= d.code " +
            "ORDER BY yp_mz_fytj.item_no ASC ",
            "</script>"})
    List<Map<String,Object>> selectRefundMedicineDetail(ChargeFeeParamsVo chargeFeeParamsVo);
    /**
     * 修改本院记账的发药统计的流水号为新收款的流水号
     * @param ypMzFytj
     * @return
     */
    @Update("update yp_mz_fytj set serial_no=#{serialNo},real_no=#{realNo},receipt_no=#{receiptNo} where patient_id = #{patientId} and times = #{times} ")
    int updateSeralNoForByjz(YpMzFytj ypMzFytj);

    /**
     * 查询最后一条发药记录
     * @param groupNo 药房分组 71 西药房 72 住院药房 81 中药房 82 颗粒剂药房
     * @param beginDate 开始时间
     * @param endDate 结束时间
     * @return
     */
    @Select("  SELECT DISTINCT " +
            "          top 1 patient_id = a.patient_id,   " +
            "         times = a.times,   " +
            "         receipt_no = a.receipt_no,   " +
            "         order_no = a.order_no,   " +
            "         group_no = a.group_no,   " +
            "         real_no = a.real_no,   " +
            "         name = a.name     ," +
            "         doctor_flag = ''               ," +
            "         warn_dept =warn_dept ," +
            "         windows_no_yf = a.win_no," +
            "         confirm_flag = confirm_flag," +
            " charge_date=a.charge_date" +
            "    FROM yp_mz_fytj a WITH(NOLOCK)" +
            "   WHERE        " +
            "         ( a.group_no_out = #{groupNo} ) AND  " +
            "         ( a.charge_date >= #{beginDate} ) AND  " +
            "         ( a.charge_date <= #{endDate} ) AND  " +
            "         ( a.pay_mark='0' ) and" +
            "         ( a.confirm_flag>'0') " +
            " order by charge_date desc")
    YpMzFytj selectlastItem(@Param(value = "groupNo") String groupNo, @Param("beginDate") Date beginDate, @Param("endDate") Date endDate);

    /**
     * 统计药品最近用量
     * @param code
     * @param serial
     * @param groupNo
     * @param day
     * @return
     */
    @Select("select isnull(sum(quantity),0) from yp_mz_fytj WITH(NOLOCK) where group_no_out = #{groupNo} and confirm_flag = 1 and serial = #{serial} and charge_item_code = #{code} and DATEDIFF( day, confirm_time,getdate())<${day} and DATEPART(w,  confirm_time) <DATEPART(w, getdate())")
    int selectDrugUsageStatistics(@Param("code") String code,@Param("serial") String serial, @Param("groupNo") String groupNo,@Param("day")int day);


    /**
     * 删除发药申请
     * @param ypMzFytj
     * @return
     */
    @Delete("delete from yp_mz_fytj where patient_id =#{patientId} and  times = #{times} and receipt_no =#{receiptNo} and order_no=#{orderNo} and confirm_flag=2 ")
    int deleteYpMzFytj(YpMzFytj ypMzFytj);


    /**
     * 撤销退药申请
     *
     * @param ypMzFytj
     * @return
     */
    @Update("update yp_mz_fytj set real_no=-real_no,serial_no=-serial_no where patient_id = #{patientId} and times = #{times} and receipt_no =abs(#{receiptNo}) and order_no=#{orderNo}")
    int repealRefund(YpMzFytj ypMzFytj);

    /**
     * 查询最近发药量
     * @param chargeCode
     * @param serial
     * @param packSize
     * @param groupNo
     * @return
     */
    @Select("select" +
            "      a.name," +
            "      a.charge_item_code," +
            "      a.serial," +
            "      a.confirm_time" +
            "    from yp_mz_fytj a WITH(NOLOCK)" +
            "     where a.charge_item_code=#{chargeCode} and" +
            "           datediff(day,confirm_time,getdate()) < 31 and" +
            "         a.group_no=#{groupNo}" +
            "     group by a.name,a.charge_item_code,a.serial,a.confirm_time")
    List<Map<String, Object>> selectLatelyYpMzFytj(@Param("chargeCode") String chargeCode, @Param("serial") String serial, @Param("packSize") Double packSize, @Param("groupNo") String groupNo);

    /**
     * 查询科室药品销售金额
     * @param map
     * @return
     */
    @Select("<script>"+
            "select type='门诊',\n" +
            "       kindName= c.name,\n" +
            "       totalMoney =sum(round(convert(decimal(18,3),(convert(decimal(18,3),a.unit_price)) * a.quantity * a.cy_fy),2)),\n" +
            "       deptCode =(case when isnull(a.warn_dept,'')='' then '2020100' else a.warn_dept end) \n" +
            "from yp_mz_fytj a WITH(NOLOCK), (select code,drug_kind from yp_zd_dict WITH(NOLOCK) group by code,drug_kind) b,yp_zd_drug_kind c WITH(NOLOCK)\n" +
            "where  a.charge_item_code=b.code and\n" +
            "       b.drug_kind=c.code and\n" +
            "      a.confirm_time <![CDATA[>=]]> #{beginDate,jdbcType=TIMESTAMP} and\n" +
            "      a.confirm_time <![CDATA[<=]]> #{endDate,jdbcType=TIMESTAMP}\n" +
            "group by c.name,a.warn_dept order by warn_dept,c.name\n" +
            "</script>")
    List<Map<String, Object>> selectYpMzFytjDeptMoney(Map map);

    /**
     * 药房配药更新处方药品发药批次
     *
     * @return
     */
    @Update("update yp_mz_fytj set manu_no=#{manuNo} where real_no=#{realNo} and order_no=#{orderNo} and item_no=#{itemNo}")
    int updateManuNo(@Param("manuNo") String manuNo, @Param("realNo") Integer realNo, @Param("orderNo") Integer orderNo, @Param("itemNo") Integer itemNo);

    @Select(" select count(*) from yp_mz_fytj where serial_no = #{oriSerialNo} ")
    int queryYpMzFytjByOriSerialNo(@Param("oriSerialNo") Integer oriSerialNo);

    @Update(" update yp_mz_fytj set receipt_no =#{receiptNo},serial_no=#{serialNo},real_no=#{serialNo} where serial_no = #{oriSerialNo} ")
    void updateYpMzFytjBySerialNo(@Param("receiptNo") Integer receiptNo, @Param("serialNo") Integer serialNo, @Param("oriSerialNo") Integer oriSerialNo);

}