Imports System.Data
Imports System.Data.OleDb
Public Class Health
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gSQL As String
    Private Sub Health_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Call connectdatabase()
        Call fillcboN()
        Call Cleartext()
        Call SetGrid()
        Call GetDocNo()
    End Sub

    Private Sub fillcboN()
        gSQL = "Select * From Institution_Train"
        Dim da As New OleDbDataAdapter(gSQL, cnn)
        Dim dt As New DataTable
        da.Fill(dt)
        With cboN
            .DataSource = dt
            .DisplayMember = dt.Columns.Item("Ins_name").ColumnName
            .ValueMember = dt.Columns.Item("Ins_ID").ColumnName
        End With
    End Sub
    Private Sub GetDocNo()
        gSQL = "Select * From RunDoc Where DocNo = 'HRF'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader()
        End With
        dr.Read()
        If dr.HasRows Then
            hNo.Text = dr.Item("RunNo") + 1
        Else

        End If
        dr.Close()
    End Sub
    Private Sub UpdateDocNo()
        gSQL = "Update RunDoc Set RunNo = '" & hNo.Text & "'"
        gSQL += " Where DocNo = 'HRF'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            .ExecuteNonQuery()
        End With
    End Sub
    Private Sub hNo_TextChanged(sender As Object, e As EventArgs) Handles hNo.TextChanged

    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 Employee_ID,Employee_name,H_date,Ins_ID From health Where Employee_name  Like '%" & txtSearch.Text & "%'"

        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader
        End With

        If dr.HasRows Then
            dt.Load(dr)
            dgvhealth.DataSource = dt
            SetGrid()

        End If
        dr.Close()
    End Sub

    Private Sub PictureBox6_Click(sender As Object, e As EventArgs) Handles PictureBox6.Click
        gSQL = "Insert Into health Values ("
        gSQL += "'" & hNo.Text & "',"
        gSQL += "'" & hEno.Text & "',"
        gSQL += "'" & hEname.Text & "',"
        gSQL += "'" & DateTimePicker1.Text & "',"
        gSQL += "'" & cboN.SelectedValue & "',"
        gSQL += "'" & hFiles.Text & "')"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            .ExecuteNonQuery()
        End With
        MessageBox.Show("เพิ่มรายการใหม่แล้ว", "เพิ่มขอ้มูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Call UpdateDocNo()
        Call Cleartext()
    End Sub

    Private Sub Cleartext()
        txtSearch.Text = ""
        hEno.Text = ""
        cboN.SelectedValue = ""
        hEname.Text = ""
        hFiles.Text = ""

    End Sub
    Private Sub SetGrid()
        If dgvhealth.RowCount > 0 Then
            dgvhealth.Columns(0).HeaderText = "รหัสพนักงาน"
            dgvhealth.Columns(1).HeaderText = "ชื่อพนักงาน"
            dgvhealth.Columns(1).Width = 170
            dgvhealth.Columns(2).HeaderText = "วันที่ตรวจ"
            dgvhealth.Columns(2).Width = 120
            dgvhealth.Columns(3).HeaderText = "รหัสหน่วยงาน"

        End If
    End Sub
    Private Sub hEno_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles hEno.KeyPress
        If e.KeyChar = Chr(13) Then
            gSQL = "Select * From Employeee Where Employee_ID = '" & hEno.Text & "'"
            With cmd
                .CommandType = CommandType.Text
                .CommandText = gSQL
                .Connection = cnn
                dr = .ExecuteReader()
            End With
            dr.Read()
            If dr.HasRows Then
                hEname.Text = dr.Item("Employee_name").ToString
            Else
                MessageBox.Show("ไม่พบข้อมูลลูกค้า")
            End If
            dr.Close()

        End If
    End Sub


    Private Sub PictureBox7_Click(sender As Object, e As EventArgs) Handles PictureBox7.Click
        wel.Show()
        Hide()
    End Sub

    Private Sub PictureBox1_Click(sender As Object, e As EventArgs) Handles PictureBox1.Click
        '/ ประกาศใช้งาน Open File Dialog ในแบบ Run Time
        Dim dlgOpenFile As OpenFileDialog = New OpenFileDialog()

        ' / ตั้งค่าการใช้งาน Open File Dialog
        With dlgOpenFile
            .InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
            .Title = "เลือกไฟล์ MS Excel"
            .Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
            .FilterIndex = 1
            .RestoreDirectory = True
        End With
        '/ หากเลือกปุ่ม OK หลังจากการ Browse ...
        If dlgOpenFile.ShowDialog() = DialogResult.OK Then

            '  If dlgOpenFile.ShowDialog() = DialogResult.OK Then
            hFiles.Text = dlgOpenFile.FileName
            Dim strConn As String =
                " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
                dlgOpenFile.FileName & ";" &
                " Extended Properties=""Excel 12.0 Xml; HDR=YES"";"
            Dim Conn As New OleDbConnection(strConn)
            Conn.Open()
        End If
    End Sub

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

    End Sub
End Class