USP_DATALIST_STUDENT_CLASSCALENDAR

CREATE procedure dbo.USP_DATALIST_STUDENT_CLASSCALENDAR
(
    @STUDENTID 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,
            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.STUDENTCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
            inner join dbo.STUDENTCOURSE on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
        where STUDENTCOURSE.STUDENTID = @STUDENTID
            and COURSE.SCHOOLID = @SCHOOLID
      and STUDENTCLASSMEETINGGROUP.STATUSCODE = 0
    end
    else if @SESSIONID is not null and @SESSIONID != '00000000-0000-0000-0000-000000000000'
    begin
        with CLASSMAP_CTE (CLASSID, CLASSNAME, TERMNAME, STARTDATE, ROW)
        as
        (
            select
                CLASS.ID,
                COURSE.[COURSEID] + ' - ' + CLASS.[SECTION] + ' - ' as NAME,
                TERMNAMECODE.DESCRIPTION as TERMNAME,
                CLASSMEETINGGROUP.STARTDATE,
                (ROW_NUMBER() over (partition by CLASS.ID, CLASS.SECTION order by CLASSMEETINGGROUP.STARTDATE)) as ROW
            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 CLASSMEETINGGROUP.STARTDATE = TERM.STARTDATE
                    and CLASSMEETINGGROUP.ENDDATE = TERM.ENDDATE
                inner join dbo.TERMNAMECODE on TERM.TERMNAMECODEID = TERMNAMECODE.ID
                inner join dbo.STUDENTCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                inner join dbo.STUDENTCOURSE on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
            where STUDENTCOURSE.STUDENTID = @STUDENTID
                and COURSE.SCHOOLID = @SCHOOLID
        and STUDENTCLASSMEETINGGROUP.STATUSCODE = 0
        )
        insert into @CLASSMAP
        select
            T.ID,            
            T.CLASSNAME +
                case when T.CLASSRANK > 1
                    then (select stuff(
                            (select ', ' + LOCAL_CTE.TERMNAME
                            from CLASSMAP_CTE LOCAL_CTE
                            where LOCAL_CTE.CLASSID = T.ID
                            order by LOCAL_CTE.STARTDATE
                            for xml path('')),
                        1, 2, ''))
                    else T.TERMNAME
                end as NAME,
            T.COLORSTYLECODE
        from
            (
                select
                    CLASSMAP_CTE.CLASSID as ID,
                    CLASSMAP_CTE.CLASSNAME,
                    CLASSMAP_CTE.TERMNAME,
                    rank() over (order by CLASSMAP_CTE.CLASSID) as CLASSRANK,
                    (ROW_NUMBER() over (order by CLASSMAP_CTE.CLASSNAME) -1) % 12 as COLORSTYLECODE,
                    CLASSMAP_CTE.ROW
                from
                    CLASSMAP_CTE
            ) T
        where T.ROW = 1
    end

    select
        T.ID,
        CLASS.ID as CLASSID,
        T.CYCLEDAYID,
        @CURRENTDATE as STARTDATE,
        CLASS.NAME as NAME,
        coalesce(TIMETABLEDAYPERIOD.PERIOD, '') + coalesce(' (' + PATTERNBLOCK.NAME + ')', '')
            + coalesce(char(10) + T.FACULTY, '') 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 CLASSMEETINGGROUP.STARTDATE 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