delete from pal_SurfaceFieldData
where surfacefieldid = (select recid from pal_SurfaceField where surfaceFieldName = 'SchoolPostbox_SchoolPostbox_SchoolPostboxName')

insert into pal_SurfaceFieldData
select (select top 1 recid from pal_Surface where name = 'SchoolPostbox') as surfaceId, 
	t.surfaceItemId as surfaceItemId, 
	(select recId from pal_SurfaceField where surfaceFieldName = 'SchoolPostbox_SchoolPostbox_SchoolPostboxName') as surfaceFieldId,
	null as surfaceFieldValueDate,
	null as surfaceFieldValueDecimal,
	(
		CONCAT((select top 1 sfd.surfaceFieldValueChar from pal_SurfaceField sf
		left join pal_SurfaceFieldData sfd on sf.recId = sfd.surfaceFieldID
		where sf.surfaceFieldName = 'SchoolPostbox_SchoolPostbox_SchoolName' and surfaceItemId = t.surfaceItemId), ' [', CASE 
		(select top 1 sfd.surfaceFieldLookupID from pal_SurfaceField sf
		left join pal_SurfaceFieldData sfd on sf.recId = sfd.surfaceFieldID
		where sf.surfaceFieldName = 'SchoolPostbox_SchoolPostbox_SchoolType' and surfaceItemId = t.surfaceItemId) WHEN 1094 THEN 'P' ELSE 'S' END, ']')) as surfaceFieldValueChar,
	null as surfaceFieldValueNum,
	null as surfaceFieldValueBool,
	null as surfaceFieldValueTime,
	null as surfaceFieldLookupID
from (select surfaceItemId from pal_SurfaceFieldData where surfaceFieldID = 514 group by surfaceItemId) t

GO

IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[sp_INDE_6_GetPublishedSurfaceDataPaged]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    DROP PROCEDURE [dbo].[sp_INDE_6_GetPublishedSurfaceDataPaged]
GO
CREATE PROCEDURE [dbo].[sp_INDE_6_GetPublishedSurfaceDataPaged]
	@SurfaceID INT,
	@StartIndex INT,
	@MaxRows INT,
	@OrderBy VARCHAR(500),
	@Search VARCHAR(500),
	@UserLink INT = 0,
	@Filter VARCHAR(1000) = '',
	@UserId INT,
	@ShowAllFields BIT = 0,
	@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 VARCHAR(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' or sf.isGrouped = 1)
						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 * 
						FROM (
							SELECT
							*, '

								IF @OrderBy <> ''
										BEGIN
										set @query = @query + 'ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') [RowRank] '
										END
									ELSE
										BEGIN						
										SET @query = @query + 'ROW_NUMBER() OVER (ORDER BY itemID DESC) [RowRank] '
								END	

								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 '
								SET @query2 = ''									

									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 (LEN(@Filter) > 0)
										BEGIN
											SET @query2 += ' AND ' + @Filter															
										END
									END
									ELSE
										BEGIN											
											IF (LEN(@Filter) > 0)
											BEGIN
												SET @query2 += ' WHERE ' + @Filter															
											END	
										END							
					
						SET @query2 += ') AS PagedFiles

						WHERE [RowRank] > ' + CAST(@StartIndex AS VARCHAR(50)) + ' AND [RowRank] <= ' + CAST((@StartIndex + @MaxRows) AS VARCHAR(50))  + ' '

		
		--print @cols;									
		--print @query;
		--PRINT @query2;
		EXEC (@query+@query2)
END