Hello everyone,
Thanks to this forum I have achieved some progress on my application, though, I am stuck in one part where I can’t seem to fix it up.
I have this chart that I change it whit my Combo box options, i.e. (grocery, department, electronics...) and I have created annotations for those charts, the chart where I select the option "All" from my combo box works fine but when I select any other option in my combo box then I have no results (No error as well), it's just empty.
This is the full code for what I have described so far:
Private Sub DataChartExpensesbyDay1()
Dim dt As DataTable = GetData("[Chart_ExpensesbyDayTotal2]")
Dim Department As List(Of String) = (From p In dt.AsEnumerable()
Select p.Field(Of String)("Department")).Distinct().ToList()
Dim years As List(Of Int32) = (From p In dt.AsEnumerable()
Order By p.Field(Of Int32)("Day")
Select p.Field(Of Int32)("Day")).Distinct().ToList()
If Chartday.Series.Count() = 1 Then
Chartday.Series.Remove(Chartday.Series(0))
End If
Chartday.Series.Clear()
For Each Departments As String In Department
'Dim x As List(Of Decimal) = New List(Of Decimal)()
'Dim y As List(Of Decimal) = New List(Of Decimal)()
Dim x As List(Of String) = New List(Of String)()
Dim y As List(Of Decimal) = New List(Of Decimal)()
For Each year As String In years
x.Add(year)
y.Add((From p In dt.AsEnumerable()
Where p.Field(Of String)("Department") = Departments And p.Field(Of Int32)("Day") = year
Order By p.Field(Of Int32)("Day")
Select p.Field(Of Decimal)("Total")).FirstOrDefault())
Next
Chartday.Series.Add(New Series(Departments))
Chartday.Series(Departments).IsValueShownAsLabel = True
Chartday.Series(Departments).BorderWidth = 2
Chartday.Series(Departments).ChartType = SeriesChartType.Spline
Chartday.Series(Departments).Points.DataBindXY(x, y)
Chartday.Series(Departments).LabelForeColor = Color.NavajoWhite
Chartday.Series(Departments).EmptyPointStyle.Color = Color.Red
Chartday.Series(Departments).EmptyPointStyle.AxisLabel = "Empty"
Chartday.Series(Departments).EmptyPointStyle.IsValueShownAsLabel = False
'Chart1.Series(department).Color = Color.FromArgb(rnd.Next(253), rnd.Next(256), rnd.Next(256))
Next
Chartday.Legends(0).Enabled = True
Dim MyData As Single() = New Single(Chartday.Series.Count - 1) {}
For i = 0 To Chartday.Series.Count - 1
MyData(i) = Convert.ToSingle(5 * Math.Sin(i / CDbl(2)))
Dim withBlock = Chartday.Series(i)
withBlock.BorderWidth = 2
withBlock.MarkerStyle = MarkerStyle.Circle
withBlock.MarkerSize = 8
Chartday.Annotations.Add(New CalloutAnnotation())
Next
End Sub
Private Function GetData2(ByVal query As String) As DataTable
Dim dt As DataTable = New DataTable()
Dim constr As String = "
Using con As SqlConnection = New SqlConnection(constr)
Using command As New SqlCommand("Chart_ExpensesbyDayTotal2", con)
command.CommandType = CommandType.StoredProcedure
If Not String.IsNullOrEmpty(DateTimePickerM.Text) Then
command.Parameters.AddWithValue("@DateDe", Convert.ToDateTime(DateTimePickerM.Text).ToString("yyyy-dd")) '"2020-01-01"
Else
command.Parameters.AddWithValue("@DateDe", DBNull.Value)
End If
command.Parameters.AddWithValue("@Month", "1")
If Not ComboBoxDepartment.Text = "All" Then
command.Parameters.AddWithValue("@Department", ComboBoxDepartment.Text)
Else
command.Parameters.AddWithValue("@Department", DBNull.Value)
End If
Using sda As SqlDataAdapter = New SqlDataAdapter(command)
sda.Fill(dt)
End Using
End Using
End Using
Return dt
End Function
Private prevPosition As Point? = Nothing
Private tooltip As ToolTip = New ToolTip()
'--------The right Version---------
Private Sub Chart1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles Chartday.MouseMove
Dim pos = e.Location
If prevPosition.HasValue AndAlso pos = prevPosition.Value Then Return
tooltip.RemoveAll()
prevPosition = pos
Dim results = Chartday.HitTest(pos.X, pos.Y, False, ChartElementType.DataPoint)
For Each result In results
If result.ChartElementType = ChartElementType.DataPoint Then
Dim department As String = result.Series.Name
Dim day As String = result.Series.Points(result.PointIndex).AxisLabel
Dim total As Double = result.Series.Points(result.PointIndex).YValues(0)
Dim yVal = result.ChartArea.AxisY.PixelPositionToValue(pos.Y)
tooltip.BackColor = Color.LightGray
tooltip.ToolTipTitle = "Data Information"
tooltip.Show(GetDetails(day, department), Chartday, pos.X, pos.Y - 15)
End If
Next
End Sub
Private Sub toolTip_Draw(ByVal sender As Object, ByVal e As DrawToolTipEventArgs)
Dim f As Font = New Font("Arial", 8.0F)
Dim b As Brush = New SolidBrush(tooltip.BackColor)
e.Graphics.FillRectangle(b, e.Bounds)
e.DrawBackground()
e.DrawBorder()
e.Graphics.DrawString(e.ToolTipText, f, SystemBrushes.ActiveCaptionText, e.Bounds)
End Sub
Private Function GetDetails(ByVal day As String, ByVal department As String) As String
Dim data As String = ""
Dim constr As String =
Dim query As String = ""
Dim dt As DataTable = New DataTable()
Using con As SqlConnection = New SqlConnection(constr)
Using command As New SqlCommand()
command.CommandType = CommandType.Text
command.Parameters.AddWithValue("@Day", day.Trim())
command.Parameters.AddWithValue("@Month", (DateTimePickerM.Text.Trim()))
'command.Parameters.AddWithValue("@Year", DateTime.Now.Year)
query += "SELECT Date,Description,Price,Quantity,Department FROM [dbo].[Expenses] "
query += "WHERE DATEPART(DAY,Date) = @Day AND CONVERT(char(7), date, 120) = @Month AND LTRIM(RTRIM(Department)) = LTRIM(RTRIM(@Department)) "
'query += "AND DATEPART(Year,Date) = @Year"
If ComboBoxDepartment.Text.Trim() = "All" Then
command.Parameters.AddWithValue("@Department", department.Trim())
Else
command.Parameters.AddWithValue("@Department", ComboBoxDepartment.Text.Trim())
End If
If Not query = "" Then
command.CommandText = query
command.Connection = con
Using sda As SqlDataAdapter = New SqlDataAdapter(command)
sda.Fill(dt)
End Using
End If
End Using
End Using
For Each row As DataRow In dt.Rows
Dim [date] As String = Convert.ToDateTime(row("Date")).ToString("dd/MM/yyyy")
Dim Username1 As String = row("Department")
Dim description As String = row("Description")
Dim price As String = If(row("Price").ToString = "", 0, row("Price").ToString)
Dim quantity As String = If(row("Quantity").ToString = "", 0, row("Quantity").ToString)
data = data & [date] & " - " & Username1.Trim() & " - " & description & " - " & price & " - " & quantity & Environment.NewLine
Next
Return data
End Function
and this is the SQL part :
ALTER Procedure [dbo].[Chart_ExpensesbyDayTotal2]
(
@DateDe VARCHAR(50) = NULL,
@Month VARCHAR(50) = NULL,
@Department VARCHAR(50) = NULL
)
as
Begin
SELECT SUM(Price) AS 'Total' ,
Sum(CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as NOA,
CONVERT(char(7), Date, 120) as 'Month',
Department as 'Department',
DATEPART(DAY, Date) AS 'Day'
FROM [dbo].[Expenses]
WHERE (CONVERT(char(7), date, 120) = @DateDe OR @DateDe IS NULL)
AND (DATEPART(Month,Date) = @Month OR @Month IS NULL)
AND (LTRIM(RTRIM(Department)) = LTRIM(RTRIM(@Department)) OR @Department IS NULL)
GROUP BY Description,CONVERT(char(7),date,120),Department,DATEPART(DAY,Date)
ORDER BY SUM(Price) DESC
End
Any helps is much appreciated.
Thank you
