USP_REPORT_RECONCILEDEPOSITS_MAIN_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_MAIN_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_MAIN] @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'set nocount on;
            /*I couldn''t get it to group over the subselect amount column, which was returning duplicate data. So I am now inserting into a temp table and
              group over the results from that temp table (which doesn''t require the use of a subselect) */

            declare @RESULTS table
            (
                TRANSACTIONID uniqueidentifier, BATCHNUMBER nvarchar(100), CONSTITUENTNAME varchar(300), CONSTITUENTLOOKUPID nvarchar(100), PAYMENTMETHODCODE tinyint, 
                PAYMENTMETHOD nvarchar(100), AMOUNT money, CHECKNUMBER nvarchar(20), CHECKDATE char(8), 
                CARDHOLDERNAME nvarchar(100), CREDITCARDPARTIALNUMBER nvarchar(10), CREDITTYPE nvarchar(100), DATE datetime,
                CONSTITUENTKEYNAME nvarchar(100), CONSTITUENTFIRSTNAME nvarchar(100), CONSTITUENTMIDDLENAME nvarchar(100)
            );

            exec dbo.USP_GET_KEY_ACCESS;

            insert into @RESULTS
                select 
                    [TOPREVENUE].TRANSACTIONID,
                    (select top 1 BATCHNUMBER from dbo.REVENUE where TRANSACTIONID = [TOPREVENUE].TRANSACTIONID) as [BATCHNUMBER],
                    CONSTITUENT.NAME,
                    CONSTITUENT.LOOKUPID,
                    (select top 1 PAYMENTMETHODCODE from dbo.REVENUE where TRANSACTIONID = [TOPREVENUE].TRANSACTIONID) as [PAYMENTMETHODCODE],
                    (select top 1 PAYMENTMETHOD from dbo.REVENUE where TRANSACTIONID = [TOPREVENUE].TRANSACTIONID) as [PAYMENTMETHOD],
                    (select sum(R.AMOUNT) from dbo.REVENUE R where R.TRANSACTIONID = [TOPREVENUE].TRANSACTIONID) as [AMOUNT],
                    [CHECK].CHECKNUMBER,
                    [CHECK].CHECKDATE,
                    [CC].CARDHOLDERNAME,
                    coalesce(convert(nvarchar(10), DecryptByKey([CC].CREDITCARDPARTIALNUMBER)), ''''),
                    [CCTYPE].DESCRIPTION as [CREDITTYPE],
                    (select top 1 DATE from dbo.REVENUE where TRANSACTIONID = [TOPREVENUE].TRANSACTIONID) as [DATE],
                    CONSTITUENT.KEYNAME,
                    CONSTITUENT.FIRSTNAME,
                    CONSTITUENT.MIDDLENAME
                from dbo.REVENUE as [TOPREVENUE]
                 inner join dbo.' + @DBOBJECTNAME + ' as [IDSET_181b45a6_8771_428d_82d7_0b999d41bdc2] on [TOPREVENUE].[TRANSACTIONID] = [IDSET_181b45a6_8771_428d_82d7_0b999d41bdc2].[ID] 
                inner join dbo.CONSTITUENT on CONSTITUENT.ID = [TOPREVENUE].CONSTITUENTID
                left join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] on [CHECK].ID = [TOPREVENUE].ID
                left join dbo.CREDITCARDPAYMENTMETHODDETAIL as [CC] on [CC].ID = [TOPREVENUE].ID
                left join dbo.CREDITTYPECODE as [CCTYPE] on [CCTYPE].ID = [CC].CREDITTYPECODEID
                where PAYMENTMETHODCODE in (0, 1, 2, 3) --Limit to Cash, Check, Credit Card, and Direct Debit

                      and [TOPREVENUE].TYPECODE in (0, 4, 5, 6, 8) --Limit to payments

                      and (dbo.UFN_DATE_GETEARLIESTTIME([TOPREVENUE].DATE) = dbo.UFN_DATE_GETEARLIESTTIME(@REVENUEDATE) or @REVENUEDATE is null)
                      and (dbo.UFN_DATE_GETEARLIESTTIME([TOPREVENUE].POSTDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@POSTDATE) or @POSTDATE is null)
            close symmetric key sym_BBInfinity;

            select 
                TRANSACTIONID as [ID], 
                BATCHNUMBER, 
                CONSTITUENTNAME as [NAME], 
                CONSTITUENTLOOKUPID as [LOOKUPID], 
                PAYMENTMETHODCODE, 
                PAYMENTMETHOD, 
                AMOUNT,
                CHECKNUMBER, 
                CHECKDATE, 
                CARDHOLDERNAME, 
                CREDITCARDPARTIALNUMBER, 
                CREDITTYPE, 
                DATE
            from @RESULTS as [RESULTS]
            group by TRANSACTIONID, BATCHNUMBER, CONSTITUENTNAME, CONSTITUENTLOOKUPID, PAYMENTMETHODCODE, PAYMENTMETHOD, CHECKNUMBER, CHECKDATE, 
                     CARDHOLDERNAME, CREDITCARDPARTIALNUMBER, CREDITTYPE, DATE, CONSTITUENTKEYNAME, CONSTITUENTFIRSTNAME, CONSTITUENTMIDDLENAME, DATE, AMOUNT
            order by CONSTITUENTKEYNAME, CONSTITUENTFIRSTNAME, CONSTITUENTMIDDLENAME, DATE, AMOUNT';

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

  return @r;