USP_DATAFORMTEMPLATE_VIEW_CLEARSTUDENTSCHEDULES_STUDENTS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CLEARSTUDENTSCHEDULES_STUDENTS
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @STUDENTS xml = null output
)
as
    set nocount on;

    if @ID = '00000000-0000-0000-0000-000000000000'
        set @ID = null

    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date
    declare @SESSIONID uniqueidentifier
    declare @SCHOOLID uniqueidentifier

    select
        @DATALOADED = 1,
        @SESSION_STARTDATE = min(TERM.STARTDATE),
        @SESSION_ENDDATE = max(TERM.ENDDATE),
        @SESSIONID = SESSION.ID,
        @SCHOOLID = ACADEMICYEAR.SCHOOLID
    from dbo.TERM
        inner join dbo.APPUSERSESSION on TERM.SESSIONID = APPUSERSESSION.SESSIONID
        inner join dbo.SESSION on APPUSERSESSION.SESSIONID = SESSION.ID
        inner join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
    where APPUSERSESSION.ID = @CURRENTAPPUSERID
    group by APPUSERSESSION.ID, SESSION.ID, ACADEMICYEAR.SCHOOLID;

    -- Due to SQL performance quirks, moved the processing of the selection into a temp table to create a better execution plan.

    declare @STUDENTLIST table ([ID] uniqueidentifier)
    if @ID is not null
        insert into @STUDENTLIST
        select ID
        from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@ID);

    with ENROLLMENT_CTE (STUDENTID, STUDENTNAME, STUDENTLASTNAME, GRADELEVEL, HOMEROOMTEACHER, GENDER)
    as
    (
        select
            EDUCATIONALHISTORY.CONSTITUENTID as STUDENTID,
            CONSTITUENT.NAME + case when LEN(CONSTITUENT.NICKNAME) > 0
                then ' (' + CONSTITUENT.NICKNAME + ')'
                else ''
            end as STUDENTNAME,
            CONSTITUENT.KEYNAME as STUDENTLASTNAME,
            GRADELEVEL.DESCRIPTION as GRADELEVEL,
            HOMEROOMTEACHER.NAME as HOMEROOMTEACHER,
            CONSTITUENT.GENDER
        from dbo.CONSTITUENT
            inner join dbo.EDUCATIONALHISTORY on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
            inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
            left join dbo.CONSTITUENT HOMEROOMTEACHER on STUDENTPROGRESSION.HOMEROOMTEACHERID = HOMEROOMTEACHER.ID
            inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
            inner join dbo.GRADELEVEL on SCHOOLGRADELEVEL.GRADELEVELID = GRADELEVEL.ID
            inner join dbo.STUDENTCOURSE on EDUCATIONALHISTORY.CONSTITUENTID = STUDENTCOURSE.STUDENTID
            inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
                and STUDENTCLASSMEETINGGROUP.STATUSCODE in (0, 3)
            inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                and not (CLASSMEETINGGROUP.ENDDATE < @SESSION_STARTDATE or CLASSMEETINGGROUP.STARTDATE > @SESSION_ENDDATE)
        where SCHOOLGRADELEVEL.SCHOOLID = @SCHOOLID
            and not (STUDENTPROGRESSION.ENDDATE < @SESSION_STARTDATE or STUDENTPROGRESSION.STARTDATE > @SESSION_ENDDATE)
            and not exists (
                select STUDENTMARKINGCOLUMNGRADE.ID
                from dbo.STUDENTCOURSE
                    inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
                    inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                        and not (CLASSMEETINGGROUP.ENDDATE < @SESSION_STARTDATE or CLASSMEETINGGROUP.STARTDATE > @SESSION_ENDDATE)
                    inner join dbo.STUDENTMARKINGCOLUMNGRADE on STUDENTCLASSMEETINGGROUP.ID = STUDENTMARKINGCOLUMNGRADE.STUDENTCLASSMEETINGGROUPID
                where STUDENTCOURSE.STUDENTID = CONSTITUENT.ID
            )                
            and    (@ID is null or STUDENTCOURSE.STUDENTID in (
                    select ID
                    from @STUDENTLIST
                )
            )
    )
    select @STUDENTS =
        (select
            T.STUDENTID as ID,
            1 as CLEAR,
            T.STUDENTNAME as NAME,
            (select stuff(
                (select ', ' + LOCAL_CTE.GRADELEVEL
                from ENROLLMENT_CTE LOCAL_CTE
                where LOCAL_CTE.STUDENTID = T.STUDENTID
                group by GRADELEVEL
                order by LOCAL_CTE.GRADELEVEL
                for xml path('')),
            1, 2, '')) as GRADELEVEL,
            (select stuff(
                (select ', ' + LOCAL_CTE.HOMEROOMTEACHER
                from ENROLLMENT_CTE LOCAL_CTE
                where LOCAL_CTE.STUDENTID = T.STUDENTID
                group by HOMEROOMTEACHER
                order by LOCAL_CTE.HOMEROOMTEACHER
                for xml path('')),
            1, 2, '')) as HOMEROOMTEACHER,
            T.GENDER
        from (
                select
                    STUDENTID,
                    STUDENTNAME,
                    STUDENTLASTNAME,
                    GENDER,
                    ROW_NUMBER() over (partition by STUDENTID order by STUDENTNAME) as ROW
                from
                    ENROLLMENT_CTE
            ) T
        where T.ROW = 1
        order by T.STUDENTLASTNAME
        for xml raw('ITEM'),type,elements,root('STUDENTS'),binary base64)

    return 0;