USP_REPORT_RECONCILEDEPOSITS_GROUPEDTOTALS

            CREATE procedure dbo.USP_REPORT_RECONCILEDEPOSITS_GROUPEDTOTALS
            (
                @DATETOUSE int = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @REVENUETRANSACTIONQUERY uniqueidentifier = null,
                @CONSTITUENTID uniqueidentifier = null,
                @REPORTUSERID nvarchar(128) = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            with execute as owner
            as
            set nocount on;

                declare @CURRENTAPPUSERID uniqueidentifier;
                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;

                set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                declare @SQLTOEXEC nvarchar(max);

                declare @DBOBJECTNAME nvarchar(128);
                declare @DBOBJECTTYPE smallint;

                if @REVENUETRANSACTIONQUERY is not null begin
                    if not exists(select ID from dbo.IDSETREGISTER where ID = @REVENUETRANSACTIONQUERY) raiserror('ID set does not exist in the database.', 15, 1);
                    select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @REVENUETRANSACTIONQUERY;
                    if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
                    else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @REVENUETRANSACTIONQUERY) + ''')';
                end

                set @SQLTOEXEC = 
                    ';with REVENUE_CTE as
                    (
                        select 
                            [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD],
                            [REVENUE].[ID],
                            [REVENUE].[AMOUNT],
                            CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                            CURRENCYPROPERTIES.CURRENCYSYMBOL,
                            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                            CURRENCYPROPERTIES.DECIMALDIGITS
                        from dbo.REVENUE with (nolock)
                        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUE.BASECURRENCYID) CURRENCYPROPERTIES' + nchar(13);

                if @REVENUETRANSACTIONQUERY is not null
                    set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVENUE.[ID] = SELECTION.[ID]' + nchar(13);

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'where PAYMENTMETHODCODE in (0, 1, 2, 3) --Limit to Cash, Check, Credit Card, and Direct Debit

                          and REVENUE.TRANSACTIONTYPECODE = 0 --Limit to payment

                          and ' + nchar(13);

                if @DATETOUSE = 0
                    set @SQLTOEXEC = @SQLTOEXEC + 
                        ' REVENUE.DATE between @STARTDATE and @ENDDATE ' + nchar(13);
                else
                    set @SQLTOEXEC = @SQLTOEXEC + 
                        ' REVENUE.POSTDATE between @STARTDATE and @ENDDATE ' + nchar(13);

                if @CONSTITUENTID is not null
                    set @SQLTOEXEC = @SQLTOEXEC + 'and REVENUE.CONSTITUENTID = @CONSTITUENTID' + nchar(13);

                if @ISADMIN = 0 
                begin 
                    if @APPUSER_IN_NONRACROLE = 0
                        set @SQLTOEXEC = @SQLTOEXEC + '
                          and (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REVENUE.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)' + nchar(13);

                    set @SQLTOEXEC = @SQLTOEXEC + '
                      and exists (select 1 from dbo.REVENUESPLIT where REVENUEID = [REVENUE].ID 
                            -- Check site security

                            and exists
                            (
                                select HASPERMISSION
                                from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) REVSITES
                                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''9A014198-B7EB-4A06-8FDC-951207AE6D03'', REVSITES.SITEID)
                            ))' + nchar(13);
                end    


                set @SQLTOEXEC +=     '
                    union all

                    --refunds

                    select
                        [CREDITPAYMENT].[PAYMENTMETHOD],
                        [CREDIT].[ID],
                        -1 * [CREDITPAYMENT].[AMOUNT],
                        [CURRENCYPROPERTIES].[ISO4217] [ISOCURRENCYCODE],
                        [CURRENCYPROPERTIES].[CURRENCYSYMBOL],
                        [CURRENCYPROPERTIES].[SYMBOLDISPLAYSETTINGCODE] [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                        [CURRENCYPROPERTIES].[DECIMALDIGITS]
                    from dbo.[CREDIT]
                    inner join dbo.[CREDITPAYMENT] on [CREDIT].[ID] = [CREDITPAYMENT].[CREDITID]
                    left join dbo.[REVENUESPLIT] on [REVENUESPLIT].[ID] = [CREDITPAYMENT].[REVENUESPLITID]
                    inner join dbo.[REVENUE] on ([REVENUE].[ID] = [CREDITPAYMENT].[REVENUEID] or [REVENUE].[ID] = [REVENUESPLIT].[REVENUEID])
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES([REVENUE].[BASECURRENCYID]) [CURRENCYPROPERTIES]' + nchar(13);

                if @DATETOUSE = 0
                    set @SQLTOEXEC +=
                        'where [CREDIT].[DATEADDED] between @STARTDATE and @ENDDATE ' + nchar(13);
                else
                    set @SQLTOEXEC +=
                        '    left join dbo.[CREDITGLDISTRIBUTION] on [CREDITGLDISTRIBUTION].[CREDITPAYMENTID] = [CREDITPAYMENT].[ID]
                            left join dbo.[GLTRANSACTION] on [CREDITGLDISTRIBUTION].[GLTRANSACTIONID] = [GLTRANSACTION].[ID]
                            where [GLTRANSACTION].[POSTDATE] between @STARTDATE and @ENDDATE ' + nchar(13);

                set @SQLTOEXEC += 
                    'and [CREDITPAYMENT].[PAYMENTMETHODCODE] in (0, 1, 2, 3) --Limit to Cash, Check, Credit Card, and Direct Debit

                    ' + nchar(13);

                if @CONSTITUENTID is not null
                    set @SQLTOEXEC += 'and [CREDIT].[CONSTITUENTID] = @CONSTITUENTID' + nchar(13);


                if @APPUSER_IN_NONRACROLE = 0 and @ISADMIN = 0
                    set @SQLTOEXEC += '
                      and (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, [CREDIT].[CONSTITUENTID], @APPUSER_IN_NOSECGROUPROLE) = 1)' + nchar(13);

                set @SQLTOEXEC +=
                    'group by CREDITPAYMENT.PAYMENTMETHOD, CREDIT.ID, CREDITPAYMENT.AMOUNT, [CURRENCYPROPERTIES].[ISO4217],
                    [CURRENCYPROPERTIES].[CURRENCYSYMBOL], [CURRENCYPROPERTIES].[SYMBOLDISPLAYSETTINGCODE], [CURRENCYPROPERTIES].[DECIMALDIGITS]'

                set @SQLTOEXEC +=     ')

                    select 
                        ''Total '' + lower([PAYMENTMETHOD]) + '': '' as [LABEL],
                        count([ID]) as [COUNT],
                        sum([AMOUNT]) as [AMOUNT],
                        [ISOCURRENCYCODE],
                        [CURRENCYSYMBOL],
                        [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                        [DECIMALDIGITS]
                    from REVENUE_CTE' + nchar(13);

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'group by [PAYMENTMETHOD], [ISOCURRENCYCODE], [CURRENCYSYMBOL],
                                            [CURRENCYSYMBOLDISPLAYSETTINGCODE], [DECIMALDIGITS]
                    order by PAYMENTMETHOD'

        print @SQLTOEXEC;

                exec sp_executesql @SQLTOEXEC, 
                    N'@DATETOUSE int, @STARTDATE datetime, @ENDDATE datetime, @CONSTITUENTID uniqueidentifier, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier',
                    @DATETOUSE=@DATETOUSE, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @CONSTITUENTID=@CONSTITUENTID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID;