Imports MySql.Data.MySqlClient
Public Class barangaypurok
    Public sCon As MySqlConnection = New MySqlConnection
    Public sComand As MySqlDataAdapter = New MySqlDataAdapter
    Public ds As DataSet = New DataSet
    Dim scrollVal As Integer
    Dim pagingAdapter As MySqlDataAdapter
    Dim pagingDS As DataSet
    Dim pagingDS2 As DataSet
    Private sRecord_Count As Integer
    Dim conn As MySqlConnection
    Dim cmd As MySqlCommand
    Dim dr As MySqlDataReader
    Dim connectionString As New MySqlConnection("server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706")
    Private Sub hhldata_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
        conn.Close()
        conn.Dispose()
        connectionString.Close()
        connectionString.Dispose()
        MySqlConnection.ClearAllPools()
    End Sub
    Private Sub barangaypurok_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        conns()
        selectbargy()
        selectpurok()
        fillist()
        ComboBox1.Text = ""
        ComboBox2.Text = ""
        countbrgy()
        countpurok()
        Label3.Visible = False
        conn.Close()
        conn.Dispose()
        connectionString.Close()
        connectionString.Dispose()
        MySqlConnection.ClearAllPools()
    End Sub
    Private Sub selectbargy()
        Dim adapter As New MySqlDataAdapter("SELECT * FROM purok WHERE barangay LIKE '%" & ComboBox1.Text & "%'  ORDER BY barangay ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        ComboBox1.DataSource = table
        ComboBox1.ValueMember = "barangay"
        ComboBox1.DisplayMember = "barangay"
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(2)).Distinct.ToList
        ComboBox1.DataSource = lst
    End Sub
    Private Sub selectpurok()
        Dim adapter As New MySqlDataAdapter("SELECT * FROM purok WHERE purok LIKE '%" & ComboBox2.Text & "%'  ORDER BY purok ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        ComboBox2.DataSource = table
        ComboBox2.ValueMember = "purok"
        ComboBox2.DisplayMember = "purok"
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(1)).Distinct.ToList
        ComboBox2.DataSource = lst
    End Sub
    Private Sub conns()
        Try
            conn = New MySqlConnection("server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706")
            conn.Open()
        Catch ex As Exception
            MessageBox.Show("Please Close to finish update 5 seconds & Open the program again. Or contact the programmer", "Update",
  MessageBoxButtons.OK, MessageBoxIcon.Error)
            End
        End Try
    End Sub
    Private Sub fillist()
        conns()
        cmd = New MySqlCommand("SELECT * FROM purok where barangay LIKE  '%" & ComboBox1.Text & "%' AND purok LIKE  '%" & ComboBox2.Text & "%'  ORDER BY id DESC", conn)
        cmd.ExecuteNonQuery()
        conn.Close()
        pagingAdapter = New MySqlDataAdapter(cmd)
        pagingDS = New DataSet()
        pagingDS2 = New DataSet()
        conn.Open()
        pagingAdapter.Fill(pagingDS2, "purok_table")
        pagingAdapter.Fill(pagingDS, scrollVal, 20, "purok_table")
        conn.Close()
        DataGridView1.DataSource = pagingDS
        DataGridView1.DataMember = "purok_table"
        Dim table As New DataTable()
        pagingAdapter.Fill(table)
        Dim lst = (From row In table.AsEnumerable
                  Select row.Field(Of String)(1)).Distinct.Count
        Label5.Text = lst
        With DataGridView1
            .Columns(0).HeaderCell.Value = "ID No."
            .Columns(2).HeaderCell.Value = "Barangay"
            .Columns(1).HeaderCell.Value = "Purok"
        End With
        DataGridView1.Columns.Item("purok").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        DataGridView1.Columns.Item("barangay").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        DataGridView1.Columns.Item("id").Visible = False
        DataGridView1.DefaultCellStyle.Font = New Font("arial", 12)
        'DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        'DataGridView1.Dock = DockStyle.Fill
         Dashboard.counters()
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.Click

    End Sub

    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.Click

    End Sub
    Private Sub clear()
        countbrgy()
        countpurok()
        fillist()
        scrollVal = 0
        ComboBox1.Text = ""
        ComboBox2.Text = ""
        Label3.Text = ""
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        clear()
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged_1(sender As Object, e As EventArgs) Handles ComboBox1.TextChanged
        fillist()
    End Sub

    Private Sub ComboBox2_SelectedIndexChanged_1(sender As Object, e As EventArgs) Handles ComboBox2.TextChanged
        fillist()
    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        Try
            scrollVal = scrollVal - 20
            If scrollVal <= 0 Then
                scrollVal = 0
            End If
            pagingDS.Clear()
            pagingAdapter.Fill(pagingDS, scrollVal, 20, "purok_table")
        Catch ex As Exception
            MessageBox.Show("Please Close to finish update 5 seconds & Open the program again.", "Update",
  MessageBoxButtons.OK, MessageBoxIcon.Error)
            End
        End Try
    End Sub

    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        Try
            scrollVal = scrollVal + 20
            'If scrollVal > 23 Then
            '    scrollVal = 18
            'End If
            If scrollVal >= 0 Then
                'scrollVal = 40
            End If
            pagingDS.Clear()
            pagingAdapter.Fill(pagingDS, scrollVal, 20, "purok_table")
        Catch ex As Exception
            MessageBox.Show("Please Close to finish update 5 seconds & Open the program again.", "Update",
 MessageBoxButtons.OK, MessageBoxIcon.Error)
            End
        End Try
    End Sub
    Private Sub SaveData()
        If ComboBox1.Text = "" Or ComboBox2.Text = "" Then
            MessageBox.Show("Please Check Input", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Call conns()
            cmd = New MySqlCommand("Insert into purok (purok, barangay) Values" _
        & " ('" & ComboBox2.Text & "', '" & ComboBox1.Text & "')", conn)
            cmd.ExecuteNonQuery()
            conn.Close()
            MsgBox("Successfully Saved", MsgBoxStyle.Information, "Save")
            fillist()
            clear()
        End If
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        SaveData()
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        If ComboBox1.Text = "" Or ComboBox2.Text = "" Or Label3.Text = "" Then
            MessageBox.Show("Please Check Input", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            If MsgBox("Are you sure do you want to delete?", CType(MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Yes Then
                Call conns()
                cmd = New MySqlCommand("Delete from purok where id = '" & Label3.Text & "'", conn)
                cmd.ExecuteNonQuery()
                MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
                fillist()
                clear()
            End If
        End If
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        If ComboBox1.Text = "" Or ComboBox2.Text = "" Or Label3.Text = "" Then
            MessageBox.Show("Please Check Input", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Call conns()
            cmd = New MySqlCommand("Update purok set purok = '" & ComboBox2.Text & "', barangay ='" & ComboBox1.Text & "' where id = '" & Label3.Text & "'", conn)
            cmd.ExecuteNonQuery()
            conn.Close()
            MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
            fillist()
            clear()
        End If
    End Sub
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Try
            Dim i As Integer
            With DataGridView1
                If e.RowIndex >= 0 Then
                    i = .CurrentRow.Index
                    Label3.Text = .Rows(i).Cells("id").Value.ToString
                    ComboBox2.Text = .Rows(i).Cells("purok").Value.ToString
                    ComboBox1.Text = .Rows(i).Cells("barangay").Value.ToString
                End If
            End With
        Catch exx As Exception
        End Try
    End Sub
    Private Sub countbrgy()
        Call conns()
        cmd = New MySqlCommand("SELECT * FROM purok ORDER BY barangay ASC", conn)
        cmd.ExecuteNonQuery()
        conn.Close()
        pagingAdapter = New MySqlDataAdapter(cmd)
        pagingDS2 = New DataSet()
        conn.Open()
        pagingAdapter.Fill(pagingDS2, "barangay_table")
        conn.Close()
        sRecord_Count = pagingDS2.Tables(0).Rows.Count
        Dim table As New DataTable()
        pagingAdapter.Fill(table)
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(2)).Distinct.Count
        Label7.Text = lst
    End Sub
    Private Sub countpurok()
        Call conns()
        cmd = New MySqlCommand("SELECT * FROM purok ORDER BY purok ASC", conn)
        cmd.ExecuteNonQuery()
        conn.Close()
        pagingAdapter = New MySqlDataAdapter(cmd)
        pagingDS2 = New DataSet()
        conn.Open()
        pagingAdapter.Fill(pagingDS2, "purok_table")
        conn.Close()
        sRecord_Count = pagingDS2.Tables(0).Rows.Count
        Dim table As New DataTable()
        pagingAdapter.Fill(table)
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(1)).Distinct.Count
        'Label5.Text = lst
        Label5.Text = sRecord_Count
    End Sub

    Private Sub Label5_Click(sender As Object, e As EventArgs) Handles Label5.Click

    End Sub

    Private Sub Label4_Click(sender As Object, e As EventArgs) Handles Label4.Click

    End Sub
End Class