USP_DATAFORMTEMPLATE_VIEW_CLASSDETAILPAGEDATA

The load procedure used by the view dataform template "Class Detail Page Data 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.
@COURSE	nvarchar(60)	INOUT	COURSE
@COURSEID	uniqueidentifier	INOUT	COURSEID
@NAME	nvarchar(60)	INOUT	NAME
@MALESTUDENTS	int	INOUT	MALESTUDENTS
@FEMALESTUDENTS	int	INOUT	FEMALESTUDENTS
@SHOWADDCLASS	bit	INOUT	SHOWADDCLASS
@SHOWADDCLASSOVERTARGET	bit	INOUT	SHOWADDCLASSOVERTARGET
@SHOWMEETINGSWITHPERIODS	bit	INOUT	SHOWMEETINGSWITHPERIODS
@SHOWCLASSASSIGNEDBLOCK	bit	INOUT	SHOWCLASSASSIGNEDBLOCK
@SCHOOLID	uniqueidentifier	INOUT	SCHOOLID
@ACADEMICYEARID	uniqueidentifier	INOUT	ACADEMICYEARID
@SESSIONID	uniqueidentifier	INOUT	SESSIONID
@SESSION_HEADER	nvarchar(200)	INOUT	SESSION_HEADER

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CLASSDETAILPAGEDATA
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @COURSE nvarchar(60) = null output,
    @COURSEID uniqueidentifier = null output,
    @NAME nvarchar(60) = null output,
    @MALESTUDENTS int = null output,
    @FEMALESTUDENTS int = null output,
    @SHOWADDCLASS bit = null output,
    @SHOWADDCLASSOVERTARGET bit = null output,
    @SHOWMEETINGSWITHPERIODS bit = null output,
    @SHOWCLASSASSIGNEDBLOCK bit = null output,
    @SCHOOLID uniqueidentifier = null output,
    @ACADEMICYEARID uniqueidentifier = null output,
    @SESSIONID uniqueidentifier = null output,
    @SESSION_HEADER nvarchar(200) = null output
)
as
    set nocount on;

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

    set @DATALOADED = 0;

    declare @TARGETSIZE int
    declare @MAXSIZE int
    declare @NUMBERENROLLED int

    -- 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,
           @COURSE =  dbo.COURSE.NAME, 
           @COURSEID = dbo.COURSE.ID,
           @NAME = COURSE.[COURSEID] + ' - ' + CLASS.[SECTION],
           @TARGETSIZE = CLASS.CLASSSIZETARGET,
           @MAXSIZE = CLASS.CLASSSIZEMAXIMUM,
           @NUMBERENROLLED = dbo.UFN_CLASS_GETNUMBERENROLLED(@ID),
           @MALESTUDENTS = dbo.UFN_CLASS_GETSTUDENTCOUNTBYGENDER(@ID, 1),
           @FEMALESTUDENTS = dbo.UFN_CLASS_GETSTUDENTCOUNTBYGENDER(@ID, 2),
           @SHOWMEETINGSWITHPERIODS = case when TIMETABLE.SCHEDULETIMECODE = 0 then 1 else 0 end,
           @SHOWCLASSASSIGNEDBLOCK = case when COURSERESTRICTION.PATTERNID is null then 0 else 1 end,

           @SCHOOLID = COURSE.SCHOOLID,
           @ACADEMICYEARID = SESSION.ACADEMICYEARID,
           @SESSIONID = SESSION.ID,
           @SESSION_HEADER = case when dbo.UFN_SCHOOL_ISSINGLESCHOOL() = 0 then 
                       dbo.UFN_SCHOOL_GETNAME(COURSE.SCHOOLID) + ', ' else '' end + 
                       dbo.UFN_ACADEMICYEAR_GETNAME(SESSION.ACADEMICYEARID) + ', ' +
                       dbo.UFN_SESSIONNAMECODE_GETDESCRIPTION(SESSION.SESSIONNAMECODEID)
    from dbo.CLASS        
        inner join dbo.COURSE on CLASS.COURSEID = COURSE.ID
        inner join dbo.SESSION on SESSION.ID = dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, CLASS.STARTDATE, CLASS.ENDDATE)
        inner join dbo.TIMETABLE on SESSION.TIMETABLEID = TIMETABLE.ID
        inner join dbo.V_COURSERESTRICTION COURSERESTRICTION on COURSE.ID = COURSERESTRICTION.COURSEID
            and COURSERESTRICTION.STARTDATE <= CLASS.STARTDATE
            and COURSERESTRICTION.ENDDATE >= CLASS.ENDDATE
    where dbo.CLASS.ID = @ID

    if @DATALOADED = 1
    begin
        select
            @SHOWADDCLASS = case when @NUMBERENROLLED < @TARGETSIZE then 1 else 0 end,
            @SHOWADDCLASSOVERTARGET = case when @MAXSIZE <= 0 or @NUMBERENROLLED < @MAXSIZE then 1 else 0 end;
    end

    return 0;