USP_STUDENTCLASS_ADD

CREATE procedure dbo.USP_STUDENTCLASS_ADD
(
    @STUDENTID uniqueidentifier,
    @CLASSID uniqueidentifier,
    @ALLOWOVERTARGET bit,
    @CHANGEAGENTID uniqueidentifier,
    @STUDENTCOURSEID uniqueidentifier = null,    -- Used by Transfer Class

    @STARTTERMID uniqueidentifier = null
)
as
begin

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        declare @TARGETSIZE int
        declare @MAXSIZE int
        declare @NUMBERENROLLED int

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

        select
           @TARGETSIZE = CLASS.CLASSSIZETARGET,
           @MAXSIZE = case when CLASS.CLASSSIZEMAXIMUM = 0 then null else Cast(COURSE.CLASSSIZEMAXIMUM as varchar(10)) end,
           @NUMBERENROLLED = dbo.UFN_CLASS_GETNUMBERENROLLED(@CLASSID),
           @CLASS_STARTDATE = CLASS.STARTDATE,
           @CLASS_ENDDATE = CLASS.ENDDATE,
           @COURSEID = CLASS.COURSEID
        from dbo.CLASS
            left join dbo.COURSE on dbo.CLASS.COURSEID = dbo.COURSE.ID
        where dbo.CLASS.ID = @CLASSID

        if not exists (
            select 1
            from dbo.EDUCATIONALHISTORY
                inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
            where EDUCATIONALHISTORY.CONSTITUENTID = @STUDENTID
                and STUDENTPROGRESSION.STARTDATE <= @CLASS_STARTDATE
                and STUDENTPROGRESSION.ENDDATE >= @CLASS_ENDDATE 
            )
        begin
            raiserror('BBERR_STUDENTCLASS_ELIGIBLESTUDENT', 13, 1);
        end

        if @NUMBERENROLLED >= @MAXSIZE
            raiserror('BBERR_STUDENTCLASS_MAXSIZE_EXCEEDED', 13, 1);

        if @NUMBERENROLLED >= @TARGETSIZE and @ALLOWOVERTARGET = 0
            raiserror('BBERR_STUDENTCLASS_TARGETSIZE_EXCEEDED', 13, 1);

        declare @WITHDRAWN_TRANSFERRED int = 0

        if @STUDENTCOURSEID is null
        begin
            -- Select the existing class enrollment if one exists,

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


            select
                @STUDENTCOURSEID = STUDENTCOURSE.ID,
                @WITHDRAWN_TRANSFERRED = count(case when STUDENTCLASSMEETINGGROUP.STATUSCODE in (2,3) then 1 else null end) over (partition by STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID)
            from dbo.STUDENTCOURSE
                inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
                inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
            where STUDENTCOURSE.COURSEID = @COURSEID
                and STUDENTCOURSE.STUDENTID = @STUDENTID
                and CLASSMEETINGGROUP.CLASSID = @CLASSID
        end
        else
        begin
            -- Handle the case where we transfer from one class to another, where we were previously enrolled with the current student course record

            select
                @WITHDRAWN_TRANSFERRED = count(case when STUDENTCLASSMEETINGGROUP.STATUSCODE in (2,3) then 1 else null end) over (partition by STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID)
            from dbo.STUDENTCLASSMEETINGGROUP
                inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
            where STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = @STUDENTCOURSEID
                and CLASSMEETINGGROUP.CLASSID = @CLASSID
        end

        -- If the student is being added to a class they withdrew/transferred from, simply reactivate the old enrollment.

        if @WITHDRAWN_TRANSFERRED > 0
        begin
            -- handle updating the data

            update dbo.STUDENTCLASSMEETINGGROUP set
                STATUSCODE = 0,
                STATUSDATE = null,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from  dbo.STUDENTCOURSE
                inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
                inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
            where STUDENTCOURSE.COURSEID = @COURSEID
                and STUDENTCOURSE.STUDENTID = @STUDENTID
                and CLASSMEETINGGROUP.CLASSID = @CLASSID
        end
        else
        begin
            -- Select the first non-fulfilled request if one exists

            if @STUDENTCOURSEID is null
            begin
                select @STUDENTCOURSEID = STUDENTCOURSE.ID
                from dbo.STUDENTCOURSE
                    left join dbo.STUDENTCOURSEREQUEST on STUDENTCOURSE.ID = STUDENTCOURSEREQUEST.ID
                where STUDENTCOURSE.COURSEID = @COURSEID and STUDENTCOURSE.STUDENTID = @STUDENTID
                    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)
            end

            if @STUDENTCOURSEID is null
            begin
                set @STUDENTCOURSEID = newid()

                -- add the student to the student course

                insert into dbo.STUDENTCOURSE
                (
                    ID,
                    STUDENTID,
                    COURSEID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                values
                (
                    @STUDENTCOURSEID,
                    @STUDENTID,
                    @COURSEID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                )
            end

            declare @TERM_STARTDATE date
            if @STARTTERMID is not null
                select @TERM_STARTDATE = TERM.STARTDATE
                from dbo.TERM
                where TERM.ID = @STARTTERMID

            -- handle inserting the data

            insert into dbo.STUDENTCLASSMEETINGGROUP
            (
                ID,
                STUDENTCOURSEID,
                CLASSMEETINGGROUPID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select
                newid(),
                @STUDENTCOURSEID,
                ID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from dbo.CLASSMEETINGGROUP
            where CLASSMEETINGGROUP.CLASSID = @CLASSID
                and (@STARTTERMID is null or CLASSMEETINGGROUP.STARTDATE >= @TERM_STARTDATE)
        end
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

end