"ExceedsLimit" and "ConflictsWithOccurrences" difficulties

[Henric]

I am using the scheduler with the following setup:
- Multiple scheduler instances
- Common resources (soccer fields, rooms etc.)
- Common categories (match, training, other)
- Advanced Form template

Conditions:
Resources is not allowed for double-booking/overlap in a timeslot in any scheduler instance.
Categories can be the same in multiple timeslots in any scheduler instance.

Now, to achieve this I've combined the functions "ExceedsLimit" and "ConflictsWithOccurrences" (described by Telerik elsewhere) and expanded them with ResourceID. Like this, used in the AppointmentInsert event:

If ExceedsLimit(e.Appointment, e.Appointment.Resources.GetResourceByType("Resource").Key) OrElse ConflictsWithOccurrences(e.Appointment, e.Appointment.Resources.GetResourceByType("Resurs").Key) Then
...
End If

And in the AppointmentUpdate event:

If ExceedsLimit(e.ModifiedAppointment, e.ModifiedAppointment.Resources.GetResourceByType("Resurs").Key) OrElse ConflictsWithOccurrences(e.ModifiedAppointment, e.ModifiedAppointment.Resources.GetResourceByType("Resurs").Key) Then
...
End If

I've modified the "ExceedsLimit" like this:

Protected Function ExceedsLimit(ByVal apt As Appointment, ByVal ResourceID As String) As Boolean
    Dim appointmentsCount As Integer = 0
    Dim allAppointments As New AppointmentCollection
    'Fill temporary appointments collection for use in comparison
    'Subjectfield is used for comparison on ResourceID
    For Each dr As DataRow In cS.GetReservationsByResourceID(ResourceID)
        DirectCast(allAppointments, IList).Add(New Appointment(dr("ID"), dr("Start"), dr("End"), dr("ResourceID")))
    Next
    'Search through all appointments from all calendar instances and compare to current moved/changed/inserted appointment
    For Each existingApt As Appointment In allAppointments.GetAppointmentsInRange(apt.Start, apt.[End])
        If existingApt.Visible And existingApt.Subject = ResourceID Then
            appointmentsCount += 1
        End If
        ' If this is a recurring appointment, we will also check if it's occurrences
        ' overlap with other appointments in the current view
        Dim parsedRule As RecurrenceRule
        If RecurrenceRule.TryParse(existingApt.RecurrenceRule.ToString(), parsedRule) And existingApt.Subject = ResourceID Then
            parsedRule.SetEffectiveRange(RadScheduler1.VisibleRangeStart, RadScheduler1.VisibleRangeEnd)
            For Each occurrence As DateTime In parsedRule.Occurrences
                appointmentsCount = RadScheduler1.Appointments.GetAppointmentsInRange(occurrence, occurrence.Add(existingApt.Duration)).Count
                If (appointmentsCount > (AppointmentsLimit - 1)) Then
                    Return True
                End If
            Next
        End If
    Next
    Return (appointmentsCount > AppointmentsLimit - 1)
End Function

And the  "ConflictsWithOccurrences" like this:

