USP_DATAFORMTEMPLATE_EDITLOAD_REGISTRANT_REGISTRATION

Load current selected registrant registration.

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.
@CONSTITUENTID	uniqueidentifier	INOUT	Registrant name
@CONSTITUENTIDORIGINAL	uniqueidentifier	INOUT
@APPURL	nvarchar(1024)	INOUT
@PERSONALPAGEURL	nvarchar(100)	INOUT	Personal page URL
@PERSONALVANITYID	int	INOUT
@PERSONALPAGEURLORIGINAL	nvarchar(100)	INOUT
@GROUPPAGEURL	nvarchar(100)	INOUT	Group page URL
@GROUPVANITYID	int	INOUT
@GROUPPAGEURLORIGINAL	nvarchar(100)	INOUT
@ROLECODE	tinyint	INOUT	Fundraising role name
@ROLECODEORIGINAL	tinyint	INOUT
@GROUPID	uniqueidentifier	INOUT	Group name
@GROUPIDORIGINAL	uniqueidentifier	INOUT
@LEADERS	nvarchar(200)	INOUT	Leaders
@REGISTRATIONOPTIONID	uniqueidentifier	INOUT	Registration option name and description
@REGISTRATIONOPTIONIDORIGINAL	uniqueidentifier	INOUT
@FEE	money	INOUT	Registration fee
@WAIVEREGISTRATIONFEE	bit	INOUT	Waive registration fee
@BENEFITS	nvarchar(200)	INOUT	Benefits
@WAIVEBENEFITS	bit	INOUT	Waive benefits
@FUNDRAISINGGOAL	money	INOUT	Minimum fundraising goal
@TARGETFUNDRAISINGGOAL	money	INOUT	Target fundraising goal
@PARTICIPANTRECRUITGOAL	int	INOUT	Number of participants to recruit
@DONORRETENTIONGOAL	decimal(7, 2)	INOUT	Percentage of donors to retain
@COMMUNICATIONGOAL	int	INOUT	Number of communications to send
@GROUPFUNDRAISINGGOAL	money	INOUT	Minimum fundraising goal
@GROUPPARTICIPANTRECRUITMENTGOAL	int	INOUT	Number of participants to recruit
@GROUPDONORRETENTIONGOAL	decimal(7, 2)	INOUT	Percentage of donors to retain
@GROUPTEAMMEMBERSGOAL	int	INOUT	Number of Team members
@GROUPTEAMMEMBERSRETENTIONGOAL	decimal(7, 2)	INOUT	Percentage of team members to retain
@GROUPTEAMSGOAL	int	INOUT	Number of Teams
@GROUPTEAMSRETENTIONGOAL	decimal(7, 2)	INOUT	Percentage of teams to retain
@GROUPCOMMUNICATIONGOAL	int	INOUT	Number of communications to send
@ROLE	nvarchar(20)	INOUT	Role name
@EVENTID	uniqueidentifier	INOUT	EventID
@CLIENTSITESID	int	INOUT
@ORGANIZATIONCONSTITUENTID	uniqueidentifier	INOUT
@PARENTGROUPID	uniqueidentifier	INOUT
@PARENTGROUPIDORIGINAL	uniqueidentifier	INOUT
@APPEALID	uniqueidentifier	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_REGISTRANT_REGISTRATION(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
      @CONSTITUENTID uniqueidentifier = null output,
    @CONSTITUENTIDORIGINAL uniqueidentifier = null output,    
    @APPURL nvarchar(1024) = null output,
      @PERSONALPAGEURL nvarchar(100) = null output,
    @PERSONALVANITYID int = null output,
    @PERSONALPAGEURLORIGINAL nvarchar(100) = null output,
      @GROUPPAGEURL nvarchar(100) = null output,
    @GROUPVANITYID int = null output,
    @GROUPPAGEURLORIGINAL nvarchar(100) = null output,
      @ROLECODE tinyint = null output,
    @ROLECODEORIGINAL tinyint = null output,
      @GROUPID uniqueidentifier = null output,
    @GROUPIDORIGINAL uniqueidentifier = null output,
      @LEADERS nvarchar(200) = null output,
      @REGISTRATIONOPTIONID uniqueidentifier = null output,
    @REGISTRATIONOPTIONIDORIGINAL uniqueidentifier = null output,
      @FEE money = null output,
      @WAIVEREGISTRATIONFEE bit= null output,
      @BENEFITS nvarchar(200) = null output, 
      @WAIVEBENEFITS bit= null output,

      @FUNDRAISINGGOAL money = null output,
      @TARGETFUNDRAISINGGOAL money = null output,
      @PARTICIPANTRECRUITGOAL int = null output,
      @DONORRETENTIONGOAL decimal(7,2) = null output,
      @COMMUNICATIONGOAL int = null output,
      @GROUPFUNDRAISINGGOAL money = null output, 
      @GROUPPARTICIPANTRECRUITMENTGOAL int = null output, 
      @GROUPDONORRETENTIONGOAL decimal(7,2) = null output,
    @GROUPTEAMMEMBERSGOAL int = null output,
      @GROUPTEAMMEMBERSRETENTIONGOAL decimal(7,2) = null output,
      @GROUPTEAMSGOAL int = null output,
      @GROUPTEAMSRETENTIONGOAL decimal(7,2) = null output,
      @GROUPCOMMUNICATIONGOAL int = null output,
      @ROLE nvarchar(20) = null output,
      @EVENTID uniqueidentifier = null output,
    @CLIENTSITESID int = null output,
    @ORGANIZATIONCONSTITUENTID uniqueidentifier = null output,
    @PARENTGROUPID uniqueidentifier = null output,
    @PARENTGROUPIDORIGINAL uniqueidentifier = null output,
    @APPEALID uniqueidentifier = 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 @TEAMFUNDRAISINGTEAMID uniqueidentifier,
          @GROUPTYPECODE tinyint

      SELECT @EVENTID = R.EVENTID FROM dbo.REGISTRANT R WHERE R.ID = @ID   
      select @APPEALID = APPEALID from dbo.EVENT where ID = @EVENTID

      SELECT @DATALOADED = 1,   
        @TSLONG = R.TSLONG,
        @CONSTITUENTID = C.ID,
        @CONSTITUENTIDORIGINAL =  C.ID,
        @ROLECODE = CASE 
                  WHEN RL.[ROLE] =  'Company leader' THEN 0
                  WHEN RL.[ROLE] =  'Team leader' THEN 1
                  WHEN RL.[ROLE] =  'Team member' THEN 2
                  WHEN RL.[ROLE] =  'Head of household' THEN 3
                  WHEN RL.[ROLE] =  'Household member' THEN 4
                  WHEN RL.[ROLE] =  'Individual' THEN 5
                       END,
         @ROLECODEORIGINAL = @ROLECODE,
        @ROLE = RL.[ROLE],
         @REGISTRATIONOPTIONID = EP.ID,
         @REGISTRATIONOPTIONIDORIGINAL = EP.ID,
         @FEE = RR.AMOUNT,
         @WAIVEREGISTRATIONFEE = (CASE WHEN EP.AMOUNT = 0 AND RR.AMOUNT = 0 THEN 1 ELSE 0 END),
         @BENEFITS = RB.BENEFITS,
         @WAIVEBENEFITS = R.BENEFITSWAIVED,
         @FUNDRAISINGGOAL = RE.FUNDRAISINGGOAL,
         @TARGETFUNDRAISINGGOAL = RE.TARGETFUNDRAISINGGOAL,
         @PARTICIPANTRECRUITGOAL = RE.MEMBERECRUITMENTGOAL,
         @DONORRETENTIONGOAL = RE.DONORRETENTIONGOAL * 100,
         @COMMUNICATIONGOAL = RE.COMMUNICATIONGOAL,
         @TEAMFUNDRAISINGTEAMID = RL.TEAMFUNDRAISINGTEAMID,
         @ORGANIZATIONCONSTITUENTID = RE.ORGANIZATIONCONSTITUENTID

    FROM dbo.REGISTRANT R 
    join dbo.CONSTITUENT C ON R.CONSTITUENTID = C.ID
    join dbo.REGISTRANTEXTENSION RE ON RE.REGISTRANTID = R.ID
    join dbo.REGISTRANTREGISTRATION RR ON RR.REGISTRANTID = R.ID
    join dbo.EVENTPRICE EP ON RR.EVENTPRICEID = EP.ID
    join dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, NULL) RL ON RL.REGISTRANTID = R.ID
    left join (select RB.REGISTRANTID, dbo.UDA_BUILDLIST(B.NAME) as BENEFITS 
              from REGISTRANTBENEFIT RB 
              left join BENEFIT B on RB.BENEFITID = B.ID 
              where RB.REGISTRANTID = @ID and RB.BENEFITTYPECODE = 0  --only show registration option benefits 

              group by RB.REGISTRANTID) RB ON RB.REGISTRANTID = R.ID 
    WHERE R.ID = @ID

    SELECT @GROUPID = T.ID,
           @GROUPIDORIGINAL = T.ID,
           @GROUPFUNDRAISINGGOAL = T.GOAL,
           @GROUPPARTICIPANTRECRUITMENTGOAL = TE.PARTICIPANTGOAL,
           @GROUPDONORRETENTIONGOAL = TE.DONORRETENTIONGOAL * 100,
           @GROUPCOMMUNICATIONGOAL = TE.COMMUNICATIONGOAL,
           @GROUPTEAMMEMBERSGOAL =  TE.TEAMMEMBERGOAL,
           @GROUPTEAMMEMBERSRETENTIONGOAL = TE.PCTTEAMMEMBERRETENSION * 100,
           @GROUPTEAMSGOAL = TE.TEAMRECRUITMENTGOAL,
           @GROUPTEAMSRETENTIONGOAL = TE.PCTTEAMSRETENSION * 100,
           @LEADERS = TFTCs.LEADER,
           @GROUPTYPECODE = TE.TYPECODE,
           @PARENTGROUPID = T.PARENTTEAMID,
           @PARENTGROUPIDORIGINAL = T.PARENTTEAMID
      FROM dbo.TEAMFUNDRAISINGTEAM T 
       join dbo.TEAMEXTENSION TE ON T.ID = TE.TEAMFUNDRAISINGTEAMID
       left join (select dbo.UDA_BUILDLIST(C.NAME) as LEADER, iTX.TEAMFUNDRAISINGTEAMID from TEAMEXTENSION iTX
                    left join TEAMFUNDRAISINGTEAMCAPTAIN tftc on iTX.TEAMFUNDRAISINGTEAMID = tftc.TEAMFUNDRAISINGTEAMID
                    left join CONSTITUENT c on tftc.CONSTITUENTID = c.ID
                  group by iTX.TEAMFUNDRAISINGTEAMID
                    ) TFTCs on TFTCs.TEAMFUNDRAISINGTEAMID = T.ID    
      WHERE T.ID = @TEAMFUNDRAISINGTEAMID

   --IF @ROLECODE = 3

      --  SELECT @GROUPID = T.ID,

      --     @FUNDRAISINGGOAL = T.GOAL,

      --     @PARTICIPANTRECRUITGOAL = TE.PARTICIPANTGOAL,

      --     @DONORRETENTIONGOAL = TE.DONORRETENTIONGOAL * 100,

      --     @COMMUNICATIONGOAL = TE.COMMUNICATIONGOAL,

      --     @TARGETFUNDRAISINGGOAL = TE.TARGETFUNDRAISINGGOAL


      --FROM dbo.TEAMFUNDRAISINGTEAM T (NOLOCK)

      -- INNER JOIN dbo.TEAMEXTENSION TE (NOLOCK)

      --ON T.ID = TE.TEAMFUNDRAISINGTEAMID

      --WHERE T.ID = @TEAMFUNDRAISINGTEAM




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

      select @PERSONALPAGEURL = VanityURL, @PERSONALPAGEURLORIGINAL = VanityURL, @PERSONALVANITYID = vURL.ID from VanityURL vURL
      join FAFFRIENDLYURLPARAMS vFAF on vFAF.SEQUENCEID = vURL.RealmID and vURL.RealmTypeID = 7 and vFAF.PAGETYPE = 0
      where PARTICIPANTID = @ID

      if @TEAMFUNDRAISINGTEAMID is not null
        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 = @TEAMFUNDRAISINGTEAMID

    return 0;