
/****** Object:  StoredProcedure [dbo].[sp_REAP1_MatchPromotions]    Script Date: 2020/05/06 09:43:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_REAP1_MatchPromotions] 
	@masterNames VARCHAR(MAX) = '',
	@purchaseDate VARCHAR(MAX),
	@areas VARCHAR(MAX),
	@shoppingListItemId VARCHAR(50)
AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @query VARCHAR(MAX);

	SELECT @query = 
	'SELECT p.*, w.Wholesalers_WholesalerDetails_Logo, s.*
	FROM [publ_PromotionItems] p
	JOIN [qt_Wholesalers] w ON w.Wholesaler_WholesalerUniqueName = p.PromotionItems_PromotionDetails_WholesalerUniqueName
	LEFT JOIN [publ_ShoppingListSelections] s ON s.ShoppingListSelections_ShoppingListSelections_UnitEAN = p.PromotionItems_PromotionItem_UnitEAN
	AND s.ShoppingListSelections_ShoppingListSelections_WholesalerUniqueName = p.PromotionItems_PromotionDetails_WholesalerUniqueName
	AND s.ParentSurfaceItemId = '
	+ @shoppingListItemId
	+' AND s.isActive = 1'
	+' WHERE p.isActive = 1 
	AND PromotionItems_PromotionItem_ProductMasterName IN ('
	+ @masterNames +
	') AND CONVERT(DATE, ' 
	+ CHAR(39) + @purchaseDate + CHAR(39) 
	+', 111) BETWEEN CONVERT(DATE, PromotionItems_PromotionDetails_PromotionStartDate, 111) 
	AND CONVERT(DATE, PromotionItems_PromotionDetails_PromotionEndDate, 111)
	AND PromotionItems_PromotionItems_Area IN (' 
	+ @areas
	+ ') 
	AND ISNULL(PromotionItems_PromotionItem_UnitEAN, '''') != ''''
 	ORDER BY PromotionItems_PromotionItem_ProductDescription ASC, 
	PromotionItems_PromotionItem_UnitSize ASC, 
	PromotionItems_PromotionItem_Variant ASC,
	PromotionItems_PromotionItem_Price ASC';

	EXECUTE (@query);

END
GO