USP_MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION_SAVE

CREATE procedure dbo.[USP_MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION_SAVE]
(
  @ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID uniqueidentifier,
  @SEGMENTATIONID uniqueidentifier,
  @ACKNOWLEDGEDATE datetime = null,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @REVENUELETTERTABLENAME nvarchar(128);
  declare @REVENUERECEIPTTABLENAME nvarchar(128);
  declare @SQL nvarchar(max);

  begin try
    if @CHANGEAGENTID is null  
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();

    if exists(select 1 from dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] where [ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID)
      /* Update the existing row */
      update dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] set
        [SEGMENTATIONID] = @SEGMENTATIONID,
        [ACKNOWLEDGEDATE] = @ACKNOWLEDGEDATE,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID;
    else
      /* Insert a new row */
      insert into dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] (
        [ID],
        [ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID],
        [SEGMENTATIONID],
        [ACKNOWLEDGEDATE],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
      ) values (
        newid(),
        @ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID,
        @SEGMENTATIONID,
        @ACKNOWLEDGEDATE,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );


    --Loop through every record source and update the MKTSEGMENTATIONID on the MKTREVENUELETTER and MKTREVENUERECEIPT tables...

    declare RECORDSOURCECURSOR cursor local fast_forward for
      select
        (case when dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([ID]) = 1 then 'REVENUELETTERMARKETING' else dbo.[UFN_MKTREVENUELETTER_MAKETABLENAME]([ID]) end),
        (case when dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([ID]) = 1 then 'REVENUERECEIPTMARKETING' else dbo.[UFN_MKTREVENUERECEIPT_MAKETABLENAME]([ID]) end)
      from dbo.[MKTGIFTRECORDSOURCE]
      where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([ID]) = 1;

    open RECORDSOURCECURSOR;
    fetch next from RECORDSOURCECURSOR into @REVENUELETTERTABLENAME, @REVENUERECEIPTTABLENAME;

    while (@@FETCH_STATUS = 0)
    begin
      --Update records in the REVENUELETTER table...

      set @SQL = 'update dbo.[' + @REVENUELETTERTABLENAME + '] set' + char(13) +
                 '  [MKTSEGMENTATIONID] = @SEGMENTATIONID,' + char(13) +
                 '  [CHANGEDBYID] = @CHANGEAGENTID,' + char(13) +
                 '  [DATECHANGED] = @CURRENTDATE' + char(13) +
                 'where [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID';
      exec sp_executesql @SQL, N'@ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID uniqueidentifier, @SEGMENTATIONID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime', @ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID = @ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID, @SEGMENTATIONID = @SEGMENTATIONID, @CHANGEAGENTID = @CHANGEAGENTID, @CURRENTDATE = @CURRENTDATE;

      --Update records in the REVENUERECEIPT table...

      set @SQL = 'update dbo.[' + @REVENUERECEIPTTABLENAME + '] set' + char(13) +
                 '  [MKTSEGMENTATIONID] = @SEGMENTATIONID,' + char(13) +
                 '  [CHANGEDBYID] = @CHANGEAGENTID,' + char(13) +
                 '  [DATECHANGED] = @CURRENTDATE' + char(13) +
                 'where [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = @ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID';
      exec sp_executesql @SQL, N'@ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID uniqueidentifier, @SEGMENTATIONID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime', @ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID = @ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID, @SEGMENTATIONID = @SEGMENTATIONID, @CHANGEAGENTID = @CHANGEAGENTID, @CURRENTDATE = @CURRENTDATE;

      fetch next from RECORDSOURCECURSOR into @REVENUELETTERTABLENAME, @REVENUERECEIPTTABLENAME;
    end

    close RECORDSOURCECURSOR;
    deallocate RECORDSOURCECURSOR;

    -- also cache segment record count

    -- this is the equivalent of the code in Marketing.Catalog.Segmentation.Segments.GetRecordCountForSegment


    declare @SEGMENTATIONSEGMENTID uniqueidentifier;
    declare @SEGMENTID uniqueidentifier;
    declare @PACKAGEID uniqueidentifier;
    declare @REVENUESEGMENTINFO table ([RECORDSOURCEID] uniqueidentifier, [REVENUELETTERTABLENAME] nvarchar(128), [SEGMENTID] uniqueidentifier, [SEGMENTATIONID] uniqueidentifier, [PACKAGEID] uniqueidentifier);
    declare @RECORDSOURCEID uniqueidentifier;
    declare @COUNT integer;

    declare SEGMENTATIONSEGMENTS cursor local fast_forward for
      select [ID], [SEGMENTID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID;

    open SEGMENTATIONSEGMENTS
    fetch next from SEGMENTATIONSEGMENTS into @SEGMENTATIONSEGMENTID, @SEGMENTID;

    while (@@FETCH_STATUS = 0)
      begin
        delete from @REVENUESEGMENTINFO;
        insert into @REVENUESEGMENTINFO exec dbo.[USP_MKTSEGMENTATIONSEGMENT_GETREVENUESEGMENTINFO] @SEGMENTATIONSEGMENTID;

        select top 1
          @RECORDSOURCEID = [RECORDSOURCEID],
          @REVENUELETTERTABLENAME = [REVENUELETTERTABLENAME]
        from @REVENUESEGMENTINFO;

        set @SQL = 
          'select @COUNT = count(1)' + char(13) +
          'from dbo.[' + @REVENUELETTERTABLENAME + '] as [RL]' + char(13);

        if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1
          set @SQL = @SQL +
            'inner join dbo.[REVENUELETTERMARKETING] as [RLM] on [RLM].[ID] = [RL].[ID]' + char(13) +
            'where [RLM].[MKTSEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
            'and [RLM].[MKTSEGMENTID] = @SEGMENTID' + char(13) +
            'and [RLM].[MKTPACKAGEID] = @PACKAGEID;'
        else
          set @SQL = @SQL +        
            'where [RL].[MKTSEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
            'and [RL].[MKTSEGMENTID] = @SEGMENTID;'

        exec sp_executesql @SQL, N'@COUNT integer output, @SEGMENTATIONID uniqueidentifier, @SEGMENTID uniqueidentifier, @PACKAGEID uniqueidentifier', @COUNT = @COUNT output, @SEGMENTATIONID = @SEGMENTATIONID, @SEGMENTID = @SEGMENTID, @PACKAGEID = @PACKAGEID;

        exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CACHERECORDCOUNT] @SEGMENTATIONSEGMENTID, @COUNT;

        fetch next from SEGMENTATIONSEGMENTS into @SEGMENTATIONSEGMENTID, @SEGMENTID;
      end

    close SEGMENTATIONSEGMENTS;
    deallocate SEGMENTATIONSEGMENTS;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;