package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaDeliveryNoteItemDTO;
import com.sunda.spmsoversea.dto.OverseaDeliveryNotePageDTO;
import com.sunda.spmsoversea.entity.OverseaDeliveryNote;
import com.sunda.spmsoversea.entity.OverseaDeliveryNoteDtl;
import com.sunda.spmsoversea.entity.OverseaWhsIn;
import com.sunda.spmsoversea.entity.OverseaWhsInventoryLoss;

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-10-22
 */
@Mapper
public interface OverseaDeliveryNoteMapper extends BaseMapper<OverseaDeliveryNote> {

    @Select("<script>" +
            "SELECT ODN.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       ODN.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODN.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       ODN.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       ODN.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       ODN.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ODN.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       ODN.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       ODN.REMARKS AS \"remarks\",\n" +
            "       ODN.COMMENTS AS \"comments\",\n" +
            "       ODN.POSTING_DATE AS \"postingDate\",\n" +
            "       ODN.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       ODN.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       ODN.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       ODN.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       ODN.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       ODN.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPostingDate\",\n" +
            "       ODN.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       ODN.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       ODN.SAP_REMARK AS \"sapRemark\",\n" +
            "       ODN.DATA_VERSION AS \"dataVersion\",\n" +
            "       ODN.OA_WORKFLOW AS \"oaWorkflow\",\n" +
            "       ODN.OA_BIANHAO AS \"oaBianhao\",\n" +
            "       ODN.OA_REMARK AS \"oaRemark\",\n" +
            "       ODN.SUBMIT_OA_DATE AS \"submitOaDate\",\n" +
            "       ODN.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "       ODN.COST_CENTER AS \"costCenter\",\n" +
            "       OPL.MERCHANDISER AS \"merchandiser\",\n" +
            "       OPL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       OPL.FROM_PORT AS \"fromPort\",\n" +
            "       OPL.TO_PORT AS \"toPort\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"actualWerksReceive\",\n" +
            "       ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"actualWhsLocationCodeReceive\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "    LEFT JOIN OVERSEA_PACKAGE_LIST OPL ON ODN.SAP_PACKAGE_LIST = OPL.SAP_PACKAGE_LIST\n" +
            "WHERE ODN.SPMS_STATUS != '12' " +
            "  <when test='dto.sapDeliveryNote!=null and dto.sapDeliveryNote != \"\"'> AND ODN.SAP_DELIVERY_NOTE = #{dto.sapDeliveryNote}</when>" +
            "  <when test='dto.sapPackageList!=null and dto.sapPackageList != \"\"'> AND ODN.SAP_PACKAGE_LIST = #{dto.sapPackageList}</when>" +
            "  <when test='dto.cabinetNumber!=null and dto.cabinetNumber != \"\"'> AND ODN.CABINET_NUMBER = #{dto.cabinetNumber}</when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND ODN.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.werksReceive!=null and dto.werksReceive != \"\"'> AND ODN.WERKS_RECEIVE = #{dto.werksReceive}</when>" +
            "  <when test='dto.werksDelivery!=null and dto.werksDelivery != \"\"'> AND ODN.WERKS_DELIVERY = #{dto.werksDelivery}</when>" +
            "ORDER BY TO_NUMBER(ODN.SPMS_STATUS) ASC, ODN.SAP_DELIVERY_NOTE ASC " +
            "</script>")
    /** 获取交货单表头分页列表 */
    List<Map<String, Object>> getOverseaDeliveryNoteIpage(IPage page, @Param("dto") OverseaDeliveryNotePageDTO dto);

