UFN_DISCOUNT_ELIGIBLEFORORDER
Determines if a discount is eligible for order - constituency code and memberships of constituent.
Return
| Return Type |
|---|
| bit |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DISCOUNTID | uniqueidentifier | IN | |
| @SALESORDERID | uniqueidentifier | IN |
Definition
Copy
CREATE function [dbo].[UFN_DISCOUNT_ELIGIBLEFORORDER]
(
@DISCOUNTID uniqueidentifier,
@SALESORDERID uniqueidentifier
)
returns bit
with execute as caller
as begin
declare @CONSTITUENTID uniqueidentifier;
declare @ELIGIBLEMEMBEREXISTS bit = 0;
declare @ELIGIBLECONSTITUENCYEXISTS bit = 0;
declare @ELIGIBLEADDRESSEXISTS bit = 0; -- LeeCh, 07/27/2009, Check address restriction
declare @CURRENTDATE datetime;
declare @RESULT bit;
set @RESULT = 1
if exists(select * from dbo.DISCOUNTMEMBER where DISCOUNTID = @DISCOUNTID)
set @ELIGIBLEMEMBEREXISTS = 1
if exists(select * from dbo.DISCOUNTCONSTITUENCY where DISCOUNTID = @DISCOUNTID)
set @ELIGIBLECONSTITUENCYEXISTS = 1
if exists(select ID from dbo.DISCOUNTADDRESS where ID = @DISCOUNTID)
set @ELIGIBLEADDRESSEXISTS = 1
if @ELIGIBLEMEMBEREXISTS = 1 or @ELIGIBLECONSTITUENCYEXISTS = 1 or @ELIGIBLEADDRESSEXISTS = 1
begin
declare @SALESMETHODTYPECODE tinyint;
select
@SALESMETHODTYPECODE = [SALESMETHODTYPECODE],
@CONSTITUENTID = CONSTITUENTID
from
dbo.[SALESORDER]
where
[ID] = @SALESORDERID;
--Pending online orders (for the most part) do not have constituents until payment. We shouldn't exclude them.
if @CONSTITUENTID is null and not @SALESMETHODTYPECODE = 2
set @RESULT = 0;
else
begin
set @CURRENTDATE = getdate()
if @ELIGIBLECONSTITUENCYEXISTS = 1
begin
-- LeeCh, 07/28/2009
-- If there is user-defined constituency the same as system constituency,
-- the user-defined constituency should be eligible also.
if exists
(
select
CONSTITUENCY.ID
from
dbo.CONSTITUENCY
inner join CONSTITUENCYDEFINITION as USERDEFINEDCONSTITUENCYDEFINITION on CONSTITUENCY.CONSTITUENCYCODEID = USERDEFINEDCONSTITUENCYDEFINITION.ID and USERDEFINEDCONSTITUENCYDEFINITION.ISSYSTEM = 0
left join CONSTITUENCYDEFINITION as SYSTEMCONSTITUENCYDEFINITION on USERDEFINEDCONSTITUENCYDEFINITION.DESCRIPTION = SYSTEMCONSTITUENCYDEFINITION.DESCRIPTION and SYSTEMCONSTITUENCYDEFINITION.ISSYSTEM = 1
inner join dbo.DISCOUNTCONSTITUENCY on (CONSTITUENCY.CONSTITUENCYCODEID = DISCOUNTCONSTITUENCY.CONSTITUENCYCODEID or SYSTEMCONSTITUENCYDEFINITION.ID = DISCOUNTCONSTITUENCY.CONSTITUENCYSYSTEMNAMEID)
where
CONSTITUENCY.CONSTITUENTID = @CONSTITUENTID and
DISCOUNTCONSTITUENCY.DISCOUNTID = @DISCOUNTID and
(CONSTITUENCY.DATEFROM is null or dbo.UFN_DATE_GETEARLIESTTIME(CONSTITUENCY.DATEFROM) <= @CURRENTDATE) and
(CONSTITUENCY.DATETO is null or dbo.UFN_DATE_GETLATESTTIME(CONSTITUENCY.DATETO) >= @CURRENTDATE)
)
begin
--at least one constituency from discount exists on constituent
set @RESULT = 1
end
-- LeeCh, 07/19/2009
-- Check system constituencies
else if dbo.UFN_DISCOUNT_ISCONSTITUENTRECOGNITION(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('3dfac92e-78bd-4051-abdc-02c675deb8f6', @DISCOUNTID) = 1 --Recognition
set @RESULT = 1
else if dbo.UFN_DISCOUNT_ISCONSTITUENTMEMBER(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('2d11326e-8f3b-4322-9797-57c1aacfa5df', @DISCOUNTID) = 1 --Member
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('F828E957-5F5E-479A-8F23-2FFD6C7C68FF', @DISCOUNTID) = 1 --Board member
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISSTAFF(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('6093915E-ADE9-42BE-88AE-304731754467', @DISCOUNTID) = 1 --Staff
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISDONOR(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('70165682-4324-46EC-9439-83FC0CC67E7F', @DISCOUNTID) = 1 --Donor
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISFUNDRAISER(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('D2DCA06A-BE6E-40B3-B95D-59A926181923', @DISCOUNTID) = 1 --Fundraiser
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISVOLUNTEER(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('E7489703-3D63-4017-A2BC-88C092563C5D', @DISCOUNTID) = 1 --Volunteer
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISREGISTRANT(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('C49D4B46-72A7-4206-91AA-BEABA2323E3C', @DISCOUNTID) = 1 --Event registrant
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISPATRON(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('A843B859-4C6B-445B-97F3-179582E270A5', @DISCOUNTID) = 1 --Patron
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISPROSPECT(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('00E748FB-940D-4A7D-A133-C148B29410A8', @DISCOUNTID) = 1 --Major giving prospect
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634', @DISCOUNTID) = 1 --Planned giver
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISSTUDENT(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('4DB8F4FC-BC43-421D-B592-69BEF109B5FC', @DISCOUNTID) = 1 --Student
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISALUMNUS(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('46EC3424-BA54-4431-A7DC-C6CEBB3B4592', @DISCOUNTID) = 1 --Alumnus
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISLOYALDONOR(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('E5A0EA42-65BA-4B25-AFE2-9B709F99E72B', @DISCOUNTID) = 1 --Loyal donor
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISMAJORDONOR(@CONSTITUENTID) = 1 and dbo.UFN_DISCOUNT_HASCONSTITUENCY_2('1A9BFE80-604D-4B5B-8065-E751DDF6EF39', @DISCOUNTID) = 1 --Major donor
set @RESULT = 1
else
begin
--no constituencies from discount exist on constituent
set @RESULT = 0
end
end
if @ELIGIBLEMEMBEREXISTS = 1 and @RESULT = 1
begin
if exists
(
select
*
from
dbo.MEMBER
inner join dbo.MEMBERSHIP on
MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.MEMBERSHIPLEVELTERM
on [MEMBERSHIP].[MEMBERSHIPLEVELTERMID] = [MEMBERSHIPLEVELTERM].[ID]
inner join dbo.DISCOUNTMEMBER on
MEMBERSHIP.MEMBERSHIPPROGRAMID = DISCOUNTMEMBER.MEMBERSHIPPROGRAMID and
(DISCOUNTMEMBER.MEMBERSHIPLEVELID is null or
MEMBERSHIP.MEMBERSHIPLEVELID = DISCOUNTMEMBER.MEMBERSHIPLEVELID)
where
MEMBER.CONSTITUENTID = @CONSTITUENTID and
DISCOUNTMEMBER.DISCOUNTID = @DISCOUNTID and
MEMBER.ISDROPPED = 0 and
MEMBERSHIP.STATUSCODE = 0
)
begin
--at least one member/level from discount exists and is active on constituent
set @RESULT = 1
end
else
--no member/levels from discount are active on constituent
--need to check order
begin
if exists
(
select
*
from
dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMMEMBERSHIP on
SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID
inner join dbo.DISCOUNTMEMBER on
SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = DISCOUNTMEMBER.MEMBERSHIPPROGRAMID and
(DISCOUNTMEMBER.MEMBERSHIPLEVELID is null or
SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELID = DISCOUNTMEMBER.MEMBERSHIPLEVELID)
left join dbo.SALESORDERITEMMEMBER on
SALESORDERITEMMEMBERSHIP.ID =SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID
where
(
--Online orders will not have member records while the order is pending. They are created on payment.
(@SALESMETHODTYPECODE = 2 and SALESORDERITEMMEMBERSHIP.[MEMBERSHIPID] is null) or
(SALESORDERITEMMEMBER.CONSTITUENTID = @CONSTITUENTID)
) and
SALESORDERITEM.SALESORDERID = @SALESORDERID and
DISCOUNTMEMBER.DISCOUNTID = @DISCOUNTID and
SALESORDERITEM.TYPECODE = 1
)
begin
--at least one member/level from discount exists in order
set @RESULT = 1
end
else
begin
--no member/level from discount is active on constituent or in the order
set @RESULT = 0
end
end
end
if @ELIGIBLEADDRESSEXISTS = 1 and @RESULT = 1
begin
declare @SELECTIONID uniqueidentifier
select @SELECTIONID = IDSETREGISTERID
from dbo.DISCOUNTADDRESS
where DISCOUNTADDRESS.ID = @DISCOUNTID
if dbo.UFN_CONSTITUENT_ISINCONSTITUENTBYADDRESS(@CONSTITUENTID, @SELECTIONID) = 1
set @RESULT = 1;
else
set @RESULT = 0;
end
end
end
return @RESULT
end