USP_REPORT_DEPOSITREPORT_SUMMARY

        create procedure dbo.USP_REPORT_DEPOSITREPORT_SUMMARY
            (
                @DATETOUSE int = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @BANKACCOUNTDEPOSITQUERY uniqueidentifier = null,
                @DEPOSITID uniqueidentifier = null,
                @REPORTUSERID 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_GETFROMLOGIN(@REPORTUSERID);
                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 @BANKACCOUNTDEPOSITQUERY is not null begin
                    if not exists(select ID from dbo.IDSETREGISTER where ID = @BANKACCOUNTDEPOSITQUERY) raiserror('ID set does not exist in the database.', 15, 1);
                    select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @BANKACCOUNTDEPOSITQUERY;
                    if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
                    else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @BANKACCOUNTDEPOSITQUERY) + ''')';
                end

                set @SQLTOEXEC = 
                    'select sum(REVENUE.AMOUNT) as [AMOUNT],
                        ''Total''  + ISNULL(MISCELLANEOUSPAYMENT.PAYMENTMETHOD, REVENUEPAYMENTMETHOD.PAYMENTMETHOD) AS [PAYMENTMETHOD]
                    from 
                        dbo.REVENUE with (nolock)
                        inner join dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
                        left outer join dbo.MISCELLANEOUSPAYMENT on REVENUE.ID = MISCELLANEOUSPAYMENT.ID
                        left outer join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
                        left outer join REVENUEREFERENCE on REVENUE.ID = REVENUEREFERENCE.ID
                        left outer join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID ' + nchar(13);


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

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'where ((NOT MISCELLANEOUSPAYMENT.ID is null) or REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE IN (0,1,2,9,10)) 
                          and REVENUE.TRANSACTIONTYPECODE IN (0,10) and 
                          (
                            (@DATETOUSE = 0 and BANKACCOUNTTRANSACTION.TRANSACTIONDATE between @STARTDATE and @ENDDATE) or
                            (@DATETOUSE = 1 and REVENUE.POSTDATE between @STARTDATE and @ENDDATE)
                          )' 

                          --and (@ISADMIN = 1 or

                            --    @APPUSER_IN_NONRACROLE = 1 or

                            --    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REVENUE.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)'


                set @SQLTOEXEC = @SQLTOEXEC + 
                    'group by ISNULL(MISCELLANEOUSPAYMENT.PAYMENTMETHOD, REVENUEPAYMENTMETHOD.PAYMENTMETHOD), BANKACCOUNTDEPOSITPAYMENT.depositid
                    having (BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = @DEPOSITID) '
                      --and 

                    --(@ISADMIN = 1 or

                    --@APPUSER_IN_NONRACROLE = 1 or

                    --dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REVENUE.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)'



                /*set @SQLTOEXEC = 
                    ' union all select sum(REVENUE.AMOUNT) as [AMOUNT],
                        ''Total ''  + ISNULL(MISCELLANEOUSPAYMENT.PAYMENTMETHOD, REVENUEPAYMENTMETHOD.PAYMENTMETHOD) AS [PAYMENTMETHOD]
                    from 
                        dbo.REVENUE with (nolock)
                        inner join dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
                        left outer join dbo.MISCELLANEOUSPAYMENT on REVENUE.ID = MISCELLANEOUSPAYMENT.ID
                        left outer join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
                        left outer join REVENUEREFERENCE on REVENUE.ID = REVENUEREFERENCE.ID
                        left outer join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID ' + nchar(13);


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

                set @SQLTOEXEC = @SQLTOEXEC + 
                    'where ((NOT MISCELLANEOUSPAYMENT.ID is null) or REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE IN (0,1,2,9,10)) 
                          and REVENUE.TRANSACTIONTYPECODE IN (0,10) and 
                          (
                            (@DATETOUSE = 0 and BANKACCOUNTTRANSACTION.TRANSACTIONDATE between @STARTDATE and @ENDDATE) or
                            (@DATETOUSE = 1 and REVENUE.POSTDATE between @STARTDATE and @ENDDATE)
                          )' 
                          --and (@ISADMIN = 1 or
                            --    @APPUSER_IN_NONRACROLE = 1 or
                            --    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REVENUE.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)'
                */


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