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

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

import cn.hnthyy.thmz.entity.his.yp.YpInDetl;
import cn.hnthyy.thmz.vo.YpInDetlVo;
import org.apache.ibatis.annotations.*;

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

/**
 * 药库入库持久化类
 */
public interface YpInDetlMapper {

    /**
     * 查询药品入库信息
     *
     * @param ypInDetl
     * @return
     */
    @Select("<script>" +
            "select manu_code,rtrim(manu_no)manu_no,rtrim(in_docu_no)in_docu_no,in_seri,rtrim(charge_code)charge_code,buy_price,avg_price,buy_amt,group_no,serial," +
            " supply_code,eff_date,current_stock,in_date from yp_in_detl WITH(NOLOCK) " +
            "<where>" +
            "   <if test=\"chargeCode != null and chargeCode != ''\">" +
            "        AND charge_code = #{chargeCode}" +
            "   </if>" +
            "   <if test=\"inSeri != null and inSeri != ''\">" +
            "        AND in_seri = #{inSeri}" +
            "   </if>" +
            "   <if test=\"serial != null and serial != ''\">" +
            "        AND serial = #{serial}" +
            "   </if>" +
            "   <if test=\"inDocuNo != null and inDocuNo != ''\">" +
            "        AND in_docu_no = #{inDocuNo}" +
            "   </if>" +
            "   <if test=\"groupNo != null and groupNo != ''\">" +
            "        AND group_no = #{groupNo}" +
            "   </if>" +
            "   <if test=\"currentStock != null\">" +
            "        AND current_stock > ${currentStock}" +
            "   </if>" +
            "</where>" +
            "</script>")
    List<YpInDetl> selectYpInDetl(YpInDetl ypInDetl);

    /**
     * 分页查询入库单记录
     *
     * @param ypInDetlVo
     * @return
     */
    @Select("<script>" +
            "select top ${pageSize} * " +
            " from (select ROW_NUMBER() OVER (ORDER BY in_docu_no DESC)AS RowNumber," +
            " in_date =max(a.in_date)," +
            " rept_no =rtrim(max(a.rept_no))," +
            " supply_code =max(a.supply_code)," +
            " in_docu_no =rtrim(a.in_docu_no)," +
            " purchaser =max(a.purchaser)," +
            " checker =max(a.checker)," +
            " buy_value=sum(round(buy_amt * buy_price,2))," +
            " pack_value=sum(round(buy_amt * pack_retprice,2))" +
            " FROM yp_in_detl a WITH(NOLOCK)" +
            "<where> " +
            "<when test='inType != null'>" +
            "  and in_type in (${inType})" +
            "</when>" +
            "<when test='inDocuNo != null'>" +
            "  and in_docu_no=#{inDocuNo}" +
            "</when>" +
            "<when test='confirmFlag != null'>" +
            " and isnull(a.confirm_flag,'0') = #{confirmFlag}" +
            "</when>" +
            "<when test='reptNo != null'>" +
            " and a.rept_no = #{reptNo}" +
            "</when>" +
            "<when test='groupNo != null'>" +
            "  and group_no=#{groupNo}" +
            "</when>" +
            "<when test='beginTime != null'>" +
            "  and in_date <![CDATA[>=]]> #{beginTime}" +
            "</when>" +
            "<when test='endTime != null'>" +
            " and in_date <![CDATA[<=]]> #{endTime}" +
            "</when>" +
            "</where>" +
            " group by a.in_docu_no) as A WHERE RowNumber >#{pageSize}*#{pageIndex} order by RowNumber asc" +
            "</script>")
    List<YpInDetlVo> selectYpInDetlPage(YpInDetlVo ypInDetlVo);

