package com.sunda.spmsorder.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmsorder.entity.MaterialSap;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

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

/**
 * <p>
 * 物料表SAP Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2020-12-09
 */
@Component
@Mapper
public interface MaterialSapMapper extends BaseMapper<MaterialSap> {

    @Select("<script>" +
            "SELECT\n" +
            "  sap.MATERIAL_NO AS \"materialNo\",\n" +
            "  sap.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  sap.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  sap.PRODUCT_LEVEL AS \"productLevel\",\n" +
            "  sap.PRODUCT_LEVEL_DESC AS \"productLevelDesc\",\n" +
            "  sap.MATERIAL_NAME AS \"materialName\",\n" +
            "  sap.PRODUCT_LEVEL_LARGE AS \"productLevelLarge\",\n" +
            "  sap.PRODUCT_LEVEL_LARGE_DESC AS \"productLevelLargeDesc\",\n" +
            "  sap.PRODUCT_LEVEL_MID AS \"productLevelMid\",\n" +
            "  sap.PRODUCT_LEVEL_MID_DESC AS \"productLevelMidDesc\",\n" +
            "  sap.PRODUCT_LEVEL_SMALL AS \"productLevelSmall\",\n" +
            "  sap.PRODUCT_LEVEL_SMALL_DESC AS \"productLevelSmallDesc\",\n" +
            "  sap.BRAND AS \"brand\",\n" +
            "  sap.BASIC_UNIT AS \"basicUnit\",\n" +
            "  sap.PURCHASE_UNIT AS \"purchaseUnit\",\n" +
            "  sap.NET_WEIGHT AS \"netWeight\",\n" +
            "  sap.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "  sap.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "  sap.VOLUME_QTY AS \"volumeQty\",\n" +
            "  sap.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "  sap.LIGHT_HEAVY_MARK AS \"lightHeavyMark\",\n" +
            "  sap.MATERIAL_STATUS AS \"materialStatus\",\n" +
            "  sap.MATERIAL_STANDARD AS \"materialStandard\"," +
            "  sap.SPECS AS \"specs\",\n" +
            "  sap.MATERIAL_MODEL AS \"materialModel\",\n" +
            "  sap.SUPPLY_DESC AS \"supplyDesc\",\n" +
            "  sap.SUPPLIER_MATERIAL_NO AS \"supplierMaterialNo\",\n" +
            "  sap.ZMOQ AS \"zmoq\",\n" +
            "  sap.ZWERKS_LIMIT AS \"zwerksLimit\",\n" +
            "  sap.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
            "  MPR.PRICE AS \"price\",\n" +
            "  MPR.CURRENCY AS \"currency\",\n" +
            "  spms.CATEGORY_ID AS \"categoryId\",\n" +
            "  mcl.RPTSTART_DATE AS \"prtStartDate\",\n" +
            "  mcl.RPTEND_DATE AS \"prtEndDate\",\n" +
            "  mbew.VERPR AS \"materialPrice\",\n" +
            "  sap.ATTACH_FILE AS \"photos\" " +
            "FROM MATERIAL_CATEGORY c\n" +
            "    LEFT JOIN (select MATERIAL_NO,max(CATEGORY_ID) as CATEGORY_ID from MATERIAL_SPMS GROUP BY MATERIAL_NO) spms ON c.CATEGORY_ID = spms.CATEGORY_ID\n" +
            "  LEFT JOIN MATERIAL_SAP sap ON spms.MATERIAL_NO = sap.MATERIAL_NO\n" +
            "  LEFT JOIN MATERIAL_PRICE MPR ON spms.MATERIAL_NO = MPR.MATERIAL_NO\n" +
            "  LEFT JOIN MATERIAL_CATEGORY_LABOR mcl ON substr(c.CATEGORY_ID,0,2) = mcl.CATEGORY_ID \n" +
            "  LEFT JOIN MATERIAL_PHOTOS mp ON spms.MATERIAL_NO = mp.MATERIAL_NO\n" +
            "  LEFT JOIN Z_SPMS_GET_MBEW mbew ON spms.MATERIAL_NO = mbew.MATNR   and mbew.BWKEY='CN01' \n" +
            "WHERE sap.MATERIAL_NO IS NOT NULL \n" +
            "  AND  sap.ON_MALL = 'X' AND NVL(sap.IS_DELETE, ' ') != 'X' AND sap.MATERIAL_STATUS NOT IN ( 'Z1', 'Z3', 'Z5' ) \n" +
            "<when test='categoryId!=null'> AND c.CATEGORY_ID = #{categoryId}</when> \n" +
            "<when test='purchaseLocation!=null'> AND sap.PURCHASE_LOCATION = #{purchaseLocation}</when> \n" +
            "<when test='materialNoList!=null'> AND sap.MATERIAL_NO IN \n" +
            "   <foreach item='item' index='index' collection='materialNoList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "   </foreach>" +
            "</when> \n" +
//            "<when test='materialZhDesc!=null'> AND sap.MATERIAL_ZH_DESC LIKE '%'||#{materialZhDesc}||'%' </when> \n" +
//            "<when test='materialEnDesc!=null'> AND sap.MATERIAL_EN_DESC LIKE '%'||#{materialEnDesc}||'%' </when> \n" +
            " <when test='searchInfos!=null ' > " +
            "       <foreach item='item' index='index' collection='searchInfos' open='' separator='' close=''>\n" +
            "           AND INSTR(sap.MATERIAL_ZH_DESC || ',' || sap.MATERIAL_EN_DESC, #{item} , 1, 1) > 0" +
            "       </foreach>" +
            " </when>" +
            "<when test='productLevelDesc!=null'> AND UPPER(sap.PRODUCT_LEVEL_DESC) LIKE '%'||#{productLevelDesc}||'%' </when> \n" +
            "<when test='specs!=null'> AND UPPER(sap.SPECS) LIKE '%'||#{specs}||'%' </when> \n" +
            "<when test='brand!=null'> AND UPPER(sap.BRAND) LIKE '%'||#{brand}||'%' </when> \n" +
            " ORDER BY sap.MATERIAL_NO ASC" +
            "</script>\n")
    List<Map<String, Object>> searchMaterialSap(@Param("categoryId") String categoryId,
                                                @Param("materialNoList") List<String> materialNoList,
                                                @Param("materialZhDesc")String materialZhDesc,
                                                @Param("materialEnDesc")String materialEnDesc,
                                                @Param("productLevelDesc") String productLevelDesc,
                                                @Param("specs")String specs,
                                                @Param("brand")String brand,
                                                @Param("searchInfos") List<String> searchInfos,
                                                @Param("purchaseLocation")String purchaseLocation);

