Imports System.Data.OleDb
Imports System.Text.RegularExpressions 'needed to verify username of newly added user

Public Class users
    Const connStr As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\#Record System\RecordSystemDatabase.accdb"

    Private Sub userDataGrid_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Text = "EPRS - Users"
        Me.CenterToScreen()
        Me.FormBorderStyle = FormBorderStyle.Fixed3D
        Me.MaximizeBox = False
        timer.Interval = 100
        timer.Enabled = True
        lblTime.TextAlign = ContentAlignment.MiddleLeft
        txtAdminPword.UseSystemPasswordChar = True
        txtAdminPword.Hide()
        txtNewUserFname.Hide()
        txtNewUserSname.Hide()
        lblEnterPword.Hide()
        lblNewUserSname.Hide()
        lblAndForename.Hide()
        lblSure.Hide()
        btnOKpword.Hide()
        btnAdd.Hide()
        btnExitAddUser.Hide()
        btnSureDel.Hide()
        btnNoDel.Hide()
        comboBoxUpdateInfo.Text = "Username"
        dataGrid.RowHeadersVisible = False
        getData()
    End Sub

    Private Sub btnAddNewUser_Click(sender As Object, e As EventArgs) Handles btnAddNewUser.Click
        txtAdminPword.Show()
        lblEnterPword.Show()
        btnOKpword.Show()
        btnExitAddUser.Show()
        btnAddNewUser.Enabled = False
    End Sub
    Private Sub btnExitAddUser_Click(sender As Object, e As EventArgs) Handles btnExitAddUser.Click
        txtAdminPword.Clear()
        txtNewUserFname.Clear()
        txtNewUserSname.Clear()
        txtAdminPword.Hide()
        txtNewUserFname.Hide()
        txtNewUserSname.Hide()
        lblEnterPword.Hide()
        lblNewUserSname.Hide()
        lblAndForename.Hide()
        btnOKpword.Hide()
        btnAdd.Hide()
        btnExitAddUser.Hide()
        btnAddNewUser.Enabled = True
    End Sub
    Private Sub btnOKpword_Click(sender As Object, e As EventArgs) Handles btnOKpword.Click
        If txtAdminPword.Text = "" Then
            MsgBox("Please enter your password.")
        ElseIf login.validCredentials("admin", txtAdminPword.Text) = True Then
            txtNewUserFname.Show()
            txtNewUserSname.Show()
            lblNewUserSname.Show()
            lblAndForename.Show()
            btnAdd.Show()
            txtAdminPword.Hide()
            btnOKpword.Hide()
        Else
            txtAdminPword.Clear()
            MsgBox("Invalid password")
        End If
    End Sub
    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click 'uses Regex to validate new user credentials
        Dim regexName As New Regex("^[a-z]+$")
        If txtNewUserSname.Text = "" Then
            MsgBox("Please enter the user's surname")
        ElseIf txtNewUserFname.Text = "" Then
            MsgBox("Please enter the user's forename")
        ElseIf regexName.IsMatch(LCase(txtNewUserSname.Text)) = False Then
            txtNewUserSname.Clear()
            MsgBox("Names must be letters only")
        ElseIf regexName.IsMatch(LCase(txtNewUserFname.Text)) = False Then
            txtNewUserFname.Clear()
            MsgBox("Names must be letters only")
        Else
            'capitalises first letter of forename
            Dim fName As String = UCase(Mid(txtNewUserFname.Text, 1, 1)) & Mid(txtNewUserFname.Text, 2, Len(txtNewUserFname.Text))
            Dim newUsername As String = LCase(Mid(fName, 1, 1)) & LCase(txtNewUserSname.Text)
            Dim newPword As String = login.makeRandPword()

            If Len(newUsername) > 100 Then
                txtNewUserFname.Clear()
                txtNewUserSname.Clear()
                MsgBox("That name is too long.")
            Else
                Dim conn As New OleDbConnection(connStr)
                Dim cmmdInsert As New OleDbCommand
                cmmdInsert.Connection = conn
                cmmdInsert.CommandText = "INSERT INTO tblUsers (Username, Pword) " &
                        "VALUES ('" & newUsername & "', '" & newPword & "')"
                conn.Open()
                cmmdInsert.ExecuteNonQuery()
                conn.Close()
                getData()
                txtAdminPword.Clear()
                txtNewUserFname.Clear()
                txtNewUserSname.Clear()
                txtAdminPword.Hide()
                txtNewUserFname.Hide()
                txtNewUserSname.Hide()
                lblEnterPword.Hide()
                lblNewUserSname.Hide()
                lblAndForename.Hide()
                btnOKpword.Hide()
                btnAdd.Hide()
                btnExitAddUser.Hide()
                btnAdd.Enabled = True
                btnUpdate.Enabled = True
                btnDel.Enabled = True
                btnAddNewUser.Enabled = True
                MsgBox(fName & " has been added." & vbNewLine &
                    "Username = " & newUsername & vbNewLine &
                    "First password = " & newPword & " (they can change it when they log in).")
            End If
        End If
    End Sub
    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim userID As Integer = dataGrid.Rows.Item(dataGrid.CurrentRow.Index).Cells(0).Value
        Dim dataGridUsername As String = dataGrid.Rows.Item(dataGrid.CurrentRow.Index).Cells(1).Value
        Dim dataGridPword As String = dataGrid.Rows.Item(dataGrid.CurrentRow.Index).Cells(2).Value
        Dim regexLetters As New Regex("^[a-z]+$")
        Dim SQLfield As String = getDataToUpdate()
        Dim cmmdUpdateStr As String
        Dim erroneous As Boolean = False

        'validating all possible entries for user data updates
        If txtNewData.Text = "" Then
            erroneous = True
            MsgBox("Please enter the new data")
        ElseIf dataGridUsername = "admin" Then
            erroneous = True
            txtNewData.Clear()
            MsgBox("Can't update admin info here")
        Else
            If SQLfield = "Username" Then
                If regexLetters.IsMatch(LCase(txtNewData.Text)) = False Then
                    txtNewData.Clear()
                    erroneous = True
                    MsgBox("Username must be letters only.")
                Else
                    cmmdUpdateStr = "UPDATE tblUsers " &
                        "SET Username = '" & LCase(txtNewData.Text) & "' " &
                        "WHERE UserID = " & userID
                End If
            ElseIf SQLfield = "Pword" Then
                Dim regLcase As New Regex("[a-z]+")
                Dim regUcase As New Regex("[A-Z]+")
                Dim regNums As New Regex("[0-9]+")
                Dim regLen As New Regex("^[a-zA-Z0-9]{6,100}$")

                If regLcase.IsMatch(txtNewData.Text) = False Or regUcase.IsMatch(txtNewData.Text) = False Or regNums.IsMatch(txtNewData.Text) = False Or regLen.IsMatch(txtNewData.Text) = False Then
                    txtNewData.Clear()
                    erroneous = True
                    MsgBox("Password must:" & vbNewLine &
                       " - Be 6-100 characters long" & vbNewLine &
                       " - Contain at least 1 lower case AND upper case letter" & vbNewLine &
                       " - And at least 1 number" & vbNewLine &
                       " - And no other characters.")
                Else
                    cmmdUpdateStr = "UPDATE tblUsers SET Pword = '" & txtNewData.Text & "' WHERE UserID = " & userID
                End If
            End If
        End If

        If erroneous = False Then
            Dim conn As New OleDbConnection(connStr)
            Dim cmmdUpdate As New OleDbCommand
            cmmdUpdate.Connection = conn
            cmmdUpdate.CommandText = cmmdUpdateStr
            conn.Open()
            cmmdUpdate.ExecuteNonQuery()
            conn.Close()
            txtNewData.Clear()
            getData()
            MsgBox(comboBoxUpdateInfo.Text & " of '" & dataGridUsername & "' updated.")
        End If
    End Sub
    Private Sub btnDelUser_Click(sender As Object, e As EventArgs) Handles btnDel.Click
        Try
            If dataGrid.Rows.Item(dataGrid.CurrentRow.Index).Cells(1).Value = "admin" Then
                MsgBox("You can't delete an admin")
            Else
                lblSure.Text = "Sure you want to delete" & vbNewLine & "'" & dataGrid.Rows.Item(dataGrid.CurrentRow.Index).Cells(1).Value & "' ?"
                lblSure.Show()
                btnSureDel.Show()
                btnNoDel.Show()
                btnDel.Enabled = False
            End If
        Catch ex As Exception
            MsgBox("That's not a user")
        End Try
    End Sub
    Private Sub btnSureDel_Click(sender As Object, e As EventArgs) Handles btnSureDel.Click
        Dim userID As Integer = dataGrid.Rows.Item(dataGrid.CurrentRow.Index).Cells(0).Value
        Dim uName As String = dataGrid.Rows.Item(dataGrid.CurrentRow.Index).Cells(1).Value

        If dataGrid.SelectedCells.Count < 1 Then
            lblSure.Hide()
            btnSureDel.Hide()
            btnNoDel.Hide()
            btnDel.Enabled = True
            MsgBox("You have to select a user")
        Else
            Dim connDelUser As New OleDbConnection(connStr)
            Dim cmmdDel As New OleDbCommand
            cmmdDel.Connection = connDelUser
            cmmdDel.CommandText = "DELETE FROM tblUsers WHERE UserID = " & userID
            connDelUser.Open()
            cmmdDel.ExecuteNonQuery()
            connDelUser.Close()
            getData()
            lblSure.Hide()
            btnSureDel.Hide()
            btnNoDel.Hide()
            btnDel.Enabled = True
            MsgBox("'" & uName & "' has been deleted.")
        End If
    End Sub
    Private Sub btnNoDel_Click(sender As Object, e As EventArgs) Handles btnNoDel.Click
        lblSure.Hide()
        btnSureDel.Hide()
        btnNoDel.Hide()
        btnDel.Enabled = True
    End Sub

    Sub getData() 'gets data from user entity into data grid of users
        dataGrid.Rows.Clear()
        Dim rowNum As Integer
        Dim conn As New OleDbConnection(connStr)
        Dim cmmdGetData As New OleDbCommand
        cmmdGetData.Connection = conn
        cmmdGetData.CommandText = "SELECT * FROM tblUsers ORDER BY Username ASC"
        conn.Open()
        Dim reader As OleDbDataReader = cmmdGetData.ExecuteReader

        Do While reader.Read()
            dataGrid.Rows.Add()
            With dataGrid.Rows.Item(rowNum)
                .Cells(0).Value = reader("UserID")
                .Cells(1).Value = reader("Username")
                .Cells(2).Value = reader("Pword")
            End With

            rowNum += 1
        Loop

        If rowNum = 1 Then
            lblNumUsers.Text = "1 user"
        Else
            lblNumUsers.Text = rowNum & " users"
        End If

        conn.Close()
    End Sub

    Private Sub btnHome_Click(sender As Object, e As EventArgs) Handles btnHome.Click
        Form1.Show()
        Me.Close()
    End Sub
    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnLogOut.Click
        login.Close()
    End Sub

    Function getDataToUpdate() As String
        Select Case comboBoxUpdateInfo.Text
            Case "Username"
                Return "Username"
            Case "Password"
                Return "Pword"
        End Select
    End Function
    Private Sub comboBoxUpdateInfo_SelectedIndexChanged(sender As Object, e As EventArgs) Handles comboBoxUpdateInfo.SelectedIndexChanged
        Select Case comboBoxUpdateInfo.Text
            Case "Username"
                lblEnterUpdateData.Text = "Enter the username:"
                txtNewData.UseSystemPasswordChar = False
            Case "Password"
                lblEnterUpdateData.Text = "Enter the  password:"
                txtNewData.UseSystemPasswordChar = True
        End Select
        txtNewData.Clear()
    End Sub

    Private Sub draw(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles Me.Paint
        Dim xPos As Integer = 611
        Dim greenShade As Integer
        Dim numSquares As Byte = 8
        For i = 1 To numSquares
            Dim colour As Color = ColorTranslator.FromHtml("#00" & Hex(greenShade).ToString.PadLeft(2, "0") & "FF")
            Dim pen As New Pen(colour, 1)
            pen.Width = 2
            Dim rectangle As New Rectangle(xPos, 5, 30, 20)
            e.Graphics.DrawRectangle(pen, rectangle)
            xPos += 34
            greenShade += Math.Floor(255 / numSquares)
        Next
    End Sub
    Private Sub timer_Tick(sender As Object, e As EventArgs) Handles timer.Tick
        lblTime.Text = DateTime.Now.Hour.ToString.PadLeft(2, "0") & ":" &
            DateTime.Now.Minute.ToString.PadLeft(2, "0") & ":" &
            DateTime.Now.Second.ToString.PadLeft(2, "0") & vbNewLine &
            DateTime.Now.Day.ToString.PadLeft(2, "0") & "-" &
            DateTime.Now.Month.ToString.PadLeft(2, "0") & "-" &
            DateTime.Now.Year.ToString
    End Sub
End Class