USP_MKTSEGMENTATION_GETRESPONSECOUNTS

CREATE procedure dbo.[USP_MKTSEGMENTATION_GETRESPONSECOUNTS]
(
  @SEGMENTATIONID uniqueidentifier,
  @ASOFDATE datetime = null
)
as
  set nocount on;

  declare @RECORDSOURCEID uniqueidentifier;
  declare @TOTALOFFERS int;
  declare @TOTALRESPONDERS int;
  declare @TOTALRESPONSES int;
  declare @TOTALGIFTAMOUNT money;
  declare @TOTALORGANIZATIONGIFTAMOUNT money;
  declare @RESPONDERS int;
  declare @RESPONSES int;
  declare @GIFTAMOUNT money;
  declare @ORGANIZATIONGIFTAMOUNT money;
  declare @SQL nvarchar(max);
  declare @PARAMDEF nvarchar(255);
  declare @GIFTIDSETID uniqueidentifier;
  declare @MAILINGTYPECODE tinyint;
  declare @ACTIVE bit;
  declare @FIRSTRESPONSEDATE date;
  declare @RECORDSOURCEFIRSTRESPONSEDATE date;

  set @TOTALRESPONDERS = 0;
  set @TOTALRESPONSES = 0;
  set @TOTALGIFTAMOUNT = 0;
  set @TOTALORGANIZATIONGIFTAMOUNT = 0;
  set @FIRSTRESPONSEDATE = null;

  set @PARAMDEF = '@ASOFDATE datetime, ' + 
                  '@RESPONDERS int output, ' +
                  '@RESPONSES int output, ' +
                  '@GIFTAMOUNT money output, ' +
                  '@ORGANIZATIONGIFTAMOUNT money output,' +
                  '@RECORDSOURCEFIRSTRESPONSEDATE date output';

  select
    @MAILINGTYPECODE = [MAILINGTYPECODE],
    @ACTIVE = [ACTIVE]
  from dbo.[MKTSEGMENTATION]
  where [ID] = @SEGMENTATIONID;

  declare RECORDSOURCECURSOR cursor local fast_forward for
    select
      [QUERYVIEWCATALOGID]
    from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID);

  open RECORDSOURCECURSOR;
  fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;

  while (@@FETCH_STATUS = 0)
    begin
      /* Gather some info so we can build the SQL for each record source */
      select
        @GIFTIDSETID = [NORMALGIFTIDSETREGISTERID]
      from dbo.[MKTSEGMENTATIONACTIVATE]
      where [SEGMENTATIONID] = @SEGMENTATIONID
      and [RECORDSOURCEID] = @RECORDSOURCEID;

      /* Build the SQL statement for this record source */
      set @SQL = 'select' + char(13) +
                 '  @RESPONDERS = count(distinct([GIFTIDSET].[DONORID])),' + char(13) + 
                 '  @RESPONSES = count([GIFTIDSET].[DONORID]),' + char(13) + 
                 '  @GIFTAMOUNT = isnull(sum([GIFTIDSET].[AMOUNT]),0),' + char(13) +
                 '  @ORGANIZATIONGIFTAMOUNT = isnull(sum([GIFTIDSET].[ORGANIZATIONAMOUNT]),0),' + char(13) +
                 '  @RECORDSOURCEFIRSTRESPONSEDATE = MIN([GIFTIDSET].[DATE])' + char(13) +
                 'from dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@GIFTIDSETID) + ' as [GIFTIDSET]';

      if @ASOFDATE is not null
        set @SQL = @SQL + char(13) + 'where [GIFTIDSET].[DATE] <= @ASOFDATE';

      /* Execute the SQL to get the counts/amounts for this record source */
      exec sp_executesql @SQL, @PARAMDEF,
        @ASOFDATE = @ASOFDATE,
        @RESPONDERS = @RESPONDERS output, 
        @RESPONSES = @RESPONSES output, 
        @GIFTAMOUNT = @GIFTAMOUNT output,
        @ORGANIZATIONGIFTAMOUNT = @ORGANIZATIONGIFTAMOUNT output,
        @RECORDSOURCEFIRSTRESPONSEDATE = @RECORDSOURCEFIRSTRESPONSEDATE output;

      /* Keep the running totals for all record sources */
      set @TOTALRESPONDERS = @TOTALRESPONDERS + @RESPONDERS;
      set @TOTALRESPONSES = @TOTALRESPONSES + @RESPONSES;
      set @TOTALGIFTAMOUNT = @TOTALGIFTAMOUNT + @GIFTAMOUNT;
      set @TOTALORGANIZATIONGIFTAMOUNT = @TOTALORGANIZATIONGIFTAMOUNT + @ORGANIZATIONGIFTAMOUNT;
      set @FIRSTRESPONSEDATE = case when @FIRSTRESPONSEDATE is null then @RECORDSOURCEFIRSTRESPONSEDATE when @RECORDSOURCEFIRSTRESPONSEDATE < @FIRSTRESPONSEDATE then @RECORDSOURCEFIRSTRESPONSEDATE else @FIRSTRESPONSEDATE end

      fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
    end;

  close RECORDSOURCECURSOR;
  deallocate RECORDSOURCECURSOR;

  if @MAILINGTYPECODE <> 4
    begin
      /* Get the total offer count for the activated mailing */
      set @SQL = 'select @TOTALOFFERS = count([DATA].[' + (case @MAILINGTYPECODE when 1 then 'REVENUEID' when 2 then 'MEMBERSHIPID' else 'DONORID' end) + '])' + char(13) +
                 'from dbo.[' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID) + '] as [DATA]' + char(13) +
                 'inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DATA].[SEGMENTID]' + char(13) +
                 'where [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0' + char(13) +
                 'and [DATA].[DONORQUERYVIEWCATALOGID] is not null';
      set @PARAMDEF = '@TOTALOFFERS int output';
      exec sp_executesql @SQL, @PARAMDEF, @TOTALOFFERS = @TOTALOFFERS output;

      /* Get the total offer count for any vendor managed list segments and add their offers to the total offer count */
      select
        @TOTALOFFERS = @TOTALOFFERS + isnull(sum(dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETQUANTITY]([MKTSEGMENTATIONSEGMENT].[ID], 3)), 0)
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTSEGMENTLIST].[TYPECODE] = 1;

      /* Get the total offer count for any historical segments (non-lists) and add their offers to the total offer count */
      select
        @TOTALOFFERS = @TOTALOFFERS + isnull(sum((case when [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] > 0 then [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] else [MKTSEGMENT].[HISTORICALQUANTITY] end)), 0)
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTSEGMENT].[ISHISTORICAL] = 1;

      /* Get the total offer count for all seeds */
      select
        @TOTALOFFERS = @TOTALOFFERS + count(1)
      from dbo.[MKTSEGMENTATIONPACKAGE]
      inner join dbo.[MKTSEGMENTATIONSEED] on [MKTSEGMENTATIONSEED].[SEGMENTATIONID] = [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID]
      where [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @SEGMENTATIONID;
    end
  else
    begin
      select 
        @TOTALOFFERS = isnull(sum(dbo.[UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETQUANTITY](
                                        [MKTSEGMENTATIONSEGMENT].[ID],
                                        @ACTIVE,
                                        case @ACTIVE when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONCALCULATIONMETHODCODE] else [MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHODCODE] end,
                                        case @ACTIVE when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONS] else [MKTSEGMENTPASSIVE].[IMPRESSIONS] end,
                                        [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
                                        [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE])), 0)
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      left outer join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    end

  /* Return the values */
  select
    @TOTALOFFERS as [OFFERS],
    @TOTALRESPONDERS as [RESPONDERS],
    @TOTALRESPONSES as [RESPONSES],
    @TOTALGIFTAMOUNT as [TOTALGIFTAMOUNT],
    (case when @TOTALOFFERS > 0 then ((cast(@TOTALRESPONSES as decimal(20,5)) / cast(@TOTALOFFERS as decimal(20,5))) * 100) else 0 end) as [RESPONSERATE],
    @TOTALORGANIZATIONGIFTAMOUNT as [TOTALORGANIZATIONGIFTAMOUNT],
    @FIRSTRESPONSEDATE as [FIRSTRESPONSEDATE];

  return 0;