USP_DATALIST_FACULTY_CLASSCALENDAR

CREATE procedure dbo.USP_DATALIST_FACULTY_CLASSCALENDAR
(
    @FACULTYID uniqueidentifier,
    @CONTEXTRECORDID uniqueidentifier = null,
    @ACADEMICYEARID uniqueidentifier = null,
    @SESSIONID uniqueidentifier = null,
    @TERMID uniqueidentifier = null
)
as
    set nocount on;

    declare @CURRENTDATE date = getdate()

    declare @CLASSMAP table
    (
        ID uniqueidentifier,
        NAME nvarchar(200),
        COLORSTYLECODE int
    )

    declare @SCHOOLID uniqueidentifier = dbo.UFN_ACADEMICYEAR_GETSCHOOL_GROUPBYTIMEFILTER(@ACADEMICYEARID)
    if @SCHOOLID is null
        select @SCHOOLID = dbo.UFN_SESSION_GETSCHOOL_GROUPBYTIMEFILTER(@ACADEMICYEARID, @SESSIONID)

    if @TERMID is not null and @TERMID != '00000000-0000-0000-0000-000000000000'
    begin    
        if @SCHOOLID is null
            select @SCHOOLID = dbo.UFN_TERM_GETSCHOOL_GROUPBYTIMEFILTER(@SESSIONID, @TERMID)

        insert into @CLASSMAP
        select
            CLASS.ID,
            COURSE.[COURSEID] + ' - ' + CLASS.[SECTION] as NAME,
            (ROW_NUMBER() over (order by CLASS.STARTDATE, CLASS.SECTION) -1) % 12
        from dbo.CLASS
            inner join dbo.COURSE on CLASS.COURSEID = COURSE.ID
            inner join dbo.CLASSMEETINGGROUP on CLASS.ID = CLASSMEETINGGROUP.CLASSID
            inner join dbo.TERM on TERM.ID = @TERMID
                and CLASSMEETINGGROUP.STARTDATE >= TERM.STARTDATE
                and CLASSMEETINGGROUP.ENDDATE <= TERM.ENDDATE
            inner join dbo.FACULTYCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
            inner join dbo.FACULTYCOURSE on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
        where FACULTYCOURSE.FACULTYID = @FACULTYID
            and (@SCHOOLID is null or @SCHOOLID = COURSE.SCHOOLID)
    end
    else if @SESSIONID is not null and @SESSIONID != '00000000-0000-0000-0000-000000000000'
    begin
        insert into @CLASSMAP
        select
            CLASS.ID,
            COURSE.[COURSEID] + ' - ' + CLASS.[SECTION] + ' - ' + TERMNAMECODE.DESCRIPTION as NAME,
            (ROW_NUMBER() over (order by CLASS.STARTDATE, CLASS.SECTION) -1) % 12
        from dbo.CLASS
            inner join dbo.COURSE on CLASS.COURSEID = COURSE.ID
            inner join dbo.CLASSMEETINGGROUP on CLASS.ID = CLASSMEETINGGROUP.CLASSID
            inner join dbo.UFN_SESSION_GETDATES(@SESSIONID) as SESSION on
                CLASS.STARTDATE >= SESSION.STARTDATE and
                CLASS.ENDDATE <= SESSION.ENDDATE
            inner join dbo.TERM on @SESSIONID = TERM.SESSIONID
                and CLASS.STARTDATE = TERM.STARTDATE
            inner join dbo.TERMNAMECODE on TERM.TERMNAMECODEID = TERMNAMECODE.ID
            inner join dbo.FACULTYCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
            inner join dbo.FACULTYCOURSE on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
        where FACULTYCOURSE.FACULTYID = @FACULTYID
            and (@SCHOOLID is null or @SCHOOLID = COURSE.SCHOOLID)
        group by CLASS.ID, COURSE.COURSEID, CLASS.SECTION, CLASS.STARTDATE, CLASS.SECTION, TERMNAMECODE.DESCRIPTION
    end

    select
        T.ID,
        CLASS.ID as CLASSID,
        T.CYCLEDAYID,
        @CURRENTDATE as STARTDATE,
        CLASS.NAME,
        coalesce(TIMETABLEDAYPERIOD.PERIOD, '') + coalesce(' (' + PATTERNBLOCK.NAME + ')', '')
            + char(10) + '(' + cast(dbo.UFN_CLASS_GETNUMBERENROLLED(CLASS.ID) as nvarchar) + ')' as DESCRIPTION,
        T.STARTTIME,
        T.ENDTIME,
        CLASS.COLORSTYLECODE
    from (
        select
            CLASSMEETINGTEMPLATE.ID,
            CLASSMEETINGTEMPLATE.CYCLEDAYID,
            CLASSMEETINGTEMPLATE.STARTTIME,
            CLASSMEETINGTEMPLATE.ENDTIME,
            CLASSMEETINGTEMPLATE.TIMETABLEDAYPERIODID,
            CLASSMEETINGGROUP.CLASSID,
            CLASSMEETINGGROUP.PATTERNBLOCKID,
            ROW_NUMBER() OVER (PARTITION BY
                CLASSMEETINGTEMPLATE.CYCLEDAYID,
                CLASSMEETINGTEMPLATE.STARTTIME,
                CLASSMEETINGTEMPLATE.ENDTIME,
                CLASSMEETINGTEMPLATE.TIMETABLEDAYPERIODID,
                CLASSMEETINGGROUP.CLASSID
                ORDER BY CLASSMEETINGTEMPLATE.DATEADDED DESC
            ) ROW
        from dbo.CLASSMEETINGTEMPLATE
            inner join dbo.CLASSMEETINGGROUP on CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
    ) T
        inner join @CLASSMAP CLASS on T.CLASSID = CLASS.ID
        inner join dbo.CYCLEDAY on T.CYCLEDAYID = CYCLEDAY.ID
        left join dbo.TIMETABLEDAYPERIOD on T.TIMETABLEDAYPERIODID = TIMETABLEDAYPERIOD.ID
        left join dbo.PATTERNBLOCK on T.PATTERNBLOCKID = PATTERNBLOCK.ID
    where T.ROW = 1
    order by CYCLEDAY.SEQUENCE, T.STARTTIME