Show (Display) current year data in annotation on Chart mouse move using VB.Net in Windows Application

Last Reply 2 months ago By dharmendr

Posted 2 months ago

Hi Everyone, 

I have been struggling a while with this code and I can't seem to fix it. I need to show information in annotation form when I move my mouse over the points in my charts.

The SQL code works fine in SQL but in my vb.net format does not work.

this is the SQL code :

SELECT Date,Description,Price,Quantity,Department 'Department' FROM [dbo].[Expenses] 
WHERE DATEPART(DAY,Date) = '06' AND DATEPART(Month,Date) = '11' AND LTRIM(RTRIM(Department)) = LTRIM(RTRIM('Grocery'))

which gives me this values :

2020-11-06 00:00:00.000 Delivery Food 14.07 5 Grocery 

2020-11-06 00:00:00.000 Grocery 1.95 1 Grocery

This is the code that I have for the data chart :

    Private Function GetData1() As DataTable
        Dim dtChartData As New DataTable()
        Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("db").ConnectionString)
            Using cmd As New SqlCommand("Chart_daily1", conn)
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()
                'Counts Year only'
                cmd.Parameters.AddWithValue("@DateD", Convert.ToDateTime(DateTimePickerM.Text).ToString("yyyy-MM"))
                cmd.Parameters.AddWithValue("@Department", ComboBoxDepartment.Text)
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtChartData.Load(reader)

            End Using
        End Using

        Return dtChartData
    End Function

and this is for the annotation :

    Private prevPosition As Point? = Nothing
    Private tooltip As ToolTip = New ToolTip()
    Private Sub Chartday_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 Year 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(Year, 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 Year As String, ByVal department As String) As String

        Dim data As String = ""
        Dim query As String = "SELECT Date,Description,Price,Quantity,Department 'Department' FROM [dbo].[Expenses] "
        query += "WHERE DATEPART(DAY,Date) = @Day AND DATEPART(Month,Date) = @Month AND LTRIM(RTRIM(Department)) = LTRIM(RTRIM(@Username))"
        Dim dt As DataTable = New DataTable()
        Using con As SqlConnection = New SqlConnection("Data Source=ELVIS\SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;")
            Using command As New SqlCommand(query, con)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Day", Year.Trim())

                command.Parameters.AddWithValue("@Month", DateTime.Now.Month)
                command.Parameters.AddWithValue("@UserName", department.Trim())
                ' command.Parameters.AddWithValue("@UserName", (ComboBoxDepartment.Text))
                Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                    sda.Fill(dt)
                End Using
            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

with this code, i get information in my chart with specific date and month but not a specific year but all the years included in the dataset. Here is the view :

Datashow

as you can see in the picture how is shown. Hower this shows information only when i select ALL button but when i select the specific department it wont work at all :

here is a view :

Departments

What I want is that I can see annotation when I select the departments and when I select all of them. I know it's all confusing but please let me know if there are any questions. I am able to provide anything that is necessary to fix this issue.

Thank you, best REgards, Elvis Idrizi

Posted 2 months ago

Use below code.

SQL

ALTER Procedure [dbo].[Chart_ExpensesbyDayTotal1]  
(  
    @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,
		Store as 'Store',  
		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),Store,Department,DATEPART(DAY,Date)  
    ORDER BY SUM(Price) DESC  
End   

Code

Dim constr As String = "Data Source=.;Initial Catalog=test;UID=sa;PWD=pass@123;"
Private Sub Form2_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    DataChartExpensesbyDay()
End Sub
Private Sub DataChartExpensesbyDay()
    Dim dt As DataTable = GetData("[Chart_ExpensesbyDayTotal1]")
    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 GetData(ByVal query As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Using con As SqlConnection = New SqlConnection(constr)
        Using command As New SqlCommand("Chart_ExpensesbyDayTotal1", 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()
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 query As String = "SELECT Date,Description,Price,Quantity,Department 'Department' FROM [dbo].[Expenses] "
    query += "WHERE DATEPART(DAY,Date) = @Day AND DATEPART(Month,Date) = @Month AND LTRIM(RTRIM(Department)) = LTRIM(RTRIM(@Department))"
    Dim dt As DataTable = New DataTable()
    Using con As SqlConnection = New SqlConnection(constr)
        Using command As New SqlCommand(query, con)
            command.CommandType = CommandType.Text
            command.Parameters.AddWithValue("@Day", day.Trim())
            command.Parameters.AddWithValue("@Month", DateTime.Now.Month)
            command.Parameters.AddWithValue("@Department", department.Trim())
            Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                sda.Fill(dt)
            End Using
        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

 


Posted 2 months ago

You need to add current year condition if don't want to display except current year to the query.

For department query you need to verify the record might not present in database for that department in that particular date and month.

you can't do any thing for that.

Private Function GetDetails(ByVal day As String, ByVal department As String) As String
    Dim data As String = ""
    Dim query As String = "SELECT Date,Description,Price,Quantity,Department 'Department' FROM [dbo].[Expenses] "
    query += "WHERE DATEPART(DAY,Date) = @Day AND DATEPART(Month,Date) = @Month AND LTRIM(RTRIM(Department)) = LTRIM(RTRIM(@Department)) "
    query += "AND DATEPART(Year,Date) = @Year"
    Dim dt As DataTable = New DataTable()
    Using con As SqlConnection = New SqlConnection(constr)
        Using command As New SqlCommand(query, con)
            command.CommandType = CommandType.Text
            command.Parameters.AddWithValue("@Day", day.Trim())
            command.Parameters.AddWithValue("@Month", DateTime.Now.Month)
            command.Parameters.AddWithValue("@Department", department.Trim())
            command.Parameters.AddWithValue("@Year", DateTime.Now.Year)
            Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                sda.Fill(dt)
            End Using
        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