USP_DATALIST_MYCLASSES

CREATE procedure dbo.USP_DATALIST_MYCLASSES
(
    @CURRENTAPPUSERID uniqueidentifier
)
as

    set nocount on;

    declare @FACULTYID uniqueidentifier;
    declare @ACADEMICYEARNAMECODEID uniqueidentifier;
    declare @SESSIONNAMECODEID uniqueidentifier;
    declare @TODAY datetime = floor(cast(getdate() as float));

    declare @CLASSES table
    (
        ID uniqueidentifier not null, 
        NAME nvarchar(200) not null, 
        CLASSMEETINGID uniqueidentifier null, 
        STARTTIME datetime null, 
        PERIOD nvarchar(20) null, 
        SHOWSETUP bit not null,
        SHOWGRADEBOOK bit not null
    )

    select 
        @FACULTYID = CONSTITUENTID
    from 
        dbo.APPUSER
    where 
        ID = @CURRENTAPPUSERID

    select 
        @SESSIONNAMECODEID=SESSIONNAMECODEID, 
        @ACADEMICYEARNAMECODEID=ACADEMICYEARNAMECODEID 
    from 
        dbo.SESSION
        inner join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
        inner join dbo.GRADINGAPPUSERSESSION on SESSION.ID = GRADINGAPPUSERSESSION.SESSIONID
    where 
        GRADINGAPPUSERSESSION.ID = @CURRENTAPPUSERID

    insert into @CLASSES
    (
        ID, 
        NAME,
        SHOWSETUP,
        SHOWGRADEBOOK
    )
    select 
        DISTINCT CLASSID, 
        NAME,
        1,
        1
    from 
        dbo.UFN_FACULTY_GETCLASSMEETINGGROUPS
        (
            @FACULTYID,
            @ACADEMICYEARNAMECODEID,
            @SESSIONNAMECODEID,
            null
        ) T1

    update CL set 
        CLASSMEETINGID = 
        (
            select 
                TOP 1 CLASSMEETING.ID
            from 
                dbo.FACULTYCLASSMEETINGGROUP
                inner join dbo.FACULTYCOURSE on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
                inner join dbo.CLASSMEETINGGROUP on FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                inner join dbo.CLASS on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                inner join dbo.CLASSMEETING on CLASSMEETINGGROUP.ID = CLASSMEETING.CLASSMEETINGGROUPID
                inner join dbo.EDUCATIONMEETING on CLASSMEETING.EDUCATIONMEETINGID = EDUCATIONMEETING.ID
            where 
                FACULTYID = @FACULTYID 
                and CLASS.ID = CL.ID
                and dateadd(mi, cast(substring(EDUCATIONMEETING.STARTTIME, 3, 2) as int), dateadd(hh, cast(substring(EDUCATIONMEETING.STARTTIME, 1, 2) as int), convert(datetime, EDUCATIONMEETING.STARTDATE))) >= GETDATE()
            order by 
                EDUCATIONMEETING.STARTDATE, EDUCATIONMEETING.STARTTIME
        )
    from 
        @CLASSES CL

    update CL set 
        STARTTIME = dbo.UFN_DATE_ADDHOURMINUTE(EDUCATIONMEETING.STARTDATE, EDUCATIONMEETING.STARTTIME),
        PERIOD = CASE WHEN EDUCATIONMEETING.STARTDATE = @TODAY THEN '' ELSE CYCLEDAY.DAY + ' ' END + dbo.UFN_CLASSMEETINGTEMPLATE_GETPERIODSTRING(CLASSMEETINGTEMPLATE.ID, CLASSMEETINGTEMPLATE.LENGTHINPERIODS)
    from 
        @CLASSES CL
        inner join dbo.CLASSMEETING on CL.CLASSMEETINGID = CLASSMEETING.ID
        inner join dbo.EDUCATIONMEETING on CLASSMEETING.EDUCATIONMEETINGID = EDUCATIONMEETING.ID
        inner join dbo.CLASS on CL.ID = CLASS.ID
        inner join dbo.COURSE on CLASS.COURSEID = COURSE.ID
        inner join dbo.SCHEDULEDATE on COURSE.SCHOOLID = SCHEDULEDATE.SCHOOLID
            and EDUCATIONMEETING.STARTDATE = SCHEDULEDATE.DATE
        inner join dbo.TIMETABLEDAY on SCHEDULEDATE.TIMETABLEDAYID = TIMETABLEDAY.ID
        inner join dbo.CYCLEDAY on TIMETABLEDAY.CYCLEDAYID = CYCLEDAY.ID
        inner join dbo.CLASSMEETINGTEMPLATE on CLASSMEETING.CLASSMEETINGGROUPID = CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID
            and TIMETABLEDAY.CYCLEDAYID = CLASSMEETINGTEMPLATE.CYCLEDAYID
            and EDUCATIONMEETING.STARTTIME = CLASSMEETINGTEMPLATE.STARTTIME
            and EDUCATIONMEETING.ENDTIME = CLASSMEETINGTEMPLATE.ENDTIME

    update CL set
        SHOWSETUP = 0,
        SHOWGRADEBOOK = 0
    from
        @CLASSES CL
        inner join dbo.CLASS on CL.ID = CLASS.ID
    where 
        not exists(select COURSEGRADINGMARKINGCOLUMN.ID from dbo.COURSEGRADINGMARKINGCOLUMN
                        inner join dbo.COURSEGRADING on COURSEGRADINGMARKINGCOLUMN.COURSEGRADINGID = COURSEGRADING.ID
                        where COURSEGRADING.STARTDATE <= CLASS.STARTDATE and COURSEGRADING.ENDDATE >= CLASS.ENDDATE and COURSEGRADING.COURSEID = CLASS.COURSEID)

    update CL set
        SHOWSETUP = 0
    from 
        @CLASSES CL
    where
        exists(select ID from dbo.CLASSCATEGORY where CLASSID = CL.ID)

    select TOP 8
        ID,
        NAME,
        PERIOD,
        CASE SHOWGRADEBOOK WHEN 1 THEN 'Gradebook' ELSE '' END "GRADEBOOK",
        CASE SHOWSETUP WHEN 1 THEN 'Setup' ELSE '' END "SETUP"
    from 
        @CLASSES
    order by 
        ISNULL(STARTTIME, '99991231'), NAME