Solutions Schedule Studio Controls - How To... How To ...
Optimize Queries with Solutions Schedule .NET (VB .NET)

We often come across unique reporting needs, such as querying a resource plan or an existing schedule to display / report all scheduled service calls for districts 5 and 6 between 6:00am and 9:00am for instance, and we want to do so expeditiously.  This How To demonstrates one approach for optimizing fast reporting of appointment / time bar details implemented with Solutions Schedule for .NET.

Creating a custom collection of time bars removes the necessity of typical nested loops and recursive calls to return the time values we're looking for. This approach not only provides fast, responsive results it also helps in optimizing our code.


The following sample shows setting up a variable collection of time bars (CRUD activity) and then setting the query in motion...

Solutions Schedule for .NET
   
   
     
     
     
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
 

Imports Dbi.WinControl.Schedule

Public Class Form1

    'Declare the global variable for storing a secondary handle to every TimeBar loaded/added in to the dbiSchedule control.

    Private globalTimeBarCollection As System.Collections.Generic.List(Of dbiTimeBarItem) = New System.Collections.Generic.List(Of dbiTimeBarItem)

    Private Sub DbiSchedule1_AfterTimeBarInserted(ByVal sender As Object, ByVal e As AfterTimeBarInsertedEventArgs) Handles DbiSchedule1.AfterTimeBarInserted

        'After a timebar is inserted in to the control (through user interaction), it needs to be added to the global collection.

        globalTimeBarCollection.Add(e.TimeBarItem)

        'This is done in the AfterTimeBarInserted event, to ensure there is a proper reference to the timebar that now exists inside the control.

    End Sub

    Private Sub DbiSchedule1_BeforeTimeBarRemove(ByVal sender As Object, ByVal e As BeforeTimeBarRemoveEventArgs) Handles DbiSchedule1.BeforeTimeBarRemove

        'Before the timebar is removed from the control, remove it from the global collection.

        globalTimeBarCollection.Remove(e.TimeBarItem)

        'This is done in the BeforeTimeBarRemove event, to ensure there is still a valid handle to the timebar object to remove it from the

        'global collection.

        'If attempted from the AfterTimeBarRemove, there would no longer be an actual reference to the object, and additional code would need to

        'be used to determine which timebar needs to be removed from the global collection.

    End Sub

    Private Sub DbiSchedule1_FirstDraw(ByVal sender As Object, ByVal e As FirstDrawEventArgs) Handles DbiSchedule1.FirstDraw

        Dim cntItem As Integer

        Dim curSched As New dbiScheduleObject

        curSched.Start = New DateTime(2013, 1, 1)

        curSched.End = New DateTime(2014, 1, 1)

        curSched.TimeType = enumTimeType.Days

        curSched.DisplayTimeBarDates = True

        'The datetime pickers are synched with the date range of the dbiScheduleObject being used.

        Me.DateTimePicker1.MinDate = curSched.Start

        Me.DateTimePicker1.MaxDate = curSched.End

        Me.DateTimePicker1.Value = curSched.Start

        Me.DateTimePicker2.MinDate = curSched.Start

        Me.DateTimePicker2.MaxDate = curSched.End

        Me.DateTimePicker2.Value = curSched.End

        Me.DbiSchedule1.Schedules.Add(curSched)

        For cntItem = 1 To 10

            Dim newItem As New dbiScheduleItem

            newItem.Text = "Test " & cntItem.ToString

            Dim newTimeBar As New dbiTimeBarItem

            newTimeBar.Start = curSched.Start.AddDays(Int(Rnd(1) * 6))

            newTimeBar.End = newTimeBar.Start.AddDays(Int(Rnd(1) * 6))

            newItem.TimeBars.Add(newTimeBar)

            'After a timebar is added to an internal collection through code, it must also be added to the global collection.

            globalTimeBarCollection.Add(newTimeBar)

            newTimeBar = New dbiTimeBarItem

            newTimeBar.Start = curSched.Start.AddDays(Int(Rnd(1) * 6) + 12)

            newTimeBar.End = newTimeBar.Start.AddDays(Int(Rnd(1) * 6))

            newItem.TimeBars.Add(newTimeBar)

            'After a timebar is added to an internal collection through code, it must also be added to the global collection.

            globalTimeBarCollection.Add(newTimeBar)

            Me.DbiSchedule1.Items.Add(newItem)

        Next

        DbiSchedule1.BarSelectBackColor = SystemColors.Highlight

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Unselect all the currently selected timebars in the control

        For Each curSelTimeBar As dbiTimeBarItem In Me.DbiSchedule1.SelectedTimeBars

            curSelTimeBar.IsSelected = False

        Next

        Dim TotalTimeBarCount As Integer = 0

        'Loop through all the timebars stored in the global collection

        'if any of them intersect with the current date range selected in the datetimepickers, they will be selected and counted

        'After the loop finishes, the total amount of timebars that intersect with the date range, will be displayed in the textbox.

        For Each curTimeBar As dbiTimeBarItem In globalTimeBarCollection

            Dim inRange As Boolean = False

            If (curTimeBar.Start.Date = DateTimePicker1.Value.Date Or curTimeBar.End.Date = DateTimePicker2.Value.Date) Then

                inRange = True

            End If

            If (curTimeBar.Start.Date = DateTimePicker2.Value.Date Or curTimeBar.End.Date = DateTimePicker1.Value.Date) Then

                inRange = True

            End If

            If (curTimeBar.Start.Date < DateTimePicker1.Value.Date) Then

                If (curTimeBar.End.Date > DateTimePicker1.Value.Date And curTimeBar.End.Date < DateTimePicker2.Value.Date) Then

                    inRange = True

                End If

                If (curTimeBar.End.Date > DateTimePicker2.Value.Date) Then

                    inRange = True

                End If

            Else

                If (DateTimePicker2.Value.Date > curTimeBar.Start.Date And DateTimePicker2.Value.Date < curTimeBar.End.Date) Then

                    inRange = True

                End If

                If (DateTimePicker2.Value.Date > curTimeBar.End.Date) Then

                    inRange = True

                End If

            End If

            'The TimeBar was determined to be within the Date Range identified by the two DatePickers.

            'The count of total TimeBars within the range is incremented, and the TimeBar is selected.

            If inRange Then

                TotalTimeBarCount = TotalTimeBarCount + 1

                curTimeBar.IsSelected = True

            End If

        Next

        Me.TextBox1.Text = "TimeBars within Range = " + TotalTimeBarCount.ToString

    End Sub

End Class

 

















 
As always, take care and have a nice day.   
     
     
     
     
 
DBI Support Products Downloads Purchase
         
Customers Support Request Form COM Tools Trial Downloads Order Page
News Releases FAQ dbi Calendar Silverlight Online Evaluations Academic
Contact Us License Registration dbi Calendar WPF Legacy
Updates Extractor - Essential Content Summary
Utilities Solutions Schedule for COM
Support Policies Solutions Schedule Silverlight
Product Life Support Solutions Schedule for .NET
Platform Products Solutions Schedule WPF
Contact Us Studio Controls for COM
  Studio Controls for .NET
.NET Warehouse Scheduling Framework
  all rights reserved  |  copyright  1996 - 2016  |  Terms of Use