-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-07-06
-- Description:	Based on GetChildSurfaceQueryDataSummarized. Gets a list of all Primary Caregivers linked to given Profile itemID (gets from 2 levels down)
-- =============================================
CREATE PROCEDURE [dbo].[sp_STUD1_GetProfilePrimaryCaregivers] 
	@SurfaceID int,
	@ParentSurfaceItemId int,
	@RowLimit INT = 0,
	@GridOnly bit = 1
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @cols AS NVARCHAR(MAX), @selectColSum AS NVARCHAR(MAX), @groupByCols AS NVARCHAR(MAX),
		@query  AS NVARCHAR(MAX),
		@childParentItemIds AS NVARCHAR(MAX)

		DECLARE @QueryTableName varchar(300) = ''

		SET @QueryTableName = 'qt_' + (SELECT name from pal_Surface Where recId = @SurfaceID)

		--get all items on lower level surface linked to parent item Id 
		SELECT @childParentItemIds =  STUFF((SELECT ',' + CONVERT(VARCHAR(50), d1.surfaceItemId)
			FROM pal_SurfaceFieldData d1 WITH(NOLOCK)
			LEFT OUTER JOIN pal_SurfaceField f1 ON f1.recId = d1.surfaceFieldID 
			LEFT OUTER JOIN pal_SurfaceItem i1 ON i1.recId = d1.surfaceItemId 
			WHERE f1.surfaceFieldDisplay = 'ParentSurfaceItemId' 
			AND d1.surfaceFieldValueNum = @parentSurfaceItemId
			AND i1.isActive = 1 AND i1.isDeleted = 0
			AND d1.surfaceId IN (
				--get all surface Id's where target surface is a child surface on one level lower than target field
				SELECT f3.surfaceId 
				FROM pal_SurfaceField f3 
				LEFT OUTER JOIN pal_Surface s3 ON s3.name = f3.relationalSurface  
				WHERE f3.surfaceFieldTypeId = 11 --grid 
				AND s3.recId = @surfaceId 
				AND f3.surfaceId NOT IN (SELECT i3.surfaceId 
				FROM pal_SurfaceItem i3 
				WHERE i3.recId = @parentSurfaceItemId))
		FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)') 
		,1,1,'')

		select @cols = STUFF((SELECT ',' + QUOTENAME(surfaceFieldName) 
                    from pal_SurfaceField sf where sf.surfaceFieldTypeId <> 1 AND sf.surfaceFieldTypeId <> 2 and sf.surfaceId = @SurfaceID
					and sf.surfaceFieldDisplay <> 'ParentSurfaceItemId' 
                    group by sf.surfaceFieldName, sf.recId
                    order by sf.surfaceFieldName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

		IF(@RowLimit > 0)
		BEGIN 
			SET @query = N'SELECT TOP '+CAST(@RowLimit AS VARCHAR)+' '
		END
		ELSE
		BEGIN
			SET @query = N'SELECT '
		END

		IF (LEN(@selectColSum) > 0)
		BEGIN
			set @query += CONVERT(VARCHAR(50), @ParentSurfaceItemId) + ' AS ParentSurfaceItemId, 1 AS itemID, isActive, isComplete, surfaceId, linkUser, showWizardStatus, allowEdit, editAccessType, allowView, viewAccessType, allowRemove, removeAccessType, allowClone, cloneAccessType, ' + @selectColSum + ' '
		END
		ELSE
		BEGIN
			set @query += ' * '
		END
		
		set @query += N' 
					FROM  ' + @QueryTableName + '
					

		Where [ParentSurfaceItemId] IS NOT NULL AND ISNULL([ParentSurfaceItemId], 0) IN (' + ISNULL(@childParentItemIds, '-1') + ') '

		IF(@RowLimit > 0)
		BEGIN 
			SET @query += N' ORDER BY 1 DESC '
		END

--print @query;
		exec sp_executesql @query;
END
