Untitled

Imports Google.Apis.Sheets.v4
Imports Google.Apis.Sheets.v4.Data
Imports System.Data
Imports System.Globalization
Imports System.Threading
Imports ClosedXML.Excel
Public Class FormMenu3
    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 FormMenu3_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 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
        txtEmployeeID.Text = String.Empty
        txtFullName.Text = String.Empty
        txtIDCardNumber.Text = String.Empty
        txtContactNumber.Text = String.Empty
        txtDepartment.Text = String.Empty
        txtEmployeeStatus.Text = String.Empty
        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 btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
        Dim dt As DataTable = CType(DataGridView2.DataSource, DataTable)
        If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
            Using workbook As New XLWorkbook()
                workbook.Worksheets.Add(dt, "Sheet1")
                Dim saveFileDialog As New SaveFileDialog()
                saveFileDialog.Filter = "Excel Files|*.xlsx"
                saveFileDialog.Title = "Save an Excel File"
                If saveFileDialog.ShowDialog() = DialogResult.OK Then
                    Try
                        workbook.SaveAs(saveFileDialog.FileName)
                        MessageBox.Show("Data exported successfully!", "Export", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Catch ex As Exception
                        MessageBox.Show("Error while saving the file: " & ex.Message, "Export Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End Try
                End If
            End Using
        Else
            MessageBox.Show("No data to export!", "Export Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End If
    End Sub
End Class