USP_REVENUEBATCHREGISTRANT_GETREGISTRATIONSANDREGISTRANTMAPPINGS
Returns a registrant's registrations and guests.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @MAINEVENTID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @PACKAGEREGISTRATIONS | xml | INOUT | |
| @SINGLEEVENTREGISTRATIONS | xml | INOUT | |
| @REGISTRANTMAPPINGS | xml | INOUT | |
| @BATCHID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_REVENUEBATCHREGISTRANT_GETREGISTRATIONSANDREGISTRANTMAPPINGS
(
@MAINEVENTID uniqueidentifier = null,
@CONSTITUENTID uniqueidentifier = null,
@PACKAGEREGISTRATIONS xml = null output,
@SINGLEEVENTREGISTRATIONS xml = null output,
@REGISTRANTMAPPINGS xml = null output,
@BATCHID uniqueidentifier = null
)
with execute as caller
as
set nocount on;
--Breaks the registrant registrations down into groups that are mapped to the same set
--registrant packages.
--Sql Server is randomly throwing fatal exception errors parsing this as XML
--Storing as a table to work around issue.
declare @PACKAGEREGISTRATIONSTABLE table
(
[PACKAGEREGISTRATIONID] uniqueidentifier,
[PACKAGEREGISTRATIONPACKAGEID] uniqueidentifier,
[QUANTITY] int,
[AMOUNT] money,
[RECEIPTAMOUNT] money,
[PACKAGEREGISTRANTREGISTRATIONS] xml
);
with [PACKAGEREGISTRATIONS]
as
(
select
row_number() over (partition by BATCHREVENUEREGISTRANTPACKAGE.ID order by BATCHREVENUEREGISTRANT.EVENTID) [ROWNUMBER],
BATCHREVENUEREGISTRANTPACKAGE.ID [REGISTRANTPACKAGEID],
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID [REGISTRANTREGISTRATIONMAPID],
BATCHREVENUEREGISTRANTREGISTRATION.ID [REGISTRANTREGISTRATIONID],
BATCHREVENUEREGISTRANTPACKAGE.REGISTRATIONPACKAGEID,
BATCHREVENUEREGISTRANTREGISTRATION.QUANTITY
from
dbo.BATCHREVENUEREGISTRANTPACKAGE
inner join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on BATCHREVENUEREGISTRANTPACKAGE.ID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID
left join dbo.BATCHREVENUEREGISTRANTREGISTRATION on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = BATCHREVENUEREGISTRANTREGISTRATION.ID
left join dbo.BATCHREVENUEREGISTRANT on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID = BATCHREVENUEREGISTRANT.ID
left join dbo.REGISTRATIONPACKAGE on BATCHREVENUEREGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGE.ID
left join dbo.EVENT RELATEDEVENTS on RELATEDEVENTS.ID = REGISTRATIONPACKAGE.EVENTID --JamesWill WI147843 2011-07-06 Pull in multi-level event packages
where
BATCHREVENUEREGISTRANT.BATCHID = @BATCHID ----JamesWill WI147843 2011-07-06 Limit the search for packages to this batch
and
(
BATCHREVENUEREGISTRANTPACKAGE.CONSTITUENTID = @CONSTITUENTID
or BATCHREVENUEREGISTRANTPACKAGE.GUESTOFCONSTITUENTID = @CONSTITUENTID
)
and
( --JamesWill WI147843 2011-07-06 Search for multi-level event packages
RELATEDEVENTS.MAINEVENTID = @MAINEVENTID
or RELATEDEVENTS.ID = @MAINEVENTID
)
)
insert into
@PACKAGEREGISTRATIONSTABLE(
[PACKAGEREGISTRATIONID],
[PACKAGEREGISTRATIONPACKAGEID],
[QUANTITY],
[AMOUNT],
[RECEIPTAMOUNT],
[PACKAGEREGISTRANTREGISTRATIONS])
select
newid() [PACKAGEREGISTRATIONID],
[PACKAGEREGISTRATIONS].REGISTRATIONPACKAGEID [PACKAGEREGISTRATIONPACKAGEID],
[PACKAGEREGISTRATIONS].QUANTITY,
[REGISTRANTREGISTRATIONSAGGREGATE].AMOUNT,
[REGISTRANTREGISTRATIONSAGGREGATE].RECEIPTAMOUNT,
(
select
BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID [PACKAGEEVENTPRICEID],
BATCHREVENUEREGISTRANTREGISTRATION.ID [PACKAGEREGISTRANTREGISTRATIONID],
BATCHREVENUEREGISTRANTREGISTRATION.AMOUNT,
BATCHREVENUEREGISTRANTREGISTRATION.RECEIPTAMOUNT,
EVENT.NAME [EVENTNAME]
from
(
select top (1)
[PACKAGEREGISTRANTREGISTRATIONS].REGISTRANTREGISTRATIONID,
[PACKAGEREGISTRANTREGISTRATIONS].REGISTRANTPACKAGEID,
[PACKAGEREGISTRANTREGISTRATIONS].REGISTRATIONPACKAGEID
from
[PACKAGEREGISTRATIONS] [PACKAGEREGISTRANTREGISTRATIONS]
where
[PACKAGEREGISTRATIONS].REGISTRANTREGISTRATIONID = [PACKAGEREGISTRANTREGISTRATIONS].REGISTRANTREGISTRATIONID
) [FIRSTREGISTRANTPACKAGE]
left join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on [FIRSTREGISTRANTPACKAGE].REGISTRANTPACKAGEID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID
left join dbo.BATCHREVENUEREGISTRANTREGISTRATION on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = BATCHREVENUEREGISTRANTREGISTRATION.ID
left join dbo.EVENTPRICE on BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
left join dbo.EVENT on EVENTPRICE.EVENTID = EVENT.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
and [FIRSTREGISTRANTPACKAGE].REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
where
REGISTRATIONPACKAGEPRICE.ID is not null
order by
EVENT.NAME
for xml path ('ITEM'), type
)
from
[PACKAGEREGISTRATIONS]
cross apply
(
select
sum(BATCHREVENUEREGISTRANTREGISTRATION.AMOUNT) [AMOUNT],
sum(BATCHREVENUEREGISTRANTREGISTRATION.RECEIPTAMOUNT) [RECEIPTAMOUNT]
from
(
select top (1)
[PACKAGEREGISTRANTREGISTRATIONS].REGISTRANTREGISTRATIONID,
[PACKAGEREGISTRANTREGISTRATIONS].REGISTRANTPACKAGEID,
[PACKAGEREGISTRANTREGISTRATIONS].REGISTRATIONPACKAGEID
from
[PACKAGEREGISTRATIONS] [PACKAGEREGISTRANTREGISTRATIONS]
where
[PACKAGEREGISTRATIONS].REGISTRANTREGISTRATIONID = [PACKAGEREGISTRANTREGISTRATIONS].REGISTRANTREGISTRATIONID
) [FIRSTREGISTRANTPACKAGE]
left join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on [FIRSTREGISTRANTPACKAGE].REGISTRANTPACKAGEID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID
left join dbo.BATCHREVENUEREGISTRANTREGISTRATION on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = BATCHREVENUEREGISTRANTREGISTRATION.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
and [FIRSTREGISTRANTPACKAGE].REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
where
REGISTRATIONPACKAGEPRICE.ID is not null
) [REGISTRANTREGISTRATIONSAGGREGATE]
where
PACKAGEREGISTRATIONS.ROWNUMBER = 1
group by
PACKAGEREGISTRATIONS.REGISTRANTREGISTRATIONID,
PACKAGEREGISTRATIONS.REGISTRATIONPACKAGEID,
PACKAGEREGISTRATIONS.QUANTITY,
[REGISTRANTREGISTRATIONSAGGREGATE].AMOUNT,
[REGISTRANTREGISTRATIONSAGGREGATE].RECEIPTAMOUNT;
select
@PACKAGEREGISTRATIONS =
(select
[PACKAGEREGISTRATIONID],
[PACKAGEREGISTRATIONPACKAGEID],
QUANTITY,
AMOUNT,
RECEIPTAMOUNT,
PACKAGEREGISTRANTREGISTRATIONS
from @PACKAGEREGISTRATIONSTABLE
for xml path ('ITEM'), root ('PACKAGEREGISTRATIONS'), type);
with [SINGLEEVENTREGISTRATIONS]
as
(
select
BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID [SINGLEEVENTREGISTRATIONEVENTPRICEID],
BATCHREVENUEREGISTRANTREGISTRATION.QUANTITY,
BATCHREVENUEREGISTRANTREGISTRATION.AMOUNT,
BATCHREVENUEREGISTRANTREGISTRATION.RECEIPTAMOUNT,
BATCHREVENUEREGISTRANTREGISTRATION.ID [SINGLEEVENTREGISTRANTREGISTRATIONID],
BATCHREVENUEREGISTRANTREGISTRATION.REGISTRANTID
from
dbo.BATCHREVENUEREGISTRANTREGISTRATION
left join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on BATCHREVENUEREGISTRANTREGISTRATION.ID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID
left join dbo.BATCHREVENUEREGISTRANTPACKAGE on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = BATCHREVENUEREGISTRANTPACKAGE.ID
left join dbo.EVENTPRICE on BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
BATCHREVENUEREGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
and BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
where
REGISTRATIONPACKAGEPRICE.ID is null
group by
BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID,
BATCHREVENUEREGISTRANTREGISTRATION.QUANTITY,
BATCHREVENUEREGISTRANTREGISTRATION.AMOUNT,
BATCHREVENUEREGISTRANTREGISTRATION.RECEIPTAMOUNT,
BATCHREVENUEREGISTRANTREGISTRATION.ID,
BATCHREVENUEREGISTRANTREGISTRATION.REGISTRANTID
)
select
@SINGLEEVENTREGISTRATIONS =
(
select
newid() [SINGLEEVENTREGISTRATIONID],
BATCHREVENUEREGISTRANT.EVENTID [SINGLEEVENTREGISTRATIONEVENTID],
[SINGLEEVENTREGISTRATIONS].[SINGLEEVENTREGISTRATIONEVENTPRICEID],
[SINGLEEVENTREGISTRATIONS].[QUANTITY],
[SINGLEEVENTREGISTRATIONS].[AMOUNT],
[SINGLEEVENTREGISTRATIONS].[RECEIPTAMOUNT],
[SINGLEEVENTREGISTRATIONS].[SINGLEEVENTREGISTRANTREGISTRATIONID]
from
[SINGLEEVENTREGISTRATIONS]
left join dbo.BATCHREVENUEREGISTRANT on [SINGLEEVENTREGISTRATIONS].REGISTRANTID = BATCHREVENUEREGISTRANT.ID
left join dbo.EVENT on BATCHREVENUEREGISTRANT.EVENTID = EVENT.ID
where
BATCHREVENUEREGISTRANT.CONSTITUENTID = @CONSTITUENTID
and
(
EVENT.ID = @MAINEVENTID
or
EVENT.MAINEVENTID = @MAINEVENTID
)
and BATCHREVENUEREGISTRANT.BATCHID = @BATCHID
for xml path ('ITEM'), root ('SINGLEEVENTREGISTRATIONS'), type
);
declare @UNKNOWNGUESTWELLKNOWNGUID uniqueidentifier;
set @UNKNOWNGUESTWELLKNOWNGUID = 'C776DAB5-65B2-4258-ADAE-AE396D28E251'; --Well-known GUID for unknown guest
declare @CONSTITUENTRECORDTYPEID uniqueidentifier
set @CONSTITUENTRECORDTYPEID=dbo.UFN_RECORDTYPE_GETIDBYNAME('Constituent');
--There is a bug in SQL Server that causes error message 6611: "The XML data type is damaged." to
--occur if FOR XML is combined with multiple calls to nodes(). By incrementally adding registrations
--to a table variable, we avoid the error. See "Bug: #50000918 (SQL Hotfix)." on MSDN.
declare @REGISTRANTMAPPINGSITEM table
(
[ID] uniqueidentifier default newid(),
[REGISTRANTPACKAGEID] uniqueidentifier,
[REGISTRATIONPACKAGEID] uniqueidentifier,
[EVENTID] uniqueidentifier,
[EVENTPRICEID] uniqueidentifier,
[REGISTRANTID] uniqueidentifier,
[REGISTRATIONSCOLLECTIONID] uniqueidentifier,
[SINGLEEVENTREGISTRANTREGISTRATIONMAPID] uniqueidentifier,
[NAME] nvarchar(250),
[GUESTCONSTITUENTID] uniqueidentifier,
[WAIVEREGISTRATIONFEE] bit,
[REGISTRANTREGISTRATIONMAPS] xml,
[REGISTRANTWAIVEBENEFITS] xml,
[PREFERENCES] xml,
[BATCHREVENUEREGISTRANTID] uniqueidentifier
);
--Registrant mappings linked to packages
insert into @REGISTRANTMAPPINGSITEM
(
[REGISTRANTPACKAGEID],
[REGISTRATIONPACKAGEID],
[EVENTID],
[EVENTPRICEID],
[REGISTRANTID],
[REGISTRATIONSCOLLECTIONID],
[SINGLEEVENTREGISTRANTREGISTRATIONMAPID],
[NAME],
[GUESTCONSTITUENTID],
[WAIVEREGISTRATIONFEE],
[BATCHREVENUEREGISTRANTID]
)
select
BATCHREVENUEREGISTRANTPACKAGE.ID [REGISTRANTPACKAGEID],
REGISTRATIONPACKAGE.ID [REGISTRATIONPACKAGEID],
null [EVENTID],
null [EVENTPRICEID],
null [REGISTRANTID], --REGISTRANTID is only used for additional guests
[PACKAGESFIRSTREGISTRATION].PACKAGEREGISTRATIONID [REGISTRATIONSCOLLECTIONID],
null [SINGLEEVENTREGISTRANTREGISTRATIONMAPID],
REGISTRATIONPACKAGE.NAME,
coalesce(
BATCHREVENUEREGISTRANT.CONSTITUENTID,
@UNKNOWNGUESTWELLKNOWNGUID
) [GUESTCONSTITUENTID],
case
when
[PACKAGESFIRSTREGISTRATION].AMOUNT = 0 --TODO: What if the other registrations have amount > 0
and
dbo.UFN_REGISTRATIONPACKAGE_GETAMOUNT(REGISTRATIONPACKAGE.ID) > 0
then
1
else
0
end [WAIVEREGISTRATIONFEE],
BATCHREVENUEREGISTRANT.ID
from
dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
left join dbo.BATCHREVENUEREGISTRANT on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID = BATCHREVENUEREGISTRANT.ID
left join @PACKAGEREGISTRATIONSTABLE [PACKAGESFIRSTREGISTRATION] on
BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = PACKAGEREGISTRANTREGISTRATIONS.value('/ITEM[1]/PACKAGEREGISTRANTREGISTRATIONID[1]', 'uniqueidentifier')
left join dbo.BATCHREVENUEREGISTRANTPACKAGE on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = BATCHREVENUEREGISTRANTPACKAGE.ID
left join dbo.REGISTRATIONPACKAGE on BATCHREVENUEREGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGE.ID
where
PACKAGEREGISTRANTREGISTRATIONS.value('/ITEM[1]/PACKAGEREGISTRANTREGISTRATIONID[1]', 'uniqueidentifier') is not null;
--Registrant mappings not linked to packages
insert into @REGISTRANTMAPPINGSITEM
(
[REGISTRANTPACKAGEID],
[REGISTRATIONPACKAGEID],
[EVENTID],
[EVENTPRICEID],
[REGISTRANTID],
[REGISTRATIONSCOLLECTIONID],
[SINGLEEVENTREGISTRANTREGISTRATIONMAPID],
[NAME],
[GUESTCONSTITUENTID],
[WAIVEREGISTRATIONFEE],
[BATCHREVENUEREGISTRANTID]
)
select
null [REGISTRANTPACKAGEID],
null [REGISTRATIONPACKAGEID],
EVENT.ID [EVENTID],
EVENTPRICE.ID [EVENTPRICEID],
null [REGISTRANTID], --REGISTRANTID is only used for additional guests
[SINGLEEVENTREGISTRATION].c.value('SINGLEEVENTREGISTRATIONID[1]', 'uniqueidentifier') [REGISTRATIONSCOLLECTIONID],
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID [SINGLEEVENTREGISTRANTREGISTRATIONMAPID], --Store registrant registration map ID here temporarily
(EVENT.NAME + ' (' + EVENTREGISTRATIONTYPE.DESCRIPTION + ')') [NAME], --TODO: Internationalize
coalesce(
BATCHREVENUEREGISTRANT.CONSTITUENTID,
@UNKNOWNGUESTWELLKNOWNGUID
) [GUESTCONSTITUENTID],
case
when
[SINGLEEVENTREGISTRATION].c.value('AMOUNT[1]', 'money') = 0
and
EVENTPRICE.AMOUNT > 0
then
1
else
0
end [WAIVEREGISTRATIONFEE],
BATCHREVENUEREGISTRANT.ID
from
dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
left join dbo.BATCHREVENUEREGISTRANT on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID = BATCHREVENUEREGISTRANT.ID
left join @SINGLEEVENTREGISTRATIONS.nodes('/SINGLEEVENTREGISTRATIONS/ITEM') [SINGLEEVENTREGISTRATION](c) on
BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = [SINGLEEVENTREGISTRATION].c.value('SINGLEEVENTREGISTRANTREGISTRATIONID[1]', 'uniqueidentifier')
left join dbo.EVENT on [SINGLEEVENTREGISTRATION].c.value('SINGLEEVENTREGISTRATIONEVENTID[1]', 'uniqueidentifier') = EVENT.ID
left join dbo.EVENTPRICE on [SINGLEEVENTREGISTRATION].c.value('SINGLEEVENTREGISTRATIONEVENTPRICEID[1]', 'uniqueidentifier') = EVENTPRICE.ID
left join dbo.EVENTREGISTRATIONTYPE on EVENTPRICE.EVENTREGISTRATIONTYPEID = EVENTREGISTRATIONTYPE.ID
where
[SINGLEEVENTREGISTRATION].c is not null;
--Additional guests not linked to registrant mappings
insert into @REGISTRANTMAPPINGSITEM
(
[REGISTRANTPACKAGEID],
[REGISTRATIONPACKAGEID],
[EVENTID],
[EVENTPRICEID],
[REGISTRANTID],
[REGISTRATIONSCOLLECTIONID],
[SINGLEEVENTREGISTRANTREGISTRATIONMAPID],
[NAME],
[GUESTCONSTITUENTID],
[WAIVEREGISTRATIONFEE],
[BATCHREVENUEREGISTRANTID]
)
select
null [REGISTRANTPACKAGEID],
null [REGISTRATIONPACKAGEID],
BATCHREVENUEREGISTRANT.EVENTID [EVENTID],
null [EVENTPRICEID],
BATCHREVENUEREGISTRANT.ID [REGISTRANTID], --Use REGISTRANTID for these additional guests
null [REGISTRATIONSCOLLECTIONID],
null [SINGLEEVENTREGISTRANTREGISTRATIONMAPID],
EVENT.NAME,
coalesce(
BATCHREVENUEREGISTRANT.CONSTITUENTID,
@UNKNOWNGUESTWELLKNOWNGUID
) [GUESTCONSTITUENTID],
0 [WAIVEREGISTRATIONFEE],
BATCHREVENUEREGISTRANT.ID
from
dbo.BATCHREVENUEREGISTRANT
left join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on BATCHREVENUEREGISTRANT.ID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID
left join dbo.BATCHREVENUEREGISTRANT [HOST] on BATCHREVENUEREGISTRANT.GUESTOFREGISTRANTID = [HOST].ID
left join dbo.EVENT on BATCHREVENUEREGISTRANT.EVENTID = EVENT.ID
where
[HOST].CONSTITUENTID = @CONSTITUENTID
and
(
EVENT.ID = @MAINEVENTID
or
EVENT.MAINEVENTID = @MAINEVENTID
)
and [HOST].BATCHID = @BATCHID
and
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID is null;
with [FIRSTREGISTRANTMAPPINGBYREGISTRANT]
as
(
select
[REGISTRANTMAPPINGSBYREGISTRANT].[ID],
[REGISTRANTMAPPINGSBYREGISTRANT].[REGISTRANTID]
from
(
select
row_number() over
(
partition by
[REGISTRANTMAPPINGSITEMREGISTRANT].[REGISTRANTID]
order by
case
when [REGISTRANTMAPPINGSITEM].[REGISTRATIONPACKAGEID] is null then 1
else 0
end,
[REGISTRANTMAPPINGSITEM].[NAME]
) [ROW],
[REGISTRANTMAPPINGSITEM].[ID],
[REGISTRANTMAPPINGSITEMREGISTRANT].[REGISTRANTID]
from
@REGISTRANTMAPPINGSITEM [REGISTRANTMAPPINGSITEM]
outer apply
(
select
[REGISTRANTREGISTRATIONMAPSUNION].[EVENTID],
[REGISTRANTREGISTRATIONMAPSUNION].[REGISTRANTID],
[REGISTRANTREGISTRATIONMAPSUNION].[REGISTRANTREGISTRATIONMAPID]
from
(
--For additional guests (REGISTRANTID is only used for additional guests)
select
BATCHREVENUEREGISTRANT.EVENTID,
BATCHREVENUEREGISTRANT.ID [REGISTRANTID],
null [REGISTRANTREGISTRATIONMAPID]
from
dbo.BATCHREVENUEREGISTRANT
where
BATCHREVENUEREGISTRANT.ID = [REGISTRANTMAPPINGSITEM].[REGISTRANTID]
union all
--For guests mapped to registrations
select
EVENTPRICE.EVENTID,
BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID,
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID [REGISTRANTREGISTRATIONMAPID]
from
dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
left join dbo.BATCHREVENUEREGISTRANTREGISTRATION on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = BATCHREVENUEREGISTRANTREGISTRATION.ID
left join dbo.EVENTPRICE on BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
where
BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = [REGISTRANTMAPPINGSITEM].[REGISTRANTPACKAGEID]
or
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID = [REGISTRANTMAPPINGSITEM].[SINGLEEVENTREGISTRANTREGISTRATIONMAPID]
) [REGISTRANTREGISTRATIONMAPSUNION]
) [REGISTRANTMAPPINGSITEMREGISTRANT]
) [REGISTRANTMAPPINGSBYREGISTRANT]
where
[REGISTRANTMAPPINGSBYREGISTRANT].[ROW] = 1
)
update @REGISTRANTMAPPINGSITEM
set
[REGISTRANTREGISTRATIONMAPS] =
(
select
[REGISTRANTREGISTRATIONMAPSUNION].[EVENTID],
[REGISTRANTREGISTRATIONMAPSUNION].[REGISTRANTID],
[REGISTRANTREGISTRATIONMAPSUNION].[REGISTRANTREGISTRATIONMAPID],
[REGISTRANTREGISTRATIONMAPSUNION].[CONSTITUENTID],
[REGISTRANTREGISTRATIONMAPSUNION].[CONSTITUENTNAME]
from
(
--For additional guests not mapped to registrant mappings (REGISTRANTID is only used for additional guests)
select
BATCHREVENUEREGISTRANT.EVENTID,
BATCHREVENUEREGISTRANT.ID [REGISTRANTID],
null [REGISTRANTREGISTRATIONMAPID],
BATCHREVENUEREGISTRANT.CONSTITUENTID [CONSTITUENTID],
coalesce(CONSTITUENT_NF.NAME, BATCHREVENUECONSTITUENT.NAME) [CONSTITUENTNAME]
from
dbo.BATCHREVENUEREGISTRANT
outer apply
dbo.UFN_CONSTITUENT_DISPLAYNAME(BATCHREVENUEREGISTRANT.CONSTITUENTID) CONSTITUENT_NF
left join
dbo.BATCHREVENUECONSTITUENT on BATCHREVENUEREGISTRANT.CONSTITUENTID = BATCHREVENUECONSTITUENT.ID
where
BATCHREVENUEREGISTRANT.ID = [REGISTRANTMAPPINGSITEM].[REGISTRANTID]
union all
--For guests mapped to registrations
select
EVENTPRICE.EVENTID,
BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID,
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID [REGISTRANTREGISTRATIONMAPID],
coalesce(BATCHREVENUEREGISTRANT.CONSTITUENTID, @UNKNOWNGUESTWELLKNOWNGUID), --The constituent may have been changed on one of the registrations in a package
coalesce(CONSTITUENT_NF.NAME, BATCHREVENUECONSTITUENT.NAME)
from
dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
left join dbo.BATCHREVENUEREGISTRANTREGISTRATION on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = BATCHREVENUEREGISTRANTREGISTRATION.ID
left join dbo.EVENTPRICE on BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
left join dbo.BATCHREVENUEREGISTRANTPACKAGE on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = BATCHREVENUEREGISTRANTPACKAGE.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
BATCHREVENUEREGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
and BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
left join dbo.BATCHREVENUEREGISTRANT on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID = BATCHREVENUEREGISTRANT.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(BATCHREVENUEREGISTRANT.CONSTITUENTID) CONSTITUENT_NF
left join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUEREGISTRANT.CONSTITUENTID = BATCHREVENUECONSTITUENT.ID
where
(
BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = [REGISTRANTMAPPINGSITEM].[REGISTRANTPACKAGEID]
and
REGISTRATIONPACKAGEPRICE.ID is not null
)
or
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID = [REGISTRANTMAPPINGSITEM].[SINGLEEVENTREGISTRANTREGISTRATIONMAPID]
) [REGISTRANTREGISTRATIONMAPSUNION]
for xml path ('ITEM'), root('REGISTRANTREGISTRATIONMAPS'), type
),
[REGISTRANTWAIVEBENEFITS] =
(
select
BATCHREVENUEREGISTRANT.EVENTID,
BATCHREVENUEREGISTRANT.BENEFITSWAIVED [WAIVEBENEFITS]
from
dbo.BATCHREVENUEREGISTRANT
left join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on BATCHREVENUEREGISTRANT.ID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID
left join dbo.BATCHREVENUEREGISTRANTREGISTRATION on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = BATCHREVENUEREGISTRANTREGISTRATION.ID
left join dbo.BATCHREVENUEREGISTRANTPACKAGE on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = BATCHREVENUEREGISTRANTPACKAGE.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
BATCHREVENUEREGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
and BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
where
(
BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = [REGISTRANTMAPPINGSITEM].[REGISTRANTPACKAGEID]
and
REGISTRATIONPACKAGEPRICE.ID is not null
)
or
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID = [REGISTRANTMAPPINGSITEM].[SINGLEEVENTREGISTRANTREGISTRATIONMAPID]
or
(
--For additional guests not mapped to registrant mappings (REGISTRANTID is only used for additional guests)
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID is null
and
BATCHREVENUEREGISTRANT.ID = [REGISTRANTMAPPINGSITEM].[REGISTRANTID]
)
for xml path ('ITEM'), root('REGISTRANTWAIVEBENEFITS'), type
),
[PREFERENCES] =
(
--TODO: Change this to be based on each constituent ID since constituents may have been changed on one of the registrations in the package
select
BATCHREVENUEREGISTRANTPREFERENCE.ID [REGISTRANTPREFERENCEID],
EVENTPREFERENCE.EVENTPREFERENCEGROUPID,
BATCHREVENUEREGISTRANTPREFERENCE.EVENTPREFERENCEID
from
dbo.BATCHREVENUEREGISTRANT
inner join dbo.BATCHREVENUEREGISTRANTPREFERENCE on BATCHREVENUEREGISTRANT.ID = BATCHREVENUEREGISTRANTPREFERENCE.REGISTRANTID
left join dbo.BATCHREVENUEREGISTRANTPREFERENCEMAP on BATCHREVENUEREGISTRANTPREFERENCE.ID = BATCHREVENUEREGISTRANTPREFERENCEMAP.ID
left join dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP on BATCHREVENUEREGISTRANTPREFERENCEMAP.REGISTRANTREGISTRATIONMAPID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID
left join dbo.BATCHREVENUEREGISTRANTREGISTRATION on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = BATCHREVENUEREGISTRANTREGISTRATION.ID
left join dbo.BATCHREVENUEREGISTRANTPACKAGE on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = BATCHREVENUEREGISTRANTPACKAGE.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
BATCHREVENUEREGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
and BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
left join dbo.EVENTPREFERENCE on BATCHREVENUEREGISTRANTPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
where
(
BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = [REGISTRANTMAPPINGSITEM].[REGISTRANTPACKAGEID]
and
REGISTRATIONPACKAGEPRICE.ID is not null
)
or
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID = [REGISTRANTMAPPINGSITEM].[SINGLEEVENTREGISTRANTREGISTRATIONMAPID]
or
(
--Additional guests not mapped to registrant mappings
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID is null
and
BATCHREVENUEREGISTRANT.ID = [REGISTRANTMAPPINGSITEM].[REGISTRANTID]
)
or
(
--Unknown guests that are additional gusts not mapped to registrant mappings
BATCHREVENUEREGISTRANTREGISTRATIONMAP.ID is null
and
[REGISTRANTMAPPINGSITEM].[REGISTRANTID] is null
and
exists(select 1 from [FIRSTREGISTRANTMAPPINGBYREGISTRANT] where [FIRSTREGISTRANTMAPPINGBYREGISTRANT].[REGISTRANTID] = BATCHREVENUEREGISTRANT.ID and [FIRSTREGISTRANTMAPPINGBYREGISTRANT].[ID] = [REGISTRANTMAPPINGSITEM].[ID])
)
for xml path ('ITEM'), root ('PREFERENCES'), type
)
from
@REGISTRANTMAPPINGSITEM [REGISTRANTMAPPINGSITEM];
select
@REGISTRANTMAPPINGS =
(
select
[REGISTRANTMAPPINGSITEM].[REGISTRANTPACKAGEID],
[REGISTRANTMAPPINGSITEM].[REGISTRATIONPACKAGEID],
[REGISTRANTMAPPINGSITEM].[EVENTID],
[REGISTRANTMAPPINGSITEM].[EVENTPRICEID],
[REGISTRANTMAPPINGSITEM].[REGISTRATIONSCOLLECTIONID],
[REGISTRANTMAPPINGSITEM].[REGISTRANTREGISTRATIONMAPS] as '*',
[REGISTRANTMAPPINGSITEM].[REGISTRANTWAIVEBENEFITS] as '*',
[REGISTRANTMAPPINGSITEM].[NAME],
[REGISTRANTMAPPINGSITEM].[GUESTCONSTITUENTID],
[REGISTRANTMAPPINGSITEM].[WAIVEREGISTRATIONFEE],
[REGISTRANTMAPPINGSITEM].[BATCHREVENUEREGISTRANTID],
--TODO: Change this to be based on all of the registrations since the first registration may have a $0 amount for the package
(
select
[REGISTRANTREGISTRATIONMAPITEM].[EVENTID],
TEAMFUNDRAISER.GOAL [TEAMFUNDRAISERGOAL],
APPEAL.BASECURRENCYID [TEAMFUNDRAISERCURRENCYID],
(
select
TEAMFUNDRAISINGTEAMMEMBER.ID [TEAMFUNDRAISINGTEAMMEMBERID],
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID,
case
when TEAMFUNDRAISINGTEAMCAPTAIN.ID is null then 0
else 1
end [ISTEAMCAPTAIN]
from
dbo.TEAMFUNDRAISINGTEAMMEMBER
left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN on
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID
and TEAMFUNDRAISER.CONSTITUENTID = TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID
where
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID = TEAMFUNDRAISER.ID
order by
TEAMFUNDRAISINGTEAMMEMBER.DATECHANGED desc
for xml path ('ITEM'), root ('TEAMFUNDRAISINGTEAMS'), type
)
from
(
select
T.c.value('(EVENTID)[1]', 'uniqueidentifier') as 'EVENTID',
T.c.value('(CONSTITUENTID)[1]', 'uniqueidentifier') as 'CONSTITUENTID'
from
[REGISTRANTMAPPINGSITEM].[REGISTRANTREGISTRATIONMAPS].nodes('/REGISTRANTREGISTRATIONMAPS/ITEM') T(c)
) [REGISTRANTREGISTRATIONMAPITEM]
inner join dbo.EVENT on [REGISTRANTREGISTRATIONMAPITEM].[EVENTID] = EVENT.ID
inner join dbo.TEAMFUNDRAISER on
[REGISTRANTREGISTRATIONMAPITEM].[CONSTITUENTID] = TEAMFUNDRAISER.CONSTITUENTID
and EVENT.APPEALID = TEAMFUNDRAISER.APPEALID
left join dbo.APPEAL on APPEAL.ID = TEAMFUNDRAISER.APPEALID
for xml path ('ITEM'), root('TEAMFUNDRAISING'), type
),
[REGISTRANTMAPPINGSITEM].[PREFERENCES] as '*'
from
@REGISTRANTMAPPINGSITEM [REGISTRANTMAPPINGSITEM]
for xml path ('ITEM'), root ('REGISTRANTMAPPINGS'), type
);
return 0;