CREATE PROC [dbo].[sp_ReSetItemLabelValues]
(
	@srcItemId INT 
	,@srcSurfaceId INT 
	,@surfaceList VARCHAR(250) OUTPUT
)
AS

----TEST
--EXEC sp_SetItemLabelValues 16441,	9
SET NOCOUNT ON
--DECLARE @srcItemId INT = 16441--36460
--		,@srcSurfaceId INT = 9--3098

DECLARE @updateQT BIT = 1

CREATE TABLE #TempSource
(
	surfaceFieldTypeId INT
	,surfaceDateTypeId INT
	,controlText VARCHAR(150)
	,action INT
	,actionSource INT
	,actionType INT
	,actionValue VARCHAR(250)
	,display VARCHAR(150)
	,recId	int
	,surfaceId	int
	,surfaceItemId	int
	,surfaceFieldID	int
	,surfaceFieldValueDate	datetime
	,surfaceFieldValueDecimal	decimal(18,2)
	,surfaceFieldValueChar	varchar(500)
	,surfaceFieldValueNum	int
	,surfaceFieldValueBool	bit
	,surfaceFieldValueTime	datetime
	,surfaceFieldLookupID	int
)
CREATE CLUSTERED INDEX IX_TempSource
		ON #TempSource (surfaceId,surfaceFieldID)

--populate temp data table
INSERT INTO #TempSource
SELECT	F.surfaceFieldTypeId,F.surfaceDateTypeId,F.controlText,F.action,F.actionSource,F.actionType,F.actionValue
		,L.display
		,D.*
--INTO	#TempSource
FROM	pal_SurfaceField F
		JOIN pal_SurfaceFieldData D WITH(NOLOCK)
			ON D.surfaceFieldID = F.recId
			AND D.surfaceId = F.surfaceId
			AND D.surfaceItemId = @srcItemId
		LEFT JOIN pal_SurfaceLookup L 
			ON L.recId = D.surfaceFieldLookupID	
WHERE	F.surfaceId = @srcSurfaceId

--insert self data for labels
INSERT INTO pal_SurfaceFieldData
(surfaceId,surfaceItemId,surfaceFieldID)
SELECT	I.surfaceId,I.recId,F.recId
FROM	pal_SurfaceItem I
		JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
			ON F.surfaceId = I.surfaceId
			AND F.surfaceFieldTypeId = 29
		JOIN #TempSource S
			ON S.surfaceItemId = I.recId
			AND S.surfaceId = I.surfaceId
			AND S.surfaceFieldID = F.recId
		LEFT JOIN pal_SurfaceFieldData D WITH(NOLOCK)
			ON D.surfaceFieldID = F.recId
			AND D.surfaceId = F.surfaceId
			AND D.surfaceItemId = I.recId
WHERE	D.recId IS NULL

--update self label values
UPDATE	D
	SET	surfaceFieldValueChar =
