USP_DATAFORMTEMPLATE_EDIT_WITHDRAWSTUDENT

The save procedure used by the edit dataform template "Withdraw Student Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(73)	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@WITHDRAWNDATE	date	IN	Withdrawn date
@WITHDRAWNTERMID	uniqueidentifier	IN	Withdraw starting in
@MARKINGCOLUMNGRADES	xml	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_WITHDRAWSTUDENT (
    @ID nvarchar(73),
    @CHANGEAGENTID uniqueidentifier,
    @WITHDRAWNDATE date,
    @WITHDRAWNTERMID uniqueidentifier,
    @MARKINGCOLUMNGRADES xml
)
as
    set nocount on;

    declare @CLASSID uniqueidentifier
    declare @STUDENTID uniqueidentifier

    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 @TERM_STARTDATE date
        select @TERM_STARTDATE = TERM.STARTDATE
        from dbo.TERM
        where TERM.ID = @WITHDRAWNTERMID    

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

        declare @CURRENTDATE datetime = getdate()

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

        update dbo.STUDENTCLASSMEETINGGROUP set
            STUDENTCLASSMEETINGGROUP.STATUSCODE = 2,
            STUDENTCLASSMEETINGGROUP.STATUSDATE = coalesce(@WITHDRAWNDATE, getdate()),
            STUDENTCLASSMEETINGGROUP.CHANGEDBYID = @CHANGEAGENTID,
            STUDENTCLASSMEETINGGROUP.DATECHANGED = @CURRENTDATE
        from dbo.STUDENTCLASSMEETINGGROUP
            inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
        where STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = @STUDENTCOURSEID
            and CLASSMEETINGGROUP.STARTDATE >= @TERM_STARTDATE
            and STUDENTCLASSMEETINGGROUP.STATUSCODE in (0,2)
            and (STUDENTCLASSMEETINGGROUP.STATUSDATE is null or STUDENTCLASSMEETINGGROUP.STATUSDATE != coalesce(@WITHDRAWNDATE, getdate()))

        exec dbo.USP_STUDENTMARKINGCOLUMN_CLEARBLANKGRADES @STUDENTCOURSEID, @CHANGEAGENTID

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

return 0;