USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMMEMBERSHIP2
The save procedure used by the add dataform template "Sales Order Item Online Membership Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @SalesOrderItemID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @SALESORDERID | uniqueidentifier | IN | Sales Order ID |
| @MEMBERSHIPID | uniqueidentifier | IN | Membership |
| @MEMBERSHIPPROGRAMID | uniqueidentifier | IN | Program |
| @MEMBERSHIPLEVELID | uniqueidentifier | IN | Level |
| @MEMBERSHIPLEVELTERMID | uniqueidentifier | IN | Term |
| @MEMBERSHIPLEVELTYPECODEID | uniqueidentifier | IN | Type |
| @NUMBEROFCHILDREN | tinyint | IN | No. of children |
| @COMMENTS | nvarchar(1000) | IN | Comments |
| @ISGIFT | bit | IN | This membership is a gift |
| @SENDRENEWALCODE | tinyint | IN | Send renewal notice to |
| @EXPIRATIONDATE | datetime | IN | Expiration date |
| @MEMBERS | xml | IN | Members |
| @GIVENBYID | uniqueidentifier | IN | Given by |
| @COUNTRYID | uniqueidentifier | IN | Country |
| @STATEID | uniqueidentifier | IN | State |
| @ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @CITY | nvarchar(50) | IN | City |
| @POSTCODE | nvarchar(12) | IN | ZIP |
| @MEMBERSHIPADDONS | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMMEMBERSHIP2
(
@ID uniqueidentifier output,
@SalesOrderItemID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@SALESORDERID uniqueidentifier = null,
@MEMBERSHIPID uniqueidentifier = null,
@MEMBERSHIPPROGRAMID uniqueidentifier = null,
@MEMBERSHIPLEVELID uniqueidentifier = null,
@MEMBERSHIPLEVELTERMID uniqueidentifier = null,
@MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null,
@NUMBEROFCHILDREN tinyint = 0,
@COMMENTS nvarchar(1000) = null,
@ISGIFT bit = 0,
@SENDRENEWALCODE tinyint = 1,
@EXPIRATIONDATE datetime = null,
@MEMBERS xml = null,
@GIVENBYID uniqueidentifier = null,
@COUNTRYID uniqueidentifier = null,
@STATEID uniqueidentifier = null,
@ADDRESSBLOCK nvarchar(150) = '',
@CITY nvarchar(50) = '',
@POSTCODE nvarchar(12) = '',
@MEMBERSHIPADDONS xml= ''
)
as
set nocount on;
set @ID = @SalesOrderItemID
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
begin try
if (select STATUSCODE from dbo.SALESORDER where ID = @SALESORDERID) not in (0, 6, 7)
raiserror('ERR_ORDERNOTPENDING', 13, 1);
--Populating add-ons
--Note: add-ons will be updated on order in this sproc
--If add-ons differ from what is currently on the order, the order will probably not resolve (i.e. will become an unresolved order)
declare @ADDONS_TABLE table (
[ID] uniqueidentifier,
[ADDONID] uniqueidentifier,
[ADDONNAME] nvarchar(100),
[ADDONTYPECODE] tinyint,
[PRICE] money,
[QUANTITY] smallint
);
insert into @ADDONS_TABLE select
isnull(SALESORDERITEMMEMBERSHIPADDON.ID, newid()),
ADDON.ID,
ADDON.NAME,
ADDON.ADDONTYPECODE,
T.membershipaddon.value('(PRICE)[1]','money') as 'PRICE',
ADDONS.QUANTITY as 'NUMBEROFADDONS'
from @MEMBERSHIPADDONS.nodes('/MEMBERSHIPADDONS/ITEM') T(membershipaddon)
cross apply (
select T.membershipaddon.value('(QUANTITY)[1]','int') as QUANTITY
) ADDONS
inner join dbo.ADDON with (nolock) on
T.membershipaddon.value('(ADDONID)[1]','uniqueidentifier') = ADDON.ID
left join dbo.SALESORDERITEMMEMBERSHIPADDON on
SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = @ID and
SALESORDERITEMMEMBERSHIPADDON.ADDONID = ADDON.ID
where ADDONS.QUANTITY > 0
--cr 9.17.12 Removing error. If this condition exists on the membership, it will fail in the complete order sproc
--If we fail here, resolving the order later will be more time-consuming for the client
-- Number of members validation
--if @MEMBERS is null
--raiserror('ERR_ATLEASTONEMEMBER', 13, 1);
select @ISGIFT = case when @GIVENBYID is null then 0 else @ISGIFT end
declare @MEMBERS_TABLE table (
[ID] uniqueidentifier,
[CONSTITUENTID] uniqueidentifier,
[ISPRIMARY] bit,
[MEMBERID] uniqueidentifier,
[ISCAREGIVER] bit,
[FIRSTNAME] nvarchar(50),
[KEYNAME] nvarchar(100),
[TITLECODEID] nvarchar(100),
[EMAIL] dbo.UDT_EMAILADDRESS,
[PHONE] nvarchar(100),
[ISNEWCONSTITUENT] bit,
[ADDONID] uniqueidentifier,
[REVIEWED] bit, --Whether the member as been updated by the member cursor
--If we determine that this add-on member makes the quantity of add-ons larger than submitted (QUANTITY in @ADDONS_TABLE)
--Then we will null its ADDONID and mark it for deletion
--Once everyone has been reviewed, we will delete enough members to make the membership member count valid
[ADDONMARKEDFORDELETION] bit --When we delete a member (the comment above), we will start with the members that have been "marked".
--If there is enough space on the membership for this member, we will allow them to stay
);
insert into @MEMBERS_TABLE
select
T.members.value('(ID)[1]','uniqueidentifier') as 'ID',
case [CONSTITUENTID].[VALUE] when '00000000-0000-0000-0000-000000000000' then null else [CONSTITUENTID].[VALUE] end as 'CONSTITUENTID',
isnull(T.members.value('(ISPRIMARY)[1]','bit'),0) as 'ISPRIMARY',
case [MEMBERID].[VALUE] when '00000000-0000-0000-0000-000000000000' then null else [MEMBERID].[VALUE] end as 'MEMBERID',
isnull(T.members.value('(ISCAREGIVER)[1]','bit'), 0) as 'ISCAREGIVER',
isnull(T.members.value('(FIRSTNAME)[1]','nvarchar(50)'),'') as 'FIRSTNAME',
isnull(T.members.value('(KEYNAME)[1]','nvarchar(100)'),'') as 'KEYNAME',
dbo.UFN_TITLECODE_GETID(T.members.value('(TITLE)[1]','nvarchar(100)')) as 'TITLECODEID',
isnull(T.members.value('(EMAIL)[1]','dbo.UDT_EMAILADDRESS'),'') as 'EMAIL',
isnull(T.members.value('(PHONE)[1]','nvarchar(100)'),'') as 'PHONE',
0 as ISNEWCONSTITUENT,
case [ADDONID].[VALUE] when '00000000-0000-0000-0000-000000000000' then null else [ADDONID].[VALUE] end as 'ADDONID',
0 as REVIEWED,
0 as ADDONMARKEDFORDELETION
from @MEMBERS.nodes('/MEMBERS/ITEM') T(members)
outer apply (
select T.members.value('(CONSTITUENTID)[1]','uniqueidentifier') as VALUE
) as [CONSTITUENTID]
outer apply (
select T.members.value('(MEMBERID)[1]','uniqueidentifier') as VALUE
) as [MEMBERID]
outer apply (
select T.members.value('(ADDONID)[1]','uniqueidentifier') as VALUE
) as [ADDONID]
update @MEMBERS_TABLE set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
--Checking if address is valid for use so we don't have to do the check each time through the loop
declare @VALIDADDRESS as bit = 0
if @STATEID = '00000000-0000-0000-0000-000000000000'
set @STATEID = null
set @ADDRESSBLOCK = isnull(@ADDRESSBLOCK,'')
set @CITY = isnull(@CITY, '')
set @POSTCODE = isnull(@POSTCODE, '')
if (@COUNTRYID is not null and @COUNTRYID <> '00000000-0000-0000-0000-000000000000') and
(
@STATEID is not null or
@ADDRESSBLOCK <> '' or
@CITY <> '' or
@POSTCODE <> ''
)
begin
set @VALIDADDRESS = 1;
end
--Constituent matching setting
declare @AUTOMATCHTHRESHOLD decimal(20,4) = dbo.UFN_CONSTITUENTDUPLICATESEARCHSETTINGS_GETAUTOMATCHTHRESHOLD_BYID('7BDE63AA-73B8-4A31-BE9F-82D92B67E2F4')
if @AUTOMATCHTHRESHOLD is null
set @AUTOMATCHTHRESHOLD = 95
--Member variables for automatching and addon validation work
declare @MEMBER_ID uniqueidentifier
declare @CONSTITUENTID uniqueidentifier
declare @FIRSTNAME nvarchar(50)
declare @KEYNAME nvarchar(100)
declare @PHONE nvarchar(100)
declare @TITLECODEID uniqueidentifier
declare @EMAIL dbo.UDT_EMAILADDRESS
declare @ISPRIMARY bit
declare @ADDONID uniqueidentifier
--Determine auto-renewal
declare @PROGRAMALLOWSMULTIPLEMEMBERSHIPS bit = null
select @PROGRAMALLOWSMULTIPLEMEMBERSHIPS = ALLOWMULTIPLEMEMBERSHIPS
from dbo.MEMBERSHIPPROGRAM
where ID = @MEMBERSHIPPROGRAMID
if @MEMBERSHIPID = '00000000-0000-0000-0000-000000000000'
set @MEMBERSHIPID = null
declare @ISAUTORENEWAL bit = 0
if
@MEMBERSHIPID is null and
@VALIDADDRESS = 1 and --If the address isn't valid, we can't auto-match on a constituent to do auto-reneal
@PROGRAMALLOWSMULTIPLEMEMBERSHIPS = 0
begin
--Auto-renewal is done based only on the primary member
--We need to do a constituent match on primary if we don't already have a constituentid
select top 1
@MEMBER_ID = [ID],
@CONSTITUENTID = [CONSTITUENTID],
@FIRSTNAME = isnull(rtrim(ltrim([FIRSTNAME])),''),
@KEYNAME = isnull(rtrim(ltrim([KEYNAME])),''),
@TITLECODEID = [TITLECODEID],
@PHONE = isnull(rtrim(ltrim([PHONE])),''),
@EMAIL = isnull(rtrim(ltrim([EMAIL])),'')
from @MEMBERS_TABLE
where ISPRIMARY = 1
declare @MATCHES table (
CONSTITUENTID uniqueidentifier,
MATCHPERCENTAGE tinyint
)
if @CONSTITUENTID is null
begin
--Check top constituent match with a membership
insert into @MATCHES
select top 50 --Just to limit us to something sane
CONSTITUENTID,
MATCHPERCENTAGE
from dbo.UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_2 (
@TITLECODEID,
@FIRSTNAME,
'', -- @MIDDLENAME: We don't do middle name currently
@KEYNAME,
null, --@SUFFIXCODEID: We don't do suffix currently
@ADDRESSBLOCK,
@POSTCODE,
@COUNTRYID,
0, -- @ISORGANIZATION bit: We assume individuals currently
0, -- @ISGROUP bit: We assume individuals currently
null, --@LOOKUPID
null, --@ALTERNATELOOKUPIDS
@EMAIL,
@PHONE,
@AUTOMATCHTHRESHOLD, --@OVERALLMATCHTHRESHOLD: our bail-out threshold
@AUTOMATCHTHRESHOLD
) as [MATCHES]
order by MATCHPERCENTAGE desc
end
else
begin
insert into @MATCHES
values (@CONSTITUENTID, 100)
--If this constituent has more than 1 membership for this program, we don't want to keep them around
--We'll be checking that next
set @CONSTITUENTID = null
end
--If there is a top-match with a membership for this program, let's run with them
declare @TOPMATCHPERCENTAGE tinyint = (select max(MATCHPERCENTAGE) from @MATCHES)
select top 1
@CONSTITUENTID = CONSTITUENTID,
@MEMBERSHIPID = [HASMEMBERSHIP].MEMBERSHIPID --***Setting @MEMBERSHIPID for renewal here (which could--still--be null). I snuck it in there, but it's kind of a big deal.***
from @MATCHES [MATCHES]
cross apply ( --We can cross apply because we're doing a count and will get a row from this
select
top 1 cast(ID as uniqueidentifier) as MEMBERSHIPID,
MEMBERSHIPCOUNT
from (
select
min(cast([MEMBERSHIP].ID as nvarchar(36))) as ID, --I want to get a membershipid if the constituent only has one. Using an aggregate here just so I can get count and the id at the same time.
count([MEMBERSHIP].ID) as MEMBERSHIPCOUNT
from dbo.MEMBERSHIP
inner join dbo.MEMBER on
MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
where
MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
MEMBER.CONSTITUENTID = [MATCHES].CONSTITUENTID and
MEMBER.ISDROPPED = 0
) [MEMBERSHIPS]
) [HASMEMBERSHIP]
where
MATCHPERCENTAGE = @TOPMATCHPERCENTAGE and
[HASMEMBERSHIP].MEMBERSHIPCOUNT <= 1 --Only want to renew if the constituent has one membership
order by [HASMEMBERSHIP].[MEMBERSHIPID] desc --Null last. We still want to set ConstituentID even if they don't have a membership so we don't have to do this check again later.
update @MEMBERS_TABLE
set CONSTITUENTID = @CONSTITUENTID
where ID = @MEMBER_ID
if @MEMBERSHIPID is not null
set @ISAUTORENEWAL = 1
end
--End auto-renewal
declare @RENEWALMEMBERS table (
[MEMBERID] uniqueidentifier,
[ISPRIMARY] bit,
[ISDROPPED] bit,
[CONSTITUENTID] uniqueidentifier,
[FIRSTNAME] nvarchar(50),
[KEYNAME] nvarchar(100),
[NICKNAME] nvarchar(50),
[HASDUPLICATEACTIVEMEMBERSHIP] bit
)
if @MEMBERSHIPID is not null --If we're doing a renewal, we'll want to do a name-match on members. Getting members as a set for performance
begin
insert into @RENEWALMEMBERS (
[MEMBERID],
[CONSTITUENTID],
[ISPRIMARY],
[ISDROPPED],
[FIRSTNAME],
[KEYNAME],
[HASDUPLICATEACTIVEMEMBERSHIP]
)
select
[MEMBERID],
[CONSTITUENTID],
[ISPRIMARY],
[ISDROPPED],
[FIRSTNAME],
[KEYNAME],
[HASDUPLICATEACTIVEMEMBERSHIP]
from dbo.UFN_MEMBERSHIP_MEMBER_WITHALIASANDNICKNAME(@MEMBERSHIPID)
end
--Creating constituent records for members without them
--Updating constituent records for title, firstname
declare @ISNEWCONSTITUENT bit = 0
--Keep track of add-on members and their quantity
declare @VERIFYADDONCOUNT table (ADDONID uniqueidentifier, QUANTITY integer)
declare MEMBER_CURSOR cursor local fast_forward for
select
[ID],
[CONSTITUENTID],
isnull(ltrim(rtrim([FIRSTNAME])),''),
isnull(ltrim(rtrim([KEYNAME])),''),
[TITLECODEID],
isnull(ltrim(rtrim([PHONE])),''),
isnull(ltrim(rtrim([EMAIL])),''),
isnull([ISPRIMARY], 0),
[ADDONID]
from @MEMBERS_TABLE;
open MEMBER_CURSOR
fetch next from MEMBER_CURSOR into @MEMBER_ID, @CONSTITUENTID, @FIRSTNAME, @KEYNAME, @TITLECODEID, @PHONE, @EMAIL, @ISPRIMARY, @ADDONID
while @@FETCH_STATUS = 0
begin
set @ISNEWCONSTITUENT = 0
if
@CONSTITUENTID is not null and
exists(select 1 from @MEMBERS_TABLE where CONSTITUENTID = @CONSTITUENTID and REVIEWED = 1)
begin
set @CONSTITUENTID = null
end
if --Make sure the constituent we passed in doesn't have a competing membership for this program
@PROGRAMALLOWSMULTIPLEMEMBERSHIPS = 0 and
not(@ISPRIMARY = 1 and @ISAUTORENEWAL = 1) and --We already did this check for the autorenewal primary member
@CONSTITUENTID is not null and
exists (
select 1
from dbo.MEMBERSHIP
inner join dbo.MEMBER on
MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
where
MEMBER.CONSTITUENTID = @CONSTITUENTID and
MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
(
MEMBERSHIPID <> @MEMBERSHIPID or
@MEMBERSHIPID is null
)
)
begin
set @CONSTITUENTID = null
end
if @CONSTITUENTID is null
begin
if len(@KEYNAME) = 0
begin
delete @MEMBERS_TABLE where ID = @MEMBER_ID
end
else
begin
--Renewal: See if constituent is a member on this membership--just a name match
if @MEMBERSHIPID is not null
begin
select top 1 @CONSTITUENTID = [MEMBERS].CONSTITUENTID
from @RENEWALMEMBERS [MEMBERS]
where
lower([MEMBERS].KEYNAME) = lower(@KEYNAME) and
(
[MEMBERS].FIRSTNAME = '' or
@FIRSTNAME = '' or
lower([MEMBERS].FIRSTNAME) = lower(@FIRSTNAME) --Actually, first name, nickname, or alias
) and
--If the constituent has another membership for this program, make sure that the program is still okay with that
(
@PROGRAMALLOWSMULTIPLEMEMBERSHIPS = 1 or --Program allows more than one
[MEMBERS].[HASDUPLICATEACTIVEMEMBERSHIP] = 0 --Member doesn't have another membership for this program
) and
not exists(select 1 from @MEMBERS_TABLE where CONSTITUENTID = [MEMBERS].CONSTITUENTID and REVIEWED = 1) --Not already listed as a member
end
--Check the database for this constituent
if @CONSTITUENTID is null
begin
select top 1 @CONSTITUENTID = CONSTITUENTID
from dbo.UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_2 (
@TITLECODEID,
@FIRSTNAME,
'', -- @MIDDLENAME: We don't do middle name currently
@KEYNAME,
null, --@SUFFIXCODEID: We don't do suffix currently
@ADDRESSBLOCK,
@POSTCODE,
@COUNTRYID,
0, -- @ISORGANIZATION bit: We assume individuals currently
0, -- @ISGROUP bit: We assume individuals currently
null, --@LOOKUPID
null, --@ALTERNATELOOKUPIDS
@EMAIL,
@PHONE,
@AUTOMATCHTHRESHOLD, --@OVERALLMATCHTHRESHOLD: our bail-out threshold
@AUTOMATCHTHRESHOLD
) as [MATCHES]
where
--Not already listed as a member on this membership
not exists(select 1 from @MEMBERS_TABLE where CONSTITUENTID = [MATCHES].CONSTITUENTID and REVIEWED = 1) and
--If the constituent has another membership for this program, make sure that the program is still okay with that
(
@PROGRAMALLOWSMULTIPLEMEMBERSHIPS = 1 or --Program allows more than one
--Member doesn't have another membership for this program
not exists (
select 1
from dbo.[MEMBER] as [MEM]
inner join dbo.[MEMBERSHIP]
on [MEM].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
where
[MEM].[ISDROPPED] = 0 and
[MEM].[CONSTITUENTID] = [MATCHES].CONSTITUENTID and
(
@MEMBERSHIPID is null or
[MEM].[MEMBERSHIPID] <> @MEMBERSHIPID
) and
[MEMBERSHIP].[MEMBERSHIPPROGRAMID] = @MEMBERSHIPPROGRAMID
)
)
order by MATCHPERCENTAGE desc
end
if @CONSTITUENTID is null
begin
--We'll create these constituents as part of a mass insert below
--This has been found to reduce chance of deadlocking
--This also allows us remove members that cannot become constituent records prior to doing our post-cursor delete on @MEMBERS_TABLE to fix a potential member count issue
--Basically, it lets us keep more valid members on the membership
set @ISNEWCONSTITUENT = 1
set @CONSTITUENTID = newid()
end
update @MEMBERS_TABLE
set
CONSTITUENTID = @CONSTITUENTID,
ISNEWCONSTITUENT = @ISNEWCONSTITUENT
where ID = @MEMBER_ID
end
end
if @CONSTITUENTID is not null and @ISNEWCONSTITUENT = 0 --Update constituent
begin
if --Constituent update with address update
(@ISPRIMARY = 1 and @ISGIFT = 1) or --Primary gift member
not exists(select 1 from dbo.ADDRESS with (nolock) where CONSTITUENTID = @CONSTITUENTID) --Constituent doesn't have an address
begin
exec dbo.USP_WEBFORMS_CONSTITUENT_ADDRESSINFORMATION_AUTOMATCHORCREATE
@CONSTITUENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@TITLECODEID,
@FIRSTNAME,
@KEYNAME,
@PHONE,
@EMAIL,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE
end
else --Update constituent minus address
begin
exec dbo.USP_WEBFORMS_CONSTITUENT_ADDRESSINFORMATION_AUTOMATCHORCREATE
@CONSTITUENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@TITLECODEID,
@FIRSTNAME,
@KEYNAME,
@PHONE,
@EMAIL
end
end
--If member hasn't been removed
if exists(select 1 from @MEMBERS_TABLE where ID = @MEMBER_ID)
begin
--Mark member as reviewed
update @MEMBERS_TABLE
set REVIEWED = 1
where ID = @MEMBER_ID
--See if add-on member is outside valid add-on quantities
--If they are, we'll remove them from the membership
if @ADDONID is not null
begin
merge @VERIFYADDONCOUNT as [TARGET]
using (select @ADDONID as ADDONID) as [SOURCE]
on (TARGET.ADDONID = SOURCE.ADDONID)
when matched then
update
set [QUANTITY] += 1
when not matched then
insert (
[ADDONID],
[QUANTITY]
)
values (
@ADDONID,
1
);
--If add-on shouldn't exist, mark for deletion and null addonid
if exists (
select 1
from @VERIFYADDONCOUNT [TESTCOUNT]
left join @ADDONS_TABLE [TRUECOUNT] on
[TESTCOUNT].ADDONID = [TRUECOUNT].ADDONID
where
[TESTCOUNT].ADDONID = @ADDONID and
(
[TRUECOUNT].ADDONID is null or --Add-on shouldn't exist
[TRUECOUNT].QUANTITY < [TESTCOUNT].QUANTITY --Add-on is too many for transaction
)
)
begin
update @MEMBERS_TABLE
set
[ADDONMARKEDFORDELETION] = 1,
ADDONID = null
where ID = @MEMBER_ID
end
end
end
fetch next from MEMBER_CURSOR into @MEMBER_ID, @CONSTITUENTID, @FIRSTNAME, @KEYNAME, @TITLECODEID, @PHONE, @EMAIL, @ISPRIMARY, @ADDONID
end
close MEMBER_CURSOR
deallocate MEMBER_CURSOR
--cr 9.17.12 Removing error. If a primary member isn't on the membership, it will fail in the complete order sproc
--If we fail here, the constituent records won't be created and resolving the order will be more time-consuming for the client
--if not exists
--(
--select 1
--from @MEMBERS_TABLE
--where ISPRIMARY = 1
--)
--begin
--raiserror('ERR_NOPRIMARYMEMBER',13,1)
--end
--Raising error for a constituent appearing on the membership more than once:
--The cursor above should have avoided this.
--This sproc will fail on a db constraint if we got it wrong
--So, just catching this condition early to avoid doing more work than necessary
if exists
(
select count(CONSTITID)
from
(
select CONSTITUENTID CONSTITID
from @MEMBERS_TABLE
) Result
group by CONSTITID
having count(*) > 1
)
raiserror('ERR_CONSTITUENTMORETHANONCE', 13,1);
-- Check for correct number of children, members, and cards
declare @ALLOWEDNUMBEROFMEMBERS smallint = 0;
declare @ALLOWEDNUMBEROFCHILDREN smallint = 0;
declare @CARDSALLOWED smallint = 0;
select
@ALLOWEDNUMBEROFCHILDREN = MEMBERSHIPLEVEL.CHILDRENALLOWED,
@ALLOWEDNUMBEROFMEMBERS = MEMBERSHIPLEVEL.MEMBERSALLOWED,
@CARDSALLOWED = MEMBERSHIPLEVEL.CARDSALLOWED
from
dbo.MEMBERSHIPLEVEL
inner join
dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
inner join
dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVEL.ID = MEMBERSHIPLEVELTERM.LEVELID
where
MEMBERSHIPLEVEL.ID = @MEMBERSHIPLEVELID
and MEMBERSHIPLEVELTERM.ID = @MEMBERSHIPLEVELTERMID;
-- If number of children is greater than allowed, just setting to max
if @ALLOWEDNUMBEROFCHILDREN < @NUMBEROFCHILDREN and @ALLOWEDNUMBEROFCHILDREN > 0
begin
set @NUMBEROFCHILDREN = @ALLOWEDNUMBEROFCHILDREN
end
declare @MEMBERSCOUNT integer = 0
select @MEMBERSCOUNT = count(ID)
from @MEMBERS_TABLE
where ADDONID is null --We accounted for valid add-on member counts in cursor
if @MEMBERSCOUNT > @ALLOWEDNUMBEROFMEMBERS
begin
--Allowing as many members as possible
delete from @MEMBERS_TABLE
where exists(
select 1
from(
select top(@MEMBERSCOUNT - @ALLOWEDNUMBEROFMEMBERS) [MEMBERS].ID
from @MEMBERS_TABLE [MEMBERS]
where ADDONID is null --We've already taken addon member counts into account...
order by
[MEMBERS].[ADDONMARKEDFORDELETION] desc, --Delete 'marked' first
[MEMBERS].[ISPRIMARY] asc --Delete primary last (should be never)
) as [MEMBERS]
where [@MEMBERS_TABLE].[ID] = [MEMBERS].[ID]
)
end
--Get existing memberids for renewals
update @MEMBERS_TABLE
set [MEMBERID] = [MEMBER].[MEMBERID]
from @MEMBERS_TABLE
inner join @RENEWALMEMBERS [MEMBER]
on [@MEMBERS_TABLE].[CONSTITUENTID] = [MEMBER].[CONSTITUENTID]
where [MEMBER].[ISDROPPED] = 0
--Create new constituents
insert into dbo.CONSTITUENT
(
[ID],
[KEYNAME],
[FIRSTNAME],
[TITLECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
(
select
CONSTITUENTID,
KEYNAME,
FIRSTNAME,
TITLECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @MEMBERS_TABLE
where ISNEWCONSTITUENT = 1
)
insert into dbo.CONSTITUENTORIGINATION (
[ID],
[ORIGINCODE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
(
select
CONSTITUENTID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @MEMBERS_TABLE
where ISNEWCONSTITUENT = 1
)
/*Start name format defaults*/
insert into [dbo].[NAMEFORMAT] (
[CONSTITUENTID],
[NAMEFORMATTYPECODEID],
[NAMEFORMATFUNCTIONID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[PRIMARYADDRESSEE],
[PRIMARYSALUTATION],
[SEQUENCE]
)
select
[MEMBERS].[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 @MEMBERS_TABLE [MEMBERS]
where
NFD.APPLYTOCODE = 0 and
[MEMBERS].[ISNEWCONSTITUENT] = 1
insert into dbo.[PHONE] (
ID,
CONSTITUENTID,
NUMBER,
ORIGINCODE,
ISPRIMARY,
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
CONSTITUENTID,
PHONE,
1,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @MEMBERS_TABLE
where
[ISNEWCONSTITUENT] = 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 @MEMBERS_TABLE
where
[ISNEWCONSTITUENT] = 1 and
[EMAIL] <> ''
if @VALIDADDRESS = 1
begin
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 @MEMBERS_TABLE
where [ISNEWCONSTITUENT] = 1
insert into dbo.ADDRESSVALIDATIONUPDATE
(
[ID],
[ORIGINCODE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
ADDRESS.ID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @MEMBERS_TABLE [MEMBERS]
inner join dbo.ADDRESS
on [MEMBERS].[CONSTITUENTID] = [ADDRESS].[CONSTITUENTID]
where [ISNEWCONSTITUENT] = 1
end
--Membership cards!
declare @CARDS_TABLE table (
[ID] uniqueidentifier,
[MEMBERSHIPCARDID] uniqueidentifier,
[NAMEONCARD] nvarchar(700),
[EXPIRATIONDATE] datetime,
[MEMBERID] uniqueidentifier
);
insert into @CARDS_TABLE
select
newid(),
[MEMBERSHIPCARD].[ID],
case
when [MEMBERSHIPCARD].[ID] is null then dbo.UFN_CONSTITUENT_BUILDNAME([MEMBERS].[CONSTITUENTID])
else [MEMBERSHIPCARD].[NAMEONCARD]
end,
case
when [MEMBERSHIPCARD].[ID] is null then getdate() -- will be updated to @EXPIRATIONDATE when insert into table
else [MEMBERSHIPCARD].[EXPIRATIONDATE]
end,
[MEMBERS].[ID]
from @MEMBERS_TABLE [MEMBERS]
left join dbo.[MEMBERSHIPCARD]
on
[MEMBERSHIPCARD].[MEMBERID] = [MEMBERS].[MEMBERID] and
MEMBERSHIPCARD.STATUSCODE <> 2;
declare @CARDCOUNT smallint;
select @CARDCOUNT = count(ID)
from @CARDS_TABLE;
if @CARDCOUNT > @CARDSALLOWED
begin
--Issuing as many cards as possible
delete from @CARDS_TABLE
where exists(
select 1
from(
select top(@CARDCOUNT - @CARDSALLOWED) [@CARDS_TABLE].[ID]
from @CARDS_TABLE
inner join @MEMBERS_TABLE [MEMBERS]
on [@CARDS_TABLE].[MEMBERID] = [MEMBERS].[ID]
order by
[MEMBERS].[ISPRIMARY] asc,
--Add-on members get last dibs on cards
--Design didn't have a preference here
--They believe the card count limit functionality is flawed and that there's not a good solution here
--Just ordering so the behavior is predictable
[MEMBERS].ADDONID desc
) as [CARDS]
where [CARDS].[ID] = [@CARDS_TABLE].[ID]
)
end
declare @ACTIONCODE tinyint;
declare @ACTION nvarchar(25);
select @ACTIONCODE = dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(@MEMBERSHIPID, @MEMBERSHIPLEVELID, @CURRENTDATE);
declare @ORDERITEMDESCRIPTION nvarchar(255);
set @ORDERITEMDESCRIPTION = isnull(dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(@MEMBERSHIPPROGRAMID) + ' - ' +
dbo.UFN_MEMBERSHIPLEVEL_GETNAME(@MEMBERSHIPLEVELID) + ' (' +
dbo.UFN_MEMBERSHIPLEVELTERM_GETVALUE(@MEMBERSHIPLEVELTERMID) + '): ', '');
declare @STATUSCODE tinyint;
select @STATUSCODE = STATUSCODE from dbo.MEMBERSHIP where ID = @MEMBERSHIPID;
if @ACTIONCODE = 0 or @STATUSCODE = 2
set @ACTION = 'Join';
else if @ACTIONCODE = 1
set @ACTION = 'Renew';
else if @ACTIONCODE = 2
set @ACTION = 'Upgrade';
else if @ACTIONCODE = 3
set @ACTION = 'Downgrade';
else if @ACTIONCODE = 5
set @ACTION = 'Rejoin';
else
set @ACTION = 'Unknown';
set @ORDERITEMDESCRIPTION = @ORDERITEMDESCRIPTION + @ACTION;
update dbo.[SALESORDERITEM]
set
[DESCRIPTION] = isnull(@ORDERITEMDESCRIPTION,''),
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @ID
--Make the CONSTITUENTID the recipient of the order if there is no recipient
if @GIVENBYID is not null and (select [RECIPIENTID] from dbo.[SALESORDER] where [ID] = @SALESORDERID) is null
begin
update dbo.[SALESORDER]
set
[RECIPIENTID] = @GIVENBYID,
[ADDRESSID] = (select top(1) [ID] from dbo.[ADDRESS] where [CONSTITUENTID] = @GIVENBYID and [ISPRIMARY] = 1 and [DONOTMAIL] = 0),
[PHONEID] = (select top(1) [ID] from dbo.[PHONE] where [PHONE].[CONSTITUENTID] = @GIVENBYID and [PHONE].[ISPRIMARY] = 1 and [DONOTCALL] = 0),
[EMAILADDRESSID] = (select top(1) [ID] from dbo.[EMAILADDRESS] where [CONSTITUENTID] = @GIVENBYID and [ISPRIMARY] = 1 and [DONOTEMAIL] = 0),
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @SALESORDERID
end
if @EXPIRATIONDATE is null
begin
if @ACTIONCODE = 0 or @ACTIONCODE = 5 --join/rejoin
set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @CURRENTDATE);
else
begin
declare @CURRENTSTATUS tinyint;
select
@EXPIRATIONDATE = [EXPIRATIONDATE],
@CURRENTSTATUS = [STATUSCODE]
from dbo.[MEMBERSHIP]
where ID = @MEMBERSHIPID;
if @CURRENTSTATUS = 2 --pending membership
set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @CURRENTDATE);
else
set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATERENEWALEXPIRATION_2(@EXPIRATIONDATE, @MEMBERSHIPLEVELTERMID);
end
end
if not exists(select 1 from dbo.[SALESORDERITEMMEMBERSHIP] where [ID] = @ID)
begin
insert into dbo.[SALESORDERITEMMEMBERSHIP]
(
ID,
MEMBERSHIPID,
MEMBERSHIPPROGRAMID,
MEMBERSHIPPROGRAMNAME,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
MEMBERSHIPLEVELTYPECODEID,
NUMBEROFCHILDREN,
COMMENTS,
ISGIFT,
SENDRENEWALCODE,
EXPIRATIONDATE,
GIVENBYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@MEMBERSHIPID,
@MEMBERSHIPPROGRAMID,
isnull((select [MEMBERSHIPPROGRAM].[NAME] from dbo.[MEMBERSHIPPROGRAM] where [MEMBERSHIPPROGRAM].[ID] = @MEMBERSHIPPROGRAMID),''),
@MEMBERSHIPLEVELID,
@MEMBERSHIPLEVELTERMID,
@MEMBERSHIPLEVELTYPECODEID,
@NUMBEROFCHILDREN,
@COMMENTS,
case when @GIVENBYID is null then 0 else @ISGIFT end,
@SENDRENEWALCODE,
@EXPIRATIONDATE,
case when @ISGIFT = 0 then null else @GIVENBYID end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
else
begin
update dbo.[SALESORDERITEMMEMBERSHIP]
set
MEMBERSHIPID = @MEMBERSHIPID,
MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID,
MEMBERSHIPPROGRAMNAME = isnull((select [MEMBERSHIPPROGRAM].[NAME] from dbo.[MEMBERSHIPPROGRAM] where [MEMBERSHIPPROGRAM].[ID] = @MEMBERSHIPPROGRAMID),''),
MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID,
MEMBERSHIPLEVELTYPECODEID = @MEMBERSHIPLEVELTYPECODEID,
NUMBEROFCHILDREN = @NUMBEROFCHILDREN,
COMMENTS = @COMMENTS,
ISGIFT = case when @GIVENBYID is null then 0 else @ISGIFT end,
SENDRENEWALCODE = @SENDRENEWALCODE,
EXPIRATIONDATE = @EXPIRATIONDATE,
GIVENBYID = case when @ISGIFT = 0 then null else @GIVENBYID end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID
end
--delete, add, and update sales order items
delete dbo.SALESORDERITEM
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMMEMBERSHIPADDON on
SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPADDON.ID
where
SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = @ID and
not exists (
select 1
from @ADDONS_TABLE [ADDONS]
where SALESORDERITEM.ID = ADDONS.ID
)
update dbo.SALESORDERITEM
set
[DESCRIPTION] = ADDONS.ADDONNAME,
QUANTITY = ADDONS.QUANTITY,
PRICE = ADDONS.PRICE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.SALESORDERITEM
inner join @ADDONS_TABLE [ADDONS] on
SALESORDERITEM.ID = [ADDONS].ID
insert into dbo.SALESORDERITEM
(
ID,
SALESORDERID,
TYPECODE,
[DESCRIPTION],
QUANTITY,
PRICE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
(
select
[ADDONS].ID,
@SALESORDERID,
16,
[ADDONS].ADDONNAME,
[ADDONS].QUANTITY,
[ADDONS].PRICE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @ADDONS_TABLE [ADDONS]
where not exists (select 1 from dbo.SALESORDERITEM where ID = ADDONS.ID)
);
update dbo.SALESORDERITEMMEMBERSHIPADDON
set
SALESORDERITEMMEMBERSHIPID = @ID,
ADDONTYPECODE = ADDONS.ADDONTYPECODE,
ADDONID = ADDONS.ADDONID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.SALESORDERITEMMEMBERSHIPADDON
inner join @ADDONS_TABLE [ADDONS] on
SALESORDERITEMMEMBERSHIPADDON.ID = [ADDONS].ID
insert into dbo.SALESORDERITEMMEMBERSHIPADDON
(
ID,
SALESORDERITEMMEMBERSHIPID,
ADDONTYPECODE,
ADDONID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
(
select
[ADDONS].ID,
@ID,
[ADDONS].ADDONTYPECODE,
[ADDONS].ADDONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @ADDONS_TABLE [ADDONS]
where not exists (select 1 from dbo.SALESORDERITEMMEMBERSHIPADDON where ID = ADDONS.ID)
);
-- The following commented out execute is refactored to be inline below.
--exec dbo.USP_SALESORDERITEMMEMBERSHIP_GETMEMBERS_ADDFROMXML @ID, @MEMBERS, @CHANGEAGENTID, @CURRENTDATE;
insert into dbo.[SALESORDERITEMMEMBER]
([SALESORDERITEMMEMBERSHIPID],
[CONSTITUENTID],
[ID],
[ISCAREGIVER],
[ISPRIMARY],
[MEMBERID],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select
@ID,
[CONSTITUENTID],
[ID],
[ISCAREGIVER],
[ISPRIMARY],
[MEMBERID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @MEMBERS_TABLE;
-- The following commented out execute is refactored to be inline below.
--exec dbo.USP_SALESORDERITEMMEMBERSHIP_GETMEMBERSHIPCARDS_ADDFROMXML @MEMBERS, @CHANGEAGENTID, @CURRENTDATE;
insert into dbo.[SALESORDERITEMMEMBERSHIPCARD]
(
[ID],
[MEMBERSHIPCARDID],
[SALESORDERITEMMEMBERID],
[NAMEONCARD],
[EXPIRATIONDATE],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
[ID],
[MEMBERSHIPCARDID],
[MEMBERID],
[NAMEONCARD],
case when [EXPIRATIONDATE] is null then null
when [EXPIRATIONDATE] is not null and [EXPIRATIONDATE] > @EXPIRATIONDATE then EXPIRATIONDATE
else @EXPIRATIONDATE
end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@CARDS_TABLE;
--This sp is called after the order is paid. We don't want to potentially change the order amount after that.
--exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
--exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
--exec dbo.USP_SALESORDER_CALCULATETAXES @SALESORDERID, @CHANGEAGENTID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;