Imports System.Data
Imports System.Data.OleDb
Public Class newtimework
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gSQL As String

    Private Sub newtimework_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Call connectdatabase()

        'Call SetupDGVData()
    End Sub
    ' / --------------------------------------------------------------------
    ' / เลือกไฟล์ Excel
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.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
            txtFileName.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()
            '/ มอง WorkSheet ให้เป็นตารางข้อมูล (Table)
            Dim dtSheets As DataTable = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            Dim drSheet As DataRow

            cmbSheetName.Items.Clear()
            '/ นำรายชื่อ WorkSheet ทั้งหมด มาเก็บไว้ที่ ComboBox เพื่อรอให้ User เลือกนำไปใช้งาน
            For Each drSheet In dtSheets.Rows
                cmbSheetName.Items.Add(drSheet("TABLE_NAME").ToString)
            Next
            Conn.Close()
        End If

    End Sub
    ' / --------------------------------------------------------------------
    '// เลือก WorkSheet แล้วแสดงผลข้อมูลลงในตารางกริด
    Private Sub cmbSheetName_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSheetName.SelectedIndexChanged
        Dim Conn As OleDbConnection
        Dim Comm As OleDbCommand
        Dim DAP As OleDbDataAdapter
        Dim DS As DataSet

        Dim strConn As String =
            " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
            txtFileName.Text & ";" &
            " Extended Properties=""Excel 12.0 Xml; HDR=YES"";"
        Try
            Conn = New OleDbConnection
            Conn.ConnectionString = strConn
            Comm = New OleDbCommand
            '/ เสมือน WorkSheet เป็น Table ในฐานข้อมูล
            Comm.CommandText = "Select * FROM [" & cmbSheetName.Text & "]"
            Comm.Connection = Conn
            DAP = New OleDbDataAdapter(Comm)
            DS = New DataSet
            Conn.Open()
            DAP.Fill(DS, "Sheet1")
            '/ ผูกข้อมูล (Bound Data) เข้ากับ Sheet1
            dgvData.DataSource = DS.Tables("Sheet1")

            '//
            Call SetupDGVData()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            'Finally
            '    Conn = Nothing
            '    Comm = Nothing
            '    DAP = Nothing
            '    DS = Nothing
        End Try
    End Sub



    ' / --------------------------------------------------------------------
    '// Initialize DataGridView @Run Time
    Private Sub SetupDGVData()
        With dgvData
            .RowHeadersVisible = False
            .AllowUserToAddRows = False
            .AllowUserToDeleteRows = False
            .AllowUserToResizeRows = False
            .MultiSelect = False
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            .ReadOnly = False
            .Font = New Font("Tahoma", 9)
            ' Autosize Column
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            '// Even-Odd Color
            .AlternatingRowsDefaultCellStyle.BackColor = Color.AliceBlue
            ' Adjust Header Styles
            With .ColumnHeadersDefaultCellStyle
                .BackColor = Color.Navy
                .ForeColor = Color.Black ' Color.White
                .Font = New Font("Tahoma", 9, FontStyle.Bold)
            End With
        End With
    End Sub

    Private Sub frmExcel2DataGrid_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Me.Dispose()
        Application.Exit()
    End Sub

    ' / ------------------------------------------------------------------
    ' / ฟังค์ชั่นที่เราสามารถกำหนด Path ให้กับโปรแกรมของเราเอง
    ' / Ex.
    ' / AppPath = C:\My Project\bin\debug
    ' / Replace "\bin\debug" with ""
    ' / Return : C:\My Project\
    Function MyPath(AppPath As String) As String
        AppPath = AppPath.ToLower()
        MyPath = AppPath.Replace("\bin\debug", "").Replace("\bin\release", "")
        '// If not found folder then put the \ (BackSlash ASCII Code = 92) at the end.
        If Microsoft.VisualBasic.Right(MyPath, 1) <> Chr(92) Then MyPath = MyPath & Chr(92)
    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        dgvData.DataSource = ""
        txtFileName.Text = ""
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        wel.Show()
        Me.Hide()
    End Sub
    'Private Sub load_data()

    '    Dim checkboxcol As New DataGridViewCheckBoxColumn
    '    checkboxcol.Width = 40
    '    checkboxcol.Name = "checkboxcol"
    '    checkboxcol.HeaderText = ""
    '    dgvData.Columns.Insert(0, checkboxcol)
    'End Sub
    Private Sub SetHeaderGrid()
        If dgvData.RowCount > 0 Then
            dgvData.Columns(0).HeaderText = "รหัสพนักงาน"
            dgvData.Columns(1).HeaderText = "ชื่อพนักงาน"
            dgvData.Columns(2).HeaderText = "เดือน"
            dgvData.Columns(3).HeaderText = "วันที่"
            dgvData.Columns(4).HeaderText = "เวลาเข้างานเช้า"
            dgvData.Columns(5).HeaderText = "เวลาออกงานเช้า"
            dgvData.Columns(6).HeaderText = "เวลาเข้างานบ่าย"
            dgvData.Columns(7).HeaderText = "เวลาออกงานบ่าย"
            dgvData.Columns(8).HeaderText = "เวลาเข้างานOT"
            dgvData.Columns(9).HeaderText = "เวลาออกงานOT"
        End If
    End Sub

    'Private Sub Button5_Click(sender As Object, e As EventArgs)
    '    Dim i As Integer = 0
    '    For i = 0 To dgvData.RowCount - 1
    '        dgvData.Rows(i).Cells(0).Value = True
    '    Next
    'End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        '        ''create connection 
        '        Dim conn As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;data source= D:\Project\WindowsApp1\Database.accdb;")
        '        conn.Open()

        '        'Dim comm As SqlCommand = New SqlCommand()
        '        'comm.Connection = conn

        '        ''insert data to sql database row by row
        '        Dim a As String
        '        Dim b As String
        '        Dim c As String
        '        Dim d As String
        '        Dim ee As String
        '        Dim e1 As String
        '        Dim f As String
        '        Dim f1 As String
        '        Dim ii As String
        '        Dim i1 As String
        '        Dim qty As String
        '        Dim status As String

        '        For i As Integer = 0 To Me.dgvData.Rows.Count
        '            a = Me.dgvData.Rows(i).Cells(0).ToString()
        '            b = Me.dgvData.Rows(i).Cells(1).ToString()
        '            c = Me.dgvData.Rows(i).Cells(2).ToString()
        '            d = Me.dgvData.Rows(i).Cells(3).ToString()
        '            ee = Me.dgvData.Rows(i).Cells(4).ToString()
        '            e1 = Me.dgvData.Rows(i).Cells(5).ToString()
        '            f = Me.dgvData.Rows(i).Cells(6).ToString()
        '            f1 = Me.dgvData.Rows(i).Cells(7).ToString()
        '            ii = Me.dgvData.Rows(i).Cells(8).ToString()
        '            i1 = Me.dgvData.Rows(i).Cells(9).ToString()
        '            qty = Me.dgvData.Rows(i).Cells(10).ToString()
        '            status = Me.dgvData.Rows(i).Cells(11).ToString()

        '            gSQL = "insert into Timework (Employee_ID,Employee_name,TimeW_month,TimeW_Date,TimeW_in1,TimeW_out1,TimeW_in2,TimeW_out2,TimeW_inOT1,TimeW_outOT2,TimeW_qty,TimeW_statas)
        'values('" & a & "','" & b & "','" & c & "','" & d & "','" & ee & "','" & e1 & "','" & f & "','" & f1 & "','" & ii & "','" & i1 & "','" & qty & "','" & status & "',)"
        '            'comm.ExecuteNonQuery()
        '        Next
        '        With cmd
        '            .CommandType = CommandType.Text
        '            .CommandText = gSQL
        '            .Connection = cnn
        '            .ExecuteNonQuery()
        '        End With
        '        conn.Close()
        '    End Sub



        '    Dim conn As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;data source= D:\Project\WindowsApp1\Database.accdb;")
        '    For Each row As DataGridViewRow In dgvData.Rows
        '        Dim select1 As Boolean = Convert.ToBoolean(row.Cells("checkboxcol").Value)
        '        If select1 Then
        '            conn.Open()

        '            Dim sql As String = "Insert into Timework (Employee_ID,Employee_name,TimeW_month,TimeW_Date,TimeW_in1,TimeW_out1,TimeW_in2,TimeW_out2,TimeW_inOT1,TimeW_outOT2,TimeW_qty,TimeW_statas)
        'values (@รหัสพนักงาน,@ชื่อพนักงาน,@เดือน,@วันที่,@เวลาเข้างานเช้า,@เวลาออกงานเช้า,@เวลาเข้างานบ่าย,@เวลาออกงานบ่าย,@เวลาเข้างานOT,@เวลาออกงาน OT,@รวมจำนวนชั่วโมง OT,@สถานะ)"
        '            Dim cmd As New OleDbCommand(sql, conn)
        '            'cmd.Parameters.AddWithValue(dgvData.Item, row.Cells("TimeW_ID").Value)
        '            'salary.eno.Text = dgvem.Rows(e.RowIndex).Cells(0).Value
        '            For i As Integer = 0 To dgvData.Rows.Count - 1
        '                cmd.Parameters.AddWithValue("@รหัสพนักงาน", row.Cells("Employee_ID").Value)
        '                'cmd.Parameters.AddWithValue("@รหัสพนักงาน", conn).Value = dgvData.Rows(i).calls(0).value.tostring()
        '                cmd.Parameters.AddWithValue("@ชื่อพนักงาน", row.Cells("Employee_name").Value)
        '                cmd.Parameters.AddWithValue(“@เดือน”, row.Cells("TimeW_month").Value)
        '                cmd.Parameters.AddWithValue(“@วันที่”, row.Cells("TimeW_Date").Value)
        '                cmd.Parameters.AddWithValue(“@เวลาเข้างานเช้า”, row.Cells("TimeW_in1").Value)
        '                cmd.Parameters.AddWithValue(“@เวลาออกงานเช้า”, row.Cells("TimeW_out1").Value)
        '                cmd.Parameters.AddWithValue(“@เวลาเข้างานบ่าย”, row.Cells("TimeW_in2 ").Value)
        '                cmd.Parameters.AddWithValue(“@เวลาออกงานบ่าย”, row.Cells("TimeW_out2").Value)
        '                cmd.Parameters.AddWithValue(“@เวลาเข้างานOT”, row.Cells("TimeW_inOT1").Value)
        '                cmd.Parameters.AddWithValue(“@เวลาออกงาน OT”, row.Cells("TimeW_outOT2").Value)
        '                cmd.Parameters.AddWithValue(“@รวมจำนวนชั่วโมง OT”, row.Cells("TimeW_qty").Value)
        '                cmd.Parameters.AddWithValue(“@สถานะ”, row.Cells("TimeW_statas").Value)
        '                cmd.ExecuteNonQuery()
        '                conn.Close()
        '            Next
        '        End If
        '    Next
        '    MessageBox.Show("บันทึกรายการเรียบร้อย")
        'End Sub

        For i As Integer = 0 To Me.dgvData.Rows.Count
            gSQL = "Insert Into work Values ("
            gSQL += "'" & dgvData.Columns(0).ToString & "',"
            gSQL += "'" & dgvData.Columns(1).ToString & "',"
            gSQL += "'" & dgvData.Columns(2).ToString & "',"
            gSQL += "'" & dgvData.Columns(3).ToString & "',"
            gSQL += "'" & dgvData.Columns(4).ToString & "',"
            gSQL += "'" & dgvData.Columns(5).ToString & "',"
            gSQL += "'" & dgvData.Columns(6).ToString & "',"
            gSQL += "'" & dgvData.Columns(7).ToString & "',"
            gSQL += "'" & dgvData.Columns(8).ToString & "',"
            gSQL += "'" & dgvData.Columns(9).ToString & "',"
            gSQL += "'" & dgvData.Columns(10).ToString & "',"
            gSQL += "'" & dgvData.Columns(11).ToString & "')"
            With cmd
                .CommandType = CommandType.Text
                .CommandText = gSQL
                .Connection = cnn
                .ExecuteNonQuery()
            End With
        Next
        MessageBox.Show("เพิ่มรายการใหม่แล้ว", "เพิ่มขอ้มูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub
End Class