package com.sunda.spmsorder.mapper;

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;

@Mapper
public interface OrderTraceMapper {

    @Select("<script>" +
            "SELECT WS.UUID AS \"orderWorkshopUuid\",\n" +
            "       WS.SPMS_WS_ID AS \"spmsWsId\",\n" +
            "       WSD.ROW_ID AS \"rowIdWorkshop\",\n" +
            "       W.UUID AS \"orderWerksUuid\",\n" +
            "       W.SPMS_ID AS \"spmsId\",\n" +
            "       WD.ROW_ID_WERKS AS \"rowIdWerks\",\n" +
            "       WSD.MATERIAL_NO AS \"materialNo\",\n" +
            "       WSD.REQUEST_QTY AS \"requestQty\",\n" +
            "       WSD.APPROVAL_QTY AS \"approvalQty\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "       MS.UNIT_SALES AS \"unitSales\"\n" +
            "FROM ORDER_WORKSHOP WS\n" +
            "    LEFT JOIN ORDER_WORKSHOP_DTL WSD ON WS.UUID = WSD.UUID\n" +
            "    LEFT JOIN ORDER_WERKS_DTL WD ON WSD.SPMS_WS_ID = WD.SPMS_WS_ID AND WSD.ROW_ID = WD.ROW_ID_WS\n" +
            "    LEFT JOIN ORDER_WERKS W ON WD.UUID = W.UUID\n" +
            "    LEFT JOIN MATERIAL_SAP MS ON MS.MATERIAL_NO = WSD.MATERIAL_NO\n" +
            "WHERE 1=1 " +
            "  <when test='orderType == \"1\"'> AND W.UUID = #{uuid}</when>" +
            "  <when test='orderType == \"2\"'> AND WS.UUID = #{uuid}</when>" +
            "ORDER BY WS.SPMS_WS_ID, TO_NUMBER(WSD.ROW_ID)" +
            "</script>")
    List<Map<String, Object>> getOrderDtl(@Param("orderType") String orderType, @Param("uuid") String uuid);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\"," +
            "       '1' AS \"STATSU_PKCODE\",\n" +
            "       SBN.PACKAGE_NOTE AS \"packageNote\",\n" +
            "       SBN.BOX_NOTE AS \"boxNote\",\n" +
            "       SDN.PLANNED_ARRIVAL_DATE AS \"plannedAprivalDate\",\n" +
            "       SDND.WERKS AS \"werks\",\n" +
            "       SDND.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       SBN.BOX_MATERIAL_NAME AS \"boxMaterialName\",\n" +
            "       SBN.TOTAL_QTY AS \"totalQty\",\n" +
            "       SBN.BOX_GROSS_WEIGHT AS \"boxGrossWeight\",\n" +
            "       SBN.BOX_VOLUME AS \"boxVolume\",\n" +
            "       SBN.BOX_LENGTH AS \"boxLength\",\n" +
            "       SBN.BOX_WIDTH AS \"boxWidth\",\n" +
            "       SBN.BOX_HEIGHT AS \"boxHeight\",\n" +
            "       SBN.BOX_COLOR AS \"boxColor\",\n" +
            "       SBN.BOX_QTY_RECEIVED AS \"boxQtyReceived\",\n" +
            "       SBN.ITEM_STATUS AS \"itemStatus\",\n" +
            "       SBN.REMARKS AS \"remarks\",\n" +
            "       SBN.CREATE_TIME AS \"createTime\",\n" +
            "       SBN.ARRIVAL_DATE AS \"arrivalDate\",\n" +
            "       SBN.PARENT_BOX_NOTE AS \"parentBoxNote\"\n" +
            "FROM SAP_BOX_NOTE SBN\n" +
            "LEFT JOIN SAP_DELIVERY_NOTE SDN ON SDN.PACKAGE_NOTE=SBN.PACKAGE_NOTE \n" +
            "LEFT JOIN (SELECT DELIVERY_NOTE_SAP,WERKS,WHS_LOCATION_CODE FROM SAP_DELIVERY_NOTE_DTL GROUP BY  DELIVERY_NOTE_SAP,WERKS,WHS_LOCATION_CODE) SDND ON SDN.DELIVERY_NOTE_SAP=SDND.DELIVERY_NOTE_SAP \n" +
            "WHERE SBN.BOX_NOTE NOT IN (\n" +
            "    SELECT WSI.BOX_NOTE FROM WHS_STORAGE_INVENTORY WSI\n" +
            "        LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "    WHERE WSI.BOX_NOTE IS NOT NULL AND WS.WERKS = 'CN01'\n" +
            "    )\n" +
            "  AND TO_DATE(TO_CHAR(SBN.CREATE_TIME, 'yyyy-mm-dd'), 'yyyy-MM-dd') &lt;= TO_DATE(#{endDate}, 'yyyy-MM-dd')\n" +
            "  AND TO_DATE(TO_CHAR(SBN.CREATE_TIME, 'yyyy-mm-dd'), 'yyyy-MM-dd') &gt;= TO_DATE(#{beginDate}, 'yyyy-MM-dd') " +
            "</script>")
    List<Map<String, Object>> getWaitingForArrivalBoxList(@Param("beginDate") String beginDate,
                                                          @Param("endDate") String endDate);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       '2' AS \"STATSU_PKCODE\",\n" +
            "       RE.* FROM (\n" +
            "            SELECT WS.WERKS AS \"werks\",\n" +
            "                WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                WS.UUID AS \"uuid\",\n" +
            "                WS.STORAGE_AREA AS \"storageArea\",\n" +
            "                WS.STORAGE_NO AS \"storageNo\",\n" +
            "                WSI.BOX_NOTE AS \"boxNote\",\n" +
            "                (CASE WHEN WSI.UPDATE_TIME IS NULL\n" +
            "                          THEN WSI.CREATE_TIME\n" +
            "                      ELSE WSI.UPDATE_TIME END ) AS \"updateTime\",\n" +
            "                SBN.PACKAGE_NOTE AS \"packageNote\",\n" +
            "                SBN.BOX_MATERIAL_NAME AS \"boxMaterialName\",\n" +
            "                SBN.TOTAL_QTY AS \"totalQty\",\n" +
            "                SBN.BOX_GROSS_WEIGHT AS \"boxGrossWeight\",\n" +
            "                SBN.BOX_VOLUME AS \"boxVolume\",\n" +
            "                SBN.BOX_LENGTH AS \"boxLength\",\n" +
            "                SBN.BOX_WIDTH AS \"boxWidth\",\n" +
            "                SBN.BOX_HEIGHT AS \"boxHeight\",\n" +
            "                SBN.BOX_COLOR AS \"boxColor\",\n" +
            "                SBN.BOX_QTY_RECEIVED AS \"boxQtyReceived\",\n" +
            "                SBN.ITEM_STATUS AS \"itemStatus\",\n" +
            "                SBN.REMARKS AS \"remarks\",\n" +
            "                SBN.CREATE_TIME AS \"createTime\",\n" +
            "                SBN.ARRIVAL_DATE AS \"arrivalDate\",\n" +
            "                SBN.PARENT_BOX_NOTE AS \"parentBoxNote\"\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 WSI.BOX_NOTE IS NOT NULL\n" +
            "            AND WS.WERKS = 'CN01'\n" +
            "  <when test='whsLocationCode!=null and whsLocationCode != \"\"'> AND WS.WHS_LOCATION_CODE = #{whsLocationCode} </when> \n" +
            "            AND TO_DATE(TO_CHAR(WSI.CREATE_TIME, 'yyyy-mm-dd'), 'yyyy-MM-dd') &lt;= TO_DATE(#{endDate}, 'yyyy-MM-dd')\n" +
            "            AND TO_DATE(TO_CHAR(WSI.CREATE_TIME, 'yyyy-mm-dd'), 'yyyy-MM-dd') &gt;= TO_DATE(#{beginDate}, 'yyyy-MM-dd')\n" +
            "            ORDER BY WS.WHS_LOCATION_CODE, WS.STORAGE_NO, WSI.BOX_NOTE\n" +
            "           ) RE" +
            "</script>")
    List<Map<String, Object>> getCN01BoxList(@Param("werks") String werks,
                                             @Param("whsLocationCode") String whsLocationCode,
                                             @Param("beginDate") String beginDate,
                                             @Param("endDate") String endDate);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       '3' AS \"STATSU_PKCODE\",\n" +
            "       RESULT.*\n" +
            "FROM(SELECT WOL.LOG_AUTO_ID AS \"logAuToId\",\n" +
            "       WOL.WERKS AS \"werks\",\n" +
            "       WOL.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       WOL.BOX_NOTE AS \"boxNote\",\n" +
            "       WOL.QUANTITY AS \"quantity\",\n" +
            "       WOL.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       WOL.CREATE_TIME AS \"logCreateTime\",\n" +
            "       SBN.PACKAGE_NOTE AS \"packageNote\",\n" +
            "       SBN.BOX_MATERIAL_NAME AS \"boxMaterialName\",\n" +
            "       SBN.TOTAL_QTY AS \"totalQty\",\n" +
            "       SBN.BOX_GROSS_WEIGHT AS \"boxGrossWeight\",\n" +
            "       SBN.BOX_VOLUME AS \"boxVolume\",\n" +
            "       SBN.BOX_LENGTH AS \"boxLength\",\n" +
            "       SBN.BOX_WIDTH AS \"boxWidth\",\n" +
            "       SBN.BOX_HEIGHT AS \"boxHeight\",\n" +
            "       SBN.BOX_COLOR AS \"boxColor\",\n" +
            "       SBN.BOX_QTY_RECEIVED AS \"boxQtyReceived\",\n" +
            "       SBN.ITEM_STATUS AS \"itemStatus\",\n" +
            "       SBN.REMARKS AS \"remarks\",\n" +
            "       SBN.CREATE_TIME AS \"createTime\",\n" +
            "       SBN.ARRIVAL_DATE AS \"arrivalDate\",\n" +
            "       SBN.PARENT_BOX_NOTE AS \"parentBoxNote\"\n" +
            "FROM (\n" +
            "        SELECT MAX(OL.LOG_AUTO_ID) AS \"ID\"\n" +
            "        FROM WHS_OPERATE_LOG OL\n" +
            "        WHERE OL.BOX_NOTE IS NOT NULL AND OL.WERKS = 'CN01'\n" +
            "              AND OL.OPERATION_TYPE IN ('3', '16', '22')\n" +
            "              AND OL.COMMENTS != '自管仓备件入库撤销下架'\n" +
            "        GROUP BY OL.BOX_NOTE\n" +
            "         ) RE\n" +
            "    LEFT JOIN WHS_OPERATE_LOG WOL ON RE.ID = WOL.LOG_AUTO_ID\n" +
            "    LEFT JOIN SAP_BOX_NOTE SBN ON WOL.BOX_NOTE = SBN.BOX_NOTE\n" +
            "    WHERE WOL.BOX_NOTE IS NOT NULL\n" +
            "    AND TO_DATE(TO_CHAR(WOL.CREATE_TIME, 'yyyy-mm-dd'), 'yyyy-MM-dd') &lt;= TO_DATE(#{endDate}, 'yyyy-MM-dd')\n" +
            "    AND TO_DATE(TO_CHAR(WOL.CREATE_TIME, 'yyyy-mm-dd'), 'yyyy-MM-dd') &gt;= TO_DATE(#{beginDate}, 'yyyy-MM-dd')\n" +
            "ORDER BY WOL.WERKS, WOL.BOX_NOTE) RESULT " +
            "</script>")
    List<Map<String, Object>> getOverseaOnTheWayBoxList(@Param("beginDate") String beginDate,
                                                        @Param("endDate") String endDate);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       '4' AS \"STATSU_PKCODE\",\n" +
            "       RESULT.*\n" +
            "FROM (SELECT WOL.WERKS AS \"werks\",\n" +
            "             WOL.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "             WOL.BOX_NOTE AS \"boxNote\",\n" +
            "             WOL.QUANTITY AS \"quantity\",\n" +
            "             WOL.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "             WOL.CREATE_TIME AS \"logCreateTime\",\n" +
            "             WOL.LOG_AUTO_ID AS \"logAuToId\",\n" +
            "             SBN.PACKAGE_NOTE AS \"packageNote\",\n" +
            "             SBN.BOX_MATERIAL_NAME AS \"boxMaterialName\",\n" +
            "             SBN.TOTAL_QTY AS \"totalQty\",\n" +
            "             SBN.BOX_GROSS_WEIGHT AS \"boxGrossWeight\",\n" +
            "             SBN.BOX_VOLUME AS \"boxVolume\",\n" +
            "             SBN.BOX_LENGTH AS \"boxLength\",\n" +
            "             SBN.BOX_WIDTH AS \"boxWidth\",\n" +
            "             SBN.BOX_HEIGHT AS \"boxHeight\",\n" +
            "             SBN.BOX_COLOR AS \"boxColor\",\n" +
            "             SBN.BOX_QTY_RECEIVED AS \"boxQtyReceived\",\n" +
            "             SBN.ITEM_STATUS AS \"itemStatus\",\n" +
            "             SBN.REMARKS AS \"remarks\",\n" +
            "             SBN.CREATE_TIME AS \"createTime\",\n" +
            "             SBN.ARRIVAL_DATE AS \"arrivalDate\",\n" +
            "             SBN.PARENT_BOX_NOTE AS \"parentBoxNote\"\n" +
            "      FROM (\n" +
            "               SELECT MAX(OL.LOG_AUTO_ID) AS \"ID\", OL.BOX_NOTE\n" +
            "               FROM WHS_OPERATE_LOG OL\n" +
            "               WHERE OL.BOX_NOTE IS NOT NULL AND OL.OPERATION_TYPE = '21' GROUP BY OL.BOX_NOTE)\n" +
            "               RE\n" +
            "               LEFT JOIN WHS_OPERATE_LOG WOL ON RE.ID = WOL.LOG_AUTO_ID\n" +
            "               LEFT JOIN SAP_BOX_NOTE SBN ON WOL.BOX_NOTE = SBN.BOX_NOTE\n" +
            "       WHERE WOL.BOX_NOTE IS NOT NULL\n" +
            "            AND TO_DATE(TO_CHAR(WOL.CREATE_TIME, 'yyyy-mm-dd'), 'yyyy-MM-dd') &lt;= TO_DATE(#{endDate}, 'yyyy-MM-dd')\n" +
            "            AND TO_DATE(TO_CHAR(WOL.CREATE_TIME, 'yyyy-mm-dd'), 'yyyy-MM-dd') &gt;= TO_DATE(#{beginDate}, 'yyyy-MM-dd')\n" +
            "      ORDER BY WOL.WERKS, WOL.BOX_NOTE)\n" +
            "    RESULT" +
            "</script>")
    List<Map<String, Object>> getOverseaArrivalBoxList(@Param("beginDate") String beginDate,
                                                       @Param("endDate") String endDate);

}
