package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmswms.entity.WhsOut;
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>
 * 出库任务表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-04-23
 */
@Mapper
public interface WhsOutMapper extends BaseMapper<WhsOut> {

    @Select("SELECT PLD.PACKAGE_LIST AS \"packageList\",\n" +
            "       ROWNUM AS \"rowId\",\n" +
            "       PLD.ITEM AS \"item\",\n" +
            "       PLD.SPMS_ITEM_NO AS \"spmsItemNo\",\n" +
            "       PLD.SHIPPER AS \"shipper\",\n" +
            "       PLD.CONSIGNEE AS \"consignee\",\n" +
            "       PLD.BUKRS AS \"bukrs\",\n" +
            "       PLD.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       PLD.CONTAINER_LOADING_POSITION AS \"containerLoadingPosition\",\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.ACTUAL_RECEIVING_WERKS AS \"actualReceivingWerks\",\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.PLANNED_PACKING_NUMBER AS \"plannedPackingNumber\",\n" +
            "       PLD.PACKING_UNIT AS \"packingUnit\",\n" +
            "       PLD.CABINET_WEIGHT AS \"cabinetWeight\",\n" +
            "       PLD.WEIGHT_UNIT AS \"cabinetWeightUnit\",\n" +
            "       PLD.CABINET_VOLUME AS \"cabinetVolume\",\n" +
            "       PLD.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "       PLD.TRAILER_LINE AS \"trailerLine\",\n" +
            "       PLD.TRAILER_LINE_DESC AS \"trailerLineDesc\",\n" +
            "       PLD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       PLD.WHS_OUT_UUID AS \"whsOutUuid\",\n" +
            "       PLD.TEMP_STORAGE AS \"tempStorage\",\n" +
            "       PLD.UNPLANNED AS \"unplanned\",\n" +
            "       PLD.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "       PLD.CONTAINER_BRAND AS \"containerBrand\",\n" +
            "       PLD.COLOR_NUMBER AS \"colorNumber\",\n" +
            "       PLD.THICKNESS AS \"thickness\",\n" +
            "       PLD.ACTUAL_AVG_GROSS_WEIGHT AS \"actualAvgGrossWeight\",\n" +
            "       PLD.ACTUAL_PACKING_NUMBER AS \"actualPackingNumber\",\n" +
            "       PLD.CUSTOMS_DECLARATION_NUMBER AS \"customsDeclarationNumber\",\n" +
            "       PLD.PACKING_SPEC AS \"packingSpec\",\n" +
            "       PLD.LOADING_TYPE AS \"loadingType\",\n" +
            "       PLD.REMARKS AS \"remarks\",\n" +
            "      	PLD.BATCH_NO AS \"batchNo\",\n" +
            "      	PLD.PACKAGE_QUANTITY_MAX AS \"packageQuantityMax\",\n" +
            "      	PLD.PACKAGE_UNIT_MAX AS \"packageUnitMax\",\n" +
            "       (CASE WHEN SAP.UNIT_SALES IS NULL THEN SAP.BASIC_UNIT\n" +
            "           ELSE SAP.UNIT_SALES END) AS \"unitSales\",\n" +
            "       (CASE WHEN SAP.NUMERATOR IS NULL THEN 1\n" +
            "           ELSE SAP.NUMERATOR END) AS \"numerator\",\n" +
            "       (CASE WHEN SAP.DENOMINATOR IS NULL THEN 1\n" +
            "           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" +
            "                         (SELECT ListAgg(WS.STORAGE_NO||'-'||WSI.QUANTITY||'-'||WS.REMARKS, ',') within group ( order by WS.STORAGE_NO) AS RECOMMEND_STORAGE\n" +
            "                            FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                            LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID AND WS.WERKS = WOUT.WERKS AND WS.WHS_LOCATION_CODE = WOUT.WHS_LOCATION_CODE \n" +
            "                            WHERE WSI.MATERIAL_NO = PLD.MATERIAL_NO)\n" +
            "                         AS \"recommendStorage\"\n" +
            "FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON PLD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "    LEFT JOIN WHS_OUT WOUT ON PLD.WHS_OUT_UUID = WOUT.UUID\n" +
            "WHERE PLD.WHS_OUT_UUID = #{uuid} ORDER BY PLD.SPMS_ITEM_NO ASC ")
    List<Map<String, Object>> getPackageListDtlM(@Param("uuid") String uuid);

    @Select("SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "    SELECT DISTINCT PLD.PACKAGE_LIST AS \"packageList\",\n" +
//            "       		  ROWNUM AS \"rowId\",\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.RECEIVING_WERKS AS \"receivingWerks\",\n" +
            "       		  PLD.ACTUAL_RECEIVING_WERKS AS \"actualReceivingWerks\",\n" +
            "                 PLD.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "                 PLD.GOODS_TYPE AS \"goodsType\",\n" +
//            "       		  PLD.SPMS_ITEM_NO AS \"spmsItemNo\",\n" +
            "       		  PLD.SHIPPER AS \"shipper\",\n" +
            "       		  PLD.CONSIGNEE AS \"consignee\",\n" +
            "      			  PLD.BUKRS AS \"bukrs\",\n" +
            "      			  PLD.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       		  PLD.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       		  PLD.LEAD_SEAL AS \"leadSeal\",\n" +
            "       		  PLD.SHIPMENT_CATEGORY AS \"shipmentCategory\",\n" +
            "       		  '1' AS \"plannedPackingQty\",\n" +
            "       		  '1' AS \"plannedPackingNumber\",\n" +
//            "       		  PLD.PACKING_UNIT AS \"packingUnit\",\n" +
            "       		  PLD.CABINET_WEIGHT AS \"cabinetWeight\",\n" +
            "       		  PLD.WEIGHT_UNIT AS \"cabinetWeightUnit\",\n" +
            "       		  PLD.CABINET_VOLUME AS \"cabinetVolume\",\n" +
            "       		  PLD.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "       		  PLD.TRAILER_LINE AS \"trailerLine\",\n" +
            "       		  PLD.TRAILER_LINE_DESC AS \"trailerLineDesc\",\n" +
            "       		  PLD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "      			  PLD.COLOR_NUMBER AS \"colorNumber\",\n" +
            "       		  PLD.THICKNESS AS \"thickness\",\n" +
            "       		  PLD.ACTUAL_AVG_GROSS_WEIGHT AS \"actualAvgGrossWeight\",\n" +
            "      			  PLD.REMARKS AS \"remarks\",\n" +
            "      			  PLD.CONTAINER_LOADING_POSITION AS \"containerLoadingPosition\",\n" +
            "      			  PLD.BATCH_NO AS \"batchNo\",\n" +
            "      			  PLD.PACKAGE_QUANTITY_MAX AS \"packageQuantityMax\",\n" +
            "      			  PLD.PACKAGE_UNIT_MAX AS \"packageUnitMax\",\n" +
            "                 PLD.PACKAGE_CODE AS \"boxNote\",\n" +
            "                 PLD.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "       		  PLD.ACTUAL_PACKING_NUMBER AS \"actualPackingNumber\",\n" +
            "       		  PLD.CUSTOMS_DECLARATION_NUMBER AS \"customsDeclarationNumber\",\n" +
            "                 PLD.UNPLANNED AS \"unplanned\",\n" +
            "                 PLD.CONTAINER_BRAND AS \"containerBrand\",\n" +
            "                 PLD.PACKING_SPEC AS \"packingSpec\",\n" +
            "                 PLD.LOADING_TYPE AS \"loadingType\",\n" +
            "                 PLD.NET_WEIGHT AS \"netWeight\",\n" +
            "                 WSI.UUID AS \"targetStorage\",\n" +
            "                 WS.STORAGE_NO AS \"storageNo\",\n" +
            "                 PLD.WHS_OUT_UUID AS \"whsOutUuid\"\n" +
            "    FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            //"        LEFT JOIN SAP_PACKAGE_BOX BOX ON ((PLD.PACKAGE_LIST = BOX.PACKAGE_LIST AND PLD.ITEM = BOX.ITEM) OR (PLD.WHS_OUT_UUID = BOX.WHS_OUT_UUID))\n" +
            "        LEFT JOIN WHS_STORAGE_INVENTORY WSI ON PLD.PACKAGE_CODE = WSI.BOX_NOTE\n" +
            "        LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID AND WS.WERKS = PLD.LOADING_WERKS AND WS.WHS_LOCATION_CODE = PLD.LOADING_WHS_LOCATION_CODE\n" +
            "    WHERE PLD.WHS_OUT_UUID = #{uuid} ORDER BY PLD.PACKAGE_CODE ASC \n" +
            ") RE ")
    List<Map<String, Object>> getPackageListDtlX(@Param("uuid") String uuid);

