Untitled

Imports Google.Apis.Sheets.v4
Imports Google.Apis.Sheets.v4.Data
Imports System.Data
Imports System.Globalization
Imports System.Threading
Public Class FormMenu2
    Private service As SheetsService
    Private spreadsheetId As String
    Private sheetName As String
    Private dataTable As DataTable
    Public Sub New(googleSheetsService As SheetsService, sheetId As String, sheet As String)
        InitializeComponent()
        service = googleSheetsService
        spreadsheetId = sheetId
        sheetName = sheet
        dataTable = New DataTable()
        dataTable.Columns.Add("RowIndex", GetType(Integer))
        dataTable.Columns.AddRange(New DataColumn() {
            New DataColumn("รหัสพนักงาน"),
            New DataColumn("ชื่อ-สกุล"),
            New DataColumn("เลขบัตรประชาชน"),
            New DataColumn("วันเกิด"),
            New DataColumn("เบอร์ติดต่อ"),
            New DataColumn("วันเริ่มงาน"),
            New DataColumn("แผนก"),
            New DataColumn("สถานะพนักงาน"),
            New DataColumn("ที่อยู่")
        })
        DataGridView2.DataSource = dataTable
    End Sub
    Private Sub FormMenu1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim thaiCulture As New CultureInfo("th-TH")
        thaiCulture.DateTimeFormat.Calendar = New ThaiBuddhistCalendar()
        Thread.CurrentThread.CurrentCulture = thaiCulture
        Thread.CurrentThread.CurrentUICulture = thaiCulture
        DataGridView2.Columns("RowIndex").Visible = False
        DateTimePickerStartDateFrom.Enabled = False
        DateTimePickerStartDateTo.Enabled = False
        CheckBox1.Checked = False
        DateTimePickerStartDateFrom.Format = DateTimePickerFormat.Short
        DateTimePickerStartDateFrom.ShowUpDown = False
        DateTimePickerStartDateTo.Format = DateTimePickerFormat.Short
        DateTimePickerStartDateTo.ShowUpDown = False
    End Sub
    Private Sub FormMenu2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim thaiCulture As New CultureInfo("th-TH")
        thaiCulture.DateTimeFormat.Calendar = New ThaiBuddhistCalendar()
        Thread.CurrentThread.CurrentCulture = thaiCulture
        Thread.CurrentThread.CurrentUICulture = thaiCulture
        DateTimePickerStartDateFrom.Format = DateTimePickerFormat.Short
        DateTimePickerStartDateFrom.ShowUpDown = False
        DateTimePickerStartDateTo.Format = DateTimePickerFormat.Short
        DateTimePickerStartDateTo.ShowUpDown = False
        DateTimePickerStartDateFrom.Value = DateTime.Now
        DateTimePickerStartDateTo.Value = DateTime.Now
    End Sub
    Private Sub DateTimePickerStartDateFrom_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePickerStartDateFrom.ValueChanged
    End Sub
    Private Sub DateTimePickerStartDateTo_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePickerStartDateTo.ValueChanged
    End Sub
    Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged
        DateTimePickerStartDateFrom.Enabled = CheckBox1.Checked
        DateTimePickerStartDateTo.Enabled = CheckBox1.Checked
    End Sub
    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnsearch.Click
        dataTable.Rows.Clear()
        Dim range As String = $"{sheetName}!A:I"
        Dim request = service.Spreadsheets.Values.Get(spreadsheetId, range)
        Dim response = request.Execute()
        Dim values = response.Values
        If values IsNot Nothing AndAlso values.Count > 0 Then
            For i As Integer = 0 To values.Count - 1
                Dim row As IList(Of Object) = values(i)
                If row.Count >= 9 Then
                    Dim empID As String = row(0).ToString()
                    Dim fullName As String = row(1).ToString()
                    Dim idCardNumber As String = row(2).ToString()
                    Dim birthDate As String = row(3).ToString()
                    Dim contactNumber As String = row(4).ToString()
                    Dim startDate As String = row(5).ToString()
                    Dim department As String = row(6).ToString()
                    Dim empStatus As String = row(7).ToString()
                    Dim address As String = If(row.Count > 8, row(8).ToString(), "")
                    Dim isMatch As Boolean = True
                    If Not String.IsNullOrEmpty(txtEmployeeID.Text) AndAlso Not empID.Contains(txtEmployeeID.Text) Then
                        isMatch = False
                    End If
                    If Not String.IsNullOrEmpty(txtFullName.Text) AndAlso Not fullName.Contains(txtFullName.Text) Then
                        isMatch = False
                    End If
                    If Not String.IsNullOrEmpty(txtIDCardNumber.Text) AndAlso Not idCardNumber.Contains(txtIDCardNumber.Text) Then
                        isMatch = False
                    End If
                    If Not String.IsNullOrEmpty(txtContactNumber.Text) AndAlso Not contactNumber.Contains(txtContactNumber.Text) Then
                        isMatch = False
                    End If
                    If CheckBox1.Checked Then
                        If DateTimePickerStartDateFrom.Format <> DateTimePickerFormat.Custom AndAlso DateTimePickerStartDateTo.Format <> DateTimePickerFormat.Custom Then
                            Dim startDateValue As DateTime
                            If DateTime.TryParseExact(startDate, "yyyy-MM-dd", Nothing, DateTimeStyles.None, startDateValue) Then
                                If startDateValue < DateTimePickerStartDateFrom.Value.Date OrElse startDateValue > DateTimePickerStartDateTo.Value.Date Then
                                    isMatch = False
                                End If
                            Else
                                MessageBox.Show("รูปแบบวันที่ไม่ถูกต้องในข้อมูล Google Sheets สำหรับวันเริ่มงาน", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
                                isMatch = False
                            End If
                        End If
                    End If
                    If Not String.IsNullOrEmpty(txtDepartment.Text) AndAlso Not department.Contains(txtDepartment.Text) Then
                        isMatch = False
                    End If
                    If Not String.IsNullOrEmpty(txtEmployeeStatus.Text) AndAlso Not empStatus.Contains(txtEmployeeStatus.Text) Then
                        isMatch = False
                    End If
                    If isMatch Then
                        Dim dataRow As DataRow = dataTable.NewRow()
                        dataRow("รหัสพนักงาน") = empID
                        dataRow("ชื่อ-สกุล") = fullName
                        dataRow("เลขบัตรประชาชน") = idCardNumber
                        dataRow("วันเกิด") = birthDate
                        dataRow("เบอร์ติดต่อ") = contactNumber
                        dataRow("วันเริ่มงาน") = startDate
                        dataRow("แผนก") = department
                        dataRow("สถานะพนักงาน") = empStatus
                        dataRow("ที่อยู่") = address
                        dataRow("RowIndex") = i + 1
                        dataTable.Rows.Add(dataRow)
                    End If
                End If
            Next
        End If
    End Sub
    Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
        Dim result As DialogResult = MessageBox.Show("คุณต้องการแก้ไขข้อมูลนี้ใช่หรือไม่?", "ยืนยันการแก้ไข", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If result = DialogResult.Yes Then
            Try
                If DataGridView2.SelectedRows.Count > 0 Then
                    Dim selectedRow As DataGridViewRow = DataGridView2.SelectedRows(0)
                    Dim rowIndex As Integer = Convert.ToInt32(selectedRow.Cells("RowIndex").Value) ' ใช้ค่า RowIndex
                    Dim updateRange As String = $"{sheetName}!A{rowIndex}:I{rowIndex}"
                    Dim valueRange As New ValueRange()
                    valueRange.Values = New List(Of IList(Of Object)) From {
                        New List(Of Object) From {
                            selectedRow.Cells("รหัสพนักงาน").Value,
                            selectedRow.Cells("ชื่อ-สกุล").Value,
                            selectedRow.Cells("เลขบัตรประชาชน").Value,
                            selectedRow.Cells("วันเกิด").Value,
                            selectedRow.Cells("เบอร์ติดต่อ").Value,
                            selectedRow.Cells("วันเริ่มงาน").Value,
                            selectedRow.Cells("แผนก").Value,
                            selectedRow.Cells("สถานะพนักงาน").Value,
                            selectedRow.Cells("ที่อยู่").Value
                        }
                    }
                    Dim updateRequest = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, updateRange)
                    updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED
                    updateRequest.Execute()

                    MessageBox.Show("แก้ไขข้อมูลเรียบร้อย", "การแก้ไข", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Else
                    MessageBox.Show("กรุณาเลือกแถวที่ต้องการแก้ไข", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If
            Catch ex As Exception
                MessageBox.Show("เกิดข้อผิดพลาดในการแก้ไขข้อมูล: " & ex.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If
    End Sub
    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        Dim result = MessageBox.Show("คุณต้องการลบข้อมูลนี้ใช่หรือไม่?", "ยืนยันการลบ", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If result = DialogResult.Yes Then
            Try
                If DataGridView2.SelectedRows.Count > 0 Then
                    Dim selectedRow = DataGridView2.SelectedRows(0)
                    Dim rowIndex = Convert.ToInt32(selectedRow.Cells("RowIndex").Value)
                    Dim deleteRange = $"{sheetName}!A{rowIndex}:I{rowIndex}"
                    Dim clearRequest = service.Spreadsheets.Values.Clear(New ClearValuesRequest, spreadsheetId, deleteRange)
                    clearRequest.Execute()
                    MessageBox.Show("ลบข้อมูลเรียบร้อย", "การลบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    DataGridView2.Rows.Remove(selectedRow)
                Else
                    MessageBox.Show("กรุณาเลือกแถวที่ต้องการลบ", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If
            Catch ex As Exception
                MessageBox.Show("เกิดข้อผิดพลาดในการลบข้อมูล: " & ex.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If
    End Sub
End Class