Imports MySql.Data.MySqlClient
Public Class bccdata
    Public sCon As MySqlConnection = New MySqlConnection
    Public sComand As MySqlDataAdapter = New MySqlDataAdapter
    Public ds As DataSet = New DataSet
    Dim scrollVal As New Integer
    Dim pagingAdapter As New MySqlDataAdapter
    Dim pagingAdapter1 As New MySqlDataAdapter
    Dim pagingDS As New DataSet
    Dim pagingDS2 As New 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
    '          MessageBox.Show("Please Close to finish update 5 seconds & Open the program again. Or contact the programmer", "Update",
    'MessageBoxButtons.OK, MessageBoxIcon.Error)
    '          End
    '      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 bcc_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        conns()
        selectbargy()
        selectpurok()
        countpco()
        purokid.Visible = False
        'loadnames()
        'fillist()
        purok.Text = ""
        'barangay.Text = ""
        names.Text = ""
        Label7.Visible = False
        conclos()
    End Sub
    Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles purok.SelectedIndexChanged
        namesugg()
        fillist()
    End Sub
    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles barangay.SelectedIndexChanged
        'namesugg()
        selectpurok1()
    End Sub
    Private Sub name_SelectedIndexChanged(sender As Object, e As EventArgs) Handles names.SelectedIndexChanged
        selectinfo()
    End Sub
    Private Sub selectpurok1()
        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
        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
            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
            conclos()
        End If
    End Sub
    Private Sub selectpurok()
        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
        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)
        While sdr.Read()
            'birth.Text = sdr("birthday")
            'gender.Text = sdr("Gender")
            precint.Text = sdr("precinct_number")
            purok.Text = sdr("address")
            precint.Enabled = False
        End While
        conclos()
    End Sub
    Private Sub namesugg()
        Dim adapter As New MySqlDataAdapter("SELECT * FROM full_data where barangay LIKE  '%" & barangay.Text & "%' AND address = '" & purok.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
        conclos()
    End Sub
    Private Sub loadnames()
        Try
            conn1.Open()
            Dim dtt As New DataTable()
            With cmd
                .Connection = conn1
                .CommandText = "Select * FROM full_data WHERE barangay LIKE  '%" & barangay.Text & "%'"
            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()

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        conclos()
    End Sub
    Private Sub clear()
        fillist()
        scrollVal = 0
        'Label3.Text = ""
        barangay.Text = ""
        names.Text = ""
        purok.Text = ""
        precint.Text = ""
        purokid.Text = ""
        Label7.Text = ""
        conclos()
    End Sub
    Private Sub fillist()
        Try

            conns()
            cmd = New MySqlCommand("SELECT * FROM bcc where barangay LIKE  '%" & barangay.Text & "%' ORDER BY id DESC", conn1)
            cmd.ExecuteNonQuery()
            conn1.Close()
            pagingAdapter1 = New MySqlDataAdapter(cmd)
            pagingDS = New DataSet()
            'pagingDS2 = New DataSet()
            conn1.Open()
            'pagingAdapter1.Fill(pagingDS2, "bcc_table")
            pagingAdapter1.Fill(pagingDS, scrollVal, 20, "bcc_table")
            conn1.Close()
            DataGridView1.DataSource = pagingDS
            DataGridView1.DataMember = "bcc_table"
            With DataGridView1
                .Columns(1).HeaderCell.Value = "Name"
                .Columns(2).HeaderCell.Value = "Purok"
                .Columns(3).HeaderCell.Value = "Barangay"
                .Columns(6).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("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
            Dim i As Integer
            With DataGridView1
                If e.RowIndex >= 0 Then
                    i = .CurrentRow.Index
                    purokid.Text = .Rows(i).Cells("id").Value.ToString
                    names.Text = .Rows(i).Cells("names").Value.ToString
                    Label7.Text = .Rows(i).Cells("names").Value.ToString
                    purok.Text = .Rows(i).Cells("purok").Value.ToString
                    barangay.Text = .Rows(i).Cells("barangay").Value.ToString
                    contact.Text = .Rows(i).Cells("contact").Value.ToString
                    votemj.Text = .Rows(i).Cells("status").Value.ToString
                    precint.Text = .Rows(i).Cells("precint").Value.ToString
                End If
            End With
            ' name.Text = Decryptel(name.Text, 1)
        Catch exx As Exception

        End Try
        conclos()
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            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()
                                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
            clear()

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

        End Try
        If Dashboard.Label17.Text = "lhoy" Or Dashboard.Label17.Text = "EL" Then
            sqlbackuperclik()
        Else

        End If
        conclos()
    End Sub
    Private Sub savebcc()
        Call conns()
        cmd = New MySqlCommand("Insert into bcc (names,purok,barangay,contact,precint,status,cardno) Values" _
    & " ('" & names.Text & "', '" & purok.Text & "','" & barangay.Text _
    & "', '" & contact.Text & "', '" & precint.Text & "', '" & votemj.Text & "', '" & cardno.Text & "')", conn1)
        cmd.ExecuteNonQuery()
    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
        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 full_data where name LIKE  '%" & names.Text & "%'", conn1)
                        dr = cmd.ExecuteReader
                        If (dr.Read) Then
                            updatepcc2()
                            Call conns()
                            cmd = New MySqlCommand("Update bcc set names = '" & names.Text & "', purok ='" & purok.Text & "', barangay ='" & barangay.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
        conclos()
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        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
                updatepcc()
                Call conns()
                cmd = New MySqlCommand("Delete from bcc where id = '" & purokid.Text & "'", conn1)
                cmd.ExecuteNonQuery()
                MsgBox("Successfully Deleted", MsgBoxStyle.Information, "Deleted")
                clear()
            End If
        End If
        conclos()
    End Sub
    Private Sub updatepcc()
        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 pco where bcc LIKE  '%" & names.Text & "%' and barangay LIKE '%" & barangay.Text & "%'", conn1)
            dr = cmd.ExecuteReader
            If (dr.Read) Then
                If MsgBox("PCC with assigned BCC will update to blank BCC", 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 pco set bcc = '' where bcc 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
        conclos()
    End Sub
    Private Sub updatepcc2()
        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 pco where bcc LIKE  '%" & names.Text & "%' and barangay LIKE '%" & barangay.Text & "%'", conn1)
            dr = cmd.ExecuteReader
            If (dr.Read) Then

            Else
                If MsgBox("PCC with assigned BCC 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 pco set bcc = '" & names.Text & "' where bcc LIKE  '" & Label7.Text & "' and barangay LIKE '%" & barangay.Text & "%'", conn1)
                    cmd.ExecuteNonQuery()
                    'MsgBox("Successfully Updated", MsgBoxStyle.Information, "Update")
                    'clear()
                End If
            End If
        End If
        conclos()
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click, Button7.Click
        clear()
    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 - 20
            If scrollVal <= 0 Then
                scrollVal = 0
            End If
            pagingDS.Clear()
            pagingAdapter1.Fill(pagingDS, scrollVal, 20, "bcc_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
            'If conn.State = ConnectionState.Open Then
            '    conn1.Close()
            'ElseIf connectionString.State = ConnectionState.Open Then
            '    connectionString.Close()
            'Else
            scrollVal = scrollVal + 20
            'If scrollVal > 23 Then
            '    scrollVal = 18
            'End If
            If scrollVal >= 0 Then
                'scrollVal = 40
            End If
            pagingDS.Clear()
            pagingAdapter1.Fill(pagingDS, scrollVal, 20, "bcc_table")
            'End If
        Catch ex As Exception

        End Try
        conclos()
    End Sub
    Private Sub countpco()
        Call conns()
        cmd = New MySqlCommand("SELECT * FROM bcc ORDER BY id ASC", conn1)
        cmd.ExecuteNonQuery()
        conn1.Close()
        pagingAdapter = New MySqlDataAdapter(cmd)
        pagingDS2 = New DataSet()
        conn1.Open()
        pagingAdapter.Fill(pagingDS2, "bcc_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
        conclos()
    End Sub
    Private Sub names_SelectedIndexChanged(sender As Object, e As KeyPressEventArgs) Handles names.KeyPress
        e.KeyChar = UCase(e.KeyChar)
    End Sub
End Class