Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports MySql.Data.MySqlClient
Imports System.IO
Public Class UpdateLetterInNames
    Private sCon As MySqlConnection = New MySqlConnection
    Private sComand As MySqlDataAdapter = New MySqlDataAdapter
    Private 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 table As New DataTable()
    'Dim conn As MySqlConnection
    Dim cmd As MySqlCommand
    Dim dr As MySqlDataReader
    Dim connStrr As String = "server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706"
    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 and try again.", "Close",
    'MessageBoxButtons.OK, MessageBoxIcon.Error)
    '          'End
    '      End Try
    '  End Sub
    Private Sub UpdateLetterInNames_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Label1.Text = "0"
        selecbrgy()
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        findletters()
    End Sub
    Private Sub selecbrgy()
        Try
            Dim adapter As New MySqlDataAdapter("SELECT * FROM purok ORDER BY barangay ASC", connectionString)
            Dim table As New DataTable()
            adapter.Fill(table)
            ComboBox3.DataSource = table
            ComboBox3.ValueMember = "barangay"
            ComboBox3.DisplayMember = "barangay"
            Dim lst = (From row In table.AsEnumerable
                       Select row.Field(Of String)(2)).Distinct.ToList
            ComboBox3.DataSource = lst
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            connectionString.Close()
            connectionString.Dispose()
        End Try
    End Sub
    Private Sub findletters()
        Dim table As New DataTable()
        Try
            conns()
            Dim adapter As New MySqlDataAdapter("SELECT name,birthday,precinct_number,Gender,barangay,address,number FROM full_data_nametes1 where name LIKE '%" & ComboBox1.Text & "%' AND barangay = '" & ComboBox3.Text & "' ORDER BY barangay ASC", conn1)
            ' command.CommandText = "INSERT INTO full_data_x (name, birthday, precinct_number, Gender, barangay, address, number) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7)"
            adapter.Fill(table)

            DataGridView1.DataSource = table
            With DataGridView1
                .Columns(0).HeaderCell.Value = "Name"
                .Columns(1).HeaderCell.Value = "Birthday"
                .Columns(2).HeaderCell.Value = "Precinct"
                .Columns(3).HeaderCell.Value = "Gender"
                .Columns(4).HeaderCell.Value = "Barangay"
                .Columns(5).HeaderCell.Value = "Purok"
                .Columns(6).HeaderCell.Value = "VN"
            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.Columns(4).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns(5).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns(6).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.DefaultCellStyle.Font = New Font("arial", 12)

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conn1.Close()
        conn1.Dispose()
        'counall()
        findletters2()
    End Sub
    Private Sub findletters2()
        Dim table As New DataTable()
        Try
            conns()
            Dim adapter As New MySqlDataAdapter("SELECT name,birthday,precinct_number,Gender,barangay,address,number FROM masterlist2_test1 where name LIKE '%" & ComboBox1.Text & "%' AND barangay = '" & ComboBox3.Text & "' ORDER BY barangay ASC", conn1)
            ' command.CommandText = "INSERT INTO full_data_x (name, birthday, precinct_number, Gender, barangay, address, number) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7)"
            adapter.Fill(table)

            DataGridView2.DataSource = table
            With DataGridView2
                .Columns(0).HeaderCell.Value = "Name"
                .Columns(1).HeaderCell.Value = "Birthday"
                .Columns(2).HeaderCell.Value = "Precinct"
                .Columns(3).HeaderCell.Value = "Gender"
                .Columns(4).HeaderCell.Value = "Barangay"
                .Columns(5).HeaderCell.Value = "Purok"
                .Columns(6).HeaderCell.Value = "VN"
            End With
            DataGridView2.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(3).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(4).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(5).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(6).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.DefaultCellStyle.Font = New Font("arial", 12)

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conn1.Close()
        conn1.Dispose()
        'counall()
    End Sub
    Private Sub fillbyBarangay()
        'DataGridView1.DataSource = Nothing
        'DataGridView1.Refresh()
        Dim table As New DataTable()
        Try
            conns()
            Dim adapter As New MySqlDataAdapter("SELECT names,number FROM pco where  barangay = '" & ComboBox3.Text & "' ORDER BY purok ASC, names ASC", conn1)
            ' command.CommandText = "INSERT INTO full_data_x (name, birthday, precinct_number, Gender, barangay, address, number) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7)"
            adapter.Fill(table)

            DataGridView1.DataSource = table
            With DataGridView1
                .Columns(0).HeaderCell.Value = "Name"
                .Columns(1).HeaderCell.Value = "number"
                '.Columns(2).HeaderCell.Value = "Precinct"
                '.Columns(3).HeaderCell.Value = "Gender"
                '.Columns(4).HeaderCell.Value = "Barangay"
                '.Columns(5).HeaderCell.Value = "Purok"
                '.Columns(6).HeaderCell.Value = "VN"
            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.Columns(4).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView1.Columns(5).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView1.Columns(6).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.DefaultCellStyle.Font = New Font("arial", 12)

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conn1.Close()
        conn1.Dispose()
        fillbyBarangay2()
    End Sub
    Private Sub fillbyBarangay2()
        'DataGridView2.DataSource = Nothing
        'DataGridView2.Refresh()
        Dim table As New DataTable()
        Try
            conns()
            Dim adapter As New MySqlDataAdapter("SELECT name,birthday,precinct_number,Gender,barangay,address,number FROM full_data_nametes1 where  barangay = '" & ComboBox3.Text & "' ORDER BY address ASC, name ASC", conn1)
            ' command.CommandText = "INSERT INTO full_data_x (name, birthday, precinct_number, Gender, barangay, address, number) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7)"
            adapter.Fill(table)

            DataGridView2.DataSource = table
            With DataGridView2
                .Columns(0).HeaderCell.Value = "Name"
                .Columns(1).HeaderCell.Value = "Birthday"
                .Columns(2).HeaderCell.Value = "Precinct"
                .Columns(3).HeaderCell.Value = "Gender"
                .Columns(4).HeaderCell.Value = "Barangay"
                .Columns(5).HeaderCell.Value = "Purok"
                .Columns(6).HeaderCell.Value = "VN"
            End With
            DataGridView2.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(3).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(4).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(5).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns(6).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.DefaultCellStyle.Font = New Font("arial", 12)

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conn1.Close()
        conn1.Dispose()
        'counall()
    End Sub
    Private Sub counall()
        Dim dt As DataTable
        Dim dg As DataGrid
        'dt is the datatable
        DataGridView1.DataSource = dt

        'display number of rows in grid
        Label1.Text = dt.Rows.Count.ToString

    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        updater2()
    End Sub
    Private Sub updater2()
        For i As Integer = 0 To DataGridView2.Rows.Count - 1
            Call conns()
            Dim cmd4 As New MySqlCommand("server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706", conn1)
            cmd4.CommandText = "update pco SET number = '" & DataGridView2.Rows(i).Cells(6).Value & "' WHERE names = '" & DataGridView2.Rows(i).Cells(0).Value & "' AND barangay = '" & ComboBox3.Text & "'"
            'conn.Open()
            cmd4.ExecuteNonQuery()
            conclos()
        Next
        MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")

 
    End Sub
    Private Sub updater1()
        If ComboBox1.Text = "" Then
            MessageBox.Show("Please Select Purok", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            conns()

            For i As Integer = 0 To DataGridView2.Rows.Count - 1

                'cmd = New MySqlCommand("Update full_data_nametes1 set address = '" & Label1.Text & "' where name LIKE '%" & DataGridView1.Rows(i).Cells(0).Value & "%'", conn1)
                'cmd = New MySqlCommand("UPDATE full_data_nametes1 t1  INNER JOIN masterlist2_test1 t2  ON t1.number = t2.number SET t1.name = t2.name WHERE t2.number = '%" & DataGridView2.Rows(i).Cells(6).Value & "%'", conn1)
                cmd = New MySqlCommand("Update full_data_nametes1 SET  name = '" & DataGridView2.Rows(i).Cells(0).Value & "' WHERE name = '" & DataGridView2.Rows(i).Cells(0).Value & "' AND barangay = '" & DataGridView2.Rows(i).Cells(4).Value & "')", conn1)

            Next
            Call connsel.conclos()
            MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
            'findletters()
        End If
    End Sub
    Private Sub updatecardno()
        If ComboBox3.Text = "" Then
            MessageBox.Show("Please Select Purok", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            conns()

            For i As Integer = 0 To DataGridView1.Rows.Count - 1

                'cmd = New MySqlCommand("Update full_data_nametes1 set address = '" & Label1.Text & "' where name LIKE '%" & DataGridView1.Rows(i).Cells(0).Value & "%'", conn1)
                'cmd = New MySqlCommand("UPDATE full_data_nametes1 t1  INNER JOIN masterlist2_test1 t2  ON t1.number = t2.number SET t1.name = t2.name WHERE t2.number = '%" & DataGridView2.Rows(i).Cells(6).Value & "%'", conn1)
                cmd = New MySqlCommand("Update full_data_nametes1 SET  name = '" & DataGridView1.Rows(i).Cells(0).Value & "' WHERE name = '" & DataGridView1.Rows(i).Cells(0).Value & "' AND barangay = '" & DataGridView1.Rows(i).Cells(4).Value & "')", conn1)

            Next
            Call connsel.conclos()
            MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
            'findletters()
        End If
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        DataGridView1.DataSource = table
        Try
            OpenFileDialog1.Title = "Please select a  file"
            OpenFileDialog1.InitialDirectory = "C:\Users\Administrator\Documents\2025 caraga\caraga-list of voters\2025\names export"
            OpenFileDialog1.Filter = "txt|*.txt"

            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
                'Do things here, the path is stored in openFileDialog1.Filename
                'If no files were selected, openFileDialog1.Filename is ""  
                TextBox3.Text = OpenFileDialog1.FileName
            End If

            'Dim contents As String = System.IO.File.ReadAllText("C:\MyFile.txt", System.Text.Encoding.UTF8)

            Dim lines() As String
            Dim vals() As String

            ' get lines from the text file
            'lines = System.IO.File.ReadAllLines("" & TextBox3.Text & "", System.Text.Encoding.UTF8)
            lines = System.IO.File.ReadAllLines("" & TextBox3.Text & "", System.Text.Encoding.UTF8)

            For i As Integer = 0 To lines.Length - 1 Step +1  ' lines

                vals = lines(i).ToString().Split("|")
                Dim row(vals.Length - 1) As String

                For j As Integer = 0 To vals.Length - 1 Step +1  ' columns

                    row(j) = vals(j).Trim()

                Next j

                table.Rows.Add(row)

            Next i

        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox3.SelectedIndexChanged
        fillbyBarangay()
    End Sub

End Class