USP_REPORT_REVENUEDETAILGROUPTOTALS

            CREATE procedure dbo.USP_REPORT_REVENUEDETAILGROUPTOTALS
            (
                @STARTDATE datetime = null, 
                @ENDDATE datetime = null,
                @REVENUETRANSACTIONQUERY uniqueidentifier = null,
                @CONSTITUENTID uniqueidentifier = null,
                @APPEALID uniqueidentifier = null,
                @DESIGNATIONID uniqueidentifier = null,
                @INCLUDEGROUPS bit = 0,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @REPORTUSERID nvarchar(128) = null,
                @CURRENCYCODE tinyint = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            with execute as owner
            as
            set nocount on;

            if @CURRENTAPPUSERID is null
                set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

            declare @NOW datetime = getdate();

            declare @STARTDATEEARLIEST datetime = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
            declare @ENDDATELATEST datetime = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

            declare @ISADMIN bit = 0; 
            if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                set @ISADMIN = 1;

            declare @HASSITES bit = 0;
            if exists(select 1 from dbo.SITE)
                set @HASSITES = 1;


            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

            declare @ORIGINCODE tinyint;
            select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0) from dbo.MULTICURRENCYCONFIGURATION;

            declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
            declare @DECIMALDIGITS tinyint;
            declare @ROUNDINGTYPECODE tinyint;
            select
                @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
            from dbo.CURRENCY where CURRENCY.ID = @ORGANIZATIONCURRENCYID;

            set @SQLTOEXEC = 
                '
                with DONATIONREFUNDED_CTE as (
                   select ORDERSPLIT.ID as REVENUESPLITID, CREDITLINEITEM.BASEAMOUNT as DONATIONREFUNDED
                    from dbo.FINANCIALTRANSACTIONLINEITEM CREDITLINEITEM
                        inner join dbo.CREDITITEM_EXT on CREDITLINEITEM.ID = CREDITITEM_EXT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM ORDERLINEITEM on ORDERLINEITEM.ID = CREDITLINEITEM.SOURCELINEITEMID
                        inner join dbo.REVENUESPLIT_EXT ORDERSPLIT on ORDERLINEITEM.ID = ORDERSPLIT.ID
                    where CREDITITEM_EXT.TYPECODE = 2 and ORDERSPLIT.TYPECODE = 0 AND ORDERSPLIT.APPLICATIONCODE = 0
                ),
                ORDERPAYMENT_CTE as (
                    select ORDERPAYMENTLINEITEM.SOURCELINEITEMID as ID, coalesce(sum(ORDERPAYMENTLINEITEM.BASEAMOUNT), 0) as BASEAMOUNT, coalesce(sum(ORDERPAYMENTLINEITEM.TRANSACTIONAMOUNT), 0) as TRANSACTIONAMOUNT
                        from dbo.FINANCIALTRANSACTIONLINEITEM ORDERPAYMENTLINEITEM
                        inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = ORDERPAYMENTLINEITEM.FINANCIALTRANSACTIONID 
                    where ORDERPAYMENTLINEITEM.SOURCELINEITEMID is not null
                    group by ORDERPAYMENTLINEITEM.SOURCELINEITEMID
                ),
                ORDERITEMPAYMENT_CTE as (
                    select CONTRIBUTEDMEMBERSHIPLINEITEM.ID, isnull(ORDERPAYMENT_CTE.BASEAMOUNT, 0) as BASEAMOUNT, isnull(ORDERPAYMENT_CTE.TRANSACTIONAMOUNT, 0) as TRANSACTIONAMOUNT
                        from dbo.FINANCIALTRANSACTIONLINEITEM CONTRIBUTEDMEMBERSHIPLINEITEM
                        inner join dbo.REVENUESPLIT_EXT CONTRIBUTEDMEMBERSHIPSPLIT on CONTRIBUTEDMEMBERSHIPSPLIT.ID = CONTRIBUTEDMEMBERSHIPLINEITEM.ID
                      inner join dbo.SALESORDER on CONTRIBUTEDMEMBERSHIPLINEITEM.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
                        left join ORDERPAYMENT_CTE on CONTRIBUTEDMEMBERSHIPLINEITEM.ID = ORDERPAYMENT_CTE.ID
                    where 
                        ((CONTRIBUTEDMEMBERSHIPSPLIT.TYPECODE = 0 
                            and CONTRIBUTEDMEMBERSHIPSPLIT.APPLICATIONCODE = 0 
                            and CONTRIBUTEDMEMBERSHIPLINEITEM.SOURCELINEITEMID is not null) 
                        or 
                        (CONTRIBUTEDMEMBERSHIPSPLIT.TYPECODE = 2 
                            and CONTRIBUTEDMEMBERSHIPSPLIT.APPLICATIONCODE = 5 
                            and exists (select * from dbo.FINANCIALTRANSACTIONLINEITEM DONATIONLINEITEM
                                            inner join dbo.REVENUESPLIT_EXT DONATIONSPLIT on DONATIONLINEITEM.ID = DONATIONSPLIT.ID
                                            where DONATIONSPLIT.TYPECODE = 0 and DONATIONSPLIT.APPLICATIONCODE = 0 and DONATIONLINEITEM.SOURCELINEITEMID = CONTRIBUTEDMEMBERSHIPSPLIT.ID
                            )))
                )' + nchar(13)

            set @SQLTOEXEC = @SQLTOEXEC + 
                '
                select sum(case when REVENUE.TYPECODE = 5 and ORDERPAYMENTFORMEMBERSHIP.ID is not null  then ORDERPAYMENTFORMEMBERSHIP.BASEAMOUNT - coalesce(DONATIONREFUNDED_CTE.DONATIONREFUNDED, 0)
                                when REVENUE.TYPECODE in (0,5,7) and ORDERPAYMENTFORMEMBERSHIP.ID is null then case @CURRENCYCODE when 0 then LI.BASEAMOUNT when 2 then LI.TRANSACTIONAMOUNT else LI.ORGAMOUNT end - coalesce(DONATIONREFUNDED_CTE.DONATIONREFUNDED, 0) 
                                else 0.00 end) as GIFTTOTAL,
                        sum(case when REVENUE.TYPECODE in (1,3,4,6,8)
                            then 
                             PLEDGESPLIT.AMOUNT
                            else 0.00 end) as PLEDGEBALANCETOTAL

                from dbo.FINANCIALTRANSACTION REVENUE ' + nchar(13)
                if @APPEALID is not null
                  set @SQLTOEXEC = @SQLTOEXEC + ' inner join dbo.REVENUE_EXT REX on REX.ID = REVENUE.ID ' + nchar(13)
                set @SQLTOEXEC = @SQLTOEXEC 
                + 'inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = REVENUE.ID
                inner join dbo.REVENUESPLIT_EXT REVENUESPLIT on REVENUESPLIT.ID = LI.ID
                inner join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
                left outer join DONATIONREFUNDED_CTE on DONATIONREFUNDED_CTE.REVENUESPLITID = REVENUESPLIT.ID
                 left outer join ORDERITEMPAYMENT_CTE as ORDERPAYMENTFORMEMBERSHIP on ORDERPAYMENTFORMEMBERSHIP.ID = REVENUESPLIT.ID

                 left outer join 
                (select P.ID,P.AMOUNT from dbo.FINANCIALTRANSACTION F 
                  inner join dbo.FINANCIALTRANSACTIONLINEITEM L on F.ID = L.FINANCIALTRANSACTIONID
                  inner join dbo.REVENUESPLIT_EXT R on R.ID = L.ID
                  inner join dbo.UFN_PLEDGE_CONVERTSPLITBALANCESBYPROPORTION_SPLIT
                  (
                      @ENDDATE,
                      0,
                      0,
                      @DECIMALDIGITS,
                      @STARTDATE,
                      @ENDDATE          
                  ) as  P on P.ID = L.ID 
                where L.TYPECODE = 0 and L.DELETEDON is null and F.TYPECODE in (1,3,4,6,8)
                  and F.CALCULATEDDATE between @STARTDATEEARLIEST and  @ENDDATELATEST
                  and (@DESIGNATIONID is null or R.DESIGNATIONID = @DESIGNATIONID)
                  and (@CONSTITUENTID is null or F.CONSTITUENTID = @CONSTITUENTID)
                  and exists (
                  select
                      MEMBERID 
                  from dbo.GROUPMEMBER GM
                  inner join dbo.GROUPDATA GD on GM.GROUPID = GD.ID
                  left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                  left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
                  where GM.MEMBERID = F.CONSTITUENTID ' + nchar(13)

             if @INCLUDEGROUPS = 1
                set @SQLTOEXEC = @SQLTOEXEC + ' and (GD.GROUPTYPECODE = 0 or GT.INCLUDEMEMBERGIVING = 1)))as PLEDGESPLIT on     PLEDGESPLIT.ID = LI.ID ' + nchar(13)
            else
                set @SQLTOEXEC = @SQLTOEXEC + ' and GD.GROUPTYPECODE = 0 )) as PLEDGESPLIT on     PLEDGESPLIT.ID = LI.ID  ' + 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 LI.TYPECODE != 1 and LI.DELETEDON is null and REVENUE.CALCULATEDDATE between @STARTDATEEARLIEST and  @ENDDATELATEST and
                    (REVENUE.TYPECODE in (1,3,4,6,7,8) or
                    (REVENUE.TYPECODE = 5 and REVENUESPLIT.APPLICATIONCODE = 0) or
                    (REVENUE.TYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0,2,3,4,6,7,8,12,13)) or
                    (REVENUE.TYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0))  ' + nchar(13)

             if @APPEALID is not null 
            set @SQLTOEXEC = @SQLTOEXEC + 'and (@APPEALID is null or REX.APPEALID = @APPEALID) ' + nchar(13)

             set @SQLTOEXEC = @SQLTOEXEC + ' and (@DESIGNATIONID is null or DESIGNATION.ID = @DESIGNATIONID)
                    and (@CONSTITUENTID is null or REVENUE.CONSTITUENTID = @CONSTITUENTID)
                    -- Make sure the constituent is a member of a group

                    and exists (
                        select
                            MEMBERID 
                        from dbo.GROUPMEMBER GM
                        inner join dbo.GROUPDATA GD on GM.GROUPID = GD.ID
                        left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                        left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
                        where 
                            GM.MEMBERID = REVENUE.CONSTITUENTID' + nchar(13)

            if @INCLUDEGROUPS = 1
                set @SQLTOEXEC = @SQLTOEXEC + ' and (GD.GROUPTYPECODE = 0 or GT.INCLUDEMEMBERGIVING = 1) -- Is either a household or a custom group with include group member giving turned on ' + nchar(13)

            else
                set @SQLTOEXEC = @SQLTOEXEC + ' and GD.GROUPTYPECODE = 0 -- Is a household ' + nchar(13)


            set @SQLTOEXEC = @SQLTOEXEC + 
                'and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= @CURRENTDATEEARLIESTTIME))
                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME))
                or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO >= @CURRENTDATEEARLIESTTIME))
                )' + nchar(13)

            if @ISADMIN != 1 and @HASSITES = 1
                set @SQLTOEXEC = @SQLTOEXEC +
                    'and exists (select 1 from dbo.REVENUESPLIT where REVENUEID = REVENUE.ID 
                                and exists
                                (
                                    select HASPERMISSION
                                    from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
                                    cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''8F250015-83FB-44d5-A467-E0A760256768'', REVSITES.SITEID)
                                ))
            ' + nchar(13)

            declare @CURRENTDATEEARLIESTTIME datetime;
            set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@NOW);

            exec sp_executesql @SQLTOEXEC, 
                    N'@STARTDATE datetime, @ENDDATE datetime, @APPEALID uniqueidentifier, @DESIGNATIONID uniqueidentifier, @CONSTITUENTID uniqueidentifier, @CURRENTDATEEARLIESTTIME datetime, @CURRENTAPPUSERID uniqueidentifier, @CURRENCYCODE tinyint, @CURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint, @ORIGINCODE tinyint, @NOW datetime, @STARTDATEEARLIEST datetime, @ENDDATELATEST datetime',
                    @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @APPEALID=@APPEALID, @DESIGNATIONID=@DESIGNATIONID, @CONSTITUENTID=@CONSTITUENTID, @CURRENTDATEEARLIESTTIME=@CURRENTDATEEARLIESTTIME, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @CURRENCYCODE=@CURRENCYCODE,
                    @CURRENCYID=@ORGANIZATIONCURRENCYID, @ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID, @DECIMALDIGITS=@DECIMALDIGITS, @ROUNDINGTYPECODE=@ROUNDINGTYPECODE, @ORIGINCODE = @ORIGINCODE, @NOW=@NOW, @STARTDATEEARLIEST=@STARTDATEEARLIEST, @ENDDATELATEST=@ENDDATELATEST;