USP_DATAFORMTEMPLATE_VIEW_STUDENTPROFILE_BYEDUCATIONALHISTORY

The load procedure used by the view dataform template "Student Summary Profile By Educational History View 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.
@ISCONSTITUENTPROFILE	bit	INOUT	ISCONSTITUENTPROFILE
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@PHONETYPE	nvarchar(100)	INOUT	PHONETYPE
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@DONOTPHONE	bit	INOUT	DONOTPHONE
@WEBADDRESS	UDT_WEBADDRESS	INOUT	WEBADDRESS
@ISINACTIVE	bit	INOUT	ISINACTIVE
@PICTURE	varbinary	INOUT	PICTURE
@LOOKUPID	nvarchar(100)	INOUT	Student ID
@DECEASEDDATE	UDT_FUZZYDATE	INOUT	DECEASEDDATE
@DECEASED	bit	INOUT	DECEASED
@CURRENTGRADELEVEL	nvarchar(200)	INOUT	Grade level
@CURRENTSTATUS	nvarchar(200)	INOUT	Current status
@CURRENTSCHOOL	nvarchar(100)	INOUT	CURRENTSCHOOL
@CURRENTENROLLMENTID	uniqueidentifier	INOUT	CURRENTENROLLMENTID
@CURRENTSCHOOL2	nvarchar(100)	INOUT	CURRENTSCHOOL2
@CURRENTENROLLMENTID2	uniqueidentifier	INOUT	CURRENTENROLLMENTID2
@CURRENTSCHOOL3	nvarchar(100)	INOUT	CURRENTSCHOOL3
@CURRENTENROLLMENTID3	uniqueidentifier	INOUT	CURRENTENROLLMENTID3
@CURRENTADVISOR	nvarchar(154)	INOUT	CURRENTADVISOR
@CURRENTADVISORID	uniqueidentifier	INOUT	CURRENTADVISORID
@CURRENTADVISOR2	nvarchar(154)	INOUT	CURRENTADVISOR2
@CURRENTADVISORID2	uniqueidentifier	INOUT	CURRENTADVISORID2
@CURRENTADVISOR3	nvarchar(154)	INOUT	CURRENTADVISOR3
@CURRENTADVISORID3	uniqueidentifier	INOUT	CURRENTADVISORID3
@CURRENTHOMEROOMTEACHER	nvarchar(154)	INOUT	CURRENTHOMEROOMTEACHER
@CURRENTHOMEROOMTEACHERID	uniqueidentifier	INOUT	CURRENTHOMEROOMTEACHERID
@CURRENTHOMEROOMTEACHER2	nvarchar(154)	INOUT	CURRENTHOMEROOMTEACHER2
@CURRENTHOMEROOMTEACHERID2	uniqueidentifier	INOUT	CURRENTHOMEROOMTEACHERID2
@CURRENTHOMEROOMTEACHER3	nvarchar(154)	INOUT	CURRENTHOMEROOMTEACHER3
@CURRENTHOMEROOMTEACHERID3	uniqueidentifier	INOUT	CURRENTHOMEROOMTEACHERID3
@INACTIVITYREASON	nvarchar(63)	INOUT	Inactive reason
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL
@ADDRESSISCONFIDENTIAL	bit	INOUT	ADDRESSISCONFIDENTIAL
@ADDRESSID	uniqueidentifier	INOUT	ADDRESSID
@PHONENUMBERID	uniqueidentifier	INOUT	PHONENUMBERID
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID

Definition

