Imports MySql.Data.MySqlClient
Public Class MasterlistandOldList
    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 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", "hhl no connection",
  MessageBoxButtons.OK, MessageBoxIcon.Error)
            End
        End Try
    End Sub
    Private Sub MasterlistandOldList_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        selectbrgy()
    End Sub
    Private Sub selectbrgy()
        Try
            Dim adapter As New MySqlDataAdapter("SELECT barangay FROM purok 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)(0)).Distinct.ToList
            ComboBox1.DataSource = lst

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            connectionString.Close()
            connectionString.Dispose()
        End Try
    End Sub
    Private Sub selectpurokfromnew()
        Try
            Dim adapter As New MySqlDataAdapter("SELECT address FROM full_data where barangay LIKE  '%" & ComboBox1.Text & "%' ORDER BY address ASC", connectionString)
            Dim table As New DataTable()
            adapter.Fill(table)
            ComboBox2.DataSource = table
            ComboBox2.ValueMember = "address"
            ComboBox2.DisplayMember = "address"
            Dim lst = (From row In table.AsEnumerable
                       Select row.Field(Of String)(0)).Distinct.ToList
            ComboBox2.DataSource = lst

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            connectionString.Close()
            connectionString.Dispose()
        End Try
    End Sub
    Private Sub fillistnewlist()
        Dim table As New DataTable()
        Try

            conns()
            Dim adapter As New MySqlDataAdapter("SELECT name,precinct_number,barangay,address FROM full_data where barangay LIKE  '%" & ComboBox1.Text & "%' AND address LIKE '%" & ComboBox2.Text & "%'  ORDER BY name ASC", conn)
            adapter.Fill(table)

            DataGridView1.DataSource = table
            With DataGridView1
                .Columns(0).HeaderCell.Value = "Name"
                .Columns(1).HeaderCell.Value = "Precinct"
                .Columns(2).HeaderCell.Value = "Barangay"
                .Columns(3).HeaderCell.Value = "Purok"
            End With
            DataGridView1.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns(3).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.DefaultCellStyle.Font = New Font("arial", 12)

        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Sub
    Private Sub fillistpurokbyBrgy()
        Dim table As New DataTable()
        Try

            conns()
            Dim adapter As New MySqlDataAdapter("SELECT barangay,purok  FROM purok where barangay LIKE  '%" & ComboBox1.Text & "%'  ORDER BY purok ASC", conn)
            adapter.Fill(table)

            DataGridView2.DataSource = table
            With DataGridView2
                .Columns(0).HeaderCell.Value = "Barangay"
                .Columns(1).HeaderCell.Value = "Purok"
                '.Columns(2).HeaderCell.Value = "Purok"
                '.Columns(3).HeaderCell.Value = "Barangay"
            End With
            DataGridView2.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView1.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView1.Columns(3).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.DefaultCellStyle.Font = New Font("arial", 12)

        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Sub
    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        fillistpurokbyBrgy()
        'fillistnewlist()
        selectpurokfromnew()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If Label1.Text = "" Or Label1.Text = "Purok Name" Then
            MessageBox.Show("Please Select Purok", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Call conns()
            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                cmd = New MySqlCommand("Update full_data set address = '" & Label1.Text & "' where name LIKE '%" & DataGridView1.Rows(i).Cells(0).Value & "%'", conn)
                cmd.ExecuteNonQuery()
            Next
            MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")

            selectpurokfromnew()
            fillistnewlist()
        End If
    End Sub

    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged
        fillistnewlist()
    End Sub

    Private Sub DataGridView2_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView2.CellClick
        Try
            Dim i As Integer
            With DataGridView2
                If e.RowIndex >= 0 Then
                    i = .CurrentRow.Index
                    Label1.Text = .Rows(i).Cells(1).Value.ToString

                End If
            End With
        Catch exx As Exception
            conn.Close()
            conn.Dispose()
            connectionString.Close()
            connectionString.Dispose()
            MySqlConnection.ClearAllPools()
        End Try
    End Sub
    Private Sub DataGridView2_CellContentClick_1(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView2.CellDoubleClick
        Button1.PerformClick()
    End Sub
    Private Sub ComboBox2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles ComboBox2.KeyDown
        If e.KeyCode = Keys.Enter Then
            Button1.PerformClick()
        Else

        End If
    End Sub
    Private Sub Savepurok()
        If ComboBox1.Text = "" Or ComboBox2.Text = "" Then
            MessageBox.Show("Please Select Barangay", "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("Purok Successfully Saved", MsgBoxStyle.Information, "Save")
            fillistpurokbyBrgy()
        End If
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Savepurok()
    End Sub
    Private Sub delpurok()
        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 purok = '" & Label1.Text & "' and barangay = '" & ComboBox1.Text & "'", conn)
                cmd.ExecuteNonQuery()
                MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
                fillistpurokbyBrgy()
            End If
        End If
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        delpurok()
    End Sub
End Class