USP_DATAFORMTEMPLATE_VIEW_STUDENTPROFILE

The load procedure used by the view dataform template "Student Summary Profile 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
@PRIMARYBUSINESS	nvarchar(100)	INOUT	Primary business
@PRIMARYBUSINESSID	uniqueidentifier	INOUT	PRIMARYBUSINESSID
@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
(
    @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,
    @PRIMARYBUSINESS nvarchar(100) = null output,
    @PRIMARYBUSINESSID 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;

    --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 = @ID;

    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 = @ID and
        EMAILADDRESS.ISPRIMARY = 1;

    --PHONE

    SELECT
        @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, 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 = @ID 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 = @ID 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=@ID;

    --CURRENT STUDENT PROGRESSION INFORMATION

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

    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(@ID, 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))


    -- primary business, if available

    select
        @PRIMARYBUSINESSID = [ORG].ID,
        @PRIMARYBUSINESS = [ORG].KEYNAME
    from
        dbo.RELATIONSHIP
        inner join dbo.CONSTITUENT as [ORG] on [ORG].ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
    where 
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
        and    RELATIONSHIP.ISPRIMARYBUSINESS = 1;