USP_REPORT_APPEALPERIODCOMPARISON

CREATE procedure dbo.USP_REPORT_APPEALPERIODCOMPARISON
(
    @APPEAL1ID uniqueidentifier,
    @APPEAL1YEAR smallint,
    @APPEAL2ID uniqueidentifier,
    @APPEAL2YEAR smallint,
    @PERIODTYPE tinyint,
    @YEARTYPE tinyint,
    @CURRENCYCODE tinyint = null
)
as

    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @GRAPHCURRENCYID uniqueidentifier;
    declare @SELECTEDCURRENCYID uniqueidentifier;
    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE tinyint;
    declare @SELECTEDCURRENCY2ID uniqueidentifier;
    declare @DECIMALDIGITS2 tinyint;
    declare @ROUNDINGTYPECODE2 tinyint;

    if coalesce(@CURRENCYCODE, 1) = 1
    begin
        set @CURRENCYCODE = 1;

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

        --Find base currency of the first appeal

        select
            @GRAPHCURRENCYID = BASECURRENCYID,
            @SELECTEDCURRENCYID = BASECURRENCYID,
            @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
            @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
        from
            dbo.APPEAL
        inner join 
            dbo.CURRENCY on APPEAL.BASECURRENCYID = CURRENCY.ID
        where APPEAL.ID = @APPEAL1ID

        --Find base of the second appeal

        select 
            @SELECTEDCURRENCY2ID = BASECURRENCYID,
            @DECIMALDIGITS2 = CURRENCY.DECIMALDIGITS,
            @ROUNDINGTYPECODE2 = CURRENCY.ROUNDINGTYPECODE
        from
            dbo.APPEAL
        inner join 
            dbo.CURRENCY on APPEAL.BASECURRENCYID = CURRENCY.ID
        where APPEAL.ID = @APPEAL2ID

        set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        --If appeals' currencies are different set the graph currency to the organizational currency

        if @GRAPHCURRENCYID <> @SELECTEDCURRENCY2ID
                set @GRAPHCURRENCYID =  @ORGANIZATIONCURRENCYID;
    end

    declare @YEARSTART table
    (
        APPEALID uniqueidentifier,
        YEARSTART datetime
    );

    -- using a results table to later insert dummy rows for {appeal, period} combinations without data

    declare @RESULTS table
    (        
        REVENUECATEGORY nvarchar(25),
        APPEALID uniqueidentifier,
        NAME nvarchar(100),
        PERIOD int,
        CONSTITUENTCOUNT int,
        REVENUECOUNT int,
        REVENUEAMOUNT money,
        REVENUEAMOUNTINGRAPHCURRENCY money,
        TOTALCONSTITUENTCOUNT int,
        TOTALGIFTCOUNT int
    );

    declare @STARTMONTH smallint;

    if @YEARTYPE = 0
    begin
        set @STARTMONTH = 1;
    end
    else if @YEARTYPE = 1
    begin
        declare @FISCALYEARFINALMONTH tinyint;
        select @FISCALYEARFINALMONTH = FISCALYEARFINALMONTH from dbo.INSTALLATIONINFO;

        declare @FISCALYEARFIRSTMONTH tinyint = (@FISCALYEARFINALMONTH + 1);
        if @FISCALYEARFIRSTMONTH = 13
            set @FISCALYEARFIRSTMONTH = 1;

        set @STARTMONTH = @FISCALYEARFIRSTMONTH;
    end

    insert into @YEARSTART
        select @APPEAL1ID, cast(cast(@APPEAL1YEAR * 10000 + @STARTMONTH * 100 + 1 as varchar) as datetime) union all
        select @APPEAL2ID, cast(cast(@APPEAL2YEAR * 10000 + @STARTMONTH * 100 + 1 as varchar) as datetime);

    -- Cash gifts --> Pledges and donations --> LocalType = 0

    -- Regular gifts --> Recurring gift payments --> LocalType = 1

    declare @GIFTS table (        
        REVENUEID uniqueidentifier,
        APPEALID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        AMOUNT money,
        AMOUNTINGRAPHCURRENCY money,
        PERIOD int,
        LOCALTYPE int
    )

    insert into @GIFTS (REVENUEID, APPEALID, CONSTITUENTID, AMOUNT, AMOUNTINGRAPHCURRENCY, PERIOD, LOCALTYPE)
        select
            REVENUE.ID as REVENUEID,
            REVENUE.APPEALID,
            REVENUE.CONSTITUENTID,
            case 
                when (REVENUE.APPEALID = @APPEAL1ID) then sum(RS_A1.AMOUNTINCURRENCY)
                else sum(RS_A2.AMOUNTINCURRENCY)
            end as [AMOUNT],
            sum(REVENUESPLIT.AMOUNTINCURRENCY) [AMOUNTINGRAPHCURRENCY],
            case @PERIODTYPE
                when 0 then datediff(week, APPEALYEARSTART.YEARSTART, REVENUE.DATE) + 1
                when 1 then datediff(month, APPEALYEARSTART.YEARSTART, REVENUE.DATE) + 1
                when 2 then datediff(quarter, APPEALYEARSTART.YEARSTART, REVENUE.DATE) + 1
            end    as PERIOD,
            case 
                when REVENUE.TRANSACTIONTYPECODE = 1 then 0
                when REVENUE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE = 0 or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)) then 0
                when REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 3 then 1
            end as LOCALTYPE
        from
            dbo.REVENUE
        left outer join
            @YEARSTART [APPEALYEARSTART] on APPEALYEARSTART.APPEALID = REVENUE.APPEALID
        inner join
            dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@GRAPHCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
        inner join 
            dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as RS_A1 on REVENUESPLIT.ID = RS_A1.ID
        inner join 
            dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCY2ID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS2, @ROUNDINGTYPECODE2) as RS_A2 on REVENUESPLIT.ID = RS_A2.ID
        left join
            dbo.APPEAL on APPEAL.ID = REVENUE.APPEALID
        where
            (REVENUE.APPEALID = @APPEAL1ID or REVENUE.APPEALID = @APPEAL2ID)
        and
            DATEDIFF(month, APPEALYEARSTART.YEARSTART, REVENUE.DATE) <= 12
        and
            (
                (REVENUE.TRANSACTIONTYPECODE = 1)
                or 
                (REVENUE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE = 0 or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)))
                or
                (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 3)
            )
        group by
            REVENUE.ID, REVENUE.APPEALID, REVENUE.CONSTITUENTID, REVENUE.DATE, REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.APPLICATIONCODE, REVENUESPLIT.TYPECODE, APPEALYEARSTART.YEARSTART;

    insert into @RESULTS (REVENUECATEGORY, APPEALID, NAME, PERIOD, CONSTITUENTCOUNT, REVENUECOUNT, REVENUEAMOUNT, REVENUEAMOUNTINGRAPHCURRENCY)
        select
            'Cash gifts',
            gifts.APPEALID,
            dbo.UFN_APPEAL_GETNAME(gifts.APPEALID) [NAME],
            gifts.PERIOD,
            coalesce(COUNT(distinct gifts.CONSTITUENTID), 0) [CONSTITUENTCOUNT],
            coalesce(COUNT(distinct gifts.REVENUEID), 0) [REVENUECOUNT],
            coalesce(SUM(gifts.AMOUNT), 0) [REVENUEAMOUNT],
            coalesce(SUM(gifts.AMOUNTINGRAPHCURRENCY), 0) [REVENUEAMOUNTINGRAPHCURRENCY]
        from @GIFTS gifts
        where LOCALTYPE = 0
        group by gifts.APPEALID, gifts.PERIOD;

    insert into @RESULTS (REVENUECATEGORY, APPEALID, NAME, PERIOD, CONSTITUENTCOUNT, REVENUECOUNT, REVENUEAMOUNT, REVENUEAMOUNTINGRAPHCURRENCY)
        select
            'Regular gifts',
            gifts.APPEALID,
            dbo.UFN_APPEAL_GETNAME(gifts.APPEALID) [NAME],
            gifts.PERIOD,
            coalesce(COUNT(distinct gifts.CONSTITUENTID), 0) [CONSTITUENTCOUNT],
            coalesce(COUNT(gifts.REVENUEID), 0) [REVENUECOUNT],
            coalesce(SUM(gifts.AMOUNT), 0) [REVENUEAMOUNT],
            coalesce(SUM(gifts.AMOUNTINGRAPHCURRENCY), 0) [REVENUEAMOUNTINGRAPHCURRENCY]
        from @GIFTS gifts
        where gifts.LOCALTYPE = 1
        group by gifts.APPEALID, gifts.PERIOD;

    -- Insert dummy rows for {appeal, period} combinations which do not have revenue.

    --    @PERIODUPPERBOUND is how many periods appear in a year given @PERIODTYPE

    --    PERIODS_CTE is a list of all of those periods

    --    APPEALPERIODS_CTE is a list of all possible {appeal, period} combinations

    declare @PERIODUPPERBOUND int = 
        case @PERIODTYPE 
            when 0 then 52
            when 1 then 12
            when 2 then 4
        end;

    with PERIODS_CTE as
    (
        select 1 as PERIOD
        union all
        select PERIOD + 1 from PERIODS_CTE where PERIOD < @PERIODUPPERBOUND
    ),
    APPEALPERIODS_CTE as
    (
        select 
            APPEALID, 
            PERIOD 
        from 
            PERIODS_CTE 
        cross join 
            (select @APPEAL1ID [APPEALID] union select @APPEAL2ID [APPEALID]) as APPEALS
    )
    insert into @RESULTS
    (
        REVENUECATEGORY,
        APPEALID,
        NAME,
        PERIOD
    )
    select
        'Cash gifts',
        APPEALPERIODS_CTE.APPEALID,
        (select APPEAL.NAME from dbo.APPEAL where APPEAL.ID = APPEALPERIODS_CTE.APPEALID),
        APPEALPERIODS_CTE.PERIOD
    from
        APPEALPERIODS_CTE
    except
        (select REVENUECATEGORY, APPEALID, NAME, PERIOD from @RESULTS)

    union all

    select
        'Regular gifts',
        APPEALPERIODS_CTE.APPEALID,
        (select APPEAL.NAME from dbo.APPEAL where APPEAL.ID = APPEALPERIODS_CTE.APPEALID),
        APPEALPERIODS_CTE.PERIOD
    from
        APPEALPERIODS_CTE
    except
        (select REVENUECATEGORY, APPEALID, NAME, PERIOD from @RESULTS);

    update 
        @RESULTS
    set
        CONSTITUENTCOUNT = coalesce(CONSTITUENTCOUNT, 0),
        REVENUECOUNT = coalesce(REVENUECOUNT, 0),
        REVENUEAMOUNT = coalesce(REVENUEAMOUNT, 0),
        REVENUEAMOUNTINGRAPHCURRENCY = coalesce(REVENUEAMOUNTINGRAPHCURRENCY, 0),
        TOTALCONSTITUENTCOUNT = coalesce(TOTALCONSTITUENTCOUNT, 0),
        TOTALGIFTCOUNT = coalesce(TOTALGIFTCOUNT, 0);    

    -- Count the total number of constituents and gifts per period and appeal

    --    Only setting total counts on 'Cash gifts' records to support running totals in the report

    --    If the value is set for both and use running totals is selected, the values will be doubled

    update @RESULTS set
        TOTALCONSTITUENTCOUNT = (
                select 
                    coalesce(count(distinct CONSTITUENTID), 0)
                from @GIFTS gifts
                where gifts.APPEALID = results.APPEALID
                    and gifts.PERIOD = results.PERIOD
            ),
        TOTALGIFTCOUNT = (
                select 
                    coalesce(count(distinct REVENUEID), 0)
                from @GIFTS gifts
                where gifts.APPEALID = results.APPEALID
                    and gifts.PERIOD = results.PERIOD
            )
    from @RESULTS results
    where results.REVENUECATEGORY = 'Cash gifts';

    select 
        REVENUECATEGORY,
        APPEALID,
        r.NAME,
        PERIOD,
        CONSTITUENTCOUNT,
        REVENUECOUNT,
        REVENUEAMOUNT,
        case
            when APPEALID = @APPEAL1ID then 1
            when APPEALID = @APPEAL2ID then 2
        end as APPEALORDER,
        TOTALCONSTITUENTCOUNT,
        TOTALGIFTCOUNT,
        CURRENCYPROPERTIES.ID as CURRENCYID,
        CURRENCYPROPERTIES.ISO4217 as CURRENCYISO,
        CURRENCYPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
        CURRENCYPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
        GRAPHCURRENCYPROPERTIES.ID as GRAPHCURRENCYID,
        GRAPHCURRENCYPROPERTIES.ISO4217 as GRAPHCURRENCYISO,
        GRAPHCURRENCYPROPERTIES.DECIMALDIGITS as GRAPHCURRENCYDECIMALDIGITS,
        GRAPHCURRENCYPROPERTIES.CURRENCYSYMBOL as GRAPHCURRENCYSYMBOL,
        GRAPHCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as GRAPHCURRENCYSYMBOLDISPLAYSETTINGCODE,
        REVENUEAMOUNTINGRAPHCURRENCY
    from 
        @RESULTS r
        left join dbo.APPEAL on APPEAL.ID = r.APPEALID
        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case when @CURRENCYCODE = 1 then @ORGANIZATIONCURRENCYID else APPEAL.BASECURRENCYID end) CURRENCYPROPERTIES
        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case when @CURRENCYCODE = 1 then @ORGANIZATIONCURRENCYID else @GRAPHCURRENCYID end) GRAPHCURRENCYPROPERTIES
    where
        (r.PERIOD > 0 and r.PERIOD <= @PERIODUPPERBOUND)
    order by 
        APPEALORDER, APPEALID, r.NAME, PERIOD;