package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmswms.entity.SapDeliveryNoteBatchReceipt;
import com.sunda.spmswms.entity.SapDeliveryNoteBatchReceiptDtl;

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-06-16
 */
@Mapper
public interface SapDeliveryNoteBatchReceiptMapper extends BaseMapper<SapDeliveryNoteBatchReceipt> {
	
	@Select("<script>" +
			"SELECT \n" +
            "       OD.UUID AS \"uuid\",\n" +
            "       OD.ITEM AS \"item\",\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.BOX_NOTE AS \"boxNote\",\n" +
            "       OD.BASIC_UNIT AS \"basicUnit\",\n" +
            "       OD.DELIVERY_QTY AS \"deliveryQty\",\n" +
            "       OD.RECEIVED_QTY AS \"receivedQty\",\n" +
            "       OD.UNIT_SALES AS \"unitSales\",\n" +
            "       OD.QTY_SALES AS \"qtySales\",\n" +
            "       OD.ACTUAL_QTY_SALES AS \"actualQtySales\",\n" +
            "       OD.TEMP_STORAGE AS \"tempStorage\",\n" +
            "       OD.STORAGE_UUID AS \"storageUuid\",\n" +
            "       OD.BOX_MATERIAL_NAME AS \"boxMaterialName\",\n" +
            "       OD.BOX_GROSS_WEIGHT AS \"boxGrossWeight\",\n" +
            "       OD.BOX_NET_WEIGHT AS \"boxNetWeight\",\n" +
            "       OD.BOX_COLOR AS \"boxColor\",\n" +
            "       OD.WAERS AS \"waers\",\n" +
            "       OD.DMBTR AS \"dmbtr\",\n" +
            "       OD.REMARK AS \"remark\",\n" +
            "       OD.VOLUME AS \"volume\",\n" +
            "       OD.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       OD.DELIVERY_NOTE_SAP_ITEM AS \"deliveryNoteSapItem\",\n" +
            "       OD.FBK1 AS \"fbk1\",\n" +
            "       OD.FBK2 AS \"fbk2\",\n" +
            "       OD.FBK3 AS \"fbk3\",\n" +
            "       OD.FBK4 AS \"fbk4\",\n" +
            "       OD.FBK5 AS \"fbk5\"\n" +
            "FROM SAP_DELIVERY_NOTE_BATCH_RECEIPT_DTL OD\n" +
            "	LEFT JOIN MATERIAL_SAP MS ON OD.MATERIAL_NO = MS.MATERIAL_NO \n" +
            "WHERE OD.SPMS_ID = #{spmsId} \n" +
            //" <when test='operationType!=null and operationType == \"pdaSN\" '> AND (OD.RECEIVED_QTY = 0 or OD.RECEIVED_QTY is null)</when> \n" +
            "ORDER BY OD.ITEM+0 \n" +
            "</script>")
	List<Map<String, Object>> selectBatchReceiptDtl(@Param("spmsId") String spmsId);
	
	@Select("SELECT \n" +
            "       SDNB.DELIVERY_NOTE_SAP AS \"XBLNR\",\n" +
            "       SDNB.DELIVERY_NOTE_SAP AS \"VBELN\",\n" +
            "       SDNB.DELIVERY_NOTE_SAP AS \"VBELN_IM\",\n" +
            "       SDNB.SPMS_ID AS \"LYDJH\",\n" +
            "       SDN.MANDT AS \"MANDT_PO\",\n" +
            "       'A' AS \"SHLX\",\n" +
            "       TO_CHAR(SDNB.CREATE_TIME, 'yyyyMMdd') AS \"BLDAT\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"BUDAT\",\n" +
            "       #{userId} AS \"CREATEDBY\",\n" +
            "       (CASE WHEN SDN.MOBILE_TYPE IS NOT NULL THEN SDN.MOBILE_TYPE ELSE '101' END) AS \"BWART\",\n" +
            "       SDNB.REMARKS AS \"BKTXT\"\n" +
            "FROM SAP_DELIVERY_NOTE_BATCH_RECEIPT SDNB\n" +
            "	LEFT JOIN SAP_DELIVERY_NOTE SDN ON SDNB.DELIVERY_NOTE_SAP = SDN.DELIVERY_NOTE_SAP \n" +
            "WHERE SDNB.UUID = #{uuid} \n"
            )
	Map<String, Object> dataHead(@Param("uuid") String uuid, @Param("postingDate") String postingDate, @Param("userId") String userId);
	
