Imports System.Text
Imports System.Data.OleDb
Imports System.Security.Cryptography

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

#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 "Form actions"
    Private Sub frmLogin_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        closeconnection()
        Me.Dispose()
        Me.Close()
    End Sub
    Private Sub frmLogin_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        openconnection()
        TimerDate.Start()

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

#Region "Timer"
    Private Sub TimerDate_Tick(sender As System.Object, e As System.EventArgs) Handles TimerDate.Tick
        lblTime.Text = TimeOfDay
        lblDate.Text = Today.Date.ToString("dddd, dd MMMM yyyy")
    End Sub
#End Region

#Region "LOGIN"
    Private Sub btnLogin_Click(sender As System.Object, e As System.EventArgs) Handles btnLogin.Click
        Dim uname As String = txtUsername.Text.Trim
        Dim pass As String = txtPassword.Text.Trim

        If uname = "" Or IsNothing(uname) = True Then
            MsgBox("Please enter username", MessageBoxIcon.Warning, "Error")
            txtUsername.Focus()
        ElseIf uname.Length > 10 Then
            MsgBox("Username can not have greater than 10 characters", MessageBoxIcon.Warning, "Error")
            txtUsername.Focus()

        ElseIf pass = "" Or IsNothing(pass) = True Then
            MsgBox("Please enter password", MessageBoxIcon.Warning, "Error")
            txtPassword.Focus()
        ElseIf pass.Length > 20 Then
            MsgBox("Password can not have greater than 20 characters", MessageBoxIcon.Warning, "Error")
            txtPassword.Focus()

        Else
            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)

            Dim selectqry As String = "SELECT * FROM users WHERE uname='" + uname + "'"
            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 dbuname, dbpass, dbrole, dbstatus, dbaccess As String
            For Each temprow In dttable.Rows
                dbuname = temprow("uname").ToString
                dbpass = temprow("upass").ToString
                dbrole = temprow("role").ToString
                dbstatus = temprow("status").ToString
                dbaccess = temprow("access").ToString

                If (StrComp(dbuname, uname) = 0) And (StrComp(dbpass, hashPwd) = 0) And (StrComp(dbrole, "Administrator") = 0) And (StrComp(dbstatus, "ACTIVE") = 0) And (StrComp(dbaccess, "LOGOUT") = 0) Then

                    Dim unamelog As String = uname
                    Dim activity As String = unamelog + " Logged in"
                    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='LOGIN' WHERE uname='" + uname + "'"
                    Dim updatecmd As New OleDbCommand
                    With updatecmd
                        .CommandText = updateqry
                        .Connection = connect
                        .ExecuteNonQuery()
                    End With

                    lbluname.Text = uname
                    frmAdminENG.Show()
                    clearinputs_login()
                    Me.Hide()
                    Exit Sub

                ElseIf (StrComp(dbuname, uname) = 0) And (StrComp(dbpass, hashPwd) = 0) And (StrComp(dbrole, "Employee") = 0) And (StrComp(dbstatus, "ACTIVE") = 0) And (StrComp(dbaccess, "LOGOUT") = 0) Then
                    Dim unamelog As String = uname
                    Dim activity As String = unamelog + " Logged in"
                    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='LOGIN' WHERE uname='" + uname + "'"
                    Dim updatecmd As New OleDbCommand
                    With updatecmd
                        .CommandText = updateqry
                        .Connection = connect
                        .ExecuteNonQuery()
                    End With

                    lbluname.Text = uname
                    frmUserENG.Show()
                    clearinputs_login()
                    Me.Hide()
                    Exit Sub

                ElseIf (StrComp(dbuname, uname) = 0) And (StrComp(dbpass, hashPwd) = 0) And (StrComp(dbrole, "Employee") = 0) And (StrComp(dbstatus, "ACTIVE") = 0) And (StrComp(dbaccess, "LOGIN") = 0) Then
                MsgBox("You have already logged in using this ID on another computer", MessageBoxIcon.Warning, "Error")
                clearinputs_login()
                Exit Sub

                ElseIf (StrComp(dbuname, uname) = 0) And (StrComp(dbpass, hashPwd) = 0) And (StrComp(dbrole, "Administrator") = 0) And (StrComp(dbstatus, "ACTIVE") = 0) And (StrComp(dbaccess, "LOGIN") = 0) Then
                MsgBox("You have already logged in using this ID on another computer", MessageBoxIcon.Warning, "Error")
                clearinputs_login()
                Exit Sub

                ElseIf (StrComp(dbuname, uname) = 0) And (StrComp(dbpass, hashPwd) = 0) And (StrComp(dbrole, "Employee") = 0) And (StrComp(dbstatus, "SUSPENDED") = 0) Then
                MsgBox("Your account is suspended. Please contact administrator", MessageBoxIcon.Warning, "Error")
                clearinputs_login()
                Exit Sub

                ElseIf (StrComp(dbuname, uname) = 0) And (StrComp(dbpass, hashPwd) = 0) And (StrComp(dbrole, "Administrator") = 0) And (StrComp(dbstatus, "SUSPENDED") = 0) Then
                MsgBox("Your account is suspended. Please contact administrator", MessageBoxIcon.Warning, "Error")
                clearinputs_login()
                Exit Sub
                ElseIf (StrComp(dbuname, uname) <> 0) Or (StrComp(dbpass, hashPwd) <> 0) Then
                MsgBox("Wrong username or password", MessageBoxIcon.Warning, "Error")
                clearinputs_login()
                Exit Sub
                End If
            Next

        End If
    End Sub

#End Region

#Region "CLEAR"
    Private Sub btnClear_Click(sender As System.Object, e As System.EventArgs) Handles btnClear.Click
        clearinputs_login()
    End Sub
#End Region

#Region "Clear inputs"
    Private Sub clearinputs_login()
        txtUsername.Clear()
        txtPassword.Clear()
    End Sub
#End Region

End Class