package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaDnSearchDTO;
import com.sunda.spmsoversea.entity.OverseaDn;
import com.sunda.spmsoversea.entity.OverseaDnDtl;
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-12-14
 */
@Mapper
public interface OverseaDnMapper extends BaseMapper<OverseaDn> {

    /** 获取转储交货单表头分页列表 */
    @Select("<script>" +
            "SELECT D.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       D.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       D.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       D.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       D.WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCodeReceive\",\n" +
            "       D.SAP_CREATE_DATE AS \"sapCreateDate\",\n" +
            "       D.DELIVERY_DATE AS \"deliveryDate\",\n" +
            "       D.ARRIVAL_DATE AS \"arrivalDate\",\n" +
            "       D.REMARKS AS \"remarks\",\n" +
            "       D.COMMENTS AS \"comments\",\n" +
            "       RE.SAP_PURCHASE_ORDER AS \"sapPurchaseOrder\",\n" +
            "       RE.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       RE.SUPPLIER_CODE AS \"supplierCode\",\n" +
            "       RE.CREATE_USER AS \"createUser\",\n" +
            "       D.POSTING_DATE AS \"postingDate\",\n" +
            "       D.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       D.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       D.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       D.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       D.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       D.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       D.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       D.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       D.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPosting\",\n" +
            "       D.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       D.COST_CENTER AS \"costCenter\",\n" +
            "       D.DATA_VERSION AS \"dataVersion\",\n" +
            "       D.CANCEL_REASON AS \"cancelReason\",\n" +
            "       D.BUSINESS_TYPE AS \"businessType\"\n" +
            "FROM OVERSEA_DN D\n" +
            "    LEFT JOIN (\n" +
            "        SELECT DISTINCT DD.SAP_DELIVERY_NOTE, DD.WERKS_DELIVERY,\n" +
            "               PO.SAP_PURCHASE_ORDER, PO.SUPPLIER_CODE, PO.CREATE_USER\n" +
            "        FROM OVERSEA_DN_DTL DD\n" +
            "            LEFT JOIN OVERSEA_PURCHASE_ORDER PO ON DD.SAP_PURCHASE_ORDER = PO.SAP_PURCHASE_ORDER\n" +
            "        GROUP BY DD.SAP_DELIVERY_NOTE, DD.WERKS_DELIVERY, PO.SAP_PURCHASE_ORDER, PO.SUPPLIER_CODE, PO.CREATE_USER\n" +
            "        ) RE ON D.SAP_DELIVERY_NOTE = RE.SAP_DELIVERY_NOTE\n" +
            "WHERE 1=1 \n" +
            "  <when test='dto.sapDeliveryNote!=null and dto.sapDeliveryNote != \"\"'> AND D.SAP_DELIVERY_NOTE LIKE concat(concat('%',#{dto.sapDeliveryNote}),'%') </when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND D.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.werksDelivery!=null and dto.werksDelivery != \"\"'> AND RE.WERKS_DELIVERY = #{dto.werksDelivery}</when>" +
            "  <when test='dto.werksReceive!=null and dto.werksReceive != \"\"'> AND D.WERKS_RECEIVE = #{dto.werksReceive}</when>" +
            "  <when test='dto.businessType!=null and dto.businessType != \"\"'> AND D.BUSINESS_TYPE = #{dto.businessType}</when>" +
            "  <when test='dto.deliveryDateBegin!=null and dto.deliveryDateBegin != \"\"'> AND TO_DATE(D.DELIVERY_DATE, 'yyyyMMdd') &gt;= TO_DATE(#{dto.deliveryDateBegin}, 'yyyy-MM-dd') </when>" +
            "  <when test='dto.deliveryDateEnd!=null and dto.deliveryDateEnd != \"\"'> AND TO_DATE(D.DELIVERY_DATE, 'yyyyMMdd') &lt;= TO_DATE(#{dto.deliveryDateEnd}, 'yyyy-MM-dd') </when>" +
            " <when test='dto.werksReceiveList != null'> AND D.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 RE.WERKS_DELIVERY in \n" +
            "    <foreach item='item' index='index' collection='dto.werksDeliveryList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "    </foreach>\n" +
            " </when>\n" +
            "  ORDER BY D.UPDATE_TIME DESC, D.SPMS_STATUS ASC, D.SAP_DELIVERY_NOTE ASC " +
            "</script>")
    List<Map<String, Object>> getDnPage(IPage page, @Param("dto") OverseaDnSearchDTO overseaDnSearchDTO);

