-- =============================================
-- Author:		<Dirk Strauss>
-- Create date:	<9 May 2017>
-- Description:	<Add or update an image on the Surface Gallery Control>
-- =============================================
CREATE PROCEDURE sproc_PROP1_SurfaceGallery_AddNewImage 
	-- Add the parameters for the stored procedure here
	@recId INT
	,@moduleId INT
	,@typeId INT
	,@folderName VARCHAR(350)
	,@entityId INT 
	,@display VARCHAR (350)
	,@fileName VARCHAR(350)
	,@dateSaved DATETIME
	,@dateUpdated DATETIME
	,@sequence INT
	,@isActive BIT
	,@userIdSaved INT
	,@userIdUpdated INT
	,@sourcePath VARCHAR(350)
	,@fieldName VARCHAR(500)
	,@isPrimary BIT
AS
BEGIN
	SET NOCOUNT ON;
	-- Find the current Primary Image ID
	DECLARE @previousPrimaryImageID INT = (SELECT recId FROM [pal_Attachment] WHERE isPrimary = 1)

     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	BEGIN TRANSACTION;	   
	-- Try update
	   UPDATE [dbo].[pal_Attachment]
		 SET [moduleId] = @moduleId
		    ,[typeId] = @typeId
		    ,[folderName] = @folderName
		    ,[entityId] = @entityId
		    ,[display] = @display
		    ,[fileName] = @fileName
		    ,[dateSaved] = @dateSaved
		    ,[dateUpdated] = @dateUpdated
		    ,[sequence] = @sequence
		    ,[isActive] = @isActive
		    ,[userIdSaved] = @userIdSaved
		    ,[userIdUpdated] = @userIdUpdated
		    ,[sourcePath] = @sourcePath
		    ,[fieldName] = @fieldName
		    ,[isPrimary] = @isPrimary
	    WHERE [recId] = @recId AND isActive = 1;
	    
	IF @@ROWCOUNT = 0
	BEGIN
	-- If nothing updated, try insert
	   INSERT INTO [dbo].[pal_Attachment]
			    ([moduleId]
			    ,[typeId]
			    ,[folderName]
			    ,[entityId]
			    ,[display]
			    ,[fileName]
			    ,[dateSaved]
			    ,[dateUpdated]
			    ,[sequence]
			    ,[isActive]
			    ,[userIdSaved]
			    ,[userIdUpdated]
			    ,[sourcePath]
			    ,[fieldName]
			    ,[isPrimary])
		   VALUES
			    (@moduleId
			    ,@typeId
			    ,@folderName
			    ,@entityId
			    ,@display
			    ,@fileName
			    ,@dateSaved
			    ,@dateUpdated
			    ,@sequence
			    ,@isActive
			    ,@userIdSaved
			    ,@userIdUpdated
			    ,@sourcePath
			    ,@fieldName
			    ,@isPrimary)

	END
		
	COMMIT TRANSACTION;

	-- Set the previous primary image to 0 only if the new image being saved or updated is set as the primary image
	IF (@isPrimary = 1)
	BEGIN     
	    UPDATE [dbo].[pal_Attachment]
			SET [isPrimary] = 0
		   WHERE [recId] = @previousPrimaryImageID;
	END 
END

