USP_CONSTITUENTSOLICITCODE_ADJUSTPREVIOUSDATERANGE_BULK

CREATE procedure dbo.USP_CONSTITUENTSOLICITCODE_ADJUSTPREVIOUSDATERANGE_BULK
(
  @CONSTITUENTID uniqueidentifier,
  @SOLICITCODES xml,
  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
  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


  declare @INPUT table
  (
    SOLICITCODEID uniqueidentifier,
    NEW_STARTDATE date
  );

  declare @UPDATEROWS table
  (
    ROWNUMBER bigint,
    CONSTITUENTSOLICITCODEID uniqueidentifier,
    AUTOENDDATE date
  );

  -- Populate temp table with selected data

  insert into @INPUT
  select
    T.c.value('(SOLICITCODEID)[1]','uniqueidentifier') AS 'SOLICITCODEID',
    T.c.value('(STARTDATE)[1]','datetime') AS 'STARTDATE'
  from @SOLICITCODES.nodes('/SOLICITCODES/ITEM') T(c);

  begin try
    -- Gather data

    insert into @UPDATEROWS
    select
      ROW_NUMBER() over (partition by CONSTITUENTSOLICITCODE.CONSTITUENTID, CONSTITUENTSOLICITCODE.SOLICITCODEID order by CONSTITUENTSOLICITCODE.STARTDATE desc) as ROWNUMBER,
      CONSTITUENTSOLICITCODE.ID as CONSTITUENTSOLICITCODEID,
      dateadd(day, -1, INPUT.NEW_STARTDATE) as AUTOENDDATE
    from @INPUT INPUT
      inner join dbo.CONSTITUENTSOLICITCODE on INPUT.SOLICITCODEID = CONSTITUENTSOLICITCODE.SOLICITCODEID
      inner join dbo.SOLICITCODE on CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
    where
      CONSTITUENTSOLICITCODE.CONSTITUENTID = @CONSTITUENTID
      and (SOLICITCODE.CONSENTCODE = 1
           or
           SOLICITCODE.CONSENTCODE = 2) -- EU / Advanced Consent

      and CONSTITUENTSOLICITCODE.STARTDATE < INPUT.NEW_STARTDATE
      and CONSTITUENTSOLICITCODE.ENDDATE is null;

    -- Discard entries that are not the most recent solicit code association

    delete from @UPDATEROWS
    where ROWNUMBER <> 1;

    if (select count(CONSTITUENTSOLICITCODEID) from @UPDATEROWS) > 0
    begin
      -- End date previous solicit code associations

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

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

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

  return 0;
end