Imports MySql.Data.MySqlClient
Public Class pco
    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")
    '        conn1.Open()
    '    Catch ex As Exception
    '        'If conn.State = ConnectionState.Open Then
    '        '    conn1.Close()
    '        'ElseIf connectionString.State = ConnectionState.Open Then
    '        '    conn1.Close()
    '        'Else
    '        MsgBox(ex.Message)
    '        'MessageBox.Show("Please Close to finish update 5 seconds & Open the program again. Or contact the programmer", "Update",  MessageBoxButtons.OK, MessageBoxIcon.Error)
    '        ' End
    '        'End If
    '    End Try
    'End Sub
    'Private Sub conclos()
    '    Try
    '        connectionString.Close()
    '        connectionString.Dispose()
    '        conn1.Close()
    '        conn.Dispose()
    '        MySqlConnection.ClearPool(conn)
    '        MySqlConnection.ClearPool(connectionString)
    '    Catch ex As Exception

    '    End Try

    'End Sub
    Private Sub hhldata_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
        conclos()
    End Sub
    Private Sub pco_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        conns()
        selectbargy()
        'selectpurok()
        'namesugg()
        countpco()
        purokid.Visible = False
        names.Text = ""
        bcc.Text = ""
        purok.Text = ""
        'barangay.Text = ""
        Label1.Visible = False
        bccid.Visible = False
        'loadnames()
        'fillist()
        'fillist()
        conclos()
    End Sub
    Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles purok.SelectedIndexChanged
        'selectbargy1()
        'namesugg()
        'loadnames()
        fillist()
        namesugg()
    End Sub
    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles barangay.SelectedIndexChanged
        selectpurok()
        bcc.Text = ""
    End Sub
    Private Sub name_SelectedIndexChanged(sender As Object, e As EventArgs) Handles names.SelectedIndexChanged
        selectinfo()
        'namesuggbcc()
    End Sub
    Private Sub names_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles names.KeyDown
        'If e.KeyCode = Keys.Enter Then
        '    Button1.PerformClick()
        'Else
        'End If
    End Sub
    Private Sub bcc_SelectedIndexChanged(sender As Object, e As EventArgs) Handles bcc.SelectedIndexChanged
        selectinfobcc()
    End Sub
    Private Sub selectpurok1()
        Try
            Dim adapter As New MySqlDataAdapter("SELECT * FROM purok WHERE barangay LIKE '%" & barangay.Text & "%'  ORDER BY purok ASC", connectionString)
            Dim table As New DataTable()
            adapter.Fill(table)
            purok.DataSource = table
            purok.ValueMember = "purok"
            purok.DisplayMember = "purok"
            Dim lst = (From row In table.AsEnumerable
                       Select row.Field(Of String)(1)).Distinct.ToList
            purok.DataSource = lst
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub selectbargy()
        If Dashboard.Label17.Text = "jeleen" Then
            barangay.Items.Clear()
            barangay.DropDownStyle = ComboBoxStyle.DropDownList
            barangay.Items.Add("POBLACION")
            barangay.SelectedItem = "POBLACION"
        Else
            Try
                Dim adapter As New MySqlDataAdapter("SELECT * FROM purok ORDER BY barangay ASC", connectionString)
                Dim table As New DataTable()
                adapter.Fill(table)
                barangay.DataSource = table
                barangay.ValueMember = "barangay"
                barangay.DisplayMember = "barangay"
                Dim lst = (From row In table.AsEnumerable
                           Select row.Field(Of String)(2)).Distinct.ToList
                barangay.DataSource = lst
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally

            End Try
        End If
        conclos()
    End Sub
    Private Sub selectpurok()
        Try
            Dim adapter As New MySqlDataAdapter("SELECT * FROM purok WHERE barangay LIKE '%" & barangay.Text & "%'  ORDER BY purok ASC", connectionString)
            Dim table As New DataTable()
            adapter.Fill(table)
            purok.DataSource = table
            purok.ValueMember = "purok"
            purok.DisplayMember = "purok"
            Dim lst = (From row In table.AsEnumerable
                       Select row.Field(Of String)(1)).Distinct.ToList
            purok.DataSource = lst
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub selectinfo()
        Call conns()
        Dim sqlcmd As New MySqlCommand("SELECT * FROM full_data where name LIKE '%" & names.Text & "%'", conn1)
        Dim sdr As MySqlDataReader
        sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
        If sdr.HasRows Then
            Do While sdr.Read()
                'birth.Text = sdr("birthday")
                'gender.Text = sdr("Gender")
                precint.Text = sdr("precinct_number")
                ' purok.Text = sdr("address")
                ' barangay.Text = sdr("barangay")
                precint.Enabled = False
            Loop
        Else

        End If
        conclos()
    End Sub
    Private Sub selectinfobcc()
        Call conns()
        Dim sqlcmd As New MySqlCommand("SELECT * FROM full_data where name LIKE '%" & bcc.Text & "%'", conn1)
        Dim sdr As MySqlDataReader
        sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
        While sdr.Read()
            'birth.Text = sdr("birthday")
            'gender.Text = sdr("Gender")
            Label10.Text = sdr("precinct_number")
        End While
        conclos()
    End Sub
    Private Sub namesugg()
        Try
            Dim adapter As New MySqlDataAdapter("SELECT * FROM full_data where barangay = '" & barangay.Text & "' ORDER BY name ASC", connectionString)
            Dim table As New DataTable()
            adapter.Fill(table)
            With names
                .DataSource = table
                .DisplayMember = "name"
                .ValueMember = "name"
                .DropDownStyle = ComboBoxStyle.DropDown
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.ListItems
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub loadnames()
        Try
            conn1.Open()
            Dim dtt As New DataTable()
            With cmd
                .Connection = conn1
                .CommandText = "Select * FROM full_data where name LIKE  '%" & names.Text & "%' ORDER BY name ASC"
            End With
            Dim daa As New MySqlDataAdapter
            daa.SelectCommand = cmd
            daa.Fill(dtt)
            Dim r As DataRow
            names.AutoCompleteCustomSource.Clear()
            For Each r In dtt.Rows
                names.AutoCompleteCustomSource.Add(r.Item("name"))
            Next
            daa.Dispose()
            conn1.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        'daa.Dispose()
        conclos()
    End Sub
    Private Sub namesuggbcc()
        Try
            conn1.Open()
            Dim dtt As New DataTable()
            With cmd
                .Connection = conn1
                .CommandText = "SELECT * FROM bcc where barangay LIKE  '%" & barangay.Text & "%'  ORDER BY names ASC"
            End With
            Dim daa As New MySqlDataAdapter
            daa.SelectCommand = cmd
            daa.Fill(dtt)
            'Dim r As DataRow
            Dim sdr As MySqlDataReader
            sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If sdr.HasRows Then
                With bcc
                    .DataSource = dtt
                    .DisplayMember = "names"
                    .ValueMember = "names"
                    .DropDownStyle = ComboBoxStyle.DropDownList
                    .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                    .AutoCompleteSource = AutoCompleteSource.ListItems
                End With
                'daa.Dispose()
            Else
                With bcc
                    .DataSource = dtt
                    .DisplayMember = ""
                    .ValueMember = ""
                    .DropDownStyle = ComboBoxStyle.DropDownList
                    .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                    .AutoCompleteSource = AutoCompleteSource.ListItems
                End With
                bcc.Text = ""
                Label10.Text = ""
            End If

        Catch ex As Exception
            ' MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub clear()
        Dim scrollVal As Integer
        fillist()
        scrollVal = 0
        names.Text = ""
        purok.Text = ""
        barangay.Text = ""
        precint.Text = ""
        bcc.Text = ""
        Label10.Text = ""
        purokid.Text = ""
        Label1.Text = ""
        conclos()
    End Sub
    Private Sub fillist()
        Try
            conns()
            cmd = New MySqlCommand("SELECT * FROM pco where barangay LIKE  '%" & barangay.Text & "%' ORDER BY id DESC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS = New DataSet()
            pagingDS2 = New DataSet()
            conn1.Open()
            pagingAdapter.Fill(pagingDS2, "pco_table")
            pagingAdapter.Fill(pagingDS, scrollVal, 10, "pco_table")
            conn1.Close()
            DataGridView1.DataSource = pagingDS
            DataGridView1.DataMember = "pco_table"
            With DataGridView1
                .Columns(1).HeaderCell.Value = "Name"
                .Columns(2).HeaderCell.Value = "Purok"
                .Columns(3).HeaderCell.Value = "Barangay"
                ' .Columns(4).HeaderCell.Value = "BCC"
                .Columns(7).HeaderCell.Value = "Status"
            End With
            DataGridView1.Columns.Item("id").Visible = False
            DataGridView1.Columns.Item("names").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("purok").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("barangay").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.Columns.Item("bcc").Visible = False
            DataGridView1.Columns.Item("boo").Visible = False
            DataGridView1.Columns.Item("contact").Visible = False
            DataGridView1.Columns.Item("status").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView1.DefaultCellStyle.Font = New Font("arial", 12)
            'DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            'DataGridView1.Dock = DockStyle.Fill
            countpco()
            Dashboard.counters()

        Catch ex As Exception
            ' MsgBox(ex.Message)
        Finally

        End Try
        conclos()

    End Sub
    Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Try
            bcc.Enabled = True
            namesuggbcc()
            Dim i As Integer
            With DataGridView1
                If e.RowIndex >= 0 Then
                    i = .CurrentRow.Index
                    purokid.Text = .Rows(i).Cells(0).Value.ToString
                    names.Text = .Rows(i).Cells(1).Value.ToString
                    Label1.Text = .Rows(i).Cells(1).Value.ToString
                    purok.Text = .Rows(i).Cells(2).Value.ToString
                    barangay.Text = .Rows(i).Cells(3).Value.ToString
                    bcc.Text = .Rows(i).Cells(4).Value.ToString
                    contact.Text = .Rows(i).Cells(6).Value.ToString
                    votemj.Text = .Rows(i).Cells(7).Value.ToString
                    precint.Text = .Rows(i).Cells(8).Value.ToString
                End If
            End With
            ' name.Text = Decryptel(name.Text, 1)
        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        bccfromtable()
    End Sub
    Private Sub bccfromtable()
        Try
            conns()
            cmd = New MySqlCommand("SELECT * FROM bccofpcc WHERE pccidno='" & purokid.Text & "'  ORDER BY bccname ASC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS = New DataSet()
            pagingDS2 = New DataSet()
            conn1.Open()
            'pagingAdapter.Fill(pagingDS2, "pco_table")
            'pagingAdapter.Fill(pagingDS, scrollVal, 10, "pco_table")
            pagingAdapter.Fill(pagingDS, "bccofpcc_table")
            conn1.Close()
            DataGridView2.DataSource = pagingDS
            DataGridView2.DataMember = "bccofpcc_table"
            With DataGridView1
                .Columns(3).HeaderCell.Value = "BCC"
                .Columns(4).HeaderCell.Value = "Barangay"
                .Columns(5).HeaderCell.Value = "Purok"
                '.Columns(1).HeaderCell.Value = "Name"
                '.Columns(2).HeaderCell.Value = "Purok"
                '.Columns(3).HeaderCell.Value = "Barangay"
                '.Columns(4).HeaderCell.Value = "BCC"
                '.Columns(7).HeaderCell.Value = "Status"
            End With
            DataGridView2.Columns.Item("idno").Visible = False
            DataGridView2.Columns.Item("pccidno").Visible = False
            DataGridView2.Columns.Item("bccidno").Visible = False
            DataGridView2.Columns.Item("bccname").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns.Item("bccbrgy").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.Columns.Item("bccpurok").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView2.Columns.Item("boo").Visible = False
            'DataGridView2.Columns.Item("contact").Visible = False
            DataGridView2.Columns.Item("status").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView2.DefaultCellStyle.Font = New Font("arial", 12)
            'DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            'DataGridView1.Dock = DockStyle.Fill


        Catch ex As Exception
            ' MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            bcc.DataSource = Nothing
            bcc.Refresh()
            bcc.Text = ""
            'bcc.Enabled = False
            bccid.Text = ""
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Then
                MessageBox.Show("Please Check Input", "Warning",
    MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                conns()
                cmd = New MySqlCommand("SELECT * FROM hhl where names LIKE  '%" & names.Text & "%'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then
                    MessageBox.Show("NAME ALREADY IN HHL", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Else
                    conns()
                    cmd = New MySqlCommand("SELECT * FROM pco where names LIKE  '%" & names.Text & "%'", conn1)
                    dr = cmd.ExecuteReader
                    If (dr.Read) Then
                        MessageBox.Show("NAME ALREADY IN PCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        conns()
                        cmd = New MySqlCommand("SELECT * FROM full_data_entry where name LIKE  '%" & names.Text & "%'", conn1)
                        dr = cmd.ExecuteReader
                        If (dr.Read) Then
                            MessageBox.Show("NAME ALREADY IN Data Entry", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Else
                            conns()
                            cmd = New MySqlCommand("SELECT * FROM bcc where names LIKE  '%" & names.Text & "%'", conn1)
                            dr = cmd.ExecuteReader
                            If (dr.Read) Then
                                MessageBox.Show("NAME ALREADY IN BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                            Else
                                conns()
                                cmd = New MySqlCommand("SELECT * FROM full_data where name LIKE  '%" & names.Text & "%'", conn1)
                                dr = cmd.ExecuteReader
                                If (dr.Read) Then
                                    'savebcc()
                                    savepcc()
                                Else
                                    MessageBox.Show("NAME Not in Master List", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                                End If
                            End If
                        End If
                    End If
                End If
            End If
            clear()
            If Dashboard.Label17.Text = "lhoy" Or Dashboard.Label17.Text = "EL" Then
                sqlbackuperclik()
            Else

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub savepcc()
        Try
            conns()
            cmd = New MySqlCommand("SELECT * FROM bcc where names LIKE  '%" & bcc.Text & "%'", conn1)
            dr = cmd.ExecuteReader
            If (dr.Read) Then
                Call conns()
                cmd = New MySqlCommand("Insert into pco (names,purok,barangay,bcc,contact,precint,status,cardno) Values" _
            & " ('" & names.Text & "', '" & purok.Text & "','" & barangay.Text _
            & "', '" & bcc.Text & "', '" & contact.Text & "', '" & precint.Text & "', '" & votemj.Text & "', '" & cardno.Text & "')", conn1)
                cmd.ExecuteNonQuery()
                conn1.Close()

                Call conns()
                cmd = New MySqlCommand("Insert into bccofpcc (pccidno,bccname) Values" _
            & " ('" & purokid.Text & "', '" & bcc.Text & "')", conn1)
                cmd.ExecuteNonQuery()
                conn1.Close()

                MsgBox("Successfully Saved", MsgBoxStyle.Information, "Save")
            Else
                MessageBox.Show("NAME IS NOT BCC, PLEASE SELECT BCC NAME", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        'savebccofpcc()
    End Sub
    Private Sub savebcc()
        Try
            conns()
            cmd = New MySqlCommand("SELECT * FROM pco where names LIKE  '%" & bcc.Text & "%'", conn1)
            dr = cmd.ExecuteReader
            If (dr.Read) Then
                MessageBox.Show("NAME ALREADY PCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                conns()
                cmd = New MySqlCommand("SELECT * FROM bcc where names LIKE  '%" & bcc.Text & "%'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then

                Else
                    conns()
                    cmd = New MySqlCommand("SELECT * FROM full_data where names LIKE  '%" & bcc.Text & "%'", conn1)
                    dr = cmd.ExecuteReader
                    If (dr.Read) Then
                        Call conns()
                        cmd = New MySqlCommand("Insert into bcc (names,purok,barangay) Values" _
                    & " ('" & bcc.Text & "', '" & purok.Text & "','" & barangay.Text _
                    & "')", conn1)
                        cmd.ExecuteNonQuery()
                    Else
                        MessageBox.Show("BCC Not in Master List", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End If
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub DataGridView1_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        'If (e.ColumnIndex = 1 AndAlso e.Value IsNot Nothing) Then
        '    'e.Value = New String("*", e.Value.ToString().Length)
        '    e.Value = Decryptel(e.Value, 1)
        'End If
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Try
            bcc.DataSource = Nothing
            bcc.Refresh()
            bcc.Text = ""
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Or purokid.Text = "" Then
                MessageBox.Show("Please Check Input", "Warning",
    MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                conns()
                cmd = New MySqlCommand("SELECT * FROM hhl where names LIKE  '%" & names.Text & "%'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then
                    MessageBox.Show("NAME ALREADY IN HHL", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Else
                    'conns()
                    'cmd = New MySqlCommand("SELECT * FROM pco where names LIKE  '%" & names.Text & "%'", conn1)
                    'dr = cmd.ExecuteReader
                    'If (dr.Read) Then
                    '    MessageBox.Show("NAME ALREADY IN PCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    'Else
                    conns()
                    cmd = New MySqlCommand("SELECT * FROM full_data_entry where name LIKE  '%" & names.Text & "%'", conn1)
                    dr = cmd.ExecuteReader
                    If (dr.Read) Then
                        MessageBox.Show("NAME ALREADY IN Data Entry", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        conns()
                        cmd = New MySqlCommand("SELECT * FROM bcc where names LIKE  '%" & names.Text & "%'", conn1)
                        dr = cmd.ExecuteReader
                        If (dr.Read) Then
                            MessageBox.Show("NAME ALREADY IN BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Else
                            conns()
                            cmd = New MySqlCommand("SELECT * FROM full_data where name LIKE  '%" & names.Text & "%'", conn1)
                            dr = cmd.ExecuteReader
                            If (dr.Read) Then
                                updatehhl2()
                                Call conns()
                                cmd = New MySqlCommand("Update pco set names = '" & names.Text & "', purok ='" & purok.Text & "', barangay ='" & barangay.Text _
                                                       & "', bcc ='" & bcc.Text & "', contact ='" & contact.Text & "', precint ='" & precint.Text & "', status ='" & votemj.Text & "', cardno ='" & cardno.Text & "' where id = '" & purokid.Text & "'", conn1)
                                cmd.ExecuteNonQuery()
                                MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
                                clear()
                            Else
                                MessageBox.Show("BCC Not in Master List", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                            End If
                        End If
                    End If
                End If
            End If
            'End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Try
            bcc.DataSource = Nothing
            bcc.Refresh()
            bcc.Text = ""
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Or purokid.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
                    updatehhl()
                    Call conns()
                    cmd = New MySqlCommand("Delete from pco where id = '" & purokid.Text & "'", conn1)
                    cmd.ExecuteNonQuery()
                    delbccbypccid()
                    MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
                    clear()
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub updatehhl()
        Try
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Or purokid.Text = "" Then
                MessageBox.Show("Please Check Input", "Warning",
    MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                conns()
                cmd = New MySqlCommand("SELECT * FROM hhl where pcc LIKE  '%" & names.Text & "%' and barangay LIKE '%" & barangay.Text & "%'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then
                    If MsgBox("HHL with assigned PCC will update to blank PCC", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then
                        ' MessageBox.Show("NAME ALREADY BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Call conns()
                        cmd = New MySqlCommand("Update hhl set pcc = '' where pcc LIKE  '" & names.Text & "' and barangay LIKE '%" & barangay.Text & "%'", conn1)
                        cmd.ExecuteNonQuery()
                        'MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
                        'clear()
                    End If

                Else

                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub updatehhl2()
        Try
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Or purokid.Text = "" Then
                MessageBox.Show("Please Check Input", "Warning",
    MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                conns()
                cmd = New MySqlCommand("SELECT * FROM hhl where pcc LIKE  '%" & names.Text & "%' and barangay LIKE '%" & barangay.Text & "%'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then


                Else
                    If MsgBox("HHL with assigned PCC will update to '" & names.Text & "' ", CType(MsgBoxStyle.OkOnly + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Ok Then
                        ' MessageBox.Show("NAME ALREADY BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Call conns()
                        cmd = New MySqlCommand("Update hhl set pcc = '" & names.Text & "' where pcc LIKE  '" & Label1.Text & "' and barangay LIKE '%" & barangay.Text & "%'", conn1)
                        cmd.ExecuteNonQuery()
                        'MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
                        'clear()
                    End If
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        'clear()
        Me.Controls.Clear() 'removes all the controls on the form
        InitializeComponent() 'load all the controls again
        pco_Load(e, e)
        'MsgBox("Thank you for sending report", vbInformation, "")
        Refresh()
    End Sub
    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        Try
            'If conn.State = ConnectionState.Open Then
            '    conn1.Close()
            'ElseIf connectionString.State = ConnectionState.Open Then
            '    connectionString.Close()
            'Else
            scrollVal = scrollVal - 10
            If scrollVal <= 0 Then
                scrollVal = 0
            End If
            pagingDS.Clear()
            pagingAdapter.Fill(pagingDS, scrollVal, 10, "pco_table")
            'End If
        Catch ex As Exception

        End Try
        conclos()
    End Sub
    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        Try
            scrollVal = scrollVal + 10

            If scrollVal >= 0 Then

            End If
            pagingDS.Clear()
            pagingAdapter.Fill(pagingDS, scrollVal, 10, "pco_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 countpco()
        Try
            ' Dim scrollVal As Integer
            Dim pagingAdapter As New MySqlDataAdapter
            Dim pagingDS As New DataSet
            Dim pagingDS2 As New DataSet
            Dim sRecord_Count As Integer
            Call conns()
            cmd = New MySqlCommand("SELECT * FROM pco ORDER BY id ASC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS2 = New DataSet()
            conn1.Open()
            pagingAdapter.Fill(pagingDS2, "pco_table")
            conn1.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
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub names_SelectedIndexChanged(sender As Object, e As KeyPressEventArgs) Handles names.KeyPress
        e.KeyChar = UCase(e.KeyChar)
    End Sub
    Private Sub bcc_SelectedIndexChanged_1(sender As Object, e As KeyPressEventArgs) Handles bcc.KeyPress
        e.KeyChar = UCase(e.KeyChar)
    End Sub
    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
        savebccofpcc()
    End Sub
    Private Sub savebccofpcc()
        Try
            Call conns()
            cmd = New MySqlCommand("Insert into bccofpcc (pccidno,bccname,bccbrgy,bccpurok) Values" _
        & " ('" & purokid.Text & "', '" & bcc.Text & "', '" & barangay.Text & "', '" & purok.Text & "')", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            MsgBox("Successfully updated", MsgBoxStyle.Information, "Save")
            clear()
            If Dashboard.Label17.Text = "lhoy" Or Dashboard.Label17.Text = "EL" Then
                sqlbackuperclik()
            Else

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        conclos()
        bccfromtable()
    End Sub

    Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
        delbcc()
    End Sub
    Private Sub delbcc()
        Try
            bcc.DataSource = Nothing
            bcc.Refresh()
            bcc.Text = ""
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Or purokid.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
                    'updatehhl()
                    Call conns()
                    cmd = New MySqlCommand("Delete from bccofpcc where idno = '" & bccid.Text & "'", conn1)
                    cmd.ExecuteNonQuery()
                    MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
                    'clear()
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        bccfromtable()
    End Sub
    Private Sub delbccbypccid()
        Try
            bcc.DataSource = Nothing
            bcc.Refresh()
            bcc.Text = ""
            If names.Text = "" Or purok.Text = "" Or barangay.Text = "" Or purokid.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
                    'updatehhl()
                    Call conns()
                    cmd = New MySqlCommand("Delete from bccofpcc where pccidno = '" & purokid.Text & "'", conn1)
                    cmd.ExecuteNonQuery()
                    MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
                    'clear()
                End If
            End If
        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        'bccfromtable()
    End Sub
    Private Sub DataGridView2_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView2.CellClick
        Try
            bcc.Enabled = True
            Dim i As Integer
            With DataGridView2
                If e.RowIndex >= 0 Then
                    i = .CurrentRow.Index
                    bccid.Text = .Rows(i).Cells(0).Value.ToString
                    purokid.Text = .Rows(i).Cells(1).Value.ToString

                End If
            End With

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
    End Sub
End Class