USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUPSUMMARY

The load procedure used by the view dataform template "Constituent Group Summary View"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@GROUPID	uniqueidentifier	INOUT	GROUPID
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PRIMARYCONTACTNAME	nvarchar(700)	INOUT	Primary contact
@PRIMARYCONTACTID	uniqueidentifier	INOUT	PRIMARYCONTACTID
@DESCRIPTION	nvarchar(300)	INOUT	Description
@GROUPCREATEDONDATE	datetime	INOUT	Group created on
@GROUPINCLUDESMEMBERGIVING	bit	INOUT	Group includes member giving
@GIVINGBYGROUP	money	INOUT	Total revenue by group
@GIVINGBYGROUPMEMBERS	money	INOUT	Total revenue by group members
@TOTALGIVING	money	INOUT	Total revenue
@ISHOUSEHOLD	bit	INOUT	Is household
@GIVESANONYMOUSLY	bit	INOUT	Gives anonymously
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@GIVINGBYGROUPMEMBERSWITHGIFTAID	money	INOUT	Total revenue with Gift Aid
@CURRENCYID	uniqueidentifier	INOUT	CURRENCYID
@ISINACTIVE	bit	INOUT
@ISDISSOLVED	bit	INOUT
@GROUPTYPE	nvarchar(150)	INOUT
@WEBADDRESS	UDT_WEBADDRESS	INOUT

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUPSUMMARY
                (
                    @ID uniqueidentifier,
                    @GROUPID uniqueidentifier = null output,
                    @DATALOADED bit = 0 output,
                    @PRIMARYCONTACTNAME nvarchar(700) = null output,
                    @PRIMARYCONTACTID uniqueidentifier = null output,
                    @DESCRIPTION nvarchar(300) = null output,
                    @GROUPCREATEDONDATE datetime = null output,
                    @GROUPINCLUDESMEMBERGIVING bit = null output,
                    @GIVINGBYGROUP money = null output,
                    @GIVINGBYGROUPMEMBERS money = null output,
                    @TOTALGIVING money = null output,
                    @ISHOUSEHOLD bit = null output,
                    @GIVESANONYMOUSLY bit = null output,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @GIVINGBYGROUPMEMBERSWITHGIFTAID money = null output,
                    @CURRENCYID uniqueidentifier = null output,
                    @ISINACTIVE bit = null output,
                    @ISDISSOLVED bit = null output,
                    @GROUPTYPE nvarchar(150) = null output,
                    @WEBADDRESS dbo.UDT_WEBADDRESS = null output
                )
                as
                begin
                    declare @SECURITYFEATUREID uniqueidentifier;
                    declare @SECURITYFEATURETYPE tinyint;
                    set @SECURITYFEATUREID = '12134504-ebca-47ac-8b13-b54f8a8c48d8';
                    set @SECURITYFEATURETYPE = 1;

                    declare @MULTICURRENCYENABLED bit;
                    set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
                    if @MULTICURRENCYENABLED = 1 set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

                    set nocount on;

                    -- households are always donors and include member giving, other types get looked up

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

                    declare @REVENUEFILTERID uniqueidentifier;
                    select @REVENUEFILTERID = ID from dbo.REVENUEFILTER where ISDEFAULT = 1;

                    exec dbo.USP_CONSTITUENT_REVENUESUMMARYEXPANDED
                      @CONSTITUENTID = @ID,
                      @ISGROUP = 1,
                      @HOUSEHOLDID = @ID,
                      @REVENUEFILTERID = @REVENUEFILTERID,
                      @STARTDATE = null,
                      @ENDDATE = null,
                      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
                      @SITEFILTERMODE = 0,
                      @SITESSELECTED = null,
                      @SECURITYFEATUREID = @SECURITYFEATUREID,
                      @SECURITYFEATURETYPE = @SECURITYFEATURETYPE,
                      @CURRENCYCODE = 3, --BASE

                      @TOTALNUMBER = null,
                      @TOTALAMOUNT = @TOTALGIVING output,
                      @TOTALAMOUNT_HOUSEHOLD = @GIVINGBYGROUPMEMBERS output,
                      @TOTALYEARS = null,
                      @CONSECUTIVEYEARS = null,
                      @GIVENSINCEFISCALYEAR = null,
                      @TOTALREVENUEWITHGIFTAID = @GIVINGBYGROUPMEMBERSWITHGIFTAID output,

                      @FIRSTID = null,
                      @FIRSTRECORDID = null,
                      @FIRSTDATE = null,
                      @FIRSTTYPECODE = null,
                      @FIRSTTYPE = null,
                      @FIRSTAMOUNT = null,
                      @LATESTID = null,
                      @LATESTRECORDID = null,
                      @LATESTDATE = null,
                      @LATESTTYPECODE = null,
                      @LATESTTYPE = null,
                      @LATESTAMOUNT = null,

                      @CURRENCYISOCURRENCYCODE = null,
                      @CURRENCYDECIMALDIGITS = 0,
                      @CURRENCYSYMBOL = null,
                      @CURRENCYSYMBOLDISPLAYSETTINGCODE = 0,
                      @ONLYGETSUMMARY = 1;

                    --@GIVINGBYGROUPMEMBERSWITHGIFTAID = case @MULTICURRENCYENABLED


                    select
                        @GROUPID = @ID,
                        @DATALOADED = 1,
                        @PRIMARYCONTACTNAME = GM_NF.NAME,
                        @PRIMARYCONTACTID = GM.MEMBERID,
                        @DESCRIPTION = G.DESCRIPTION,
                        @GROUPCREATEDONDATE = G.DATEADDED,
                        @GIVINGBYGROUP = @TOTALGIVING - COALESCE(@GIVINGBYGROUPMEMBERS, 0),
                        @ISHOUSEHOLD = 
                        case
                            when G.GROUPTYPECODE = 0 then 1
                            else 0
                        end,
                        @GIVESANONYMOUSLY = C.GIVESANONYMOUSLY,
                        @ISINACTIVE = C.ISINACTIVE,
                        @ISDISSOLVED = case when DISSOLVEDGROUP.ID is null then 0 else 1 end,
                        @WEBADDRESS = C.WEBADDRESS,
                        @GROUPTYPE = GROUPTYPE.NAME
                    from dbo.GROUPDATA G
                        inner join dbo.CONSTITUENT C on G.ID = C.ID
                        left join dbo.GROUPMEMBER GM on GM.GROUPID=@ID and GM.ISPRIMARY=1
                        left join dbo.DISSOLVEDGROUP on DISSOLVEDGROUP.ID = C.ID
                        left join dbo.GROUPTYPE on G.GROUPTYPEID = GROUPTYPE.ID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GM.MEMBERID) GM_NF
                    where G.ID = @ID;
                end