USP_MKTSEGMENTATIONACTIVATE_CACHELISTRESPONSECOUNTS

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

  declare @LISTID uniqueidentifier;
  declare @SEGMENTID uniqueidentifier;
  declare @PACKAGEID uniqueidentifier;
  declare @RESPONSECOUNTS table([QUANTITY] int not null, [RESPONDERS] int not null, [RESPONSES] int not null, [TOTALCOST] money not null, [TOTALGIFTAMOUNT] money not null, [AVERAGEGIFTAMOUNT] money not null, [ORGANIZATIONTOTALCOST] money not null, [ORGANIZATIONTOTALGIFTAMOUNT] money not null, [ORGANIZATIONAVERAGEGIFTAMOUNT] money not null);
  declare @BASECURRENCYID uniqueidentifier;

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

    select @BASECURRENCYID = [BASECURRENCYID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

    declare RESPONSECOUNTCURSOR cursor local fast_forward for
      --Imported list segments

      select distinct
        [MKTLIST].[ID],
        [MKTSEGMENTATIONSEGMENT].[ID],
        [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[IDSETRECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID] or [MKTSEGMENTLIST].[IDSETRECORDTYPEID] in (select [LS].[IDSETRECORDTYPEID] from dbo.[MKTSEGMENTLIST] as [LS] inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [LS].[CONSOLIDATEDQUERYVIEWID] where [QUERYVIEWCATALOG].[RECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID])
      inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTLIST].[RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      and [MKTSEGMENTLIST].[TYPECODE] <> 1

      union

      --Imported list test segments

      select distinct
        [MKTLIST].[ID],
        [MKTSEGMENTATIONSEGMENT].[ID],
        [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[IDSETRECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID] or [MKTSEGMENTLIST].[IDSETRECORDTYPEID] in (select [LS].[IDSETRECORDTYPEID] from dbo.[MKTSEGMENTLIST] as [LS] inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [LS].[CONSOLIDATEDQUERYVIEWID] where [QUERYVIEWCATALOG].[RECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID])
      inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTLIST].[RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      and [MKTSEGMENTLIST].[TYPECODE] <> 1

      union

      --Vendor managed list segments

      select distinct
        [MKTLIST].[ID],
        [MKTSEGMENTATIONSEGMENT].[ID],
        [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
      inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTLIST].[RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      and [MKTSEGMENTLIST].[TYPECODE] = 1

      union

      --Vendor managed list test segments

      select distinct
        [MKTLIST].[ID],
        [MKTSEGMENTATIONSEGMENT].[ID],
        [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
      inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTLIST].[RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      and [MKTSEGMENTLIST].[TYPECODE] = 1;

    open RESPONSECOUNTCURSOR;
    fetch next from RESPONSECOUNTCURSOR into @LISTID, @SEGMENTID, @PACKAGEID;

    while (@@FETCH_STATUS = 0)
    begin
      delete from @RESPONSECOUNTS;

      insert into @RESPONSECOUNTS
        exec dbo.[USP_MKTSEGMENTATIONLIST_GETRESPONSECOUNTS] @LISTID, @SEGMENTATIONID, @SEGMENTID, @PACKAGEID;

      if exists(select 1 from dbo.[MKTSEGMENTATIONLISTACTIVE] where [LISTID] = @LISTID and [SEGMENTID] = @SEGMENTID and [PACKAGEID] = @PACKAGEID)
        --The list cache row already exists, so just update the existing row...

        update dbo.[MKTSEGMENTATIONLISTACTIVE] set
          [MKTSEGMENTATIONLISTACTIVE].[QUANTITY] = [RC].[QUANTITY],
          [MKTSEGMENTATIONLISTACTIVE].[RESPONDERS] = [RC].[RESPONDERS],
          [MKTSEGMENTATIONLISTACTIVE].[RESPONSES] = [RC].[RESPONSES],
          [MKTSEGMENTATIONLISTACTIVE].[TOTALCOST] = [RC].[TOTALCOST],
          [MKTSEGMENTATIONLISTACTIVE].[TOTALGIFTAMOUNT] = [RC].[TOTALGIFTAMOUNT],
          [MKTSEGMENTATIONLISTACTIVE].[CHANGEDBYID] = @CHANGEAGENTID,
          [MKTSEGMENTATIONLISTACTIVE].[DATECHANGED] = getdate(),
          [MKTSEGMENTATIONLISTACTIVE].[ORGANIZATIONTOTALCOST] = [RC].[ORGANIZATIONTOTALCOST],
          [MKTSEGMENTATIONLISTACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT] = [RC].[ORGANIZATIONTOTALGIFTAMOUNT]
        from @RESPONSECOUNTS as [RC]
        where [MKTSEGMENTATIONLISTACTIVE].[LISTID] = @LISTID
        and [MKTSEGMENTATIONLISTACTIVE].[SEGMENTID] = @SEGMENTID
        and [MKTSEGMENTATIONLISTACTIVE].[PACKAGEID] = @PACKAGEID;
      else
        --Insert a new list cache row...

        insert into dbo.[MKTSEGMENTATIONLISTACTIVE] ([LISTID], [SEGMENTID], [PACKAGEID], [QUANTITY], [RESPONDERS], [RESPONSES], [TOTALCOST], [TOTALGIFTAMOUNT], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], [ORGANIZATIONTOTALCOST], [ORGANIZATIONTOTALGIFTAMOUNT], [BASECURRENCYID])
          select
            @LISTID,
            @SEGMENTID,
            @PACKAGEID,
            [QUANTITY],
            [RESPONDERS],
            [RESPONSES],
            [TOTALCOST],
            [TOTALGIFTAMOUNT],
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            getdate(),
            getdate(),
            [ORGANIZATIONTOTALCOST],
            [ORGANIZATIONTOTALGIFTAMOUNT],
            @BASECURRENCYID
          from @RESPONSECOUNTS;

      fetch next from RESPONSECOUNTCURSOR into @LISTID, @SEGMENTID, @PACKAGEID;
    end

    close RESPONSECOUNTCURSOR;
    deallocate RESPONSECOUNTCURSOR;
  end try

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

  return 0;