USP_REPORT_REVENUEDYNAMICS

CREATE procedure dbo.USP_REPORT_REVENUEDYNAMICS
(
    @PREVIOUSFROMDATE datetime = null,
    @PREVIOUSTODATE datetime = null,
    @THISFROMDATE datetime = null,
    @THISTODATE datetime = null,
    @DESIGNATIONID uniqueidentifier = null,
    @CAMPAIGNID uniqueidentifier = null,
    @REPORTUSERID nvarchar(128) = null,
    @EXCLUDEGIFTSOVER money = 0,
    @CURRENCYCODE tinyint = null, --(null, 1) = Organization, 3 = My base

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

    set @EXCLUDEGIFTSOVER = coalesce(@EXCLUDEGIFTSOVER, 0);

    declare @CURRENTAPPUSERID uniqueidentifier;
    declare @ISADMIN bit;
    declare @APPUSER_IN_NONRACROLE bit;
    declare @APPUSER_IN_NOSECGROUPROLE 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);

    declare @CAMPAIGNHIERARCHYPATH hierarchyid;
    select 
        @CAMPAIGNHIERARCHYPATH=HIERARCHYPATH
    from dbo.CAMPAIGN
        where CAMPAIGN.ID = @CAMPAIGNID;

    declare @SELECTEDCURRENCYID uniqueidentifier;
    if coalesce(@CURRENCYCODE, 1) = 1
        set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    else if @CURRENCYCODE = 3
        set @SELECTEDCURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE tinyint;

  select 
    @DECIMALDIGITS = DECIMALDIGITS,
    @ROUNDINGTYPECODE = ROUNDINGTYPECODE
  from
    dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID);

    --JamesWill WI168303 2011-08-31 

    declare @USERREPORTSITE table 
    (
        SITEID uniqueidentifier
    );
    insert into @USERREPORTSITE(SITEID)
        select SITEID 
        from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, '2a1af012-2e35-4453-9b34-ea37569f52fc', 21);

    ;with REVENUE_CTE as
    (
        select
            REVENUE.ID,
            REVENUE.CONSTITUENTID,
            sum
                (
                    case --JamesWill WI168303 2011-08-31 The report doesn't actually care about amounts before @PREVIOUSFROMDATE. It just needs to know if they 

                         --existed or not. So don't waste time converting these records. 

                        when cast(REVENUE.DATE as datetime) < @PREVIOUSFROMDATE 
                            then 1 
                        when (@SELECTEDCURRENCYID is null or @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID) 
                            then REVENUESPLIT.ORGAMOUNT
                        when (@SELECTEDCURRENCYID = V.BASECURRENCYID) 
                            then REVENUESPLIT.BASEAMOUNT 
                        when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUESPLIT.ORGAMOUNT, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                        when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUESPLIT.ORGAMOUNT, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                        else
                            0
                    end
                 ) - case when cast(REVENUE.DATE as datetime) < @PREVIOUSFROMDATE then 0 else coalesce(sum([CREDITITEM].[TOTAL]),0) end [AMOUNT],
            cast(REVENUE.DATE as datetime) [DATE]
        from dbo.FINANCIALTRANSACTION REVENUE
        inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
        inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
        inner join dbo.UFN_SITEID_MAPFROM_REVENUESPLITID_BULK() as REVENUESITE on REVENUESITE.ID = REVENUESPLIT.ID
        outer apply
            ( 
                select 
                    0 RATE 
                where 
                    @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID 
                    or @SELECTEDCURRENCYID = V.BASECURRENCYID
                union all
                select
                    RATE
                from
                    dbo.CURRENCYEXCHANGERATE
                where
                    @SELECTEDCURRENCYID <> @ORGANIZATIONCURRENCYID 
                    and @SELECTEDCURRENCYID <> V.BASECURRENCYID
                    and @ORGANIZATIONCURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID
                    and @SELECTEDCURRENCYID = CURRENCYEXCHANGERATE.TOCURRENCYID
                    and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                    and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                    and dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
                    and dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
            ) LATESTORGANIZATIONEXCHANGERATE
        outer apply
            (
                select 
                    0 RATE 
                where 
                    @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID 
                    or @SELECTEDCURRENCYID = V.BASECURRENCYID
                union all
                select
                    RATE
                from
                    dbo.CURRENCYEXCHANGERATE
                where
                    @SELECTEDCURRENCYID <> @ORGANIZATIONCURRENCYID 
                    and @SELECTEDCURRENCYID <> V.BASECURRENCYID
                    and @SELECTEDCURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID
                    and @ORGANIZATIONCURRENCYID= CURRENCYEXCHANGERATE.TOCURRENCYID
                    and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                    and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                    and dateadd(ms, 86399996, REVENUE.DATE) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
                    and dateadd(ms, 86399996, REVENUE.DATE) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
            ) LATESTINVERSEORGANIZATIONEXCHANGERATE
        left join
            dbo.[SALESORDER] on [SALESORDER].[REVENUEID] = [REVENUE].[ID]
        left join
            dbo.[CREDIT] on [CREDIT].[SALESORDERID] = [SALESORDER].[ID]
        left join
            dbo.[CREDITITEM] on [CREDITITEM].[CREDITID] = [CREDIT].[ID]
        where REVENUE.DELETEDON is null and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1 and
            (
                (REVENUE.TYPECODE = 1) --Pledge

                or 
                (REVENUE.TYPECODE = 0 and (REVENUESPLIT_EXT.APPLICATIONCODE in (0, 4) or (REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT_EXT.TYPECODE = 0) or (REVENUESPLIT_EXT.APPLICATIONCODE = 3 and REVENUESPLIT_EXT.TYPECODE <> 2))) --Payment (Gift, Other, and Recurring Gift)

                or
                (REVENUE.TYPECODE = 7) --Auction donation

                or
                ([REVENUE].TYPECODE = 5 and REVENUESPLIT_EXT.[APPLICATIONCODE] = 0 and [REVENUE].[CONSTITUENTID] is not null)  -- Order Donation w/constituent

                or
                ([REVENUE].TYPECODE = 5 and REVENUESPLIT_EXT.[APPLICATIONCODE] = 1 and REVENUESPLIT_EXT.[DESIGNATIONID] is not null)
            )
        and 
            ( @ISADMIN = 1 or (
                  (
                          @APPUSER_IN_NONRACROLE = 1 or
                          dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REVENUE.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1
                  )
                and exists 
                    (
                        --JamesWill WI168303 2011-08-31 Check site security without using a scaler

                        select 1 from @USERREPORTSITE USERREPORTSITE
                        where USERREPORTSITE.SITEID = REVENUESITE.SITEID 
                        or (USERREPORTSITE.SITEID is null and REVENUESITE.SITEID is null)
                    )
          )

            )        
        and
            (
                (@DESIGNATIONID is null)
                or
                (REVENUESPLIT_EXT.DESIGNATIONID = @DESIGNATIONID)
            )
        and
            (
                (@CAMPAIGNID is null)
                or 
                exists (
                    select top(1)
                        REVENUESPLITCAMPAIGN.ID
                    from 
                        dbo.REVENUESPLITCAMPAIGN 
                    inner join 
                        CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                    where 
                        REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID 
                    and 
                        CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                )
            )
        and ([CREDITITEM].[TYPECODE] = 2 or [CREDITITEM].[TYPECODE] is null) --donation refunds

        group by
            REVENUE.ID, REVENUE.CONSTITUENTID, REVENUE.TYPECODE, cast(REVENUE.DATE as datetime)
    ),
    CONSTITUENTREVENUE_CTE as
    (
        select
            REVENUE_CTE.CONSTITUENTID,
            SUM(
                case
                    when (REVENUE_CTE.DATE < @PREVIOUSFROMDATE) then REVENUE_CTE.AMOUNT
                    else 0
                end        
            ) [ALLOTHERAMOUNT],
            SUM(
                case
                    when REVENUE_CTE.DATE between @PREVIOUSFROMDATE and @PREVIOUSTODATE then REVENUE_CTE.AMOUNT
                    else 0
                end
            ) [PREVIOUSPERIODAMOUNT],
            SUM(
                case 
                    when REVENUE_CTE.DATE between @THISFROMDATE and @THISTODATE then REVENUE_CTE.AMOUNT
                    else 0
                end
            ) [THISPERIODAMOUNT]
        from
            REVENUE_CTE
        where --JamesWill WI168303 2011-08-31 To avoid duplicating the currency conversion case statement from the REVENUE_CTE, I needed to bring 

              --this filter out of REVENUE_CTE and into CONSTITUENTREVENUE_CTE. This made a slight semantic change which I've documented in the bug.

            (@EXCLUDEGIFTSOVER = 0 or REVENUE_CTE.AMOUNT <= @EXCLUDEGIFTSOVER)
        group by
            REVENUE_CTE.CONSTITUENTID
    )
    select
        'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36),CONSTITUENTREVENUE_CTE.CONSTITUENTID) as CONSTITUENTID,
        NF.NAME,
        CONSTITUENTREVENUE_CTE.PREVIOUSPERIODAMOUNT,
        CONSTITUENTREVENUE_CTE.THISPERIODAMOUNT,
        case when ALLOTHERAMOUNT > 0 then 1.0 else 0.0 end as ALLOTHERAMOUNT, --JamesWill WI168303 2011-08-31 The report doesn't actually use this value and calculating it is very expensive. But don't return an apparently random value

        case
            when THISPERIODAMOUNT > 0 and PREVIOUSPERIODAMOUNT = 0 and ALLOTHERAMOUNT = 0 then 0  --New

            when THISPERIODAMOUNT > 0 and PREVIOUSPERIODAMOUNT <= 0 and ALLOTHERAMOUNT > 0 then 1  --Recapture

            when THISPERIODAMOUNT > PREVIOUSPERIODAMOUNT and PREVIOUSPERIODAMOUNT > 0 then 2  --Upgrade

            when THISPERIODAMOUNT = PREVIOUSPERIODAMOUNT and PREVIOUSPERIODAMOUNT > 0 then 3  --Same

            when THISPERIODAMOUNT < PREVIOUSPERIODAMOUNT and THISPERIODAMOUNT <> 0 then 4 --Downgrade

            when THISPERIODAMOUNT <= 0 and PREVIOUSPERIODAMOUNT > 0 and ALLOTHERAMOUNT = 0 then 5  --Lapsed new

            when THISPERIODAMOUNT <= 0 and PREVIOUSPERIODAMOUNT > 0 and ALLOTHERAMOUNT > 0 then 6  --Lapsed repeat

        else -1 
    end [BEHAVIORTYPE]
    from
        CONSTITUENTREVENUE_CTE
    left outer join
        dbo.CONSTITUENT on CONSTITUENTREVENUE_CTE.CONSTITUENTID = CONSTITUENT.ID
    outer apply 
        dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    where
        not (PREVIOUSPERIODAMOUNT = 0 and THISPERIODAMOUNT = 0)
    order by
        CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME