USP_DATAFORMTEMPLATE_ADD_SOLICITCODECHANNELOPTOUT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SOLICITCODECHANNELOPTOUT
(
  @ID uniqueidentifier = null output,
  @CONSTITUENTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @SOLICITCODECHANNELCODE tinyint = null,
  @CONSENTCODE tinyint = 1,
  @STARTDATE datetime = null,
  @SOURCEEVIDENCECODEID uniqueidentifier = null,
  @COMMENTS nvarchar(255) = ''
)
as

set nocount on;

if @ID is null
    set @ID = newid();

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

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

begin try

  -- handle inserting the data

  insert into dbo.SOLICITCODECHANNELOPTOUT
    ([ID],[CONSTITUENTID],[SOLICITCODECHANNELCODE],[CONSENTCODE],[STARTDATE],[SOURCECODEID],[COMMENTS],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
  values
    (@ID,@CONSTITUENTID,@SOLICITCODECHANNELCODE,@CONSENTCODE,@STARTDATE,@SOURCEEVIDENCECODEID,@COMMENTS,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

    -- select set of SC to end / start opt-outs on

    -- create working set of CONSTITUENTSOLICITCODE's

    declare @SCWORKING table(
      [ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
      [CONSTITUENTID] [uniqueidentifier] NOT NULL,
      [SOLICITCODEID] [uniqueidentifier] NOT NULL,
      [SEQUENCE] [int] NOT NULL,
      [ADDEDBYID] [uniqueidentifier] NOT NULL,
      [CHANGEDBYID] [uniqueidentifier] NOT NULL,
      [DATEADDED] [datetime] NOT NULL,
      [DATECHANGED] [datetime] NOT NULL,
      [TS] [timestamp] NOT NULL,
      [TSLONG]  AS (CONVERT([bigint],[TS],0)),
      [STARTDATE] [datetime] NULL,
      [ENDDATE] [datetime] NULL,
      [COMMENTS] [nvarchar](100) NOT NULL,
      [CONSENTPREFERENCECODE] [tinyint] NOT NULL,
      [CONSENTPREFERENCE]  AS (case [CONSENTPREFERENCECODE] when (0) then N'No response' when (1) then N'Opt-out' when (2) then N'Opt-in'  end),
      [SOURCECODEID] [uniqueidentifier] NULL,
      [SOURCEFILEPATH] [nvarchar](260) NOT NULL,
      [PRIVACYPOLICYFILEPATH] [nvarchar](260) NOT NULL,
      [SUPPORTINGINFORMATION] [nvarchar](max) NOT NULL,
      [CONSENTSTATEMENT] [nvarchar](max) NOT NULL,
      [SOLICITCODECHANNELOPTOUTID]  [uniqueidentifier] NULL
    );

    if @CONSENTCODE = 255
    begin
        -- fill working set - 255 = All so we don't care about the consent type

        insert into @SCWORKING
        (
          [ID],
          [CONSTITUENTID],
          [SOLICITCODEID],
          [SEQUENCE],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED],
          [STARTDATE],
          [ENDDATE],
          [COMMENTS],
          [CONSENTPREFERENCECODE],
          [SOURCECODEID],
          [SOURCEFILEPATH],
          [PRIVACYPOLICYFILEPATH],
          [SUPPORTINGINFORMATION],
          [CONSENTSTATEMENT],
          [SOLICITCODECHANNELOPTOUTID]
        )
        select  
          csc.[ID],
          csc.[CONSTITUENTID],
          csc.[SOLICITCODEID],
          csc.[SEQUENCE],
          csc.[ADDEDBYID],
          csc.[CHANGEDBYID],
          csc.[DATEADDED],
          csc.[DATECHANGED],
          csc.[STARTDATE],
          csc.[ENDDATE],
          csc.[COMMENTS],
          csc.[CONSENTPREFERENCECODE],
          csc.[SOURCECODEID],
          csc.[SOURCEFILEPATH],
          csc.[PRIVACYPOLICYFILEPATH],
          csc.[SUPPORTINGINFORMATION],
          csc.[CONSENTSTATEMENT],
          csc.[SOLICITCODECHANNELOPTOUTID]
        from    dbo.CONSTITUENTSOLICITCODE csc
        join    dbo.SOLICITCODE sc on sc.ID = csc.SOLICITCODEID
        where    CONSTITUENTID = @CONSTITUENTID
        and        (ENDDATE is null or ENDDATE >= @CURRENTDATE)
        and        sc.ACTIVE = 1
        and        sc.SOLICITCODECHANNELCODE = @SOLICITCODECHANNELCODE
        and        (CONSENTPREFERENCECODE = 0 OR CONSENTPREFERENCECODE = 2)
        and        SOLICITCODECHANNELOPTOUTID is null;
    end
    else
    begin
    -- fill working set

        insert into @SCWORKING
        (
          [ID],
          [CONSTITUENTID],
          [SOLICITCODEID],
          [SEQUENCE],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED],
          [STARTDATE],
          [ENDDATE],
          [COMMENTS],
          [CONSENTPREFERENCECODE],
          [SOURCECODEID],
          [SOURCEFILEPATH],
          [PRIVACYPOLICYFILEPATH],
          [SUPPORTINGINFORMATION],
          [CONSENTSTATEMENT],
          [SOLICITCODECHANNELOPTOUTID]
        )
        select  
          csc.[ID],
          csc.[CONSTITUENTID],
          csc.[SOLICITCODEID],
          csc.[SEQUENCE],
          csc.[ADDEDBYID],
          csc.[CHANGEDBYID],
          csc.[DATEADDED],
          csc.[DATECHANGED],
          csc.[STARTDATE],
          csc.[ENDDATE],
          csc.[COMMENTS],
          csc.[CONSENTPREFERENCECODE],
          csc.[SOURCECODEID],
          csc.[SOURCEFILEPATH],
          csc.[PRIVACYPOLICYFILEPATH],
          csc.[SUPPORTINGINFORMATION],
          csc.[CONSENTSTATEMENT],
          csc.[SOLICITCODECHANNELOPTOUTID]
        from    dbo.CONSTITUENTSOLICITCODE csc
        join    dbo.SOLICITCODE sc on sc.ID = csc.SOLICITCODEID
        where    CONSTITUENTID = @CONSTITUENTID
        and        (ENDDATE is null or ENDDATE >= @CURRENTDATE)
        and        sc.ACTIVE = 1 -- should we care about this?

        and        sc.SOLICITCODECHANNELCODE = @SOLICITCODECHANNELCODE
        and        (CONSENTPREFERENCECODE = 0 OR CONSENTPREFERENCECODE = 2)
        and        SOLICITCODECHANNELOPTOUTID is null
        and        sc.CONSENTCODE = @CONSENTCODE;
    end

    -- validate we are not going to set end dates in the past

    declare @COUNTOFWRONGDATES int = 0
    select  @COUNTOFWRONGDATES = count(STARTDATE)
    from    @SCWORKING
    where   STARTDATE = @STARTDATE;

    if @COUNTOFWRONGDATES > 0 
        raiserror('BBERR_WRONGDATE_ENDDATEINPAST', 13, 1);

    -- update End Dates as (date-1) days on SC in temp table

    update    CONSTITUENTSOLICITCODE
    set          CONSTITUENTSOLICITCODE.ENDDATE = DATEADD(d, -1, @STARTDATE),
            CONSTITUENTSOLICITCODE.CHANGEDBYID = @CHANGEAGENTID,
            CONSTITUENTSOLICITCODE.DATECHANGED = @CURRENTDATE
    from    dbo.CONSTITUENTSOLICITCODE
    join      @SCWORKING wip on wip.id = CONSTITUENTSOLICITCODE.ID;

    if (convert(DATE, @CURRENTDATE) >= convert(DATE, @STARTDATE))
    begin
        -- set any associated MP to 'do not send'

        update    MAILPREFERENCE
        set
          MAILPREFERENCE.SENDMAIL = 0,
          MAILPREFERENCE.USESEASONALADDRESS = 0,
          MAILPREFERENCE.USEPRIMARYADDRESS = 0,
          MAILPREFERENCE.USEPRIMARYEMAIL = 0,
          MAILPREFERENCE.CHANGEDBYID = @CHANGEAGENTID,
          MAILPREFERENCE.DATECHANGED = @CURRENTDATE
        from    dbo.MAILPREFERENCE
        join    @SCWORKING wip on wip.id = MAILPREFERENCE.CONSTITUENTSOLICITCODEID;
    end

    -- update held data to re-insert

    update  @SCWORKING
    set
      [ID] = NEWID(),
      [ADDEDBYID] = @CHANGEAGENTID,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATEADDED] = GETDATE(),
      [DATECHANGED] = GETDATE(),
      [STARTDATE] = @STARTDATE,
      [ENDDATE] = null,
      [COMMENTS] = 'Channel Opt-out',
      [CONSENTPREFERENCECODE] = 1,
      [SOURCECODEID] = @SOURCEEVIDENCECODEID,
      [SOURCEFILEPATH] = '',
      [PRIVACYPOLICYFILEPATH] = '',
      [SUPPORTINGINFORMATION] = 'Channel Opt-out',
      [CONSENTSTATEMENT] = 'Channel Opt-out',
      [SOLICITCODECHANNELOPTOUTID] = @ID;

    -- insert opt-out records

    insert into dbo.CONSTITUENTSOLICITCODE
    (
      [ID],
      [CONSTITUENTID],
      [SOLICITCODEID],
      [SEQUENCE],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [STARTDATE],
      [ENDDATE],
      [COMMENTS],
      [CONSENTPREFERENCECODE],
      [SOURCECODEID],
      [SOURCEFILEPATH],
      [PRIVACYPOLICYFILEPATH],
      [SUPPORTINGINFORMATION],
      [CONSENTSTATEMENT],
      [SOLICITCODECHANNELOPTOUTID]
    )
    select
      [ID],
      [CONSTITUENTID],
      [SOLICITCODEID],
      [SEQUENCE],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [STARTDATE],
      [ENDDATE],
      [COMMENTS],
      [CONSENTPREFERENCECODE],
      [SOURCECODEID],
      [SOURCEFILEPATH],
      [PRIVACYPOLICYFILEPATH],
      [SUPPORTINGINFORMATION],
      [CONSENTSTATEMENT],
      [SOLICITCODECHANNELOPTOUTID]
 from
      @SCWORKING;

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0