USP_DATAFORMTEMPLATE_VIEW_CLASS

The load procedure used by the view dataform template "Class View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@COURSEID	nvarchar(60)	INOUT	Course ID
@NAME	nvarchar(60)	INOUT	Name
@STARTTERM	nvarchar(100)	INOUT	Start term
@SECTION	nvarchar(20)	INOUT	Section
@SCHOOL	nvarchar(100)	INOUT	School
@ACADEMICYEAR	nvarchar(100)	INOUT	Academic year
@SESSION	nvarchar(100)	INOUT	Session
@GRADELEVELS	nvarchar(200)	INOUT	Grade levels
@TARGETSIZE	int	INOUT	Target class size
@MAXSIZE	nvarchar(10)	INOUT	Maximum class size
@NUMBERENROLLED	int	INOUT	Number enrolled
@SEATSAVAILABLE	int	INOUT	Seats available
@CURRENTFACULTY	nvarchar(100)	INOUT	CURRENTFACULTY
@CURRENTFACULTYID	uniqueidentifier	INOUT	CURRENTFACULTYID
@CURRENTFACULTY2	nvarchar(100)	INOUT	CURRENTFACULTY2
@CURRENTFACULTYID2	uniqueidentifier	INOUT	CURRENTFACULTYID2
@CURRENTFACULTY3	nvarchar(100)	INOUT	CURRENTFACULTY3
@CURRENTFACULTYID3	uniqueidentifier	INOUT	CURRENTFACULTYID3
@PATTERNBLOCK	nvarchar(100)	INOUT	Block
@FIRSTMEETING	nvarchar(100)	INOUT	First meeting

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CLASS 
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @COURSEID nvarchar(60) = null output,
    @NAME nvarchar(60) = null output,
    @STARTTERM nvarchar(100) = null output,
    @SECTION nvarchar(20) = null output,
    @SCHOOL nvarchar(100) = null output,
    @ACADEMICYEAR nvarchar(100) = null output,
    @SESSION nvarchar(100) = null output,
    @GRADELEVELS nvarchar(200) = null output,
    @TARGETSIZE int = null output,
    @MAXSIZE nvarchar(10) = null output,
    @NUMBERENROLLED int = null output,
    @SEATSAVAILABLE int = null output,
    @CURRENTFACULTY nvarchar(100) = null output,
    @CURRENTFACULTYID uniqueidentifier = null output,
    @CURRENTFACULTY2 nvarchar(100) = null output,
    @CURRENTFACULTYID2 uniqueidentifier = null output,
    @CURRENTFACULTY3 nvarchar(100) = null output,
    @CURRENTFACULTYID3 uniqueidentifier = null output,
    @PATTERNBLOCK nvarchar(100) = null output,
    @FIRSTMEETING nvarchar(100) = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    -- populate the output parameters, which correspond to fields on the form.  Note that

    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

    -- will display a "no data loaded" message.

    select @DATALOADED = 1,
           @COURSEID =  dbo.COURSE.COURSEID, 
           @NAME = dbo.CLASS.NAME,
           @STARTTERM = dbo.UFN_TERMNAMECODE_GETDESCRIPTION(TERM.TERMNAMECODEID),
           @SECTION = dbo.CLASS.SECTION,
           @SCHOOL = dbo.CONSTITUENT.NAME,
           @ACADEMICYEAR = dbo.UFN_ACADEMICYEARNAMECODE_GETDESCRIPTION(ACADEMICYEAR.ACADEMICYEARNAMECODEID),
           @SESSION = dbo.UFN_SESSIONNAMECODE_GETDESCRIPTION(SESSION.SESSIONNAMECODEID),
           @GRADELEVELS = dbo.UFN_COURSE_GETGRADELEVELSTRING(COURSE.ID),
           @TARGETSIZE = CLASS.CLASSSIZETARGET,
           @MAXSIZE = case when CLASS.CLASSSIZEMAXIMUM = 0 then null else Cast(CLASS.CLASSSIZEMAXIMUM as varchar(10)) end,
           @NUMBERENROLLED = dbo.UFN_CLASS_GETNUMBERENROLLED(CLASS.ID),
           @SEATSAVAILABLE = 0,
           @PATTERNBLOCK = PATTERNBLOCK.NAME,
           @FIRSTMEETING = dbo.UFN_CLASSMEETINGGROUP_GETFIRSTMEETING(CLASSMEETINGGROUP.ID)
    from dbo.CLASS
        inner join dbo.CLASSMEETINGGROUP on CLASS.ID = CLASSMEETINGGROUP.CLASSID and CLASS.STARTDATE = CLASSMEETINGGROUP.STARTDATE
        inner join dbo.COURSE on dbo.CLASS.COURSEID = dbo.COURSE.ID
        inner join dbo.ACADEMICYEAR on COURSE.SCHOOLID = ACADEMICYEAR.SCHOOLID
        inner join dbo.CONSTITUENT on COURSE.SCHOOLID = CONSTITUENT.ID
        inner join dbo.SESSION on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID
        inner join dbo.TERM on SESSION.ID = TERM.SESSIONID
            and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
            and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
        left join dbo.PATTERNBLOCK on CLASSMEETINGGROUP.PATTERNBLOCKID = PATTERNBLOCK.ID
    where dbo.CLASS.ID = @ID

    if @DATALOADED = 1
    begin
        if @TARGETSIZE > @NUMBERENROLLED
        begin
            select @SEATSAVAILABLE = @TARGETSIZE - @NUMBERENROLLED
        end

        declare @FACULTYRECORD table
        (
            ID uniqueidentifier,
            NAME nvarchar(154),
            ROW tinyint
        )

        insert into @FACULTYRECORD
        select
            CONSTITUENT.ID,
            CONSTITUENT.NAME,
            ROW_NUMBER() over (order by CONSTITUENT.NAME asc)
        from dbo.CLASSMEETINGGROUP
            inner join dbo.FACULTYCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
            inner join dbo.FACULTYCOURSE on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
            inner join dbo.CONSTITUENT on FACULTYCOURSE.FACULTYID = CONSTITUENT.ID
        where CLASSMEETINGGROUP.CLASSID = @ID
        group by CONSTITUENT.ID, CONSTITUENT.NAME
        order by CONSTITUENT.NAME asc

        select
            @CURRENTFACULTY = NAME,
            @CURRENTFACULTYID = ID
        from @FACULTYRECORD
        where [row] = 1

        select
            @CURRENTFACULTY2 = NAME,
            @CURRENTFACULTYID2 = ID
        from @FACULTYRECORD
        where [row] = 2

        select
            @CURRENTFACULTY3 = NAME,
            @CURRENTFACULTYID3 = ID
        from @FACULTYRECORD
        where [row] = 3
    end

    return 0;