USE [Framework_REAP-1_Dev]
GO

/****** Object:  StoredProcedure [dbo].[sp_REAP1_MatchPromotions_GetMasterNames]    Script Date: 2020/07/06 10:30:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





ALTER PROCEDURE [dbo].[sp_REAP1_MatchPromotions_GetMasterNames] 
	@unitEANs VARCHAR(MAX) = '',
	@purchaseDate VARCHAR(MAX),
	@areas VARCHAR(MAX)
AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @query NVARCHAR(MAX), @unitEANsForFiltering VARCHAR(MAX);
	SET @purchaseDate = CONCAT(CHAR(39), @purchaseDate, CHAR(39)); 
	SELECT @query = 
	'select @unitEANsForFiltering = COALESCE(@unitEANsForFiltering + '','', '''') + CONCAT(CHAR(39), p.Products_ProductDetails_UnitEAN, CHAR(39))
	from qt_ProductAlternateEAN a
	left join publ_Products p
	on p.itemID = a.ParentSurfaceItemId
	where a.ProductAlternateEAN_AlternateEANDetails_AlternateEAN in (' + @unitEANs + ')
	and p.isActive = 1';

	EXECUTE sp_executeSQL @query, N'@unitEANsForFiltering VARCHAR(MAX) OUT', @unitEANsForFiltering out;

	IF LEN(@unitEANsForFiltering) <> 0
	BEGIN
		SELECT @query = 'SELECT DISTINCT PromotionItems_PromotionItem_UnitEAN, PromotionItems_PromotionItem_ProductMasterName 
		FROM [publ_PromotionItems]
		WHERE PromotionItems_PromotionItem_UnitEAN IN (' 
		+ @unitEANsForFiltering
		+ ') AND CONVERT(DATE, ' + @purchaseDate + ', 111) BETWEEN CONVERT(DATE, PromotionItems_PromotionDetails_PromotionStartDate, 111) 
		AND CONVERT(DATE, PromotionItems_PromotionDetails_PromotionEndDate, 111)
		AND PromotionItems_PromotionItems_Area IN (' + @areas + ')';

		EXECUTE (@query);
	END
	ELSE
	BEGIN
		SELECT '' as PromotionItems_PromotionItem_UnitEAN, '' as  PromotionItems_PromotionItem_ProductMasterName;
	END;

END
GO