    @Select("<script>" +
            "SELECT ODN.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       ODN.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODN.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       ODN.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       ODN.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       ODN.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ODN.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       ODN.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       ODN.REMARKS AS \"remarks\",\n" +
            "       ODN.COMMENTS AS \"comments\",\n" +
            "       ODN.POSTING_DATE AS \"postingDate\",\n" +
            "       ODN.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       ODN.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       ODN.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       ODN.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       ODN.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       ODN.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPostingDate\",\n" +
            "       ODN.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       ODN.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       ODN.SAP_REMARK AS \"sapRemark\",\n" +
            "       ODN.DATA_VERSION AS \"dataVersion\",\n" +
            "       ODN.OA_WORKFLOW AS \"oaWorkflow\",\n" +
            "       ODN.OA_BIANHAO AS \"oaBianhao\",\n" +
            "       ODN.OA_REMARK AS \"oaRemark\",\n" +
            "       ODN.SUBMIT_OA_DATE AS \"submitOaDate\",\n" +
            "       ODN.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "       ODN.COST_CENTER AS \"costCenter\",\n" +
            "       OPL.MERCHANDISER AS \"merchandiser\",\n" +
            "       OPL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       OPL.FROM_PORT AS \"fromPort\",\n" +
            "       OPL.TO_PORT AS \"toPort\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"actualWerksReceive\",\n" +
            "       ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"actualWhsLocationCodeReceive\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "    LEFT JOIN OVERSEA_PACKAGE_LIST OPL ON ODN.SAP_PACKAGE_LIST = OPL.SAP_PACKAGE_LIST\n" +
            "WHERE ODN.SPMS_STATUS != '12' " +
            "  <when test='dto.sapDeliveryNote!=null and dto.sapDeliveryNote != \"\"'> AND ODN.SAP_DELIVERY_NOTE = #{dto.sapDeliveryNote}</when>" +
            "  <when test='dto.sapPackageList!=null and dto.sapPackageList != \"\"'> AND ODN.SAP_PACKAGE_LIST = #{dto.sapPackageList}</when>" +
            "  <when test='dto.cabinetNumber!=null and dto.cabinetNumber != \"\"'> AND ODN.CABINET_NUMBER = #{dto.cabinetNumber}</when>" +
            "  <when test='spmsStatusList!=null'> " +
            "     AND ODN.SPMS_STATUS IN " +
            "         <foreach item='item' index='index' collection='spmsStatusList' open='(' separator=',' close=')'>\n" +
            "            #{item} \n" +
            "         </foreach>" +
            "  </when>\n" +
            "  <when test='dto.werksReceive!=null and dto.werksReceive != \"\"'> AND ODN.WERKS_RECEIVE = #{dto.werksReceive}</when>" +
            "  <when test='dto.werksDelivery!=null and dto.werksDelivery != \"\"'> AND ODN.WERKS_DELIVERY = #{dto.werksDelivery}</when>" +
            " <when test='dto.werksReceiveList != null'> AND ODN.WERKS_RECEIVE in \n" +
            "    <foreach item='item' index='index' collection='dto.werksReceiveList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "    </foreach>\n" +
            " </when>\n" +
            " <when test='dto.werksDeliveryList != null'> AND ODN.WERKS_DELIVERY in \n" +
            "    <foreach item='item' index='index' collection='dto.werksDeliveryList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "    </foreach>\n" +
            " </when>\n" +
            "ORDER BY TO_NUMBER(ODN.SPMS_STATUS) ASC, ODN.SAP_DELIVERY_NOTE ASC " +
            "</script>")
    /** 获取交货单表头分页列表，PDA多状态查询 */
    List<Map<String, Object>> getOverseaDeliveryNoteIpagePda(IPage page, @Param("dto") OverseaDeliveryNotePageDTO dto, @Param("spmsStatusList") List<String> spmsStatusList);

