UFN_COURSEGRADING_GETMARKINGCOLUMNS

CREATE function dbo.UFN_COURSEGRADING_GETMARKINGCOLUMNS
(
    @COURSEGRADINGID uniqueidentifier = null,
    @COURSEID uniqueidentifier,
    @SESSIONSTARTDATE date,
    @SESSIONENDDATE date   
)
returns @TEMPMARKINGCOLUMNS table
    (
        ID uniqueidentifier,
        MARKINGCOLUMNID uniqueidentifier,
        DISPLAYNAME varchar(10),
        SEQUENCE int,
        GRADED bit,
        TRANSLATIONTABLEID uniqueidentifier,
        VALUESALLOWEDCODE tinyint,
        HASGRADE bit,
        HASINVALIDTT bit,
        VALIDVALUESALLOWED tinyint
    )
as
begin
        declare @SESSIONID uniqueidentifier

        --grab sessionID for easier lookup

        select @SESSIONID = dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, @SESSIONSTARTDATE, @SESSIONENDDATE)
        from dbo.COURSE
        where COURSE.ID = @COURSEID

        if @COURSEGRADINGID is null 
        begin
            insert into @TEMPMARKINGCOLUMNS
                (ID, MARKINGCOLUMNID, DISPLAYNAME, SEQUENCE, GRADED, TRANSLATIONTABLEID, VALUESALLOWEDCODE, HASGRADE, HASINVALIDTT, VALIDVALUESALLOWED)   
            select 
                null,
                MARKINGCOLUMN.ID,
                MARKINGCOLUMN.DISPLAYNAME,
                MARKINGCOLUMN.SEQUENCE,
                0,
                null,
                0,
                0,
                0,
                0
            from dbo.MARKINGCOLUMN 
            join dbo.MARKINGCOLUMNDESCRIPTIONCODE on MARKINGCOLUMN.MARKINGCOLUMNDESCRIPTIONCODEID = MARKINGCOLUMNDESCRIPTIONCODE.ID
            join dbo.SESSION on MARKINGCOLUMN.MARKINGCOLUMNSETID = SESSION.MARKINGCOLUMNSETID
            where 
                SESSION.ID = @SESSIONID and
                (((select count(*) from dbo.UFN_COURSERESTRICTION_VALIDDATESBYLENGTHINTERMS(@SESSIONSTARTDATE, @SESSIONENDDATE, @COURSEID)) = 0) or                  
                    (MARKINGCOLUMN.ALLTERMS = 1) or 
                    (exists 
                        (select * 
                        from dbo.MARKINGCOLUMNTERM
                        join dbo.TERM on MARKINGCOLUMNTERM.TERMNAMECODEID = TERM.TERMNAMECODEID                 
                        join dbo.UFN_COURSERESTRICTION_VALIDDATESBYLENGTHINTERMS(@SESSIONSTARTDATE, @SESSIONENDDATE, @COURSEID) CRT on TERM.STARTDATE between CRT.STARTDATE and CRT.ENDDATE
                        where 
                            MARKINGCOLUMNID = MARKINGCOLUMN.ID and 
                            TERM.SESSIONID = @SESSIONID )))                          
        end
        else
        begin                
            insert into @TEMPMARKINGCOLUMNS
                (ID, MARKINGCOLUMNID, DISPLAYNAME, SEQUENCE, GRADED, TRANSLATIONTABLEID, VALUESALLOWEDCODE, HASGRADE, HASINVALIDTT, VALIDVALUESALLOWED)   
            select 
                COURSEGRADINGMARKINGCOLUMN.ID,
                coalesce(COURSEGRADINGMARKINGCOLUMN.MARKINGCOLUMNID, MARKINGCOLUMN.ID) 'MARKINGCOLUMNID',
                MARKINGCOLUMN.DISPLAYNAME,
                MARKINGCOLUMN.SEQUENCE,
                COURSEGRADINGMARKINGCOLUMN.GRADED 'GRADED',
                COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID,
                coalesce(COURSEGRADINGMARKINGCOLUMN.VALUESALLOWEDCODE, 0) 'VALUESALLOWEDCODE',
                case when exists (select * from dbo.STUDENTMARKINGCOLUMNGRADE 
                                    where (STUDENTMARKINGCOLUMNGRADE.COURSEGRADINGMARKINGCOLUMNID = COURSEGRADINGMARKINGCOLUMN.ID) and 
                                    STUDENTMARKINGCOLUMNGRADE.GRADEISBLANK = 0) then 1 else 0 end,
                case when exists (select * from dbo.UFN_COURSEGRADINGMARKINGCOLUMN_INVALIDTRANSLATIONTABLES(COURSEGRADINGMARKINGCOLUMN.ID, COURSEGRADINGMARKINGCOLUMN.VALUESALLOWEDCODE)) then 1 else 0 end,
                coalesce(dbo.UFN_COURSEGRADINGMARKINGCOLUMN_VALIDVALUESALLOWED(COURSEGRADINGMARKINGCOLUMN.ID), 0) 'VALIDVALUESALLOWED'
            from dbo.COURSEGRADINGMARKINGCOLUMN
            join dbo.COURSEGRADING on COURSEGRADINGMARKINGCOLUMN.COURSEGRADINGID = COURSEGRADING.ID
            join dbo.COURSE on COURSEGRADING.COURSEID = COURSE.ID   
            --don't join on the CourseGradingMarkingColumn associated with the wrong CourseGradingID

            right join dbo.MARKINGCOLUMN on COURSEGRADINGMARKINGCOLUMN.MARKINGCOLUMNID = MARKINGCOLUMN.ID and (COURSEGRADINGID = @COURSEGRADINGID)                      
            --need to join on the session on COURSEGRADING in the case where marking column set on the session gets changed.

            right join dbo.SESSION on (MARKINGCOLUMN.MARKINGCOLUMNSETID = SESSION.MARKINGCOLUMNSETID) or (SESSION.ID = dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSEGRADING.STARTDATE, COURSEGRADING.ENDDATE))
            right join dbo.MARKINGCOLUMNDESCRIPTIONCODE on MARKINGCOLUMN.MARKINGCOLUMNDESCRIPTIONCODEID = MARKINGCOLUMNDESCRIPTIONCODE.ID
            where 
                (SESSION.ID = @SESSIONID) and        
                ((COURSEGRADINGID = @COURSEGRADINGID) or 
                    -- add marking columns that are now valid after some changes in marking column set or course restriction

                    -- delete is handled in UIModel in order for them to be deleted from the db

                    ((COURSEGRADINGID is null) and 
                        (((select count(*) from dbo.UFN_COURSERESTRICTION_VALIDDATESBYLENGTHINTERMS(@SESSIONSTARTDATE, @SESSIONENDDATE, @COURSEID)) = 0) or         
                            (MARKINGCOLUMN.ALLTERMS = 1) or 
                            (exists 
                                (select * 
                                from dbo.MARKINGCOLUMNTERM
                                join dbo.TERM on MARKINGCOLUMNTERM.TERMNAMECODEID = TERM.TERMNAMECODEID                 
                                join dbo.UFN_COURSERESTRICTION_VALIDDATESBYLENGTHINTERMS(@SESSIONSTARTDATE, @SESSIONENDDATE, @COURSEID) CRT on TERM.STARTDATE between CRT.STARTDATE and CRT.ENDDATE
                                where 
                                    MARKINGCOLUMNID = MARKINGCOLUMN.ID and 
                                    TERM.SESSIONID = @SESSIONID )))))                
        end        
    return
end