-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-05-12
-- Description:	Get a list of Profile Email addresses of Profiles in a specific status
-- =============================================
CREATE PROCEDURE [dbo].[sp_STUD1_GetProfileEmailAddresses]
	@option INT -- 1 = DEVICES. 2 = MENTORSHIP
AS
BEGIN
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF (@option = 1)
	BEGIN
		SELECT p.surfaceFieldValueNum AS itemId, d2.surfaceFieldValueChar AS Email 
		FROM pal_SurfaceFieldData d2 
		LEFT OUTER JOIN pal_SurfaceItem i2 ON i2.recId = d2.surfaceItemId 
		LEFT OUTER JOIN pal_SurfaceField f2 ON f2.recId = d2.surfaceFieldID 
		INNER JOIN (SELECT d1.surfaceItemId, d1.surfaceFieldValueNum 
			FROM pal_Surface s1 
			LEFT OUTER JOIN pal_SurfaceField f1 ON f1.surfaceId = s1.recId 
			LEFT OUTER JOIN pal_SurfaceFieldData d1 ON d1.surfaceFieldID = f1.recId 
			LEFT OUTER JOIN pal_SurfaceItem i1 ON i1.recId = d1.surfaceItemId 
			WHERE i1.isDeleted = 0 
			AND s1.name LIKE 'Application%Recruitment' 
			AND f1.surfaceFieldName = 'ParentSurfaceItemId' 
			--CVH 2018-02-12 Show all profile statuses (Terminated and Rejected also)
			--AND d1.surfaceFieldValueNum IN (SELECT d.surfaceItemId  
			--	FROM pal_SurfaceFieldData d 
			--	LEFT OUTER JOIN pal_SurfaceField f ON f.recId = d.surfaceFieldID
			--	LEFT OUTER JOIN pal_Surface s ON s.recId = d.surfaceId 
			--	LEFT OUTER JOIN pal_SurfaceItem i ON i.recId = d.surfaceItemId 
			--	LEFT OUTER JOIN pal_SurfaceLookup l ON l.recId = d.surfaceFieldLookupID 
			--	WHERE i.isDeleted = 0 
			--	AND s.name = 'Profiles'
			--	AND f.surfaceFieldDisplay = 'Status' 
			--	AND l.display IN ('Prep Programme', 'School Accepted', 'Scholar', 'Student', 'Alumni')
			--)
		) p ON p.surfaceItemId = d2.surfaceItemId 
		--only select Profile (parent) items that haven't been deleted
		LEFT OUTER JOIN pal_SurfaceItem i3 ON i3.recId = p.surfaceFieldValueNum
		WHERE i3.isDeleted = 0 
		AND i2.isDeleted = 0 
		AND f2.surfaceFieldDisplay = 'Email Address'
		AND d2.surfaceFieldValueChar != ''
		ORDER BY Email
	END
	ELSE
	BEGIN
		SELECT p.surfaceFieldValueNum AS itemId, d2.surfaceFieldValueChar AS Email
		FROM pal_SurfaceFieldData d2 
		LEFT OUTER JOIN pal_SurfaceItem i2 ON i2.recId = d2.surfaceItemId 
		LEFT OUTER JOIN pal_SurfaceField f2 ON f2.recId = d2.surfaceFieldID 
		INNER JOIN (SELECT d1.surfaceItemId, d1.surfaceFieldValueNum  
			FROM pal_Surface s1 
			LEFT OUTER JOIN pal_SurfaceField f1 ON f1.surfaceId = s1.recId 
			LEFT OUTER JOIN pal_SurfaceFieldData d1 ON d1.surfaceFieldID = f1.recId 
			LEFT OUTER JOIN pal_SurfaceItem i1 ON i1.recId = d1.surfaceItemId 
			WHERE i1.isDeleted = 0 
			AND s1.name LIKE 'Application%Recruitment' 
			AND f1.surfaceFieldName = 'ParentSurfaceItemId' 
			AND d1.surfaceFieldValueNum IN (SELECT d.surfaceItemId 
					FROM pal_SurfaceFieldData d 
					LEFT OUTER JOIN pal_SurfaceField f ON f.recId = d.surfaceFieldID
					LEFT OUTER JOIN pal_Surface s ON s.recId = d.surfaceId
					LEFT OUTER JOIN pal_SurfaceLookup l ON l.recId = d.surfaceFieldLookupID 
					WHERE s.name = 'Profiles'
					AND f.surfaceFieldDisplay = 'Grade' 
					AND l.display IN ('11', '12')
			)
		) p ON p.surfaceItemId = d2.surfaceItemId 
		AND i2.isDeleted = 0 
		AND f2.surfaceFieldDisplay = 'Email Address'
		AND d2.surfaceFieldValueChar != ''
		ORDER BY Email
	END
END

