USP_SEARCHLIST_PATRON_SEARCH

CREATE procedure dbo.USP_SEARCHLIST_PATRON_SEARCH
(
    @MAGIC 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;

declare @MAGICLASTNAME nvarchar(100),
        @MAGICFIRSTNAME nvarchar(100),
        @CURRENTDATEEARLIESTTIME datetime;

--Work some MAGIC!


if not (@MAGIC is null)
begin
    set @MAGIC = ltrim(rtrim(@MAGIC))

    if charindex(',',@MAGIC) > 0
    begin
        set @MAGICLASTNAME = rtrim(left(@MAGIC,charindex(',',@MAGIC) - 1))
        set @MAGICFIRSTNAME = ltrim(right(@MAGIC,len(@MAGIC) - charindex(',',@MAGIC)))
        if charindex(' ',@MAGICFIRSTNAME) > 0
        begin
            set @MAGICFIRSTNAME = rtrim(left(@MAGICFIRSTNAME,charindex(' ',@MAGICFIRSTNAME)))
        end
    end
    else
    begin
        if charindex(' ',@MAGIC) > 0
        begin
            set @MAGICFIRSTNAME = rtrim(left(@MAGIC,charindex(' ',@MAGIC)))
            set @MAGICLASTNAME = ltrim(right(@MAGIC,len(@MAGIC) - charindex(' ',@MAGIC)))
        end
        else
        begin
            set @MAGICFIRSTNAME = @MAGIC
            set @MAGICLASTNAME = null
        end
        while charindex(' ',@MAGICLASTNAME) > 0
        begin
            set @MAGICLASTNAME = ltrim(right(@MAGICLASTNAME,len(@MAGICLASTNAME) - charindex(' ',@MAGICLASTNAME)))
        end
    end
end

--enough magic


-- remove formatting (dashes, parens, etc...) from phone number input

declare @UNFORMATTEDPHONENUMBER nvarchar(100) = dbo.UFN_PHONE_REMOVEFORMATTING(@PHONENUMBER)
-- in case the quick search was a phone number

declare @UNFORMATTEDQUICKSEARCHPHONENUMBER nvarchar(100) = dbo.UFN_PHONE_REMOVEFORMATTING(@MAGIC);

-- Get basic results

declare @BASICRESULTS as table
(
    ID uniqueidentifier,
    DISPLAYNAME nvarchar(154),
    SORTNAME nvarchar(155)
);
insert into @BASICRESULTS
select distinct top(@MAXROWS)
    CONSTITUENT.ID,
    CONSTITUENT.NAME as DISPLAYNAME,
    case
        when CONSTITUENT.FIRSTNAME is null then CONSTITUENT.KEYNAME
        when CONSTITUENT.FIRSTNAME = '' then CONSTITUENT.KEYNAME
        else CONSTITUENT.KEYNAME + ', ' + CONSTITUENT.FIRSTNAME + case CONSTITUENT.MIDDLENAME when '' then '' else ' ' + left(CONSTITUENT.MIDDLENAME,1) + '.' end
    end as SORTNAME
from dbo.CONSTITUENT
    left join dbo.MEMBER on MEMBER.CONSTITUENTID = CONSTITUENT.ID
    left join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
    left join dbo.MEMBERSHIPCARD on MEMBERSHIPCARD.MEMBERID = MEMBER.ID
    left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID
    left join dbo.[ADDRESS] on [ADDRESS].CONSTITUENTID = CONSTITUENT.ID
where
    (
        @MAGIC is null or
        CONSTITUENT.KEYNAME like @MAGIC + '%' or
        MEMBERSHIPCARD.CARDNUMBER = @MAGIC or
        MEMBERSHIP.LOOKUPID = @MAGIC or
        PHONE.NUMBERNOFORMAT like @UNFORMATTEDQUICKSEARCHPHONENUMBER + '%' or
        (
            CONSTITUENT.FIRSTNAME like @MAGICFIRSTNAME + '%' and
            (@MAGICLASTNAME is null or CONSTITUENT.KEYNAME like @MAGICLASTNAME + '%')
        )
    ) and
    (@FIRSTNAME is null or CONSTITUENT.FIRSTNAME like @FIRSTNAME + '%') and
    (@LASTNAME is null 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
    (@INCLUDEINDIVIDUALS = 1 or CONSTITUENT.ISORGANIZATION = 1 or CONSTITUENT.ISGROUP = 1) and
    (@INCLUDEORGANIZATIONS = 1 or CONSTITUENT.ISORGANIZATION = 0) and
    (@INCLUDEGROUPS = 1 or CONSTITUENT.ISGROUP = 0) and
    (@INCLUDEINACTIVE = 1 or CONSTITUENT.ISINACTIVE = 0) and
    (
        @INCLUDEDECEASED = 1 or 
        not exists (
            select 1
            from dbo.[DECEASEDCONSTITUENT]
            where [DECEASEDCONSTITUENT].[ID] = [CONSTITUENT].[ID]
        )
    ) and
    CONSTITUENT.ISCONSTITUENT = 1
order by SORTNAME

--If Groups/Households is not an option, return the basic results as-is

if @INCLUDEGROUPS = 0
begin
    select
        RESULTS.ID,
        RESULTS.DISPLAYNAME,
        RESULTS.SORTNAME as NAME,
        dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, ADDRESS.COUNTRYID) as [ADDRESSBLOCK],
        NUMBER as [PHONENUMBER]
    from @BASICRESULTS as RESULTS
        left join dbo.ADDRESS on RESULTS.ID = ADDRESS.CONSTITUENTID
        left join dbo.PHONE on RESULTS.ID = PHONE.CONSTITUENTID
    where
        (ADDRESS.ISPRIMARY is null or ADDRESS.ISPRIMARY = 1) and
        (PHONE.ISPRIMARY is null or PHONE.ISPRIMARY = 1)
    order by
        NAME,
        ADDRESSBLOCK,
        PHONENUMBER
end
else --Union in the groups/households!

begin

    set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    select top(@MAXROWS) * from (

    select
        RESULTS.ID,
        RESULTS.DISPLAYNAME,
        RESULTS.SORTNAME as NAME,
        dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, ADDRESS.COUNTRYID) as [ADDRESSBLOCK],
        NUMBER as [PHONENUMBER]
    from @BASICRESULTS as RESULTS
        left join dbo.ADDRESS on RESULTS.ID = ADDRESS.CONSTITUENTID
        left join dbo.PHONE on RESULTS.ID = PHONE.CONSTITUENTID
    where
        (ADDRESS.ID is null or ADDRESS.ISPRIMARY = 1) and
        (PHONE.ID is null or PHONE.ISPRIMARY = 1)

    union all

    select distinct
        [GROUP].ID,
        [GROUP].NAME as DISPLAYNAME,
        [GROUP].NAME,
        dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, ADDRESS.COUNTRYID) as [ADDRESSBLOCK],
        NUMBER as [PHONENUMBER]
    from dbo.GROUPMEMBER GM with (nolock)
        inner join @BASICRESULTS as RESULTS on RESULTS.ID = GM.MEMBERID
        inner join dbo.CONSTITUENT [GROUP] with (nolock) on GM.GROUPID = [GROUP].ID
        left outer join dbo.GROUPMEMBERDATERANGE GMDR with (nolock) on GM.ID = GMDR.GROUPMEMBERID
        left join dbo.ADDRESS on [GROUP].ID = ADDRESS.CONSTITUENTID
        left join dbo.PHONE on [GROUP].ID = PHONE.CONSTITUENTID
    where
    not ([GROUP].ID in (select ID from @BASICRESULTS)) and
    (
        (GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME)) or
        (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) or 
        (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME)
    ) and 
    (ADDRESS.ID is null or ADDRESS.ISPRIMARY = 1) and
    (PHONE.ID is null or PHONE.ISPRIMARY = 1)

    ) as RESULTSET

    order by
        NAME,
        ADDRESSBLOCK,
        PHONENUMBER

end