    @Select("SELECT BND.PACKAGE_NOTE AS \"packageNote\",\n" +
            "       ROWNUM AS \"rowId\",\n" +
            "       BND.ITEM AS \"item\",\n" +
            "       BND.BOX_NOTE AS \"boxNote\",\n" +
            "       BND.PACKAGE_POSNR AS \"packagePosnr\",\n" +
            "       BND.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       BND.SPECS AS \"spec\",\n" +
            "       BND.QUANTITY AS \"quantity\",\n" +
            "       BND.BASIC_UNIT AS \"basicUnit\",\n" +
            "       BND.SRM_CONTRACT_NO AS \"srmContractNo\",\n" +
            "       BND.PURCHASE_NO AS \"purchaseNo\",\n" +
            "       BND.PURCHASE_NO_ITEM AS \"purchaseNoItem\",\n" +
            "       BND.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       BND.DELIVERY_NOTE_SAP_ITEM AS \"deliveryNoteSapItem\",\n" +
            "       BND.DEMAND_NO AS \"demandNo\",\n" +
            "       BND.DEMAND_NO_ITEM AS \"demandNoItem\",\n" +
            "       BND.DEMANDER AS \"demander\",\n" +
            "       BND.REMARK AS \"remark\",\n" +
            "       BND.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "       BND.NET_WEIGHT AS \"netWeight\",\n" +
            "       BND.DOMESTIC_QTY_RECEIVABLE AS \"domesticQtyReceivable\",\n" +
            "       BND.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
            "       (CASE WHEN SAP.UNIT_SALES IS NULL THEN SAP.BASIC_UNIT\n" +
            "           ELSE SAP.UNIT_SALES END) AS \"unitSales\",\n" +
            "       (CASE WHEN SAP.NUMERATOR IS NULL THEN 1\n" +
            "           ELSE SAP.NUMERATOR END) AS \"numerator\",\n" +
            "       (CASE WHEN SAP.DENOMINATOR IS NULL THEN 1\n" +
            "           ELSE SAP.DENOMINATOR END) AS \"denominator\"\n" +
            "FROM SAP_BOX_NOTE_DTL BND\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON BND.MATERIAL_NO = SAP.MATERIAL_NO \n" +
            "WHERE BND.BOX_NOTE = #{boxNote} ORDER BY BND.ITEM ASC\n")
    List<Map<String, Object>> getBoxNoteDtl(@Param("boxNote") String boxNote);


    @Select("SELECT WO.PACKAGE_LIST AS \"packageList\",\n" +
            "       WO.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "       WO.CABINET_CHECK_RESULT AS \"cabinetCheckResult\",\n" +
            "       WO.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       W.WHS_LOCATION_DESC AS \"whsLocationDesc\",\n" +
            "       WO.MONITOR_LOADING AS \"monitorLoading\",\n" +
            "       WO.LOADING_TYPE AS \"loadingType\",\n" +
            "       WO.GOODS_TYPE AS \"goodsType\"" +
            "FROM WHS_OUT WO\n" +
            "    LEFT JOIN WHS W ON WO.WERKS = W.WERKS AND WO.WHS_LOCATION_CODE = W.WHS_LOCATION_CODE\n" +
            "WHERE WO.UUID = #{whsOutUuid} \n ")
    Map<String, Object> getHead(@Param("whsOutUuid") String whsOutUuid);

    @Select("SELECT PLD.PACKAGE_LIST AS \"packageList\",\n" +
            "       PLD.WHS_OUT_UUID AS \"whsOutUuid\",\n" +
            "       PLD.UNPLANNED AS \"unplanned\",\n" +
            "       WO.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "       WO.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       WO.LEAD_SEAL AS \"leadSeal\",\n" +
            "       PLD.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       PLD.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "       PLD.CONTAINER_BRAND AS \"containerBrand\",\n" +
            "       PLD.COLOR_NUMBER AS \"colorNumber\",\n" +
            "       PLD.THICKNESS AS \"thickness\",\n" +
            "       PLD.ACTUAL_AVG_GROSS_WEIGHT AS \"actualAvgGrossWeight\"\n" +
            "FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    LEFT JOIN WHS_OUT WO ON PLD.WHS_OUT_UUID = WO.UUID\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON PLD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE PLD.ACTUAL_OPERATE_QTY > 0 AND PLD.WHS_OUT_UUID = #{whsOutUuid} \n")
    List<Map<String, Object>> getItemM(@Param("whsOutUuid") String whsOutUuid);

    @Select("SELECT WO.PACKAGE_LIST AS \"packageList\",\n" +
            "       WO.UUID AS \"whsOutUuid\",\n" +
            "       PB.UNPLANNED AS \"unplanned\",\n" +
            "       WO.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "       WO.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       WO.LEAD_SEAL AS \"leadSeal\",\n" +
            "       BN.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       BN.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "       PB.CONTAINER_BRAND AS \"containerBrand\"\n" +
            "FROM WHS_OUT WO\n" +
            "    LEFT JOIN SAP_PACKAGE_BOX PB ON WO.UUID = PB.WHS_OUT_UUID\n" +
            "    LEFT JOIN SAP_BOX_NOTE_DTL BN ON PB.BOX_NOTE = BN.BOX_NOTE\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON BN.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE WO.UUID = #{whsOutUuid} AND PB.ACTUAL_OPERATE_QTY > 0 \n")
    List<Map<String, Object>> getItemX(@Param("whsOutUuid") String whsOutUuid);

