USP_REPORT_GROUPFUNDRAISINGANDGIVING_GIVINGSUMMARY

CREATE procedure dbo.USP_REPORT_GROUPFUNDRAISINGANDGIVING_GIVINGSUMMARY
(
    @GROUPID uniqueidentifier,
    @REPORTUSERID nvarchar(128) = null,
    @CURRENCYCODE smallint = null, --3 = My base, (null, 1) = Organization

    @ALTREPORTUSERID nvarchar(128) = null
)
as
    set nocount on;

    declare @CURRENTAPPUSERID uniqueidentifier
    declare @ISADMIN bit;
    declare @APPUSER_IN_NONRACROLE bit;
    declare @APPUSER_IN_NOSECGROUPROLE bit;
    declare @APPUSER_IN_NONSITEROLE bit;
    declare @APPUSER_IN_NOSITEROLE 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);
    set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

    declare @SELECTEDCURRENCYID uniqueidentifier;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE tinyint;
    declare @ORIGINCODE tinyint;

    if @CURRENCYCODE = 3
    begin
        if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null
        begin                    
            select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID,
                   @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                   @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                from dbo.CURRENCYSET
                inner join dbo.CURRENCY on CURRENCYSET.BASECURRENCYID = CURRENCY.ID
                where
                    CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);

            set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
        end
        else
        begin
            select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID,
                   @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                   @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                from dbo.CURRENCYSET
                inner join dbo.CURRENCY on CURRENCYSET.BASECURRENCYID = CURRENCY.ID
                where
                    CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();

            set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
        end
    end
    else
        select
                @ORGANIZATIONCURRENCYID = CURRENCY.ID,
                @SELECTEDCURRENCYID = CURRENCY.ID,
                @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
            from
                dbo.CURRENCY
            where
                CURRENCY.ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

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

    select
        GS.NAME,
        CONSTITURL,
        KEYNAME,
        REVENUESUM,
        REVENUECOUNT,
        DATEFROM,
        DATETO,
        CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
        CURRENCYPROPERTIES.DECIMALDIGITS
    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
            'http://www.blackbaud.com/CONSTITID?CONSTITID=' + CONVERT(nvarchar(36),C.ID) as CONSTITURL,
            C.KEYNAME,
            C.FIRSTNAME,
            C.NAME,
            cast(sum(cast(R.AMOUNTINCURRENCY as decimal(20,5)) - cast(coalesce(WO.AMOUNT, 0) as decimal(20,5))) as money) as REVENUESUM,
            count(R.AMOUNTINCURRENCY) as REVENUECOUNT,
            GMDR.DATEFROM,
            GMDR.DATETO
        from dbo.UFN_GROUP_GETGROUPANDMEMBERS_ONELEVELDEEP(@GROUPID, 1) 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.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) 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 join
            dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = R.ID
        left join
            dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
        left outer join
            (select
                WRITEOFF.REVENUEID,
                sum(coalesce(INSTALLMENTSPLITWRITEOFF.AMOUNTINCURRENCY, 0)) AMOUNT
            from
                dbo.WRITEOFF
            left join
                dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE, @CURRENCYCODE) INSTALLMENTSPLITWRITEOFF
            on
                INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
            group by
                WRITEOFF.REVENUEID) WO on WO.REVENUEID = R.ID
        where
            (R.TRANSACTIONTYPECODE = 1 or --Pledge

            (R.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9)) or --Planned gift

            ((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, C.NAME, GMDR.DATEFROM, GMDR.DATETO
    ) as GS
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
    order by GS.KEYNAME, GS.FIRSTNAME, CURRENCYPROPERTIES.ISO4217, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, CURRENCYPROPERTIES.DECIMALDIGITS