Protected Function ConflictsWithOccurrences(ByVal apt As Appointment, ByVal ResourceID As String) As Boolean
    Dim allAppointments As New AppointmentCollection
    'Fill temporary appointments collection for use in comparison
    'Subjectfield is used for comparison on ResourceID
    For Each dr As DataRow In cS.GetReservationsByResourceID(ResourceID)
        DirectCast(allAppointments, IList).Add(New Appointment(dr("ID"), dr("Start"), dr("End"), dr("ResourceID")))
    Next
    Dim parsedRule As RecurrenceRule
    For Each existingApt As Appointment In allAppointments.GetAppointmentsInRange(apt.Start, apt.[End])
        If RecurrenceRule.TryParse(existingApt.RecurrenceRule.ToString(), parsedRule) Then
            'parsedRule.SetEffectiveRange(RadScheduler1.VisibleRangeStart, RadScheduler1.VisibleRangeEnd);
            For Each occurrence As DateTime In parsedRule.Occurrences
                'check if the start time of the occurrence is within the range of the appointment:
                If (occurrence.CompareTo(apt.Start) > 0) And (occurrence.CompareTo(apt.[End]) < 0) Then
                    Return True
                End If
                'check if the end time of the occurrence is within the range of the appointment:
                If (occurrence.Add(existingApt.Duration).CompareTo(apt.Start) > 0) And (occurrence.Add(existingApt.Duration).CompareTo(apt.[End]) < 0) Then
                    Return True
                End If
                'check if the start time of the appointment is within the range of the occurrence:
                If (apt.Start.CompareTo(occurrence) > 0) And (apt.Start.CompareTo(occurrence.Add(existingApt.Duration)) < 0) Then
                    Return True
                End If
                'check if the end time of the appointment is within the range of the occurrence:
                If (apt.[End].CompareTo(occurrence) > 0) And (apt.[End].CompareTo(occurrence.Add(existingApt.Duration)) < 0) Then
                    Return True
                End If
                'check if start or end the appoitnment coincide with start or end of the occurrence
                If (apt.Start.CompareTo(occurrence) = 0) OrElse (apt.Start.CompareTo(occurrence.Add(existingApt.Duration)) = 0) OrElse (apt.[End].CompareTo(occurrence) = 0) OrElse (apt.[End].CompareTo(occurrence.Add(existingApt.Duration)) = 0) Then
                    Return True
                End If
            Next
        End If
    Next
    Return False
End Function

This has helped me alot, but not enough as it fails in some cases:

1. The first ocurrence in a recurring appointment is not allowed to be updated, it conflicts whith itself.

No matter how I try to modify the functions, I can't seem to solve It completely. Only new pitfalls arises... I try to check and compare for "Insert new", "update existing", "If recurring" and so on.

Is there any obvoius faults in the above code or is there any better solution to meet these requirements?

[Henric]

Well, while struggling with this for a few days it seems like I have advanced a bit further.

To make it short, the solution is to remove the modified appointment from the temporary used comparison collection. In addition to this, to make comparison on Resource, the AppointmentCollection can not be used. I needed to create a new Radscheduler object (in code behind, not visible in GUI) and fill it with the necessary comparison data.
 

If anyone is interested, this is my solution so far. Note the parameter "ParentID" used in the sqldatasources for the two schedulers.

Default.aspx:

<%@ Page Title="Home Page" Language="vb" MasterPageFile="~/Site.Master" AutoEventWireup="false"
    CodeBehind="Default.aspx.vb" Inherits="RadSchedulerTest._Default" %>

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">

    <asp:Label runat="server" ID="label1" style="color:Red"></asp:Label>

    <telerik:RadScriptManager ID="RadScriptManager1" runat="server"></telerik:RadScriptManager>

    <h3>Scheduler 1</h3>
    <telerik:RadScheduler ID="RadScheduler1" runat="server"
        DataDescriptionField="Description" DataEndField="End" DataKeyField="ID"
        DataRecurrenceField="RecurrenceRule"
        DataRecurrenceParentKeyField="RecurrenceParentID" DataReminderField="Reminder"
        DataSourceID="SqlDsScheduler1" DataStartField="Start" DataSubjectField="Subject"  SelectedView="WeekView"
        EnableDescriptionField="false" OnAppointmentInsert="AppointmentInsert" OnAppointmentUpdate="AppointmentUpdate">
        <ResourceTypes>
            <telerik:ResourceType DataSourceID="SqlDsUsers"
                ForeignKeyField="UserID" KeyField="ID" Name="Users" TextField="UserName" />
            <telerik:ResourceType DataSourceID="SqlDsRooms" ForeignKeyField="RoomID"
                KeyField="ID" Name="Rooms" TextField="RoomName" />
        </ResourceTypes>
        <Reminders Enabled="True" />
    </telerik:RadScheduler>

    <h3>Scheduler 2</h3>
    <telerik:RadScheduler ID="RadScheduler2" runat="server"
        DataDescriptionField="Description" DataEndField="End" DataKeyField="ID"
        DataRecurrenceField="RecurrenceRule"
        DataRecurrenceParentKeyField="RecurrenceParentID" DataReminderField="Reminder"
        DataSourceID="SqlDsScheduler2" DataStartField="Start" DataSubjectField="Subject" SelectedView="WeekView"
        EnableDescriptionField="false" OnAppointmentInsert="AppointmentInsert" OnAppointmentUpdate="AppointmentUpdate">
        <ResourceTypes>
            <telerik:ResourceType DataSourceID="SqlDsUsers"
                ForeignKeyField="UserID" KeyField="ID" Name="Users" TextField="UserName" />
            <telerik:ResourceType DataSourceID="SqlDsRooms" ForeignKeyField="RoomID"
                KeyField="ID" Name="Rooms" TextField="RoomName" />
        </ResourceTypes>
        <Reminders Enabled="True" />
    </telerik:RadScheduler>

        <asp:SqlDataSource ID="SqlDsScheduler1" runat="server"
        ConnectionString="<%$ ConnectionStrings:RadSchedulerTestConnectionString %>"
        DeleteCommand="DELETE FROM [Appointments] WHERE [ID] = @ID"
        InsertCommand="INSERT INTO [Appointments] (ParentID, [Subject], [Description], [Start], [End], [RoomID], [UserID], [RecurrenceRule], [RecurrenceParentID], [Reminder], [Annotations]) VALUES (@ParentID, @Subject, @Description, @Start, @End, @RoomID, @UserID, @RecurrenceRule, @RecurrenceParentID, @Reminder, @Annotations)"
        SelectCommand="SELECT * FROM [Appointments] where parentid=1"
        UpdateCommand="UPDATE [Appointments] SET [Subject] = @Subject, [Description] = @Description, [Start] = @Start, [End] = @End, [RoomID] = @RoomID, [UserID] = @UserID, [RecurrenceRule] = @RecurrenceRule, [RecurrenceParentID] = @RecurrenceParentID, [Reminder] = @Reminder, [Annotations] = @Annotations WHERE [ID] = @ID">
        <DeleteParameters>
            <asp:Parameter Name="ID" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="ParentID" Type="Int32" DefaultValue="1" />
            <asp:Parameter Name="Subject" Type="String" />
            <asp:Parameter Name="Description" Type="String" />
            <asp:Parameter Name="Start" Type="DateTime" />
            <asp:Parameter Name="End" Type="DateTime" />
            <asp:Parameter Name="RoomID" Type="Int32" />
            <asp:Parameter Name="UserID" Type="Int32" />
            <asp:Parameter Name="RecurrenceRule" Type="String" />
            <asp:Parameter Name="RecurrenceParentID" Type="Int32" />
            <asp:Parameter Name="Reminder" Type="String" />
            <asp:Parameter Name="Annotations" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="Subject" Type="String" />
            <asp:Parameter Name="Description" Type="String" />
            <asp:Parameter Name="Start" Type="DateTime" />
            <asp:Parameter Name="End" Type="DateTime" />
            <asp:Parameter Name="RoomID" Type="Int32" />
            <asp:Parameter Name="UserID" Type="Int32" />
            <asp:Parameter Name="RecurrenceRule" Type="String" />
            <asp:Parameter Name="RecurrenceParentID" Type="Int32" />
            <asp:Parameter Name="Reminder" Type="String" />
            <asp:Parameter Name="Annotations" Type="String" />
            <asp:Parameter Name="ID" Type="Int32" />
        </UpdateParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDsScheduler2" runat="server"
        ConnectionString="<%$ ConnectionStrings:RadSchedulerTestConnectionString %>"
        DeleteCommand="DELETE FROM [Appointments] WHERE [ID] = @ID"
        InsertCommand="INSERT INTO [Appointments] (ParentID, [Subject], [Description], [Start], [End], [RoomID], [UserID], [RecurrenceRule], [RecurrenceParentID], [Reminder], [Annotations]) VALUES (@ParentID, @Subject, @Description, @Start, @End, @RoomID, @UserID, @RecurrenceRule, @RecurrenceParentID, @Reminder, @Annotations)"
        SelectCommand="SELECT * FROM [Appointments] where parentid=2"
        UpdateCommand="UPDATE [Appointments] SET [Subject] = @Subject, [Description] = @Description, [Start] = @Start, [End] = @End, [RoomID] = @RoomID, [UserID] = @UserID, [RecurrenceRule] = @RecurrenceRule, [RecurrenceParentID] = @RecurrenceParentID, [Reminder] = @Reminder, [Annotations] = @Annotations WHERE [ID] = @ID">
        <DeleteParameters>
            <asp:Parameter Name="ID" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="ParentID" Type="Int32" DefaultValue="2" />
            <asp:Parameter Name="Subject" Type="String" />
            <asp:Parameter Name="Description" Type="String" />
            <asp:Parameter Name="Start" Type="DateTime" />
            <asp:Parameter Name="End" Type="DateTime" />
            <asp:Parameter Name="RoomID" Type="Int32" />
            <asp:Parameter Name="UserID" Type="Int32" />
            <asp:Parameter Name="RecurrenceRule" Type="String" />
            <asp:Parameter Name="RecurrenceParentID" Type="Int32" />
            <asp:Parameter Name="Reminder" Type="String" />
            <asp:Parameter Name="Annotations" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="Subject" Type="String" />
            <asp:Parameter Name="Description" Type="String" />
            <asp:Parameter Name="Start" Type="DateTime" />
            <asp:Parameter Name="End" Type="DateTime" />
            <asp:Parameter Name="RoomID" Type="Int32" />
            <asp:Parameter Name="UserID" Type="Int32" />
            <asp:Parameter Name="RecurrenceRule" Type="String" />
            <asp:Parameter Name="RecurrenceParentID" Type="Int32" />
            <asp:Parameter Name="Reminder" Type="String" />
            <asp:Parameter Name="Annotations" Type="String" />
            <asp:Parameter Name="ID" Type="Int32" />
        </UpdateParameters>
    </asp:SqlDataSource>

    <asp:SqlDataSource ID="SqlDsUsers" runat="server"
        ConnectionString="<%$ ConnectionStrings:RadSchedulerTestConnectionString %>"
        SelectCommand="SELECT [ID], [UserName] FROM [Users]"></asp:SqlDataSource>

    <asp:SqlDataSource ID="SqlDsRooms" runat="server"
        ConnectionString="<%$ ConnectionStrings:RadSchedulerTestConnectionString %>"
        SelectCommand="SELECT [ID], [RoomName] FROM [Rooms]"></asp:SqlDataSource>

    <asp:SqlDataSource ID="SqlDsAllAppointmentsByRoomID" runat="server"
        ConnectionString="<%$ ConnectionStrings:RadSchedulerTestConnectionString %>"
        SelectCommand="SELECT * FROM [Appointments] WHERE RoomID = @RoomID">
        <SelectParameters>
            <asp:Parameter Name="RoomID" ConvertEmptyStringToNull="true" />
        </SelectParameters>
    </asp:SqlDataSource>

