package com.sunda.spmswms.mapper;

import com.sunda.spmswms.entity.SapPackageListDtl;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

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

/**
 * <p>
 * SAP装箱单明细表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-04-22
 */
@Mapper
public interface SapPackageListDtlMapper extends BaseMapper<SapPackageListDtl> {

    @Select("<script>" +
    		"SELECT ROWNUM AS \"rowId\", RESULT.*\n" +
            "FROM (\n" +
    		"SELECT RE.* FROM (\n" +
    		"	SELECT \n " +
            "       PLD.PACKAGE_LIST AS \"packageList\",\n" +
            "       PLD.ITEM AS \"item\",\n" +
            "       PLD.SPMS_ITEM_NO AS \"spmsItemNo\",\n" +
            "       PLD.SPMS_ITEM_NO AS \"oldSpmsItemNo\",\n" +
            "       PLD.DATE_OF_LOADING AS \"dateOfLoading\",\n" +
            "       PLD.LOADING_WERKS AS \"loadingWerks\",\n" +
            "       PLD.LOADING_WHS_LOCATION_CODE AS \"loadingWhsLocationCode\",\n" +
            "       PLD.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "       PLD.GOODS_TYPE AS \"goodsType\",\n" +
            "       PLD.PACKAGE_CODE AS \"boxNote\",\n" +
            "       PLD.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       PLD.LEAD_SEAL AS \"leadSeal\",\n" +
            "       PLD.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       PLD.SHIPMENT_CATEGORY AS \"shipmentCategory\",\n" +
            "       PLD.PLANNED_PACKING_QTY AS \"plannedPackingQty\",\n" +
            "       PLD.BASIC_UNIT AS \"basicUnit\",\n" +
            "       PLD.PACKING_UNIT AS \"packingUnit\",\n" +
            "       PLD.PLANNED_PACKING_NUMBER AS \"plannedPackingNumber\",\n" +
            "       PLD.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "       PLD.ACTUAL_PACKING_NUMBER AS \"actualPackingNumber\",\n" +
            "       PLD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       PLD.RECEIVING_WERKS AS \"receivingWerks\",\n" +
            "       PLD.ACTUAL_RECEIVING_WERKS AS \"actualReceivingWerks\",\n" +
            "       PLD.PACKING_UNIT AS \"unitSales\",\n" +
            "       PLD.PACKAGE_CODE AS \"packageCode\",\n" +
            "       PLD.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       PLD.SHIPPER AS \"shipper\",\n" +
            "       PLD.CONSIGNEE AS \"consignee\",\n" +
            "       PLD.BUKRS AS \"bukrs\",\n" +
            "       PLD.CONTAINER_LOADING_POSITION AS \"loadingWhsLocationName\",\n" +
            "       PLD.CABINET_WEIGHT AS \"cabinetWeight\",\n" +
            "       PLD.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "       PLD.CABINET_VOLUME AS \"cabinetVolume\",\n" +
            "       PLD.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "       PLD.CONTAINER_BRAND AS \"containerBrand\",\n" +
            "       PLD.COLOR_NUMBER AS \"colorNumber\",\n" +
            "       PLD.PACKING_SPEC AS \"packingSpec\",\n" +
            "       PLD.LOADING_TYPE AS \"loadingType\",\n" +
            "       PLD.NET_WEIGHT AS \"netWeight\",\n" +
            "       PLD.PACKAGE_QUANTITY_MAX AS \"packageQuantityMax\",\n" +
            "       PLD.PACKAGE_UNIT_MAX AS \"packageUnitMax\",\n" +
            "       PLD.BATCH_NO AS \"batchNo\",\n" +
            "       SAP.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "       SAP.LIGHT_HEAVY_MARK AS \"lightHeavyMark\"\n" +
            "	FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    	LEFT JOIN SAP_PACKAGE_BOX PB ON PLD.PACKAGE_LIST = PB.PACKAGE_LIST AND PLD.ITEM = PB.ITEM\n" +
            "    	LEFT JOIN MATERIAL_SAP SAP ON PLD.MATERIAL_NO = SAP.MATERIAL_NO \n" +
            "	WHERE PLD.PACKAGE_LIST = #{packageList} \n" +
            "		AND PLD.MATERIAL_NO IS NOT NULL" +
            "   	<when test='goodsType != null and goodsType != \"\"'> AND PLD.GOODS_TYPE = #{goodsType} </when> \n" +
            "	UNION ALL \n" +
            "	SELECT \n " +
            "       PLD.PACKAGE_LIST AS \"packageList\",\n" +
            "       PLD.ITEM AS \"item\",\n" +
            "       PB.SPMS_ITEM_NO AS \"spmsItemNo\",\n" +
            "       PLD.SPMS_ITEM_NO AS \"oldSpmsItemNo\",\n" +
            "       PLD.DATE_OF_LOADING AS \"dateOfLoading\",\n" +
            "       PLD.LOADING_WERKS AS \"loadingWerks\",\n" +
            "       PLD.LOADING_WHS_LOCATION_CODE AS \"loadingWhsLocationCode\",\n" +
            "       PLD.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "       PLD.GOODS_TYPE AS \"goodsType\",\n" +
            "       PLD.PACKAGE_CODE AS \"boxNote\",\n" +
            "       PLD.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       PLD.LEAD_SEAL AS \"leadSeal\",\n" +
            "       PB.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       PLD.SHIPMENT_CATEGORY AS \"shipmentCategory\",\n" +
            "       PB.DOMESTIC_QTY_RECEIVED AS \"plannedPackingQty\",\n" +
            "       PB.BASIC_UNIT AS \"basicUnit\",\n" +
            "       SAP.UNIT_SALES AS \"packingUnit\",\n" +
            "       ROUND((NVL(PB.DOMESTIC_QTY_RECEIVED, 0)*NVL(SAP.DENOMINATOR, 1)/NVL(SAP.NUMERATOR, 1)), 2) AS \"plannedPackingNumber\",\n" +
            "       PLD.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "       PLD.ACTUAL_PACKING_NUMBER AS \"actualPackingNumber\",\n" +
            "       PLD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       PLD.RECEIVING_WERKS AS \"receivingWerks\",\n" +
            "       PLD.ACTUAL_RECEIVING_WERKS AS \"actualReceivingWerks\",\n" +
            "       SAP.UNIT_SALES AS \"unitSales\",\n" +
            "       PLD.PACKAGE_CODE AS \"packageCode\",\n" +
            "       PLD.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       PLD.SHIPPER AS \"shipper\",\n" +
            "       PLD.CONSIGNEE AS \"consignee\",\n" +
            "       PLD.BUKRS AS \"bukrs\",\n" +
            "       PLD.CONTAINER_LOADING_POSITION AS \"loadingWhsLocationName\",\n" +
            "       PLD.CABINET_WEIGHT AS \"cabinetWeight\",\n" +
            "       PLD.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "       PLD.CABINET_VOLUME AS \"cabinetVolume\",\n" +
            "       PLD.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "       PLD.CONTAINER_BRAND AS \"containerBrand\",\n" +
            "       PLD.COLOR_NUMBER AS \"colorNumber\",\n" +
            "       PLD.PACKING_SPEC AS \"packingSpec\",\n" +
            "       PLD.LOADING_TYPE AS \"loadingType\",\n" +
            "       PLD.NET_WEIGHT AS \"netWeight\",\n" +
            "       PLD.PACKAGE_QUANTITY_MAX AS \"packageQuantityMax\",\n" +
            "       PLD.PACKAGE_UNIT_MAX AS \"packageUnitMax\",\n" +
            "       PLD.BATCH_NO AS \"batchNo\",\n" +
            "       SAP.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "       SAP.LIGHT_HEAVY_MARK AS \"lightHeavyMark\"\n" +
            "	FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    	LEFT JOIN SAP_BOX_NOTE_DTL PB ON PLD.PACKAGE_CODE = PB.BOX_NOTE \n" +
            "    	LEFT JOIN MATERIAL_SAP SAP ON PB.MATERIAL_NO = SAP.MATERIAL_NO \n" +
            "	WHERE PLD.PACKAGE_LIST = #{packageList} \n" +
            "		AND PLD.PACKAGE_CODE IS NOT NULL AND PLD.MATERIAL_NO IS NULL \n" +
            "   	<when test='goodsType != null and goodsType != \"\"'> AND PLD.GOODS_TYPE = #{goodsType} </when> \n" +
    		"	) RE \n" +
    		") RESULT \n" +
            "ORDER BY ROWNUM ASC \n" +
            "</script>")
    List<Map<String, Object>> getSapPackageList(@Param("packageList") String packageList, @Param("goodsType") String goodsType);

