CREATE PROCEDURE [dbo].[sp_CAPI1_GetSurfaceQueryDataPagedPartsMaster]
	@SurfaceID int,
	@StartIndex int,
	@MaxRows INT = 0,
	@OrderBy Varchar(500),
	@Search Varchar(500),
	@UserLink int = 0,
	@Filter Varchar(MAX) = '',
	@UserId INT,
	@ShowAllFields BIT = 0
AS
BEGIN
	--For testing:
	--DECLARE
	--@SurfaceID int = 3046,
	--@StartIndex int = 0,
	--@MaxRows int = 20,
	--@OrderBy Varchar(500),
	--@Search Varchar(500),
	--@userLink int = 0,
	--@Filter Varchar(1000) = '',
	--@UserId INT = 219 --Jas Admin Id

	-- 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),
		@querySearch  AS VARCHAR(MAX)

		IF(@ShowAllFields=1)
		BEGIN
		select @cols = STUFF((SELECT ',' + QUOTENAME(surfaceFieldName) 
                    from pal_SurfaceField sf where sf.surfaceFieldTypeId NOT IN (1,2,16) 
					and sf.surfaceId = @SurfaceID 
                    group by sf.surfaceFieldName, sf.recId, sf.gridSequence
                    order by sf.gridSequence
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')
		END	
		ELSE
		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' 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,'')
		END	

		SET @querySearch = ''
		IF @Search <> ''
		BEGIN
			set @querySearch += ' AND ( '
			declare @S varchar(max)
			declare @whereCols varchar(max) = ' PartsAlternates_PartsAlternates_AlternatePartNumber LIKE ''%' + @Search + '%''';
			set @S = @cols
			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 @querySearch += @whereCols + ' ) '
					
		END			

		set @query = N' SELECT itemID, isActive, isComplete, surfaceId, linkUser, [userLinkEmail], [userLinkId], userType,
									showWizardStatus, allowEdit, editAccessType, allowView, viewAccessType, allowRemove, removeAccessType,
									allowClone, cloneAccessType, ' + @cols + ' 
						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  m.[itemID], m.isActive, ISNULL(m.isComplete,0) isComplete, 
									m.surfaceId, m.linkUser, m.[userLinkEmail], m.[userLinkId],	m.userType,
									ISNULL(m.showWizardStatus,0) showWizardStatus,
									m.allowEdit, ISNULL(m.editAccessType,1)editAccessType,
									m.allowView, ISNULL(m.viewAccessType,1)viewAccessType,
									m.allowRemove, ISNULL(m.removeAccessType,1)removeAccessType,
									ISNULL(m.allowClone, 0) allowClone, ISNULL(m.cloneAccessType,1)cloneAccessType, ' + @cols + ' 
									FROM 	qt_PartsMaster m LEFT OUTER JOIN qt_PartsAlternates a ON a.ParentSurfaceItemId = m.itemID 							
									WHERE m.isActive = 1 									
									AND ('+CAST(@UserLink AS VARCHAR)+' = 0 OR ISNULL(m.userLinkId,0) = '+CAST(@UserLink AS VARCHAR)+') 
									' + @querySearch + ' 									
									) DataTable 
									GROUP BY itemID, isActive, isComplete, surfaceId, linkUser, [userLinkEmail], [userLinkId], userType,
									showWizardStatus, allowEdit, editAccessType, allowView, viewAccessType, allowRemove, removeAccessType,
									allowClone, cloneAccessType, ' + @cols + '  ' 

								SET @query2 = ''

								IF (CHARINDEX('ParentSurfaceItemId', @cols) > 0) 
								BEGIN
									set @query2 += ' WHERE ISNULL(ParentSurfaceItemId, 0) = 0 ' 

									IF (LEN(@Filter) > 0)
									BEGIN
										set @query2 += ' AND ' + @Filter															
									END	
								END
								ELSE
								BEGIN
									IF (LEN(@Filter) > 0)
									BEGIN
										set @query2 += ' WHERE ' + @Filter															
									END	
								END

							
					IF(@MaxRows>0)
					BEGIN
						set @query2 += ') AS PagedFiles

						WHERE [RowRank] > ' + CAST(@StartIndex AS VARCHAR(50)) + ' AND [RowRank] <= ' + CAST((@StartIndex + @MaxRows) AS VARCHAR(50))  + '  '
					END
					ELSE
					BEGIN
						SET @query2 += ') AS PagedFiles GROUP BY itemID, isActive, isComplete, surfaceId, linkUser, [userLinkEmail], [userLinkId], userType,
									showWizardStatus, allowEdit, editAccessType, allowView, viewAccessType, allowRemove, removeAccessType,
									allowClone, cloneAccessType, ' + @cols + '  '
					END
		
		--print @cols;									
		--print @query;
		--PRINT @query2;
		exec (@query+@query2)
END






