USE [Framework_REAP-1_Dev]
GO

/****** Object:  StoredProcedure [dbo].[sp_REAP1_RetailerDataImport]    Script Date: 2020/07/06 10:46:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[sp_REAP1_RetailerDataImport]	
	@EAN VARCHAR(MAX) = '',	
	@RetailerName VARCHAR(MAX) = '',	
	@RetailerCode VARCHAR(MAX) = '',	
	@CostPrice DECIMAL(18,2) = 0.0,
	@ImportedBy VARCHAR(MAX) = '',	
	@UserId INT = 217,	
	@ParentSurfaceItemId INT = 0,	
	@FileName VARCHAR(MAX) = ''
AS
BEGIN
	--For testing:
	--DECLARE
	-- @EAN VARCHAR(MAX) = '',	
	-- @RetailerName VARCHAR(MAX) = 'TestRetailer',	
	-- @RetailerCode VARCHAR(MAX) = '000',	
	-- @CostPrice DECIMAL(18,2) = 0.0,
	-- @ImportedBy VARCHAR(MAX) = 'Dylan Reevell',	
	-- @UserId INT = 217,		
	-- @ParentSurfaceItemId INT = 0,	
	-- @FileName VARCHAR(MAX) = 'TestFileName'

	SET NOCOUNT ON;
	DECLARE @itemID INT = 0, @SurfaceID INT,@isPublished bit = 0, @tableName varchar(500) = '', 
	@query nvarchar(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 @SurfaceID = recId FROM pal_Surface WHERE name = 'RetailerDataItems';
	--get retailer data item	
	SELECT @itemID = itemID 
	FROM publ_RetailerDataItems 
	WHERE isActive = 1
	AND RetailerDataItems_RetailerDataItems_Barcode = @EAN
	AND RetailerDataItems_RetailerDataItems_RetailerName = @RetailerName;
	
	IF(@itemID <> 0)
	BEGIN
		--exists -> update
		UPDATE publ_RetailerDataItems
		SET RetailerDataItems_RetailerDataItems_CostPrice = @CostPrice,
		RetailerDataItems_RetailerDataItems_LastUpdated = CONVERT(varchar, GETDATE(), 120),
		RetailerDataItems_RetailerDataItems_UpdatedBy = @ImportedBy
		WHERE itemID = @itemID;
	END
	ELSE
	BEGIN
		--not exists -> create
		--create item
		INSERT INTO [dbo].[pal_SurfaceItem]
			   ([surfaceId]
			   ,[dateCreated]
			   ,[isActive]
			   ,[createdBy]
			   ,[updatedBy]
			   ,[dateUpdated]
			   ,[isDeleted]
			   ,[isWizardCompleted]
			   ,[lastTabCompleted]
			   ,[userLink]
			   ,[surfaceLinkItems])
		 VALUES
			   (@SurfaceID
			   ,GETDATE()
			   ,1
			   ,@UserId
			   ,0
			   ,CAST('1900-01-01 00:00:00' AS DATETIME)
			   ,0
			   ,0
			   ,0
			   ,0
			   ,'');
		SET @itemID = SCOPE_IDENTITY();

		INSERT INTO [dbo].[publ_RetailerDataItems]
           ([itemID]
           ,[isActive]
           ,[surfaceId]
           ,[ParentSurfaceItemId]
           ,[RetailerDataItems_RetailerDataItems_Barcode]
           ,[RetailerDataItems_RetailerDataItems_RetailerName]
           ,[RetailerDataItems_RetailerDataItems_RetailerCode]
           ,[RetailerDataItems_RetailerDataItems_CostPrice]
           ,[RetailerDataItems_RetailerDataItems_LastUpdated]
           ,[RetailerDataItems_RetailerDataItems_UpdatedBy]
           ,[RetailerDataItems_RetailerDataItems_ImportFileName])
		VALUES
           (@itemID
           ,1
           ,@SurfaceID
           ,@ParentSurfaceItemId
           ,@EAN
           ,@RetailerName
           ,@RetailerCode
           ,@CostPrice
           ,CONVERT(varchar, GETDATE(), 120)
           ,@ImportedBy
           ,@FileName)
	END;
	--update Alternate records	
	SET @query =  
	CONCAT('select @result = COALESCE(@result + '','', '''') + CONVERT(VARCHAR, itemID) ',
	'from publ_RetailerDataItems where RetailerDataItems_RetailerDataItems_Barcode in ',
	'(select ProductAlternateEAN_AlternateEANDetails_AlternateEAN from ', @tableName, 
	' where ParentSurfaceItemId in (select ParentSurfaceItemId from ', @tableName,
	' where ProductAlternateEAN_AlternateEANDetails_AlternateEAN = ', CHAR(39), @EAN, CHAR(39), ') ',
	'and ProductAlternateEAN_AlternateEANDetails_AlternateEAN <> ', CHAR(39), @EAN, CHAR(39), ') ',
	'and RetailerDataItems_RetailerDataItems_RetailerName = ', CHAR(39), @RetailerName, CHAR(39), 
	' and isActive = 1');

	DECLARE @result varchar(max)
	EXECUTE sp_executeSQL @query, N'@result VARCHAR(MAX) OUT', @result out;
	PRINT(@result);

	IF LEN(@result) <> 0
	BEGIN
		SET @query = CONCAT('UPDATE publ_RetailerDataItems 
		SET RetailerDataItems_RetailerDataItems_CostPrice = ', @CostPrice, 
		', RetailerDataItems_RetailerDataItems_LastUpdated = ''', CONVERT(varchar, GETDATE(), 120),
		''', RetailerDataItems_RetailerDataItems_UpdatedBy = ''', @ImportedBy,
		''' WHERE itemID IN (', @result, ')');

		EXECUTE(@query);
	END;
	
	SELECT * 
	FROM publ_RetailerDataItems 
	WHERE isActive = 1
	AND RetailerDataItems_RetailerDataItems_Barcode = @EAN
	AND RetailerDataItems_RetailerDataItems_RetailerName = @RetailerName; 
	
END
GO


