USP_DATAFORMTEMPLATE_VIEW_ENTERREQUESTSBYSTUDENT_STUDENT

The load procedure used by the view dataform template "Enter Requests By Student, Student View"

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.
@STUDENT_NAME	nvarchar(100)	INOUT	STUDENT_NAME
@GRADELEVELID	uniqueidentifier	INOUT	GRADELEVELID
@GRADELEVEL	nvarchar(200)	INOUT	GRADELEVEL
@ADVISOR	nvarchar(1000)	INOUT	ADVISOR
@HOMEROOMTEACHER	nvarchar(1000)	INOUT	HOMEROOMTEACHER
@COURSEREQUESTS	xml	INOUT	COURSEREQUESTS
@HASCORECURRICULUM	bit	INOUT	HASCORECURRICULUM
@GENDER	nvarchar(100)	INOUT	GENDER
@SCHOOLGRADELEVELID	uniqueidentifier	INOUT	SCHOOLGRADELEVELID

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ENTERREQUESTSBYSTUDENT_STUDENT
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @STUDENT_NAME nvarchar(100) = null output,
    @GRADELEVELID uniqueidentifier = null output,
    @GRADELEVEL nvarchar(200) = null output,
    @ADVISOR nvarchar(1000) = null output,
    @HOMEROOMTEACHER nvarchar(1000) = null output,
    @COURSEREQUESTS xml = null output,
    @HASCORECURRICULUM bit = null output,
    @GENDER nvarchar(100) = null output,
    @SCHOOLGRADELEVELID uniqueidentifier = null output
)
as
    set nocount on;

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

    set @DATALOADED = 0;

    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

    exec dbo.USP_STUDENT_GETSCHEDULINGPROFILE
        @ID,
        @SESSION_STARTDATE output,
        @SESSION_ENDDATE output,
        @STUDENT_NAME output,
        @GRADELEVELID output,
        @GRADELEVEL output,
        @ADVISOR output,
        @HOMEROOMTEACHER output,
        @GENDER output,
        @SCHOOLGRADELEVELID output

    if @GRADELEVEL is not null
    begin
        select
            @DATALOADED = 1,
            @COURSEREQUESTS = dbo.UFN_GETSTUDENTCOURSEREQUESTS_TOITEMLISTXML(@CURRENTAPPUSERID, @ID);

        select @HASCORECURRICULUM =
            case when
                exists(
                    select 1
                    from dbo.EDUCATIONALHISTORY
                        inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
                        inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
                        inner join dbo.CORECURRICULUM on SCHOOLGRADELEVEL.ID = CORECURRICULUM.SCHOOLGRADELEVELID
                            or (SCHOOLGRADELEVEL.SCHOOLID = CORECURRICULUM.SCHOOLID and CORECURRICULUM.SCHOOLGRADELEVELID is null)
                    where EDUCATIONALHISTORY.CONSTITUENTID = @ID
                        and not (STUDENTPROGRESSION.ENDDATE < @SESSION_STARTDATE or STUDENTPROGRESSION.STARTDATE > @SESSION_ENDDATE)
                )
                then 1
                else 0
            end
    end    

    return 0;