package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

/**
 * @program: spms
 * @description:
 * @author: Wayne Wu
 * @create: 2021-10-21 15:36
 **/
@Mapper
public interface SapRepackageMapper {

    @Select("SELECT SPN.PACKAGE_NOTE AS \"PACKAGE_NO\",\n" +
            "       SPN.DELIVERY_NOTE_SRM AS \"SRM_DN\",\n" +
            "       SPN.DELIVERY_NOTE_SAP AS \"SAP_DN\",\n" +
            "       SPN.SUPPLIER_CODE AS \"LIFNR\",\n" +
            "       TO_CHAR(SPN.TOTAL_VOLUME) AS \"VOLUM_T\",\n" +
            "       TO_CHAR(SPN.TOTAL_WEIGHT) AS \"BRGEW_T\",\n" +
            "       SPN.LOAD_BEGIN_TIME AS \"ZXDATE_F\",\n" +
            "       SPN.LOAD_END_TIME AS \"ZXDATE_T\",\n" +
            "       SPN.TRANSPORTER AS \"YSR\",\n" +
            "       SPN.CONTRACTOR_INFO AS \"CYRLXFS\",\n" +
            "       SPN.RECEIVER AS \"SHR\",\n" +
            "       SPN.RECEIVER_INFO AS \"SHRLXFS\",\n" +
            "       SPN.RECEIVING_ADDRESS AS \"SHDZ\",\n" +
            "       SPN.SPMS_CONTRACT_NOTE AS \"SPMS_CONTRACT_NO\",\n" +
            "       'S' AS \"SPMS_FLG\"\n" +
            "FROM SAP_PACKAGE_NOTE SPN, SAP_BOX_NOTE SBN\n" +
            "WHERE SPN.PACKAGE_NOTE = SBN.PACKAGE_NOTE\n" +
            "  AND SBN.BOX_NOTE = #{boxNote} ")
    Map<String, Object> getHeadToSap(@Param("boxNote") String boxNote);

    @Select("SELECT SBN.PACKAGE_NOTE AS \"PACKAGE_NO\",\n" +
            "       SBN.BOX_NOTE AS \"PACKAGE_CODE\",\n" +
            "       NVL(SBN.BOX_MATERIAL_NAME, '') AS \"OUTER_BOX_TEXTURE\",\n" +
            "       TO_CHAR(SBN.TOTAL_QTY) AS \"TOTAL_QTY\",\n" +
            "       TO_CHAR(SBN.BOX_GROSS_WEIGHT) AS \"BOX_BRGEW\",\n" +
            "       TO_CHAR(SBN.BOX_VOLUME) AS \"BOX_VOLUM\",\n" +
            "       TO_CHAR(SBN.BOX_LENGTH) AS \"LENGTH\",\n" +
            "       TO_CHAR(SBN.BOX_WIDTH) AS \"WIDTH\",\n" +
            "       TO_CHAR(SBN.BOX_HEIGHT) AS \"HEIGHT\",\n" +
            "       NVL(SBN.BOX_COLOR, '') AS \"COLOR\"\n" +
            "FROM SAP_BOX_NOTE SBN\n" +
            "WHERE SBN.BOX_NOTE = #{boxNote} ")
    Map<String, Object> boxNoteToSap(@Param("boxNote") String boxNote);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "    SELECT WSI.BOX_NOTE AS \"boxNote\",\n" +
            "           WSI.UUID AS \"uuid\",\n" +
            "           WS.WERKS AS \"werks\",\n" +
            "           WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "           WS.STORAGE_NO AS \"storageNo\",\n" +
            "           SBN.PARENT_BOX_NOTE AS \"parentBoxNote\",\n" +
            "           (SELECT LISTAGG(SBN2.BOX_NOTE, ',')\n" +
            "               WITHIN GROUP ( ORDER BY SBN.BOX_NOTE)\n" +
            "           FROM SAP_BOX_NOTE SBN2\n" +
            "           WHERE SBN.BOX_NOTE = SBN2.PARENT_BOX_NOTE) AS \"childSapBoxNote\"\n" +
            "    FROM WHS_STORAGE_INVENTORY WSI\n" +
            "        LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "        LEFT JOIN SAP_BOX_NOTE SBN ON WSI.BOX_NOTE = SBN.BOX_NOTE\n" +
            "    WHERE SBN.BOX_NOTE IS NOT NULL\n" +
            "          AND WSI.QUANTITY > 0\n" +
            "      AND WS.WERKS = #{werks}\n" +
            "      AND WS.WHS_LOCATION_CODE = #{whsLocationCode}\n" +
            "      <when test='boxNote!=null'> AND SBN.BOX_NOTE = #{boxNote} </when> \n" +
            "    ORDER BY \"childSapBoxNote\" NULLS LAST, SBN.BOX_NOTE ASC\n" +
            "    ) RE" +
            "</script>")
    List<Map<String, Object>> pageMaDataMap(Page<Map<String, Object>> pages,
                                            @Param("werks") String werks,
                                            @Param("whsLocationCode") String whsLocationCode,
                                            @Param("boxNote") String boxNote);
    
    @Select("SELECT RE.BUKRS, \n" +
    		"	RE.WERKS , \n" +
    		"	RE.WHS_LOCATION_CODE  \n" +
            "FROM (\n" +
            "    SELECT SBN.BUKRS,   \n" +
            "		WS.WERKS, \n" +
            "		WS.WHS_LOCATION_CODE \n" +
            "    FROM WHS_STORAGE_INVENTORY WSI \n" +
            "        LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "        LEFT JOIN Z_SAP_ORG_CLIENT SBN ON WS.WERKS = SBN.WERKS\n" +
            "    WHERE WSI.BOX_NOTE = #{boxNote}\n" +
            "    ) RE \n" +
            "WHERE 1=1 and ROWNUM <= 1")
    Map<String, String> selectBukrsByBoxNote(@Param("boxNote") String boxNote);



}