USP_REGISTRANT_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 | |
| @MAXTSLONG | bigint | INOUT | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_REGISTRANT_GETREGISTRATIONSANDREGISTRANTMAPPINGS
(
@MAINEVENTID uniqueidentifier = null,
@CONSTITUENTID uniqueidentifier = null,
@PACKAGEREGISTRATIONS xml = null output,
@SINGLEEVENTREGISTRATIONS xml = null output,
@REGISTRANTMAPPINGS xml = null output,
@MAXTSLONG bigint = 0 output,
@CURRENTAPPUSERID 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 REGISTRANTPACKAGE.ID order by REGISTRANT.EVENTID) [ROWNUMBER],
REGISTRANTPACKAGE.ID [REGISTRANTPACKAGEID],
REGISTRANTREGISTRATIONMAP.ID [REGISTRANTREGISTRATIONMAPID],
REGISTRANTREGISTRATION.ID [REGISTRANTREGISTRATIONID],
REGISTRANTPACKAGE.REGISTRATIONPACKAGEID,
REGISTRANTREGISTRATION.QUANTITY
from
dbo.REGISTRANTPACKAGE
inner join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANTPACKAGE.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
left join dbo.REGISTRANT on REGISTRANTREGISTRATIONMAP.REGISTRANTID = REGISTRANT.ID
inner join dbo.REGISTRATIONPACKAGE on REGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGE.ID
inner join dbo.EVENT RELATEDEVENTS on RELATEDEVENTS.ID = REGISTRATIONPACKAGE.EVENTID
where
(
REGISTRANTPACKAGE.CONSTITUENTID = @CONSTITUENTID
or REGISTRANTPACKAGE.GUESTOFCONSTITUENTID = @CONSTITUENTID
)
and (
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
REGISTRANTREGISTRATION.EVENTPRICEID [PACKAGEEVENTPRICEID],
REGISTRANTREGISTRATION.ID [PACKAGEREGISTRANTREGISTRATIONID],
REGISTRANTREGISTRATION.AMOUNT,
REGISTRANTREGISTRATION.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.REGISTRANTREGISTRATIONMAP on [FIRSTREGISTRANTPACKAGE].REGISTRANTPACKAGEID = REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
left join dbo.EVENTPRICE on REGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
left join dbo.EVENT on EVENTPRICE.EVENTID = EVENT.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
REGISTRANTREGISTRATION.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(REGISTRANTREGISTRATION.AMOUNT) [AMOUNT],
sum(REGISTRANTREGISTRATION.RECEIPTAMOUNT) [RECEIPTAMOUNT]
from
(
select top (1)
[PACKAGEREGISTRANTREGISTRATIONS].REGISTRANTREGISTRATIONID,
[PACKAGEREGISTRANTREGISTRATIONS].REGISTRANTPACKAGEID,
[PACKAGEREGISTRANTREGISTRATIONS].REGISTRATIONPACKAGEID
from
[PACKAGEREGISTRATIONS] [PACKAGEREGISTRANTREGISTRATIONS]
where
[PACKAGEREGISTRATIONS].REGISTRANTREGISTRATIONID = [PACKAGEREGISTRANTREGISTRATIONS].REGISTRANTREGISTRATIONID
) [FIRSTREGISTRANTPACKAGE]
left join dbo.REGISTRANTREGISTRATIONMAP on [FIRSTREGISTRANTPACKAGE].REGISTRANTPACKAGEID = REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
REGISTRANTREGISTRATION.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
REGISTRANTREGISTRATION.EVENTPRICEID [SINGLEEVENTREGISTRATIONEVENTPRICEID],
REGISTRANTREGISTRATION.QUANTITY,
REGISTRANTREGISTRATION.AMOUNT,
REGISTRANTREGISTRATION.RECEIPTAMOUNT,
REGISTRANTREGISTRATION.ID [SINGLEEVENTREGISTRANTREGISTRATIONID],
REGISTRANTREGISTRATION.REGISTRANTID
from
dbo.REGISTRANTREGISTRATION
left join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANTREGISTRATION.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID
left join dbo.REGISTRANTPACKAGE on REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = REGISTRANTPACKAGE.ID
left join dbo.EVENTPRICE on REGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
REGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
and REGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
where
REGISTRATIONPACKAGEPRICE.ID is null
group by
REGISTRANTREGISTRATION.EVENTPRICEID,
REGISTRANTREGISTRATION.QUANTITY,
REGISTRANTREGISTRATION.AMOUNT,
REGISTRANTREGISTRATION.RECEIPTAMOUNT,
REGISTRANTREGISTRATION.ID,
REGISTRANTREGISTRATION.REGISTRANTID
)
select
@SINGLEEVENTREGISTRATIONS =
(
select
newid() [SINGLEEVENTREGISTRATIONID],
REGISTRANT.EVENTID [SINGLEEVENTREGISTRATIONEVENTID],
[SINGLEEVENTREGISTRATIONS].[SINGLEEVENTREGISTRATIONEVENTPRICEID],
[SINGLEEVENTREGISTRATIONS].[QUANTITY],
[SINGLEEVENTREGISTRATIONS].[AMOUNT],
[SINGLEEVENTREGISTRATIONS].[RECEIPTAMOUNT],
[SINGLEEVENTREGISTRATIONS].[SINGLEEVENTREGISTRANTREGISTRATIONID]
from
[SINGLEEVENTREGISTRATIONS]
left join dbo.REGISTRANT on [SINGLEEVENTREGISTRATIONS].REGISTRANTID = REGISTRANT.ID
left join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
where
REGISTRANT.CONSTITUENTID = @CONSTITUENTID
and
(
EVENT.ID = @MAINEVENTID
or
EVENT.MAINEVENTID = @MAINEVENTID
)
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,
[HASNOTIFICATIONS] bit
);
--Registrant mappings linked to packages
insert into @REGISTRANTMAPPINGSITEM
(
[REGISTRANTPACKAGEID],
[REGISTRATIONPACKAGEID],
[EVENTID],
[EVENTPRICEID],
[REGISTRANTID],
[REGISTRATIONSCOLLECTIONID],
[SINGLEEVENTREGISTRANTREGISTRATIONMAPID],
[NAME],
[GUESTCONSTITUENTID],
[WAIVEREGISTRATIONFEE],
[HASNOTIFICATIONS]
)
select
REGISTRANTPACKAGE.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(
REGISTRANT.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],
case
when REGISTRANT.CONSTITUENTID is null then 0
when dbo.UFN_NOTIFICATION_RECORDHASNOTIFICATIONS(REGISTRANT.CONSTITUENTID,@CONSTITUENTRECORDTYPEID,@CURRENTAPPUSERID) = 1 then 1
else 0
end [HASNOTIFICATIONS]
from
@PACKAGEREGISTRATIONSTABLE [PACKAGESFIRSTREGISTRATION]
inner join dbo.REGISTRANTREGISTRATIONMAP on
REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = PACKAGESFIRSTREGISTRATION.PACKAGEREGISTRANTREGISTRATIONS.value('/ITEM[1]/PACKAGEREGISTRANTREGISTRATIONID[1]', 'uniqueidentifier')
left join dbo.REGISTRANT on REGISTRANTREGISTRATIONMAP.REGISTRANTID = REGISTRANT.ID
left join dbo.REGISTRANTPACKAGE on REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = REGISTRANTPACKAGE.ID
left join dbo.REGISTRATIONPACKAGE on REGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGE.ID
where
PACKAGESFIRSTREGISTRATION.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],
[HASNOTIFICATIONS]
)
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],
REGISTRANTREGISTRATIONMAP.ID [SINGLEEVENTREGISTRANTREGISTRATIONMAPID], --Store registrant registration map ID here temporarily
(EVENT.NAME + ' (' + EVENTREGISTRATIONTYPE.DESCRIPTION + ')') [NAME], --TODO: Internationalize
coalesce(
REGISTRANT.CONSTITUENTID,
@UNKNOWNGUESTWELLKNOWNGUID
) [GUESTCONSTITUENTID],
case
when
[SINGLEEVENTREGISTRATION].c.value('AMOUNT[1]', 'money') = 0
and
EVENTPRICE.AMOUNT > 0
then
1
else
0
end [WAIVEREGISTRATIONFEE],
case
when REGISTRANT.CONSTITUENTID is null then 0
when dbo.UFN_NOTIFICATION_RECORDHASNOTIFICATIONS(REGISTRANT.CONSTITUENTID,@CONSTITUENTRECORDTYPEID,@CURRENTAPPUSERID) = 1 then 1
else 0
end [HASNOTIFICATIONS]
from
dbo.REGISTRANTREGISTRATIONMAP
left join dbo.REGISTRANT on REGISTRANTREGISTRATIONMAP.REGISTRANTID = REGISTRANT.ID
left join @SINGLEEVENTREGISTRATIONS.nodes('/SINGLEEVENTREGISTRATIONS/ITEM') [SINGLEEVENTREGISTRATION](c) on
REGISTRANTREGISTRATIONMAP.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],
[HASNOTIFICATIONS]
)
select
null [REGISTRANTPACKAGEID],
null [REGISTRATIONPACKAGEID],
REGISTRANT.EVENTID [EVENTID],
null [EVENTPRICEID],
REGISTRANT.ID [REGISTRANTID], --Use REGISTRANTID for these additional guests
null [REGISTRATIONSCOLLECTIONID],
null [SINGLEEVENTREGISTRANTREGISTRATIONMAPID],
EVENT.NAME,
coalesce(
REGISTRANT.CONSTITUENTID,
@UNKNOWNGUESTWELLKNOWNGUID
) [GUESTCONSTITUENTID],
0 [WAIVEREGISTRATIONFEE],
case
when REGISTRANT.CONSTITUENTID is null then 0
when dbo.UFN_NOTIFICATION_RECORDHASNOTIFICATIONS(REGISTRANT.CONSTITUENTID,@CONSTITUENTRECORDTYPEID,@CURRENTAPPUSERID) = 1 then 1
else 0
end [HASNOTIFICATIONS]
from
dbo.REGISTRANT
left join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANT.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
left join dbo.REGISTRANT [HOST] on REGISTRANT.GUESTOFREGISTRANTID = [HOST].ID
left join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
where
[HOST].CONSTITUENTID = @CONSTITUENTID
and
(
EVENT.ID = @MAINEVENTID
or
EVENT.MAINEVENTID = @MAINEVENTID
)
and
REGISTRANTREGISTRATIONMAP.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
REGISTRANT.EVENTID,
REGISTRANT.ID [REGISTRANTID],
null [REGISTRANTREGISTRATIONMAPID]
from
dbo.REGISTRANT
where
REGISTRANT.ID = [REGISTRANTMAPPINGSITEM].[REGISTRANTID]
union all
--For guests mapped to registrations
select
EVENTPRICE.EVENTID,
REGISTRANTREGISTRATIONMAP.REGISTRANTID,
REGISTRANTREGISTRATIONMAP.ID [REGISTRANTREGISTRATIONMAPID]
from
dbo.REGISTRANTREGISTRATIONMAP
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
left join dbo.EVENTPRICE on REGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
where
REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = [REGISTRANTMAPPINGSITEM].[REGISTRANTPACKAGEID]
or
REGISTRANTREGISTRATIONMAP.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
REGISTRANT.EVENTID,
REGISTRANT.ID [REGISTRANTID],
null [REGISTRANTREGISTRATIONMAPID],
REGISTRANT.CONSTITUENTID [CONSTITUENTID],
NF.NAME [CONSTITUENTNAME]
from
dbo.REGISTRANT
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) NF
where
REGISTRANT.ID = [REGISTRANTMAPPINGSITEM].[REGISTRANTID]
union all
--For guests mapped to registrations
select
EVENTPRICE.EVENTID,
REGISTRANTREGISTRATIONMAP.REGISTRANTID,
REGISTRANTREGISTRATIONMAP.ID [REGISTRANTREGISTRATIONMAPID],
coalesce(REGISTRANT.CONSTITUENTID, @UNKNOWNGUESTWELLKNOWNGUID), --The constituent may have been changed on one of the registrations in a package
NF.NAME
from
dbo.REGISTRANTREGISTRATIONMAP
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
left join dbo.EVENTPRICE on REGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
left join dbo.REGISTRANTPACKAGE on REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = REGISTRANTPACKAGE.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
REGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
and REGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
left join dbo.REGISTRANT on REGISTRANTREGISTRATIONMAP.REGISTRANTID = REGISTRANT.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) NF
where
REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = [REGISTRANTMAPPINGSITEM].[REGISTRANTPACKAGEID]
and REGISTRATIONPACKAGEPRICE.ID is not null
union all
select
EVENTPRICE.EVENTID,
REGISTRANTREGISTRATIONMAP.REGISTRANTID,
REGISTRANTREGISTRATIONMAP.ID [REGISTRANTREGISTRATIONMAPID],
coalesce(REGISTRANT.CONSTITUENTID, @UNKNOWNGUESTWELLKNOWNGUID), --The constituent may have been changed on one of the registrations in a package
NF.NAME
from
dbo.REGISTRANTREGISTRATIONMAP
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
left join dbo.EVENTPRICE on REGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
left join dbo.REGISTRANT on REGISTRANTREGISTRATIONMAP.REGISTRANTID = REGISTRANT.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) NF
where
REGISTRANTREGISTRATIONMAP.ID = [REGISTRANTMAPPINGSITEM].[SINGLEEVENTREGISTRANTREGISTRATIONMAPID]
) [REGISTRANTREGISTRATIONMAPSUNION]
for xml path ('ITEM'), root('REGISTRANTREGISTRATIONMAPS'), type
),
[REGISTRANTWAIVEBENEFITS] =
(
select
REGISTRANTUNION.EVENTID,
REGISTRANTUNION.BENEFITSWAIVED [WAIVEBENEFITS]
from
(
select
REGISTRANT.EVENTID,
REGISTRANT.BENEFITSWAIVED
from
dbo.REGISTRANT
left join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANT.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
left join dbo.REGISTRANTPACKAGE on REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = REGISTRANTPACKAGE.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
REGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
and REGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
where
REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = [REGISTRANTMAPPINGSITEM].[REGISTRANTPACKAGEID]
and REGISTRATIONPACKAGEPRICE.ID is not null
union all
select
REGISTRANT.EVENTID,
REGISTRANT.BENEFITSWAIVED
from
dbo.REGISTRANT
left join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANT.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
where
REGISTRANTREGISTRATIONMAP.ID = [REGISTRANTMAPPINGSITEM].[SINGLEEVENTREGISTRANTREGISTRATIONMAPID]
union all
select
REGISTRANT.EVENTID,
REGISTRANT.BENEFITSWAIVED
from
dbo.REGISTRANT
left join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANT.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
where
--For additional guests not mapped to registrant mappings (REGISTRANTID is only used for additional guests)
REGISTRANTREGISTRATIONMAP.ID is null
and REGISTRANT.ID = [REGISTRANTMAPPINGSITEM].[REGISTRANTID]
) REGISTRANTUNION
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
REGISTRANTPREFERENCEID,
EVENTPREFERENCEGROUPID,
EVENTPREFERENCEID
from
(
select
REGISTRANTPREFERENCE.ID [REGISTRANTPREFERENCEID],
EVENTPREFERENCE.EVENTPREFERENCEGROUPID,
REGISTRANTPREFERENCE.EVENTPREFERENCEID
from
dbo.REGISTRANT
inner join dbo.REGISTRANTPREFERENCE on REGISTRANT.ID = REGISTRANTPREFERENCE.REGISTRANTID
left join dbo.REGISTRANTPREFERENCEMAP on REGISTRANTPREFERENCE.ID = REGISTRANTPREFERENCEMAP.ID
left join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANTPREFERENCEMAP.REGISTRANTREGISTRATIONMAPID = REGISTRANTREGISTRATIONMAP.ID
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
left join dbo.REGISTRANTPACKAGE on REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = REGISTRANTPACKAGE.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
REGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
and REGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
left join dbo.EVENTPREFERENCE on REGISTRANTPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
where
REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = [REGISTRANTMAPPINGSITEM].[REGISTRANTPACKAGEID]
and REGISTRATIONPACKAGEPRICE.ID is not null
union all
select
REGISTRANTPREFERENCE.ID [REGISTRANTPREFERENCEID],
EVENTPREFERENCE.EVENTPREFERENCEGROUPID,
REGISTRANTPREFERENCE.EVENTPREFERENCEID
from
dbo.REGISTRANT
inner join dbo.REGISTRANTPREFERENCE on REGISTRANT.ID = REGISTRANTPREFERENCE.REGISTRANTID
left join dbo.REGISTRANTPREFERENCEMAP on REGISTRANTPREFERENCE.ID = REGISTRANTPREFERENCEMAP.ID
left join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANTPREFERENCEMAP.REGISTRANTREGISTRATIONMAPID = REGISTRANTREGISTRATIONMAP.ID
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
left join dbo.REGISTRANTPACKAGE on REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = REGISTRANTPACKAGE.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
REGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
and REGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
left join dbo.EVENTPREFERENCE on REGISTRANTPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
where
REGISTRANTREGISTRATIONMAP.ID = [REGISTRANTMAPPINGSITEM].[SINGLEEVENTREGISTRANTREGISTRATIONMAPID]
union all
select
REGISTRANTPREFERENCE.ID [REGISTRANTPREFERENCEID],
EVENTPREFERENCE.EVENTPREFERENCEGROUPID,
REGISTRANTPREFERENCE.EVENTPREFERENCEID
from
dbo.REGISTRANT
inner join dbo.REGISTRANTPREFERENCE on REGISTRANT.ID = REGISTRANTPREFERENCE.REGISTRANTID
left join dbo.REGISTRANTPREFERENCEMAP on REGISTRANTPREFERENCE.ID = REGISTRANTPREFERENCEMAP.ID
left join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANTPREFERENCEMAP.REGISTRANTREGISTRATIONMAPID = REGISTRANTREGISTRATIONMAP.ID
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
left join dbo.REGISTRANTPACKAGE on REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = REGISTRANTPACKAGE.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
REGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
and REGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
left join dbo.EVENTPREFERENCE on REGISTRANTPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
where
--Additional guests not mapped to registrant mappings
REGISTRANTREGISTRATIONMAP.ID is null
and REGISTRANT.ID = [REGISTRANTMAPPINGSITEM].[REGISTRANTID]
union all
select
REGISTRANTPREFERENCE.ID [REGISTRANTPREFERENCEID],
EVENTPREFERENCE.EVENTPREFERENCEGROUPID,
REGISTRANTPREFERENCE.EVENTPREFERENCEID
from
dbo.REGISTRANT
inner join dbo.REGISTRANTPREFERENCE on REGISTRANT.ID = REGISTRANTPREFERENCE.REGISTRANTID
left join dbo.REGISTRANTPREFERENCEMAP on REGISTRANTPREFERENCE.ID = REGISTRANTPREFERENCEMAP.ID
left join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANTPREFERENCEMAP.REGISTRANTREGISTRATIONMAPID = REGISTRANTREGISTRATIONMAP.ID
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
left join dbo.REGISTRANTPACKAGE on REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = REGISTRANTPACKAGE.ID
left join dbo.REGISTRATIONPACKAGEPRICE on
REGISTRANTPACKAGE.REGISTRATIONPACKAGEID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
and REGISTRANTREGISTRATION.EVENTPRICEID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
left join dbo.EVENTPREFERENCE on REGISTRANTPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
where
--Unknown guests that are additional gusts not mapped to registrant mappings
REGISTRANTREGISTRATIONMAP.ID is null
and [REGISTRANTMAPPINGSITEM].[REGISTRANTID] is null
and exists(select 1 from [FIRSTREGISTRANTMAPPINGBYREGISTRANT] where [FIRSTREGISTRANTMAPPINGBYREGISTRANT].[REGISTRANTID] = REGISTRANT.ID and [FIRSTREGISTRANTMAPPINGBYREGISTRANT].[ID] = [REGISTRANTMAPPINGSITEM].[ID])
) PREFERENCESUNION
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].[HASNOTIFICATIONS],
--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
);
--Set TSLONG to the max from all related records
declare @REGISTRANTANDGUESTS as table(
ID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
APPEALID uniqueidentifier
)
-- Splitting into a union to avoid table scans on dbo.REGISTRANT
insert into @REGISTRANTANDGUESTS
select ID, CONSTITUENTID, APPEALID from
(
select
REGISTRANT.ID,
REGISTRANT.CONSTITUENTID,
EVENT.APPEALID
from
dbo.REGISTRANT
left join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
where
REGISTRANT.CONSTITUENTID = @CONSTITUENTID
and
(
EVENT.ID = @MAINEVENTID
or EVENT.MAINEVENTID = @MAINEVENTID
)
union
select
REGISTRANT.ID,
REGISTRANT.CONSTITUENTID,
EVENT.APPEALID
from
dbo.REGISTRANT
left join dbo.REGISTRANT HOST on REGISTRANT.GUESTOFREGISTRANTID = HOST.ID
left join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
where
HOST.CONSTITUENTID = @CONSTITUENTID
and
(
EVENT.ID = @MAINEVENTID
or EVENT.MAINEVENTID = @MAINEVENTID
)
) as SUBQ;
select
@MAXTSLONG = max([TSLONGUNION].TSLONG)
from
(
select
REGISTRANT.TSLONG
from
@REGISTRANTANDGUESTS as [REGISTRANTANDGUESTS]
left join dbo.REGISTRANT on [REGISTRANTANDGUESTS].ID = REGISTRANT.ID
union all
select
REGISTRANTREGISTRATIONMAP.TSLONG
from
@REGISTRANTANDGUESTS as [REGISTRANTANDGUESTS]
left join dbo.REGISTRANTREGISTRATIONMAP on [REGISTRANTANDGUESTS].ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
union all
select
REGISTRANTPREFERENCEMAP.TSLONG
from
@REGISTRANTANDGUESTS as [REGISTRANTANDGUESTS]
left join dbo.REGISTRANTREGISTRATIONMAP on [REGISTRANTANDGUESTS].ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
left join dbo.REGISTRANTPREFERENCEMAP on REGISTRANTREGISTRATIONMAP.ID = REGISTRANTPREFERENCEMAP.REGISTRANTREGISTRATIONMAPID
union all
select
REGISTRANTPREFERENCE.TSLONG
from
@REGISTRANTANDGUESTS as [REGISTRANTANDGUESTS]
left join dbo.REGISTRANTPREFERENCE on [REGISTRANTANDGUESTS].ID = REGISTRANTPREFERENCE.REGISTRANTID
union all
select
REGISTRANTPACKAGE.TSLONG
from
@REGISTRANTANDGUESTS as [REGISTRANTANDGUESTS]
left join dbo.REGISTRANTREGISTRATIONMAP on [REGISTRANTANDGUESTS].ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
left join dbo.REGISTRANTPACKAGE on REGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID = REGISTRANTPACKAGE.ID
union all
select
REGISTRANTBENEFIT.TSLONG
from
@REGISTRANTANDGUESTS as [REGISTRANTANDGUESTS]
left join dbo.REGISTRANTBENEFIT on [REGISTRANTANDGUESTS].ID = REGISTRANTBENEFIT.REGISTRANTID
union all
select
REGISTRANTREGISTRATION.TSLONG
from
@REGISTRANTANDGUESTS as [REGISTRANTANDGUESTS]
left join dbo.REGISTRANTREGISTRATION on [REGISTRANTANDGUESTS].ID = REGISTRANTREGISTRATION.REGISTRANTID
union all
select
TEAMFUNDRAISER.TSLONG
from
@REGISTRANTANDGUESTS as [REGISTRANTANDGUESTS]
left join dbo.TEAMFUNDRAISER on
[REGISTRANTANDGUESTS].APPEALID = TEAMFUNDRAISER.APPEALID
and [REGISTRANTANDGUESTS].CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
) [TSLONGUNION];
return 0;