UFN_FINDCONSTITUENTMATCHES_3

CREATE function dbo.UFN_FINDCONSTITUENTMATCHES_3 (
  @TITLECODEID uniqueidentifier,
  @FIRSTNAME nvarchar(50),
  @MIDDLENAME nvarchar(50),
  @KEYNAME nvarchar(100),
  @SUFFIXCODEID uniqueidentifier,
  @ADDRESSBLOCK nvarchar(150),
  @POSTCODE nvarchar(12),
  @COUNTRYID uniqueidentifier,
  @ISORGANIZATION bit,
  @ISGROUP bit,
  @LOOKUPID nvarchar(100),
  @ALTERNATELOOKUPIDS xml,
  @EMAILADDRESS nvarchar(100),
  @PHONENUMBER nvarchar(100),
  @OVERALLMATCHTHRESHOLD tinyint,
  @AUTOMATCHTHRESHOLD tinyint
)
returns @MATCHES table (
  CONSTITUENTID uniqueidentifier,
  ADDRESSID uniqueidentifier,
  EMAILADDRESSID uniqueidentifier,
  PHONEID uniqueidentifier,
  --

  IDRESULT tinyint,
  EMAILADDRESSRESULT tinyint,
  TITLERESULT tinyint,
  FIRSTNAMERESULT tinyint,
  MIDDLENAMERESULT tinyint,
  KEYNAMERESULT tinyint,
  SUFFIXRESULT tinyint,
  STREETNUMBERRESULT tinyint,
  STREETNAMERESULT tinyint,
  POSTCODERESULT tinyint,
  PHONENUMBERRESULT tinyint,
  -- scores are included for informational purposes, the results will be what is used to determine matches

  FIRSTNAMESCORE tinyint,
  MIDDLENAMESCORE tinyint,
  KEYNAMESCORE tinyint,
  STREETNUMBERSCORE tinyint,
  STREETNAMESCORE tinyint,
  POSTCODESCORE tinyint,
  -- this constituent is not a match, but the two constituents may be a household

  HOUSEHOLDMATCH bit,
  COMPOSITESCORE int
)
begin
  -- (1) match on lookup ID or alternate lookup IDs. Any matches found are exact matches.  If any are found, we're done.


    insert into @MATCHES (CONSTITUENTID, IDRESULT, COMPOSITESCORE)
    select CONSTITUENTID, 0, 100
    from dbo.UFN_FINDCONSTITUENTMATCHES_BYLOOKUPIDS(@LOOKUPID, @ALTERNATELOOKUPIDS)

    if @@ROWCOUNT > 0
      return;

  -- No matches found by lookup ID or alternate ID, proceed with the fuzzy matching.


  -- Clean incoming data

  declare @CLEANFIRSTNAME nvarchar(50),
          @CLEANMIDDLENAME nvarchar(50),
          @CLEANKEYNAME nvarchar(100),
          @STREETNUMBER nvarchar(12),
          @STREETNAME nvarchar(150),
          @CLEANPOSTCODE nvarchar(12),
          @CLEANPHONENUMBER nvarchar(100)

  select @CLEANFIRSTNAME = FIRSTNAME,
         @CLEANMIDDLENAME = MIDDLENAME,
         @CLEANKEYNAME = KEYNAME,
         @STREETNUMBER = STREETNUMBER,
         @STREETNAME = STREETNAME,
         @CLEANPOSTCODE = POSTCODE
  from dbo.UFN_CLEANCONSTITUENTSTRINGS_2(@FIRSTNAME, @MIDDLENAME, @KEYNAME, @ADDRESSBLOCK, @POSTCODE)

  declare @LOGICALRESULTMAPPING dbo.UDT_GENERIC_NVARCHAR100_TINYINT;
  insert into @LOGICALRESULTMAPPING
  select CASENAME, RESULTCODE
  from dbo.CONSTITUENTMATCHLOGICALRESULTMAPPING

  -- (2) Get candidates by email address.

  --     These candidates will be run through the main scoring algorithm.  Any email match will be considered at least a manual review match.

  --     If a single auto-match is found, we're done.

  --     If multiple auto-matches are found, or only manual matches are found, we will also examine name/address-based candidates.

  if @EMAILADDRESS <> ''
  begin
    insert into @MATCHES
    select distinct 
           c.CONSTITUENTID,
           -- only return addressid if the name/address is considered a match

           case when s.BAILEDCOMPARISON = 1 then null else c.ADDRESSID end,
           e.ID,
           NULL,
           1,
           0,
           s.TITLERESULT,
           s.FIRSTNAMERESULT,
           s.MIDDLENAMERESULT,
           s.KEYNAMERESULT,
           s.SUFFIXRESULT,
           s.STREETNUMBERRESULT,
           s.STREETNAMERESULT,
           s.POSTCODERESULT,
           NULL,
           s.FIRSTNAMESCORE,
           s.MIDDLENAMESCORE,
           s.KEYNAMESCORE,
           s.STREETNUMBERSCORE,
           s.STREETNAMESCORE,
           s.POSTCODESCORE,
           s.HOUSEHOLDMATCH,
           case 
            when s.BAILEDCOMPARISON = 1 then 70 --Email matches score at least 70

            when s.COMPOSITESCORE < 98 then s.COMPOSITESCORE + 2 --Email matches add 2 to scores below 98

            else s.COMPOSITESCORE
           end
    from dbo.EMAILADDRESS e
    inner join dbo.SEARCHCONSTITUENT c on c.CONSTITUENTID = e.CONSTITUENTID
    -- outer apply seems to resolve some performance problems with cross apply, since the UFN always returns a row, the results will be the same

    outer apply dbo.UFN_COMPARECONSTITUENTS_4(@TITLECODEID, @CLEANFIRSTNAME, @CLEANMIDDLENAME, @CLEANKEYNAME, @SUFFIXCODEID, @STREETNUMBER, @STREETNAME, @CLEANPOSTCODE,
                                              c.TITLECODEID, c.FIRSTNAME, c.MIDDLENAME, c.KEYNAME, c.SUFFIXCODEID, c.STREETNUMBER, c.STREETNAME, c.POSTCODE, c.COUNTRYID, 0, @ISORGANIZATION, @LOGICALRESULTMAPPING, 69) s -- Email matches won't be scored below 70, so only scores of 71 or more are meaningful. Using 69 since match can provide a +2 bonus score to get to 71.

    where e.EMAILADDRESS = @EMAILADDRESS
    and c.ISORGANIZATION = @ISORGANIZATION
    and c.ISGROUP = @ISGROUP;

    if @@ROWCOUNT > 0 and @AUTOMATCHTHRESHOLD > 0
    begin
      if ((select count(distinct CONSTITUENTID)
            from @MATCHES
            where COMPOSITESCORE >= @AUTOMATCHTHRESHOLD) = 1)
        return
    end
  end

  declare @NOEMAILRESULT tinyint = case when @EMAILADDRESS = '' then 4 else 6 end
  declare @NOPHONERESULT tinyint = case when @CLEANPHONENUMBER = '' then 4 else 6 end

  -- (3) Find candidates by phone.

  if @PHONENUMBER <> ''
  begin
    set @CLEANPHONENUMBER = dbo.UFN_PHONE_REMOVEFORMATTING(@PHONENUMBER);
    declare @NULLID uniqueidentifier = cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)

    insert into @MATCHES
    select distinct
           c.CONSTITUENTID,
           -- only return addressid if the name/address is considered a match

           case when s.BAILEDCOMPARISON = 1 then null else c.ADDRESSID end,
           NULL,
           p.ID,
           1,
           @NOEMAILRESULT,
           s.TITLERESULT,
           s.FIRSTNAMERESULT,
           s.MIDDLENAMERESULT,
           s.KEYNAMERESULT,
           s.SUFFIXRESULT,
           s.STREETNUMBERRESULT,
           s.STREETNAMERESULT,
           s.POSTCODERESULT,
           0,
           s.FIRSTNAMESCORE,
           s.MIDDLENAMESCORE,
           s.KEYNAMESCORE,
           s.STREETNUMBERSCORE,
           s.STREETNAMESCORE,
           s.POSTCODESCORE,
           s.HOUSEHOLDMATCH,
           case 
            when s.BAILEDCOMPARISON = 1 then 70 --Phone matches score at least 70

            when s.COMPOSITESCORE < 98 then s.COMPOSITESCORE + 2 --Phone matches add 2 to scores below 98

            else s.COMPOSITESCORE
           end
    from dbo.PHONE p
    inner join dbo.SEARCHCONSTITUENT c on c.CONSTITUENTID = p.CONSTITUENTID 
    outer apply dbo.UFN_COMPARECONSTITUENTS_4(@TITLECODEID, @CLEANFIRSTNAME, @CLEANMIDDLENAME, @CLEANKEYNAME, @SUFFIXCODEID, @STREETNUMBER, @STREETNAME, @CLEANPOSTCODE,
                                              c.TITLECODEID, c.FIRSTNAME, c.MIDDLENAME, c.KEYNAME, c.SUFFIXCODEID, c.STREETNUMBER, c.STREETNAME, c.POSTCODE, c.COUNTRYID, 0, @ISORGANIZATION, @LOGICALRESULTMAPPING, 69) s -- Phone matches won't be scored below 70, so only scores of 71 or more are meaningful. Using 69 since match can provide a +2 bonus score to get to 71.

    where p.NUMBERNOFORMAT = @CLEANPHONENUMBER
    and c.ISORGANIZATION = @ISORGANIZATION
    and c.ISGROUP = @ISGROUP
    and coalesce(p.COUNTRYID,c.COUNTRYID,@NULLID) = coalesce(@COUNTRYID,p.COUNTRYID,c.COUNTRYID,@NULLID)
    -- constituent not already found by email address

    and not exists(select 1 from @MATCHES where CONSTITUENTID = c.CONSTITUENTID);
  end

  --Caching values for query checks below

  declare @KEYNAME4 nvarchar(4) = left(@CLEANKEYNAME,4)
  declare @FIRSTNAME3 nvarchar(3) = left(@CLEANFIRSTNAME,3)
  declare @STREETNAME4 nvarchar(4) = left(@STREETNAME,4)
 declare @POSTCODE3 nvarchar(3) = left(@CLEANPOSTCODE,3)
  declare @CLEANFIRSTNAME3 nvarchar(3) = left(@CLEANFIRSTNAME,3)

  -- (4) Find candidates by name and address.

  if @CLEANKEYNAME <> '' and @CLEANPOSTCODE <> ''
  begin

    -- Force checks that have to pass for constituent to have any chance at matching above overall match threshold

    -- This is a help in automatch-only cases where the overall threshold is as high as @AUTOMATCHTHRESHOLD

    declare @FORCESTREETNUMBERCHECK bit = 0
    declare @FORCEPOSTCODECHECK bit = 0

    if @STREETNUMBER <> '' and @OVERALLMATCHTHRESHOLD > 100 - (select DEDUCTION from dbo.UFN_CONSTITUENTMATCH_STREETNUMBERSCOREDEDUCTION_BYRESULT(1))
      set @FORCESTREETNUMBERCHECK = 1
    if @CLEANPOSTCODE <> '' and @OVERALLMATCHTHRESHOLD > 100 - (select DEDUCTION from dbo.UFN_CONSTITUENTMATCH_POSTCODESCOREDEDUCTION_BYRESULT(1))
      set @FORCEPOSTCODECHECK = 1

    --*********************************************************

    -- THIS QUERY IS CAREFULLY WRITTEN TO MAKE USE OF THREE AVAILABLE INDEXES ON THE SEARCHCONSTITUENT TABLE, referred to as universes 1-3 below.

    -- Do not make any changes to this query without verifying that the execution plan, and specifically the FULL use of the three indexes (all columns used in the lookup), remains intact.

    -- *********************************************************

    insert into @MATCHES
    select c.CONSTITUENTID,
           c.ADDRESSID,
           NULL,
           NULL,
           1,
           @NOEMAILRESULT,
           s.TITLERESULT,
           s.FIRSTNAMERESULT,
           s.MIDDLENAMERESULT,
           s.KEYNAMERESULT,
           s.SUFFIXRESULT,
           s.STREETNUMBERRESULT,
           s.STREETNAMERESULT,
           s.POSTCODERESULT,
           @NOPHONERESULT,
           s.FIRSTNAMESCORE,
           s.MIDDLENAMESCORE,
           s.KEYNAMESCORE,
           s.STREETNUMBERSCORE,
           s.STREETNAMESCORE,
           s.POSTCODESCORE,
           s.HOUSEHOLDMATCH,
           s.COMPOSITESCORE
    from dbo.SEARCHCONSTITUENT c
    outer apply dbo.UFN_COMPARECONSTITUENTS_4(@TITLECODEID, @CLEANFIRSTNAME, @CLEANMIDDLENAME, @CLEANKEYNAME, @SUFFIXCODEID, @STREETNUMBER, @STREETNAME, @CLEANPOSTCODE,
                                              c.TITLECODEID, c.FIRSTNAME, c.MIDDLENAME, c.KEYNAME, c.SUFFIXCODEID, c.STREETNUMBER, c.STREETNAME, c.POSTCODE, c.COUNTRYID, 0, @ISORGANIZATION, @LOGICALRESULTMAPPING, @OVERALLMATCHTHRESHOLD) s
    where c.ISORGANIZATION = @ISORGANIZATION
      and c.ISGROUP = @ISGROUP
      and c.COUNTRYID = isnull(@COUNTRYID,c.COUNTRYID)
      and (
        @FORCESTREETNUMBERCHECK = 0 or 
        c.STREETNUMBER = '' or
        c.STREETNUMBER = @STREETNUMBER or
        c.STREETNUMBER like @STREETNUMBER + '-%' or 
        @STREETNUMBER like c.STREETNUMBER + '-%'
      )
         --

         -- universe 1 - postcode, keyname soundex

      and ((c.POSTCODE = @CLEANPOSTCODE and
          c.KEYNAME4 = @KEYNAME4) or
         --

         -- universe 2 - postcode, street name soundex, first name soundex

         (c.POSTCODE = @CLEANPOSTCODE and
          c.STREETNAMESOUNDEX = soundex(@STREETNAME) and
          c.FIRSTNAME3 = @FIRSTNAME3) or
         --

         -- universe 3 - 1st 3 characters of postcode, 1st 4 characters of keyname,

         --              1st 4 characters of street name, street number

          ((@FORCEPOSTCODECHECK = 0 or
             c.POSTCODE = @CLEANPOSTCODE) and
             c.POSTCODE3 = @POSTCODE3 and
          c.KEYNAMESOUNDEX = soundex(@CLEANKEYNAME) and
          c.STREETNAME4 = @STREETNAME4 and
          c.STREETNUMBER = @STREETNUMBER)
        )
    -- constituent not already found by email address or phone

    and not exists(select 1 from @MATCHES where CONSTITUENTID = c.CONSTITUENTID);
    -- ******************************

    -- END OF QUERY - see important note above regarding changes to this query

    -- ******************************

  end

  -- (5) Find candidates by name only (either no incoming address, or no existing records with a matching address).

  if @CLEANKEYNAME <> '' and not exists(select 1 from @MATCHES)
  begin
    -- prevent long-running searches for names like "Friend of X" or "Anonymous"

    if not exists(select 'x' from dbo.SEARCHCONSTITUENTEXCLUDE
                  where KEYNAME = @CLEANKEYNAME
                  and FIRSTNAME3 = @CLEANFIRSTNAME3)
    begin
      -- For performance reasons, search first for records with no address.

      -- If we don't have an incoming address, these will score higher than any matches we might find with addresses.

      -- If we have an incoming address, existing constituents with addresses would have been matched above so no reason to search those again.

      insert into @MATCHES    
      select distinct
             c.CONSTITUENTID,
             NULL,
             NULL,
             NULL,
             1,
             @NOEMAILRESULT,
             s.TITLERESULT,
             s.FIRSTNAMERESULT,
             s.MIDDLENAMERESULT,
             s.KEYNAMERESULT,
             s.SUFFIXRESULT,
             s.STREETNUMBERRESULT,
             s.STREETNAMERESULT,
             s.POSTCODERESULT,
             @NOPHONERESULT,
             s.FIRSTNAMESCORE,
             s.MIDDLENAMESCORE,
             s.KEYNAMESCORE,
             s.STREETNUMBERSCORE,
             s.STREETNAMESCORE,
             s.POSTCODESCORE,
             s.HOUSEHOLDMATCH,
             s.COMPOSITESCORE
      from dbo.SEARCHCONSTITUENT c
      outer apply dbo.UFN_COMPARECONSTITUENTS_4(@TITLECODEID, @CLEANFIRSTNAME, @CLEANMIDDLENAME, @CLEANKEYNAME, @SUFFIXCODEID, @STREETNUMBER, @STREETNAME, @CLEANPOSTCODE,
                                                c.TITLECODEID, c.FIRSTNAME, c.MIDDLENAME, c.KEYNAME, c.SUFFIXCODEID, c.STREETNUMBER, c.STREETNAME, c.POSTCODE, c.COUNTRYID, 0, @ISORGANIZATION, @LOGICALRESULTMAPPING, @OVERALLMATCHTHRESHOLD) s
      where c.KEYNAME = @CLEANKEYNAME
      and c.FIRSTNAME3 = @CLEANFIRSTNAME3
      and c.POSTCODE = ''
      and c.ISORGANIZATION = @ISORGANIZATION
      and c.ISGROUP = @ISGROUP;

      -- If we don't have an address and didn't find any records without an address, try existing constituents with an address.

      if @CLEANPOSTCODE = '' and not exists(select 1 from @MATCHES)
        insert into @MATCHES    
        select distinct
               c.CONSTITUENTID,
               NULL,
               NULL,
               NULL,
               1,
               @NOEMAILRESULT,
               s.TITLERESULT,
               s.FIRSTNAMERESULT,
               s.MIDDLENAMERESULT,
               s.KEYNAMERESULT,
               s.SUFFIXRESULT,
               s.STREETNUMBERRESULT,
               s.STREETNAMERESULT,
               s.POSTCODERESULT,
               @NOPHONERESULT,
               s.FIRSTNAMESCORE,
               s.MIDDLENAMESCORE,
               s.KEYNAMESCORE,
               s.STREETNUMBERSCORE,
               s.STREETNAMESCORE,
               s.POSTCODESCORE,
               s.HOUSEHOLDMATCH,
               s.COMPOSITESCORE
        from dbo.SEARCHCONSTITUENT c
        outer apply dbo.UFN_COMPARECONSTITUENTS_4(@TITLECODEID, @CLEANFIRSTNAME, @CLEANMIDDLENAME, @CLEANKEYNAME, @SUFFIXCODEID, @STREETNUMBER, @STREETNAME, @CLEANPOSTCODE,
                                                  c.TITLECODEID, c.FIRSTNAME, c.MIDDLENAME, c.KEYNAME, c.SUFFIXCODEID, c.STREETNUMBER, c.STREETNAME, c.POSTCODE, c.COUNTRYID, 0, @ISORGANIZATION, @LOGICALRESULTMAPPING, @OVERALLMATCHTHRESHOLD) s
        where c.KEYNAME = @CLEANKEYNAME
        and c.FIRSTNAME3 = @CLEANFIRSTNAME3
        and c.POSTCODE <> ''
        and c.ISORGANIZATION = @ISORGANIZATION
        and c.ISGROUP = @ISGROUP;
    end
  end

  -- If the email or phone are different, we want to subtract 1 so the score reflects the difference.

  -- However, in these situations, we want to ensure that we (a) don't auto match and (b) don't take a potential match out of manual review range. 

  -- Thus we ensure that the score is no more than 94 and we keep it at 70 if it was 70.

  if @EMAILADDRESS <> ''
    begin
      update M
      set COMPOSITESCORE = case when COMPOSITESCORE = 70 then COMPOSITESCORE when COMPOSITESCORE > 95 then 94 else COMPOSITESCORE - 1 end
      from @MATCHES M
      where M.EMAILADDRESSRESULT > 0 
      and exists (select 'x' from dbo.EMAILADDRESS E where E.CONSTITUENTID = M.CONSTITUENTID)      
    end

  if @PHONENUMBER <> ''
    begin
      -- if phone wasn't processed (phone number result is null) try finding a match and increase the score     

      update M
      set COMPOSITESCORE = case when COMPOSITESCORE < 98 then COMPOSITESCORE + 2 else COMPOSITESCORE end, 
          PHONEID = P.ID,
          PHONENUMBERRESULT = 0
      from @MATCHES M
      inner join dbo.PHONE P on P.CONSTITUENTID = M.CONSTITUENTID
      where M.PHONENUMBERRESULT is null
      and (P.COUNTRYID = @COUNTRYID or P.COUNTRYID is null or @COUNTRYID is null)
      and P.NUMBERNOFORMAT = @CLEANPHONENUMBER

      -- decrease the score if the incoming phone does not have a match

      update M
      set COMPOSITESCORE = case when COMPOSITESCORE = 70 then COMPOSITESCORE when COMPOSITESCORE > 95 then 94 else COMPOSITESCORE - 1 end
      from @MATCHES M
      where coalesce(M.PHONENUMBERRESULT,1) > 0
      and exists (select 'x' from dbo.PHONE P where P.CONSTITUENTID = M.CONSTITUENTID)          
    end

  return
end