USP_CONSTITUENT_5YEARGIVINGGET

            CREATE procedure dbo.USP_CONSTITUENT_5YEARGIVINGGET
            (
                @CONSTITUENTID uniqueidentifier,
                @DESIGNATIONLEVELCATEGORYCODEID uniqueidentifier = null,
                @DESIGNATIONID uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @CAMPAIGNID uniqueidentifier = null,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED xml = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null,
                @CURRENCYCODE tinyint = 1 -- (1, null) = organization, 3 = my base

            )
            as
                set nocount on;

                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;
                declare @APPUSER_IN_NONSITEROLE bit;
                declare @APPUSER_IN_NOSITEROLE bit;

                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 @RANGESTARTDATE datetime;
                set @RANGESTARTDATE = dateadd(year,-5,dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(getdate(),1));

                declare @RANGEENDDATE datetime;
                set @RANGEENDDATE = dateadd(year,1,dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(getdate(),0));

                declare @CURRENTDATEEARLIESTTIME datetime
                set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                declare @SELECTEDCURRENCYID uniqueidentifier;

                if @CURRENCYCODE = 3
                begin
                    if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null
                    begin                    
                        select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
                            from dbo.CURRENCYSET
                            where
                                CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);
                    end
                    else
                    begin
                        select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
                            from dbo.CURRENCYSET
                            where
                                CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();
                    end
                end
                else
                    set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;

                declare @CURRENCYISOCODE nvarchar(3);
                declare @CURRENCYDECIMALDIGITS tinyint;
                declare @CURRENCYSYMBOL nvarchar(5);
                declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
                declare @CURRENCYROUNDINGTYPECODE tinyint;

                select
                    @CURRENCYISOCODE = CURRENCYPROPERTIES.ISO4217,
                    @CURRENCYDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
                    @CURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
                    @CURRENCYROUNDINGTYPECODE = CURRENCYPROPERTIES.ROUNDINGTYPECODE
                from
                    dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES;

                with MYSITESREV_CTE as 
                (
                    select
                        ID,
                        CONSTITUENTID,
                        TRANSACTIONTYPECODE,
                        DATE
                    from
                        dbo.REVENUE
                    where 
                        REVENUE.CONSTITUENTID = @CONSTITUENTID
                    and    exists 
                        (
                            select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                            where 
                                RSSUB.REVENUEID = REVENUE.ID
                                -- Using a case statement since the standard site extension filters

                                -- resulted in a poor plan

                                and case 
                                        when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
                                        when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
                                        else 0 
                                    end = 1
                                and 
                                (
                                    @SITEFILTERMODE = 0
                                    or 
                                    exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                                )
                        )
                )

                select 
                    FISCALYEAR,
                    sum(case when TYPE in (0) then AMOUNT else 0 end) as GIFTS,        -- (Gift)

                    sum(case when TYPE in (1) then AMOUNT else 0 end) as PLEDGES,   -- (Pledge)

                    sum(case when TYPE in (2) then AMOUNT else 0 end) as PAYMENTS,    -- (Pledge Payments)

                    sum(case when TYPE = 3 then AMOUNT else 0 end) as WRITEOFFS,
                    count(distinct case when TYPE in (0, 1) then REVENUEID else null end) as NUMBER,
                    sum(PLEDGEBALANCE) as PLEDGEBALANCE,
                    sum(TAXCLAIMAMOUNT) as TAXCLAIMAMOUNT,
                    @CURRENCYISOCODE ISO4217,
                    @CURRENCYDECIMALDIGITS DECIMALDIGITS,
                    @CURRENCYSYMBOL CURRENCYSYMBOL,
                    @CURRENCYSYMBOLDISPLAYSETTINGCODE SYMBOLDISPLAYSETTINGCODE
                from
                (
                    select
                        MYSITESREV_CTE.ID as REVENUEID,
                        case 
                            when (MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE in (0,3) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0))) then 0    -- Gift

                            when (MYSITESREV_CTE.TRANSACTIONTYPECODE = 1) then 1                                                        -- Pledge

                            when (MYSITESREV_CTE.TRANSACTIONTYPECODE = 7) then 0                                                        -- Auction donation

                            when (MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 2) then 2                                -- Payment (Pledge payment)

                        end as TYPE,
                        REVENUESPLIT.AMOUNTINCURRENCY AMOUNT,                        
                        dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(MYSITESREV_CTE.DATE,0) FISCALYEAR,
                        -- Bug 24790 - AdamBu 3/17/09 - Corrected the condition on which we get pledge balance.

                        case when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 then (select dbo.UFN_PLEDGE_GETDESIGNATIONBALANCEINCURRENCY(MYSITESREV_CTE.ID, REVENUESPLIT.DESIGNATIONID, @SELECTEDCURRENCYID)) else 0 end as PLEDGEBALANCE,
                        case 
                            -- Gift or Pledge

                            when (MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE in (0,3) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0))) or (MYSITESREV_CTE.TRANSACTIONTYPECODE = 1) 
                                then isnull(REVENUESPLITGIFTAID.TAXCLAIMAMOUNTINCURRENCY, 0)
                            else 0
                        end as TAXCLAIMAMOUNT 
                    from MYSITESREV_CTE
                        left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUESPLIT on REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID
                        left join dbo.DESIGNATION DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
                        left join dbo.DESIGNATIONLEVEL DESIGNATIONLEVEL1 on DESIGNATIONLEVEL1.ID =
                            case 
                                when DESIGNATION.DESIGNATIONLEVEL5ID is not null then DESIGNATION.DESIGNATIONLEVEL5ID
                                when DESIGNATION.DESIGNATIONLEVEL4ID is not null then DESIGNATION.DESIGNATIONLEVEL4ID
                                when DESIGNATION.DESIGNATIONLEVEL3ID is not null then DESIGNATION.DESIGNATIONLEVEL3ID
                                when DESIGNATION.DESIGNATIONLEVEL2ID is not null then DESIGNATION.DESIGNATIONLEVEL2ID
                                else DESIGNATION.DESIGNATIONLEVEL1ID
                            end
                        left join dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) ELIGIBLEREVENUESPLITGIFTAID on ELIGIBLEREVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
                        left join dbo.UFN_REVENUESPLITGIFTAID_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = ELIGIBLEREVENUESPLITGIFTAID.ID
                        left join dbo.UFN_PLEDGE_GETDESIGNATIONBALANCEINCURRENCY_BULK(@SELECTEDCURRENCYID) DESIGNATIONPLEDGEBALANCE on DESIGNATIONPLEDGEBALANCE.ID = MYSITESREV_CTE.ID and DESIGNATIONPLEDGEBALANCE.DESIGNATIONID = DESIGNATION.ID
                    where
                        MYSITESREV_CTE.CONSTITUENTID = @CONSTITUENTID and
                        MYSITESREV_CTE.DATE > @RANGESTARTDATE and
                        MYSITESREV_CTE.DATE < @RANGEENDDATE and
                        (@DESIGNATIONLEVELCATEGORYCODEID is null or @DESIGNATIONLEVELCATEGORYCODEID = DESIGNATIONLEVEL1.DESIGNATIONLEVELCATEGORYCODEID) and
                        (@DESIGNATIONID is null or DESIGNATION.ID = @DESIGNATIONID) and
                        (@CAMPAIGNID is null or exists(select top 1 RSC.ID from REVENUESPLITCAMPAIGN RSC where RSC.CAMPAIGNID = @CAMPAIGNID))
                            and (@ISADMIN = 1 or
                                    (
                                        (@APPUSER_IN_NONRACROLE = 1 or
                                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, MYSITESREV_CTE.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                        and
                                        (@APPUSER_IN_NONSITEROLE = 1 or
                                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, MYSITESREV_CTE.CONSTITUENTID, @APPUSER_IN_NOSITEROLE) = 1)
                                    )
                                )
                    union all

                    select
                        MYSITESREV_CTE.ID as REVENUEID,
                        3 as TYPE,                                                                        -- WRITE-OFF

                        WRITEOFFSPLIT.AMOUNTINCURRENCY as AMOUNT,                            
                        dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(MYSITESREV_CTE.DATE,0) FISCALYEAR,
                        0 as PLEDGEBALANCE,
                        0 as TAXCLAIMAMOUNT
                    from dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) WRITEOFFSPLIT
                        left join dbo.WRITEOFF WO on WRITEOFFSPLIT.WRITEOFFID = WO.ID
                        left join MYSITESREV_CTE on WO.REVENUEID = MYSITESREV_CTE.ID            
                        left join dbo.DESIGNATION DESIGNATION on DESIGNATION.ID = WRITEOFFSPLIT.DESIGNATIONID
                        left join dbo.DESIGNATIONLEVEL DESIGNATIONLEVEL1 on DESIGNATIONLEVEL1.ID =
                            case 
                                when DESIGNATION.DESIGNATIONLEVEL5ID is not null then DESIGNATION.DESIGNATIONLEVEL5ID
                                when DESIGNATION.DESIGNATIONLEVEL4ID is not null then DESIGNATION.DESIGNATIONLEVEL4ID
                                when DESIGNATION.DESIGNATIONLEVEL3ID is not null then DESIGNATION.DESIGNATIONLEVEL3ID
                                when DESIGNATION.DESIGNATIONLEVEL2ID is not null then DESIGNATION.DESIGNATIONLEVEL2ID
                                else DESIGNATION.DESIGNATIONLEVEL1ID
                            end
                    where                            
                        MYSITESREV_CTE.CONSTITUENTID = @CONSTITUENTID and
                        MYSITESREV_CTE.DATE > @RANGESTARTDATE and
                        MYSITESREV_CTE.DATE < @RANGEENDDATE and
                        MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 and -- Do not include write-offs for other revenue types

                        (@DESIGNATIONLEVELCATEGORYCODEID is null or @DESIGNATIONLEVELCATEGORYCODEID = DESIGNATIONLEVEL1.DESIGNATIONLEVELCATEGORYCODEID) and
                        (@DESIGNATIONID is null or DESIGNATION.ID = @DESIGNATIONID)
                        and (@ISADMIN = 1 or 
                                (
                                    (@APPUSER_IN_NONRACROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, MYSITESREV_CTE.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                    and
                                    (@APPUSER_IN_NONSITEROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, MYSITESREV_CTE.CONSTITUENTID, @APPUSER_IN_NOSITEROLE) = 1)
                                )
                            )
                ) as DATA
                where TYPE is not NULL
                group by FISCALYEAR
                order by FISCALYEAR;