USP_DATAFORMTEMPLATE_VIEW_REGISTRANT_GOALS

The load procedure used by the view dataform template "Event Registrant Goals View 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.
@FUNDRAISINGGOAL	money	INOUT	Minimum fundraising goal
@TARGETFUNDRAISINGGOAL	money	INOUT	Target fundraising goal
@MEMBERECRUITMENTGOAL	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
@VOLUNTEERRECRUITMENTGOAL	int	INOUT	Number of volunteers to recruit
@OTHERUNITGOAL	int	INOUT	Number of Other Goal
@PROGRESSFUNDRAISINGGOAL	money	INOUT	Total Fundraising
@PROGRESSMEMBERECRUITMENTGOAL	int	INOUT	Number of participants recruited
@PROGRESSDONORRETENTIONGOAL	decimal(5, 2)	INOUT	Percentage of donors retained
@PROGRESSCOMMUNICATIONGOAL	int	INOUT	Number of communications sent

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REGISTRANT_GOALS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @FUNDRAISINGGOAL money = null output,
    @TARGETFUNDRAISINGGOAL money = null output,
    @MEMBERECRUITMENTGOAL int = null output,
    @DONORRETENTIONGOAL decimal(5,2) = null output,
    @COMMUNICATIONGOAL int = null output,
    @VOLUNTEERRECRUITMENTGOAL int = null output,
    @OTHERUNITGOAL int = null output,

    @PROGRESSFUNDRAISINGGOAL money = null output,
    @PROGRESSMEMBERECRUITMENTGOAL int = null output,
    @PROGRESSDONORRETENTIONGOAL decimal(5,2) = null output,
    @PROGRESSCOMMUNICATIONGOAL int = null output
)
as
    set nocount on;

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

    -- 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, 
        @FUNDRAISINGGOAL = FUNDRAISINGGOAL,          
        @MEMBERECRUITMENTGOAL = MEMBERECRUITMENTGOAL, 
        @COMMUNICATIONGOAL = COMMUNICATIONGOAL, 
        @VOLUNTEERRECRUITMENTGOAL = VOLUNTEERRECRUITMENTGOAL,
        @TARGETFUNDRAISINGGOAL = TARGETFUNDRAISINGGOAL,
        @DONORRETENTIONGOAL = DONORRETENTIONGOAL * 100,
        @OTHERUNITGOAL = OTHERUNITGOAL,
        @PROGRESSFUNDRAISINGGOAL = dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(R.ID, R.EVENTID),
        @PROGRESSMEMBERECRUITMENTGOAL = dbo.UFN_PARTICIPANT_RECRUITMENT_COUNT(R.CONSTITUENTID, R.EVENTID),
        @PROGRESSDONORRETENTIONGOAL = cast((select RetentionRate * 100 from dbo.UFN_PARTICIPANT_DONOR_RETENTION(R.CONSTITUENTID, R.EVENTID)) as decimal(5,2)),
        @PROGRESSCOMMUNICATIONGOAL = dbo.UFN_FAFGETTOTALCOMMUNICATIONS(R.EVENTID, dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(R.CONSTITUENTID)) + (select count(ID) from FAFEVENTSOCIALNETWORKPOST where REGISTRANTID=R.ID)
    FROM dbo.REGISTRANT R
    INNER JOIN dbo.REGISTRANTEXTENSION RE ON RE.REGISTRANTID = R.ID
    WHERE R.ID = @ID

    return 0;