USP_DATAFORMTEMPLATE_ADD_ENROLLGROUPINCLASS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ENROLLGROUPINCLASS
(
    @ID uniqueidentifier = null output,
    @CLASSID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @STUDENTS xml = null,
    @EXCEEDTARGETCLASSSIZE bit = null
)
as
    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @STUDENTCOUNT int
    declare @STUDENTLIST table
    (
        ID uniqueidentifier
    )

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

    set @STUDENTCOUNT = @@ROWCOUNT

    if @STUDENTCOUNT <= 0
    begin
        raiserror('BBERR_ENROLLGROUPINCLASS_NOSTUDENTS', 13, 1);
        return 1;
    end

    declare @CLASSSIZETARGET int
    declare @CLASSSIZEMAXIMUM int
    declare @CURRENTCLASSSIZE int

    declare @CLASS_STARTDATE date
    declare @CLASS_ENDDATE date
    declare @COURSEID uniqueidentifier

    select
        @CLASSSIZETARGET = CLASS.CLASSSIZETARGET,
        @CLASSSIZEMAXIMUM = CLASS.CLASSSIZEMAXIMUM,
        @CURRENTCLASSSIZE = coalesce((
            select MAX(CT)
                from (
                    select
                        COUNT(distinct STUDENTCLASSMEETINGGROUP.ID) - COUNT(distinct STUDENTS.ID) as CT
                    from dbo.CLASSMEETINGGROUP
                        inner join dbo.STUDENTCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                        inner join dbo.STUDENTCOURSE on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
                        left join @STUDENTLIST STUDENTS on STUDENTCOURSE.STUDENTID = STUDENTS.ID
                    where CLASSMEETINGGROUP.CLASSID = @CLASSID
                        and STUDENTCLASSMEETINGGROUP.STATUSCODE = 0
                    group by CLASSMEETINGGROUP.ID
                ) T
            ), 0),
        @CLASS_STARTDATE = CLASS.STARTDATE,
        @CLASS_ENDDATE = CLASS.ENDDATE,
        @COURSEID = CLASS.COURSEID
    from dbo.CLASS
    where CLASS.ID = @CLASSID 

    if @CLASSSIZEMAXIMUM > 0 and (@CURRENTCLASSSIZE + @STUDENTCOUNT) > @CLASSSIZEMAXIMUM
    begin
        raiserror('BBERR_ENROLLGROUPINCLASS_MAXIMUMEXCEEDED', 13, 1);
        return 1;
    end

    if @EXCEEDTARGETCLASSSIZE = 0 and (@CURRENTCLASSSIZE + @STUDENTCOUNT) > @CLASSSIZETARGET
    begin
        raiserror('BBERR_ENROLLGROUPINCLASS_TARGETEXCEEDED', 13, 1);
        return 1;
    end

    declare @STUDENTCOURSE table
    (
        ID uniqueidentifier,
        STUDENTID uniqueidentifier,
        ADDRECORD bit
    )

    -- Select the existing class enrollment if one exists,

    --   to prevent adding the student to a class they are already in

    insert into @STUDENTCOURSE
    (
        ID,
        STUDENTID,
        ADDRECORD
    )
    select
        STUDENTCOURSES.ID,
        STUDENTS.ID as STUDENTID,
        0
    from @STUDENTLIST STUDENTS
        left join (
            select
                STUDENTCOURSE.ID,
                STUDENTCOURSE.STUDENTID
            from dbo.STUDENTCOURSE
                left join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
                left join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
            where STUDENTCOURSE.COURSEID = @COURSEID
                and CLASSMEETINGGROUP.CLASSID = @CLASSID
            group by STUDENTCOURSE.ID, STUDENTCOURSE.STUDENTID
        ) STUDENTCOURSES on STUDENTS.ID = STUDENTCOURSES.STUDENTID

    -- Select the first non-fulfilled request if one exists

    update @STUDENTCOURSE
    set
        ID = coalesce(STUDENTCOURSES2.ID, newid()),
        ADDRECORD = case when STUDENTCOURSES2.ID is null then 1 else 0 end
    from @STUDENTCOURSE STUDENTCOURSES
        left join
        ( 
            select
                STUDENTCOURSE.ID,
                STUDENTCOURSE.STUDENTID,
                ROW_NUMBER() over (partition by STUDENTCOURSE.STUDENTID order by STUDENTCOURSE.DATEADDED) as ROW 
            from dbo.STUDENTCOURSE
                inner join dbo.STUDENTCOURSEREQUEST on STUDENTCOURSE.ID = STUDENTCOURSEREQUEST.ID
            where STUDENTCOURSE.COURSEID = @COURSEID
                and STUDENTCOURSEREQUEST.STARTDATE <= @CLASS_STARTDATE
                and STUDENTCOURSEREQUEST.ENDDATE >= @CLASS_ENDDATE
                and (
                        STUDENTCOURSEREQUEST.CLASSSTARTDATE is null or
                        STUDENTCOURSEREQUEST.CLASSSTARTDATE = @CLASS_STARTDATE
                    )
                and not exists (select ID from dbo.STUDENTCLASSMEETINGGROUP where STUDENTCOURSEID = STUDENTCOURSE.ID)
        ) STUDENTCOURSES2 on STUDENTCOURSES2.STUDENTID = STUDENTCOURSES.STUDENTID
            and STUDENTCOURSES2.ROW = 1
    where STUDENTCOURSES.ID is null

    insert into dbo.STUDENTCOURSE
    (
        ID,
        STUDENTID,
        COURSEID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        STUDENTCOURSES.ID,
        STUDENTCOURSES.STUDENTID,
        @COURSEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @STUDENTCOURSE STUDENTCOURSES
    where STUDENTCOURSES.ADDRECORD <> 0

    insert into dbo.STUDENTCLASSMEETINGGROUP
    (
        ID,
        STUDENTCOURSEID,
        CLASSMEETINGGROUPID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        newid(),
        STUDENTCOURSES.ID,
        CLASSMEETINGGROUP.ID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @STUDENTCOURSE STUDENTCOURSES
        cross join dbo.CLASS
        inner join dbo.CLASSMEETINGGROUP on CLASS.ID = CLASSMEETINGGROUP.CLASSID
        left join dbo.STUDENTCLASSMEETINGGROUP
            on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
            and STUDENTCOURSES.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
    where @CLASSID = CLASS.ID and
        STUDENTCLASSMEETINGGROUP.ID is null

return 0