package com.sunda.spmswms.mapper;

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

    /** 其他移入移出提交 SAP 表头行结构获取 */
    @Select("SELECT WOT.WERKS_MOVE_IN AS \"WERKS\",\n" +
            //"       WOT.SPMS_ID || WOT.REMARKS AS \"SPMSID\",\n" +
            "       WOT.WHS_LOCATION_CODE_MOVE_IN AS \"LGORT\",\n" +
            "       REPLACE(WOT.CREATE_DATE, '-', '') AS \"BLDAT\",\n" +
            "       REPLACE(WOT.POSTING_DATE, '-', '') AS \"BUDAT\",\n" +
            "       WOT.SAP_MOVEMENT_TYPE AS \"BWART\",\n" +
            "       WOT.SPMS_ID AS \"XBLNR\",\n" +
            "       WOT.SPMS_ID ||' '|| WOT.REMARKS AS \"BKTXT\"\n" +
            "FROM WHS_OTHER_UNLOAD WOT WHERE UUID = #{uuid} ")
    Map<String, Object> moveOut311DataToSapHead(@Param("uuid") String uuid);

    @Select("SELECT TO_CHAR(WOD.ROW_ID) AS \"ZEILE\",\n" +
            "       WOD.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(WOD.OPERATE_QTY, 'fm9999999990.000') AS \"MENGE\",\n" +
            "       SAP.BASIC_UNIT AS \"MEINS\",\n" +
            "       '' AS \"LIFNR\",\n" +
            "       WOD.COST_CENTER AS \"KOSTL\",\n" +
            "       WOT.WERKS_MOVE_OUT AS \"WERKS\",\n" +
            "       WOT.WHS_LOCATION_CODE_MOVE_OUT AS \"LGORT\",\n" +
            "       WOD.ROW_REMARKS AS \"SGTXT\"\n" +
            "FROM WHS_OTHER_Unload_DTL WOD LEFT JOIN WHS_OTHER_UNLOAD WOT ON WOT.UUID = WOD.WHS_OTHER_UUID\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON WOD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE WOD.WHS_OTHER_UUID = #{uuid} ")
    List<Map<String, Object>> moveOut311DataToSapItem(@Param("uuid") String uuid);

    /** 领用/报废出库提交 SAP 表头行结构获取 */
    @Select("SELECT WOT.WERKS AS \"WERKS\",\n" +
            "       WOT.WHS_LOCATION_CODE AS \"LGORT\",\n" +
//            "       WOT.SPMS_ID || WOT.REMARKS AS \"SPMSID\",\n" +
            "       REPLACE(WOT.CREATE_DATE, '-', '') AS \"BLDAT\",\n" +
            "       REPLACE(WOT.POSTING_DATE, '-', '') AS \"BUDAT\",\n" +
            "       WOT.SAP_MOVEMENT_TYPE AS \"BWART\",\n" +
            "       WOT.SPMS_ID AS \"XBLNR\",\n" +
            "       WOT.SPMS_ID ||' '|| WOT.REMARKS AS \"BKTXT\"\n" +
            "FROM WHS_OTHER_UNLOAD WOT WHERE UUID = #{uuid} \n")
    Map<String, Object> acquistionAndScrapDataToSapHead(@Param("uuid") String uuid);

    @Select("SELECT TO_CHAR(WOD.ROW_ID) AS \"ZEILE\",\n" +
            "       WOD.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(WOD.OPERATE_QTY, 'fm9999999990.000') AS \"MENGE\",\n" +
            "       SAP.BASIC_UNIT AS \"MEINS\",\n" +
            "       WOD.SUPPLIER_NO AS \"LIFNR\",\n" +
            "       WOD.COST_CENTER AS \"KOSTL\",\n" +
            "       WOD.WERKS AS \"WERKS\",\n" +
            "       WOD.WHS_LOCATION_CODE AS \"LGORT\",\n" +
            "       TO_CHAR(NVL(WOT.JUSTIFICATION, '0')) AS \"GRUND\",\n" +
            "       WOD.ROW_REMARKS AS \"SGTXT\"\n" +
            "FROM WHS_OTHER_Unload_DTL WOD\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON WOD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "    LEFT JOIN WHS_OTHER_UNLOAD WOT ON WOD.WHS_OTHER_UUID = WOT.UUID\n" +
            "WHERE WOD.WHS_OTHER_UUID = #{uuid} \n")
    List<Map<String, Object>> acquistionAndScrapDataToSapItem(@Param("uuid") String uuid);

    /** 卸货入库提交 SAP 表头行结构获取 */
    @Select("SELECT 'C' AS \"IFLAG\",\n" +
            "       '' AS \"IMBLNR\",\n" +
            "       '' AS \"IMJAHR\",\n" +
            "       WOT.PACKAGE_LIST AS \"IXDNUM\",\n" +
            "       WOT.SPMS_ID ||' '|| WOT.REMARKS AS \"IBKTXT\",\n" +
            "       REPLACE(WOT.CREATE_DATE, '-', '') AS \"IBLDAT\",\n" +
            "       REPLACE(WOT.POSTING_DATE, '-', '') AS \"IBUDAT\"\n" +
            "FROM WHS_OTHER_UNLOAD WOT\n" +
            "WHERE UUID = #{uuid} ")
    Map<String, Object> handleInHouseUnloadDataToSapHead(@Param("uuid") String uuid);

    @Select("SELECT WOT.SPMS_ID AS \"LYDJH\",\n" +
            "       WOD.PACKAGE_LIST AS \"XDNUM\",\n" +
            "       '' AS \"ZGNSJ_NO\",\n" +
            "       TO_CHAR(WOD.PACKAGE_ROW_ID) AS \"POSNR_2\",\n" +
            "       WOD.WHS_LOCATION_CODE AS \"XALGORT\",\n" +
            "       WOD.MATERIAL_NO AS \"MATNR\",\n" +
            "       WOD.BOX_NOTE AS \"BOX_CODE\",\n" +
            "       WOD.OPERATE_QTY AS \"XALMEN\",\n" +
            "       WOD.BASIC_UNIT AS \"MEINS\",\n" +
            "       WOD.PACKAGE_QTY AS \"XALMNG\",\n" +
            "       WOD.UNIT_SALES AS \"VRKME\",\n" +
            "       TO_CHAR(WOT.CREATE_TIME, 'yyyyMMdd') AS \"X_ERDAT\",\n" +
            "       WOD.NET_WEIGHT AS \"XNTGEW\",\n" +
            "       WOD.GROSS_WEIGHT AS \"XBRGEW\",\n" +
            "       WOD.WEIGHT_UNIT AS \"GEWEI\",\n" +
            "       WOD.VOLUME_QTY AS \"XVOLUM\",\n" +
            "       WOD.VOLUME_UNIT AS \"VOLEH\",\n" +
            "       WOD.CHARG AS \"CHARG\",\n" +
            "       '' AS \"LOEKZ\"\n" +
            "FROM WHS_OTHER_Unload_DTL WOD LEFT JOIN WHS_OTHER_UNLOAD WOT ON WOD.WHS_OTHER_UUID = WOT.UUID\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON WOD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE WOD.WHS_OTHER_UUID = #{uuid} ")
    List<Map<String, Object>> handleInHouseUnloadDataToSapItem(@Param("uuid") String uuid);

    @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" +
            "    (CASE WHEN RE.UNIT_SALES IS NULL \n" +
            "       THEN RE.BASIC_UNIT ELSE RE.UNIT_SALES END) AS \"unitSales\",\n" +
            "    RE.BASIC_UNIT AS \"basicUnit\",\n" +
            "    RE.OPERATE_QTY AS \"operateQty\",\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.REMARKS AS \"remarks\",\n" +
            "    RE.UUID AS \"uuid\",\n" +
            "    RE.UNPLANNED AS \"unplanned\",\n" +
            "    RE.EMPTY_CABINET_WEIGHT AS \"emptyCabinetWeight\",\n" +
            "    RE.PACKAGE_QTY AS \"packingQty\",\n" +
            "    RE.CUSTOMS_DECLARATION_NUMBER AS \"customsDeclarationNumber\",\n" +
            "    RE.PACKING_SPEC AS \"packingSpec\",\n" +
            "       RE.LOADING_TYPE AS \"loadingType\"\n" +
            "FROM (\n" +
            "     SELECT DISTINCT OT.PACKAGE_LIST,OT.CABINET_NUMBER,OT.ACTUAL_LOADING_DATE,OT.WHS_LOCATION_CODE,W.WHS_LOCATION_DESC,OT.LEAD_SEAL,\n" +
            "                     OD.MATERIAL_NO,SAP.MATERIAL_ZH_DESC,SAP.UNIT_SALES,SAP.BASIC_UNIT,OD.OPERATE_QTY,OT.CABINET_CHECK_RESULT,\n" +
            "                     OD.CONTAINER_BRAND,OD.COLOR_NUMBER,OD.THICKNESS,OD.ACTUAL_AVG_GROSS_WEIGHT,OT.MONITOR_LOADING,OT.REMARKS,OT.UUID,\n" +
            "                     OD.UNPLANNED,OT.EMPTY_CABINET_WEIGHT,OD.PACKAGE_QTY,OD.CUSTOMS_DECLARATION_NUMBER,OD.PACKING_SPEC, OD.LOADING_TYPE\n" +
            "     FROM WHS_OTHER_UNLOAD OT\n" +
            "              LEFT JOIN WHS_OTHER_Unload_DTL OD ON OT.UUID = OD.WHS_OTHER_UUID\n" +
            "              LEFT JOIN WHS W ON OT.WERKS = W.WERKS AND OT.WHS_LOCATION_CODE = W.WHS_LOCATION_CODE\n" +
            "              LEFT JOIN MATERIAL_SAP SAP ON OD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "     WHERE OD.OPERATE_QTY > 0 AND OT.UUID = #{uuid} \n" +
            "         ) RE")
    List<Map<String, Object>> whsOtherPackageListToSapM(@Param("uuid") String uuid);

    @Select("SELECT ROWNUM * 10 AS \"itemNo\", RESULT.*\n" +
            "FROM (\n" +
            "         SELECT RE.PACKAGE_LIST AS \"packageList\",\n" +
            "                RE.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "                RE.DATE_OF_LOADING 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 \"operateQty\",\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 \"uuid\",\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.DENOMINATOR AS \"denominator\",\n" +
            "                RE.NUMERATOR AS \"numerator\",\n" +
            "                TO_CHAR(RE.ITEM) AS \"boxNoteItem\",\n" +
            "                RE.BOX_NOTE AS \"boxNote\"\n" +
            "         FROM (\n" +
            "                  SELECT DISTINCT WOT.PACKAGE_LIST, WOD.CABINET_NUMBER, WOD.DATE_OF_LOADING, WOD.WHS_LOCATION_CODE, W.WHS_LOCATION_DESC,\n" +
            "                                  WOD.LEAD_SEAL, SBND.MATERIAL_NO, SAP.MATERIAL_ZH_DESC, SBND.DOMESTIC_QTY_RECEIVED AS \"DOMESTIC_QTY_RECEIVED\", WOT.CABINET_CHECK_RESULT,\n" +
            "                                  WOD.CONTAINER_BRAND, WOT.MONITOR_LOADING, WOT.REMARKS, WOT.UUID, WOT.EMPTY_CABINET_WEIGHT,\n" +
            "                                  WOD.PACKING_SPEC, WOD.LOADING_TYPE, SAP.BASIC_UNIT, SBND.ITEM, SBND.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_OTHER_UNLOAD WOT\n" +
            "                           LEFT JOIN WHS_OTHER_Unload_DTL WOD ON WOT.UUID = WOD.WHS_OTHER_UUID\n" +
            "                           LEFT JOIN SAP_BOX_NOTE SBN ON WOD.BOX_NOTE = SBN.BOX_NOTE\n" +
            "                           LEFT JOIN SAP_BOX_NOTE_DTL SBND ON SBN.BOX_NOTE = SBND.BOX_NOTE\n" +
            "                           LEFT JOIN WHS W ON WOT.WERKS = W.WERKS AND WOT.WHS_LOCATION_CODE = W.WHS_LOCATION_CODE\n" +
            "                           LEFT JOIN MATERIAL_SAP SAP ON SBND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "                  WHERE WOD.OPERATE_QTY > 0 AND SBND.DOMESTIC_QTY_RECEIVED > 0 AND WOT.UUID = #{uuid} \n" +
            "              ) RE GROUP BY RE.PACKAGE_LIST, RE.CABINET_NUMBER, RE.DATE_OF_LOADING, RE.WHS_LOCATION_CODE, RE.WHS_LOCATION_DESC, RE.LEAD_SEAL,\n" +
            "                            RE.MATERIAL_NO, RE.MATERIAL_ZH_DESC, RE.CABINET_CHECK_RESULT, RE.CONTAINER_BRAND, RE.MONITOR_LOADING, RE.REMARKS, RE.UUID,\n" +
            "                            RE.EMPTY_CABINET_WEIGHT, RE.PACKING_SPEC, RE.LOADING_TYPE, RE.BASIC_UNIT, RE.UNIT_SALES, RE.DENOMINATOR, RE.NUMERATOR, RE.ITEM, RE.BOX_NOTE\n" +
            "         ) RESULT ")
    List<Map<String, Object>> whsOtherPackageListToSapX(@Param("uuid") String uuid);

    @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_Unload_DTL WOD\n" +
            "    LEFT JOIN WHS_OTHER_UNLOAD 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);

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

    @Select("SELECT WOT.WERKS AS \"WERKS\",\n" +
//            "       WOT.SPMS_ID || WOT.REMARKS AS \"SPMSID\",\n" +
            "       WOT.WHS_LOCATION_CODE AS \"LGORT\",\n" +
            "       WOT.MOVEMENT_TYPE AS \"BWART\",\n" +
            "       WOT.SPMS_ID AS \"XBLNR\",\n" +
            "       REPLACE(WOT.CREATE_DATE, '-', '') AS \"BLDAT\",\n" +
            "       REPLACE(WOT.POSTING_DATE, '-', '') AS \"BUDAT\",\n" +
            "       WOT.SPMS_ID ||' '|| WOT.REMARKS AS \"BKTXT\"\n" +
            "FROM WHS_OTHER_UNLOAD WOT\n" +
            "WHERE UUID = #{uuid} ")
    Map<String, Object> getWhsOtherTaskAdjustmentHead(@Param("uuid") String uuid);

    @Select("SELECT TO_CHAR(WOD.ROW_ID) AS \"ZEILE\",\n" +
            "       WOD.MATERIAL_NO_OUT AS \"MATNR\",\n" +
            "       TO_CHAR(WOD.QTY_OUT, 'fm9999999990.000') AS \"MENGE\",\n" +
            "       WOD.MATERIAL_NO_IN AS \"ZSHWL\",\n" +
            "       SAP.BASIC_UNIT AS \"MEINS\",\n" +
            "       '' AS \"LIFNR\",\n" +
            "       '' AS \"KOSTL\",\n" +
            "       WOD.WERKS AS \"WERKS\",\n" +
            "       WOD.WHS_LOCATION_CODE AS \"LGORT\",\n" +
            "       (CASE WHEN WOD.ROW_REMARKS IS NULL THEN '-' ELSE WOD.ROW_REMARKS END) AS \"SGTXT\"\n" +
            "FROM WHS_OTHER_Unload_DTL WOD LEFT JOIN MATERIAL_SAP SAP ON WOD.MATERIAL_NO_IN = SAP.MATERIAL_NO\n" +
            "WHERE WOD.WHS_OTHER_UUID = #{uuid} AND WOD.QTY_OUT > 0 ")
    List<Map<String, Object>> getWhsOtherTaskAdjustmentItems(@Param("uuid") String uuid);

    /** 获取其他出入库 - 卸货入库 - 撤销操作时提交 SAP 的行数据 */
    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"zeile\",\n" +
            "       RE.*\n" +
            "FROM (SELECT WOD.PACKAGE_LIST AS \"xdnum\",\n" +
            "             TO_CHAR(WOD.PACKAGE_ROW_ID) AS \"posnr\",\n" +
            "             WOD.MATERIAL_NO AS \"matnr\",\n" +
            "             TO_CHAR(WOD.OPERATE_QTY, 'fm9999999990.000') AS \"menge\",\n" +
            "             SAP.BASIC_UNIT AS \"meins\",\n" +
            "             WOT.WHS_LOCATION_CODE AS \"lgort\",\n" +
            "             ' ' AS \"sgtxt\"\n" +
            "      FROM WHS_OTHER_Unload_DTL WOD\n" +
            "               LEFT JOIN WHS_OTHER_UNLOAD WOT ON WOD.WHS_OTHER_UUID = WOT.UUID\n" +
            "               LEFT JOIN MATERIAL_SAP SAP ON WOD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "      WHERE WOD.WHS_OTHER_UUID = #{whsOtherUuid} AND WOD.OPERATE_QTY > 0 ) RE ")
    List<Map<String, Object>> getCancelWhsOtherTaskItemsM(@Param("whsOtherUuid") String whsOtherUuid);

    /**
     * 获取其他出入库 - 退货出库 - 操作时提交 SAP 的头数据
     * @param uuid
     * @return
     */
    @Select("SELECT WOT.SPMS_ID || WOT.REMARKS AS \"BKTXT\",\n" +
            "       WOT.SPMS_ID || WOT.REMARKS AS \"SPMSID\",\n" +
            "       WOT.SPMS_ID AS \"LYDJH\",\n" +
            "       REPLACE(WOT.DOCUMENT_DATE, '-', '') AS \"BLDAT\",\n" +
            "       REPLACE(WOT.POSTING_DATE, '-', '') AS \"BUDAT\",\n" +
            "       '101' AS \"BWART\",\n" +
            "       U.LAST_NAME AS \"CREATEDBY\",\n" +
            "       '' AS \"VBELN_IM\",\n" +
            "       '' AS \"XBLNR\",\n" +
            "       'A' AS \"SHLX\",\n" +
            "       WOT.SAP_PO AS \"EBELN\"\n" +
            "FROM WHS_OTHER_UNLOAD WOT LEFT JOIN SPMS_USER U ON WOT.CREATE_USERID = U.WORK_CODE\n" +
            "WHERE WOT.UUID = #{uuid} ")
    Map<String, Object> getWhsOtherTaskReturnOutHouseHead(@Param("uuid") String uuid);

    /**
     * 获取其他出入库 - 退货出库 - 操作时提交 SAP 的行数据
     * @param uuid
     * @return
     */
    @Select("SELECT '' AS \"VBELN_IM\",\n" +
            "       '' AS \"VBELP_IM\",\n" +
            "       WOD.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(WOD.OPERATE_QTY, 'fm9999999990.000') AS \"BSTMG\",\n" +
            "       WOD.BASIC_UNIT AS \"BSTME\",\n" +
            "       WOD.WERKS AS \"WERKS\",\n" +
            "       WOD.WHS_LOCATION_CODE AS \"LGORT\",\n" +
            "       WOD.ROW_REMARKS AS \"SGTXT\",\n" +
            "       WOD.DELIVERY_NOTE_SAP AS \"EBELN\",\n" +
            "       TO_CHAR(WOD.DELIVERY_NOTE_SAP_ITEM) AS \"EBELP\"\n" +
            "FROM WHS_OTHER_Unload_DTL WOD\n" +
            "WHERE WOD.WHS_OTHER_UUID = #{uuid} AND WOD.OPERATE_QTY > 0 \n")
    List<Map<String, Object>> getWhsOtherTaskReturnOutHouseItem(@Param("uuid") String uuid);

    /***
     * 退货出库，查询获取物料的基本信息，单位换算关系等；
     * @param materialNo
     * @return
     */
    @Select("SELECT SAP.MATERIAL_NO AS \"materialNo\",\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.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       SAP.UNIT_SALES AS \"unitSales\"\n" +
            "FROM MATERIAL_SAP SAP\n" +
            "WHERE SAP.MATERIAL_NO = #{materialNo} ")
    Map<String, Object> getNumeratorDenominator(@Param("materialNo") String materialNo);
    
    @Select("SELECT SPMS_ID AS \"spmsId\", \n" +
            "       BATCH_NO AS \"batchNo\", \n" +
            "       WAREHOUSE_KEEPER AS \"warehouseKeeper\", \n" +
            "       TO_WAREHOUSE_TIME AS \"toWarehouseTime\", \n" +
            "       START_OPERATION_TIME AS \"startOperationTime\", \n" +
            "       END_OPERATION_TIME AS \"endOperationTime\", \n" +
            "       HANDLING_GROUP AS \"handlingGroup\" \n" +
            "     FROM WHS_OTHER_UNLOAD " +
            "       WHERE SPMS_ID = #{spmsId} ")
	Map<String, Object> selectLoadingData(@Param("spmsId") String spmsId);
    
    @Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update WHS_OTHER_Unload_DTL set \n" +
    		"		WAERS = #{item.waers,jdbcType=VARCHAR}, \n" +
    		"		DMBTR = #{item.dmbtr,jdbcType=VARCHAR} \n" +
    		"	where WHS_OTHER_UUID = #{item.whsOtherUuid,jdbcType=VARCHAR} \n" +
    		"		and ROW_ID = #{item.rowId} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtl(List<WhsOtherUnloadDtl> list);
    
    @Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update WHS_OTHER_Unload_DTL set \n" +
    		"		WAERS = #{item.waers,jdbcType=VARCHAR}, \n" +
    		"		DMBTR = #{item.dmbtr,jdbcType=VARCHAR} \n" +
    		"	where WHS_OTHER_UUID = #{item.whsOtherUuid,jdbcType=VARCHAR} \n" +
    		"		and DELIVERY_NOTE_SAP_ITEM = #{item.deliveryNoteSapItem} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtlByMigo(List<WhsOtherUnloadDtl> list);
}