CREATE PROC [dbo].[sp_SetSurfaceDataItemFromLinks]
	@surfaceId INT 
	,@sourceItemId INT
AS

--DECLARE @surfaceId INT = 3086
--		,@sourceItemId INT = 18913

DECLARE @linkedSurfaceId INT = 0, @linkNum INT = 0
DECLARE @surfaceLinkItems VARCHAR(MAX) = '', @filter VARCHAR(MAX) = '', @filterField VARCHAR(MAX) = '', @filterValue VARCHAR(MAX) = ''

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

DECLARE myCursor CURSOR FOR
	
	SELECT linkedSurfaceId,filterField,filterValue FROM pal_SurfaceLink 
	WHERE surfaceID = @surfaceId	
		
	OPEN myCursor      
		FETCH NEXT FROM myCursor INTO @linkedSurfaceId,@filterField,@filterValue      
    
	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)+' 
			AND I'+CAST(@linkNum AS VARCHAR)+'.isActive= 1 AND I'+CAST(@linkNum AS VARCHAR)+'.isDeleted = 0 '
			
		IF(@filterField>0)
		BEGIN
			SET @linkQuery = @linkQuery + '
		JOIN pal_SurfaceField F'+CAST(@linkNum AS VARCHAR)+'
			ON F'+CAST(@linkNum AS VARCHAR)+'.recId = '+CAST(@filterField AS VARCHAR)+'
		JOIN pal_SurfaceFieldData D'+CAST(@linkNum AS VARCHAR)+' WITH(NOLOCK)
			ON D'+CAST(@linkNum AS VARCHAR)+'.surfaceId = F'+CAST(@linkNum AS VARCHAR)+'.surfaceId
			AND D'+CAST(@linkNum AS VARCHAR)+'.surfaceFieldID = F'+CAST(@linkNum AS VARCHAR)+'.recId
			AND D'+CAST(@linkNum AS VARCHAR)+'.surfaceItemId = I'+CAST(@linkNum AS VARCHAR)+'.recId
		JOIN pal_SurfaceLookup L'+CAST(@linkNum AS VARCHAR)+'
			ON L'+CAST(@linkNum AS VARCHAR)+'.recId = D'+CAST(@linkNum AS VARCHAR)+'.surfaceFieldLookupID
			AND L'+CAST(@linkNum AS VARCHAR)+'.display IN ('+CAST(@filterValue AS VARCHAR(50))+') ';
		END

		FETCH NEXT FROM myCursor INTO @linkedSurfaceId,@filterField,@filterValue          
	END   

CLOSE myCursor      
DEALLOCATE myCursor  

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

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

--PRINT @linkQuery
EXEC (@linkQuery)
		
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 IN (3,4,5,6,7,8,9,14,15,20,25,26,29,30)
		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 = @surfaceId
		AND D.recId IS NULL

DECLARE @tmpItems TABLE
(recId INT,splitdata INT)

