USP_DATALIST_FAFEVENTREGISTRANTS

List of registrants in an event.

Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@RECORDTYPE	nvarchar(25)	IN	Fundraising roles
@STATUS	nvarchar(2)	IN	Status
@LOOKUPID	nvarchar(100)	IN	Registrant ID
@FIRSTNAME	nvarchar(100)	IN	First Name
@LASTNAME	nvarchar(100)	IN	Last Name
@GROUP	nvarchar(100)	IN
@MAXROWS	int	IN	Input parameter indicating the maximum number of rows to return.
@RECORDTYPECODE	tinyint	IN	Fundraising roles
@SITEFILTERMODE	tinyint	IN
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN
@SECURITYFEATURETYPE	tinyint	IN

Definition

Copy



CREATE procedure dbo.USP_DATALIST_FAFEVENTREGISTRANTS
(
  @EVENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @RECORDTYPE nvarchar(25) = '',
  @STATUS nvarchar(2) = '',
  @LOOKUPID nvarchar(100) = '',
  @FIRSTNAME nvarchar(100) = '',
  @LASTNAME nvarchar(100) = '',
  @GROUP nvarchar(100) = '',
  @MAXROWS int = 1000,
  @RECORDTYPECODE tinyint = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as 
set nocount on;

set @RECORDTYPE = isnull(@RECORDTYPE, '')
set @STATUS = isnull(@STATUS, '')

;With Registrants 
as 
(
    select top (@MAXROWS)
      REGISTRANT.ID as [ID] 
    , REGISTRANT.CONSTITUENTID
    , REGISTRANT.EVENTID
    , REGISTRANT.LOOKUPID 
    , REGISTRANTEXTENSION.TARGETFUNDRAISINGGOAL
    , REGISTRANT.DATEADDED
    , CONSTITUENT.NAME
  , AG.[Role] as [Role] 
    from dbo.REGISTRANT With (NOLOCK)
    inner join    dbo.CONSTITUENT With (NOLOCK) on REGISTRANT.CONSTITUENTID = CONSTITUENT.ID
    left join dbo.REGISTRANTEXTENSION With (NOLOCK) on REGISTRANT.ID = REGISTRANTEXTENSION.REGISTRANTID
  left join dbo.UFN_REGISTRANT_GETFAFROLE (@eventid, NULL) AG on REGISTRANT.ID = AG.REGISTRANTID
    where REGISTRANT.EVENTID = @EVENTID 
  and (AG.RoleCode = @RECORDTYPECODE or @RECORDTYPECODE is null)
    and (REGISTRANT.LOOKUPID like '' + @LOOKUPID + '%' or @LOOKUPID = '')
    and (CONSTITUENT.FIRSTNAME like '' + @FIRSTNAME + '%' or @FIRSTNAME = '')
    and (CONSTITUENT.KEYNAME like '' + @LASTNAME + '%' or @LASTNAME = '')
  and
      (
        @SITEFILTERMODE = 0
        or exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(REGISTRANT.EVENTID) EVENTSITE
            where EVENTSITE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
           )
      )
  -- Check site security
    and (
          exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(REGISTRANT.EVENTID) EVENTSITE
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null))) )
        )
)
, RegistrantBenefits
as
(
    select R.ID, dbo.UDA_BUILDLIST(B1.NAME) AS [Benefits] 
    from Registrants R
    left join dbo.REGISTRANTBENEFIT RBE With (NOLOCK) on RBE.REGISTRANTID = R.ID
    left join    dbo.BENEFIT B1 With (NOLOCK) on RBE.BENEFITID= B1.ID 
    where R.EVENTID = @eventid group by R.ID
)
, Members
as 
(
    select TF.CONSTITUENTID, TX.EVENTID, TFT.NAME
    from dbo.TEAMFUNDRAISINGTEAM TFT With (NOLOCK)
    join dbo.TEAMEXTENSION TX With (NOLOCK) on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
    left join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM With (NOLOCK) on TFT.ID = TFTM.TEAMFUNDRAISINGTEAMID
    left join dbo.TEAMFUNDRAISER TF With (NOLOCK) on TF.ID = TFTM.TEAMFUNDRAISERID
    where TX.EVENTID = @EVENTID and (TFT.NAME like '' + @GROUP + '%' or @GROUP = '')
)


select  
      R.ID as [ID] 
    , R.LOOKUPID 
    , R.NAME 
    , R.[Role] as [Role] 
    , M.NAME as [Group] 
    , R.TARGETFUNDRAISINGGOAL
    , VF.AMOUNTRAISED AS [Total amount raised] 
    , R.DATEADDED as [Date]
    , B.[Benefits] AS [Benefits]
    , EMAILADDRESS.EMAILADDRESS
    , PHONE.NUMBER
    , case when dbo.UFN_REGISTRANT_ISCANCELLED (R.ID)=0 then 'Active' else 'Cancelled' end as [Status]
    , '' as [URL] 
  , 0 as PROGRESSMEMBERECRUITMENTGOAL
  , 0 as PROGRESSDONORRETENTIONGOAL
  , 0 as PROGRESSCOMMUNICATIONGOAL
    from Registrants R
    left join dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_2(@eventid) VF ON VF.CONSTITUENTID = R.CONSTITUENTID /*V_QUERY_REGISTRANT_FUNDRAISINGTOTAL VF on VF.ID = REGISTRANT.ID*/
    left join RegistrantBenefits B ON B.ID = R.ID
    left join dbo.EMAILADDRESS    With (NOLOCK)        on R.CONSTITUENTID = EMAILADDRESS.CONSTITUENTID AND EMAILADDRESS.ISPRIMARY=1
    left join dbo.PHONE          With (NOLOCK)            on R.CONSTITUENTID= PHONE.CONSTITUENTID AND PHONE.ISPRIMARY=1
    left join Members M on M.CONSTITUENTID = R.CONSTITUENTID
    where (M.NAME like '' + @GROUP + '%' or @GROUP = '')