USP_SEARCHLIST_MEMBER_DAILYSALESSEARCH
Provides the ability to search for a member.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @MEMBERQUICKSEARCH | nvarchar(100) | IN | Quick search |
| @LASTNAME | nvarchar(100) | IN | Last/Group/Org name |
| @FIRSTNAME | nvarchar(100) | IN | First name |
| @MEMBERSHIPLOOKUPID | nvarchar(50) | IN | Membership ID |
| @PHONENUMBER | nvarchar(50) | IN | Phone number |
| @COUNTRYID | uniqueidentifier | IN | Country |
| @ADDRESSBLOCK | nvarchar(100) | IN | Address |
| @CITY | nvarchar(100) | IN | City |
| @STATEID | uniqueidentifier | IN | State |
| @POSTCODE | nvarchar(20) | IN | ZIP/Postal code |
| @INCLUDEINDIVIDUALS | bit | IN | Individuals |
| @INCLUDEORGANIZATIONS | bit | IN | Organizations |
| @INCLUDEGROUPS | bit | IN | Groups/Households |
| @INCLUDEINACTIVE | bit | IN | Include inactive |
| @INCLUDEDECEASED | bit | IN | Include Deceased |
| @MAXROWS | smallint | IN | Input parameter indicating the maximum number of rows to return. |
Definition
Copy
CREATE procedure dbo.USP_SEARCHLIST_MEMBER_DAILYSALESSEARCH
(
@MEMBERQUICKSEARCH nvarchar(100) = null,
@LASTNAME nvarchar(100) = null,
@FIRSTNAME nvarchar(100) = null,
@MEMBERSHIPLOOKUPID nvarchar(50) = null,
@PHONENUMBER nvarchar(50) = null,
@COUNTRYID uniqueidentifier = null,
@ADDRESSBLOCK nvarchar(100) = null,
@CITY nvarchar(100) = null,
@STATEID uniqueidentifier = null,
@POSTCODE nvarchar(20) = null,
@INCLUDEINDIVIDUALS bit = 1,
@INCLUDEORGANIZATIONS bit = 1,
@INCLUDEGROUPS bit = 0,
@INCLUDEINACTIVE bit = 0,
@INCLUDEDECEASED bit = 0,
@MAXROWS smallint = 100
)
as
set nocount on;
set @MEMBERQUICKSEARCH = ltrim(rtrim(@MEMBERQUICKSEARCH))
declare @QUICKCARDNUMBER nvarchar(200) = @MEMBERQUICKSEARCH
set @MEMBERQUICKSEARCH = replace(replace(@MEMBERQUICKSEARCH,'*','%'),'?','_')
declare @QUICKORGGROUPKEYNAME nvarchar(100) = @MEMBERQUICKSEARCH
declare @QUICKFIRSTNAME nvarchar(100) = ''
declare @QUICKLASTNAME nvarchar(100) = ''
if @MEMBERQUICKSEARCH <> ''
begin
if charindex(',',@MEMBERQUICKSEARCH) > 0
begin
set @QUICKLASTNAME = rtrim(left(@MEMBERQUICKSEARCH,charindex(',',@MEMBERQUICKSEARCH) - 1))
set @QUICKFIRSTNAME = ltrim(right(@MEMBERQUICKSEARCH,len(@MEMBERQUICKSEARCH) - charindex(',',@MEMBERQUICKSEARCH)))
end
else
begin
if charindex(' ',@MEMBERQUICKSEARCH) > 0
begin
set @QUICKFIRSTNAME = rtrim(left(@MEMBERQUICKSEARCH,charindex(' ',@MEMBERQUICKSEARCH)))
set @QUICKLASTNAME = ltrim(right(@MEMBERQUICKSEARCH,len(@MEMBERQUICKSEARCH) - charindex(' ',@MEMBERQUICKSEARCH)))
end
else
set @QUICKLASTNAME = @MEMBERQUICKSEARCH
end
end
-- remove formatting (dashes, parens, etc...) from phone number input
declare @UNFORMATTEDPHONENUMBER nvarchar(100) = dbo.UFN_PHONE_REMOVEFORMATTING(@PHONENUMBER)
select distinct top(@MAXROWS)
[CONSTITUENT].[ID],
dbo.UFN_CONSTITUENT_BUILDNAME([CONSTITUENT].[ID]) as [MEMBERNAME],
[MEMBERSHIPPROGRAM].[NAME] + ' - ' + [MEMBERSHIPLEVEL].[NAME] + ' - ' + [MEMBERSHIPLEVELTERM].[TERM] as [MEMBERSHIPNAME],
(
select top 1 [NAMEONCARD]
from dbo.[MEMBERSHIPCARD]
where [MEMBERID] = [MEMBER].[ID]
) as [NAMEONCARD],
[MEMBERSHIP].[EXPIRATIONDATE],
(
select top 1 dbo.UFN_BUILDFULLADDRESS([ADDRESS].[ID], [ADDRESS].[ADDRESSBLOCK], [ADDRESS].[CITY], [ADDRESS].[STATEID], [ADDRESS].[POSTCODE], [ADDRESS].[COUNTRYID]) as [ADDRESSBLOCK]
from dbo.[ADDRESS]
where
[ADDRESS].[ISPRIMARY] = 1 and
[ADDRESS].[CONSTITUENTID] = [CONSTITUENT].[ID]
) as [ADDRESS],
(
select top 1 [NUMBER]
from dbo.[PHONE]
where
[PHONE].[ISPRIMARY] = 1 and
[PHONE].[CONSTITUENTID] = [CONSTITUENT].[ID]
),
[MEMBER].[ISPRIMARY],
[MEMBERSHIP].[ID]
from dbo.[CONSTITUENT]
inner join dbo.[MEMBER]
on [MEMBER].[CONSTITUENTID] = [CONSTITUENT].[ID]
inner join dbo.[MEMBERSHIP]
on [MEMBERSHIP].[ID] = [MEMBER].[MEMBERSHIPID]
inner join dbo.[MEMBERSHIPPROGRAM]
on [MEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
inner join dbo.[MEMBERSHIPLEVEL]
on [MEMBERSHIP].[MEMBERSHIPLEVELID] = [MEMBERSHIPLEVEL].[ID]
inner join dbo.[MEMBERSHIPLEVELTERM]
on [MEMBERSHIP].[MEMBERSHIPLEVELTERMID] = [MEMBERSHIPLEVELTERM].[ID]
left join dbo.[PHONE]
on [PHONE].[CONSTITUENTID] = [CONSTITUENT].[ID]
left join dbo.[ADDRESS]
on [ADDRESS].[CONSTITUENTID] = [CONSTITUENT].[ID]
where
(
(@INCLUDEINDIVIDUALS = 1 and [CONSTITUENT].[ISORGANIZATION] = 0 and [CONSTITUENT].[ISGROUP] = 0) or
(@INCLUDEORGANIZATIONS = 1 and [CONSTITUENT].[ISORGANIZATION] = 1) or
(@INCLUDEGROUPS = 1 and [CONSTITUENT].[ISGROUP] = 1)
) and
( --Quick Search
( --Name using quick search fields
(
([CONSTITUENT].[ISGROUP] = 0 and [CONSTITUENT].[ISORGANIZATION] = 0) and
(coalesce(@QUICKFIRSTNAME,'') = '' or [CONSTITUENT].[FIRSTNAME] like @QUICKFIRSTNAME + '%') and
(coalesce(@QUICKLASTNAME,'') = '' or [CONSTITUENT].[KEYNAME] like @QUICKLASTNAME + '%')
) or
(
([CONSTITUENT].[ISGROUP] = 1 or [CONSTITUENT].[ISORGANIZATION] = 1) and
(coalesce(@QUICKORGGROUPKEYNAME,'') = '' or [CONSTITUENT].[KEYNAME] like @QUICKORGGROUPKEYNAME + '%')
)
) or
exists (
select 1
from dbo.[MEMBERSHIPCARD]
where
(
[CARDNUMBER] = @MEMBERQUICKSEARCH or
[NAMEONCARD] like @MEMBERQUICKSEARCH + '%'
) and
[MEMBERID] = [MEMBER].[ID]
) or
[MEMBERSHIP].[LOOKUPID] = @MEMBERQUICKSEARCH or
[PHONE].[NUMBER] = @UNFORMATTEDPHONENUMBER
) and
(
( --Name using search fields other than quick search
(
([CONSTITUENT].[ISGROUP] = 0 and [CONSTITUENT].[ISORGANIZATION] = 0) and
(coalesce(@FIRSTNAME,'') = '' or [CONSTITUENT].[FIRSTNAME] like @FIRSTNAME + '%') and
(coalesce(@LASTNAME,'') = '' or [CONSTITUENT].[KEYNAME] like @LASTNAME + '%')
) or
(
([CONSTITUENT].[ISGROUP] = 1 or [CONSTITUENT].[ISORGANIZATION] = 1) and
(coalesce(@LASTNAME,'') = '' or [CONSTITUENT].[KEYNAME] like @LASTNAME + '%')
)
) and
(@MEMBERSHIPLOOKUPID is null or [MEMBERSHIP].[LOOKUPID] like @MEMBERSHIPLOOKUPID + '%') and
(@PHONENUMBER is null or [PHONE].[NUMBERNOFORMAT] like @UNFORMATTEDPHONENUMBER + '%') and
(@COUNTRYID is null or [ADDRESS].[COUNTRYID] = @COUNTRYID) and
(@ADDRESSBLOCK is null or [ADDRESS].[ADDRESSBLOCK] like @ADDRESSBLOCK + '%') and
(@CITY is null or [ADDRESS].[CITY] like @CITY + '%') and
(@STATEID is null or [ADDRESS].[STATEID] = @STATEID) and
(@POSTCODE is null or [ADDRESS].[POSTCODE] like @POSTCODE + '%')
) and
(
@INCLUDEDECEASED = 1 or
not exists (
select 1
from dbo.[DECEASEDCONSTITUENT]
where [DECEASEDCONSTITUENT].[ID] = [CONSTITUENT].[ID]
)
) and
[MEMBER].[ISDROPPED] = 0
order by
[MEMBERSHIPNAME],
[MEMBERSHIP].[ID],
[MEMBER].[ISPRIMARY] desc,
[MEMBERNAME]