--SELECT	
		CASE WHEN F.relationalSurface NOT IN ('user','date','static') 
		THEN CAST(ISNULL(
				CASE S.surfaceFieldTypeId
				WHEN 3 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 4 Then CAST(S.surfaceFieldValueNum AS VARCHAR(MAX))
				WHEN 5 THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
				WHEN 6 THEN  
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE S.display
							END
				WHEN 7 Then
						CASE S.surfaceDateTypeId
							WHEN 2 THEN DATENAME(MONTH, S.surfaceFieldValueDate) + ' ' + CONVERT(VARCHAR,DATEPART(YYYY, S.surfaceFieldValueDate))
							WHEN 3 THEN CONVERT(VARCHAR,DATEPART(YYYY, S.surfaceFieldValueDate))
						ELSE CONVERT(VARCHAR, S.surfaceFieldValueDate, 111) /* yyyy/mm/dd*/ 
						END
				WHEN 8 THEN CAST(S.surfaceFieldValueBool AS VARCHAR(MAX))									 
				WHEN 9 THEN  
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE S.display
							END
				WHEN 10 THEN S.controlText
				WHEN 14 THEN
						CASE WHEN S.actionType = 8
						THEN CASE S.[action]
							WHEN 12
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(S.surfaceItemId,S.actionValue,0) AS VARCHAR(MAX))
							WHEN 14
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							WHEN 15
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							END
						WHEN S.actionType = 1
						THEN CASE WHEN S.[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 = S.surfaceItemId
											AND DD.surfaceFieldID = S.actionSource)	
							ELSE 
								CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
									THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
									ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
											THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
										END
								END
							END
						ELSE
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
									THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
									END
								END
						END				
				WHEN 14 THEN
						CASE WHEN S.actionType = 8
						THEN CASE S.[action]
							WHEN 12
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(S.surfaceItemId,S.actionValue,0) AS VARCHAR(MAX))
							WHEN 14
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							WHEN 15
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							END
						WHEN S.actionType = 1
						THEN CASE WHEN S.[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 = S.surfaceItemId
											AND DD.surfaceFieldID = S.actionSource)	
							ELSE 
								CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
									THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
									ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
											THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
										END
								END
							END
						ELSE
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
									THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
									END
								END
						END				
				WHEN 15 THEN CONVERT(VARCHAR,S.surfaceFieldValueTime,  114) /* hh:mm:ss:mmm(24h)*/									 
				WHEN 17 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 20 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 25 Then CAST(S.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(S.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(S.surfaceFieldValueChar, ',')) X
							WHERE recId = X.splitdata
							FOR XML PATH (''))
							, 1, 2, '')
				ELSE S.surfaceFieldValueChar
				END
				,S.surfaceFieldValueChar)AS VARCHAR(MAX))
			END
--		,F.*,S.*
FROM	#TempSource S
		JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
			ON S.surfaceFieldId = F.relationalFields
			AND S.surfaceId = CAST(F.relationalSurface AS INT)
			AND F.surfaceFieldTypeId = 29
			AND ISNUMERIC(F.relationalSurface)=1
		JOIN pal_SurfaceFieldData D WITH(NOLOCK)
			ON F.recId = D.surfaceFieldID
			AND F.surfaceId = D.surfaceId
		JOIN #TempSource P
			ON P.surfaceItemId = D.surfaceItemId
			AND P.surfaceId = D.surfaceId

--update self qt table
IF(@updateQT=1)
BEGIN
	DECLARE @selfSurfaceId INT
			,@selfItemId INT
	SELECT	@selfSurfaceId = I.surfaceId
			,@selfItemId = I.recId
	FROM	#TempSource I
			JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
				ON F.surfaceId = I.surfaceId
				AND F.surfaceFieldTypeId = 29
	--EXEC sp_SaveMultiSurfaceItemQueryTable @selfSurfaceId,0,219,@selfItemId
	SELECT @surfaceList = CAST(@selfSurfaceId AS VARCHAR(50))
END

SELECT	I.recId,I.surfaceId
INTO	#parentId
FROM	pal_SurfaceField F-- WITH(INDEX(IX_SurfaceField_surfaceId))
		JOIN pal_SurfaceFieldData D WITH(NOLOCK)
			ON F.recId = D.surfaceFieldID
			AND F.surfaceId = D.surfaceId
			AND D.surfaceItemId = @srcItemId
		JOIN pal_SurfaceItem I
			ON I.recId = D.surfaceFieldValueNum
WHERE	F.surfaceFieldName = 'ParentSurfaceItemId'

--insert main parent data for labels
INSERT INTO pal_SurfaceFieldData
(surfaceId,surfaceItemId,surfaceFieldID)
SELECT	I.surfaceId,I.recId,F.recId
FROM	pal_SurfaceItem I
		JOIN #parentId P
			ON P.recId = I.recId
			AND P.surfaceId = I.surfaceId
		JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
			ON F.surfaceId = I.surfaceId
			AND F.surfaceFieldTypeId = 29
		LEFT JOIN pal_SurfaceFieldData D WITH(NOLOCK)
			ON D.surfaceFieldID = F.recId
			AND D.surfaceId = F.surfaceId
			AND D.surfaceItemId = I.recId
WHERE	D.recId IS NULL

--update main parent label values
UPDATE	D
	SET	surfaceFieldValueChar =
