CREATE PROCEDURE [dbo].[sp_GetSurfaceDataPaged]
	@SurfaceID int,
	@StartIndex int,
	@MaxRows int,
	@OrderBy Varchar(500),
	@Search Varchar(500),
	@UserLink int = 0,
	@Filter Varchar(1000) = '',
	@UserId INT
AS
BEGIN
	--For testing:
	--DECLARE
	--@SurfaceID int = 3038,
	--@StartIndex int = 0,
	--@MaxRows int = 20,
	--@OrderBy Varchar(500),
	--@Search Varchar(500),
	--@userLink int = 0,
	--@Filter Varchar(1000) = '',
	--@UserId INT = 219 --Jas Admin Id

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @IsLinkedSurface BIT = 0;

		--JR 2017-03-16 Set Surface Data From Links
		IF (SELECT COUNT(1) FROM pal_SurfaceLink WHERE surfaceId = @surfaceId) > 0
		BEGIN
			SET  @IsLinkedSurface = 1
			--EXEC sp_SetSurfaceDataFromLinks @surfaceId --do this on count to balance the load
		END

		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 <> 1 AND sf.surfaceFieldTypeId <> 2 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' SELECT * 
						FROM (
							SELECT
							*, '

								IF @OrderBy <> ''
										BEGIN
										set @query = @query + 'ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') [RowRank] '
										END
									ELSE
										BEGIN						
										set @query = @query + 'ROW_NUMBER() OVER (ORDER BY itemID DESC) [RowRank] '
								END	

								set @query = @query + 'FROM
								(
									SELECT  item.recId AS [itemID], item.isActive, ISNULL(item.isWizardCompleted,0) isComplete, 
									field.surfaceId,
									S.linkUser,
									UL.email [userLinkEmail],
									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,
									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

														CASE WHEN ( CAST((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) AS VARCHAR(100)) IS NULL)
															THEN '''' 
															ELSE
															CAST(RANK() OVER
															(PARTITION BY
																(
																SELECT STUFF((SELECT '','' + D2.surfaceFieldValueChar
																FROM pal_SurfaceField F2  
																JOIN pal_SurfaceFieldData D2 ON D2.surfaceFieldID = F2.recId AND D2.surfaceItemId = item.recId
																WHERE F2.surfaceId = field.surfaceId 
																AND F2.recId IN (SELECT CASE WHEN ISNUMERIC(splitdata) = 1 THEN splitdata ELSE 0 END FROM dbo.SplitString(field.actionValue, ''|''))
																FOR XML PATH(''''), TYPE
																		).value(''.'', ''VARCHAR(MAX)'') 
																		,1,1,'''')
																)	
															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))	
															END			
														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 20 Then CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
											WHEN 25 Then CAST(data.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(data.surfaceFieldValueChar, ''~|~'')) Y
													WHERE recId = SUBSTRING(Y.val, 0, CHARINDEX(''~R~'', 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 (field.isGrid = 1 OR field.isFilter = 1 OR field.surfaceFieldName = ''ParentSurfaceItemId'') 
									AND S.recId = ' + CAST(@SurfaceID AS VARCHAR(MAX)) + ' 
									AND field.surfaceFieldTypeId IN (3,4,5,6,7,8,9,10,14,15,20,25,26,29,30)
									AND item.isDeleted = 0 
									AND ('+CAST(@UserLink AS VARCHAR)+' = 0 OR ISNULL(item.userLink,0) = '+CAST(@UserLink AS VARCHAR)+')
									) DataTable '
								SET @query2 += '
									PIVOT
									(
										MAX(FieldValue)
										FOR FieldCode
										IN 
											(
												' + @cols + '
											)
									) PivotTable '

									IF @Search <> ''
									BEGIN
										set @query2 += ' WHERE ( '
										declare @S varchar(max)
										declare @whereCols varchar(max) = ''
										set @S = @cols
										while len(@S) > 0
										begin
											IF @whereCols = ''
											BEGIN
											set @whereCols = @whereCols + '  ' + left(@S, charindex(',', @S+',')-1) + ' LIKE ''%' + @Search + '%'' '
											END
											ELSE
											BEGIN
											set @whereCols = @whereCols + ' OR  ' + left(@S, charindex(',', @S+',')-1) + ' LIKE ''%' + @Search + '%'' '
											END
											set @S = stuff(@S, 1, charindex(',', @S+','), '')
										end							 
										set @query2 += @whereCols + ' ) '
										
										IF (CHARINDEX('ParentSurfaceItemId', @cols) > 0 AND @IsLinkedSurface = 0) 
										BEGIN
											set @query2 += ' AND ISNULL(ParentSurfaceItemId, 0) = 0 ' 
										END	
										
										IF (LEN(@Filter) > 0)
										BEGIN
											set @query2 += ' AND ' + @Filter															
										END
									END
									ELSE
										BEGIN
											IF (CHARINDEX('ParentSurfaceItemId', @cols) > 0 AND @IsLinkedSurface = 0) 
											BEGIN
												set @query2 += ' WHERE ISNULL(ParentSurfaceItemId, 0) = 0 ' 

												IF (LEN(@Filter) > 0)
												BEGIN
													set @query2 += ' AND ' + @Filter															
												END	
											END
											ELSE
											BEGIN
												IF (LEN(@Filter) > 0)
												BEGIN
													set @query2 += ' WHERE ' + @Filter															
												END	
											END
										END

							
					
						set @query2 += ') AS PagedFiles

						WHERE [RowRank] > ' + CAST(@StartIndex AS VARCHAR(50)) + ' AND [RowRank] <= ' + CAST((@StartIndex + @MaxRows) AS VARCHAR(50))  + ' '

											
		--print @query;
		--PRINT @query2;
		exec (@query+@query2)
END


