USP_MKTSEGMENTATION_GETINDIRECTRESPONSECOUNTS

CREATE procedure dbo.[USP_MKTSEGMENTATION_GETINDIRECTRESPONSECOUNTS]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  declare @GIFTIDSETTABLE nvarchar(128);
  declare @REVENUESEGMENTTABLE nvarchar(128);
  declare @SQL nvarchar(max);
  declare @PARAMDEF nvarchar(max);

  declare @INDIRECTRESPONDERS int;
  declare @INDIRECTRESPONSES int;
  declare @INDIRECTGIFTAMOUNT money;
  declare @INDIRECTORGANIZATIONGIFTAMOUNT money;

  declare @TOTALINDIRECTRESPONDERS int = 0;
  declare @TOTALINDIRECTRESPONSES int = 0;
  declare @TOTALINDIRECTGIFTAMOUNT money = 0;
  declare @TOTALINDIRECTORGANIZATIONGIFTAMOUNT money = 0;


  begin try
    set @PARAMDEF = '@SEGMENTATIONID uniqueidentifier, ' +
                    '@INDIRECTRESPONDERS int output, ' +
                    '@INDIRECTRESPONSES int output, ' +
                    '@INDIRECTGIFTAMOUNT money output, ' +
                    '@INDIRECTORGANIZATIONGIFTAMOUNT money output';


    declare RECORDSOURCECURSOR cursor local fast_forward for
      select
        dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID]),
        dbo.[UFN_REVENUESEGMENT_MAKETABLENAME]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID])
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [DRS]
      inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [DRS].[QUERYVIEWCATALOGID] and [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID

    open RECORDSOURCECURSOR;
    fetch next from RECORDSOURCECURSOR into @GIFTIDSETTABLE, @REVENUESEGMENTTABLE;

    while (@@FETCH_STATUS = 0)
    begin
      -- build the SQL to count all revenue in the normal gifts table that is not matched to a segment in the REVENUESEGMENT table...

      set @SQL = 'select' + char(13) +
                 '  @INDIRECTRESPONDERS = count(distinct([DONORID])),' + char(13) +
                 '  @INDIRECTRESPONSES = count([ID]),' + char(13) +
                 '  @INDIRECTGIFTAMOUNT = isnull(sum([AMOUNT]), 0),' + char(13) +
                 '  @INDIRECTORGANIZATIONGIFTAMOUNT = isnull(sum([ORGANIZATIONAMOUNT]), 0)' + char(13) +
                 'from dbo.' + @GIFTIDSETTABLE + ' as [GIFTIDSET]' + char(13) +
                 'where not exists(select * from dbo.[' + @REVENUESEGMENTTABLE + '] where [REVENUEID] = [GIFTIDSET].[ID] and [SEGMENTID] in (select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID));';

      -- execute the SQL to get the counts/amounts for this record source

      exec sp_executesql @SQL, @PARAMDEF,
        @SEGMENTATIONID = @SEGMENTATIONID,
        @INDIRECTRESPONDERS = @INDIRECTRESPONDERS output,
        @INDIRECTRESPONSES = @INDIRECTRESPONSES output,
        @INDIRECTGIFTAMOUNT = @INDIRECTGIFTAMOUNT output,
        @INDIRECTORGANIZATIONGIFTAMOUNT = @INDIRECTORGANIZATIONGIFTAMOUNT output;

      -- keep the running totals for all record sources

      set @TOTALINDIRECTRESPONDERS += @INDIRECTRESPONDERS;
      set @TOTALINDIRECTRESPONSES += @INDIRECTRESPONSES;
      set @TOTALINDIRECTGIFTAMOUNT += @INDIRECTGIFTAMOUNT;
      set @TOTALINDIRECTORGANIZATIONGIFTAMOUNT += @INDIRECTORGANIZATIONGIFTAMOUNT;

      fetch next from RECORDSOURCECURSOR into @GIFTIDSETTABLE, @REVENUESEGMENTTABLE;
    end;

    close RECORDSOURCECURSOR;
    deallocate RECORDSOURCECURSOR;


    -- return the values

    select 
      @TOTALINDIRECTRESPONDERS as [INDIRECTRESPONDERS],
      @TOTALINDIRECTRESPONSES as [INDIRECTRESPONSES],
      @TOTALINDIRECTGIFTAMOUNT as [INDIRECTTOTALGIFTAMOUNT],
      (case when @TOTALINDIRECTRESPONSES > 0 then @TOTALINDIRECTGIFTAMOUNT / cast(@TOTALINDIRECTRESPONSES as money) else 0 end) as [INDIRECTAVERAGEGIFTAMOUNT],
      (case when @TOTALINDIRECTRESPONSES > 0 then @TOTALINDIRECTORGANIZATIONGIFTAMOUNT / cast(@TOTALINDIRECTRESPONSES as money) else 0 end) as [ORGANIZATIONINDIRECTAVERAGEGIFTAMOUNT],
      @TOTALINDIRECTORGANIZATIONGIFTAMOUNT as [ORGANIZATIONTOTALINDIRECTGIFTAMOUNT];
  end try

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

  return 0;