USP_KPI_REVENUE_SELECTIONMONEYTOTAL_INTERNAL_2_WITHIDSET

/*
Generated by Blackbaud Application Framework
Date: 3/19/2013 1:39:21 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
*/
create procedure dbo.USP_KPI_REVENUE_SELECTIONMONEYTOTAL_INTERNAL_2_WITHIDSET (@IDSETID uniqueidentifier = null, @ASOFDATE datetime, @TOTAL money output, @AGGREGATENAME nvarchar(10), @CURRENCYID uniqueidentifier)
with execute as owner
as
  set nocount on;

  declare @r int;

  if @IDSETID is null
    begin
      exec @r = dbo.[USP_KPI_REVENUE_SELECTIONMONEYTOTAL_INTERNAL_2] @ASOFDATE, @TOTAL output, @AGGREGATENAME, @CURRENCYID;
    end
  else
    begin
      if not exists(select [ID] from dbo.[IDSETREGISTER] where [ID] = @IDSETID)
        raiserror('ID set does not exist in the database.', 15, 1);

      declare @DBOBJECTNAME nvarchar(max);
      declare @DBOBJECTTYPE smallint;
      declare @SQLTOEXEC nvarchar(max);
      declare @USEASOFDATE bit;

      select
        @USEASOFDATE = (case [DBOBJECTNAMEWITHASOFDATE] when '' then 0 else 1 end),
        @DBOBJECTNAME = (case [DBOBJECTNAMEWITHASOFDATE] when '' then [DBOBJECTNAME] else [DBOBJECTNAMEWITHASOFDATE] end),
        @DBOBJECTTYPE = [OBJECTTYPE]
      from dbo.[IDSETREGISTER]
      where [ID] = @IDSETID;

      if left(@DBOBJECTNAME, 1) <> '['
        set @DBOBJECTNAME = '[' + @DBOBJECTNAME + ']';

      if @DBOBJECTTYPE = 1
        begin
          set @DBOBJECTNAME = @DBOBJECTNAME + '(';

          if @USEASOFDATE = 1
            set @DBOBJECTNAME = @DBOBJECTNAME + '@ASOFDATE';

          set @DBOBJECTNAME = @DBOBJECTNAME + ')';
        end
      else
        begin
          if @DBOBJECTTYPE = 2
            set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @IDSETID) + ''')';
        end

      set @SQLTOEXEC = cast(N'set nocount on;

            declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
            declare @DECIMALDIGITS tinyint;
            declare @ROUNDINGTYPECODE tinyint;

            if @CURRENCYID is null
                set @CURRENCYID = @ORGANIZATIONCURRENCYID;

            select 
                @DECIMALDIGITS = DECIMALDIGITS,
                @ROUNDINGTYPECODE = ROUNDINGTYPECODE
            from 
                dbo.CURRENCY
            where
                CURRENCY.ID = @CURRENCYID;

            if @AGGREGATENAME=''SUM''
                select @TOTAL = coalesce(SUM(REVENUE.AMOUNTINCURRENCY), 0) from dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) AS REVENUE            
                 inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_920e4c4b_6ad4_4c9f_b679_6d706d5ff6e4] on [REVENUE].[ID] = [IDSET_920e4c4b_6ad4_4c9f_b679_6d706d5ff6e4].[ID] 
                where REVENUE.DATE <= @ASOFDATE

            else if @AGGREGATENAME=''AVG''
                select @TOTAL = coalesce(AVG(REVENUE.AMOUNTINCURRENCY), 0) from dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) AS REVENUE                
                 inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_920e4c4b_6ad4_4c9f_b679_6d706d5ff6e4] on [REVENUE].[ID] = [IDSET_920e4c4b_6ad4_4c9f_b679_6d706d5ff6e4].[ID] 
                where REVENUE.DATE <= @ASOFDATE

            else if @AGGREGATENAME=''MIN''
                select @TOTAL = coalesce(MIN(REVENUE.AMOUNTINCURRENCY), 0) from dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) AS REVENUE                
                 inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_920e4c4b_6ad4_4c9f_b679_6d706d5ff6e4] on [REVENUE].[ID] = [IDSET_920e4c4b_6ad4_4c9f_b679_6d706d5ff6e4].[ID] 
                where REVENUE.DATE <= @ASOFDATE

            else if @AGGREGATENAME=''MAX''
                select @TOTAL = coalesce(MAX(REVENUE.AMOUNTINCURRENCY), 0) from dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) AS REVENUE                
                 inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_920e4c4b_6ad4_4c9f_b679_6d706d5ff6e4] on [REVENUE].[ID] = [IDSET_920e4c4b_6ad4_4c9f_b679_6d706d5ff6e4].[ID] 
                where REVENUE.DATE <= @ASOFDATE
            else 
                BEGIN
                    SET @TOTAL =0;
                    RAISERROR(''Valid values for @AGGREGATENAME are SUM, AVG, MIN, and MAX.'',16,10);
                END' as nvarchar(max));

      exec @r = sp_executesql @SQLTOEXEC, N'@ASOFDATE datetime, @TOTAL money output, @AGGREGATENAME nvarchar(10), @CURRENCYID uniqueidentifier', @ASOFDATE = @ASOFDATE, @TOTAL = @TOTAL output, @AGGREGATENAME = @AGGREGATENAME, @CURRENCYID = @CURRENCYID;
    end

  return @r;