USP_DATALIST_COURSE_CLASSCALENDAR

CREATE procedure dbo.USP_DATALIST_COURSE_CLASSCALENDAR
(
    @COURSEID uniqueidentifier,
    @CONTEXTRECORDID uniqueidentifier = null,
    @SCHOOLID 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
    )

    if @TERMID is not null and @TERMID != '00000000-0000-0000-0000-000000000000'
    begin        
        insert into @CLASSMAP
        select
            CLASS.ID,
            CLASS.SECTION,
            (ROW_NUMBER() over (order by CLASS.STARTDATE, CLASS.SECTION) -1) % 12
        from dbo.CLASS
            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
        where CLASS.COURSEID = @COURSEID            
    end
    else if @SESSIONID is not null and @SESSIONID != '00000000-0000-0000-0000-000000000000'
    begin
        insert into @CLASSMAP
        select
            CLASS.ID,
            CLASS.SECTION + ' - ' + TERMNAMECODE.DESCRIPTION as NAME,
            (ROW_NUMBER() over (order by CLASS.STARTDATE, CLASS.SECTION) -1) % 12
        from dbo.CLASS
            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
        where CLASS.COURSEID = @COURSEID
    end

    select
        T.ID,
        CLASS.ID as CLASSID,
        T.CYCLEDAYID,
        @CURRENTDATE as STARTDATE,
        CLASS.NAME,
        coalesce(TIMETABLEDAYPERIOD.PERIOD, '') + coalesce(' (' + PATTERNBLOCK.NAME + ')', '')
            + coalesce(char(10) + T.FACULTY, '')
            + 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,
            CONSTITUENT.KEYNAME + ', ' + CONSTITUENT.FIRSTNAME as FACULTY,
            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
            left join dbo.FACULTYCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
            left join dbo.FACULTYCOURSE on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
            left join dbo.CONSTITUENT on FACULTYCOURSE.FACULTYID = CONSTITUENT.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