V_QUERY_REGISTRANT
This provides the ability to query for event registrants.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| EVENTID | uniqueidentifier | Event system ID | |
| CONSTITUENTID | uniqueidentifier | yes | Constituent system ID |
| REGISTRANTNAME | nvarchar(700) | yes | Name |
| GUESTOFREGISTRANTID | uniqueidentifier | yes | Guest of registrant system ID |
| ATTENDED | bit | Attended | |
| WILLNOTATTEND | bit | Will not attend | |
| EVENTSEATINGNOTE | nvarchar(250) | Seating note | |
| HOSTNAME | nvarchar(700) | yes | Host name |
| NOOFGUESTS | int | yes | Number of guests |
| ONLINEREGISTRANT | bit | Online registrant | |
| ISGUEST | int | Is guest | |
| BALANCE | money | yes | Balance |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| SECTIONNAME | nvarchar(100) | yes | Section name |
| SECTIONNUMBER | int | yes | Section number |
| SUBSECTIONNAME | nvarchar(100) | yes | Table/row name |
| SUBSECTIONNUMBER | int | yes | Table/row number |
| SEATNAME | nvarchar(100) | yes | Seat name |
| SEATNUMBER | int | yes | Seat number |
| REGISTRANTLOOKUPID | nvarchar(100) | yes | Registrant ID |
| NOTES | nvarchar(255) | Notes | |
| ISCANCELLED | bit | Is canceled | |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| GROUPID | uniqueidentifier | yes | Group ID |
| PARTICIPANTPAGEFRIENDLYURL | nvarchar(100) | yes | Participant page friendly URL |
| TEAMFRIENDLYURL | nvarchar(100) | yes | Team page friendly URL |
| COMPANYFRIENDLYURL | nvarchar(100) | yes | Company page friendly URL |
| HOUSEHOLDFRIENDLYURL | nvarchar(100) | yes | Household page friendly URL |
| REGISTRATIONROLES | varchar(17) | yes | Registration roles |
| WAIVERACCEPTED | bit | yes | Waiver |
| IsStoryPersonalized | int | Story personalized | |
| IsVideoPersonalized | int | Image or video personalized | |
| NumberReturningParticipant | int | yes | Returning participant |
| IsNextYearRegistrant | int | Is next year's registrant | |
| ORGANIZATIONBALANCE | money | yes | Balance (organization currency) |
| REGISTRANTTRAVELID | uniqueidentifier | yes | Registrant travel ID |
| TYPE | varchar(15) | Type | |
| STATUS | varchar(15) | Status | |
| ATTENDED2 | varchar(7) | yes | Attended/No Show |
| REGISTRANTLODGINGID | uniqueidentifier | yes | Registrant lodging ID |
| EVENTGROUPNAME | nvarchar(100) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 2/9/2018 5:32:52 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.174.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REGISTRANT AS
select
REGISTRANT.ID,
REGISTRANT.EVENTID,
REGISTRANT.CONSTITUENTID,
dbo.UFN_REGISTRANT_GETNAME(REGISTRANT.ID) as [REGISTRANTNAME],
REGISTRANT.GUESTOFREGISTRANTID,
REGISTRANT.ATTENDED,
REGISTRANT.WILLNOTATTEND,
REGISTRANT.EVENTSEATINGNOTE,
dbo.UFN_REGISTRANT_GETHOSTNAME(REGISTRANT.ID, REGISTRANT.GUESTOFREGISTRANTID) as [HOSTNAME],
coalesce((select count(GUESTS.ID) from dbo.REGISTRANT as GUESTS where GUESTS.GUESTOFREGISTRANTID = REGISTRANT.ID), 0) as NOOFGUESTS,
REGISTRANT.ONLINEREGISTRANT,
case when REGISTRANT.GUESTOFREGISTRANTID is not null then 1 else 0 end as [ISGUEST],
REGISTRANTBALANCE.BALANCE,
REGISTRANT.DATEADDED,
REGISTRANT.DATECHANGED,
REGISTRANT.TSLONG,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
case SEATING.SUBSECTIONTYPECODE when 2 then
SUBSECTION.NAME
else
SECTION.NAME
end as [SECTIONNAME],
SECTION.SEQUENCE [SECTIONNUMBER],
case when SEATING.SUBSECTIONTYPECODE = 2 then null else SUBSECTION.NAME end as [SUBSECTIONNAME],
case when SEATING.SUBSECTIONTYPECODE = 2 then null else SUBSECTION.SEQUENCE end as [SUBSECTIONNUMBER],
SEAT.NAME [SEATNAME],
SEAT.SEQUENCE [SEATNUMBER],
REGISTRANT.LOOKUPID [REGISTRANTLOOKUPID],
REGISTRANT.NOTES,
REGISTRANT.ISCANCELLED,
EVENT.BASECURRENCYID as [BASECURRENCYID]
,TFT.ID as GROUPID
--Friendly Url
,dbo.UFN_FAF_FRIENDLYURLS(C.ID,0) PARTICIPANTPAGEFRIENDLYURL
,case when TE.TYPECODE=1 then dbo.UFN_FAF_FRIENDLYURLS(C.ID,1) ELSE NULL END AS TEAMFRIENDLYURL
,case when TE.TYPECODE=2 then dbo.UFN_FAF_FRIENDLYURLS(C.ID,2) ELSE NULL END AS COMPANYFRIENDLYURL
,case when TE.TYPECODE=3 then dbo.UFN_FAF_FRIENDLYURLS(C.ID,3) ELSE NULL END AS HOUSEHOLDFRIENDLYURL
--Registration
--,(select Role
--from dbo.UFN_REGISTRANT_GETFAFROLE(REGISTRANT.EVENTID,TFT.ID)
--where REGISTRANTID=REGISTRANT.ID) as REGISTRATIONROLES
,case when TE.ID is not null and TFTC.ID is not null and TE.TYPECODE = 3 then 'Head of household'
when TE.ID is not null and TFTC.ID is null and TE.TYPECODE = 3 then 'Household member'
when TE.ID is not null and TFTC.ID is not null and TE.TYPECODE = 1 then 'Team leader'
when TE.ID is not null and TFTC.ID is null and (TE.TYPECODE = 1) then 'Team member'
when TE.ID is not null and TFTC.ID is not null and TE.TYPECODE = 2 then 'Company leader'
when TE.ID is not null and TFTC.ID is null and (TE.TYPECODE = 2) then 'Individual'
when TE.ID is null then 'Individual' end as [REGISTRATIONROLES]
,RW.ISCURRENT AS WAIVERACCEPTED
, case when story.ID IS NULL THEN 0 ELSE 1 END as IsStoryPersonalized
, case when VIDEO.ID IS NULL THEN 0 ELSE 1 END as IsVideoPersonalized
, (select COUNT(*) from FAFCOMMUNICATIONSLOG F
inner join REGISTRANT R ON R.ID=REGISTRANT.ID AND dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(R.ConstituentID)=F.CLIENTUSERSID
inner join FAFEVENTCOMMUNICATIONCHANNEL fc on f.EMAILJOBID = fc.EMAILJOBID
) as NumberReturningParticipant
, case when NextReg.ID is null then 0 else 1 end as IsNextYearRegistrant
, REGISTRANTBALANCE.[BALANCEINCURRENCY] as [ORGANIZATIONBALANCE],
REGISTRANTTRAVEL.ID [REGISTRANTTRAVELID],
case
when REGISTRANT.ISWALKIN = 1 then 'Walk-in'
when INVITEE.ID is not null then 'Invitation'
else 'Preregistration'
end[TYPE],
case
when INVITEE.DECLINED = 1 AND REGISTRANT.ID is null then 'Declined'
when REGISTRANT.ISCANCELLED = 1 then 'Canceled'
when REGISTRANT.WILLNOTATTEND = 1 then 'Will not attend'
else 'Registered'
end as [STATUS],
case
when REGISTRANT.ATTENDED = 1 then 'Yes'
when REGISTRANT.ISCANCELLED = 1 or REGISTRANT.WILLNOTATTEND = 1 then 'No'
when EVENT.ISPROCESSED = 1 or REGISTRANT.USERMARKEDATTENDANCE = 1 and REGISTRANT.ATTENDED = 0 then 'No-show'
end [ATTENDED2],
REGISTRANTLODGING.ID REGISTRANTLODGINGID,
EVENTGROUP.NAME EVENTGROUPNAME
/*#EXTENSION*/
from
dbo.REGISTRANT
left join dbo.UFN_EVENTREGISTRANT_GETBALANCEINCURRENCY_BULK(null) as REGISTRANTBALANCE on REGISTRANT.ID = REGISTRANTBALANCE.ID -- null indicates ORGANIZATIONCURRENCY
left join dbo.REGISTRANT GUESTOF on GUESTOF.ID = REGISTRANT.GUESTOFREGISTRANTID
-- Bug 263947
outer apply (
select top 1 *
from dbo.INVITEE
where INVITEE.EVENTID = REGISTRANT.EVENTID
and coalesce(GUESTOF.CONSTITUENTID,REGISTRANT.CONSTITUENTID) = INVITEE.CONSTITUENTID
and INVITEE.INVITATIONSENTON is not null
order by INVITEE.INVITATIONSENTON desc
) as INVITEE
--left join dbo.INVITEE INVITEE on INVITEE.EVENTID = REGISTRANT.EVENTID and coalesce(GUESTOF.CONSTITUENTID,REGISTRANT.CONSTITUENTID) = INVITEE.CONSTITUENTID and INVITEE.INVITATIONSENTON is not null
inner join dbo.EVENT as EVENT on EVENT.ID = REGISTRANT.EVENTID
left join dbo.EVENTSEATINGSEAT as SEAT on REGISTRANT.ID = SEAT.REGISTRANTID
left join dbo.EVENTSEATINGSUBSECTION as SUBSECTION on SUBSECTION.ID = SEAT.EVENTSEATINGSUBSECTIONID
left join dbo.EVENTSEATINGSECTION as SECTION on SECTION.ID = SUBSECTION.EVENTSEATINGSECTIONID
left join dbo.EVENTSEATING as SEATING on SEATING.ID = SECTION.EVENTSEATINGID
--Friendly URL
left join dbo.CONSTITUENT C ON C.ID=REGISTRANT.CONSTITUENTID
left outer join dbo.TEAMFUNDRAISER TF on TF.CONSTITUENTID=C.ID AND TF.APPEALID=EVENT.APPEALID
left outer join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISERID =TF.ID
left outer join dbo.TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TFTM.TEAMFUNDRAISINGTEAMID
left outer join dbo.TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID
--Registration
left join dbo.REGISTRATIONWAIVER as RW ON RW.EVENTID=REGISTRANT.EVENTID
left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = REGISTRANT.ADDEDBYID
left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = REGISTRANT.CHANGEDBYID
left join dbo.STORY on STORY.REGISTRANTID=registrant.ID
left join dbo.VIDEO on VIDEO.CONSTITUENTID=REGISTRANT.CONSTITUENTID
--next year's registrant
left join dbo.EVENTEXTENSION NextET on NextET.PRIORYEAREVENTID = REGISTRANT.EVENTID
left join dbo.REGISTRANT NextReg on NextReg.EVENTID = NextET.EVENTID and NextReg.CONSTITUENTID = REGISTRANT.CONSTITUENTID
left join dbo.REGISTRANTTRAVEL on REGISTRANTTRAVEL.MAINEVENTID = coalesce(EVENT.MAINEVENTID, EVENT.ID) and REGISTRANTTRAVEL.REGISTRANTCONSTITUENTID = REGISTRANT.CONSTITUENTID
left join dbo.REGISTRANTLODGING on REGISTRANTLODGING.REGISTRANTCONSTITUENTID = REGISTRANT.CONSTITUENTID and REGISTRANTLODGING.EVENTID = REGISTRANT.EVENTID
--role
left outer join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC on TF.CONSTITUENTID = TFTC.CONSTITUENTID and TE.TEAMFUNDRAISINGTEAMID = TFTC.TEAMFUNDRAISINGTEAMID
left join dbo.EVENTGROUPMEMBER on EVENTGROUPMEMBER.REGISTRANTID = REGISTRANT.ID
left join dbo.EVENTGROUP on EVENTGROUP.ID = EVENTGROUPMEMBER.EVENTGROUPID