    /**
     * 查询入库单记录总数
     *
     * @param ypInDetlVo
     * @return
     */
    @Select("<script>" +
            " select count(1) from (select a.in_docu_no" +
            " FROM yp_in_detl a WITH(NOLOCK)" +
            "<where> " +
            "<when test='inType != null'>" +
            "  and in_type in (${inType})" +
            "</when>" +
            "<when test='inDocuNo != null'>" +
            "  and in_docu_no=#{inDocuNo}" +
            "</when>" +
            "<when test='confirmFlag != null'>" +
            " and isnull(a.confirm_flag,'0') = #{confirmFlag}" +
            "</when>" +
            "<when test='reptNo != null'>" +
            " and a.rept_no = #{reptNo}" +
            "</when>" +
            "<when test='groupNo != null'>" +
            "  and group_no=#{groupNo}" +
            "</when>" +
            "<when test='beginTime != null'>" +
            "  and in_date <![CDATA[>=]]> #{beginTime}" +
            "</when>" +
            "<when test='endTime != null'>" +
            " and in_date <![CDATA[<=]]> #{endTime}" +
            "</when>" +
            "</where>" +
            " group by a.in_docu_no)b" +
            "</script>")
    int selectYpInDetlCount(YpInDetlVo ypInDetlVo);

    /**
     * 查询记录详细
     *
     * @param ypInDetlVo
     * @return
     */
    @Select("<script>" +
            "SELECT yp.in_date," +
            "       yp.in_seri," +
            "       rtrim(yp.charge_code)charge_code," +
            "       yp.serial," +
            "       yp.pack_retprice," +
            "       yp.buy_price," +
            "       yp.buy_amt," +
            "       yp.eff_date," +
            "       rtrim(yp.manu_no)manu_no," +
            "       rtrim(yp.rept_no)rept_no," +
            "       rtrim(yp.in_docu_no)in_docu_no," +
            "       yp.supply_code," +
            "       yp.manu_code," +
            "       rtrim(yp.license_no)license_no," +
            "       yp.purchaser," +
            "       yp.checker," +
            "       yp.in_comment," +
            "       yp.in_type," +
            "       yp.acct_type," +
            "       yp.stock_amount," +
            "       yp.fix_price," +
            "       yp.group_no," +
            "       yp.acct_date," +
            "       yp.current_stock," +
            "       yp.kl," +
            "       yp.sys_date," +
            "       yp.fp_date," +
            "       rtrim(yp.hg_flag) as hg_flag," +
            "       '0' as new_flag," +
            "       yp.buy_amt," +
            "       yp.confirm_flag," +
            "       yp.confirm_id," +
            "       yp.accept_id," +
            "       yp.rept_date," +
            "       yp.confirm_date," +
            "       yp.producing_date, " +
            "       zd.national_code " +
            " FROM yp_in_detl yp WITH(NOLOCK)" +
            " left join yp_zd_dict zd on yp.charge_code = zd.code and yp.serial = zd.serial  " +
            "<where>" +
            "   <if test=\"chargeCode != null and chargeCode != ''\">" +
            "        AND yp.charge_code=#{chargeCode}" +
            "   </if>" +
            "   <if test=\"inSeri != null and inSeri != ''\">" +
            "        AND yp.in_seri=#{inSeri}" +
            "   </if>" +
            "   <if test=\"serial != null and serial != ''\">" +
            "        AND yp.serial=#{serial}" +
            "   </if>" +
            "   <if test=\"inDocuNo != null and inDocuNo != ''\">" +
            "        AND yp.in_docu_no=#{inDocuNo}" +
            "   </if>" +
            "   <if test=\"supplyCode != null and supplyCode != ''\">" +
            "        AND yp.supply_code = #{supplyCode}" +
            "   </if>" +
            "   <if test=\"groupNo != null and groupNo != ''\">" +
            "        AND yp.group_no=#{groupNo}" +
            "   </if>" +
            "   <if test=\"serialTh != null and serialTh != ''\">" +
            "        AND yp.serial_th=#{serialTh}" +
            "   </if>" +
            "   <if test=\"beginTime != null and beginTime != ''\">" +
            "        AND yp.in_date &gt;=#{beginTime}" +
            "   </if>" +
            "   <if test=\"endTime != null and endTime != ''\">" +
            "        AND yp.in_date &lt;=#{endTime}" +
            "   </if>" +
            "</where>" +
            " order by yp.supply_code,yp.in_docu_no"+
            "</script>")
    List<YpInDetl> selectYpInDetlDetailed(YpInDetlVo ypInDetlVo);


