USP_DATAFORMTEMPLATE_VIEW_PROFILEFIELDSDATA

The load procedure used by the view dataform template "Constituent 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.
@LASTNAME	nvarchar(200)	INOUT	LastName
@FIRSTNAME	nvarchar(100)	INOUT	FirstName
@MIDDLENAME	nvarchar(100)	INOUT	MiddleName
@TITLE	nvarchar(20)	INOUT	Title
@SUFFIX	nvarchar(20)	INOUT	Suffix
@NICKNAME	nvarchar(20)	INOUT	NickName
@MAIDENNAME	nvarchar(20)	INOUT	MaidenName
@BIRTHDATE	nvarchar(20)	INOUT	BirthDate
@GENDER	nvarchar(20)	INOUT	Gender
@MARITALSTATUS	nvarchar(20)	INOUT	MaritalStatus
@ADDRESSTYPE	nvarchar(20)	INOUT	AddressType
@COUNTRY	nvarchar(200)	INOUT	Country
@ADDRESS	nvarchar(200)	INOUT	Address
@CITY	nvarchar(200)	INOUT	City
@STATE	nvarchar(200)	INOUT	State
@POSTCODE	nvarchar(20)	INOUT	PostCode
@DONOTMAIL	nvarchar(20)	INOUT	DoNotMail
@DONOTMAILREASON	nvarchar(200)	INOUT	DoNotMailReason
@PHONETYPE	nvarchar(20)	INOUT	PhoneType
@PHONENUMBER	nvarchar(20)	INOUT	PhoneNumber
@EMAILTYPE	nvarchar(200)	INOUT	EmailType
@EMAILADDRESS	nvarchar(200)	INOUT	EmailAddress
@USERNAME	nvarchar(200)	INOUT	UserName
@PASSWORD	nvarchar(200)	INOUT	Password
@NAME	nvarchar(308)	INOUT	NAME
@BILLINGADDRESS	nvarchar(200)	INOUT	Billing address
@BILLINGCOUNTRY	nvarchar(100)	INOUT	Billing country
@BILLINGCITY	nvarchar(50)	INOUT	Billing city
@BILLINGSTATE	nvarchar(100)	INOUT	Billing state
@BILLINGZIP	nvarchar(12)	INOUT	Billing ZIP
@ENEWSLETTERSIGNUP	nvarchar(1000)	INOUT	eNewsletter sign-up
@SHIPPINGADDRESS	nvarchar(200)	INOUT	Shipping Address
@SHIPPINGCITY	nvarchar(50)	INOUT	Shipping city
@SHIPPINGSTATE	nvarchar(100)	INOUT	Shipping state
@SHIPPINGZIP	nvarchar(12)	INOUT	Shipping ZIP
@LASTLOGINDATE	datetime	INOUT	Last login date
@COMMENTS	nvarchar(2000)	INOUT	Comments
@RELATEDORGANIZATION	nvarchar(200)	INOUT	Related Organization
@MANGEEMAILPREFERENCESURL	nvarchar(500)	INOUT	Mange email preferences URL
@CONSTITUENTID	uniqueidentifier	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROFILEFIELDSDATA(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @LASTNAME nvarchar(200) = null output,
    @FIRSTNAME nvarchar(100) = null output,
    @MIDDLENAME nvarchar(100) = null output,
    @TITLE nvarchar(20)= null output,
    @SUFFIX nvarchar(20)= null output,
    @NICKNAME nvarchar(20)= null output,
    @MAIDENNAME nvarchar(20)= null output,
    @BIRTHDATE nvarchar(20)= null output,
    @GENDER nvarchar(20)= null output,
    @MARITALSTATUS nvarchar(20)= null output,
    @ADDRESSTYPE nvarchar(20)= null output,
    @COUNTRY nvarchar(200)= null output,
    @ADDRESS nvarchar(200)= null output,
    @CITY nvarchar(200)= null output,
    @STATE nvarchar(200)= null output,
    @POSTCODE nvarchar(20)= null output,
    @DONOTMAIL nvarchar(20)= null output,
    @DONOTMAILREASON nvarchar(200)= null output,
    @PHONETYPE nvarchar(20)= null output,
    @PHONENUMBER nvarchar(20)= null output,
    @EMAILTYPE nvarchar(200)= null output,
    @EMAILADDRESS nvarchar(200)= null output,
  @USERNAME nvarchar(200)=null output,
  @PASSWORD nvarchar(200)=null output,
  @NAME nvarchar(308) = null output,
  @BILLINGADDRESS nvarchar(200) = null output,
  @BILLINGCOUNTRY nvarchar(100) = null output,
  @BILLINGCITY nvarchar(50) = null output,
  @BILLINGSTATE nvarchar(100) = null output,
  @BILLINGZIP nvarchar(12) = null output,
  @ENEWSLETTERSIGNUP nvarchar(1000) = null output,
  @SHIPPINGADDRESS nvarchar(200) = null output,
  @SHIPPINGCITY nvarchar(50) = null output,
  @SHIPPINGSTATE nvarchar(100) = null output,
  @SHIPPINGZIP nvarchar(12) = null output,
  @LASTLOGINDATE DATETIME = null output,
  @COMMENTS NVARCHAR(2000) = null output,
  @RELATEDORGANIZATION nvarchar(200)= null output,
  @MANGEEMAILPREFERENCESURL nvarchar(500)=null output,
  @CONSTITUENTID uniqueidentifier = null output
)
as
    set nocount on;

  set @DATALOADED = 0;

