CREATE PROCEDURE [dbo].[sp_GetChildSurfaceQueryData]
	@SurfaceID int,
	@ParentSurfaceItemId int,
	@RowLimit INT = 0,
	@GridOnly bit = 1
AS
BEGIN
	--For testing:
	--DECLARE
	--@SurfaceID int = 3046,
	--@ParentSurfaceItemId int,
	--@RowLimit INT = 0,
	--@GridOnly bit = 1

	-- 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),
		@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
			)
		SET 
			@isLinkedSurface = 1
		ELSE 
		SET 
			@isLinkedSurface = 0

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






