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

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
   
   
     
     
     
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using Dbi.WinControl.Schedule;

 

namespace TimeBarCollectionCsharp

{

    public partial class Form1 : Form

    {

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

        private System.Collections.Generic.List<dbiTimeBarItem> globalTimeBarCollection = new System.Collections.Generic.List<dbiTimeBarItem>();

 

        public Form1()

        {

            InitializeComponent();

        }

 

        private void DbiSchedule1_AfterTimeBarInserted(object sender, AfterTimeBarInsertedEventArgs e)

        {

            //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.

        }

 

        private void DbiSchedule1_BeforeTimeBarRemove(object sender, BeforeTimeBarRemoveEventArgs e)

        {

            //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.

        }

        private void DbiSchedule1_FirstDraw(object sender, FirstDrawEventArgs e)

        {

            int cntItem;

 

            dbiScheduleObject curSched = 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.

            this.DateTimePicker1.MinDate = curSched.Start;

            this.DateTimePicker1.MaxDate = curSched.End;

            this.DateTimePicker1.Value = curSched.Start;

            this.DateTimePicker2.MinDate = curSched.Start;

            this.DateTimePicker2.MaxDate = curSched.End;

            this.DateTimePicker2.Value = curSched.End;

 

            this.DbiSchedule1.Schedules.Add(curSched);

 

            Random randGen;

            randGen = new Random(2360);

 

            for (cntItem = 1;cntItem<=10;cntItem++)

            {

                dbiScheduleItem newItem = new dbiScheduleItem();

                newItem.Text = "Test " + cntItem.ToString();

 

                dbiTimeBarItem newTimeBar = new dbiTimeBarItem();

                newTimeBar.Start = curSched.Start.AddDays(randGen.Next(6));

                newTimeBar.End = newTimeBar.Start.AddDays(randGen.Next(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(randGen.Next(6) + 12);

                newTimeBar.End = newTimeBar.Start.AddDays(randGen.Next(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);

 

                this.DbiSchedule1.Items.Add(newItem);

            }

 

        DbiSchedule1.BarSelectBackColor = SystemColors.Highlight;

        }

 

        private void Button1_Click(object sender, EventArgs e)

        {

            //Unselect all the currently selected timebars in the control

            foreach(dbiTimeBarItem curSelTimeBar in this.DbiSchedule1.SelectedTimeBars())

            {

                curSelTimeBar.IsSelected = false;

            }

 

            int TotalTimeBarCount = 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.

            foreach (dbiTimeBarItem curTimeBar in globalTimeBarCollection)

            {

                bool inRange = false;

 

                if ((curTimeBar.Start.Date == DateTimePicker1.Value.Date) || (curTimeBar.End.Date == DateTimePicker2.Value.Date))

                {

                    inRange = true;

                }

 

                if ((curTimeBar.Start.Date == DateTimePicker2.Value.Date) || (curTimeBar.End.Date == DateTimePicker1.Value.Date))

                {

                    inRange = true;

                }

 

                if (curTimeBar.Start.Date < DateTimePicker1.Value.Date)

                {

 

                    if ((curTimeBar.End.Date > DateTimePicker1.Value.Date) && (curTimeBar.End.Date < DateTimePicker2.Value.Date))

                    {

                        inRange = true;

                    }

                   

                    if (curTimeBar.End.Date > DateTimePicker2.Value.Date)

                    {

                        inRange = true;

                    }

                }

                else

                {

                    if ((DateTimePicker2.Value.Date > curTimeBar.Start.Date) && (DateTimePicker2.Value.Date < curTimeBar.End.Date))

                    {

                        inRange = true;

                    }

 

                    if (DateTimePicker2.Value.Date > curTimeBar.End.Date)

                    {

                        inRange = true;

                    }

                }

 

                //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)

                {

                    TotalTimeBarCount = TotalTimeBarCount + 1;

                    curTimeBar.IsSelected = true;

                }

 

            }

 

            this.TextBox1.Text = "TimeBars within Range = " + TotalTimeBarCount.ToString();

        }

 

    }

}

















 
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 - Text Analytics
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