--SELECT	
		CASE WHEN F.relationalSurface NOT IN ('user','date','static') 
		THEN CAST(ISNULL(
				CASE S.surfaceFieldTypeId
				WHEN 3 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 4 Then CAST(S.surfaceFieldValueNum AS VARCHAR(MAX))
				WHEN 5 THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
				WHEN 6 THEN  
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE S.display
							END
				WHEN 7 Then
						CASE S.surfaceDateTypeId
							WHEN 2 THEN DATENAME(MONTH, S.surfaceFieldValueDate) + ' ' + CONVERT(VARCHAR,DATEPART(YYYY, S.surfaceFieldValueDate))
							WHEN 3 THEN CONVERT(VARCHAR,DATEPART(YYYY, S.surfaceFieldValueDate))
						ELSE CONVERT(VARCHAR, S.surfaceFieldValueDate, 111) /* yyyy/mm/dd*/ 
						END
				WHEN 8 THEN CAST(S.surfaceFieldValueBool AS VARCHAR(MAX))									 
				WHEN 9 THEN  
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE S.display
							END
				WHEN 10 THEN S.controlText
				WHEN 14 THEN
						CASE WHEN S.actionType = 8
						THEN CASE S.[action]
							WHEN 12
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(S.surfaceItemId,S.actionValue,0) AS VARCHAR(MAX))
							WHEN 14
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							WHEN 15
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							END
						WHEN S.actionType = 1
						THEN CASE WHEN S.[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 = S.surfaceItemId
											AND DD.surfaceFieldID = S.actionSource)	
							ELSE 
								CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
									THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
									ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
											THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
										END
								END
							END
						ELSE
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
									THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
									END
								END
						END				
				WHEN 15 THEN CONVERT(VARCHAR,S.surfaceFieldValueTime,  114) /* hh:mm:ss:mmm(24h)*/									 
				WHEN 17 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 20 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 25 Then CAST(S.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(S.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(S.surfaceFieldValueChar, ',')) X
							WHERE recId = X.splitdata
							FOR XML PATH (''))
							, 1, 2, '')
				ELSE S.surfaceFieldValueChar
				END
				,S.surfaceFieldValueChar)AS VARCHAR(MAX))
			END
--		,F.*,S.*
FROM	#TempSource S
		JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
			ON S.surfaceFieldId = F.relationalFields
			AND S.surfaceId = CAST(F.relationalSurface AS INT)
			AND F.surfaceFieldTypeId = 29
			AND ISNUMERIC(F.relationalSurface)=1
		JOIN pal_SurfaceFieldData D WITH(NOLOCK)
			ON F.recId = D.surfaceFieldID
			AND F.surfaceId = D.surfaceId
		JOIN #parentId P
			ON P.recId = D.surfaceItemId
			AND P.surfaceId = D.surfaceId

--update parent qt table
IF(@updateQT=1)
BEGIN
	DECLARE @parentSurfaceId INT
			,@parentItemId INT
	SELECT	@parentSurfaceId = I.surfaceId
			,@parentItemId = I.recId
	FROM	#parentId I
			JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
				ON F.surfaceId = I.surfaceId
				AND F.surfaceFieldTypeId = 29
	--EXEC sp_SaveMultiSurfaceItemQueryTable @parentSurfaceId,0,219,@parentItemId
	SELECT @surfaceList += ','+CAST(@parentSurfaceId AS VARCHAR(50))
END