    /**
     * 分页查询记录详细
     *
     * @param ypInDetlVo
     * @return
     */
    @Select("<script>" +
            "select top ${pageSize} * " +
            " from (select ROW_NUMBER() OVER (ORDER BY in_docu_no DESC)AS RowNumber," +
            " in_date, " +
            " in_seri, " +
            " rtrim(charge_code)charge_code, " +
            " serial, " +
            " pack_retprice, " +
            " buy_price, " +
            " buy_amt, " +
            " eff_date, " +
            " rtrim(manu_no)manu_no, " +
            " rtrim(rept_no)rept_no, " +
            " rtrim(in_docu_no)in_docu_no, " +
            " supply_code, " +
            " manu_code, " +
            " rtrim(license_no)license_no, " +
            " purchaser, " +
            " checker, " +
            " in_comment, " +
            " in_type, " +
            " acct_type, " +
            " stock_amount, " +
            " fix_price, " +
            " group_no, " +
            " acct_date, " +
            " current_stock, " +
            " kl, " +
            " sys_date, " +
            " fp_date, " +
            " rtrim(hg_flag) as hg_flag, " +
            " confirm_flag, " +
            " confirm_id, " +
            " accept_id, " +
            " rept_date, " +
            " confirm_date, " +
            " producing_date " +
            " FROM yp_in_detl a WITH(NOLOCK)" +
            "<where> " +
            " and confirm_flag=1" +
            "<when test='inType != null'>" +
            "  and in_type in (${inType})" +
            "</when>" +
            "<when test='chargeCode != null'>" +
            "  and charge_code=#{chargeCode}" +
            "</when>" +
            "<when test='manuCode != null'>" +
            "  and manu_code=#{manuCode}" +
            "</when>" +
            "<when test='supplyCode != null'>" +
            "  and supply_code=#{supplyCode}" +
            "</when>" +
            "<when test='inDocuNo != null'>" +
            "  and in_docu_no=#{inDocuNo}" +
            "</when>" +
            "<when test='confirmFlag != null'>" +
            " and isnull(a.confirm_flag,'0') = #{confirmFlag}" +
            "</when>" +
            "<when test='reptNo != null'>" +
            " and a.rept_no = #{reptNo}" +
            "</when>" +
            "<when test='groupNo != null'>" +
            "  and group_no=#{groupNo}" +
            "</when>" +
            "<when test='beginTime != null'>" +
            "  and in_date <![CDATA[>=]]> #{beginTime}" +
            "</when>" +
            "<when test='endTime != null'>" +
            " and in_date <![CDATA[<=]]> #{endTime}" +
            "</when>" +
            "</where>" +
            " ) as A WHERE RowNumber >#{pageSize}*#{pageIndex} order by RowNumber asc" +
            "</script>")
    List<YpInDetl> selectYpInDetlDetailedPage(YpInDetlVo ypInDetlVo);

    /**
     * 查询入库单详细记录总数
     *
     * @param ypInDetlVo
     * @return
     */
    @Select("<script>" +
            " select count(1) from (select a.in_docu_no" +
            " FROM yp_in_detl a WITH(NOLOCK)" +
            "<where> " +
            "  and confirm_flag=1" +
            "<when test='inType != null'>" +
            "  and in_type in (${inType})" +
            "</when>" +
            "<when test='chargeCode != null'>" +
            "  and charge_code=#{chargeCode}" +
            "</when>" +
            "<when test='inDocuNo != null'>" +
            "  and in_docu_no=#{inDocuNo}" +
            "</when>" +
            "<when test='confirmFlag != null'>" +
            " and isnull(a.confirm_flag,'0') = #{confirmFlag}" +
            "</when>" +
            "<when test='reptNo != null'>" +
            " and a.rept_no = #{reptNo}" +
            "</when>" +
            "<when test='groupNo != null'>" +
            "  and group_no=#{groupNo}" +
            "</when>" +
            "<when test='beginTime != null'>" +
            "  and in_date <![CDATA[>=]]> #{beginTime}" +
            "</when>" +
            "<when test='endTime != null'>" +
            " and in_date <![CDATA[<=]]> #{endTime}" +
            "</when>" +
            "</where>" +
            " )b" +
            "</script>")
    int selectYpInDetlDetailedCount(YpInDetlVo ypInDetlVo);

    /**
     * 删除入库记录单
     *
     * @param inDocuNo
     * @return
     */
    @Delete("delete from yp_in_detl where in_docu_no=#{inDocuNo}")
    int deleteYpInDetlByInDocuNo(@Param("inDocuNo") String inDocuNo);

