Imports System.IO
Imports System.Windows.Forms
Imports System.Net.Mail
Imports System.Data.OleDb
Imports System.Security.Permissions
Imports System.Text
Imports System.Security.Cryptography

Public Class frmAdminENG
    Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|datadirectory|\sis.accdb"
    Dim connect As New OleDbConnection

#Region "Connections"
    Public Sub openconnection()
        If connect.State = ConnectionState.Closed Then
            connect.ConnectionString = connstring
            connect.Open()
        ElseIf connect.State = ConnectionState.Open Then
            Me.Refresh()
        End If
    End Sub

    Public Sub closeconnection()
        If connect.State = ConnectionState.Open Then
            connect.Close()
        ElseIf connect.State = ConnectionState.Closed Then
            Me.Refresh()
        End If
    End Sub
#End Region

#Region "Buttons"
#Region "HOME"
    Private Sub btnHome_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHome.Click
        enablebtns()
        btnHome.Enabled = False
        hidegb()
        gbHome.Dock = DockStyle.Fill
        gbHome.Show()
        clearinputs_home()
    End Sub

    Private Sub btnHomepost_Click(sender As System.Object, e As System.EventArgs) Handles btnHomepost.Click
        Dim ntitle As String = txtHometitle.Text.Trim
        Dim ndescription As String = txtHomedescription.Text.Trim
        Dim ntime As String = lblTime.Text.Trim & " " & lblDate.Text.Trim

        If IsNothing(ntitle) = True Then
            MsgBox("Please enter the title of news", MsgBoxStyle.Information, "Error")
            txtHometitle.Focus()
        ElseIf ntitle.Length < 3 Then
            MsgBox("News title can't be less than 3 characters", MsgBoxStyle.Information, "Error")
            txtHometitle.Focus()
        ElseIf IsNothing(ndescription) = True Then
            MsgBox("Please enter the description of news", MsgBoxStyle.Information, "Error")
            txtHomedescription.Focus()
        ElseIf ndescription.Length < 3 Then
            MsgBox("News description can't be less than 3 characters", MsgBoxStyle.Information, "Error")
            txtHomedescription.Focus()
        Else
            'for log file
            'Dim uname As String =frmlogin.lbluname.text
            Dim uname As String = "cngirwa"
            Dim activity As String = uname + " posted a news with title " + ntitle
            Dim activitydate As String = lblTime.Text + " " + lblDate.Text
            Dim insertlog As String = "INSERT INTO logfiles VALUES('" + uname + "','" + activity + "','" + activitydate + "')"
            Dim insertlogcmd As New OleDbCommand
            With insertlogcmd
                .CommandText = insertlog
                .Connection = connect
                .ExecuteNonQuery()
            End With

            Dim insertqry As String = "INSERT INTO news(ntitle,ndescription,ntime) VALUES('" + ntitle + "','" + ndescription + "','" + ntime + "')"
            Dim insertcmd As New OleDbCommand
            With insertcmd
                .CommandText = insertqry
                .Connection = connect
                .ExecuteNonQuery()
            End With
            MsgBox("News posted", MsgBoxStyle.Information, "Success")
            viewnews()
            clearinputs_home()
        End If
    End Sub

    Private Sub btnHomeclear_Click(sender As System.Object, e As System.EventArgs) Handles btnHomeclear.Click
        clearinputs_home()
    End Sub

    Private Sub clearinputs_home()
        txtHometitle.Clear()
        txtHomedescription.Clear()
    End Sub

    Private Sub viewnews()
        Dim selectqry As String = "SELECT * FROM news"
        Dim da As OleDbDataAdapter
        Dim dtset As New DataSet
        Dim dttable As New DataTable
        da = New OleDbDataAdapter(selectqry, connect)
        da.Fill(dtset, "news")
        dttable = dtset.Tables("news")

        dgvHome.DataSource = dttable
        dgvHome.ColumnHeadersDefaultCellStyle.Font = New Font(dgvHome.Font, FontStyle.Bold)
        dgvHome.ForeColor = Color.Black
        dgvHome.Font = New Font(dgvHome.Font, FontStyle.Regular)

        dgvHome.Columns("ID").HeaderText = "News number"
        dgvHome.Columns("ID").Width = 150

        dgvHome.Columns("ntitle").HeaderText = "News Title"
        dgvHome.Columns("ntitle").Width = 230

        dgvHome.Columns("ndescription").HeaderText = "News Description"
        dgvHome.Columns("ndescription").Width = 478

        dgvHome.Columns("ntime").HeaderText = "Time Posted"
        dgvHome.Columns("ntime").Width = 270
    End Sub

    Private Sub btnHomedelete_Click(sender As System.Object, e As System.EventArgs) Handles btnHomedelete.Click
        Dim ntitle As String = txtHometitle.Text.Trim
        Dim ndescription As String = txtHomedescription.Text.Trim
        Dim ntime As String = lbltimedelete.Text
        Dim confirmdel As String = "Are you sure you want to delete news titled " & ntitle & "?"
        Dim deletemsg As String

        If StrComp(ntitle, "") = 0 Or IsDBNull(ntitle) = True Then
            MsgBox("Please select a news to delete", MsgBoxStyle.Information, "Error")
        ElseIf StrComp(ndescription, "") = 0 Or IsDBNull(ndescription) = True Then
            MsgBox("Please select a news to delete", MsgBoxStyle.Information, "Error")
        Else
            'for log file
            'Dim uname As String =frmlogin.lbluname.text
            Dim uname As String = "cngirwa"
            Dim activity As String = uname + " deleted a news with title " + ntitle
            Dim activitydate As String = lblTime.Text + " " + lblDate.Text
            Dim insertlog As String = "INSERT INTO logfiles VALUES('" + uname + "','" + activity + "','" + activitydate + "')"
            Dim insertlogcmd As New OleDbCommand
            With insertlogcmd
                .CommandText = insertlog
                .Connection = connect
                .ExecuteNonQuery()
            End With

            Dim deleteqry As String = "DELETE * FROM news WHERE ntime='" + ntime + "'"
            Dim deletecmd As New OleDbCommand
            deletemsg = MessageBox.Show(confirmdel, "Delete news titled " & ntitle & "?", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
            If deletemsg = MsgBoxResult.No Then
                Me.Refresh()
            Else
                With deletecmd
                    .CommandText = deleteqry
                    .Connection = connect
                    .ExecuteNonQuery()
                End With
                MsgBox("News deleted", MsgBoxStyle.Information, "Success")
                txtHometitle.Clear()
                txtHomedescription.Clear()
                viewnews()
            End If
        End If
    End Sub

    Private Sub dgvHome_CellContentClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvHome.CellContentClick
        Dim s As System.Drawing.Point
        s = dgvHome.CurrentCellAddress
        Dim dbtitle As String = dgvHome.Item("ntitle", s.Y).Value.ToString
        txtHometitle.Text = dbtitle
        Dim dbdescription As String = dgvHome.Item("ndescription", s.Y).Value.ToString
        txtHomedescription.Text = dbdescription
        Dim dbtime As String = dgvHome.Item("ntime", s.Y).Value.ToString
        lbltimedelete.Text = dbtime
    End Sub

    Private Sub btnHomerefresh_Click(sender As System.Object, e As System.EventArgs) Handles btnHomerefresh.Click
        viewnews()
    End Sub

    Private Sub btnhomesearch_Click(sender As System.Object, e As System.EventArgs) Handles btnhomesearch.Click
        Dim search As String = txthomesearch.Text.Trim
        If (search = "" Or IsNothing(search) = True) Then
            MsgBox("Please enter something to search", MsgBoxStyle.Information, "Error")
            txthomesearch.Focus()
        Else
            txthomesearch.Clear()
            Dim searchqry As String = "SELECT * FROM news WHERE id LIKE '%" + search + "%' OR ntitle LIKE '%" + search + "%' OR ndescription LIKE '%" + search + "%' OR ntime LIKE '%" + search + "%'"
            Dim da As OleDbDataAdapter
            Dim dtset As New DataSet
            Dim dttable As New DataTable
            da = New OleDbDataAdapter(searchqry, connect)
            da.Fill(dtset, "news")
            dttable = dtset.Tables("news")

            dgvHome.DataSource = dttable
            dgvHome.ColumnHeadersDefaultCellStyle.Font = New Font(dgvHome.Font, FontStyle.Bold)
            dgvHome.ForeColor = Color.Black
            dgvHome.Font = New Font(dgvHome.Font, FontStyle.Regular)

            dgvHome.Columns("ID").HeaderText = "News number"
            dgvHome.Columns("ID").Width = 150

            dgvHome.Columns("ntitle").HeaderText = "News Title"
            dgvHome.Columns("ntitle").Width = 230

            dgvHome.Columns("ndescription").HeaderText = "News Description"
            dgvHome.Columns("ndescription").Width = 478

            dgvHome.Columns("ntime").HeaderText = "Time Posted"
            dgvHome.Columns("ntime").Width = 270

        End If
    End Sub
#End Region

#Region "ADD STOCK"
    Private Sub btnAddstock_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddstock.Click
        enablebtns()
        btnAddstock.Enabled = False
        hidegb()
        gbAddstock.Dock = DockStyle.Fill
        gbAddstock.Show()
        clearinputs_addstock()
        lbldateadded.Text = ""
    End Sub

    Private Sub btnSaveaddstock_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveaddstock.Click

        Dim pname As String = txtnameaddstock.Text.Trim
        Dim pcategory As String = cbcategoryaddstock.Text.Trim
        Dim pquantity As String = txtquantityaddstock.Text.Trim
        Dim pbuying As String = txtbuyingpriceaddstock.Text.Trim
        Dim pselling As String = txtsellpriceaddstock.Text.Trim
        Dim pinfo As String = txtInfoaddstock.Text.Trim
        Dim ptime As String = lblTime.Text.Trim & " " & lblDate.Text.Trim

        If pname.Length < 2 Then
            MsgBox("Product name can not be less than 2 characters", MsgBoxStyle.Information, "Error")
            txtnameaddstock.Focus()
        ElseIf pname.Length > 25 Then
            MsgBox("Product name can not be more than 25 characters", MsgBoxStyle.Information, "Error")
            txtnameaddstock.Focus()

        ElseIf cbcategoryaddstock.Text = "" Then
            MsgBox("Please select product category", MsgBoxStyle.Information, "Error")
            cbcategoryaddstock.Focus()

        ElseIf IsNumeric(pquantity) = False Then
            MsgBox("Only numbers are allowed", MsgBoxStyle.Information, "Error")
            txtquantityaddstock.Focus()
        ElseIf pquantity.Length > 25 Then
            MsgBox("Product quantity can not be more than 25 digits", MsgBoxStyle.Information, "Error")
            txtquantityaddstock.Focus()

        ElseIf IsNumeric(pbuying) = False Then
            MsgBox("Only numbers are allowed", MsgBoxStyle.Information, "Error")
            txtbuyingpriceaddstock.Focus()
        ElseIf pbuying.Length > 25 Then
            MsgBox("Product buying price can not be more than 25 digits", MsgBoxStyle.Information, "Error")
            txtbuyingpriceaddstock.Focus()

        ElseIf IsNumeric(pselling) = False Then
            MsgBox("Only numbers are allowed", MsgBoxStyle.Information, "Error")
            txtsellpriceaddstock.Focus()
        ElseIf pselling.Length > 25 Then
            MsgBox("Product selling price can not be more than 25 digits", MsgBoxStyle.Information, "Error")
            txtsellpriceaddstock.Focus()

        ElseIf pinfo.Length < 2 Then
            MsgBox("Product informations can't be less than 2 characters", MsgBoxStyle.Information, "Error")
            txtInfoaddstock.Focus()

        ElseIf txtInfoaddstock.Text = "" Then
            MsgBox("Please enter information about the product", MsgBoxStyle.Information, "Error")
            txtInfoaddstock.Focus()

        ElseIf lbldateadded.Text <> "" Then

            'for log file
            'Dim uname As String =frmlogin.lbluname.text
            Dim uname As String = "cngirwa"
            Dim activity As String = uname + " updated a product with name " + pname
            Dim activitydate As String = lblTime.Text + " " + lblDate.Text
            Dim insertlog As String = "INSERT INTO logfiles VALUES('" + uname + "','" + activity + "','" + activitydate + "')"
            Dim insertlogcmd As New OleDbCommand
            With insertlogcmd
                .CommandText = insertlog
                .Connection = connect
                .ExecuteNonQuery()
            End With

            Dim updateqry As String = "UPDATE stock SET pname='" + pname + "',pcategory='" + pcategory + "',pquantity='" + pquantity + "',pbuying='" + pbuying + "',pselling='" + pselling + "',pinfo='" + pinfo + "',pdate='" + ptime + "' WHERE pdate='" + lbldateadded.Text + "'"
            Dim updatecmd As New OleDbCommand
            With updatecmd
                .CommandText = updateqry
                .Connection = connect
                .ExecuteNonQuery()
            End With
            MsgBox("Product updated successfuly", MsgBoxStyle.Information, "Success")
            clearinputs_addstock()
            lbldateadded.Text = ""

        Else
            'for log file
            'Dim uname As String =frmlogin.lbluname.text
            Dim uname As String = "cngirwa"
            Dim activity As String = uname + " added a new product with name " + pname
            Dim activitydate As String = lblTime.Text + " " + lblDate.Text
            Dim insertlog As String = "INSERT INTO logfiles VALUES('" + uname + "','" + activity + "','" + activitydate + "')"
            Dim insertlogcmd As New OleDbCommand
            With insertlogcmd
                .CommandText = insertlog
                .Connection = connect
                .ExecuteNonQuery()
            End With

            Dim insertqry As String = "INSERT INTO stock(pname,pcategory,pquantity,pbuying,pselling,pinfo,pdate) VALUES('" + pname + "','" + pcategory + "','" + pquantity + "','" + pbuying + "','" + pselling + "','" + pinfo + "','" + ptime + "')"
            Dim insertcmd As New OleDbCommand
            With insertcmd
                .CommandText = insertqry
                .Connection = connect
                .ExecuteNonQuery()
            End With
            MsgBox("New product added successfuly", MsgBoxStyle.Information, "Success")
            clearinputs_addstock()
            lbldateadded.Text = ""
        End If
    End Sub

    Private Sub btnClearaddstock_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClearaddstock.Click
        clearinputs_addstock()
    End Sub

    Private Sub clearinputs_addstock()
        txtnameaddstock.Clear()
        txtInfoaddstock.Clear()
        txtnameaddstock.Clear()
        txtquantityaddstock.Clear()
        txtsellpriceaddstock.Clear()
        txtbuyingpriceaddstock.Clear()
    End Sub
#End Region

#Region "VIEW STOCK"
    Private Sub btnViewstock_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewstock.Click
        enablebtns()
        btnViewstock.Enabled = False
        hidegb()
        gbViewstock.Dock = DockStyle.Fill
        gbViewstock.Show()
        viewstock()
        lblViewstoredelete.Text = ""
        lblViewstorename.Text = ""
    End Sub

    Private Sub btnViewstorerefresh_Click(sender As System.Object, e As System.EventArgs) Handles btnViewstorerefresh.Click
        viewstock()
    End Sub

    Private Sub viewstock()
        'for log file
        'Dim uname As String =frmlogin.lbluname.text
        Dim uname As String = "cngirwa"
        Dim activity As String = uname + " viewed stock "
        Dim activitydate As String = lblTime.Text + " " + lblDate.Text
        Dim insertlog As String = "INSERT INTO logfiles VALUES('" + uname + "','" + activity + "','" + activitydate + "')"
        Dim insertlogcmd As New OleDbCommand
        With insertlogcmd
            .CommandText = insertlog
            .Connection = connect
            .ExecuteNonQuery()
        End With

        Dim selectqry As String = "SELECT * FROM stock"
        Dim da As OleDbDataAdapter
        Dim dtset As DataSet
        Dim dttable As New DataTable

        da = New OleDbDataAdapter(selectqry, connect)
        dtset = New DataSet
        da.Fill(dtset, "stock")
        dttable = dtset.Tables("stock")

        dgvViewstore.DataSource = dttable
        dgvViewstore.ColumnHeadersDefaultCellStyle.Font = New Font(dgvViewstore.Font, FontStyle.Bold)
        dgvViewstore.Font = New Font(dgvViewstore.Font, FontStyle.Regular)
        dgvViewstore.ForeColor = Color.Black

        dgvViewstore.Columns("ID").HeaderText = "S/N"
        dgvViewstore.Columns("ID").Width = 35

        dgvViewstore.Columns("pname").HeaderText = "Product name"
        dgvViewstore.Columns("pname").Width = 160

        dgvViewstore.Columns("pcategory").HeaderText = "Category"
        dgvViewstore.Columns("pcategory").Width = 90

        dgvViewstore.Columns("pquantity").HeaderText = "Quantity"
        dgvViewstore.Columns("pquantity").Width = 70

        dgvViewstore.Columns("pbuying").HeaderText = "Buying Price"
        dgvViewstore.Columns("pbuying").Width = 70

        dgvViewstore.Columns("pselling").HeaderText = "Selling Price"
        dgvViewstore.Columns("pselling").Width = 70

        dgvViewstore.Columns("pinfo").HeaderText = "Description of Product"
        dgvViewstore.Columns("pinfo").Width = 370

        dgvViewstore.Columns("pdate").HeaderText = "Date"
        dgvViewstore.Columns("pdate").Width = 270

    End Sub

    Private Sub dgvViewstore_CellContentClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvViewstore.CellContentClick
        Dim s As System.Drawing.Point
        s = dgvViewstore.CurrentCellAddress
        Try
            Dim dbtime As String = dgvViewstore.Item("pdate", s.Y).Value.ToString
            lblViewstoredelete.Text = dbtime
            Dim dbname As String = dgvViewstore.Item("pname", s.Y).Value.ToString
            lblViewstorename.Text = dbname
        Catch ex As Exception
            MsgBox("Please select a product first", MessageBoxIcon.Warning, "Error")
        End Try
    End Sub

    Private Sub btnViewstoredelete_Click(sender As System.Object, e As System.EventArgs) Handles btnViewstoredelete.Click
        Dim pname As String = lblViewstorename.Text

        Dim confirmdel As String = "Are you sure you want to delete product named " & pname & "?"
        Dim deletemsg As String
        Dim ptime As String = lblViewstoredelete.Text

        If ptime = "" Then
            MsgBox("Please select a product first", MessageBoxIcon.Warning, "Error")
        Else

            'for log file
            'Dim uname As String =frmlogin.lbluname.text
            Dim uname As String = "cngirwa"
            Dim activity As String = uname + " deleted a product with name " + pname
            Dim activitydate As String = lblTime.Text + " " + lblDate.Text
            Dim insertlog As String = "INSERT INTO logfiles VALUES('" + uname + "','" + activity + "','" + activitydate + "')"
            Dim insertlogcmd As New OleDbCommand
            With insertlogcmd
                .CommandText = insertlog
                .Connection = connect
                .ExecuteNonQuery()
            End With

            Dim deleteqry As String = "DELETE * FROM stock WHERE pdate='" + ptime + "'"
            Dim deletecmd As New OleDbCommand
            deletemsg = MessageBox.Show(confirmdel, "Delete product named " & pname, MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
            If deletemsg = MsgBoxResult.No Then
                Me.Refresh()
            Else
                With deletecmd
                    .CommandText = deleteqry
                    .Connection = connect
                    .ExecuteNonQuery()
                End With
                MsgBox("Product deleted", MsgBoxStyle.Information, "Success")
                lblViewstoredelete.Text = "delete"
                viewstock()
            End If
        End If
    End Sub

    Private Sub btnViewstoreedit_Click(sender As System.Object, e As System.EventArgs) Handles btnViewstoreedit.Click
        Dim pdate As String = lblViewstoredelete.Text
        Dim selectqry As String = "SELECT * FROM stock WHERE pdate='" + pdate + "'"

        If pdate = "" Then
            MsgBox("Please select a product first", MsgBoxStyle.Information, "Error")
        Else
            Dim da As OleDbDataAdapter
            da = New OleDbDataAdapter(selectqry, connect)

            Dim dtset As DataSet
            dtset = New DataSet()
            da.Fill(dtset, "stock")

            Dim dttable As DataTable
            dttable = New DataTable()
            dttable = dtset.Tables("stock")

            Dim dbpname, dbpcategory, dbpquantity, dbpbuying, dbpselling, dbpinfo, dbpdate As String
            For Each temprow In dttable.Rows
                dbpname = temprow("pname").ToString
                dbpcategory = temprow("pcategory").ToString
                dbpquantity = temprow("pquantity").ToString
                dbpbuying = temprow("pbuying").ToString
                dbpselling = temprow("pselling").ToString
                dbpinfo = temprow("pinfo").ToString
                dbpdate = temprow("pdate").ToString
            Next

            txtnameaddstock.Text = dbpname
            cbcategoryaddstock.Text = dbpcategory
            txtquantityaddstock.Text = dbpquantity
            txtbuyingpriceaddstock.Text = dbpbuying
            txtsellpriceaddstock.Text = dbpselling
            txtInfoaddstock.Text = dbpinfo
            lbldateadded.Text = dbpdate

            enablebtns()
            btnAddstock.Enabled = False
            hidegb()
            gbAddstock.Dock = DockStyle.Fill
            gbAddstock.Show()
        End If
    End Sub

    Private Sub btnViewstoresearch_Click(sender As System.Object, e As System.EventArgs) Handles btnViewstoresearch.Click
        Dim search As String = txtViewstoresearch.Text.Trim
        If (search = "" Or IsNothing(search) = True) Then
            MsgBox("Please enter something to search", MsgBoxStyle.Information, "Error")
            txtViewstoresearch.Focus()
        Else
            txtViewstoresearch.Clear()
            Dim searchqry As String = "SELECT * FROM stock WHERE id LIKE '%" + search + "%' OR pname LIKE '%" + search + "%' OR pcategory LIKE '%" + search + "%' OR pquantity LIKE '%" + search + "%' OR pbuying LIKE '%" + search + "%' OR pselling LIKE '%" + search + "%' OR pinfo LIKE '%" + search + "%' OR pdate LIKE '%" + search + "%'"
            Dim da As OleDbDataAdapter
            Dim dtset As DataSet
            Dim dttable As New DataTable

            da = New OleDbDataAdapter(searchqry, connect)
            dtset = New DataSet
            da.Fill(dtset, "stock")
            dttable = dtset.Tables("stock")

            dgvViewstore.DataSource = dttable
            dgvViewstore.ColumnHeadersDefaultCellStyle.Font = New Font(dgvViewstore.Font, FontStyle.Bold)
            dgvViewstore.Font = New Font(dgvViewstore.Font, FontStyle.Regular)
            dgvViewstore.ForeColor = Color.Black

            dgvViewstore.Columns("ID").HeaderText = "S/N"
            dgvViewstore.Columns("ID").Width = 35

            dgvViewstore.Columns("pname").HeaderText = "Product name"
            dgvViewstore.Columns("pname").Width = 160

            dgvViewstore.Columns("pcategory").HeaderText = "Category"
            dgvViewstore.Columns("pcategory").Width = 90

            dgvViewstore.Columns("pquantity").HeaderText = "Quantity"
            dgvViewstore.Columns("pquantity").Width = 70

            dgvViewstore.Columns("pbuying").HeaderText = "Buying Price"
            dgvViewstore.Columns("pbuying").Width = 70

            dgvViewstore.Columns("pselling").HeaderText = "Selling Price"
            dgvViewstore.Columns("pselling").Width = 70

            dgvViewstore.Columns("pinfo").HeaderText = "Description of Product"
            dgvViewstore.Columns("pinfo").Width = 370

            dgvViewstore.Columns("pdate").HeaderText = "Date"
            dgvViewstore.Columns("pdate").Width = 270
        End If
    End Sub
#End Region

#Region "VIEW SALES"
    Private Sub btnViewsales_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewsales.Click
        enablebtns()
        btnViewsales.Enabled = False
        hidegb()
        gbViewsales.Dock = DockStyle.Fill
        gbViewsales.Show()
        viewsales()
    End Sub

    Private Sub btnViewsalesrefresh_Click(sender As System.Object, e As System.EventArgs) Handles btnViewsalesrefresh.Click
        viewsales()
    End Sub

    Private Sub viewsales()
        'for log file
        'Dim uname As String =frmlogin.lbluname.text
        Dim uname As String = "cngirwa"
        Dim activity As String = uname + " viewed sales"
        Dim activitydate As String = lblTime.Text + " " + lblDate.Text
        Dim insertlog As String = "INSERT INTO logfiles VALUES('" + uname + "','" + activity + "','" + activitydate + "')"
        Dim insertlogcmd As New OleDbCommand
        With insertlogcmd
            .CommandText = insertlog
            .Connection = connect
            .ExecuteNonQuery()
        End With

        Dim selectqry As String = "SELECT id,pname,pcategory,pquantitysold,pquantityremain,pselling,pprofit,pdate FROM sales"

        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(selectqry, connect)

        Dim dtset As DataSet
        dtset = New DataSet()
        da.Fill(dtset, "sales")

        Dim dttable As DataTable
        dttable = New DataTable()
        dttable = dtset.Tables("sales")

        dgvViewsales.DataSource = dttable
        dgvViewsales.ColumnHeadersDefaultCellStyle.Font = New Font(dgvViewsales.Font, FontStyle.Bold)
        dgvViewsales.Font = New Font(dgvViewsales.Font, FontStyle.Regular)
        dgvViewsales.ForeColor = Color.Black

        dgvViewsales.Columns("ID").HeaderText = "ID"
        dgvViewsales.Columns("ID").Width = 30

        dgvViewsales.Columns("pname").HeaderText = "Name"
        dgvViewsales.Columns("pname").Width = 183

        dgvViewsales.Columns("pcategory").HeaderText = "Category"
        dgvViewsales.Columns("pcategory").Width = 120

        dgvViewsales.Columns("pquantitysold").HeaderText = "Quantity sold"
        dgvViewsales.Columns("pquantitysold").Width = 120

        dgvViewsales.Columns("pquantityremain").HeaderText = "Quantity remain"
        dgvViewsales.Columns("pquantityremain").Width = 150

        dgvViewsales.Columns("pselling").HeaderText = "Sell Price"
        dgvViewsales.Columns("pselling").Width = 120

        dgvViewsales.Columns("pprofit").HeaderText = "Profit"
        dgvViewsales.Columns("pprofit").Width = 120

        dgvViewsales.Columns("pdate").HeaderText = "Date Sold"
        dgvViewsales.Columns("pdate").Width = 290
    End Sub

    Private Sub btnViewsalessearch_Click(sender As System.Object, e As System.EventArgs) Handles btnViewsalessearch.Click
        Dim search As String = txtViewsalessearch.Text.Trim
        If (search = "" Or IsNothing(search) = True) Then
            MsgBox("Please enter something to search", MsgBoxStyle.Information, "Error")
            txtViewsalessearch.Focus()
        Else
            txtViewsalessearch.Clear()
            Dim searchqry As String = "SELECT * FROM sales WHERE id LIKE '%" + search + "%' OR pname LIKE '%" + search + "%' OR pcategory LIKE '%" + search + "%' OR pquantitysold LIKE '%" + search + "%' OR pquantityremain LIKE '%" + search + "%' OR pselling LIKE '%" + search + "%' OR pprofit LIKE '%" + search + "%' OR pinfo LIKE '%" + search + "%' OR pdate LIKE '%" + search + "%'"
            Dim da As OleDbDataAdapter
            da = New OleDbDataAdapter(searchqry, connect)

            Dim dtset As DataSet
            dtset = New DataSet()
            da.Fill(dtset, "sales")

            Dim dttable As DataTable
            dttable = New DataTable()
            dttable = dtset.Tables("sales")

            dgvViewsales.DataSource = dttable
            dgvViewsales.ColumnHeadersDefaultCellStyle.Font = New Font(dgvViewsales.Font, FontStyle.Bold)
            dgvViewsales.Font = New Font(dgvViewsales.Font, FontStyle.Regular)
            dgvViewsales.ForeColor = Color.Black

            dgvViewsales.Columns("ID").HeaderText = "ID"
            dgvViewsales.Columns("ID").Width = 30

            dgvViewsales.Columns("pname").HeaderText = "Name"
            dgvViewsales.Columns("pname").Width = 183

            dgvViewsales.Columns("pcategory").HeaderText = "Category"
            dgvViewsales.Columns("pcategory").Width = 120

            dgvViewsales.Columns("pquantitysold").HeaderText = "Quantity sold"
            dgvViewsales.Columns("pquantitysold").Width = 120

            dgvViewsales.Columns("pquantityremain").HeaderText = "Quantity remain"
            dgvViewsales.Columns("pquantityremain").Width = 150

            dgvViewsales.Columns("pselling").HeaderText = "Sell Price"
            dgvViewsales.Columns("pselling").Width = 120

            dgvViewsales.Columns("pprofit").HeaderText = "Profit"
            dgvViewsales.Columns("pprofit").Width = 120

            dgvViewsales.Columns("pdate").HeaderText = "Date Sold"
            dgvViewsales.Columns("pdate").Width = 290
        End If
    End Sub
#End Region

#Region "REPORT"
    Private Sub btnReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReport.Click
        enablebtns()
        btnReport.Enabled = False
        hidegb()
        gbReport.Dock = DockStyle.Fill
        gbReport.Show()

        'for log file
        'Dim uname As String =frmlogin.lbluname.text
        Dim uname As String = "cngirwa"
        Dim activity As String = uname + " viewed report "
        Dim activitydate As String = lblTime.Text + " " + lblDate.Text
        Dim insertlog As String = "INSERT INTO logfiles VALUES('" + uname + "','" + activity + "','" + activitydate + "')"
        Dim insertlogcmd As New OleDbCommand
        With insertlogcmd
            .CommandText = insertlog
            .Connection = connect
            .ExecuteNonQuery()
        End With
    End Sub
#End Region

#Region "MANAGE USERS"
    Private Sub btnManageaccounts_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnManageaccounts.Click
        enablebtns()
        btnManageaccounts.Enabled = False
        hidegb()
        gbManageusers.Dock = DockStyle.Fill
        gbManageusers.Show()
        clearinputs_manageuser()
        viewusers()
    End Sub

    Private Sub btnManageuseradd_Click(sender As System.Object, e As System.EventArgs) Handles btnManageuseradd.Click
        Dim fname As String = txtManageuserfname.Text.Trim
        Dim mname As String = txtManageusermname.Text.Trim
        Dim lname As String = txtManageuserlname.Text.Trim
        Dim email As String = txtManageuseremail.Text.Trim
        Dim dob As String = dtpManageuserdob.Text.Trim
        Dim sex As String = cbManageusersex.Text.Trim
        Dim mobile As String = txtManageusermobile.Text.Trim
        Dim marital As String = cbManageusermarital.Text.Trim
        Dim role As String = cbManageuserRole.Text.Trim
        Dim uname As String = txtManageuseruname.Text.Trim
        Dim pass As String = txtManageuserpassword.Text.Trim
        Dim cpass As String = txtManageusercpassword.Text.Trim
        Dim regdate As String = lblTime.Text.Trim & " " & lblDate.Text.Trim
        Dim status As String = "ACTIVE"
        Dim access As String = "LOGOUT"

        Dim selecrtqry As String = "SELECT * FROM users"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(selecrtqry, connect)
        Dim dtset As DataSet
        dtset = New DataSet()
        da.Fill(dtset, "users")
        Dim dttable As DataTable
        dttable = New DataTable()
        dttable = dtset.Tables("users")
        Dim dbuname As String
        For Each temprow In dttable.Rows
            dbuname = temprow("uname").ToString
        Next

        Dim Ue As New UnicodeEncoding()
        Dim ByteSourceText() As Byte = Ue.GetBytes(pass)
        Dim Md5 As New MD5CryptoServiceProvider()
        Dim ByteHash() As Byte = Md5.ComputeHash(ByteSourceText)
        Convert.ToBase64String(ByteHash)
        Dim hashPwd As String
        hashPwd = Convert.ToBase64String(ByteHash)

        If (fname.Length < 4) Then
            MsgBox("First name can not have less than 4 characters", MsgBoxStyle.Information, "Error")
            txtManageuserfname.Focus()
        ElseIf fname.Contains(" ") Then
            MsgBox("Please enter first name correctly", MsgBoxStyle.Information, "Error")
            txtManageuserfname.Focus()
        ElseIf fname.Length > 25 Then
            MsgBox("First name can not have more than 25 characters", MsgBoxStyle.Information, "Error")
            txtManageuserfname.Focus()

        ElseIf (mname.Length < 4) Then
            MsgBox("Middle name can not have less than 4 characters", MsgBoxStyle.Information, "Error")
            txtManageusermname.Focus()
        ElseIf mname.Contains(" ") Then
            MsgBox("Please enter middle name correctly", MsgBoxStyle.Information, "Error")
            txtManageusermname.Focus()
        ElseIf fname.Length > 25 Then
            MsgBox("Middle name can not have more than 25 characters", MsgBoxStyle.Information, "Error")
            txtManageusermname.Focus()

        ElseIf (lname.Length < 4) Then
            MsgBox("Last name can not have less than 4 characters", MsgBoxStyle.Information, "Error")
            txtManageuserlname.Focus()
        ElseIf lname.Contains(" ") Then
            MsgBox("Please enter last name correctly", MsgBoxStyle.Information, "Error")
            txtManageuserlname.Focus()
        ElseIf lname.Length > 25 Then
            MsgBox("Last name can not have more than 25 characters", MsgBoxStyle.Information, "Error")
            txtManageuserlname.Focus()

        ElseIf (email.Length < 4) Then
            MsgBox("E-Mail address can not have less than 4 characters", MsgBoxStyle.Information, "Error")
            txtManageuseremail.Focus()
        ElseIf (email.Contains("@") = False) Or (email.Contains(".") = False) Then
            MsgBox("Please enter E-Mail address correctly", MsgBoxStyle.Information, "Error")
            txtManageuseremail.Focus()
        ElseIf email.Contains(" ") Then
            MsgBox("Please enter E-Mail address correctly", MsgBoxStyle.Information, "Error")
            txtManageuseremail.Focus()
        ElseIf email.Length > 50 Then
            MsgBox("E-Mail can not have more than 50 characters", MsgBoxStyle.Information, "Error")
            txtManageuseremail.Focus()

        ElseIf sex = "" Then
            MsgBox("Please select sex", MsgBoxStyle.Information, "Error")
            cbManageusersex.Focus()

        ElseIf (mobile.Length < 10) Then
            MsgBox("Mobile number can not have less than 10 digits", MsgBoxStyle.Information, "Error")
            txtManageusermobile.Focus()
        ElseIf (mobile.Length > 15) Then
            MsgBox("Mobile number can not have more than 15 digits", MsgBoxStyle.Information, "Error")
            txtManageusermobile.Focus()
        ElseIf (IsNumeric(mobile) = False) Then
            MsgBox("Only numbers are allowed", MsgBoxStyle.Information, "Error")
            txtManageusermobile.Focus()

        ElseIf marital = "" Then
            MsgBox("Please select marital status", MsgBoxStyle.Information, "Error")
            cbManageusermarital.Focus()

        ElseIf role = "" Then
            MsgBox("Please select a role", MsgBoxStyle.Information, "Error")
            cbManageuserRole.Focus()

        ElseIf (uname.Length < 4) Then
            MsgBox("Username can not have less than 4 characters", MsgBoxStyle.Information, "Error")
            txtManageuseruname.Focus()
        ElseIf uname.Contains(" ") Then
            MsgBox("Please enter username correctly", MsgBoxStyle.Information, "Error")
            txtManageuseruname.Focus()
        ElseIf uname.Length > 15 Then
            MsgBox("Username can not have more than 15 characters", MsgBoxStyle.Information, "Error")
            txtManageuseruname.Focus()

        ElseIf (pass.Length < 6) Then
            MsgBox("Password can not have less than 6 characters", MsgBoxStyle.Information, "Error")
            txtManageuserpassword.Focus()

        ElseIf (cpass.Length < 6) Then
            MsgBox("Password can not have less than 6 characters", MsgBoxStyle.Information, "Error")
            txtManageusercpassword.Focus()

        ElseIf (StrComp(pass, cpass) <> 0) Then
            MsgBox("Password did not match. Please enter password correctly", MsgBoxStyle.Information, "Error")
            txtManageuserpassword.Focus()

        ElseIf (dbuname = uname) Then
            Dim question As String
            question = MessageBox.Show("User exists. Do you wish to update user information?", "Stationary Information System", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
            If question = MsgBoxResult.No Then
                Me.Refresh()
                clearinputs_manageuser()
                viewusers()
            Else

                'for log file
                'Dim uname As String =frmlogin.lbluname.text
                Dim unamelog As String = "cngirwa"
                Dim activity As String = unamelog + " updated informations of user with username " + uname
                Dim activitydate As String = lblTime.Text + " " + lblDate.Text
                Dim insertlog As String = "INSERT INTO logfiles VALUES('" + unamelog + "','" + activity + "','" + activitydate + "')"
                Dim insertlogcmd As New OleDbCommand
                With insertlogcmd
                    .CommandText = insertlog
                    .Connection = connect
                    .ExecuteNonQuery()
                End With


                Dim updateqry As String = "UPDATE users SET fname='" + fname + "',mname='" + mname + "',lname='" + lname + "',email='" + email + "',dob='" + dob + "',sex='" + sex + "',mobile='" + mobile + "',marital='" + marital + "',role='" + role + "',uname='" + uname + "',upass='" + hashPwd + "',regdate='" + regdate + "',status='" + status + "',access='" + access + "' WHERE uname='" + uname + "'"
                Dim updatecmd As New OleDbCommand
                With updatecmd
                    .CommandText = updateqry
                    .Connection = connect
                    .ExecuteNonQuery()
                End With
                MsgBox("User informations updated successfuly", MsgBoxStyle.Information, "Success")
                clearinputs_manageuser()
                viewusers()
            End If

        Else
            'for log file
            'Dim uname As String =frmlogin.lbluname.text
            Dim unamelog As String = "cngirwa"
            Dim activity As String = unamelog + " added new user with username " + uname
            Dim activitydate As String = lblTime.Text + " " + lblDate.Text
            Dim insertlog As String = "INSERT INTO logfiles VALUES('" + unamelog + "','" + activity + "','" + activitydate + "')"
            Dim insertlogcmd As New OleDbCommand
            With insertlogcmd
                .CommandText = insertlog
                .Connection = connect
                .ExecuteNonQuery()
            End With

            Dim insertqry As String = "INSERT INTO users(fname,mname,lname,email,dob,sex,mobile,marital,role,uname,upass,regdate,status,access) VALUES('" + fname + "','" + mname + "','" + lname + "','" + email + "','" + dob + "','" + sex + "','" + mobile + "','" + marital + "','" + role + "','" + uname + "','" + hashPwd + "','" + regdate + "','" + status + "','" + access + "')"
            Dim insertcmd As New OleDbCommand
            With insertcmd
                .CommandText = insertqry
                .Connection = connect
                .ExecuteNonQuery()
            End With
            MsgBox("New user added successfuly", MsgBoxStyle.Information, "Success")
            clearinputs_manageuser()
            viewusers()
        End If
    End Sub

    Private Sub btnManageuserclear_Click(sender As System.Object, e As System.EventArgs) Handles btnManageuserclear.Click
        clearinputs_manageuser()
    End Sub

    Private Sub clearinputs_manageuser()
        txtManageuserfname.Clear()
        txtManageusermname.Clear()
        txtManageuserlname.Clear()
        txtManageuseremail.Clear()
        txtManageusermobile.Clear()
        txtManageuseruname.Clear()
        txtManageuserpassword.Clear()
        txtManageusercpassword.Clear()
        lbldeleteuname.Text = ""
    End Sub

    Private Sub btnManageuserDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnManageuserDelete.Click
        Dim uname As String = lbldeleteuname.Text
        Dim deleteqry As String = "DELETE * FROM users WHERE uname='" + uname + "'"
        Dim deletecmd As New OleDbCommand
        Dim confirm As String

        If uname = frmLogin.lbluname.Text Then
            MsgBox("You can not delete you own account", MsgBoxStyle.Critical, "Error")
            Exit Sub
        Else
        End If

        confirm = MessageBox.Show("Are you sure you want to delete user " & uname & "?", "Delete username " & uname, MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
        If confirm = MsgBoxResult.No Then
            Me.Refresh()
        Else
            'for log file
            Dim uname1 As String = frmLogin.lbluname.Text
            Dim unamelog As String = uname1
            Dim activity As String = unamelog + " deleted a user with username " + uname1
            Dim activitydate As String = lblTime.Text + " " + lblDate.Text
            Dim insertlog As String = "INSERT INTO logfiles VALUES('" + unamelog + "','" + activity + "','" + activitydate + "')"
            Dim insertlogcmd As New OleDbCommand
            With insertlogcmd
                .CommandText = insertlog
                .Connection = connect
                .ExecuteNonQuery()
            End With

            With deletecmd
                .CommandText = deleteqry
                .Connection = connect
                .ExecuteNonQuery()
            End With
            MsgBox("User deleted successfuly", MsgBoxStyle.Information, "Success")
            viewusers()
            clearinputs_manageuser()
        End If
    End Sub

    Private Sub viewusers()
        Dim selectqry As String = "SELECT id,fname,lname,sex,mobile,role,uname,status FROM users"

        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(selectqry, connect)

        Dim dtset As DataSet
        dtset = New DataSet()
        da.Fill(dtset, "users")

        Dim dttable As DataTable
        dttable = New DataTable
        dttable = dtset.Tables("users")

        dgvManageruser.DataSource = dttable
        dgvManageruser.ColumnHeadersDefaultCellStyle.Font = New Font(dgvManageruser.Font, FontStyle.Bold)
        dgvManageruser.Font = New Font(dgvManageruser.Font, FontStyle.Regular)
        dgvManageruser.ForeColor = Color.Black

        dgvManageruser.Columns("ID").HeaderText = "ID"
        dgvManageruser.Columns("ID").Width = 30

        dgvManageruser.Columns("fname").HeaderText = "First Name"
        dgvManageruser.Columns("fname").Width = 150

        dgvManageruser.Columns("lname").HeaderText = "Last Name"
        dgvManageruser.Columns("lname").Width = 150

        dgvManageruser.Columns("sex").HeaderText = "Sex"
        dgvManageruser.Columns("sex").Width = 60

        dgvManageruser.Columns("mobile").HeaderText = "Mobile"
        dgvManageruser.Columns("mobile").Width = 100

        dgvManageruser.Columns("role").HeaderText = "Role"
        dgvManageruser.Columns("role").Width = 90

        dgvManageruser.Columns("uname").HeaderText = "Username"
        dgvManageruser.Columns("uname").Width = 100

        dgvManageruser.Columns("status").HeaderText = "Status"
        dgvManageruser.Columns("status").Width = 110

    End Sub

    Private Sub btnManageusersearch_Click(sender As System.Object, e As System.EventArgs) Handles btnManageusersearch.Click
        Dim search As String = txtManageusersearch.Text.Trim
        If (search = "" Or IsNothing(search) = True) Then
            MsgBox("Please enter something to search", MsgBoxStyle.Information, "Error")
            txtManageusersearch.Focus()
        Else
            txtManageusersearch.Clear()
            Dim searchqry As String = "SELECT id,fname,lname,sex,mobile,role,uname,status FROM users WHERE id LIKE '%" + search + "%' OR fname LIKE '%" + search + "%' OR lname LIKE '%" + search + "%' OR sex LIKE '%" + search + "%' OR mobile LIKE '%" + search + "%' OR role LIKE '%" + search + "%' OR uname LIKE '%" + search + "%' OR status LIKE '%" + search + "%'"

            Dim da As OleDbDataAdapter
            da = New OleDbDataAdapter(searchqry, connect)

            Dim dtset As DataSet
            dtset = New DataSet()
            da.Fill(dtset, "users")

            Dim dttable As DataTable
            dttable = New DataTable
            dttable = dtset.Tables("users")

            dgvManageruser.DataSource = dttable
            dgvManageruser.ColumnHeadersDefaultCellStyle.Font = New Font(dgvManageruser.Font, FontStyle.Bold)
            dgvManageruser.Font = New Font(dgvManageruser.Font, FontStyle.Regular)
            dgvManageruser.ForeColor = Color.Black

            dgvManageruser.Columns("ID").HeaderText = "ID"
            dgvManageruser.Columns("ID").Width = 30

            dgvManageruser.Columns("fname").HeaderText = "First Name"
            dgvManageruser.Columns("fname").Width = 150

            dgvManageruser.Columns("lname").HeaderText = "Last Name"
            dgvManageruser.Columns("lname").Width = 150

            dgvManageruser.Columns("sex").HeaderText = "Sex"
            dgvManageruser.Columns("sex").Width = 60

            dgvManageruser.Columns("mobile").HeaderText = "Mobile"
            dgvManageruser.Columns("mobile").Width = 100

            dgvManageruser.Columns("role").HeaderText = "Role"
            dgvManageruser.Columns("role").Width = 90

            dgvManageruser.Columns("uname").HeaderText = "Username"
            dgvManageruser.Columns("uname").Width = 100

            dgvManageruser.Columns("status").HeaderText = "Status"
            dgvManageruser.Columns("status").Width = 110
        End If
    End Sub

    Private Sub btnManageuserRefresh_Click(sender As System.Object, e As System.EventArgs) Handles btnManageuserRefresh.Click
        viewusers()
        clearinputs_manageuser()
    End Sub

    Private Sub dgvManageruser_CellContentClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvManageruser.CellContentClick
        Dim s As System.Drawing.Point
        s = dgvManageruser.CurrentCellAddress
        Try
            Dim dbid As String = dgvManageruser.Item("uname", s.Y).Value.ToString
            lbldeleteuname.Text = dbid
        Catch ex As Exception
            MsgBox("Please select a user to delete", MessageBoxIcon.Warning, "Error")
        End Try
        
    End Sub

    Private Sub btnManageuserEdit_Click(sender As System.Object, e As System.EventArgs) Handles btnManageuserEdit.Click

        Dim uname As String = lbldeleteuname.Text
        Dim selectqry As String = "SELECT * FROM users WHERE uname= '" + uname + "'"

        If (lbldeleteuname.Text = "") Or (IsNothing(lbldeleteuname.Text) = True) Then
            MsgBox("Select a user to edit", MsgBoxStyle.Information, "Error")

        Else
            Dim da As OleDbDataAdapter
            da = New OleDbDataAdapter(selectqry, connect)

            Dim dtset As DataSet
            dtset = New DataSet()
            da.Fill(dtset, "users")

            Dim dttable As DataTable
            dttable = New DataTable
            dttable = dtset.Tables("users")

            Dim dbfname, dbmname, dblname, dbemail, dbdob, dbgender, dbmobile, dbmarital, dbrole, dbuname As String
            For Each temprow In dttable.Rows
                dbfname = temprow("fname").ToString
                dbmname = temprow("mname").ToString
                dblname = temprow("lname").ToString
                dbemail = temprow("email").ToString
                dbdob = temprow("dob").ToString
                dbgender = temprow("sex").ToString
                dbmobile = temprow("mobile").ToString
                dbmarital = temprow("marital").ToString
                dbrole = temprow("role").ToString
                dbuname = temprow("uname").ToString
            Next

            txtManageuserfname.Text = dbfname
            txtManageusermname.Text = dbmname
            txtManageuserlname.Text = dblname
            txtManageuseremail.Text = dbemail
            dtpManageuserdob.Text = dbdob
            cbManageusersex.Text = dbgender
            txtManageusermobile.Text = dbmobile
            cbManageusermarital.Text = dbmarital
            cbManageuserRole.Text = dbrole
            txtManageuseruname.Text = dbuname

        End If
    End Sub

    Private Sub txtManageusermobile_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtManageusermobile.TextChanged
        If txtManageusermobile.Text = "" Then
            txtManageusermobile.Clear()
            Exit Sub
        ElseIf IsNumeric(txtManageusermobile.Text) = False Then
            MsgBox("Only numbers are allowed", MessageBoxIcon.Warning, "Error")
            txtManageusermobile.Clear()
            Exit Sub
        End If
    End Sub
#End Region

#Region "VIEW LOGS"
    Private Sub btnLog_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLog.Click
        enablebtns()
        btnLog.Enabled = False
        hidegb()
        gbActivitylog.Dock = DockStyle.Fill
        gbActivitylog.Show()
        viewlog()
    End Sub

    Private Sub viewlog()
        'for log file
        'Dim uname As String =frmlogin.lbluname.text
        Dim unamelog As String = "cngirwa"
        Dim activity As String = unamelog + " viewed activity log file"
        Dim activitydate As String = lblTime.Text + " " + lblDate.Text
        Dim insertlog As String = "INSERT INTO logfiles VALUES('" + unamelog + "','" + activity + "','" + activitydate + "')"
        Dim insertlogcmd As New OleDbCommand
        With insertlogcmd
            .CommandText = insertlog
            .Connection = connect
            .ExecuteNonQuery()
        End With

        Dim selectqry As String = "SELECT * FROM logfiles"

        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(selectqry, connect)

        Dim dtset As DataSet
        dtset = New DataSet()
        da.Fill(dtset, "logfiles")

        Dim dttable As DataTable
        dttable = New DataTable()
        dttable = dtset.Tables("logfiles")

        dgvActivitylog.DataSource = dttable
        dgvActivitylog.ColumnHeadersDefaultCellStyle.Font = New Font(dgvActivitylog.Font, FontStyle.Bold)
        dgvActivitylog.Font = New Font(dgvActivitylog.Font, FontStyle.Regular)
        dgvActivitylog.ForeColor = Color.Black

        dgvActivitylog.Columns("uname").HeaderText = "Username"
        dgvActivitylog.Columns("uname").Width = 120

        dgvActivitylog.Columns("activity").HeaderText = "Activities done by user"
        dgvActivitylog.Columns("activity").Width = 700

        dgvActivitylog.Columns("activitydate").HeaderText = "Time and date the activity was done"
        dgvActivitylog.Columns("activitydate").Width = 310

    End Sub


    Private Sub btnActivitysearch_Click(sender As System.Object, e As System.EventArgs) Handles btnActivitysearch.Click
        Dim search As String = txtActivitysearch.Text.Trim
        If (search = "" Or IsNothing(search) = True) Then
            MsgBox("Please enter something to search", MsgBoxStyle.Information, "Error")
            txtActivitysearch.Focus()
        Else
            txtActivitysearch.Clear()
            Dim searchqry As String = "SELECT * FROM logfiles WHERE uname LIKE '%" + search + "%' OR activity LIKE '%" + search + "%' OR activitydate LIKE '%" + search + "%'"
            Dim da As OleDbDataAdapter
            da = New OleDbDataAdapter(searchqry, connect)

            Dim dtset As DataSet
            dtset = New DataSet()
            da.Fill(dtset, "logfiles")

            Dim dttable As DataTable
            dttable = New DataTable()
            dttable = dtset.Tables("logfiles")

            dgvActivitylog.DataSource = dttable
            dgvActivitylog.ColumnHeadersDefaultCellStyle.Font = New Font(dgvActivitylog.Font, FontStyle.Bold)
            dgvActivitylog.Font = New Font(dgvActivitylog.Font, FontStyle.Regular)
            dgvActivitylog.ForeColor = Color.Black

            dgvActivitylog.Columns("uname").HeaderText = "Username"
            dgvActivitylog.Columns("uname").Width = 120

            dgvActivitylog.Columns("activity").HeaderText = "Activities done by user"
            dgvActivitylog.Columns("activity").Width = 700

            dgvActivitylog.Columns("activitydate").HeaderText = "Time and date the activity was done"
            dgvActivitylog.Columns("activitydate").Width = 310
        End If
    End Sub
#End Region

#Region "SETTINGS"
    Private Sub btnSettings_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSettings.Click
        enablebtns()
        btnSettings.Enabled = False
        hidegb()
        gbSetting.Dock = DockStyle.Fill
        gbSetting.Show()
        clearinputs_settings()
    End Sub

    Private Sub btnCheckupdates_Click(sender As System.Object, e As System.EventArgs) Handles btnCheckupdates.Click
        lblUpdates.Show()
        pbUpdates.Show()
        Me.Enabled = False

        Dim response As System.Net.HttpWebResponse
        Try
            Dim request As System.Net.HttpWebRequest = System.Net.HttpWebRequest.Create("https://googledrive.com/host/0BzlScedc3p2fUEQ0MjJFa1cxUWM/Tool Update.txt")
            response = request.GetResponse()
        Catch ex As Exception
          
            MsgBox("Error occured. Please try again", MessageBoxIcon.Warning, "Error")
            lblUpdates.Hide()
            pbUpdates.Hide()
            Me.Enabled = True
            Exit Sub
        End Try
        Dim sr As StreamReader = New StreamReader(response.GetResponseStream())

        Dim newsoftversion As String = sr.ReadToEnd()
        Dim currentsoftversion As String = Application.ProductVersion

        lblUpdates.Hide()
        pbUpdates.Hide()
        If newsoftversion.Contains(currentsoftversion) Then
            MsgBox("You have updated software version", MsgBoxStyle.Information, "Update")
            Me.Enabled = True
        Else
            Dim updatesoft As String
            updatesoft = MessageBox.Show("There is a new software version. Do you want to update now?", "Update", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
            If updatesoft = MsgBoxResult.Yes Then
                System.Diagnostics.Process.Start("https://googledrive.com/host/0BzlScedc3p2fUEQ0MjJFa1cxUWM/Stationary Information System.exe")
                Me.Enabled = True
            Else
                MsgBox("Update cancelled", MsgBoxStyle.Information, "Cancel")
                Me.Enabled = True
            End If

        End If

    End Sub

    Private Sub clearinputs_settings()
        txtMailTo.Clear()
        lblMailattach.Text = "No attachment selected"
    End Sub

    Private Sub btnMailsend_Click(sender As System.Object, e As System.EventArgs) Handles btnMailsend.Click
        Dim attachlocation As String = lblMailattach.Text
        If StrComp(attachlocation, "No attachment selected") <> 0 Then
            Dim uname As String = "sis.manager.backup@gmail.com"
            Dim pass As String = "sis.manager"
            Dim receiver As String = txtMailTo.Text.Trim
            Dim subject As String = "Backup File"
            Dim body As String = "Please find an attachment on this E-Mail. It contains your backup file which you created on " + lblTime.Text + " : " + lblDate.Text
            Dim attach As New Attachment(attachlocation)
            Dim mail As New MailMessage()


            If (StrComp(receiver, "") <> 0) And (attachlocation <> "No attachment selected") Then
                Try
                    mail.From = New MailAddress(uname)
                    mail.To.Add(receiver)
                    mail.Subject = subject
                    mail.Body = body
                    mail.Attachments.Add(attach)

                    Dim smtpsettings As New SmtpClient("smtp.gmail.com")
                    smtpsettings.Port = 587
                    smtpsettings.Credentials = New System.Net.NetworkCredential(uname, pass)
                    smtpsettings.EnableSsl = True
                    Try
                        Me.Enabled = False
                        Me.WindowState = FormWindowState.Normal
                        smtpsettings.Send(mail)
                        MsgBox("A backup copy is sent successfuly", MsgBoxStyle.Information, "Success")
                        clearinputs_settings()
                        Me.Enabled = True
                    Catch ex As Exception
                        MsgBox(ex.Message, MessageBoxIcon.Error, "Error")
                        Me.Enabled = True
                    End Try
                Catch ex As Exception
                    MsgBox(ex.Message, MessageBoxIcon.Error, "Error")
                    Me.Enabled = True
                End Try
            Else
                MsgBox("Error occured. Please fill all fields correctly", MessageBoxIcon.Warning, "Error")
            End If
        Else
            MsgBox("Error occured. Please make backup first", MessageBoxIcon.Warning, "Error")
            clearinputs_settings()
        End If
    End Sub

    Private Sub btnRestorebackup_Click(sender As System.Object, e As System.EventArgs) Handles btnRestorebackup.Click
        Me.Enabled = False
        MsgBox("Please choose the backup database which you have downloaded from your e-mail", MsgBoxStyle.Information, "Stationary Information System")
        With ofdRestore
            .Filter = "Database file (*.accdb)|*.accdb"
            .Title = "Select Database file"
            .FileName = ""
        End With
        If Me.ofdRestore.ShowDialog = DialogResult.Cancel Then
            Me.Enabled = True
            Exit Sub
        End If
    End Sub

    Private Sub ofdRestore_FileOk(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles ofdRestore.FileOk
        Dim backuplocation As String
        backuplocation = Path.GetFullPath(ofdRestore.FileName)

        Dim currentDir As String = My.Computer.FileSystem.CurrentDirectory.ToString()
        Directory.SetCurrentDirectory(currentDir)
        Directory.CreateDirectory(currentDir & "\error db\")
        Dim olddb As String = currentDir & "\error db\"

        Try
            If File.Exists("sis.accdb") Then
                closeconnection()
                File.Copy(currentDir & "\sis.accdb", olddb & "sis.accdb", True)
                File.Delete(currentDir & "\sis.accdb")
                File.Copy(backuplocation, currentDir & "\sis.accdb", True)
                MsgBox("Restore succefully!123312", MsgBoxStyle.Information, "Success")
                MsgBox("S.I.S will now shutdown in order for changes to take effects", MessageBoxIcon.Warning, "Warning")
                Me.Dispose()
                Me.Close()

            Else
                closeconnection()
                File.Copy(currentDir & "\sis.accdb", olddb)
                File.Copy(backuplocation, currentDir)
                MsgBox("Restore succefully!", MsgBoxStyle.Information, "Success")
                MsgBox("S.I.S will now shutdown in order for changes to take effects", MessageBoxIcon.Warning, "Warning")
                Me.Dispose()
                Me.Close()
            End If
        Catch ex As Exception
            MsgBox("Error occured. Please try again", MsgBoxStyle.Information, "Error")
        End Try

        Me.Enabled = True
    End Sub

    Private Sub btnSavebackup_Click(sender As System.Object, e As System.EventArgs) Handles btnSavebackup.Click
        Me.Enabled = False
        MsgBox("Please choose location to save a backup database, then send it to your e-mail", MsgBoxStyle.Information, "Stationary Information System")
        With sfdBackup
            .Filter = "Database file (*.accdb)|*.accdb"
            .Title = "Choose location"
            .FileName = ""
        End With
        If Me.sfdBackup.ShowDialog = DialogResult.Cancel Then
            Me.Enabled = True
            Exit Sub
        End If
    End Sub

    Private Sub sfdBackup_FileOk(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles sfdBackup.FileOk
        Dim backuplocation As String
        backuplocation = Path.GetFullPath(sfdBackup.FileName)
        lblMailattach.Text = backuplocation

        Dim currentDir As String = My.Computer.FileSystem.CurrentDirectory.ToString()
        Directory.SetCurrentDirectory(currentDir)
        Try
            If File.Exists("sis.accdb") Then
                closeconnection()
                File.Copy(currentDir & "\sis.accdb", backuplocation, True)
                MsgBox("Backup succefully!123312", MsgBoxStyle.Information, "Success")
                MsgBox("Please send the backup file to your e-mail", MessageBoxIcon.Warning, "Warning")

            Else
                MsgBox("Error occured. Please try again", MsgBoxStyle.Information, "Error")
            End If
        Catch ex As Exception
            MsgBox("Error occured. Please try again", MsgBoxStyle.Information, "Error")
        End Try

        Me.Enabled = True
    End Sub
#End Region

#Region "HELP"
    Private Sub btnHelp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHelp.Click
        enablebtns()
        btnHelp.Enabled = False
        hidegb()
        gbHelp.Dock = DockStyle.Fill
        gbHelp.Show()
    End Sub
#End Region

#Region "LOGOUT"
    Private Sub btnLogout_Click(sender As System.Object, e As System.EventArgs) Handles btnLogout.Click
        Dim logout As String
        logout = MessageBox.Show("Are you sure you want to logout?", "Stationary Information System", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
        If logout = MsgBoxResult.No Then
            Me.Refresh()
            enablebtns()
            btnHome.Enabled = False
            hidegb()
            gbHome.Dock = DockStyle.Fill
            gbHome.Show()
            clearinputs_home()
        Else
            'for log file
            Dim uname As String = frmLogin.lbluname.Text
            Dim unamelog As String = uname
            Dim activity As String = unamelog + " logged out "
            Dim activitydate As String = lblTime.Text + " " + lblDate.Text
            Dim insertlog As String = "INSERT INTO logfiles VALUES('" + unamelog + "','" + activity + "','" + activitydate + "')"
            Dim insertlogcmd As New OleDbCommand
            With insertlogcmd
                .CommandText = insertlog
                .Connection = connect
                .ExecuteNonQuery()
            End With

            Dim updateqry As String = "UPDATE users SET access='LOGOUT' WHERE uname='" + uname + "'"
            Dim updatecmd As New OleDbCommand
            With updatecmd
                .CommandText = updateqry
                .Connection = connect
                .ExecuteNonQuery()
            End With

            frmLogin.Show()
            Me.Dispose()
            closeconnection()
        End If
    End Sub
#End Region

#End Region


#Region "Form actions"
    Private Sub frmAdminENG_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        openconnection()
        Dim close As String
        close = MessageBox.Show("Are you sure you want to close?", "Stationary Information System", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
        If close = MsgBoxResult.No Then
            Me.Refresh()
            e.Cancel = True
        Else
            'for log file
            Dim uname As String = frmLogin.lbluname.Text
            Dim unamelog As String = uname
            Dim activity As String = unamelog + " logged out "
            Dim activitydate As String = lblTime.Text + " " + lblDate.Text
            Dim insertlog As String = "INSERT INTO logfiles VALUES('" + unamelog + "','" + activity + "','" + activitydate + "')"
            Dim insertlogcmd As New OleDbCommand
            With insertlogcmd
                .CommandText = insertlog
                .Connection = connect
                .ExecuteNonQuery()
            End With

            Dim updateqry As String = "UPDATE users SET access='LOGOUT' WHERE uname='" + uname + "'"
            Dim updatecmd As New OleDbCommand
            With updatecmd
                .CommandText = updateqry
                .Connection = connect
                .ExecuteNonQuery()
            End With

            frmLogin.Show()
            Me.Dispose()
            closeconnection()

        End If
    End Sub

    Private Sub frmAdminENG_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TimerDate.Start()
        hidegb()
        gbHome.Dock = DockStyle.Fill
        gbHome.Show()
        enablebtns()
        btnHome.Enabled = False
        openconnection()
        viewnews()
    End Sub

#End Region

#Region "Time, Date and Zone"
    Private Sub TimerDate_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TimerDate.Tick
        lblTime.Text = TimeOfDay
        lblDate.Text = Today.Date.ToString("dddd, dd MMMM yyyy")
        lblZone.Text = TimeZone.CurrentTimeZone.StandardName
    End Sub
#End Region

#Region "Hide all gb"
    Private Sub hidegb()
        gbSetting.Hide()
        gbActivitylog.Hide()
        gbAddstock.Hide()
        gbHelp.Hide()
        gbHome.Hide()
        gbManageusers.Hide()
        gbReport.Hide()
        gbViewsales.Hide()
        gbViewstock.Hide()
    End Sub
#End Region

#Region "Enable all Buttons"
    Private Sub enablebtns()
        btnHome.Enabled = True
        btnAddstock.Enabled = True
        btnViewstock.Enabled = True
        btnViewsales.Enabled = True
        btnReport.Enabled = True
        btnManageaccounts.Enabled = True
        btnLog.Enabled = True
        btnSettings.Enabled = True
        btnHelp.Enabled = True
        btnLog.Enabled = True
    End Sub
#End Region

#Region "notification icon"
    Private Sub frmAdminENG_Move(sender As Object, e As System.EventArgs) Handles Me.Move
        If Me.WindowState = FormWindowState.Minimized Then
            Me.Hide()
            niSIS.ShowBalloonTip(4600, "Stationary Information System", "Stationary Information System have been minimized. Click this icon to maximise it", ToolTipIcon.Info)
        End If
    End Sub

    Private Sub niSIS_MouseDoubleClick(sender As System.Object, e As System.Windows.Forms.MouseEventArgs) Handles niSIS.MouseDoubleClick
        Me.Show()
        Me.WindowState = FormWindowState.Normal
    End Sub
#End Region

End Class
