USP_REPORT_GROUPFUNDRAISINGANDGIVING_GOALS

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

    @ALTREPORTUSERID nvarchar(128) = null
)
as
begin

    declare @CURRENTAPPUSERID uniqueidentifier = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

    declare @SELECTEDCURRENCYID uniqueidentifier;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE 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 top 2 AMOUNT,
            case when ISFUNDRAISINGGOAL = 0 then PROGRESS else dbo.UFN_REVENUESOLICITOR_GETSUM_INCURRENCY_BULK(@GROUPID,STARTDATE,GOALDATE,@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) end PROGRESS, 
            ISFUNDRAISINGGOAL,
            dbo.UFN_GROUPGOALUNITCODE_GETDESCRIPTION(GROUPGOALUNITCODEID) as GROUPGOALUNITCODE,
            GOALDATE,
            CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
            CURRENCYPROPERTIES.CURRENCYSYMBOL,
            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
            CURRENCYPROPERTIES.DECIMALDIGITS
    from dbo.UFN_GROUPGOAL_DATALIST_WITHCURRENCY(@GROUPID, null, @SELECTEDCURRENCYID)
    outer apply    dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
    order by 
        DATESORT desc,
        Case DATESORT When 1 Then GOALDATE When 0 Then null Else GetDate() - GOALDATE End asc

end