
CREATE PROCEDURE [dbo].[sp_GetChildPublishedSurfaceData]
	@SurfaceID int,
	@ParentSurfaceItemId int,
	@RowLimit INT = 0,
	@GridOnly bit = 1,
	@UserId INT,
	@Cols VARCHAR(MAX) = ''
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 added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

		DECLARE @QueryTableName varchar(300) = ''

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

		IF @Cols = ''
			BEGIN
			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,'')				
			END

		set @query = N' 
				SELECT  publ.[itemID], publ.[ParentSurfaceItemId], publ.isActive, ISNULL(item.isWizardCompleted,0) isComplete, 
									publ.surfaceId, S.linkUser, UL.email [userLinkEmail], 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,' + @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 '					
														
				set @query += ' AND ISNULL(publ.[ParentSurfaceItemId], 0) = ' + CAST(@ParentSurfaceItemId as VARCHAR(10))
		--print @query;
		--PRINT @query2;
		exec (@query)
END