    @Select("SELECT DISTINCT PLD.WHS_OUT_UUID AS \"whsOutUuid\",\n" +
            "                PLD.PACKAGE_CODE AS \"boxNote\"\n" +
            "FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    LEFT JOIN SAP_PACKAGE_BOX SPB\n" +
            "        ON PLD.PACKAGE_LIST = SPB.PACKAGE_LIST AND PLD.ITEM = SPB.ITEM\n" +
            "WHERE PLD.GOODS_TYPE = #{goodsType} AND PLD.WHS_OUT_UUID = #{uuid}\n")
    List<Map<String, Object>> getWhsOutDtlX(@Param("uuid") String uuid, @Param("goodsType") String goodsType);

    @Update("UPDATE SAP_PACKAGE_LIST_DTL SET SPMS_STATUS = #{targetStatus} WHERE WHS_OUT_UUID = #{whsOutUuid} ")
    int uSapPackageListDtl(@Param("targetStatus") String targetStatus, @Param("whsOutUuid") String whsOutUuid);

    @Select("SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT PLD.PACKAGE_LIST AS \"packageList\",\n" +
            "                PLD.ITEM AS \"item\",\n" +
            "                PLD.DATE_OF_LOADING AS \"dateOfLoading\",\n" +
            "                PLD.LOADING_WERKS AS \"loadingWerks\",\n" +
            "                PLD.LOADING_WHS_LOCATION_CODE AS \"loadingWhsLocationCode\",\n" +
            "                PLD.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "                PLD.GOODS_TYPE AS \"goodsType\",\n" +
            "                PLD.RECEIVING_WERKS AS \"receivingWerks\",\n" +
            "                PLD.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "                PLD.LEAD_SEAL AS \"leadSeal\",\n" +
            "                PLD.MATERIAL_NO AS \"materialNo\",\n" +
            "                SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "                PLD.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "                PLD.BASIC_UNIT AS \"basicUnit\",\n" +
            "                PLD.ACTUAL_PACKING_NUMBER AS \"actualPackingNumber\",\n" +
            "                PLD.PACKING_UNIT AS \"packingUnit\",\n" +
            "                PLD.CONTAINER_BRAND AS \"contaningBrand\",\n" +
            "                PLD.CUSTOMS_DECLARATION_NUMBER AS \"customsDeclarationNumber\",\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" +
            "         FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "                  LEFT JOIN MATERIAL_SAP SAP ON PLD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "         WHERE PLD.PACKAGE_LIST = #{packageList} AND PLD.GOODS_TYPE = 'M' ORDER BY ITEM ASC\n" +
            "         ) RE ORDER BY \"rowId\" ")
    List<Map<String, Object>> getSapPackageListDtlM(@Param("packageList") String packageList);
    
    @Select(
    		"SELECT MAX(TO_NUMBER(NVL(SPMS_ITEM_NO, 0))) FROM SAP_PACKAGE_LIST_DTL WHERE PACKAGE_LIST = #{packageNumber}"
    )
	int selectMaxSpmsItemNo(@Param("packageNumber") String packageNumber);
}