USP_REPORT_FACULTYSCHEDULELIST

create procedure dbo.USP_REPORT_FACULTYSCHEDULELIST(
    @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 @FACULTYIDS table (ID uniqueidentifier)

    -- single teacher, or all teachers; filter by session dates

    if @RECORDQUERYID is null
        insert into @FACULTYIDS 
            select 
                FACULTY.ID
            from dbo.FACULTY
            where (@RECORDID_SINGLE is null or FACULTY.ID = @RECORDID_SINGLE)
                and exists 
                    (select 
                        FACULTYCOURSE.FACULTYID 
                    from dbo.FACULTYCOURSE 
                    join dbo.FACULTYCLASSMEETINGGROUP 
                        on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
                    join dbo.CLASSMEETINGGROUP 
                        on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                    join dbo.COURSE 
                        on COURSE.ID = FACULTYCOURSE.COURSEID
                    where 
                        FACULTYCOURSE.FACULTYID = FACULTY.ID and 
                        COURSE.SCHOOLID = @SCHOOLID and
                        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 @FACULTYIDS 
            select 
                FACULTY.ID
            from FACULTY
                inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@RECORDQUERYID) SELECTION 
                    on SELECTION.ID = FACULTY.ID
            where (@RECORDID_SINGLE is null or FACULTY.ID = @RECORDID_SINGLE)
                and exists 
                    (select 
                        FACULTYCOURSE.FACULTYID 
                    from dbo.FACULTYCOURSE 
                        inner join dbo.FACULTYCLASSMEETINGGROUP 
                            on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
                        inner join dbo.CLASSMEETINGGROUP 
                            on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                        inner join dbo.COURSE 
                            on COURSE.ID = FACULTYCOURSE.COURSEID
                    where 
                        FACULTYCOURSE.FACULTYID = FACULTY.ID and 
                        COURSE.SCHOOLID = @SCHOOLID and
                        not (CLASSMEETINGGROUP.ENDDATE < @SESSION_STARTDATE or 
                        CLASSMEETINGGROUP.STARTDATE > @SESSION_ENDDATE)
                    )
    end

    select
        'http://www.blackbaud.com?FACULTYID=' + CONVERT(nvarchar(36), FACULTY.ID) as FACULTYID,
        FACULTY.NAME as FACULTYNAME,
        FACULTY.KEYNAME as FACULTY_KEYNAME,
        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_SCHEDULELISTREPORT_GETFILTEREDTIMELIST(CLASSMEETINGGROUP.[ID], @PERIODS, @CYCLEDAYS) as MEETINGS,
        @DISPLAYBY as DISPLAYBY
    from dbo.CLASSMEETINGGROUP
        inner join dbo.FACULTYCLASSMEETINGGROUP           
            on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
        inner join dbo.FACULTYCOURSE                      
            on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
        inner join dbo.CONSTITUENT FACULTY                
            on FACULTY.ID = FACULTYCOURSE.FACULTYID
        inner join @FACULTYIDS FACULTYS                   
            on FACULTYS.ID = FACULTY.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
        (@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))
            ))
        )