-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-07-04
-- Description:	Determines the lookup value for the Surface Action: Calculation Lookup Field
-- =============================================
-- Modified:    2018-03-19
-- Author:      Charlene van Heerden
-- Description: If this is a field lookup linked to a module picklist, use given itemId to get lookup field value, not text value, solves duplicate problem
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSurfaceActionLookupFieldValue]  
	@surfaceFieldId INT, 
	@surfaceItemId INT,
	@unsavedItemValueToMatch VARCHAR(500) = '',
	@fromSurfacePicklist BIT = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @findColumn VARCHAR(250), @findSurfaceId INT, @valueToMatch VARCHAR(500), @fromColumn VARCHAR(250);

	SELECT @findColumn = f2.surfaceFieldName, @findSurfaceId = f2.surfaceId FROM pal_SurfaceField f2 WHERE recId IN (SELECT 
		CASE WHEN ISNUMERIC(f1.actionValue) = 1 AND CHARINDEX(',',f1.actionValue) < 1 AND CHARINDEX('.',f1.actionValue) < 1 THEN CAST(f1.actionValue AS INT) ELSE 0 END
		FROM pal_SurfaceField f1 
		WHERE f1.recId = @surfaceFieldId )

	IF (@unsavedItemValueToMatch <> '')
	BEGIN
		SELECT @valueToMatch = @unsavedItemValueToMatch
	END
	ELSE
	BEGIN
		SELECT @valueToMatch = d2.surfaceFieldValueChar 
		FROM pal_SurfaceFieldData d2 
		WHERE d2.surfaceItemId = @surfaceItemId
		AND d2.surfaceFieldID IN (SELECT f3.actionSource 
			FROM pal_SurfaceField f3 
			WHERE f3.recId = @surfaceFieldId )
	END

	SELECT @fromColumn = f6.surfaceFieldName  
	FROM pal_SurfaceField f5 
	LEFT OUTER JOIN pal_SurfaceField f6 ON f6.surfaceFieldDisplay = f5.surfaceFieldDisplay AND f6.surfaceId = ISNULL(@findSurfaceId,0) 
	WHERE f5.recId IN (SELECT f4.actionSource  
		FROM pal_SurfaceField f4 
		WHERE f4.recId = @surfaceFieldId)

	IF (ISNULL(@findColumn,'') = '' OR ISNULL(@valueToMatch,'') = '' OR ISNULL(@fromColumn,'') = '' OR ISNULL(@findSurfaceId,0) = 0) 
		SELECT '' 
	ELSE
	BEGIN	

		DECLARE @QueryTableName varchar(300) = ''		
		DECLARE @query  AS VARCHAR(MAX)

		SET @QueryTableName = 'qt_' + (SELECT name from pal_Surface Where recId = ISNULL(@findSurfaceId,0)) 

		IF @fromSurfacePicklist = 1 
		BEGIN
			SET @query = 'SELECT TOP 1 ' + @findColumn + '  
							FROM ' + @QueryTableName + ' 
							WHERE itemID = ' + CAST(@surfaceItemId AS VARCHAR(MAX)) + ' '
		END
		ELSE
		BEGIN
			SET @query = 'SELECT TOP 1 ' + @findColumn + '  
							FROM ' + @QueryTableName + ' 
							WHERE ' + @fromColumn + ' = ''' + @valueToMatch + ''''
		END

		--print @query
		EXEC (@query)

	END 

END

