USP_REPORT_STUDENTSCHEDULELIST

create procedure dbo.USP_REPORT_STUDENTSCHEDULELIST(
    @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
)    
as

    declare @TERM_STARTDATE date
    declare @TERM_ENDDATE date
    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date

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

    declare @SCHEDULETIMECODE as bit
    select @SCHEDULETIMECODE = SCHEDULETIMECODE 
    from 
        dbo.TIMETABLE 
    inner join dbo.SESSION
        on SESSION.TIMETABLEID = TIMETABLE.ID
    where SESSION.ID = @SESSIONID

    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)

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

    if @RECORDQUERYID is null
        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
                left join SCHOOLGRADELEVEL 
                    on SCHOOLGRADELEVEL.ID = STUDENTPROGRESSION.SCHOOLGRADELEVELID 
                left join GRADELEVEL 
                    on GRADELEVEL.ID = SCHOOLGRADELEVEL.GRADELEVELID
            where STUDENTPROGRESSION.STARTDATE <= @SESSION_STARTDATE
                and STUDENTPROGRESSION.ENDDATE >= @SESSION_ENDDATE    
                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
                        STUDENTCLASSMEETINGGROUP.STATUSCODE = 0 and --enrolled

                        not (CLASSMEETINGGROUP.ENDDATE < @SESSION_STARTDATE or 
                        CLASSMEETINGGROUP.STARTDATE > @SESSION_ENDDATE)
                    )
    else begin
        if not exists(select ID from dbo.IDSETREGISTER where ID = @RECORDQUERYID) 
            raiserror('Selection ID set does not exist in the database.', 15, 1)

        -- filter by selection and session dates

        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
                left join SCHOOLGRADELEVEL 
                    on SCHOOLGRADELEVEL.ID = STUDENTPROGRESSION.SCHOOLGRADELEVELID 
                left join GRADELEVEL 
                    on GRADELEVEL.ID = SCHOOLGRADELEVEL.GRADELEVELID
                inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@RECORDQUERYID) SELECTION 
                    on SELECTION.ID = STUDENT.ID
            where STUDENTPROGRESSION.STARTDATE <= @SESSION_STARTDATE
                and STUDENTPROGRESSION.ENDDATE >= @SESSION_ENDDATE    
                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
                        STUDENTCLASSMEETINGGROUP.STATUSCODE = 0 and --enrolled

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

    select
        'http://www.blackbaud.com?STUDENTID=' + CONVERT(nvarchar(36), STUDENT.ID) as STUDENTID,
        STUDENT.NAME as STUDENTNAME,
        STUDENT.KEYNAME as STUDENT_KEYNAME,
        STUDENTS.HOMEROOMTEACHERNAME,
        STUDENTS.HOMEROOMTEACHER_KEYNAME,
        STUDENTS.ADVISORNAME,
        STUDENTS.ADVISOR_KEYNAME,
        STUDENTS.GRADELEVEL, 
        STUDENTS.GRADELEVEL_SEQ,
        CLASSMEETINGGROUP.ID,
        'http://www.blackbaud.com?CLASSID=' + CONVERT(nvarchar(36), CLASSMEETINGGROUP.CLASSID) as CLASSID,
        COURSE.[COURSEID] + ' - ' + CLASS.[SECTION] as NAME,
        CLASS.[NAME] as CLASSNAME,
        dbo.UFN_TERMNAMECODE_GETDESCRIPTION(TERM.TERMNAMECODEID) as TERM,
        TERM.STARTDATE as TERM_STARTDATE,
        dbo.UFN_ACADEMICYEAR_GETNAME(ACADEMICYEAR.ID) as ACADEMICYEAR,
        dbo.UFN_CLASSMEETINGGROUP_GETFACULTYSTRING(CLASSMEETINGGROUP.[ID]) as FACULTY,
        dbo.UFN_SCHEDULELISTREPORT_GETFILTEREDTIMELIST(CLASSMEETINGGROUP.[ID], @PERIODS, @CYCLEDAYS) as MEETINGS,
        @DISPLAYBY as DISPLAYBY
    from dbo.CLASSMEETINGGROUP
        inner join dbo.STUDENTCLASSMEETINGGROUP           
            on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
        inner join dbo.STUDENTCOURSE                      
            on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
        inner join dbo.CONSTITUENT STUDENT                
            on STUDENT.ID = STUDENTCOURSE.STUDENTID
        inner join @STUDENTIDS STUDENTS                   
            on STUDENTS.ID = STUDENT.ID
        inner join dbo.CLASS                              
            on CLASSMEETINGGROUP.CLASSID = CLASS.ID
        inner join dbo.COURSE                             
            on dbo.CLASS.COURSEID = dbo.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 TERMS.ELEMENT_VALUE = TERM.ID                                         
    where 
        not (CLASSMEETINGGROUP.ENDDATE < @SESSION_STARTDATE or CLASSMEETINGGROUP.STARTDATE > @SESSION_ENDDATE) and
        COURSE.PRINTONSCHEDULE = 1 and
        STUDENTCLASSMEETINGGROUP.STATUSCODE = 0 and --enrolled

        (@INCLUDENOMEETINGS = 1 or 
        (@INCLUDENOMEETINGS = 0 and 
            (
             (@SCHEDULETIMECODE = 0 and --filter by cycle day and period when timetable uses periods

                exists (select CLASSMEETINGTEMPLATE.ID 
                        from dbo.CLASSMEETINGTEMPLATE 
                            inner join dbo.CYCLEDAY 
                                on CLASSMEETINGTEMPLATE.CYCLEDAYID = CYCLEDAY.ID
                            inner join dbo.UFN_PARSE_STRING(@CYCLEDAYS,',') CYCLEDAYS 
                                on CYCLEDAYS.ELEMENT_VALUE = CYCLEDAY.ID 
                            left join dbo.TIMETABLEDAYPERIOD [STARTPERIOD] 
                                on CLASSMEETINGTEMPLATE.TIMETABLEDAYPERIODID = [STARTPERIOD].ID
                            inner join dbo.UFN_PARSE_STRING(@PERIODS,',') PERIODS_START 
                                on PERIODS_START.ELEMENT_VALUE = [STARTPERIOD].PERIOD
                            left join dbo.TIMETABLEDAYPERIOD [ENDPERIOD]
                                on [STARTPERIOD].TIMETABLEDAYID = [ENDPERIOD].TIMETABLEDAYID
                                and CLASSMEETINGTEMPLATE.ENDTIME = [ENDPERIOD].ENDTIME
                            inner join dbo.UFN_PARSE_STRING(@PERIODS,',') PERIODS_END on PERIODS_END.ELEMENT_VALUE = [ENDPERIOD].PERIOD
                        where CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID))
             or
             (@SCHEDULETIMECODE = 1 and --filter by cycle day only...not period when timetable uses time

                exists (select CLASSMEETINGTEMPLATE.ID 
                        from dbo.CLASSMEETINGTEMPLATE 
                            inner join dbo.CYCLEDAY 
                                on CLASSMEETINGTEMPLATE.CYCLEDAYID = CYCLEDAY.ID
                            inner join dbo.UFN_PARSE_STRING(@CYCLEDAYS,',') CYCLEDAYS 
                                on CYCLEDAYS.ELEMENT_VALUE = CYCLEDAY.ID 
                        where CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID))
            ))
        )