USP_DATAFORMTEMPLATE_CLEAR_STUDENTSCHEDULES

CREATE procedure dbo.USP_DATAFORMTEMPLATE_CLEAR_STUDENTSCHEDULES
(
    @ID uniqueidentifier = null output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @STUDENTS xml = null,
    @SELECTIONID uniqueidentifier = null
)
as
    set nocount on;

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

    if @SELECTIONID = '00000000-0000-0000-0000-000000000000'
        set @SELECTIONID = null

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date
    declare @SESSIONID uniqueidentifier
    declare @SCHOOLID uniqueidentifier

    select
        @SESSION_STARTDATE = min(TERM.STARTDATE),
        @SESSION_ENDDATE = max(TERM.ENDDATE),
        @SESSIONID = SESSION.ID,
        @SCHOOLID = ACADEMICYEAR.SCHOOLID
    from dbo.TERM
        inner join dbo.APPUSERSESSION on TERM.SESSIONID = APPUSERSESSION.SESSIONID
        inner join dbo.SESSION on APPUSERSESSION.SESSIONID = SESSION.ID
        inner join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
    where APPUSERSESSION.ID = @CURRENTAPPUSERID
    group by APPUSERSESSION.ID, SESSION.ID, ACADEMICYEAR.SCHOOLID;

    -- Because of a performance quirk in SQL, we must do the XML processing for this query independent of the main query.

    declare @STUDENTLIST table (ID uniqueidentifier)    
    insert into @STUDENTLIST
    select T.c.value('(ID)[1]','uniqueidentifier')
    from @STUDENTS.nodes('/STUDENTS/ITEM') T(c)
    where coalesce(T.c.value('(CLEAR)[1]','bit'), 0) <> 0

    declare @TempTbl table (
        [STUDENTCLASSMEETINGGROUPID] uniqueidentifier,
        [STUDENTID] uniqueidentifier,
        [CLEAR] bit,
        [HASGRADES] bit)

    insert into @TempTbl
        select
            [STUDENTCLASSMEETINGGROUP].ID as [STUDENTCLASSMEETINGGROUPID],
            EDUCATIONALHISTORY.CONSTITUENTID as [STUDENTID],
            case when STUDENTLIST.ID is null then 0 else 1 end as [CLEAR],
            case when exists(
                    select STUDENTMARKINGCOLUMNGRADE.ID
                    from dbo.STUDENTCOURSE
                        inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
                        inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                        inner join dbo.STUDENTMARKINGCOLUMNGRADE on STUDENTCLASSMEETINGGROUP.ID = STUDENTMARKINGCOLUMNGRADE.STUDENTCLASSMEETINGGROUPID
                    where STUDENTCOURSE.STUDENTID = EDUCATIONALHISTORY.CONSTITUENTID
                        and CLASSMEETINGGROUP.CLASSID = CLASS.ID)
                then 1
                else 0
            end as [HASGRADES]
        from dbo.EDUCATIONALHISTORY
            inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
                and not (STUDENTPROGRESSION.ENDDATE < @SESSION_STARTDATE or STUDENTPROGRESSION.STARTDATE > @SESSION_ENDDATE)
            inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
                and SCHOOLGRADELEVEL.SCHOOLID = @SCHOOLID
            inner join dbo.STUDENTCOURSE on EDUCATIONALHISTORY.CONSTITUENTID = STUDENTCOURSE.STUDENTID
            inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
                and STUDENTCLASSMEETINGGROUP.STATUSCODE in (0, 3)
            inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
            inner join dbo.CLASS on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                and not (CLASS.ENDDATE < @SESSION_STARTDATE or CLASS.STARTDATE > @SESSION_ENDDATE)
            left join @STUDENTLIST STUDENTLIST on EDUCATIONALHISTORY.CONSTITUENTID = STUDENTLIST.ID
        where @SELECTIONID is null or EDUCATIONALHISTORY.CONSTITUENTID in (
                    select ID
                    from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID)
                )

    if not exists(select 1 from @TempTbl STUDENTCLASSES where STUDENTCLASSES.CLEAR <> 0 and STUDENTCLASSES.HASGRADES = 0)
    begin
        raiserror('BBERR_CLEAR_STUDENTSCHEDULES_ATLEASTONESTUDENT', 13, 1)
        return
    end

    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.[STUDENTCLASSMEETINGGROUP]
    from dbo.STUDENTCLASSMEETINGGROUP
        inner join @TempTbl STUDENTCLASSES on STUDENTCLASSMEETINGGROUP.ID = STUDENTCLASSES.STUDENTCLASSMEETINGGROUPID
    where STUDENTCLASSES.CLEAR <> 0
        and STUDENTCLASSES.HASGRADES = 0
        and STUDENTCLASSMEETINGGROUP.STATUSCODE in (0, 3)

    select @e=@@error;

    -- reset CONTEXT_INFO to previous value 

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    if @e <> 0
        return 2;