package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmswms.dto.WhsSearchDTO;
import com.sunda.spmswms.entity.WhsStorageInventory;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;

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

/**
 * <p>
 * 储位库存信息表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-03-18
 */
@Mapper
public interface WhsStorageInventoryMapper extends BaseMapper<WhsStorageInventory> {

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "    LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "    LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID\n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL AND WSI.BOX_NOTE IN (" +
            "    SELECT DISTINCT WHSLOG.BOX_NOTE\n" +
            "    FROM WHS_OPERATE_LOG WHSLOG\n" +
            "             LEFT JOIN WHS_STORAGE WHSSTORAGE ON WHSLOG.UUID_STORAGE = WHSSTORAGE.UUID\n" +
            "    WHERE WHSLOG.UUID_STORAGE IS NOT NULL\n" +
            "      AND WHSLOG.BOX_NOTE IS NOT NULL\n" +
            "      AND WHSLOG.DOCUMENT_TYPE = '2'\n" +
            "      AND WHSSTORAGE.WERKS = #{werks}\n" +
            "      AND WHSSTORAGE.WHS_LOCATION_CODE = #{whsLocationCode}\n" +
            "      AND WHSLOG.CREATE_TIME BETWEEN TO_DATE(#{startDate}, 'yyyy-MM-dd hh24:mi:ss') AND TO_DATE(#{endDate}, 'yyyy-MM-dd hh24:mi:ss')\n" +
            ")\n" +
            "</script>")
    List<Map<String, Object>> getStorageXByDate(@Param("werks") String werks,
                                                @Param("whsLocationCode") String whsLocationCode,
                                                @Param("startDate") String startDate,
                                                @Param("endDate") String endDate
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "    LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "    LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID\n" +
            "WHERE WSI.QUANTITY>0 AND WHS.WERKS = #{werks} AND WHS.WHS_LOCATION_CODE = #{whsLocationCode} \n" +
            "   AND WSI.MATERIAL_NO IS NOT NULL AND WSI.MATERIAL_NO IN (" +
            "    SELECT DISTINCT WHSLOG.MATERIAL_NO\n" +
            "    FROM WHS_OPERATE_LOG WHSLOG\n" +
            "             LEFT JOIN WHS_STORAGE WHSSTORAGE ON WHSLOG.UUID_STORAGE = WHSSTORAGE.UUID\n" +
            "    WHERE WHSLOG.UUID_STORAGE IS NOT NULL\n" +
            "      AND WHSLOG.MATERIAL_NO IS NOT NULL\n" +
            "      AND WHSLOG.DOCUMENT_TYPE = '2'\n" +
            "      AND WHSSTORAGE.WERKS = #{werks}\n" +
            "      AND WHSSTORAGE.WHS_LOCATION_CODE = #{whsLocationCode}\n" +
            "      AND WHSLOG.CREATE_TIME BETWEEN TO_DATE(#{startDate}, 'yyyy-MM-dd hh24:mi:ss') AND TO_DATE(#{endDate}, 'yyyy-MM-dd hh24:mi:ss')\n" +
            ")\n" +
            "</script>")
    List<Map<String, Object>> getStorageMByDate(@Param("werks") String werks,
                                                @Param("whsLocationCode") String whsLocationCode,
                                                @Param("startDate") String startDate,
                                                @Param("endDate") String endDate
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "    LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "    LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID\n" +
            "WHERE WSI.QUANTITY>0 AND WHS.WERKS = #{werks} AND WHS.WHS_LOCATION_CODE = #{whsLocationCode} \n" +
            "   AND WSI.MATERIAL_NO IS NOT NULL AND WSI.MATERIAL_NO IN (" +
            "    SELECT DISTINCT WHSLOG.MATERIAL_NO\n" +
            "    FROM WHS_OPERATE_LOG WHSLOG\n" +
            "             LEFT JOIN WHS_STORAGE WHSSTORAGE ON WHSLOG.UUID_STORAGE = WHSSTORAGE.UUID\n" +
            "    WHERE WHSLOG.UUID_STORAGE IS NOT NULL\n" +
            "      AND WHSLOG.MATERIAL_NO IS NOT NULL\n" +
            "      AND WHSLOG.DOCUMENT_TYPE = #{documentType}\n" +
            "      AND WHSSTORAGE.WERKS = #{werks}\n" +
            "      AND WHSSTORAGE.WHS_LOCATION_CODE = #{whsLocationCode}\n" +
            "      AND WHSLOG.CREATE_TIME BETWEEN TO_DATE(#{startDate}, 'yyyy-MM-dd hh24:mi:ss') AND TO_DATE(#{endDate}, 'yyyy-MM-dd hh24:mi:ss')\n" +
            ")\n" +
            "</script>")
    List<Map<String, Object>> getStorageByDate(@Param("werks") String werks,
                                                @Param("whsLocationCode") String whsLocationCode,
                                                @Param("startDate") String startDate,
                                                @Param("endDate") String endDate,
                                                @Param("documentType") String documentType
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  WSI.CREATE_TIME AS \"createTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID  and  WHS.STORAGE_TYPE!='02' \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "   <when test='packageNote != null and packageNote != \"\"'> AND WSI.BOX_NOTE in (\n" +
            "		SELECT BOX_NOTE FROM SAP_BOX_NOTE_DTL WHERE PACKAGE_NOTE = #{packageNote})\n" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryX(@Param("werks") List<String> werks,
                                                      @Param("whsLocationCode") List<String> whsLocationCode,
                                                      @Param("storageArea") List<String> storageArea,
                                                      @Param("shelfNo") List<String> shelfNo,
                                                      @Param("shelfLayer") List<String> shelfLayer,
                                                      @Param("shelfCell") List<String> shelfCell,
                                                      @Param("storageNo") List<String> storageNo,
                                                      @Param("boxNote") List<String> boxNote,
                                                      @Param("packageNote") String packageNote
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  WSI.CREATE_TIME AS \"createTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryXX(@Param("werks") List<String> werks,
                                                      @Param("whsLocationCode") List<String> whsLocationCode,
                                                      @Param("storageArea") List<String> storageArea,
                                                      @Param("shelfNo") List<String> shelfNo,
                                                      @Param("shelfLayer") List<String> shelfLayer,
                                                      @Param("shelfCell") List<String> shelfCell,
                                                      @Param("storageNo") List<String> storageNo,
                                                      @Param("boxNote") List<String> boxNote
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID and  WHS.STORAGE_TYPE!='02'  \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.MATERIAL_NO IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryM(@Param("werks") List<String> werks,
                                                      @Param("whsLocationCode") List<String> whsLocationCode,
                                                      @Param("storageArea") List<String> storageArea,
                                                      @Param("shelfNo") List<String> shelfNo,
                                                      @Param("shelfLayer") List<String> shelfLayer,
                                                      @Param("shelfCell") List<String> shelfCell,
                                                      @Param("storageNo") List<String> storageNo,
                                                      @Param("materialNo") List<String> materialNo
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID and  WHS.STORAGE_TYPE='02'  \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.MATERIAL_NO IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryOld(@Param("werks") List<String> werks,
                                                      @Param("whsLocationCode") List<String> whsLocationCode,
                                                      @Param("storageArea") List<String> storageArea,
                                                      @Param("shelfNo") List<String> shelfNo,
                                                      @Param("shelfLayer") List<String> shelfLayer,
                                                      @Param("shelfCell") List<String> shelfCell,
                                                      @Param("storageNo") List<String> storageNo,
                                                      @Param("materialNo") List<String> materialNo
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  SBN.BOX_GROSS_WEIGHT AS \"boxGrossWeight\",\n" +
            "  SBN.BOX_VOLUME AS \"boxVolume\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID  and  WHS.STORAGE_TYPE!='02' \n" +
            "       LEFT JOIN SAP_BOX_NOTE SBN ON WSI.BOX_NOTE=SBN.BOX_NOTE \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "   <when test='searchInfo != null and searchInfo != \"\" '> AND (MA.MATERIAL_NO LIKE concat(concat('%',#{searchInfo}),'%') OR MA.MATERIAL_ZH_DESC LIKE concat(concat('%',#{searchInfo}),'%') OR MA.MATERIAL_EN_DESC LIKE concat(concat('%',#{searchInfo}),'%') ) </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventory(@Param("werks") List<String> werks,
                                                     @Param("whsLocationCode") List<String> whsLocationCode,
                                                     @Param("storageArea") List<String> storageArea,
                                                     @Param("shelfNo") List<String> shelfNo,
                                                     @Param("shelfLayer") List<String> shelfLayer,
                                                     @Param("shelfCell") List<String> shelfCell,
                                                     @Param("storageNo") List<String> storageNo,
                                                     @Param("materialNo") List<String> materialNo,
                                                     @Param("boxNote") List<String> boxNote, 
                                                     @Param("searchInfo") String searchInfo
    );

    @Select("SELECT AVAILABLE_QTY FROM WHS_STORAGE_INVENTORY " +
            "WHERE UUID = #{storageUuid} " +
            "   AND BOX_NOTE = #{boxNote} AND AVAILABLE_QTY != 0 ")
    String getAvailableQtyByUuidBoxNote(@Param("storageUuid") String storageUuid, @Param("boxNote") String boxNote);

    @Select("SELECT ROWNUM AS \"rownum\",\n" +
            "       WSI.UUID AS \"uuid\",\n" +
            "       MATERIAL_NO AS \"materialNo\",\n" +
            "       QUANTITY AS \"quantity\",\n" +
            "       AVAILABLE_QTY AS \"availableQty\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "WHERE WSI.UUID = #{storageUuid,jdbcType=VARCHAR} \n" +
            "  AND WSI.MATERIAL_NO = #{materialNo,jdbcType=VARCHAR} \n" +
            "  AND WSI.AVAILABLE_QTY > 0 AND rownum = 1 \n")
    Map<String, Object> getWhsStorageInventoryByUuidMaterialNo(@Param("storageUuid") String storageUuid,
                                                               @Param("materialNo") String materialNo);

    @Select("<script>" +
            "SELECT COUNT(*) FROM WHS_STORAGE_INVENTORY WHS LEFT JOIN WHS_STORAGE WHSTORAGE ON WHS.UUID=WHSTORAGE.UUID \n" +
            "   WHERE WHS.BOX_NOTE= #{boxNote} \n" +
            "      <when test='werks!=null'> AND WHSTORAGE.WERKS = #{werks} </when> \n" +
            "      <when test='whsLocationCode!=null'> AND WHSTORAGE.WHS_LOCATION_CODE = #{whsLocationCode} </when> \n" +
            "</script>")
    int validateBoxNote(@Param("boxNote") String boxNote,@Param("werks") String werks,@Param("whsLocationCode") String whsLocationCode);


    @Select("<script>"+
            "SELECT BOX_NOTE AS \"boxNote\",\n"+
    "CREATE_TIME AS \"createTime\" FROM WHS_STORAGE_INVENTORY WHS WHERE WHS.BOX_NOTE IS NOT NULL \n"+
            "<when test='boxNote!=null'>AND WHS.BOX_NOTE = #{boxNote} </when> \n"+
    "</script>")
    List<Map<String, Object>> pageMaDataMap(Page<Map<String, Object>> pages,
                                            @Param("boxNote") String boxNote,
                                            @Param("werks") List<String> werks,
                                            @Param("whsLocationCode") List<String> whsLocationCode,
                                            @Param("storageArea") List<String> storageArea,
                                            @Param("shelfNo") List<String> shelfNo,
                                            @Param("shelfLayer") List<String> shelfLayer,
                                            @Param("shelfCell") List<String> shelfCell,
                                            @Param("storageNo") List<String> storageNo,
                                            @Param("materialNo") List<String> materialNo
);

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> pageDataMap(Page<Map<String, Object>> pages,
//                                            @Param("boxNote") String boxNote,
                                            @Param("werks") List<String> werks,
                                            @Param("whsLocationCode") List<String> whsLocationCode,
                                            @Param("storageArea") List<String> storageArea,
                                            @Param("shelfNo") List<String> shelfNo,
                                            @Param("shelfLayer") List<String> shelfLayer,
                                            @Param("shelfCell") List<String> shelfCell,
                                            @Param("storageNo") List<String> storageNo,
                                            @Param("materialNo") List<String> materialNo,
                                            @Param("boxNote") List<String> boxNote
    );




    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 \n"+
            "   <when test='werks!=null'> AND WHS.WERKS = #{werks} </when> \n" +
            "   <when test='whsLocationCode!=null'> AND WHS.WHS_LOCATION_CODE = #{whsLocationCode} </when> \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWSInventory(@Param("werks") String werks,
                                                     @Param("whsLocationCode") String whsLocationCode,
                                                     @Param("materialNo") List<String> materialNo,
                                                     @Param("boxNote") List<String> boxNote
    );


    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.MATERIAL_NO IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryMPage(Page<Map<String, Object>> pages, @Param("werks") List<String> werks,
                                                      @Param("whsLocationCode") List<String> whsLocationCode,
                                                      @Param("storageArea") List<String> storageArea,
                                                      @Param("shelfNo") List<String> shelfNo,
                                                      @Param("shelfLayer") List<String> shelfLayer,
                                                      @Param("shelfCell") List<String> shelfCell,
                                                      @Param("storageNo") List<String> storageNo,
                                                      @Param("materialNo") List<String> materialNo
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  WSI.CREATE_TIME AS \"createTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryXPage(Page<Map<String, Object>> pages, @Param("werks") List<String> werks,
                                                      @Param("whsLocationCode") List<String> whsLocationCode,
                                                      @Param("storageArea") List<String> storageArea,
                                                      @Param("shelfNo") List<String> shelfNo,
                                                      @Param("shelfLayer") List<String> shelfLayer,
                                                      @Param("shelfCell") List<String> shelfCell,
                                                      @Param("storageNo") List<String> storageNo,
                                                      @Param("boxNote") List<String> boxNote
    );

    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_AREA AS \"storageArea\",\n" +
            "  WHS.SHELF_NO AS \"shelfNo\",\n" +
            "  WHS.SHELF_LAYER AS \"shelfLayer\",\n" +
            "  WHS.SHELF_CELL AS \"shelfCell\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.UUID IN ( \n" +
            "   SELECT WHS.UUID FROM WHS_STORAGE WHS WHERE 1=1 \n" +
            "            <when test='werks != null'> AND WHS.WERKS in \n" +
            "               <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='whsLocationCode != null'> AND WHS.WHS_LOCATION_CODE in \n" +
            "               <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageArea != null'> AND WHS.STORAGE_AREA in \n" +
            "               <foreach item='item' index='index' collection='storageArea' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfNo != null'> AND WHS.SHELF_NO in \n" +
            "               <foreach item='item' index='index' collection='shelfNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfLayer != null'> AND WHS.SHELF_LAYER in \n" +
            "               <foreach item='item' index='index' collection='shelfLayer' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='shelfCell != null'> AND WHS.SHELF_CELL in \n" +
            "               <foreach item='item' index='index' collection='shelfCell' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "            <when test='storageNo != null'> AND WHS.STORAGE_NO in \n" +
            "               <foreach item='item' index='index' collection='storageNo' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "            </when>\n" +
            "   ) \n" +
            "   <when test='materialNo != null'> AND WSI.MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWhsStorageInventoryPage(Page<Map<String, Object>> pages, @Param("werks") List<String> werks,
                                                     @Param("whsLocationCode") List<String> whsLocationCode,
                                                     @Param("storageArea") List<String> storageArea,
                                                     @Param("shelfNo") List<String> shelfNo,
                                                     @Param("shelfLayer") List<String> shelfLayer,
                                                     @Param("shelfCell") List<String> shelfCell,
                                                     @Param("storageNo") List<String> storageNo,
                                                     @Param("materialNo") List<String> materialNo,
                                                     @Param("boxNote") List<String> boxNote
    );

    @Select("SELECT SUM(QUANTITY) AS total, TO_CHAR(CREATE_TIME,'yyyy-mm-dd') AS incomingDate, TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd')-TO_DATE(TO_CHAR(CREATE_TIME,'yyyy-mm-dd'),'yyyy-mm-dd') as diff FROM WHS_STORAGE_INVENTORY GROUP BY TO_CHAR(CREATE_TIME,'yyyy-mm-dd') ORDER BY TO_CHAR(CREATE_TIME,'yyyy-mm-dd');")
    List<Map<String, Object>> getAge();


    @Select("<script>" +
            "SELECT * \n" +
            "FROM MATERIAL_MARM_SAP  \n" +
            "WHERE 1=1   \n" +
            "   <when test='materialNo != null'> AND MATERIAL_NO in \n" +
            "       <foreach item='item' index='index' collection='materialNo' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "   ORDER BY MATERIAL_NO \n" +
            "</script>")
    List<Map<String, Object>> getMaterialMarmByNo(@Param("materialNo") List<String> materialNo );
    
    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  'M' AS \"goodsType\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  WSI.CREATE_TIME AS \"createTime\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\" \n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.MATERIAL_NO IS NOT NULL \n" +
            " 		<when test='dto.werks!=null and dto.werks != \"\"'> AND WHS.WERKS = #{dto.werks}</when>" +
            " 		<when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND WHS.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " 		<when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WHS.STORAGE_NO = #{dto.storageNo}</when>" +
            " 		<when test='dto.searchInfo!=null and dto.searchInfo != \"\"'> AND (WSI.MATERIAL_NO LIKE concat(concat('%',#{dto.searchInfo}),'%') OR MA.MATERIAL_ZH_DESC LIKE concat(concat('%',#{dto.searchInfo}),'%'))</when>" +
            " 		<when test='dto.packageNote!=null and dto.packageNote != \"\"'> AND WSI.UUID = #{dto.packageNote}</when>" +
            " 		<when test='dto.boxNote!=null and dto.boxNote != \"\"'> AND WSI.BOX_NOTE = #{dto.boxNote}</when>" +
            "</script>")
	List<Map<String, Object>> getWhsStockMPage(Page<Map<String, Object>> pages, @Param("dto")WhsSearchDTO dto);
    
    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  'X' AS \"goodsType\",\n" +
            "  SBN.PACKAGE_NOTE AS \"packageNote\",\n" +
            "  SBN.ITEM AS \"item\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  SBN.MATERIAL_NO AS \"materialNo\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  SBN.DOMESTIC_QTY_RECEIVED AS \"availableQty\",\n" +
            "  SBN.BASIC_UNIT AS \"basicUnit\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  WSI.CREATE_TIME AS \"createTime\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\" \n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            " 		LEFT JOIN SAP_BOX_NOTE_DTL SBN ON WSI.BOX_NOTE = SBN.BOX_NOTE\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON SBN.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL \n" +
            " 		<when test='dto.werks!=null and dto.werks != \"\"'> AND WHS.WERKS = #{dto.werks}</when>" +
            " 		<when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND WHS.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " 		<when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WHS.STORAGE_NO = #{dto.storageNo}</when>" +
            " 		<when test='dto.searchInfo!=null and dto.searchInfo != \"\"'> AND (SBN.MATERIAL_NO LIKE concat(concat('%',#{dto.searchInfo}),'%') OR MA.MATERIAL_ZH_DESC LIKE concat(concat('%',#{dto.searchInfo}),'%'))</when>" +
            " 		<when test='dto.packageNote!=null and dto.packageNote != \"\"'> AND SBN.PACKAGE_NOTE = #{dto.packageNote}</when>" +
            " 		<when test='dto.boxNote!=null and dto.boxNote != \"\"'> AND WSI.BOX_NOTE = #{dto.boxNote}</when>" +
            "   	<when test='dto.werksList != null'> AND WHS.WERKS in \n" +
            "       	<foreach item='item' index='index' collection='dto.werksList' open='(' separator=',' close=')'>\n" +
            "           	#{item} \n" +
            "       	</foreach>" +
            "   	</when>\n" +
            "</script>")
	List<Map<String, Object>> getWhsStockXPage(Page<Map<String, Object>> pages, @Param("dto")WhsSearchDTO dto);
    
    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", DD.* FROM (\n" +
            "  SELECT \n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  'X' AS \"goodsType\",\n" +
            "  SBN.PACKAGE_NOTE AS \"packageNote\",\n" +
            "  SBN.ITEM AS \"item\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  SBN.MATERIAL_NO AS \"materialNo\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  SBN.DOMESTIC_QTY_RECEIVED AS \"availableQty\",\n" +
            "  SBN.BASIC_UNIT AS \"basicUnit\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS \"unitSales\",\n" +
            "  WSI.CREATE_TIME AS \"createTime\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\" \n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            " 		LEFT JOIN SAP_BOX_NOTE_DTL SBN ON WSI.BOX_NOTE = SBN.BOX_NOTE\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON SBN.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "WHERE WSI.QUANTITY>0 AND WSI.BOX_NOTE IS NOT NULL \n" +
            " 		<when test='dto.werks!=null and dto.werks != \"\"'> AND WHS.WERKS = #{dto.werks}</when>" +
            " 		<when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND WHS.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " 		<when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WHS.STORAGE_NO = #{dto.storageNo}</when>" +
            " 		<when test='dto.searchInfo!=null and dto.searchInfo != \"\"'> AND (SBN.MATERIAL_NO LIKE concat(concat('%',#{dto.searchInfo}),'%') OR MA.MATERIAL_ZH_DESC LIKE concat(concat('%',#{dto.searchInfo}),'%'))</when>" +
            " 		<when test='dto.packageNote!=null and dto.packageNote != \"\"'> AND SBN.PACKAGE_NOTE = #{dto.packageNote}</when>" +
            " 		<when test='dto.boxNote!=null and dto.boxNote != \"\"'> AND WSI.BOX_NOTE = #{dto.boxNote}</when>" +
            "UNION ALL \n" +
            "  SELECT \n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  'M' AS \"goodsType\",\n" +
            "  '' AS \"packageNote\",\n" +
            "  null AS \"item\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  MA.BASIC_UNIT AS \"basicUnit\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS \"unitSales\",\n" +
            "  WSI.CREATE_TIME AS \"createTime\",\n" +
            "  WSI.UPDATE_TIME AS \"updateTime\" \n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON WSI.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 AND WSI.MATERIAL_NO IS NOT NULL \n" +
            " 		<when test='dto.werks!=null and dto.werks != \"\"'> AND WHS.WERKS = #{dto.werks}</when>" +
            " 		<when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND WHS.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " 		<when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WHS.STORAGE_NO = #{dto.storageNo}</when>" +
            " 		<when test='dto.searchInfo!=null and dto.searchInfo != \"\"'> AND (WSI.MATERIAL_NO LIKE concat(concat('%',#{dto.searchInfo}),'%') OR MA.MATERIAL_ZH_DESC LIKE concat(concat('%',#{dto.searchInfo}),'%'))</when>" +
            " 		<when test='dto.packageNote!=null and dto.packageNote != \"\"'> AND WSI.UUID = #{dto.packageNote}</when>" +
            " 		<when test='dto.boxNote!=null and dto.boxNote != \"\"'> AND WSI.BOX_NOTE = #{dto.boxNote}</when>" +
            ") DD \n" +
            "</script>")
	List<Map<String, Object>> getAllWhsStockPage(Page<Map<String, Object>> pages, @Param("dto")WhsSearchDTO dto);
    
    @Select("<script>" +
            "SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  WSI.UUID AS \"uuid\",\n" +
            "  WSI.BOX_NOTE AS \"boxNote\",\n" +
            "  WSI.QUANTITY AS \"qty\",\n" +
            "  WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "  WHS.WERKS AS \"werks\",\n" +
            "  WHS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=WSI.UUID \n" +
            "WHERE WSI.QUANTITY>0 \n"+
            "   <when test='boxNote != null'> AND WSI.BOX_NOTE in \n" +
            "       <foreach item='item' index='index' collection='boxNote' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "   </when>\n" +
            "</script>")
    List<Map<String, Object>> getWSInventoryByBox(@Param("boxNote") List<String> boxNote);
}