    @Select("<script>" +
            "SELECT\n" +
            "  sap.MATERIAL_NO AS \"materialNo\",\n" +
            "  sap.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  sap.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  sap.PRODUCT_LEVEL AS \"productLevel\",\n" +
            "  sap.PRODUCT_LEVEL_DESC AS \"productLevelDesc\",\n" +
            "  sap.MATERIAL_NAME AS \"materialName\",\n" +
            "  sap.PRODUCT_LEVEL_LARGE AS \"productLevelLarge\",\n" +
            "  sap.PRODUCT_LEVEL_LARGE_DESC AS \"productLevelLargeDesc\",\n" +
            "  sap.PRODUCT_LEVEL_MID AS \"productLevelMid\",\n" +
            "  sap.PRODUCT_LEVEL_MID_DESC AS \"productLevelMidDesc\",\n" +
            "  sap.PRODUCT_LEVEL_SMALL AS \"productLevelSmall\",\n" +
            "  sap.PRODUCT_LEVEL_SMALL_DESC AS \"productLevelSmallDesc\",\n" +
            "  sap.BRAND AS \"brand\",\n" +
            "  sap.BASIC_UNIT AS \"basicUnit\",\n" +
            "  sap.PURCHASE_UNIT AS \"purchaseUnit\",\n" +
            "  sap.NET_WEIGHT AS \"netWeight\",\n" +
            "  sap.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "  sap.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "  sap.VOLUME_QTY AS \"volumeQty\",\n" +
            "  sap.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "  sap.LIGHT_HEAVY_MARK AS \"lightHeavyMark\",\n" +
            "  sap.MATERIAL_STATUS AS \"materialStatus\",\n" +
            "  sap.MATERIAL_STANDARD AS \"materialStandard\"," +
            "  sap.SPECS AS \"specs\",\n" +
            "  sap.MATERIAL_MODEL AS \"materialModel\",\n" +
            "  sap.SUPPLY_DESC AS \"supplyDesc\",\n" +
            "  sap.SUPPLIER_MATERIAL_NO AS \"supplierMaterialNo\",\n" +
            "  sap.ZMOQ AS \"zmoq\",\n" +
            "  sap.ZWERKS_LIMIT AS \"zwerksLimit\",\n" +
            "  sap.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
            "  MPR.PRICE AS \"price\",\n" +
            "  MPR.CURRENCY AS \"currency\",\n" +
            "  spms.CATEGORY_ID AS \"categoryId\",\n" +
            "  mcl.RPTSTART_DATE AS \"prtStartDate\",\n" +
            "  mcl.RPTEND_DATE AS \"prtEndDate\",\n" +
            "  mbew.VERPR AS \"materialPrice\",\n" +
            "  sap.ATTACH_FILE AS \"photos\" " +
            "FROM MATERIAL_SAP sap\n" +
            "    LEFT JOIN (select MATERIAL_NO,max(CATEGORY_ID) as CATEGORY_ID from MATERIAL_SPMS GROUP BY MATERIAL_NO) spms ON sap.MATERIAL_NO = spms.MATERIAL_NO\n" +
            "  LEFT JOIN MATERIAL_CATEGORY c ON c.CATEGORY_ID = spms.CATEGORY_ID\n" +
            "  LEFT JOIN MATERIAL_PRICE MPR ON spms.MATERIAL_NO = MPR.MATERIAL_NO\n" +
            "  LEFT JOIN MATERIAL_CATEGORY_LABOR mcl ON substr(c.CATEGORY_ID,0,2) = mcl.CATEGORY_ID \n" +
            "  LEFT JOIN MATERIAL_PHOTOS mp ON spms.MATERIAL_NO = mp.MATERIAL_NO\n" +
            "  LEFT JOIN Z_SPMS_GET_MBEW mbew ON spms.MATERIAL_NO = mbew.MATNR   and mbew.BWKEY='CN01' \n" +
            "WHERE sap.MATERIAL_NO IS NOT NULL \n" +
            "  AND  sap.ON_MALL = 'X' AND NVL(SAP.IS_DELETE, ' ') != 'X' AND SAP.MATERIAL_STATUS NOT IN ( 'Z1', 'Z3', 'Z5' ) \n" +
            "<when test='materialNo!=null'> AND sap.MATERIAL_NO = #{materialNo}</when> \n" +
            " ORDER BY sap.MATERIAL_NO ASC" +
            "</script>\n")
    List<Map<String, Object>> searchMaterialSapByNo(@Param("materialNo")String materialNo);


