USP_DATAFORMTEMPLATE_VIEW_ADVOCATE

The load procedure used by the view dataform template "Advocate 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.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PICTURE	varbinary	INOUT	PICTURE
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@USERNAME	nvarchar(154)	INOUT	Username
@LASTLOGIN	datetime	INOUT	Last Login
@FEDERALCONGRESSIONALDISTRICT	nvarchar(100)	INOUT	Federal state district code
@STATEUPPER	nvarchar(50)	INOUT	Upper state district code
@STATELOWER	nvarchar(50)	INOUT	Lower state district code
@BOARDMEMBERCONSTITUENCYTEXT	nvarchar(19)	INOUT	BOARDMEMBERCONSTITUENCYTEXT
@STAFFCONSTITUENCYTEXT	nvarchar(12)	INOUT	STAFFCONSTITUENCYTEXT
@FUNDRAISERCONSTITUENCYTEXT	nvarchar(17)	INOUT	FUNDRAISERCONSTITUENCYTEXT
@VOLUNTEERCONSTITUENCYTEXT	nvarchar(16)	INOUT	VOLUNTEERCONSTITUENCYTEXT
@ADVOCATECONSTITUENCYTEXT	nvarchar(16)	INOUT	ADVOCATECONSTITUENCYTEXT
@MYADDRESS	nvarchar(300)	INOUT	MYADDRESS
@CURRENTAPPUSERID	uniqueidentifier	IN
@MEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT
@SOCIALMEDIAACCOUNTS	xml	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ADVOCATE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @PICTURE varbinary(max) = null output,
    @ADDRESS nvarchar(300) = null output,
    @PHONENUMBER nvarchar(100) = null output,
  @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
  @LOOKUPID nvarchar(100) = null output,
  @USERNAME nvarchar(154) = null output,
  @LASTLOGIN datetime = null output,
  @FEDERALCONGRESSIONALDISTRICT nvarchar(100) = null output,
  @STATEUPPER nvarchar(50) = null output,
  @STATELOWER nvarchar(50) = null output,
  @BOARDMEMBERCONSTITUENCYTEXT nvarchar(19) = null output,
  @STAFFCONSTITUENCYTEXT nvarchar(12) = null output,
  @FUNDRAISERCONSTITUENCYTEXT nvarchar(17) = null output,
  @VOLUNTEERCONSTITUENCYTEXT nvarchar(16) = null output,
  @ADVOCATECONSTITUENCYTEXT nvarchar(16) = null output,
  @MYADDRESS nvarchar(300) = null output,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
  @SOCIALMEDIAACCOUNTS xml = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

     SELECT 
          @DATALOADED = 1,
                    @PICTURE = C.PICTURETHUMBNAIL,
                    @USERNAME = cu.USERNAME,
                    @LOOKUPID = C.LOOKUPID,
          @LASTLOGIN = cu.DateLastLogin 
     FROM dbo.CONSTITUENT C  
           Left JOIN dbo.BackOfficeSystemPeople bosp  
              ON bosp.BackofficeRecordID = C.SEQUENCEID
           Left JOIN dbo.BackOfficeSystemUsers bosu      
              ON bosp.ID = bosu.BackofficePeopleID AND bosp.BackOfficeSystemID = 0    
           Left Join dbo.ClientUsers cu    
              ON cu.ID = bosu.ClientUsersID AND bosu.[current] = 1 AND cu.Deleted = 0 
     WHERE
         C.ID = @ID

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

  -- my formatted address
      Select @MYADDRESS = A.ADDRESSBLOCK + '|' + A.CITY + '|' + S.ABBREVIATION + '|' + A.POSTCODE  from dbo.ADDRESS A
          Inner Join dbo.STATE S On A.STATEID = S.ID
          where A.CONSTITUENTID = @ID
           and A.ISPRIMARY = 1


  --PHONE
                SELECT
                  @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER)
                FROM dbo.PHONE
                WHERE 
                  PHONE.CONSTITUENTID = @ID and
                  PHONE.ISPRIMARY = 1;

  --EMAIL
                SELECT 
          @EMAILADDRESS = EMAILADDRESS
                FROM dbo.EMAILADDRESS
                WHERE
                  EMAILADDRESS.CONSTITUENTID = @ID and
                  EMAILADDRESS.ISPRIMARY = 1;

  --SOCIAL MEDIA ACCOUNTS
  select @SOCIALMEDIAACCOUNTS = dbo.UFN_CONSTITUENT_GETSOCIALMEDIAACCOUNTS_TOITEMLISTXML(@ID);

  -- get district info
  If Exists(Select top 1 ID from ADVOCACYLOG Where CONSTITUENTID = @ID)
    Begin
       Select top 1 @FEDERALCONGRESSIONALDISTRICT = FEDERALDISTRICTCODE, 
              @STATEUPPER = UPPERSTATEDISTRICTCODE, @STATELOWER = LOWERSTATEDISTRICTCODE 
       From ADVOCACYLOG Where CONSTITUENTID = @ID
       Order By DateAdded desc
    End
  Else
     Begin
        Select Top 1 @FEDERALCONGRESSIONALDISTRICT = FEDERALDISTRICTCODE, 
              @STATEUPPER = UPPERSTATEDISTRICTCODE, @STATELOWER = LOWERSTATEDISTRICTCODE 
        From ADVOCACYPETITION Where CONSTITUENTID = @ID
        Order By DateAdded desc
     End

   -- constituent actions
   set    @BOARDMEMBERCONSTITUENCYTEXT = case
                                                    when dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF') --Board member
                                                    else null end;
      set @STAFFCONSTITUENCYTEXT = case
                                                when dbo.UFN_CONSTITUENT_ISSTAFF(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467') --Staff
                                                else null end;
   set @FUNDRAISERCONSTITUENCYTEXT = case
                                                    when dbo.UFN_CONSTITUENT_ISFUNDRAISER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D2DCA06A-BE6E-40B3-B95D-59A926181923') --Fundraiser
                                                    else null end;

   set @VOLUNTEERCONSTITUENCYTEXT = case
                                                    when dbo.UFN_CONSTITUENT_ISVOLUNTEER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E7489703-3D63-4017-A2BC-88C092563C5D') --Volunteer
                                                    else null end;

    set @MEMBERCONSTITUENCYTEXT = case
                                                    when dbo.UFN_CONSTITUENT_ISMEMBER(@ID,@CURRENTAPPUSERID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2d11326e-8f3b-4322-9797-57c1aacfa5df')  --Member
                                                    else null end;

   --set @ADVOCATECONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C6899') -- advocate
   set @ADVOCATECONSTITUENCYTEXT = ''

  return 0;