-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-05-23 
-- Description:	Get's the Mentorship Child Grids Data - option 1 for Mentee data, option 2 for Mentor data
-- =============================================
CREATE PROCEDURE [dbo].[sp_STUD1_GetMentorshipChildSurfaceData]  
	@ProfilesItemId INT, 
	@option INT    -- 1-Mentee, 2-Mentor
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

		DECLARE @SurfaceId8 INT, @SurfaceId9 INT;
		DECLARE @Gr8QTName varchar(300) = '', @Gr9QTName varchar(300) = '' ;
		DECLARE @ItemIdCol VARCHAR(300) = '';

		SET @SurfaceId8 = (SELECT TOP 1 recId from pal_Surface Where name Like '%Mentorship%8%')
		SET @SurfaceId9 = (SELECT TOP 1 recId from pal_Surface Where name Like '%Mentorship%9%')

		SET @Gr8QTName = 'qt_' + (SELECT name from pal_Surface Where recId = @SurfaceId8)
		SET @Gr9QTName = 'qt_' + (SELECT name from pal_Surface Where recId = @SurfaceId9)

		SET @ItemIdCol = (SELECT TOP 1 surfaceFieldName FROM pal_SurfaceField WHERE surfaceId = @SurfaceId8 AND surfaceFieldDisplay = 'Profiles Item Id');
		
		DECLARE @cols8 AS VARCHAR(MAX), @cols9 AS VARCHAR(MAX),
		@query  AS VARCHAR(MAX)

		--if linked to a child surface, go one level up to get true ProfilesItemId
		IF ((SELECT COUNT(recId)  
					FROM pal_SurfaceLink l
					WHERE l.surfaceId = @SurfaceId8 
					AND l.linkedSurfaceId IN (SELECT i.surfaceId 
						FROM pal_SurfaceItem i 
						WHERE i.recId = @ProfilesItemId)) = 0)
			BEGIN
				SELECT @ProfilesItemId = 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 = @ProfilesItemId 
				AND f1.surfaceFieldDisplay = 'ParentSurfaceItemId'
			END

		select @cols8 = STUFF((SELECT ',' + QUOTENAME(surfaceFieldName) 
                    from pal_SurfaceField sf 
					where sf.surfaceFieldTypeId NOT IN (1,2,16) 
						and sf.surfaceId = @SurfaceId8 
                    group by sf.surfaceFieldName, sf.recId
                    order by sf.surfaceFieldName
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'');		

		select @cols9 = STUFF((SELECT ',' + QUOTENAME(surfaceFieldName) 
                    from pal_SurfaceField sf 
					where sf.surfaceFieldTypeId NOT IN (1,2,16) 
						and sf.surfaceId = @SurfaceId9 
                    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], [userLinkId],	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,' + @cols8 + ' FROM 	' + @Gr8QTName + '							
				WHERE isActive = 1 	'		
				
				IF (@option = 1)
					SET @query += N' AND ' + CAST(@ProfilesItemId as VARCHAR(10)) + ' IN (select splitdata from dbo.SplitString(surfaceLinkItems, '',''))  '
				ELSE
				BEGIN
					IF (@ProfilesItemId = 0)
						SET @query += N' AND [' + @ItemIdCol + '] = -1';
					ELSE
						SET @query += N' AND [' + @ItemIdCol + '] = ' + CAST(@ProfilesItemId as VARCHAR(10));
				END 

		set @query += N' UNION
				SELECT  [itemID],[ParentSurfaceItemId], isActive, ISNULL(isComplete,0) isComplete, 
									surfaceId, linkUser, [userLinkEmail], [userLinkId],	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,' + @cols9 + ' FROM 	' + @Gr9QTName + '							
				WHERE isActive = 1 	'		
				
				IF (@option = 1)
					SET @query += N' AND ' + CAST(@ProfilesItemId as VARCHAR(10)) + ' IN (select splitdata from dbo.SplitString(surfaceLinkItems, '',''))  '
				ELSE
				BEGIN
					IF (@ProfilesItemId = 0)
						SET @query += N' AND [' + @ItemIdCol + '] = -1';
					ELSE
						SET @query += N' AND [' + @ItemIdCol + '] = ' + CAST(@ProfilesItemId as VARCHAR(10));
				END 
		

		--print @query;
		exec (@query)


END