spAddUpdate_CalendarEvent_Recur
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PKID | int | INOUT | |
| @EventCalendarID | int | IN | |
| @Category | nvarchar(100) | IN | |
| @BeginDate | datetime | IN | |
| @EndDate | datetime | IN | |
| @Title | nvarchar(255) | IN | |
| @SubTitle | nvarchar(255) | IN | |
| @Location | nvarchar(255) | IN | |
| @Details | ntext | IN | |
| @Fees | nvarchar(255) | IN | |
| @MoreInfoURL | nvarchar(255) | IN | |
| @ContactInfo | nvarchar(255) | IN | |
| @CreatedUserID | int | IN | |
| @CreateDate | datetime | IN | |
| @ApprovedDate | datetime | IN | |
| @ApprovedUserID | int | IN | |
| @Deleted | bit | IN | |
| @OccurenceGroupID | int | IN | |
| @AllDayEvent | bit | IN | |
| @UpdateDate | datetime | INOUT | |
| @MapLocation | int | IN | |
| @Cancelled | bit | IN | |
| @TimeZoneID | int | IN | |
| @RecurFrequency | int | IN | |
| @RecurDay | int | IN | |
| @RecurMonth | int | IN | |
| @RecurDayOfWeek | nvarchar(200) | IN | |
| @RecurStartDate | datetime | IN | |
| @RecurEndDate | datetime | IN | |
| @RecurPosition | int | IN | |
| @RecurMonthlyMethod | int | IN | |
| @RecurUpdateFlag | int | IN | |
| @RecurID | int | INOUT | |
| @RecurInstances | nvarchar(4000) | IN | |
| @AthleticEventID | int | IN |
Definition
Copy
CREATE procedure [dbo].[spAddUpdate_CalendarEvent_Recur]
(
@PKID int output,
@EventCalendarID int,
@Category nvarchar (100),
@BeginDate datetime,
@EndDate datetime,
@Title nvarchar(255),
@SubTitle nvarchar(255),
@Location nvarchar (255),
@Details ntext,
@Fees nvarchar(255),
@MoreInfoURL nvarchar(255),
@ContactInfo nvarchar(255),
@CreatedUserID int,
@CreateDate datetime,
@ApprovedDate datetime,
@ApprovedUserID int,
@Deleted bit,
@OccurenceGroupID int,
@AllDayEvent bit,
@UpdateDate datetime output,
@MapLocation int,
@Cancelled bit,
@TimeZoneID int,
@RecurFrequency int,
@RecurDay int,
@RecurMonth int,
@RecurDayOfWeek nvarchar(200),
@RecurStartDate datetime,
@RecurEndDate datetime,
@RecurPosition int,
@RecurMonthlyMethod int, -- 1: by every day of month, 2: by day of week
@RecurUpdateFlag int, -- 1: all dates, 2: all future dates, 3: this date only
@RecurID int output,
@RecurInstances nvarchar(4000), -- xml string storing all the recur start / end date pairs
@AthleticEventID int
)
AS
begin
set nocount on
if @AthleticEventID <=0
SET @AthleticEventID = null
IF (@PKID > 0) -- UPDATE A SERIES
begin
declare @newPKID int
set @newPKID = 0
IF (@RecurFrequency = 0) -- Check if updating series from recurring to "does not repeat"; if so, treat all updateflags equal
begin
BEGIN TRANSACTION
-- STEP 1. delete this series (deleting the event in CalendarEvent table cascade deletes all related records in CalendarEvtRecur table)
DELETE from dbo.CalendarEvent where ID = @PKID
-- STEP 2. add a new event record
EXEC dbo.spAddUpdate_CalendarEvent
@newPKID, @EventCalendarID, @Category, @BeginDate, @EndDate, @Title, @SubTitle,
@Location, @Details, @Fees, @MoreInfoURL, @ContactInfo, @CreatedUserID, @CreateDate,
@ApprovedDate, @ApprovedUserID, @Deleted, 0, @AllDayEvent,
@UpdateDate, @MapLocation, @Cancelled, @TimeZoneID, 0, @AthleticEventID
COMMIT TRANSACTION
end
ELSE
begin
if (@RecurUpdateFlag = 2) -- update FUTURE DATES
begin
BEGIN TRANSACTION
-- Is this date the only occurrence in the series?
-- OR, is this date the first in the series?
if ((select count(*) from dbo.CalendarEvtRecur where EventID = @PKID) = 1
OR ((SELECT min(A.BeginDate) FROM dbo.CalendarEvtRecur A WHERE A.EventID = @PKID)
= (SELECT B.BeginDate FROM dbo.CalendarEvtRecur B WHERE B.RecurID = @RecurID AND B.EventID = @PKID)))
begin
-- treat as update all dates
SELECT @RecurUpdateFlag = 1
end
else -- there are > 1 occurrences and this date is not the first in the series
begin
-- separate occurrences before this date from those on/after this date
-- STEP 1. what is the recur end date of the occurrence just before this date?
declare @dPrevEndDate as datetime
set @dPrevEndDate = (SELECT max(A.EndDate) FROM dbo.CalendarEvtRecur A
WHERE A.EventID = @PKID
AND A.BeginDate < (SELECT B.BeginDate FROM dbo.CalendarEvtRecur B
WHERE B.RecurID = @RecurID AND B.EventID = @PKID))
-- use this date to update the RecurEndDate in CalendarEvent table of the current series
UPDATE dbo.CalendarEvent SET RecurEndDate = @dPrevEndDate WHERE ID = @PKID
-- STEP 2. delete occurrences on/after this date
DELETE from dbo.CalendarEvtRecur
where EventID = @PKID and EndDate > @dPrevEndDate
-- STEP 3. add a new series of this date
SELECT @PKID = @newPKID, @RecurUpdateFlag = 0, @RecurID = 0
end
COMMIT TRANSACTION
end
if (@RecurUpdateFlag = 3) -- update THIS DATE ONLY
begin
BEGIN TRANSACTION
-- Is this date the only occurrence in the series?
if ((select count(*) from dbo.CalendarEvtRecur where EventID = @PKID) = 1)
begin
-- treat as update all dates
SELECT @RecurUpdateFlag = 1
end
else -- there are > 1 occurrences
begin
-- STEP 1. exclude this date occurrence from the series by deleting it from CalendarEvtRecur table
DELETE from dbo.CalendarEvtRecur
where EventID = @PKID and RecurID = @RecurID
-- STEP 2. update the begin date, end date and recur start date of the series,
-- in case this date occurrence is the first one in the series
declare @dNextBeginDate as datetime
set @dNextBeginDate = (select min(BeginDate) from dbo.CalendarEvtRecur where EventID = @PKID)
declare @dNextEndDate as datetime
set @dNextEndDate = (select min(EndDate) from dbo.CalendarEvtRecur where EventID = @PKID)
UPDATE dbo.CalendarEvent SET
BeginDate = @dNextBeginDate,
EndDate = @dNextEndDate,
RecurStartDate = @dNextBeginDate
WHERE ID = @PKID
-- STEP 3. add a new event record of this date occurrence; mark it as exception by passing 1 to @RecurException
EXEC dbo.spAddUpdate_CalendarEvent
@PKID = @newPKID OUTPUT, @EventCalendarID = @EventCalendarID, @Category = @Category, @BeginDate = @BeginDate, @EndDate = @EndDate, @Title = @Title, @SubTitle = @Subtitle,
@Location = @Location, @Details = @Details, @Fees = @Fees, @MoreInfoURL = @MoreInfoURL, @ContactInfo = @ContactInfo, @CreatedUserID = @CreatedUserID, @CreateDate = @CreateDate,
@ApprovedDate = @ApprovedDate, @ApprovedUserID = @ApprovedUserID, @Deleted = @Deleted, @OccurenceGroupID = 0, @AllDayEvent = @AllDayEvent,
@UpdateDate = @UpdateDate, @MapLocation = @MapLocation, @Cancelled = @Cancelled, @TimeZoneID = @TimeZoneID, @RecurException = 1, @AthleticEventID = @AthleticEventID
SELECT @PKID = @newPKID, @RecurID = 0
end
COMMIT TRANSACTION
end
if (@RecurUpdateFlag = 1) -- update ALL DATES
begin
BEGIN TRANSACTION
-- STEP 1. delete this series (deleting the event in CalendarEvent table cascade deletes all related records in CalendarEvtRecur table)
DELETE from dbo.CalendarEvent where ID = @PKID
-- STEP 2. add a new series
SELECT @PKID = @newPKID, @RecurUpdateFlag = 0, @RecurID = 0
COMMIT TRANSACTION
end
end
end
IF (@PKID <= 0) -- ADD A SERIES
begin
BEGIN TRANSACTION
--this id will be saved in the OccurenceGroupId column to all the recurring event records:
declare @myNextOccurID int
set @myNextOccurID = ( select
case when (@PKID<=0) then max(OccurenceGroupId) + 1
else max(OccurenceGroupId) end
from dbo.CalendarEvent
)
if @myNextOccurID is null -- a recurring event is added to the calendar as its first event
set @myNextOccurID = 1
INSERT INTO dbo.CalendarEvent
(
EventCalendarID,
Category,
BeginDate,
EndDate,
Title,
SubTitle,
Location,
Details,
Fees,
MoreInfoURL,
ContactInfo,
CreatedUserID,
CreateDate,
ApprovedDate,
ApprovedUserID,
Deleted,
OccurenceGroupID,
AllDayEvent,
MapLocation,
Cancelled,
TimeZoneID,
RecurFrequency,
RecurDay,
RecurMonth,
RecurDayOfWeek,
RecurStartDate,
RecurEndDate,
RecurPosition,
RecurMonthlyMethod,
AthleticEventID
)
VALUES
(
@EventCalendarID,
@Category,
@BeginDate,
@EndDate,
@Title,
@SubTitle,
@Location,
@Details,
@Fees,
@MoreInfoURL,
@ContactInfo,
@CreatedUserID,
getutcdate(),
@ApprovedDate,
@ApprovedUserID,
@Deleted,
@myNextOccurID,
@AllDayEvent,
@MapLocation,
@Cancelled,
@TimeZoneID,
@RecurFrequency,
@RecurDay,
@RecurMonth,
@RecurDayOfWeek,
@RecurStartDate,
@RecurEndDate,
@RecurPosition,
@RecurMonthlyMethod,
@AthleticEventID
)
SELECT @PKID = @@Identity
declare @docHandle int
exec sp_xml_preparedocument @docHandle OUTPUT, @RecurInstances
insert into dbo.CalendarEvtRecur (EventID, BeginDate, EndDate)
select @PKID, s, e from OPENXML(@docHandle, N'/r/dt') WITH (s datetime, e datetime)
exec sp_xml_removedocument @docHandle
COMMIT TRANSACTION
end
SELECT @PKID
end