Imports System.Data.SqlClient
Imports System.Configuration
Public Class EmpDashboard
    Shared Property UserDashboard As String
    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        Label1.Text = DateAndTime.Now.ToString("MMMM dd, yyyy HH:ss:tt")
    End Sub

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Refresh()
        Timer1.Start()
        Label2.Text = UserDashboard
        Dim query As String = "SELECT * FROM AttendanceRecord WHERE USERNAME='" & Label2.Text & "'"
        Using conn As SqlConnection = New SqlConnection("Data Source=.;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using resultcmd As SqlCommand = New SqlCommand(query, conn)
                Using sda As SqlDataAdapter = New SqlDataAdapter()
                    sda.SelectCommand = resultcmd
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        DataGridView1.DataSource = dt
                        DataGridView1.GridColor = Color.Red
                        DataGridView1.CellBorderStyle = DataGridViewCellBorderStyle.None
                        DataGridView1.BackgroundColor = Color.LightGray

                        DataGridView1.DefaultCellStyle.SelectionBackColor = Color.Red
                        DataGridView1.DefaultCellStyle.SelectionForeColor = Color.White

                        DataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.[True]

                        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
                        DataGridView1.AllowUserToResizeColumns = False

                        DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
                        DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
                        DataGridView1.Columns(0).Visible = False
                        DataGridView1.Columns(1).Width = 170
                        DataGridView1.Columns(2).Width = 190
                        DataGridView1.Columns(3).Width = 190
                    End Using
                End Using
            End Using
        End Using

        Dim NAMEquery As String = "SELECT * FROM UserTable WHERE USERNAME='" & Label2.Text & "'"
        Using NAMEcon As SqlConnection = New SqlConnection("Data Source=.;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using NAMEcmd As SqlCommand = New SqlCommand(NAMEquery, NAMEcon)
                Using da As New SqlDataAdapter()
                    da.SelectCommand = NAMEcmd
                    Using dt As New DataTable()
                        NAMEcon.Open()
                        da.Fill(dt)

                        If dt.Rows.Count > 0 Then
                            Label6.Text = dt.Rows(0)(3).ToString & " " & dt.Rows(0)(4).ToString()
                        Else
                            Label6.Text = ""
                        End If
                    End Using
                    NAMEcon.Close()
                End Using
            End Using
        End Using
    End Sub

    Public Sub BindData()
        Dim query As String = "SELECT * FROM AttendanceRecord WHERE USERNAME='" & Label2.Text & "'"
        Using conn As SqlConnection = New SqlConnection("Data Source=.;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using resultcmd As SqlCommand = New SqlCommand(query, conn)
                Using sda As SqlDataAdapter = New SqlDataAdapter()
                    sda.SelectCommand = resultcmd
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        DataGridView1.DataSource = dt
                    End Using
                End Using
            End Using
        End Using
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim USERNAME As String = Label2.Text 'USER ID 
        Dim TIME As String = TimeOfDay.ToString("HH:ss:tt")
        Dim DATEIN As String = DateTime.Now.ToString("dd/MM/yyyy")
        Dim STATUS As String = "TimeOUT"
        Dim validate As String = "SELECT * FROM AttendanceRecord WHERE [USERNAME] = @USERNAME AND [DATE]=@DATE AND [STATUS]=@STATUS"
        Using con As SqlConnection = New SqlConnection("Data Source=.;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using cmd As SqlCommand = New SqlCommand(validate, con)
                cmd.Parameters.AddWithValue("@USERNAME", USERNAME)

                cmd.Parameters.AddWithValue("@DATE", DATEIN)
                cmd.Parameters.AddWithValue("@STATUS", STATUS)
                Dim adapter As New SqlDataAdapter(cmd)
                Dim myTable As New DataTable

                adapter.Fill(myTable)
                If myTable.Rows.Count > 0 Then
                    MessageBox.Show("YOU HAVE ALREADY TIME-OUT FOR TODAY!", "INFORMATION", MessageBoxButtons.OK, MessageBoxIcon.Hand)
    
                Else
                    Dim addquery As String = "INSERT INTO AttendanceRecord([USERNAME],[TIME],[DATE],[STATUS])VALUES(@USERNAME,@TIME,@DATE,@STATUS)"
                    Dim com = New SqlCommand(addquery, con)

                    com.Parameters.AddWithValue("@USERNAME", USERNAME)
                    com.Parameters.AddWithValue("@TIME", TIME)
                    com.Parameters.AddWithValue("@DATE", DATEIN)
                    com.Parameters.AddWithValue("@STATUS", STATUS)

                    Dim x As Integer = 0
                    Try
                        con.Open()
                        x = com.ExecuteNonQuery()
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    Finally
                        con.Close()
                        cmd.Parameters.Clear()
                    End Try
                    Select Case x
                        Case 1
                            MessageBox.Show("TIME-OUT SUCCESSFULLY ADDED!", "INFORMATION", MessageBoxButtons.OK, MessageBoxIcon.Information)
                            BindData()
                        Case 0
                            MessageBox.Show("Opps, wait!! wrong entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End Select
                End If
            End Using
        End Using
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim USERNAME As String = Label2.Text 'USER ID 
        Dim TIME As String = TimeOfDay.ToString("HH:ss:tt")
        Dim DATEIN As String = DateTime.Now.ToString("dd/MM/yyyy")
        Dim STATUS As String = "TimeIN"
        Dim validate As String = "SELECT * FROM AttendanceRecord WHERE [USERNAME] = @USERNAME AND [DATE]=@DATE AND [STATUS]=@STATUS"
        Using con As SqlConnection = New SqlConnection("Data Source=.;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using cmd As SqlCommand = New SqlCommand(validate, con)
                cmd.Parameters.AddWithValue("@USERNAME", USERNAME)
                cmd.Parameters.AddWithValue("@DATE", DATEIN)
                cmd.Parameters.AddWithValue("@STATUS", STATUS)
                Dim adapter As New SqlDataAdapter(cmd)
                Dim myTable As New DataTable

                adapter.Fill(myTable)
                If myTable.Rows.Count > 0 Then
                    MessageBox.Show("YOU HAVE ALREADY TIME-IN FOR TODAY!", "INFORMATION", MessageBoxButtons.OK, MessageBoxIcon.Hand)
                Else
                    Dim addquery As String = "INSERT INTO AttendanceRecord([USERNAME],[TIME],[DATE],[STATUS])VALUES(@USERNAME,@TIME,@DATE,@STATUS)"
                    Dim com = New SqlCommand(addquery, con)

                    com.Parameters.AddWithValue("@USERNAME", USERNAME)
                    com.Parameters.AddWithValue("@TIME", TIME)
                    com.Parameters.AddWithValue("@DATE", DATEIN)
                    com.Parameters.AddWithValue("@STATUS", STATUS)

                    Dim x As Integer = 0
                    Try
                        con.Open()
                        x = com.ExecuteNonQuery()
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    Finally
                        con.Close()
                        cmd.Parameters.Clear()
                    End Try
                    Select Case x
                        Case 1
                            MessageBox.Show("TIME-IN SUCCESSFULLY ADDED!", "INFORMATION", MessageBoxButtons.OK, MessageBoxIcon.Information)
                            BindData()
                        Case 0
                            MessageBox.Show("Opps, wait!! wrong entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End Select
                End If
            End Using
        End Using
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        If Label2.Text = UserDashboard Then
            Dim EmProfile As New EmpProfile
            EmpProfile.EmProfile = Label2.Text
            EmpProfile.Show()
        End If

    End Sub
 
    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        If Label2.Text = UserDashboard Then
            Dim EmpSettingForm As New EmpSetting
            EmpSetting.EmpSettingForm = Label2.Text
            EmpSetting.Show()
        End If
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        If Label2.Text = UserDashboard Then
            Dim EmpaAttendanceForm As New EmpSetting
            EmpAttendance.EmpaAttendanceForm = Label2.Text
            EmpAttendance.Show()
        End If
    End Sub

    Private Sub EmpDashboard_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        Dim message As String = "DO YOU WANT TO EXIT?"
        Dim caption As String = "EXIT"
        Dim result = MessageBox.Show(message, caption, MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If (result = Windows.Forms.DialogResult.Yes) Then
            Application.Exit()
        ElseIf (result = Windows.Forms.DialogResult.No) Then
            e.Cancel = True
        End If
    End Sub
End Class