Imports System.IO
Imports MySql.Data.MySqlClient
Imports System.Text
Public Class cardNoAssign
    Public sCon As MySqlConnection = New MySqlConnection
    Public sComand As MySqlDataAdapter = New MySqlDataAdapter
    Public ds As DataSet = New DataSet
    Dim table As New DataTable()
    'Dim conn As MySqlConnection
    Dim cmd As MySqlCommand
    Dim dr As MySqlDataReader
    Dim scrollVal As Integer
    Dim pagingAdapter As MySqlDataAdapter
    Dim pagingDS As DataSet
    Dim pagingDS2 As DataSet
    Dim connectionString As New MySqlConnection("server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706")


    Private Sub cardNoAssign_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        loadbrgy()
    End Sub
    Private Sub loadbrgy()
        Try
            Dim adapter As New MySqlDataAdapter("SELECT * FROM purok ORDER BY barangay ASC", connectionString)
            Dim table As New DataTable()
            adapter.Fill(table)
            brgy.DataSource = table
            brgy.ValueMember = "barangay"
            brgy.DisplayMember = "barangay"
            Dim lst = (From row In table.AsEnumerable
                       Select row.Field(Of String)(2)).Distinct.ToList
            brgy.DataSource = lst
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        loadpurok()
    End Sub
    Private Sub loadpurok()
        Try
            Dim adapter As New MySqlDataAdapter("SELECT * FROM purok WHERE barangay = '" & brgy.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 cardlastno()
        Call conns()
        Dim sqlcmd As New MySqlCommand("SELECT * FROM idcardrecor ORDER BY id DESC LIMIT 1 ", conn1)
        Dim sdr As MySqlDataReader
        sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
        While sdr.Read() 
            Label1.Text = sdr("cardno")
        End While
        conclos()

    End Sub
    Private Sub btnGen_Click(sender As Object, e As EventArgs) Handles btnGen.Click
        'savenabcc()
        'savenaPcc()
        'savenaHHL()
        'updateallcard()
        'loaddataEntrybyHHL() 
        updatePrinted()
    End Sub
    Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
        savememberCard()
    End Sub
    Private Sub savememberCard() 
           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 idcardrecor (cardno, memidno) VALUES (@d1,@d2)"

                command.Parameters.Add("@d1", MySqlDbType.VarChar)
                command.Parameters.Add("@d2", MySqlDbType.VarChar)
                connection.Open()
                command.Connection = connection

                For i As Integer = 0 To DataGridView4.Rows.Count - 1
                    conns()
                    cmd = New MySqlCommand("SELECT * FROM idcardrecor where memidno =  '" & DataGridView4.Rows(i).Cells(0).Value & "'", conn1)
                    dr = cmd.ExecuteReader
                    If (dr.Read) Then
                        'MessageBox.Show(" ALREADY IN BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        cardlastno()
                        Label1.Text = Label1.Text + 1
                        command.Parameters("@d1").Value = Label1.Text
                        command.Parameters("@d2").Value = DataGridView4.Rows(i).Cells(0).Value
                        command.ExecuteNonQuery()
                End If
                conclos()
                Next

                MessageBox.Show("Rows saved successfully")
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally

            End Try
            conn1.Close()
            conn1.Dispose()
        Label1.Text = 0
        conclos()
    End Sub
    Private Sub loaddataEntrybyHHL() 
        Dim table As New DataTable()
        Try
            For i As Integer = 0 To DataGridView3.Rows.Count - 1
                conns() 
                Dim adapter As New MySqlDataAdapter("SELECT id,name FROM full_data_entry where pco =  '" & DataGridView3.Rows(i).Cells(1).Value & "'  ORDER BY pco ASC", conn1)
                adapter.Fill(table)
                conclos()
            Next
            DataGridView4.DataSource = table
            With DataGridView4
                .Columns(0).HeaderCell.Value = "id"
                .Columns(1).HeaderCell.Value = "name"
                '.Columns(2).HeaderCell.Value = "Barangay"
                '.Columns(3).HeaderCell.Value = "Purok"
            End With
            DataGridView4.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView4.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView2.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView2.Columns(3).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView4.DefaultCellStyle.Font = New Font("arial", 12)
            'DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn1.Close()
            conn1.Dispose()
        End Try
        conclos()
        rowscountd.Text = DataGridView4.Rows.Count - 1
        ' Dashboard.counters()
    End Sub
    Private Sub updatePrinted()
        If ComboBox1.Text = "BCC" Then
            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                conns()
                cmd = New MySqlCommand("SELECT bccidno FROM idcardrecor where bccidno =  '" & DataGridView1.Rows(i).Cells(0).Value & "'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then
                    For r As Integer = 0 To DataGridView1.Rows.Count - 2
                        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 `idcardrecor` SET `printed`='Y' where bccidno = '" & DataGridView1.Rows(r).Cells(0).Value & "'"
                        cmd4.ExecuteNonQuery()
                        conclos()
                    Next
                Else
                End If
                conclos()
            Next
            MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
        ElseIf ComboBox1.Text = "PCC" Then
            For r As Integer = 0 To DataGridView2.Rows.Count - 2
                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 idcardrecor SET printed = 'Y' where pccidno = '" & DataGridView2.Rows(r).Cells(0).Value & "'"
                cmd4.ExecuteNonQuery()
                conclos()
            Next
            MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
        ElseIf ComboBox1.Text = "HHL" Then
            For r As Integer = 0 To DataGridView3.Rows.Count - 2
                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 idcardrecor SET printed = 'Y' where hhlidno = '" & DataGridView3.Rows(r).Cells(0).Value & "'"
                cmd4.ExecuteNonQuery()
                conclos()
            Next
            MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
        ElseIf ComboBox1.Text = "MEM" Then
            For r As Integer = 0 To DataGridView4.Rows.Count - 2
                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 idcardrecor SET printed = 'Y' where memidno = '" & DataGridView4.Rows(r).Cells(0).Value & "'"
                cmd4.ExecuteNonQuery()
                conclos()
            Next
            MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
        End If


    End Sub
    Private Sub updateallcard()
        Try
            For i As Integer = 1 To 4271
                'cardlastno()
                Label1.Text = Label1.Text + 1
                Label2.Text = Label2.Text + 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 idcardrecor SET printed = 'Y' WHERE id = '" & Label2.Text & "'"
                'conn.Open()
                cmd4.ExecuteNonQuery()
                conclos()
            Next
            MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conn1.Close()
        conn1.Dispose()
        'Label1.Text = 0
    End Sub
    Private Sub savenaHHL()
        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 idcardrecor (cardno, hhlidno) VALUES (@d1,@d2)"

            command.Parameters.Add("@d1", MySqlDbType.VarChar)
            command.Parameters.Add("@d2", MySqlDbType.VarChar)
            connection.Open()
            command.Connection = connection

            For i As Integer = 0 To DataGridView3.Rows.Count - 1
                conns()
                cmd = New MySqlCommand("SELECT * FROM idcardrecor where hhlidno =  '" & DataGridView3.Rows(i).Cells(0).Value & "'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then
                    'MessageBox.Show(" ALREADY IN BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Else
                    cardlastno()
                    Label1.Text = Label1.Text + 1
                    command.Parameters("@d1").Value = Label1.Text
                    command.Parameters("@d2").Value = DataGridView3.Rows(i).Cells(0).Value
                    command.ExecuteNonQuery()
                End If
            Next

            MessageBox.Show("Rows saved successfully")
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conn1.Close()
        conn1.Dispose()
        Label1.Text = 0
    End Sub
    Private Sub savenaPcc()
        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 idcardrecor (cardno, pccidno) VALUES (@d1,@d2)"

            command.Parameters.Add("@d1", MySqlDbType.VarChar)
            command.Parameters.Add("@d2", MySqlDbType.VarChar)
            connection.Open()
            command.Connection = connection

            For i As Integer = 0 To DataGridView2.Rows.Count - 1
                conns()
                cmd = New MySqlCommand("SELECT * FROM idcardrecor where pccidno =  '" & DataGridView2.Rows(i).Cells(0).Value & "'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then
                    'MessageBox.Show(" ALREADY IN BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Else
                    cardlastno()
                    Label1.Text = Label1.Text + 1
                    command.Parameters("@d1").Value = Label1.Text
                    command.Parameters("@d2").Value = DataGridView2.Rows(i).Cells(0).Value
                    command.ExecuteNonQuery()
                End If
            Next

            MessageBox.Show("Rows saved successfully")
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conn1.Close()
        conn1.Dispose()
        Label1.Text = 0
    End Sub
    Private Sub savenabcc()
        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 idcardrecor (cardno, bccidno) VALUES (@d1,@d2)"

            command.Parameters.Add("@d1", MySqlDbType.VarChar)
            command.Parameters.Add("@d2", MySqlDbType.VarChar)
            connection.Open()
            command.Connection = connection

            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                conns()
                cmd = New MySqlCommand("SELECT * FROM idcardrecor where bccidno =  '" & DataGridView1.Rows(i).Cells(0).Value & "'", conn1)
                dr = cmd.ExecuteReader
                If (dr.Read) Then
                    'MessageBox.Show(" ALREADY IN BCC", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Else
                    cardlastno()
                    Label1.Text = Label1.Text + 1
                    command.Parameters("@d1").Value = Label1.Text
                    command.Parameters("@d2").Value = DataGridView1.Rows(i).Cells(0).Value
                    command.ExecuteNonQuery()
                End If
            Next

            MessageBox.Show("Rows saved successfully")
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conn1.Close()
        conn1.Dispose()
        Label1.Text = 0
    End Sub
    Private Sub loaddata()
        Try
            conns()
            cmd = New MySqlCommand("SELECT pco.names, hhl.names AS Expr1, full_data_entry.name, full_data_entry.pco, full_data_entry.address, bccofpcc.bccbrgy, bccofpcc.bccpurok, full_data_entry.precinct_number, full_data_entry.statusv, pco.precint, hhl.precint AS Expr2, bcc.names AS Expr3, bcc.precint AS Expr4, pco.purok, pco.barangay, hhl.purok AS Expr5, hhl.barangay AS Expr6, full_data_entry.barangay AS Expr7 FROM hhl INNER JOIN pco ON hhl.pcc = pco.names INNER JOIN bccofpcc ON pco.id = bccofpcc.pccidno INNER JOIN bcc ON bccofpcc.bccname = bcc.names RIGHT OUTER JOIN full_data_entry ON hhl.names = full_data_entry.pco", 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, "caragavotersdb")
            conn1.Close()
            DataGridView1.DataSource = pagingDS
            DataGridView1.DataMember = "caragavotersdb"
            With DataGridView1
                '.Columns(0).HeaderCell.Value = "ID"
                '.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 = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView1.Columns.Item("names").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView1.Columns.Item("Expr1").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView1.Columns.Item("name").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView1.Columns.Item("pco").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            '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


        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnload.Click
        'loaddata()
    End Sub
    Private Sub loaddataBcc()
        Try
            conns()
            cmd = New MySqlCommand("SELECT `id`,`names` FROM `bcc` WHERE `barangay` ='" & brgy.Text & "' order by names ASC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS = New DataSet()
            conn1.Open()
            pagingAdapter.Fill(pagingDS, "caragavotersdb")
            conn1.Close()
            DataGridView1.DataSource = pagingDS
            DataGridView1.DataMember = "caragavotersdb"
            With DataGridView1
            End With
            DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            DataGridView1.DefaultCellStyle.Font = New Font("arial narrow", 8)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        rowscountd.Text = DataGridView1.Rows.Count - 1
    End Sub
    Private Sub loaddataBccNotY()
        Try
            conns()
            cmd = New MySqlCommand("SELECT bcc.id, bcc.names FROM idcardrecor INNER JOIN bcc ON idcardrecor.bccidno = bcc.id WHERE  (bcc.barangay = '" & brgy.Text & "') AND (idcardrecor.printed = '') order by names ASC", conn1)
            'cmd = New MySqlCommand("SELECT `id`,`names` FROM `bcc` WHERE `barangay` ='" & brgy.Text & "' order by names ASC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS = New DataSet()
            conn1.Open()
            pagingAdapter.Fill(pagingDS, "caragavotersdb")
            conn1.Close()
            DataGridView1.DataSource = pagingDS
            DataGridView1.DataMember = "caragavotersdb"
            With DataGridView1
            End With
            DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            DataGridView1.DefaultCellStyle.Font = New Font("arial narrow", 8)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        rowscountd.Text = DataGridView1.Rows.Count - 1
    End Sub
    Private Sub pcclistbybcc()
        Try
            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                 
                    conns()
                    cmd = New MySqlCommand("SELECT `names` FROM `pco` WHERE `bcc` ='" & DataGridView1.Rows(i).Cells(0).Value & "'   order by names ASC", conn1)
                    cmd.ExecuteNonQuery()
                    conn1.Close()
                    pagingAdapter = New MySqlDataAdapter(cmd)
                    pagingDS = New DataSet()
                    conn1.Open()
                    pagingAdapter.Fill(pagingDS, "caragavotersdb")
                    conn1.Close()
                    DataGridView2.DataSource = pagingDS
                    DataGridView2.DataMember = "caragavotersdb"
                    With DataGridView2
                    End With
                    DataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                    DataGridView2.DefaultCellStyle.Font = New Font("arial narrow", 8)
               
                conclos()
            Next
            'MessageBox.Show("Rows successful ")
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn1.Close()
            conn1.Dispose()
        End Try
        rowscountd.Text = DataGridView2.Rows.Count - 1
    End Sub
    Private Sub loaddataPccNotY()
        Try
            conns()
            cmd = New MySqlCommand("SELECT pco.id, pco.names FROM idcardrecor INNER JOIN pco ON idcardrecor.pccidno = pco.id WHERE  (pco.barangay = '" & brgy.Text & "') AND (idcardrecor.printed = '') order by names ASC", conn1)
                'cmd = New MySqlCommand("SELECT `id`,`names` FROM `bcc` WHERE `barangay` ='" & brgy.Text & "' order by names ASC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS = New DataSet()
            conn1.Open()
            pagingAdapter.Fill(pagingDS, "caragavotersdb")
            conn1.Close()
            DataGridView2.DataSource = pagingDS
            DataGridView2.DataMember = "caragavotersdb"
            With DataGridView2
            End With
            DataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            DataGridView2.DefaultCellStyle.Font = New Font("arial narrow", 8)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        rowscountd.Text = DataGridView2.Rows.Count - 1
    End Sub
    Private Sub loaddataPcc()
        Try
            conns()
            cmd = New MySqlCommand("SELECT `id`, `names` FROM `pco` WHERE `barangay` ='" & brgy.Text & "' and `purok` ='" & purok.Text & "' order by names ASC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS = New DataSet()
            conn1.Open()
            pagingAdapter.Fill(pagingDS, "caragavotersdb")
            conn1.Close()
            DataGridView2.DataSource = pagingDS
            DataGridView2.DataMember = "caragavotersdb"
            With DataGridView2
            End With
            DataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            DataGridView2.DefaultCellStyle.Font = New Font("arial narrow", 8)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        'loaddataHHL()
        rowscountd.Text = DataGridView2.Rows.Count - 1
    End Sub
    Private Sub loaddataHHLNotY()
        Try
            conns() 
            cmd = New MySqlCommand("SELECT hhl.id, hhl.names FROM idcardrecor INNER JOIN hhl ON idcardrecor.hhlidno = hhl.id WHERE  (hhl.barangay = '" & brgy.Text & "') AND (idcardrecor.printed = '') order by names ASC", conn1)
                'cmd = New MySqlCommand("SELECT `id`,`names` FROM `bcc` WHERE `barangay` ='" & brgy.Text & "' order by names ASC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS = New DataSet()
            conn1.Open()
            pagingAdapter.Fill(pagingDS, "caragavotersdb")
            conn1.Close()
            DataGridView3.DataSource = pagingDS
            DataGridView3.DataMember = "caragavotersdb"
            With DataGridView3
            End With
            DataGridView3.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            DataGridView3.DefaultCellStyle.Font = New Font("arial narrow", 8)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        rowscountd.Text = DataGridView3.Rows.Count - 1
    End Sub
    Private Sub loaddataHHL()
        Try
            conns()
            cmd = New MySqlCommand("SELECT `id`, `names` FROM `hhl` WHERE `barangay` ='" & brgy.Text & "' and `purok` ='" & purok.Text & "' order by names ASC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS = New DataSet()
            conn1.Open()
            pagingAdapter.Fill(pagingDS, "caragavotersdb")
            conn1.Close()
            DataGridView3.DataSource = pagingDS
            DataGridView3.DataMember = "caragavotersdb"
            With DataGridView3
            End With
            DataGridView3.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            DataGridView3.DefaultCellStyle.Font = New Font("arial narrow", 8)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        loaddataEntrybyHHLNotY()
        'loaddataEntrybyHHL()
        rowscountd.Text = DataGridView3.Rows.Count - 1
    End Sub
    Private Sub loaddataEntrybyHHLNotY()
        Dim table As New DataTable()
        Try
            For i As Integer = 0 To DataGridView3.Rows.Count - 1
                conns()
                Dim adapter As New MySqlDataAdapter("SELECT full_data_entry.id, full_data_entry.name FROM idcardrecor INNER JOIN full_data_entry ON idcardrecor.memidno = full_data_entry.id WHERE  (full_data_entry.pco = '" & DataGridView3.Rows(i).Cells(1).Value & "') AND (idcardrecor.printed = '') order by pco ASC", conn1)
                'Dim adapter As New MySqlDataAdapter("SELECT id,name FROM full_data_entry where pco =  '" & DataGridView3.Rows(i).Cells(1).Value & "'  ORDER BY pco ASC", conn1)
                adapter.Fill(table)
                conclos()
            Next
            DataGridView4.DataSource = table
            With DataGridView4
                .Columns(0).HeaderCell.Value = "id"
                .Columns(1).HeaderCell.Value = "name"
                '.Columns(2).HeaderCell.Value = "Barangay"
                '.Columns(3).HeaderCell.Value = "Purok"
            End With
            DataGridView4.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView4.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView2.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            'DataGridView2.Columns(3).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            DataGridView4.DefaultCellStyle.Font = New Font("arial", 12)
            'DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn1.Close()
            conn1.Dispose()
        End Try
        conclos()
        rowscountd.Text = DataGridView4.Rows.Count - 1
        ' Dashboard.counters()
    End Sub
    Private Sub loaddataEntry()
        Try
            conns()
            cmd = New MySqlCommand("SELECT `id`, `name` FROM `full_data_entry` WHERE `pco` ='" & hhl1.Text & "' order by name ASC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter = New MySqlDataAdapter(cmd)
            pagingDS = New DataSet()
            conn1.Open()
            pagingAdapter.Fill(pagingDS, "caragavotersdb")
            conn1.Close()
            DataGridView4.DataSource = pagingDS
            DataGridView4.DataMember = "caragavotersdb"
            With DataGridView4
            End With
            DataGridView4.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            DataGridView4.DefaultCellStyle.Font = New Font("arial narrow", 8)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
        rowscountd.Text = DataGridView4.Rows.Count - 1
    End Sub
    Private Sub brgy_SelectedIndexChanged(sender As Object, e As EventArgs) Handles brgy.SelectedIndexChanged
        loadpurok()
        'loaddataBcc()
    End Sub
    Private Sub purok_Sel(sender As Object, e As EventArgs) Handles purok.KeyDown
        If Keys.NumPad0 Then
            updatePrinted()
        ElseIf Keys.ShiftKey + Keys.S Then
            savememberCard()
        End If
    End Sub
    Private Sub purok_SelectedIndexChanged(sender As Object, e As EventArgs) Handles purok.SelectedIndexChanged
        'loaddataPcc()
        'loaddataHHL()
        
        'If ComboBox1.Text = "BCC" Then
        '    Dim ta As New DataTable
        '    ta.Clear()
        '    DataGridView2.DataSource = ta
        '    DataGridView2.DataSource = Nothing
        '    DataGridView3.DataSource = ta
        '    DataGridView3.DataSource = Nothing
        '    DataGridView4.DataSource = ta
        '    DataGridView4.DataSource = Nothing
        '    If ComboBox2.Text = "Not Y" Then
        '        loaddataBccNotY()
        '    Else
        '        loaddataBcc()
        '    End If
        'ElseIf ComboBox1.Text = "PCC" Then
        '    Dim ta As New DataTable
        '    ta.Clear()
        '    DataGridView1.DataSource = ta
        '    DataGridView1.DataSource = Nothing
        '    DataGridView3.DataSource = ta
        '    DataGridView3.DataSource = Nothing
        '    DataGridView4.DataSource = ta
        '    DataGridView4.DataSource = Nothing
        '    If ComboBox2.Text = "Not Y" Then 
        '        loaddataPccNotY()
        '    Else
        '        loaddataPcc()
        '    End If
        'ElseIf ComboBox1.Text = "HHL" Then
        '    Dim ta As New DataTable
        '    ta.Clear()
        '    DataGridView2.DataSource = ta
        '    DataGridView2.DataSource = Nothing
        '    DataGridView1.DataSource = ta
        '    DataGridView1.DataSource = Nothing
        '    'DataGridView4.DataSource = ta
        '    'DataGridView4.DataSource = Nothing
        '    If ComboBox2.Text = "Not Y" Then
        '        loaddataHHLNotY()
        '    Else
        '        loaddataHHL()
        '    End If
        'End If
    End Sub
    Private Sub DataGridView3_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView3.CellClick
        Try
            Dim i As Integer
            With DataGridView3
                If e.RowIndex >= 0 Then
                    i = .CurrentRow.Index
                    'hhl1.Text = .Rows(i).Cells(1).Value.ToString
                End If
            End With
            'loaddataEntry()
            ' name.Text = Decryptel(name.Text, 1)
        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally

        End Try
    End Sub
    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedValueChanged
        'If ComboBox1.Text = "BCC" Then
        '    loaddataBcc()
        'ElseIf ComboBox1.Text = "PCC" Then
        '    loaddataPcc()
        'ElseIf ComboBox1.Text = "HHL" Then
        '    loaddataHHL()
        'End If
    End Sub
    Private Sub ComboBox2_key(sender As Object, e As EventArgs) Handles ComboBox2.KeyDown
        If Keys.NumPad0 Then
            updatePrinted()
        ElseIf Keys.ShiftKey And Keys.S Then
            savememberCard()
        End If
    End Sub
    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged
        If ComboBox1.Text = "BCC" And ComboBox2.Text = "Not Y" Then
            Dim ta As New DataTable
            ta.Clear()
            DataGridView2.DataSource = ta
            DataGridView2.DataSource = Nothing
            DataGridView3.DataSource = ta
            DataGridView3.DataSource = Nothing
            DataGridView4.DataSource = ta
            DataGridView4.DataSource = Nothing
            loaddataBccNotY()
        ElseIf ComboBox1.Text = "PCC" And ComboBox2.Text = "Not Y" Then
            Dim ta As New DataTable
            ta.Clear()
            DataGridView1.DataSource = ta
            DataGridView1.DataSource = Nothing
            DataGridView3.DataSource = ta
            DataGridView3.DataSource = Nothing
            DataGridView4.DataSource = ta
            DataGridView4.DataSource = Nothing
            loaddataPccNotY()
        ElseIf ComboBox1.Text = "HHL" And ComboBox2.Text = "Not Y" Then
            Dim ta As New DataTable
            ta.Clear()
            DataGridView1.DataSource = ta
            DataGridView1.DataSource = Nothing
            DataGridView2.DataSource = ta
            DataGridView2.DataSource = Nothing
            DataGridView4.DataSource = ta
            DataGridView4.DataSource = Nothing
            loaddataHHLNotY()
        ElseIf ComboBox1.Text = "MEM" And ComboBox2.Text = "Not Y" Then
            Dim ta As New DataTable
            ta.Clear()
            DataGridView1.DataSource = ta
            DataGridView1.DataSource = Nothing
            DataGridView2.DataSource = ta
            DataGridView2.DataSource = Nothing 
            loaddataHHL()
        Else
            Dim ta As New DataTable
            ta.Clear()
            DataGridView1.DataSource = ta
            DataGridView1.DataSource = Nothing
            DataGridView2.DataSource = ta
            DataGridView2.DataSource = Nothing
            DataGridView3.DataSource = ta
            DataGridView3.DataSource = Nothing
            DataGridView4.DataSource = ta
            DataGridView4.DataSource = Nothing
            ' loaddataBcc()
        End If
        
    End Sub
    Private Sub delcard_Click(sender As Object, e As EventArgs) Handles delcard.Click
        del()
    End Sub
    Private Sub del()
        If MsgBox("Are you sure do you want to delete?", CType(MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.Exclamation, MsgBoxStyle), "Delete") = MsgBoxResult.Yes Then
            If ComboBox1.Text = "BCC" Then
                For r As Integer = 0 To DataGridView1.Rows.Count - 2
                    Call conns()
                    cmd = New MySqlCommand("Delete from idcardrecor where bccidno = '" & DataGridView1.Rows(r).Cells(0).Value & "'", conn1)
                    cmd.ExecuteNonQuery()
                    conclos()
                Next
                MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
            ElseIf ComboBox1.Text = "PCC" Then
                For r As Integer = 0 To DataGridView2.Rows.Count - 2
                    Call conns()
                    cmd = New MySqlCommand("Delete from idcardrecor where pccidno = '" & DataGridView2.Rows(r).Cells(0).Value & "'", conn1)
                    cmd.ExecuteNonQuery()
                    conclos()
                Next
                MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
            ElseIf ComboBox1.Text = "HHL" Then
                For r As Integer = 0 To DataGridView3.Rows.Count - 2
                    Call conns()
                    cmd = New MySqlCommand("Delete from idcardrecor where hhlidno = '" & DataGridView3.Rows(r).Cells(0).Value & "'", conn1)
                    cmd.ExecuteNonQuery()
                    conclos()
                Next
                MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
            ElseIf ComboBox1.Text = "MEM" Then
                For r As Integer = 0 To DataGridView4.Rows.Count - 2
                    Call conns()
                    cmd = New MySqlCommand("Delete from idcardrecor where memidno = '" & DataGridView4.Rows(r).Cells(0).Value & "'", conn1)
                    cmd.ExecuteNonQuery()
                    conclos()
                Next
                MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
            End If
        End If
    End Sub
End Class