USP_MKTMEMBERSHIPMAILINGSEGMENT_GETRESPONSECOUNTS

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

  declare @EXCLUDE bit;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @DATATABLE nvarchar(255);
  declare @GIFTIDSETNAME nvarchar(255);
  declare @ACTIONCODEFIELDNAME nvarchar(255);
  declare @ISBBEC bit;
  declare @SQL nvarchar(max);
  declare @PARAMDEF nvarchar(1024);

  declare @RENEWALS int;
  declare @UPGRADES int;
  declare @DOWNGRADES int;
  declare @TOTALRENEWALAMOUNT money;
  declare @AVERAGERENEWALAMOUNT money;
  declare @ORGANIZATIONTOTALRENEWALAMOUNT money;
  declare @ORGANIZATIONAVERAGERENEWALAMOUNT money;

  declare @WHERESQL nvarchar(max);

  -- gather some info so we can build the SQL

  select distinct
    @EXCLUDE = [SS].[EXCLUDE],
    @SEGMENTATIONID = [SS].[SEGMENTATIONID],
    @GIFTIDSETNAME = dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([SA].[NORMALGIFTIDSETREGISTERID]),
    @ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([S].[QUERYVIEWCATALOGID])
  from dbo.[MKTSEGMENTATIONSEGMENT] as [SS]
  left join dbo.[MKTSEGMENT] as [S] on [S].[ID] = [SS].[SEGMENTID]
  left join dbo.[MKTSEGMENTATIONTESTSEGMENT] as [STS] on [STS].[SEGMENTID] = [SS].[ID]
  inner join dbo.[MKTSEGMENTATIONACTIVATE] as [SA] on [SA].[SEGMENTATIONID] = [SS].[SEGMENTATIONID] and [SA].[RECORDSOURCEID] = [S].[QUERYVIEWCATALOGID]
  where [SS].[ID] = @SEGMENTID
  and (@TESTSEGMENTID is null or [STS].[ID] = @TESTSEGMENTID);

  if @EXCLUDE = 0
    begin
      set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);

      set @WHERESQL = 'where [DONORS].[SEGMENTID] = @SEGMENTID' + char(13) +
                      'and [DONORS].[TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + char(13);

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

      if @ISBBEC = 1
        set @ACTIONCODEFIELDNAME = '[MEMBERSHIPTRANSACTION].[ACTIONCODE]';
      else
        set @ACTIONCODEFIELDNAME = '[TODO]';

      set @SQL = 'select' + char(13) +
                 '  @RENEWALS = isnull(sum(case when isnull(' + @ACTIONCODEFIELDNAME + ', 255) in (1, 2, 3) then 1 else 0 end), 0),' + char(13) + 
                 '  @UPGRADES = isnull(sum(case when isnull(' + @ACTIONCODEFIELDNAME + ', 255) = 2 then 1 else 0 end), 0),' + char(13) + 
                 '  @DOWNGRADES = isnull(sum(case when isnull(' + @ACTIONCODEFIELDNAME + ', 255) = 3 then 1 else 0 end), 0),' + char(13) + 
                 '  @TOTALRENEWALAMOUNT = isnull(sum(case when isnull(' + @ACTIONCODEFIELDNAME + ', 255) in (1, 2, 3) then [GIFTIDSET].[AMOUNT] else 0 end), 0),' + char(13) +
                 '  @AVERAGERENEWALAMOUNT = isnull(avg(case when isnull(' + @ACTIONCODEFIELDNAME + ', 255) in (1, 2, 3) then [GIFTIDSET].[AMOUNT] else 0 end), 0),' + char(13) +
                 '  @ORGANIZATIONTOTALRENEWALAMOUNT = isnull(sum(case when isnull(' + @ACTIONCODEFIELDNAME + ', 255) in (1, 2, 3) then [GIFTIDSET].[ORGANIZATIONAMOUNT] else 0 end), 0),' + char(13) +
                 '  @ORGANIZATIONAVERAGERENEWALAMOUNT = isnull(avg(case when isnull(' + @ACTIONCODEFIELDNAME + ', 255) in (1, 2, 3) then [GIFTIDSET].[ORGANIZATIONAMOUNT] else 0 end), 0)' + char(13);

      -- join to the activated data table

      set @SQL = @SQL + 'from dbo.' + @GIFTIDSETNAME + ' as [GIFTIDSET]' + char(13) +
                        'inner join dbo.[' + @DATATABLE + '] as [DONORS] on [DONORS].[DONORID] = [GIFTIDSET].[DONORID]' + char(13);

      if @ISBBEC = 1
        set @SQL = @SQL + 'left outer join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [GIFTIDSET].[ID]' + char(13) +
                          'left outer join dbo.[MEMBERSHIPTRANSACTION] on [MEMBERSHIPTRANSACTION].[REVENUESPLITID] = [FINANCIALTRANSACTIONLINEITEM].[ID]' + char(13);
      else
        set @SQL = @SQL + 'TODO';

      set @SQL = @SQL + @WHERESQL;

      set @PARAMDEF = '@SEGMENTID uniqueidentifier, ' +
                      '@TESTSEGMENTID uniqueidentifier, ' +
                      '@ASOFDATE datetime, ' +
                      '@RENEWALS int output, ' +
                      '@UPGRADES int output, ' +
                      '@DOWNGRADES int output, ' +
                      '@TOTALRENEWALAMOUNT money output, ' +
                      '@AVERAGERENEWALAMOUNT money output, ' +
                      '@ORGANIZATIONTOTALRENEWALAMOUNT money output, ' +
                      '@ORGANIZATIONAVERAGERENEWALAMOUNT money output';

      -- execute the SQL to get the counts/amounts

      exec sp_executesql @SQL, @PARAMDEF, 
        @SEGMENTID = @SEGMENTID,
        @TESTSEGMENTID = @TESTSEGMENTID,
        @ASOFDATE = @ASOFDATE,
        @RENEWALS = @RENEWALS output,
        @UPGRADES = @UPGRADES output, 
        @DOWNGRADES = @DOWNGRADES output, 
        @TOTALRENEWALAMOUNT = @TOTALRENEWALAMOUNT output, 
        @AVERAGERENEWALAMOUNT = @AVERAGERENEWALAMOUNT output,
        @ORGANIZATIONTOTALRENEWALAMOUNT = @ORGANIZATIONTOTALRENEWALAMOUNT output, 
        @ORGANIZATIONAVERAGERENEWALAMOUNT = @ORGANIZATIONAVERAGERENEWALAMOUNT output;
    end
  else -- @EXCLUDE = 1

    begin
      set @RENEWALS = 0;
      set @UPGRADES = 0;
      set @DOWNGRADES = 0;
      set @TOTALRENEWALAMOUNT = 0;
      set @AVERAGERENEWALAMOUNT = 0;
      set @ORGANIZATIONTOTALRENEWALAMOUNT = 0;
      set @ORGANIZATIONAVERAGERENEWALAMOUNT = 0;
    end

  -- return the values

  select 
    @RENEWALS as [RENEWALS],
    @UPGRADES as [UPGRADES],
    @DOWNGRADES as [DOWNGRADES],
    @TOTALRENEWALAMOUNT as [TOTALRENEWALAMOUNT],
    @AVERAGERENEWALAMOUNT as [AVERAGERENEWALAMOUNT],
    @ORGANIZATIONTOTALRENEWALAMOUNT as [ORGANIZATIONTOTALRENEWALAMOUNT],
    @ORGANIZATIONAVERAGERENEWALAMOUNT as [ORGANIZATIONAVERAGERENEWALAMOUNT];

  return 0;