USP_DATAFORMTEMPLATE_VIEW_EDITSTUDENTSCHEDULE_STUDENT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EDITSTUDENTSCHEDULE_STUDENT
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @STUDENT_NAME nvarchar(100) = null output,
    @GRADELEVEL nvarchar(200) = null output,
    @ADVISOR nvarchar(1000) = null output,
    @HOMEROOMTEACHER nvarchar(1000) = null output,
    @HASCORECURRICULUM bit = null output,
    @GENDER nvarchar(100) = null output,
    @COURSETERMS xml = null output,
    @SCHOOLGRADELEVELID uniqueidentifier = null output,
    @WITHDRAWNCLASSCOUNT int = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 1;

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

    select
        @SESSIONID = APPUSERSESSION.SESSIONID,
        @SESSION_STARTDATE = min(STARTDATE),
        @SESSION_ENDDATE = max(ENDDATE)
    from dbo.TERM
        inner join dbo.APPUSERSESSION on TERM.SESSIONID = APPUSERSESSION.SESSIONID
    where APPUSERSESSION.ID = @CURRENTAPPUSERID
    group by APPUSERSESSION.ID, APPUSERSESSION.SESSIONID

    select
        @SCHOOLID = SCHOOLID
    from dbo.SESSION
        inner join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
    where SESSION.ID = @SESSIONID    

    exec dbo.USP_STUDENT_GETSCHEDULINGPROFILE
        @ID,
        @SESSION_STARTDATE output,
        @SESSION_ENDDATE output,
        @STUDENT_NAME output,
        null,
        @GRADELEVEL output,
        @ADVISOR output,
        @HOMEROOMTEACHER output,
        @GENDER output,
        @SCHOOLGRADELEVELID output

    select @COURSETERMS =
        (
            select
                STUDENTCOURSE.ID,
                STUDENTCOURSE.COURSEID,
                CLASS.ID as CLASSID,
                TERM.ID as TERMID,
                case when CLASS.ID is null
                    then COURSERESTRICTION.LENGTHINTERMS
                    else null
                end as LENGTHINTERMS,                    
                case when CLASSMEETINGGROUP.ID is not null
                    then dbo.UFN_CLASSMEETINGGROUP_GETFIRSTMEETING(CLASSMEETINGGROUP.ID)
                    else null
                end as FIRSTMEETING                
            from dbo.STUDENTCOURSE
                left join dbo.COURSE on STUDENTCOURSE.COURSEID = COURSE.ID
                left join dbo.STUDENTCOURSEREQUEST on STUDENTCOURSE.ID = STUDENTCOURSEREQUEST.ID
                left join dbo.COURSERESTRICTION on STUDENTCOURSE.COURSEID = COURSERESTRICTION.COURSEID
                    and STUDENTCOURSEREQUEST.CLASSSTARTDATE >= COURSERESTRICTION.STARTDATE
                    and STUDENTCOURSEREQUEST.CLASSSTARTDATE <= COURSERESTRICTION.ENDDATE
                left join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID                  
                left join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                left join dbo.CLASS on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                left join dbo.SESSION on SESSION.ID = @SESSIONID
                left join dbo.TERM on SESSION.ID = TERM.SESSIONID
                    and coalesce(CLASSMEETINGGROUP.STARTDATE, STUDENTCOURSEREQUEST.CLASSSTARTDATE) = TERM.STARTDATE
            where (STUDENTCOURSEREQUEST.CLASSSTARTDATE is null or TERM.ID is not null)
                and STUDENTCOURSE.STUDENTID = @ID
                and COURSE.SCHOOLID = @SCHOOLID
                and (STUDENTCOURSEREQUEST.ID is not null or TERM.ID is not null)
                and (STUDENTCLASSMEETINGGROUP.ID is null or STUDENTCLASSMEETINGGROUP.STATUSCODE = 0)
            order by TERM.STARTDATE, COURSE.COURSEID
            for xml raw('ITEM'),type,elements,root('COURSETERMS'),binary base64
        )

    select @HASCORECURRICULUM =
            case when
                exists(
                    select 1
                    from dbo.EDUCATIONALHISTORY
                        inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
                        inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
                        inner join dbo.CORECURRICULUM on SCHOOLGRADELEVEL.ID = CORECURRICULUM.SCHOOLGRADELEVELID
                            or (SCHOOLGRADELEVEL.SCHOOLID = CORECURRICULUM.SCHOOLID and CORECURRICULUM.SCHOOLGRADELEVELID is null)
                    where EDUCATIONALHISTORY.CONSTITUENTID = @ID
                        and not (STUDENTPROGRESSION.ENDDATE < @SESSION_STARTDATE or STUDENTPROGRESSION.STARTDATE > @SESSION_ENDDATE)
                )
                then 1
                else 0
            end,
        @WITHDRAWNCLASSCOUNT = (select COUNT(distinct CLASSMEETINGGROUP.CLASSID)
            from dbo.STUDENTCOURSE
                inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
                inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                inner join dbo.CLASS on CLASSMEETINGGROUP.CLASSID = CLASS.ID
            where STUDENTCLASSMEETINGGROUP.STATUSCODE = 2
                and STUDENTCOURSE.STUDENTID = @ID
                and CLASSMEETINGGROUP.STARTDATE >= @SESSION_STARTDATE
                and CLASSMEETINGGROUP.ENDDATE <= @SESSION_ENDDATE)

    return 0;