#Region "Constructors"
    'Everytime we instantiate the object (ClsLogin) a connection to the database must be established
    Public Sub New()

        con.ConnectionString = dbprovider & dbsource
        con.Open()

    End Sub

#End Region

#Region "Methods"
    'Function that logs the user in by using the employeeid, username, password, roleid and email.
    Public Function Login(strUsername As String, strPassword As String) As Boolean

        'Below is the command that will enable the program to find and use the username and password that is stored in the database of an employee
        Dim cmd As String
        cmd = "SELECT Employeeid, username, password, roleid, email FROM tblEmployees WHERE username = ? AND password = ?"

        'Create a new DataAdapter and establish a new SQL Command
        da = New OleDb.OleDbDataAdapter(cmd, con)
        da.SelectCommand.Parameters.Add("@username", OleDb.OleDbType.VarChar, 25).Value = strUsername
        da.SelectCommand.Parameters.Add("@password", OleDb.OleDbType.VarChar, 25).Value = strPassword
        da.Fill(ds, "Login")
        dt = ds.Tables("Login")

        If dt.Rows.Count > 0 Then
            employeeid = dt.Rows(0).Item("Employeeid")
            username = dt.Rows(0).Item("username")
            password = dt.Rows(0).Item("password")
            Roleid = dt.Rows(0).Item("roleid")
            email = dt.Rows(0).Item("email")
            Return True
        Else
            Return False
        End If

        da.Dispose()
        con.Close()


    End Function
    'Function that builds the main menu by using the tables and information within the database
    Public Function GetMainMenu()
        Try

            'Below is the statement that will enable the program to select the correct fields to produce the correct Main Menu for each user
            Dim cmd As String
            cmd = "Select MenuText from tblMenuMaster1 Where MainMenuID = 0 and MenuID in (Select MenuID from tblAccess Where AccessID =  " & Roleid & ") And isActive = 1"

            da = New OleDb.OleDbDataAdapter(cmd, con)

            da.Fill(ds, "MenuText")
            dt = ds.Tables("Menutext")

            da.Dispose()
            con.Close()
            Return dt

        Catch ex As Exception
            MsgBox(ex)
        End Try
    End Function
    'Function that gets the MenuID from tblMenuMaster1 in the Database - regarding the specific access levels
    Public Function GetMenuID(Menutext As String)

        'Below is the command that will enable the program to ascertain the right access levels for the Manager, Admin and Employees
        Dim cmd As String
        cmd = "Select MenuID from tblMenuMaster1 Where MenuText = '" & Menutext & "'"

        da = New OleDb.OleDbDataAdapter(cmd, con)

        da.Fill(ds, "MenuText")
        dt = ds.Tables("Menutext")

        da.Dispose()
        con.Close()
        Return dt

    End Function
    'Function that ascertains the correct menu text to be displayed on the form
    Public Function GetMenuText(parentMenuID As Integer)

        'Below is the statement that will enable the program to select the correct menu text from the database to be displayed within the main menu
        Dim cmd As String
        cmd = ""
        cmd = "Select MenuText from tblMenuMaster1 Where MainMenuID = " & parentMenuID & " And isActive = 1 And MenuID in (Select MenuID from tblAccess Where AccessId =  " & Roleid & ")" & "Order BY MenuOrder"

        da = New OleDb.OleDbDataAdapter(cmd, con)

        da.Fill(ds, "parentMenuID")
        dt = ds.Tables("parentMenuID")

        da.Dispose()
        con.Close()

        Return dt

    End Function
    'Function that gets the correct formname that is stored in the database in order to produce the correct and desired main menu
    Public Function GetFormName(Menutext As String)

        'Below is the command that will select the correct formname that is stored in teh database
        Dim cmd As String
        cmd = ""
        cmd = "Select FormName from tblMenuMaster1 Where MenuText = '" & Menutext & "'"

        da = New OleDb.OleDbDataAdapter(cmd, con)

        da.Fill(ds, "formName")
        dt = ds.Tables("formName")

        da.Dispose()
        con.Close()

        Return dt

    End Function
    'Function that allows the user to update their own password
    Public Sub Updateuserpassword(stremployeeid As Integer, strpassword As String)
        Try
            da = New OleDb.OleDbDataAdapter()
            Dim command As OleDb.OleDbCommand
            command = New OleDb.OleDbCommand

            Using da
                'Below is the command that will enable the user to update their password and will be stored in the correct field in the database
                command.CommandText = "UPDATE Tblusers SET Tblusers.Password = ? WHERE (((Tblusers.UserID)=?));"
                command.Parameters.AddWithValue("@password", OleDb.OleDbType.VarChar).Value = strpassword
                command.Parameters.AddWithValue("@employeeid", stremployeeid)

                command.Connection = con
                command.ExecuteNonQuery()

            End Using

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    'Function that allows the program - according to the correct access level - to create a new user to be inserted and stored in the database
    Public Sub InsertNewuser()
        Try

            da = New OleDb.OleDbDataAdapter()
            Dim command As OleDb.OleDbCommand
            command = New OleDb.OleDbCommand
            'Dim command As OleDb.OleDbCommand
            'command = New OleDb.OleDbCommand
            'Dim da As New OleDb.OleDbDataAdapter(command, con)

            Using da

                'Below is the command that allow the user - with the correct access levels - to insert a new user with their username and password
                command.CommandText = "INSERT INTO Tblusers (Username, Password) VALUES( ?, ?)"
                'command.Parameters.AddWithValue("@username", getUsername)
                'command.Parameters.AddWithValue("@password", getpassword)
                command.Connection = con
                command.ExecuteNonQuery()

            End Using

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

#End Region


End Class