USP_DATALIST_CONSTITUENTPROFILEDASHBOARDGROUPMEMBERGIVINGSUMMARY

                    CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDGROUPMEMBERGIVINGSUMMARY
                    (
                        @CONSTITUENTID uniqueidentifier,
                        @ISVISIBLE bit = 1,
                        @CURRENTAPPUSERID uniqueidentifier,
                        @SELECTEDCURRENCYID uniqueidentifier = null
                    )
                    as
                        set nocount on;

                        declare @ISADMIN bit;
                        declare @APPUSER_IN_NONRACROLE bit;
                        declare @APPUSER_IN_NOSECGROUPROLE bit;
                        declare @APPUSER_IN_NONSITEROLE bit;
                        declare @APPUSER_IN_NOSITEROLE bit;

                        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);
                        set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
                        set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

                        if @ISVISIBLE = 1
                        begin
                            select
                                NF.NAME,
                                REVENUESUM,
                                REVENUECOUNT,
                                DATEFROM,
                                DATETO
                            from
                            -- Using a CTE so that all the values in the select list aren't returned.  C.ID, C.KEYNAME, and C.FIRSTNAME

                            -- are included in the CTE for grouping/sorting purposes but don't need to be returned.

                            (
                                select
                                    C.ID,
                                    C.KEYNAME,
                                    C.FIRSTNAME,
                                    cast(sum(cast(dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(R.ID, @SELECTEDCURRENCYID) as decimal(20,5)) - cast(coalesce(WO.AMOUNT, 0) as decimal(20,5))) as money) as REVENUESUM,
                                    count(R.AMOUNT) as REVENUECOUNT,
                                    GMDR.DATEFROM,
                                    GMDR.DATETO
                                from dbo.GROUPMEMBER GM
                                inner join dbo.CONSTITUENT C on GM.MEMBERID = C.ID
                                left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                                left outer join dbo.REVENUE R on 
                                    C.ID = R.CONSTITUENTID and
                                    (GMDR.DATEFROM is null or GMDR.DATEFROM <= R.DATE) and
                                    (GMDR.DATETO is null or GMDR.DATETO >= R.DATE)
                                left outer join
                                    (select
                                        WRITEOFF.REVENUEID,
                                        sum(coalesce(dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(INSTALLMENTSPLITWRITEOFF.ID, @SELECTEDCURRENCYID),0)) AMOUNT
                                    from
                                        dbo.WRITEOFF
                                    left join
                                        dbo.INSTALLMENTSPLITWRITEOFF
                                    on
                                        INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                                    group by
                                        WRITEOFF.REVENUEID) WO on WO.REVENUEID = R.ID
                                where
                                    GM.GROUPID = @CONSTITUENTID and
                                    (R.TRANSACTIONTYPECODE = 1 or --Pledge

                                    ((R.TRANSACTIONTYPECODE = 0 and exists(select top 1 ID from dbo.REVENUESPLIT where REVENUEID = R.ID and APPLICATIONCODE in (0, 3, 4))))) and --Payment (Gift or Recurring gift payment)

                                    (@ISADMIN = 1 or 
                                        (@APPUSER_IN_NONRACROLE = 1 or
                                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, GM.MEMBERID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                        and
                                        (@APPUSER_IN_NONSITEROLE = 1 or
                                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, GM.MEMBERID, @APPUSER_IN_NOSITEROLE) = 1)
                                    )
                                group by C.ID, C.KEYNAME, C.FIRSTNAME, GMDR.DATEFROM, GMDR.DATETO
                            ) as GS
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GS.ID) NF
                            order by GS.KEYNAME, GS.FIRSTNAME
                        end