USP_DATAFORMTEMPLATE_ADD_BATCHADDRESSVALIDATIONCOMMIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHADDRESSVALIDATIONCOMMIT
(
  @ID uniqueidentifier = null output,
  @VALIDATEONLY bit = 0,
  @BATCHNUMBER nvarchar(100),
  @CHANGEAGENTID uniqueidentifier,
  @ADDRESSVALIDATIONID uniqueidentifier,
  @ADDRESSID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @ADDRESSBLOCK nvarchar(150) = '',
  @CITY nvarchar(150) = '',
  @STATEID uniqueidentifier = null,
  @POSTCODE nvarchar(10) = '',
  @NEWADDRESSBLOCK nvarchar(150) = '',
  @NEWCITY nvarchar(150) = '',
  @NEWSTATEID uniqueidentifier = null,
  @NEWPOSTCODE nvarchar(10) = '',
  @NEWCOUNTYID uniqueidentifier = null,
  @NEWCONGRESSIONALDISTRICTID uniqueidentifier = null,
  @NEWDPC nvarchar(8) = '',
  @NEWCART nvarchar(10) = '',
  @NEWLOT nvarchar(5) = '',
  @VALIDATIONMESSAGE nvarchar(200) = '',
  @ADDRESSCHANGED bit = 0,
  @CERTIFICATIONDATA integer = 0
)
as
  set nocount on;
  exec dbo.[USP_SKIPSEARCHCONSTITUENTUPDATES];--skip the triggers while committing

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate();

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @LASTRUNDATE datetime;

  declare @INFOSOURCECODEID uniqueidentifier;
  select top(1) @INFOSOURCECODEID = ADDRESSVALIDATION_INFOSOURCECODEID from dbo.DATATUNEUP;

    select
        @LASTRUNDATE = ADDRESSVALIDATIONPROCESS.DATELASTRUN
    from
        dbo.ADDRESSVALIDATIONPROCESS
    where
        ADDRESSVALIDATIONPROCESS.ADDRESSVALIDATIONID = @ADDRESSVALIDATIONID;

  -- Update address validation process step code
  update
    dbo.[ADDRESSVALIDATION]
  set
    [STEPCODE] = 2,
    [DATECHANGED] = @CURRENTDATE,
    [CHANGEDBYID] = @CHANGEAGENTID
  where
    [ID] = @ADDRESSVALIDATIONID;

  set @ID = @ADDRESSID;        

  -- Only Update Validation Message and attempt date if the address did not validate
  if (@VALIDATIONMESSAGE is null or @VALIDATIONMESSAGE = '') and (@NEWADDRESSBLOCK is null or @NEWADDRESSBLOCK = '')
  begin
    raiserror('BBERR_BATCHADDRESSVALIDATION_BLANKVALIDATIONMESSAGE', 13, 1);
    return -1;
  end

  if @CERTIFICATIONDATA = 0
    begin
      if exists(select ID from dbo.ADDRESSVALIDATIONUPDATE where ID = @ADDRESSID)
        update dbo.ADDRESSVALIDATIONUPDATE
        set LASTVALIDATIONATTEMPTDATE = @LASTRUNDATE,
          VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
          CERTIFICATIONDATA = @CERTIFICATIONDATA,
          INFOSOURCECODEID = isnull(INFOSOURCECODEID, @INFOSOURCECODEID),
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where ID = @ADDRESSID;
      else
        insert into dbo.ADDRESSVALIDATIONUPDATE
        (ID, LASTVALIDATIONATTEMPTDATE, VALIDATIONMESSAGE, CERTIFICATIONDATA, INFOSOURCECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values
        (@ID, @LASTRUNDATE, @VALIDATIONMESSAGE, isnull(@CERTIFICATIONDATA, 0), @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
    end
  else
    begin
      -- RobertDi 4/4/2012 - Avoid unnecessary audit growth by only updating the table if something changed.
      update dbo.ADDRESS
      set ADDRESSBLOCK = @NEWADDRESSBLOCK,
        CITY = @NEWCITY,
        STATEID = @NEWSTATEID,
        POSTCODE = @NEWPOSTCODE,                    
        DPC = @NEWDPC,
        LOT = @NEWLOT,
        CART = @NEWCART,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        ID = @ADDRESSID
        and (
          not (ADDRESSBLOCK = @NEWADDRESSBLOCK or (ADDRESSBLOCK is null and @NEWADDRESSBLOCK is null))
          or not (CITY = @NEWCITY or (CITY is null and @NEWCITY is null))
          or not (STATEID = @NEWSTATEID or (STATEID is null and @NEWSTATEID is null))
          or not (POSTCODE = @NEWPOSTCODE or (POSTCODE is null and @NEWPOSTCODE is null))
          or not (DPC = @NEWDPC or (DPC is null and @NEWDPC is null))
          or not (LOT = @NEWLOT or (LOT is null and @NEWLOT is null))
          or not (CART = @NEWCART or (CART is null and @NEWCART is null))
        );

      if exists(select ID from dbo.ADDRESSVALIDATIONUPDATE where ID = @ADDRESSID)
        update dbo.ADDRESSVALIDATIONUPDATE
        set COUNTYCODEID = @NEWCOUNTYID,
          CONGRESSIONALDISTRICTCODEID = @NEWCONGRESSIONALDISTRICTID,
          LASTVALIDATIONATTEMPTDATE = @LASTRUNDATE,
          VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
          CERTIFICATIONDATA = @CERTIFICATIONDATA,
          INFOSOURCECODEID = isnull(INFOSOURCECODEID, @INFOSOURCECODEID),
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where ID = @ADDRESSID;
      else
        insert into dbo.ADDRESSVALIDATIONUPDATE
        (ID, COUNTYCODEID, CONGRESSIONALDISTRICTCODEID, LASTVALIDATIONATTEMPTDATE, VALIDATIONMESSAGE, CERTIFICATIONDATA, INFOSOURCECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values
        (@ID, @NEWCOUNTYID, @NEWCONGRESSIONALDISTRICTID, @LASTRUNDATE, @VALIDATIONMESSAGE, isnull(@CERTIFICATIONDATA, 0), @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
    end

  return 0;