    @Select("SELECT PB.PACKAGE_LIST AS \"packageList\",\n" +
            "       PB.BOX_NOTE AS \"boxNote\",\n" +
            "       PB.UNPLANNED AS \"unplanned\"\n" +
            "FROM SAP_PACKAGE_BOX PB\n" +
            "WHERE PB.WHS_OUT_UUID = #{whsOutUuid} AND PB.ACTUAL_OPERATE_QTY > 0 \n")
    List<Map<String, Object>> getBox(@Param("whsOutUuid") String whsOutUuid);

    @Select("SELECT ROWNUM * 10 AS \"itemNo\",\n" +
            "       RE.PACKAGE_LIST AS \"packageList\",\n" +
            "       RE.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       RE.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "       RE.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       RE.WHS_LOCATION_DESC AS \"whsLocationDesc\",\n" +
            "       RE.LEAD_SEAL AS \"leadSeal\",\n" +
            "       RE.MATERIAL_NO AS \"materialNo\",\n" +
            "       RE.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       RE.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "       RE.CABINET_CHECK_RESULT AS \"cabinetCheckResult\",\n" +
            "       RE.CONTAINER_BRAND AS \"containerBrand\",\n" +
            "       RE.COLOR_NUMBER AS \"colorNumber\",\n" +
            "       RE.THICKNESS AS \"thickness\",\n" +
            "       RE.ACTUAL_AVG_GROSS_WEIGHT AS \"actualAvgGrossWeight\",\n" +
            "       RE.MONITOR_LOADING AS \"monitorLoading\",\n" +
            "       RE.LOADING_TYPE AS \"loadingType\",\n" +
            "       RE.REMARKS AS \"remarks\",\n" +
            "       RE.UNPLANNED AS \"unplanned\",\n" +
            "       RE.WHS_OUT_UUID AS \"whsOutUuid\",\n" +
            "       RE.EMPTY_CABINET_WEIGHT AS \"emptyCabinetWeight\",\n" +
            "       RE.ACTUAL_PACKING_NUMBER AS \"actualPackingNumber\",\n" +
            "       RE.CUSTOMS_DECLARATION_NUMBER AS \"customsDeclarationNumber\",\n" +
            "       RE.PACKING_SPEC AS \"packingSpec\",\n" +
            "       RE.LOADING_TYPE AS \"loadingType\",\n" +
            "       RE.BASIC_UNIT AS \"basicUnit\",\n" +
            "       (CASE WHEN RE.UNIT_SALES IS NULL THEN RE.BASIC_UNIT" +
            "           ELSE RE.UNIT_SALES END) AS \"unitSales\"\n" +
            "FROM (SELECT DISTINCT WO.PACKAGE_LIST, WO.CABINET_NUMBER, WO.ACTUAL_LOADING_DATE, WO.WHS_LOCATION_CODE,\n" +
            "             W.WHS_LOCATION_DESC, WO.LEAD_SEAL, PLD.MATERIAL_NO, SAP.MATERIAL_ZH_DESC, SAP.UNIT_SALES, SAP.BASIC_UNIT,\n" +
            "             PLD.ACTUAL_OPERATE_QTY, WO.CABINET_CHECK_RESULT, PLD.CONTAINER_BRAND, PLD.COLOR_NUMBER,\n" +
            "             PLD.THICKNESS, PLD.ACTUAL_AVG_GROSS_WEIGHT, WO.MONITOR_LOADING, WO.REMARKS,\n" +
            "             PLD.WHS_OUT_UUID, PLD.UNPLANNED, WO.EMPTY_CABINET_WEIGHT, PLD.ACTUAL_PACKING_NUMBER,\n" +
            "             PLD.CUSTOMS_DECLARATION_NUMBER, PLD.PACKING_SPEC, PLD.LOADING_TYPE\n" +
            "    FROM WHS_OUT WO\n" +
            "        LEFT JOIN SAP_PACKAGE_LIST_DTL PLD ON WO.UUID = PLD.WHS_OUT_UUID\n" +
            "        LEFT JOIN WHS W ON WO.WERKS = W.WERKS AND WO.WHS_LOCATION_CODE = W.WHS_LOCATION_CODE\n" +
            "        LEFT JOIN MATERIAL_SAP SAP ON PLD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "    WHERE PLD.ACTUAL_OPERATE_QTY > 0 AND WO.UUID = #{whsOutUuid}) RE \n")
    List<Map<String, Object>> getItemDtlM(@Param("whsOutUuid") String whsOutUuid);

    /***
    @Select("SELECT ROWNUM * 10 AS \"itemNo\",\n" +
            "       RE.PACKAGE_LIST AS \"packageList\",\n" +
            "       RE.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       RE.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "       RE.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       RE.WHS_LOCATION_DESC AS \"whsLocationDesc\",\n" +
            "       RE.LEAD_SEAL AS \"leadSeal\",\n" +
            "       RE.MATERIAL_NO AS \"materialNo\",\n" +
            "       RE.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       RE.DOMESTIC_QTY_RECEIVED AS \"actualOperateQty\",\n" +
            "       RE.CABINET_CHECK_RESULT AS \"cabinetCheckResult\",\n" +
            "       RE.CONTAINER_BRAND AS \"containerBrand\",\n" +
            "       RE.MONITOR_LOADING AS \"monitorLoading\",\n" +
            "       RE.REMARKS AS \"remarks\",\n" +
            "       RE.UUID AS \"whsOutUuid\",\n" +
            "       RE.UNPLANNED AS \"unplanned\",\n" +
            "       RE.EMPTY_CABINET_WEIGHT AS \"emptyCabinetWeight\",\n" +
            "       RE.PACKING_SPEC AS \"packingSpec\",\n" +
            "       RE.LOADING_TYPE AS \"loadingType\",\n" +
            "       RE.BASIC_UNIT AS \"basicUnit\",\n" +
            "       (CASE WHEN RE.UNIT_SALES IS NULL THEN RE.BASIC_UNIT" +
            "           ELSE RE.UNIT_SALES END) AS \"unitSales\",\n" +
            "       (CASE WHEN RE.NUMERATOR IS NULL THEN 1\n" +
            "           ELSE RE.NUMERATOR END) AS \"numerator\",\n" +
            "       (CASE WHEN RE.DENOMINATOR IS NULL THEN 1\n" +
            "           ELSE RE.DENOMINATOR END) AS \"denominator\"\n" +
            "FROM (\n" +
            "    SELECT DISTINCT WO.PACKAGE_LIST, WO.CABINET_NUMBER, WO.ACTUAL_LOADING_DATE, WO.WHS_LOCATION_CODE, W.WHS_LOCATION_DESC,\n" +
            "           WO.LEAD_SEAL, BND.MATERIAL_NO, SAP.MATERIAL_ZH_DESC, BND.DOMESTIC_QTY_RECEIVED, WO.CABINET_CHECK_RESULT,\n" +
            "           PB.CONTAINER_BRAND, WO.MONITOR_LOADING, WO.REMARKS, WO.UUID, PB.UNPLANNED, WO.EMPTY_CABINET_WEIGHT,\n" +
            "           PB.PACKING_SPEC, PB.LOADING_TYPE, SAP.UNIT_SALES, SAP.BASIC_UNIT, SAP.NUMERATOR, SAP.DENOMINATOR" +
            "    FROM WHS_OUT WO\n" +
            "        LEFT JOIN SAP_PACKAGE_BOX PB ON WO.UUID = PB.WHS_OUT_UUID\n" +
            "        LEFT JOIN SAP_BOX_NOTE_DTL BND ON PB.BOX_NOTE = BND.BOX_NOTE\n" +
            "        LEFT JOIN WHS W ON WO.WERKS = W.WERKS AND WO.WHS_LOCATION_CODE = W.WHS_LOCATION_CODE\n" +
            "        LEFT JOIN MATERIAL_SAP SAP ON BND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "    WHERE PB.ACTUAL_OPERATE_QTY > 0 AND WO.UUID = #{whsOutUuid}) RE \n")
    List<Map<String, Object>> getItemDtlX(@Param("whsOutUuid") String whsOutUuid);
     备件货出库结果提交 SAP 逻辑按下面方法重写，一个物料多个箱的情形考虑在SQL语句中求和 ———— 20210816
     ***/

    /***
    @Select("SELECT ROWNUM * 10 AS \"itemNo\",\n" +
            "       RE.PACKAGE_LIST AS \"packageList\",\n" +
            "       RE.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       RE.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "       RE.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       RE.WHS_LOCATION_DESC AS \"whsLocationDesc\",\n" +
            "       RE.LEAD_SEAL AS \"leadSeal\",\n" +
            "       RE.MATERIAL_NO AS \"materialNo\",\n" +
            "       RE.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       RE.DOMESTIC_QTY_RECEIVED AS \"actualOperateQty\",\n" +
            "       RE.CABINET_CHECK_RESULT AS \"cabinetCheckResult\",\n" +
            "       RE.CONTAINER_BRAND AS \"containerBrand\",\n" +
            "       RE.MONITOR_LOADING AS \"monitorLoading\",\n" +
            "       RE.REMARKS AS \"remarks\",\n" +
            "       RE.UUID AS \"whsOutUuid\",\n" +
            "       RE.UNPLANNED AS \"unplanned\",\n" +
            "       RE.EMPTY_CABINET_WEIGHT AS \"emptyCabinetWeight\",\n" +
            "       RE.PACKING_SPEC AS \"packingSpec\",\n" +
            "       RE.LOADING_TYPE AS \"loadingType\",\n" +
            "       RE.BASIC_UNIT AS \"basicUnit\",\n" +
            "       (CASE WHEN RE.UNIT_SALES IS NULL THEN RE.BASIC_UNIT" +
            "           ELSE RE.UNIT_SALES END) AS \"unitSales\",\n" +
            "       (CASE WHEN RE.NUMERATOR IS NULL THEN 1\n" +
            "           ELSE RE.NUMERATOR END) AS \"numerator\",\n" +
            "       (CASE WHEN RE.DENOMINATOR IS NULL THEN 1\n" +
            "           ELSE RE.DENOMINATOR END) AS \"denominator\"\n" +
            "FROM (\n" +
            "    SELECT WO.PACKAGE_LIST, WO.CABINET_NUMBER, WO.ACTUAL_LOADING_DATE, WO.WHS_LOCATION_CODE, W.WHS_LOCATION_DESC, WO.LEAD_SEAL,\n" +
            "       BND.MATERIAL_NO, SAP.MATERIAL_ZH_DESC, SUM(BND.DOMESTIC_QTY_RECEIVED) AS \"DOMESTIC_QTY_RECEIVED\", WO.CABINET_CHECK_RESULT,\n" +
            "       PB.CONTAINER_BRAND, WO.MONITOR_LOADING, WO.REMARKS, WO.UUID, PB.UNPLANNED, WO.EMPTY_CABINET_WEIGHT, PB.PACKING_SPEC,\n" +
            "       PB.LOADING_TYPE, SAP.UNIT_SALES, SAP.BASIC_UNIT, SAP.NUMERATOR, SAP.DENOMINATOR\n" +
            "    FROM WHS_OUT WO\n" +
            "       LEFT JOIN SAP_PACKAGE_BOX PB ON WO.UUID = PB.WHS_OUT_UUID\n" +
            "       LEFT JOIN SAP_BOX_NOTE_DTL BND ON PB.BOX_NOTE = BND.BOX_NOTE\n" +
            "       LEFT JOIN WHS W ON WO.WERKS = W.WERKS AND WO.WHS_LOCATION_CODE = W.WHS_LOCATION_CODE\n" +
            "       LEFT JOIN MATERIAL_SAP SAP ON BND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "    WHERE PB.ACTUAL_OPERATE_QTY > 0 AND WO.UUID = #{whsOutUuid} \n" +
            "    GROUP BY BND.MATERIAL_NO, WO.PACKAGE_LIST, WO.CABINET_NUMBER, WO.ACTUAL_LOADING_DATE, WO.WHS_LOCATION_CODE,\n" +
            "         W.WHS_LOCATION_DESC, WO.LEAD_SEAL, SAP.MATERIAL_ZH_DESC, WO.CABINET_CHECK_RESULT, PB.CONTAINER_BRAND,\n" +
            "         WO.MONITOR_LOADING, WO.REMARKS, WO.UUID, PB.UNPLANNED, WO.EMPTY_CABINET_WEIGHT, PB.PACKING_SPEC, PB.LOADING_TYPE,\n" +
            "         SAP.UNIT_SALES, SAP.BASIC_UNIT, SAP.NUMERATOR, SAP.DENOMINATOR) RE \n")
    List<Map<String, Object>> getItemDtlX(@Param("whsOutUuid") String whsOutUuid);
     * 箱内物料提交SAP时重复统计——20210914
     ***/

    @Select("SELECT ROWNUM * 10 AS \"itemNo\",\n" +
            "       RESULT.*\n" +
            "FROM (\n" +
            "         SELECT RE.PACKAGE_LIST AS \"packageList\",\n" +
            "                RE.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "                RE.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "                RE.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                RE.WHS_LOCATION_DESC AS \"whsLocationDesc\",\n" +
            "                RE.LEAD_SEAL AS \"leadSeal\",\n" +
            "                RE.MATERIAL_NO AS \"materialNo\",\n" +
            "                RE.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                SUM(RE.DOMESTIC_QTY_RECEIVED)  AS \"actualOperateQty\",\n" +
            "                RE.CABINET_CHECK_RESULT AS \"cabinetCheckResult\",\n" +
            "                RE.CONTAINER_BRAND AS \"containerBrand\",\n" +
            "                RE.MONITOR_LOADING AS \"monitorLoading\",\n" +
            "                RE.REMARKS AS \"remarks\",\n" +
            "                RE.UUID AS \"whsOutUuid\",\n" +
            "                RE.UNPLANNED AS \"unplanned\",\n" +
            "                RE.EMPTY_CABINET_WEIGHT AS \"emptyCabinetWeight\",\n" +
            "                RE.PACKING_SPEC AS \"packingSpec\",\n" +
            "                RE.LOADING_TYPE AS \"loadingType\",\n" +
            "                RE.BASIC_UNIT AS \"basicUnit\",\n" +
            "                RE.UNIT_SALES AS \"unitSales\",\n" +
            "                RE.NUMERATOR AS \"numerator\",\n" +
            "                RE.DENOMINATOR AS \"denominator\",\n" +
            "                TO_CHAR(RE.ITEM) AS \"boxNoteItem\",\n" +
            "                RE.BOX_NOTE AS \"boxNote\"\n" +
            "         FROM (\n" +
            "                  SELECT DISTINCT WO.PACKAGE_LIST, WO.CABINET_NUMBER, WO.ACTUAL_LOADING_DATE, WO.WHS_LOCATION_CODE, W.WHS_LOCATION_DESC, WO.LEAD_SEAL,\n" +
            "                                  BND.MATERIAL_NO, SAP.MATERIAL_ZH_DESC, BND.DOMESTIC_QTY_RECEIVED, WO.CABINET_CHECK_RESULT,\n" +
            "                                  PB.CONTAINER_BRAND, WO.MONITOR_LOADING, WO.REMARKS, WO.UUID, PB.UNPLANNED, WO.EMPTY_CABINET_WEIGHT, PB.PACKING_SPEC,\n" +
            "                                  PB.LOADING_TYPE, SAP.BASIC_UNIT, BND.ITEM, BND.BOX_NOTE,\n" +
            "                                  (CASE WHEN SAP.UNIT_SALES IS NULL THEN SAP.BASIC_UNIT ELSE SAP.UNIT_SALES END) AS \"UNIT_SALES\",\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" +
            "                  FROM WHS_OUT WO\n" +
            "                           LEFT JOIN SAP_PACKAGE_BOX PB ON WO.UUID = PB.WHS_OUT_UUID\n" +
            "                           LEFT JOIN SAP_BOX_NOTE_DTL BND ON PB.BOX_NOTE = BND.BOX_NOTE\n" +
            "                           LEFT JOIN WHS W ON WO.WERKS = W.WERKS AND WO.WHS_LOCATION_CODE = W.WHS_LOCATION_CODE\n" +
            "                           LEFT JOIN MATERIAL_SAP SAP ON BND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "                  WHERE PB.ACTUAL_OPERATE_QTY > 0 AND BND.DOMESTIC_QTY_RECEIVED > 0 AND WO.UUID = #{whsOutUuid} \n" +
            "              ) RE GROUP BY RE.PACKAGE_LIST, RE.CABINET_NUMBER, RE.ACTUAL_LOADING_DATE, RE.WHS_LOCATION_CODE, RE.WHS_LOCATION_DESC,\n" +
            "                            RE.LEAD_SEAL, RE.MATERIAL_NO, RE.MATERIAL_ZH_DESC, RE.CABINET_CHECK_RESULT, RE.CONTAINER_BRAND, RE.MONITOR_LOADING,\n" +
            "                            RE.REMARKS, RE.UUID, RE.UNPLANNED, RE.EMPTY_CABINET_WEIGHT, RE.PACKING_SPEC, RE.LOADING_TYPE, RE.BASIC_UNIT,\n" +
            "                            RE.UNIT_SALES, RE.NUMERATOR, RE.DENOMINATOR, RE.ITEM, RE.BOX_NOTE\n" +
            "         ) RESULT ")
    List<Map<String, Object>> getItemDtlX(@Param("whsOutUuid") String whsOutUuid);

    @Update("UPDATE WHS_OUT SET TASK_STATUS = #{taskStatus} WHERE UUID = #{whsOutUuid} ")
    int updateWhsOut(@Param("whsOutUuid") String whsOutUuid, @Param("taskStatus") String taskStatus);

    @Select("SELECT COUNT(1) FROM WHS_OUT WO WHERE WO.PACKAGE_LIST = (\n" +
            "    SELECT PACKAGE_LIST FROM WHS_OUT WHERE UUID = #{uuid}\n" +
            "    ) AND WO.TASK_STATUS != '4' AND WO.TASK_STATUS != '3'\n")
    String getWhsOutStatusCount(@Param("uuid") String uuid);

    @Update("UPDATE SAP_PACKAGE_LIST PL SET PL.SPMS_STATUS = #{targetStatus} WHERE PL.PACKAGE_LIST = #{packageList} ")
    int updateSapPackageList(@Param("packageList") String packageList, @Param("targetStatus") String targetStatus);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RESULT.*\n" +
            "FROM (\n" +
            "    SELECT RE.*\n" +
            "         FROM (\n" +
            "                  SELECT PLD.PACKAGE_LIST AS \"packageList\",\n" +
            "                         PLD.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "                         WO.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "                         PLD.WHS_OUT_UUID AS \"whsOutUuid\",\n" +
            "                         WO.WHS_OUT_NO AS \"whsOutNo\",\n" +
            "                         WO.TASK_STATUS AS \"taskStatus\",\n" +
            "                         PLD.GOODS_TYPE AS \"goodsType\",\n" +
            "                         PLD.DATE_OF_LOADING AS \"dateOfLoading\",\n" +
            "                         PLD.LOADING_WERKS AS \"loadingWerks\",\n" +
            "                         PLD.LOADING_WHS_LOCATION_CODE AS \"loadingWhsLocationCode\",\n" +
            "                         PLD.MATERIAL_NO AS \"materialNoOrBoxNote\",\n" +
            "                         SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                         PLD.PLANNED_PACKING_QTY AS \"plannedPackingQty\",\n" +
            "                         null AS \"actualOperateQty\",\n" +
            "                         SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "                         PLD.PLANNED_PACKING_NUMBER AS \"plannedPackingNumber\",\n" +
            "                         null AS \"actualPackingNumber\",\n" +
            "                         PLD.TEMP_STORAGE AS \"tempStorage\",\n" +
            "                         PLD.RECEIVING_WERKS AS \"receivingWerks\",\n" +
            "                         (CASE WHEN SAP.UNIT_SALES IS NULL THEN SAP.BASIC_UNIT ELSE SAP.UNIT_SALES END) AS \"unitSales\",\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" +
            "                         (SELECT ListAgg(WS.STORAGE_NO||'-'||WSI.QUANTITY||'-'||WS.REMARKS, ',') within group ( order by WS.STORAGE_NO) AS RECOMMEND_STORAGE\n" +
            "                            FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                            LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID AND WS.WERKS = #{werks} AND WS.WHS_LOCATION_CODE = #{whsLocationCode} \n" +
            "                            WHERE WSI.MATERIAL_NO = PLD.MATERIAL_NO)\n" +
            "                         AS \"recommendStorage\"\n" +
            "                  FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "                           LEFT JOIN MATERIAL_SAP SAP ON PLD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "                           LEFT JOIN WHS_OUT WO ON WO.UUID = PLD.WHS_OUT_UUID\n" +
            "                  WHERE PLD.PACKAGE_LIST = #{packageList} AND PLD.GOODS_TYPE = 'M'\n" +
            "                    <when test='cabinetSerialNumber!=null'> AND PLD.CABINET_SERIAL_NUMBER = #{cabinetSerialNumber} </when>\n" +
            "                  UNION ALL\n" +
            "                  SELECT DISTINCT PLD.PACKAGE_LIST AS \"packageList\",\n" +
            "                                  PLD.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "                                  WO.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "                                  PLD.WHS_OUT_UUID AS \"whsOutUuid\",\n" +
            "                                  WO.WHS_OUT_NO AS \"whsOutNo\",\n" +
            "                                  WO.TASK_STATUS AS \"taskStatus\",\n" +
            "                                  PLD.GOODS_TYPE AS \"goodsType\",\n" +
            "                                  PLD.DATE_OF_LOADING AS \"dateOfLoading\",\n" +
            "                                  PLD.LOADING_WERKS AS \"loadingWerks\",\n" +
            "                                  PLD.LOADING_WHS_LOCATION_CODE AS \"loadingWhsLocationCode\",\n" +
            "                                  PLD.PACKAGE_CODE AS \"materialNoOrBoxNote\",\n" +
            "                                  null AS \"materialZhDesc\",\n" +
            "                                  1 AS \"plannedPackingQty\",\n" +
            "                                  PLD.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "                                  null AS \"basicUnit\",\n" +
            "                                  null AS \"plannedPackingNumber\",\n" +
            "                                  null AS \"actualPackingNumber\",\n" +
            "                                  PLD.TEMP_STORAGE AS \"tempStorage\",\n" +
            "                                  PLD.RECEIVING_WERKS AS \"receivingWerks\",\n" +
            "                                  null AS \"unitSales\",\n" +
            "                                  null AS \"numerator\",\n" +
            "                                  null AS \"denominator\",\n" +
            "                                  (SELECT REX.STORAGE_NO ||'-'||REX.QUANTITY||'-'||REX.REMARKS\n" +
            "                                   FROM (   SELECT WSI.BOX_NOTE, WS.STORAGE_NO, WS.REMARKS, WSI.QUANTITY FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                                                     LEFT JOIN WHS_STORAGE WS\n" +
            "                                                         ON WSI.UUID = WS.UUID AND WS.WERKS = #{werks} AND WS.WHS_LOCATION_CODE = #{whsLocationCode} )\n" +
            "                                       REX WHERE REX.BOX_NOTE = PLD.PACKAGE_CODE AND ROWNUM = 1) AS \"recommendStorage\"\n" +
            "                  FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "                           LEFT JOIN WHS_OUT WO ON WO.UUID = PLD.WHS_OUT_UUID\n" +
            "                  WHERE PLD.PACKAGE_LIST = #{packageList} AND PLD.GOODS_TYPE = 'X'\n" +
            "                    <when test='cabinetSerialNumber!=null'> AND PLD.CABINET_SERIAL_NUMBER = #{cabinetSerialNumber} </when>\n" +
            "              ) RE WHERE \"loadingWerks\" = #{werks} AND \"loadingWhsLocationCode\" = #{whsLocationCode} \n" +
            "    ORDER BY RE.\"goodsType\" ASC, RE.\"materialNoOrBoxNote\" ASC\n" +
            "         ) RESULT " +
            "</script>")
    List<Map<String, Object>> getWhsOutDtlList(@Param("packageList") String packageList,
                                               @Param("cabinetSerialNumber") String cabinetSerialNumber,
                                               @Param("werks") String werks,
                                               @Param("whsLocationCode") String whsLocationCode);

