V_QUERY_CONSTITUENTREGISTRANT
This provides the ability to query for a constituent's registrant records.
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 |
| 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 |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 2/9/2018 5:32:53 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.174.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTREGISTRANT 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 as [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]
/*#EXTENSION*/
, 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
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
--Event
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 SEATING on SEATING.ID = SECTION.EVENTSEATINGID
left join dbo.REGISTRANTTRAVEL on REGISTRANTTRAVEL.MAINEVENTID = coalesce(EVENT.MAINEVENTID, EVENT.ID) and REGISTRANTTRAVEL.REGISTRANTCONSTITUENTID = REGISTRANT.CONSTITUENTID
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.REGISTRANTLODGING on REGISTRANTLODGING.REGISTRANTCONSTITUENTID = REGISTRANT.CONSTITUENTID and REGISTRANTLODGING.EVENTID = REGISTRANT.EVENTID