--get all child items
CREATE TABLE #ChildrenTempTable (surfaceItemId INT,surfaceId INT)
IF (SELECT COUNT(1) FROM #parentId) = 0
BEGIN
	INSERT INTO #ChildrenTempTable
	SELECT	D.surfaceItemId,D.surfaceId
	FROM	pal_SurfaceField F
			JOIN pal_SurfaceFieldData D WITH(NOLOCK)
				ON D.surfaceId = F.surfaceId
				AND D.surfaceFieldID = F.recId
				AND D.surfaceFieldValueNum = @srcItemId
			JOIN pal_SurfaceItem I
				ON I.recId = D.surfaceItemId
				AND isDeleted = 0
	WHERE	F.surfaceFieldName = 'ParentSurfaceItemId'

END
ELSE
BEGIN
	INSERT INTO #ChildrenTempTable
	SELECT	D.surfaceItemId,D.surfaceId
	FROM	pal_SurfaceField F
			JOIN pal_SurfaceFieldData D WITH(NOLOCK)
				ON D.surfaceId = F.surfaceId
				AND D.surfaceFieldID = F.recId
			JOIN pal_SurfaceItem I
				ON I.recId = D.surfaceItemId
				AND isDeleted = 0
			JOIN #parentId P
				ON P.recId = D.surfaceFieldValueNum
	WHERE	F.surfaceFieldName = 'ParentSurfaceItemId'
END

--insert child data
INSERT INTO pal_SurfaceFieldData
(surfaceId,surfaceItemId,surfaceFieldID)
SELECT	I.surfaceId,I.recId,F.recId
FROM	pal_SurfaceItem I
		JOIN #ChildrenTempTable T
			ON T.surfaceId = I.surfaceId
			AND T.surfaceItemId = I.recId
		JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
			ON F.surfaceId = I.surfaceId
			AND F.surfaceFieldTypeId = 29
		LEFT JOIN pal_SurfaceFieldData D WITH(NOLOCK)
			ON D.surfaceFieldID = F.recId
			AND D.surfaceId = F.surfaceId
			AND D.surfaceItemId = I.recId
WHERE	D.recId IS NULL

--update child label values
UPDATE	D
	SET	surfaceFieldValueChar =
--SELECT	
		CASE WHEN F.relationalSurface NOT IN ('user','date','static') 
		THEN CAST(ISNULL(
				CASE S.surfaceFieldTypeId
				WHEN 3 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 4 Then CAST(S.surfaceFieldValueNum AS VARCHAR(MAX))
				WHEN 5 THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
				WHEN 6 THEN  
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE S.display
							END
				WHEN 7 Then
						CASE S.surfaceDateTypeId
							WHEN 2 THEN DATENAME(MONTH, S.surfaceFieldValueDate) + ' ' + CONVERT(VARCHAR,DATEPART(YYYY, S.surfaceFieldValueDate))
							WHEN 3 THEN CONVERT(VARCHAR,DATEPART(YYYY, S.surfaceFieldValueDate))
						ELSE CONVERT(VARCHAR, S.surfaceFieldValueDate, 111) /* yyyy/mm/dd*/ 
						END
				WHEN 8 THEN CAST(S.surfaceFieldValueBool AS VARCHAR(MAX))									 
				WHEN 9 THEN  
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE S.display
							END
				WHEN 10 THEN S.controlText		
				WHEN 14 THEN
						CASE WHEN S.actionType = 8
						THEN CASE S.[action]
							WHEN 12
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(S.surfaceItemId,S.actionValue,0) AS VARCHAR(MAX))
							WHEN 14
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							WHEN 15
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							END
						WHEN S.actionType = 1
						THEN CASE WHEN S.[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 = S.surfaceItemId
											AND DD.surfaceFieldID = S.actionSource)	
							ELSE 
								CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
									THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
									ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
											THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
										END
								END
							END
						ELSE
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
									THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
									END
								END
						END				
				WHEN 15 THEN CONVERT(VARCHAR,S.surfaceFieldValueTime,  114) /* hh:mm:ss:mmm(24h)*/									 
				WHEN 17 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 20 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 25 Then CAST(S.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(S.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(S.surfaceFieldValueChar, ',')) X
							WHERE recId = X.splitdata
							FOR XML PATH (''))
							, 1, 2, '')
				ELSE S.surfaceFieldValueChar
				END
				,S.surfaceFieldValueChar)AS VARCHAR(MAX))
			END
FROM	#TempSource S
		JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
			ON S.surfaceFieldId = F.relationalFields
			AND S.surfaceId = CAST(F.relationalSurface AS INT)
			AND F.surfaceFieldTypeId = 29
			AND ISNUMERIC(F.relationalSurface)=1
		JOIN pal_SurfaceFieldData D WITH(NOLOCK)
			ON F.recId = D.surfaceFieldID
			AND F.surfaceId = D.surfaceId
		JOIN #ChildrenTempTable T
			ON T.surfaceId = D.surfaceId
			AND T.surfaceItemId = D.surfaceItemId

