CREATE PROCEDURE [dbo].[sp_GetSurfaceDataCount]
	@SurfaceID int,
	@UserLink int = 0,
	@Filter Varchar(1000) = ''
AS
BEGIN
	--Testing
	--DECLARE
	--@SurfaceID int = 3046,
	--@UserLink int = 0,
	--@Filter Varchar(1000) = ''

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

		DECLARE @cols AS NVARCHAR(MAX),
		@query  AS NVARCHAR(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('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

		set @query = N'SELECT
					Count(1)AS Count
					FROM
					(
						SELECT  item.recId AS [itemID], item.isActive, 
						field.surfaceId,
						-- only doing count, so no need for all values. num is needed for parentsurfaceitemids
						ISNULL(CAST(data.surfaceFieldValueNum AS VARCHAR(MAX)),'''') [FieldValue]
						,  Replace(field.surfaceFieldName, '' '', '''') [FieldCode]									
						FROM pal_SurfaceField field	
						INNER join pal_SurfaceFieldData data WITH(NOLOCK)
							on data.surfaceFieldID = field.recId 
							AND data.surfaceId = field.surfaceId
						INNER join pal_SurfaceItem item on item.recId = data.surfaceItemId
						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 '

		--CVH 2016-10-19 If the surface has a field ParentSurfaceItemId, only select items where the value is NULL or 0
		IF (CHARINDEX('ParentSurfaceItemId', @cols) > 0 AND @IsLinkedSurface = 0) 
		BEGIN
			set @query = @query + ' WHERE ISNULL(ParentSurfaceItemId, 0) = 0 ' 

			if (LEN(@Filter) > 0)
				set @query = @query + ' AND ' + @Filter

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

		print @cols;
		print @query;
		exec sp_executesql @query;
END
