Imports System.Data
Imports System.Data.OleDb
Public Class Leavee
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gSQL As String
    Private Sub Leavee_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Call connectdatabase()
        Call fillcboTL()
        Call SetGrid()
        Call GetDocNo()
    End Sub
    Private Sub fillcboTL()
        gSQL = "Select * From Type_leave"
        Dim da As New OleDbDataAdapter(gSQL, cnn)
        Dim dt As New DataTable
        da.Fill(dt)
        With cboTL
            .DataSource = dt
            .DisplayMember = dt.Columns.Item("TypeLe_name").ColumnName
            .ValueMember = dt.Columns.Item("TypeLe_ID").ColumnName
        End With
    End Sub
    Private Sub PictureBox6_Click(sender As Object, e As EventArgs) Handles PictureBox6.Click
        gSQL = "Insert Into Leave Values ("
        gSQL += "'" & tno.Text & "',"
        gSQL += "'" & tEno.Text & "',"
        gSQL += "'" & tEname.Text & "',"
        gSQL += "'" & DateTimePicker1.Text & "',"
        gSQL += "'" & cboTL.SelectedValue & "')"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            .ExecuteNonQuery()
        End With
        MessageBox.Show("เพิ่มรายการใหม่แล้ว", "เพิ่มขอ้มูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Call UpdateDocNo()
        Call Cleartext()
        GetDocNo()
    End Sub
    Private Sub Cleartext()
        txtSearch.Text = ""
        tEno.Text = ""
        cboTL.SelectedValue = ""
        tEname.Text = ""
        DateTimePicker1.Text = ""
        GetDocNo()
    End Sub

    Private Sub PictureBox4_Click(sender As Object, e As EventArgs) Handles PictureBox4.Click
        Dim dt As New DataTable
        Dim cmd As New OleDbCommand

        gSQL = "Select Leave_Id,Employee_ID,Employee_name,Leave_Date,TypeLe_ID From Leave Where Employee_name ='" & txtSearch.Text & "'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader

            If dr.HasRows Then
                dt.Load(dr)
                dgvLeave.DataSource = dt
                SetGrid()
            End If
            dr.Close()
        End With
    End Sub
    Private Sub SetGrid()
        If dgvLeave.RowCount > 0 Then
            dgvLeave.Columns(0).HeaderText = "รหัสการลา"
            dgvLeave.Columns(1).HeaderText = "รหัสพนักงาน"
            dgvLeave.Columns(2).HeaderText = "ชื่อพนักงาน"
            dgvLeave.Columns(2).Width = 170
            dgvLeave.Columns(3).HeaderText = "วันที่ลา"
            dgvLeave.Columns(4).HeaderText = "รหัสประเภทการลา"
        End If
    End Sub

    Private Sub PictureBox3_Click(sender As Object, e As EventArgs) Handles PictureBox3.Click
        wel.Show()
        Hide()

    End Sub

    Private Sub GetDocNo()
        gSQL = "Select * From RunDoc Where DocNo = 'LEA'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader()
        End With
        dr.Read()
        If dr.HasRows Then
            tNo.Text = dr.Item("RunNo") + 1
        Else

        End If
        dr.Close()
    End Sub
    Private Sub UpdateDocNo()
        gSQL = "Update RunDoc Set RunNo = '" & tNo.Text & "'"
        gSQL += " Where DocNo = 'LEA'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn 
            .ExecuteNonQuery()
        End With
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        leaSR.Show()
    End Sub
End Class