--update qt table
IF(@updateQT=1)
BEGIN
	--DECLARE @ChildList varchar(MAX) = ''
	--		,@ChildSurfaceId INT

	--DECLARE cCursor CURSOR FOR
	
	--	SELECT	T.surfaceId
	--			, STUFF((
	--					SELECT ', ' + CAST(surfaceItemId AS VARCHAR(MAX)) 
	--					FROM #ChildrenTempTable 
	--					WHERE (surfaceId = T.surfaceId) 
	--					FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
	--					,1,2,'') AS surfaceItemIds
	--	FROM	#ChildrenTempTable T
	--			JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
	--				ON F.surfaceId = T.surfaceId
	--				AND F.surfaceFieldTypeId = 29
	--	GROUP	BY T.surfaceId

	--	OPEN cCursor      
	--		FETCH NEXT FROM cCursor INTO @ChildSurfaceId,@ChildList
    
	--	WHILE @@FETCH_STATUS=0 
	--	BEGIN 				
	--		EXEC sp_SaveMultiSurfaceItemQueryTable @ChildSurfaceId,0,219,@ChildList
	--		FETCH NEXT FROM cCursor INTO @ChildSurfaceId,@ChildList         
	--	END   

	--CLOSE cCursor      
	--DEALLOCATE cCursor  
	DECLARE @ChildSurfaceId INT
	DECLARE cCursor CURSOR FOR	
		SELECT	T.surfaceId
		FROM	#ChildrenTempTable T
				JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
					ON F.surfaceId = T.surfaceId
					AND F.surfaceFieldTypeId = 29
		GROUP BY T.surfaceId

		OPEN cCursor      
			FETCH NEXT FROM cCursor INTO @ChildSurfaceId
    
		WHILE @@FETCH_STATUS=0 
		BEGIN 				
			SELECT @surfaceList += ','+CAST(@ChildSurfaceId AS VARCHAR(50))
			FETCH NEXT FROM cCursor INTO @ChildSurfaceId         
		END   
	CLOSE cCursor      
	DEALLOCATE cCursor  
END

SELECT	D.surfaceItemId,D.surfaceId
INTO	#GrandChildrenTempTable
FROM	pal_SurfaceField F
		JOIN pal_SurfaceFieldData D WITH(NOLOCK)
			ON D.surfaceId = F.surfaceId
			AND D.surfaceFieldID = F.recId
			AND D.surfaceFieldValueNum IN (SELECT surfaceItemId FROM #ChildrenTempTable)
		JOIN pal_SurfaceItem I
			ON I.recId = D.surfaceItemId
			AND I.isDeleted = 0
WHERE	F.surfaceFieldName = 'ParentSurfaceItemId'

--insert grand child data
INSERT INTO pal_SurfaceFieldData
(surfaceId,surfaceItemId,surfaceFieldID)
SELECT	I.surfaceId,I.recId,F.recId
FROM	pal_SurfaceItem I
		JOIN #GrandChildrenTempTable T
			ON T.surfaceId = I.surfaceId
			AND T.surfaceItemId = I.recId
		JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
			ON F.surfaceId = I.surfaceId
			AND F.surfaceFieldTypeId = 29
		LEFT JOIN pal_SurfaceFieldData D WITH(NOLOCK)
			ON D.surfaceFieldID = F.recId
			AND D.surfaceId = F.surfaceId
			AND D.surfaceItemId = I.recId
WHERE	D.recId IS NULL

--update grand child label values
UPDATE	D
	SET	surfaceFieldValueChar =
--SELECT	
		CASE WHEN F.relationalSurface NOT IN ('user','date','static') 
		THEN CAST(ISNULL(
				CASE S.surfaceFieldTypeId
				WHEN 3 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 4 Then CAST(S.surfaceFieldValueNum AS VARCHAR(MAX))
				WHEN 5 THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
				WHEN 6 THEN  
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE S.display
							END
				WHEN 7 Then
						CASE S.surfaceDateTypeId
							WHEN 2 THEN DATENAME(MONTH, S.surfaceFieldValueDate) + ' ' + CONVERT(VARCHAR,DATEPART(YYYY, S.surfaceFieldValueDate))
							WHEN 3 THEN CONVERT(VARCHAR,DATEPART(YYYY, S.surfaceFieldValueDate))
						ELSE CONVERT(VARCHAR, S.surfaceFieldValueDate, 111) /* yyyy/mm/dd*/ 
						END
				WHEN 8 THEN CAST(S.surfaceFieldValueBool AS VARCHAR(MAX))									 
				WHEN 9 THEN  
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE S.display
							END
				WHEN 10 THEN S.controlText		
				WHEN 14 THEN
						CASE WHEN S.actionType = 8
						THEN CASE S.[action]
							WHEN 12
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(S.surfaceItemId,S.actionValue,0) AS VARCHAR(MAX))
							WHEN 14
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							WHEN 15
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							END
						WHEN S.actionType = 1
						THEN CASE WHEN S.[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 = S.surfaceItemId
											AND DD.surfaceFieldID = S.actionSource)	
							ELSE 
								CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
									THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
									ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
											THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
										END
								END
							END
						ELSE
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
									THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
									END
								END
						END				
				WHEN 15 THEN CONVERT(VARCHAR,S.surfaceFieldValueTime,  114) /* hh:mm:ss:mmm(24h)*/									 
				WHEN 17 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 20 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 25 Then CAST(S.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(S.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(S.surfaceFieldValueChar, ',')) X
							WHERE recId = X.splitdata
							FOR XML PATH (''))
							, 1, 2, '')
				ELSE S.surfaceFieldValueChar
				END
				,S.surfaceFieldValueChar)AS VARCHAR(MAX))
			END