    @Select("SELECT ODN.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       ODN.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODN.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       ODN.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       ODN.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       ODN.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ODN.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       ODN.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       ODN.REMARKS AS \"remarks\",\n" +
            "       ODN.COMMENTS AS \"comments\",\n" +
            "       ODN.POSTING_DATE AS \"postingDate\",\n" +
            "       ODN.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       ODN.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       ODN.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       ODN.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       ODN.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       ODN.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPostingDate\",\n" +
            "       ODN.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       ODN.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       ODN.SAP_REMARK AS \"sapRemark\",\n" +
            "       ODN.DATA_VERSION AS \"dataVersion\",\n" +
            "       ODN.OA_WORKFLOW AS \"oaWorkflow\",\n" +
            "       ODN.OA_BIANHAO AS \"oaBianhao\",\n" +
            "       ODN.OA_REMARK AS \"oaRemark\",\n" +
            "       ODN.SUBMIT_OA_DATE AS \"submitOaDate\",\n" +
            "       ODN.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "       ODN.COST_CENTER AS \"costCenter\",\n" +
            "       OPL.MERCHANDISER AS \"merchandiser\",\n" +
            "       OPL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       OPL.FROM_PORT AS \"fromPort\",\n" +
            "       OPL.TO_PORT AS \"toPort\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"actualWerksReceive\",\n" +
            "       ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"actualWhsLocationCodeReceive\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "    LEFT JOIN OVERSEA_PACKAGE_LIST OPL ON ODN.SAP_PACKAGE_LIST = OPL.SAP_PACKAGE_LIST\n" +
            "WHERE ODN.SAP_DELIVERY_NOTE = #{sapDeliveryNote} " )
    Map<String, Object> getOverseaDnHead(@Param("sapDeliveryNote") String sapDeliveryNote);

