USP_GLOBALCHANGE_ADDORCHANGEEVENTSPONSORSHIPOPTION
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CHOOSEEDEVENT | uniqueidentifier | IN | |
| @CHOOSEEDSPONSORSHIPOPTION | uniqueidentifier | IN | |
| @SELECTEDEVENTSFORUPDATE | xml | IN | |
| @SELECTEDSPONSORSHIPOPTIONSFORUPDATE | 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_ADDORCHANGEEVENTSPONSORSHIPOPTION
(
@CHOOSEEDEVENT uniqueidentifier,
@CHOOSEEDSPONSORSHIPOPTION uniqueidentifier,
@SELECTEDEVENTSFORUPDATE xml = null,
@SELECTEDSPONSORSHIPOPTIONSFORUPDATE 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
declare @NAME nvarchar(50),
@DESCRIPTION nvarchar(1000),
@ISNOTAVAILABLEONLINEREG bit,
@FEESAMOUNT money,
@TAXDEDUCTIBLEAMOUNT money,
@EARLYREGISTRATIONDATE datetime,
@EARLYREGISTRATIONDISCOUNT money,
@LATEFEEDATE datetime,
@LATEFEE money,
@MINFUNDRAISINGGOAL money,
@ISNOTLOWERFUNDRAISINGGOAL bit,
@SPONSORMUSTPAYBALANCE bit,
@SPONSORPAYSBALANCEMESSAGE nvarchar(1000),
@RECRUITEMENT int,
@ISNOTLOWERRECRUITEMENT bit,
@VOLUNTEERRECRUITEMENT int,
@ISNOTLOWERVOLUNTEERRECRUITEMENT bit,
@DONORRETENTION decimal(6,3),
@ISNOTLOWERDONORRETENTION bit,
@UNIT int,
@ISNOTLOWERUNIT bit,
@COMMUNICATIONS int,
@ISNOTLOWERCOMMUNICATION bit,
@LABELFORUNITGOAL nvarchar(50),
@NUMBERAVAILABLE int,
@TURNONWAITLIST bit,
@DISPLAYSPOTSAVAILABLE bit,
@SENDEVENTCONTACTALERTMAXREACHED bit,
@WAITLISTMESSAGE nvarchar(1000),
@BENEFITS xml,
@SPONSORPURCHASECOUNT int,
@ALLOWSPONSORWAIVEBENEFITS bit
declare @EventInfo table(ID int identity(1,1), EventID uniqueidentifier)
declare @EventsThatHaveAnEarlierStartDate table(ID int identity(1,1), EventID uniqueidentifier, EventName nvarchar(max))
declare @ErrorCount int, @ERRORTEXT nvarchar(max)
declare @Count int, @Index int
declare @EventID uniqueidentifier,
@EventSponshipTypeID uniqueidentifier
set @CURRENTDATE = getdate();
set @NUMBERADDED = 0;
set @NUMBEREDITED = 0;
set @NUMBERDELETED = 0;
set @ErrorCount = 0;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHOOSEEDSPONSORSHIPOPTION is not null
begin
exec USP_DATAFORMTEMPLATE_EDITLOAD_SPONSORSHIPTYPE @ID = @CHOOSEEDSPONSORSHIPOPTION,
@NAME = @NAME output,
@DESCRIPTION = @DESCRIPTION output,
@ISNOTAVAILABLEONLINEREG = @ISNOTAVAILABLEONLINEREG output,
@FEESAMOUNT = @FEESAMOUNT output,
@TAXDEDUCTIBLEAMOUNT = @TAXDEDUCTIBLEAMOUNT output,
@EARLYREGISTRATIONDATE = @EARLYREGISTRATIONDATE output,
@EARLYREGISTRATIONDISCOUNT = @EARLYREGISTRATIONDISCOUNT output,
@LATEFEEDATE = @LATEFEEDATE output,
@LATEFEE = @LATEFEE output,
@MINFUNDRAISINGGOAL = @MINFUNDRAISINGGOAL output,
@ISNOTLOWERFUNDRAISINGGOAL = @ISNOTLOWERFUNDRAISINGGOAL output,
@SPONSORMUSTPAYBALANCE = @SPONSORMUSTPAYBALANCE output,
@SPONSORPAYSBALANCEMESSAGE = @SPONSORPAYSBALANCEMESSAGE 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,
@LABELFORUNITGOAL = @LABELFORUNITGOAL output,
@NUMBERAVAILABLE = @NUMBERAVAILABLE output,
@TURNONWAITLIST = @TURNONWAITLIST output,
@DISPLAYSPOTSAVAILABLE = @DISPLAYSPOTSAVAILABLE output,
@SENDEVENTCONTACTALERTMAXREACHED = @SENDEVENTCONTACTALERTMAXREACHED output,
@WAITLISTMESSAGE = @WAITLISTMESSAGE output,
@BENEFITS = @BENEFITS output,
@SPONSORPURCHASECOUNT = @SPONSORPURCHASECOUNT output,
@ALLOWSPONSORWAIVEBENEFITS = @ALLOWSPONSORWAIVEBENEFITS output
--reget @benefits
set @BENEFITS= (SELECT [BENEFITID], [DETAILS], '00000000-0000-0000-0000-000000000000', [QUANTITY], [TOTALVALUE], [UNITVALUE], DEDUCTIBLEUNITVALUE, DEDUCTIBLEVALUE
FROM dbo.UFN_SPONSORSHIP_GETBENEFITS(@CHOOSEEDSPONSORSHIPOPTION)
for xml raw('ITEM'),type,elements,root('BENEFITS'),BINARY BASE64)
end
begin tran
begin try
if @SELECTIONOPTIONFORUPDATE =1
begin
insert into @EventInfo(EventID)
SELECT EVENTID FROM (SELECT T.c.value('(EVENTID)[1]','uniqueidentifier') AS 'EVENTID'
FROM @SELECTEDEVENTSFORUPDATE.nodes('/SELECTEDEVENTSFORUPDATE/ITEM') T(c)) AS 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)) )
)
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 @ErrorCount = Count(*) from @EventsThatHaveAnEarlierStartDate
if @ErrorCount > 0
begin
SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventsThatHaveAnEarlierStartDate
RAISERROR ('Your updates could not be processed because the source sponsorship 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 sponsorship option. Or you can remove these events from your update to continue.', 11, 1, @ERRORTEXT);
end
select @Count = COUNT(*),@Index = 1 from @EventInfo
while @Index< = @Count
begin
select @EventID =EventID from @EventInfo where ID=@Index
exec USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPTYPE @NAME=@NAME,@DESCRIPTION=@DESCRIPTION,@BENEFITS=@BENEFITS,
@ISNOTAVAILABLEONLINEREG=@ISNOTAVAILABLEONLINEREG,@FEESAMOUNT=@FEESAMOUNT,@TAXDEDUCTIBLEAMOUNT=@TAXDEDUCTIBLEAMOUNT,
@EARLYREGISTRATIONDATE=@EARLYREGISTRATIONDATE,@EARLYREGISTRATIONDISCOUNT=@EARLYREGISTRATIONDISCOUNT,@LATEFEEDATE=@LATEFEEDATE,
@LATEFEE=@LATEFEE,@MINFUNDRAISINGGOAL=@MINFUNDRAISINGGOAL,@ISNOTLOWERFUNDRAISINGGOAL=@ISNOTLOWERFUNDRAISINGGOAL,
@SPONSORMUSTPAYBALANCE=@SPONSORMUSTPAYBALANCE,@SPONSORPAYSBALANCEMESSAGE=@SPONSORPAYSBALANCEMESSAGE,@RECRUITEMENT=@RECRUITEMENT,
@ISNOTLOWERRECRUITEMENT=@ISNOTLOWERRECRUITEMENT,@VOLUNTEERRECRUITEMENT=@VOLUNTEERRECRUITEMENT,@ISNOTLOWERVOLUNTEERRECRUITEMENT=@ISNOTLOWERVOLUNTEERRECRUITEMENT,
@DONORRETENTION=@DONORRETENTION,@ISNOTLOWERDONORRETENTION=@ISNOTLOWERDONORRETENTION,@UNIT=@ISNOTLOWERDONORRETENTION,
@ISNOTLOWERUNIT=@ISNOTLOWERUNIT,@COMMUNICATIONS=@COMMUNICATIONS,@ISNOTLOWERCOMMUNICATION=@ISNOTLOWERCOMMUNICATION,@LABELFORUNITGOAL=@LABELFORUNITGOAL,
@NUMBERAVAILABLE=@NUMBERAVAILABLE,@TURNONWAITLIST=@TURNONWAITLIST,@DISPLAYSPOTSAVAILABLE=@DISPLAYSPOTSAVAILABLE,
@SENDEVENTCONTACTALERTMAXREACHED=@SENDEVENTCONTACTALERTMAXREACHED,@WAITLISTMESSAGE=@WAITLISTMESSAGE,@ALLOWSPONSORWAIVEBENEFITS=@ALLOWSPONSORWAIVEBENEFITS,
@CHANGEAGENTID=@CHANGEAGENTID,@EVENTID=@EVENTID
set @Index = @Index +1
set @NUMBERADDED = @NUMBERADDED + 1
end
end
if @SELECTIONOPTIONFORUPDATE =2
begin
DECLARE @UpdatedTable table (ID int identity(1,1), EventSponsorshipID uniqueidentifier)
insert into @UpdatedTable(EventSponsorshipID)
SELECT EVENTSPONSORSHIPID FROM (SELECT T.c.value('(RECORDID)[1]','uniqueidentifier') AS 'EVENTSPONSORSHIPID'
FROM @SELECTEDSPONSORSHIPOPTIONSFORUPDATE.nodes('/SELECTEDSPONSORSHIPOPTIONSFORUPDATE/ITEM') T(c)) AS XMLTABLE
INNER JOIN EVENTSPONSORSHIPTYPE ES ON XMLTABLE.EVENTSPONSORSHIPID = ES.ID
WHERE exists(
select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(ES.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)
Select ES.EventID
from dbo.EVENTSPONSORSHIPTYPE ES
join Event E on ES.EventID = E.ID
where ES.ID in (select EventSponsorshipID from @UpdatedTable)
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 @ErrorCount = Count(*) from @EventsThatHaveAnEarlierStartDate
if @ErrorCount > 0
begin
SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventsThatHaveAnEarlierStartDate
RAISERROR ('Your updates could not be processed because the source sponsorship 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 sponsorship 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 @EventSponshipTypeID = EventSponsorshipID
from @UpdatedTable
where ID = @Index
exec USP_DATAFORMTEMPLATE_EDIT_SPONSORSHIPTYPE @ID = @EventSponshipTypeID,
@CHANGEAGENTID = @CHANGEAGENTID,
@NAME = @NAME,
@DESCRIPTION = @DESCRIPTION,
@ISNOTAVAILABLEONLINEREG = @ISNOTAVAILABLEONLINEREG,
@FEESAMOUNT = @FEESAMOUNT,
@TAXDEDUCTIBLEAMOUNT = @TAXDEDUCTIBLEAMOUNT,
@EARLYREGISTRATIONDATE = @EARLYREGISTRATIONDATE,
@EARLYREGISTRATIONDISCOUNT = @EARLYREGISTRATIONDISCOUNT,
@LATEFEEDATE = @LATEFEEDATE,
@LATEFEE = @LATEFEE,
@MINFUNDRAISINGGOAL = @MINFUNDRAISINGGOAL,
@ISNOTLOWERFUNDRAISINGGOAL = @ISNOTLOWERFUNDRAISINGGOAL,
@SPONSORMUSTPAYBALANCE = @SPONSORMUSTPAYBALANCE,
@SPONSORPAYSBALANCEMESSAGE = @SPONSORPAYSBALANCEMESSAGE,
@RECRUITEMENT = @RECRUITEMENT,
@ISNOTLOWERRECRUITEMENT = @ISNOTLOWERRECRUITEMENT,
@VOLUNTEERRECRUITEMENT = @VOLUNTEERRECRUITEMENT,
@ISNOTLOWERVOLUNTEERRECRUITEMENT = @ISNOTLOWERVOLUNTEERRECRUITEMENT,
@DONORRETENTION = @DONORRETENTION,
@ISNOTLOWERDONORRETENTION = @ISNOTLOWERDONORRETENTION,
@UNIT = @UNIT,
@ISNOTLOWERUNIT = @ISNOTLOWERUNIT,
@COMMUNICATIONS = @COMMUNICATIONS,
@ISNOTLOWERCOMMUNICATION = @ISNOTLOWERCOMMUNICATION,
@LABELFORUNITGOAL = @LABELFORUNITGOAL,
@NUMBERAVAILABLE = @NUMBERAVAILABLE,
@TURNONWAITLIST = @TURNONWAITLIST,
@DISPLAYSPOTSAVAILABLE = @DISPLAYSPOTSAVAILABLE,
@SENDEVENTCONTACTALERTMAXREACHED = @SENDEVENTCONTACTALERTMAXREACHED,
@WAITLISTMESSAGE = @WAITLISTMESSAGE,
@BENEFITS = @BENEFITS,
@SPONSORPURCHASECOUNT = @SPONSORPURCHASECOUNT,
@ALLOWSPONSORWAIVEBENEFITS = @ALLOWSPONSORWAIVEBENEFITS
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