    @Select("<script>" +
            "SELECT WO.UUID AS \"uuid\",\n" +
            "       WO.WHS_OUT_NO AS \"whsOutNo\",\n" +
            "       WO.WERKS AS \"werks\",\n" +
            "       WO.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       WO.PACKAGE_LIST AS \"packingList\",\n" +
            "       WO.GOODS_TYPE AS \"goodsType\",\n" +
            "       WO.DATE_OF_LOADING AS \"dateOfLoading\",\n" +
            "       WO.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       WO.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "       WO.TASK_STATUS AS \"taskStatus\",\n" +
            "       WO.CREATE_USERID AS \"createUserId\",\n" +
            "       WO.CREATE_DATE AS \"createDate\",\n" +
            "       WO.CREATE_TIME AS \"createTime\",\n" +
            "       WO.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       WO.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       WO.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "       WO.MONITOR_LOADING AS \"monitorLoading\",\n" +
            "       WO.LOADING_TYPE AS \"loadingType\",\n" +
            "       WO.REMARKS AS \"remarks\",\n" +
            "       WO.CABINET_CHECK_RESULT AS \"cabinetCheckResult\",\n" +
            "       WO.LEAD_SEAL AS \"leadSeal\",\n" +
            "       WO.EMPTY_CABINET_WEIGHT AS \"emptyCabinetWeight\",\n" +
            "       WO.SHIPPER AS \"shipper\",\n" +
            "       WO.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       WO.OPERATE_USER AS \"operateUser\",\n" +
            "       WO.DATA_SOURCE AS \"dataSource\",\n" +
            "       (SELECT LISTAGG(SOT.TASK_ID, ',')WITHIN GROUP ( ORDER BY SOT.RELATE_DOC_NUMBER)\n" +
            "       FROM SHELF_ON_TASK SOT WHERE SOT.RELATE_DOC_NUMBER IS NOT NULL AND SOT.RELATE_DOC_NUMBER = WO.UUID)\n" +
            "           AS \"shelfOnTaskNo\"\n" +
            "FROM WHS_OUT WO WHERE 1=1\n" +
            "       AND WO.WERKS IN " +
            "       <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "       AND WO.WHS_LOCATION_CODE IN " +
            "       <foreach item='item' index='index' collection='whsLocationCode' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "       <when test='whsOutNo!=null'> AND WO.WHS_OUT_NO = #{whsOutNo} </when>\n" +
            "       <when test='packageList!=null'> AND WO.PACKAGE_LIST = #{packageList} </when>\n" +
            "       <when test='goodsType!=null'> AND WO.GOODS_TYPE = #{goodsType} </when>\n" +
            "       <when test='dateOfLoading!=null'> AND WO.DATE_OF_LOADING = #{dateOfLoading} </when>\n" +
            "       <when test='cabinetSerialNumber!=null'> AND WO.CABINET_SERIAL_NUMBER = #{cabinetSerialNumber} </when>\n" +
            "       <when test='cabinetNumber!=null'> AND WO.CABINET_NUMBER = #{cabinetNumber} </when>\n" +
            "       <when test='shipper!=null'> AND WO.SHIPPER = #{shipper} </when>\n" +
            "       <when test='taskStatus!=null'> " +
            "           AND WO.TASK_STATUS IN " +
            "               <foreach item='item' index='index' collection='taskStatus' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "       </when>\n" +
            "       ORDER BY DECODE(WO.TASK_STATUS, '0', 1, '1', 2, '2', 3, '5', 4, '4', 5, '3', 6), WO.CREATE_DATE DESC " +
            "</script>")
    List<Map<String, Object>> getWhsOutListPage(Page<Map<String, Object>> pages,
                                            @Param("werks") List<String> werks,
                                            @Param("whsLocationCode") List<String> whsLocationCode,
                                            @Param("whsOutNo") String whsOutNo,
                                            @Param("packageList") String packageList,
                                            @Param("goodsType") String goodsType,
                                            @Param("dateOfLoading") String dateOfLoading,
                                            @Param("cabinetSerialNumber") String cabinetSerialNumber,
                                            @Param("taskStatus") List<String> taskStatus, 
                                            @Param("cabinetNumber") String cabinetNumber, 
                                            @Param("shipper") String shipper);


