USP_KPI_REVENUE_SELECTIONMONEYTOTAL_INTERNAL_WITHIDSET

/*
Generated by Blackbaud Application Framework
Date: 4/21/2018 10:21:11 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.177.0, Culture=neutral, PublicKeyToken=null
*/
CREATE procedure dbo.USP_KPI_REVENUE_SELECTIONMONEYTOTAL_INTERNAL_WITHIDSET (@IDSETID uniqueidentifier = null, @ASOFDATE datetime, @TOTAL money output, @AGGREGATENAME nvarchar(10))
with execute as owner
as
  set nocount on;

  declare @r int;

  if @IDSETID is null
    begin
      exec @r = dbo.[USP_KPI_REVENUE_SELECTIONMONEYTOTAL_INTERNAL] @ASOFDATE, @TOTAL output, @AGGREGATENAME;
    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;

            if @AGGREGATENAME=''SUM''
                select @TOTAL = coalesce(SUM(FT.BASEAMOUNT), 0) from dbo.FINANCIALTRANSACTION AS FT        
                 inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_f87b453e_3ad1_43e0_9154_1eb976e3bbd4] on [R].[ID] = [IDSET_f87b453e_3ad1_43e0_9154_1eb976e3bbd4].[ID] 
                where FT.DATE <= @ASOFDATE
                and FT.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and FT.DELETEDON is null

            else if @AGGREGATENAME=''AVG''
                select @TOTAL = coalesce(AVG(FT.BASEAMOUNT), 0) from dbo.FINANCIALTRANSACTION AS FT                
                 inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_f87b453e_3ad1_43e0_9154_1eb976e3bbd4] on [R].[ID] = [IDSET_f87b453e_3ad1_43e0_9154_1eb976e3bbd4].[ID] 
                where FT.DATE <= @ASOFDATE
                and FT.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and FT.DELETEDON is null

            else if @AGGREGATENAME=''MIN''
                select @TOTAL = coalesce(MIN(FT.BASEAMOUNT), 0) from dbo.FINANCIALTRANSACTION AS FT                
                 inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_f87b453e_3ad1_43e0_9154_1eb976e3bbd4] on [R].[ID] = [IDSET_f87b453e_3ad1_43e0_9154_1eb976e3bbd4].[ID] 
                where FT.DATE <= @ASOFDATE
                and FT.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and FT.DELETEDON is null

            else if @AGGREGATENAME=''MAX''
                select @TOTAL = coalesce(MAX(FT.BASEAMOUNT), 0) from dbo.FINANCIALTRANSACTION AS FT                
                 inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_f87b453e_3ad1_43e0_9154_1eb976e3bbd4] on [R].[ID] = [IDSET_f87b453e_3ad1_43e0_9154_1eb976e3bbd4].[ID] 
                where FT.DATE <= @ASOFDATE
                and FT.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and FT.DELETEDON is null
            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)', @ASOFDATE = @ASOFDATE, @TOTAL = @TOTAL output, @AGGREGATENAME = @AGGREGATENAME;
    end

  return @r;