VB .NET Chart from datagridview or database with dynamic series

[Albu Andi Gabriel]

Hello,

I'm trying to do a project for working hours of employees for each day. This includes what time they start the product and when they finished. I succeded to do it manually. but I need to make it automatically from database.

My database is MySQL and the table has the columns:

- employee_id

- Product_id

- time_start

- time_finish

- date_start

- date_finish

What I have done is this:

        ChartControl2.Series.Clear()

        Me.ChartControl2.PrimaryXAxis.ValueType = ChartValueType.DateTime

        Me.ChartControl2.PrimaryXAxis.DateTimeFormat = "HH:mm"

        Me.ChartControl2.PrimaryXAxis.RangeType = ChartAxisRangeType.Set

        Me.ChartControl2.PrimaryXAxis.DateTimeRange = New ChartDateTimeRange(basedate, basedate.AddHours(14), 1, ChartDateTimeIntervalType.Hours)

        Me.ChartControl2.PrimaryYAxis.ValueType = ChartValueType.Double

        Me.ChartControl2.PrimaryYAxis.RangeType = ChartAxisRangeType.Set

        Me.ChartControl2.PrimaryYAxis.Range = New MinMaxInfo(6, 10, 1) //here I have chose the date 8 as a double because we cannot have both axis with datetime format

        Me.ChartControl2.Title.Text = "Working Time"

        ChartControl2.PrimaryYAxis.Title = "Date"

        ChartControl2.PrimaryXAxis.Title = "Time"

          get_values()

End Sub

Sub get_values()

    '===================================================================================================

        Dim series0 As New ChartSeries("WASTED", ChartSeriesType.StackingBar)

        series0.Points.Add(8, [date]:="7:14")

        series0.Points.Add(9, [date]:="7:14")

        '  series0.Style.Interior = New Syncfusion.Drawing.BrushInfo(Color.Red)

        Me.ChartControl2.Series.Add(series0)

        '===================================================================================================

        Dim series1 As New ChartSeries("HTC 7020", ChartSeriesType.StackingBar)

        start_time = "07:14:00"

        end_time = "08:56:00"

        time_calc()

        series1.Points.Add(8, [date]:=TotalTime.ToString)

        series1.Points.Add(9, [date]:=TotalTime.ToString)

        Me.ChartControl2.Series.Add(series1)

        '===================================================================================================

        Dim series2 As New ChartSeries("WASTED", ChartSeriesType.StackingBar)

        series2.Points.Add(8, [date]:="0:04:00")

        series2.Points.Add(9, [date]:="0:04:00")

        series2.Style.Interior = New Syncfusion.Drawing.BrushInfo(Color.Red)

        Me.ChartControl2.Series.Add(series2)

        '===================================================================================================

        Dim series3 As New ChartSeries("HTC 7061", ChartSeriesType.StackingBar)

        start_time = "09:00:00"

        end_time = "10:00:00"

        time_calc()

        series3.Points.Add(8, [date]:=TotalTime.ToString)

        series3.Points.Add(9, [date]:=TotalTime.ToString)

        ChartControl2.Series.Add(series3)

        '===================================================================================================

        Dim series4 As New ChartSeries("BREAK", ChartSeriesType.StackingBar)

        series4.Points.Add(8, [date]:=morning_break)

        series4.Points.Add(9, [date]:=morning_break)

        series4.Style.Interior = New Syncfusion.Drawing.BrushInfo(Color.White)

        ChartControl2.Series.Add(series4)

        '===================================================================================================

        Dim series5 As New ChartSeries("HTC 7061", ChartSeriesType.StackingBar)

        start_time = "10:15:00"

        end_time = "12:45:00"

        time_calc()

        series5.Points.Add(8, [date]:=TotalTime.ToString)

        series5.Points.Add(9, [date]:=TotalTime.ToString)

        ChartControl2.Series.Add(series5)

        '===================================================================================================

        Dim series6 As New ChartSeries("Lunch Break", ChartSeriesType.StackingBar)

        series6.Points.Add(8, [date]:=lunch_weekbreak)

        series6.Points.Add(9, [date]:=lunch_weekbreak)

        series6.Style.Interior = New Syncfusion.Drawing.BrushInfo(Color.White)

        ChartControl2.Series.Add(series6)

        '===================================================================================================

        Dim series7 As New ChartSeries("HTC 7061", ChartSeriesType.StackingBar)

        start_time = "13:30:00"

        end_time = "15:45:00"

        time_calc()

        series7.Points.Add(8, [date]:=TotalTime.ToString)

        series7.Points.Add(9, [date]:=TotalTime.ToString)

        ChartControl2.Series.Add(series7)

        '===================================================================================================

        Dim series8 As New ChartSeries("NO INPUT", ChartSeriesType.StackingBar)

        start_time = "15:45:00"

        end_time = "17:30:00"

        time_calc()

        series8.Points.Add(8, [date]:=TotalTime.ToString)

        series8.Points.Add(9, [date]:=TotalTime.ToString)

        series8.Style.Interior = New Syncfusion.Drawing.BrushInfo(Color.Red)

        ChartControl2.Series.Add(series8)

        '===================================================================================================

        Dim series9 As New ChartSeries("Evening Break", ChartSeriesType.StackingBar)

        series9.Points.Add(8, [date]:=evening_break)

        series9.Points.Add(9, [date]:=evening_break)

        series9.Style.Interior = New Syncfusion.Drawing.BrushInfo(Color.White)

        ChartControl2.Series.Add(series9)

        '===================================================================================================

        Dim series10 As New ChartSeries("Wasted", ChartSeriesType.StackingBar)

        start_time = "18:00:00"

        end_time = "18:09:00"

        time_calc()

        series10.Points.Add(8, [date]:=TotalTime.ToString)

        series10.Points.Add(9, [date]:=TotalTime.ToString)

        series10.Style.Interior = New Syncfusion.Drawing.BrushInfo(Color.Red)

        ChartControl2.Series.Add(series10)

        '===================================================================================================

        Dim series11 As New ChartSeries("HTC 7128", ChartSeriesType.StackingBar)

        start_time = "18:09:00"

        end_time = "20:30:00"

        time_calc()

        series11.Points.Add(8, [date]:=TotalTime.ToString)

        series11.Points.Add(9, [date]:=TotalTime.ToString)

        ChartControl2.Series.Add(series11)

 End Sub

    Sub time_calc()

        start_time_Date = DateTime.ParseExact(start_time, Format, Nothing)

        end_time_Date = DateTime.ParseExact(end_time, Format, Nothing)

        TotalTime = end_time_Date - start_time_Date

    End Sub