    @Select("SELECT DISTINCT SPB.BOX_NOTE AS \"packageCode\",\n" +
            "                SPB.PACKAGE_LIST AS \"xdnum\"\n" +
            "FROM SAP_PACKAGE_BOX SPB\n" +
            "    LEFT JOIN SAP_PACKAGE_LIST SPL ON SPB.PACKAGE_LIST = SPL.PACKAGE_LIST\n" +
            "WHERE SPB.PACKAGE_LIST = #{packageList} \n" +
            "  AND SPB.ACTUAL_OPERATE_QTY > 0 \n" +
            "  AND SPL.SPMS_STATUS != '3' \n" +
            "UNION ALL\n " +
            "SELECT DISTINCT WOD.BOX_NOTE AS \"packageCode\",\n" +
            "                WOD.PACKAGE_LIST AS \"xdnum\"\n " +
            "FROM WHS_OTHER_DTL WOD\n" +
            "    LEFT JOIN WHS_OTHER_TASK WOT ON WOD.WHS_OTHER_UUID = WOT.UUID\n" +
            "WHERE WOT.PACKAGE_LIST = #{packageList} \n" +
            "  AND WOD.BOX_NOTE IS NOT NULL AND WOD.OPERATE_QTY > 0\n" +
            "  AND WOT.TASK_STATUS != '0' AND WOT.TASK_STATUS != '7' ")
    List<Map<String, Object>> getPackageListBoxNote(@Param("packageList") String packageList);


