USP_REPORT_APPEALPROFILE_REVENUESUMMARYUK

CREATE procedure dbo.USP_REPORT_APPEALPROFILE_REVENUESUMMARYUK
(
    @APPEALID uniqueidentifier = null,
    @SHOW bit = null,
    @CURRENCYCODE tinyint = null
)
as
set nocount on;

    declare @UKINSTALLED bit;
    set @UKINSTALLED = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45FD-8F54-21FE9654EE2D');

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

    if coalesce(@CURRENCYCODE, 1) = 1
    begin
        set @CURRENCYCODE = 1;
        select
            @ORGANIZATIONCURRENCYID = CURRENCY.ID,
            @SELECTEDCURRENCYID = CURRENCY.ID,
            @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
            @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
        from
            dbo.CURRENCY
        where
            CURRENCY.ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    end
    else begin
        set @CURRENCYCODE = 0;
        select
            @SELECTEDCURRENCYID = CURRENCY.ID,
            @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
            @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
        from
            dbo.APPEAL
            inner join dbo.CURRENCY on APPEAL.BASECURRENCYID = CURRENCY.ID
        where
            APPEAL.ID = @APPEALID;

        set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    end

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

begin try
    if @SHOW = 1 and @UKINSTALLED = 1
    begin
        declare @REGULARGIVERREVENUE as money;
        declare @AVERAGECASHGIFT as money;
        declare @AVERAGEREGULARGIFT as money;
        declare @TOTALREGULARGIFTS as int;
        declare @GROSSAMOUNT as money;

        declare @REVENUEAMOUNTSINCURRENCY table (
            REVENUEID uniqueidentifier,
            REVENUESPLITID uniqueidentifier,
            CONSTITUENTID uniqueidentifier,
            AMOUNTINCURRENCY money,
            APPEALID uniqueidentifier,
            TRANSACTIONTYPECODE tinyint,
            APPLICATIONCODE tinyint,
            TYPECODE tinyint
        )

        insert into @REVENUEAMOUNTSINCURRENCY
            (REVENUEID,REVENUESPLITID,CONSTITUENTID,AMOUNTINCURRENCY,APPEALID,TRANSACTIONTYPECODE,APPLICATIONCODE,TYPECODE)
            select 
                REVENUEID,
                ID as REVENUESPLITID,
                CONSTITUENTID,
                AMOUNTINCURRENCY,
                APPEALID,
                TRANSACTIONTYPECODE,
                APPLICATIONCODE,
                TYPECODE
            from
                dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
                                @SELECTEDCURRENCYID, 
                                @ORGANIZATIONCURRENCYID, 
                                @DECIMALDIGITS, 
                                @ROUNDINGTYPECODE)

        select 
            @REGULARGIVERREVENUE = coalesce(sum(REVENUEAMOUNTSINCURRENCY.AMOUNTINCURRENCY), 0)
        from 
            @REVENUEAMOUNTSINCURRENCY as REVENUEAMOUNTSINCURRENCY
        where 
            REVENUEAMOUNTSINCURRENCY.APPEALID = @APPEALID and
            REVENUEAMOUNTSINCURRENCY.TRANSACTIONTYPECODE = 0 and
            REVENUEAMOUNTSINCURRENCY.APPLICATIONCODE = 3 -- Recurring gift


        select 
            @AVERAGECASHGIFT = coalesce(avg(REVENUEAMOUNTSINCURRENCY.AMOUNTINCURRENCY), 0)
        from
            @REVENUEAMOUNTSINCURRENCY as REVENUEAMOUNTSINCURRENCY
        where
            REVENUEAMOUNTSINCURRENCY.APPEALID = @APPEALID and
            (
                REVENUEAMOUNTSINCURRENCY.TRANSACTIONTYPECODE = 1 
                or
                (REVENUEAMOUNTSINCURRENCY.TRANSACTIONTYPECODE = 0 and (REVENUEAMOUNTSINCURRENCY.APPLICATIONCODE = 0 or (REVENUEAMOUNTSINCURRENCY.APPLICATIONCODE = 1 and REVENUEAMOUNTSINCURRENCY.TYPECODE = 0)))
            )

        select
            @AVERAGEREGULARGIFT = coalesce(avg(REVENUEAMOUNTSINCURRENCY.AMOUNTINCURRENCY), 0)
        from
            @REVENUEAMOUNTSINCURRENCY as REVENUEAMOUNTSINCURRENCY
        where
            REVENUEAMOUNTSINCURRENCY.APPEALID = @APPEALID and
            (REVENUEAMOUNTSINCURRENCY.TRANSACTIONTYPECODE = 0 and REVENUEAMOUNTSINCURRENCY.APPLICATIONCODE = 3)

        select 
            @TOTALREGULARGIFTS = count(distinct REVENUEAMOUNTSINCURRENCY.REVENUEID)
        from 
            @REVENUEAMOUNTSINCURRENCY as REVENUEAMOUNTSINCURRENCY
        where
            REVENUEAMOUNTSINCURRENCY.APPEALID = @APPEALID and 
            (REVENUEAMOUNTSINCURRENCY.TRANSACTIONTYPECODE = 0 and REVENUEAMOUNTSINCURRENCY.APPLICATIONCODE = 3)

        set @GROSSAMOUNT = 
        (
            select
                coalesce(sum(REVENUEAMOUNTSINCURRENCY.AMOUNTINCURRENCY), 0) + 
                    coalesce(sum(REVENUESPLITGIFTAIDAMOUNTINCURRENCY.TAXCLAIMAMOUNTINCURRENCY), 0)
            from
                @REVENUEAMOUNTSINCURRENCY as REVENUEAMOUNTSINCURRENCY
                left join dbo.UFN_REVENUESPLITGIFTAID_GETAMOUNTSINCURRENCY_BULK
                (
                    @SELECTEDCURRENCYID,
                    @ORGANIZATIONCURRENCYID, 
                    @DECIMALDIGITS, 
                    @ROUNDINGTYPECODE
                ) as REVENUESPLITGIFTAIDAMOUNTINCURRENCY on REVENUEAMOUNTSINCURRENCY.REVENUESPLITID = REVENUESPLITGIFTAIDAMOUNTINCURRENCY.ID
                left outer join dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) as [REVENUESPLITGIFTAID] on [REVENUESPLITGIFTAID].[ID] = REVENUESPLITGIFTAIDAMOUNTINCURRENCY.[ID]
            where
                REVENUEAMOUNTSINCURRENCY.APPEALID = @APPEALID and
                (
                    REVENUEAMOUNTSINCURRENCY.TRANSACTIONTYPECODE = 1 
                    or
                    (REVENUEAMOUNTSINCURRENCY.TRANSACTIONTYPECODE = 0 and (REVENUEAMOUNTSINCURRENCY.APPLICATIONCODE in (0, 3) or (REVENUEAMOUNTSINCURRENCY.APPLICATIONCODE = 1 and REVENUEAMOUNTSINCURRENCY.TYPECODE = 0)))
                )
        )
        /* Subtract write-offs, just like above:*/
        -
        (
            select 
                coalesce(sum(INSTALLMENTSPLITWRITEOFFAMOUNTINCURRENCY.AMOUNTINCURRENCY), 0)
            from 
                dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTSINCURRENCY_BULK(
                    @SELECTEDCURRENCYID,
                    @ORGANIZATIONCURRENCYID, 
                    @DECIMALDIGITS, 
                    @ROUNDINGTYPECODE,
          @ORIGINCODE,
          @CURRENCYCODE
                ) as INSTALLMENTSPLITWRITEOFFAMOUNTINCURRENCY
            inner join 
                dbo.WRITEOFF on WRITEOFF.ID = INSTALLMENTSPLITWRITEOFFAMOUNTINCURRENCY.WRITEOFFID
            inner join 
                dbo.REVENUE on REVENUE.ID = WRITEOFF.REVENUEID
            where 
                REVENUE.APPEALID = @APPEALID 
                and REVENUE.TRANSACTIONTYPECODE in (1,7)
        )

        select
            @REGULARGIVERREVENUE as [REGULARGIVERREVENUE],
            @AVERAGECASHGIFT as [AVERAGECASHGIFT],
            @AVERAGEREGULARGIFT as [AVERAGEREGULARGIFT],
            @TOTALREGULARGIFTS as [TOTALREGULARGIFTS],
            @GROSSAMOUNT as [GROSSAMOUNT],
            CURRENCYPROPERTIES.ID as CURRENCYID,
            CURRENCYPROPERTIES.ISO4217 as CURRENCYISO,
            CURRENCYPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
            CURRENCYPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
        from dbo.APPEAL
            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
        where (APPEAL.ID = @APPEALID);
    end
end try

begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0;