

CREATE PROCEDURE [dbo].[sp_GetPublishedSurfaceDataCount]
	@SurfaceID INT,
	@UserLink INT = 0,
	@Filter VARCHAR(1000) = '',
	@Search VARCHAR(500),
	@UserId INT,
	@Cols VARCHAR(MAX) = ''
AS
BEGIN
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @QueryTableName varchar(300) = ''
	DECLARE @ColNames varchar(MAX) = ''
		
		SET @QueryTableName = 'publ_' + (SELECT name from pal_Surface Where recId = @SurfaceID)
		
		DECLARE @query  AS NVARCHAR(MAX),
		@query2  AS VARCHAR(MAX) = ''
		
		select @ColNames = 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,'')

		IF @Cols = ''
			BEGIN			
				SET @Cols = @ColNames
			END

		set @query = N'SELECT
					Count(*) AS Count '					

		set @query = @query + 'FROM
								(
						SELECT  publ.[itemID], publ.isActive, ISNULL(item.isWizardCompleted,0) isComplete, 
						publ.surfaceId, S.linkUser, UL.email [userLinkEmail], UL.recId [userLinkId], U.userType,
						ISNULL(G.showWizardStatus,0) showWizardStatus,
						ISNULL(G.allowEdit,1)allowEdit, ISNULL(G.editAccessType,1)editAccessType,
						ISNULL(G.allowView,1)allowView, ISNULL(G.viewAccessType,1)viewAccessType,
						ISNULL(G.allowRemove,1)allowRemove, ISNULL(G.removeAccessType,1)removeAccessType,
						ISNULL(G.allowClone, 0)allowClone, ISNULL(G.cloneAccessType,1)cloneAccessType, item.surfaceLinkItems, ' + @cols + 
						' FROM 	' + @QueryTableName + ' publ
						JOIN pal_Surface S ON S.recId = publ.surfaceId
						JOIN pal_SurfaceItem item ON item.recId = publ.itemID		
						JOIN pal_SurfaceGridOptions G ON G.surfaceId = publ.surfaceId	
						JOIN pal_vUserShared U ON U.recId = '+CAST(@UserId AS VARCHAR)+'
						LEFT JOIN pal_vUserShared UL ON UL.recId = item.userLink				
						WHERE publ.isActive = 1 									
						AND ('+CAST(@UserLink AS VARCHAR)+' = 0 OR ISNULL(item.userLink,0) = '+CAST(@UserLink AS VARCHAR)+')
						) DataTable '
					
		IF @Search <> ''
		BEGIN
			set @query2 += ' WHERE ( '
			declare @S varchar(max)
			declare @whereCols varchar(max) = ''
			set @S = @ColNames
			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