    @Update("UPDATE SAP_PACKAGE_LIST PL SET PL.SPMS_STATUS = #{packageListTaskStatus} \n" +
            "WHERE PL.PACKAGE_LIST = #{packageList} \n")
    void updatePLTaskStatus(@Param("packageList") String packageList, @Param("packageListTaskStatus") String packageListTaskStatus);
    
    
    @Select("SELECT COUNT(1) FROM WHS_OUT WO WHERE WO.PACKAGE_LIST = (\n" +
            "    SELECT PACKAGE_LIST FROM WHS_OUT WHERE UUID = #{uuid}\n" +
            "    ) \n")
    int getWhsOutAllStatusCount(@Param("uuid") String whsOutUuid);
    
    @Select("SELECT COUNT(1) FROM WHS_OUT WO WHERE WO.PACKAGE_LIST = (\n" +
    		"    SELECT PACKAGE_LIST FROM WHS_OUT WHERE UUID = #{uuid}\n" +
    		"    ) AND WO.TASK_STATUS = '4' \n")
    int getWhsOutSapStatusCount(@Param("uuid") String whsOutUuid);
    
    @Select("SELECT COUNT(1) FROM WHS_OUT WO WHERE WO.PACKAGE_LIST = (\n" +
    		"    SELECT PACKAGE_LIST FROM WHS_OUT WHERE UUID = #{uuid}\n" +
    		"    ) AND WO.TASK_STATUS = '3' \n")
    int getWhsOutCloseStatusCount(@Param("uuid") String whsOutUuid);
    