    @Select("SELECT D.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       D.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       D.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       D.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       D.WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCodeReceive\",\n" +
            "       D.SAP_CREATE_DATE AS \"sapCreateDate\",\n" +
            "       D.DELIVERY_DATE AS \"deliveryDate\",\n" +
            "       D.ARRIVAL_DATE AS \"arrivalDate\",\n" +
            "       D.REMARKS AS \"remarks\",\n" +
            "       D.COMMENTS AS \"comments\",\n" +
            "       RE.SAP_PURCHASE_ORDER AS \"sapPurchaseOrder\",\n" +
            "       RE.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       RE.SUPPLIER_CODE AS \"supplierCode\",\n" +
            "       RE.CREATE_USER AS \"createUser\",\n" +
            "       D.POSTING_DATE AS \"postingDate\",\n" +
            "       D.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       D.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       D.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       D.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       D.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       D.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       D.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       D.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       D.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPosting\",\n" +
            "       D.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       D.COST_CENTER AS \"costCenter\",\n" +
            "       D.DATA_VERSION AS \"dataVersion\",\n" +
            "       D.BUSINESS_TYPE AS \"businessType\"\n" +
            "FROM OVERSEA_DN D\n" +
            "    LEFT JOIN (\n" +
            "        SELECT ROWNUM, DD.SAP_DELIVERY_NOTE, DD.WERKS_DELIVERY,\n" +
            "               PO.SAP_PURCHASE_ORDER, PO.SUPPLIER_CODE, PO.CREATE_USER\n" +
            "        FROM OVERSEA_DN_DTL DD\n" +
            "            LEFT JOIN OVERSEA_PURCHASE_ORDER PO ON DD.SAP_PURCHASE_ORDER = PO.SAP_PURCHASE_ORDER\n" +
            "        WHERE ROWNUM = 1 AND DD.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "        ) RE ON D.SAP_DELIVERY_NOTE = RE.SAP_DELIVERY_NOTE\n" +
            "WHERE D.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> getDnInfo(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 转储收货，按应收收货提交SAP表头结构 */
    @Select("SELECT OD.SAP_DELIVERY_NOTE AS \"XBLNR\",\n" +
            "       OD.SAP_DELIVERY_NOTE AS \"VBELN_IM\",\n" +
            "       OD.SAP_DELIVERY_NOTE AS \"VBELN\",\n" +
            "       OD.SAP_DELIVERY_NOTE AS \"LYDJH\",\n" +
            "       REPLACE(OD.ARRIVAL_DATE, '-', '') AS \"BLDAT\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"BUDAT\",\n" +
            "       #{userId} AS \"CREATEDBY\",\n" +
            "       '101' AS \"BWART\",\n" +
            "       OD.REMARKS AS \"BKTXT\",\n" +
            "       (CASE WHEN OD.BUSINESS_TYPE = '02' THEN 'A' ELSE '' END) AS \"SHLX\",\n" +
            "       RE.SAP_PURCHASE_ORDER AS \"EBELN\"\n" +
            "FROM OVERSEA_DN OD\n" +
            "    LEFT JOIN (\n" +
            "        SELECT ROWNUM, ODD.SAP_PURCHASE_ORDER,\n" +
            "               ODD.SAP_DELIVERY_NOTE\n" +
            "        FROM OVERSEA_DN_DTL ODD\n" +
            "        WHERE ROWNUM = 1\n" +
            "          AND ODD.SAP_DELIVERY_NOTE = #{sapDeliveryNote})\n" +
            "        RE ON OD.SAP_DELIVERY_NOTE = RE.SAP_DELIVERY_NOTE\n" +
            "WHERE OD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHead(@Param("sapDeliveryNote") String sapDeliveryNote,
                                 @Param("postingDate") String postingDate,
                                 @Param("userId") String userId);

