<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shinsoft.sto.mapper.OnhandMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.shinsoft.sto.model.Onhand">
        <result column="ID" property="id" />
        <result column="PROCESS_INSTANCE_ID" property="processInstanceId" />
        <result column="PROCESS_INSTANCE_STATUS" property="processInstanceStatus" />
        <result column="ADD_ID" property="addId" />
        <result column="ADD_CODE" property="addCode" />
        <result column="ADD_NAME" property="addName" />
        <result column="ADD_DEPT_ID" property="addDeptId" />
        <result column="ADD_DEPT_CODE" property="addDeptCode" />
        <result column="ADD_DEPT_NAME" property="addDeptName" />
        <result column="ADD_TIME" property="addTime" />
        <result column="MODIFY_ID" property="modifyId" />
        <result column="MODIFY_CODE" property="modifyCode" />
        <result column="MODIFY_NAME" property="modifyName" />
        <result column="MODIFY_TIME" property="modifyTime" />
        <result column="CANCEL_ID" property="cancelId" />
        <result column="CANCEL_CODE" property="cancelCode" />
        <result column="CANCEL_NAME" property="cancelName" />
        <result column="CANCEL_TIME" property="cancelTime" />
        <result column="CANCEL_FLAG" property="cancelFlag" />
        <result column="BELONG_ID" property="belongId" />
        <result column="COMP_CD" property="compCd" />
        <result column="DEPT_CD" property="deptCd" />
        <result column="STORE_CD" property="storeCd" />
        <result column="STORE_NM" property="storeNm" />
        <result column="AREA_CD" property="areaCd" />
        <result column="STACK_CD" property="stackCd" />
        <result column="STORE_TYPE" property="storeType" />
        <result column="ITEM_CD" property="itemCd" />
        <result column="ITEM_NM" property="itemNm" />
        <result column="PUR_ORD_NO" property="purOrdNo" />
        <result column="PUR_ORD_LN" property="purOrdLn" />
        <result column="ITEM_TYPE" property="itemType" />
        <result column="UOM" property="uom" />
        <result column="QTY" property="qty" />
        <result column="QTY_WET" property="qtyWet" />
        <result column="LAST_NAME" property="lastName" />
        <result column="LAST_TIME" property="lastTime" />
        <result column="REMARKS" property="remarks" />
        <result column="PRICE" property="price" />
        <result column="BATCH_NO" property="batchNo" />
        <result column="ATTR1" property="attr1" />
        <result column="ATTR2" property="attr2" />
        <result column="ATTR3" property="attr3" />
        <result column="ATTR4" property="attr4" />
        <result column="ATTR5" property="attr5" />
        <result column="ATTR6" property="attr6" />
        <result column="ATTR7" property="attr7" />
        <result column="ATTR8" property="attr8" />
        <result column="ATTR9" property="attr9" />
        <result column="ATTR10" property="attr10" />
    </resultMap>

    <select id="querySumOnhand" resultType="map" parameterType="map">
        SELECT SUM(QTY) "count",
       item.ITEM_CD "itemCd",
       item.ITEM_NM "itemNm"
        FROM STO_ONHAND onhand
            LEFT JOIN BASE_ITEM item on onhand.ITEM_CD = item.ITEM_CD
        WHERE item.ITEM_CD = #{itemCd}
        group by item.ITEM_CD, item.ITEM_NM
    </select>

    <select id="querySumOnhandKf" resultType="map" parameterType="map">
        SELECT SUM(QTY) "count",
       item.ITEM_CD as "itemCd",
       item.ITEM_NM as "itemNm",
       onhand.STORE_CD as "storeCd",
       onhand.STORE_NM as "storeNm"
        FROM STO_ONHAND onhand
            LEFT JOIN BASE_ITEM item on onhand.ITEM_CD = item.ITEM_CD
        WHERE item.ITEM_CD = #{itemCd}
          and onhand.STORE_CD = #{storeCd}
        group by item.ITEM_CD, item.ITEM_NM, onhand.STORE_CD, onhand.STORE_NM
    </select>

    <select id="querySumOnhandQh" resultType="map" parameterType="map">
        SELECT SUM(QTY) "count",
       item.ITEM_CD "itemCd",
       item.ITEM_NM "itemNm",
       onhand.STORE_CD "storeCd",
       onhand.STORE_NM "storeNm",
       onhand.AREA_CD "areaCd"
        FROM STO_ONHAND onhand
            LEFT JOIN BASE_ITEM item on onhand.ITEM_CD = item.ITEM_CD
        WHERE item.ITEM_CD = #{itemCd}
          and onhand.STORE_CD = #{storeCd}
          and onhand.AREA_CD = #{areaCd}
        group by item.ITEM_CD, item.ITEM_NM, onhand.STORE_CD, onhand.STORE_NM, onhand.AREA_CD
    </select>

    <select id="querySumOnhandDw" resultType="map" parameterType="map">
        SELECT SUM(QTY) "count",
       item.ITEM_CD "itemCd",
       item.ITEM_NM "itemNm",
       onhand.STORE_CD "storeCd",
       onhand.STORE_NM "storeNm",
       onhand.AREA_CD "areaCd",
       onhand.STACK_CD "stackCd"
        FROM STO_ONHAND onhand
            LEFT JOIN BASE_ITEM item on onhand.ITEM_CD = item.ITEM_CD
        WHERE item.ITEM_CD = #{itemCd}
          and onhand.STORE_CD = #{storeCd}
          and onhand.AREA_CD = #{areaCd}
          and onhand.STACK_CD =  #{stackCd}
        group by item.ITEM_CD, item.ITEM_NM, onhand.STORE_CD, onhand.STORE_NM, onhand.AREA_CD, onhand.STACK_CD
    </select>


    <select id="selectDelvOut" resultMap="BaseResultMap" >
        SELECT *
        FROM STO_ONHAND onhand
        WHERE nvl(onhand.CANCEL_FLAG,'0')!='1'
        and  onhand.ITEM_CD = #{itemCd}
       and onhand.STORE_CD = #{storeCd}
    </select>

    <select id="queryList" resultType="com.shinsoft.sto.model.Onhand">
        SELECT HAN.ID                      ID,
        HAN.PROCESS_INSTANCE_ID     PROCESS_INSTANCE_ID,
        HAN.PROCESS_INSTANCE_STATUS PROCESS_INSTANCE_STATUS,
        HAN.ADD_ID                  ADD_ID,
        HAN.ADD_CODE                ADD_CODE,
        HAN.ADD_NAME                ADD_NAME,
        HAN.ADD_DEPT_ID             ADD_DEPT_ID,
        HAN.ADD_DEPT_CODE           ADD_DEPT_CODE,
        HAN.ADD_DEPT_NAME           ADD_DEPT_NAME,
        HAN.ADD_TIME                ADD_TIME,
        HAN.MODIFY_ID               MODIFY_ID,
        HAN.MODIFY_CODE             MODIFY_CODE,
        HAN.MODIFY_NAME             MODIFY_NAME,
        HAN.MODIFY_TIME             MODIFY_TIME,
        HAN.CANCEL_ID               CANCEL_ID,
        HAN.CANCEL_CODE             CANCEL_CODE,
        HAN.CANCEL_NAME             CANCEL_NAME,
        HAN.CANCEL_TIME             CANCEL_TIME,
        HAN.CANCEL_FLAG             CANCEL_FLAG,
        HAN.BELONG_ID               BELONG_ID,
        HAN.COMP_CD                 COMP_CD,
        HAN.DEPT_CD                 DEPT_CD,
        HAN.STORE_CD                STORE_CD,
        CON.STORE_NM                STORE_NM,
        CON.STORE_TYPE              STORE_TYPE,
        HAN.AREA_CD                 AREA_CD,
        HAN.STACK_CD                STACK_CD,

        HAN.ITEM_CD                 ITEM_CD,
        HAN.ITEM_NM                 ITEM_NM,
        HAN.PUR_ORD_NO              PUR_ORD_NO,
        HAN.PUR_ORD_LN              PUR_ORD_LN,
        HAN.ITEM_TYPE               ITEM_TYPE,
        HAN.UOM                     UOM,
        HAN.QTY                     QTY,
        HAN.QTY_WET                 QTY_WET,
        HAN.LAST_NAME               LAST_NAME,
        HAN.LAST_TIME               LAST_TIME,
        HAN.REMARKS                 REMARKS,
        HAN.PRICE                   PRICE,
        HAN.BATCH_NO                BATCH_NO
        FROM STO_ONHAND HAN
                 LEFT JOIN STO_CONFIG CON ON HAN.STORE_CD = CON.STORE_CD
        WHERE HAN.CANCEL_FLAG = '0'
        <if test="map.storeNm != null and map.storeNm != '' ">
            AND CON.store_nm like '%'||  #{map.storeNm} ||'%'
        </if>
        <if test="map.areaCd != null and map.areaCd != '' ">
            AND HAN.area_cd like '%'||  #{map.areaCd} ||'%'
        </if>
        <if test="map.stackCd != null and map.stackCd != '' ">
            AND HAN.stack_cd like '%'||  #{map.stackCd} ||'%'
        </if>
        <if test="map.itemNm != null and map.itemNm != '' ">
            AND HAN.item_nm like '%'||  #{map.itemNm} ||'%'
        </if>
        <if test="map.itemType != null and map.itemType != '' ">
            AND HAN.item_type = #{map.itemType}
        </if>
        <if test="map.storeType != null and map.storeType != '' ">
            AND CON.STORE_TYPE = #{map.storeType}
        </if>
        order by HAN.add_time desc
    </select>


    <select id="getSumList" resultType="com.shinsoft.sto.model.Onhand">
        SELECT sum(qty) qty, STORE_CD, ITEM_CD
        FROM STO_ONHAND
        WHERE STORE_CD IN
        <foreach collection="storeCd" item="store" open="(" close=")" separator=",">
            #{store}
        </foreach>
        and ITEM_CD in
        <foreach collection="itemCd" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
        group by STORE_CD, ITEM_CD
    </select>
    <select id="selectOnhand" resultType="com.shinsoft.sto.model.Onhand">
        select
        oo.PROCESS_INSTANCE_ID,
        oo.PROCESS_INSTANCE_STATUS,
        oo.ADD_ID,
        oo.ADD_CODE,
        oo.ADD_NAME,
        oo.ADD_DEPT_ID,
        oo.ADD_DEPT_CODE,
        oo.ADD_DEPT_NAME,
        oo.ADD_TIME,
        oo.MODIFY_ID,
        oo.MODIFY_CODE,
        oo.MODIFY_NAME,
        oo.MODIFY_TIME,
        oo.CANCEL_ID,
        oo.CANCEL_CODE,
        oo.CANCEL_NAME,
        oo.CANCEL_TIME,
        oo.CANCEL_FLAG,
        oo.BELONG_ID,
        oo.COMP_CD,
        oo.DEPT_CD,
        oo.STORE_CD,
        oo.STORE_TYPE,
        oo.AREA_CD,
        oo.STACK_CD,
        oo.ITEM_CD,
        oo.ITEM_NM,
        oo.PUR_ORD_NO,
        oo.PUR_ORD_LN,
        oo.ITEM_TYPE,
        oo.UOM,
        oo.QTY,
        oo.QTY_WET,
        oo.LAST_NAME,
        oo.LAST_TIME,
        oo.REMARKS,
        oo.PRICE,
        oo.BATCH_NO,
        cc.store_nm as store_nm
        from sto_onhand oo
        left join sto_config cc
        on oo.store_cd = cc.store_cd
        where oo.cancel_flag = '0'
        <if test="storeNm != null and storeNm != '' ">
            and cc.store_nm = #{storeNm}
        </if>
        <if test="itemType != null and itemType != '' ">
            and oo.item_type = #{itemType}
        </if>
        <if test="areaCd != null and areaCd != '' ">
            and oo.area_cd = #{areaCd}
        </if>
        <if test="stackCd != null and stackCd != '' ">
            and oo.stack_cd = #{stackCd}
        </if>
        <if test="itemNm != null and itemNm != '' ">
            and oo.item_nm = #{itemNm}
        </if>
    </select>


</mapper>
