CREATE PROCEDURE [dbo].[sp_SaveSurfaceItemQueryTable]
	@SurfaceID int,	
	@UserLink int = 0,	
	@UserId int = 0,
	@ItemID int
AS
BEGIN
	--For testing:
	--DECLARE
	--@SurfaceID int = 3098,
	--@userLink int = 0,
	--@UserId INT = 216, --Jas Admin Id
	--@ItemID INT = 36525

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--JR 2017-04-25 Set Surface Data into linked items
	DECLARE @linkedSurfaceId INT
	DECLARE @linkedSurfaces TABLE (surfaceLinkId INT)

	INSERT INTO @linkedSurfaces
	SELECT surfaceId FROM pal_SurfaceLink WHERE @surfaceId = linkedSurfaceId

	WHILE (SELECT COUNT(1) FROM @linkedSurfaces) > 0
	BEGIN
		SELECT @linkedSurfaceId = surfaceLinkId FROM @linkedSurfaces
		EXEC sp_SetSurfaceDataItemFromLinks @linkedSurfaceId,@ItemID
		DELETE FROM @linkedSurfaces WHERE surfaceLinkId = @linkedSurfaceId
	END

	----set labelvalues
	--EXEC sp_SetItemLabelValues @ItemID,@SurfaceID

	--set labelvalues self
	EXEC sp_SetItemLabelValuesSelf @ItemID,@SurfaceID

	DECLARE @QueryTableName varchar(300) = ''

	SET @QueryTableName = 'qt_' + (SELECT name from pal_Surface Where recId = @SurfaceID)

	DECLARE @DeleteQuery varchar(800)
	SET @DeleteQuery = 'Delete FROM ' + @QueryTableName + ' WHERE itemID = ' + CAST(@ItemID As varchar) + ' '
	EXEC(@DeleteQuery)
	
		
	DECLARE @cols AS VARCHAR(MAX),
	@query  AS VARCHAR(MAX),
	@query2  AS VARCHAR(MAX)

	select @cols = STUFF((SELECT ',' + QUOTENAME(surfaceFieldName) 
                from pal_SurfaceField sf 
				where sf.surfaceFieldTypeId NOT IN (1,2,16) 
					and sf.surfaceId = @SurfaceID 
					--and (sf.isGrid = 1 or sf.isFilter = 1 or sf.surfaceFieldName = 'ParentSurfaceItemId')
                group by sf.surfaceFieldName, sf.recId
                order by sf.surfaceFieldName
        FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)') 
    ,1,1,'')

	set @query = N' INSERT INTO ' + @QueryTableName + ' 
			SELECT
			* '									
			set @query = @query + ' FROM
			(
				SELECT  item.recId AS [itemID], item.isActive, ISNULL(item.isWizardCompleted,0) isComplete, 
				field.surfaceId,				
				S.linkUser,
				UL.email [userLinkEmail], ISNULL(UL.recId,0) AS [userLinkId],
				U.userType,
				ISNULL(G.showWizardStatus,0) showWizardStatus,
				G.allowEdit, ISNULL(G.editAccessType,1)editAccessType,
				G.allowView, ISNULL(G.viewAccessType,1)viewAccessType,
				G.allowRemove, ISNULL(G.removeAccessType,1)removeAccessType,
				ISNULL(G.allowClone, 0) allowClone, ISNULL(G.cloneAccessType,1)cloneAccessType,
				item.surfaceLinkItems,
				ISNULL(CASE field.surfaceFieldTypeId
						WHEN 3 Then CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
						WHEN 4 Then CAST(data.surfaceFieldValueNum AS VARCHAR(MAX))
						WHEN 5 THEN CAST(data.surfaceFieldValueDecimal AS VARCHAR(MAX))
						WHEN 6 THEN  
								CASE WHEN LEN(CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
									THEN CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
									ELSE SL.display --(select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = data.surfaceFieldLookupID)
									END
						WHEN 7 Then
							CASE WHEN data.surfaceFieldValueDate = ''1900-01-01'' THEN '''' ELSE 
								CASE field.surfaceDateTypeId
									WHEN 2 THEN DATENAME(MONTH, data.surfaceFieldValueDate) + '' '' + CONVERT(VARCHAR,DATEPART(YYYY, data.surfaceFieldValueDate))
									WHEN 3 THEN CONVERT(VARCHAR,DATEPART(YYYY, data.surfaceFieldValueDate))
								ELSE CONVERT(VARCHAR, data.surfaceFieldValueDate, 111) /* yyyy/mm/dd*/ 
								END
							END
						WHEN 8 THEN CAST(data.surfaceFieldValueBool AS VARCHAR(MAX))									 
						WHEN 9 THEN  
								CASE WHEN LEN(CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
									THEN CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
									ELSE SL.display --(select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = data.surfaceFieldLookupID)
									END
						WHEN 10 THEN field.controlText
						WHEN 14 THEN
								CASE WHEN field.actionType = 8
								THEN CASE field.[action]
									WHEN 12
										THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(item.recId,field.actionValue,0) AS VARCHAR(MAX))
									WHEN 14
											THEN CASE WHEN field.controlledValue = ''75'' THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(item.recId,field.actionValue, 0) AS VARCHAR(MAX)) 
												ELSE CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(item.recId,field.actionValue, DEFAULT) AS VARCHAR(MAX)) END
										WHEN 15
											THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(item.recId,field.actionValue) AS VARCHAR(MAX))
										WHEN 16
											THEN CAST(dbo.fn_GetSurfaceFieldAggregateDIFF(item.recId,field.actionValue) AS VARCHAR(MAX))
										WHEN 17
											THEN CAST(dbo.fn_GetSurfaceFieldAggregateFailed(item.recId,field.actionValue) AS VARCHAR(MAX))
										WHEN 18
											THEN CAST(dbo.fn_GetSurfaceFieldAggregatePassed(item.recId,field.actionValue) AS VARCHAR(MAX))
									END
								WHEN field.actionType = 1
								THEN CASE 
									WHEN field.[action] = 13
										THEN ''''	--updated by proc sp_UpdateQueryTableRankData called from code
										
									WHEN field.[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 = item.recId
													AND DD.surfaceFieldID = field.actionSource)	
									WHEN field.[action] = 7 -- divide
										THEN CAST(dbo.fn_GetSurfaceCalculationDivide(item.recId,field.actionSource,field.actionValue) AS VARCHAR(MAX))
									WHEN field.[action] = 20 -- percentage
										THEN CAST(dbo.fn_GetSurfaceCalculationPercentage(item.recId,field.actionSource,field.actionValue) AS VARCHAR(MAX))
									ELSE 
										CASE WHEN LEN(CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
											THEN CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
											ELSE CASE WHEN data.surfaceFieldValueDecimal > 0
													THEN CAST(data.surfaceFieldValueDecimal AS VARCHAR(MAX))
												END
										END
									END
								ELSE
								CASE WHEN LEN(CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
									THEN CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
									ELSE CASE WHEN data.surfaceFieldValueDecimal > 0
											THEN CAST(data.surfaceFieldValueDecimal AS VARCHAR(MAX))
											END
										END
								END
						WHEN 15 THEN  convert(VARCHAR,data.surfaceFieldValueTime,  114) /* hh:mm:ss:mmm(24h)*/									 
						WHEN 17 Then CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
						WHEN 20 Then CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
						WHEN 25 Then CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(data.surfaceFieldValueChar,''~|~'','', ''),''~1~'',''''),''~2~'',''''),''~3~'',''''),''~4~'',''''),''~5~'',''''),''~6~'','''') 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(data.surfaceFieldValueChar, ''~|~'')) Y
								WHERE recId = Y.val 
								FOR XML PATH (''''))
								, 1, 2, '''')  
						WHEN 29 THEN
							CASE field.relationalSurface 
								WHEN ''date'' THEN (CASE WHEN YEAR(item.dateUpdated) = 1900 THEN CONVERT(VARCHAR, item.dateCreated, 120) ELSE CONVERT(VARCHAR, item.dateCreated, 120) + '' (last updated on '' + CONVERT(VARCHAR, item.dateUpdated, 120) + '')'' END)
								WHEN ''user'' THEN 
									CASE WHEN item.updatedBy = 0 THEN UC.userDisplay 
									ELSE UC.userDisplay + '' (last updated by '' + UU.userDisplay + '')'' 
									END
								WHEN ''static'' THEN
									CASE WHEN LEFT(field.relationalValues,2) = ''{}''
										THEN
											CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
										ELSE
											CAST(field.relationalValues AS VARCHAR(MAX))
										END
								ELSE
									CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
									--CAST(ISNULL(RD.surfaceFieldValueChar,data.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(data.surfaceFieldValueChar, '','')) X
								WHERE recId = X.splitdata
								FOR XML PATH (''''))
								, 1, 2, '''') 
					ELSE
					''''
				END, '''')  [FieldValue],  Replace(field.surfaceFieldName, '' '', '''') [FieldCode] '
						
				set @query = @query + '
				FROM	pal_Surface S
						JOIN pal_SurfaceItem item WITH(INDEX(IX_SurfaceItem_SurfaceID))
							ON item.surfaceId = S.recId	
						JOIN pal_SurfaceField field  
							ON S.recId = field.surfaceId						
						JOIN pal_SurfaceGridOptions G ON G.surfaceId = S.recId
						JOIN pal_vUserShared U ON U.recId = '+CAST(@UserId AS VARCHAR)+'
						LEFT JOIN pal_SurfaceFieldData data WITH(NOLOCK)
							ON data.surfaceFieldID = field.recId 
							AND data.surfaceId = field.surfaceId
							AND data.surfaceItemId = item.recId
						LEFT JOIN pal_vUserShared UL 
							ON UL.recId = item.userLink
						LEFT JOIN pal_vUserShared UC 
							ON UC.recId = item.createdBy
						LEFT JOIN pal_vUserShared UU 
							ON UU.recId = item.updatedBy
						LEFT JOIN pal_SurfaceLookup SL 
							ON SL.recId = data.surfaceFieldLookupID								
				WHERE field.isActive = 1 
				AND S.recId = ' + CAST(@SurfaceID AS VARCHAR(MAX)) + ' 
				AND field.surfaceFieldTypeId IN (3,4,5,6,7,8,9,10,14,15,17,20,25,26,29,30)
				AND item.isDeleted = 0  	
				AND item.recId = ' + CAST(@ItemID As varchar) + ' 								
				) DataTable '
			SET @query2 = '
				PIVOT
				(
					MAX(FieldValue)
					FOR FieldCode
					IN 
						(
							' + @cols + '
						)
				) PivotTable '
									
				--IF (CHARINDEX('ParentSurfaceItemId', @cols) > 0 AND @IsLinkedSurface = 0) 
				--BEGIN
				--	set @query2 += ' WHERE ISNULL(ParentSurfaceItemId, 0) = 0 ' 												
				--END						
										
	--print @query;
	--PRINT @query2;
	exec (@query+@query2)

END