package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmsoversea.entity.OverseaDeliveryNoteDtl;
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;

/**
 * <p>
 * 海外仓交货单明细表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-10-22
 */
@Mapper
public interface OverseaDeliveryNoteDtlMapper extends BaseMapper<OverseaDeliveryNoteDtl> {

    @Select("<script>" +
    		"SELECT ODND.AUTO_ID_DELIVERY_NOTE AS \"autoIdDeliveryNote\",\n" +
            "       ODND.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODND.SAP_DELIVERY_NOTE_ITEM AS \"sapDeliveryNoteItem\",\n" +
            "       ODND.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       (CASE WHEN SAP.DENOMINATOR IS NULL THEN 1 ELSE SAP.DENOMINATOR END) AS \"denominator\",\n" +
            "       (CASE WHEN SAP.NUMERATOR IS NULL THEN 1 ELSE SAP.NUMERATOR END) AS \"numerator\",\n" +
            "       ODND.BASIC_UNIT AS \"basicUnit\",\n" +
            "       ODND.UNIT_SALES AS \"unitSales\",\n" +
            "       ODND.RECEIVABLE_QTY_BASIC_UNIT AS \"receivableQtyBasicUnit\",\n" +
            "       ODND.RECEIVABLE_QTY_UNIT_SALES AS \"receivableQtyUnitSales\",\n" +
            "       ODND.ACTUAL_QTY_BASIC_UNIT AS \"actualQtyBasicUnit\",\n" +
            "       ODND.ACTUAL_QTY_UNIT_SALES AS \"actualQtyUnitSales\",\n" +
            "       ODND.PURCHASE_NO AS \"purchaseNo\",\n" +
            "       ODND.PURCHASE_NO_ITEM AS \"purchaseNoItem\",\n" +
            "       ODND.REMARKS AS \"remarks\",\n" +
            "       ODND.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       ODND.WHS_LOCATION_CODE_DELIVERY AS \"whsLocationCodeDelivery\",\n" +
            "       ODND.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       ODND.WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCodeReceive\",\n" +
            "       ODND.OVERCHARGED_QTY_BASIC_UNIT AS \"overchargedQtyBasicUnit\",\n" +
            "       ODND.OVERCHARGED_QTY_UNIT_SALES AS \"overchargedQtyUnitSales\",\n" +
            "       ODND.UNDERCHARGED_QTY_BASIC_UNIT AS \"underchargedQtyBasicUnit\",\n" +
            "       ODND.UNDERCHARGED_QTY_UNIT_SALES AS \"underchargedQtyUnitSales\",\n" +
            "       ODND.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       ODND.SAP_REMARK AS \"sapRemark\",\n" +
            "       ODND.DATA_VERSION AS \"dataVersion\",\n" +
            "       ODND.WAERS AS \"waers\",\n" +
            "       ODND.DMBTR AS \"dmbtr\",\n" +
            "       NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0) AS \"actualQtyBasicUnitReceived\",\n" +
            "       NVL(RE.ACTUAL_QTY_UNIT_SALES, 0) AS \"actualQtyUnitSalesReceived\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE_DTL ODND\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON ODND.MATERIAL_NO = SAP.MATERIAL_NO " +
            "    LEFT JOIN (" +
            "        SELECT OWI.SAP_DELIVERY_NOTE,\n" +
            "               OWID.SAP_DELIVERY_NOTE_ITEM,\n" +
            "               OWID.MATERIAL_NO,\n" +
            "               SUM(NVL(OWID.ACTUAL_QTY_BASIC_UNIT, 0)) AS ACTUAL_QTY_BASIC_UNIT,\n" +
            "               SUM(NVL(OWID.ACTUAL_QTY_UNIT_SALES, 0)) AS ACTUAL_QTY_UNIT_SALES\n" +
            "        FROM OVERSEA_WHS_IN_DTL OWID\n" +
            "                 LEFT JOIN OVERSEA_WHS_IN OWI ON OWID.UUID_WHS_IN = OWI.UUID_WHS_IN\n" +
            "        WHERE OWI.SPMS_STATUS IN ('3', '4', '5')\n" +
            "          AND OWI.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "        GROUP BY OWI.SAP_DELIVERY_NOTE, OWID.SAP_DELIVERY_NOTE_ITEM, OWID.MATERIAL_NO\n" +
            "    ) RE ON RE.SAP_DELIVERY_NOTE = ODND.SAP_DELIVERY_NOTE AND RE.SAP_DELIVERY_NOTE_ITEM = ODND.SAP_DELIVERY_NOTE_ITEM\n" +
            "WHERE 1=1 AND ODND.SAP_DELIVERY_NOTE = #{sapDeliveryNote} \n" +
            "  <when test='materialNo!=null and materialNo != \"\"'> AND ODND.MATERIAL_NO = #{materialNo}</when>" +
            "ORDER BY TO_NUMBER(ODND.SAP_DELIVERY_NOTE_ITEM) ASC " +
            "</script>")
    List<Map<String, Object>> getDnItems(@Param("sapDeliveryNote") String sapDeliveryNote, @Param("materialNo") String materialNo);

