USP_DATAFORMTEMPLATE_VIEW_EVENTINVITATION
The load procedure used by the view dataform template "Event Invitation 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. |
| @MARKEDFORINVITATIONRESENDCOUNT | int | INOUT | Marked for invitation resend |
| @SENTINVITATIONWITHNORESPONSECOUNT | int | INOUT | Sent invitations with no response |
| @REGISTEREDINVITEECOUNT | int | INOUT | Registered invitees |
| @NOTYETSENTINVITATIONCOUNT | int | INOUT | Invitations to be sent |
| @SENTINVITATIONCOUNT | int | INOUT | Sent invitations |
| @INVITEETOTAL | int | INOUT | Total invitees |
| @DECLINEDINVITEECOUNT | int | INOUT | Declined invitees |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EVENTINVITATION
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@MARKEDFORINVITATIONRESENDCOUNT int = null output,
@SENTINVITATIONWITHNORESPONSECOUNT int = null output,
@REGISTEREDINVITEECOUNT int = null output,
@NOTYETSENTINVITATIONCOUNT int = null output,
@SENTINVITATIONCOUNT int = null output,
@INVITEETOTAL int = null output,
@DECLINEDINVITEECOUNT int = null output
)
as
set nocount on;
if exists (select ID from dbo.INVITATION where ID = @ID)
select
@MARKEDFORINVITATIONRESENDCOUNT = coalesce(sum([INVITEEPROPERTIES].[MARKEDFORINVITATIONRESEND]), 0),
@SENTINVITATIONWITHNORESPONSECOUNT = coalesce(sum([INVITEEPROPERTIES].[SENTINVITATIONWITHNORESPONSE]), 0),
@REGISTEREDINVITEECOUNT = coalesce(sum([INVITEEPROPERTIES].[REGISTEREDINVITEE]), 0),
@NOTYETSENTINVITATIONCOUNT = coalesce(sum([INVITEEPROPERTIES].[NOTYETSENTINVITATION]), 0),
@SENTINVITATIONCOUNT = coalesce(sum([INVITEEPROPERTIES].[SENTINVITATION]), 0),
@INVITEETOTAL = count(*),
@DECLINEDINVITEECOUNT = coalesce(sum([INVITEEPROPERTIES].[DECLINEDINVITEE]), 0),
@DATALOADED = 1
from
(
select
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
left join dbo.INVITEERESEND on
INVITEERESEND.INVITEEID = INVITEE.ID
and INVITEERESEND.INVITATIONID = @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
INVITEE.ID = [SENTINVITEEPROPERTIES].[INVITEEID]
and [SENTINVITEEPROPERTIES].[PARAMETERSETID] = @ID
where
INVITEE.INVITATIONID = @ID
) [INVITEEPROPERTIES];
else
set @DATALOADED = 0;
return 0;