USP_REPORT_STUDENTSCHEDULE

CREATE procedure dbo.USP_REPORT_STUDENTSCHEDULE(
    @RECORDQUERYID uniqueidentifier = null,
    @RECORDID_SINGLE uniqueidentifier = null,
    @SCHOOLID uniqueidentifier = null,
    @ACADEMICYEARID uniqueidentifier = null,
    @SESSIONID uniqueidentifier = null,  
    @TERMS nvarchar(max) = null, 
    @DISPLAYBY tinyint = null,
    @INCLUDENOMEETINGS bit = null,
    @PERIODS nvarchar(max) = null,
    @CYCLEDAYS nvarchar(max) = null,
    @STARTTIME smalldatetime = null,
    @ENDTIME smalldatetime = null,
    @INTERVAL tinyint = null,
    @BREAKDOWNBY tinyint = null
)
as
begin
    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date

    select 
        @SESSION_STARTDATE = STARTDATE, 
        @SESSION_ENDDATE = ENDDATE 
    from 
        dbo.UFN_SESSION_GETDATES(@SESSIONID)

    declare @ACADEMICYEAR nvarchar(100)
    set @ACADEMICYEAR = dbo.UFN_ACADEMICYEAR_GETNAME(@ACADEMICYEARID) 

    declare @CYCLEDAYCOUNT int
    select @CYCLEDAYCOUNT = COUNT(1) from dbo.UFN_PARSE_STRING(@CYCLEDAYS,',')

    declare @STUDENTQUERY_IDS table (ID uniqueidentifier)
    if not @RECORDQUERYID is null
        begin
            if not exists(select ID from dbo.IDSETREGISTER where ID = @RECORDQUERYID) 
                raiserror('Selection ID set does not exist in the database.', 15, 1)

            insert into @STUDENTQUERY_IDS
                select 
                    SELECTION.ID
                from 
                    dbo.UFN_IDSETREADER_GETRESULTS_GUID(@RECORDQUERYID) SELECTION 
        end

    -- single student, selection or all students; filter by session dates and enrollment                

    declare @STUDENTIDS table (ID uniqueidentifier, HOMEROOMTEACHERNAME nvarchar(100), HOMEROOMTEACHER_KEYNAME nvarchar(100), ADVISORNAME nvarchar(100), ADVISOR_KEYNAME nvarchar(100), GRADELEVEL nvarchar(100), GRADELEVEL_SEQ int)
    insert into @STUDENTIDS 
        select 
            STUDENT.ID,
            HOMEROOMTEACHER.NAME as HOMEROOMTEACHERNAME,
            HOMEROOMTEACHER.KEYNAME as HOMEROOMTEACHER_KEYNAME,
            ADVISOR.NAME as ADVISORNAME,
            ADVISOR.KEYNAME as ADVISOR_KEYNAME,
            GRADELEVEL.DESCRIPTION as GRADELEVEL,
            GRADELEVEL.SEQUENCE as GRADELEVEL_SEQ
        from STUDENT
            inner join dbo.EDUCATIONALHISTORY 
                on STUDENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
            inner join dbo.STUDENTPROGRESSION 
                on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
            left join dbo.CONSTITUENT HOMEROOMTEACHER   
                on HOMEROOMTEACHER.ID = STUDENTPROGRESSION.HOMEROOMTEACHERID
            left join dbo.STUDENTADVISOR        
                on STUDENTPROGRESSION.ID = STUDENTADVISOR.STUDENTPROGRESSIONID 
            left join dbo.CONSTITUENT ADVISOR           
                on ADVISOR.ID = STUDENTADVISOR.FACULTYID
            inner join SCHOOLGRADELEVEL 
                on SCHOOLGRADELEVEL.ID = STUDENTPROGRESSION.SCHOOLGRADELEVELID 
            inner join GRADELEVEL 
                on GRADELEVEL.ID = SCHOOLGRADELEVEL.GRADELEVELID
            left join @STUDENTQUERY_IDS SELECTION 
                on SELECTION.ID = STUDENT.ID
        where STUDENTPROGRESSION.STARTDATE <= @SESSION_STARTDATE
            and STUDENTPROGRESSION.ENDDATE >= @SESSION_ENDDATE    
            and SCHOOLGRADELEVEL.SCHOOLID = @SCHOOLID
            and (@RECORDQUERYID is null or SELECTION.ID is not null)
            and (@RECORDID_SINGLE is null or STUDENT.ID = @RECORDID_SINGLE)
            and exists 
                (select 
                    STUDENTCOURSE.STUDENTID 
                from dbo.STUDENTCOURSE 
                    inner join dbo.STUDENTCLASSMEETINGGROUP 
                        on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
                    inner join dbo.CLASSMEETINGGROUP 
                        on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                    inner join dbo.COURSE 
                        on COURSE.ID = STUDENTCOURSE.COURSEID
                where 
                    STUDENTCOURSE.STUDENTID = STUDENT.ID and 
                    COURSE.SCHOOLID = @SCHOOLID and
                    COURSE.PRINTONSCHEDULE = 1 and
                    STUDENTCLASSMEETINGGROUP.STATUSCODE = 0 and --enrolled

                    not (CLASSMEETINGGROUP.ENDDATE < @SESSION_STARTDATE or 
                    CLASSMEETINGGROUP.STARTDATE > @SESSION_ENDDATE)
                )

    --create map of possible terms/days/times/periods for each student in query...this should reflect what we see in the grid

    --only create terms for students that actually have meeting data...avoid empty grids

    declare @SCHEDULE table (ID int NOT NULL IDENTITY(1,1), STUDENTID uniqueidentifier, HOMEROOMTEACHERNAME nvarchar(100), HOMEROOMTEACHER_KEYNAME nvarchar(100), ADVISORNAME nvarchar(100), ADVISOR_KEYNAME nvarchar(100), GRADELEVEL nvarchar(100), GRADELEVEL_SEQ int, TERMID uniqueidentifier, CYCLEDAYID uniqueidentifier, PERIOD nvarchar(60), PERIOD_SEQUENCE int, LENGTHIN_TIMEROWS int, TIMEROW_COUNT int, CYCLEDAYCOUNT int, CLASSID uniqueidentifier, NAME nvarchar(32), CLASSNAME nvarchar(60), CLASS_STARTTIME smalldatetime, CLASS_ENDTIME smalldatetime, ACADEMICYEAR nvarchar(100), FACULTY nvarchar(100), LENGTHINPERIODS int, PERIODCOUNT int, INTERVAL int, STARTTIME smalldatetime, ENDTIME smalldatetime, HIDEGRID bit, HIDENOMEETINGSGRID bit)

    if @BREAKDOWNBY = 0 --Periods

        begin
            insert into @SCHEDULE (STUDENTID, HOMEROOMTEACHERNAME, HOMEROOMTEACHER_KEYNAME, ADVISORNAME, ADVISOR_KEYNAME, GRADELEVEL, GRADELEVEL_SEQ, TERMID, CYCLEDAYID, PERIOD, PERIOD_SEQUENCE, CYCLEDAYCOUNT, ACADEMICYEAR)
            select
                STUDENTS.ID,
                STUDENTS.HOMEROOMTEACHERNAME,
                STUDENTS.HOMEROOMTEACHER_KEYNAME,
                STUDENTS.ADVISORNAME,
                STUDENTS.ADVISOR_KEYNAME,
                STUDENTS.GRADELEVEL, 
                STUDENTS.GRADELEVEL_SEQ,
                TERMS.ELEMENT_VALUE as TERMID,
                CYCLEDAYS.ELEMENT_VALUE as CYCLEDAYID,
                PERIODS.ELEMENT_VALUE,
                --period start time on the first cycle day with a matching period description...used to order report

                (select top 1 TIMETABLEDAYPERIOD.STARTTIME 
                     from dbo.TIMETABLEDAYPERIOD 
                         inner join dbo.TIMETABLEDAY  
                            on TIMETABLEDAY.ID = TIMETABLEDAYPERIOD.TIMETABLEDAYID 
                         inner join dbo.CYCLEDAY  
                            on CYCLEDAY.ID = TIMETABLEDAY.CYCLEDAYID 
                         inner join SESSION 
                            on SESSION.TIMETABLEID = TIMETABLEDAY.TIMETABLEID 
                     where 
                         SESSION.ID = @SESSIONID and TIMETABLEDAYPERIOD.PERIOD = PERIODS.ELEMENT_VALUE
                     order by CYCLEDAY.SEQUENCE) as PERIOD_SEQUENCE,
                CYCLEDAYCOUNT = @CYCLEDAYCOUNT,
                ACADEMICYEAR = @ACADEMICYEAR
            from @STUDENTIDS as STUDENTS
                cross join dbo.UFN_PARSE_STRING(@CYCLEDAYS,',') CYCLEDAYS
                cross join dbo.UFN_PARSE_STRING(@PERIODS,',') as PERIODS
                cross join dbo.UFN_PARSE_STRING(@TERMS,',') TERMS
                where exists(
                    select 
                        STUDENTCOURSE.ID 
                    from dbo.STUDENTCOURSE 
                        inner join dbo.STUDENTCLASSMEETINGGROUP 
                            on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
                        inner join dbo.CLASSMEETINGGROUP 
                            on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                        inner join dbo.TERM  
                            on TERM.SESSIONID = @SESSIONID
                                and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                                and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                                and TERM.ID = TERMS.ELEMENT_VALUE 
                        inner join dbo.CLASSMEETINGTEMPLATE
                            on CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                        inner join dbo.COURSE 
                            on COURSE.ID = STUDENTCOURSE.COURSEID
                    where 
                        STUDENTS.ID = STUDENTCOURSE.STUDENTID and
                        COURSE.SCHOOLID = @SCHOOLID and
                        COURSE.PRINTONSCHEDULE = 1 and
                        STUDENTCLASSMEETINGGROUP.STATUSCODE = 0) --enrolled


            --populate all period data for each class meeting

            update @SCHEDULE
                set 
                    CLASSID = CLASSMEETINGGROUP.CLASSID,
                    NAME = COURSE.[COURSEID] + ' - ' + CLASS.[SECTION],
                    CLASSNAME = CLASS.[NAME],
                    CLASS_STARTTIME = dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.STARTTIME),
                    CLASS_ENDTIME = dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.ENDTIME),
                    LENGTHINPERIODS = CLASSMEETINGTEMPLATE.LENGTHINPERIODS,
                    PERIODCOUNT = MEETING_PERIODS.ROW,
                    FACULTY = dbo.UFN_CLASSMEETINGGROUP_GETFACULTYSTRING(CLASSMEETINGGROUP.[ID])
            from @SCHEDULE as SCHEDULE
                cross join dbo.CLASSMEETINGGROUP
                inner join dbo.STUDENTCLASSMEETINGGROUP           
                    on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                inner join dbo.STUDENTCOURSE                      
                    on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
                inner join dbo.CLASS                              
                    on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                inner join dbo.CLASSMEETINGTEMPLATE               
                    on CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                inner join dbo.COURSE                             
                    on CLASS.COURSEID = COURSE.ID
                inner join dbo.TERM                               
                    on TERM.SESSIONID = @SESSIONID
                    and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                    and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                inner join dbo.CYCLEDAY                           
                    on CYCLEDAY.ID = CLASSMEETINGTEMPLATE.CYCLEDAYID                                                
                cross apply --get all periods for the class; sql copied from UFN_CLASSMEETINGTEMPLATE_GETPERIODSTRING

                    (select
                        T.PERIOD, T.ROW
                    from
                        (select
                            [PEER].PERIOD as PERIOD,
                            [PEER].STARTTIME,
                            ROW_NUMBER() over (order by [PEER].STARTTIME) as ROW                
                        from dbo.CLASSMEETINGTEMPLATE CMT
                            left join dbo.TIMETABLEDAYPERIOD [PRIMARY] on CMT.TIMETABLEDAYPERIODID = [PRIMARY].ID 
                            left join dbo.TIMETABLEDAYPERIOD [PEER] on [PRIMARY].TIMETABLEDAYID = [PEER].TIMETABLEDAYID
                        where CMT.ID = CLASSMEETINGTEMPLATE.ID
                            and ([PRIMARY].ID is null or [PEER].STARTTIME >= [PRIMARY].STARTTIME)
                        ) T
                    where ROW <= CLASSMEETINGTEMPLATE.LENGTHINPERIODS) MEETING_PERIODS    
            where 
                TERM.ID = SCHEDULE.TERMID and 
                CYCLEDAY.ID = SCHEDULE.CYCLEDAYID and
                STUDENTCOURSE.STUDENTID = SCHEDULE.STUDENTID and
                SCHEDULE.PERIOD = MEETING_PERIODS.PERIOD and 
                COURSE.PRINTONSCHEDULE = 1 and
                STUDENTCLASSMEETINGGROUP.STATUSCODE = 0 --enrolled

        end
    else --Interval

        begin
            --number of time slices/rows in each grid

            declare @FACTOR float(2) = (1440/@INTERVAL)
            declare @ADJUSTED_STARTTIME smalldatetime = Floor(Cast(@STARTTIME as float(2))*@FACTOR)/@FACTOR
          declare @ADJUSTED_ENDTIME smalldatetime = Ceiling(Cast(@ENDTIME as float(2))*@FACTOR)/@FACTOR

            declare @TIME_ROWS int = DateDiff(minute, @ADJUSTED_STARTTIME, @ADJUSTED_ENDTIME) / @INTERVAL

            declare @TIME_INTERVALS table (INTERVAL int, STARTTIME smalldatetime, ENDTIME smalldatetime)
            insert into @TIME_INTERVALS
                select
                    NUM,
                    DateAdd(minute, NUM * @INTERVAL, @ADJUSTED_STARTTIME), 
                    DateAdd(minute, (NUM + 1) * @INTERVAL, @ADJUSTED_STARTTIME)
                from 
                    dbo.NUMBERS with (nolock)
                where 
                    NUM < @TIME_ROWS

            insert into @SCHEDULE (STUDENTID, HOMEROOMTEACHERNAME, HOMEROOMTEACHER_KEYNAME, ADVISORNAME, ADVISOR_KEYNAME, GRADELEVEL, GRADELEVEL_SEQ, TERMID, CYCLEDAYID, INTERVAL, STARTTIME, ENDTIME, CYCLEDAYCOUNT, ACADEMICYEAR)
            select
                STUDENTS.ID,
                STUDENTS.HOMEROOMTEACHERNAME,
                STUDENTS.HOMEROOMTEACHER_KEYNAME,
                STUDENTS.ADVISORNAME,
                STUDENTS.ADVISOR_KEYNAME,
                STUDENTS.GRADELEVEL, 
                STUDENTS.GRADELEVEL_SEQ,
                TERMS.ELEMENT_VALUE as TERMID,
                CYCLEDAYS.ELEMENT_VALUE as CYCLEDAYID,
                TIMES.INTERVAL,
                TIMES.STARTTIME,
                TIMES.ENDTIME,
                CYCLEDAYCOUNT = @CYCLEDAYCOUNT,
                ACADEMICYEAR = @ACADEMICYEAR
            from @STUDENTIDS as STUDENTS
                cross join dbo.UFN_PARSE_STRING(@TERMS,',') TERMS
                cross join dbo.UFN_PARSE_STRING(@CYCLEDAYS,',') CYCLEDAYS
                cross join @TIME_INTERVALS as TIMES
                where exists(
                    select STUDENTCOURSE.ID 
                    from dbo.STUDENTCOURSE 
                        inner join dbo.STUDENTCLASSMEETINGGROUP 
                            on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
                        inner join dbo.CLASSMEETINGGROUP 
                            on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                        inner join dbo.TERM  
                            on TERM.SESSIONID = @SESSIONID
                                and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                                and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                                and TERM.ID = TERMS.ELEMENT_VALUE 
                        inner join dbo.CLASSMEETINGTEMPLATE
                            on CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                        inner join dbo.COURSE 
                            on COURSE.ID = STUDENTCOURSE.COURSEID
                    where 
                        STUDENTS.ID = STUDENTCOURSE.STUDENTID and
                        COURSE.SCHOOLID = @SCHOOLID and
                        COURSE.PRINTONSCHEDULE = 1 and
                        STUDENTCLASSMEETINGGROUP.STATUSCODE = 0) --enrolled


            --populate time data for each class meeting

            update @SCHEDULE
                set 
                    CLASSID = I.CLASSID,
                    NAME = I.NAME,
                    CLASSNAME = I.CLASSNAME,
                    CLASS_STARTTIME = I.CLASS_STARTTIME,
                    CLASS_ENDTIME = I.CLASS_ENDTIME,
                    LENGTHIN_TIMEROWS = I.LENGTHIN_TIMEROWS,
                    TIMEROW_COUNT = I.TIMEROW_COUNT,
                    FACULTY = I.FACULTY,
                    PERIOD = I.PERIOD
            from @SCHEDULE as SCHEDULE
            inner join (
                select ID = T.ID,
                    CLASSID = T.CLASSID,
                    NAME = T.NAME,
                    CLASSNAME = T.CLASSNAME,
                    CLASS_STARTTIME,
                    CLASS_ENDTIME,
                    LENGTHIN_TIMEROWS = 
  case when T.CLASSID is null 
                            then null
                            else COUNT(T.STUDENTID) OVER (PARTITION BY T.STUDENTID, T.TERMID, T.CYCLEDAYCODE, T.CLASSID) end,
                    TIMEROW_COUNT = 
                        case when T.CLASSID is null 
                            then null
                            else ROW_NUMBER() OVER (PARTITION BY T.STUDENTID, T.TERMID, T.CYCLEDAYCODE, T.CLASSID order by T.INTERVAL) end,
                    FACULTY = dbo.UFN_CLASSMEETINGGROUP_GETFACULTYSTRING(T.CLASSMEETINGGROUPID),
                    PERIOD = T.PERIOD
                from
                    (select 
                        SCHEDULE.ID,
                        SCHEDULE.INTERVAL,
                        SCHEDULE.STARTTIME as ROW_STARTTIME,
                        SCHEDULE.ENDTIME as ROW_ENDTIME,
                        SCHEDULE.STUDENTID as STUDENTID,
                        case when CMT.ID is null
                            then null
                            else CLASSMEETINGGROUP.CLASSID
                        end as CLASSID,
                        COURSE.[COURSEID] + ' - ' + CLASS.[SECTION] as NAME,
                        CLASS.[NAME] as CLASSNAME,
                        CMT.STARTTIME as CLASS_STARTTIME,
                        CMT.ENDTIME as CLASS_ENDTIME,
                        CYCLEDAY.CODE as CYCLEDAYCODE,
                        TERM.ID as TERMID,
                        CLASSMEETINGGROUP.ID as CLASSMEETINGGROUPID,
                        TIMETABLEDAYPERIOD.PERIOD as PERIOD,
                        ROW_NUMBER() OVER (PARTITION BY SCHEDULE.STUDENTID, TERM.ID, SCHEDULE.INTERVAL, CYCLEDAY.ID order by SCHEDULE.INTERVAL, CLASSMEETINGGROUP.ID desc) as ROWNUMBER
                    from @SCHEDULE as SCHEDULE
                    cross join dbo.CLASSMEETINGGROUP
                    inner join dbo.STUDENTCLASSMEETINGGROUP           
                        on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                    inner join dbo.STUDENTCOURSE                      
                        on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
                        and STUDENTCOURSE.STUDENTID = SCHEDULE.STUDENTID
                    inner join dbo.CLASS                              
                        on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                    inner join (
                        select 
                             ID, 
                             CLASSMEETINGGROUPID, 
                             CYCLEDAYID,  
                             TIMETABLEDAYPERIODID, 
                             dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.STARTTIME) as STARTTIME, 
                             dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.ENDTIME) as ENDTIME,
                             DateDiff(minute, dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.STARTTIME), dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.ENDTIME)) as DIFF,
                             DateAdd(minute, -@INTERVAL, dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.STARTTIME)) as STARTTIME_MINUS_INTERVAL
                         from dbo.CLASSMEETINGTEMPLATE) CMT     
                            on CMT.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                    inner join dbo.COURSE                             
                        on CLASS.COURSEID = COURSE.ID 
                        and COURSE.PRINTONSCHEDULE = 1 
                    inner join dbo.TERM                               
                        on TERM.SESSIONID = @SESSIONID
                        and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                        and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                        and TERM.ID = SCHEDULE.TERMID
                    inner join dbo.CYCLEDAY                           
                        on CYCLEDAY.ID = CMT.CYCLEDAYID
                  and CYCLEDAY.ID = SCHEDULE.CYCLEDAYID
                    left join dbo.TIMETABLEDAYPERIOD            on TIMETABLEDAYPERIOD.ID = CMT.TIMETABLEDAYPERIODID 
                    where
                        STUDENTCLASSMEETINGGROUP.STATUSCODE = 0 and --enrolled

                        SCHEDULE.STARTTIME > CMT.STARTTIME_MINUS_INTERVAL and
                        ((SCHEDULE.ENDTIME <= CMT.ENDTIME) or 
                         ((@INTERVAL > CMT.DIFF) and (SCHEDULE.STARTTIME < CMT.ENDTIME)))
                    ) T 
                where T.ROWNUMBER = 1 
            ) I on I.ID = SCHEDULE.ID
        end

    --insert classes without meetings

    if @INCLUDENOMEETINGS = 1 
        insert into @SCHEDULE (STUDENTID, HOMEROOMTEACHERNAME, HOMEROOMTEACHER_KEYNAME, ADVISORNAME, ADVISOR_KEYNAME, GRADELEVEL, GRADELEVEL_SEQ, TERMID, CLASSID, NAME, CLASSNAME, FACULTY, ACADEMICYEAR)
            select
                STUDENTS.ID,
                STUDENTS.HOMEROOMTEACHERNAME,
                STUDENTS.HOMEROOMTEACHER_KEYNAME,
                STUDENTS.ADVISORNAME,
                STUDENTS.ADVISOR_KEYNAME,
                STUDENTS.GRADELEVEL, 
                STUDENTS.GRADELEVEL_SEQ,
                TERM.ID,
                CLASSID = CLASSMEETINGGROUP.CLASSID,
                NAME = COURSE.[COURSEID] + ' - ' + CLASS.[SECTION],
                CLASSNAME = CLASS.[NAME],
                FACULTY = dbo.UFN_CLASSMEETINGGROUP_GETFACULTYSTRING(CLASSMEETINGGROUP.[ID]),
                ACADEMICYEAR = @ACADEMICYEAR
            from @STUDENTIDS as STUDENTS
                cross join dbo.CLASSMEETINGGROUP
                inner join dbo.STUDENTCLASSMEETINGGROUP           
                    on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                inner join dbo.STUDENTCOURSE                      
                    on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
                inner join dbo.CLASS                              
                    on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                inner join dbo.COURSE                             
                    on CLASS.COURSEID = COURSE.ID
                inner join dbo.ACADEMICYEAR                       
                    on COURSE.SCHOOLID = ACADEMICYEAR.SCHOOLID
                inner join dbo.SESSION                            
                    on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID
                inner join dbo.TERM                               
                    on SESSION.ID = TERM.SESSIONID
                    and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                    and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                inner join dbo.UFN_PARSE_STRING(@TERMS,',') TERMS 
                    on TERM.ID = TERMS.ELEMENT_VALUE
            where 
                STUDENTCOURSE.STUDENTID = STUDENTS.ID and
                COURSE.PRINTONSCHEDULE = 1 and
                STUDENTCLASSMEETINGGROUP.STATUSCODE = 0 and --enrolled

                not exists (select ID from dbo.CLASSMEETINGTEMPLATE where CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID)

    --set student/term group specific column saying if grid and no meetings grid is visible

    --not visible when there are no classes with meetings   

    update @SCHEDULE
        set  
            HIDEGRID =
                case when (select count(1) 
                    from @SCHEDULE as LOOK 
                    where 
                        LOOK.STUDENTID = SCHEDULE.STUDENTID and 
                        LOOK.TERMID = SCHEDULE.TERMID and
                        not LOOK.CYCLEDAYID is null
                    ) = 0 then 1 else 0 end,
            HIDENOMEETINGSGRID =    
                case when (select count(1) 
                    from @SCHEDULE as LOOK 
                    where 
                        LOOK.STUDENTID = SCHEDULE.STUDENTID and 
                        LOOK.TERMID = SCHEDULE.TERMID and
                        LOOK.CYCLEDAYID is null
                    ) = 0 then 1 else 0 end
    from @SCHEDULE as SCHEDULE  

    select 
        'http://www.blackbaud.com?STUDENTID=' + CONVERT(nvarchar(36), STUDENTID) as STUDENTID, 
        STUDENT.NAME as STUDENTNAME, 
        STUDENT.KEYNAME as STUDENT_KEYNAME,
        GRADELEVEL,
        GRADELEVEL_SEQ,
        HOMEROOMTEACHERNAME, 
        HOMEROOMTEACHER_KEYNAME,
        ADVISORNAME, 
        ADVISOR_KEYNAME,
        TERM.ID as TERMID,
        TERMNAMECODE.DESCRIPTION as TERM, 
        TERM.STARTDATE as TERM_STARTDATE,
        ACADEMICYEAR,
        CYCLEDAYCOUNT,
        CYCLEDAY.CODE as CYCLEDAYCODE, 
        CYCLEDAY.SEQUENCE as CYCLEDAY_SEQUENCE,
        PERIOD,
        PERIOD_SEQUENCE, --start time

        'http://www.blackbaud.com?CLASSID=' + CONVERT(nvarchar(36), CLASSID) as CLASSID,
        LENGTHINPERIODS,
        PERIODCOUNT,
        STARTTIME as INTERVAL_STARTTIME,
        LENGTHIN_TIMEROWS as LENGTHIN_TIMEROWS,
        TIMEROW_COUNT as TIMEROW_COUNT,
        SCHEDULE.NAME,
        CLASSNAME, 
        case 
            when (@BREAKDOWNBY = 1 and not PERIOD is null) then
                PERIOD + ' ' +
                dbo.fnFormatDateTime(CLASS_STARTTIME,'HH:MM 12') + ' - ' + 
                dbo.fnFormatDateTime(CLASS_ENDTIME,'HH:MM 12')
            else 
                dbo.fnFormatDateTime(CLASS_STARTTIME,'HH:MM 12') + ' - ' + 
                dbo.fnFormatDateTime(CLASS_ENDTIME,'HH:MM 12') end as TIMES,
        FACULTY,
        HIDEGRID,
        HIDENOMEETINGSGRID,
        @INCLUDENOMEETINGS as INCLUDENOMEETINGS,
        @DISPLAYBY as DISPLAYBY,
        @INTERVAL as INTERVAL,
        @BREAKDOWNBY as BREAKDOWNBY 
    from @SCHEDULE as SCHEDULE
        inner join dbo.CONSTITUENT STUDENT 
            on STUDENT.ID = SCHEDULE.STUDENTID
        inner join dbo.TERM 
            on TERM.ID = SCHEDULE.TERMID
        inner join dbo.TERMNAMECODE 
            on TERMNAMECODE.ID = TERM.TERMNAMECODEID
        left join dbo.CYCLEDAY 
            on CYCLEDAY.ID = SCHEDULE.CYCLEDAYID
end