    @Select("<script>" +
            "SELECT DISTINCT  \n" +
            "  sap.MATERIAL_NO AS \"materialNo\",\n" +
            "  sap.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  sap.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  sap.PRODUCT_LEVEL AS \"productLevel\",\n" +
            "  sap.PRODUCT_LEVEL_DESC AS \"productLevelDesc\",\n" +
            "  sap.MATERIAL_NAME AS \"materialName\",\n" +
            "  sap.PRODUCT_LEVEL_LARGE AS \"productLevelLarge\",\n" +
            "  sap.PRODUCT_LEVEL_LARGE_DESC AS \"productLevelLargeDesc\",\n" +
            "  sap.PRODUCT_LEVEL_MID AS \"productLevelMid\",\n" +
            "  sap.PRODUCT_LEVEL_MID_DESC AS \"productLevelMidDesc\",\n" +
            "  sap.PRODUCT_LEVEL_SMALL AS \"productLevelSmall\",\n" +
            "  sap.PRODUCT_LEVEL_SMALL_DESC AS \"productLevelSmallDesc\",\n" +
            "  sap.BRAND AS \"brand\",\n" +
            "  sap.BASIC_UNIT AS \"basicUnit\",\n" +
            "  sap.PURCHASE_UNIT AS \"purchaseUnit\",\n" +
            "  sap.NET_WEIGHT AS \"netWeight\",\n" +
            "  sap.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "  sap.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "  sap.VOLUME_QTY AS \"volumeQty\",\n" +
            "  sap.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "  sap.LIGHT_HEAVY_MARK AS \"lightHeavyMark\",\n" +
            "  sap.MATERIAL_STATUS AS \"materialStatus\",\n" +
            "  sap.MATERIAL_STANDARD AS \"materialStandard\"," +
            "  sap.SPECS AS \"specs\",\n" +
            "  sap.MATERIAL_MODEL AS \"materialModel\",\n" +
            "  sap.SUPPLY_DESC AS \"supplyDesc\",\n" +
            "  sap.SUPPLIER_MATERIAL_NO AS \"supplierMaterialNo\",\n" +
            "  sap.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
            "  mbew.VERPR AS \"materialPrice\",\n" +
            "  sap.ATTACH_FILE AS \"photos\" " +
            "FROM MATERIAL_CATEGORY c\n" +
            "  LEFT JOIN MATERIAL_SPMS spms ON c.CATEGORY_ID = spms.CATEGORY_ID\n" +
            "  LEFT JOIN MATERIAL_SAP sap ON spms.MATERIAL_NO = sap.MATERIAL_NO\n" +
            "  LEFT JOIN MATERIAL_PHOTOS mp ON spms.MATERIAL_NO = mp.MATERIAL_NO\n" +
            "  LEFT JOIN Z_SPMS_GET_MBEW mbew ON spms.MATERIAL_NO = mbew.MATNR  and mbew.BWKEY='CN01' \n" +
            "WHERE sap.MATERIAL_NO IS NOT NULL \n" +
            "  AND sap.ON_MALL = 'X' AND NVL(SAP.IS_DELETE, ' ') != 'X' AND SAP.MATERIAL_STATUS NOT IN ( 'Z1', 'Z3', 'Z5' ) AND sap.MATERIAL_STANDARD = '企标' \n" +
            "<when test='keyword!=null'> AND " +
            "( " +
            " sap.MATERIAL_NO LIKE '%'||#{keyword}||'%' \n" +
            " OR sap.MATERIAL_ZH_DESC LIKE '%'||#{keyword}||'%' \n" +
            " OR sap.MATERIAL_EN_DESC LIKE '%'||#{keyword}||'%' \n" +
            " OR sap.PRODUCT_LEVEL_DESC LIKE '%'||#{keyword}||'%'\n" +
            " OR sap.SPECS LIKE '%'||#{keyword}||'%' \n" +
            " OR sap.BRAND LIKE '%'||#{keyword}||'%' \n" +
            ")" +
            "</when> \n" +
            " <when test='searchInfos!=null ' > " +
            "       <foreach item='item' index='index' collection='searchInfos' open='' separator='' close=''>\n" +
            "           AND INSTR(sap.MATERIAL_ZH_DESC || ',' || sap.MATERIAL_EN_DESC, #{item} , 1, 1) > 0" +
            "       </foreach>" +
            " </when>" +
            " ORDER BY sap.MATERIAL_NO ASC" +
            "</script>\n")
    List<Map<String, Object>> searchMaterialSapGlobal(@Param("keyword") String keyword,@Param("searchInfos") List<String> searchInfos);


