CREATE PROCEDURE [dbo].[sp_SaveMultiSurfaceItemQueryTable]
	@SurfaceID int,	
	@UserLink int = 0,	
	@UserId int = 0,
	@ItemIDs VARCHAR(250)
AS
BEGIN
	--For testing:
	--DECLARE
	--@SurfaceID int = 3038,
	--@userLink int = 0,
	--@UserId INT = 219, --Jas Admin Id
	--@ItemIDs VARCHAR(250) = 33080

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @QueryTableName varchar(300) = ''

	SET @QueryTableName = 'qt_' + (SELECT name from pal_Surface Where recId = @SurfaceID)
		
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
					WHERE TABLE_NAME = @QueryTableName)
	BEGIN

	DECLARE @cols AS VARCHAR(MAX),
	@query  AS VARCHAR(MAX),
	@query2  AS VARCHAR(MAX),
	@query3  AS VARCHAR(MAX),
	@updateQuery AS VARCHAR(MAX),
	@insertQuery AS VARCHAR(MAX),
	@updateCols AS VARCHAR(MAX)

	;with cte(requestId,names,namesT,rn) 
	as 
	(   
	select  requestId  = surfaceId,
			names=convert(varchar(max),QUOTENAME(surfaceFieldName)), 
			namesT=convert(varchar(max),QUOTENAME(surfaceFieldName)) +' = T.'+convert(varchar(max),QUOTENAME(surfaceFieldName)), 
			rn=ROW_NUMBER() over (order by surfaceFieldName)     
	from	pal_SurfaceField sf
	where	sf.surfaceFieldTypeId NOT IN (1,2,16) 
			and sf.surfaceId = @SurfaceID 
	group by sf.surfaceFieldName, sf.surfaceId ) 

	,cte2(outId,names,namesT,rn) as ( select requestId,names,namesT, 1 from cte where rn=1
	union all 
	select	cte2.outId
			,convert(varchar(max),cte2.names+', '+cte.names)
			,convert(varchar(max),cte2.namesT+', '+cte.namesT)
			,cte2.rn+1 from cte2 
	inner join cte on cte.requestId = cte2.outId and cte.rn=cte2.rn+1 ) 
	select	@cols = MAX(names)
			,@updateCols = MAX(namesT) 
	from	cte2 
	group by outId 
	OPTION (MAXRECURSION 200); 

	set @query = 'SELECT *  FROM
			(
				SELECT  item.recId AS [itemID], item.isActive, ISNULL(item.isWizardCompleted,0) isComplete, 
				field.surfaceId,				
				S.linkUser,
				UL.email [userLinkEmail], ISNULL(UL.recId,0) [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 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
						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
										CAST(DENSE_RANK() OVER
										(PARTITION BY
											(SELECT	D2.surfaceFieldValueChar
											FROM	pal_Surface S2
													JOIN pal_SurfaceField F2 
														ON F2.surfaceId = S2.recId
														AND S2.recId = ' + CAST(@SurfaceID AS VARCHAR(MAX)) + '
													JOIN pal_SurfaceFieldData D2  
														ON D2.surfaceFieldID = F2.recId 
														AND D2.surfaceId = F2.surfaceId
														AND D2.surfaceItemId = item.recId
											WHERE	D2.surfaceFieldID = field.contentId													
													AND field.[action] = 13)	
										ORDER BY 
											(SELECT	CASE F.surfaceFieldTypeId
													WHEN 4 THEN D.surfaceFieldValueNum 
													WHEN 5 THEN D.surfaceFieldValueDecimal  
													WHEN 14 THEN CASE WHEN F.actionType = 8
																THEN CASE WHEN F.[action] = 12
																	THEN dbo.fn_GetSurfaceFieldAggregateAverage(D.surfaceItemId,F.actionValue,0) 
																	END 
																END
															END
											FROM	pal_Surface S
													JOIN pal_SurfaceField F
														ON F.surfaceId = S.recId
														AND S.recId = ' + CAST(@SurfaceID AS VARCHAR(MAX)) + '
													JOIN pal_SurfaceFieldData D 
														ON D.surfaceFieldID = F.recId 
														AND D.surfaceId = F.surfaceId
														AND D.surfaceItemID = item.recId
											WHERE	F.recId = field.actionSource)	
										DESC)
										AS VARCHAR(MAX))	
										--CAST(dbo.fn_GetSurfaceFieldItemRanking(' + CAST(@SurfaceID AS VARCHAR(MAX)) + ',item.recId,field.recId) AS VARCHAR(MAX))				
									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
									CAST(field.relationalValues AS VARCHAR(MAX))
								ELSE
									CAST(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 @query2 = '
				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 IN (' + @ItemIDs + ') 								
				) DataTable '
			SET @query3 = '
				PIVOT
				(
					MAX(FieldValue)
					FOR FieldCode
					IN 
						(
							' + @cols + '
						)
				) PivotTable '								

	SET @updateQuery = '
	UPDATE	Q
		SET	'+@updateCols+'
	FROM	('+@query+@query2+@query3+') T
			JOIN '+@QueryTableName+' Q
				ON Q.itemID = T.itemID
	'

	SET @insertQuery = '
	INSERT INTO ' + @QueryTableName + '
	SELECT	T.*
	FROM	('+@query+@query2+@query3+') T
			LEFT JOIN '+@QueryTableName+' Q
				ON Q.itemID = T.itemID
	WHERE	Q.itemID IS NULL
	'

	--PRINT @query;
	--PRINT @query2;
	--PRINT @query3;
	--PRINT @updateCols;
	--PRINT @updateQuery;
	--PRINT @insertQuery;

	exec (@updateQuery+@insertQuery)
	END

END
