USP_CONSTITUENT_GIVINGSUMMARYGET

            CREATE procedure [dbo].[USP_CONSTITUENT_GIVINGSUMMARYGET]
            (
                @CONSTITUENTID uniqueidentifier,
                @TOTALNUMBER int = null output,
                @TOTALAMOUNT money = null output,
                @AVERAGEAMOUNT money = null output,
                @MOSTFREQUENTAMOUNT money = null output,
                @TOTALPLEDGEBALANCE money = null output,
                @TOTALYEARS int = null output,
                @CONSECUTIVEYEARS int = null output,
                @GIVENSINCEFISCALYEAR datetime = null output,
                @HOUSEHOLDID uniqueidentifier = null output,
                @TOTALHOUSEHOLDGIVING money = null output,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED xml = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null,
                @TOTALGIVINGWITHGIFTAID money = null output,
                @MATCHEDGIFTSAMOUNT money = null output,
                @CURRENCYID uniqueidentifier = null,
                @CALCULATEGIVINGHISTORY bit = 1
            )
            as
                set nocount on

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

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

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

                select @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),
                        @DECIMALDIGITS = DECIMALDIGITS,
                        @ROUNDINGTYPECODE = ROUNDINGTYPECODE
                from 
                    dbo.CURRENCY
                where
                    CURRENCY.ID = @CURRENCYID;

                declare @ALLREVENUE table
                (
                    REVENUEID uniqueidentifier,
                    TRANSACTIONTYPECODE tinyint,
                    TRANSACTIONTYPE nvarchar(21),
                    REVENUEAMOUNT money,
                    [DATE] datetime,
                    DATEADDED datetime,
                    SPLITID uniqueidentifier,
                    APPLICATIONCODE tinyint,
                    DESIGNATIONID uniqueidentifier,
                    SPLITAMOUNT money,
                    WRITEOFFAMOUNT money,
                    SPLITNETAMOUNT money
                )    

                insert @ALLREVENUE
                select * 
                from dbo.UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY_BULK(
                    @CONSTITUENTID,
                    @CURRENTAPPUSERID,
                    @SITEFILTERMODE,
                    @SITESSELECTED,
                    @SECURITYFEATUREID,
                    @SECURITYFEATURETYPE,
                    @CURRENCYID,
                    @ORGANIZATIONCURRENCYID,
                    @DECIMALDIGITS,
                    @ROUNDINGTYPECODE)    

                select
                    @TOTALNUMBER = count(distinct REVENUEID),
                    @TOTALAMOUNT = cast(sum(cast(SPLITNETAMOUNT as decimal(20,5))) as money),
                    @TOTALGIVINGWITHGIFTAID = cast(sum(cast(SPLITAMOUNT as decimal(20,5)) + cast(dbo.UFN_GIFTAID_GETTAXCLAIMAMOUNTINCURRENCY(SPLITID, TRANSACTIONTYPECODE, APPLICATIONCODE, @CURRENCYID) as decimal(20, 5)) - cast(WRITEOFFAMOUNT as decimal(20,5))) as money),
                    @TOTALPLEDGEBALANCE = cast(sum(case TRANSACTIONTYPECODE when 1 then cast(dbo.UFN_PLEDGESPLIT_GETBALANCEINCURRENCY(SPLITID, @CURRENCYID) as decimal(20,5)) else 0 end) as money)
                from
                    @ALLREVENUE REV;

                set @TOTALNUMBER = coalesce(@TOTALNUMBER, 0);
                set @TOTALAMOUNT = coalesce(@TOTALAMOUNT, 0);
                set @TOTALPLEDGEBALANCE = coalesce(@TOTALPLEDGEBALANCE, 0);
                set @TOTALGIVINGWITHGIFTAID = coalesce(@TOTALGIVINGWITHGIFTAID, 0);

                if @TOTALNUMBER > 0
                    set @AVERAGEAMOUNT = cast(@TOTALAMOUNT as decimal(20,5)) / cast(@TOTALNUMBER as decimal(20,5));

                --MODE

                with PLEDGEANDPAYMENTCTE as
                (
                    select
                        REVENUEID ID,
                        sum(SPLITNETAMOUNT) as NETAMOUNT
                    from @ALLREVENUE 
                    group by REVENUEID
                )
                select top 1
                    @MOSTFREQUENTAMOUNT = X.NETAMOUNT
                from
                    (select
                        NETAMOUNT,
                        count(R.ID) TOTAL,
                        max(count(R.ID)) over () MAXCOUNT
                    from
                        PLEDGEANDPAYMENTCTE R
                    group by
                        NETAMOUNT) X
                where
                    X.TOTAL = X.MAXCOUNT and X.TOTAL > 1
                order by
                    NETAMOUNT desc;

                --Matched by organization amount


                select
                    @MATCHEDGIFTSAMOUNT = sum(AMOUNT)
                from
                (    
                    --donation matched gift amount

                    select 
                        REVENUE.AMOUNTINCURRENCY as AMOUNT,
                        REVENUE.ID
                    from 
                        @ALLREVENUE
                    inner join
                        dbo.REVENUEMATCHINGGIFT on REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = REVENUEID
                    inner join
                        dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) REVENUE on REVENUE.ID = REVENUEMATCHINGGIFT.ID
                    group by REVENUEID, REVENUE.ID, REVENUE.AMOUNTINCURRENCY

                    union

                    --pledge/planned gift payment matched gift amount

                    select 
                        PLEDGEREVENUE.AMOUNTINCURRENCY as AMOUNT,
                        PLEDGEREVENUE.ID
                    from 
                        @ALLREVENUE ALLREVENUE
                    inner join
                        dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PLEDGEID = ALLREVENUE.REVENUEID
                    inner join
                        dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
                    inner join
                        dbo.REVENUEMATCHINGGIFT on REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = REVENUESPLIT.FINANCIALTRANSACTIONID
                    inner join
                        dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) PLEDGEREVENUE on PLEDGEREVENUE.ID = REVENUEMATCHINGGIFT.ID
                    group by ALLREVENUE.REVENUEID, PLEDGEREVENUE.ID, PLEDGEREVENUE.AMOUNTINCURRENCY
                ) SUBQ


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

                --TOTALYEARS

                select
                    @TOTALYEARS = count(distinct(dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0)))
                from
                    @ALLREVENUE 

                if @CALCULATEGIVINGHISTORY = 1
                begin
                    --CONSECUTIVE YEARS

                    declare @CURRENTDATE datetime;
                    declare @FISCALYEAR_FIRSTDAY datetime;
                    declare @PREVIOUSFISCALYEAR_FIRSTDAY datetime;
                    declare @CONTINUE bit;

                    set @CURRENTDATE = getdate();

                    declare FISCALYEARCURSOR cursor local fast_forward for
                    select
                        dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0) FISCALYEAR_FIRSTDAY
                    from
                        @ALLREVENUE
                    group by
                        dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0)
                    order by
                        dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0) desc;

                    set @CONSECUTIVEYEARS = 0;
                    set @GIVENSINCEFISCALYEAR = null;

                    open FISCALYEARCURSOR;
                        fetch next from FISCALYEARCURSOR into @FISCALYEAR_FIRSTDAY;
                        if @@FETCH_STATUS = 0 begin
                            if @FISCALYEAR_FIRSTDAY > dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0) begin
                                set @CONTINUE = 1;
                                fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                            end
                            else if @FISCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0) or
                               @FISCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@CURRENTDATE),0) begin
                                set @CONSECUTIVEYEARS = @CONSECUTIVEYEARS + 1;
                                set @CONTINUE = 1;
                                fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                            end
                            else
                                set @CONTINUE = 0;
                        end

                        while @@FETCH_STATUS = 0 and @CONTINUE = 1 begin
                            if @CONSECUTIVEYEARS = 0 begin
                                if @PREVIOUSFISCALYEAR_FIRSTDAY > dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0) begin
                                    set @FISCALYEAR_FIRSTDAY = @PREVIOUSFISCALYEAR_FIRSTDAY;
                                    fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                                end
                                else if @PREVIOUSFISCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0) or
                                   @PREVIOUSFISCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@CURRENTDATE),0) begin
                                    set @CONSECUTIVEYEARS = @CONSECUTIVEYEARS + 1;
                                    set @FISCALYEAR_FIRSTDAY = @PREVIOUSFISCALYEAR_FIRSTDAY;
                                    fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                                end
                                else
                                    set @CONTINUE = 0
                            end
                            else if @PREVIOUSFISCALYEAR_FIRSTDAY = dateadd(year, -1, @FISCALYEAR_FIRSTDAY) begin
                                set @CONSECUTIVEYEARS = @CONSECUTIVEYEARS + 1;
                                set @FISCALYEAR_FIRSTDAY = @PREVIOUSFISCALYEAR_FIRSTDAY;
                                fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                            end
                            else
                                set @CONTINUE = 0;
                        end
                    --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                    close FISCALYEARCURSOR;
                    deallocate FISCALYEARCURSOR;

                    if @CONSECUTIVEYEARS > 0
                        set @GIVENSINCEFISCALYEAR = @FISCALYEAR_FIRSTDAY;
                end

                -- household amount

                select
                    @HOUSEHOLDID = GM.GROUPID
                from
                    dbo.GROUPMEMBER GM
                left outer join
                    dbo.GROUPDATA GD on GD.ID = GM.GROUPID
                left outer join
                    dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                where
                    GM.MEMBERID = @CONSTITUENTID
                and
                    GD.GROUPTYPECODE = 0
                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
                    or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME));

                if @HOUSEHOLDID is not null
                begin
                    declare @GIVINGBYGROUPMEMBERS money;
                    declare @GIVINGBYGROUP money;
                    with MEMBERIDS_CTE as (
                    select MEMBERID from dbo.GROUPMEMBER where GROUPID = @HOUSEHOLDID)
                    select
                        @GIVINGBYGROUPMEMBERS = cast(sum(cast(SPLITNETAMOUNT as decimal(20,5))) as money)
                    from
                        MEMBERIDS_CTE
                    cross apply dbo.UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY_BULK(
                        MEMBERIDS_CTE.MEMBERID,
                        @CURRENTAPPUSERID,
                        @SITEFILTERMODE,
                        @SITESSELECTED,
                        @SECURITYFEATUREID,
                        @SECURITYFEATURETYPE,
                        @CURRENCYID,
                        @ORGANIZATIONCURRENCYID,
                        @DECIMALDIGITS,
                        @ROUNDINGTYPECODE) REV
                    inner join dbo.GROUPMEMBER GM on MEMBERIDS_CTE.MEMBERID = GM.MEMBERID
                    left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                    where 
                        GM.GROUPID = @HOUSEHOLDID and
                        ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= REV.DATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= REV.DATE)) 
                        or (GMDR.DATEFROM <= REV.DATE and GMDR.DATETO >= REV.DATE));

                     select
                        @GIVINGBYGROUP = cast(sum(cast(SPLITNETAMOUNT as decimal(20,5))) as money)
                    from dbo.UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY_BULK(
                        @HOUSEHOLDID,
                        @CURRENTAPPUSERID,
                        @SITEFILTERMODE,
                        @SITESSELECTED,
                        @SECURITYFEATUREID,
                        @SECURITYFEATURETYPE,
                        @CURRENCYID,
                        @ORGANIZATIONCURRENCYID,
                        @DECIMALDIGITS,
                        @ROUNDINGTYPECODE) REV;

                    select @GIVINGBYGROUPMEMBERS = coalesce(@GIVINGBYGROUPMEMBERS, 0);
                    select @GIVINGBYGROUP = coalesce(@GIVINGBYGROUP, 0);
                    select @TOTALHOUSEHOLDGIVING = @GIVINGBYGROUPMEMBERS + @GIVINGBYGROUP;
                end