I need to take each date from database and match with the working times in that day for each series(product_no) and arrange it as per their start and finish time.

I had to double the input also with "9" because the bar will get thinner if it is only one value( STRANGE!).

Any suggestion please?

This is the result:

Attachment: 204859_DE_GUZMAN_BLESSIE_MAY8_LINE6_1f6811ed.zip

[Dharanidharan Dharmasivam]

Hi Albu, 

Thanks for contacting Syncfusion support. 

We have analyzed your query with the provided screenshot and code snippet. We have prepared a sample with respect to the screenshot provided, by dynamically binding points to series. Find the code snippet below to achieve this requirement. 

Windows Forms: 

//Will return the count depends upon the employee ID 

var filter = data.Select(x=>x.employee_Id).Distinct().ToList(); 

 

//Dynamically bind the points to the series 

for (int j = 0; j < filter.Count; j++) { 

 

                ChartSeries series = new ChartSeries(seriesName[j]); 

                //Filtered the data with respect to employee ID 

                DateTime[] xData2 = data.Where(x => x.employee_Id == filter[j]).Select(x => x.date_Start).ToArray(); 

                DateTime[] yData2 = data.Where(x => x.employee_Id == filter[j]).Select(x => x.time_Start).ToArray(); 

                for(int k=0; k<xData2.Length;k++){ 

                    series.Points.Add(xData2[k], yData2[k].ToOADate()); 

                } 

                this.chart.Series.Add(series); 

            } 

In the sample,cwe have rendered both the axis in dateTime and also we would like to let you know that, we have bind date_Start field to x axis and time_Start field to y axis as highlighted in the above code snippet. We have filtered the data from the table with respect to employees id. You can change this with respect to your scenario. 

Screenshot: 

 

Sample for reference can be find from below link. 

http://www.syncfusion.com/downloads/support/forum/130639/ze/WindowsFormsApplication1-1687272033  

If we have misunderstood your query, kindly revert us with more information on your query. 

Thanks, 

Dharani. 

[Albu Andi Gabriel]

This is great ! Any chance to have in VB. NET language ? I'm not an expert to convert C# code in VB.

[Dharanidharan Dharmasivam]

Hi Albu, 

 

Thanks for your update. 

 

As per your request, we have created the sample in VB. Find the sample from below location. 

http://www.syncfusion.com/downloads/support/forum/130639/ze/WindowsFormsApplication1418065585  

 

Kindly revert us, if you have any concern. 

Thanks, 

Dharani.