-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-05-12
-- Description:	Get all Devices linked to the current Profile by email address
-- =============================================
CREATE PROCEDURE sp_STUD1_GetDeviceManagerChildData
	@itemId INT -- = 18913
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @query  AS NVARCHAR(MAX);
	DECLARE @emailColName varchar(150), @userEmail varchar(150);
	SELECT @emailColName = f.surfaceFieldName FROM pal_SurfaceField f LEFT OUTER JOIN pal_Surface s ON s.recId = f.surfaceId WHERE s.name = 'DeviceManagement' AND f.surfaceFieldDisplay LIKE '%Email' 

	SELECT @userEmail = d3.surfaceFieldValueChar
	FROM pal_Surface s3 
	LEFT OUTER JOIN pal_SurfaceField f3 ON f3.surfaceId = s3.recId 
	LEFT OUTER JOIN pal_SurfaceItem i3 ON i3.surfaceId = f3.surfaceId 
	LEFT OUTER JOIN pal_SurfaceFieldData d3 ON d3.surfaceFieldID = f3.recId AND d3.surfaceItemId = i3.recId 
	WHERE i3.recId IN (
		SELECT  d2.surfaceItemId 
		FROM pal_Surface s2
		LEFT OUTER JOIN pal_SurfaceField f2 ON s2.recId = f2.surfaceId 
		LEFT OUTER JOIN pal_SurfaceFieldData d2 ON f2.recId = d2.surfaceFieldID 
		WHERE f2.surfaceFieldName = 'ParentSurfaceItemId' 
		AND s2.name LIKE 'Application%Recruitment' 
		AND d2.surfaceFieldValueNum = @itemId
		)
	AND f3.surfaceFieldDisplay LIKE 'Email%'

	SET @query = N' 
		IF ''' + ISNULL(@userEmail, '') + ''' <> ''''
		BEGIN
			SELECT * 
			FROM qt_DeviceManagement 
			WHERE ' + ISNULL(@emailColName, '') + ' = ''' + ISNULL(@userEmail, '') + '''
		END
		ELSE
		BEGIN
			SELECT * 
			FROM qt_DeviceManagement 
			WHERE itemID IS NULL
		END '

	exec sp_executesql @query;

END
GO
