package com.sunda.spmsoversea.mapper;

import com.sunda.spmsoversea.dto.OverseaBatchReceiptSearchDto;
import com.sunda.spmsoversea.entity.OverseaOtherBatchReceipt;
import com.sunda.spmsoversea.entity.OverseaOtherBatchReceiptDtl;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
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 com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * <p>
 * 其他收货分批收货表头 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2023-02-10
 */
@Mapper
public interface OverseaOtherBatchReceiptMapper extends BaseMapper<OverseaOtherBatchReceipt> {
	
	@Select("SELECT \n" +
            "       OD.UUID AS \"uuid\",\n" +
            "       OD.OPERATE_TITLE AS \"operateTitle\",\n" +
            "       OD.SPMS_ID AS \"spmsId\",\n" +
            "       OD.WERKS AS \"werks\",\n" +
            "       OD.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       OD.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OD.SAP_RELATION_NUMBER AS \"sapRelationNumber\",\n" +
            "       OD.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OD.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OD.POSTING_DATE AS \"postingDate\",\n" +
            "       OD.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       OD.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       OD.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OD.SAP_DELIVERY_NOTE_RELATION AS \"sapDeliveryNoteRelation\",\n" +
            "       OD.SAP_PURCHASE_ORDER AS \"sapPurchaseOrder\",\n" +
            "       OD.REMARK AS \"remark\",\n" +
            "       OD.FBK1 AS \"fbk1\",\n" +
            "       OD.CREATE_TIME AS \"createTime\",\n" +
            "       OD.UPDATE_TIME AS \"updateTime\",\n" +
            "       OD.CREATE_USERID AS \"createUserid\",\n" +
            "       OD.UPDATE_USERID AS \"updateUserid\" \n" +
            "FROM OVERSEA_OTHER_BATCH_RECEIPT OD\n" +
            "WHERE OD.SAP_DELIVERY_NOTE_RELATION = #{dto.sapDeliveryNote} \n" +
            "	AND OD.SPMS_STATUS != '0' \n" +
            "ORDER BY OD.CREATE_TIME DESC \n"
            )
	List<Map<String, Object>> selectBatchReceiptList(@Param("dto") OverseaBatchReceiptSearchDto dto);
	
	@Select("SELECT \n" +
            "       OD.UUID AS \"uuid\",\n" +
            "       OD.ITEM_NUMBER AS \"itemNumber\",\n" +
            "       OD.SPMS_ID AS \"spmsId\",\n" +
            "       OD.MATERIAL_NO AS \"materialNo\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       OD.QUANTITY AS \"quantity\",\n" +
            "       OD.ACTUAL_QUANTITY AS \"actualQuantity\",\n" +
            "       OD.BASIC_UNIT AS \"basicUnit\",\n" +
            "       OD.UNIT_SALES AS \"unitSales\",\n" +
            "       OD.STORAGE_INFO AS \"storageInfo\",\n" +
            "       OD.STORAGE_NO AS \"storageNo\",\n" +
            "       OD.COST_CENTER AS \"costCenter\",\n" +
            "       OD.REMARK AS \"remark\",\n" +
            "       OD.ITEM_RELATION AS \"itemRelation\",\n" +
            "       OD.SAP_DELIVERY_NOTE_RELATION AS \"sapDeliveryNoteRelation\",\n" +
            "       OD.DEMAND_APPLICANT AS \"demandApplicant\",\n" +
            "       OD.DEMAND_WORKSHOP AS \"demandWorkshop\",\n" +
            "       OD.SAP_PURCHASE_ORDER AS \"sapPurchaseOrder\",\n" +
            "       OD.SAP_PURCHASE_ORDER_ITEM AS \"sapPurchaseOrderItem\",\n" +
            "       OD.CREATE_TIME AS \"createTime\",\n" +
            "       OD.UPDATE_TIME AS \"updateTime\",\n" +
            "       OD.CREATE_USERID AS \"createUserid\",\n" +
            "       OD.UPDATE_USERID AS \"updateUserid\" ,\n" +
            "       OD.WAERS AS \"waers\" ,\n" +
            "       OD.DMBTR AS \"dmbtr\" \n" +
            "FROM OVERSEA_OTHER_BATCH_RECEIPT_DTL OD\n" +
            "	LEFT JOIN MATERIAL_SAP MS ON OD.MATERIAL_NO = MS.MATERIAL_NO \n" +
            "WHERE OD.SPMS_ID = #{dto.spmsId} \n" +
            "ORDER BY OD.ITEM_NUMBER+0 \n"
            )
	List<Map<String, Object>> selectBatchReceiptDtlList(@Param("dto") OverseaBatchReceiptSearchDto overseaDnSearchDTO);
	