declare @EVENTID uniqueidentifier
declare @EmailPreferencePageID int   --get the manager Preference Page  url
declare @ClientSitesID int

-- get the event id
if @EVENTID is null
    select @EVENTID = EVENTID from REGISTRANT WHERE CONSTITUENTID = @ID 
if @EVENTID is null
    select @EVENTID = EVENTID FROM EVENTSPONSOR WHERE CONSTITUENTID = @ID 

--get the ClientSitesID
if @EVENTID is not null
    select @ClientSitesID = CLIENTSITESID from EVENTEXTENSION where EVENTID  = @EVENTID 

--get the enews letter sign up   
set @ENEWSLETTERSIGNUP = '';
select @ENEWSLETTERSIGNUP = @ENEWSLETTERSIGNUP + 
    case when @ENEWSLETTERSIGNUP = '' then ET.NAME else '; ' + ET.NAME end
from dbo.EmailList_Subscription ES
INNER JOIN dbo.EmailList EL ON EL.ID=ES.EmailListID
INNER JOIN dbo.EmailTemplate_EmailList ETEL ON ETEL.EmailListID=EL.ID
inner join dbo.EmailTemplate ET ON ET.ID=ETEL.EmailTemplateID
where ES.UserID = dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(@ID)    
group by ET.Name
order by ET.Name;

--get username and password
SELECT  @ClientSitesID = CLIENTSITESID
FROM dbo.EVENTEXTENSION EE 
INNER JOIN REGISTRANT REG ON REG.EVENTID=EE.EVENTID and REG.CONSTITUENTID=@ID 

--get the email preference page id 
select top 1 @EmailPreferencePageID = VPC.PageID
from vwPageContent VPC
inner join dbo.SitePages SP on SP.ID = VPC.PageID
inner join dbo.EVENTEXTENSION EE ON EE.CLIENTSITESID = SP.ClientSitesID 
LEFT OUTER JOIN
    (select PAGEMODELITEM.OBJECTGUID
    from dbo.PAGEMODELITEM 
    inner join dbo.PAGEMODEL on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
    where PAGEMODEL.LOCKARTIFACTS = 1
    ) PMI on PMI.OBJECTGUID = VPC.PageGuid    
inner join dbo.APEXFORMCONTENT AFC ON AFC.ID = VPC.Guid
inner join dbo.APEXFORM AF ON AF.ID = AFC.FORMID AND AF.FORMTYPEID = '89D3C4CC-4D01-4E8B-B82E-04AD966EEE09'     
WHERE EE.EVENTID = @EVENTID
    and VPC.IsTemplatePage = 0
    and VPC.ContentTypesID = 121
    and (PMI.OBJECTGUID is null)

--get username and password
declare @ClientUserID int
select @ClientUserID = dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(@ID)
if exists (select * from dbo.CLIENTUSERSEXTENSION where CLIENTUSERID = @ClientUserID)
    begin
        SELECT @USERNAME =COALESCE(PREFERREDUSERNAME, DISPLAYNAME, FORMATTEDNAME, '') + ' with ' + PROVIDERNAME 
        FROM CLIENTUSERSEXTENSION
        WHERE CLIENTUSERID = @ClientUserID
        set @PASSWORD = ''
    end    
else
    begin
        select @USERNAME = UserName from dbo.clientusers where ID= @ClientUserID
        select @PASSWORD = Password from dbo.clientusers where ID= @ClientUserID
    end