    /**
     * 删除入库药品信息
     *
     * @param inDocuNo
     * @param inSeri
     * @return
     */
    @Delete("delete from yp_in_detl where in_docu_no=#{inDocuNo} and in_seri=#{inSeri}")
    int deleteYpInDetl(@Param("inDocuNo") String inDocuNo, @Param("inSeri") Integer inSeri);

    /**
     * 保存入库记录单
     *
     * @param ypInDetlList
     * @return
     */
    @Insert("<script>" +
            "insert into yp_in_detl ( in_date , in_seri , charge_code , serial , buy_amt , buy_price , eff_date , manu_no , rept_no , in_docu_no , supply_code , manu_code , " +
            "license_no , purchaser , checker , in_comment , in_type , pack_retprice , acct_type , fix_price , group_no , current_stock , sys_date , rept_date , input_id , stock_amount , avg_price,producing_date )" +
            " values" +
            " <foreach item='item' collection='ypInDetlList' separator=','> " +
            "  (#{item.inDate,jdbcType=TIMESTAMP},#{item.inSeri,jdbcType=BIGINT},#{item.chargeCode,jdbcType=VARCHAR},#{item.serial,jdbcType=VARCHAR},#{item.buyAmt,jdbcType=DOUBLE}," +
            " cast(#{item.buyPrice} as decimal(15,3)),#{item.effDate,jdbcType=DATE},#{item.manuNo,jdbcType=VARCHAR},#{item.reptNo,jdbcType=VARCHAR}," +
            " #{item.inDocuNo,jdbcType=VARCHAR},#{item.supplyCode,jdbcType=VARCHAR},#{item.manuCode,jdbcType=VARCHAR},#{item.licenseNo,jdbcType=VARCHAR}," +
            " #{item.purchaser,jdbcType=VARCHAR},#{item.checker,jdbcType=VARCHAR},#{item.inComment,jdbcType=VARCHAR},#{item.inType,jdbcType=VARCHAR}," +
            " cast(#{item.packRetprice} as decimal(15,3)),#{item.acctType,jdbcType=VARCHAR},cast(#{item.fixPrice} as decimal(15,3)),#{item.groupNo,jdbcType=VARCHAR}," +
            " #{item.currentStock,jdbcType=DOUBLE},#{item.sysDate,jdbcType=TIMESTAMP},#{item.reptDate,jdbcType=DATE},#{item.inputId,jdbcType=VARCHAR}," +
            " #{item.stockAmount,jdbcType=DOUBLE},#{item.avgPrice,jdbcType=DOUBLE},#{item.producingDate,jdbcType=DATE})" +
            " </foreach>" +
            "</script>")
    int insertYpInDetlList(@Param("ypInDetlList") List<YpInDetl> ypInDetlList);
    /**
     * 保存入库记录单
     *
     * @param sql
     * @return
     */
    @Insert("<script>" +
            "insert into yp_in_detl ${sql}"+
            "</script>")
    int insertYpInDetl(@Param("sql") String sql);

    /**
     * 更新入库单信息
     *
     * @return
     */
    @Update({"<script>",
            "update yp_in_detl ",
            "<trim prefix='set' prefixOverrides=','>",
            "<when test='buyPrice!=null'>",
            ",buy_price =#{buyPrice,jdbcType=DOUBLE}",
            "</when>",
            "<when test='buyAmt!=null'>",
            ",buy_amt =#{buyAmt,jdbcType=DOUBLE}",
            "</when>",
            "<when test='confirmFlag!=null'>",
            ",confirm_flag =#{confirmFlag}",
            "</when>",
            "<when test='confirmId!=null'>",
            ",confirm_id =#{confirmId}",
            "</when>",
            "<when test='confirmDate!=null'>",
            ",confirm_date =#{confirmDate}",
            "</when>",
            "<when test='currentStock!=null'>",
            ",current_stock =current_stock+${currentStock}",
            "</when>",
            "</trim>",
            "<where>" +
            "   <if test=\"inDocuNo != null and inDocuNo != ''\">" +
            "        AND in_docu_no = #{inDocuNo}" +
            "   </if>" +
            "   <if test=\"inSeri != null and inSeri != ''\">" +
            "        AND in_seri = #{inSeri}" +
            "   </if>" +
            "   <if test=\"chargeCode != null and chargeCode != ''\">" +
            "        AND charge_code = #{chargeCode}" +
            "   </if>" +
            "   <if test=\"serial != null and serial != ''\">" +
            "        AND serial = #{serial}" +
            "   </if>" +
            "</where>" +
            "</script>"})
    int updateYpInDetl(YpInDetl ypInDetl);

