CREATE PROCEDURE [dbo].[sp_GetSurfaceQueryDataPagedNoZeroes]
	@SurfaceID int,
	@StartIndex int,
	@MaxRows int,
	@OrderBy Varchar(500),
	@Search Varchar(500),
	@UserLink int = 0,
	@Filter Varchar(1000) = '',
	@UserId INT,
	@ShowAllFields BIT = 0
AS
BEGIN

	-- 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)
		

		DECLARE @cols AS VARCHAR(MAX),
		@colsSelect AS VARCHAR(MAX),
		@query  AS VARCHAR(MAX),
		@query2  AS VARCHAR(MAX)

		IF(@ShowAllFields=1)
		BEGIN
		select @cols = STUFF((SELECT ',' + QUOTENAME(surfaceFieldName) 
                    from pal_SurfaceField sf where sf.surfaceFieldTypeId NOT IN (1,2,16) 
					and sf.surfaceId = @SurfaceID 
                    group by sf.surfaceFieldName, sf.recId, sf.gridSequence
                    order by sf.gridSequence
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')	
		select @colsSelect = STUFF((SELECT ',' + 
					CASE WHEN sf.surfaceFieldTypeId IN (4,5) AND sf.surfaceFieldName <> 'ParentSurfaceItemId' 
						THEN 'CASE WHEN ISNULL(' + QUOTENAME(surfaceFieldName) + ', ''0'') = ''0'' THEN '''' ELSE ' + QUOTENAME(surfaceFieldName) + ' END AS ' + QUOTENAME(surfaceFieldName) 
						ELSE QUOTENAME(surfaceFieldName) 
					END
                    from pal_SurfaceField sf where sf.surfaceFieldTypeId NOT IN (1,2,16) 
					and sf.surfaceId = @SurfaceID 
                    group by sf.surfaceFieldName, sf.surfaceFieldTypeId, sf.recId, sf.gridSequence
                    order by sf.gridSequence
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')
		END	
		ELSE
		BEGIN
		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' or sf.isGrouped = 1)
                    group by sf.surfaceFieldName, sf.recId, sf.gridSequence
                    order by sf.gridSequence
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')
		select @colsSelect = STUFF((SELECT ',' + 
					CASE WHEN sf.surfaceFieldTypeId IN (4,5) AND sf.surfaceFieldName <> 'ParentSurfaceItemId' 
						THEN 'CASE WHEN ISNULL(' + QUOTENAME(surfaceFieldName) + ', ''0'') = ''0'' THEN '''' ELSE ' + QUOTENAME(surfaceFieldName) + ' END AS ' + QUOTENAME(surfaceFieldName) 
						ELSE QUOTENAME(surfaceFieldName) 
					END
                    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' or sf.isGrouped = 1)
                    group by sf.surfaceFieldName, sf.surfaceFieldTypeId, sf.recId, sf.gridSequence
                    order by sf.gridSequence
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')
		END	

		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  [itemID], isActive, ISNULL(isComplete,0) isComplete, 
									surfaceId, linkUser, [userLinkEmail], [userLinkId],	userType,
									ISNULL(showWizardStatus,0) showWizardStatus,
									allowEdit, ISNULL(editAccessType,1)editAccessType,
									allowView, ISNULL(viewAccessType,1)viewAccessType,
									allowRemove, ISNULL(removeAccessType,1)removeAccessType,
									ISNULL(allowClone, 0) allowClone, ISNULL(cloneAccessType,1)cloneAccessType,' + @colsSelect + ' FROM 	' + @QueryTableName + '							
									WHERE isActive = 1 									
									AND ('+CAST(@UserLink AS VARCHAR)+' = 0 OR ISNULL(userLinkId,0) = '+CAST(@UserLink AS VARCHAR)+')
									) DataTable '
								SET @query2 = ''									

									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) 
										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) 
											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 @cols;									
		--print @query;
		--PRINT @query2;
		exec (@query+@query2)
END