</asp:Content>

Default.aspx.vb:

Imports Telerik.Web.UI

Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        label1.Text = ""
    End Sub

    Protected Sub AppointmentInsert(ByVal sender As Object, ByVal e As Telerik.Web.UI.SchedulerCancelEventArgs)
        If HasSameResource(e.Appointment) Then
            label1.Text = "Another appointment exists in this time slot."
            e.Cancel = True
        End If
    End Sub

    Protected Sub AppointmentUpdate(sender As Object, e As Telerik.Web.UI.AppointmentUpdateEventArgs)
        If HasSameResource(e.ModifiedAppointment) Then
            label1.Text = "Another appointment exists in this time slot."
            e.Cancel = True
        End If
    End Sub

    Protected Function ExceedsLimit(ByVal apt As Appointment) As Boolean
        Dim AppointmentsLimit As Integer = 1
        Dim appointmentsCount As Integer = RadScheduler1.Appointments.GetAppointmentsInRange(apt.Start, apt.[End]).Count
        Return (appointmentsCount > AppointmentsLimit - 1)
    End Function

    Protected Function HasSameResource(ByVal apt As Appointment) As Boolean

        Dim RoomID As String
        If apt.Resources.GetResourceByType("Rooms") Is Nothing Then
            RoomID = ""
        Else
            RoomID = apt.Resources.GetResourceByType("Rooms").Key
        End If

        'Fill temporary scheduler object for use in comparison, from all schedulers
        Dim tempScheduler As New RadScheduler
        Dim rt As New ResourceType
        tempScheduler.DataSource = SqlDsAllAppointmentsByRoomID
        SqlDsAllAppointmentsByRoomID.SelectParameters.Item("RoomID").DefaultValue = RoomID
        tempScheduler.DataKeyField = "id"
        tempScheduler.DataStartField = "start"
        tempScheduler.DataEndField = "end"
        tempScheduler.DataSubjectField = "subject"
        tempScheduler.DataRecurrenceField = "RecurrenceRule"
        tempScheduler.DataRecurrenceParentKeyField = "RecurrenceParentID"
        tempScheduler.EnableRecurrenceSupport = True
        rt.DataSource = SqlDsRooms
        rt.Name = "Rooms"
        rt.ForeignKeyField = "roomid"
        rt.KeyField = "id"
        rt.TextField = "roomname"
        tempScheduler.ResourceTypes.Add(rt)
        tempScheduler.DataBind()
        If Not apt.ID Is Nothing Then
            Try
                tempScheduler.DeleteAppointment(apt, True) 'Remove edited appointment from comparison collection
            Catch
            End Try
        End If

        For Each otherApt As Appointment In tempScheduler.Appointments
            For Each otherAptRes As Resource In otherApt.Resources.GetResourcesByType("Rooms")
                For Each res As Resource In apt.Resources.GetResourcesByType("Rooms")
                    If otherAptRes.Key.Equals(res.Key) Then

                        Dim parsedRule As RecurrenceRule
                        For Each a As Appointment In tempScheduler.Appointments

                            If otherAptRes.Key.Equals(res.Key) And tempScheduler.Appointments.GetAppointmentsInRange(apt.Start, apt.[End]).Count Then
                                Return True
                            End If

                            'TODO:
                            'Recurring appointments overlapping single appoinments ahead in time is not trapped.

                            If RecurrenceRule.TryParse(a.RecurrenceRule.ToString(), parsedRule) Then
                                'parsedRule.SetEffectiveRange(RadScheduler1.VisibleRangeStart, RadScheduler1.VisibleRangeEnd);
                                For Each occurrence As DateTime In parsedRule.Occurrences
                                    'check if the start time of the occurrence is within the range of the appointment:
                                    If (occurrence.CompareTo(apt.Start) > 0) And (occurrence.CompareTo(apt.[End]) < 0) Then
                                        Return True
                                    End If
                                    'check if the end time of the occurrence is within the range of the appointment:
                                    If (occurrence.Add(a.Duration).CompareTo(apt.Start) > 0) And (occurrence.Add(a.Duration).CompareTo(apt.[End]) < 0) Then
                                        Return True
                                    End If

                                    'check if the start time of the appointment is within the range of the occurrence:
                                    If (apt.Start.CompareTo(occurrence) > 0) And (apt.Start.CompareTo(occurrence.Add(a.Duration)) < 0) Then
                                        Return True
                                    End If
                                    'check if the end time of the appointment is within the range of the occurrence:
                                    If (apt.[End].CompareTo(occurrence) > 0) And (apt.[End].CompareTo(occurrence.Add(a.Duration)) < 0) Then
                                        Return True
                                    End If
                                    'check if start or end the appoitnment coincide with start or end of the occurrence
                                    If (apt.Start.CompareTo(occurrence) = 0) OrElse (apt.Start.CompareTo(occurrence.Add(a.Duration)) = 0) OrElse (apt.[End].CompareTo(occurrence) = 0) OrElse (apt.[End].CompareTo(occurrence.Add(a.Duration)) = 0) Then
                                        'Not used, fails on valid situations like "App1, 10.00-11.00" conflicts with "App2 11.00-12.00". To exact time comparison, maybe?
                                        'Return True
                                    End If
                                Next
                            End If

                            'To deny insert of new recurring appointment, starting before any other recurring
                            'this "switched" conditions seems to work. At least partly...

                            If RecurrenceRule.TryParse(apt.RecurrenceRule.ToString(), parsedRule) Then
                                'parsedRule.SetEffectiveRange(RadScheduler1.VisibleRangeStart, RadScheduler1.VisibleRangeEnd);
                                For Each occurrence As DateTime In parsedRule.Occurrences
                                    'check if the start time of the occurrence is within the range of the appointment:
                                    If (occurrence.CompareTo(a.Start) > 0) And (occurrence.CompareTo(a.[End]) < 0) Then
                                        Return True
                                    End If
                                    'check if the end time of the occurrence is within the range of the appointment:
                                    If (occurrence.Add(apt.Duration).CompareTo(a.Start) > 0) And (occurrence.Add(apt.Duration).CompareTo(a.[End]) < 0) Then
                                        Return True
                                    End If

                                    'check if the start time of the appointment is within the range of the occurrence:
                                    If (a.Start.CompareTo(occurrence) > 0) And (a.Start.CompareTo(occurrence.Add(apt.Duration)) < 0) Then
                                        Return True
                                    End If
                                    'check if the end time of the appointment is within the range of the occurrence:
                                    If (a.[End].CompareTo(occurrence) > 0) And (a.[End].CompareTo(occurrence.Add(apt.Duration)) < 0) Then
                                        Return True
                                    End If
                                    'check if start or end the appoitnment coincide with start or end of the occurrence
                                    If (a.Start.CompareTo(occurrence) = 0) OrElse (a.Start.CompareTo(occurrence.Add(apt.Duration)) = 0) OrElse (a.[End].CompareTo(occurrence) = 0) OrElse (a.[End].CompareTo(occurrence.Add(apt.Duration)) = 0) Then
                                        'Not used, fails on valid situations like "App1, 10.00-11.00" conflicts with "App2 11.00-12.00". To exact time comparison, maybe?
                                        'Return True
                                    End If
                                Next


                            End If

                        Next

                    End If
                Next
            Next
        Next
        Return False
    End Function

End Class

As far as I can see, now only on thing remains:
"Recurring appointments overlapping single appointments ahead in time is not trapped."

I would highly appreciate if someone has a solution to this?

 

[Peter]

Hi Henric,

Thank you for posting your solution and please accept our apologies for the late reply.

Indeed, neither of the demos address the scenario with recurring appointments. This problem does not have a straight-forward and simple solution and that's why we've decided to keep the examples simple and understandable.

You have to be aware that occurrences set to repeat indefinitely are very hard, if not impossible, to check for overlaps. The recommended solution is to limit the verification to a reasonable time period - say one month ahead of now.

Attached is our solution for this scenario so you can use it for reference.

Best wishes,
Peter
the Telerik team