	@Select("SELECT OD.SAP_DELIVERY_NOTE_RELATION AS \"XBLNR\",\n" +
            "       OD.SAP_DELIVERY_NOTE_RELATION AS \"VBELN_IM\",\n" +
            //"       '' AS \"bldat\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"BUDAT\",\n" +
            "       #{userId} AS \"CREATEDBY\",\n" +
            "       '101' AS \"BWART\",\n" +
            "       OD.REMARK AS \"BKTXT\",\n" +
            "       OD.SPMS_ID AS \"LYDJH\",\n" +
            "       'A' AS \"SHLX\",\n" +
            "       OD.SAP_PURCHASE_ORDER AS \"EBELN\"\n" +
            "FROM OVERSEA_OTHER_BATCH_RECEIPT OD\n" +
            "WHERE OD.SPMS_ID = #{spmsId} ")
	Map<String, Object> dataHead(@Param("spmsId") String spmsId, @Param("postingDate") String postingDate, @Param("userId") String userId);
	
	@Select("SELECT ODD.SAP_DELIVERY_NOTE_RELATION AS \"VBELN_IM\",\n" +
            "       ODD.ITEM_RELATION AS \"VBELP_IM\",\n" +
            "       ODD.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(ODD.ACTUAL_QUANTITY) AS \"BSTMG\",\n" +
            "       ODD.BASIC_UNIT AS \"BSTME\",\n" +
            "       OB.WERKS AS \"WERKS\",\n" +
            "       OB.WHS_LOCATION_CODE AS \"LGORT\",\n" +
            "       NVL(ODD.REMARK, ' ') AS \"SGTXT\",\n" +
            "       ODD.SAP_PURCHASE_ORDER AS \"EBELN\",\n" +
            "       ODD.SAP_PURCHASE_ORDER_ITEM AS \"EBELP\"\n" +
            "FROM OVERSEA_OTHER_BATCH_RECEIPT_DTL ODD\n" +
            "	LEFT JOIN OVERSEA_OTHER_BATCH_RECEIPT OB ON ODD.SPMS_ID = OB.SPMS_ID \n" +
            "WHERE ODD.SPMS_ID = #{spmsId} AND ODD.ACTUAL_QUANTITY > 0\n" +
            "ORDER BY TO_NUMBER(ODD.ITEM_NUMBER)")
	List<Map<String, Object>> actualDataItems(@Param("spmsId") String spmsId);
	
	@Select("SELECT OD.SAP_VOUCHER_NUMBER AS \"MBLNR\",\n" +
            "       OD.SAP_VOUCHER_YEAR AS \"MJAHR\",\n" +
            "       OD.SPMS_ID AS \"LYDJH\",\n" +
            "       ODD.MANDT AS \"CLIENT\",\n" +
            "       REPLACE(#{cancelPostingDate}, '-', '') AS \"BUDAT\"\n" +
            "FROM OVERSEA_OTHER_BATCH_RECEIPT OD\n" +
            "LEFT JOIN OVERSEA_DN ODD ON ODD.SAP_DELIVERY_NOTE = OD.SAP_DELIVERY_NOTE_RELATION AND ODD.WERKS_RECEIVE = OD.WERKS \n" +
            "WHERE OD.SPMS_ID = #{spmsId} ")
	Map<String, Object> dataHeadCancelMigo(@Param("spmsId") String spmsId, @Param("cancelPostingDate") String cancelPostingDate);
	
	@Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update OVERSEA_OTHER_BATCH_RECEIPT_DTL set \n" +
    		"		WAERS = #{item.waers,jdbcType=VARCHAR}, \n" +
    		"		DMBTR = #{item.dmbtr,jdbcType=VARCHAR} \n" +
    		"	where SPMS_ID = #{item.spmsId,jdbcType=VARCHAR} \n" +
    		"		and ITEM_RELATION = #{item.itemRelation,jdbcType=VARCHAR} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtl(List<OverseaOtherBatchReceiptDtl> list);

}