CREATE PROC sp_STUD_1_GetReportPrepPage
( @itemId INT )
AS

SELECT	DISTINCT P.Prep2017_SessionReviewTool_Name+' '+P.Prep2017_SessionReviewTool_Surname AS [scholarName]
		,(dbo.fn_STUD_1_GetPrepAttendancePrecentage(P.Prep2017_SessionReviewTool_Name,P.Prep2017_SessionReviewTool_Surname,P.Prep2017_SessionReviewTool_PrepProgrammeYear)) AS [attendance]
		,(dbo.fn_STUD_1_GetPrepMathsTeachers(P.Prep2017_SessionReviewTool_Name,P.Prep2017_SessionReviewTool_Surname,P.Prep2017_SessionReviewTool_PrepProgrammeYear)) AS [mathsTeachers]
		,(dbo.fn_STUD_1_GetPrepEnglishTeachers(P.Prep2017_SessionReviewTool_Name,P.Prep2017_SessionReviewTool_Surname,P.Prep2017_SessionReviewTool_PrepProgrammeYear)) AS [englishTeachers]
		,ISNULL(NM.note,'') [mathsComment]
		,ISNULL(NE.note,'') [englishComment]
FROM	dbo.qt_PrepProgramme2017 P
		CROSS APPLY (SELECT splitdata FROM dbo.SplitString(surfaceLinkItems, ',')) X 
		JOIN dbo.pal_SurfaceItem I
			ON I.surfaceId = (SELECT recId FROM pal_Surface WHERE name = 'Profiles')
			AND I.recId = X.splitdata
		JOIN dbo.pal_Report RE
			ON RE.name = 'Prep Programme: English'
		LEFT JOIN dbo.pal_ReportNote NE
			ON NE.itemId = I.recId
			AND NE.reportId = RE.recId
		JOIN dbo.pal_Report RM
			ON RM.name = 'Prep Programme: Maths'
		LEFT JOIN dbo.pal_ReportNote NM
			ON NM.itemId = I.recId
			AND NM.reportId = RM.recId
WHERE	I.recId = @itemId