    @Select(
    	"	SELECT PACKAGE_CODE AS \"boxNote\",\n" +
    	"		MATERIAL_NO AS \"materialNo\"\n" +
    	"	FROM SAP_PACKAGE_LIST_DTL \n" +
    	"	WHERE GOODS_TYPE = 'X' AND (UNPLANNED IS NULL OR UNPLANNED != '1') \n" +
    	"	AND WHS_OUT_UUID = #{uuid} \n" +
    	"	UNION ALL \n" +
    	"	SELECT SBN.BOX_NOTE AS \"boxNote\",\n" +
    	"		SBN.MATERIAL_NO AS \"materialNo\"\n" +
    	"	FROM SAP_PACKAGE_LIST_DTL SPL \n" +
    	"		LEFT JOIN SAP_BOX_NOTE_DTL SBN ON SPL.PACKAGE_CODE = SBN.BOX_NOTE" +
    	"	WHERE SPL.GOODS_TYPE = 'X' AND SPL.UNPLANNED = '1' \n" +
    	"	AND SPL.WHS_OUT_UUID = #{uuid} \n" 
    )
	List<Map<String, Object>> selectBoxNoteAndMaterial(@Param("uuid") String whsOutUuid);
    
    @Select(
    		"SELECT MATERIAL_NO as \"materialNo\",\n" +
    		"	MATERIAL_ZH_DESC as \"materialDesc\",\n" +
    		"	BASIC_UNIT as \"basicUnit\",\n" +
    		"	UNIT_SALES as \"unitSales\",\n" +
    		"	NET_WEIGHT as \"netWeight\",\n" +
    		"	GROSS_WEIGHT as \"grossWeight\",\n" +
    		"	WEIGHT_UNIT as \"weightUnit\",\n" +
    		"	VOLUME_QTY as \"volumeQty\",\n" +
    		"	VOLUME_UNIT as \"volumeUnit\",\n" +
    		"	NUMERATOR as \"numerator\",\n" +
    		"	DENOMINATOR as \"denominator\"\n" +
    		"FROM MATERIAL_SAP WHERE MATERIAL_NO = #{materialNo}\n" 
    )
	Map<String, Object> selectMaterial(@Param("materialNo") String materialNo);
    
    @Select(
    		"SELECT \n" +
    		"	WS.UUID as \"uuid\",\n" +
    		"	WS.STORAGE_NO as \"storageNo\"\n" +
    		"FROM WHS_STORAGE WS  \n" +
    		"LEFT JOIN WHS_STORAGE_INVENTORY WSI ON WS.UUID = WSI.UUID \n" +
    		"WHERE WSI.BOX_NOTE = #{packCode}"
    )
	Map<String, String> selectStorageNo(@Param("packCode") String packCode);

