USP_DATAFORMTEMPLATE_VIEW_REGISTRANT_REGISTRATION

The load procedure used by the view dataform template "FAF Registrant Registration 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.
@REGISTRANTNAME	nvarchar(100)	INOUT	Registrant name
@PERSONALPAGEURL	nvarchar(max)	INOUT	Personal page URL
@GROUPPAGEURL	nvarchar(max)	INOUT	Group page URL
@FUNDRAISINGROLENAME	nvarchar(100)	INOUT	Fundraising role name
@GROUPNAME	nvarchar(100)	INOUT	Fundraising group
@LEADERS	nvarchar(200)	INOUT	Leaders
@REGISTRATIONOPTIONNAME	nvarchar(100)	INOUT	Registration option name
@REGISTRATIONFEE	money	INOUT	Registration fee
@REGISTRATIONFEEWAIVED	bit	INOUT	Registration fee waived
@BENEFITS	nvarchar(200)	INOUT	Benefits
@BENEFITSWAIVED	bit	INOUT	Benefits waived
@MINIMUMFUNDRAISING	money	INOUT	Minimum fundraising goal
@TARGETFUNDRAISING	money	INOUT	Target fundraising goal
@PARTICIPANTSTORECRUIT	int	INOUT	Number of participants to recruit
@DONORRETENTIONGOAL	decimal(9, 2)	INOUT	Percentage of donors to retain
@COMMUNICATIONGOAL	int	INOUT	Number of communications to send
@GROUPTOTALFUNDRAISING	money	INOUT	Fundraising total amount
@GROUPPARTICIPANTRECRUITMENT	int	INOUT	Number of participants to recruit
@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
@ROLE	nvarchar(20)	INOUT	Role name
@EVENTID	uniqueidentifier	INOUT	EventID
@PARENTGROUPNAME	nvarchar(100)	INOUT	Parent fundraising group
@ORGANIZATIONCONSTITUENTNAME	nvarchar(100)	INOUT	organization
@TEAMFUNDRAISINGTEAMID	uniqueidentifier	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REGISTRANT_REGISTRATION
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @REGISTRANTNAME nvarchar(100) = null output,
    @PERSONALPAGEURL  nvarchar(max) = null output,
    @GROUPPAGEURL nvarchar(max) = null output,
    @FUNDRAISINGROLENAME nvarchar(100) = null output, 
    @GROUPNAME nvarchar(100) = null output, 
    @LEADERS nvarchar(200) = null output,
    @REGISTRATIONOPTIONNAME nvarchar(100) = null output,  
    @REGISTRATIONFEE money = null output,
    @REGISTRATIONFEEWAIVED bit = null output, 
    @BENEFITS nvarchar(200) = null output, 
    @BENEFITSWAIVED bit = null output,  
    @MINIMUMFUNDRAISING money = null output, 
    @TARGETFUNDRAISING money = null output,
    @PARTICIPANTSTORECRUIT int = null output, 
    @DONORRETENTIONGOAL decimal(9,2) = null output, 
    @COMMUNICATIONGOAL int = null output,  
    @GROUPTOTALFUNDRAISING money = null output, 
    @GROUPPARTICIPANTRECRUITMENT int = null output,   
    --@GROUPDONORRETENTION 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,  
    @ROLE nvarchar(20) = null output,
    @EVENTID uniqueidentifier = null output ,
    @PARENTGROUPNAME nvarchar(100) = null output,
    @ORGANIZATIONCONSTITUENTNAME nvarchar(100) = null output,
  @TEAMFUNDRAISINGTEAMID uniqueidentifier = null output

)
as
    set nocount on;

    DECLARE @APPEALID uniqueidentifier
      DECLARE @CONSTITUENTID uniqueidentifier

      SET @DATALOADED = 0;      

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

      SELECT @DATALOADED = 1,
              @REGISTRANTNAME = C.NAME,
              @FUNDRAISINGROLENAME = RL.[ROLE],
              @ROLE = RL.[ROLE],
              @REGISTRATIONOPTIONNAME = EP.NAME,
              @REGISTRATIONFEE = RR.AMOUNT,
              @REGISTRATIONFEEWAIVED = MAX(CASE WHEN EP.AMOUNT = 0 AND RR.AMOUNT = 0 THEN 1 ELSE 0 END),
              @BENEFITS =       dbo.UDA_BUILDLIST(dbo.UFN_BENEFIT_GETNAME(RB.BENEFITID)) ,
              @BENEFITSWAIVED = R.BENEFITSWAIVED,
              @MINIMUMFUNDRAISING = RE.FUNDRAISINGGOAL,
              @TARGETFUNDRAISING = RE.TARGETFUNDRAISINGGOAL,
              @PARTICIPANTSTORECRUIT = RE.MEMBERECRUITMENTGOAL,
              @DONORRETENTIONGOAL = RE.DONORRETENTIONGOAL * 100 ,
              @COMMUNICATIONGOAL = RE.COMMUNICATIONGOAL,
              @CONSTITUENTID = C.ID,
              @TEAMFUNDRAISINGTEAMID = RL.TEAMFUNDRAISINGTEAMID,
              @ORGANIZATIONCONSTITUENTNAME = IC.NAME                              
      FROM dbo.REGISTRANT R 
      INNER JOIN dbo.CONSTITUENT C 
            ON R.CONSTITUENTID = C.ID
      INNER JOIN dbo.REGISTRANTEXTENSION RE 
            ON RE.REGISTRANTID = R.ID
      INNER JOIN dbo.REGISTRANTREGISTRATION RR 
            ON RR.REGISTRANTID = R.ID
      INNER JOIN dbo.EVENTPRICE EP 
            ON RR.EVENTPRICEID = EP.ID
      INNER JOIN dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, NULL) RL
            ON RL.REGISTRANTID = R.ID
      LEFT OUTER JOIN REGISTRANTBENEFIT RB 
            ON RB.REGISTRANTID = R.ID and RB.BENEFITTYPECODE = 0 --only show registration option benefits 
      LEFT OUTER JOIN dbo.CONSTITUENT IC(nolock)
            ON IC.ID = RE.ORGANIZATIONCONSTITUENTID
      WHERE R.ID = @ID     
     group by C.name,C.ID,RL.TEAMFUNDRAISINGTEAMID,
               RL.[ROLE],EP.NAME,RR.AMOUNT,R.BENEFITSWAIVED,RE.FUNDRAISINGGOAL,RE.TARGETFUNDRAISINGGOAL,RE.MEMBERECRUITMENTGOAL,
              RE.COMMUNICATIONGOAL,EP.AMOUNT,RE.DONORRETENTIONGOAL,IC.NAME




      SELECT @LEADERS = dbo.UDA_BUILDLIST(C.NAME) 
      FROM dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC 
      LEFT OUTER JOIN dbo.CONSTITUENT C  
                ON C.ID = TC.CONSTITUENTID
      WHERE TC.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID
      GROUP BY TC.TEAMFUNDRAISINGTEAMID


       DECLARE @CURRENTGROUPTYPE tinyint

      SELECT @GROUPNAME = T.NAME,
            @GROUPTOTALFUNDRAISING = T.GOAL,
            @GROUPPARTICIPANTRECRUITMENT = TE.PARTICIPANTGOAL,
           -- @GROUPDONORRETENTION = TE.DONORRETENTIONGOAL * 100,
            @GROUPCOMMUNICATIONGOAL = TE.COMMUNICATIONGOAL,            
            @GROUPTEAMMEMBERSGOAL =  TE.TEAMMEMBERGOAL,
            @GROUPTEAMMEMBERSRETENTIONGOAL = TE.PCTTEAMMEMBERRETENSION * 100,
            @GROUPTEAMSGOAL = TE.TEAMRECRUITMENTGOAL,
            @GROUPTEAMSRETENTIONGOAL = TE.PCTTEAMSRETENSION * 100,
            @CURRENTGROUPTYPE = TE.TYPECODE,
            @PARENTGROUPNAME = PT.NAME

      FROM dbo.TEAMFUNDRAISINGTEAM T 
      INNER JOIN dbo.TEAMEXTENSION TE ON T.ID = TE.TEAMFUNDRAISINGTEAMID
      LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAM PT (NOLOCK) on PT.ID = T.PARENTTEAMID
      WHERE T.ID = @TEAMFUNDRAISINGTEAMID


      -- IF @ROLE = 'Head of household'
      --  SELECT @GROUPNAME = T.NAME,
      --     @MINIMUMFUNDRAISING = T.GOAL,
      --     @PARTICIPANTSTORECRUIT = TE.PARTICIPANTGOAL,
      --     @DONORRETENTIONGOAL = TE.DONORRETENTIONGOAL * 100,
      --     @COMMUNICATIONGOAL = TE.COMMUNICATIONGOAL,
      --     @TARGETFUNDRAISING = TE.TARGETFUNDRAISINGGOAL

      --FROM dbo.TEAMFUNDRAISINGTEAM T (NOLOCK)
      -- INNER JOIN dbo.TEAMEXTENSION TE (NOLOCK)
      --ON T.ID = TE.TEAMFUNDRAISINGTEAMID
      --WHERE T.ID = @TEAMFUNDRAISINGTEAMID

         set @PERSONALPAGEURL = dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(@ID, @EVENTID, 0)
          set @GROUPPAGEURL = dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(@TEAMFUNDRAISINGTEAMID, @EVENTID, @CURRENTGROUPTYPE)
      return 0;