USP_GROUP_GIVINGSUMMARYGET

            CREATE procedure [dbo].[USP_GROUP_GIVINGSUMMARYGET]
            (
                @CONSTITUENTID uniqueidentifier,
                @GROUPINCLUDESMEMBERGIVING bit = null output,
                @ISHOUSEHOLD bit = null output,
                @TOTALNUMBER int = null output,
                @TOTALAMOUNT money = null output,
                @TOTALGROUPAMOUNT money = null output,
                @TOTALGROUPMEMBERAMOUNT 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,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED xml = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null,
                @GROUPMEMBERTAXCLAIMAMOUNT money = null output,
                @MATCHEDGIFTSAMOUNT money = null output,
                @CURRENCYID uniqueidentifier = null
            )
            as
                set nocount on

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                declare @HOUSEHOLDSCANBEDONORS as bit
                set @HOUSEHOLDSCANBEDONORS = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS()

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

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

                -- Households can be donors depending on a value of a system flag, other groups are allowed to be

                -- donors through their type

                select 
                @GROUPINCLUDESMEMBERGIVING = 
                  case
                    when GD.GROUPTYPECODE = 0 then 1
                    when GD.GROUPTYPECODE = 1 then GT.INCLUDEMEMBERGIVING
                  end,
                @ISHOUSEHOLD = 
                  case
                    when GD.GROUPTYPECODE = 0 then 1
                    when GD.GROUPTYPECODE = 1 then 0
                  end
                from dbo.GROUPDATA GD
                left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
                where GD.ID=@CONSTITUENTID;

                -- put all group and member giving into table variable, then perform calculations/get data from it.  This simplifies design

                -- by placing the selection logic in a single function, rather than a half-dozen restatements of the same lengthy joins and filters. 

                -- It also improves performance by avoiding performing the same joins/filters multiple times


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

                declare @MEMBERIDS table
                (
                    ID uniqueidentifier,
                    DATEFROM datetime,
                    DATETO datetime
                )

                if @GROUPINCLUDESMEMBERGIVING = 1
                begin
                    insert into @MEMBERIDS
                    select 
                        GROUPMEMBER.MEMBERID ID, DATEFROM, DATETO
                    from 
                        dbo.GROUPMEMBER
                    left join 
                        dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
                    where 
                        GROUPMEMBER.GROUPID = @CONSTITUENTID;    

                    insert into @GIVING 
                    select             
                        REVENUEID,
                        TRANSACTIONTYPECODE,
                        TRANSACTIONTYPE,
                        REVENUEAMOUNT,
                        [DATE],
                        DATEADDED,
                        SPLITID,
                        APPLICATIONCODE,
                        REV.DESIGNATIONID,
                        SPLITAMOUNT,
                        WRITEOFFAMOUNT,
                        MEMBERIDS.ID CONSTITUENTID,
                        SPLITNETAMOUNT money
                    from 
                        @MEMBERIDS MEMBERIDS
                    cross apply dbo.UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY_BULK(
                        MEMBERIDS.ID,
                        @CURRENTAPPUSERID,
                        @SITEFILTERMODE,
                        @SITESSELECTED,
                        @SECURITYFEATUREID,
                        @SECURITYFEATURETYPE,
                        @CURRENCYID,
                        @ORGANIZATIONCURRENCYID,
                        @DECIMALDIGITS,
                        @ROUNDINGTYPECODE) REV
                    where
                        ((MEMBERIDS.DATEFROM is null or REV.DATE >= MEMBERIDS.DATEFROM) and
                        (MEMBERIDS.DATETO is null or REV.DATE <= MEMBERIDS.DATETO));
                end

                if not (@ISHOUSEHOLD = 1 and @HOUSEHOLDSCANBEDONORS = 0)
                begin
                    insert into @GIVING
                    select 
                        REVENUEID,
                        TRANSACTIONTYPECODE,
                        TRANSACTIONTYPE,
                        REVENUEAMOUNT,
                        [DATE],
                        DATEADDED,
                        SPLITID,
                        APPLICATIONCODE,
                        REV.DESIGNATIONID,
                        SPLITAMOUNT,
                        WRITEOFFAMOUNT,
                        @CONSTITUENTID,
                        SPLITNETAMOUNT
                    from dbo.UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY_BULK(
                        @CONSTITUENTID,
                        @CURRENTAPPUSERID,
                        @SITEFILTERMODE,
                        @SITESSELECTED,
                        @SECURITYFEATUREID,
                        @SECURITYFEATURETYPE,
                        @CURRENCYID,
                        @ORGANIZATIONCURRENCYID,
                        @DECIMALDIGITS,
                        @ROUNDINGTYPECODE) REV
                end

                select
                    @TOTALNUMBER = count(distinct G.REVENUEID),
                    @TOTALAMOUNT = cast(sum(cast(SPLITNETAMOUNT as decimal(20,5))) as money),
                    @TOTALPLEDGEBALANCE = cast(sum(case TRANSACTIONTYPECODE when 1 then cast(dbo.UFN_PLEDGESPLIT_GETBALANCEINCURRENCY(G.SPLITID, @CURRENCYID) as decimal(20,5)) else 0 end) as money)
                from @GIVING G

                select 
                    @TOTALGROUPMEMBERAMOUNT = cast(sum(cast(SPLITNETAMOUNT as decimal(20,5))) as money),
                    @GROUPMEMBERTAXCLAIMAMOUNT = cast(sum(cast(dbo.UFN_GIFTAID_GETTAXCLAIMAMOUNTINCURRENCY(SPLITID, TRANSACTIONTYPECODE, APPLICATIONCODE, @CURRENCYID) as decimal(20,5))) as money)
                from @GIVING G
                where CONSTITUENTID in (select ID from @MEMBERIDS)

                select 
                    @TOTALGROUPAMOUNT = cast(sum(cast(SPLITNETAMOUNT as decimal(20,5))) as money)
                from @GIVING G
                where CONSTITUENTID not in (select ID from @MEMBERIDS)

                -- values will be null if the selects got short-circuited for number of gifts, amounts, and pledge balances

                set @TOTALGROUPMEMBERAMOUNT = ISNULL(@TOTALGROUPMEMBERAMOUNT, 0)
                set @TOTALGROUPAMOUNT = ISNULL(@TOTALGROUPAMOUNT, 0)
                set @TOTALNUMBER = isnull(@TOTALNUMBER, 0);
                set @TOTALAMOUNT = isnull(@TOTALAMOUNT, 0);
                set @TOTALPLEDGEBALANCE = isnull(@TOTALPLEDGEBALANCE, 0);

                set @AVERAGEAMOUNT = 0;
                if @TOTALNUMBER <> 0
                    set @AVERAGEAMOUNT = @TOTALAMOUNT / @TOTALNUMBER;

                --Matched by organization amount

                select
                    @MATCHEDGIFTSAMOUNT = sum(AMOUNT)
                from
                (
                    select
                        REVENUE.AMOUNTINCURRENCY as AMOUNT
                    from
                        @GIVING
                    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.AMOUNTINCURRENCY
                ) SUBQ

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

            /*
              For the most frequent gifts, the pattern taken is:
                1. Figure out what the variant would be only for the group members IF
                    members are included in the giving.
                2. Over-ride those values using the variant for the group itself IF the group 
                    can be a donor AND a comparison passes (i.e. the largest gift from the group
                    is larger than the largest gift from the group members)
            */

                -- most frequent group member amount

                declare @GROUPMEMBERAMOUNTFREQUENCY integer;
                declare @MOSTFREQUENTGROUPMEMBERAMOUNT money;

                with PLEDGEANDPAYMENTCTE as
                (
                    select
                        REVENUEID ID,
                        sum(SPLITNETAMOUNT) as NETAMOUNT
                    from @GIVING 
                    where CONSTITUENTID in (select ID from @MEMBERIDS)
                    group by REVENUEID
                )
                select top 1
                    @MOSTFREQUENTGROUPMEMBERAMOUNT = X.NETAMOUNT,
                    @GROUPMEMBERAMOUNTFREQUENCY = X.MAXCOUNT
                from
                    (select
                        R.NETAMOUNT 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
                    X.NETAMOUNT desc;

                -- most frequent group amount

                declare @MOSTFREQUENTGROUPAMOUNT money;
                declare @GROUPAMOUNTFREQUENCY integer;

                with PLEDGEANDPAYMENTCTE as
                (
                    select
                        REVENUEID ID,
                        sum(SPLITNETAMOUNT) as NETAMOUNT
                    from @GIVING 
                    where CONSTITUENTID not in (select ID from @MEMBERIDS)
                    group by REVENUEID
                )
                select top 1
                    @MOSTFREQUENTGROUPAMOUNT = X.NETAMOUNT,
                    @GROUPAMOUNTFREQUENCY = X.MAXCOUNT
                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
                    X.NETAMOUNT desc;

                select top(1)
                    @MOSTFREQUENTAMOUNT = coalesce(AMOUNT, 0)
                from
                    (select @MOSTFREQUENTGROUPAMOUNT as AMOUNT, @GROUPAMOUNTFREQUENCY as FREQUENCY
                     union all
                     select @MOSTFREQUENTGROUPMEMBERAMOUNT as AMOUNT, @GROUPMEMBERAMOUNTFREQUENCY as FREQUENCY) X
                order by X.AMOUNT desc;

                -- YEAR CALCULATIONS

                declare @GIFTYEARS table([YEAR] datetime);
                insert into @GIFTYEARS 
                select dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0) [YEAR] from @GIVING
                group by
                    dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0)
                order by
                    dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0) desc;

                --TOTALYEARS

                select
                    @TOTALYEARS = count([YEAR])
                from
                    @GIFTYEARS

                --CONSECUTIVE YEARS

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

                declare FISCALYEARCURSOR cursor local fast_forward for
                select
                    [YEAR] FISCALYEAR_FIRSTDAY
                from
                    @GIFTYEARS;

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

                declare @CURRENTFIRSCALYEAR_FIRSTDAY datetime, @LASTFISCALYEAR_FIRSTDAY datetime
                set @CURRENTFIRSCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0)
                set @LASTFISCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@CURRENTDATE),0)

                open FISCALYEARCURSOR;
                    fetch next from FISCALYEARCURSOR into @FISCALYEAR_FIRSTDAY;
                    if @@FETCH_STATUS = 0 begin
                        if @FISCALYEAR_FIRSTDAY > @CURRENTFIRSCALYEAR_FIRSTDAY begin
                            set @CONTINUE = 1;
                            fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                        end
                        else if @FISCALYEAR_FIRSTDAY = @CURRENTFIRSCALYEAR_FIRSTDAY or
                           @FISCALYEAR_FIRSTDAY = @LASTFISCALYEAR_FIRSTDAY 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 > @CURRENTFIRSCALYEAR_FIRSTDAY begin
                                set @FISCALYEAR_FIRSTDAY = @PREVIOUSFISCALYEAR_FIRSTDAY;
                                fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                            end
                            else if @PREVIOUSFISCALYEAR_FIRSTDAY = @CURRENTFIRSCALYEAR_FIRSTDAY or
                               @PREVIOUSFISCALYEAR_FIRSTDAY = @LASTFISCALYEAR_FIRSTDAY 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;