UFN_FINDADDRESSMATCH_CONSTITUENTUPDATEBATCH2

CREATE function dbo.UFN_FINDADDRESSMATCH_CONSTITUENTUPDATEBATCH2(
  @CONSTITUENTID uniqueidentifier, --constituent ID we are trying to find an address matching the rest of the parameters.

  @ADDRESSBLOCK nvarchar(150),
  @POSTCODE nvarchar(12),
  @COUNTRYID uniqueidentifier,
  @CITY nvarchar(50),
  @STATEID uniqueidentifier,
  @ADDRESSTYPECODEID uniqueidentifier,
  @ADDRESSMATCHTHRESHOLD tinyint,
  @BATCHCONSTITUENTUPDATEID uniqueidentifier, -- Batch row ID to keep track of addresses that are already in the batch.

  @HISTORICALENDDATE date
)
returns @MATCH table (
  ADDRESSID uniqueidentifier,
  NOCONFLICTS bit,
  STREETNUMBERRESULT tinyint,
  STREETNAMERESULT tinyint,
  POSTCODERESULT tinyint
)
as
begin
  declare @MATCHES table (ADDRESSID uniqueidentifier,
                          COMPOSITESCORE tinyint,
                          STREETNUMBERRESULT tinyint,
                          STREETNAMERESULT tinyint,
                          POSTCODERESULT tinyint,
                          STREETNUMBERSCORE tinyint,
                          STREETNAMESCORE tinyint,
                          POSTCODESCORE tinyint,
                          NOCONFLICTS bit,
                          ISPRIMARY bit,
                          HISTORICALENDDATE date,
                          DATECHANGED datetime,
                          ADDRESSTYPECODEID uniqueidentifier,
                          ADDRESSBLOCK nvarchar(150)
                          );

  declare @BESTMATCHID uniqueidentifier;

  -- Clean incoming data

  declare @STREETNUMBER nvarchar(12),
          @STREETNAME nvarchar(150),
          @CLEANPOSTCODE nvarchar(12)

  select @STREETNUMBER = STREETNUMBER,
         @STREETNAME = STREETNAME,
         @CLEANPOSTCODE = POSTCODE
  from dbo.UFN_CLEANCONSTITUENTSTRINGS_2(null, null, null, @ADDRESSBLOCK, @POSTCODE)

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

  if @ADDRESSMATCHTHRESHOLD is null
    select @ADDRESSMATCHTHRESHOLD = ADDRESSMATCHTHRESHOLD
    from dbo.CONSTITUENTDUPLICATESEARCHSETTINGS
    where TRANSACTIONTYPE = 'Default Criteria'
    order by DATECHANGED;

  insert into @MATCHES (ADDRESSID, STREETNUMBERRESULT, STREETNAMERESULT, POSTCODERESULT,
                        STREETNUMBERSCORE, STREETNAMESCORE, POSTCODESCORE, COMPOSITESCORE)
  select Addresses.ADDRESSID,
          score.STREETNUMBERRESULT,
          score.STREETNAMERESULT,
          score.POSTCODERESULT,
          score.STREETNUMBERSCORE,
          score.STREETNAMESCORE,
          score.POSTCODESCORE,
          score.COMPOSITESCORE
          from (select SEARCHCONSTITUENT.ADDRESSID, SEARCHCONSTITUENT.STREETNUMBER, SEARCHCONSTITUENT.STREETNAME, SEARCHCONSTITUENT.POSTCODE, SEARCHCONSTITUENT.COUNTRYID, SEARCHCONSTITUENT.ISORGANIZATION
              from dbo.SEARCHCONSTITUENT 
              where SEARCHCONSTITUENT.CONSTITUENTID = @CONSTITUENTID
              and SEARCHCONSTITUENT.COUNTRYID = isnull(@COUNTRYID,SEARCHCONSTITUENT.COUNTRYID)
              and SEARCHCONSTITUENT.POSTCODE3 = left(@CLEANPOSTCODE,3) 
              --do not want to re-match any addresses that are already in the batch, so we exclude them here by using the batch row ID.

              and NOT EXISTS (select bcua.ADDRESSID from dbo.BATCHCONSTITUENTUPDATEADDRESSES bcua where bcua.BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID and bcua.ADDRESSID = SEARCHCONSTITUENT.ADDRESSID)) Addresses 
    outer apply dbo.UFN_COMPARECONSTITUENTS_4(null, null, null, null, null, @STREETNUMBER, @STREETNAME, @CLEANPOSTCODE, null, null, null, null, null, 
                                              Addresses.STREETNUMBER, Addresses.STREETNAME, Addresses.POSTCODE, Addresses.COUNTRYID, 1, Addresses.ISORGANIZATION, @LOGICALRESULTMAPPING, @ADDRESSMATCHTHRESHOLD) score
    where score.BAILEDCOMPARISON = 0;

  delete from @MATCHES
  where COMPOSITESCORE < (select max(COMPOSITESCORE) from @MATCHES);

  -- only the best match or matches are left

  -- determine whether there are conflicts or not

  update matches
  set NOCONFLICTS = case when matches.STREETNUMBERRESULT in(0,4,5,6) and
                              matches.STREETNAMERESULT in(0,4,5,6) and
                              matches.POSTCODERESULT in(0,4) and
                              (ADDRESS.CITY = @CITY or ADDRESS.CITY = '' or isnull(@CITY,'') = '') and
                              (ADDRESS.STATEID = @STATEID or ADDRESS.STATEID is null or @STATEID is null) and
                              (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or ADDRESS.ADDRESSTYPECODEID is null or @ADDRESSTYPECODEID is null) and
                              not (ADDRESS.HISTORICALENDDATE is not null and @HISTORICALENDDATE is null) then 1 else 0 end,
      ISPRIMARY = ADDRESS.ISPRIMARY,
      HISTORICALENDDATE = ADDRESS.HISTORICALENDDATE,
      DATECHANGED = ADDRESS.DATECHANGED,
      ADDRESSTYPECODEID = ADDRESS.ADDRESSTYPECODEID,
      ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK
  from @MATCHES matches
  inner join dbo.ADDRESS on ADDRESS.ID = matches.ADDRESSID;

  declare @NULLVALUE uniqueidentifier = (select cast(cast(0 as binary) as uniqueidentifier))
  insert into @MATCH
  select top 1 ADDRESSID, NOCONFLICTS, STREETNUMBERRESULT, STREETNAMERESULT, POSTCODERESULT
  from @MATCHES
  order by NOCONFLICTS desc,
            case when ADDRESSTYPECODEID = isnull(@ADDRESSTYPECODEID,@NULLVALUE) then 1 else 2 end,
            case when isnull(@ADDRESSBLOCK,'') = isnull(ADDRESSBLOCK,'') then 1 else 2 end,
            ISPRIMARY desc,
            case when HISTORICALENDDATE is null then 1 else 2 end,
            DATECHANGED desc

  return;
end