package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmswms.entity.WhsOtherUnloadDtl;
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
 * @since
 */
@Mapper
public interface WhsOtherUnloadDtlMapper extends BaseMapper<WhsOtherUnloadDtl> {

    @Select("SELECT\n" +
            "    WHODTL.WHS_OTHER_UUID AS \"whsOtherUuid\",\n" +
            "    WHODTL.AUTO_ID AS \"autoId\",\n" +
            "    WHODTL.ROW_ID AS \"rowId\",\n" +
            "    WHODTL.BOX_NOTE AS \"boxNote\",\n" +
            "    WHODTL.MATERIAL_NO AS \"materialNo\",\n" +
            "    WHODTL.OPERATE_QTY AS \"operateQty\",\n" +
            "    WHODTL.BASIC_UNIT AS \"basicUnit\",\n" +
            "    WHODTL.PACKAGE_UNIT AS \"packageUnit\",\n" +
            "    WHODTL.PACKAGE_LIST AS \"packageList\",\n" +
            "    WHODTL.PACKAGE_ROW_ID AS \"packageRowId\",\n" +
            "    WHODTL.PACKAGE_QTY AS \"packageQty\",\n" +
            "    WHODTL.COST_CENTER AS \"costCenter\",\n" +
            "    WHODTL.SUPPLIER_NO AS \"supplierNo\",\n" +
            "    WHODTL.SUPPLIER_DESC AS \"supplierDesc\",\n" +
            "    WHODTL.MATERIAL_NO_OUT AS \"materialNoOut\",\n" +
            "    WHODTL.QTY_OUT AS \"qtyOut\",\n" +
            "    WHODTL.MATERIAL_NO_IN AS \"materialNoIn\",\n" +
            "    WHODTL.QTY_IN AS \"qtyIn\",\n" +
            "    WHODTL.WERKS AS \"werks\",\n" +
            "    WHODTL.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "    WHODTL.ROW_REMARKS AS \"rowRemarks\",\n" +
            "    WHODTL.TEMP_STORAGE AS \"tempStorage\",\n" +
            "    WHODTL.STORAGE_OUT AS \"storageOut\",\n" +
            "    WHODTL.STORAGE_IN AS \"storageIn\",\n" +
            "    WHODTL.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "    WHODTL.DELIVERY_NOTE_SAP_ITEM AS \"deliveryNoteSapItem\",\n" +
            "    WHODTL.PURCHASE_GROUP AS \"purchaseGroup\",\n" +
            "    WHODTL.PURCHASE_GROUP_NAME AS \"purchaseGroupName\",\n" +
            "    WHODTL.DELIVERY_QTY AS \"deliveryQty\",\n" +
            "    WHODTL.DELIVERY_UNIT AS \"deliveryUnit\",\n" +
            "    WHODTL.RECEIVED_QTY AS \"receivedQty\",\n" +
            "    WHODTL.OVER_DELIVERY_LIMIT AS \"overDeliveryLimit\",\n" +
            "    WHODTL.UNDER_DELIVERY_LIMIT AS \"underDeliveryLimit\",\n" +
            "    WHODTL.UNLIMITED AS \"unlimited\",\n" +
            "    WHODTL.WERKS_XQ AS \"werksXq\",\n" +
            "    WHODTL.QTY_SALES AS \"qtySales\",\n" +
            "    WHODTL.UNIT_SALES AS \"unitSales\",\n" +
            "    WHODTL.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "    WHODTL.GOODS_TYPE AS \"goodsType\",\n" +
            "    WHODTL.RECEIVING_WERKS AS \"receivingWerks\",\n" +
            "    WHODTL.ACTUAL_RECEIVING_WERKS AS \"actualReceivingWerks\",\n" +
            "    WHODTL.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "    WHODTL.LEAD_SEAL AS \"leadSeal\",\n" +
            "    WHODTL.SHIPMENT_CATEGORY AS \"shipmentCategory\",\n" +
            "    WHODTL.PLANNED_PACKING_QTY AS \"plannedPackingQty\",\n" +
            "    WHODTL.ITEM AS \"item\",\n" +
            "    WHODTL.DATE_OF_LOADING AS \"dateOfLoading\",\n" +
            "    WHODTL.LOADING_WERKS AS \"loadingWerks\",\n" +
            "    WHODTL.LOADING_WHS_LOCATION_CODE AS \"loadingWhsLocationCode\",\n" +
            "    WHODTL.PLANNED_PACKING_NUMBER AS \"plannedPackingNumber\",\n" +
            "    WHODTL.PACKING_UNIT AS \"packingUnit\",\n" +
            "    WHODTL.CABINET_WEIGHT AS \"cabinetWeight\",\n" +
            "    WHODTL.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "    WHODTL.CABINET_VOLUME AS \"cabinetVolume\",\n" +
            "    WHODTL.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "    WHODTL.TRAILER_LINE AS \"trailerLine\",\n" +
            "    WHODTL.TRAILER_LINE_DESC AS \"trailerLineDesc\",\n" +
            "    WHODTL.SPMS_STATUS AS \"spmsStatus\",\n" +
            "    WHODTL.UNPLANNED AS \"unplanned\",\n" +
            "    WHODTL.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "    WHODTL.CONTAINER_BRAND AS \"containerBrand\",\n" +
            "    WHODTL.COLOR_NUMBER AS \"colorNumber\",\n" +
            "    WHODTL.THICKNESS AS \"thickness\",\n" +
            "    WHODTL.ACTUAL_AVG_GROSS_WEIGHT AS \"actualAvgGrossWeight\",\n" +
            "    WHODTL.ACTUAL_PACKING_NUMBER AS \"actualPackingNumber\",\n" +
            "    WHODTL.CUSTOMS_DECLARATION_NUMBER AS \"customsDeclarationNumber\",\n" +
            "    WHODTL.PACKING_SPEC AS \"packingSpec\",\n" +
            "    WHODTL.LOADING_TYPE AS \"loadingType\",\n" +
            "    WHODTL.WAERS AS \"waers\",\n" +
            "    WHODTL.DMBTR AS \"dmbtr\",\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" +
            "    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" +
            "    (CASE WHEN SAP.UNIT_SALES IS NULL THEN '1' ELSE SAP.UNIT_SALES END) AS \"sapUnitSales\",\n" +
            "    WSI.UUID AS \"storageUuid\",\n" +
            "    WS.STORAGE_NO AS \"storageNo\"\n" +
            "FROM WHS_OTHER_UNLOAD_DTL WHODTL\n" +
            "         LEFT JOIN MATERIAL_SAP SAP ON WHODTL.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "         LEFT JOIN WHS_STORAGE_INVENTORY WSI ON WHODTL.BOX_NOTE = WSI.BOX_NOTE\n" +
            "         LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID AND (WS.WERKS = WHODTL.LOADING_WERKS OR WS.WERKS = WHODTL.WERKS) AND (WS.WHS_LOCATION_CODE = WHODTL.LOADING_WHS_LOCATION_CODE OR WS.WHS_LOCATION_CODE = WHODTL.WHS_LOCATION_CODE)\n"+
            "WHERE WHODTL.WHS_OTHER_UUID= #{whsOtherUuid} ")
    List<Map<String, Object>> selectWhsOtherDtl(@Param("whsOtherUuid") String whsOtherUuid);

    @Select("SELECT \n" +
            "       NVL(MAX(TO_NUMBER(ROW_ID)),0) AS \"rowId\"  \n" +
            "FROM WHS_OTHER_UNLOAD_DTL \n" +
            "WHERE WHS_OTHER_UUID= #{uuid}")
    int selectMaxRowId(@Param("uuid") String uuid);

}