USP_REFUND_MEMBERSHIPSEARCH

        CREATE procedure dbo.USP_REFUND_MEMBERSHIPSEARCH
        (
            @GROUPORGKEYNAME nvarchar(100) = null,
            @NAME nvarchar(100) = null,
            @KEYNAME nvarchar(100) = null,
            @FIRSTNAME nvarchar(50) = null,
            @MAXROWS int = 100,
            @COUNTRYID uniqueidentifier = null,
            @STATEID uniqueidentifier = null,
            @CITY nvarchar(50) = null,
            @ADDRESSBLOCK nvarchar(100) = null,
            @POSTCODE nvarchar(20) = null,
            @PHONENUMBER nvarchar(25) = null,
            @MEMBERSHIPLOOKUPID nvarchar(25) = null,
            @MEMBERSHIPPROGRAMID uniqueidentifier = null,
            @INCLUDEINDIVIDUALS bit,
            @INCLUDEORGANIZATIONS bit,
            @INCLUDEGROUPS bit,
            @FUZZYSEARCHONNAME bit,
            @CHECKNICKNAMES bit,
            @CHECKALIASES bit,
            @EXCLUDENONREFUNDABLE bit,
            @LAPSEDSTRING nvarchar(10) = 'Lapsed',
            @HASADDRESSFILTER bit,
            @HASPHONEFILTER bit,
            @FIRSTNAMEPRESENT bit,
            @EXACTMATCHONLY bit
        )
        as
        begin
            set nocount on;

            declare @CURRENTDATE datetime = getdate();

            declare @PHONENUMBERNOFORMAT nvarchar(50)
            if @HASPHONEFILTER = 1
            begin
                if @EXACTMATCHONLY = 0
                    set @PHONENUMBERNOFORMAT = dbo.UFN_PHONE_REMOVEFORMATTING(@PHONENUMBER) + '%';
                else
                    set @PHONENUMBERNOFORMAT = dbo.UFN_PHONE_REMOVEFORMATTING(@PHONENUMBER);
            end

            -- If search criteria has been given, search for constituents whose info matches the criteria

            if (@NAME is not null and @NAME <> '') or @HASADDRESSFILTER = 1 or @HASPHONEFILTER = 1
            begin
            declare @CONSTITUENTMEMBERS table (ID uniqueidentifier)
            insert into @CONSTITUENTMEMBERS
            select ID from dbo.CONSTITUENT as C where
            -- match on name

            (
                (@NAME is null or @NAME = '') or
                (
                    (([C].[KEYNAME] like @KEYNAME escape '\' and [C].[ISORGANIZATION] = 0 and [C].[ISGROUP] = 0) or
                    ([C].[KEYNAME] like @GROUPORGKEYNAME escape '\' and ([C].[ISORGANIZATION] = 1 or [C].[ISGROUP] = 1)))
                    or
                    (@FUZZYSEARCHONNAME = 1 and
                    ((soundex([C].[KEYNAME]) like soundex(@KEYNAME) escape '\' and [C].[ISORGANIZATION] = 0 and [C].[ISGROUP] = 0) or
                    (soundex([C].[KEYNAME]) like soundex(@GROUPORGKEYNAME) escape '\' and ([C].[ISORGANIZATION] = 1 or [C].[ISGROUP] = 1))))
                    or
                    (@CHECKALIASES = 1 and
                    exists(
                        select [A].[ID] from dbo.[ALIAS] as [A] 
                        where [C].[ID] = [A].[CONSTITUENTID] and
                        ((
                        (([A].[KEYNAME] like @KEYNAME escape '\' and (C.ISORGANIZATION = 0 and C.ISGROUP = 0)) or 
                        ([A].[KEYNAME] like @GROUPORGKEYNAME escape '\' and (C.ISORGANIZATION = 1 or C.ISGROUP = 1)))
                        or 
                        (@FUZZYSEARCHONNAME = 1 and ((soundex([A].[KEYNAME]) like soundex(@KEYNAME) escape '\' and (C.ISORGANIZATION = 0 and C.ISGROUP = 0)) or
                        (soundex([A].[KEYNAME]) like soundex(@GROUPORGKEYNAME) escape '\' and (C.ISORGANIZATION = 1 or C.ISGROUP = 1))))
                        ))
                    ))
                    or
                    (@FIRSTNAMEPRESENT = 1 and
                    ([C].[FIRSTNAME] like @FIRSTNAME escape '\' or 
                    (@FUZZYSEARCHONNAME = 1 and soundex([C].[FIRSTNAME]) like soundex(@FIRSTNAME) escape '\') or
                    (@CHECKALIASES = 1 and 
                    exists(
                        select [A].[ID] from dbo.[ALIAS] as [A]
                        where [C].[ID] = [A].[CONSTITUENTID] and
                        ([A].[FIRSTNAME] like @FIRSTNAME escape '\'
                        or (@FUZZYSEARCHONNAME = 1 and soundex([A].[FIRSTNAME]) like soundex(@FIRSTNAME) escape '\')
                        )
                    ))))
                    or
                    ((@CHECKNICKNAMES = 1 and @FIRSTNAMEPRESENT = 1) and
                    ([C].[NICKNAME] like @FIRSTNAME escape '\' or
                    (@FUZZYSEARCHONNAME = 1 and soundex([C].[NICKNAME]) like soundex(@FIRSTNAME) escape '\')))
                )
            )
            and
            -- match address

            (
                @HASADDRESSFILTER = 0 or
                exists(
                    select [ADDRESS].[ID] from dbo.[ADDRESS] 
                    where 
                    [ADDRESS].[CONSTITUENTID] = [C].[ID] and 
                    (@COUNTRYID is null or [ADDRESS].[COUNTRYID] = @COUNTRYID) and
                    ((@ADDRESSBLOCK is null or @ADDRESSBLOCK = '') or [ADDRESS].[ADDRESSBLOCK] like @ADDRESSBLOCK escape '\') and
                    ((@CITY is null or @CITY = '') or [ADDRESS].[CITY] like @CITY escape '\') and
                    (@STATEID is null or [ADDRESS].[STATEID] = @STATEID) and
                    ((@POSTCODE is null or @POSTCODE = '') or [ADDRESS].[POSTCODE] like @POSTCODE escape '\')
                )
            )
            and
            -- match phone

            (
                @HASPHONEFILTER = 0 or
                exists(
                    select [PHONE].[ID] from dbo.[PHONE] 
                    where [PHONE].[NUMBERNOFORMAT] like @PHONENUMBERNOFORMAT escape '\' and 
                    [PHONE].[CONSTITUENTID] = [C].[ID]
                )
            )
            and 
            (
                (@INCLUDEINDIVIDUALS = 1 and @INCLUDEGROUPS = 1 and @INCLUDEORGANIZATIONS = 1) or
                (@INCLUDEGROUPS = 1 and [C].[ISGROUP] = 1) or 
                (@INCLUDEORGANIZATIONS = 1 and [C].[ISORGANIZATION] = 1) or
                (@INCLUDEINDIVIDUALS = 1 and ([C].[ISGROUP] = 0 and [C].[ISORGANIZATION] = 0))
            );

            with MEMBERSHIP_CTE as (
                select top(@MAXROWS) 
                    [MEMBERSHIP].[ID],
                    [MEMBERSHIPTRANSACTION].[TRANSACTIONDATE] as [DATE],
                    [MEMBERSHIP].[LOOKUPID] as [LOOKUPID],
                    [CONSTITUENT].[NAME] as [PRIMARYMEMBERNAME],
                    -- build membership string

                    [MEMBERSHIPTRANSACTION].[ACTION] + ' - ' + 
                    [MEMBERSHIPPROGRAM].[NAME] + ' - ' + 
                    [MEMBERSHIPLEVEL].[NAME] + ' (' + 
                    [MEMBERSHIPLEVELTERM].[TERM] + ')' as [MEMBERSHIP],
                    [MEMBERSHIP].[EXPIRATIONDATE],
                    coalesce(REVENUESPLIT.AMOUNT, 0) as AMOUNT,
                    (
                        select dbo.UDA_BUILDLIST(dbo.UFN_CONSTITUENT_BUILDNAME([MEMBER].[CONSTITUENTID])) 
                        from dbo.[MEMBER] 
                        where [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
                    ) as [MEMBERS],
                    coalesce((select NAME from dbo.CONSTITUENT where ID = [MEMBERSHIP].[GIVENBYID]),'') as [GIVENBY],
                    case 
                        when [MEMBERSHIP].[EXPIRATIONDATE] < @CURRENTDATE then @LAPSEDSTRING 
                        else [MEMBERSHIP].[STATUS] 
                    end as [STATUS],
                    [ADDRESS].POSTCODE,
                    [PHONE].NUMBER AS [PHONENUMBER]
                    from dbo.MEMBERSHIP with(nolock) 
                    inner join dbo.[MEMBERSHIPTRANSACTION] on [MEMBERSHIP].[ID] = [MEMBERSHIPTRANSACTION].[MEMBERSHIPID]
                    inner join dbo.MEMBER 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
                    inner join dbo.CONSTITUENT on MEMBER.CONSTITUENTID = CONSTITUENT.ID
                    left join dbo.ADDRESS on MEMBER.CONSTITUENTID = ADDRESS.CONSTITUENTID
                    left join dbo.PHONE on MEMBER.CONSTITUENTID = PHONE.CONSTITUENTID
                    left join dbo.REVENUESPLIT on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID

                    where
                    MEMBER.ISPRIMARY = 1
                    and MEMBER.CONSTITUENTID in (select ID from @CONSTITUENTMEMBERS)
                    and (ADDRESS.ISPRIMARY is null or ADDRESS.ISPRIMARY = 1)
                    and (PHONE.ISPRIMARY is null or PHONE.ISPRIMARY = 1)
                    and ((@MEMBERSHIPLOOKUPID is null or @MEMBERSHIPLOOKUPID = '') or [MEMBERSHIP].[LOOKUPID] like @MEMBERSHIPLOOKUPID escape '\')
                    and (@MEMBERSHIPPROGRAMID is null or [MEMBERSHIP].[MEMBERSHIPPROGRAMID] = @MEMBERSHIPPROGRAMID)
                    and [MEMBERSHIPTRANSACTION].[ID] = dbo.UFN_MEMBERSHIP_GETLASTMEMBERSHIPTRANSACTIONID([MEMBERSHIP].[ID])
                    and 
                    -- Exclude non-refundable

                    (
                        @EXCLUDENONREFUNDABLE = 0 or
                        (
                            [MEMBERSHIP].[STATUSCODE] = 0 and 
                            [MEMBERSHIP].[EXPIRATIONDATE] > @CURRENTDATE
                        )
                    )
            )
            select top (@MAXROWS) 
                [ID], 
                [DATE], 
                [LOOKUPID], 
                [PRIMARYMEMBERNAME], 
                [MEMBERSHIP], 
                [EXPIRATIONDATE], 
                [AMOUNT], 
                [MEMBERS], 
                [GIVENBY], 
                [STATUS], 
                [POSTCODE], 
                [PHONENUMBER]
            from MEMBERSHIP_CTE as MEMBERSHIP
            order by [PRIMARYMEMBERNAME] asc, [MEMBERSHIP] asc, [STATUS] asc
            end

            -- If no search criteria given, just pull back the top @MAXROWS

            else
            begin
            with MEMBERSHIP_CTE as (
                select top(@MAXROWS) 
                    [MEMBERSHIP].[ID],
                    [MEMBERSHIPTRANSACTION].[TRANSACTIONDATE] as [DATE],
                    [MEMBERSHIP].[LOOKUPID] as [LOOKUPID],
                    [CONSTITUENT].[NAME] as [PRIMARYMEMBERNAME],
                    -- build membership string

                    [MEMBERSHIPTRANSACTION].[ACTION] + ' - ' + 
                    [MEMBERSHIPPROGRAM].[NAME] + ' - ' + 
                    [MEMBERSHIPLEVEL].[NAME] + ' (' + 
                    [MEMBERSHIPLEVELTERM].[TERM] + ')' as [MEMBERSHIP],
                    [MEMBERSHIP].[EXPIRATIONDATE],
                    coalesce(REVENUESPLIT.AMOUNT, 0) as AMOUNT,
                    (
                        select dbo.UDA_BUILDLIST(dbo.UFN_CONSTITUENT_BUILDNAME([MEMBER].[CONSTITUENTID])) 
                        from dbo.[MEMBER] 
                        where [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
                    ) as [MEMBERS],
                    coalesce((select NAME from dbo.CONSTITUENT where ID = [MEMBERSHIP].[GIVENBYID]),'') as [GIVENBY],
                    case 
                        when [MEMBERSHIP].[EXPIRATIONDATE] < @CURRENTDATE then @LAPSEDSTRING 
                        else [MEMBERSHIP].[STATUS] 
                    end as [STATUS],
                    [ADDRESS].POSTCODE,
                    [PHONE].NUMBER AS [PHONENUMBER]
                    from dbo.MEMBERSHIP with(nolock) 
                    inner join dbo.[MEMBERSHIPTRANSACTION] on [MEMBERSHIP].[ID] = [MEMBERSHIPTRANSACTION].[MEMBERSHIPID]
                    inner join dbo.MEMBER 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
                    inner join dbo.CONSTITUENT on MEMBER.CONSTITUENTID = CONSTITUENT.ID
                    left join dbo.ADDRESS on MEMBER.CONSTITUENTID = ADDRESS.CONSTITUENTID
                    left join dbo.PHONE on MEMBER.CONSTITUENTID = PHONE.CONSTITUENTID
                    left join dbo.REVENUESPLIT on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID

                    where
                    MEMBER.ISPRIMARY = 1
                    and ((@INCLUDEGROUPS = 1 and @INCLUDEINDIVIDUALS = 1 and @INCLUDEORGANIZATIONS = 1)
                        or (@INCLUDEGROUPS = 1 and CONSTITUENT.ISGROUP = 1) 
                        or (@INCLUDEINDIVIDUALS = 1 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0)
                        or @INCLUDEORGANIZATIONS = 1 and CONSTITUENT.ISORGANIZATION = 1)
                    and (ADDRESS.ISPRIMARY is null or ADDRESS.ISPRIMARY = 1)
                    and (PHONE.ISPRIMARY is null or PHONE.ISPRIMARY = 1)
                    and ((@MEMBERSHIPLOOKUPID is null or @MEMBERSHIPLOOKUPID = '') or [MEMBERSHIP].[LOOKUPID] like @MEMBERSHIPLOOKUPID escape '\')
                    and (@MEMBERSHIPPROGRAMID is null or [MEMBERSHIP].[MEMBERSHIPPROGRAMID] = @MEMBERSHIPPROGRAMID)
                    and [MEMBERSHIPTRANSACTION].[ID] = dbo.UFN_MEMBERSHIP_GETLASTMEMBERSHIPTRANSACTIONID([MEMBERSHIP].[ID])
                    and 
                    -- Exclude non-refundable

                    (
                        @EXCLUDENONREFUNDABLE = 0 or
                        (
                            [MEMBERSHIP].[STATUSCODE] = 0 and 
                            [MEMBERSHIP].[EXPIRATIONDATE] > @CURRENTDATE
                        )
                    )
            )
            select top (@MAXROWS) 
                [ID], 
                [DATE], 
                [LOOKUPID], 
                [PRIMARYMEMBERNAME], 
                [MEMBERSHIP], 
                [EXPIRATIONDATE], 
                [AMOUNT], 
                [MEMBERS], 
                [GIVENBY], 
                [STATUS], 
                [POSTCODE], 
                [PHONENUMBER]
            from MEMBERSHIP_CTE as MEMBERSHIP
            order by [PRIMARYMEMBERNAME] asc, [MEMBERSHIP] asc, [STATUS] asc
            end

        end