    /**
     * 入库验收分类统计金额
     *
     * @param ypInDetlVo
     * @return
     */
    @Select("<script>" +
            "SELECT c.name," +
            "       c.yp_type as ypType," +
            "       buyMoney=sum(cast(a.buy_price* a.buy_amt as decimal(18,2)))," +
            "       packMoney=sum(cast(a.pack_retprice * a.buy_amt as decimal(18,2)))" +
            "FROM yp_in_detl a WITH(NOLOCK),yp_zd_dict b WITH(NOLOCK),yp_zd_drug_kind c WITH(NOLOCK)" +
            "<where>" +
            "    AND a.charge_code=b.code" +
            "    AND a.serial=b.serial" +
            "    AND b.drug_kind=c.code" +
            "   <if test=\"inDocuNo != null and inDocuNo != ''\">" +
            "        AND in_docu_no=#{inDocuNo}" +
            "   </if>" +
            "   <if test=\"supplyCode != null and supplyCode != ''\">" +
            "        AND supply_code = #{supplyCode}" +
            "   </if>" +
            "   <if test=\"groupNo != null and groupNo != ''\">" +
            "        AND group_no=#{groupNo}" +
            "   </if>" +
            "   <if test=\"beginTime != null and beginTime != ''\">" +
            "        AND in_date &gt;=#{beginTime}" +
            "   </if>" +
            "   <if test=\"endTime != null and endTime != ''\">" +
            "        AND in_date &lt;=#{endTime}" +
            "   </if>" +
            "</where>" +
            " group by c.name,c.yp_type"+
            "</script>")
    List<Map<String, Object>> selectYpInDetlAccSum(YpInDetlVo ypInDetlVo);

    /**
     * 查询现时库存大于0的入库记录
     * @param ypInDetl
     * @return
     */
    @Select("<script>"+
            "SELECT in_date,in_seri,rtrim(charge_code)charge_code,serial,pack_retprice," +
            "       buy_price,buy_amt, eff_date, manu_no," +
            "       rept_no,rtrim(in_docu_no)in_docu_no,supply_code,manu_code," +
            "       license_no,purchaser,checker,in_comment," +
            "       in_type,acct_type,stock_amount,fix_price,group_no,acct_date," +
            "       current_stock,kl,sys_date,fp_date,hg_flag" +
            " FROM yp_in_detl WITH(NOLOCK)" +
            "<where>" +
            "    current_stock > 0" +
            "   <if test=\"chargeCode != null and chargeCode != ''\">" +
            "        AND charge_code=#{chargeCode}" +
            "   </if>" +
            "   <if test=\"groupNo != null and groupNo != ''\">" +
            "        AND group_no = #{groupNo}" +
            "   </if>" +
            "   <if test=\"supplyCode != null and supplyCode != ''\">" +
            "        AND supply_code=#{supplyCode}" +
            "   </if>" +
            "   <if test=\"reptNo != null and reptNo != ''\">" +
            "        AND rept_no=#{reptNo}" +
            "   </if>" +
            "   <if test=\"inDocuNo != null and inDocuNo != ''\">" +
            "        AND in_docu_no=#{inDocuNo}" +
            "   </if>" +
            "</where>" +
            "</script>")
    List<YpInDetl> selectCurrentStockGreaterZero(YpInDetl ypInDetl);

