CREATE PROCEDURE [dbo].[sp_GetChildSurfaceQueryDataNoZeroes]
	@SurfaceID int,
	@ParentSurfaceItemId int,
	@RowLimit INT = 0,
	@GridOnly bit = 1
AS
BEGIN

	SET NOCOUNT ON;

		DECLARE @QueryTableName varchar(300) = ''

		SET @QueryTableName = 'qt_' + (SELECT name from pal_Surface Where recId = @SurfaceID)
		
		DECLARE @cols AS VARCHAR(MAX),
		@query  AS VARCHAR(MAX),
		@query2  AS VARCHAR(MAX)

		--CVH 2017-03-22 If it's a linked surface, filter on pal_SurfaceItem.surfaceLinkItems, not ParentSurfaceItemId
		--JR 2017-04-02 don't join items. irrelevent to determine linked surface
		DECLARE @isLinkedSurface BIT;
		IF EXISTS(
			SELECT 1 
			FROM pal_SurfaceLink l
			--INNER JOIN pal_SurfaceItem p ON p.surfaceId = l.linkedSurfaceId 
			WHERE l.surfaceId = @SurfaceID 
			--AND p.recId = @ParentSurfaceItemId
			)
		BEGIN
			SET @isLinkedSurface = 1

			--CVH 2017-05-26 If linked surface is added as a child grid to the child grid of the surface the linked surface links to, get the ParentItemId of the given ParentItemId
			--e.g. SBF Termly Review is now a child grid of the Scholar Info child grid, which is a child grid of Profiles. Where Termly Review links to Profiles.
			IF ((SELECT COUNT(recId)  
					FROM pal_SurfaceLink l
					WHERE l.surfaceId = @SurfaceID 
					AND l.linkedSurfaceId IN (SELECT i.surfaceId 
						FROM pal_SurfaceItem i 
						WHERE i.recId = @ParentSurfaceItemId)) = 0)
			BEGIN
				SELECT @ParentSurfaceItemId = surfaceFieldValueNum 
				FROM pal_SurfaceField f1 
				INNER JOIN pal_SurfaceItem i1 ON i1.surfaceId = f1.surfaceId 
				INNER JOIN pal_SurfaceFieldData d1 ON d1.surfaceItemId = i1.recId AND d1.surfaceFieldID = f1.recId	
				WHERE i1.recId = @ParentSurfaceItemId 
				AND f1.surfaceFieldDisplay = 'ParentSurfaceItemId'
			END

		END
		ELSE 
		BEGIN
			SET @isLinkedSurface = 0
		END

		select @cols = 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')
                    group by sf.surfaceFieldName, sf.recId, sf.surfaceFieldTypeId 
                    order by sf.surfaceFieldName
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')
				
		set @query = N' 
				SELECT  [itemID],[ParentSurfaceItemId], isActive, ISNULL(isComplete,0) isComplete, 
									surfaceId, linkUser, [userLinkEmail],	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,' + @cols + ' FROM 	' + @QueryTableName + '							
				WHERE isActive = 1 	'								
														
				
				
				IF (@isLinkedSurface = 1)
					SET @query += N' AND ' + CAST(@ParentSurfaceItemId as VARCHAR(10)) + ' IN (select splitdata from dbo.SplitString(surfaceLinkItems, '',''))  '
				ELSE
					set @query += ' AND ISNULL(ParentSurfaceItemId, 0) = ' + CAST(@ParentSurfaceItemId as VARCHAR(10))
		--print @query;
		--PRINT @query2;
		exec (@query)
END










