USP_DATAFORMTEMPLATE_VIEW_EDITSTUDENTSCHEDULE_STUDENTWITHDRAWNCLASSESFORM

The load procedure used by the view dataform template "Edit Student Schedule, Student Withdrawn Classes Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CLASSES	xml	INOUT	CLASSES

Definition

Copy



create procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EDITSTUDENTSCHEDULE_STUDENTWITHDRAWNCLASSESFORM
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CLASSES xml = null output
)
as
    set nocount on;

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

    set @DATALOADED = 1;

    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date

    select
        @SESSION_STARTDATE = min(STARTDATE),
        @SESSION_ENDDATE = max(ENDDATE)
    from dbo.TERM
        inner join dbo.APPUSERSESSION on TERM.SESSIONID = APPUSERSESSION.SESSIONID
    where APPUSERSESSION.ID = @CURRENTAPPUSERID
    group by APPUSERSESSION.ID;

    with CLASSMAP_CTE (COURSEID, SECTION, NAME, TERM_STARTDATE, TERM_ENDDATE, CLASS_STARTDATE, CLASS_ENDDATE, SCHOOLID, ROW)
    as
    (
        select
            COURSE.[COURSEID],
            CLASS.[SECTION],
            COURSE.[NAME],
            CLASSMEETINGGROUP.STARTDATE as TERM_STARTDATE,
            CLASSMEETINGGROUP.ENDDATE as TERM_ENDDATE,
            CLASS.STARTDATE as CLASS_STARTDATE,
            CLASS.ENDDATE as CLASS_ENDDATE,
            COURSE.SCHOOLID,        
            (ROW_NUMBER() over (partition by COURSE.[COURSEID], CLASS.[SECTION] order by CLASSMEETINGGROUP.STARTDATE)) as ROW
        from dbo.STUDENTCOURSE
            inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
            inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
            inner join dbo.CLASS on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                and CLASSMEETINGGROUP.STARTDATE >= @SESSION_STARTDATE
                and CLASSMEETINGGROUP.ENDDATE <= @SESSION_ENDDATE
            inner join dbo.COURSE on CLASS.COURSEID = COURSE.ID
        where STUDENTCOURSE.STUDENTID = @ID
            and STUDENTCLASSMEETINGGROUP.STATUSCODE = 2
    )
    select @CLASSES = (
        select
            CLASSMAP_CTE.[COURSEID] + ' - ' + CLASSMAP_CTE.[SECTION] as CLASSID,
            CLASSMAP_CTE.NAME,
            (select stuff((
                select '; ' + TERMNAMECODE.DESCRIPTION
                from CLASSMAP_CTE LOCAL_CTE
                    inner join dbo.SESSION on SESSION.ID = dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(LOCAL_CTE.SCHOOLID, LOCAL_CTE.CLASS_STARTDATE, LOCAL_CTE.CLASS_ENDDATE)
                    inner join dbo.TERM on TERM.SESSIONID = SESSION.ID
                        and LOCAL_CTE.TERM_STARTDATE = TERM.STARTDATE
                        and LOCAL_CTE.TERM_ENDDATE = TERM.ENDDATE
                    inner join dbo.TERMNAMECODE on TERM.TERMNAMECODEID = TERMNAMECODE.ID
                where LOCAL_CTE.COURSEID = CLASSMAP_CTE.COURSEID
                    and LOCAL_CTE.SECTION = LOCAL_CTE.SECTION
                order by LOCAL_CTE.TERM_STARTDATE
                for xml path(''), type).value('.', 'nvarchar(max)'),1, 2, '')) as TERMS
        from CLASSMAP_CTE
        where CLASSMAP_CTE.ROW = 1
        for xml raw('ITEM'),type,elements,root('CLASSES'),binary base64)

    return 0;