Copy



            CREATE  procedure [dbo].[USP_DATAFORMTEMPLATE_VIEW_STUDENTPROFILE_BYEDUCATIONALHISTORY]

                @ID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier=null,
                @DATALOADED bit = 0 output,
                @ISCONSTITUENTPROFILE bit = null output,
                @ADDRESS nvarchar(300) = null output,
                @PHONENUMBER nvarchar(100) = null output,
                @PHONETYPE nvarchar(100) = null output,
                @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                @DONOTMAIL bit = null output,
                @DONOTEMAIL bit = null output,
                @DONOTPHONE bit = null output,
                @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
                @ISINACTIVE bit = null output,
                @PICTURE varbinary(max) = null output,
                @LOOKUPID nvarchar(100) = null output,
                @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
                @DECEASED bit = null output,
                @CURRENTGRADELEVEL nvarchar(200) = null output,
                @CURRENTSTATUS nvarchar(200) = null output,
                @CURRENTSCHOOL nvarchar(100) = null output,
                @CURRENTENROLLMENTID uniqueidentifier = null output,
                @CURRENTSCHOOL2 nvarchar(100) = null output,
                @CURRENTENROLLMENTID2 uniqueidentifier = null output,
                @CURRENTSCHOOL3 nvarchar(100) = null output,
                @CURRENTENROLLMENTID3 uniqueidentifier = null output,
                @CURRENTADVISOR nvarchar(154) = null output,
                @CURRENTADVISORID uniqueidentifier = null output,
                @CURRENTADVISOR2 nvarchar(154) = null output,
                @CURRENTADVISORID2 uniqueidentifier = null output,
                @CURRENTADVISOR3 nvarchar(154) = null output,
                @CURRENTADVISORID3 uniqueidentifier = null output,
                @CURRENTHOMEROOMTEACHER nvarchar(154) = null output,
                @CURRENTHOMEROOMTEACHERID uniqueidentifier = null output,
                @CURRENTHOMEROOMTEACHER2 nvarchar(154) = null output,
                @CURRENTHOMEROOMTEACHERID2 uniqueidentifier = null output,
                @CURRENTHOMEROOMTEACHER3 nvarchar(154) = null output,
                @CURRENTHOMEROOMTEACHERID3 uniqueidentifier = null output,
                @INACTIVITYREASON nvarchar(63) = null output,
                @PHONEISCONFIDENTIAL bit = null output,
                @ADDRESSISCONFIDENTIAL bit = null output,
                @ADDRESSID uniqueidentifier = null output,
                @PHONENUMBERID uniqueidentifier = null output,
                @EMAILADDRESSID uniqueidentifier = null output                
     as
                set nocount on;

                set @DATALOADED = 0;

                declare @CONSTITUENTID uniqueidentifier;
                select @CONSTITUENTID = CONSTITUENT.ID
                from dbo.CONSTITUENT
                    inner join dbo.EDUCATIONALHISTORY
                        on EDUCATIONALHISTORY.CONSTITUENTID = CONSTITUENT.ID
                where EDUCATIONALHISTORY.ID = @ID;

                --CONSTIT FIELDS

                select
                    @DATALOADED = 1,
                    @WEBADDRESS = CONSTITUENT.WEBADDRESS,
                    @PICTURE = CONSTITUENT.PICTURETHUMBNAIL,
                    @ISINACTIVE = CONSTITUENT.ISINACTIVE,
                    @LOOKUPID = CONSTITUENT.LOOKUPID,
                    @ISCONSTITUENTPROFILE = 1
                from
                    dbo.CONSTITUENT
                where
                    CONSTITUENT.ID = @CONSTITUENTID;

                if @ISINACTIVE = 1
                    select @INACTIVITYREASON = dbo.UFN_CONSTITUENTINACTIVITYREASONCODE_TRANSLATE(CONSTITUENTINACTIVEDETAIL.CONSTITUENTINACTIVITYREASONCODEID)
                    from dbo.CONSTITUENTINACTIVEDETAIL
                    where ID = @ID                

                --EMAIL

                SELECT 
                    @EMAILADDRESS = EMAILADDRESS,
                    @DONOTEMAIL = EMAILADDRESS.DONOTEMAIL,
                    @EMAILADDRESSID = EMAILADDRESS.ID
                FROM dbo.EMAILADDRESS
                WHERE
                    EMAILADDRESS.CONSTITUENTID = @CONSTITUENTID and
                    EMAILADDRESS.ISPRIMARY = 1;

                --PHONE

                SELECT
                    @PHONENUMBER = PHONE.NUMBER,
                    @PHONETYPE = (select DESCRIPTION FROM dbo.PHONETYPECODE WHERE ID = PHONE.PHONETYPECODEID),
                    @DONOTPHONE = PHONE.DONOTCALL,
                    @PHONEISCONFIDENTIAL = PHONE.ISCONFIDENTIAL,
                    @PHONENUMBERID = PHONE.ID
                FROM dbo.PHONE
                WHERE 
                    PHONE.CONSTITUENTID = @CONSTITUENTID and
                    PHONE.ISPRIMARY = 1;

                --ADDRESS

                select 
                    @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
                    @DONOTMAIL = ADDRESS.DONOTMAIL,
                    @ADDRESSISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
                    @ADDRESSID = ADDRESS.ID
                from
                    dbo.ADDRESS
                where
                    ADDRESS.CONSTITUENTID = @CONSTITUENTID and
                    ADDRESS.ISPRIMARY = 1;

                --DECEASED

                SELECT 
                @DECEASEDDATE=DECEASEDDATE,
                @DECEASED = case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end
                FROM
                dbo.DECEASEDCONSTITUENT
                WHERE ID=@CONSTITUENTID;

                --CURRENT STUDENT ENROLLMENT INFORMATION

                set @CURRENTGRADELEVEL = dbo.UFN_STUDENTCURRENTGRADELEVEL_GETNAME(@CONSTITUENTID);
                set @CURRENTSTATUS = dbo.UFN_STUDENTCURRENTSTATUS_GETNAME(@CONSTITUENTID);

                declare @CURR_INFO TABLE
               (
                ENROLLMENTID    uniqueidentifier,
                SCHOOLID   uniqueidentifier,
                SCHOOL nvarchar(100),
                HOMEROOMTEACHERID uniqueidentifier,
                HOMEROOMTEACHER nvarchar(154),
                ADVISORID uniqueidentifier,
                ADVISOR nvarchar(154),
                ROW tinyint
               )

               insert into @CURR_INFO 
               select [ENROLLMENTID], [SCHOOLID], [SCHOOL], [HOMEROOMTEACHERID], [HOMEROOMTEACHER], [ADVISORID], [ADVISOR], [ROW]
               from dbo.UFN_STUDENT_CURRENTENROLLMENTINFO(@CONSTITUENTID, 1) 

                select
                    @CURRENTENROLLMENTID = CURR_INFO.[ENROLLMENTID],
                    @CURRENTSCHOOL = CURR_INFO.[SCHOOL],
                    @CURRENTADVISORID = CURR_INFO.[ADVISORID],
                    @CURRENTADVISOR = CURR_INFO.[ADVISOR],
                    @CURRENTHOMEROOMTEACHERID = CURR_INFO.[HOMEROOMTEACHERID],
                    @CURRENTHOMEROOMTEACHER = CURR_INFO.[HOMEROOMTEACHER]
                from
                    @CURR_INFO as CURR_INFO
                where 
                    CURR_INFO.[ROW] = 1

                select 
                    @CURRENTENROLLMENTID2 = CURR_INFO.[ENROLLMENTID],
                    @CURRENTSCHOOL2 = CURR_INFO.[SCHOOL]
                from
                    @CURR_INFO as CURR_INFO
                where 
                    (CURR_INFO.[ROW] = 2) and (CURR_INFO.[SCHOOLID] <>  @CURRENTENROLLMENTID)

                select
                    @CURRENTADVISORID2 = CURR_INFO.[ADVISORID],
                    @CURRENTADVISOR2 = CURR_INFO.[ADVISOR]
                from
                    @CURR_INFO as CURR_INFO
                where 
                    (CURR_INFO.[ROW] = 2) and (CURR_INFO.[ADVISORID] <>  @CURRENTADVISORID)

                select
                    @CURRENTHOMEROOMTEACHERID2 = CURR_INFO.[HOMEROOMTEACHERID],
                    @CURRENTHOMEROOMTEACHER2 = CURR_INFO.[HOMEROOMTEACHER]
                from
                    @CURR_INFO as CURR_INFO
                where 
                    (CURR_INFO.[ROW] = 2) and (CURR_INFO.[HOMEROOMTEACHERID] <>  @CURRENTHOMEROOMTEACHERID)

                select 
                    @CURRENTENROLLMENTID3 = CURR_INFO.[ENROLLMENTID],
                    @CURRENTSCHOOL3 = CURR_INFO.[SCHOOL]
                from
                    @CURR_INFO as CURR_INFO
                where 
                    (CURR_INFO.[ROW] = 3) and (CURR_INFO.[SCHOOLID] NOT IN (@CURRENTENROLLMENTID, @CURRENTENROLLMENTID2)) 

                select
                    @CURRENTADVISORID3 = CURR_INFO.[ADVISORID],
                    @CURRENTADVISOR3 = CURR_INFO.[ADVISOR]
                from
                    @CURR_INFO as CURR_INFO
                where 
                    (CURR_INFO.[ROW] = 3) and (CURR_INFO.[ADVISORID] NOT IN (@CURRENTADVISORID, @CURRENTADVISORID2))

                select
                    @CURRENTHOMEROOMTEACHERID3 = CURR_INFO.[HOMEROOMTEACHERID],
                    @CURRENTHOMEROOMTEACHER3 = CURR_INFO.[HOMEROOMTEACHER]
                from
                    @CURR_INFO as CURR_INFO
                where 
                    (CURR_INFO.[ROW] = 3) and (CURR_INFO.[HOMEROOMTEACHERID] NOT IN (@CURRENTHOMEROOMTEACHERID, @CURRENTHOMEROOMTEACHERID2))

                return 0;