Imports MySql.Data.MySqlClient
Public Class addemployees
    Dim conn As MySqlConnection
    Dim cmd As New MySqlCommand
    Dim a, b, c As String
    Private Sub addemployees_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cb1_load()
        load_data()
        TextBox1.Enabled = False
        TextBox7.Enabled = False
        TextBox6.Enabled = False
        TextBox4.Enabled = False
        Max_load()
        cb2_load()
        Coun_load()
        City_load()

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


        Dim str As String
        conn.Open()

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

            End If



            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)


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


        Dim str As String
        conn.Open()

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

            End If



            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)


        End Try
    End Sub
    Private Sub Max_load()

        Dim cmd1 As New MySqlCommand(("SELECT MAX(`id`) FROM tblemployees  "), conn)

        Try
            conn.Open()

            TextBox1.Text = cmd1.ExecuteScalar().ToString()
            conn.Close()

        Catch ex As Exception

        End Try





    End Sub
    Private Sub Days_load()

        Dim cmd1 As New MySqlCommand(("SELECT `leavedays` FROM `tbldepartments` WHERE `DepartmentName`='" & ComboBox2.Text & "' "), conn)

        Try
            conn.Open()

            TextBox6.Text = cmd1.ExecuteScalar().ToString()
            conn.Close()

        Catch ex As Exception

        End Try





    End Sub
    
    Public Sub load_data()
        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"
        Dim query As String
        query = "SELECT `id` as 'ID', `EmpId` as 'EMPLOYEE ID', `FirstName` as 'FIRST NAME', `LastName` as 'LAST NAME',`Gender` as 'GENDER', `Dob` as 'BIRTH DATE', `Department` as 'DEPARTMENT',`Company` as 'COMPANY', `Address` as 'ADDRESS', `City` as 'CITY', `Country` as 'COUNTRY', `Phonenumber` as 'PHONE NUMBER',`Leave_days` as 'LEAVE DAYS' FROM `tblemployees` "




        Try
            conn.Open()


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




            conn.Close()

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

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


        Dim str As String
        conn.Open()

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

            End If



            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)


        End Try
    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 `CompanyName` FROM `tblcompany` GROUP by `CompanyName` "
            Dim da As New MySqlDataAdapter(str, conn)
            Dim dt As New DataTable
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                ComboBox3.DataSource = dt
                ComboBox3.DisplayMember = "CompanyName"
                ComboBox3.ValueMember = ""

            End If



            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)


        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)
            Label6.Text = row.Cells("ID").Value.ToString
            TextBox1.Text = row.Cells("EMPLOYEE ID").Value.ToString
            TextBox2.Text = row.Cells("FIRST NAME").Value.ToString
            TextBox3.Text = row.Cells("LAST NAME").Value.ToString
            TextBox4.Text = row.Cells("LEAVE DAYS").Value.ToString
            ComboBox1.Text = row.Cells("GENDER").Value.ToString
            DateTimePicker1.Text = row.Cells("BIRTH DATE").Value.ToString

            ComboBox2.Text = row.Cells("DEPARTMENT").Value.ToString
            TextBox12.Text = row.Cells("ADDRESS").Value.ToString
            TextBox8.Text = row.Cells("CITY").Value.ToString
            TextBox13.Text = row.Cells("COUNTRY").Value.ToString
            TextBox10.Text = row.Cells("PHONE NUMBER").Value.ToString






        End If

    End Sub
    Private Sub Label18_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label18.Click
        Dim result As Integer
        Dim dr As MySqlDataReader
        Dim conn1 As New MySqlConnection
        Dim conn3 As New MySqlConnection("server=localhost;userid=root;password=;database=elms")
        Dim cmd1 As New MySqlCommand




        If Label6.Text > "" Then
            conn.ConnectionString = "server=localhost;userid=root;password=;database=elms"


            Try

                With cmd
                    conn3.Open()
                    cmd1.Connection = conn3
                    cmd1.CommandText = "Select * from tblemployees where FirstName = '" & TextBox2.Text & "' and Lastname='" & TextBox3.Text & "' and Phonenumber='" & TextBox10.Text & "' "
                    dr = cmd1.ExecuteReader
                    If dr.HasRows Then
                        MsgBox("Duplicate Entry")
                        conn3.Close()
                    Else
                        conn.Open()
                        .Connection = conn
                        .CommandText = "update tblemployees SET EmpId='" & TextBox1.Text & "',FirstName='" & TextBox2.Text & "',LastName='" & TextBox3.Text & "',Gender='" & ComboBox1.Text & "',Dob='" & DateTimePicker1.Text & "',Department='" & ComboBox2.Text & "',Company='" & ComboBox3.Text & "',Address='" & TextBox12.Text & "',City='" & TextBox8.Text & "',Country='" & TextBox13.Text & "',Phonenumber='" & TextBox10.Text & "',Leave_days= '" & TextBox4.Text & "' where id='" & Label6.Text & "'"
                        result = .ExecuteNonQuery


                        MessageBox.Show("Updated")
                        load_data()
                        conn.Close()
                    End If

                End With


                TextBox1.Text = ""
                TextBox2.Text = ""
                TextBox3.Text = ""
                ComboBox1.Text = ""
                ComboBox2.Text = ""
                DateTimePicker1.Text = ""
                TextBox13.Text = ""
                TextBox8.Text = ""
                TextBox12.Text = ""
                TextBox10.Text = ""
                Label6.Text = ""
                TextBox4.Text = ""


                conn.Close()

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

            Finally
                conn.Dispose()
            End Try
        ElseIf TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or ComboBox1.Text = "" Or ComboBox2.Text = "" Or DateTimePicker1.Text = "" Or TextBox13.Text = "" Or TextBox8.Text = "" Or TextBox12.Text = "" Or TextBox10.Text = "" Or TextBox4.Text = "" Then
            MsgBox("Fill All The form")
        Else
            Max_load()
            conn1.ConnectionString = "server=localhost;userid=root;password=;database=elms"
            a = TextBox7.Text
            b = TextBox1.Text
            c = a + b
            TextBox1.Text = c
            Try

                With cmd
                    conn1.Open()
                    cmd.Connection = conn1
                    cmd.CommandText = "Select * from tblemployees where FirstName = '" & TextBox2.Text & "' "
                    dr = cmd.ExecuteReader
                    If dr.HasRows Then
                        MsgBox("Duplicate Entry")
                        conn1.Close()
                    Else

                        conn.Open()

                        .Connection = conn
                        .CommandText = "INSERT INTO `tblemployees`(`EmpId`, `FirstName`, `LastName`,`Gender`, `Dob`, `Department`,`Company`, `Address`, `City`, `Country`, `Phonenumber`,`Leave_days`) VALUES ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & ComboBox1.Text & "','" & DateTimePicker1.Text & "','" & ComboBox2.Text & "','" & ComboBox3.Text & "','" & TextBox12.Text & "','" & TextBox8.Text & "','" & TextBox13.Text & "','" & TextBox10.Text & "','" & TextBox6.Text & "')"

                        result = .ExecuteNonQuery

                        If result > 0 Then

                            .Connection = conn
                            .CommandText = "INSERT INTO `tblacc`(`empId`) VALUES ('" & TextBox1.Text & "')"

                            result = .ExecuteNonQuery


                        End If

                        MessageBox.Show("Inserted")
                        conn.Close()

                    End If


                End With



                load_data()

                TextBox1.Text = ""
                TextBox2.Text = ""
                TextBox3.Text = ""

                ComboBox1.Text = ""
                ComboBox2.Text = ""
                DateTimePicker1.Text = ""
                TextBox13.Text = ""
                TextBox8.Text = ""
                TextBox12.Text = ""
                TextBox10.Text = ""





                conn.Close()

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

            Finally
                conn.Dispose()
            End Try

        End If
    End Sub

    

    Private Sub Label6_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label6.TextChanged
        If Label6.Text > "" Then
            Label18.Text = "UPDATE"
           
        Else
            Label18.Text = "SAVE"
            
        End If


    End Sub

    

    Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        ComboBox1.Text = ""
        ComboBox2.Text = ""
        DateTimePicker1.Text = ""
        TextBox13.Text = ""
        TextBox8.Text = ""
        TextBox12.Text = ""
        TextBox10.Text = ""
        Label6.Text = ""
        TextBox4.Text = "0"

        Max_load()
        load_data()


    End Sub

    Private Sub TextBox10_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox10.KeyPress
        If Asc(e.KeyChar) < 48 Or Asc(e.KeyChar) > 57 Then
            e.Handled = True
            MsgBox("NUMBERS ONLY")
            TextBox10.Text = ""
        End If
    End Sub

    Private Sub TextBox10_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox10.TextChanged

        If TextBox10.TextLength > 11 Then
            MsgBox("Too Many try again!!!")
            TextBox10.Text = ""
        
        End If
        
    End Sub
   

   
    Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
        Days_load()
    End Sub
End Class