    @Select("<script>" +
            "SELECT sap.MATERIAL_NO AS \"materialNo\",\n" +
            "  sap.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  sap.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  sap.MATERIAL_NAME AS \"materialName\",\n" +
            "  whs.WERKS AS \"werks\",\n" +
            "  whs.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  whs.QUANTITY AS \"inventoryQty\",\n" +
            "  whs.SAFETY_QUANTITY AS \"safetyQty\",\n" +
            "  sap.PRODUCT_LEVEL AS \"productLevel\",\n" +
            "  sap.PRODUCT_LEVEL_LARGE AS \"productLevelLarge\",\n" +
            "  sap.PRODUCT_LEVEL_LARGE_DESC AS \"productLevelLargeDesc\",\n" +
            "  sap.PRODUCT_LEVEL_MID AS \"productLevelMid\",\n" +
            "  sap.PRODUCT_LEVEL_MID_DESC AS \"productLevelMidDesc\",\n" +
            "  sap.PRODUCT_LEVEL_SMALL AS \"productLevelSmall\",\n" +
            "  sap.PRODUCT_LEVEL_SMALL_DESC AS \"productLevelSmallDesc\",\n" +
            "  sap.BRAND AS \"brand\",\n" +
            "  sap.BASIC_UNIT AS \"basicUnit\",\n" +
            "  sap.PURCHASE_UNIT AS \"purchaseUnit\",\n" +
            "  sap.NET_WEIGHT AS \"netWeight\",\n" +
            "  sap.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "  sap.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "  sap.VOLUME_QTY AS \"volumeQty\",\n" +
            "  sap.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "  sap.LIGHT_HEAVY_MARK AS \"lightHeavyMark\",\n" +
            "  sap.MATERIAL_STATUS AS \"materialStatus\",\n" +
            "  sap.SPECS AS \"specs\",\n" +
            "  sap.ZWERKS_LIMIT AS \"zwerksLimit\",\n" +
            "  sap.MATERIAL_MODEL AS \"materialModel\",\n" +
            "  sap.SUPPLY_DESC AS \"supplyDesc\",\n" +
            "  sap.SUPPLIER_MATERIAL_NO AS \"supplierMaterialNo\",\n" +
            "       sap.PRODUCT_LEVEL_DESC AS \"productLevelDesc\",\n" +
            "  sap.ATTACH_FILE AS \"photos\"\n" +
            "FROM MATERIAL_SAP sap\n" +
            "  LEFT JOIN (\n" +
            "              SELECT wi.WERKS,\n" +
            "                wi.WHS_LOCATION_CODE,\n" +
            "                wi.QUANTITY,\n" +
            "                wi.SAFETY_QUANTITY,\n" +
            "                wi.MATERIAL_NO\n" +
            "              FROM WHS_INVENTORY_INFO wi\n" +
            "              WHERE wi.WERKS = 'GH01') whs\n" +
            "    ON sap.MATERIAL_NO = whs.MATERIAL_NO\n" +
            "  LEFT JOIN MATERIAL_SPMS spms ON sap.MATERIAL_NO = spms.MATERIAL_NO\n" +
            "  LEFT JOIN MATERIAL_PHOTOS mp ON sap.MATERIAL_NO = mp.MATERIAL_NO\n " +
            "WHERE 1=1 \n" +
            "<when test='categoryId!=null'> AND spms.CATEGORY_ID = #{categoryId}</when>" +
            "<when test='materialNoList!=null'> AND spms.MATERIAL_NO IN \n" +
            "   <foreach item='item' index='index' collection='materialNoList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "   </foreach>" +
            "</when> \n" +
            "<when test='whsList!=null'> AND whs.WHS_LOCATION_CODE IN \n" +
            "   <foreach item='item' index='index' collection='whsList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "   </foreach>" +
            "</when> \n" +
            "<when test='materialZhDesc!=null'> AND sap.MATERIAL_ZH_DESC LIKE '%'||#{materialZhDesc}||'%' </when> \n" +
            "<when test='materialEnDesc!=null'> AND sap.MATERIAL_EN_DESC LIKE '%'||#{materialEnDesc}||'%' </when> \n" +
            " ORDER BY sap.MATERIAL_NO ASC" +
            "</script>")
    List<Map<String, Object>> getMaterialSapInventory(@Param("werks") String werks,
                                                @Param("whsList") List<String> whsList,
                                                @Param("categoryId") String categoryId,
                                                @Param("materialNoList")List<String> materialNoList,
                                                @Param("materialZhDesc")String materialZhDesc,
                                                @Param("materialEnDesc")String materialEnDesc);

