Hi everyone
The problem is that the events won't be selected by the stored procedure.
This bug in the stored procedure also causes the problem in week view (not displaying events on last day of week)!
To resolve this you can easily replace this stored procedure. Copy the whole SQL-Script which will first delete and then readd the stored procedure.
- Log in to dnn as host and select "SQL" in the host menu
- Copy and paste the following SQL Script in the textarea
- Activate "Run as script" and click on execute
To restore the script just execute the original SQL File "03.03.08.SqlDataProvider"!
What I did:
I just edited the stored procedure "EventsGetByRange" by replacing "@EndDate" with "DATEADD(DAY,1,@EndDate)" in the WHERE clause (6 times). Added one day and the events will be selected and shown.
I hope this helps until the release of 3.3.9.!
Cheers
Renato
************************ modified SQL Script (start copy next line) *****
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}EventsGetByRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}EventsGetByRange]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}EventsGetByRange
(
@Modules nvarchar(255),
@BeginDate datetime,
@EndDate datetime,
@Category nvarchar(50)
)
AS
SET DATEFORMAT mdy
IF (LEN(@Modules) = 0)
BEGIN
/** Added for Module Notification Scheduling
NOTE: This version also checks for SendReminder = True
**/
SELECT E.PortalID, EventID, ModuleID, EventDateBegin, EventDateEnd,
EventTimeBegin, Duration, EventName, EventDesc,
Importance, CreatedDate,
--CreatedBy = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
CreatedBy = U.DisplayName,
CreatorID = E.CreatedBy,
Every,
Period,
RepeatType,
Notify,
Approved,
Signups,
MaxEnrollment,
(Select count(*) from {databaseOwner}{objectQualifier}EventsSignups WHERE EventID = E.EventID and E.Signups = 1) as Enrolled,
EnrollRoleID,
EnrollFee,
EnrollType,
PayPalAccount,
PayPalPassword,
Cancelled,
ImageURL,
ImageType,
ImageWidth,
ImageHeight,
ImageDisplay,
E.Location,
c.LocationName,
c.MapURL,
E.Category,
b.CategoryName,
b.Color,
Reminder,
TimezoneOffset,
SendReminder,
ReminderTime,
ReminderTimeMeasurement,
ReminderFrom,
SearchSubmitted,
(Select ModuleTitle from {databaseOwner}{objectQualifier}Modules WHERE ModuleID = E.ModuleID) as ModuleTitle
FROM {databaseOwner}{objectQualifier}Events E
left outer join {databaseOwner}{objectQualifier}Users U on E.CreatedBy = U.UserID
left join {databaseOwner}{objectQualifier}EventsCategory b on E.Category = b.Category
left join {databaseOwner}{objectQualifier}EventsLocation c on E.Location = c.Location
WHERE (((EventTimeBegin <= DATEADD(DAY,1,@EndDate) AND DATEADD(minute,Duration,EventTimeBegin) >= @BeginDate) OR
(EventTimeBegin BETWEEN @BeginDate AND DATEADD(DAY,1,@EndDate)) OR
(EventTimeBegin <=DATEADD(DAY,1,@EndDate) AND EventDateEnd >= @BeginDate AND RepeatType <> 'N'))
AND (ModuleID in (SELECT * FROM {databaseOwner}[{objectQualifier}SplitIDs](@Modules)))
AND (Approved = 1)
AND (b.CategoryName = @Category OR @Category = ''))
ORDER BY EventDateBegin, EventTimeBegin, EventDateEnd
END
ELSE
BEGIN
SELECT E.PortalID, EventID, ModuleID, EventDateBegin, EventDateEnd,
EventTimeBegin, Duration, EventName, EventDesc,
Importance, CreatedDate,
--CreatedBy = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
createdBy = U.DisplayName,
CreatorID = E.CreatedBy,
Every,
Period,
RepeatType,
Notify,
Approved,
Signups,
MaxEnrollment,
(Select count(*) from {databaseOwner}{objectQualifier}EventsSignups WHERE EventID = E.EventID and E.Signups = 1) as Enrolled,
EnrollRoleID,
EnrollFee,
EnrollType,
PayPalAccount,
PayPalPassword,
Cancelled,
ImageURL,
ImageType,
ImageWidth,
ImageHeight,
ImageDisplay,
E.Location,
c.LocationName,
c.MapURL,
E.Category,
b.CategoryName,
b.Color,
Reminder,
TimezoneOffset,
SendReminder,
ReminderTime,
ReminderTimeMeasurement,
(Select ModuleTitle from {objectQualifier}Modules WHERE ModuleID = E.ModuleID) as ModuleTitle
FROM {databaseOwner}{objectQualifier}Events E
left outer join {databaseOwner}{objectQualifier}Users U on E.CreatedBy = U.UserID
left join {databaseOwner}{objectQualifier}EventsCategory b on E.Category = b.Category
left join {databaseOwner}{objectQualifier}EventsLocation c on E.Location = c.Location
WHERE (((EventTimeBegin <= DATEADD(DAY,1,@EndDate) AND DATEADD(minute,Duration,EventTimeBegin) >= @BeginDate) OR
(EventTimeBegin BETWEEN @BeginDate AND DATEADD(DAY,1,@EndDate)) OR
(EventTimeBegin <=DATEADD(DAY,1,@EndDate) AND EventDateEnd >= @BeginDate AND RepeatType <> 'N'))
AND (ModuleID in (SELECT * FROM {databaseOwner}[{objectQualifier}SplitIDs](@Modules)))
AND (Approved = 1)
AND (b.CategoryName = @Category OR @Category = ''))
ORDER BY EventDateBegin, EventTimeBegin, EventDateEnd
END
GO
***************************** end SQL Script ************************************************************