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 = 3067,
	--@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;

		--JR 2017-03-16 Set Surface Data From Links
		IF (SELECT COUNT(1) FROM pal_SurfaceLink WHERE surfaceId = @surfaceId) > 0
		BEGIN
			EXEC sp_SetSurfaceDataFromLinks @surfaceId
		END

		DECLARE @cols AS VARCHAR(MAX),
		@query  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 (select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = data.surfaceFieldLookupID)
														END
											WHEN 7 Then CASE WHEN field.surfaceDateTypeId = 2 THEN DATENAME(MONTH, data.surfaceFieldValueDate) + '' '' + 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 (select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = data.surfaceFieldLookupID)
														END
											WHEN 14 THEN
													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
											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 
													WHERE recId IN (select SUBSTRING(splitdata, 0, CHARINDEX(''~R~'', splitdata)) from dbo.SplitString(data.surfaceFieldValueChar, ''~|~''))
													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 (SELECT userDisplay FROM pal_vUserShared WHERE recId =  item.createdBy) 
														ELSE (SELECT userDisplay FROM pal_vUserShared WHERE recId =  item.createdBy) + '' (last updated by '' + (SELECT userDisplay FROM pal_vUserShared WHERE recId =  item.updatedBy) + '')'' 
														END
													ELSE
														CAST(data.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(data.surfaceFieldValueChar, '',''))
													FOR XML PATH (''''))
													, 1, 2, '''') 
										ELSE
										''''
									END, '''')  [FieldValue],  Replace(field.surfaceFieldName, '' '', '''') [FieldCode] '
						
									set @query = @query + 'FROM pal_SurfaceField field							
									
									INNER join pal_SurfaceFieldData data on data.surfaceFieldID = field.recId 
									INNER join pal_SurfaceItem item on item.recId = data.surfaceItemId		
									JOIN pal_Surface S ON S.recId = field.surfaceId							
									JOIN pal_SurfaceGridOptions G ON G.surfaceId = field.surfaceId
									JOIN pal_vUserShared U ON U.recId = '+CAST(@UserId AS VARCHAR(MAX)) +'
									LEFT JOIN pal_vUserShared UL ON UL.recId = item.userLink
									WHERE field.isActive = 1 AND (field.isGrid = 1 OR field.isFilter = 1 OR field.surfaceFieldName = ''ParentSurfaceItemId'') 
									AND data.surfaceId = ' + CAST(@SurfaceID AS VARCHAR(MAX)) + ' 
									AND field.surfaceFieldTypeId IN (3,4,5,6,7,8,9,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
									PIVOT
									(
										MAX(FieldValue)
										FOR FieldCode
										IN 
											(
												' + @cols + '
											)
									) PivotTable '

									IF @Search <> ''
									BEGIN
										set @query = @query + ' 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 @query = @query + @whereCols + ' ) '
										
										IF (CHARINDEX('ParentSurfaceItemId', @cols) > 0) 
										BEGIN
											set @query = @query + ' AND ISNULL(ParentSurfaceItemId, 0) = 0 ' 
										END	
										
										IF (LEN(@Filter) > 0)
										BEGIN
											set @query = @query + ' AND ' + @Filter															
										END
									END
									ELSE
										BEGIN
											IF (CHARINDEX('ParentSurfaceItemId', @cols) > 0) 
											BEGIN
												set @query = @query + ' WHERE ISNULL(ParentSurfaceItemId, 0) = 0 ' 

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

							
					
						set @query = @query + ') AS PagedFiles

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

											
		--print @query;
		exec (@query)
END

