CREATE PROC [dbo].[sp_SetItemLabelValues]
(
	@srcItemId INT --= 36460
	,@srcSurfaceId INT --= 3098
)
AS
--TEST
--EXEC sp_SetItemLabelValues 36460,3098

DECLARE @mainParentItemId INT = @srcItemId

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

--get main parent
IF EXISTS (SELECT	1
			FROM	pal_SurfaceFieldData D
					JOIN pal_SurfaceField F
						ON F.recId = D.surfaceFieldID
						AND F.surfaceId = D.surfaceId
			WHERE	D.surfaceItemId = @srcItemId
					AND F.surfaceFieldName = 'ParentSurfaceItemId')
BEGIN
	SELECT	@mainParentItemId = D.surfaceFieldValueNum
	FROM	pal_SurfaceFieldData D
			JOIN pal_SurfaceField F
				ON F.recId = D.surfaceFieldID
				AND F.surfaceId = D.surfaceId
	WHERE	D.surfaceItemId = @srcItemId
			AND F.surfaceFieldName = 'ParentSurfaceItemId'
END

--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 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	I.surfaceId = @mainParentItemId
		AND 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 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	pal_SurfaceFieldData D
		JOIN pal_SurfaceField F
			ON F.recId = D.surfaceFieldID
			AND F.surfaceFieldTypeId = 29
		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 
WHERE	D.surfaceItemId = @mainParentItemId

--get all child items
SELECT	D.surfaceItemId,D.surfaceId
INTO	#TempTable
FROM	pal_SurfaceField F
		JOIN pal_SurfaceFieldData D
			ON D.surfaceId = F.surfaceId
			AND D.surfaceFieldID = F.recId
			AND D.surfaceFieldValueNum = @mainParentItemId
WHERE	F.surfaceFieldName = 'ParentSurfaceItemId'

--insert child data
INSERT INTO pal_SurfaceFieldData
(surfaceId,surfaceItemId,surfaceFieldID)
SELECT	I.surfaceId,I.recId,F.recId
FROM	pal_SurfaceItem I
		JOIN #TempTable 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 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 #TempTable 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 

----linked children
----linked surface parent items
----DECLARE @srcItemId INT = 33080
--SELECT	I.recId surfaceItemId, I.surfaceId
--INTO	#TempLinkedItems
--FROM	pal_SurfaceItem I
--		CROSS APPLY (SELECT splitdata FROM dbo.SplitString(I.surfaceLinkItems, ',')) X
--WHERE	X.splitdata IN (SELECT surfaceItemId FROM #TempTable)

----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 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 

--update linked qt table
--DECLARE @ItemList varchar(MAX) = ''
--DECLARE @surfaceId INT
--SELECT	@ItemList = @ItemList + CONVERT(varchar, surfaceItemId) + ','
--		,@surfaceId = surfaceId
--FROM	#TempLinkedItems

--IF (LEN(@ItemList)>0)
--BEGIN
--	-- Remove last comma
--	SELECT @ItemList = LEFT(@ItemList, LEN(@ItemList) - 1)
--	--EXEC sp_SaveMultiSurfaceItemQueryTable @surfaceId,0,219,@ItemList
--END



--testing
--SELECT	D.*,F.*
--FROM	pal_SurfaceItem I
--		JOIN #TempTable T
--			ON T.surfaceId = I.surfaceId
--			AND T.surfaceItemId = I.recId
--		JOIN pal_SurfaceField F
--			ON F.surfaceId = I.surfaceId
--			AND F.surfaceFieldTypeId = 29
--		JOIN pal_SurfaceFieldData D
--			ON D.surfaceFieldID = F.recId
--			AND D.surfaceItemId = I.recId
--			AND D.surfaceId = I.surfaceId

--SELECT	*
--FROM	pal_SurfaceFieldData D
--		JOIN pal_SurfaceField F
--			ON F.recId = D.surfaceFieldID
--			AND F.surfaceFieldTypeId = 29
--WHERE	D.surfaceItemId = @mainParentItemId
		