INSERT INTO @tmpItems
SELECT	I.recId, X.splitdata
FROM	pal_SurfaceItem I
CROSS APPLY (SELECT splitdata FROM dbo.SplitString(I.surfaceLinkItems, ',')) X
WHERE	I.surfaceId = @surfaceId
		AND CAST(@sourceItemId AS VARCHAR(10)) = X.splitdata

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 L
					CROSS APPLY (SELECT SUBSTRING(splitdata, 0, CHARINDEX('~R~', splitdata)) val FROM dbo.SplitString(RD.surfaceFieldValueChar, '~|~')) Y
					WHERE recId = SUBSTRING(Y.val, 0, CHARINDEX('~R~', Y.val)) 
					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 UC.userDisplay 
							ELSE UC.userDisplay + ' (last updated by ' + UU.userDisplay + ')' 
							END
						WHEN 'static' THEN
							CAST(RF.relationalValues AS VARCHAR(MAX))
						ELSE
							CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))
					END
			WHEN 30 THEN 
				STUFF((
					SELECT CAST('; ' + display  AS VARCHAR(MAX))
					FROM pal_SurfaceLookup L
					CROSS APPLY (SELECT splitdata FROM dbo.SplitString(RD.surfaceFieldValueChar, ',')) X
					WHERE recId = X.splitdata
					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 WITH(NOLOCK)
			ON D.surfaceFieldID = F.recId
			AND D.surfaceId = S.recId
			AND D.surfaceItemId = I.recId
		JOIN pal_SurfaceField RF
			ON RF.recId = F.relationalFields
			AND RF.surfaceId = CASE WHEN F.relationalSurface IN ('user','date','static') THEN 0 ELSE F.relationalSurface END 
		JOIN @tmpItems T
			ON T.recId = I.recId
		JOIN pal_SurfaceItem RI
			ON RI.recId = T.splitdata
		JOIN pal_SurfaceFieldData RD WITH(NOLOCK)
			ON RD.surfaceFieldID = RF.recId
			AND RD.surfaceItemId = RI.recId
			AND RD.surfaceId = RI.surfaceId
		LEFT JOIN pal_SurfaceLookup SL 
			ON SL.recId = RD.surfaceFieldLookupID
		LEFT JOIN pal_vUserShared UC 
			ON UC.recId = RI.createdBy
		LEFT JOIN pal_vUserShared UU 
			ON UU.recId = RI.updatedBy
WHERE	S.recId = @surfaceId
		AND RF.surfaceFieldTypeId IN (3,4,5,6,7,8,9,14,15,20,25,26,29,30)

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 L
					CROSS APPLY (SELECT SUBSTRING(splitdata, 0, CHARINDEX('~R~', splitdata)) val FROM dbo.SplitString(RD.surfaceFieldValueChar, '~|~')) Y
					WHERE recId = SUBSTRING(Y.val, 0, CHARINDEX('~R~', Y.val)) 
					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 UC.userDisplay 
							ELSE UC.userDisplay + ' (last updated by ' + UU.userDisplay + ')' 
							END
						WHEN 'static' THEN
							CAST(RF.relationalValues AS VARCHAR(MAX))
						ELSE
							CAST(RD.surfaceFieldValueChar AS VARCHAR(MAX))
					END
			WHEN 30 THEN 
				STUFF((
					SELECT CAST('; ' + display  AS VARCHAR(MAX))
					FROM pal_SurfaceLookup L
					CROSS APPLY (SELECT splitdata FROM dbo.SplitString(RD.surfaceFieldValueChar, ',')) X
					WHERE recId = X.splitdata
					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 WITH(NOLOCK)
			ON D.surfaceFieldID = F.recId
			AND D.surfaceId = S.recId
			AND D.surfaceItemId = I.recId
		JOIN pal_SurfaceField RF
			ON RF.recId = F.relationalFields
			AND RF.surfaceId = CASE WHEN F.relationalSurface IN ('user','date','static') THEN 0 ELSE F.relationalSurface END 
		JOIN @tmpItems T
			ON T.recId = I.recId
		JOIN pal_SurfaceFieldData PD WITH(NOLOCK,INDEX([IX_SurfaceFieldData_surfaceFieldValueNum]))
			ON PD.surfaceFieldValueNum = T.splitdata
		JOIN pal_SurfaceField PF
			ON PF.recId = PD.surfaceFieldID
			AND PF.surfaceFieldName = 'ParentSurfaceItemId'
		JOIN pal_SurfaceItem RI
			ON RI.recId = PD.surfaceItemId
		JOIN pal_SurfaceFieldData RD WITH(NOLOCK)
			ON RD.surfaceFieldID = RF.recId
			AND RD.surfaceItemId = RI.recId
			AND RD.surfaceId = RI.surfaceId
		LEFT JOIN pal_SurfaceLookup SL 
			ON SL.recId = RD.surfaceFieldLookupID
		LEFT JOIN pal_vUserShared UC 
			ON UC.recId = RI.createdBy
		LEFT JOIN pal_vUserShared UU 
			ON UU.recId = RI.updatedBy
WHERE	S.recId = @surfaceId
		AND RF.surfaceFieldTypeId IN (3,4,5,6,7,8,9,14,15,20,25,26,29,30)

--delete all items that was deleted on the linked surface
IF EXISTS(
		SELECT	1
		FROM	pal_SurfaceItem 
		WHERE	surfaceId = @surfaceId
				AND isDeleted = 1)
BEGIN
DELETE	D
FROM	pal_SurfaceItem I
		JOIN pal_SurfaceFieldData D WITH(NOLOCK)
			ON D.surfaceItemId = I.recId
			AND D.surfaceId = I.surfaceId
WHERE	I.surfaceId = @surfaceId
		AND I.isDeleted = 1

DELETE	I
FROM	pal_SurfaceItem I
WHERE	I.surfaceId = @surfaceId
		AND I.isDeleted = 1
END

--update qt table
DECLARE @ItemList varchar(MAX) = ''
SELECT	@ItemList = @ItemList + CONVERT(varchar, recId) + ','
FROM	@tmpItems

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