	@Select("SELECT DTL.DELIVERY_NOTE_SAP AS \"VBELN_IM\",\n" +
            "       DTL.DELIVERY_NOTE_SAP_ITEM AS \"VBELP_IM\",\n" +
            "       DTL.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(DTL.RECEIVED_QTY) AS \"BSTMG\",\n" +
            "       DTL.BASIC_UNIT AS \"BSTME\",\n" +
            "       SAP.WERKS AS \"WERKS\",\n" +
            "       SAP.LOCATION_CODE AS \"LGORT\",\n" +
            "       NVL(DTL.REMARK, '') AS \"SGTXT\"\n" +
            "FROM SAP_DELIVERY_NOTE_BATCH_RECEIPT_DTL DTL\n" +
            "    LEFT JOIN SAP_DELIVERY_NOTE_BATCH_RECEIPT SAP ON DTL.SPMS_ID = SAP.SPMS_ID\n" +
            "WHERE DTL.SPMS_ID = #{spmsId} AND DTL.RECEIVED_QTY > 0 ")
	List<Map<String, Object>> selectNoteItemM(@Param("spmsId") String spmsId);
	
	@Select("SELECT TEMP.DELIVERY_NOTE_SAP AS \"VBELN_IM\",\n" +
            "       TO_CHAR(DND.DELIVERY_NOTE_SAP_ITEM) AS \"VBELP_IM\",\n" +
            "       TO_CHAR(TEMP.DOMESTIC_QTY_RECEIVED) AS \"BSTMG\",\n" +
            "       DND.MATERIAL_NO AS \"MATNR\",\n" +
            "       DND.WERKS AS \"WERKS\",\n" +
            "       DND.WHS_LOCATION_CODE AS \"LGORT\",\n" +
            "       SAP.BASIC_UNIT AS \"BSTME\",\n" +
            "       '' AS \"SGTXT\"\n" +
            "FROM (\n" +
            "    SELECT BND.DELIVERY_NOTE_SAP,\n" +
            "           BND.DELIVERY_NOTE_SAP_ITEM,\n" +
            "           SUM(BND.DOMESTIC_QTY_RECEIVED) AS \"DOMESTIC_QTY_RECEIVED\"\n" +
            "    FROM SAP_DELIVERY_NOTE_BATCH_RECEIPT_DTL DTL \n" +
            "		LEFT JOIN SAP_BOX_NOTE_DTL BND ON DTL.BOX_NOTE = BND.BOX_NOTE \n" +
            "	 WHERE DTL.SPMS_ID = #{spmsId} \n" +
            "    GROUP BY BND.DELIVERY_NOTE_SAP_ITEM, BND.DELIVERY_NOTE_SAP ORDER BY BND.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>> selectNoteItemX(@Param("spmsId") String spmsId);
	
	@Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update SAP_DELIVERY_NOTE_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 DELIVERY_NOTE_SAP_ITEM = #{item.deliveryNoteSapItem,jdbcType=VARCHAR} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtl(List<SapDeliveryNoteBatchReceiptDtl> pa);
	
	@Select("SELECT \n" +
            "       SAP.MATERIAL_NO AS \"materialNo\", \n" +
            "       SAP.SPMS_MARK AS \"spmsMark\"\n" +
            "FROM MATERIAL_SAP SAP \n" +
            "    WHERE SAP.MATERIAL_NO = #{materialNo} \n")
	Map<String, Object> getMaterialSapInfo(@Param("materialNo") String materialNo);
	
	@Insert("INSERT INTO WHS_INVENTORY_INFO(WERKS, WHS_LOCATION_CODE, QUANTITY, UPDATE_USER, BOX_NOTE)\n" +
            "VALUES (#{werks}, #{locationCode}, 1, #{userId}, #{boxNote})")
	int addBoxNoteToWhsInventoryInfo(@Param("werks") String werks, @Param("locationCode") String locationCode, @Param("boxNote") String boxNote, @Param("userId") String userId);
	
	@Update("UPDATE SAP_BOX_NOTE_DTL BND SET BND.DOMESTIC_QTY_RECEIVABLE = 0, BND.DOMESTIC_QTY_RECEIVED = 0\n" +
            "WHERE BND.BOX_NOTE = #{boxNote}")
	int updateSapBoxNoteDtl(@Param("boxNote") String boxNote);

	@Select("<script>" +
			"SELECT \n" +
            "       OD.UUID AS \"uuid\",\n" +
            "       OD.SPMS_ID AS \"spmsId\",\n" +
            "       OD.WERKS AS \"werks\",\n" +
            "       OD.LOCATION_CODE AS \"locationCode\",\n" +
            "       OD.SRM_ORDER_NUMBER AS \"srmOrderNumber\",\n" +
            "       OD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OD.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       OD.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OD.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OD.POSTING_DATE AS \"postingDate\",\n" +
            "       OD.CANCEL_SAP_VOUCHER_NUMBER AS \"cancelSapVoucherNumber\",\n" +
            "       OD.CANCEL_SAP_VOUCHER_YEAR AS \"cancelSapVoucherYear\",\n" +
            "       OD.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OD.REMARKS AS \"remarks\",\n" +
            "       OD.OPERATE_USER AS \"operateUser\",\n" +
            "       OD.CREATE_USERID AS \"createUserid\",\n" +
            "       OD.CREATE_TIME AS \"createTime\",\n" +
            "       OD.UPDATE_TIME AS \"updateTime\",\n" +
            "       OD.PACKAGE_NOTE AS \"packageNote\",\n" +
            "       SU.LAST_NAME AS \"createUserName\",\n" +
            "       US.LAST_NAME AS \"updateUserName\",\n" +
            "       OD.PDA_SN AS \"pdaSn\",\n" +
            "       OD.FBK1 AS \"fbk1\",\n" +
            "       OD.FBK2 AS \"fbk2\",\n" +
            "       OD.FBK3 AS \"fbk3\",\n" +
            "       OD.FBK4 AS \"fbk4\",\n" +
            "       OD.FBK5 AS \"fbk5\"\n" +
            "FROM SAP_DELIVERY_NOTE_BATCH_RECEIPT OD\n" +
            "	LEFT JOIN SPMS_USER SU ON OD.CREATE_USERID = SU.WORK_CODE \n" +
            "	LEFT JOIN SPMS_USER US ON OD.UPDATE_USERID = US.WORK_CODE \n" +
            "WHERE OD.UUID = #{uuid} \n" +
            "</script>"
            )
	Map<String, Object> selectBatchReceiptHead(@Param("uuid") String uuid);

	@Select("SELECT \n" +
            "       OD.UUID AS \"uuid\",\n" +
            "       OD.SPMS_ID AS \"spmsId\",\n" +
            "       OD.WERKS AS \"werks\",\n" +
            "       OD.LOCATION_CODE AS \"locationCode\",\n" +
            "       OD.SRM_ORDER_NUMBER AS \"srmOrderNumber\",\n" +
            "       OD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OD.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       OD.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OD.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OD.POSTING_DATE AS \"postingDate\",\n" +
            "       OD.CANCEL_SAP_VOUCHER_NUMBER AS \"cancelSapVoucherNumber\",\n" +
            "       OD.CANCEL_SAP_VOUCHER_YEAR AS \"cancelSapVoucherYear\",\n" +
            "       OD.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OD.REMARKS AS \"remarks\",\n" +
            "       OD.OPERATE_USER AS \"operateUser\",\n" +
            "       OD.CREATE_TIME AS \"createTime\",\n" +
            "       OD.UPDATE_TIME AS \"updateTime\",\n" +
            "       OD.PACKAGE_NOTE AS \"packageNote\",\n" +
            "       SU.LAST_NAME AS \"createUserName\",\n" +
            "       US.LAST_NAME AS \"updateUserName\",\n" +
            "       OD.FBK1 AS \"fbk1\",\n" +
            "       OD.FBK2 AS \"fbk2\",\n" +
            "       OD.FBK3 AS \"fbk3\",\n" +
            "       OD.FBK4 AS \"fbk4\",\n" +
            "       OD.FBK5 AS \"fbk5\"\n" +
            "FROM SAP_DELIVERY_NOTE_BATCH_RECEIPT OD\n" +
            "	LEFT JOIN SPMS_USER SU ON OD.CREATE_USERID = SU.WORK_CODE \n" +
            "	LEFT JOIN SPMS_USER US ON OD.UPDATE_USERID = US.WORK_CODE \n" +
            "WHERE OD.SPMS_STATUS != '0' AND OD.DELIVERY_NOTE_SAP = #{deliveryNoteSap} \n" +
            "ORDER BY OD.SPMS_ID DESC \n"
            )
	List<Map<String, Object>> selectBatchReceiptHeadList(@Param("deliveryNoteSap") String deliveryNoteSap);



	@Select("<script>" +
			"SELECT \n" +
			"       OD.UUID AS \"uuid\",\n" +
			"       OD.SPMS_ID AS \"spmsId\",\n" +
			"       OD.WERKS AS \"werks\",\n" +
			"       OD.LOCATION_CODE AS \"locationCode\",\n" +
			"       OD.SRM_ORDER_NUMBER AS \"srmOrderNumber\",\n" +
			"       OD.SPMS_STATUS AS \"spmsStatus\",\n" +
			"       OD.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
			"       OD.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
			"       OD.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
			"       OD.POSTING_DATE AS \"postingDate\",\n" +
			"       OD.CANCEL_SAP_VOUCHER_NUMBER AS \"cancelSapVoucherNumber\",\n" +
			"       OD.CANCEL_SAP_VOUCHER_YEAR AS \"cancelSapVoucherYear\",\n" +
			"       OD.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
			"       OD.REMARKS AS \"remarks\",\n" +
			"       OD.OPERATE_USER AS \"operateUser\",\n" +
			"       OD.CREATE_TIME AS \"createTime\",\n" +
			"       OD.UPDATE_TIME AS \"updateTime\",\n" +
			"       OD.PACKAGE_NOTE AS \"packageNote\",\n" +
			"       SU.LAST_NAME AS \"createUserName\",\n" +
			"       US.LAST_NAME AS \"updateUserName\",\n" +
			"       OD.FBK1 AS \"fbk1\",\n" +
			"       OD.FBK2 AS \"fbk2\",\n" +
			"       OD.FBK3 AS \"fbk3\",\n" +
			"       OD.FBK4 AS \"fbk4\",\n" +
			"       OD.FBK5 AS \"fbk5\"\n" +
			"FROM SAP_DELIVERY_NOTE_BATCH_RECEIPT OD\n" +
			"	LEFT JOIN SAP_DELIVERY_NOTE SDN ON OD.DELIVERY_NOTE_SAP = SDN.DELIVERY_NOTE_SAP \n" +
			"	LEFT JOIN SPMS_USER SU ON OD.CREATE_USERID = SU.WORK_CODE \n" +
			"	LEFT JOIN SPMS_USER US ON OD.UPDATE_USERID = US.WORK_CODE \n" +
			"WHERE 1=1 AND SDN.PACKAGE_NOTE is not null \n" +
			" <when test='deliveryNoteSap!=null'> AND OD.DELIVERY_NOTE_SAP = #{deliveryNoteSap}</when> \n" +
			" <when test='werks!=null'> AND OD.WERKS = #{werks}</when> \n" +
			" <when test='whsLocationCode!=null'> AND OD.LOCATION_CODE = #{whsLocationCode}</when> \n" +
			" <when test='spmsStatus!=null'> AND OD.SPMS_STATUS = #{spmsStatus}</when> \n" +
			//" <when test='operationType!=null and operationType == \"pdaSN\" '> \n" +
			//"	AND EXISTS (SELECT 1 from SAP_DELIVERY_NOTE_DTL WHERE (RECEIVED_QTY = 0 or RECEIVED_QTY is null) and SDN.DELIVERY_NOTE_SAP = DELIVERY_NOTE_SAP) \n" +	
			//"</when> \n" +
			" <when test='werksLists != null'> AND OD.WERKS in \n" +
	        " 	<foreach item='item' index='index' collection='werksLists' open='(' separator=',' close=')'>\n" +
	        "      #{item} \n" +
	        "   </foreach>" +
	        "</when>\n" +
			"ORDER BY OD.SPMS_ID DESC \n"+
			"</script>")
	List<Map<String, Object>> selectBatchReceiptHeadPage(Page<Map<String, Object>> pages, 
														@Param("deliveryNoteSap") String deliveryNoteSap, 
														@Param("werks") String werks, 
														@Param("whsLocationCode") String whsLocationCode, 
														@Param("spmsStatus") String spmsStatus, 
														@Param("werksLists") List<String> werksLists);

}