Imports MySql.Data.MySqlClient
Imports System.IO
Imports System.Data
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Security.Cryptography
Imports System.Windows.Forms
Public Class Dashboard
    Private sCon As MySqlConnection = New MySqlConnection
    Private sComand As MySqlDataAdapter = New MySqlDataAdapter
    Private 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 connStrr As String = "server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706"
    Dim connectionString As String = "server=10.10.0.6;user id=corpadmin;password=admincorp2024;database=caragavotersdb;port=3706"
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'masterlist.Show()
        selectmasterlist.Show()

    End Sub
   

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        loadingform.Show()
        loadingform.Label4.Text = "barangay"
        loadingform.Timer1.Enabled = True
        loadingform.Timer1.Start()
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        loadingform.Show()
        loadingform.Label4.Text = "dataentry"
        loadingform.Timer1.Enabled = True
        loadingform.Timer1.Start()
    End Sub

    Private Sub Dashboard_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
        LoginForm1.Show()
    End Sub
    Private Sub Dashboard_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        conns()
        counters()
        Label17.Visible = False
        totalhide()
        openapp.Visible = False
    End Sub
    Sub counters()
        countvoters()
        countpurok()
        countbrgy()
        countrecruites()
        countpco()
        countbcc()
        counthhl()
        totvotersall()
    End Sub
    Sub totvotersall()
        Dim totalla As Integer
        Dim totallb As Integer
        Dim totallc As Integer
        Dim totalld As Integer
        Dim totalle As Integer

        totallb = Label7.Text
        totallc = Label10.Text
        totalld = Label12.Text
        totalle = Label14.Text
        totalla = totallb + totallc + totalld + totalle
        Label15.Text = totalla
    End Sub
    Sub countvoters()
        Dim sql As String = "SELECT * FROM masterlist2 ORDER BY name ASC"
        Dim connection As New MySqlConnection(connectionString)
        pagingAdapter = New MySqlDataAdapter(sql, connection)
        pagingDS = New DataSet()
        pagingDS2 = New DataSet()
        connection.Open()
        pagingAdapter.Fill(pagingDS2, "masterlist2_table")
        connection.Close()
        sRecord_Count = pagingDS2.Tables(0).Rows.Count
        Label2.Text = sRecord_Count
        connection.Close()
        connection.Dispose()
    End Sub
    Sub countpurok()
        Dim sql As String = "SELECT * FROM purok ORDER BY purok ASC"
        Dim connection As New MySqlConnection(connectionString)
        pagingAdapter = New MySqlDataAdapter(sql, connection)
        pagingDS = New DataSet()
        pagingDS2 = New DataSet()
        connection.Open()
        pagingAdapter.Fill(pagingDS2, "purok_table")
        connection.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
        Label4.Text = sRecord_Count
        connection.Close()
        connection.Dispose()
    End Sub
    Sub countbrgy()
        Dim sql As String = "SELECT * FROM purok ORDER BY barangay ASC"
        Dim connection As New MySqlConnection(connectionString)
        pagingAdapter = New MySqlDataAdapter(sql, connection)
        pagingDS2 = New DataSet()
        connection.Open()
        pagingAdapter.Fill(pagingDS2, "barangay_table")
        connection.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)(2)).Distinct.Count
        Label6.Text = lst
        connection.Close()
        connection.Dispose()
    End Sub
    Sub countrecruites()
        Dim sql As String = "SELECT * FROM full_data_entry ORDER BY name ASC"
        Dim connection As New MySqlConnection(connectionString)
        pagingAdapter = New MySqlDataAdapter(sql, connection)
        pagingDS = New DataSet()
        pagingDS2 = New DataSet()
        connection.Open()
        pagingAdapter.Fill(pagingDS2, "full_data_entry_table")
        connection.Close()
        sRecord_Count = pagingDS2.Tables(0).Rows.Count
        Label7.Text = sRecord_Count
        connection.Close()
        connection.Dispose()
    End Sub
    Sub countpco()
        Dim sql As String = "SELECT * FROM pco"
        Dim connection As New MySqlConnection(connectionString)
        pagingAdapter = New MySqlDataAdapter(sql, connection)
        pagingDS2 = New DataSet()
        connection.Open()
        pagingAdapter.Fill(pagingDS2, "pco_table")
        connection.Close()
        sRecord_Count = pagingDS2.Tables(0).Rows.Count
        ' Label12.Text = sRecord_Count
        Dim table As New DataTable()
        pagingAdapter.Fill(table)
        Dim lst = (From row In table.AsEnumerable
                   Select row.Field(Of String)(1)).Count
        Label12.Text = lst
        connection.Close()
        connection.Dispose()
    End Sub
    Sub countbcc()
        Dim sql As String = "SELECT * FROM bcc"
        Dim connection As New MySqlConnection(connectionString)
        pagingAdapter = New MySqlDataAdapter(sql, connection)
        pagingDS2 = New DataSet()
        connection.Open()
        pagingAdapter.Fill(pagingDS2, "bcc_table")
        connection.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)).Count
        Label10.Text = lst
        connection.Close()
        connection.Dispose()
    End Sub
    Sub counthhl2()
        Dim sql As String = "SELECT * FROM hhl"
        Dim connection As New MySqlConnection(connectionString)
        pagingAdapter = New MySqlDataAdapter(sql, connection)
        pagingDS2 = New DataSet()
        connection.Open()
        pagingAdapter.Fill(pagingDS2, "hhl_table")
        connection.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)).Count
        Label14.Text = lst
        connection.Close()
        connection.Dispose()
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        loadingform.Show()
        loadingform.Label4.Text = "pcc"
        loadingform.Timer1.Enabled = True
        loadingform.Timer1.Start()
    End Sub
    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        loadingform.Show()
        loadingform.Label4.Text = "hhl"
        loadingform.Timer1.Enabled = True
        loadingform.Timer1.Start()
    End Sub
    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        loadingform.Show()
        loadingform.Label4.Text = "bcc"
        loadingform.Timer1.Enabled = True
        loadingform.Timer1.Start()
    End Sub
    Dim OutputStream As System.IO.StreamWriter
    Sub OnDataReceived1(ByVal Sender As Object, ByVal e As System.Diagnostics.DataReceivedEventArgs)
        If e.Data IsNot Nothing Then
            Dim text As String = e.Data
            Dim bytes As Byte() = Encoding.Default.GetBytes(text)
            text = Encoding.UTF8.GetString(bytes)
            OutputStream.WriteLine(text)
        End If
    End Sub
    Private Sub sqlbackup()
        '--host=[HOSTNAME] --user=[USER] --password=[PASSWORD] -R [DATABASE NAME]
        Process.Start("C:\xampp\mysql\bin\mysqldump.exe", " --user=corpadmin --password=admincorp2024  --host=10.10.0.6 -P 3706  caragavotersdb -r ""E:\filesss\OneDrive - MSFT\backup\b_ac_kup.sql""")
    End Sub
    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
        sqlbackup()
    End Sub
    Private Sub sqlrestore()
        Dim myProcess As New Process()
        myProcess.StartInfo.FileName = "cmd.exe"
        myProcess.StartInfo.UseShellExecute = False
        myProcess.StartInfo.WorkingDirectory = "C:\xampp\mysql\bin\"
        myProcess.StartInfo.RedirectStandardInput = True
        myProcess.StartInfo.RedirectStandardOutput = True
        myProcess.Start()
        Dim myStreamWriter As StreamWriter = myProcess.StandardInput
        Dim mystreamreader As StreamReader = myProcess.StandardOutput
        myStreamWriter.WriteLine("mysql -ucorpadmin -padmincorp2024 -h10.10.0.6 -P3706 caragavotersdb < E:\filesss\kup.sql")
        'myStreamWriter.Close()
        'myProcess.WaitForExit()
        'myProcess.Close()
    End Sub
    Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
        sqlrestore()
    End Sub
    Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click
        'chartbymembers.Show()
        loadingform.Show()
        loadingform.Label4.Text = "summary"
        loadingform.Timer1.Enabled = True
        loadingform.Timer1.Start()
    End Sub
    'Sub countperalvar()
    '    Dim sql As String = "SELECT * FROM full_data_entry where statusv = SOLID AND barangay = ALVAR"
    '    Dim connection As New MySqlConnection(connectionString)
    '    pagingAdapter = New MySqlDataAdapter(sql, connection)
    '    pagingDS2 = New DataSet()
    '    connection.Open()
    '    pagingAdapter.Fill(pagingDS2, "hhl_table")
    '    connection.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)).Count
    '    Label14.Text = lst
    '    connection.Close()
    '    connection.Dispose()
    'End Sub
    Sub counthhl()
        Dim querry As String = "SELECT COUNT(*) FROM hhl"
        Using conn As New MySqlConnection(connStrr)
            Using cmd As New MySqlCommand()
                With cmd
                    .Connection = conn
                    .CommandText = querry
                    .CommandType = CommandType.Text
                End With
                Try
                    conn.Open()
                    Dim count As Int16 = Convert.ToInt16(cmd.ExecuteScalar())
                    Label14.Text = count
                    ' MsgBox(count.ToString())
                    conn.Close()
                Catch ex As Exception
                    ' put your exception here '
                End Try
            End Using
        End Using
    End Sub
    Private Sub Button10_Click(sender As Object, e As EventArgs) Handles Button10.Click
        updatemasterlist.Show()
    End Sub
    Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click
        counters()
    End Sub
    Private Sub Button12_Click(sender As Object, e As EventArgs) Handles Button12.Click
        'Printout.Show()
        openapp.Text = "Printout"
        passopen.ShowDialog()
        'loginDialog1.Show()
    End Sub

    Private Sub Button13_Click(sender As Object, e As EventArgs) Handles Button13.Click
        Duplicate_Scanner.Show()
    End Sub

    Private Sub Button14_Click(sender As Object, e As EventArgs) Handles Button14.Click
        TextBox1.Text = Decryptel(TextBox1.Text, 1)
        TextBox1.SelectAll()
        Try
            If TextBox1.SelectedText = vbNullString Then Exit Sub
            Clipboard.SetText(TextBox1.SelectedText)
        Catch ex As Exception
        End Try
        TextBox1.PasswordChar = "*"
    End Sub
    Private Sub totalhide()
        Label1.Visible = False
        Label2.Visible = False
        Label3.Visible = False
        Label4.Visible = False
        Label5.Visible = False
        Label6.Visible = False
        Label7.Visible = False
        Label8.Visible = False
        Label9.Visible = False
        Label10.Visible = False
        Label11.Visible = False
        Label12.Visible = False
        Label13.Visible = False
        Label14.Visible = False
        Label15.Visible = False
        Label16.Visible = False
    End Sub
    Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged
        If CheckBox1.CheckState = CheckState.Checked Then
            dashboardpass.ShowDialog()
        End If
        If CheckBox1.Checked = False Then
            totalhide()
        End If
       
    End Sub

    Private Sub Button15_Click(sender As Object, e As EventArgs) Handles Button15.Click
        DeleteBy.Show()
    End Sub

    Private Sub Button16_Click(sender As Object, e As EventArgs) Handles Button16.Click
        MasterlistandOldList.Show()
    End Sub

    Private Sub Button17_Click(sender As Object, e As EventArgs) Handles Button17.Click
        List_of_X.Show()
    End Sub

    Private Sub Panel1_Paint(sender As Object, e As PaintEventArgs) Handles Panel1.Paint

    End Sub

    Private Sub Button18_Click(sender As Object, e As EventArgs) Handles Button18.Click
        useradd.Show()
    End Sub
End Class