USP_CONSTITUENTSOLICITCODE_ADJUSTPREVIOUSDATERANGE

CREATE procedure dbo.USP_CONSTITUENTSOLICITCODE_ADJUSTPREVIOUSDATERANGE
(
  @CONSTITUENTID uniqueidentifier,
  @SOLICITCODEID uniqueidentifier,
  @NEW_STARTDATE date,
  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
  -- This date adjustment applies to EU / Advanced Consent codes

  if (select CONSENTCODE from dbo.SOLICITCODE where ID = @SOLICITCODEID) = 0
    return 0;

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

  declare @CURRENTDATE datetime = getdate();
  declare @TODAY date = convert(date, @CURRENTDATE); -- Strip the time component


  begin try
    declare @UPDATEROWID uniqueidentifier = null;

    -- Gather data

    select top 1 @UPDATEROWID = ID
    from dbo.CONSTITUENTSOLICITCODE
    where
      CONSTITUENTID = @CONSTITUENTID
      and SOLICITCODEID = @SOLICITCODEID
      and STARTDATE < @NEW_STARTDATE
      and ENDDATE is null
    order by STARTDATE desc;

    if @UPDATEROWID is not null
    begin
      declare @AUTOENDDATE datetime = dateadd(day, -1, @NEW_STARTDATE);

      -- End date previous solicit code association

      update dbo.CONSTITUENTSOLICITCODE
      set
        ENDDATE = @AUTOENDDATE,
        DATECHANGED = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID
      where CONSTITUENTSOLICITCODE.ID = @UPDATEROWID;

      -- Mark associated mail preferences as Do not send if the ended solicit code is no longer active

      if @AUTOENDDATE < @TODAY
      begin
        update dbo.MAILPREFERENCE set
          SENDMAIL = 0,
          USESEASONALADDRESS = 0,
          USEPRIMARYADDRESS = 0,
          USEPRIMARYEMAIL = 0,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where CONSTITUENTSOLICITCODEID = @UPDATEROWID;
      end
    end
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;
end