USP_DATAFORMTEMPLATE_ADD_CREATEMULTIPLECLASSES

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CREATEMULTIPLECLASSES
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @COURSES xml = null,
    @TERMDATA xml = null,
    @OVERRIDECLASSLIMIT bit = null
)
as

    set nocount on

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

    begin try

        declare @COURSETERMDATATEMP table
        (
            [ID] uniqueidentifier,
            [FACULTYID] uniqueidentifier,
            [SESSIONID] uniqueidentifier,
            [TERMID] uniqueidentifier,
            [COUNT_ORIGINAL] int,
            [COUNT_NEW] int,
            [NUMWITHSTUDENTS] int
        )

        insert into @COURSETERMDATATEMP
        (
            ID,
            FACULTYID,
            SESSIONID,
            TERMID,
            COUNT_ORIGINAL,
            COUNT_NEW,
            NUMWITHSTUDENTS
        )
        select
            COURSES.ID,
            FACULTYID,
            (select SESSIONID from TERM where ID = TERMDATA.TERMID) as SESSIONID,
            TERMID,
            COUNT_ORIGINAL,
            COUNT_NEW,
            NUMWITHSTUDENTS
        from (select
                T.c.value('(ID)[1]','uniqueidentifier') as TERMID,
                T.c.value('(COUNT_ORIGINAL)[1]','int') as COUNT_ORIGINAL,
                T.c.value('(COUNT_NEW)[1]','int') as COUNT_NEW,
                T.c.value('(NUMWITHSTUDENTS)[1]','int') as NUMWITHSTUDENTS,
                T.c.value('(COURSEID)[1]','uniqueidentifier') as COURSEID
                from @TERMDATA.nodes('/TERMDATA/ITEM') as T(c)) TERMDATA
        join (select
                    T.c.value('(ID)[1]','uniqueidentifier') as ID,
                    T.c.value('(FACULTYID)[1]','uniqueidentifier') as FACULTYID
                    from @COURSES.nodes('/COURSES/ITEM') as T(c)) COURSES 
            on COURSES.ID = TERMDATA.COURSEID

        declare @MAXCOUNT_NEW as int
        select @MAXCOUNT_NEW = max(COUNT_NEW) from  @COURSETERMDATATEMP 

        declare @CLASSESTOCREATETEMP table (
           [ROWID] int identity(1,1),
           [COURSEID] uniqueidentifier,
           [SESSIONID] uniqueidentifier,
           [TERMID] uniqueidentifier,
           [FACULTYID] uniqueidentifier,
           [RESTRICTIONSTATUS] tinyint)

        insert into @CLASSESTOCREATETEMP (COURSEID, SESSIONID, TERMID, FACULTYID, RESTRICTIONSTATUS) 
        select 
            COURSETERMDATATEMP.ID,
            SESSIONID,
            TERMID,
            FACULTYID,
            (case 
                when CLASSESPERTERMMAXIMUM > 0 and (COUNT_NEW > CLASSESPERTERMMAXIMUM) then 3
                when (CLASSESPERTERMTARGET > 0 and (COUNT_NEW > CLASSESPERTERMTARGET)) or (CLASSESPERTERMMAXIMUM > 0 and (COUNT_NEW = CLASSESPERTERMMAXIMUM)) then 2
                when CLASSESPERTERMMINIMUM > 0 and (COUNT_NEW < CLASSESPERTERMMINIMUM) then 1
                when (CLASSESPERTERMTARGET = 0 and CLASSESPERTERMMAXIMUM = 0) then 5
                else 0
            end) as RESTRICTIONSTATUS
        from 
            dbo.UFN_NUMBERTABLE(0, @MAXCOUNT_NEW), @COURSETERMDATATEMP as COURSETERMDATATEMP
        inner join dbo.COURSE 
            on COURSE.ID = COURSETERMDATATEMP.ID
        where (NUMBER + COUNT_ORIGINAL) < COUNT_NEW

        if exists(select RESTRICTIONSTATUS from @CLASSESTOCREATETEMP where RESTRICTIONSTATUS = 3)
            raiserror('BBERR_CREATECLASSES_MAXIMUM', 13, 1)     

        if @OVERRIDECLASSLIMIT = 0 and exists(select RESTRICTIONSTATUS from @CLASSESTOCREATETEMP where RESTRICTIONSTATUS = 2)
            raiserror('BBERR_CREATECLASSES_NEED_OVERRIDE', 13, 1)  

        declare @CLASSESTODELETETEMP table (
           [ROWID] int identity(1,1),
           [COURSEID] uniqueidentifier,
           [ID] uniqueidentifier,
           [TERMID] uniqueidentifier,
           [SECTION] nvarchar(20),
           [COUNT_ORIGINAL] int,
           [COUNT_NEW] int,
           [RESTRICTIONSTATUS] tinyint,
           [STUDENTCOUNT] int,
           [ROWNUMBER] int)

        insert into @CLASSESTODELETETEMP (COURSEID, ID, TERMID, SECTION, COUNT_ORIGINAL, COUNT_NEW, RESTRICTIONSTATUS, STUDENTCOUNT, ROWNUMBER) 
        select COURSEID, ID, TERMID, SECTION, COUNT_ORIGINAL, COUNT_NEW, RESTRICTIONSTATUS, STUDENTCOUNT,
            ROW_NUMBER() OVER(PARTITION BY COURSEID, TERMID ORDER BY STUDENTCOUNT DESC, SECTION ASC) AS ROWNUM 
        from (select 
                COURSEID, 
                CLASS.ID, 
                TERMID,
                SECTION, 
                COUNT_ORIGINAL, 
                COUNT_NEW,
                (case 
                    when (NUMWITHSTUDENTS > 0) and (NUMWITHSTUDENTS > COUNT_NEW) then 4
                    else 0
                end) as RESTRICTIONSTATUS,
                (select count(*)
                        from dbo.CLASSMEETINGGROUP 
                        inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                        inner join dbo.TERM on TERM.ID = COURSES.TERMID and  
                            TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE and 
                            TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                        where CLASSMEETINGGROUP.CLASSID = CLASS.ID 
                ) STUDENTCOUNT
            from dbo.CLASS 
            inner join dbo.CLASSMEETINGGROUP  on CLASSMEETINGGROUP.CLASSID = CLASS.ID
            inner join @COURSETERMDATATEMP as COURSES 
                on COURSES.ID = CLASS.COURSEID 
            inner join dbo.TERM 
                on TERM.ID = COURSES.TERMID
            where 
                COUNT_ORIGINAL > COUNT_NEW and
                TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE and
                TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE ) t
        group by COURSEID, TERMID, STUDENTCOUNT, ID, SECTION, COUNT_ORIGINAL, COUNT_NEW, RESTRICTIONSTATUS

        if exists(select RESTRICTIONSTATUS from @CLASSESTODELETETEMP where RESTRICTIONSTATUS = 4)
            raiserror('BBERR_CREATECLASSES_CANNOT_DELETE', 13, 1)  

        --generate new classes

        declare @RowId int
        declare @MaxRowId int   

        declare @COURSEID uniqueidentifier = null     
        declare @SESSIONID uniqueidentifier = null 
        declare @TERMID uniqueidentifier = null 
        declare @FACULTYID uniqueidentifier = null 
        declare @SECTION nvarchar(20) = null

        select
           @RowId = min(ROWID),
           @MaxRowId = max(ROWID)
        from @CLASSESTOCREATETEMP

        while @RowId <= @MaxRowId
        begin
            select
               @COURSEID = COURSEID, @SESSIONID = SESSIONID, @TERMID = TERMID, @FACULTYID = FACULTYID
            from
               @CLASSESTOCREATETEMP
            where 
               ROWID = @RowId

            select @SECTION = dbo.UFN_CLASS_GETDEFAULTSECTION(@COURSEID, @SESSIONID, @TERMID)
            exec dbo.USP_DATAFORMTEMPLATE_ADD_CLASS null, @SESSIONID, @COURSEID, @TERMID, @SECTION, @FACULTYID, @CHANGEAGENTID

            set @RowId = @RowId + 1
        end    

        --delete classes

        declare @CLASSID uniqueidentifier
        declare @ROWNUMBER int
        declare @COUNT_NEW int
        declare @STUDENTCOUNT int

        select
           @RowId = min(ROWID),
           @MaxRowId = max(ROWID)
        from @CLASSESTODELETETEMP

        while @RowId <= @MaxRowId
        begin
            select
               @CLASSID = ID,
               @ROWNUMBER = ROWNUMBER,
               @COUNT_NEW = COUNT_NEW,
               @STUDENTCOUNT = STUDENTCOUNT  
            from
               @CLASSESTODELETETEMP
            where 
               ROWID = @RowId

            if @ROWNUMBER > @COUNT_NEW and @STUDENTCOUNT = 0
                exec dbo.USP_CLASS_DELETE @CLASSID, @CHANGEAGENTID

            set @RowId = @RowId + 1
        end    
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0