CREATE PROC [dbo].[sp_SetSurfaceDataFromLinks]
	@surfaceId INT 
AS

--DECLARE @surfaceId INT = 3046

DECLARE @linkedSurfaceId INT = 0, @linkNum INT = 0
DECLARE @surfaceLinkItems VARCHAR(MAX) = '', @setValues VARCHAR(MAX) = '', @deleted 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 @updateQuery VARCHAR(MAX) = 'UPDATE	SI 
	{setValues} 
FROM	pal_Surface S
		JOIN pal_SurfaceItem SI
			ON SI.surfaceId = S.recId'

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 @setValues += 'SET isDeleted = CASE WHEN {isDeleted} > 0 THEN 1 ELSE 0 END';
			SET @surfaceLinkItems = 'CAST(I'+CAST(@linkNum AS VARCHAR)+'.recId AS VARCHAR)';
			SET @deleted = 'CAST(I1.isDeleted AS INT)';			
		END
		ELSE
		BEGIN
			SET @surfaceLinkItems = @surfaceLinkItems + '+'',''+CAST(I'+CAST(@linkNum AS VARCHAR)+'.recId AS VARCHAR)';
			SET @deleted += ' + CAST(I'+CAST(@linkNum AS VARCHAR)+'.isDeleted AS INT)';
		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
			IF((SELECT	surfaceFieldDisplay
			FROM	pal_SurfaceField F
			WHERE	F.recId = @filterField) = 'Grade')
			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)+'
				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 <= '''+CAST(@filterValue AS VARCHAR)+''' ';
			
			END
			ELSE
			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)+'
				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 = '''+CAST(@filterValue AS VARCHAR)+''' ';
			
			END

		END

		SET @updateQuery += '
		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,@filterField,@filterValue          
	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 
		{filter}'

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

SET @updateQuery += '
WHERE	S.recId = '+CAST(@surfaceId AS VARCHAR)+''

SET @updateQuery = REPLACE(@updateQuery,'{setValues}',@setValues)
SET @updateQuery = REPLACE(@updateQuery,'{isDeleted}',@deleted)

--PRINT @updateQuery
--JR 2017-03-23 - status update for deleted from source not working right, so commenting out
--EXEC (@updateQuery)

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

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

--DECLARE @linkedItems TABLE
--(recId INT,surfaceId INT,mainSurfaceId INT
--,dateUpdated DATETIME, dateCreated DATETIME
--,updatedBy INT, createdBy INT)

--INSERT INTO @linkedItems
--SELECT	RI.recId,RI.surfaceId,I.surfaceId
--		,RI.dateUpdated,RI.dateCreated
--		,RI.updatedBy,RI.createdBy
--FROM	pal_SurfaceItem I
--		JOIN pal_SurfaceItem RI
--			ON RI.recId IN (SELECT splitdata FROM dbo.SplitString(I.surfaceLinkItems, ','))
--WHERE	I.surfaceId = @surfaceId

SELECT	I.recId, X.splitdata
INTO	#tmpItems
FROM	pal_SurfaceItem I
CROSS APPLY (SELECT splitdata FROM dbo.SplitString(I.surfaceLinkItems, ',')) X
WHERE	I.surfaceId = @surfaceId

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_SurfaceField RF
			ON RF.recId = F.relationalFields
			AND RF.surfaceId = F.relationalSurface
		JOIN #tmpItems T
			ON T.recId = I.recId
		JOIN pal_SurfaceItem RI
			ON RI.recId = T.splitdata
		JOIN pal_SurfaceFieldData RD
			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
WHERE	S.recId = @surfaceId
		AND RF.surfaceFieldTypeId IN (3,4,5,6,7,8,9,14,15,20,25,26,29,30)

DROP TABLE  #tmpItems


--delete all items that was deleted on the linked surface
DELETE	D
FROM	pal_SurfaceItem I
		JOIN pal_SurfaceFieldData D
			ON D.surfaceItemId = I.recId
WHERE	I.surfaceId = @surfaceId
		AND I.isDeleted = 1

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

