UFN_COMBINATION_ELIGIBLEFORORDER
Determines if a combination is eligible for order - constituency code and memberships of constituent.
Return
| Return Type |
|---|
| bit |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @COMBINATIONID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN |
Definition
Copy
CREATE function [dbo].[UFN_COMBINATION_ELIGIBLEFORORDER]
(
@COMBINATIONID uniqueidentifier,
@CONSTITUENTID uniqueidentifier
)
returns bit
with execute as caller
as begin
declare @ELIGIBLEMEMBEREXISTS bit = 0;
declare @ELIGIBLECONSTITUENCYEXISTS bit = 0;
declare @CURRENTDATE datetime;
declare @RESULT bit;
set @RESULT = 1
if exists(select COMBINATIONMEMBER.ID from dbo.COMBINATIONMEMBER where COMBINATIONID = @COMBINATIONID)
set @ELIGIBLEMEMBEREXISTS = 1
if exists(select COMBINATIONCONSTITUENCY.ID from dbo.COMBINATIONCONSTITUENCY where COMBINATIONID = @COMBINATIONID)
set @ELIGIBLECONSTITUENCYEXISTS = 1
if @ELIGIBLEMEMBEREXISTS = 1 or @ELIGIBLECONSTITUENCYEXISTS = 1
begin
if @CONSTITUENTID is null
set @RESULT = 0;
else
begin
set @CURRENTDATE = getdate()
if @ELIGIBLECONSTITUENCYEXISTS = 1
begin
-- If there is user-defined constituency the same as system constituency,
-- the user-defined constituency should be eligible also.
if exists(
select CONSTITUENCY.ID, USERDEFINEDCONSTITUENCYDEFINITION.DESCRIPTION, SYSTEMCONSTITUENCYDEFINITION.DESCRIPTION
from 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 COMBINATIONCONSTITUENCY
on (
CONSTITUENCY.CONSTITUENCYCODEID = COMBINATIONCONSTITUENCY.CONSTITUENCYCODEID
or SYSTEMCONSTITUENCYDEFINITION.ID = COMBINATIONCONSTITUENCY.CONSTITUENCYSYSTEMNAMEID
)
where CONSTITUENCY.CONSTITUENTID = @CONSTITUENTID
and COMBINATIONCONSTITUENCY.COMBINATIONID = @COMBINATIONID
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 combination exists on constituent
set @RESULT = 1
end
-- Check system constituencies
else if dbo.UFN_DISCOUNT_ISCONSTITUENTRECOGNITION(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('3dfac92e-78bd-4051-abdc-02c675deb8f6') = 1 --Recognition
set @RESULT = 1
else if dbo.UFN_DISCOUNT_ISCONSTITUENTMEMBER(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('2d11326e-8f3b-4322-9797-57c1aacfa5df') = 1 --Member
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('F828E957-5F5E-479A-8F23-2FFD6C7C68FF') = 1 --Board member
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISSTAFF(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('6093915E-ADE9-42BE-88AE-304731754467') = 1 --Staff
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISDONOR(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('70165682-4324-46EC-9439-83FC0CC67E7F') = 1 --Donor
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISFUNDRAISER(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('D2DCA06A-BE6E-40B3-B95D-59A926181923') = 1 --Fundraiser
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISVOLUNTEER(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('E7489703-3D63-4017-A2BC-88C092563C5D') = 1 --Volunteer
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISREGISTRANT(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('C49D4B46-72A7-4206-91AA-BEABA2323E3C') = 1 --Event registrant
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISPATRON(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('A843B859-4C6B-445B-97F3-179582E270A5') = 1 --Patron
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISPROSPECT(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('00E748FB-940D-4A7D-A133-C148B29410A8') = 1 --Major giving prospect
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634') = 1 --Planned giver
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISSTUDENT(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('4DB8F4FC-BC43-421D-B592-69BEF109B5FC') = 1 --Student
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISALUMNUS(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('46EC3424-BA54-4431-A7DC-C6CEBB3B4592') = 1 --Alumnus
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISLOYALDONOR(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('E5A0EA42-65BA-4B25-AFE2-9B709F99E72B') = 1 --Loyal donor
set @RESULT = 1
else if dbo.UFN_CONSTITUENT_ISMAJORDONOR(@CONSTITUENTID) = 1 and dbo.UFN_COMBINATION_HASCONSTITUENCY('1A9BFE80-604D-4B5B-8065-E751DDF6EF39') = 1 --Major donor
set @RESULT = 1
else
begin
--no constituencies from COMBINATION exist on constituent
set @RESULT = 0
end
end
if @ELIGIBLEMEMBEREXISTS = 1 and @RESULT = 1
begin
if exists(select
MEMBER.ID
from
dbo.MEMBER
inner join dbo.MEMBERSHIP on
MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.COMBINATIONMEMBER on
MEMBERSHIP.MEMBERSHIPPROGRAMID = COMBINATIONMEMBER.MEMBERSHIPPROGRAMID and
(COMBINATIONMEMBER.MEMBERSHIPLEVELID is null or
MEMBERSHIP.MEMBERSHIPLEVELID = COMBINATIONMEMBER.MEMBERSHIPLEVELID)
where
MEMBER.CONSTITUENTID = @CONSTITUENTID and
COMBINATIONMEMBER.COMBINATIONID = @COMBINATIONID and
MEMBER.ISDROPPED = 0 and
MEMBERSHIP.STATUSCODE = 0)
begin
--at lesast one member/level from combination exists and is active on constituent
set @RESULT = 1
end
else
--no member/levels from combination are active on constituent
--need to check order
begin
if exists(select
SALESORDERITEM.ID
from
dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMMEMBERSHIP on
SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID
inner join dbo.COMBINATIONMEMBER on
SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = COMBINATIONMEMBER.MEMBERSHIPPROGRAMID and
(COMBINATIONMEMBER.MEMBERSHIPLEVELID is null or
SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELID = COMBINATIONMEMBER.MEMBERSHIPLEVELID)
inner join dbo.SALESORDERITEMMEMBER on
SALESORDERITEMMEMBERSHIP.ID =SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID
inner join dbo.SALESORDER on
SALESORDER.ID = SALESORDERITEM.SALESORDERID
where
SALESORDER.STATUSCODE = 0 and
COMBINATIONMEMBER.COMBINATIONID = @COMBINATIONID and
SALESORDERITEM.TYPECODE = 1 and
SALESORDERITEMMEMBER.CONSTITUENTID = @CONSTITUENTID)
begin
--at least one member/level from combination exists in order
set @RESULT = 1
end
else
begin
--no member/level from combination is active on constituent or in the order
set @RESULT = 0
end
end
end
end
end
return @RESULT
end