    /**
     * 统计供应商药品购入汇总
     * @param map
     * @return
     */
    @Select("<script>"+
            "SELECT " +
            "       buyAmt = sum(buy_amt),\n" +
            "       packRetpriceSum = sum(buy_amt *convert(decimal(18,4), pack_retprice)),\n" +
            "       buyPriceSum = sum(buy_amt * convert(decimal(18,4),buy_price)),\n" +
            "       reptNoSum=count(distinct rept_no),\n" +
            "       manuCode=max(manu_code),\n" +
            "       supplyCode=max(supply_code),\n" +
            "       in_docu_no AS inDocuNo" +
            " FROM yp_in_detl WITH(NOLOCK)" +
            "<where>" +
            "   <if test=\"beginDate != null and beginDate != ''\">" +
            "       and in_date <![CDATA[>=]]> #{beginDate}"+
            "   </if>"+
            "   <if test=\"endDate != null and endDate != ''\">" +
            "       and in_date <![CDATA[<=]]> #{endDate}"+
            "   </if>"+
            "   <if test=\"groupNo != null and groupNo != ''\">" +
            "        AND group_no = #{groupNo}" +
            "   </if>" +
            "       GROUP BY supply_code,in_docu_no" +
            "       ORDER BY supply_code,sum(buy_amt * buy_price) DESC" +
            "</where>" +
            "</script>")
    List<Map<String,Object>> selectDrugSupplySum(Map map);

    /**
     * 统计供应商药品购入明细
     * @param map
     * @return
     */
    @Select("<script>"+
            "SELECT chargeCode=rtrim(charge_code)," +
            "       serial," +
            "       buyAmt = sum(buy_amt),\n" +
            "       packRetpriceSum = sum(buy_amt *convert(decimal(18,4), pack_retprice)),\n" +
            "       buyPriceSum = sum(buy_amt * convert(decimal(18,4),buy_price)),\n" +
            "       reptNoSum=count(distinct rept_no),\n" +
            "       manuCode=max(manu_code),\n" +
            "       supplyCode=max(supply_code)" +
            " FROM yp_in_detl WITH(NOLOCK)" +
            "<where>" +
            "   <if test=\"beginDate != null and beginDate != ''\">" +
            "       and in_date <![CDATA[>=]]> #{beginDate}"+
            "   </if>"+
            "   <if test=\"endDate != null and endDate != ''\">" +
            "       and in_date <![CDATA[<=]]> #{endDate}"+
            "   </if>"+
            "   <if test=\"groupNo != null and groupNo != ''\">" +
            "        AND group_no = #{groupNo}" +
            "   </if>" +
            "       GROUP BY charge_code," +
            "         serial     ," +
            "         supply_code" +
            "       order by supply_code,sum(buy_amt * buy_price) desc" +
            "</where>" +
            "</script>")
    List<Map<String,Object>> selectDrugSupplyDetailed(Map map);

    /**
     * 统计生产厂家药品购入汇总
     * @param map
     * @return
     */
    @Select("<script>"+
            "SELECT " +
            "       buyAmt = sum(buy_amt),\n" +
            "       packRetpriceSum = sum(buy_amt *convert(decimal(18,4), pack_retprice)),\n" +
            "       buyPriceSum = sum(buy_amt * convert(decimal(18,4),buy_price)),\n" +
            "       reptNoSum=count(distinct rept_no),\n" +
            "       manuCode=max(manu_code),\n" +
            "       supplyCode=max(supply_code)" +
            " FROM yp_in_detl WITH(NOLOCK)" +
            "<where>" +
            "   <if test=\"beginDate != null and beginDate != ''\">" +
            "       and in_date <![CDATA[>=]]> #{beginDate}"+
            "   </if>"+
            "   <if test=\"endDate != null and endDate != ''\">" +
            "       and in_date <![CDATA[<=]]> #{endDate}"+
            "   </if>"+
            "   <if test=\"groupNo != null and groupNo != ''\">" +
            "        AND group_no = #{groupNo}" +
            "   </if>" +
            "       GROUP BY manu_code" +
            "       ORDER BY manu_code,sum(buy_amt * buy_price) DESC" +
            "</where>" +
            "</script>")
    List<Map<String,Object>> selectDrugManuSum(Map map);