    @Select("SELECT whs.WERKS AS \"werks\",\n" +
            "  whs.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "  whs.MATERIAL_NO AS \"materialNo\",\n" +
            "  whs.QUANTITY AS \"inventoryQty\",\n" +
            "  whs.SAFETY_QUANTITY AS \"safetyQty\"\n" +
            "FROM WHS_INVENTORY_INFO whs WHERE whs.WERKS = #{werks} AND whs.MATERIAL_NO = #{materialNo}")
    List<Map<String, Object>> getMaterialInventory(@Param("werks") String werks, @Param("materialNo") String materialNo);

    /** 查询所有没有目录归属的物料信息 */
    @Select("SELECT SAP.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       SAP.MATERIAL_NAME AS \"materialName\",\n" +
            "       SAP.BRAND AS \"brand\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "       SAP.PURCHASE_UNIT AS \"purchaseUnit\",\n" +
            "       SAP.NET_WEIGHT AS \"netWeight\",\n" +
            "       SAP.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "       SAP.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "       SAP.VOLUME_QTY AS \"volumeQty\",\n" +
            "       SAP.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "       SAP.LIGHT_HEAVY_MARK AS \"lightHeavyMark\",\n" +
            "       SAP.SPECS AS \"specs\",\n" +
            "       SAP.MATERIAL_MODEL AS \"materialModel\",\n" +
            "       SAP.SUPPLY_DESC AS \"supplyDesc\",\n" +
            "       SAP.SPMS_MARK AS \"spmsMark\"," +
            "       SAP.PRODUCT_LEVEL AS \"productLevel\",\n" +
            "       SAP.PRODUCT_LEVEL_DESC AS \"productLevelDesc\",\n" +
            "  SAP.PRODUCT_LEVEL_LARGE AS \"productLevelLarge\",\n" +
            "  SAP.PRODUCT_LEVEL_LARGE_DESC AS \"productLevelLargeDesc\",\n" +
            "  SAP.PRODUCT_LEVEL_MID AS \"productLevelMid\",\n" +
            "  SAP.PRODUCT_LEVEL_MID_DESC AS \"productLevelMidDesc\",\n" +
            "  SAP.PRODUCT_LEVEL_SMALL AS \"productLevelSmall\",\n" +
            "  SAP.PRODUCT_LEVEL_SMALL_DESC AS \"productLevelSmallDesc\",\n" +
            "  SAP.SUPPLIER_MATERIAL_NO AS \"supplierMaterialNo\",\n" +
            "  SAP.MATERIAL_STANDARD AS \"materialStandard\",\n" +
            "  SAP.MATERIAL_STATUS AS \"materialStatus\",\n" +
            "  SAP.ON_MALL AS \"onMall\",\n" +
            "       sap.ATTACH_FILE AS \"photos\"\n" +
            "FROM MATERIAL_SAP SAP\n" +
            "  LEFT JOIN MATERIAL_SPMS SPMS ON SAP.MATERIAL_NO = SPMS.MATERIAL_NO " +
            "  LEFT JOIN MATERIAL_PHOTOS MP ON SAP.MATERIAL_NO = MP.MATERIAL_NO\n " +
            "WHERE SAP.ON_MALL = 'X' AND NVL(SAP.IS_DELETE, ' ') != 'X' AND SAP.MATERIAL_STATUS NOT IN ( 'Z1', 'Z3', 'Z5' ) \n" +
            " AND SPMS.CATEGORY_ID IS NULL AND SAP.MATERIAL_STANDARD = '企标' ")
    List<Map<String, Object>> getNonCategoryMaterialSap();

