Imports ADOX
Imports System.IO
Imports System.Data.OleDb


Public Class Login
    Public Club As String
    Public AdminAccess As Boolean
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"

    Private Sub Login_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'This sub creates the file and all of the tables when the program is first run. It also run time generates the whole login screen.

        Dim cat As Catalog = New Catalog()
        Dim SQLCommand As String
        Dim con As New OleDbConnection(ConnectionString)
        Dim cmd As New OleDbCommand
        Dim HashedAdminPWord As String = HashPassword("Password123")


        'This checks if the file already exists
        If File.Exists("N:\Whizzkids\WhizzkidsDB.accdb") = True Then
        Else
            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=N:\Whizzkids\WhizzkidsDB.accdb;")

            'This creates the table 'Staff' in the database
            SQLCommand = "CREATE TABLE Staff(StaffID AUTOINCREMENT PRIMARY KEY, StaffName VarChar(255), Username VarChar(255), PWord VarChar(255), Club VarChar(255), IsAdmin Bit);"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = (SQLCommand)
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception
            End Try

            'This inserts the default information so you can log on the first time
            cmd.CommandText = "INSERT INTO Staff(StaffName, Username, PWord, Club, IsAdmin) VALUES ('AdminName', 'Admin', '" & HashedAdminPWord & "', '', True);"
            cmd.ExecuteNonQuery()
            con.Close()

            SQLCommand = "CREATE TABLE ParentLink(FamilyID VarChar(255) PRIMARY KEY, ChildNameOne VarChar(255), ChildNameTwo VarChar(255), ChildNameThree VarChar(255)," &
            "ChildNameFour VarChar(255));"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = (SQLCommand)
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception
            End Try
            con.Close()

            SQLCommand = "CREATE TABLE Register(ChildID VarChar(255) PRIMARY KEY, Present Bit, TimeIn Time, TimeOut Time, Club VarChar(255));"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = (SQLCommand)
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception
            End Try
            con.Close()

            SQLCommand = "CREATE TABLE PaymentLog(ParentID VarChar(255) PRIMARY KEY, ParentName VarChar(255), PaymentsOwed Decimal(5,2), AmountPaid Decimal(5,2), PaymentMethod VarChar(255));"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = (SQLCommand)
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception
            End Try
            con.Close()

            SQLCommand = "CREATE TABLE PaymentHistory(ParentID VarChar(255), AmountPaid Decimal(5,2), TransactionDate VarChar(255), PRIMARY KEY (ParentID, TransactionDate));"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = (SQLCommand)
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception
            End Try
            con.Close()

            SQLCommand = "CREATE TABLE ChildInformation(ChildID VarChar(255), ChildName VarChar(255), Age SmallInt, FamilyID VarChar(255), StaffID Integer, Club VarChar(255)," &
            "PRIMARY KEY (ChildName, FamilyID), FOREIGN KEY (StaffID) REFERENCES Staff(StaffID), FOREIGN KEY (FamilyID) REFERENCES ParentLink(FamilyID)," &
            "FOREIGN KEY (ChildID) REFERENCES Register(ChildID));"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = (SQLCommand)
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception
            End Try
            con.Close()

            SQLCommand = "CREATE TABLE ParentInformation(ParentName VarChar(255), ParentID VarChar(255), FamilyID VarChar(255), ContactNumber Float, Address VarChar(255)," &
            "PaymentsOwed Decimal(5,2), FamilyGP VarChar(255), PRIMARY KEY (ParentName, Address), FOREIGN KEY (FamilyID) REFERENCES ParentLink(FamilyID)," &
            "FOREIGN KEY (ParentID) REFERENCES PaymentLog(ParentID));"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = (SQLCommand)
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception
            End Try
            con.Close()


        End If




        'Creation of title
        Dim lblTitle As New Label
        With lblTitle
            .Size = New Size(300, 50)
            .Location = New Size(80, 20)
            .Text = "Whizzkids Staff Login"
            .Font = New Font("Cambria", 22)
        End With
        'Creation of Username Box
        Dim txtUsername As New TextBox
        With txtUsername
            .Size = New Size(270, 20)
            .Location = New Size(81, 100)
            .Text = "Username"
            .ForeColor = Color.DimGray
            .Font = New Font("Calbri", 18)
            .Name = "UsernameBox"
        End With
        'Creation of Password Box
        Dim txtPassword As New TextBox
        With txtPassword
            .Size = New Size(270, 20)
            .Location = New Size(81, 157)
            .Text = "Password"
            .ForeColor = Color.DimGray
            .Font = New Font("Calbri", 18)
            .Name = "PasswordBox"
        End With
        'Creation of Login Button
        Dim btnLogin As New Button
        With btnLogin
            .Size = New Size(120, 50)
            .Location = New Point(160, 230)
            .Text = "Login"
            .Name = "LoginButton"
        End With

        'Adding all the runtime objects
        Me.Controls.Add(lblTitle)
        Me.Controls.Add(txtUsername)
        Me.Controls.Add(txtPassword)
        Me.Controls.Add(btnLogin)

        'Runs the subs when the boxes and buttons are clicked
        AddHandler btnLogin.Click, AddressOf LoginClick
        AddHandler txtUsername.Click, AddressOf ClickMouseUserName
        AddHandler txtPassword.Click, AddressOf ClickMousePassword

    End Sub
    Sub ClickMouseUserName()
        'This sub runs when the Username textbox is clicked.

        'This calls the class to change the password text to make it hidden
        Dim txtpassword As New StarText
        'If the Username textbox contains 'Username' when it is clicked, that is deleted and the text colour is set to black instead of grey
        If Me.Controls("UsernameBox").Text = "Username" Then
            Me.Controls("UsernameBox").Text = ""
            Me.Controls("UsernameBox").ForeColor = Color.Black
        End If
        'This places the 'Password' back in the Password textbox if it is empty when the Username box is clicked
        If Me.Controls("PasswordBox").Text = "" Then
            Me.Controls("PasswordBox").Text = "Password"
            Me.Controls("PasswordBox").ForeColor = Color.DimGray
            txtpassword.UnstarPassword(Me.Controls("PasswordBox"))
        End If
    End Sub

    Sub ClickMousePassword()
        'This sub does the same as the sub above, but for the other respective textboxes

        Dim txtpassword As New StarText
        If Me.Controls("PasswordBox").Text = "Password" Then
            Me.Controls("PasswordBox").Text = ""
            Me.Controls("PasswordBox").ForeColor = Color.Black
        End If
        If Me.Controls("UsernameBox").Text = "" Then
            Me.Controls("UsernameBox").Text = "Username"
            Me.Controls("UsernameBox").ForeColor = Color.DimGray
        End If
        txtpassword.StarPassword(Me.Controls("PasswordBox"))
    End Sub
    Sub LoginClick()
        'This sub validates the login with the Staff table

        Dim LoginUsername As String = ""
        Dim LoginPassword As String = ""
        Dim LoginCorrect As Boolean
        Dim ReaderAdmin As Boolean = False
        Dim ReaderClub As Boolean = False
        Dim cat As Catalog = New Catalog()
        Dim con As New OleDbConnection(ConnectionString)
        Dim cmd As New OleDbCommand

        'This searches the staff table for the correct Username and Password and gives the user, if an Admin, the option to select which club that they are at
        'Reading and writing from files - Grade B
        cmd.Connection = con
        cmd.CommandText = "SELECT Username, Pword, IsAdmin, Club FROM Staff WHERE Username = '" & Me.Controls("UsernameBox").Text & "'" &
        "AND PWord = '" & HashPassword(Me.Controls("PasswordBox").Text) & "'"
        con.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Do While reader.Read()
            LoginUsername = reader("Username")
            LoginPassword = reader("PWord")
            ReaderAdmin = reader("IsAdmin")
        Loop
        If LoginUsername <> "" And LoginPassword <> "" Then
            LoginCorrect = True
            If ReaderAdmin = True Then
                AdminAccess = True
                Club_Selection.Show()
            Else
                Main_Menu.Show()
            End If
            Me.Hide()
        Else
            MsgBox("Login details incorrect. Please try again.")
            LoginCorrect = False
        End If


        reader.Close()
        con.Close()



    End Sub
    Public Shared Function HashPassword(ByVal HPassword As String) As String
        'This sub is the hashing algorithm. It hashes the password when stored and hashes it when a login is attempted, so the true password is never visible anywhere
        'Hashing - Grade A

        Dim HashAlgorithm As New System.Security.Cryptography.MD5CryptoServiceProvider()
        Dim PasswordBytes() As Byte = System.Text.Encoding.ASCII.GetBytes(HPassword)

        PasswordBytes = HashAlgorithm.ComputeHash(PasswordBytes)

        Dim HashedPassword As String = ""
        Dim b As Byte

        For Each b In PasswordBytes
            HashedPassword += b.ToString("x2")
        Next

        Return HashedPassword
    End Function

End Class
Class StarText
    'These subs make the password textbox on the login visible and hidden when called
    Public Sub StarPassword(ByVal txtpassword As TextBox)
        txtpassword.UseSystemPasswordChar = True
    End Sub
    Public Sub UnstarPassword(ByVal txtpassword As TextBox)
        txtpassword.UseSystemPasswordChar = False
    End Sub
End Class