USP_DATAFORMTEMPLATE_EDITLOAD_FAF_GROUP

The load procedure used by the edit dataform template "FAF Group Edit Data Form"

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.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@GROUPNAME	nvarchar(100)	INOUT	Group name
@APPURL	nvarchar(1024)	INOUT
@GROUPPAGEURL	nvarchar(100)	INOUT	Group page URL
@GROUPVANITYID	int	INOUT
@GROUPPAGEURLORIGINAL	nvarchar(100)	INOUT
@PARENTGROUPID	uniqueidentifier	INOUT	Parent group
@ORGANIZATIONID	uniqueidentifier	INOUT	Organization
@FUNDRAISINGGOAL	money	INOUT	Minimum fundraising goal
@TARGETFUNDRAISINGGOAL	money	INOUT	Target fundraising goal
@PARTICIPANTRECRUITMENTGOAL	int	INOUT	Number of participants to recruit
@DONORRETENTIONGOAL	decimal(5, 2)	INOUT	Percentage of donors to retain
@COMMUNICATIONGOAL	int	INOUT	Number of communications to send
@TEAMMEMBERSGOAL	int	INOUT	Number of team members
@TEAMMEMBERSRETENTIONGOAL	decimal(5, 2)	INOUT	Percentage of team members to retain
@TEAMSGOAL	int	INOUT	Number of teams
@TEAMRETENTIONGOAL	decimal(5, 2)	INOUT	Percentage of teams to retain
@ROLE	nvarchar(20)	INOUT	Role name
@EVENTID	uniqueidentifier	INOUT	EventID
@CLIENTSITESID	int	INOUT	Client Sites ID

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_FAF_GROUP(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
      @GROUPNAME nvarchar(100) = null output,
      @APPURL nvarchar(1024) = null output,
      @GROUPPAGEURL nvarchar(100) = null output,
    @GROUPVANITYID int = null output,
    @GROUPPAGEURLORIGINAL nvarchar(100) = null output,
    @PARENTGROUPID uniqueidentifier = null output,
    @ORGANIZATIONID uniqueidentifier = null output,

    @FUNDRAISINGGOAL money = null output,
    @TARGETFUNDRAISINGGOAL money = null output,
    @PARTICIPANTRECRUITMENTGOAL int = null output,
    @DONORRETENTIONGOAL decimal(5,2) = null output,
    @COMMUNICATIONGOAL int = null output,    
    @TEAMMEMBERSGOAL int = null output,
      @TEAMMEMBERSRETENTIONGOAL decimal(5,2) = null output,
      @TEAMSGOAL int = null output,
    @TEAMRETENTIONGOAL decimal(5,2) = null output,    
      @ROLE nvarchar(20) = null output,
      @EVENTID uniqueidentifier = null output,
    @CLIENTSITESID int = null output
)
as

    set nocount on;

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

      DECLARE @APPEALID uniqueidentifier
        DECLARE @RELATIONSHIPTYPECODEID uniqueidentifier
        DECLARE @GROUPTYPECODE tinyint

        select @EVENTID = EVENT.ID, @APPEALID = EVENT.APPEALID 
        FROM dbo.TEAMFUNDRAISINGTEAM 
        join dbo.EVENT on TEAMFUNDRAISINGTEAM.APPEALID = EVENT.APPEALID
        where TEAMFUNDRAISINGTEAM.ID = @ID

      SELECT @RELATIONSHIPTYPECODEID = ID from dbo.RELATIONSHIPTYPECODE (NOLOCK) WHERE DESCRIPTION = 'FAF Organization'

    -- 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.  Also note that we fetch the TSLONG so that concurrency
    -- can be considered.
        select @DATALOADED = 1,
          @GROUPNAME = TFT.NAME,         
          @PARENTGROUPID = PT.ID, 
          @ORGANIZATIONID  = C.ID,
          @FUNDRAISINGGOAL =  TFT.[GOAL],
          @TEAMMEMBERSGOAL = TE.TEAMRECRUITMENTGOAL,
          @TEAMRETENTIONGOAL  = TE.PCTTEAMSRETENSION * 100,
          @TEAMMEMBERSGOAL  = TE.TEAMMEMBERGOAL,
          @TEAMMEMBERSRETENTIONGOAL  = TE.PCTTEAMMEMBERRETENSION * 100,   
          @PARTICIPANTRECRUITMENTGOAL = TE.PARTICIPANTGOAL,
          @COMMUNICATIONGOAL  = TE.COMMUNICATIONGOAL,
          @TARGETFUNDRAISINGGOAL = TE.TARGETFUNDRAISINGGOAL,
          @DONORRETENTIONGOAL  =   TE.DONORRETENTIONGOAL * 100,
          @GROUPTYPECODE = TE.TYPECODE,
          @ROLE = TE.TYPE

      from  dbo.TEAMFUNDRAISINGTEAM TFT (NOLOCK)
    join dbo.TEAMEXTENSION TE (NOLOCK) on TFT.ID = TE.TEAMFUNDRAISINGTEAMID
    LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAM PT (NOLOCK) on PT.ID = TFT.PARENTTEAMID
    LEFT OUTER JOIN dbo.RELATIONSHIP R (NOLOCK) 
            on R.RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID and RECIPROCALCONSTITUENTID = TE.TEAMCONSTITUENTID
      LEFT OUTER JOIN dbo.CONSTITUENT C (NOLOCK)
            on C.ID = R.RELATIONSHIPCONSTITUENTID
      where TFT.ID = @ID

    select @APPURL = Value, @CLIENTSITESID = CLIENTSITESID from CMSSITESETTING where CLIENTSITESID in (select CLIENTSITESID from dbo.EVENTEXTENSION where EVENTID = @EVENTID) and ENUMID = 11

    select @GROUPPAGEURL = VanityURL, @GROUPPAGEURLORIGINAL = VanityURL, @GROUPVANITYID = vURL.ID from VanityURL vURL
          join FAFFRIENDLYURLPARAMS vFAF on vFAF.SEQUENCEID = vURL.RealmID and vURL.RealmTypeID = 7 and vFAF.PAGETYPE = @GROUPTYPECODE
          where PARTICIPANTID = @ID
    return 0;