package com.sunda.spmswms.mapper;

import com.sunda.spmswms.entity.WhsStorageInventoryLastMonth;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * <p>
 * 上月底库存结余数量表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2023-01-18
 */
@Mapper
@Component
public interface WhsStorageInventoryLastMonthMapper extends BaseMapper<WhsStorageInventoryLastMonth> {
	
	@Select("<script>" +
			"	SELECT DD.* FROM (" +
			"		SELECT \n" +
			"			WSI.MATERIAL_NO AS \"materialNo\", \n" +
			"			SUM(WSI.AVAILABLE_QTY) AS \"quantity\" ,\n" +
			"			NULL AS \"DeliveryNoteSap\" ,\n" +
			"			SYSDATE AS \"createTime\" \n" +
			"		FROM \n" +
			"			WHS_STORAGE_INVENTORY WSI \n" +
			"			LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID \n" +
			"		WHERE WS.WERKS = 'CN01' \n" +
			"		AND WSI.MATERIAL_NO IS NOT NULL \n" +
			"		GROUP BY WSI.MATERIAL_NO \n" +
			"		UNION ALL \n" +
			"		SELECT \n" +
			"			NULL AS \"materialNo\", \n" +
			"			SUM(WSI.AVAILABLE_QTY) AS \"quantity\" ,\n" +
			"			SBN.DELIVERY_NOTE_SAP AS \"DeliveryNoteSap\" ,\n" +
			"			SYSDATE AS \"createTime\" \n" +
			"		FROM \n" +
			"			WHS_STORAGE_INVENTORY WSI \n" +
			"			LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID \n" +
			"			LEFT JOIN SAP_BOX_NOTE_DTL SBN ON WSI.BOX_NOTE = SBN.BOX_NOTE \n" +
			"		WHERE WS.WERKS = 'CN01' \n" +
			"		AND WSI.BOX_NOTE IS NOT NULL \n" +
			"		AND SBN.DELIVERY_NOTE_SAP IS NOT NULL \n" +
			"		GROUP BY SBN.DELIVERY_NOTE_SAP \n" +
			"	) DD" +
			"</script>")
	List<WhsStorageInventoryLastMonth> selectMaterialDeliveryInventory();
	
	@Delete("<script>" +
			"DELETE FROM WHS_STORAGE_INVENTORY_LAST_MONTH \n" +
			"</script>")
	int deleteBatch();

}