USP_DATAFORMTEMPLATE_ADD_ENROLLGROUPINCLASSES

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ENROLLGROUPINCLASSES
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier,
    @ENROLLMETHOD tinyint,
    @FACULTYID uniqueidentifier = null,
    @CLASSID uniqueidentifier = null,
    @GRADELEVELID uniqueidentifier = null,
    @STUDENTSELECTIONID uniqueidentifier = null,
    @CLASSES xml = null,
    @EXCEEDTARGETCLASSSIZE bit = null
)
as
    declare @SESSIONID uniqueidentifier
    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date
    declare @STUDENTCOUNT int

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

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

    declare @STUDENTS table
    (
        ID uniqueidentifier
    )

    if @ENROLLMETHOD = 0
    begin
        insert into @STUDENTS
        select EDUCATIONALHISTORY.CONSTITUENTID
        from dbo.STUDENTPROGRESSION
            inner join dbo.EDUCATIONALHISTORY on STUDENTPROGRESSION.ENROLLMENTID = EDUCATIONALHISTORY.ID
        where STUDENTPROGRESSION.HOMEROOMTEACHERID = @FACULTYID
            and STUDENTPROGRESSION.STARTDATE <= @SESSION_STARTDATE
            and STUDENTPROGRESSION.ENDDATE >= @SESSION_ENDDATE 
        group by EDUCATIONALHISTORY.CONSTITUENTID

        set @STUDENTCOUNT = @@ROWCOUNT
    end
    else if @ENROLLMETHOD = 1
    begin
        insert into @STUDENTS
        select EDUCATIONALHISTORY.CONSTITUENTID
        from dbo.STUDENTPROGRESSION
            inner join dbo.EDUCATIONALHISTORY on STUDENTPROGRESSION.ENROLLMENTID = EDUCATIONALHISTORY.ID
            inner join dbo.STUDENTCOURSE on EDUCATIONALHISTORY.CONSTITUENTID = STUDENTCOURSE.STUDENTID
            inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
            inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
        where CLASSMEETINGGROUP.CLASSID = @CLASSID
            and STUDENTPROGRESSION.STARTDATE <= @SESSION_STARTDATE
            and STUDENTPROGRESSION.ENDDATE >= @SESSION_ENDDATE
      and STUDENTCLASSMEETINGGROUP.STATUSCODE = 0
        group by EDUCATIONALHISTORY.CONSTITUENTID

        set @STUDENTCOUNT = @@ROWCOUNT
    end
    else if @ENROLLMETHOD = 2
    begin
        insert into @STUDENTS
        select EDUCATIONALHISTORY.CONSTITUENTID
        from dbo.STUDENTPROGRESSION
            inner join dbo.EDUCATIONALHISTORY on STUDENTPROGRESSION.ENROLLMENTID = EDUCATIONALHISTORY.ID
            inner join SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
        where SCHOOLGRADELEVEL.GRADELEVELID = @GRADELEVELID
            and STUDENTPROGRESSION.STARTDATE <= @SESSION_STARTDATE
            and STUDENTPROGRESSION.ENDDATE >= @SESSION_ENDDATE
        group by EDUCATIONALHISTORY.CONSTITUENTID

        set @STUDENTCOUNT = @@ROWCOUNT
    end
    else if @ENROLLMETHOD = 3
    begin
        insert into @STUDENTS (ID)
        exec dbo.USP_STUDENT_GETSTUDENTSELECTION @STUDENTSELECTIONID

        delete from @STUDENTS
        from @STUDENTS STUDENTS
            left join dbo.EDUCATIONALHISTORY on STUDENTS.ID = EDUCATIONALHISTORY.CONSTITUENTID
            left join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
                and STUDENTPROGRESSION.STARTDATE <= @SESSION_STARTDATE
                and STUDENTPROGRESSION.ENDDATE >= @SESSION_ENDDATE
        where STUDENTPROGRESSION.ID is null

        select @STUDENTCOUNT = count(STUDENTS.ID)
        from @STUDENTS STUDENTS
    end

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

    declare @CLASS table
    (
        ID uniqueidentifier,
        CLASSSIZETARGET int,
        CLASSSIZEMAXIMUM int,
        CURRENTCLASSSIZE int
    )

    insert into @CLASS
    (
        ID,
        CLASSSIZETARGET,
        CLASSSIZEMAXIMUM,
        CURRENTCLASSSIZE    -- Ignores students both in the class and in the list to be added

    )
    select
        CLASS.ID,
        CLASS.CLASSSIZETARGET,
        CLASS.CLASSSIZEMAXIMUM,
        coalesce(CLASSENROLLMENT.[COUNT], 0)
    from (select
            T.c.value('(CLASSID)[1]','uniqueidentifier') as ID
            from @CLASSES.nodes('/CLASSES/ITEM') as T(c)) CLASSES
        inner join dbo.CLASS on CLASSES.ID = CLASS.ID
        left join
        (
            select
                CLASSID,
                max([COUNT]) as [COUNT]
            from (
                select 
                    CLASSMEETINGGROUP.CLASSID,
                    (count(distinct STUDENTCLASSMEETINGGROUP.ID) - count(distinct STUDENTS.ID)) as [COUNT]
                from dbo.CLASSMEETINGGROUP
                    inner join dbo.STUDENTCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                    inner join dbo.STUDENTCOURSE on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
                    left join @STUDENTS STUDENTS on STUDENTCOURSE.STUDENTID = STUDENTS.ID
                where STUDENTCLASSMEETINGGROUP.STATUSCODE = 0
                group by CLASSMEETINGGROUP.ID, CLASSMEETINGGROUP.CLASSID
            ) T
            group by CLASSID
        ) CLASSENROLLMENT on CLASS.ID = CLASSENROLLMENT.CLASSID

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

    -- validate that the number of students being added to every course is less then the number needed

    if @EXCEEDTARGETCLASSSIZE = 0 and exists(
        select 1
        from @CLASS
        where (CURRENTCLASSSIZE + @STUDENTCOUNT) > CLASSSIZETARGET)
    begin
        raiserror('BBERR_ENROLLGROUPINCLASSES_TARGETEXCEEDED', 13, 1);
        return 1;
    end

    declare @STUDENTCOURSE table
    (
        ID uniqueidentifier,
        STUDENTID uniqueidentifier,
        CLASSID uniqueidentifier,
        COURSEID 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,
        CLASSID,
        COURSEID,
        ADDRECORD
    )
    select
        STUDENTCOURSES.ID,
        STUDENTS.ID as STUDENTID,
        CLASSES.ID as CLASSID,
        CLASS.COURSEID as COURSEID,
        0 as ADDRECORD
    from @STUDENTS STUDENTS
        cross join @CLASS CLASSES
        inner join dbo.CLASS on CLASSES.ID = CLASS.ID
        left join (
            select
                STUDENTCOURSE.ID,
                STUDENTCOURSE.STUDENTID,
                STUDENTCOURSE.COURSEID,
                CLASSMEETINGGROUP.CLASSID
            from dbo.STUDENTCOURSE
                left join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
                left join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
            group by STUDENTCOURSE.ID, STUDENTCOURSE.STUDENTID, STUDENTCOURSE.COURSEID, CLASSMEETINGGROUP.CLASSID
        ) STUDENTCOURSES on STUDENTS.ID = STUDENTCOURSES.STUDENTID
            and CLASSES.ID = STUDENTCOURSES.CLASSID
            and CLASS.COURSEID = STUDENTCOURSES.COURSEID

    -- 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,
                CLASS.ID as CLASSID,
                STUDENTCOURSE.COURSEID,
                ROW_NUMBER() over (partition by STUDENTCOURSE.STUDENTID, CLASS.ID order by STUDENTCOURSE.DATEADDED) as ROW 
            from dbo.STUDENTCOURSE
                inner join dbo.STUDENTCOURSEREQUEST on STUDENTCOURSE.ID = STUDENTCOURSEREQUEST.ID
                inner join dbo.CLASS on STUDENTCOURSE.COURSEID = CLASS.COURSEID
            where 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.COURSEID = STUDENTCOURSES.COURSEID
            and STUDENTCOURSES2.CLASSID = STUDENTCOURSES.CLASSID
            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,
        STUDENTCOURSES.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
        inner join dbo.CLASSMEETINGGROUP on STUDENTCOURSES.CLASSID = CLASSMEETINGGROUP.CLASSID
        left join dbo.STUDENTCLASSMEETINGGROUP
            on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
            and STUDENTCOURSES.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
    where STUDENTCLASSMEETINGGROUP.ID is null

return 0