Imports MySql.Data.MySqlClient

Public Class Manageleavefrm

    Private Sub Manageleavefrm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        GroupBox1.Enabled = False
        cb2_load()
        load_data()
    End Sub
    Private Sub load_data()
        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"
        Dim query As String
        query = "SELECT `id` as 'ID', `LeaveType` as 'LEAVE TYPE', `Days_of_Leave` as 'DAYS OF LEAVE', `ToDate` as 'TO DATE', `FromDate` as 'FROM DATE', `Description` as 'DESCRIPTION', `PostingDate` as 'POSTING DATE',`Status` as 'STATUS',`EmployeeName` as 'EMPLOYEE NAME', `EmployeeId` as 'EMPLOYEE ID' FROM `tblleaves` WHERE `Status` = 'PENDING' "



        Try
            conn.Open()


            Dim search As New MySqlDataAdapter(query, conn)
            Dim ds As New DataSet
            search.Fill(ds, "tblleaves")
            DataGridView1.DataSource = ds.Tables("tblleaves")




            conn.Close()

        Catch ex As MySqlException
            MessageBox.Show(ex.Message)

        Finally
            conn.Dispose()
        End Try
    End Sub
    Public Sub load_info()
        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"

        Dim comm As New MySqlCommand("select * from tblemployees where EmpId='" & TextBox1.Text & "' ", conn)

        comm.Parameters.Add(" '" & TextBox1.Text & "' ", MySqlDbType.Int64).Value = TextBox1.Text
        Dim adt As New MySqlDataAdapter(comm)

        Dim table As New DataTable()
        adt.Fill(table)

        TextBox2.Text = table.Rows(0)(2).ToString()
        TextBox4.Text = table.Rows(0)(13).ToString()

    End Sub
   
    Public Sub cb2_load()
        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"


        Dim str As String
        conn.Open()


        Try
            str = "SELECT `LeaveType` FROM `tblleaveType` GROUP by `LeaveType` "
            Dim da As New MySqlDataAdapter(str, conn)
            Dim dt As New DataTable
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                ComboBox1.DataSource = dt
                ComboBox1.DisplayMember = "LeaveType"
                ComboBox1.ValueMember = ""

            End If

        Catch ex As Exception

        End Try

    End Sub
   Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        If e.RowIndex >= 0 Then
            Dim row As DataGridViewRow
            row = Me.DataGridView1.Rows(e.RowIndex)
            Label16.Text = row.Cells("ID").Value.ToString
            TextBox3.Text = row.Cells("DAYS OF LEAVE").Value.ToString
            ComboBox1.Text = row.Cells("LEAVE TYPE").Value.ToString
            DateTimePicker1.Text = row.Cells("TO DATE").Value.ToString
            DateTimePicker2.Text = row.Cells("FROM DATE").Value.ToString
            TextBox5.Text = row.Cells("DESCRIPTION").Value.ToString
            ComboBox2.Text = row.Cells("status").Value.ToString
            TextBox1.Text = row.Cells("EMPLOYEE ID").Value.ToString


        End If
    End Sub
    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        If TextBox1.Text > "" Then
            load_info()
        End If

       
    End Sub

    Private Sub DataGridView2_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        conn = New MySqlConnection
        Dim a, b, c As String
        Dim result As Integer
        Dim cmd As New MySqlCommand

        a = TextBox4.Text
        b = TextBox3.Text
        c = a - b
        Label14.Text = c



        If TextBox1.Text = "" Or TextBox2.Text = "" Then
            MessageBox.Show("fill all the form")
        Else

            conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"

            Try
                conn.Open()
                With cmd

                    .Connection = conn
                    .CommandText = "UPDATE `tblleaves` SET `AdminRemark`='" & TextBox6.Text & "' ,`AdminRemarkDate`= '" & DateTimePicker3.Text & "', `Status`= '" & Label15.Text & "' WHERE `id` = '" & Label16.Text & "'"

                    result = .ExecuteNonQuery

                    If result > 0 Then
                        .Connection = conn
                        .CommandText = "UPDATE `tblemployees` SET `Leave_days`='" & Label14.Text & "'  WHERE `EmpId` = '" & TextBox1.Text & "'"

                        result = .ExecuteNonQuery
                    End If

                    MessageBox.Show("Apply Accepted")
                  
                    cb2_load()

                End With
                TextBox1.Text = ""
                TextBox2.Text = ""
                TextBox3.Text = ""
                TextBox4.Text = ""
                TextBox5.Text = ""
                TextBox6.Text = ""
                DateTimePicker1.Text = ""
                DateTimePicker2.Text = ""
                ComboBox1.Text = ""
                ComboBox2.Text = ""


                conn.Close()
                load_data()
            Catch ex As MySqlException
                MessageBox.Show(ex.Message)

            Finally
                conn.Dispose()
            End Try
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        conn = New MySqlConnection

        Dim result As Integer
        Dim cmd As New MySqlCommand

       
        If TextBox1.Text = "" Or TextBox2.Text = "" Then
            MessageBox.Show("fill all the form")
        Else

            conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"

            Try
                conn.Open()
                With cmd

                    .Connection = conn
                    .CommandText = "UPDATE `tblleaves` SET `AdminRemark`='" & TextBox6.Text & "' ,`AdminRemarkDate`= '" & DateTimePicker3.Text & "', `Status`= '" & Label17.Text & "' WHERE `id` = '" & Label16.Text & "'"

                    result = .ExecuteNonQuery


                    MessageBox.Show("Apply Rejected")
                   

                    cb2_load()

                End With
                TextBox1.Text = ""
                TextBox2.Text = ""
                TextBox3.Text = ""
                TextBox4.Text = ""
                TextBox5.Text = ""
                TextBox6.Text = ""
                DateTimePicker1.Text = ""
                DateTimePicker2.Text = ""
                ComboBox1.Text = ""
                ComboBox2.Text = ""

                conn.Close()
                load_data()
            Catch ex As MySqlException
                MessageBox.Show(ex.Message)

            Finally
                conn.Dispose()
            End Try
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub
End Class