    @Select("<script>" +
            "SELECT WO.UUID AS \"uuid\",\n" +
            "       WO.WHS_OUT_NO AS \"whsOutNo\",\n" +
            "       WO.WERKS AS \"werks\",\n" +
            "       WO.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       WO.PACKAGE_LIST AS \"packingList\",\n" +
            "       WO.GOODS_TYPE AS \"goodsType\",\n" +
            "       WO.DATE_OF_LOADING AS \"dateOfLoading\",\n" +
            "       WO.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       WO.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "       WO.TASK_STATUS AS \"taskStatus\",\n" +
            "       WO.CREATE_USERID AS \"createUserId\",\n" +
            "       WO.CREATE_DATE AS \"createDate\",\n" +
            "       WO.CREATE_TIME AS \"createTime\",\n" +
            "       WO.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       WO.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       WO.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "       WO.MONITOR_LOADING AS \"monitorLoading\",\n" +
            "       WO.LOADING_TYPE AS \"loadingType\",\n" +
            "       WO.REMARKS AS \"remarks\",\n" +
            "       WO.CABINET_CHECK_RESULT AS \"cabinetCheckResult\",\n" +
            "       WO.LEAD_SEAL AS \"leadSeal\",\n" +
            "       WO.EMPTY_CABINET_WEIGHT AS \"emptyCabinetWeight\",\n" +
            "       WO.SHIPPER AS \"shipper\",\n" +
            "       WO.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       WO.OPERATE_USER AS \"operateUser\",\n" +
            "       WO.DATA_SOURCE AS \"dataSource\",\n" +
            "       (SELECT LISTAGG(SOT.TASK_ID, ',')WITHIN GROUP ( ORDER BY SOT.RELATE_DOC_NUMBER)\n" +
            "       FROM SHELF_ON_TASK SOT WHERE SOT.RELATE_DOC_NUMBER IS NOT NULL AND SOT.RELATE_DOC_NUMBER = WO.UUID)\n" +
            "           AS \"shelfOnTaskNo\"\n" +
            "FROM WHS_OUT WO WHERE 1=1\n" +
            "       AND WO.PACKAGE_LIST = #{packageList} \n " +
            "       AND WO.GOODS_TYPE = 'X' \n " +
            "       <when test='whsOutNo!=null'> AND WO.WHS_OUT_NO = #{whsOutNo} </when>\n" +
            "       <when test='goodsType!=null'> AND WO.GOODS_TYPE = #{goodsType} </when>\n" +
            "       <when test='dateOfLoading!=null'> AND WO.DATE_OF_LOADING = #{dateOfLoading} </when>\n" +
            "       <when test='cabinetSerialNumber!=null'> AND WO.CABINET_SERIAL_NUMBER = #{cabinetSerialNumber} </when>\n" +
            "       <when test='cabinetNumber!=null'> AND WO.CABINET_NUMBER = #{cabinetNumber} </when>\n" +
            "       <when test='shipper!=null'> AND WO.SHIPPER = #{shipper} </when>\n" +
            "       <when test='taskStatus!=null'> " +
            "           AND WO.TASK_STATUS IN " +
            "               <foreach item='item' index='index' collection='taskStatus' open='(' separator=',' close=')'>\n" +
            "                   #{item} \n" +
            "               </foreach>" +
            "       </when>\n" +
            "       ORDER BY DECODE(WO.TASK_STATUS, '0', 1, '1', 2, '2', 3, '5', 4, '4', 5, '3', 6), WO.CREATE_DATE DESC " +
            "</script>")
    List<Map<String, Object>> getWhsOutListPagePda(Page<Map<String, Object>> pages,
                                            @Param("whsOutNo") String whsOutNo,
                                            @Param("packageList") String packageList,
                                            @Param("goodsType") String goodsType,
                                            @Param("dateOfLoading") String dateOfLoading,
                                            @Param("cabinetSerialNumber") String cabinetSerialNumber,
                                            @Param("taskStatus") List<String> taskStatus, 
                                            @Param("cabinetNumber") String cabinetNumber, 
                                            @Param("shipper") String shipper);
    
    
    @Select("SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "    SELECT DISTINCT PLD.PACKAGE_LIST AS \"packageList\",\n" +
//            "       		  ROWNUM AS \"rowId\",\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.RECEIVING_WERKS AS \"receivingWerks\",\n" +
            "       		  PLD.ACTUAL_RECEIVING_WERKS AS \"actualReceivingWerks\",\n" +
            "                 PLD.CABINET_SERIAL_NUMBER AS \"cabinetSerialNumber\",\n" +
            "                 PLD.GOODS_TYPE AS \"goodsType\",\n" +
//            "       		  PLD.SPMS_ITEM_NO AS \"spmsItemNo\",\n" +
            "       		  PLD.SHIPPER AS \"shipper\",\n" +
            "       		  PLD.CONSIGNEE AS \"consignee\",\n" +
            "      			  PLD.BUKRS AS \"bukrs\",\n" +
            "      			  PLD.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       		  PLD.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       		  PLD.LEAD_SEAL AS \"leadSeal\",\n" +
            "       		  PLD.SHIPMENT_CATEGORY AS \"shipmentCategory\",\n" +
            "       		  '1' AS \"plannedPackingQty\",\n" +
            "       		  '1' AS \"plannedPackingNumber\",\n" +
//            "       		  PLD.PACKING_UNIT AS \"packingUnit\",\n" +
            "       		  PLD.CABINET_WEIGHT AS \"cabinetWeight\",\n" +
            "       		  PLD.WEIGHT_UNIT AS \"cabinetWeightUnit\",\n" +
            "       		  PLD.CABINET_VOLUME AS \"cabinetVolume\",\n" +
            "       		  PLD.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "       		  PLD.TRAILER_LINE AS \"trailerLine\",\n" +
            "       		  PLD.TRAILER_LINE_DESC AS \"trailerLineDesc\",\n" +
            "       		  PLD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "      			  PLD.COLOR_NUMBER AS \"colorNumber\",\n" +
            "       		  PLD.THICKNESS AS \"thickness\",\n" +
            "       		  PLD.ACTUAL_AVG_GROSS_WEIGHT AS \"actualAvgGrossWeight\",\n" +
            "      			  PLD.REMARKS AS \"remarks\",\n" +
            "      			  PLD.CONTAINER_LOADING_POSITION AS \"containerLoadingPosition\",\n" +
            "      			  PLD.BATCH_NO AS \"batchNo\",\n" +
            "      			  PLD.PACKAGE_QUANTITY_MAX AS \"packageQuantityMax\",\n" +
            "      			  PLD.PACKAGE_UNIT_MAX AS \"packageUnitMax\",\n" +
            "                 PLD.PACKAGE_CODE AS \"boxNote\",\n" +
            "                 PLD.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "       		  PLD.ACTUAL_PACKING_NUMBER AS \"actualPackingNumber\",\n" +
            "       		  PLD.CUSTOMS_DECLARATION_NUMBER AS \"customsDeclarationNumber\",\n" +
            "                 PLD.UNPLANNED AS \"unplanned\",\n" +
            "                 PLD.CONTAINER_BRAND AS \"containerBrand\",\n" +
            "                 PLD.PACKING_SPEC AS \"packingSpec\",\n" +
            "                 PLD.LOADING_TYPE AS \"loadingType\",\n" +
            "                 PLD.NET_WEIGHT AS \"netWeight\",\n" +
            "                 WS.UUID AS \"targetStorage\",\n" +
            "                 WS.STORAGE_NO AS \"storageNo\",\n" +
            "                 PLD.WHS_OUT_UUID AS \"whsOutUuid\"\n" +
            "    FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "		LEFT JOIN ( \n" +
            "			SELECT UUID,RELATE_DOC_NUMBER FROM (\n" +
            "				SELECT UUID,RELATE_DOC_NUMBER from SHELF_ON_TASK WHERE RELATE_DOC_NUMBER = #{uuid} ORDER BY CREATE_TIME desc" +
            "			) dd WHERE ROWNUM <= 1 \n" +
            "		) SS on PLD.WHS_OUT_UUID = SS.RELATE_DOC_NUMBER \n" +
            "		LEFT JOIN SHELF_ON_TASK_DTL SOTD\n" +
            "		ON PLD.PACKAGE_CODE = SOTD.BOX_NOTE AND SS.UUID = SOTD.UUID" +
            //"        LEFT JOIN WHS_STORAGE_INVENTORY WSI ON SOTD.BOX_NOTE = WSI.BOX_NOTE\n" +
            "        LEFT JOIN WHS_STORAGE WS ON SOTD.TARGET_STORAGE = WS.UUID\n" +
            "    WHERE PLD.WHS_OUT_UUID = #{uuid} ORDER BY PLD.PACKAGE_CODE ASC \n" +
            ") RE ")
    List<Map<String, Object>> selectPackageListDtlX(@Param("uuid") String uuid);
}