UFN_CONSTITUENTUPDATEBATCH_DOMANUALREVIEWFORAUTOMATCH_2

CREATE function dbo.UFN_CONSTITUENTUPDATEBATCH_DOMANUALREVIEWFORAUTOMATCH_2(
  @BATCHCONSTITUENTUPDATEID uniqueidentifier,
  @MATCHEDRECORDID uniqueidentifier,
  @NAMEEXCEPTIONCODE tinyint,
  @ADDRESSSIMILAREXCEPTIONCODE tinyint,
  @ADDRESSNOTSIMILAREXCEPTIONCODE tinyint,
  @DIFFERENTPHONECODE tinyint,
  @DIFFERENTEMAILCODE tinyint
)
returns bit
with execute as caller
as begin

  declare @DOMANUALREVIEWFORAUTOMATCH bit;
  declare @KEYNAME nvarchar(255);
  declare @FIRSTNAME nvarchar(255);
  declare @MIDDLENAME nvarchar(255);
  declare @TITLECODEID uniqueidentifier;
  declare @SUFFIXCODEID uniqueidentifier;
  declare @EXISTINGFIRSTNAME nvarchar(100);
  declare @EXISTINGKEYNAME nvarchar(200);
  declare @EXISTINGMIDDLENAME nvarchar(100);
  declare @EXISTINGSUFFIXCODEID uniqueidentifier;
  declare @EXISTINGTITLECODEID uniqueidentifier;
  declare @ADDRESSMATCHTHRESHOLD decimal(20,4);

  set @DOMANUALREVIEWFORAUTOMATCH = 0;

  select
    @KEYNAME = KEYNAME,
    @FIRSTNAME = FIRSTNAME,
    @MIDDLENAME = MIDDLENAME,
    @TITLECODEID = TITLECODEID,
    @SUFFIXCODEID = SUFFIXCODEID
  from dbo.BATCHCONSTITUENTUPDATE where ID = @BATCHCONSTITUENTUPDATEID;  

  if @NAMEEXCEPTIONCODE = 2
      select
        @EXISTINGKEYNAME = KEYNAME,
        @EXISTINGFIRSTNAME = FIRSTNAME,
        @EXISTINGMIDDLENAME = MIDDLENAME,
        @EXISTINGTITLECODEID = TITLECODEID,
        @EXISTINGSUFFIXCODEID = SUFFIXCODEID
      from dbo.CONSTITUENT where ID = @MATCHEDRECORDID;


  if @NAMEEXCEPTIONCODE = 2 and ((@EXISTINGFIRSTNAME <> @FIRSTNAME and @FIRSTNAME <> '') or 
                                  (@EXISTINGKEYNAME <> @KEYNAME  and @KEYNAME <> '') or 
                                  (@EXISTINGMIDDLENAME <> @MIDDLENAME  and @MIDDLENAME <> '') or 
                                  (@EXISTINGSUFFIXCODEID <> @SUFFIXCODEID  and @SUFFIXCODEID is not null) or 
                                  (@EXISTINGTITLECODEID <> @TITLECODEID and @TITLECODEID is not null))
    set @DOMANUALREVIEWFORAUTOMATCH = 1;

  else if (@ADDRESSSIMILAREXCEPTIONCODE = 2 or @ADDRESSNOTSIMILAREXCEPTIONCODE = 2)
    begin
      -- Get the address match threshold 

      select top 1
        @ADDRESSMATCHTHRESHOLD = ADDRESSMATCHTHRESHOLD
      from dbo.CONSTITUENTDUPLICATESEARCHSETTINGS;

      if exists(select 'x'
            from dbo.BATCHCONSTITUENTUPDATEADDRESSES  BCUA
            outer apply dbo.UFN_FINDADDRESSMATCH(@MATCHEDRECORDID, ADDRESSBLOCK, POSTCODE, COUNTRYID, CITY, STATEID, ADDRESSTYPECODEID, @ADDRESSMATCHTHRESHOLD) FA
            where BCUA.BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID
            and ((FA.ADDRESSID is null and @ADDRESSNOTSIMILAREXCEPTIONCODE = 2)
            or (FA.ADDRESSID is not null and FA.NOCONFLICTS = 0 and @ADDRESSSIMILAREXCEPTIONCODE = 2)))
      set @DOMANUALREVIEWFORAUTOMATCH = 1;
    end
  -- check phones

  else if @DIFFERENTPHONECODE = 2 and exists (select 'x' 
                                              from dbo.BATCHCONSTITUENTUPDATEPHONES BP 
                                              left join 
                                              dbo.PHONE P 
                                              on P.NUMBER = BP.NUMBER and (P.PHONETYPECODEID is null or BP.PHONETYPECODEID is null or P.PHONETYPECODEID = BP.PHONETYPECODEID)
                                              where P.ID is null
                                              and BP.BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID
                                              )
      set @DOMANUALREVIEWFORAUTOMATCH = 1; 
  -- check emails

  else if @DIFFERENTEMAILCODE = 2 and exists (select 'x' 
                                              from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES BE 
                                              left join 
                                              dbo.EMAILADDRESS E 
                                              on E.EMAILADDRESS = BE.EMAILADDRESS and (E.EMAILADDRESSTYPECODEID is null or BE.EMAILADDRESSTYPECODEID is null or E.EMAILADDRESSTYPECODEID = BE.EMAILADDRESSTYPECODEID)
                                              where E.ID is null
                                              and BE.BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID
                                              )
      set @DOMANUALREVIEWFORAUTOMATCH = 1;       

    return @DOMANUALREVIEWFORAUTOMATCH;
end