USP_REPORT_CLASSROSTER

create procedure dbo.USP_REPORT_CLASSROSTER(
    @CLASSQUERYID uniqueidentifier = null,
    @CLASSID uniqueidentifier = null,
    @SCHOOLID uniqueidentifier = null,
    @ACADEMICYEARID uniqueidentifier = null,
    @SESSIONID uniqueidentifier = null    
)
as

    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date
    declare @AY_STARTDATE date
    declare @AY_ENDDATE date

    select 
        @AY_STARTDATE = STARTDATE, 
        @AY_ENDDATE = ENDDATE 
    from 
        dbo.ACADEMICYEAR
    where ID = @ACADEMICYEARID

    select 
        @SESSION_STARTDATE = STARTDATE, 
        @SESSION_ENDDATE = ENDDATE 
    from 
        dbo.UFN_SESSION_GETDATES(@SESSIONID)


    declare @CLASSIDS table (ID uniqueidentifier)

    -- single class, or all classes; filter by school and session dates

    if @CLASSQUERYID is null
        insert into @CLASSIDS 
            select CLASS.ID
            from dbo.CLASSMEETINGGROUP
                inner join dbo.CLASS                      
                    on CLASS.ID = CLASSMEETINGGROUP.CLASSID and CLASS.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                inner join dbo.COURSE                     
                    on COURSE.ID = CLASS.COURSEID
            where COURSE.SCHOOLID = @SCHOOLID
                and not (CLASSMEETINGGROUP.ENDDATE < @SESSION_STARTDATE or CLASSMEETINGGROUP.STARTDATE > @SESSION_ENDDATE)
                and (@CLASSID is null or CLASS.ID = @CLASSID)
    else begin
        if not exists(select ID from dbo.IDSETREGISTER where ID = @CLASSQUERYID) 
            raiserror('Selection ID set does not exist in the database.', 15, 1);

        -- filter by selection, school and session dates

        insert into @CLASSIDS (ID) 
            select 
                CLASS.ID
            from 
                dbo.CLASSMEETINGGROUP
            inner join dbo.CLASS                      
                on CLASS.ID = CLASSMEETINGGROUP.CLASSID and CLASS.STARTDATE = CLASSMEETINGGROUP.STARTDATE
            inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@CLASSQUERYID) SELECTION 
                on SELECTION.ID = CLASS.ID
            inner join dbo.COURSE                     
                on COURSE.ID = CLASS.COURSEID
            where COURSE.SCHOOLID = @SCHOOLID
                and not (CLASSMEETINGGROUP.ENDDATE < @SESSION_STARTDATE or CLASSMEETINGGROUP.STARTDATE > @SESSION_ENDDATE)
    end

    select distinct
        CLASS.ID,
        'http://www.blackbaud.com?CLASSRECORDID=' + CONVERT(nvarchar(36), CLASS.ID) as CLASSRECORDID,            
        dbo.UFN_CLASS_GETNAME(CLASS.ID) as CLASSID,
        CLASS.NAME as CLASSNAME,
        (select TERMNAMECODE.DESCRIPTION
            from dbo.TERM
            inner join dbo.TERMNAMECODE 
                on TERM.TERMNAMECODEID = TERMNAMECODE.ID
            where TERM.SESSIONID = @SESSIONID
                and TERM.STARTDATE = CLASS.STARTDATE) as STARTTERM,
        case when PATTERNBLOCKID is null
            then dbo.UFN_CLASSMEETINGTEMPLATE_GETTIMELIST_FROMCLASSMEETINGGROUP(STUDENT_CLASS.CLASSMEETINGGROUPID)
            else PATTERNBLOCK.NAME + ' (' + dbo.UFN_CLASSMEETINGTEMPLATE_GETTIMELIST_FROMCLASSMEETINGGROUP(STUDENT_CLASS.CLASSMEETINGGROUPID) + ')'
        end as MEETINGS,
        'http://www.blackbaud.com?STUDENTID=' + CONVERT(nvarchar(36), STUDENT.ID) as STUDENTID,            
        STUDENT.KEYNAME,
        STUDENT.FIRSTNAME,
        STUDENT.NAME as STUDENTNAME,
        STUDENT.NICKNAME,
        STUDENT.GENDER, 
        (select stuff((select '; ' + CONSTITUENT.NAME
                       from dbo.CONSTITUENT
                       where CONSTITUENT.ID = FACULTYCOURSE.FACULTYID
                       order by CONSTITUENT.NAME    
                       for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')) as FACULTY,
        (select stuff((select '; ' + GRADELEVEL.ABBREVIATION
                       from dbo.SCHOOLGRADELEVEL
                        join dbo.GRADELEVEL on GRADELEVEL.ID = SCHOOLGRADELEVEL.GRADELEVELID
                       where SCHOOLGRADELEVEL.ID = STUDENTPROGRESSION.SCHOOLGRADELEVELID
                       order by GRADELEVEL.SEQUENCE
                       for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')) as GRADELEVEL,
        (select stuff((select '; ' + CONSTITUENT.NAME
                       from dbo.CONSTITUENT
                       where CONSTITUENT.ID = STUDENTADVISOR.FACULTYID
                       order by CONSTITUENT.NAME
                       for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')) as ADVISOR,
        dbo.UFN_CLASS_GETNUMBERENROLLED(CLASSID) as NUMENROLLED,
        dbo.UFN_CLASS_GETSTUDENTCOUNTBYGENDER(CLASS.ID, 1) as NUMMALE,
        dbo.UFN_CLASS_GETSTUDENTCOUNTBYGENDER(CLASS.ID, 2) as NUMFEMALE,
        dbo.UFN_CLASS_GETSTUDENTTERMSTRING(CLASS.ID, STUDENTCOURSEID) as TERMS,
        case when STATUSCODE <> 0 then STATUS else '' end as STATUS
    from 
        @CLASSIDS CLASSES
    inner join dbo.UFN_STUDENT_CLASS(null) as STUDENT_CLASS
        on STUDENT_CLASS.CLASSID = CLASSES.ID and STUDENT_CLASS.SESSIONID = @SESSIONID
    inner join dbo.CLASS                
        on CLASS.ID = STUDENT_CLASS.CLASSID                                                            
    left join dbo.PATTERNBLOCK          
        on PATTERNBLOCK.ID = STUDENT_CLASS.PATTERNBLOCKID
    left join dbo.FACULTYCLASSMEETINGGROUP 
        on FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID = STUDENT_CLASS.CLASSMEETINGGROUPID
    left join dbo.FACULTYCOURSE         
        on FACULTYCOURSE.ID = FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID
    inner join dbo.STUDENTCOURSE              
        on STUDENTCOURSE.ID = STUDENT_CLASS.STUDENTCOURSEID
    inner join dbo.CONSTITUENT as STUDENT     
        on STUDENT.ID = STUDENTCOURSE.STUDENTID
    inner join dbo.EDUCATIONALHISTORY         
        on EDUCATIONALHISTORY.CONSTITUENTID = STUDENT.ID
    inner join dbo.STUDENTPROGRESSION         
        on STUDENTPROGRESSION.ENROLLMENTID = EDUCATIONALHISTORY.ID
    left join dbo.STUDENTADVISOR        
        on STUDENTPROGRESSION.ID = STUDENTADVISOR.STUDENTPROGRESSIONID
    left join dbo.CONSTITUENT as ADVISOR 
        on STUDENTADVISOR.FACULTYID = ADVISOR.ID
    where
        not (STUDENTPROGRESSION.ENDDATE < @AY_STARTDATE or STUDENTPROGRESSION.STARTDATE > @AY_ENDDATE)
    order by 
        CLASS.ID,
        STUDENT.KEYNAME, 
        STUDENT.FIRSTNAME