USP_DATAFORMTEMPLATE_EDIT_TRANSFERREDGRADES

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_TRANSFERREDGRADES (
    @ID nvarchar(73),
    @CHANGEAGENTID uniqueidentifier = null,
  @XFERFROMGRADES xml,
  @XFERTOGRADES xml
)
as

    set nocount on;

  declare @CLASSID uniqueidentifier
    declare @STUDENTID uniqueidentifier

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try

    select
            @CLASSID = cast(substring(@ID,1,36) as uniqueidentifier),
            @STUDENTID = cast(substring(@ID,38,36) as uniqueidentifier)

        declare @STUDENTCOURSEID uniqueidentifier
        select
            @STUDENTCOURSEID = STUDENTCOURSE.ID
        from dbo.STUDENTCOURSE
            inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
            inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
        where STUDENTCOURSE.STUDENTID = @STUDENTID
            and CLASSMEETINGGROUP.CLASSID = @CLASSID

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

    insert into @TempTbl select
    T.c.value('(ID)[1]','uniqueidentifier') as 'ID',
    T.c.value('(STUDENTCLASSMEETINGGROUPID)[1]','uniqueidentifier') as 'STUDENTCLASSMEETINGGROUPID',
    T.c.value('(COURSEGRADINGMARKINGCOLUMNID)[1]','uniqueidentifier') as 'COURSEGRADINGMARKINGCOLUMNID',
    T.c.value('(TRANSLATIONTABLEGRADEID)[1]','uniqueidentifier') as 'TRANSLATIONTABLEGRADEID',
    T.c.value('(GRADETYPECODE)[1]','tinyint') as 'GRADETYPECODE',
    T.c.value('(NUMERICGRADE)[1]','numeric(14,2)') as 'NUMERICGRADE',
    T.c.value('(GRADEISBLANK)[1]','bit') as 'GRADEISBLANK'
    from @XFERFROMGRADES.nodes('/XFERFROMGRADES/ITEM') T(c)

    declare @MARKINGCOLUMNGRADES xml
    set @MARKINGCOLUMNGRADES = (select [ID],[STUDENTCLASSMEETINGGROUPID],[COURSEGRADINGMARKINGCOLUMNID],[TRANSLATIONTABLEGRADEID],
      [GRADETYPECODE],[NUMERICGRADE],[GRADEISBLANK] 
    from @TempTbl for xml raw('ITEM'),type,elements,root('MARKINGCOLUMNGRADES'),BINARY BASE64)

        exec dbo.USP_STUDENTMARKINGCOLUMN_GETCLASSGRADES_UPDATEFROMXML @STUDENTCOURSEID, @MARKINGCOLUMNGRADES, @CHANGEAGENTID, @CURRENTDATE, 3

    delete from @TempTbl

    insert into @TempTbl select
    T.c.value('(ID)[1]','uniqueidentifier') as 'ID',
    T.c.value('(STUDENTCLASSMEETINGGROUPID)[1]','uniqueidentifier') as 'STUDENTCLASSMEETINGGROUPID',
    T.c.value('(COURSEGRADINGMARKINGCOLUMNID)[1]','uniqueidentifier') as 'COURSEGRADINGMARKINGCOLUMNID',
    T.c.value('(TRANSLATIONTABLEGRADEID)[1]','uniqueidentifier') as 'TRANSLATIONTABLEGRADEID',
    T.c.value('(GRADETYPECODE)[1]','tinyint') as 'GRADETYPECODE',
    T.c.value('(NUMERICGRADE)[1]','numeric(14,2)') as 'NUMERICGRADE',
    T.c.value('(GRADEISBLANK)[1]','bit') as 'GRADEISBLANK'
    from @XFERTOGRADES.nodes('/XFERTOGRADES/ITEM') T(c)

    set @MARKINGCOLUMNGRADES = (select [ID],[STUDENTCLASSMEETINGGROUPID],[COURSEGRADINGMARKINGCOLUMNID],[TRANSLATIONTABLEGRADEID],
      [GRADETYPECODE],[NUMERICGRADE],[GRADEISBLANK] 
    from @TempTbl for xml raw('ITEM'),type,elements,root('MARKINGCOLUMNGRADES'),BINARY BASE64)

    exec dbo.USP_STUDENTMARKINGCOLUMN_GETCLASSGRADES_UPDATEFROMXML @STUDENTCOURSEID, @MARKINGCOLUMNGRADES, @CHANGEAGENTID, @CURRENTDATE, 0
      exec dbo.USP_STUDENTMARKINGCOLUMN_CLEARBLANKGRADES @STUDENTCOURSEID, @CHANGEAGENTID

  end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return