V_QUERY_EVENTINVITATION
This provides the ability to query for invitations.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | Invitation system ID | |
| EVENTID | uniqueidentifier | yes | Event system ID |
| NAME | nvarchar(100) | Name | |
| INVITEEMARKEDFORINVITATIONRESENDCOUNT | int | yes | Number of invitees marked for invitation resend |
| INVITEESENTINVITATIONWITHNORESPONSECOUNT | int | yes | Number of invitees sent invitation with no response |
| INVITEEREGISTEREDINVITEECOUNT | int | yes | Number of registered invitees |
| INVITEENOTYETSENTINVITATIONCOUNT | int | yes | Number of invitees not yet sent invitation |
| INVITEESENTINVITATIONCOUNT | int | yes | Number of invitees sent invitation |
| INVITEETOTAL | int | yes | Total number of invitees |
| INVITEEDECLINEDINVITEECOUNT | int | yes | Number of declined invitees |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| ADDEDBYAPPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBYUSERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBYAPPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBYUSERNAME | nvarchar(128) | yes | Changed by user name |
| MAILDATE | datetime | yes | Mail date |
| CHANNEL | varchar(78) | yes | Channel |
| CHANNELPREFERENCE | varchar(5) | yes | Channel preference |
| MAILPACKAGEID | uniqueidentifier | yes | Mail package system record ID |
| MAILPACKAGE | nvarchar(100) | yes | Mail package |
| EMAILPACKAGEID | uniqueidentifier | yes | Email package system record ID |
| EMAILPACKAGE | nvarchar(100) | yes | Email package |
| HOUSEHOLDINGTYPE | varchar(40) | yes | Householding rules |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:23:14 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EVENTINVITATION AS
select
INVITATION.ID,
INVITATION.EVENTID,
INVITATION.NAME,
coalesce([INVITATIONINVITEESUMMARY].[MARKEDFORINVITATIONRESENDCOUNT], 0) [INVITEEMARKEDFORINVITATIONRESENDCOUNT],
coalesce([INVITATIONINVITEESUMMARY].[SENTINVITATIONWITHNORESPONSECOUNT], 0) [INVITEESENTINVITATIONWITHNORESPONSECOUNT],
coalesce([INVITATIONINVITEESUMMARY].[REGISTEREDINVITEECOUNT], 0) [INVITEEREGISTEREDINVITEECOUNT],
coalesce([INVITATIONINVITEESUMMARY].[NOTYETSENTINVITATIONCOUNT], 0) [INVITEENOTYETSENTINVITATIONCOUNT],
coalesce([INVITATIONINVITEESUMMARY].[SENTINVITATIONCOUNT], 0) [INVITEESENTINVITATIONCOUNT],
coalesce([INVITATIONINVITEESUMMARY].[INVITEETOTAL], 0) [INVITEETOTAL],
coalesce([INVITATIONINVITEESUMMARY].[DECLINEDINVITEECOUNT], 0) [INVITEEDECLINEDINVITEECOUNT],
INVITATION.DATEADDED,
INVITATION.DATECHANGED,
INVITATION.TSLONG,
ADDEDBY.APPLICATIONNAME as ADDEDBYAPPLICATION,
ADDEDBY.USERNAME as ADDEDBYUSERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBYAPPLICATION,
CHANGEDBY.USERNAME as CHANGEDBYUSERNAME,
INVITATION.MAILDATE,
case INVITATION.CHANNELCODE
when 0 then 'Send through both mail and email, according to each constituent''s preferences.'
when 1 then 'Send through email only'
when 2 then 'Send through mail only'
end CHANNEL,
case INVITATION.CHANNELPREFERENCECODE
when 0 then 'Email'
when 1 then 'Mail'
end CHANNELPREFERENCE,
INVITATION.MAILPACKAGEID,
dbo.UFN_MKTPACKAGE_GETNAME(INVITATION.MAILPACKAGEID) as MAILPACKAGE,
INVITATION.EMAILPACKAGEID,
dbo.UFN_MKTPACKAGE_GETNAME(INVITATION.EMAILPACKAGEID) as EMAILPACKAGE,
case INVITATION.HOUSEHOLDINGTYPECODE
when 0 then 'All qualifying constituents'
when 1 then 'Qualifying individuals and organizations'
when 2 then 'Qualifying households'
end HOUSEHOLDINGTYPE
/*#EXTENSION*/
from
dbo.INVITATION
left join
(
select
EVENTID,
INVITATIONID,
sum([INVITEEPROPERTIES].[MARKEDFORINVITATIONRESEND]) [MARKEDFORINVITATIONRESENDCOUNT],
sum([INVITEEPROPERTIES].[SENTINVITATIONWITHNORESPONSE]) [SENTINVITATIONWITHNORESPONSECOUNT],
sum([INVITEEPROPERTIES].[REGISTEREDINVITEE]) [REGISTEREDINVITEECOUNT],
sum([INVITEEPROPERTIES].[NOTYETSENTINVITATION]) [NOTYETSENTINVITATIONCOUNT],
sum([INVITEEPROPERTIES].[SENTINVITATION]) [SENTINVITATIONCOUNT],
count(*) [INVITEETOTAL],
sum([INVITEEPROPERTIES].[DECLINEDINVITEE]) [DECLINEDINVITEECOUNT]
from
(
select
INVITEE.EVENTID,
[INVITATIONINVITEEPROPERTIES].ID [INVITATIONID],
case
when
INVITEERESEND.ID is not null
then
1
else
0
end [MARKEDFORINVITATIONRESEND],
case
when
INVITEE.DECLINED = 0
and REGISTRANT.ID is null
and [SENTINVITEEPROPERTIES].[INVITEEID] is not null
then
1
else
0
end [SENTINVITATIONWITHNORESPONSE],
case
when
REGISTRANT.ID is not null
then
1
else
0
end [REGISTEREDINVITEE],
case
when
[SENTINVITEEPROPERTIES].[INVITEEID] is null
and INVITEE.DECLINED = 0
then
1
else
0
end [NOTYETSENTINVITATION],
case
when
[SENTINVITEEPROPERTIES].[INVITEEID] is not null
then
1
else
0
end [SENTINVITATION],
case
when
INVITEE.DECLINED = 1
then
1
else
0
end [DECLINEDINVITEE]
from
dbo.INVITEE
inner join dbo.INVITATION [INVITATIONINVITEEPROPERTIES] on INVITEE.INVITATIONID = [INVITATIONINVITEEPROPERTIES].ID
left join dbo.INVITEERESEND on
INVITEERESEND.INVITEEID = INVITEE.ID
and INVITEERESEND.INVITATIONID = [INVITATIONINVITEEPROPERTIES].ID
left join dbo.REGISTRANT on
INVITEE.CONSTITUENTID = REGISTRANT.CONSTITUENTID
and INVITEE.EVENTID = REGISTRANT.EVENTID
left join
(
select distinct
[SENTINVITEE].ID [INVITEEID],
INVITATIONSTATUS.PARAMETERSETID
from
dbo.INVITEE [SENTINVITEE]
left join dbo.INVITEEHISTORY
on [SENTINVITEE].CONSTITUENTID = INVITEEHISTORY.CONSTITUENTID
and [SENTINVITEE].EVENTID = INVITEEHISTORY.EVENTID
left join dbo.INVITATIONHISTORY on INVITEEHISTORY.INVITATIONHISTORYID = INVITATIONHISTORY.ID
left join dbo.INVITATIONSTATUS on INVITATIONHISTORY.BUSINESSPROCESSSTATUSID = INVITATIONSTATUS.ID
)
[SENTINVITEEPROPERTIES] on
[SENTINVITEEPROPERTIES].[INVITEEID] = INVITEE.ID
and [SENTINVITEEPROPERTIES].[PARAMETERSETID] = [INVITATIONINVITEEPROPERTIES].ID
)
[INVITEEPROPERTIES]
group by
[INVITEEPROPERTIES].[EVENTID], [INVITEEPROPERTIES].[INVITATIONID]
) [INVITATIONINVITEESUMMARY] on
INVITATION.ID = [INVITATIONINVITEESUMMARY].[INVITATIONID]
left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = INVITATION.ADDEDBYID
left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = INVITATION.CHANGEDBYID