USP_DATAFORMTEMPLATE_VIEW_FAFGROUPREGISTRATION

The load procedure used by the view dataform template "Friends Asking Friends group information"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@EVENTID	uniqueidentifier	INOUT	EVENTID
@GROUPPAGEURL	nvarchar(max)	INOUT	Group page URL
@TARGETFUNDRAISING	money	INOUT	Target fundraising goal
@GROUPTOTALFUNDRAISING	money	INOUT	Fundraising total amount
@GROUPPARTICIPANTRECRUITMENT	int	INOUT	Number of participants to recruit
@DONORRETENTIONGOAL	decimal(9, 2)	INOUT	Percentage of donors to retain
@GROUPCOMMUNICATIONGOAL	int	INOUT	Number of communications to send
@GROUPTEAMMEMBERSGOAL	int	INOUT	Number of team members
@GROUPTEAMMEMBERSRETENTIONGOAL	decimal(9, 2)	INOUT	Percentage of team members to retain
@GROUPTEAMSGOAL	int	INOUT	Number of teams
@GROUPTEAMSRETENTIONGOAL	decimal(9, 2)	INOUT	Percentage of teams to retain
@PARENTGROUPNAME	nvarchar(max)	INOUT	Parent fundraising group
@GROUPTYPECODE	tinyint	INOUT	GROUPTYPECODE
@GROUPTYPENAME	nvarchar(100)	INOUT
@GROUPCONSTITUENTNAME	nvarchar(300)	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FAFGROUPREGISTRATION
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
  @EVENTID uniqueidentifier = null output,
    @GROUPPAGEURL nvarchar(max) = null output,
    @TARGETFUNDRAISING money = null output,
    @GROUPTOTALFUNDRAISING money = null output, 
    @GROUPPARTICIPANTRECRUITMENT int = null output,   
  @DONORRETENTIONGOAL decimal(9,2) = null output, 
    @GROUPCOMMUNICATIONGOAL int = null output, 
    @GROUPTEAMMEMBERSGOAL int = null output,
    @GROUPTEAMMEMBERSRETENTIONGOAL decimal(9,2) = null output,
    @GROUPTEAMSGOAL int = null output,
    @GROUPTEAMSRETENTIONGOAL decimal(9,2) = null output,
    @PARENTGROUPNAME nvarchar(max) = null output,
  @GROUPTYPECODE tinyint = null output,
  @GROUPTYPENAME nvarchar(100) = null output,
  @GROUPCONSTITUENTNAME nvarchar(300) = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

  declare @NFGCAMPAIGNLEVELID uniqueidentifier = null

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.
    SELECT @DATALOADED = 1,
        @EVENTID = TE.EVENTID,
        @GROUPTOTALFUNDRAISING = T.GOAL,
        @GROUPPARTICIPANTRECRUITMENT = TE.PARTICIPANTGOAL,
        @GROUPCOMMUNICATIONGOAL = TE.COMMUNICATIONGOAL,            
        @GROUPTEAMMEMBERSGOAL =  TE.TEAMMEMBERGOAL,
        @GROUPTEAMMEMBERSRETENTIONGOAL = TE.PCTTEAMMEMBERRETENSION * 100,
        @GROUPTEAMSGOAL = TE.TEAMRECRUITMENTGOAL,
        @GROUPTEAMSRETENTIONGOAL = TE.PCTTEAMSRETENSION * 100,
        @GROUPTYPECODE = TE.TYPECODE,
        @PARENTGROUPNAME = PT.NAME,
        @TARGETFUNDRAISING = TE.TARGETFUNDRAISINGGOAL,
        @DONORRETENTIONGOAL = TE.DONORRETENTIONGOAL * 100,
        @NFGCAMPAIGNLEVELID = TE.NFGCAMPAIGNLEVELID,
        @GROUPTYPENAME = TE.TYPE,
        @GROUPCONSTITUENTNAME = C.KEYNAME
      FROM dbo.TEAMFUNDRAISINGTEAM T 
      INNER JOIN dbo.TEAMEXTENSION TE ON T.ID = TE.TEAMFUNDRAISINGTEAMID
      INNER JOIN dbo.CONSTITUENT C on TE.TEAMCONSTITUENTID = C.ID
      LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAM PT (NOLOCK) on PT.ID = T.PARENTTEAMID
      WHERE T.ID = @ID

      If @NFGCAMPAIGNLEVELID is not null begin
        SELECT @PARENTGROUPNAME = FNC.NAME + 
          CASE WHEN HIERARCHYPATH.GetLevel() > 1 THEN '/' + dbo.UFN_NFGLEVEL_GETFULLPATHNAME(FNCL.ID, '/', 0) ELSE '' END
          FROM FAFNFGCAMPAIGNLEVEL FNCL 
          INNER JOIN FAFNFGCAMPAIGN FNC ON FNC.ID = FNCL.NFGCAMPAIGNID
          WHERE FNCL.ID=@NFGCAMPAIGNLEVELID
      end

  set @GROUPPAGEURL = dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(@ID, @EVENTID, @GROUPTYPECODE)

    return 0;