FROM	pal_SurfaceFieldData D
		JOIN pal_SurfaceField F
			ON F.recId = D.surfaceFieldID
			AND F.surfaceFieldTypeId = 29
		JOIN #GrandChildrenTempTable T
			ON T.surfaceId = D.surfaceId
			AND T.surfaceItemId = D.surfaceItemId
		JOIN #TempSource S
			ON S.surfaceFieldId = F.relationalFields
			AND S.surfaceId = CASE WHEN F.relationalSurface IN ('user','date','static') 
								THEN 0 
								ELSE CASE WHEN IsNumeric(F.relationalSurface) = 1 
										THEN CAST(F.relationalSurface AS INT)
										ELSE 0 END END 

--update qt table
IF(@updateQT=1)
BEGIN
	--DECLARE @GrandChildList varchar(MAX) = ''
	--		,@GrandChildSurfaceId INT

	--DECLARE gcCursor CURSOR FOR
	
	--	SELECT	T.surfaceId
	--			, STUFF((
	--					SELECT ', ' + CAST(surfaceItemId AS VARCHAR(MAX)) 
	--					FROM #GrandChildrenTempTable 
	--					WHERE (surfaceId = T.surfaceId) 
	--					FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
	--				  ,1,2,'') AS surfaceItemIds
	--	FROM	#GrandChildrenTempTable T
	--			JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
	--				ON F.surfaceId = T.surfaceId
	--				AND F.surfaceFieldTypeId = 29
	--	GROUP	BY T.surfaceId

	--	OPEN gcCursor      
	--		FETCH NEXT FROM gcCursor INTO @GrandChildSurfaceId,@GrandChildList
    
	--	WHILE @@FETCH_STATUS=0 
	--	BEGIN 
				
	--		EXEC sp_SaveMultiSurfaceItemQueryTable @GrandChildSurfaceId,0,219,@GrandChildList

	--		FETCH NEXT FROM gcCursor INTO @GrandChildSurfaceId,@GrandChildList         
	--	END   

	--CLOSE gcCursor      
	--DEALLOCATE gcCursor  
	DECLARE @GrandChildSurfaceId INT
	DECLARE gcCursor CURSOR FOR	
		SELECT	T.surfaceId
		FROM	#GrandChildrenTempTable T
				JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
					ON F.surfaceId = T.surfaceId
					AND F.surfaceFieldTypeId = 29
		GROUP	BY T.surfaceId

		OPEN gcCursor      
			FETCH NEXT FROM gcCursor INTO @GrandChildSurfaceId
    
		WHILE @@FETCH_STATUS=0 
		BEGIN 
			SELECT @surfaceList += ','+CAST(@GrandChildSurfaceId AS VARCHAR(50))
			FETCH NEXT FROM gcCursor INTO @GrandChildSurfaceId     
		END   

	CLOSE gcCursor      
	DEALLOCATE gcCursor  

END

CREATE TABLE #TempLinkedSource
(
	surfaceItemId INT
	--,surfaceId INT
)

;WITH CTE (surfaceItemId) AS
--INSERT INTO #TempLinkedSource
(SELECT	recId
FROM	#parentId
UNION ALL
SELECT	surfaceItemId
FROM	#TempSource
UNION All
SELECT	surfaceItemId
FROM	#ChildrenTempTable
UNION All
SELECT	surfaceItemId
FROM	#GrandChildrenTempTable)

SELECT	I.recId surfaceItemId, I.surfaceId
INTO	#TempLinkedItems
FROM	pal_SurfaceItem I
		CROSS APPLY (SELECT splitdata FROM dbo.SplitString(I.surfaceLinkItems, ',')) X
		JOIN CTE C
			ON C.surfaceItemId = X.splitdata
--WHERE	X.splitdata IN (SELECT surfaceItemId FROM CTE)


----insert linked data
--INSERT INTO pal_SurfaceFieldData
--(surfaceId,surfaceItemId,surfaceFieldID)
--SELECT	I.surfaceId,I.recId,F.recId
--FROM	pal_SurfaceItem I
--		JOIN #TempLinkedItems T
--			ON T.surfaceId = I.surfaceId
--			AND T.surfaceItemId = I.recId
--		JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
--			ON F.surfaceId = I.surfaceId
--			AND F.surfaceFieldTypeId = 29
--		LEFT JOIN pal_SurfaceFieldData D WITH(NOLOCK)
--			ON D.surfaceFieldID = F.recId
--			AND D.surfaceId = F.surfaceId
--			AND D.surfaceItemId = I.recId
--WHERE	D.recId IS NULL

----update linked data
UPDATE	D
	SET	surfaceFieldValueChar =
--SELECT	
		CASE WHEN F.relationalSurface NOT IN ('user','date','static') 
		THEN CAST(ISNULL(
				CASE S.surfaceFieldTypeId
				WHEN 3 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 4 Then CAST(S.surfaceFieldValueNum AS VARCHAR(MAX))
				WHEN 5 THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
				WHEN 6 THEN  
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE S.display
							END
				WHEN 7 Then
						CASE S.surfaceDateTypeId
							WHEN 2 THEN DATENAME(MONTH, S.surfaceFieldValueDate) + ' ' + CONVERT(VARCHAR,DATEPART(YYYY, S.surfaceFieldValueDate))
							WHEN 3 THEN CONVERT(VARCHAR,DATEPART(YYYY, S.surfaceFieldValueDate))
						ELSE CONVERT(VARCHAR, S.surfaceFieldValueDate, 111) /* yyyy/mm/dd*/ 
						END
				WHEN 8 THEN CAST(S.surfaceFieldValueBool AS VARCHAR(MAX))									 
				WHEN 9 THEN  
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE S.display
							END
				WHEN 10 THEN S.controlText		
				WHEN 14 THEN
						CASE WHEN S.actionType = 8
						THEN CASE S.[action]
							WHEN 12
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(S.surfaceItemId,S.actionValue,0) AS VARCHAR(MAX))
							WHEN 14
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							WHEN 15
								THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(S.surfaceItemId,S.actionValue) AS VARCHAR(MAX))
							END
						WHEN S.actionType = 1
						THEN CASE WHEN S.[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 = S.surfaceItemId
											AND DD.surfaceFieldID = S.actionSource)	
							ELSE 
								CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
									THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
									ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
											THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
										END
								END
							END
						ELSE
						CASE WHEN LEN(CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE CASE WHEN S.surfaceFieldValueDecimal > 0
									THEN CAST(S.surfaceFieldValueDecimal AS VARCHAR(MAX))
									END
								END
						END				
				WHEN 15 THEN CONVERT(VARCHAR,S.surfaceFieldValueTime,  114) /* hh:mm:ss:mmm(24h)*/									 
				WHEN 17 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 20 Then CAST(S.surfaceFieldValueChar AS VARCHAR(MAX))
				WHEN 25 Then CAST(S.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(S.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(S.surfaceFieldValueChar, ',')) X
							WHERE recId = X.splitdata
							FOR XML PATH (''))
							, 1, 2, '')
				ELSE S.surfaceFieldValueChar
				END
				,S.surfaceFieldValueChar)AS VARCHAR(MAX))
			END