    /**
     * 统计生产厂家药品购入明细
     * @param map
     * @return
     */
    @Select("<script>"+
            "SELECT chargeCode=rtrim(charge_code)," +
            "       serial," +
            "       buyAmt = sum(buy_amt),\n" +
            "       packRetpriceSum = sum(buy_amt *convert(decimal(18,4), pack_retprice)),\n" +
            "       buyPriceSum = sum(buy_amt * convert(decimal(18,4),buy_price)),\n" +
            "       reptNoSum=count(distinct rept_no),\n" +
            "       manuCode=max(manu_code),\n" +
            "       supplyCode=max(supply_code)" +
            " FROM yp_in_detl WITH(NOLOCK)" +
            "<where>" +
            "   <if test=\"beginDate != null and beginDate != ''\">" +
            "       and in_date <![CDATA[>=]]> #{beginDate}"+
            "   </if>"+
            "   <if test=\"endDate != null and endDate != ''\">" +
            "       and in_date <![CDATA[<=]]> #{endDate}"+
            "   </if>"+
            "   <if test=\"groupNo != null and groupNo != ''\">" +
            "        AND group_no = #{groupNo}" +
            "   </if>" +
            "       GROUP BY charge_code," +
            "         serial     ," +
            "         manu_code" +
            "       order by manu_code,sum(buy_amt * buy_price) desc" +
            "</where>" +
            "</script>")
    List<Map<String,Object>> selectDrugManuDetailed(Map map);

    /**
     * 统计药库入库金额
     * @param map
     * @return
     */
    @Select("<script>"+
            "select  a.group_no as groupNo,\n" +
            "        left(convert(varchar(12),in_date,111),7) as countDate,\n" +
            "        sum(round(convert(decimal(18,3),(convert(decimal(18,3),a.buy_price )* a.buy_amt)),2)) as inMoneyBuy,\n" +
            "        sum(round(convert(decimal(18,3),(convert(decimal(18,3),a.pack_retprice )* a.buy_amt)),2)) as inMoneyRet,\n" +
            "        outMoneyRet=0\n" +
            "   from yp_in_detl a WITH(NOLOCK)\n" +
            "   where  in_date <![CDATA[>=]]> #{beginDate} and\n" +
            "   in_date <![CDATA[<=]]> #{endDate} and\n" +
            "   a.group_no =#{groupNo}" +
            "   group by a.group_no,\n" +
            "         left(convert(varchar(12),in_date,111),7)" +
            "</script>")
    List<Map<String,Object>> selectDrugInMoneyCount(Map map);


    /**
     * 按药品分类统计入库金额
     * @param map
     * @return
     */
    @Select("<script>"+
            "select c.name,sum(b.buy_amt*b.buy_price) inMoneyBuy,sum(b.buy_amt*b.pack_retprice) inMoneyRet from yp_zd_dict a WITH(NOLOCK),yp_in_detl b WITH(NOLOCK),yp_zd_drug_kind c WITH(NOLOCK)\n" +
            " where a.code=b.charge_code and a.serial=b.serial and a.drug_kind=c.code\n" +
            "  and b.in_date<![CDATA[>=]]>#{beginDate} and b.in_date<![CDATA[<=]]>#{endDate} and b.group_no=#{groupNo}\n" +
            "  group by c.code,c.name" +
            "</script>")
    List<Map<String,Object>> selectDrugKindInMoneyCount(Map map);

    /**
     * 按药性分类统计入库金额
     * @param map
     * @return
     */
    @Select("<script>"+
            "select\n" +
            "  inMoneyBuy = sum(a.buy_price * a.buy_amt),\n" +
            "  inMoneyRet = sum(a.pack_retprice * a.buy_amt),\n" +
            "  outMoneyRet = 0,\n" +
            "  classCode = c.class_code\n" +
            "from yp_in_detl a WITH(NOLOCK),yp_zd_dict c WITH(NOLOCK)\n" +
            "where in_date <![CDATA[>=]]>#{beginDate} and\n" +
            "    in_date <![CDATA[<=]]>#{endDate} and\n" +
            "    a.group_no=#{groupNo} and\n" +
            "    a.charge_code=c.code and\n" +
            "    a.serial=c.serial\n" +
            "group by c.class_code" +
            "</script>")
    List<Map<String,Object>> selectDrugClassInMoneyCount(Map map);

    @Select(" select min(eff_date) as effDate from yp_in_detl where charge_code = #{chargeCode} and current_stock >= 1 ")
    String selectEffDateByCode(@Param("chargeCode") String chargeCode);

    @Select(" select max(eff_date) as effDate from yp_in_detl where charge_code = #{chargeCode} and current_stock = 0 ")
    String selectEffDateZeroByCode(@Param("chargeCode") String chargeCode);
}