    @Select("SELECT ROWNUM AS \"rowId\",\n" +
            "       ODND.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODND.SAP_DELIVERY_NOTE_ITEM AS \"sapDeliveryNoteItem\",\n" +
            "       ODND.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       (CASE WHEN SAP.DENOMINATOR IS NULL THEN 1 ELSE SAP.DENOMINATOR END) AS \"denominator\",\n" +
            "       (CASE WHEN SAP.NUMERATOR IS NULL THEN 1 ELSE SAP.NUMERATOR END) AS \"numerator\",\n" +
            "       ODND.BASIC_UNIT AS \"basicUnit\",\n" +
            "       ODND.UNIT_SALES AS \"unitSales\",\n" +
            "       ODND.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       ODND.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       ODND.WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCodeReceive\",\n" +
            "       ODND.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       ODND.WAERS AS \"waers\",\n" +
            "       ODND.DMBTR AS \"dmbtr\",\n" +
            "       OPLD.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       OPLD.SAP_PACKAGE_LIST_ITEM AS \"sapPackageListItem\",\n" +
            "       OPLB.BOX_NOTE AS \"boxNote\",\n" +
            "       OPLB.BOX_NOTE_ITEM AS \"boxNoteItem\",\n" +
            "       OPLB.RECEIVABLE_QTY_BASIC_UNIT AS \"receivableQtyBasicUnit\",\n" +
            "       OPLB.ACTUAL_QTY_BASIC_UNIT AS \"actualQtyBasicUnit\",\n" +
            "       OPLB.BOX_STATUS AS \"boxStatus\",\n" +
            "       OPLB.DEMAND_NO AS \"demandNo\",\n" +
            "       OPLB.DEMAND_ITEM AS \"demandItem\",\n" +
            "       OPLB.DEMANDER AS \"demander\",\n" +
            "       OPLB.ARRIVAL_DATE AS \"arrivalDate\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE_DTL ODND\n" +
            "    LEFT JOIN OVERSEA_PACKAGE_LIST_DTL OPLD\n" +
            "        ON ODND.SAP_DELIVERY_NOTE = OPLD.SAP_DELIVERY_NOTE AND ODND.PURCHASE_NO = OPLD.PURCHASE_NO\n" +
            "               AND ODND.PURCHASE_NO_ITEM = OPLD.PURCHASE_NO_ITEM\n" +
            "               AND ODND.SAP_DELIVERY_NOTE_ITEM = OPLD.SAP_DELIVERY_NOTE_ITEM\n" +
            "    LEFT JOIN OVERSEA_PACKAGE_LIST_BOX OPLB\n" +
            "        ON OPLD.SAP_PACKAGE_LIST = OPLB.SAP_PACKAGE_LIST\n" +
            "               AND OPLD.SAP_PACKAGE_LIST_ITEM = OPLB.SAP_PACKAGE_LIST_ITEM\n" +
            "               AND OPLD.BOX_NOTE_ITEM = OPLB.BOX_NOTE_ITEM\n" +
            "               AND OPLD.BOX_NOTE = OPLB.BOX_NOTE\n" +
            "               AND OPLD.SAP_DELIVERY_NOTE = OPLB.SAP_DELIVERY_NOTE\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON ODND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE ODND.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "ORDER BY TO_NUMBER(ODND.SAP_DELIVERY_NOTE_ITEM), OPLB.BOX_NOTE_ITEM")
    List<Map<String, Object>> getDnBoxItems(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 交货单多收少收请求SAP行项目信息结构，盘盈盘亏接口;701-盘盈，702-盘亏 */
    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"zeile\",\n" +
            "       ODND.MATERIAL_NO AS \"matnr\",\n" +
            "       TO_CHAR(ODND.OVERCHARGED_QTY_BASIC_UNIT) AS \"menge\",\n" +
            "       ODND.BASIC_UNIT AS \"meins\",\n" +
            "       '' AS \"lifnr\",\n" +
            "       '' AS \"kostl\",\n" +
            "       ODND.REMARKS AS \"sgtxt\",\n" +
            "       ODND.WERKS_RECEIVE AS \"werks\",\n" +
            "       ODND.WHS_LOCATION_CODE_RECEIVE AS \"lgort\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE_DTL ODND\n" +
            "WHERE ODND.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "  AND ODND.OVERCHARGED_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY ODND.SAP_DELIVERY_NOTE_ITEM ASC ")
    List<Map<String, Object>> getOverchargedItemsToSap(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 交货单多收少收请求SAP行项目信息结构，盘盈盘亏接口;701-盘盈，702-盘亏 */
    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"zeile\",\n" +
            "       ODND.MATERIAL_NO AS \"matnr\",\n" +
            "       TO_CHAR(ODND.UNDERCHARGED_QTY_BASIC_UNIT) AS \"menge\",\n" +
            "       ODND.BASIC_UNIT AS \"meins\",\n" +
            "       '' AS \"lifnr\",\n" +
            "       '' AS \"kostl\",\n" +
            "       ODND.REMARKS AS \"sgtxt\",\n" +
            "       ODND.WERKS_RECEIVE AS \"werks\",\n" +
            "       ODND.WHS_LOCATION_CODE_RECEIVE AS \"lgort\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE_DTL ODND\n" +
            "WHERE ODND.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "  AND ODND.UNDERCHARGED_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY TO_NUMBER(ODND.SAP_DELIVERY_NOTE_ITEM) ASC ")
    List<Map<String, Object>> getUnderchargedItemsToSap(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 收货多收少收请求SAP行项目结构，成本中心接口；多收对应移动类型Z16，少收对应移动类型Z15； */
    @Select("SELECT TO_CHAR(ODND.SAP_DELIVERY_NOTE_ITEM) AS \"ZEILE\",\n" +
            "       ODND.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(ODND.UNDERCHARGED_QTY_BASIC_UNIT) AS \"MENGE\",\n" +
            "       ODND.BASIC_UNIT AS \"MEINS\",\n" +
            "       '' AS \"LIFNR\",\n" +
            "       #{costCenter} AS \"KOSTL\",\n" +
            "       ODND.REMARKS AS \"SGTXT\",\n" +
            "       ODND.WERKS_RECEIVE AS \"WERKS\",\n" +
            "       ODND.WHS_LOCATION_CODE_RECEIVE AS \"LGORT\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE_DTL ODND\n" +
            "WHERE ODND.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "  AND ODND.UNDERCHARGED_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY ODND.SAP_DELIVERY_NOTE_ITEM ASC ")
    List<Map<String, Object>> getUnderchargedItemsToSapAnomalyPost(@Param("sapDeliveryNote") String sapDeliveryNote, @Param("costCenter") String costCenter);
}