USP_DATAFORMTEMPLATE_EDITLOAD_EVENTTEAMHIERARCHY_CONFIG

The load procedure used by the edit dataform template "EventTeamHierarchyConfig Edit 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.
@INDIVIDUALSFUNDRAISE	bit	INOUT	Individual participants can fundraise
@HOUSEHOLDSFUNDRAISE	bit	INOUT	Households can fundraise
@HOUSEHOLDLEADERAPPOINTHOUSEHOLDCOLEADERS	tinyint	INOUT
@HOUSEHOLDSJOINTEAMS	bit	INOUT	Households can join teams
@TEAMSFUNDRAISE	bit	INOUT	Teams can fundraise
@TEAMSREQUESTFUNDRAISE	bit	INOUT	Event administrator must approve new teams
@TEAMNAMECHANGEAFTERREG	bit	INOUT	Team name can change after registration
@TEAMLEADERAPPOINTTEAMCOLEADERS	tinyint	INOUT
@COMPANIESFUNDRAISE	bit	INOUT	Companies can fundraise
@COMPANYTEAMSAPPROVEBYEVENTADMIN	bit	INOUT	Event administrator must approve new companies
@COMPANYLEADERAPPOINTCOMPANYCOLEADERS	tinyint	INOUT
@COMPANYNAMECHANGEAFTERREG	bit	INOUT	Company name can change after registration
@INDIVIDUALREGOPTIONS	int	INOUT	Individual reg options
@HOUSEHOLDREGOPTIONS	int	INOUT	Household reg options
@TEAMREGOPTIONS	int	INOUT	Team reg options
@COMPANYREGOPTIONS	int	INOUT	Company reg options
@HOUSEHOLDMEMBERSLIMIT	int	INOUT	Household members limit
@TEAMMEMBERSLIMIT	int	INOUT	Team members limit
@COMPANYTEAMSLIMIT	int	INOUT	Company teams limit
@COMPANYTEAMMEMBERSLIMIT	int	INOUT	Company team members limit
@PARTICIPANTSCANREACTIVATETEAMS	bit	INOUT	Participants can reactivate teams
@FORMERTEAMCANREACTIVATETEAMSCODE	tinyint	INOUT	Choice of team activation level
@PARTICIPANTSCANREACTIVATECOMPANYTEAMS	bit	INOUT	Participants can reactivate companies
@FORMERCOMPANYCANREACTIVATETEAMSCODE	tinyint	INOUT	Choice of company activation level
@PARTICIPANTSCANREACTIVATEHOUSEHOLDS	bit	INOUT	Participants can reactivate households
@FORMERHOUSEHOLDCANREACTIVATEHOUSEHOLDCODE	tinyint	INOUT	Choice of household team activation level
@HASPREVIOUSEVENTLINK	bit	INOUT	Has previous year event link

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_EVENTTEAMHIERARCHY_CONFIG(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
    @INDIVIDUALSFUNDRAISE bit = null output,
    @HOUSEHOLDSFUNDRAISE bit = null output,
    --@HOUSEHOLDAPPROVEBYEVENTADMIN bit = null output,
    @HOUSEHOLDLEADERAPPOINTHOUSEHOLDCOLEADERS tinyint = null output,
  @HOUSEHOLDSJOINTEAMS bit = null output,
    @TEAMSFUNDRAISE bit = null output,
    @TEAMSREQUESTFUNDRAISE bit = null output,
  @TEAMNAMECHANGEAFTERREG bit = null output,
    @TEAMLEADERAPPOINTTEAMCOLEADERS tinyint = null output,
  --@TEAMLEADERAPPROVETEAMMEMBERS tinyint = null output, -- use tinyint here since we want to use this data for an option list
    --@TEAMLEADEROPTIONAPPROVETEAMMEMBERS bit = null output,
    @COMPANIESFUNDRAISE bit = null output,
  @COMPANYTEAMSAPPROVEBYEVENTADMIN bit = null output,
    @COMPANYLEADERAPPOINTCOMPANYCOLEADERS tinyint = null output,
    --@COMPANYLEADERAPPROVETEAMS tinyint = null output, -- use tinyint here since we want to use this data for an option list
  @COMPANYNAMECHANGEAFTERREG bit = null output,
   @INDIVIDUALREGOPTIONS    int = null output,
 @HOUSEHOLDREGOPTIONS    int = null output,
 @TEAMREGOPTIONS    int = null output,
 @COMPANYREGOPTIONS    int = null output,

 @HOUSEHOLDMEMBERSLIMIT int= null output,
 @TEAMMEMBERSLIMIT int= null output,
 @COMPANYTEAMSLIMIT int= null output,
 @COMPANYTEAMMEMBERSLIMIT int= null output,
 @PARTICIPANTSCANREACTIVATETEAMS bit= null output,
 @FORMERTEAMCANREACTIVATETEAMSCODE tinyint= null output, 
 @PARTICIPANTSCANREACTIVATECOMPANYTEAMS bit= null output,
 @FORMERCOMPANYCANREACTIVATETEAMSCODE tinyint= null output,
 @PARTICIPANTSCANREACTIVATEHOUSEHOLDS bit= null output,
 @FORMERHOUSEHOLDCANREACTIVATEHOUSEHOLDCODE tinyint= null output,
 @HASPREVIOUSEVENTLINK bit = null output
)
as
    set nocount on;

  declare @HOUSEHOLDCOUNT int,
          @TEMMEMBERSCOUNT int,
          @COMPANYTEAMSCOUNT int,
          @COMPANYMEMBERSCOUNT int

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0
    set @TSLONG = 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.  Also note that we fetch the TSLONG so that concurrency
    -- can be considered.
    select
        @DATALOADED = 1,
        @TSLONG = EC.TSLONG,
        @INDIVIDUALSFUNDRAISE = EC.INDIVIDUALSFUNDRAISE,
      @HOUSEHOLDSFUNDRAISE = EC.HOUSEHOLDSFUNDRAISE,
      --@HOUSEHOLDAPPROVEBYEVENTADMIN = EC.HOUSEHOLDAPPROVEBYEVENTADMIN,
      @HOUSEHOLDLEADERAPPOINTHOUSEHOLDCOLEADERS = EC.HOUSEHOLDLEADERAPPOINTHOUSEHOLDCOLEADERS,
    @HOUSEHOLDSJOINTEAMS = EC.HOUSEHOLDSJOINTEAMS,
      @TEAMSFUNDRAISE = EC.TEAMSFUNDRAISE,
      @TEAMSREQUESTFUNDRAISE = EC.TEAMSREQUESTFUNDRAISE,
    @TEAMNAMECHANGEAFTERREG = EC.TEAMNAMECHANGEAFTERREG,
      @TEAMLEADERAPPOINTTEAMCOLEADERS = EC.TEAMLEADERAPPOINTTEAMCOLEADERS,
    --@TEAMLEADERAPPROVETEAMMEMBERS = CASE WHEN EC.TEAMLEADERAPPROVETEAMMEMBERS = 0 AND EC.TEAMLEADEROPTIONAPPROVETEAMMEMBERS = 0 THEN 2 WHEN EC.TEAMLEADERAPPROVETEAMMEMBERS = 1 THEN 1 ELSE 0 END,
      --@TEAMLEADEROPTIONAPPROVETEAMMEMBERS = EC.TEAMLEADEROPTIONAPPROVETEAMMEMBERS,
      @COMPANIESFUNDRAISE = EC.COMPANIESFUNDRAISE,
    @COMPANYTEAMSAPPROVEBYEVENTADMIN = EC.COMPANYTEAMSAPPROVEBYEVENTADMIN,
      @COMPANYLEADERAPPOINTCOMPANYCOLEADERS = EC.COMPANYLEADERAPPOINTCOMPANYCOLEADERS,
      --@COMPANYLEADERAPPROVETEAMS = CASE WHEN EC.COMPANYLEADERAPPROVETEAMS = 0 AND EC.COMPANYLEADEROPTIONAPPROVETEAMS = 0 THEN 2 WHEN EC.COMPANYLEADERAPPROVETEAMS = 1 THEN 1 ELSE 0 END,
    @COMPANYNAMECHANGEAFTERREG = EC.COMPANYNAMECHANGEAFTERREG,

      @HOUSEHOLDMEMBERSLIMIT= HOUSEHOLDMEMBERSLIMIT,
      @TEAMMEMBERSLIMIT= TEAMMEMBERSLIMIT,
      @COMPANYTEAMSLIMIT= COMPANYTEAMSLIMIT,
      @COMPANYTEAMMEMBERSLIMIT= COMPANYTEAMMEMBERSLIMIT,
      @PARTICIPANTSCANREACTIVATETEAMS= PARTICIPANTSCANREACTIVATETEAMS,
      @FORMERTEAMCANREACTIVATETEAMSCODE= FORMERTEAMCANREACTIVATETEAMSCODE,
      @PARTICIPANTSCANREACTIVATECOMPANYTEAMS= PARTICIPANTSCANREACTIVATECOMPANYTEAMS,
      @FORMERCOMPANYCANREACTIVATETEAMSCODE= FORMERCOMPANYCANREACTIVATETEAMSCODE,
    @PARTICIPANTSCANREACTIVATEHOUSEHOLDS = PARTICIPANTSCANREACTIVATEHOUSEHOLDS,
    @FORMERHOUSEHOLDCANREACTIVATEHOUSEHOLDCODE = FORMERHOUSEHOLDCANREACTIVATEHOUSEHOLDCODE


    from dbo.EVENTTEAMFAFCONFIG EC
    where EVENTID = @ID

  select @HOUSEHOLDCOUNT = MAXHOUSEHOLD,
         @TEMMEMBERSCOUNT = MAXTEAMMEMBERS, 
         @COMPANYTEAMSCOUNT = MAXCOMPANYTEAMS, 
         @COMPANYMEMBERSCOUNT = MAXCOMPANYMEMBERS
   from dbo.UFN_FAF_GROUPCOUNT_BY_EVENTID(@ID)

  select @INDIVIDUALREGOPTIONS =  max(CASE WHEN F.ISREGTYPEAVAILABLEINDIVIDUAL = 1 THEN 1 ELSE 0 END),
         @HOUSEHOLDREGOPTIONS = max(CASE WHEN (F.ISREGTYPEAVAILABLEFAMILYMEMBER= 1 or F.ISREGTYPEAVAILABLEHEADHOUSEHOLD= 1 or @HOUSEHOLDCOUNT > 0) THEN 1 else 0 END),
         @TEAMREGOPTIONS = max(CASE WHEN (F.ISREGTYPEAVAILABLETEAMLEADER= 1 or F.ISREGTYPEAVAILABLETEAMMEMBER= 1 or @TEMMEMBERSCOUNT > 0) THEN 1 else 0 END),
         @COMPANYREGOPTIONS = max(CASE WHEN F.ISREGTYPEAVAILABLECOMPANYLEADER= 1 or @COMPANYTEAMSCOUNT > 0 or @COMPANYMEMBERSCOUNT > 0 THEN 1 ELSE 0 END) 
  from dbo.EVENTPRICE E
  inner join dbo.FAFREGISTRATIONTYPE F
    on E.ID = F.EVENTPRICEID
  where E.EVENTID = @ID
  group by E.EVENTID


select @HASPREVIOUSEVENTLINK = case when PRIORYEAREVENTID is not null then 1 else 0 end from dbo.EVENTEXTENSION where EVENTID = @ID


    return 0;