-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-07-11
-- Description:	Update Query Table Rank Data
-- =============================================
CREATE PROCEDURE [dbo].[sp_UpdateQueryTableRankData]
	@surfaceId INT,
	@rankFieldName VARCHAR(150), 
	@rankFieldSource INT, 
	@rankFieldGroupBy VARCHAR(250)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @QueryTableName varchar(300) = ''
	SET @QueryTableName = 'qt_' + (SELECT name from pal_Surface Where recId = @surfaceId)

	DECLARE @rankFieldSourceName VARCHAR(150), @rankCastType INT; --1=INT, 2=DECIMAL, 3=VARCHAR
	SELECT @rankFieldSourceName = surfaceFieldName,  
		@rankCastType = CASE WHEN sf.surfaceFieldTypeId = 4 OR (sf.surfaceFieldTypeId = 14 AND sf.actionType = 8 AND sf.action = 12) THEN 1 
							WHEN sf.surfaceFieldTypeId = 5 THEN 2
							ELSE 3 END 
		FROM pal_SurfaceField sf WHERE recId = @rankFieldSource

	DECLARE @query VARCHAR(MAX)

	SET @query = 'UPDATE up 
		SET up.' + @rankFieldName + ' = rst.RankValue 
		FROM ' + @QueryTableName + ' up
		INNER JOIN (SELECT itemID, 
			CASE WHEN ISNULL(' + @rankFieldSourceName + ', '''') = '''' THEN '''' 
			ELSE 
				CAST(DENSE_RANK() OVER
					(PARTITION BY
						(
						SELECT STUFF((SELECT '','' + D2.surfaceFieldValueChar
						FROM pal_SurfaceField F2  
						JOIN pal_SurfaceFieldData D2 ON D2.surfaceFieldID = F2.recId AND D2.surfaceItemId = qt.itemID
						WHERE F2.surfaceId = qt.surfaceId 
						AND F2.recId IN (SELECT CASE WHEN ISNUMERIC(splitdata) = 1 THEN splitdata ELSE 0 END FROM dbo.SplitString(''' + @rankFieldGroupBy + ''', ''|''))
						FOR XML PATH(''''), TYPE
								).value(''.'', ''VARCHAR(MAX)'') 
								,1,1,'''')
						)    
					ORDER BY ( ' 

	IF @rankCastType = 1
		SET @query += ' CASE WHEN ISNULL(' + @rankFieldSourceName + ', '''') = '''' THEN 0 ELSE CAST(' + @rankFieldSourceName + ' AS INT) END '
	ELSE IF @rankCastType = 2
		SET @query += ' CASE WHEN ISNULL(' + @rankFieldSourceName + ', '''') = '''' THEN 0 ELSE CAST(' + @rankFieldSourceName + ' AS DECIMAL(18,2)) END '
	ELSE
		SET @query += @rankFieldSourceName


	SET @query += ' )  
					DESC)
					AS VARCHAR(MAX))
			END AS RankValue
			FROM ' + @QueryTableName + ' qt 
		) rst ON rst.itemID = up.itemID '

	--print @query
	exec (@query)


END