    /** 根据物料编号查询关联目录信息 */
    @Select("SELECT MC2.CATEGORY_ID AS \"categoryIdL1\",\n" +
            "       MC2.CATEGORY_NAME AS \"categoryNameL1\",\n" +
            "       MC2.CATEGORY_EN_NAME AS \"categoryEnNameL1\",\n" +
            "       MC1.CATEGORY_ID AS \"categoryIdL2\",\n" +
            "       MC1.CATEGORY_NAME AS \"categoryNameL2\",\n" +
            "       MC1.CATEGORY_EN_NAME AS \"categoryEnNameL2\",\n" +
            "       MC.CATEGORY_ID AS \"categoryIdL3\",\n" +
            "       MC.CATEGORY_NAME AS \"categoryNameL3\",\n" +
            "       MC.CATEGORY_EN_NAME AS \"categoryEnNameL3\",\n" +
            "       MS.MATERIAL_NO AS \"materialNo\",\n" +
            "       MS.UUID AS \"uuid\",\n" +
            "       SAP.BRAND AS \"brand\",\n" +
            "       SAP.MATERIAL_NAME AS \"materialName\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "       SAP.PURCHASE_UNIT AS \"purchaseUnit\",\n" +
            "       SAP.NET_WEIGHT AS \"netWeight\",\n" +
            "       SAP.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "       SAP.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "       SAP.VOLUME_QTY AS \"volumeQty\",\n" +
            "       SAP.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "       SAP.LIGHT_HEAVY_MARK AS \"lightHeavyMark\",\n" +
            "       SAP.SPECS AS \"specs\",\n" +
            "       SAP.MATERIAL_MODEL AS \"materialModel\",\n" +
            "       SAP.SUPPLY_DESC AS \"supplyDesc\",\n" +
            "       SAP.SPMS_MARK AS \"spmsMark\"," +
            "       SAP.PRODUCT_LEVEL AS \"productLevel\",\n" +
            "       SAP.PRODUCT_LEVEL_DESC AS \"productLevelDesc\",\n" +
            "  SAP.PRODUCT_LEVEL_LARGE AS \"productLevelLarge\",\n" +
            "  SAP.PRODUCT_LEVEL_LARGE_DESC AS \"productLevelLargeDesc\",\n" +
            "  SAP.PRODUCT_LEVEL_MID AS \"productLevelMid\",\n" +
            "  SAP.PRODUCT_LEVEL_MID_DESC AS \"productLevelMidDesc\",\n" +
            "  SAP.PRODUCT_LEVEL_SMALL AS \"productLevelSmall\",\n" +
            "  SAP.PRODUCT_LEVEL_SMALL_DESC AS \"productLevelSmallDesc\",\n" +
            "  SAP.SUPPLIER_MATERIAL_NO AS \"supplierMaterialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  SAP.MATERIAL_STATUS AS \"materialStatus\",\n" +
            "  SAP.ON_MALL AS \"onMall\",\n" +
            "  SAP.MATERIAL_STANDARD AS \"materialStandard\"\n" +
            "FROM MATERIAL_CATEGORY MC\n" +
            "    LEFT JOIN MATERIAL_SPMS MS ON MC.CATEGORY_ID = MS.CATEGORY_ID\n" +
            "    LEFT JOIN MATERIAL_CATEGORY MC1 ON MC.PARENT_ID = MC1.CATEGORY_ID\n" +
            "    LEFT JOIN MATERIAL_CATEGORY MC2 ON MC1.PARENT_ID = MC2.CATEGORY_ID\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON SAP.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "WHERE SAP.ON_MALL = 'X' AND NVL(SAP.IS_DELETE, ' ') != 'X' AND SAP.MATERIAL_STATUS NOT IN ( 'Z1', 'Z3', 'Z5' ) AND\n " +
            " MC.CATEGORY_ID IS NOT NULL AND MC.CATEGORY_LEVEL = '3' " +
            " AND INSTR(MS.MATERIAL_NO || ',' || SAP.MATERIAL_ZH_DESC || ',' || SAP.MATERIAL_EN_DESC, #{materialNo}, 1, 1) > 0 ")
    List<Map<String,Object>> getCategoryByMaterialSap(@Param("materialNo") String materialNo);

