USP_STUDENTMARKINGCOLUMN_GETCLASSGRADES_UPDATEFROMXML

CREATE procedure dbo.USP_STUDENTMARKINGCOLUMN_GETCLASSGRADES_UPDATEFROMXML
(
    @STUDENTCOURSEID uniqueidentifier,
    @XML xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null,
  @STATUSCODE tinyint
)
as
begin
    set nocount on;

    if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    if @CHANGEDATE is null 
        set @CHANGEDATE = getdate()

    -- build a temporary table containing the values from the XML

    declare @TempTbl table (
        [ID] uniqueidentifier,
        [STUDENTCLASSMEETINGGROUPID] uniqueidentifier,
        [COURSEGRADINGMARKINGCOLUMNID] uniqueidentifier,
        [TRANSLATIONTABLEGRADEID] uniqueidentifier,
        [GRADETYPECODE] tinyint,
        [NUMERICGRADE] integer,
        [GRADEISBLANK] bit)

    insert into @TempTbl select 
        [ID],
        [STUDENTCLASSMEETINGGROUPID],
        [COURSEGRADINGMARKINGCOLUMNID],
        [TRANSLATIONTABLEGRADEID],
        [GRADETYPECODE],
        [NUMERICGRADE],
        [GRADEISBLANK]
    from dbo.UFN_STUDENTMARKINGCOLUMN_GETCLASSGRADES_FROMITEMLISTXML(@XML)

    update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

    if @@Error <> 0
        return 1;

    declare @contextCache varbinary(128);
    declare @e int;

    -- cache current context information 

    set @contextCache = CONTEXT_INFO();

    -- set CONTEXT_INFO to @CHANGEAGENTID 

    if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

    -- delete any items that no longer exist in the XML table

    delete from dbo.[STUDENTMARKINGCOLUMNGRADE] where [STUDENTMARKINGCOLUMNGRADE].ID in 
        (select ID from dbo.UFN_STUDENTMARKINGCOLUMN_GETCLASSGRADES
        (
            @STUDENTCOURSEID,
      @STATUSCODE
        )
        EXCEPT select ID from @TempTbl)    

    select @e=@@error;

    -- reset CONTEXT_INFO to previous value 

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    if @e <> 0
        return 2;

    -- update the items that exist in the XML table and the db

    update [STUDENTMARKINGCOLUMNGRADE] set
        [STUDENTMARKINGCOLUMNGRADE].[GRADETYPECODE] = [temp].[GRADETYPECODE],
        [STUDENTMARKINGCOLUMNGRADE].[NUMERICGRADE] =
            case when [temp].[GRADEISBLANK] = 0 and [temp].[GRADETYPECODE] <> 1
                then [temp].[NUMERICGRADE]
                else -1
            end,
        [STUDENTMARKINGCOLUMNGRADE].[TRANSLATIONTABLEGRADEID] = 
            case when [temp].[GRADEISBLANK] = 0 and [temp].[GRADETYPECODE] <> 2
                then [temp].[TRANSLATIONTABLEGRADEID]
                else null
            end,
        [STUDENTMARKINGCOLUMNGRADE].CHANGEDBYID = @CHANGEAGENTID,
        [STUDENTMARKINGCOLUMNGRADE].DATECHANGED = @CHANGEDATE
    from dbo.[STUDENTMARKINGCOLUMNGRADE]
        inner join @TempTbl as [temp] on [STUDENTMARKINGCOLUMNGRADE].ID = [temp].ID
    where
        ([STUDENTMARKINGCOLUMNGRADE].[GRADETYPECODE] <> temp.[GRADETYPECODE]) or
        ([STUDENTMARKINGCOLUMNGRADE].[GRADEISBLANK] <> temp.[GRADEISBLANK]) or 
        ([STUDENTMARKINGCOLUMNGRADE].[GRADETYPECODE] = 2 and [STUDENTMARKINGCOLUMNGRADE].[NUMERICGRADE] <> temp.[NUMERICGRADE]) or
        ([STUDENTMARKINGCOLUMNGRADE].[TRANSLATIONTABLEGRADEID] is null and temp.[TRANSLATIONTABLEGRADEID] is not null) or
        ([STUDENTMARKINGCOLUMNGRADE].[TRANSLATIONTABLEGRADEID] is not null and temp.[TRANSLATIONTABLEGRADEID] is null) or
        ([STUDENTMARKINGCOLUMNGRADE].[TRANSLATIONTABLEGRADEID] <> temp.[TRANSLATIONTABLEGRADEID])

    if @@Error <> 0
        return 3;

    -- insert new items

    insert into [STUDENTMARKINGCOLUMNGRADE]
        ([ID],
        [STUDENTCLASSMEETINGGROUPID],
        [COURSEGRADINGMARKINGCOLUMNID],
        [TRANSLATIONTABLEGRADEID],
        [GRADETYPECODE],
        [NUMERICGRADE],
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED)
    select
        [temp].[ID],
        [temp].[STUDENTCLASSMEETINGGROUPID],
        [temp].[COURSEGRADINGMARKINGCOLUMNID],
        case when [temp].[GRADEISBLANK] = 0 and [temp].[GRADETYPECODE] <> 2
                then [temp].[TRANSLATIONTABLEGRADEID]
                else null
            end as [TRANSLATIONTABLEGRADEID], 
        case when [temp].GRADEISBLANK = 0
                then [temp].[GRADETYPECODE]
                else 2
            end as [GRADETYPECODE],
        case when [temp].[GRADEISBLANK] = 0 and [temp].[GRADETYPECODE] <> 1 and [temp].[TRANSLATIONTABLEGRADEID] is null
                then [temp].[NUMERICGRADE]
                else -1
            end as [NUMERICGRADE],
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CHANGEDATE, 
        @CHANGEDATE
    from @TempTbl as [temp]
    where not exists (select ID from dbo.[STUDENTMARKINGCOLUMNGRADE] as data where data.ID = [temp].ID)

    if @@Error <> 0
        return 4;

    return 0;

end