Imports System.Windows.Forms
Imports MySql.Data.MySqlClient
Imports Microsoft.Reporting
Imports Microsoft.Reporting.WinForms
Imports System.IO 
Public Class printperpuroksum
    Public sCon As MySqlConnection = New MySqlConnection
    Public sComand As MySqlDataAdapter = New MySqlDataAdapter
    Public ds As DataSet = New DataSet
    Dim scrollVal As Integer
    Dim pagingAdapter As New MySqlDataAdapter
    Dim pagingDS As New DataSet
    Dim pagingDS2 As New DataSet
    Private sRecord_Count As Integer
    Dim conn As New MySqlConnection
    Dim cmd As New MySqlCommand
    Dim dr As MySqlDataReader
    Dim table As New DataTable() 
    Private Sub conns()
        Try
            conn = New MySqlConnection("server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706")
            conn.Open()
        Catch ex As Exception
            MessageBox.Show("Please Try Again", "connection",
  MessageBoxButtons.OK, MessageBoxIcon.Error)
            conn.Close()
            conn.Open()
            'End
        End Try
    End Sub
    Dim connectionString As New MySqlConnection("server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706")
    Private Sub printperprecint_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'caragavotersdbDataSet.full_data_entry' table. You can move, or remove it, as needed.
        'Me.full_data_entryTableAdapter.Fill(Me.caragavotersdbDataSet.full_data_entry)
        ''TODO: This line of code loads data into the 'caragavotersdbDataSet.full_data' table. You can move, or remove it, as needed.
        'Me.full_dataTableAdapter.FillBy(Me.caragavotersdbDataSet.full_data)
        ''TODO: This line of code loads data into the 'caragavotersdbDataSet.full_data' table. You can move, or remove it, as needed.
        ''Me.full_data_entryTableAdapter.Fill(Me.caragavotersdbDataSet.full_data_entry)
        ''Me.ReportViewer1.RefreshReport()
        selectbargy2()
    End Sub
    Private Sub selectbargy2()
        Dim adapter As New MySqlDataAdapter("SELECT barangay FROM purok ORDER BY barangay ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        ComboBox1.DataSource = table
        ComboBox1.ValueMember = "barangay"
        ComboBox1.DisplayMember = "barangay"
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(0)).Distinct.ToList
        ComboBox1.DataSource = lst
        connectionString.Close()
        connectionString.Dispose()
        MySqlConnection.ClearPool(connectionString)
    End Sub
    Private Sub selectprec()
        Dim adapter As New MySqlDataAdapter("SELECT purok FROM purok WHERE barangay LIKE '%" & ComboBox1.Text & "%' ORDER BY purok ASC", connectionString)
        Dim table As New DataTable()
        adapter.Fill(table)
        ComboBox2.DataSource = table
        ComboBox2.ValueMember = "purok"
        ComboBox2.DisplayMember = "purok"
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(0)).Distinct.ToList
        ComboBox2.DataSource = lst
        connectionString.Close()
        connectionString.Dispose()
        MySqlConnection.ClearPool(connectionString)
    End Sub
    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        selectprec()
    End Sub

    '    SELECT        COUNT(*) AS Expr1, barangay, address
    'FROM            full_data_entry
    'WHERE        (address = @Param2) AND (barangay = @Param1)
    'GROUP BY barangay, address

    '    str = "SELECT COUNT(Nickname) as myCount FROM Backup"
    'cmd.CommandText = str
    'cmd.Connection = myConn
    'myConn.Open()
    'Using cntPlns As SqlDataReader = command.ExecuteReader
    '        If cntPlns.Read Then
    '             MessageBox.Show(cntPlns.GetInt32(0).ToString)  ' the first column
    '        End If
    'End Using

    Private Sub fillbypurok()
        Dim table As New DataTable()
        Try
            conns()
            Dim adapter As New MySqlDataAdapter("SELECT COUNT(name) as myname FROM full_data  where barangay LIKE '%" & ComboBox1.Text & "%' ORDER BY barangay, address ASC", conn)
            ' command.CommandText = "INSERT INTO full_data_x (name, birthday, precinct_number, Gender, barangay, address, number) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7)"
            adapter.Fill(table)

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

        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally
            conn.Close()
            conn.Dispose()
        End Try
    End Sub

    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged
        Try
            Me.full_dataTableAdapter.FillByPurokSum(Me.caragavotersdbDataSet.full_data, ComboBox2.Text, ComboBox1.Text)
            Me.full_data_entryTableAdapter.FillBypurokSum(Me.caragavotersdbDataSet.full_data_entry, ComboBox2.Text, ComboBox1.Text)
            'Me.ReportViewer1.RefreshReport()
            DataGridView1.Refresh()
            DataGridView2.Refresh()

        Catch ex As Exception

        End Try
    End Sub


End Class