USP_DATAFORMTEMPLATE_ADD_SALESORDERITEM_PREREGISTEREDEVENT_ONLINE
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @HOSTCONSTITUENTID | uniqueidentifier | IN | |
| @REGISTRANTMAPPINGS | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESORDERITEM_PREREGISTEREDEVENT_ONLINE
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@HOSTCONSTITUENTID uniqueidentifier,
@REGISTRANTMAPPINGS xml
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @EVENTID uniqueidentifier
select @EVENTID = [EVENTID] from dbo.[SALESORDERITEMTICKET] where [ID] = @ID
if @EVENTID is null or @ID is null
return;
begin try
declare @REGISTRANTS_TABLE table (
[ID] uniqueidentifier,
[REGISTERLATER] bit,
[CONSTITUENTID] uniqueidentifier,
[FIRSTNAME] nvarchar(50),
[KEYNAME] nvarchar(100),
[PHONE] nvarchar(100),
[EMAIL] dbo.UDT_EMAILADDRESS,
[TITLECODEID] uniqueidentifier,
[ADDRESSBLOCK] nvarchar(150),
[CITY] nvarchar(50),
[STATEID] uniqueidentifier,
[POSTCODE] nvarchar(12),
[COUNTRYID] uniqueidentifier,
[PREFERENCES] xml,
[REGISTRATIONINFORMATION] xml,
[NEWCONSTITUENT] bit,
[VALIDADDRESS] bit
);
insert into @REGISTRANTS_TABLE
select
newid(),
isnull(T.registrants.value('(REGISTERLATER)[1]','bit'),1) as 'REGISTERLATER',
T.registrants.value('(GUESTCONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID',
isnull(T.registrants.value('(FIRSTNAME)[1]','nvarchar(50)'),'') as 'FIRSTNAME',
isnull(T.registrants.value('(KEYNAME)[1]','nvarchar(100)'),'') as 'KEYNAME',
isnull(T.registrants.value('(PHONE)[1]','nvarchar(100)'),'') as 'PHONE',
isnull(T.registrants.value('(EMAIL)[1]','dbo.UDT_EMAILADDRESS'),'') as 'EMAIL',
dbo.UFN_TITLECODE_GETID(T.registrants.value('(TITLE)[1]','nvarchar(100)')) as 'TITLECODEID',
isnull(T.registrants.value('(ADDRESSBLOCK)[1]','nvarchar(150)'),'') as 'ADDRESSBLOCK',
isnull(T.registrants.value('(CITY)[1]','nvarchar(50)'),'') as 'CITY',
T.registrants.value('(STATEID)[1]','uniqueidentifier') as 'STATEID',
isnull(T.registrants.value('(POSTCODE)[1]','nvarchar(12)'),'') as 'POSTCODE',
T.registrants.value('(COUNTRYID)[1]','uniqueidentifier') as 'COUNTRYID',
T.registrants.query('PREFERENCES') as 'PREFERENCES',
T.registrants.query('REGISTRATIONINFORMATION') as 'REGISTRATIONINFORMATION',
0,
0
from @REGISTRANTMAPPINGS.nodes('/REGISTRANTMAPPINGS/ITEM') T(registrants)
update @REGISTRANTS_TABLE
set
[STATEID] = case when [STATEID] = '00000000-0000-0000-0000-000000000000' then null else [STATEID] end,
[COUNTRYID] = case when [COUNTRYID] = '00000000-0000-0000-0000-000000000000' then null else [COUNTRYID] end,
[CONSTITUENTID] = case when [CONSTITUENTID] = '00000000-0000-0000-0000-000000000000' then null else [CONSTITUENTID] end,
[VALIDADDRESS] = case
when COUNTRYID is not null and
(
STATEID is not null or
ADDRESSBLOCK <> '' or
CITY <> '' or
POSTCODE <> ''
)
then 1
else 0
end
--Automatch prep (don't want to do this work each time through the registrant cursor)
declare @AUTOMATCHTHRESHOLD decimal(20,4) = dbo.UFN_CONSTITUENTDUPLICATESEARCHSETTINGS_GETAUTOMATCHTHRESHOLD_BYID('7BDE63AA-73B8-4A31-BE9F-82D92B67E2F4')
if @AUTOMATCHTHRESHOLD is null
set @AUTOMATCHTHRESHOLD = 92
--End automatch prep
--Registrant cursor prep
--Matching/Creating constituent records for registrants without them
--Updating constituent records for title, firstname
declare
@REGISTRANTID uniqueidentifier,
@REGISTRANTREGISTERLATER bit,
@REGISTRANTCONSTITUENTID uniqueidentifier,
@REGISTRANTFIRSTNAME nvarchar(50),
@REGISTRANTKEYNAME nvarchar(100),
@REGISTRANTPHONE nvarchar(100),
@REGISTRANTTITLECODEID uniqueidentifier,
@REGISTRANTEMAIL dbo.UDT_EMAILADDRESS,
@REGISTRANTCOUNTRYID uniqueidentifier,
@REGISTRANTSTATEID uniqueidentifier,
@REGISTRANTADDRESSBLOCK nvarchar(150),
@REGISTRANTCITY nvarchar(50),
@REGISTRANTPOSTCODE nvarchar(12),
@REGISTRANTVALIDADDRESS bit
declare REGISTRANT_CURSOR cursor local fast_forward for
select
[ID],
[REGISTERLATER],
[CONSTITUENTID],
[FIRSTNAME],
[KEYNAME],
[TITLECODEID],
[PHONE],
[EMAIL],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[VALIDADDRESS]
from @REGISTRANTS_TABLE;
--End registrant cursor prep
--Starting registrant matching/creating work
open REGISTRANT_CURSOR
fetch next from REGISTRANT_CURSOR into
@REGISTRANTID,
@REGISTRANTREGISTERLATER,
@REGISTRANTCONSTITUENTID,
@REGISTRANTFIRSTNAME,
@REGISTRANTKEYNAME,
@REGISTRANTTITLECODEID,
@REGISTRANTPHONE,
@REGISTRANTEMAIL,
@REGISTRANTCOUNTRYID,
@REGISTRANTSTATEID,
@REGISTRANTADDRESSBLOCK,
@REGISTRANTCITY,
@REGISTRANTPOSTCODE,
@REGISTRANTVALIDADDRESS
while @@FETCH_STATUS = 0
begin
--Find a constituent record match if we don't have one for this registrant
if @REGISTRANTCONSTITUENTID is null and @REGISTRANTREGISTERLATER = 0
begin
declare @CONSTITUENTMATCHID uniqueidentifier = null
select top 1
@CONSTITUENTMATCHID = CONSTITUENTID
from dbo.UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_2 (
@REGISTRANTTITLECODEID,
@REGISTRANTFIRSTNAME,
'', -- middle name
@REGISTRANTKEYNAME,
null, --suffix
@REGISTRANTADDRESSBLOCK,--no city or state in this algorithm
@REGISTRANTPOSTCODE,
@REGISTRANTCOUNTRYID,
0, --is organization
0, --is group
null, --lookup ID
null, --alternate lookup IDs
@REGISTRANTEMAIL,
@REGISTRANTPHONE,
@AUTOMATCHTHRESHOLD, --overall match threshold
@AUTOMATCHTHRESHOLD
) as CONSTITUENTMATCHES
order by
case when CONSTITUENTID = @HOSTCONSTITUENTID then 1 else 0 end desc,
MATCHPERCENTAGE desc
if @CONSTITUENTMATCHID is not null
begin
if --Not already a registrant for this event
not exists(
select 1
from dbo.[REGISTRANT]
where
[REGISTRANT].[CONSTITUENTID] = @CONSTITUENTMATCHID and
[REGISTRANT].[EVENTID] = @EVENTID and
--Allow hosts that haven't been marked as attending to match
(
[REGISTRANT].[WILLNOTATTEND] = 0 or
exists(select top 1 [ID] from dbo.REGISTRANTREGISTRATIONMAP where [REGISTRANTID] = [REGISTRANT].[ID])
)
) and
--Not already in registrant mapping
not exists (
select 1
from @REGISTRANTS_TABLE [R]
where [R].[CONSTITUENTID] = @CONSTITUENTMATCHID
)
begin
set @REGISTRANTCONSTITUENTID = @CONSTITUENTMATCHID
update @REGISTRANTS_TABLE
set [CONSTITUENTID] = @REGISTRANTCONSTITUENTID
where [ID] = @REGISTRANTID
--If we picked a constituent who's a host who hasn't committed to attending yet, we'll need to use their registrant ID
select @REGISTRANTID = [ID]
from dbo.[REGISTRANT]
where
[REGISTRANT].[CONSTITUENTID] = @REGISTRANTCONSTITUENTID and
[REGISTRANT].[EVENTID] = @EVENTID
update @REGISTRANTS_TABLE
set [ID] = @REGISTRANTID
where [CONSTITUENTID] = @REGISTRANTCONSTITUENTID
end
end
end
--Find a match or add a constituent record if we don't have one for this registrant
if @REGISTRANTCONSTITUENTID is null and @REGISTRANTREGISTERLATER = 0
begin
update @REGISTRANTS_TABLE
set
[CONSTITUENTID] = newid(),
[NEWCONSTITUENT] = 1
where [ID] = @REGISTRANTID
end
else if not @REGISTRANTCONSTITUENTID is null and @REGISTRANTREGISTERLATER = 0 --Update constituent
begin
exec dbo.USP_WEBFORMS_CONSTITUENT_ADDRESSINFORMATION_AUTOMATCHORCREATE
@REGISTRANTCONSTITUENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@REGISTRANTTITLECODEID,
@REGISTRANTFIRSTNAME,
@REGISTRANTKEYNAME,
@REGISTRANTPHONE,
@REGISTRANTEMAIL,
@REGISTRANTCOUNTRYID,
@REGISTRANTSTATEID,
@REGISTRANTADDRESSBLOCK,
@REGISTRANTCITY,
@REGISTRANTPOSTCODE,
null, --address ID
null, --phone ID
null, --email address ID
1, --@ORIGINCODE, 1 = Web Forms
null, --@INFOSOURCECODEID
null, --@ADDRESSTYPECODEID
null, --@EMAILADDRESSTYPECODEID
null --@PHONETYPECODEID
end
else --Registrant is 'register later' but has constituent ID set. Let's knock it out
begin
set @REGISTRANTCONSTITUENTID = null
update @REGISTRANTS_TABLE
set [CONSTITUENTID] = null
where [ID] = @REGISTRANTID
end
fetch next from REGISTRANT_CURSOR into
@REGISTRANTID,
@REGISTRANTREGISTERLATER,
@REGISTRANTCONSTITUENTID,
@REGISTRANTFIRSTNAME,
@REGISTRANTKEYNAME,
@REGISTRANTTITLECODEID,
@REGISTRANTPHONE,
@REGISTRANTEMAIL,
@REGISTRANTCOUNTRYID,
@REGISTRANTSTATEID,
@REGISTRANTADDRESSBLOCK,
@REGISTRANTCITY,
@REGISTRANTPOSTCODE,
@REGISTRANTVALIDADDRESS
end
close REGISTRANT_CURSOR
deallocate REGISTRANT_CURSOR
--Create new constituents
if exists(select 1 from @REGISTRANTS_TABLE where [NEWCONSTITUENT] = 1)
begin
insert into dbo.CONSTITUENT
(
[ID],
[KEYNAME],
[FIRSTNAME],
[TITLECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
CONSTITUENTID,
KEYNAME,
FIRSTNAME,
TITLECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRANTS_TABLE
where [NEWCONSTITUENT] = 1
insert into dbo.CONSTITUENTORIGINATION (
[ID],
[ORIGINCODE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
CONSTITUENTID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRANTS_TABLE
where [NEWCONSTITUENT] = 1
/*Start name format defaults*/
insert into [dbo].[NAMEFORMAT] (
[CONSTITUENTID],
[NAMEFORMATTYPECODEID],
[NAMEFORMATFUNCTIONID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[PRIMARYADDRESSEE],
[PRIMARYSALUTATION],
[SEQUENCE]
)
select
[REGISTRANTS].[CONSTITUENTID],
NFD.NAMEFORMATTYPECODEID,
NFD.NAMEFORMATFUNCTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
NFD.PRIMARYADDRESSEE,
NFD.PRIMARYSALUTATION,
(
select count(ID)
from dbo.NAMEFORMATDEFAULT as SUBNFD
where
NFD.ID > SUBNFD.ID
and NFD.APPLYTOCODE = SUBNFD.APPLYTOCODE
)
from dbo.NAMEFORMATDEFAULT as NFD
cross join @REGISTRANTS_TABLE [REGISTRANTS]
where
NFD.APPLYTOCODE = 0 and
[REGISTRANTS].[NEWCONSTITUENT] = 1
insert into dbo.[PHONE] (
ID,
CONSTITUENTID,
NUMBER,
ORIGINCODE,
ISPRIMARY,
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[COUNTRYID]
)
select
newid(),
CONSTITUENTID,
PHONE,
1,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
COUNTRYID
from @REGISTRANTS_TABLE
where
[NEWCONSTITUENT] = 1 and
[PHONE] <> ''
insert into dbo.[EMAILADDRESS] (
ID,
CONSTITUENTID,
EMAILADDRESS,
ORIGINCODE,
ISPRIMARY,
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
CONSTITUENTID,
EMAIL,
1,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRANTS_TABLE
where
[NEWCONSTITUENT] = 1 and
[EMAIL] <> ''
insert into dbo.[ADDRESS] (
ID,
CONSTITUENTID,
ISPRIMARY,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
CONSTITUENTID,
1,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRANTS_TABLE
where
[NEWCONSTITUENT] = 1 and
[VALIDADDRESS] <> ''
insert into dbo.ADDRESSVALIDATIONUPDATE
(
[ID],
[ORIGINCODE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
ADDRESS.ID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRANTS_TABLE [REGISTRANTS]
inner join dbo.ADDRESS
on [REGISTRANTS].[CONSTITUENTID] = [ADDRESS].[CONSTITUENTID]
where [NEWCONSTITUENT] = 1
end
if
exists(select 1 from @REGISTRANTS_TABLE) and
exists(select 1 from dbo.[SALESORDERITEMTICKET] where [ID] = @ID)
begin
--Check if the host registrant is already a guest
declare @HOSTREGISTRANTID uniqueidentifier = null
select @HOSTREGISTRANTID = isnull([GUESTOFREGISTRANTID], [ID])
from dbo.[REGISTRANT]
where
[CONSTITUENTID] = @HOSTCONSTITUENTID and
[EVENTID] = @EVENTID
if @HOSTREGISTRANTID is null
begin
--Make the host registrant the host
declare @HOSTWILLNOTATTEND bit = 1
select @HOSTREGISTRANTID = [ID] from @REGISTRANTS_TABLE where [CONSTITUENTID] = @HOSTCONSTITUENTID
if @HOSTREGISTRANTID is not null
set @HOSTWILLNOTATTEND = 0
else
set @HOSTREGISTRANTID = newid()
--Add host registrant
insert into dbo.[REGISTRANT] (
[ID],
[CONSTITUENTID],
[EVENTID],
[WILLNOTATTEND],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values (
@HOSTREGISTRANTID,
@HOSTCONSTITUENTID,
@EVENTID,
@HOSTWILLNOTATTEND,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
--Add remaining new registrants
insert into dbo.[REGISTRANT] (
[ID],
[CONSTITUENTID],
[EVENTID],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
[ID],
[CONSTITUENTID],
@EVENTID,
0,
@HOSTREGISTRANTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRANTS_TABLE [RT]
where not exists(
select 1
from dbo.[REGISTRANT]
where [REGISTRANT].[ID] = [RT].[ID]
)
--Update existing host registrants who are now guests of this registration
--But, first, we need to update guests of those hosts who need to recognize @HOSTREGISTRANTID as their host now
--If we don't, we'll get guests claiming their host who is a guest of someone else, and that's not allowed
update dbo.[REGISTRANT]
set
[GUESTOFREGISTRANTID] = @HOSTREGISTRANTID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where
exists (
select 1
from @REGISTRANTS_TABLE [RT]
where [RT].[ID] = [REGISTRANT].[GUESTOFREGISTRANTID]
)
--Then hosts
update dbo.[REGISTRANT]
set
[GUESTOFREGISTRANTID] = @HOSTREGISTRANTID,
[WILLNOTATTEND] = 0,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where
exists (
select 1
from @REGISTRANTS_TABLE [RT]
where
(
[RT].[ID] = [REGISTRANT].[ID] and
[REGISTRANT].[GUESTOFREGISTRANTID] is null and
[REGISTRANT].[ID] <> @HOSTREGISTRANTID
)
)
--also flip the will-not-attend bit for a host created for a previous price type's call who is now a registrant created in this price type's call
update dbo.[REGISTRANT]
set
[WILLNOTATTEND] = 0,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where
exists (
select 1
from @REGISTRANTS_TABLE [RT]
where
(
[RT].[ID] = [REGISTRANT].[ID] and
[REGISTRANT].[ID] = @HOSTREGISTRANTID
)
)
--Although existing guests (previously hosts) shouldn't have stated preferences, let's not assume.
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
set CONTEXT_INFO @CHANGEAGENTID;
delete dbo.[REGISTRANTPREFERENCE]
where exists(
select 1 from @REGISTRANTS_TABLE [RT] where [RT].[ID] = [REGISTRANTPREFERENCE].[REGISTRANTID]
)
if not @contextCache is null
set CONTEXT_INFO @contextCache;
insert dbo.[REGISTRANTPREFERENCE] (
[ID],
[REGISTRANTID],
[EVENTPREFERENCEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
[RT].[ID],
T.preferences.value('(EVENTPREFERENCEID)[1]', 'uniqueidentifier'),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRANTS_TABLE [RT]
cross apply [RT].[PREFERENCES].nodes('/PREFERENCES/ITEM') T(preferences)
delete dbo.[REGISTRANTREGISTRATIONINFORMATION]
where exists(
select 1 from @REGISTRANTS_TABLE [RT] where [RT].[ID] = [REGISTRANTREGISTRATIONINFORMATION].[REGISTRANTID]
)
insert into dbo.[REGISTRANTREGISTRATIONINFORMATION] (
ID,
REGISTRANTID,
REGISTRATIONINFORMATIONID,
PERSONDETAILTYPECODE,
TEXTVALUE,
BOOLEANVALUE,
REGISTRATIONINFORMATIONOPTIONID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
[RT].[ID],
T.reginfo.value('(REGISTRATIONINFORMATIONID)[1]', 'uniqueidentifier'),
T.reginfo.value('(PERSONDETAILTYPECODE)[1]', 'int'),
T.reginfo.value('(TEXTVALUE)[1]', 'nvarchar(max)'),
T.reginfo.value('(BOOLEANVALUE)[1]', 'bit'),
cast(nullif(T.reginfo.value('(REGISTRATIONINFORMATIONOPTIONID)[1]', 'nvarchar(36)'), '') as uniqueidentifier),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRANTS_TABLE [RT]
cross apply [RT].[REGISTRATIONINFORMATION].nodes('/REGISTRATIONINFORMATION/ITEM') T(reginfo)
insert into dbo.[SALESORDERITEMTICKETREGISTRANT] (
ID,
SALESORDERITEMTICKETID,
REGISTRANTID,
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@ID,
[ID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRANTS_TABLE
end
--End registrants check
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0