USP_DATALIST_CONSTITUENTPROFILEDASHBOARDGIVINGSUMMARY

This datalist returns giving summary information that is used by the constituent profile dashboard.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Constituent
@ISVISIBLE	bit	IN	Visible
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SELECTEDCURRENCYID	uniqueidentifier	IN	Selected currency ID
Definition

Copy


                    CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDGIVINGSUMMARY
                    (
                        @CONSTITUENTID uniqueidentifier,
                        @ISVISIBLE bit = 1,
                        @CURRENTAPPUSERID uniqueidentifier = null,
                        @SELECTEDCURRENCYID uniqueidentifier = null
                    )
                    as
                        set nocount on;

                        if @ISVISIBLE = 1
                        begin                            
                            declare    @TOTALNUMBER int
                            declare    @TOTALAMOUNT money
                            declare    @AVERAGEAMOUNT money 
                            declare    @MOSTFREQUENTAMOUNT money 
                            declare    @TOTALPLEDGEBALANCE money 
                            declare    @TOTALYEARS int 
                            declare    @CONSECUTIVEYEARS int 
                            declare    @GIVENSINCEFISCALYEAR datetime
                            declare @TOTALGIVINGWITHGIFTAID money
                            declare @MATCHEDGIFTSAMOUNT money

                            -- Only applicable for individuals

                            declare @TOTALBELONGEDTOHOUSEHOLDAMOUNT money -- Holds the amount given by the household and its members the constituent belongs to


                            -- Only applicable for groups

                            declare @TOTALGROUPAMOUNT money
                            declare @TOTALGROUPMEMBERAMOUNT money

                            declare @ISGROUP bit
                            select @ISGROUP = ISGROUP
                            from dbo.CONSTITUENT where ID = @CONSTITUENTID;

                            if @ISGROUP = 1
                            begin
                                exec dbo.USP_GROUP_GIVINGSUMMARYGET
                                    @CONSTITUENTID,
                                    @TOTALNUMBER = @TOTALNUMBER output,
                                    @TOTALAMOUNT = @TOTALAMOUNT output,
                                    @TOTALGROUPAMOUNT = @TOTALGROUPAMOUNT output,
                                    @TOTALGROUPMEMBERAMOUNT = @TOTALGROUPMEMBERAMOUNT output,
                                    @AVERAGEAMOUNT = @AVERAGEAMOUNT output,
                                    @MOSTFREQUENTAMOUNT = @MOSTFREQUENTAMOUNT output,
                                    @TOTALPLEDGEBALANCE = @TOTALPLEDGEBALANCE output,
                                    @TOTALYEARS = @TOTALYEARS output,
                                    @CONSECUTIVEYEARS = @CONSECUTIVEYEARS output,
                                    @GIVENSINCEFISCALYEAR = @GIVENSINCEFISCALYEAR output,
                                    @CURRENTAPPUSERID = @CURRENTAPPUSERID,
                                    @MATCHEDGIFTSAMOUNT = @MATCHEDGIFTSAMOUNT output,
                                    @CURRENCYID = @SELECTEDCURRENCYID;
                            end
                            else
                            begin
                                declare @HOUSEHOLDID uniqueidentifier
                                exec dbo.USP_CONSTITUENT_GIVINGSUMMARYGET
                                    @CONSTITUENTID,
                                    @TOTALNUMBER = @TOTALNUMBER output,
                                    @TOTALAMOUNT = @TOTALAMOUNT output,
                                    @AVERAGEAMOUNT = @AVERAGEAMOUNT output,
                                    @MOSTFREQUENTAMOUNT = @MOSTFREQUENTAMOUNT output,
                                    @TOTALPLEDGEBALANCE = @TOTALPLEDGEBALANCE output,
                                    @TOTALYEARS = @TOTALYEARS output,
                                    @CONSECUTIVEYEARS = @CONSECUTIVEYEARS output,
                                    @GIVENSINCEFISCALYEAR = @GIVENSINCEFISCALYEAR output,
                                    @HOUSEHOLDID = @HOUSEHOLDID output,
                                    @TOTALHOUSEHOLDGIVING = @TOTALBELONGEDTOHOUSEHOLDAMOUNT output,
                                    @CURRENTAPPUSERID = @CURRENTAPPUSERID,
                                    @TOTALGIVINGWITHGIFTAID = @TOTALGIVINGWITHGIFTAID output,
                                    @MATCHEDGIFTSAMOUNT = @MATCHEDGIFTSAMOUNT output,
                                    @CURRENCYID = @SELECTEDCURRENCYID;

                                if @HOUSEHOLDID is null
                                    set @TOTALBELONGEDTOHOUSEHOLDAMOUNT = null
                            end

                            select 
                                @TOTALNUMBER TOTALNUMBER,
                                @TOTALAMOUNT TOTALAMOUNT,
                                @AVERAGEAMOUNT AVERAGEAMOUNT,
                                @MOSTFREQUENTAMOUNT MOSTFREQUENTAMOUNT,
                                @TOTALPLEDGEBALANCE TOTALPLEDGEBALANCE,
                                @TOTALYEARS TOTALYEARS,
                                @CONSECUTIVEYEARS CONSECUTIVEYEARS,
                                @GIVENSINCEFISCALYEAR GIVENSINCEFISCALYEAR,
                                @TOTALBELONGEDTOHOUSEHOLDAMOUNT TOTALBELONGEDTOHOUSEHOLDAMOUNT,
                                @TOTALGROUPAMOUNT TOTALGROUPAMOUNT,
                                @TOTALGROUPMEMBERAMOUNT TOTALGROUPMEMBERAMOUNT,
                                @TOTALGIVINGWITHGIFTAID TOTALGIVINGWITHGIFTAID,
                                @MATCHEDGIFTSAMOUNT MATCHEDGIFTSAMOUNT
                        end