Imports System.Data.SqlClient
Imports Npgsql
Module SQL_Module
    Public 版本 As String = "2024102901"
    Public gUserName, 登入人部門, 版本號 As String
    Public SQL1, SQL2, SQL3, SQL4, SQL5, SQL6, SQL7, SQL8, SQL9, SQL10 As String
    Public CC(11), 重新計算, 首次進入 As Boolean
    Public lastFoundRowIndex, lastFoundColIndex, NU1 As Integer
    Public PA, PA1 As String

    Public 登入人IP As String = "59.125.246.164"
    Public ConString As String = "Host=" & 登入人IP & ";Port=4556;Database=""htp"";Username=odoo;Password=odootyughj;Pooling=true;Maximum Pool Size=200;Timeout=0"
    Public conn1 As New SqlConnection : Public cmd1 As New SqlCommand
    Public dr1 As SqlDataReader : Public da1 As New SqlDataAdapter

    Public conn As New NpgsqlConnection : Public cmd As New NpgsqlCommand
    Public dr As NpgsqlDataReader : Public da As New NpgsqlDataAdapter
    Friend Sub ConnOpen_SQL()
        登入人IP = "59.125.246.164,4567"
        ConString = "Data Source=" & 登入人IP & ";Initial Catalog=HTODOO;Persist Security Info=True;User ID=b70340;Password=Lee0911274990;Max pool size = 200;Connection Timeout=0"
        conn1.Close() : If conn1.State = ConnectionState.Closed Then : conn1.ConnectionString = ConString : conn1.Open() : End If
    End Sub
    Friend Sub CmdSet_For_DGV_SQL()
        cmd1.Connection = conn1 : cmd1.CommandText = SQL1 : da1.SelectCommand = cmd1
    End Sub
    Friend Sub CmdSet_For_dr_SQL()
        cmd1.CommandText = SQL1 : cmd1.Connection = conn1 : dr1 = cmd1.ExecuteReader
    End Sub
    Friend Sub ConnOpen()
        登入人IP = "59.125.246.164"
        ConString = "Host=" & 登入人IP & ";Port=4556;Database=""htp"";Username=odoo;Password=odootyughj;Pooling=true;Maximum Pool Size=200;Timeout=0"
        conn.Close() : If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
    End Sub
    Friend Sub CmdSet_For_DGV()
        cmd.Connection = conn : cmd.CommandText = SQL1 : da.SelectCommand = cmd
    End Sub
    Friend Sub CmdSet_For_dr()
        cmd.CommandText = SQL1 : cmd.Connection = conn : dr = cmd.ExecuteReader
    End Sub

    Public Sub 查詢DGV中的關鍵字(ByVal dgv As DataGridView, ByVal txtKeyword As TextBox)
        Dim keyword As String = txtKeyword.Text
        Dim found As Boolean = False
        dgv.ClearSelection()        ' 清除先前的选择
        Dim numColumns As Integer = dgv.Columns.Count        ' 获取 DataGridView 的列数
        Dim startRowIndex As Integer = If(lastFoundRowIndex = -1, 0, lastFoundRowIndex + 1)
        For colIndex As Integer = If(lastFoundRowIndex = -1, 0, lastFoundColIndex) To numColumns - 1
            For rowIndex As Integer = startRowIndex To dgv.Rows.Count - 1
                Dim cellValue As String
                If IsNothing(dgv.Rows(rowIndex).Cells(colIndex).Value) Then
                    cellValue = ""
                Else
                    cellValue = dgv.Rows(rowIndex).Cells(colIndex).Value.ToString()
                End If
                If cellValue.Contains(keyword) Then
                    dgv.Rows(rowIndex).Selected = True : dgv.FirstDisplayedScrollingRowIndex = rowIndex : NU1 = rowIndex : found = True
                    lastFoundRowIndex = rowIndex : lastFoundColIndex = colIndex : Exit For
                End If
            Next : If found Then : Exit For : End If
        Next
        If Not found Then
            lastFoundRowIndex = -1 : lastFoundColIndex = -1 : dgv.FirstDisplayedScrollingRowIndex = 0 : 重新計算 = True : dgv.ClearSelection()
        Else : 重新計算 = False
        End If
    End Sub
End Module