USP_REPORT_APPEALPROFILE_REVENUESUMMARY

CREATE procedure dbo.[USP_REPORT_APPEALPROFILE_REVENUESUMMARY]
(
  @APPEALID uniqueidentifier = null,
  @SHOW bit = null,
  @CURRENCYCODE tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @REPORTUSERID nvarchar(128) = null,
  @ALTREPORTUSERID nvarchar(128) = null
)
as
  begin
    set nocount on;

    declare @CURRENCYID uniqueidentifier;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @CURRENCYISO nvarchar(3);
    declare @CURRENCYDECIMALDIGITS tinyint;
    declare @CURRENCYSYMBOL nvarchar(5);
    declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
    declare @CURRENCYROUNDINGTYPECODE tinyint;
    declare @ORGANIZATIONAMOUNTORIGINCODE tinyint;

    declare @REVENUECOUNT integer;
    declare @DONORCOUNT integer;
    declare @GOAL money;
    declare @REVENUE as money;

    begin try
      if @SHOW = 1
        begin
          -- NOTE: Be sure to update AppealProfileReportSectionsHaveData.View.xml if you modify

          -- the joins/constraints of the below query.


          exec dbo.[USP_REPORT_APPEALPROFILE_REVENUEPARAMETERS]
            @APPEALID,
            @REPORTUSERID,
            @ALTREPORTUSERID,
            @CURRENCYCODE,
            @CURRENTAPPUSERID output,
            @CURRENCYID output,
            @ORGANIZATIONCURRENCYID output,
            @CURRENCYISO output,
            @CURRENCYDECIMALDIGITS output,
            @CURRENCYSYMBOL output,
            @CURRENCYSYMBOLDISPLAYSETTINGCODE output,
            @CURRENCYROUNDINGTYPECODE output,
            @ORGANIZATIONAMOUNTORIGINCODE output;

          select
            @GOAL = sum(distinct [GOALINCURRENCY]) -- I've no idea why this sum(distinct is here...I'll bet it's copy-pasted...

          from dbo.[UFN_APPEAL_GETGOALINCURRENCY_BULK2](@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE, 1)
          where [ID] = @APPEALID;

          select
            @REVENUECOUNT = count(distinct [REVENUEID]),
            @DONORCOUNT = count(distinct [CONSTITUENTID]),
            @REVENUE = sum([AMOUNT])
          from dbo.[UFN_REPORT_APPEALPROFILE_REVENUE] (
            @CURRENTAPPUSERID,
            @CURRENCYCODE,
            @CURRENCYID,
            @ORGANIZATIONCURRENCYID,
            @CURRENCYISO,
            @CURRENCYDECIMALDIGITS,
            @CURRENCYSYMBOL,
            @CURRENCYSYMBOLDISPLAYSETTINGCODE,
            @CURRENCYROUNDINGTYPECODE,
            @ORGANIZATIONAMOUNTORIGINCODE) as [REVENUE]
          where
            [REVENUE].[APPEALID] = @APPEALID
            and [REVENUE].[INCLUDEINSUMMARY] = 1;

          set @REVENUECOUNT = isnull(@REVENUECOUNT, 0);
          set @GOAL = isnull(@GOAL, 0);
          set @REVENUE = isnull(@REVENUE, 0);

          select
            @REVENUE as [TOTALREVENUE],
            case when @GOAL = 0 then 0 else @REVENUE / @GOAL end as [PERCENT],
            @REVENUECOUNT as [TOTALGIFTS],
            isnull(@DONORCOUNT, 0) as [TOTALDONORS],
            case when @REVENUECOUNT = 0 then 0 else @REVENUE / @REVENUECOUNT end as [AVERAGEGIFT],
            @CURRENCYID as [CURRENCYID],
            @CURRENCYISO as [CURRENCYISO],
            @CURRENCYDECIMALDIGITS as [CURRENCYDECIMALDIGITS],
            @CURRENCYSYMBOL as [CURRENCYSYMBOL],
            @CURRENCYSYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE]
        end
    end try

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

    return 0;
  end