select  
    @CONSTITUENTID = C.ID,
    @DATALOADED = 1,
    @LASTNAME=C.KEYNAME,
        @FIRSTNAME=C.FIRSTNAME,
        @MIDDLENAME=C.MIDDLENAME,
        @TITLE=TC.DESCRIPTION,
        @SUFFIX=SC.DESCRIPTION,
        @NICKNAME=C.NICKNAME,
        @MAIDENNAME=C.MAIDENNAME,
        @BIRTHDATE=(CASE C.BIRTHDATE WHEN '00000000' THEN NULL ELSE CONVERT(nvarchar(10), CAST(C.BIRTHDATE as datetime),101) END),
        @GENDER=(CASE  C.GENDERCODE WHEN (1) THEN 'Male' WHEN (2) THEN 'Female' ELSE 'Unknown' END),
        @MARITALSTATUS=MSC.DESCRIPTION,
        @ADDRESSTYPE=AT.DESCRIPTION,
        @COUNTRY=COUNTRY.DESCRIPTION,
        @ADDRESS=ADDR.ADDRESSBLOCK,
        @CITY=ADDR.CITY,
        @STATE=STATE.DESCRIPTION,
        @POSTCODE=ADDR.POSTCODE,
        @DONOTMAIL=(CASE ADDR.DONOTMAIL WHEN (1) then 'Yes' else 'No' End),
        @DONOTMAILREASON=DNMRC.DESCRIPTION,
        @PHONETYPE=PT.DESCRIPTION,
        @PHONENUMBER=P.NUMBER,
        @EMAILTYPE=EAC.DESCRIPTION,
        @EMAILADDRESS=EA.EMAILADDRESS,
    @NAME = C.NAME,
    @BILLINGADDRESS = case AT.DESCRIPTION when 'billing' then ADDR.ADDRESSBLOCK else '' end,
    @BILLINGCOUNTRY = case AT.DESCRIPTION when 'billing' then COUNTRY.DESCRIPTION else '' end,
    @BILLINGCITY = case AT.DESCRIPTION when 'billing' then ADDR.CITY else '' end,
    @BILLINGSTATE = case AT.DESCRIPTION when 'billing' then STATE.DESCRIPTION  else '' end,
    @BILLINGZIP = case AT.DESCRIPTION when 'billing' then ADDR.POSTCODE else '' end,
    @SHIPPINGADDRESS = case AT.DESCRIPTION when 'shipping' then ADDR.ADDRESSBLOCK else '' end,
    @SHIPPINGCITY = case AT.DESCRIPTION when 'shipping' then ADDR.CITY else '' end,
    @SHIPPINGSTATE = case AT.DESCRIPTION when 'shipping' then STATE.DESCRIPTION else '' end,
    @SHIPPINGZIP = case AT.DESCRIPTION when 'shipping' then ADDR.POSTCODE else '' end,
    @LASTLOGINDATE=(select DateLastLogin from dbo.clientusers where ID= dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(c.ID)),
    @COMMENTS=FC.COMMENTTEXT,
    @RELATEDORGANIZATION='',
    @MANGEEMAILPREFERENCESURL=cmss3.VALUE +'page.aspx?pid=' + CAST(@EmailPreferencePageID as nvarchar(10)) + '&frmfaf=1' -- to identify that the link is in a faf email
  From CONSTITUENT C
      left JOIN dbo.REGISTRANT R on R.CONSTITUENTID=C.ID 
      LEFT JOIN TITLECODE TC ON C.TITLECODEID=TC.ID 
      LEFT JOIN SUFFIXCODE SC ON C.SUFFIXCODEID=SC.ID
      LEFT JOIN MARITALSTATUSCODE MSC ON C.MARITALSTATUSCODEID=MSC.ID
      LEFT JOIN ADDRESS ADDR ON C.ID=ADDR.CONSTITUENTID 
      LEFT JOIN ADDRESSTYPECODE AT ON ADDR.ADDRESSTYPECODEID=AT.ID 
      LEFT JOIN COUNTRY ON ADDR.COUNTRYID=COUNTRY.ID 
      LEFT JOIN STATE ON ADDR.STATEID=STATE.ID 
      LEFT JOIN DONOTMAILREASONCODE DNMRC ON ADDR.DONOTMAILREASONCODEID=DNMRC.ID 
      LEFT JOIN PHONE P ON P.CONSTITUENTID=C.ID AND P.ISPRIMARY=1
      LEFT JOIN PHONETYPECODE PT ON P.PHONETYPECODEID=PT.ID 
      LEFT JOIN EMAILADDRESS EA ON EA.CONSTITUENTID=C.ID AND EA.ISPRIMARY=1
      LEFT JOIN EMAILADDRESSTYPECODE EAC ON EA.EMAILADDRESSTYPECODEID=EAC.ID 
    LEFT JOIN dbo.FAFCOMMENTS FC ON FC.REGISTRANTID=R.ID AND FC.EVENTID=R.EVENTID
    left join dbo.TEAMFUNDRAISER TF ON TF.CONSTITUENTID=C.ID 
    left join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISERID=TF.ID
    left join dbo.TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TFTM.TEAMFUNDRAISINGTEAMID 
    left join dbo.CMSSITESETTING CMSS3 on  CMSS3.CLIENTSITESID=@ClientSitesID and CMSS3.ENUMID = 11
    WHERE C.ID=@ID  
    if LEN(@CITY)>0 and LEN(@STATE)>0
      SET @CITY=@CITY + ','
  RETURN 0