package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmsoversea.entity.OverseaDnDtl;
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-12-14
 */
@Mapper
public interface OverseaDnDtlMapper extends BaseMapper<OverseaDnDtl> {

    @Select("SELECT DD.AUTO_ID_DN AS \"autoIdDn\",\n" +
            "       DD.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       DD.SAP_DELIVERY_NOTE_ITEM AS \"sapDeliveryNoteItem\",\n" +
            "       DD.SAP_PURCHASE_ORDER AS \"sapPurchaseOrder\",\n" +
            "       DD.SAP_PURCHASE_ORDER_ITEM AS \"sapPurchaseOrderItem\",\n" +
            "       DD.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       DD.WHS_LOCATION_CODE_DELIVERY AS \"whsLocationCodeDelivery\",\n" +
            "       DD.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       DD.WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCodeReceive\",\n" +
            "       DD.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       (CASE WHEN SAP.NUMERATOR IS NULL THEN 1 ELSE SAP.NUMERATOR END) AS \"numerator\",\n" +
            "       (CASE WHEN SAP.DENOMINATOR IS NULL THEN 1 ELSE SAP.DENOMINATOR END) AS \"denominator\",\n" +
            "       DD.BASIC_UNIT AS \"basicUnit\",\n" +
            "       DD.UNIT_SALES AS \"unitSales\",\n" +
            "       DD.RECEIVABLE_QTY_BASIC_UNIT AS \"receivableQtyBasicUnit\",\n" +
            "       DD.RECEIVABLE_QTY_UNIT_SALES AS \"receivableQtyUnitSales\",\n" +
            "       DD.ACTUAL_QTY_BASIC_UNIT AS \"actualQtyBasicUnit\",\n" +
            "       DD.ACTUAL_QTY_UNIT_SALES AS \"actualQtyUnitSales\",\n" +
            "       DD.STORAGE_INFO AS \"storageInfo\",\n" +
            "       DD.REMARKS AS \"remarks\",\n" +
            "       DD.COMMENTS AS \"comments\",\n" +
            "       DD.BOX_NOTE AS \"boxNote\",\n" +
            "       DD.BOX_NOTE_ITEM AS \"boxNoteItem\",\n" +
            "       DD.DOMESTIC_ACTUAL_QTY AS \"domesticActualQty\",\n" +
            "       DD.DEMAND_NO AS \"demandNo\",\n" +
            "       DD.DEMAND_ITEM AS \"demandItem\",\n" +
            "       DD.DEMANDER AS \"demander\",\n" +
            "       DD.DATA_VERSION AS \"dataVersion\", \n" +
            "       DD.WAERS AS \"waers\", \n" +
            "       DD.DMBTR AS \"dmbtr\", \n" +
            "		TT.SUMACTUALQUANTITY AS \"sumActualQuantity\" \n" +
            "FROM OVERSEA_DN_DTL DD\n" +
            "   LEFT JOIN MATERIAL_SAP SAP ON DD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "	LEFT JOIN ( \n" +
            "		SELECT \n" +
            "			OB.SAP_DELIVERY_NOTE_RELATION , \n" +
            "			SUM(NVL(OB.ACTUAL_QUANTITY, 0)) AS \"SUMACTUALQUANTITY\" \n" +
            "		FROM OVERSEA_OTHER_BATCH_RECEIPT_DTL OB \n" +
            "			LEFT JOIN OVERSEA_OTHER_BATCH_RECEIPT OH ON OB.SPMS_ID = OH.SPMS_ID \n" +
            "		WHERE OH.SPMS_STATUS = '5' \n" +
            "			AND OB.SAP_DELIVERY_NOTE_RELATION = #{sapDeliveryNote}" +
            "		GROUP BY OB.SAP_DELIVERY_NOTE_RELATION \n" +
            "	) TT ON DD.SAP_DELIVERY_NOTE = TT.SAP_DELIVERY_NOTE_RELATION \n" +
            "WHERE DD.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "ORDER BY TO_NUMBER(DD.SAP_DELIVERY_NOTE_ITEM) ")
    List<Map<String, Object>> getDnDtlList(@Param("sapDeliveryNote") String sapDeliveryNote);
}