USP_GLOBALCHANGE_ADDORCHANGEEVENTREGISTRATIONOPTION
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEEVENT | uniqueidentifier | IN | |
| @SOURCEREGISTRATIONOPTION | uniqueidentifier | IN | |
| @SELECTEDEVENTSFORUPDATE | xml | IN | |
| @SELECTEDREGISTRATIONOPTIONSFORUPDATE | xml | IN | |
| @SELECTIONOPTIONFORUPDATE | smallint | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @ASOF | datetime | IN | |
| @NUMBERADDED | int | INOUT | |
| @NUMBEREDITED | int | INOUT | |
| @NUMBERDELETED | int | INOUT | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_GLOBALCHANGE_ADDORCHANGEEVENTREGISTRATIONOPTION
(
@SOURCEEVENT uniqueidentifier,
@SOURCEREGISTRATIONOPTION uniqueidentifier,
@SELECTEDEVENTSFORUPDATE xml = null,
@SELECTEDREGISTRATIONOPTIONSFORUPDATE xml = null,
@SELECTIONOPTIONFORUPDATE smallint,
@CHANGEAGENTID uniqueidentifier = null,
@ASOF as datetime = null,
@NUMBERADDED int = 0 output,
@NUMBEREDITED int = 0 output,
@NUMBERDELETED int = 0 output,
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount off;
declare @CURRENTDATE datetime,
@ISERROR int,
@ERRORTEXT nvarchar(max),
@Count int,
@Index int,
@EventID uniqueidentifier,
@EventPriceID uniqueidentifier
declare @EventsThatAlreadyHaveOptionsWithThisName table(ID int identity(1,1), EventID uniqueidentifier, EventName nvarchar(max))
declare @EventsThatHaveAnEarlierStartDate table(ID int identity(1,1), EventID uniqueidentifier, EventName nvarchar(max))
declare @EventsThatHaveALowerGroupMemberLimit table(ID int identity(1,1), EventID uniqueidentifier, EventName nvarchar(max))
declare @EventInfo table(ID int identity(1,1), EventID uniqueidentifier, EventName nvarchar(max))
declare @EVENTREGISTRATIONTYPEID uniqueidentifier,
@REGISTRATIONCOUNT int,
@NAME nvarchar(100),
@DESCRIPTION nvarchar(1000),
@ISNOTAVAILABLEONLINEREG bit,
@ISREGTYPEAVAILABLEINDIVIDUAL bit,
@ISREGTYPEAVAILABLEFAMILYMEMBER bit,
@ISREGTYPEAVAILABLEHEADHOUSEHOLD bit,
@ISREGTYPEAVAILABLETEAMMEMBER bit,
@ISREGTYPEAVAILABLETEAMLEADER bit,
@ISREGTYPEAVAILABLECOMPANYLEADER bit,
@AMOUNT money,
@RECEIPTAMOUNT money,
@EARLYREGISTRATIONDATE datetime,
@LATEFEEDATE datetime,
@EARLYREGISTRATIONDISCOUNT money,
@LATEFEE money,
@MINFUNDRAISING money,
@ISNOTLOWERFUNDRAISING bit,
@PARTICIPANTMUSTPAYBALANCE bit,
@PARTICIPANTPAYSBALANCEMESSAGE nvarchar(1000),
@RECRUITEMENT int,
@ISNOTLOWERRECRUITEMENT bit,
@VOLUNTEERRECRUITEMENT int,
@ISNOTLOWERVOLUNTEERRECRUITEMENT bit,
@DONORRETENTION decimal(6,3),
@ISNOTLOWERDONORRETENTION bit,
@UNIT int,
@ISNOTLOWERUNIT bit,
@COMMUNICATIONS int,
@ISNOTLOWERCOMMUNICATION bit,
@LABELFORUNIT nvarchar(50),
@TARGETFUNDRAISING money,
@ISNOTLOWERTARGETFUNDRAISING bit,
@TEAMMINFUNDRAISING money,
@TEAMISNOTLOWERFUNDRAISING bit,
@TEAMRECRUITEMENT int,
@TEAMISNOTLOWERRECRUITEMENT bit,
@TEAMMEMBERS int,
@ISNOTLOWERTEAMMEMBERS bit,
@TEAMVOLUNTEERRECRUITEMENT int,
@ISNOTLOWERVOLUNTEERRECRUITEMENTTEAMGOAL bit,
@TEAMMEMBERRETENTION decimal(6,3),
@ISNOTLOWERTEAMMEMBERRETENTION bit,
@TEAMUNIT int,
@TEAMISNOTLOWERUNIT bit,
@TEAMCOMMUNICATIONS int,
@TEAMSNOTLOWERCOMMUNICATION bit,
@TEAMLABELFORUNIT nvarchar(50),
@COMPANYMINFUNDRAISING money,
@COMPANYISNOTLOWERFUNDRAISING bit,
@COMPANYRECRUITEMENT int,
@COMPANYISNOTLOWERRECRUITEMENT bit,
@TEAMS int,
@ISNOTLOWERTEAMS bit,
@COMPANYCOMMUNICATIONS int,
@COMPANYISNOTLOWERCOMMUNICATION bit,
@TEAMRETENTION decimal(6,3),
@ISNOTLOWERTEAMRETENTION bit,
@COMPANYVOLUNTEERRECRUITEMENT int,
@COMPANYISNOTLOWERVOLUNTEERRECRUITEMENT bit,
@COMPANYTEAMMEMBERS int,
@COMPANYISNOTLOWERTEAMMEMBERS bit,
@COMPANYUNIT int,
@COMPANYISNOTLOWERUNIT bit,
@COMPANYTEAMMEMBERRETENTION decimal(6,3),
@COMPANYISNOTLOWERTEAMMEMBERRETENTION bit,
@COMPANYLABELFORUNIT nvarchar(50),
@HOUSEHOLDMINFUNDRAISING money,
@HOUSEHOLDISNOTLOWERFUNDRAISING bit,
@HOUSEHOLDPARTICIPANTMUSTPAYBALANCE bit,
@HOUSEHOLDPARTICIPANTPAYSBALANCEMESSAGE nvarchar(1000),
@HOUSEHOLDRECRUITEMENT int,
@HOUSEHOLDISNOTLOWERRECRUITEMENT bit,
@HOUSEHOLDVOLUNTEERRECRUITEMENT int,
@HOUSEHOLDISNOTLOWERVOLUNTEERRECRUITEMENT int,
@HOUSEHOLDDONORRETENTION decimal(6,3),
@HOUSEHOLDISNOTLOWERDONORRETENTION bit,
@HOUSEHOLDUNIT int,
@HOUSEHOLDISNOTLOWERUNIT bit,
@HOUSEHOLDCOMMUNICATIONS int,
@HOUSEHOLDISNOTLOWERCOMMUNICATION bit,
@HOUSEHOLDLABELFORUNIT nvarchar(50),
@HOUSEHOLDTARGETFUNDRAISING money,
@HOUSEHOLDISNOTLOWERTARGETFUNDRAISING bit,
@MAXQUANTITY int,
@TURNONWAITLIST bit,
@DISPLAYSPOTSAVAILABLE bit,
@SENDEVENTCONTACTALERTMAXREACHED bit,
@WAITLISTMESSAGE nvarchar(1000),
@MINIMUMAGE varchar(5),
@MAXIMUMAGE varchar(5),
@INDIVIDUALSFUNDRAISE bit,
@HOUSEHOLDSFUNDRAISE bit,
@TEAMSFUNDRAISE bit,
@COMPANIESFUNDRAISE bit,
@BENEFITS xml,
@ALLOWPARTICIPANTSWAIVEBENEFITS bit,
@CURRENTREGISTRATIONCOUNT int,
@PRIORYEAREVENTID uniqueidentifier,
@HOUSEHOLDMEMBERS int,
@HOUSEHOLDISNOTLOWERTEAMMEMBERS bit,
@HOUSEHOLDMEMBERRETENTION decimal(6,3),
@HOUSEHOLDISNOTLOWERTEAMMEMBERRETENTION bit
set @CURRENTDATE = getdate();
set @ISERROR = 0;
set @NUMBERADDED = 0;
set @NUMBEREDITED = 0;
set @NUMBERDELETED = 0;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @SOURCEREGISTRATIONOPTION is not null
begin
exec USP_DATAFORMTEMPLATE_EDITLOAD_FAFREGISTRATIONTYPE @ID = @SOURCEREGISTRATIONOPTION,
@EVENTREGISTRATIONTYPEID = @EVENTREGISTRATIONTYPEID output,
@REGISTRATIONCOUNT = @REGISTRATIONCOUNT output,
@NAME = @NAME output,
@DESCRIPTION = @DESCRIPTION output,
@ISNOTAVAILABLEONLINEREG = @ISNOTAVAILABLEONLINEREG output,
@ISREGTYPEAVAILABLEINDIVIDUAL = @ISREGTYPEAVAILABLEINDIVIDUAL output,
@ISREGTYPEAVAILABLEFAMILYMEMBER = @ISREGTYPEAVAILABLEFAMILYMEMBER output,
@ISREGTYPEAVAILABLEHEADHOUSEHOLD = @ISREGTYPEAVAILABLEHEADHOUSEHOLD output,
@ISREGTYPEAVAILABLETEAMMEMBER = @ISREGTYPEAVAILABLETEAMMEMBER output,
@ISREGTYPEAVAILABLETEAMLEADER = @ISREGTYPEAVAILABLETEAMLEADER output,
@ISREGTYPEAVAILABLECOMPANYLEADER = @ISREGTYPEAVAILABLECOMPANYLEADER output,
@AMOUNT= @AMOUNT output,
@RECEIPTAMOUNT = @RECEIPTAMOUNT output,
@EARLYREGISTRATIONDATE = @EARLYREGISTRATIONDATE output,
@LATEFEEDATE = @LATEFEEDATE output,
@EARLYREGISTRATIONDISCOUNT = @EARLYREGISTRATIONDISCOUNT output,
@LATEFEE = @LATEFEE output,
@MINFUNDRAISING = @MINFUNDRAISING output,
@ISNOTLOWERFUNDRAISING = @ISNOTLOWERFUNDRAISING output,
@PARTICIPANTMUSTPAYBALANCE = @PARTICIPANTMUSTPAYBALANCE output,
@PARTICIPANTPAYSBALANCEMESSAGE = @PARTICIPANTPAYSBALANCEMESSAGE output,
@RECRUITEMENT = @RECRUITEMENT output,
@ISNOTLOWERRECRUITEMENT = @ISNOTLOWERRECRUITEMENT output,
@VOLUNTEERRECRUITEMENT = @VOLUNTEERRECRUITEMENT output,
@ISNOTLOWERVOLUNTEERRECRUITEMENT = @ISNOTLOWERVOLUNTEERRECRUITEMENT output,
@DONORRETENTION = @DONORRETENTION output,
@ISNOTLOWERDONORRETENTION = @ISNOTLOWERDONORRETENTION output,
@UNIT = @UNIT output,
@ISNOTLOWERUNIT = @ISNOTLOWERUNIT output,
@COMMUNICATIONS = @COMMUNICATIONS output,
@ISNOTLOWERCOMMUNICATION = @ISNOTLOWERCOMMUNICATION output,
@LABELFORUNIT = @LABELFORUNIT output,
@TARGETFUNDRAISING = @TARGETFUNDRAISING output,
@ISNOTLOWERTARGETFUNDRAISING = @ISNOTLOWERTARGETFUNDRAISING output,
@TEAMMINFUNDRAISING = @TEAMMINFUNDRAISING output,
@TEAMISNOTLOWERFUNDRAISING = @TEAMISNOTLOWERFUNDRAISING output,
@TEAMRECRUITEMENT = @TEAMRECRUITEMENT output,
@TEAMISNOTLOWERRECRUITEMENT = @TEAMISNOTLOWERRECRUITEMENT output,
@TEAMMEMBERS = @TEAMMEMBERS output,
@ISNOTLOWERTEAMMEMBERS = @ISNOTLOWERTEAMMEMBERS output,
@TEAMVOLUNTEERRECRUITEMENT = @TEAMVOLUNTEERRECRUITEMENT output,
@ISNOTLOWERVOLUNTEERRECRUITEMENTTEAMGOAL = @ISNOTLOWERVOLUNTEERRECRUITEMENTTEAMGOAL output,
@TEAMMEMBERRETENTION = @TEAMMEMBERRETENTION output,
@ISNOTLOWERTEAMMEMBERRETENTION = @ISNOTLOWERTEAMMEMBERRETENTION output,
@TEAMUNIT = @TEAMUNIT output,
@TEAMISNOTLOWERUNIT = @TEAMISNOTLOWERUNIT output,
@TEAMCOMMUNICATIONS = @TEAMCOMMUNICATIONS output,
@TEAMSNOTLOWERCOMMUNICATION = @TEAMSNOTLOWERCOMMUNICATION output,
@TEAMLABELFORUNIT = @TEAMLABELFORUNIT output,
@COMPANYMINFUNDRAISING = @COMPANYMINFUNDRAISING output,
@COMPANYISNOTLOWERFUNDRAISING = @COMPANYISNOTLOWERFUNDRAISING output,
@COMPANYRECRUITEMENT = @COMPANYRECRUITEMENT output,
@COMPANYISNOTLOWERRECRUITEMENT = @COMPANYISNOTLOWERRECRUITEMENT output,
@TEAMS = @TEAMS output,
@ISNOTLOWERTEAMS = @ISNOTLOWERTEAMS output,
@COMPANYCOMMUNICATIONS = @COMPANYCOMMUNICATIONS output,
@COMPANYISNOTLOWERCOMMUNICATION = @COMPANYISNOTLOWERCOMMUNICATION output,
@TEAMRETENTION = @TEAMRETENTION output,
@ISNOTLOWERTEAMRETENTION = @ISNOTLOWERTEAMRETENTION output,
@COMPANYVOLUNTEERRECRUITEMENT = @COMPANYVOLUNTEERRECRUITEMENT output,
@COMPANYISNOTLOWERVOLUNTEERRECRUITEMENT = @COMPANYISNOTLOWERVOLUNTEERRECRUITEMENT output,
@COMPANYTEAMMEMBERS = @COMPANYTEAMMEMBERS output,
@COMPANYISNOTLOWERTEAMMEMBERS = @COMPANYISNOTLOWERTEAMMEMBERS output,
@COMPANYUNIT = @COMPANYUNIT output,
@COMPANYISNOTLOWERUNIT = @COMPANYISNOTLOWERUNIT output,
@COMPANYTEAMMEMBERRETENTION = @COMPANYTEAMMEMBERRETENTION output,
@COMPANYISNOTLOWERTEAMMEMBERRETENTION = @COMPANYISNOTLOWERTEAMMEMBERRETENTION output,
@COMPANYLABELFORUNIT = @COMPANYLABELFORUNIT output,
@HOUSEHOLDMINFUNDRAISING = @HOUSEHOLDMINFUNDRAISING output,
@HOUSEHOLDISNOTLOWERFUNDRAISING = @HOUSEHOLDISNOTLOWERFUNDRAISING output,
@HOUSEHOLDPARTICIPANTMUSTPAYBALANCE = @HOUSEHOLDPARTICIPANTMUSTPAYBALANCE output,
@HOUSEHOLDPARTICIPANTPAYSBALANCEMESSAGE = @HOUSEHOLDPARTICIPANTPAYSBALANCEMESSAGE output,
@HOUSEHOLDRECRUITEMENT = @HOUSEHOLDRECRUITEMENT output,
@HOUSEHOLDISNOTLOWERRECRUITEMENT = @HOUSEHOLDISNOTLOWERRECRUITEMENT output,
@HOUSEHOLDVOLUNTEERRECRUITEMENT = @HOUSEHOLDVOLUNTEERRECRUITEMENT output,
@HOUSEHOLDISNOTLOWERVOLUNTEERRECRUITEMENT = @HOUSEHOLDISNOTLOWERVOLUNTEERRECRUITEMENT output,
@HOUSEHOLDDONORRETENTION = @HOUSEHOLDDONORRETENTION output,
@HOUSEHOLDISNOTLOWERDONORRETENTION = @HOUSEHOLDISNOTLOWERDONORRETENTION output,
@HOUSEHOLDUNIT = @HOUSEHOLDUNIT output,
@HOUSEHOLDISNOTLOWERUNIT = @HOUSEHOLDISNOTLOWERUNIT output,
@HOUSEHOLDCOMMUNICATIONS = @HOUSEHOLDCOMMUNICATIONS output,
@HOUSEHOLDISNOTLOWERCOMMUNICATION = @HOUSEHOLDISNOTLOWERCOMMUNICATION output,
@HOUSEHOLDLABELFORUNIT = @HOUSEHOLDLABELFORUNIT output,
@HOUSEHOLDTARGETFUNDRAISING = @HOUSEHOLDTARGETFUNDRAISING output,
@HOUSEHOLDISNOTLOWERTARGETFUNDRAISING = @HOUSEHOLDISNOTLOWERTARGETFUNDRAISING output,
@MAXQUANTITY = @MAXQUANTITY output,
@TURNONWAITLIST = @TURNONWAITLIST output,
@DISPLAYSPOTSAVAILABLE = @DISPLAYSPOTSAVAILABLE output,
@SENDEVENTCONTACTALERTMAXREACHED = @SENDEVENTCONTACTALERTMAXREACHED output,
@WAITLISTMESSAGE = @WAITLISTMESSAGE output,
@MINIMUMAGE = @MINIMUMAGE output,
@MAXIMUMAGE = @MAXIMUMAGE output,
@INDIVIDUALSFUNDRAISE = @INDIVIDUALSFUNDRAISE output,
@HOUSEHOLDSFUNDRAISE = @HOUSEHOLDSFUNDRAISE output,
@TEAMSFUNDRAISE = @TEAMSFUNDRAISE output,
@COMPANIESFUNDRAISE = @COMPANIESFUNDRAISE output,
@BENEFITS = @BENEFITS output,
@ALLOWPARTICIPANTSWAIVEBENEFITS = @ALLOWPARTICIPANTSWAIVEBENEFITS output,
@CURRENTREGISTRATIONCOUNT = @CURRENTREGISTRATIONCOUNT output,
@PRIORYEAREVENTID = @PRIORYEAREVENTID output,
@HOUSEHOLDMEMBERS = @HOUSEHOLDMEMBERS output,
@HOUSEHOLDISNOTLOWERTEAMMEMBERS = @HOUSEHOLDISNOTLOWERTEAMMEMBERS output,
@HOUSEHOLDMEMBERRETENTION = @HOUSEHOLDMEMBERRETENTION output,
@HOUSEHOLDISNOTLOWERTEAMMEMBERRETENTION = @HOUSEHOLDISNOTLOWERTEAMMEMBERRETENTION output
SET @CURRENTREGISTRATIONCOUNT = 0;
SET @MINIMUMAGE = ISNULL(@MINIMUMAGE, 0)
SET @MAXIMUMAGE = ISNULL(@MAXIMUMAGE, 0)
--reget @benefits
set @BENEFITS= (select [BENEFITID], [NAME], [DETAILS], '00000000-0000-0000-0000-000000000000', [QUANTITY], [TOTALVALUE], [UNITVALUE], DEDUCTIBLEUNITVALUE, DEDUCTIBLEVALUE
FROM dbo.UFN_REGISTRATION_GETBENEFITS(@SOURCEREGISTRATIONOPTION)
for xml raw('ITEM'),type,elements,root('BENEFITS'),BINARY BASE64)
end
begin tran
begin try
if @SELECTIONOPTIONFORUPDATE =1 --add to selected events
begin
insert into @EventInfo(EventID)
SELECT EVENTID FROM (SELECT T.c.value('(EVENTID)[1]','uniqueidentifier') AS 'EVENTID'
FROM @SELECTEDEVENTSFORUPDATE.nodes('/SELECTEDEVENTSFORUPDATE/ITEM') T(c)) XMLTABLE
WHERE exists(
select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(XMLTABLE.EVENTID) EVENTSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'800093A6-B727-490B-8CC4-C0C0CF2148F0',20)
where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)) )
)
select @Count = COUNT(*),@Index = 1 from @EventInfo
insert into @EventsThatAlreadyHaveOptionsWithThisName(EventID, EventName)
Select EP.EventID, E.Name
from dbo.EVENTPRICE EP
JOIN Event E on E.ID = EP.EventID
Where EP.Name=@NAME and EP.EVENTID in (Select EventID from @EventInfo)
select @ISERROR = Count(*) from @EventsThatAlreadyHaveOptionsWithThisName
if @ISERROR > 0
begin
SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventsThatAlreadyHaveOptionsWithThisName
RAISERROR ('Your updates could not be processed because registration option names must be unique for each event. A registration option named ?%s? already exists for the following events: %s. To continue, please remove or rename the existing registration option in these events. Or you can remove these events from your update to continue.', 11, 1, @NAME, @ERRORTEXT);
end
insert into @EventsThatHaveAnEarlierStartDate(EventID, EventName)
Select E.ID, E.Name
from dbo.EVENT E
Where E.STARTDATE < @EARLYREGISTRATIONDATE and E.ID in (Select EventID from @EventInfo)
select @ISERROR = Count(*) from @EventsThatHaveAnEarlierStartDate
if @ISERROR > 0
begin
SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventsThatHaveAnEarlierStartDate
RAISERROR ('Your updates could not be processed because the source registration option has an early discount date later than the event start date for the following events: %s. To continue, please edit the early discount date for this registration option. Or you can remove these events from your update to continue.', 11, 1, @ERRORTEXT);
end
insert into @EventsThatHaveALowerGroupMemberLimit(EventID, EventName)
Select E.ID, E.Name
from dbo.EVENT E
INNER JOIN dbo.EVENTTEAMFAFCONFIG EC ON E.ID = EC.EVENTID
Where (EC.COMPANYTEAMSLIMIT < @TEAMS OR
EC.COMPANYTEAMMEMBERSLIMIT < @COMPANYTEAMMEMBERS OR
EC.TEAMMEMBERSLIMIT < @TEAMMEMBERS OR
EC.HOUSEHOLDMEMBERSLIMIT < @HOUSEHOLDMEMBERS)
and E.ID in (Select EventID from @EventInfo)
select @ISERROR = Count(*) from @EventsThatHaveALowerGroupMemberLimit
if @ISERROR > 0
begin
SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventsThatHaveALowerGroupMemberLimit
RAISERROR ('Your updates could not be processed because the source registration option has a member recruitment goal that exceeds the member limit for the following events: %s. To continue, please edit the member recruitment goal for this registration option. Or you can remove these events from your update to continue.', 11, 1, @ERRORTEXT);
end
while @Index< = @Count
begin
select @EventID =EventID from @EventInfo where ID=@Index
exec USP_DATAFORMTEMPLATE_FAF_REGISTRATION_TYPE_ADD @CHANGEAGENTID=@CHANGEAGENTID, @EVENTID=@EVENTID, @EVENTREGISTRATIONTYPEID=@EVENTREGISTRATIONTYPEID, @REGISTRATIONCOUNT=@REGISTRATIONCOUNT,
@NAME=@NAME, @DESCRIPTION=@DESCRIPTION, @ISNOTAVAILABLEONLINEREG=@ISNOTAVAILABLEONLINEREG, @ISREGTYPEAVAILABLEINDIVIDUAL=@ISREGTYPEAVAILABLEINDIVIDUAL, @ISREGTYPEAVAILABLEFAMILYMEMBER=@ISREGTYPEAVAILABLEFAMILYMEMBER,
@ISREGTYPEAVAILABLEHEADHOUSEHOLD=@ISREGTYPEAVAILABLEHEADHOUSEHOLD, @ISREGTYPEAVAILABLETEAMMEMBER=@ISREGTYPEAVAILABLETEAMMEMBER, @ISREGTYPEAVAILABLETEAMLEADER=@ISREGTYPEAVAILABLETEAMLEADER,
@ISREGTYPEAVAILABLECOMPANYLEADER=@ISREGTYPEAVAILABLECOMPANYLEADER, @AMOUNT=@AMOUNT, @RECEIPTAMOUNT=@RECEIPTAMOUNT, @EARLYREGISTRATIONDATE=@EARLYREGISTRATIONDATE, @LATEFEEDATE=@LATEFEEDATE,
@EARLYREGISTRATIONDISCOUNT=@EARLYREGISTRATIONDISCOUNT, @LATEFEE=@LATEFEE, @MINFUNDRAISING=@MINFUNDRAISING, @ISNOTLOWERFUNDRAISING=@ISNOTLOWERFUNDRAISING, @PARTICIPANTMUSTPAYBALANCE=@PARTICIPANTMUSTPAYBALANCE,
@PARTICIPANTPAYSBALANCEMESSAGE=@PARTICIPANTPAYSBALANCEMESSAGE, @RECRUITEMENT=@RECRUITEMENT, @ISNOTLOWERRECRUITEMENT=@ISNOTLOWERRECRUITEMENT, @VOLUNTEERRECRUITEMENT=@VOLUNTEERRECRUITEMENT,
@ISNOTLOWERVOLUNTEERRECRUITEMENT=@ISNOTLOWERVOLUNTEERRECRUITEMENT, @DONORRETENTION=@DONORRETENTION, @ISNOTLOWERDONORRETENTION=@ISNOTLOWERDONORRETENTION, @UNIT=@UNIT, @ISNOTLOWERUNIT=@ISNOTLOWERUNIT,
@COMMUNICATIONS=@COMMUNICATIONS, @ISNOTLOWERCOMMUNICATION=@ISNOTLOWERCOMMUNICATION, @LABELFORUNIT=@LABELFORUNIT, @TARGETFUNDRAISING=@TARGETFUNDRAISING, @ISNOTLOWERTARGETFUNDRAISING=@ISNOTLOWERTARGETFUNDRAISING,
@TEAMMINFUNDRAISING=@TEAMMINFUNDRAISING, @TEAMISNOTLOWERFUNDRAISING=@TEAMISNOTLOWERFUNDRAISING, @TEAMRECRUITEMENT=@TEAMRECRUITEMENT, @TEAMISNOTLOWERRECRUITEMENT=@TEAMISNOTLOWERRECRUITEMENT,
@TEAMMEMBERS=@TEAMMEMBERS, @ISNOTLOWERTEAMMEMBERS=@ISNOTLOWERTEAMMEMBERS, @TEAMVOLUNTEERRECRUITEMENT=@TEAMVOLUNTEERRECRUITEMENT, @ISNOTLOWERVOLUNTEERRECRUITEMENTTEAMGOAL=@ISNOTLOWERVOLUNTEERRECRUITEMENTTEAMGOAL,
@TEAMMEMBERRETENTION=@TEAMMEMBERRETENTION, @ISNOTLOWERTEAMMEMBERRETENTION=@ISNOTLOWERTEAMMEMBERRETENTION, @TEAMUNIT=@TEAMUNIT, @TEAMISNOTLOWERUNIT=@TEAMISNOTLOWERUNIT, @TEAMCOMMUNICATIONS=@TEAMCOMMUNICATIONS,
@TEAMSNOTLOWERCOMMUNICATION=@TEAMSNOTLOWERCOMMUNICATION, @TEAMLABELFORUNIT=@TEAMLABELFORUNIT, @COMPANYMINFUNDRAISING=@COMPANYMINFUNDRAISING, @COMPANYISNOTLOWERFUNDRAISING=@COMPANYISNOTLOWERFUNDRAISING,
@COMPANYRECRUITEMENT=@COMPANYRECRUITEMENT, @COMPANYISNOTLOWERRECRUITEMENT=@COMPANYISNOTLOWERRECRUITEMENT, @TEAMS=@TEAMS, @ISNOTLOWERTEAMS=@ISNOTLOWERTEAMS, @COMPANYCOMMUNICATIONS=@COMPANYCOMMUNICATIONS,
@COMPANYISNOTLOWERCOMMUNICATION=@COMPANYISNOTLOWERCOMMUNICATION, @TEAMRETENTION=@TEAMRETENTION, @ISNOTLOWERTEAMRETENTION=@ISNOTLOWERTEAMRETENTION, @COMPANYVOLUNTEERRECRUITEMENT=@COMPANYVOLUNTEERRECRUITEMENT,
@COMPANYISNOTLOWERVOLUNTEERRECRUITEMENT=@COMPANYISNOTLOWERVOLUNTEERRECRUITEMENT, @COMPANYTEAMMEMBERS=@COMPANYTEAMMEMBERS, @COMPANYISNOTLOWERTEAMMEMBERS=@COMPANYISNOTLOWERTEAMMEMBERS,
@COMPANYUNIT=@COMPANYUNIT, @COMPANYISNOTLOWERUNIT=@COMPANYISNOTLOWERUNIT, @COMPANYTEAMMEMBERRETENTION=@COMPANYTEAMMEMBERRETENTION, @COMPANYISNOTLOWERTEAMMEMBERRETENTION=@COMPANYISNOTLOWERTEAMMEMBERRETENTION,
@COMPANYLABELFORUNIT=@COMPANYLABELFORUNIT, @MAXQUANTITY=@MAXQUANTITY, @TURNONWAITLIST=@TURNONWAITLIST, @DISPLAYSPOTSAVAILABLE=@DISPLAYSPOTSAVAILABLE, @SENDEVENTCONTACTALERTMAXREACHED=@SENDEVENTCONTACTALERTMAXREACHED,
@WAITLISTMESSAGE=@WAITLISTMESSAGE, @MINIMUMAGE=@MINIMUMAGE, @MAXIMUMAGE=@MAXIMUMAGE, @BENEFITS=@BENEFITS, @ALLOWPARTICIPANTSWAIVEBENEFITS=@ALLOWPARTICIPANTSWAIVEBENEFITS, @HOUSEHOLDMINFUNDRAISING=@HOUSEHOLDMINFUNDRAISING,
@HOUSEHOLDISNOTLOWERFUNDRAISING=@HOUSEHOLDISNOTLOWERFUNDRAISING, @HOUSEHOLDPARTICIPANTMUSTPAYBALANCE=@HOUSEHOLDPARTICIPANTMUSTPAYBALANCE, @HOUSEHOLDPARTICIPANTPAYSBALANCEMESSAGE=@HOUSEHOLDPARTICIPANTPAYSBALANCEMESSAGE,
@HOUSEHOLDRECRUITEMENT=@HOUSEHOLDRECRUITEMENT, @HOUSEHOLDISNOTLOWERRECRUITEMENT=@HOUSEHOLDISNOTLOWERRECRUITEMENT, @HOUSEHOLDVOLUNTEERRECRUITEMENT=@HOUSEHOLDVOLUNTEERRECRUITEMENT,
@HOUSEHOLDISNOTLOWERVOLUNTEERRECRUITEMENT=@HOUSEHOLDISNOTLOWERVOLUNTEERRECRUITEMENT, @HOUSEHOLDDONORRETENTION=@HOUSEHOLDDONORRETENTION, @HOUSEHOLDISNOTLOWERDONORRETENTION=@HOUSEHOLDISNOTLOWERDONORRETENTION,
@HOUSEHOLDUNIT=@HOUSEHOLDUNIT, @HOUSEHOLDISNOTLOWERUNIT=@HOUSEHOLDISNOTLOWERUNIT, @HOUSEHOLDCOMMUNICATIONS=@HOUSEHOLDCOMMUNICATIONS, @HOUSEHOLDISNOTLOWERCOMMUNICATION=@HOUSEHOLDISNOTLOWERCOMMUNICATION,
@HOUSEHOLDLABELFORUNIT=@HOUSEHOLDLABELFORUNIT, @HOUSEHOLDTARGETFUNDRAISING=@HOUSEHOLDTARGETFUNDRAISING, @HOUSEHOLDISNOTLOWERTARGETFUNDRAISING=@HOUSEHOLDISNOTLOWERTARGETFUNDRAISING,
@HOUSEHOLDMEMBERS=@HOUSEHOLDMEMBERS, @HOUSEHOLDISNOTLOWERTEAMMEMBERS=@HOUSEHOLDISNOTLOWERTEAMMEMBERS, @HOUSEHOLDMEMBERRETENTION=@HOUSEHOLDMEMBERRETENTION, @HOUSEHOLDISNOTLOWERTEAMMEMBERRETENTION=@HOUSEHOLDISNOTLOWERTEAMMEMBERRETENTION
set @Index = @Index +1
set @NUMBERADDED = @NUMBERADDED + 1
end
end
if @SELECTIONOPTIONFORUPDATE =2 -- replace selected registration options
begin
DECLARE @UpdatedTable table (ID int identity(1,1), EventPriceID uniqueidentifier)
insert into @UpdatedTable(EventPriceID)
SELECT EVENTPRICEID FROM (SELECT T.c.value('(RECORDID)[1]','uniqueidentifier') AS 'EVENTPRICEID'
FROM @SELECTEDREGISTRATIONOPTIONSFORUPDATE.nodes('/SELECTEDREGISTRATIONOPTIONSFORUPDATE/ITEM') T(c)) XMLTABLE
INNER JOIN EVENTPRICE EP ON XMLTABLE.EVENTPRICEID = EP.ID
WHERE exists(
select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(EP.EVENTID) EVENTSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'800093A6-B727-490B-8CC4-C0C0CF2148F0',20)
where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)) )
)
insert into @EventInfo(EventID,EventName)
Select EP.EventID, E.Name from dbo.EventPrice EP
JOIN Event E on EP.EventID = E.ID
where EP.ID in (select EventPriceID from @UpdatedTable)
select @ISERROR = COUNT(EventID) from @EventInfo Group By EventID HAVING (Count(EventID) > 1 )
if @ISERROR > 0
begin
SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventInfo Group By EventName HAVING (Count(EventName) > 1 )
RAISERROR ('Your updates could not be processed because registration option names must be unique for each event. You selected to update more than one registration option for the following events: %s. To continue, please change your selections to ensure that only one registration per event is selected.', 11, 1, @ERRORTEXT);
end
insert into @EventsThatAlreadyHaveOptionsWithThisName(EventID, EventName)
Select EP.EventID, E.Name
from dbo.EVENTPRICE EP
JOIN Event E on E.ID = EP.EventID
Where EP.Name=@NAME and EP.EVENTID in (Select EventID from @EventInfo) And EP.ID not in (Select EventPriceID from @UpdatedTable)
select @ISERROR = Count(*) from @EventsThatAlreadyHaveOptionsWithThisName
if @ISERROR > 0
begin
SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventsThatAlreadyHaveOptionsWithThisName
RAISERROR ('Your updates could not be processed because registration option names must be unique for each event. A registration option named ?%s? already exists for the following events: %s. To continue, please remove or rename the existing registration option in these events. Or you can remove these events from your update to continue.', 11, 1, @NAME, @ERRORTEXT);
end
insert into @EventsThatHaveAnEarlierStartDate(EventID, EventName)
Select E.ID, E.Name
from dbo.EVENT E
Where E.STARTDATE < @EARLYREGISTRATIONDATE and E.ID in (Select EventID from @EventInfo)
select @ISERROR = Count(*) from @EventsThatHaveAnEarlierStartDate
if @ISERROR > 0
begin
SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventsThatHaveAnEarlierStartDate
RAISERROR ('Your updates could not be processed because the source registration option has an early discount date later than the event start date for the following events: %s. To continue, please edit the early discount date for this registration option. Or you can remove these events from your update to continue.', 11, 1, @ERRORTEXT);
end
insert into @EventsThatHaveALowerGroupMemberLimit(EventID, EventName)
Select E.ID, E.Name
from dbo.EVENT E
INNER JOIN dbo.EVENTTEAMFAFCONFIG EC ON E.ID = EC.EVENTID
Where (EC.COMPANYTEAMSLIMIT < @TEAMS OR
EC.COMPANYTEAMMEMBERSLIMIT < @COMPANYTEAMMEMBERS OR
EC.TEAMMEMBERSLIMIT < @TEAMMEMBERS OR
EC.HOUSEHOLDMEMBERSLIMIT < @HOUSEHOLDMEMBERS)
and E.ID in (Select EventID from @EventInfo)
select @ISERROR = Count(*) from @EventsThatHaveALowerGroupMemberLimit
if @ISERROR > 0
begin
SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventsThatHaveALowerGroupMemberLimit
RAISERROR ('Your updates could not be processed because the source registration option has a member recruitment goal that exceeds the member limit for the following events: %s. To continue, please edit the member recruitment goal for this registration option. Or you can remove these events from your update to continue.', 11, 1, @ERRORTEXT);
end
select @Count = COUNT(*),@Index = 1 from @UpdatedTable
while @Index< = @Count
begin
select @EventPriceID = EventPriceID
from @UpdatedTable
where ID = @Index
exec USP_DATAFORMTEMPLATE_EDIT_FAF_REGISTRATION_TYPE2 @ID = @EventPriceID,
@CHANGEAGENTID=@CHANGEAGENTID, @EVENTREGISTRATIONTYPEID=@EVENTREGISTRATIONTYPEID, @REGISTRATIONCOUNT=@REGISTRATIONCOUNT, @NAME=@NAME, @DESCRIPTION=@DESCRIPTION,
@ISNOTAVAILABLEONLINEREG=@ISNOTAVAILABLEONLINEREG, @ISREGTYPEAVAILABLEINDIVIDUAL=@ISREGTYPEAVAILABLEINDIVIDUAL, @ISREGTYPEAVAILABLEFAMILYMEMBER=@ISREGTYPEAVAILABLEFAMILYMEMBER,
@ISREGTYPEAVAILABLEHEADHOUSEHOLD=@ISREGTYPEAVAILABLEHEADHOUSEHOLD, @ISREGTYPEAVAILABLETEAMMEMBER=@ISREGTYPEAVAILABLETEAMMEMBER, @ISREGTYPEAVAILABLETEAMLEADER=@ISREGTYPEAVAILABLETEAMLEADER,
@ISREGTYPEAVAILABLECOMPANYLEADER=@ISREGTYPEAVAILABLECOMPANYLEADER, @AMOUNT=@AMOUNT, @RECEIPTAMOUNT=@RECEIPTAMOUNT, @EARLYREGISTRATIONDATE=@EARLYREGISTRATIONDATE, @LATEFEEDATE=@LATEFEEDATE,
@EARLYREGISTRATIONDISCOUNT=@EARLYREGISTRATIONDISCOUNT, @LATEFEE=@LATEFEE, @MINFUNDRAISING=@MINFUNDRAISING, @ISNOTLOWERFUNDRAISING=@ISNOTLOWERFUNDRAISING, @PARTICIPANTMUSTPAYBALANCE=@PARTICIPANTMUSTPAYBALANCE,
@PARTICIPANTPAYSBALANCEMESSAGE=@PARTICIPANTPAYSBALANCEMESSAGE, @RECRUITEMENT=@RECRUITEMENT, @ISNOTLOWERRECRUITEMENT=@ISNOTLOWERRECRUITEMENT, @VOLUNTEERRECRUITEMENT=@VOLUNTEERRECRUITEMENT,
@ISNOTLOWERVOLUNTEERRECRUITEMENT=@ISNOTLOWERVOLUNTEERRECRUITEMENT, @DONORRETENTION=@DONORRETENTION, @ISNOTLOWERDONORRETENTION=@ISNOTLOWERDONORRETENTION, @UNIT=@UNIT, @ISNOTLOWERUNIT=@ISNOTLOWERUNIT,
@COMMUNICATIONS=@COMMUNICATIONS, @ISNOTLOWERCOMMUNICATION=@ISNOTLOWERCOMMUNICATION, @LABELFORUNIT=@LABELFORUNIT, @TARGETFUNDRAISING=@TARGETFUNDRAISING, @ISNOTLOWERTARGETFUNDRAISING=@ISNOTLOWERTARGETFUNDRAISING,
@TEAMMINFUNDRAISING=@TEAMMINFUNDRAISING, @TEAMISNOTLOWERFUNDRAISING=@TEAMISNOTLOWERFUNDRAISING, @TEAMRECRUITEMENT=@TEAMRECRUITEMENT, @TEAMISNOTLOWERRECRUITEMENT=@TEAMISNOTLOWERRECRUITEMENT,
@TEAMMEMBERS=@TEAMMEMBERS, @ISNOTLOWERTEAMMEMBERS=@ISNOTLOWERTEAMMEMBERS, @TEAMVOLUNTEERRECRUITEMENT=@TEAMVOLUNTEERRECRUITEMENT, @ISNOTLOWERVOLUNTEERRECRUITEMENTTEAMGOAL=@ISNOTLOWERVOLUNTEERRECRUITEMENTTEAMGOAL,
@TEAMMEMBERRETENTION=@TEAMMEMBERRETENTION, @ISNOTLOWERTEAMMEMBERRETENTION=@ISNOTLOWERTEAMMEMBERRETENTION, @TEAMUNIT=@TEAMUNIT, @TEAMISNOTLOWERUNIT=@TEAMISNOTLOWERUNIT, @TEAMCOMMUNICATIONS=@TEAMCOMMUNICATIONS,
@TEAMSNOTLOWERCOMMUNICATION=@TEAMSNOTLOWERCOMMUNICATION, @TEAMLABELFORUNIT=@TEAMLABELFORUNIT, @COMPANYMINFUNDRAISING=@COMPANYMINFUNDRAISING, @COMPANYISNOTLOWERFUNDRAISING=@COMPANYISNOTLOWERFUNDRAISING,
@COMPANYRECRUITEMENT=@COMPANYRECRUITEMENT, @COMPANYISNOTLOWERRECRUITEMENT=@COMPANYISNOTLOWERRECRUITEMENT, @TEAMS=@TEAMS, @ISNOTLOWERTEAMS=@ISNOTLOWERTEAMS, @COMPANYCOMMUNICATIONS=@COMPANYCOMMUNICATIONS,
@COMPANYISNOTLOWERCOMMUNICATION=@COMPANYISNOTLOWERCOMMUNICATION, @TEAMRETENTION=@TEAMRETENTION, @ISNOTLOWERTEAMRETENTION=@ISNOTLOWERTEAMRETENTION, @COMPANYVOLUNTEERRECRUITEMENT=@COMPANYVOLUNTEERRECRUITEMENT,
@COMPANYISNOTLOWERVOLUNTEERRECRUITEMENT=@COMPANYISNOTLOWERVOLUNTEERRECRUITEMENT, @COMPANYTEAMMEMBERS=@COMPANYTEAMMEMBERS, @COMPANYISNOTLOWERTEAMMEMBERS=@COMPANYISNOTLOWERTEAMMEMBERS,
@COMPANYUNIT=@COMPANYUNIT, @COMPANYISNOTLOWERUNIT=@COMPANYISNOTLOWERUNIT, @COMPANYTEAMMEMBERRETENTION=@COMPANYTEAMMEMBERRETENTION, @COMPANYISNOTLOWERTEAMMEMBERRETENTION=@COMPANYISNOTLOWERTEAMMEMBERRETENTION,
@COMPANYLABELFORUNIT=@COMPANYLABELFORUNIT,@HOUSEHOLDMINFUNDRAISING=@HOUSEHOLDMINFUNDRAISING,@HOUSEHOLDISNOTLOWERFUNDRAISING=@HOUSEHOLDISNOTLOWERFUNDRAISING, @HOUSEHOLDPARTICIPANTMUSTPAYBALANCE=@HOUSEHOLDPARTICIPANTMUSTPAYBALANCE,
@HOUSEHOLDPARTICIPANTPAYSBALANCEMESSAGE=@HOUSEHOLDPARTICIPANTPAYSBALANCEMESSAGE, @HOUSEHOLDRECRUITEMENT=@HOUSEHOLDRECRUITEMENT, @HOUSEHOLDISNOTLOWERRECRUITEMENT=@HOUSEHOLDISNOTLOWERRECRUITEMENT,
@HOUSEHOLDVOLUNTEERRECRUITEMENT=@HOUSEHOLDVOLUNTEERRECRUITEMENT, @HOUSEHOLDISNOTLOWERVOLUNTEERRECRUITEMENT=@HOUSEHOLDISNOTLOWERVOLUNTEERRECRUITEMENT, @HOUSEHOLDDONORRETENTION=@HOUSEHOLDDONORRETENTION,
@HOUSEHOLDISNOTLOWERDONORRETENTION=@HOUSEHOLDISNOTLOWERDONORRETENTION, @HOUSEHOLDUNIT=@HOUSEHOLDUNIT, @HOUSEHOLDISNOTLOWERUNIT=@HOUSEHOLDISNOTLOWERUNIT, @HOUSEHOLDCOMMUNICATIONS=@HOUSEHOLDCOMMUNICATIONS,
@HOUSEHOLDISNOTLOWERCOMMUNICATION=@HOUSEHOLDISNOTLOWERCOMMUNICATION, @HOUSEHOLDLABELFORUNIT=@HOUSEHOLDLABELFORUNIT, @HOUSEHOLDTARGETFUNDRAISING=@HOUSEHOLDTARGETFUNDRAISING,
@HOUSEHOLDISNOTLOWERTARGETFUNDRAISING=@HOUSEHOLDISNOTLOWERTARGETFUNDRAISING, @MAXQUANTITY=@MAXQUANTITY, @TURNONWAITLIST=@TURNONWAITLIST, @DISPLAYSPOTSAVAILABLE=@DISPLAYSPOTSAVAILABLE,
@SENDEVENTCONTACTALERTMAXREACHED=@SENDEVENTCONTACTALERTMAXREACHED, @WAITLISTMESSAGE=@WAITLISTMESSAGE, @MINIMUMAGE=@MINIMUMAGE, @MAXIMUMAGE=@MAXIMUMAGE, @BENEFITS=@BENEFITS, @ALLOWPARTICIPANTSWAIVEBENEFITS=@ALLOWPARTICIPANTSWAIVEBENEFITS,
@CURRENTREGISTRATIONCOUNT=@CURRENTREGISTRATIONCOUNT, @HOUSEHOLDMEMBERS=@HOUSEHOLDMEMBERS, @HOUSEHOLDISNOTLOWERTEAMMEMBERS=@HOUSEHOLDISNOTLOWERTEAMMEMBERS, @HOUSEHOLDMEMBERRETENTION=@HOUSEHOLDMEMBERRETENTION,
@HOUSEHOLDISNOTLOWERTEAMMEMBERRETENTION=@HOUSEHOLDISNOTLOWERTEAMMEMBERRETENTION
set @Index = @Index +1
set @NUMBEREDITED = @NUMBEREDITED + 1
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
rollback tran
return 1;
end catch
commit tran