USP_DATAFORMTEMPLATE_ADD_STUDENTMARKINGCOLUMNGRADERECORDS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_STUDENTMARKINGCOLUMNGRADERECORDS
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @GRADES xml = null
)
as

set nocount on;

--We're not actually using ID so we don't need to set it.

--if @ID is null set @ID = newid()


if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try
    declare @emptyGuid uniqueidentifier = '00000000-0000-0000-0000-000000000000'


    --Insert new grades

    --We're inserting all items from the incoming GradesXML that don't already have a StudentMarkingColumnGradeID.

    Insert into dbo.STUDENTMARKINGCOLUMNGRADE 
        (TRANSLATIONTABLEGRADEID,
        NUMERICGRADE,
        STUDENTCLASSMEETINGGROUPID,
        COURSEGRADINGMARKINGCOLUMNID,
        GRADETYPECODE,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED)
    (select
        case TRANSLATIONTABLEGRADEID when @emptyGuid then null else TRANSLATIONTABLEGRADEID end,
        NUMERICGRADE,
        STUDENTCLASSMEETINGGROUPID,
        COURSEGRADINGMARKINGCOLUMNID,
        GRADETYPECODE,
        @CHANGEAGENTID AS 'ADDEDBYID',
        @CHANGEAGENTID AS 'CHANGEDBYID',
        @CURRENTDATE AS 'DATEADDED',
        @CURRENTDATE AS 'DATECHANGED'
    from (select
            T.c.value('(STUDENTMARKINGCOLUMNGRADEID)[1]','uniqueidentifier') STUDENTMARKINGCOLUMNGRADEID,
            T.c.value('(TRANSLATIONTABLEGRADEID)[1]','uniqueidentifier') TRANSLATIONTABLEGRADEID,
            T.c.value('(NUMERICGRADE)[1]','numeric(14, 2)') NUMERICGRADE,
            T.c.value('(STUDENTCLASSMEETINGGROUPID)[1]','uniqueidentifier') STUDENTCLASSMEETINGGROUPID,
            T.c.value('(COURSEGRADINGMARKINGCOLUMNID)[1]','uniqueidentifier') COURSEGRADINGMARKINGCOLUMNID,
            T.c.value('(GRADETYPECODE)[1]','tinyint') GRADETYPECODE
        from @GRADES.nodes('/GRADES/ITEM') T(c)) GRADESDATA
    where (GRADESDATA.STUDENTMARKINGCOLUMNGRADEID is null) or (GRADESDATA.STUDENTMARKINGCOLUMNGRADEID = @emptyGuid))


    --Update existing grades      

    update g
    set
          g.TRANSLATIONTABLEGRADEID = case GRADESDATA.TRANSLATIONTABLEGRADEID when @emptyGuid then null else GRADESDATA.TRANSLATIONTABLEGRADEID end,
          g.NUMERICGRADE = GRADESDATA.NUMERICGRADE,
          g.GRADETYPECODE = GRADESDATA.GradeTypeCode,
          g.CHANGEDBYID = @CHANGEAGENTID,
          g.DATECHANGED = @CURRENTDATE,
          --If grade was previously blank, update the DateAdded with today, otherwise just reselect the original date added so we don't change the value.

          g.DATEADDED = case when g.GRADEISBLANK = 1 and (GRADESDATA.TRANSLATIONTABLEGRADEID <> @emptyGuid or GRADESDATA.NUMERICGRADE <> -1) then @CURRENTDATE else g.DATEADDED end
    from (select
                T.c.value('(STUDENTMARKINGCOLUMNGRADEID)[1]','uniqueidentifier') STUDENTMARKINGCOLUMNGRADEID,
                T.c.value('(TRANSLATIONTABLEGRADEID)[1]','uniqueidentifier') TRANSLATIONTABLEGRADEID,
                T.c.value('(NUMERICGRADE)[1]','numeric(14, 2)') NUMERICGRADE,
                T.c.value('(GRADETYPECODE)[1]','tinyint') GRADETYPECODE
            from @GRADES.nodes('/GRADES/ITEM') T(c)) GRADESDATA
        inner join dbo.STUDENTMARKINGCOLUMNGRADE g on GRADESDATA.STUDENTMARKINGCOLUMNGRADEID = g.ID
    where (GRADESDATA.STUDENTMARKINGCOLUMNGRADEID is not null) and (GRADESDATA.STUDENTMARKINGCOLUMNGRADEID <> @emptyGuid)

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0