package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.WerksAllStorageInvSearchDTO;
import com.sunda.spmsoversea.dto.WerksStorageInvSearchDTO;
import com.sunda.spmsoversea.dto.WhsStorageInvSearchDTO;
import com.sunda.spmsoversea.entity.WhsStorageInventory;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

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

/**
 * <p>
 * 储位库存信息表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-09-10
 */
@Mapper
@Repository("overseaWhsStorageInventoryMapper")
public interface WhsStorageInventoryMapper extends BaseMapper<WhsStorageInventory> {

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WSI.UUID AS \"uuid\",\n" +
            "                WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "                WSI.QUANTITY AS \"quantity\",\n" +
            "                WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "                MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "                (CASE WHEN MS.UNIT_SALES IS NULL\n" +
            "                          THEN MS.BASIC_UNIT\n" +
            "                      ELSE MS.UNIT_SALES END) AS \"unitSales\",\n" +
            "                MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "                MS.MOVEMENT_TYPE AS \"movementType\",\n" +
            "                WSI.CREATE_TIME AS \"createTime\",\n" +
            "                WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "                WS.WERKS AS \"werks\",\n" +
            "                WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                WS.STORAGE_NO AS \"storageNo\",\n" +
            "                mbew.VERPR AS \"materialPrice\",\n" +
            "                WS.STORAGE_AREA AS \"storageArea\"\n" +
            "         FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                  LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID and  WS.STORAGE_TYPE!='02' \n" +
            "                  LEFT JOIN MATERIAL_SAP MS ON WSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "                  LEFT JOIN Z_SPMS_GET_MBEW mbew ON WSI.MATERIAL_NO = mbew.MATNR   and mbew.BWKEY='CN01' \n" +
            "         WHERE WS.STORAGE_STATUS = '1' AND WSI.AVAILABLE_QTY > 0\n" +
            " <when test='dto.werks!=null and dto.werks != \"\"'> AND WS.WERKS = #{dto.werks}</when>" +
            " <when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WS.STORAGE_AREA = #{dto.storageArea}</when>" +
            " <when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WS.STORAGE_NO = #{dto.storageNo}</when>" +
            " <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND WS.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " <when test='dto.searchInfos!=null ' > " +
            "       <foreach item='item' index='index' collection='dto.searchInfos' open='' separator='' close=''>\n" +
            "           AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{item} , 1, 1) > 0" +
            "       </foreach>" +
            //"   AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{dto.searchInfo}, 1, 1) > 0" +
            " </when> \n" +
            " <when test='dto.werksList != null'> AND WS.WERKS in \n" +
	        " 	<foreach item='item' index='index' collection='dto.werksList' open='(' separator=',' close=')'>\n" +
	        "      #{item} \n" +
	        "   </foreach>" +
	        "</when>\n" +
            "         ORDER BY WS.STORAGE_AREA, WS.STORAGE_NO, WSI.MATERIAL_NO\n" +
            "         ) RE" +
            "</script>")
    List<Map<String, Object>> getWerksStorageInvList(IPage iPage, @Param("dto")WerksStorageInvSearchDTO dto);


    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WSI.UUID AS \"uuid\",\n" +
            "                WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "                WSI.QUANTITY AS \"quantity\",\n" +
            "                WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "                MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "                (CASE WHEN MS.UNIT_SALES IS NULL\n" +
            "                          THEN MS.BASIC_UNIT\n" +
            "                      ELSE MS.UNIT_SALES END) AS \"unitSales\",\n" +
            "                MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "                MS.MOVEMENT_TYPE AS \"movementType\",\n" +
            "                WSI.CREATE_TIME AS \"createTime\",\n" +
            "                WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "                WS.WERKS AS \"werks\",\n" +
            "                WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                WS.STORAGE_NO AS \"storageNo\",\n" +
            "                mbew.VERPR AS \"materialPrice\",\n" +
            "                WS.STORAGE_AREA AS \"storageArea\"\n" +
            "         FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                  LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID and  WS.STORAGE_TYPE = '02' \n" +
            "                  LEFT JOIN MATERIAL_SAP MS ON WSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "                  LEFT JOIN Z_SPMS_GET_MBEW mbew ON WSI.MATERIAL_NO = mbew.MATNR   and mbew.BWKEY='CN01' \n" +
            "         WHERE WS.STORAGE_STATUS = '1' AND WSI.AVAILABLE_QTY > 0\n" +
            " <when test='dto.werks!=null and dto.werks != \"\"'> AND WS.WERKS = #{dto.werks}</when>" +
            " <when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WS.STORAGE_AREA = #{dto.storageArea}</when>" +
            " <when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WS.STORAGE_NO = #{dto.storageNo}</when>" +
            " <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND WS.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " <when test='dto.searchInfos!=null ' > " +
            "       <foreach item='item' index='index' collection='dto.searchInfos' open='' separator='' close=''>\n" +
            "           AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{item} , 1, 1) > 0" +
            "       </foreach>" +
            //"   AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{dto.searchInfo}, 1, 1) > 0" +
            " </when> \n" +
            " <when test='dto.werksList != null'> AND WS.WERKS in \n" +
	        " 	<foreach item='item' index='index' collection='dto.werksList' open='(' separator=',' close=')'>\n" +
	        "      #{item} \n" +
	        "   </foreach>" +
	        "</when>\n" +
            "         ORDER BY WS.STORAGE_AREA, WS.STORAGE_NO, WSI.MATERIAL_NO\n" +
            "         ) RE" +
            "</script>")
    List<Map<String, Object>> getWerksOldStorageInvList(IPage iPage, @Param("dto")WerksStorageInvSearchDTO dto);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WSI.UUID AS \"uuid\",\n" +
            "                WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "                WSI.QUANTITY AS \"quantity\",\n" +
            "                WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "                MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "                (CASE WHEN MS.UNIT_SALES IS NULL\n" +
            "                          THEN MS.BASIC_UNIT\n" +
            "                      ELSE MS.UNIT_SALES END) AS \"unitSales\",\n" +
            "                (CASE WHEN #{dto.language} = 'en-US' THEN MS.MATERIAL_EN_DESC ELSE MS.MATERIAL_ZH_DESC END) AS \"materialZhDesc\",\n" +
            "                MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "                MS.MOVEMENT_TYPE AS \"movementType\",\n" +
            "                WSI.CREATE_TIME AS \"createTime\",\n" +
            "                WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "                WS.WERKS AS \"werks\",\n" +
            "                WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                WS.STORAGE_NO AS \"storageNo\",\n" +
            "                WS.STORAGE_AREA AS \"storageArea\"\n" +
            "         FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                  LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "                  LEFT JOIN MATERIAL_SAP MS ON WSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "         WHERE WS.STORAGE_STATUS = '1' AND WSI.AVAILABLE_QTY > 0\n" +
            "           AND WS.WERKS = #{dto.werks}\n" +
            " <when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WS.STORAGE_AREA = #{dto.storageArea}</when>" +
            " <when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WS.STORAGE_NO = #{dto.storageNo}</when>" +
            " <when test='dto.searchInfo!=null and dto.searchInfo != \"\"'> " +
            "   AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{dto.searchInfo}, 1, 1) > 0" +
            " </when>" +
            "         ORDER BY WS.STORAGE_AREA, WS.STORAGE_NO, WSI.MATERIAL_NO\n" +
            "         ) RE" +
            "</script>")
    List<Map<String, Object>> getWerksAllStorageInv(@Param("dto") WerksAllStorageInvSearchDTO dto);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RESULT.*\n" +
            "FROM (\n" +
            "     SELECT RE.WERKS AS \"werks\",\n" +
            "            RE.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "            RE.MATERIAL_NO AS \"materialNo\",\n" +
            "            RE.\"totalQuantity\" AS \"totalQuantity\",\n" +
            "            (CASE WHEN SI.SAFETY_QTY_BASIC_UNIT IS NULL\n" +
            "                      THEN 0 ELSE SI.SAFETY_QTY_BASIC_UNIT END) AS \"safetyQtyBasicUnit\",\n" +
            "            MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "            MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "            MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "            MS.MOVEMENT_TYPE AS \"movementType\",\n" +
            "            (CASE WHEN MS.UNIT_SALES IS NULL\n" +
            "                      THEN MS.BASIC_UNIT ELSE MS.UNIT_SALES END) AS \"unitSales\"\n" +
            "     FROM (\n" +
            "              SELECT WS.WERKS,\n" +
            "                     WS.WHS_LOCATION_CODE,\n" +
            "                     WSI.MATERIAL_NO,\n" +
            "                     SUM(WSI.QUANTITY) AS \"totalQuantity\"\n" +
            "              FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                       LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "              WHERE WERKS != 'CN01'\n" +
            "              GROUP BY WS.WERKS, WS.WHS_LOCATION_CODE, WSI.MATERIAL_NO\n" +
            "          ) RE LEFT JOIN MATERIAL_SAP MS ON RE.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "               LEFT JOIN SAFETY_INVENTORY SI ON SI.WERKS = RE.WERKS AND SI.MATERIAL_NO = RE.MATERIAL_NO\n" +
            "     WHERE RE.\"totalQuantity\" > 0\n" +
            "  <when test='dto.werks!=null and dto.werks != \"\"'> AND RE.WERKS = #{dto.werks}</when>" +
            "  <when test='dto.searchInfos!=null ' > " +
            "       <foreach item='item' index='index' collection='dto.searchInfos' open='' separator='' close=''>\n" +
            "           AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{item} , 1, 1) > 0" +
            "       </foreach>" +
            //"    AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{dto.searchInfo}, 1, 1) > 0" +
            "  </when>" +
            "     ORDER BY RE.WERKS, RE.MATERIAL_NO\n" +
            " ) RESULT" +
            "</script>")
    List<Map<String, Object>> getStorageInvList(IPage iPage, @Param("dto") WhsStorageInvSearchDTO dto);

    @Select("SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WSI.UUID,\n" +
            "                WSI.MATERIAL_NO,\n" +
            "                WSI.QUANTITY,\n" +
            "                WSI.AVAILABLE_QTY,\n" +
            "                MS.BASIC_UNIT,\n" +
            "                MS.UNIT_SALES,\n" +
            "                MS.MATERIAL_ZH_DESC,\n" +
            "                MS.MATERIAL_EN_DESC,\n" +
            "                WSI.CREATE_TIME,\n" +
            "                WSI.UPDATE_TIME,\n" +
            "                WS.WERKS,\n" +
            "                WS.WHS_LOCATION_CODE,\n" +
            "                WS.STORAGE_NO,\n" +
            "                WS.STORAGE_AREA\n" +
            "         FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                  LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "                  LEFT JOIN MATERIAL_SAP MS ON WSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "         WHERE WSI.QUANTITY > 0 AND WS.WERKS = #{werks}\n" +
            "           AND WSI.MATERIAL_NO = #{materialNo}\n" +
            "         ORDER BY WS.STORAGE_AREA, WS.STORAGE_NO\n" +
            "         ) RE")
    List<Map<String, Object>> getWhsStorageInventory(@Param("werks") String werks, @Param("materialNo") String materialNo);


    @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 ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WSI.UUID AS \"uuid\",\n" +
            "                WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "                WSI.QUANTITY AS \"quantity\",\n" +
            "                WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "                MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "                (CASE WHEN MS.UNIT_SALES IS NULL\n" +
            "                          THEN MS.BASIC_UNIT\n" +
            "                      ELSE MS.UNIT_SALES END) AS \"unitSales\",\n" +
            "                MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "                MS.MOVEMENT_TYPE AS \"movementType\",\n" +
            "                WSI.CREATE_TIME AS \"createTime\",\n" +
            "                WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "                WS.WERKS AS \"werks\",\n" +
            "                WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                WS.STORAGE_NO AS \"storageNo\",\n" +
            "                WS.STORAGE_AREA AS \"storageArea\"\n" +
            "         FROM (SELECT WHSS.WERKS AS \"WERKS\",\n" +
            "					WHSSI.MATERIAL_NO AS \"MATERIAL_NO\",\n" +
            "					(SELECT SSI.UUID \n" +
            "					FROM WHS_STORAGE_INVENTORY SSI \n" +
            "					LEFT JOIN WHS_STORAGE WST ON WST.UUID = SSI.UUID \n" +
            "					WHERE SSI.MATERIAL_NO = WHSSI.MATERIAL_NO \n" +
            "					AND WHSS.WERKS = WST.WERKS \n" +
            " 					<when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WST.STORAGE_AREA = #{dto.storageArea}</when>" +
            " 					<when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WST.STORAGE_NO = #{dto.storageNo}</when>" +
            "					AND SSI.AVAILABLE_QTY = 0 AND <![CDATA[ ROWNUM <= 1 ]]>) AS \"UUID\"\n" +
            "				FROM WHS_STORAGE_INVENTORY WHSSI\n" +
            "				LEFT JOIN WHS_STORAGE WHSS ON WHSSI.UUID = WHSS.UUID\n" +
            "				LEFT JOIN MATERIAL_SAP MSA ON WHSSI.MATERIAL_NO = MSA.MATERIAL_NO\n" +
            "				WHERE WHSS.STORAGE_STATUS = '1' AND WHSSI.AVAILABLE_QTY = 0\n" +
            "				AND WHSS.WERKS = #{dto.werks}\n" +
            " 				<when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WHSS.STORAGE_AREA = #{dto.storageArea}</when>" +
            " 				<when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WHSS.STORAGE_NO = #{dto.storageNo}</when>" +
            " 				<when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND WHSS.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " 				<when test='dto.searchInfos!=null ' > " +
            "       			<foreach item='item' index='index' collection='dto.searchInfos' open='' separator='' close=''>\n" +
            "           			AND INSTR(MSA.MATERIAL_NO || ',' || MSA.MATERIAL_ZH_DESC || ',' || MSA.MATERIAL_EN_DESC, #{item} , 1, 1) > 0" +
            "       			</foreach>" +
            " 				</when>\n" +
            "				GROUP BY WHSS.WERKS,WHSSI.MATERIAL_NO\n" +
            "				) WWWSI\n" +
            "               LEFT JOIN WHS_STORAGE_INVENTORY WSI ON WSI.UUID = WWWSI.UUID AND WSI.MATERIAL_NO = WWWSI.MATERIAL_NO\n" +
            "               LEFT JOIN WHS_STORAGE WS ON WWWSI.UUID = WS.UUID\n" +
            "               LEFT JOIN MATERIAL_SAP MS ON WWWSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "         ORDER BY WS.STORAGE_AREA, WS.STORAGE_NO, WSI.MATERIAL_NO\n" +
            "         ) RE" +
            "</script>")
    List<Map<String, Object>> getWerksStorageZeroInvList(IPage iPage, @Param("dto")WerksStorageInvSearchDTO dto);
    
    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WSI.UUID AS \"uuid\",\n" +
            "                WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "                WSI.QUANTITY AS \"quantity\",\n" +
            "                WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "                MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "                (CASE WHEN MS.UNIT_SALES IS NULL\n" +
            "                          THEN MS.BASIC_UNIT\n" +
            "                      ELSE MS.UNIT_SALES END) AS \"unitSales\",\n" +
            "                MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "                MS.MOVEMENT_TYPE AS \"movementType\",\n" +
            "                WSI.CREATE_TIME AS \"createTime\",\n" +
            "                WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "                WS.WERKS AS \"werks\",\n" +
            "                WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                WS.STORAGE_NO AS \"storageNo\",\n" +
            "                WS.STORAGE_AREA AS \"storageArea\"\n" +
            "         FROM (SELECT WHSS.WERKS AS \"WERKS\",\n" +
            "					WHSSI.MATERIAL_NO AS \"MATERIAL_NO\",\n" +
            "					(SELECT SSI.UUID \n" +
            "					FROM WHS_STORAGE_INVENTORY SSI \n" +
            "					LEFT JOIN WHS_STORAGE WST ON WST.UUID = SSI.UUID \n" +
            "					WHERE SSI.MATERIAL_NO = WHSSI.MATERIAL_NO \n" +
            "					AND WHSS.WERKS = WST.WERKS \n" +
            " 					<when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WST.STORAGE_AREA = #{dto.storageArea}</when>" +
            " 					<when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WST.STORAGE_NO = #{dto.storageNo}</when>" +
            "					AND SSI.AVAILABLE_QTY = 0 AND <![CDATA[ ROWNUM <= 1 ]]>) AS \"UUID\"\n" +
            "				FROM WHS_STORAGE_INVENTORY WHSSI\n" +
            "				LEFT JOIN WHS_STORAGE WHSS ON WHSSI.UUID = WHSS.UUID\n" +
            "				LEFT JOIN MATERIAL_SAP MSA ON WHSSI.MATERIAL_NO = MSA.MATERIAL_NO\n" +
            "				WHERE WHSS.STORAGE_STATUS = '1' AND WHSSI.AVAILABLE_QTY = 0\n" +
            "				AND WHSS.WERKS = #{dto.werks}\n" +
            " 				<when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WHSS.STORAGE_AREA = #{dto.storageArea}</when>" +
            " 				<when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WHSS.STORAGE_NO = #{dto.storageNo}</when>" +
            " 				<when test='dto.searchInfo !=null and dto.searchInfo != \"\"' > " +
            "           		AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{dto.searchInfo}, 1, 1) > 0" +
            " 				</when>\n" +
            "				GROUP BY WHSS.WERKS,WHSSI.MATERIAL_NO\n" +
            "				) WWWSI\n" +
            "               LEFT JOIN WHS_STORAGE_INVENTORY WSI ON WSI.UUID = WWWSI.UUID AND WSI.MATERIAL_NO = WWWSI.MATERIAL_NO\n" +
            "               LEFT JOIN WHS_STORAGE WS ON WWWSI.UUID = WS.UUID\n" +
            "               LEFT JOIN MATERIAL_SAP MS ON WWWSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "         ORDER BY WS.STORAGE_AREA, WS.STORAGE_NO, WSI.MATERIAL_NO\n" +
            "         ) RE" +
            "</script>")
    List<Map<String, Object>> getWerksAllZeroStorageInv(@Param("dto") WerksAllStorageInvSearchDTO dto);
    
    @Select("<script>" +
    		"SELECT \n" +
    		"	MATERIAL_NO AS \"materialNo\" ,\n" +
    		"	MATERIAL_ZH_DESC AS \"materialZhDesc\" ,\n" +
    		"	MATERIAL_EN_DESC AS \"materialEnDesc\" \n" +
    		"FROM MATERIAL_SAP \n" +
    		"WHERE MATERIAL_NO = #{materialNo} \n" +
    		"</script>")
	Map<String, Object> selectMaterialSapByMaterialNumber(@Param("materialNo") String materialNo);
    
    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WSI.UUID AS \"uuid\",\n" +
            "                WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "                WSI.QUANTITY AS \"quantity\",\n" +
            "                WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "                MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "                MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "                MS.MOVEMENT_TYPE AS \"movementType\",\n" +
            "                WSI.CREATE_TIME AS \"createTime\",\n" +
            "                WSI.UPDATE_TIME AS \"updateTime\",\n" +
            "                WS.WERKS AS \"werks\",\n" +
            "                WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                WS.STORAGE_NO AS \"storageNo\",\n" +
            "                WS.STORAGE_AREA AS \"storageArea\"\n" +
            "         FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                  LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "                  LEFT JOIN MATERIAL_SAP MS ON WSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "         WHERE WS.STORAGE_STATUS = '1' AND WSI.AVAILABLE_QTY > 0\n" +
            "           AND WS.WERKS = #{dto.werks}\n" +
            " <when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WS.STORAGE_AREA = #{dto.storageArea}</when>" +
            " <when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WS.STORAGE_NO = #{dto.storageNo}</when>" +
            " <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND WS.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " <when test='dto.searchInfos!=null ' > " +
            "       <foreach item='item' index='index' collection='dto.searchInfos' open='' separator='' close=''>\n" +
            "           AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{item} , 1, 1) > 0" +
            "       </foreach>" +
            " </when>" +
            "         ORDER BY WS.STORAGE_AREA, WS.STORAGE_NO, WSI.MATERIAL_NO\n" +
            "         ) RE" +
            "</script>")
	List<Map<String, Object>> getWerksStorageInvListByParam(@Param("dto") WerksStorageInvSearchDTO dto);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WSI.UUID AS \"storageId\",\n" +
            "                WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "                WSI.AVAILABLE_QTY AS \"availableQty\",\n" +
            "                (CASE WHEN MS.BASIC_UNIT IS NULL\n" +
            "                          THEN MS.UNIT_SALES\n" +
            "                      ELSE MS.BASIC_UNIT END) AS \"unit\",\n" +
            "                WS.WERKS AS \"werks\",\n" +
            "                WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                WS.STORAGE_NO AS \"storageNo\",\n" +
            "				 WS.STORAGE_TYPE AS \"storageType\",\n" +
            "				 ZSM.VERPR AS \"unitCost\",\n" +
            "				 ZSM.WAERS AS \"currency\"\n" +
            "         FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                  LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "                  LEFT JOIN MATERIAL_SAP MS ON WSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "				   LEFT JOIN Z_SPMS_GET_MBEW ZSM ON ZSM.MATNR = WSI.MATERIAL_NO AND ZSM.BWKEY = WS.WERKS \n" +
            "         WHERE WS.STORAGE_STATUS = '1' AND WSI.MATERIAL_NO IS NOT NULL \n" +
            "           AND WS.WERKS = #{dto.werks}\n" +
            " <when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WS.STORAGE_AREA = #{dto.storageArea}</when>" +
            " <when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WS.STORAGE_NO = #{dto.storageNo}</when>" +
            " <when test='dto.searchInfo!=null and dto.searchInfo != \"\"'> " +
            "   AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{dto.searchInfo}, 1, 1) > 0" +
            " </when>" +
            "         ORDER BY WS.STORAGE_AREA, WS.STORAGE_NO, WSI.MATERIAL_NO\n" +
            "         ) RE" +
            "</script>")
	List<Map<String, Object>> getWerksAllStorageInvM(@Param("dto") WerksAllStorageInvSearchDTO dto);
}