USP_DATAFORMTEMPLATE_VIEW_FACULTY_CURRENTTERM

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FACULTY_CURRENTTERM
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ACADEMICYEARID uniqueidentifier = null output,
    @SESSIONID uniqueidentifier = null output,
    @TERMID uniqueidentifier = null output
)
as
    set nocount on;

    set @DATALOADED = 1;

    -- WARNING: The Academic Year, Session, and Term do not necessarily reflect the actual ID - but are instead representative


    declare @CURRENTDATE date = getdate()

    -- Track the number of courses a teacher is teaching currently per school to break ties

    declare @SCHOOLFACULTY table
    (
        ID uniqueidentifier,
        [COUNT] int
    )
    insert into @SCHOOLFACULTY
    select
        COURSE.SCHOOLID,
        count(FACULTYCLASSMEETINGGROUP.ID)
    from dbo.FACULTYCOURSE
        inner join dbo.FACULTYCLASSMEETINGGROUP on FACULTYCOURSE.ID = FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID    
        inner join dbo.CLASSMEETINGGROUP on FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID    
        inner join dbo.COURSE on FACULTYCOURSE.COURSEID = COURSE.ID
    where @CURRENTDATE >= CLASSMEETINGGROUP.STARTDATE
        and @CURRENTDATE <= CLASSMEETINGGROUP.ENDDATE
    group by COURSE.SCHOOLID

    declare @ACADEMICYEARS table
    (
        ID uniqueidentifier,
        LABEL nvarchar(200)
    )
    insert into @ACADEMICYEARS    
    exec dbo.USP_SIMPLEDATALIST_ACADEMICYEAR_GROUPEDBYTIME

    select top(1)
        @ACADEMICYEARID = AY.ID
    from @ACADEMICYEARS AY
        inner join dbo.ACADEMICYEAR on AY.ID = ACADEMICYEAR.ID
        left join @SCHOOLFACULTY SCHOOLS on ACADEMICYEAR.SCHOOLID = SCHOOLS.ID
    where @CURRENTDATE >= ACADEMICYEAR.STARTDATE
        and @CURRENTDATE <= ACADEMICYEAR.ENDDATE
    order by SCHOOLS.COUNT desc    

    if @ACADEMICYEARID is not null
    begin
        declare @SESSIONS table
        (
            ID uniqueidentifier,
            LABEL nvarchar(200)
        )
        insert into @SESSIONS    
        exec dbo.USP_SIMPLEDATALIST_SESSION_GROUPEDBYTIME @ACADEMICYEARID

        select top(1)
            @SESSIONID = S.ID
        from @SESSIONS S
            inner join dbo.SESSION on S.ID = SESSION.ID
            inner join dbo.TERM on SESSION.ID = TERM.SESSIONID
            inner join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
            left join @SCHOOLFACULTY SCHOOLS on ACADEMICYEAR.SCHOOLID = SCHOOLS.ID
        group by S.ID, SCHOOLS.[COUNT]
        having @CURRENTDATE >= min(TERM.STARTDATE)
            and @CURRENTDATE <= max(TERM.ENDDATE)
        order by SCHOOLS.[COUNT] desc

        if @SESSIONID is not null
        begin
            declare @TERMS table
            (
                ID uniqueidentifier,
                LABEL nvarchar(200)
            )
            insert into @TERMS    
            exec dbo.USP_SIMPLEDATALIST_TERM_GROUPEDBYTIME @ACADEMICYEARID, @SESSIONID

            select top(1)
                @TERMID = T.ID
            from @TERMS T
                inner join dbo.TERM on T.ID = TERM.ID                
                inner join dbo.SESSION on TERM.SESSIONID = SESSION.ID
                inner join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
                left join @SCHOOLFACULTY SCHOOLS on ACADEMICYEAR.SCHOOLID = SCHOOLS.ID
            where @CURRENTDATE >= TERM.STARTDATE
                and @CURRENTDATE <= TERM.ENDDATE
            order by SCHOOLS.[COUNT] desc
        end
    end

    return 0;