Imports System.IO
Imports MySql.Data.MySqlClient
Public Class List_of_X
    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 New MySqlDataAdapter
    Dim pagingDS As New DataSet
    Dim pagingDS2 As New DataSet
    Private sRecord_Count As Integer
    Dim conn As New MySqlConnection
    Dim cmd As New MySqlCommand
    Dim dr As MySqlDataReader
    Dim table As New DataTable()
    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 Try Again", "connection",
  MessageBoxButtons.OK, MessageBoxIcon.Error)
            conn.Close()
            conn.Open()
            'End
        End Try
    End Sub
    Private Sub List_of_X_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        fillistnewlist()
        countperX()
    End Sub
    Private Sub fillistnewlist()
        Dim table As New DataTable()
        Try
            conns()
            Dim adapter As New MySqlDataAdapter("SELECT name,birthday,precinct_number,Gender,barangay,address,number FROM full_data_x  where d_ LIKE 'D' AND name LIKE '%" & TextBox1.Text & "%' ORDER BY barangay, address ASC", conn)
            ' 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
            conn.Close()
            conn.Dispose()
        End Try
    End Sub
    Private Sub updateX()
        Call conns()
        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            cmd = New MySqlCommand("Update full_data set d_ ='D' where name LIKE '%" & DataGridView1.Rows(i).Cells(0).Value & "%'", conn)
            cmd.ExecuteNonQuery()
        Next
        MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")

    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'updateX()
        DeceasedUpdater.Show()
    End Sub
    Sub countperX()
        Dim table As New DataTable()
        conns()
        Dim adapter As New MySqlDataAdapter("SELECT name FROM full_data_x  where d_ LIKE 'D' ", conn)
        adapter.Fill(table)
        pagingDS2 = New DataSet()
        adapter.Fill(pagingDS2, "full_data_table")
        sRecord_Count = pagingDS2.Tables(0).Rows.Count
        'Dim table As New DataTable()
        'adapter.Fill(table)
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(0)).Distinct.Count
        Label2.Text = lst
    End Sub
    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        If TextBox1.Text = "" Then
            fillistnewlist()
        Else
            Dim table As New DataTable()
            Try
                conns()
                Dim adapter As New MySqlDataAdapter("SELECT name,birthday,precinct_number,Gender,barangay,address,number FROM full_data_x  where d_ LIKE 'D' AND name LIKE '%" & TextBox1.Text & "%' ORDER BY barangay, address ASC", conn)
                ' 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
                conn.Close()
                conn.Dispose()
            End Try
        End If
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        updatexs()
    End Sub
    Private Sub updatexs()
        Try
            Call conns()
            cmd = New MySqlCommand("INSERT INTO full_data_x (name, birthday, precinct_number, Gender, barangay, address, number, d_) Select name, birthday, precinct_number, Gender, barangay, address, number, d_ FROM full_data WHERE d_ ='D'", conn)

            cmd.ExecuteNonQuery()

            MessageBox.Show("Saved successfully")

            Call conns()
            cmd = New MySqlCommand("Delete from full_data WHERE d_ ='D'", conn)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally
            conn.Close()
            conn.Dispose()
        End Try
        countperX()
        fillistnewlist()
    End Sub
    Private Sub unmarkdecease()
        Try
            Call conns()
            cmd = New MySqlCommand("INSERT INTO full_data (name, birthday, precinct_number, Gender, barangay, address, number) Select name, birthday, precinct_number, Gender, barangay, address, number FROM full_data_x WHERE name = '" & TextBox3.Text & "' ", conn)
            cmd.ExecuteNonQuery()
            MessageBox.Show("Saved successfully")

            Call conns()
            cmd = New MySqlCommand("Delete from full_data_x WHERE name = '" & TextBox3.Text & "'", conn)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally
            conn.Close()
            conn.Dispose()
        End Try
        countperX()
        fillistnewlist()
    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
                    TextBox3.Text = .Rows(i).Cells("name").Value.ToString
                End If
            End With
        Catch exx As Exception

        End Try
    End Sub
    Private Sub savedata()
        Try
            Dim connection As New MySqlConnection
            Dim command As New MySqlCommand

            connection.ConnectionString = "server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706"
            command.CommandText = "INSERT INTO full_data_x (name, birthday, precinct_number, Gender, barangay, address, number, d_) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8)"

            command.Parameters.Add("@d1", MySqlDbType.VarChar)
            command.Parameters.Add("@d2", MySqlDbType.VarChar)
            command.Parameters.Add("@d3", MySqlDbType.VarChar)
            command.Parameters.Add("@d4", MySqlDbType.VarChar)
            command.Parameters.Add("@d5", MySqlDbType.VarChar)
            command.Parameters.Add("@d6", MySqlDbType.VarChar)
            command.Parameters.Add("@d7", MySqlDbType.VarChar)
            command.Parameters.Add("@d8", MySqlDbType.VarChar)
            connection.Open()
            command.Connection = connection

            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                'command.Parameters.AddWithValue("@name1", DataGridView1.Rows(i).Cells(0).Value)
                'command.Parameters.AddWithValue("@idno1", DataGridView1.Rows(i).Cells(1).Value)
                command.Parameters(0).Value = DataGridView1.Rows(i).Cells(0).Value
                command.Parameters(1).Value = DataGridView1.Rows(i).Cells(1).Value
                command.Parameters(2).Value = DataGridView1.Rows(i).Cells(2).Value
                command.Parameters(3).Value = DataGridView1.Rows(i).Cells(3).Value
                command.Parameters(4).Value = DataGridView1.Rows(i).Cells(4).Value
                command.Parameters(5).Value = DataGridView1.Rows(i).Cells(5).Value
                command.Parameters(6).Value = DataGridView1.Rows(i).Cells(6).Value
                command.Parameters(7).Value = "D"
                command.ExecuteNonQuery()
            Next

            MessageBox.Show("Saved successfully")
        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally
            conn.Close()
            conn.Dispose()
        End Try
    End Sub
    Private Sub savedata2()
        Try
            Dim connection As New MySqlConnection
            Dim command As New MySqlCommand

            connection.ConnectionString = "server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706"
            command.CommandText = "INSERT INTO full_data_x (name, birthday, precinct_number, Gender, barangay, address, number, d_) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8)"

            command.Parameters.Add("@d1", MySqlDbType.VarChar)
            command.Parameters.Add("@d2", MySqlDbType.VarChar)
            command.Parameters.Add("@d3", MySqlDbType.VarChar)
            command.Parameters.Add("@d4", MySqlDbType.VarChar)
            command.Parameters.Add("@d5", MySqlDbType.VarChar)
            command.Parameters.Add("@d6", MySqlDbType.VarChar)
            command.Parameters.Add("@d7", MySqlDbType.VarChar)
            command.Parameters.Add("@d8", MySqlDbType.VarChar)
            connection.Open()
            command.Connection = connection

            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                'command.Parameters.AddWithValue("@name1", DataGridView1.Rows(i).Cells(0).Value)
                'command.Parameters.AddWithValue("@idno1", DataGridView1.Rows(i).Cells(1).Value)
                command.Parameters(0).Value = DataGridView1.Rows(i).Cells(0).Value
                command.Parameters(1).Value = DataGridView1.Rows(i).Cells(1).Value
                command.Parameters(2).Value = DataGridView1.Rows(i).Cells(2).Value
                command.Parameters(3).Value = DataGridView1.Rows(i).Cells(3).Value
                command.Parameters(4).Value = DataGridView1.Rows(i).Cells(4).Value
                command.Parameters(5).Value = DataGridView1.Rows(i).Cells(5).Value
                command.Parameters(6).Value = DataGridView1.Rows(i).Cells(6).Value
                command.Parameters(7).Value = "D"
                command.ExecuteNonQuery()
            Next

            MessageBox.Show("Saved successfully")
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
            conn.Dispose()
        End Try
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Try
            OpenFileDialog1.Title = "Please select a  file"
            OpenFileDialog1.InitialDirectory = "C:\"
            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 lines() As String
            Dim vals() As String

            ' get lines from the text file
            lines = File.ReadAllLines("" & TextBox3.Text & "")

            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 Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

    End Sub
    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        If ComboBox1.Text = "BCC" Then
            Try
                Dim table As New DataTable()
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
                    conns()
                    Dim adapter As New MySqlDataAdapter("SELECT names,barangay,purok FROM bcc where names LIKE  '%" & DataGridView1.Rows(i).Cells(0).Value & "%'  ORDER BY names ASC", conn)
                    adapter.Fill(table)
                Next
                DataGridView2.DataSource = table
                With DataGridView2
                    .Columns(0).HeaderCell.Value = "Name" 
                    .Columns(1).HeaderCell.Value = "Barangay"
                    .Columns(2).HeaderCell.Value = "Purok"
                End With
                DataGridView2.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                DataGridView2.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                DataGridView2.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                DataGridView2.DefaultCellStyle.Font = New Font("arial", 12)
                conn.Close()
                conn.Dispose()
                cmd.Dispose()
                MySqlConnection.ClearPool(conn)
            Catch ex As Exception
                'MsgBox(ex.Message)
            Finally

            End Try
        ElseIf ComboBox1.Text = "PCC" Then
            Try
                Dim table As New DataTable()
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
                    conns()
                    Dim adapter As New MySqlDataAdapter("SELECT names,barangay,purok,bcc FROM pco where names LIKE  '%" & DataGridView1.Rows(i).Cells(0).Value & "%'  ORDER BY names ASC", conn)
                    adapter.Fill(table)
                Next
                DataGridView2.DataSource = table
                With DataGridView2
                    .Columns(0).HeaderCell.Value = "Name"
                    .Columns(1).HeaderCell.Value = "Barangay"
                    .Columns(2).HeaderCell.Value = "Purok"
                    .Columns(3).HeaderCell.Value = "BCC"
                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.DefaultCellStyle.Font = New Font("arial", 12)
                conn.Close()
                conn.Dispose()
                cmd.Dispose()
                MySqlConnection.ClearPool(conn)
            Catch ex As Exception
                'MsgBox(ex.Message)
            Finally

            End Try
        ElseIf ComboBox1.Text = "HHL" Then
            Try
                Dim table As New DataTable()
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
                    conns()
                    Dim adapter As New MySqlDataAdapter("SELECT names,barangay,purok,pcc FROM hhl where names LIKE  '%" & DataGridView1.Rows(i).Cells(0).Value & "%'  ORDER BY names ASC", conn)
                    adapter.Fill(table)
                Next
                DataGridView2.DataSource = table
                With DataGridView2
                    .Columns(0).HeaderCell.Value = "Name"
                    .Columns(1).HeaderCell.Value = "Barangay"
                    .Columns(2).HeaderCell.Value = "Purok"
                    .Columns(3).HeaderCell.Value = "PCC"
                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.DefaultCellStyle.Font = New Font("arial", 12)
                conn.Close()
                conn.Dispose()
                cmd.Dispose()
                MySqlConnection.ClearPool(conn)
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally

            End Try
        ElseIf ComboBox1.Text = "MEMBER" Then
            Try
                Dim table As New DataTable()
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
                    conns()
                    Dim adapter As New MySqlDataAdapter("SELECT name,barangay,address,pco FROM full_data_entry where name LIKE  '%" & DataGridView1.Rows(i).Cells(0).Value & "%'  ORDER BY name ASC", conn)
                    adapter.Fill(table)
                Next
                DataGridView2.DataSource = table
                With DataGridView2
                    .Columns(0).HeaderCell.Value = "Name"
                    .Columns(1).HeaderCell.Value = "Barangay"
                    .Columns(2).HeaderCell.Value = "Purok"
                    .Columns(3).HeaderCell.Value = "HHL"
                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.DefaultCellStyle.Font = New Font("arial", 12)
                conn.Close()
                conn.Dispose()
                cmd.Dispose()
                MySqlConnection.ClearPool(conn)
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally

            End Try
        End If

       
    End Sub
  
    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        unmarkdecease()
    End Sub
End Class