CREATE PROC sp_SetSurfaceDataFromLinks
	@surfaceId INT 
AS

--DECLARE @surfaceId INT = 3067

DECLARE @linkedSurfaceId INT = 0, @linkNum INT = 0
DECLARE @surfaceLinkItems VARCHAR(MAX) = '';
DECLARE @linkQuery VARCHAR(MAX) = 'INSERT INTO pal_SurfaceItem
(surfaceId,dateCreated,isActive,createdBy,surfaceLinkItems)
SELECT	S.recId,GETDATE(),1,219, {surfaceLinkItems}
FROM	pal_Surface S '

DECLARE myCursor CURSOR FOR
	
	SELECT linkedSurfaceId FROM pal_SurfaceLink 
	WHERE surfaceID = @surfaceId	
		
	OPEN myCursor      
		FETCH NEXT FROM myCursor INTO @linkedSurfaceId      
    
	WHILE @@FETCH_STATUS=0 
	BEGIN   
		SET @linkNum = @linkNum + 1
		IF(@linkNum=1)
		BEGIN
			SET @surfaceLinkItems = 'CAST(I'+CAST(@linkNum AS VARCHAR)+'.recId AS VARCHAR)';
		END
		ELSE
		BEGIN
			SET @surfaceLinkItems = @surfaceLinkItems + '+'',''+CAST(I'+CAST(@linkNum AS VARCHAR)+'.recId AS VARCHAR)';
		END
		SET @linkQuery = @linkQuery + '
		JOIN pal_SurfaceItem I'+CAST(@linkNum AS VARCHAR)+' 
			ON I'+CAST(@linkNum AS VARCHAR)+'.surfaceId = '+CAST(@LinkedSurfaceId AS VARCHAR)+' ';

		FETCH NEXT FROM myCursor INTO @linkedSurfaceId      
	END   

CLOSE myCursor      
DEALLOCATE myCursor  

SET	@linkQuery = @linkQuery + '
		LEFT JOIN pal_SurfaceItem SI ON SI.surfaceId = S.recId
WHERE	S.recId = '+CAST(@surfaceId AS VARCHAR)+'
		AND SI.recId IS NULL'

SET @linkQuery = REPLACE(@linkQuery,'{surfaceLinkItems}',@surfaceLinkItems)

--PRINT @linkQuery
EXEC (@linkQuery)
		
INSERT INTO pal_SurfaceFieldData
(surfaceId,surfaceItemId,surfaceFieldID)
SELECT	S.recId,I.recId,F.recId
FROM	pal_Surface S
		JOIN pal_SurfaceItem I
			ON I.surfaceId = S.recId
		JOIN pal_SurfaceField F
			ON F.surfaceId = S.recId
		LEFT JOIN pal_SurfaceFieldData D
			ON D.surfaceFieldID = F.recId
			AND D.surfaceItemId = I.recId
WHERE	S.recId = @surfaceId
		AND F.surfaceFieldTypeId IN (3,4,5,6,7,8,9,14,15,20,25,26,29,30)
		AND D.recId IS NULL

UPDATE	D
	SET	surfaceFieldValueChar = 
		CASE RF.surfaceFieldTypeId
			WHEN 3 Then CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))
			WHEN 4 Then CAST(RD.surfaceFieldValueNum AS VARCHAR(MAX))
			WHEN 5 THEN CAST(RD.surfaceFieldValueDecimal AS VARCHAR(MAX))
			WHEN 6 THEN CASE WHEN LEN(CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
						THEN CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))
						ELSE (select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = RD.surfaceFieldLookupID)
						END
			WHEN 7 THEN CASE WHEN RF.surfaceDateTypeId = 2 
						THEN DATENAME(MONTH, RD.surfaceFieldValueDate) + ' ' + CONVERT(VARCHAR,DATEPART(YYYY, RD.surfaceFieldValueDate))
						ELSE CONVERT(VARCHAR, RD.surfaceFieldValueDate, 111) /* yyyy/mm/dd*/ 
						END
			WHEN 8 THEN CAST(RD.surfaceFieldValueBool AS VARCHAR(MAX))									 
			WHEN 9 THEN CASE WHEN LEN(CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
						THEN CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))
						ELSE (select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = RD.surfaceFieldLookupID)
						END
			WHEN 14 THEN CASE WHEN LEN(CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
						THEN CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))
						ELSE CASE WHEN RD.surfaceFieldValueDecimal > 0
								THEN CAST(RD.surfaceFieldValueDecimal AS VARCHAR(MAX))
								END
						END
			WHEN 15 THEN CONVERT(VARCHAR,RD.surfaceFieldValueTime, 114) /* hh:mm:ss:mmm(24h)*/									 
			WHEN 20 THEN CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))
			WHEN 25 THEN CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))
			WHEN 26 THEN STUFF((
								SELECT CAST(', ' + display  AS VARCHAR(MAX))
								FROM pal_SurfaceLookup 
								WHERE recId IN (SELECT	SUBSTRING(splitdata, 0, CHARINDEX('~R~', splitdata)) 
												FROM	dbo.SplitString(RD.surfaceFieldValueChar, '~|~'))
								FOR XML PATH (''))
								, 1, 2, '') 
			WHEN 29 THEN CASE RF.relationalSurface 
							WHEN 'date' THEN 
							CASE WHEN YEAR(RI.dateUpdated) = 1900 THEN CONVERT(VARCHAR, RI.dateCreated, 120) 
								ELSE CONVERT(VARCHAR, RI.dateCreated, 120) + ' (last updated on ' + CONVERT(VARCHAR, RI.dateUpdated, 120) + ')'
								END
							WHEN 'user' THEN 
							CASE WHEN RI.updatedBy = 0 THEN (SELECT userDisplay FROM pal_vUserShared WHERE recId =  RI.createdBy) 
								ELSE (SELECT userDisplay FROM pal_vUserShared WHERE recId =  RI.createdBy) 
									+ ' (last updated by ' + (SELECT userDisplay FROM pal_vUserShared WHERE recId =  RI.updatedBy) + ')' 
								END
							ELSE
								CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))
						END
			WHEN 30 THEN 
				STUFF((
					SELECT CAST('; ' + display  AS VARCHAR(MAX))
					FROM pal_SurfaceLookup 
					WHERE recId IN (select splitdata from dbo.SplitString(RD.surfaceFieldValueChar, ','))
					FOR XML PATH (''))
					, 1, 2, '') 
			ELSE
			''
		END
--SELECT	RD.*
FROM	pal_Surface S
		JOIN pal_SurfaceItem I
			ON I.surfaceId = S.recId
		JOIN pal_SurfaceField F
			ON F.surfaceId = S.recId
			AND F.surfaceFieldTypeId = 29	
		JOIN pal_SurfaceFieldData D
			ON D.surfaceFieldID = F.recId
			AND D.surfaceId = S.recId
			AND D.surfaceItemId = I.recId
		JOIN pal_SurfaceFieldData RD
			ON RD.surfaceFieldID = F.relationalFields
			AND RD.surfaceId = F.relationalSurface
			AND RD.surfaceItemId IN (SELECT splitdata FROM dbo.SplitString(I.surfaceLinkItems, ','))
		JOIN pal_SurfaceField RF
			ON RF.recId = RD.surfaceFieldID
		JOIN pal_SurfaceItem RI
			ON RI.recId = RD.surfaceItemId
WHERE	S.recId = @surfaceId
