CREATE PROC sp_SetItemLabelValuesSelf
(
	@itemId INT
	,@surfaceId INT
)
AS
/*
TESTS FROM SBF:
parent from child: 
itemID = 41574
surfaceID = 3038

sibling from sibling: 
itemID = 41648
surfaceID = 3100

cousin from uncle
itemID = 37711
surfaceID = 3048

no need for self to self as sp_SetItemLabelValues takes care of that
*/

DECLARE @fieldId INT
		--,@itemId INT = 37711
		--,@surfaceId INT = 3048
		,@relationalField INT
		,@relationalItem INT
		,@relationalSurface INT
		,@mainParentItem INT
		,@mainParentSurface INT

SELECT	@mainParentItem = @itemId
		,@mainParentSurface = @surfaceId

DECLARE fieldCursor CURSOR FOR
	
SELECT	F.recId,F.relationalFields,F.relationalSurface
FROM	pal_SurfaceField F
WHERE	F.surfaceId = @surfaceId
		AND F.surfaceFieldTypeId = 29
		AND F.relationalSurface NOT IN (@surfaceId,'user','date','static')

OPEN fieldCursor      
	FETCH NEXT FROM fieldCursor INTO @fieldId,@relationalField,@relationalSurface
    
WHILE @@FETCH_STATUS = 0 
BEGIN 
	
	--go up to main parent - 2 levels for now
	SELECT	@mainParentItem = ISNULL(A2.surfaceFieldValueNum,D.surfaceFieldValueNum)
			,@mainParentSurface = ISNULL(A2.surfaceId,I.surfaceId)
	FROM	pal_SurfaceItem I
			JOIN pal_SurfaceFieldData D WITH(NOLOCK)
				ON D.surfaceItemId = 41648
				AND D.surfaceFieldValueNum = I.recId
			JOIN pal_SurfaceField F
				ON F.recId = D.surfaceFieldID
				AND F.surfaceId = D.surfaceId
				AND F.surfaceFieldName = 'ParentSurfaceItemId'
			LEFT JOIN (SELECT	D.surfaceFieldValueNum,D.surfaceItemId,I.surfaceId
						FROM	pal_SurfaceItem I
								JOIN pal_SurfaceFieldData D WITH(NOLOCK)
									ON D.surfaceFieldValueNum = I.recId
								JOIN pal_SurfaceField F
									ON F.recId = D.surfaceFieldID
									AND F.surfaceId = D.surfaceId
									AND F.surfaceFieldName = 'ParentSurfaceItemId') A2
				ON A2.surfaceItemId = I.recId

	--go down to all children to find relational surface - one level for now
	SELECT	@relationalItem = D.surfaceItemId
	FROM	pal_SurfaceField F
			JOIN pal_SurfaceFieldData D WITH(NOLOCK)
				ON D.surfaceId = F.surfaceId
				AND D.surfaceFieldID = F.recId
				AND D.surfaceFieldValueNum = @mainParentItem
			JOIN pal_SurfaceItem I
				ON I.recId = D.surfaceItemId
				AND isDeleted = 0
	WHERE	F.surfaceFieldName = 'ParentSurfaceItemId'	
			AND D.surfaceId = @relationalSurface	
	
	--value source
	UPDATE	DU
		SET surfaceFieldValueChar = CAST(ISNULL(
			CASE F.surfaceFieldTypeId
			WHEN 3 Then CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))
			WHEN 4 Then CAST(D.surfaceFieldValueNum AS VARCHAR(MAX))
			WHEN 5 THEN CAST(D.surfaceFieldValueDecimal AS VARCHAR(MAX))
			WHEN 6 THEN  
					CASE WHEN LEN(CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
						THEN CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))
						ELSE L.display
						END
			WHEN 7 Then
					CASE F.surfaceDateTypeId
						WHEN 2 THEN DATENAME(MONTH, D.surfaceFieldValueDate) + ' ' + CONVERT(VARCHAR,DATEPART(YYYY, D.surfaceFieldValueDate))
						WHEN 3 THEN CONVERT(VARCHAR,DATEPART(YYYY, D.surfaceFieldValueDate))
					ELSE CONVERT(VARCHAR, D.surfaceFieldValueDate, 111) /* yyyy/mm/dd*/ 
					END
			WHEN 8 THEN CAST(D.surfaceFieldValueBool AS VARCHAR(MAX))									 
			WHEN 9 THEN  
					CASE WHEN LEN(CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
						THEN CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))
						ELSE L.display
						END
			WHEN 10 THEN F.controlText		
			WHEN 14 THEN
					CASE WHEN F.actionType = 8
					THEN CASE F.[action]
						WHEN 12
							THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(D.surfaceItemId,F.actionValue,0) AS VARCHAR(MAX))
						WHEN 14
							THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(D.surfaceItemId,F.actionValue) AS VARCHAR(MAX))
						WHEN 15
							THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(D.surfaceItemId,F.actionValue) AS VARCHAR(MAX))
						END
					WHEN F.actionType = 1
					THEN CASE WHEN F.[action] = 2 --age
							THEN (SELECT CAST((CONVERT(int,CONVERT(char(8),GETDATE(),112))-CONVERT(char(8),DD.surfaceFieldValueDate,112))/10000  AS VARCHAR(10))
								FROM	pal_SurfaceFieldData DD
								WHERE	DD.surfaceItemId = D.surfaceItemId
										AND DD.surfaceFieldID = F.actionSource)	
						ELSE 
							CASE WHEN LEN(CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
								THEN CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))
								ELSE CASE WHEN D.surfaceFieldValueDecimal > 0
										THEN CAST(D.surfaceFieldValueDecimal AS VARCHAR(MAX))
									END
							END
						END
					ELSE
					CASE WHEN LEN(CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
						THEN CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))
						ELSE CASE WHEN D.surfaceFieldValueDecimal > 0
								THEN CAST(D.surfaceFieldValueDecimal AS VARCHAR(MAX))
								END
							END
					END				
			WHEN 15 THEN CONVERT(VARCHAR,D.surfaceFieldValueTime,  114) /* hh:mm:ss:mmm(24h)*/									 
			WHEN 17 Then CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))
			WHEN 20 Then CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))
			WHEN 25 Then CAST(D.surfaceFieldValueChar AS VARCHAR(MAX))
			WHEN 26 THEN 
				STUFF((
					SELECT CAST(', ' + display  AS VARCHAR(MAX))
					FROM pal_SurfaceLookup L
					CROSS APPLY (SELECT SUBSTRING(splitdata, 0, CHARINDEX('~R~', splitdata)) val FROM dbo.SplitString(D.surfaceFieldValueChar, '~|~')) Y
					WHERE recId = Y.val
					FOR XML PATH (''))
					, 1, 2, '')  
			WHEN 30 THEN 
					STUFF((
						SELECT CAST('; ' + display  AS VARCHAR(MAX))
						FROM pal_SurfaceLookup L
						CROSS APPLY (SELECT splitdata FROM dbo.SplitString(D.surfaceFieldValueChar, ',')) X
						WHERE recId = X.splitdata
						FOR XML PATH (''))
						, 1, 2, '')
			ELSE D.surfaceFieldValueChar
			END
			,D.surfaceFieldValueChar)AS VARCHAR(MAX))
	FROM	pal_SurfaceItem I
			JOIN pal_SurfaceField F
				ON F.surfaceId = I.surfaceId
				AND F.recId = @relationalField
			JOIN pal_SurfaceFieldData D WITH(NOLOCK)
				ON D.surfaceFieldID = F.recId
				AND D.surfaceId = F.surfaceId
				AND D.surfaceItemId = I.recId
			JOIN pal_SurfaceFieldData DU
				ON DU.surfaceFieldID = @fieldId
				AND DU.surfaceItemId = @itemId
				AND DU.surfaceId = @surfaceId
			LEFT JOIN pal_SurfaceLookup L 
				ON L.recId = D.surfaceFieldLookupID	
	WHERE	I.recId= @relationalItem
			AND I.surfaceId = @relationalSurface
	
	FETCH NEXT FROM fieldCursor INTO @fieldId,@relationalField,@relationalSurface     
END  

CLOSE fieldCursor      
DEALLOCATE fieldCursor  

--update qt table --no need as this is called from sp_SaveSurfaceItemQueryTable which takes care of that
--EXEC sp_SaveMultiSurfaceItemQueryTable @surfaceId,0,219,@itemId