CREATE PROCEDURE [dbo].[sp_GetSurfaceQueryDataCount]
	@SurfaceID int,
	@UserLink int = 0,
	@Filter Varchar(1000) = '',
	@Search Varchar(500)
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 NVARCHAR(MAX),
		@query  AS NVARCHAR(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, sf.gridSequence
                    order by sf.gridSequence
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')

		set @query = N'SELECT
					Count(*) AS Count
					FROM ' + @QueryTableName
					
		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
		
			
		exec (@query+@query2);
END




