
/****** Object:  StoredProcedure [dbo].[sp_REAP1_GetWholesalerDataItem]   Script Date: 2020/07/01 17:10:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_REAP1_GetWholesalerDataItem]	
	@EAN VARCHAR(MAX) = '',	
	@WholesalerUniqueName VARCHAR(MAX) = '',
	@RowNum INT = 0	
AS
BEGIN
	--For testing:
	--DECLARE
	-- @EAN VARCHAR(MAX) = '',	
	-- @WholesalerUniqueName VARCHAR(MAX) = 'TestWholesaler',
	-- @RowNum int = 5		

	SET NOCOUNT ON;
	DECLARE @itemIDList VARCHAR(max), @SurfaceID INT,@isPublished BIT = 0, @tableName VARCHAR(500) = '', 
	@query NVARCHAR(max) = '', @eanList VARCHAR(max);
	SELECT @isPublished = isPublished FROM pal_Surface
	WHERE name = 'ProductAlternateEAN';
	IF @isPublished = 0
	BEGIN
		SET @tableName = 'qt_ProductAlternateEAN';
	END
	ELSE
	BEGIN
		SET @tableName = 'publ_ProductAlternateEAN';
	END;

	SELECT DISTINCT TOP(@RowNum) @itemIDList = COALESCE(@itemIDList + ',', '') + CONVERT(VARCHAR, itemID) 
	FROM publ_WholesalerDataItems 
	WHERE WholesalerDataItems_WholesalerDataItems_WholesalerUniqueName = @WholesalerUniqueName
	AND isActive = 1
	AND WholesalerDataItems_WholesalerDataItems_Barcode = @ean;

	IF ISNULL(LEN(@itemIDList), 0) = 0
	BEGIN
		SET @query = 
		CONCAT('SELECT @eanList = COALESCE(@eanList + '','', '''') + CONCAT(CHAR(39), ProductAlternateEAN_AlternateEANDetails_AlternateEAN, CHAR(39))',
		'FROM ', @tableName, ' WHERE ParentSurfaceItemId IN ',
		'(SELECT ParentSurfaceItemId FROM ', @tableName,
		' WHERE ProductAlternateEAN_AlternateEANDetails_AlternateEAN = ',CHAR(39), @EAN, CHAR(39),')');		
		EXECUTE sp_executeSQL @query, N'@eanList varchar(max) OUT', @eanList OUT;

		IF ISNULL(LEN(@eanList), 0) <> 0
		BEGIN
			SET @query = 
			CONCAT('select @itemIDList = COALESCE(@itemIDList + '','', '''') + CONVERT(VARCHAR, itemID)', 
			' from publ_WholesalerDataItems where WholesalerDataItems_WholesalerDataItems_WholesalerUniqueName = ', 
			CONCAT(CHAR(39), @WholesalerUniqueName, CHAR(39)),
			' and WholesalerDataItems_WholesalerDataItems_Barcode in (',@eanList, ')',
			' and isActive = 1');

			EXECUTE sp_executeSQL @query, N'@itemIDList varchar(max) OUT', @itemIDList OUT;
		END;
	END;

	IF ISNULL(LEN(@itemIDList), 0) = 0
	BEGIN
		SET @itemIDList = '0';
	END;
	SET @query = 
	CONCAT('SELECT TOP(',@RowNum,') * FROM publ_WholesalerDataItems WHERE itemID IN (', @itemIDList, ') order by itemID');
	EXECUTE(@query);	
END
GO


