USP_CONSTITUENT_GETGIVINGHISTORY_2

            CREATE procedure dbo.USP_CONSTITUENT_GETGIVINGHISTORY_2
            (
                @CONSTITUENTID uniqueidentifier,
                @INCLUDESOFTCREDIT bit,
                @NUMBERTOSHOWCODE smallint,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED xml = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null,
                @INCLUDEGROUPMEMBERGIVING bit = 0,
        @CURRENCYCODE tinyint = null
            ) as
                set nocount on;

                declare @CURRENTDATE datetime = getdate();
                declare @ISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                declare @NODATELIMITS bit = 0
                if @NUMBERTOSHOWCODE in (0,1,2)
                    set @NODATELIMITS = 1;

                declare @ENDDATE datetime = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);
                declare @STARTDATE datetime = 
                    CASE @NUMBERTOSHOWCODE 
                        when 3 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE)) -- last 30 days

                        when 4 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE)) -- last 90 days

                        when 5 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-1,@CURRENTDATE))) -- last year

                        else @CURRENTDATE
                    end;

                declare @NUMBERTOSHOW int;
                set @NUMBERTOSHOW = 
                    CASE @NUMBERTOSHOWCODE 
                        when 1 then 5
                        when 2 then 10
                        else 1000000  -- an extremely large number that no donor's gift count will ever approach; i.e., show everything

                    end;

        declare @CURRENCYID uniqueidentifier
        declare @ORGTOMYBASEEXCHANGERATE uniqueidentifier = null

        declare @MULTICURRENCYENABLED bit;
        set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
        if @MULTICURRENCYENABLED = 0 
          set @CURRENCYCODE = 1


        if @CURRENCYCODE = 1
          set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        if @CURRENCYCODE = 3
          set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

                -- ChrisFal, 12/17/09 - there is a performance trade-off that may need further testing: this CTE

                -- avoids the perf hit from using TempDB, but places locks for the duration of this

                -- sproc.  The main alternative is to use a table variable, which takes the TempDB perf

                -- hit but minimizes the locking.

                with CONSTITUENTIDS_CTE as
                (
                    select 
                        @CONSTITUENTID ID, null DATEFROM, null DATETO
                    union all
                    select 
                        GROUPMEMBER.MEMBERID ID, DATEFROM, DATETO
                    from 
                        dbo.GROUPMEMBER
                    left join 
                        dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
                    where 
                        @INCLUDEGROUPMEMBERGIVING = 1
                        and
                        GROUPMEMBER.GROUPID = @CONSTITUENTID                
                ),
                REV_CTE as 
                (
                    select
                        REVENUEID,
                        CONSTITUENTIDS_CTE.ID CONSTITUENTID,
                        sum(SPLITAMOUNT) AMOUNT,
                        TRANSACTIONTYPE,
                        TRANSACTIONTYPECODE,
                        [DATE],
                        REV.DATEADDED,
                        case when @INCLUDEGROUPMEMBERGIVING = 1 then (select NAME from dbo.CONSTITUENT where ID = CONSTITUENTIDS_CTE.ID) else null end CONSTITUENTNAME,
            TRANSACTIONCURRENCYID,
            BASECURRENCYID,
            TRANSACTIONAMOUNT,
            ORGANIZATIONAMOUNT
                    from 
                        CONSTITUENTIDS_CTE
                    cross apply dbo.UFN_CONSTITUENT_GIVINGHISTORY_2(
                        CONSTITUENTIDS_CTE.ID,
                        @CURRENTAPPUSERID,
                        @SITEFILTERMODE,
                        @SITESSELECTED,
                        @SECURITYFEATUREID,
                        @SECURITYFEATURETYPE) REV
                    where 
                        ((CONSTITUENTIDS_CTE.DATEFROM is null or REV.DATE >= CONSTITUENTIDS_CTE.DATEFROM) and
                        (CONSTITUENTIDS_CTE.DATETO is null or REV.DATE <= CONSTITUENTIDS_CTE.DATETO))
                    and 
                        (@NODATELIMITS = 1 or
                         REV.DATE between @STARTDATE and @ENDDATE) 
                    group by
                        REVENUEID, CONSTITUENTIDS_CTE.ID, TRANSACTIONTYPE, TRANSACTIONTYPECODE, [DATE], REV.DATEADDED,TRANSACTIONCURRENCYID,BASECURRENCYID,TRANSACTIONAMOUNT,
            ORGANIZATIONAMOUNT
                ) 
                select top(@NUMBERTOSHOW) * from (
                    select
                        REVENUEID ID,
                        CONSTITUENTID,
                        CONSTITUENTNAME,
                        REVENUEID as RECORDID,
                        REV.[DATE],
                        REV.DATEADDED,
            case @CURRENCYCODE
            when 0 then REV.AMOUNT
            when 1 then REV.ORGANIZATIONAMOUNT
            when 2 then REV.TRANSACTIONAMOUNT
            when 3 then case @CURRENCYID
                        when REV.TRANSACTIONCURRENCYID then REV.TRANSACTIONAMOUNT
                        else dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUEID,@CURRENCYID)
                        end
            end as AMOUNT,
                        case TRANSACTIONTYPECODE
                          when 1 then case @CURRENCYCODE
                                when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(REVENUEID,0)
                                when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(REVENUEID,2)
                                when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(REVENUEID,1)
                                when 3 then case @CURRENCYID
                                            when REV.TRANSACTIONCURRENCYID then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(REVENUEID,1)
                                            else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNTINCURRENCY(REVENUEID,@CURRENCYID)
                                            end 
                                end
                          else case @CURRENCYCODE
                        when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(REVENUEID,1,0)
                        when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(REVENUEID,1,2)
                        when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(REVENUEID,1,1)
                        when 3 then case @CURRENCYID
                                    when REV.TRANSACTIONCURRENCYID then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(REVENUEID,1,1)
                                    else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNTINCURRENCY(REVENUEID,1,@CURRENCYID)
                                    end
                        end
                        end as GROSSAMOUNT,
                        case TRANSACTIONTYPECODE
                          when 1 then case @CURRENCYCODE
                          when 2 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUEID,REV.TRANSACTIONCURRENCYID)
                          when 0 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUEID,REV.BASECURRENCYID)
                          else
                          dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUEID,@CURRENCYID)
                          end
                          when 4 then case @CURRENCYCODE
                          when 2 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUEID,REV.TRANSACTIONCURRENCYID)
                          when 0 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUEID,REV.BASECURRENCYID)
                          else
                          dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUEID,@CURRENCYID)
                          end
                          else null
                        end as BALANCE,
                        TRANSACTIONTYPECODE,
                        TRANSACTIONTYPE,
                        isnull((select min(REVENUESPLIT.TYPE) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = REV.REVENUEID group by REVENUESPLIT.REVENUEID having min(REVENUESPLIT.TYPECODE)=max(REVENUESPLIT.TYPECODE)),'<Split>') REVENUETYPE,
                        dbo.UFN_REVENUE_DESIGNATIONLIST(REVENUEID) as DESIGNATION,
                        (
                            select 
                                dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME) 
                            from 
                                dbo.CAMPAIGN 
                            inner join 
                                dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                            inner join
                                dbo.REVENUESPLIT on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
                            where
                                REVENUESPLIT.REVENUEID = REV.REVENUEID
                        ) as CAMPAIGNS,
                        dbo.UFN_REVENUE_BUILDSITELIST(REVENUEID) SITES,
            case @CURRENCYCODE
            when 2 then REV.TRANSACTIONCURRENCYID
            when 0 then REV.BASECURRENCYID
            else @CURRENCYID
            end as DISPLAYCURRENCY
                    from
                        REV_CTE REV) TOPREV
                order by
                    DATE desc, DATEADDED desc, AMOUNT desc;