    @Select("<script>\n" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*\n" +
            "FROM (\n" +
            "    SELECT L.LOG_TYPE AS \"logType\",\n" +
            "             L.LOG_CONTENT AS \"logContent\",\n" +
            "             L.OPERATE_TYPE AS \"operateType\",\n" +
            "             L.USER_ID AS \"userId\",\n" +
            "             U.LAST_NAME AS \"username\",\n" +
            "             L.METHOD AS \"method\",\n" +
            "             L.REQUEST_TYPE AS \"requestType\",\n" +
            "             L.CREATE_TIME AS \"createTime\",\n" +
            "             L.LOG_AUTO_ID AS \"logAutoId\",\n" +
            "             L.REQUEST_PARAM AS \"requestParam\",\n" +
            "             L.REMARKS AS \"remarks\"\n" +
            "      FROM SYS_LOG L\n" +
            "               LEFT JOIN SPMS_USER U ON L.USER_ID = U.WORK_CODE\n" +
            "      WHERE L.LOG_TYPE = '3'\n" +
            "       <when test='userId!=null'> AND L.USER_ID = #{userId} </when> \n" +
            "       <when test='beginDate!=null'> AND TO_DATE(SUBSTR(TO_CHAR(L.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss'), 0, 10), 'yyyy-MM-dd') &gt;= TO_DATE(#{beginDate}, 'yyyy-MM-dd') </when> \n" +
            "       <when test='endDate!=null'> AND TO_DATE(SUBSTR(TO_CHAR(L.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss'), 0, 10), 'yyyy-MM-dd') &lt;= TO_DATE(#{endDate}, 'yyyy-MM-dd') </when> \n" +
            "      ORDER BY \"logAutoId\" DESC)\n" +
            "RE \n" +
            "</script>\n")
    List<Map<String, Object>> getOperateLog(Page<Map<String, Object>> page,
                                            @Param("userId") String userId,
                                            @Param("beginDate") String beginDate,
                                            @Param("endDate") String endDate);

