USP_REPORT_RECONCILEDEPOSITS_GROUPEDTOTALS_WITHIDSET

/*
Generated by Blackbaud Application Framework
Date: 12/17/2008 2:13:29 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=1.7.1271.0, Culture=neutral, PublicKeyToken=null
*/
create procedure dbo.USP_REPORT_RECONCILEDEPOSITS_GROUPEDTOTALS_WITHIDSET (@IDSETID uniqueidentifier = null, @REVENUEDATE datetime, @POSTDATE datetime)
with execute as owner
as
  set nocount on;

  declare @r int;

  if @IDSETID is null
    begin
      exec @r = dbo.[USP_REPORT_RECONCILEDEPOSITS_GROUPEDTOTALS] @REVENUEDATE, @POSTDATE;
    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);

      select
        @DBOBJECTNAME = [DBOBJECTNAME],
        @DBOBJECTTYPE = [OBJECTTYPE]
      from dbo.[IDSETREGISTER]
      where [ID] = @IDSETID;

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

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

      set @SQLTOEXEC = N'select 
                ''Total '' + lower(PAYMENTMETHOD) + '': '' as [LABEL],
                count(distinct REVENUE.TRANSACTIONID) as [COUNT],
                sum(REVENUE.AMOUNT) as [AMOUNT]
            from dbo.REVENUE
             inner join dbo.' + @DBOBJECTNAME + ' as [IDSET_a93d79ce_e303_4f28_949b_1bb7edcea9bf] on [REVENUE].[TRANSACTIONID] = [IDSET_a93d79ce_e303_4f28_949b_1bb7edcea9bf].[ID]             
            where PAYMENTMETHODCODE in (0, 1, 2, 3) --Limit to Cash, Check, Credit Card, and Direct Debit

                  and REVENUE.TYPECODE in (0, 4, 5, 6, 8) --Limit to payment

                  and (dbo.UFN_DATE_GETEARLIESTTIME(REVENUE.DATE) = dbo.UFN_DATE_GETEARLIESTTIME(@REVENUEDATE) or @REVENUEDATE is null)
                  and (dbo.UFN_DATE_GETEARLIESTTIME(REVENUE.POSTDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@POSTDATE) or @POSTDATE is null)
            group by PAYMENTMETHOD
            order by PAYMENTMETHOD';

      exec @r = sp_executesql @SQLTOEXEC, N'@REVENUEDATE datetime, @POSTDATE datetime', @REVENUEDATE = @REVENUEDATE, @POSTDATE = @POSTDATE;
    end

  return @r;