
/****** Object:  StoredProcedure [dbo].[sp_REAP1_WholesalerDataImport]   Script Date: 2020/07/01 16:10:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_REAP1_WholesalerDataImport]	
	@EAN VARCHAR(MAX) = '',	
	@WholesalerUniqueName VARCHAR(MAX) = '',	
	@WholesalerCode 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) = '',	
	-- @WholesalerUniqueName VARCHAR(MAX) = 'TestWholesaler',	
	-- @WholesalerCode 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 = 'WholesalerDataItems';
	--get wholesaler data item	
	SELECT @itemID = itemID 
	FROM publ_WholesalerDataItems 
	WHERE isActive = 1
	AND WholesalerDataItems_WholesalerDataItems_Barcode = @EAN
	AND WholesalerDataItems_WholesalerDataItems_WholesalerUniqueName = @WholesalerUniqueName;
	
	IF(@itemID <> 0)
	BEGIN
		--exists -> update
		UPDATE publ_WholesalerDataItems
		SET WholesalerDataItems_WholesalerDataItems_CostPrice = @CostPrice,
		WholesalerDataItems_WholesalerDataItems_LastUpdated = CONVERT(varchar, GETDATE(), 120),
		WholesalerDataItems_WholesalerDataItems_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_WholesalerDataItems]
           ([itemID]
           ,[isActive]
           ,[surfaceId]
           ,[ParentSurfaceItemId]
           ,[WholesalerDataItems_WholesalerDataItems_Barcode]
           ,[WholesalerDataItems_WholesalerDataItems_WholesalerUniqueName]
           ,[WholesalerDataItems_WholesalerDataItems_WholesalerCode]
           ,[WholesalerDataItems_WholesalerDataItems_CostPrice]
           ,[WholesalerDataItems_WholesalerDataItems_LastUpdated]
           ,[WholesalerDataItems_WholesalerDataItems_UpdatedBy]
           ,[WholesalerDataItems_WholesalerDataItems_ImportFileName])
		VALUES
           (@itemID
           ,1
           ,@SurfaceID
           ,@ParentSurfaceItemId
           ,@EAN
           ,@WholesalerUniqueName
           ,@WholesalerCode
           ,@CostPrice
           ,CONVERT(varchar, GETDATE(), 120)
           ,@ImportedBy
           ,@FileName)
	END;
	--update Alternate records	
	SET @query =  
	CONCAT('select @result = COALESCE(@result + '','', '''') + CONVERT(VARCHAR, itemID) ',
	'from publ_WholesalerDataItems where WholesalerDataItems_WholesalerDataItems_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 WholesalerDataItems_WholesalerDataItems_WholesalerUniqueName = ', CHAR(39), @WholesalerUniqueName,
	 CHAR(39), ' and isActive = 1');

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

	UPDATE publ_WholesalerDataItems
	SET WholesalerDataItems_WholesalerDataItems_CostPrice = @CostPrice,
	WholesalerDataItems_WholesalerDataItems_LastUpdated = CONVERT(varchar, GETDATE(), 120),
	WholesalerDataItems_WholesalerDataItems_UpdatedBy = @ImportedBy
	WHERE itemID IN (@result); 

	--return 
	SELECT * 
	FROM publ_WholesalerDataItems 
	WHERE isActive = 1
	AND itemID = @itemID; 
	
END
GO