    @Select("<script>\n" +
            "SELECT\n" +
            "   MATERIAL_NO AS \"materialNo\",\n" +
            "   MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "   SPMS_MARK AS \"spmsMark\",\n" +
            "   MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "   PRODUCT_LEVEL_LARGE AS \"productLevelLarge\",\n" +
            "   PRODUCT_LEVEL_LARGE_DESC AS \"productLevelLargeDesc\",\n" +
            "   PRODUCT_LEVEL_MID AS \"productLevelMid\",\n" +
            "   PRODUCT_LEVEL_MID_DESC AS \"productLevelMidDesc\",\n" +
            "   PRODUCT_LEVEL_SMALL AS \"productLevelSmall\",\n" +
            "   PRODUCT_LEVEL_SMALL_DESC AS \"productLevelSmallDesc\",\n" +
            "   PRODUCT_LEVEL AS \"productLevel\",\n" +
            "   PRODUCT_LEVEL_DESC AS \"productLevelDesc\",\n" +
            "   MATERIAL_NAME AS \"materialName\",\n" +
            "   SPECS AS \"specs\",\n" +
            "   MATERIAL_MODEL AS \"materialModel\",\n" +
            "   SUPPLY_DESC AS \"supplyDesc\",\n" +
            "   BRAND AS \"brand\",\n" +
            "   SUPPLIER_MATERIAL_NO AS \"supplierMaterialNo\",\n" +
            "   BASIC_UNIT AS \"basicUnit\",\n" +
            "   PURCHASE_UNIT AS \"purchaseUnit\",\n" +
            "   NET_WEIGHT AS \"netWeight\",\n" +
            "   GROSS_WEIGHT AS \"grossWeight\",\n" +
            "   WEIGHT_UNIT AS \"weightUnit\",\n" +
            "   VOLUME_QTY AS \"volumeQty\",\n" +
            "   VOLUME_UNIT AS \"volumeUnit\",\n" +
            "   LIGHT_HEAVY_MARK AS \"lightHeavyMark\",\n" +
            "   IS_DELETE AS \"isDelete\",\n" +
            "   MATERIAL_STATUS AS \"materialStatus\",\n" +
            "   CREATE_DATE AS \"createDate\",\n" +
            "   SPMS_CREATE_TIME AS \"spmsCreateTime\",\n" +
            "   UNIT_SALES AS \"unitSales\",\n" +
            "   NUMERATOR AS \"numerator\",\n" +
            "   DENOMINATOR AS \"denominator\",\n" +
            "   ZWERKS_LIMIT AS \"zwerksLimit\",\n" +
            "   MATERIAL_STANDARD AS \"materialStandard\" \n" +
            "FROM\n" +
            "   MATERIAL_SAP \n" +
            "WHERE\n" +
            "   ON_MALL = 'X' \n" +
            "   AND NVL(IS_DELETE, ' ') != 'X' \n" +
            "   AND MATERIAL_STATUS NOT IN ( 'Z1', 'Z3', 'Z5' )\n" +
            "</script>")
    List<Map<String, Object>> getAllMaterialX();

    /** 一个字段模糊匹配表中多个关键字段SQL语句 */
//    @Select("SELECT * FROM MATERIAL_SAP SAP\n" +
//            "WHERE INSTR(SAP.MATERIAL_NO || ','\n" +
//            "                || NVL(SAP.MATERIAL_ZH_DESC, '') || ','\n" +
//            "                || NVL(SAP.MATERIAL_EN_DESC, '') || ','\n" +
//            "                || NVL(SAP.PRODUCT_LEVEL_LARGE_DESC, '') || ','\n" +
//            "                || NVL(SAP.PRODUCT_LEVEL_MID, '') || ','\n" +
//            "                || NVL(SAP.PRODUCT_LEVEL_MID_DESC, '') || ','\n" +
//            "                || NVL(SAP.PRODUCT_LEVEL_SMALL, '') || ','\n" +
//            "                || NVL(SAP.PRODUCT_LEVEL_SMALL_DESC, '') || ','\n" +
//            "                || NVL(SAP.PRODUCT_LEVEL, '') || ','\n" +
//            "                || NVL(SAP.PRODUCT_LEVEL_DESC, '') || ','\n" +
//            "                || NVL(SAP.MATERIAL_NAME, '') || ','\n" +
//            "                || NVL(SAP.SPECS, '') || ','\n" +
//            "                || NVL(SAP.MATERIAL_MODEL, '') || ','\n" +
//            "                || NVL(SAP.SUPPLY_DESC, '') || ','\n" +
//            "                || NVL(SAP.BRAND, '') || ','\n" +
//            "                || NVL(SAP.SUPPLIER_MATERIAL_NO, '') , #{info}, 1, 1) > 0 ")
//    List<Map<String, Object>> searchMaterialSap(Page<Map<String, Object>> pages, @Param("info") String info);

}