FROM	pal_SurfaceFieldData D
		JOIN pal_SurfaceField F
			ON F.recId = D.surfaceFieldID
			AND F.surfaceFieldTypeId = 29
		JOIN #TempLinkedItems T
			ON T.surfaceId = D.surfaceId
			AND T.surfaceItemId = D.surfaceItemId
		JOIN #TempSource S
			ON S.surfaceFieldId = F.relationalFields
			AND S.surfaceId = CASE WHEN F.relationalSurface IN ('user','date','static') 
								THEN 0 
								ELSE CASE WHEN IsNumeric(F.relationalSurface) = 1 
										THEN CAST(F.relationalSurface AS INT)
										ELSE 0 END END 

IF(@updateQT=1)
BEGIN
	--DECLARE @LinkedList varchar(MAX) = ''
	--		,@LinkedSurfaceId INT

	--DECLARE lCursor CURSOR FOR
	
	--	SELECT	T.surfaceId
	--			, STUFF((
	--					SELECT ', ' + CAST(surfaceItemId AS VARCHAR(MAX)) 
	--					FROM #TempLinkedItems 
	--					WHERE (surfaceId = T.surfaceId) 
	--					FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
	--				  ,1,2,'') AS surfaceItemIds
	--	FROM	#TempLinkedItems T
	--			JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
	--				ON F.surfaceId = T.surfaceId
	--				AND F.surfaceFieldTypeId = 29
	--	GROUP	BY T.surfaceId

	--	OPEN lCursor      
	--		FETCH NEXT FROM lCursor INTO @LinkedSurfaceId,@LinkedList
    
	--	WHILE @@FETCH_STATUS=0 
	--	BEGIN 				
	--		EXEC sp_SaveMultiSurfaceItemQueryTable @LinkedSurfaceId,0,219,@LinkedList
	--		FETCH NEXT FROM lCursor INTO @LinkedSurfaceId,@LinkedList         
	--	END   

	--CLOSE lCursor      
	--DEALLOCATE lCursor  
	DECLARE @LinkedSurfaceId INT
	DECLARE lCursor CURSOR FOR	
		SELECT	T.surfaceId
		FROM	#TempLinkedItems T
				JOIN pal_SurfaceField F WITH(INDEX(IX_SurfaceField_surfaceId))
					ON F.surfaceId = T.surfaceId
					AND F.surfaceFieldTypeId = 29
		GROUP	BY T.surfaceId

		OPEN lCursor      
			FETCH NEXT FROM lCursor INTO @LinkedSurfaceId
    
		WHILE @@FETCH_STATUS=0 
		BEGIN 				
			SELECT @surfaceList += ','+CAST(@LinkedSurfaceId AS VARCHAR(50))
			FETCH NEXT FROM lCursor INTO @LinkedSurfaceId
		END   

	CLOSE lCursor      
	DEALLOCATE lCursor  
END


DROP INDEX IX_TempSource ON #TempSource
DROP TABLE #TempSource
DROP TABLE #parentId
DROP TABLE #ChildrenTempTable
DROP TABLE #GrandChildrenTempTable
DROP TABLE #TempLinkedItems
DROP TABLE #TempLinkedSource