    /** 转储收货，按应收收货提交SAP行项目结构 */
    @Select("SELECT ODD.SAP_DELIVERY_NOTE AS \"VBELN_IM\",\n" +
            "       ODD.SAP_DELIVERY_NOTE_ITEM AS \"VBELP_IM\",\n" +
            "       ODD.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(ODD.RECEIVABLE_QTY_BASIC_UNIT) AS \"BSTMG\",\n" +
            "       ODD.BASIC_UNIT AS \"BSTME\",\n" +
            "       ODD.WERKS_RECEIVE AS \"WERKS\",\n" +
            "       ODD.WHS_LOCATION_CODE_RECEIVE AS \"LGORT\",\n" +
            "       NVL(ODD.REMARKS, ' ') AS \"SGTXT\",\n" +
            "       ODD.SAP_PURCHASE_ORDER AS \"EBELN\",\n" +
            "       ODD.SAP_PURCHASE_ORDER_ITEM AS \"EBELP\"\n" +
            "FROM OVERSEA_DN_DTL ODD\n" +
            "WHERE ODD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} AND ODD.RECEIVABLE_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY TO_NUMBER(ODD.SAP_DELIVERY_NOTE_ITEM)")
    List<Map<String, Object>> dataItems(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 转储收货，02——本地采购，按实收收货提交SAP行项目结构 */
    @Select("SELECT ODD.SAP_DELIVERY_NOTE AS \"VBELN_IM\",\n" +
            "       ODD.SAP_DELIVERY_NOTE_ITEM AS \"VBELP_IM\",\n" +
            "       ODD.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(ODD.ACTUAL_QTY_BASIC_UNIT) AS \"BSTMG\",\n" +
            "       ODD.BASIC_UNIT AS \"BSTME\",\n" +
            "       ODD.WERKS_RECEIVE AS \"WERKS\",\n" +
            "       ODD.WHS_LOCATION_CODE_RECEIVE AS \"LGORT\",\n" +
            "       NVL(ODD.REMARKS, ' ') AS \"SGTXT\",\n" +
            "       ODD.SAP_PURCHASE_ORDER AS \"EBELN\",\n" +
            "       ODD.SAP_PURCHASE_ORDER_ITEM AS \"EBELP\"\n" +
            "FROM OVERSEA_DN_DTL ODD\n" +
            "WHERE ODD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} AND ODD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY TO_NUMBER(ODD.SAP_DELIVERY_NOTE_ITEM)")
    List<Map<String, Object>> actualDataItems(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 转储收货，少收提交SAP表头结构 */
    @Select("SELECT REPLACE(OD.ARRIVAL_DATE, '-', '') AS \"BLDAT\",\n" +
            "       REPLACE(#{underPostingDate}, '-', '') AS \"BUDAT\",\n" +
            "       OD.WERKS_RECEIVE AS \"WERKS\",\n" +
            "       OD.SAP_DELIVERY_NOTE AS \"XBLNR\",\n" +
            //"       OD.SAP_DELIVERY_NOTE AS \"SPMSID\",\n" +
            "       OD.WHS_LOCATION_CODE_RECEIVE AS \"LGORT\",\n" +
            "       'Z15' AS \"BWART\",\n" +
            "       NVL(OD.REMARKS, ' ') AS \"BKTXT\"\n" +
            "FROM OVERSEA_DN OD\n" +
            "WHERE OD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHeadAnomalyPost(@Param("sapDeliveryNote") String sapDeliveryNote,
                                            @Param("underPostingDate") String underPostingDate);

    /** 转储收货，少收提交SAP行项目结构 */
    @Select("SELECT ODD.SAP_DELIVERY_NOTE_ITEM AS \"ZEILE\",\n" +
            "       ODD.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(ODD.RECEIVABLE_QTY_BASIC_UNIT - ODD.ACTUAL_QTY_BASIC_UNIT) AS \"MENGE\",\n" +
            "       ODD.BASIC_UNIT AS \"MEINS\",\n" +
            "       #{costCenter} AS \"KOSTL\",\n" +
            "       W.SUPPLIER_NO AS \"LIFNR\",\n" +
            "       ODD.WERKS_RECEIVE AS \"WERKS\",\n" +
            "       ODD.WHS_LOCATION_CODE_RECEIVE AS \"LGORT\",\n" +
            "       ODD.REMARKS AS \"SGTXT\"\n" +
            "FROM OVERSEA_DN_DTL ODD\n" +
            "    LEFT JOIN OVERSEA_DN OD ON ODD.SAP_DELIVERY_NOTE = OD.SAP_DELIVERY_NOTE\n" +
            "    LEFT JOIN WERKS W ON ODD.WERKS_RECEIVE = W.WERKS\n" +
            "WHERE ODD.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "  AND ODD.RECEIVABLE_QTY_BASIC_UNIT - ODD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY TO_NUMBER(ODD.SAP_DELIVERY_NOTE_ITEM) ")
    List<Map<String, Object>> dataItemsAnomalyPost(@Param("sapDeliveryNote") String sapDeliveryNote,
                                                   @Param("costCenter") String costCenter);

    /** 收货 ZZXD_IF_SPMS_04 撤销接口信息获取 */
    @Select("SELECT OD.SAP_VOUCHER_NUMBER AS \"MBLNR\",\n" +
            "       OD.SAP_VOUCHER_YEAR AS \"MJAHR\",\n" +
            "       OD.SAP_DELIVERY_NOTE AS \"LYDJH\",\n" +
            "       OD.MANDT AS \"CLIENT\",\n" +
            "       REPLACE(#{cancelPostingDate}, '-', '') AS \"BUDAT\"\n" +
            "FROM OVERSEA_DN OD\n" +
            "WHERE OD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHeadCancelMigo(@Param("sapDeliveryNote") String sapDeliveryNote,
                                           @Param("cancelPostingDate") String cancelPostingDate);

    /** 少收 Z_SPMS_ANOMALY_POST 撤销接口信息获取 */
    @Select("SELECT OD.UNDER_VOUCHER_NUMBER AS \"I_MBLNR\",\n" +
            "       OD.UNDER_VOUCHER_YEAR AS \"I_MJAHR\",\n" +
           // "       OD.SAP_DELIVERY_NOTE AS \"LYDJH\",\n" +
            "       REPLACE(#{cancelUnderPostingDate}, '-', '') AS \"I_BUDAT\"\n" +
            "FROM OVERSEA_DN OD\n" +
            "WHERE OD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHeadCancelAnomaly(@Param("sapDeliveryNote") String sapDeliveryNote,
                                              @Param("cancelUnderPostingDate") String cancelUnderPostingDate);
    @Select("SELECT \n" +
            "	COUNT(1) \n" +
            "FROM OVERSEA_OTHER_BATCH_RECEIPT OH\n" +
            "WHERE OH.SAP_DELIVERY_NOTE_RELATION = #{sapDeliveryNote} \n" +
            "	AND OH.SPMS_STATUS != '0' AND OH.SPMS_STATUS != '5'" )
	int selectCountByBatchReceipt(@Param("sapDeliveryNote") String sapDeliveryNote);
    
    @Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update OVERSEA_DN_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<OverseaDnDtl> list);
    
    @Select("SELECT \n" +
            "	COUNT(1) \n" +
            "FROM OVERSEA_WHS_INVENTORY_LOSS OH\n" +
            "WHERE OH.SAP_DELIVERY_NOTE = #{sapDeliveryNote} \n" +
            "	AND OH.SPMS_STATUS != '0' " )
	int getLossValidateTask(@Param("sapDeliveryNote") String sapDeliveryNote);
    
    @Select("SELECT \n" +
    		"	COUNT(1) \n" +
    		"FROM OVERSEA_OTHER_BATCH_RECEIPT OH\n" +
    		"WHERE OH.SAP_DELIVERY_NOTE_RELATION = #{sapDeliveryNote} \n" +
    		"	AND OH.WERKS = #{werks} \n" +
    		"	AND OH.SPMS_STATUS != '0' " )
    int getBatchReceiptNotDeleteCount(@Param("sapDeliveryNote") String sapDeliveryNote, @Param("werks") String werks);

}