USP_DATAFORMTEMPLATE_ADD_CREATEMULTIPLECLASSES_PRELOAD

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CREATEMULTIPLECLASSES_PRELOAD
(
    @CURRENTAPPUSERID uniqueidentifier,
    @COURSES xml = null output,
    @TERMDATA xml = null output,
    @TERMCOLMETA xml = null output,
    @HEADER nvarchar(310) = null output
)
as
    set nocount on

    declare @SESSIONID uniqueidentifier
    declare @ACADEMICYEARID uniqueidentifier
    declare @SCHOOLID uniqueidentifier    

    select
        @SESSIONID = SESSIONID,
        @ACADEMICYEARID = ACADEMICYEARID,
        @HEADER = HEADER,
        @SCHOOLID = SCHOOLID
    from dbo.UFN_APPUSERSESSION_GETHEADERTEXT(@CURRENTAPPUSERID)

    declare @STARTDATE date
    declare @ENDDATE date
    select @STARTDATE = STARTDATE, @ENDDATE = ENDDATE from dbo.UFN_SESSION_GETDATES(@SESSIONID)               

    --@COURSES

    declare @Temp table(ID uniqueidentifier, COURSEID nvarchar(12), NAME nvarchar(60), CLASSESPERTERMMINIMUM int, CLASSESPERTERMTARGET int, CLASSESPERTERMMAXIMUM int)

    insert into @Temp
    select
        COURSE.ID,
        COURSE.COURSEID,
        COURSE.NAME,
        COURSE.CLASSESPERTERMMINIMUM, 
        COURSE.CLASSESPERTERMTARGET,
        COURSE.CLASSESPERTERMMAXIMUM
    from dbo.COURSE
        inner join dbo.V_COURSERESTRICTION as COURSERESTRICTION on COURSERESTRICTION.COURSEID = COURSE.ID
    where SCHOOLID = @SCHOOLID and COURSERESTRICTION.STARTDATE = @STARTDATE
        and COURSERESTRICTION.ENDDATE = @ENDDATE
    order by dbo.UFN_COURSE_GETFIRSTGRADELEVELSEQUENCE(COURSE.ID), COURSE.COURSEID asc

    select 
        @COURSES = (select ID, COURSEID, NAME, CLASSESPERTERMMINIMUM, CLASSESPERTERMTARGET, CLASSESPERTERMMAXIMUM from @temp
            for xml raw('ITEM'),type,elements,root('COURSES'),binary base64),
        @TERMDATA = (select TM.ID, (select COUNT(*)
                    from dbo.CLASS
                    inner join dbo.CLASSMEETINGGROUP on CLASSMEETINGGROUP.CLASSID = CLASS.ID  
                    inner join dbo.TERM on TERM.ID = TM.ID and  
                        TERM.STARTDATE = CLASS.STARTDATE and
                        TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE and 
                        TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                    where CLASS.COURSEID = COURSERESTRICTION.COURSEID) as COUNT_ORIGINAL, 
                    (select COUNT(*)
                    from dbo.CLASS
                    inner join dbo.CLASSMEETINGGROUP on CLASSMEETINGGROUP.CLASSID = CLASS.ID  
                    inner join dbo.TERM on TERM.ID = TM.ID and  
                        TERM.STARTDATE = CLASS.STARTDATE and
                        TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE and 
                        TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                    where CLASS.COURSEID = COURSERESTRICTION.COURSEID) as COUNT_NEW, 
                    (select COUNT(*)
                    from dbo.CLASS
                    inner join dbo.CLASSMEETINGGROUP on CLASSMEETINGGROUP.CLASSID = CLASS.ID 
                    inner join dbo.TERM on TERM.ID = TM.ID and 
                        TERM.STARTDATE = CLASS.STARTDATE and
                        TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE and 
                        TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                    where CLASS.COURSEID = COURSERESTRICTION.COURSEID and
                        exists (select * from dbo.STUDENTCLASSMEETINGGROUP where CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID)
                    ) as NUMWITHSTUDENTS,
                    COURSERESTRICTION.COURSEID
            from dbo.V_COURSERESTRICTION as COURSERESTRICTION 
                inner join dbo.COURSERESTRICTIONSTARTTERM on COURSERESTRICTION.ID = COURSERESTRICTIONSTARTTERM.COURSERESTRICTIONID
                inner join dbo.TERM TM on TM.STARTDATE = COURSERESTRICTIONSTARTTERM.STARTDATE
                    and TM.ENDDATE = COURSERESTRICTIONSTARTTERM.ENDDATE
            where TM.SESSIONID = @SESSIONID
            for xml raw('ITEM'),type,elements,root('TERMDATA'),binary base64), 
        @TERMCOLMETA = (select distinct
                TERM.ID, 
                DESCRIPTION as TERM,
                TERM.STARTDATE
            from TERM 
                inner join dbo.TERMNAMECODE on TERM.TERMNAMECODEID = TERMNAMECODE.ID
                left join dbo.COURSERESTRICTIONSTARTTERM on TERM.STARTDATE = COURSERESTRICTIONSTARTTERM.STARTDATE
                    and TERM.ENDDATE = COURSERESTRICTIONSTARTTERM.ENDDATE
                left join dbo.V_COURSERESTRICTION as COURSERESTRICTION on COURSERESTRICTION.ID = COURSERESTRICTIONSTARTTERM.COURSERESTRICTIONID
            where TERM.SESSIONID = @SESSIONID
            order by TERM.STARTDATE
            for xml raw('ITEM'),type,elements,root('TERMCOLMETA'),binary base64)
    from 
        @Temp as T

    return 0