    @Select("SELECT ODND.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODND.SAP_DELIVERY_NOTE_ITEM AS \"sapDeliveryNoteItem\",\n" +
            "       ODND.MATERIAL_NO AS \"materialNo\",\n" +
            "       ODND.RECEIVABLE_QTY_BASIC_UNIT AS \"receivableQtyBasicUnit\",\n" +
            "       ODND.RECEIVABLE_QTY_UNIT_SALES AS \"receivableQtyUnitSales\",\n" +
            "       NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0) AS \"actualQtyBasicUnit\",\n" +
            "       NVL(RE.ACTUAL_QTY_UNIT_SALES, 0) AS \"actualQtyUnitSales\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "       NVL(SAP.UNIT_SALES, SAP.BASIC_UNIT) AS \"unitSales\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       NVL(SAP.DENOMINATOR, 1) AS \"denominator\",\n" +
            "       NVL(SAP.NUMERATOR, 1) AS \"numerator\",\n" +
            "       (CASE WHEN NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0) - ODND.RECEIVABLE_QTY_BASIC_UNIT > 0\n" +
            "           THEN NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0) - ODND.RECEIVABLE_QTY_BASIC_UNIT\n" +
            "           ELSE 0 END) AS \"overchargedQtyBasicUnit\",\n" +
            "       (CASE WHEN ODND.RECEIVABLE_QTY_BASIC_UNIT - NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0) > 0\n" +
            "           THEN ODND.RECEIVABLE_QTY_BASIC_UNIT - NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0)\n" +
            "           ELSE 0 END) AS \"underchargedQtyBasicUnit\",\n" +
            "       (CASE WHEN NVL(RE.ACTUAL_QTY_UNIT_SALES, 0) - ODND.RECEIVABLE_QTY_UNIT_SALES > 0\n" +
            "           THEN NVL(RE.ACTUAL_QTY_UNIT_SALES, 0) - ODND.RECEIVABLE_QTY_UNIT_SALES\n" +
            "           ELSE 0 END) AS \"overchargedQtyUnitSales\",\n" +
            "       (CASE WHEN ODND.RECEIVABLE_QTY_UNIT_SALES - NVL(RE.ACTUAL_QTY_UNIT_SALES, 0) > 0\n" +
            "           THEN ODND.RECEIVABLE_QTY_UNIT_SALES - NVL(RE.ACTUAL_QTY_UNIT_SALES, 0)\n" +
            "           ELSE 0 END) AS \"underchargedQtyUnitSales\",\n" +
            "       ODND.REMARKS AS \"remarks\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE_DTL ODND\n" +
            "    LEFT JOIN (\n" +
            "        SELECT OWI.SAP_DELIVERY_NOTE,\n" +
            "               OWID.SAP_DELIVERY_NOTE_ITEM,\n" +
            "               OWID.MATERIAL_NO,\n" +
            "               SUM(NVL(OWID.ACTUAL_QTY_BASIC_UNIT, 0)) AS ACTUAL_QTY_BASIC_UNIT,\n" +
            "               SUM(NVL(OWID.ACTUAL_QTY_UNIT_SALES, 0)) AS ACTUAL_QTY_UNIT_SALES\n" +
            "        FROM OVERSEA_WHS_IN_DTL OWID\n" +
            "            LEFT JOIN OVERSEA_WHS_IN OWI ON OWID.UUID_WHS_IN = OWI.UUID_WHS_IN\n" +
            "        WHERE OWI.SPMS_STATUS IN ('3', '4', '5') AND OWI.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "        GROUP BY OWI.SAP_DELIVERY_NOTE, OWID.SAP_DELIVERY_NOTE_ITEM, OWID.MATERIAL_NO\n" +
            "        ) RE ON ODND.SAP_DELIVERY_NOTE = RE.SAP_DELIVERY_NOTE\n" +
            "                    AND ODND.SAP_DELIVERY_NOTE_ITEM = RE.SAP_DELIVERY_NOTE_ITEM\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON ODND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE ODND.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ORDER BY TO_NUMBER(ODND.SAP_DELIVERY_NOTE_ITEM) ASC ")
    List<Map<String, Object>> getOverseaDnOverOrUnderCharged(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 根据交货单号获取进行中状态的入库任务；入库任务状态
     * 入库任务状态(默认0待入库;1入库中;2入库完成;3审批通过;4提交SAP成功;5提交SAP失败;6已撤销;7关闭;)
     * */
    @Select("SELECT OWI.UUID_WHS_IN AS \"uuidWhsIn\",\n" +
            "       OWI.WHS_IN_NO AS \"whsInNo\",\n" +
            "       OWI.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       OWI.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OWI.WERKS AS \"werks\",\n" +
            "       OWI.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OWI.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWI.CREATE_DATE AS \"createDate\"\n" +
            "FROM OVERSEA_WHS_IN OWI\n" +
            "WHERE OWI.SAP_DELIVERY_NOTE = #{sapDeliveryNote} \n" +
            "  AND OWI.SPMS_STATUS != '4'\n" +
            "  AND OWI.SPMS_STATUS != '7' ")
    List<OverseaWhsIn> getDoingTask(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 根据交货单号和行号，获取交货单一行明细信息传输OA */
    @Select("SELECT ODND.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "       ODND.COLOR_NO AS \"colorNo\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE_DTL ODND\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON ODND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE ODND.SAP_DELIVERY_NOTE = #{sapDeliveryNote} AND ODND.SAP_DELIVERY_NOTE_ITEM = #{sapDeliveryNoteItem} ")
    Map<String, Object> itemInfo(@Param("sapDeliveryNote") String sapDeliveryNote,
                                 @Param("sapDeliveryNoteItem") String sapDeliveryNoteItem);

    /** 交货单多收少收请求SAP信息结构，盘盈盘亏接口;701-盘盈，702-盘亏 */
    @Select("SELECT REPLACE(ODN.ARRIVAL_DATE, '-', '') AS \"BLDAT\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"BUDAT\",\n" +
            "       #{operateType} AS \"BWART\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"WERKS\",\n" +
            "       ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"LGORT\",\n" +
            "       ODN.SAP_DELIVERY_NOTE || ODN.REMARKS AS \"BKTXT\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "WHERE ODN.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHead(@Param("sapDeliveryNote") String sapDeliveryNote,
                                 @Param("operateType") String operateType,
                                 @Param("postingDate") String postingDate);

    /** 获取有效入库任务判断交货单是否能退回到SAP可能新状态，即入库任务状态 不等于7 */
    @Select("SELECT OWI.UUID_WHS_IN AS \"uuidWhsIn\",\n" +
            "       OWI.WHS_IN_NO AS \"whsInNo\",\n" +
            "       OWI.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       OWI.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OWI.WERKS AS \"werks\",\n" +
            "       OWI.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OWI.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWI.CREATE_DATE AS \"createDate\"\n" +
            "FROM OVERSEA_WHS_IN OWI\n" +
            "WHERE OWI.SAP_DELIVERY_NOTE = #{sapDeliveryNote} \n" +
            "  AND OWI.SPMS_STATUS != '7' ")
    List<OverseaWhsIn> getValidateTask(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 收货多收少收请求SAP结构，成本中心接口；多收对应移动类型Z16，少收对应移动类型Z15； */
    @Select("SELECT ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"LGORT\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"WERKS\",\n" +
            "       ODN.SAP_DELIVERY_NOTE AS \"XBLNR\",\n" +
            //"       ODN.SAP_DELIVERY_NOTE AS \"SPMSID\",\n" +
            "       REPLACE(ODN.ARRIVAL_DATE, '-', '') AS \"BLDAT\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"BUDAT\",\n" +
            "		(CASE WHEN ODN.FACTORY_TYPE = '0' THEN 'Z53' ELSE 'Z15' END) AS \"BWART\",\n" +
            "       ODN.SAP_DELIVERY_NOTE || ODN.REMARKS AS \"BKTXT\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "WHERE SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHeadAnomalyPost(@Param("sapDeliveryNote") String sapDeliveryNote,
                                            @Param("operateType") String operateType,
                                            @Param("postingDate") String postingDate);

    /** 收货少收请求SAP结构，成本中心接口SAP撤销 */
    @Select("SELECT ODN.UNDER_VOUCHER_NUMBER AS \"I_MBLNR\",\n" +
            "       ODN.UNDER_VOUCHER_YEAR AS \"I_MJAHR\",\n" +
            "       REPLACE(#{cancelUnderPostingDate}, '-', '') AS \"I_BUDAT\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "WHERE ODN.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHeadCancelAnomalyPost(@Param("sapDeliveryNote") String sapDeliveryNote,
                                                  @Param("cancelUnderPostingDate") String cancelUnderPostingDate);

    @Select("<script>" +
            "SELECT ODN.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       ODN.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODN.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       ODN.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       ODN.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       ODN.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ODN.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       ODN.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       ODN.ARRIVAL_DATE AS \"arrivalDate\",\n" +
            "       ODN.REMARKS AS \"remarks\",\n" +
            "       ODN.COMMENTS AS \"comments\",\n" +
            "       ODN.POSTING_DATE AS \"postingDate\",\n" +
            "       ODN.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       ODN.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       ODN.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       ODN.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       ODN.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       ODN.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPostingDate\",\n" +
            "       ODN.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       ODN.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       ODN.SAP_REMARK AS \"sapRemark\",\n" +
            "       ODN.DATA_VERSION AS \"dataVersion\",\n" +
            "       ODN.COST_CENTER AS \"costCenter\",\n" +
            "       OPL.MERCHANDISER AS \"merchandiser\",\n" +
            "       OPL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       OPL.FROM_PORT AS \"fromPort\",\n" +
            "       OPL.TO_PORT AS \"toPort\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"actualWerksReceive\",\n" +
            "       ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"actualWhsLocationCodeReceive\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "    LEFT JOIN OVERSEA_PACKAGE_LIST OPL ON ODN.SAP_PACKAGE_LIST = OPL.SAP_PACKAGE_LIST\n" +
            "WHERE ODN.SPMS_STATUS NOT IN ('0', '11', '12') " +
            "  <when test='werks!=null and werks != \"\"'> AND ODN.WERKS_RECEIVE = #{werks}</when>" +
            "   AND TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-MM-dd'),'yyyy-MM-dd') - " +
            "                 TO_DATE(ODN.ARRIVAL_DATE, 'yyyy-MM-dd')) >= 14 " +
            "   ORDER BY ODN.WERKS_RECEIVE, TO_NUMBER(ODN.SPMS_STATUS) ASC, ODN.SAP_DELIVERY_NOTE ASC" +
            "</script>")
    List<Map<String, Object>> getOverdueDeliveryNote(@Param("werks") String werks);
    
    @Select("<script>" +
    		"SELECT ODND.AUTO_ID_DELIVERY_NOTE AS \"autoIdDeliveryNote\",\n" +
            "       ODND.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODND.SAP_DELIVERY_NOTE_ITEM AS \"sapDeliveryNoteItem\",\n" +
            "       ODND.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       (CASE WHEN SAP.DENOMINATOR IS NULL THEN 1 ELSE SAP.DENOMINATOR END) AS \"denominator\",\n" +
            "       (CASE WHEN SAP.NUMERATOR IS NULL THEN 1 ELSE SAP.NUMERATOR END) AS \"numerator\",\n" +
            "       ODND.BASIC_UNIT AS \"basicUnit\",\n" +
            "       ODND.UNIT_SALES AS \"unitSales\",\n" +
            "       ODND.RECEIVABLE_QTY_BASIC_UNIT AS \"receivableQtyBasicUnit\",\n" +
            "       ODND.RECEIVABLE_QTY_UNIT_SALES AS \"receivableQtyUnitSales\",\n" +
            "       ODND.ACTUAL_QTY_BASIC_UNIT AS \"actualQtyBasicUnit\",\n" +
            "       ODND.ACTUAL_QTY_UNIT_SALES AS \"actualQtyUnitSales\",\n" +
            "       ODND.PURCHASE_NO AS \"purchaseNo\",\n" +
            "       ODND.PURCHASE_NO_ITEM AS \"purchaseNoItem\",\n" +
            "       ODND.REMARKS AS \"remarks\",\n" +
            "       ODND.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       ODND.WHS_LOCATION_CODE_DELIVERY AS \"whsLocationCodeDelivery\",\n" +
            "       ODND.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       ODND.WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCodeReceive\",\n" +
            "       ODND.OVERCHARGED_QTY_BASIC_UNIT AS \"overchargedQtyBasicUnit\",\n" +
            "       ODND.OVERCHARGED_QTY_UNIT_SALES AS \"overchargedQtyUnitSales\",\n" +
            "       ODND.UNDERCHARGED_QTY_BASIC_UNIT AS \"underchargedQtyBasicUnit\",\n" +
            "       ODND.UNDERCHARGED_QTY_UNIT_SALES AS \"underchargedQtyUnitSales\",\n" +
            "       ODND.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       ODND.SAP_REMARK AS \"sapRemark\",\n" +
            "       ODND.DATA_VERSION AS \"dataVersion\" \n" +
            "FROM OVERSEA_DELIVERY_NOTE_DTL ODND\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON ODND.MATERIAL_NO = SAP.MATERIAL_NO " +
            "WHERE 1=1 AND ODND.SAP_DELIVERY_NOTE = #{dto.sapDeliveryNote} \n" +
            "	<when test='dto.searchInfo != null and dto.searchInfo != \"\"'> AND (ODND.MATERIAL_NO = #{dto.searchInfo} OR SAP.MATERIAL_ZH_DESC LIKE concat(concat('%',#{dto.searchInfo}),'%') OR SAP.MATERIAL_EN_DESC LIKE concat(concat('%',#{dto.searchInfo}),'%'))</when> \n" +
            "ORDER BY TO_NUMBER(ODND.SAP_DELIVERY_NOTE_ITEM) ASC \n" +
            "</script>")
	List<Map<String, Object>> selectDnItems(@Param("dto") OverseaDeliveryNoteItemDTO dto);
    
    @Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update OVERSEA_DELIVERY_NOTE_DTL set \n" +
    		"		WAERS = #{item.waers,jdbcType=VARCHAR}, \n" +
    		"		DMBTR = #{item.dmbtr,jdbcType=VARCHAR} \n" +
    		"	where SAP_DELIVERY_NOTE = #{item.sapDeliveryNote,jdbcType=VARCHAR} \n" +
    		"		and SAP_DELIVERY_NOTE_ITEM = #{item.sapDeliveryNoteItem,jdbcType=VARCHAR} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtl(List<OverseaDeliveryNoteDtl> list);
    
    @Select("SELECT OWI.UUID AS \"uuid\",\n" +
            "       OWI.SPMS_ID AS \"spmsId\",\n" +
            "       OWI.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       OWI.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OWI.WERKS AS \"werks\",\n" +
            "       OWI.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OWI.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWI.CREATE_TIME AS \"createTime\"\n" +
            "FROM OVERSEA_WHS_INVENTORY_LOSS OWI\n" +
            "WHERE OWI.SAP_DELIVERY_NOTE = #{sapDeliveryNote} \n" +
            "  AND OWI.SPMS_STATUS != '0' ")
	List<OverseaWhsInventoryLoss> getLossValidateTask(@Param("sapDeliveryNote") String sapDeliveryNote);
}