package com.sunda.spmswms.mapper;

import com.sunda.spmswms.entity.SapDeliveryNote;
import com.sunda.spmswms.entity.SapDeliveryNoteBatchReceipt;
import com.sunda.spmswms.entity.SapDeliveryNoteDtl;
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-03-22
 */
@Mapper
public interface SapDeliveryNoteMapper extends BaseMapper<SapDeliveryNote> {

    @Select("<script>" +
            "SELECT DNINFO.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       DNINFO.PLANNED_ARRIVAL_DATE AS \"plannedArrivalDate\",\n" +
            "       DNINFO.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       DNINFO.DELIVERY_NOTE_SRM AS \"deliveryNoteSrm\",\n" +
            "       DNINFO.CREATE_DATE_SAP AS \"createDateSap\",\n" +
            "       DNINFO.SUPPLIER_CODE AS \"supplierCode\",\n" +
            "       DNINFO.SUPPLIER_DESC AS \"supplierDesc\",\n" +
            "       DNINFO.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       DNINFO.PACKAGE_NOTE AS \"packageNote\",\n" +
            "       DNINFO.WERKS AS \"werks\",\n" +
            "       DNINFO.REMARKS AS \"remarks\",\n" +
            "       DNINFO.DOCUMENT_DATE AS \"documentDate\",\n" +
            "       DNINFO.POSTING_DATE AS \"postingDate\",\n" +
            "       DNINFO.MOBILE_TYPE AS \"mobileType\",\n" +
            "       DNINFO.USER_ID AS \"userId\",\n" +
            "       DNINFO.PDA_SN AS \"pdaSN\",\n" +
            "       DNINFO.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       DNINFO.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       DNINFO.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       DNINFO.CANCEL_SAP_VOUCHER_NUMBER AS \"cancelSapVoucherNumber\",\n" +
            "       DNINFO.OPERATE_USER AS \"operateUser\",\n" +
            "       DNINFO.ORDER_NUMBER AS \"orderNumber\",\n" +
            "       DNINFO.WHS_LOCATION_CODE AS \"whsLocationCode\"\n" +
            "FROM (\n" +
            "         SELECT DISTINCT DN.DELIVERY_NOTE_SAP, DN.PLANNED_ARRIVAL_DATE, DN.DELIVERY_TYPE, DN.DELIVERY_NOTE_SRM, DN.CREATE_DATE_SAP, DN.SUPPLIER_CODE, DN.SUPPLIER_DESC, DN.SPMS_STATUS,DN.PACKAGE_NOTE, DND.WERKS, DND.WHS_LOCATION_CODE,DND.ORDER_NUMBER, DN.REMARKS,DN.USER_ID,DN.DOCUMENT_DATE,DN.POSTING_DATE,DN.MOBILE_TYPE,DN.PDA_SN,DN.SAP_VOUCHER_NUMBER,DN.SAP_VOUCHER_YEAR,DN.CANCEL_POSTING_DATE,DN.CANCEL_SAP_VOUCHER_NUMBER, DN.OPERATE_USER\n" +
            "         FROM SAP_DELIVERY_NOTE DN\n" +
            "                  LEFT JOIN SAP_DELIVERY_NOTE_DTL DND ON DN.DELIVERY_NOTE_SAP = DND.DELIVERY_NOTE_SAP\n" +
            "         WHERE DN.PACKAGE_NOTE IS NULL \n" +
            "               <when test='deliveryNoteSap!=null'> AND DN.DELIVERY_NOTE_SAP = #{deliveryNoteSap}</when>\n" +
            "               <when test='deliveryType!=null'> AND DN.DELIVERY_TYPE = #{deliveryType}</when>\n" +
            "               <when test='deliveryNoteSrm!=null'> AND DN.DELIVERY_NOTE_SRM = #{deliveryNoteSrm}</when>\n" +
            "               <when test='supplierCode!=null'> AND DN.SUPPLIER_CODE = #{supplierCode}</when>\n" +
            "               <when test='plaStartDate!=null'> AND DN.PLANNED_ARRIVAL_DATE &gt;= #{plaStartDate}</when>\n" +
            "               <when test='plaEndDate!=null'> AND DN.PLANNED_ARRIVAL_DATE &lt;= #{plaEndDate}</when>\n" +
            "               <when test='sapVoucherNumber!=null'> AND DN.SAP_VOUCHER_NUMBER= #{sapVoucherNumber}</when>\n" +
            "               <when test='spmsStatus!=null and spmsStatus!=\"0\"'> AND DN.SPMS_STATUS = #{spmsStatus}</when>\n" +
            "               <when test='spmsStatus==\"0\"'> AND (DN.SPMS_STATUS = #{spmsStatus} OR DN.SPMS_STATUS IS NULL)</when>\n" +
            "               <when test='werks!=null'> AND DND.WERKS= #{werks}</when>\n" +
            "               <when test='whsLocationCode!=null'> AND DND.WHS_LOCATION_CODE = #{whsLocationCode}</when>\n" +
            "               <when test='businessType!=null'> AND DN.BUSINESS_TYPE = #{businessType}</when>\n" +
            "         ) DNINFO\n" +
            "WHERE DNINFO.WERKS IN\n" +
            "      (SELECT SUW.WERKS FROM SPMS_USER_WHS SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "  AND DNINFO.WHS_LOCATION_CODE IN\n" +
            "      (SELECT SUW.WHS_LOCATION_CODE FROM SPMS_USER_WHS SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "</script>")
    List<Map<String, Object>> getSapDeliverNoteM(@Param("userId") String userId,
                                                 @Param("deliveryNoteSap") String deliveryNoteSap,
                                                 @Param("deliveryType") String deliveryType,
                                                 @Param("deliveryNoteSrm") String deliveryNoteSrm,
                                                 @Param("spmsMark") String spmsMark,
                                                 @Param("supplierCode") String supplierCode,
                                                 @Param("plaStartDate") String plaStartDate,
                                                 @Param("plaEndDate") String plaEndDate,
                                                 @Param("spmsStatus") String spmsStatus,
                                                 @Param("sapVoucherNumber") String sapVoucherNumber,
                                                 @Param("werks") String werks,
                                                 @Param("whsLocationCode") String whsLocationCode,
                                                 @Param("businessType") String businessType
                                                 );

    @Select("<script>" +
            "SELECT DNINFO.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       DNINFO.PLANNED_ARRIVAL_DATE AS \"plannedArrivalDate\",\n" +
            "       DNINFO.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       DNINFO.DELIVERY_NOTE_SRM AS \"deliveryNoteSrm\",\n" +
            "       DNINFO.CREATE_DATE_SAP AS \"createDateSap\",\n" +
            "       DNINFO.SUPPLIER_CODE AS \"supplierCode\",\n" +
            "       DNINFO.SUPPLIER_DESC AS \"supplierDesc\",\n" +
            "       DNINFO.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       DNINFO.PACKAGE_NOTE AS \"packageNote\",\n" +
            "       DNINFO.WERKS AS \"werks\",\n" +
            "       DNINFO.REMARKS AS \"remarks\",\n" +
            "       DNINFO.DOCUMENT_DATE AS \"documentDate\",\n" +
            "       DNINFO.POSTING_DATE AS \"postingDate\",\n" +
            "       DNINFO.MOBILE_TYPE AS \"mobileType\",\n" +
            "       DNINFO.USER_ID AS \"userId\",\n" +
            "       DNINFO.PDA_SN AS \"pdaSN\",\n" +
            "       DNINFO.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       DNINFO.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       DNINFO.OPERATE_USER AS \"operateUser\",\n" +
           // "       DNINFO.ORDER_NUMBER AS \"orderNumber\",\n" +
            "       DNINFO.WHS_LOCATION_CODE AS \"whsLocationCode\"\n" +
            "FROM (\n" +
            "         SELECT DISTINCT DN.DELIVERY_NOTE_SAP, DN.PLANNED_ARRIVAL_DATE, DN.DELIVERY_TYPE, DN.DELIVERY_NOTE_SRM, " +
            "               DN.CREATE_DATE_SAP, DN.SUPPLIER_CODE, DN.SUPPLIER_DESC, DN.SPMS_STATUS,DN.PACKAGE_NOTE, DND.WERKS, " +
            "               DND.WHS_LOCATION_CODE, DN.REMARKS,DN.USER_ID,DN.DOCUMENT_DATE,DN.POSTING_DATE,DN.MOBILE_TYPE,DN.PDA_SN, " +
            "               DN.SAP_VOUCHER_NUMBER,DN.SAP_VOUCHER_YEAR,DN.OPERATE_USER\n" +
            "         FROM SAP_DELIVERY_NOTE DN\n" +
            "                  LEFT JOIN SAP_DELIVERY_NOTE_DTL DND ON DN.DELIVERY_NOTE_SAP = DND.DELIVERY_NOTE_SAP\n" +
            "         WHERE 1=1 \n" +
            "               <when test='spmsMark != null'> AND DN.PACKAGE_NOTE IS NOT NULL </when>\n" +
            "               <when test='deliveryNoteSap!=null'> AND DN.DELIVERY_NOTE_SAP = #{deliveryNoteSap}</when>\n" +
            "               <when test='deliveryType!=null'> AND DN.DELIVERY_TYPE = #{deliveryType}</when>\n" +
            "               <when test='deliveryNoteSrm!=null'> AND DN.DELIVERY_NOTE_SRM = #{deliveryNoteSrm}</when>\n" +
            "               <when test='supplierCode!=null'> AND DN.SUPPLIER_CODE = #{supplierCode}</when>\n" +
            "               <when test='plaStartDate!=null'> AND DN.PLANNED_ARRIVAL_DATE &gt;= #{plaStartDate}</when>\n" +
            "               <when test='plaEndDate!=null'> AND DN.PLANNED_ARRIVAL_DATE &lt;= #{plaEndDate}</when>\n" +
            "               <when test='sapVoucherNumber!=null'> AND DN.SAP_VOUCHER_NUMBER= #{sapVoucherNumber}</when>\n" +
            "               <when test='spmsStatus!=null and spmsStatus!=\"0\"'> AND DN.SPMS_STATUS = #{spmsStatus}</when>\n" +
            "               <when test='spmsStatus==\"0\"'> AND (DN.SPMS_STATUS = #{spmsStatus} OR DN.SPMS_STATUS IS NULL)</when>\n" +
            "               <when test='werks!=null'> AND DND.WERKS= #{werks}</when>\n" +
            "               <when test='whsLocationCode!=null'> AND DND.WHS_LOCATION_CODE = #{whsLocationCode}</when>\n" +
            "               <when test='businessType!=null'> AND DN.BUSINESS_TYPE = #{businessType}</when>\n" +
            "   			<when test='werksList != null'> AND DND.WERKS in \n" +
            "       			<foreach item='item' index='index' collection='werksList' open='(' separator=',' close=')'>\n" +
            "           			#{item} \n" +
            "       			</foreach>" +
            "   			</when>\n" +
            "         ) DNINFO\n" +
            "WHERE DNINFO.WERKS IN\n" +
            "      (SELECT SUW.WERKS FROM SPMS_USER_WHS SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "  AND DNINFO.WHS_LOCATION_CODE IN\n" +
            "      (SELECT SUW.WHS_LOCATION_CODE FROM SPMS_USER_WHS SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "       ORDER BY DNINFO.SPMS_STATUS ASC NULLS FIRST, DNINFO.PLANNED_ARRIVAL_DATE ASC, DNINFO.DELIVERY_NOTE_SAP ASC  \n" +
            "</script>")
    List<Map<String, Object>> getSapDeliverNote(@Param("userId") String userId,
                                                @Param("deliveryNoteSap") String deliveryNoteSap,
                                                @Param("deliveryType") String deliveryType,
                                                @Param("deliveryNoteSrm") String deliveryNoteSrm,
                                                @Param("spmsMark") String spmsMark,
                                                @Param("supplierCode") String supplierCode,
                                                @Param("plaStartDate") String plaStartDate,
                                                @Param("plaEndDate") String plaEndDate,
                                                @Param("spmsStatus") String spmsStatus,
                                                @Param("sapVoucherNumber") String sapVoucherNumber,
                                                @Param("werks") String werks,
                                                @Param("whsLocationCode") String whsLocationCode,
                                                @Param("businessType") String businessType, 
                                                @Param("werksList") List<String> werksList);


    /**
     * 根据用户工号，查询名下指定条件下的所有备件货任务
     *
     * @param userId
     * @param deliveryNoteSap
     * @param plaStartDate
     * @param plaEndDate
     * @return
     */
    @Select("<script>" +
            "SELECT DNINFO.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       DNINFO.PLANNED_ARRIVAL_DATE AS \"plannedArrivalDate\",\n" +
            "       DNINFO.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       DNINFO.DELIVERY_NOTE_SRM AS \"deliveryNoteSrm\",\n" +
            "       DNINFO.CREATE_DATE_SAP AS \"createDateSap\",\n" +
            "       DNINFO.SUPPLIER_CODE AS \"supplierCode\",\n" +
            "       DNINFO.SUPPLIER_DESC AS \"supplierDesc\",\n" +
            "       DNINFO.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       DNINFO.PACKAGE_NOTE AS \"packageNote\",\n" +
            "       DNINFO.WERKS AS \"werks\",\n" +
            "       DNINFO.WHS_LOCATION_CODE AS \"whsLocationCode\"\n" +
            "FROM (\n" +
            "         SELECT DISTINCT DN.DELIVERY_NOTE_SAP, DN.PLANNED_ARRIVAL_DATE, DN.DELIVERY_TYPE, DN.DELIVERY_NOTE_SRM, DN.CREATE_DATE_SAP, DN.SUPPLIER_CODE, DN.SUPPLIER_DESC, DN.SPMS_STATUS,DN.PACKAGE_NOTE, DND.WERKS, DND.WHS_LOCATION_CODE\n" +
            "         FROM SAP_DELIVERY_NOTE DN\n" +
            "                  LEFT JOIN SAP_DELIVERY_NOTE_DTL DND ON DN.DELIVERY_NOTE_SAP = DND.DELIVERY_NOTE_SAP\n" +
            "         WHERE DN.PACKAGE_NOTE IS NOT NULL\n" +
            "               <when test='deliveryNoteSap!=null'> AND DN.DELIVERY_NOTE_SAP = #{deliveryNoteSap}</when>\n" +
            "               <when test='plaEndDate!=null'> AND DN.PLANNED_ARRIVAL_DATE &lt;= #{plaEndDate}</when>\n" +
            "               <when test='plaStartDate!=null'> AND DN.PLANNED_ARRIVAL_DATE &gt;= #{plaStartDate}</when>\n" +
            "               <when test='spmsStatus!=null'> AND DN.SPMS_STATUS = #{spmsStatus}</when>" +
            "               <when test='businessType!=null'> AND DN.BUSINESS_TYPE = #{businessType}</when>\n" +
            "         ) DNINFO\n" +
            "WHERE DNINFO.WERKS IN\n" +
            "      (SELECT SUW.WERKS FROM SPMS_USER_WHS SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "  AND DNINFO.WHS_LOCATION_CODE IN\n" +
            "      (SELECT SUW.WHS_LOCATION_CODE FROM SPMS_USER_WHS SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "</script>")
    List<Map<String, Object>> getSpDeliveryNoteTask(
            @Param("userId") String userId,
            @Param("deliveryNoteSap") String deliveryNoteSap,
            @Param("plaStartDate") String plaStartDate,
            @Param("plaEndDate") String plaEndDate,
            @Param("spmsStatus") String spmsStatus,
            @Param("businessType") String businessType);

    /**
     * 获取备件货上传SAP的明细数据。备件货收货信息存储于箱码明细表中。
     * 1.备件货不拆箱，在中转仓按箱收货时，箱码明细表中 DOMESTIC_QTY_RECEIVED 默认更新为对应物料在箱中的数量；
     * 2.交货单行项目编号，一行对应一个物料；一个物料可能存在于多个箱子中，故需要求和；
     * 3.按箱码明细表中 交货单号、交货单行项目编号 累加计算某个物料在中转仓的收货数量；
     * 4.按 交货单号、交货单行项目编号 关联交货单明细表，获取物料编号、收货工厂、仓库、单位 等信息；
     * 5.SAP交货单行号按照 '10，20，30...'编码；SRM箱码明细表交货单行号按照 '1，2，3...'编码，查询条件需做转换；
     */
    @Select("SELECT TEMP.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       DND.DELIVERY_NOTE_SAP_ITEM AS \"deliveryNoteSapItem\",\n" +
            "       TEMP.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
            "       TEMP.QUANTITY AS \"quantity\",\n" +
            "       DND.MATERIAL_NO AS \"materialNo\",\n" +
            "       DND.DELIVERY_UNIT AS \"deliveryUnit\",\n" +
            "       DND.WERKS AS \"werks\",\n" +
            "       DND.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\"\n" +
            "FROM (\n" +
            "    SELECT BND.DELIVERY_NOTE_SAP,\n" +
            "           BND.DELIVERY_NOTE_SAP_ITEM,\n" +
            "           SUM(DOMESTIC_QTY_RECEIVED) AS \"DOMESTIC_QTY_RECEIVED\",\n" +
            "           SUM(QUANTITY) AS \"QUANTITY\"\n" +
            "    FROM SAP_BOX_NOTE_DTL BND WHERE DELIVERY_NOTE_SAP = #{deliveryNoteSap} \n" +
            "    GROUP BY BND.DELIVERY_NOTE_SAP_ITEM, BND.DELIVERY_NOTE_SAP ORDER BY DELIVERY_NOTE_SAP_ITEM\n" +
            "    ) TEMP, SAP_DELIVERY_NOTE_DTL DND\n" +
            "        LEFT JOIN MATERIAL_SAP SAP ON DND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE TEMP.DELIVERY_NOTE_SAP = DND.DELIVERY_NOTE_SAP\n" +
            "  AND DND.DELIVERY_NOTE_SAP_ITEM = TEMP.DELIVERY_NOTE_SAP_ITEM\n" +
            "  AND TEMP.DOMESTIC_QTY_RECEIVED > 0 \n")
    List<Map<String, Object>> getBoxNoteDtlToSap(@Param("deliveryNoteSap") String deliveryNoteSap);

    @Select("SELECT DND.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       DND.DELIVERY_NOTE_SAP_ITEM AS \"deliveryNoteSapItem\",\n" +
            "       DND.PURCHASE_GROUP AS \"purchaseGroup\",\n" +
            "       DND.PURCHASE_GROUP_NAME AS \"purchaseGroupName\",\n" +
            "       DND.MATERIAL_NO AS \"materialNo\",\n" +
            "       DND.DELIVERY_QTY AS \"deliveryQty\",\n" +
            "       DND.DELIVERY_UNIT AS \"deliveryUnit\",\n" +
            "       DND.WERKS AS \"werks\",\n" +
            "       DND.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       DND.RECEIVED_QTY AS \"receivedQty\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "       DND.ITEM_STATUS AS \"itemStatus\",\n" +
            "       DND.OVER_DELIVERY_LIMIT AS \"overDeliveryLimit\",\n" +
            "       DND.UNDER_DELIVERY_LIMIT AS \"underDeliveryLimit\",\n" +
            "       DND.UNLIMITED AS \"unlimited\",\n" +
            "       DND.REMARKS AS \"remarks\",\n" +
            "       DND.TEMP_STORAGE AS \"tempStorage\",\n" +
            "       DND.WERKS_XQ AS \"werksXq\",\n" +
            "       DND.QTY_SALES AS \"qtySales\",\n" +
            "       DND.ACTUAL_QTY_SALES AS \"actualQtySales\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\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" +
            "       DND.UPDATE_TIME AS \"updateTime\",\n" +
            "       DND.SPMS_ITEM AS \"spmsItem\",\n" +
            "       DND.DOCUMENT_DATE AS \"documentDate\",\n" +
            "       DND.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       DND.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       DND.POSTING_DATE AS \"postingDate\"\n" +
            "FROM SAP_DELIVERY_NOTE_DTL DND\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON DND.MATERIAL_NO = SAP.MATERIAL_NO WHERE DND.DELIVERY_NOTE_SAP = #{deliveryNoteSap} ")
    List<Map<String, Object>> sapDeliveryNoteDtls(@Param("deliveryNoteSap") String deliveryNoteSap);

    @Select("SELECT DN.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\", \n" +
            "       DND.MATERIAL_NO AS \"materialNo\", \n" +
            "       SAP.SPMS_MARK AS \"spmsMark\"\n" +
            "FROM SAP_DELIVERY_NOTE DN\n" +
            "    LEFT JOIN SAP_DELIVERY_NOTE_DTL DND ON DN.DELIVERY_NOTE_SAP = DND.DELIVERY_NOTE_SAP\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON DND.MATERIAL_NO = SAP.MATERIAL_NO WHERE DN.DELIVERY_NOTE_SAP = #{deliveryNoteSap} \n")
    List<Map<String, Object>> getDeliveryNoteInfo(@Param("deliveryNoteSap") String deliveryNoteSap);

    @Update("UPDATE SAP_BOX_NOTE BOX SET BOX.BOX_QTY_RECEIVED = 1, BOX.ITEM_STATUS = '6', BOX.ARRIVAL_DATE = TO_CHAR(SYSDATE, 'yyyy-mm-dd') \n" +
            "WHERE BOX.PACKAGE_NOTE = (\n" +
            "    SELECT DN.PACKAGE_NOTE FROM SAP_DELIVERY_NOTE DN\n" +
            "    WHERE DN.DELIVERY_NOTE_SAP = #{deliveryNoteSap} )")
    void updateBoxNoteByDeliveryNoteSap(@Param("deliveryNoteSap") String deliveryNoteSap);

    /** 获取交货单关联的箱码信息 */
    @Select("SELECT DISTINCT SBN.BOX_NOTE AS \"boxNote\"\n" +
            "FROM SAP_DELIVERY_NOTE SDN\n" +
            "    LEFT JOIN SAP_PACKAGE_NOTE SPN ON SDN.PACKAGE_NOTE = SPN.PACKAGE_NOTE\n" +
            "    LEFT JOIN SAP_BOX_NOTE SBN ON SPN.PACKAGE_NOTE = SBN.PACKAGE_NOTE\n" +
            "WHERE SDN.DELIVERY_NOTE_SAP = #{deliveryNoteSap} AND SBN.BOX_NOTE IS NOT NULL ")
    List<Map<String, Object>> getBoxNoteList(@Param("deliveryNoteSap") String deliveryNoteSap);

    /** 根据箱码信息检查该箱码是否存在仓库库存 */
    @Select("SELECT WII.WERKS AS \"werks\",\n" +
            "       WII.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       WII.BOX_NOTE AS \"boxNote\"\n" +
            "FROM WHS_INVENTORY_INFO WII WHERE BOX_NOTE = #{boxNote} ")
    List<Map<String, Object>> getWhsInventoryInfo(@Param("boxNote") String boxNote);

    /** 根据箱码信息检查该箱码是否存在储位库存 */
    @Select("SELECT WSI.UUID AS \"uuid\", " +
            "       WSI.BOX_NOTE AS \"boxNote\" " +
            "       FROM WHS_STORAGE_INVENTORY WSI " +
            "       WHERE BOX_NOTE = #{boxNote} ")
    List<Map<String, Object>> getWhsStorageInventory(@Param("boxNote") String boxNote);
    
    @Select("SELECT DELIVERY_NOTE_SAP AS \"deliveryNoteSap\", \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 SAP_DELIVERY_NOTE " +
            "       WHERE DELIVERY_NOTE_SAP = #{deliveryNoteSap} ")
	Map<String, Object> selectLoadingData(@Param("deliveryNoteSap") String deliveryNoteSap);
    
    @Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update SAP_DELIVERY_NOTE_DTL set \n" +
    		"		WAERS = #{item.waers,jdbcType=VARCHAR}, \n" +
    		"		DMBTR = #{item.dmbtr,jdbcType=VARCHAR} \n" +
    		"	where DELIVERY_NOTE_SAP = #{item.deliveryNoteSap,jdbcType=VARCHAR} \n" +
    		"		and DELIVERY_NOTE_SAP_ITEM = #{item.deliveryNoteSapItem} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtl(List<SapDeliveryNoteDtl> list);
    
    @Select("SELECT * FROM SAP_DELIVERY_NOTE_BATCH_RECEIPT WHERE SPMS_STATUS != '0' AND DELIVERY_NOTE_SAP = #{deliveryNoteSap}")
	List<SapDeliveryNoteBatchReceipt> selectBatchReceipt(@Param("deliveryNoteSap") String deliveryNoteSap);
}