USP_MKTMEMBERSHIPMAILING_GETRESPONSECOUNTS

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

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

  declare @RENEWALS int;
  declare @UPGRADES int;
  declare @DOWNGRADES int;
  declare @RENEWALAMOUNT money;
  declare @TOTALRENEWALS int;
  declare @TOTALUPGRADES int;
  declare @TOTALDOWNGRADES int;
  declare @TOTALRENEWALAMOUNT money;
  declare @ORGANIZATIONRENEWALAMOUNT money;
  declare @ORGANIZATIONTOTALRENEWALAMOUNT money;

  set @TOTALRENEWALS = 0;
  set @TOTALUPGRADES = 0;
  set @TOTALDOWNGRADES = 0;
  set @TOTALRENEWALAMOUNT = 0;
  set @ORGANIZATIONTOTALRENEWALAMOUNT = 0;

  set @PARAMDEF = '@ASOFDATE datetime, ' + 
                  '@RENEWALS int output, ' +
                  '@UPGRADES int output, ' +
                  '@DOWNGRADES int output, ' +
                  '@RENEWALAMOUNT money output, ' +
                  '@ORGANIZATIONRENEWALAMOUNT money output';

  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
      @GIFTIDSETNAME = dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([NORMALGIFTIDSETREGISTERID]),
      @ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID)
    from dbo.[MKTSEGMENTATIONACTIVATE]
    where [SEGMENTATIONID] = @SEGMENTATIONID
    and [RECORDSOURCEID] = @RECORDSOURCEID;

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

    -- build the SQL statement for this record source

    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) + 
               '  @RENEWALAMOUNT = isnull(sum(case when isnull(' + @ACTIONCODEFIELDNAME + ', 255) in (1, 2, 3) then [GIFTIDSET].[AMOUNT] else 0 end), 0),' + char(13) +
               '  @ORGANIZATIONRENEWALAMOUNT = isnull(sum(case when isnull(' + @ACTIONCODEFIELDNAME + ', 255) in (1, 2, 3) then [GIFTIDSET].[ORGANIZATIONAMOUNT] else 0 end), 0)';

    -- join to the activated data table

    set @SQL = @SQL + 'from dbo.' + @GIFTIDSETNAME + ' as [GIFTIDSET]' + 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';

    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,
      @RENEWALS = @RENEWALS output, 
      @UPGRADES = @UPGRADES output, 
      @DOWNGRADES = @DOWNGRADES output, 
      @RENEWALAMOUNT = @RENEWALAMOUNT output,
      @ORGANIZATIONRENEWALAMOUNT = @RENEWALAMOUNT output;

    /* Keep the running totals for all record sources */
    set @TOTALRENEWALS = @TOTALRENEWALS + @RENEWALS;
    set @TOTALUPGRADES = @TOTALUPGRADES + @UPGRADES;
    set @TOTALDOWNGRADES = @TOTALDOWNGRADES + @DOWNGRADES;
    set @TOTALRENEWALAMOUNT = @TOTALRENEWALAMOUNT + @RENEWALAMOUNT;
    set @ORGANIZATIONTOTALRENEWALAMOUNT = @ORGANIZATIONTOTALRENEWALAMOUNT + @ORGANIZATIONRENEWALAMOUNT;

    fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
  end;

  close RECORDSOURCECURSOR;
  deallocate RECORDSOURCECURSOR;

  -- return the values

  select
    @TOTALRENEWALS as [RENEWALS],
    @TOTALUPGRADES as [UPGRADES],
    @TOTALDOWNGRADES as [DOWNGRADES],
    @TOTALRENEWALAMOUNT as [TOTALRENEWALAMOUNT],
    @ORGANIZATIONTOTALRENEWALAMOUNT as [ORGANIZATIONTOTALRENEWALAMOUNT];

  return 0;