"ExceedsLimit" and "ConflictsWithOccurrences" difficulties

3 posts, 0 answers
  1. Henric
    Henric avatar
    11 posts
    Member since:
    Nov 2008

    Posted 19 Apr 2011 Link to this post

    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?


  2. Henric
    Henric avatar
    11 posts
    Member since:
    Nov 2008

    Posted 21 Apr 2011 Link to this post

    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?

     

  3. Peter
    Admin
    Peter avatar
    6637 posts

    Posted 22 Apr 2011 Link to this post

    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

    Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

Back to Top