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 ClassImports 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

Imports ADOX
Imports System.IO
Imports System.Data.OleDb
Public Class Club_Selection
    'This form is only ever run if the user is an admin

    Dim cat As Catalog = New Catalog()
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
    Dim con As New OleDbConnection(ConnectionString)
    Dim cmd As New OleDbCommand
    Dim reader As OleDbDataReader
    Private Sub btnChooseCronton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChooseCronton.Click
        'When Cronton is selected the user's club is set to Cronton in the Staff table, so the system treats the user as a Cronton employee

        Main_Menu.Show()
        Me.Hide()
        Login.Club = "Cronton"
        'This is the updating of the users details in the Staff table
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "UPDATE Staff SET Club = '" & Login.Club & "' WHERE Username = '" & Login.Controls("UsernameBox").Text & "';"
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub

    Private Sub btnChooseFarnworth_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChooseFarnworth.Click
        'When Farnworth is selected the user's club is set to Farnworth in the Staff table, so the system treats the user as a Farnworth employee

        Main_Menu.Show()
        Me.Hide()
        Login.Club = "Farnworth"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "UPDATE Staff SET Club = '" & Login.Club & "' WHERE Username = '" & Login.Controls("UsernameBox").Text & "';"
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub
End Class

Imports ADOX
Imports System.IO
Imports System.Data.OleDb
Public Class Main_Menu
    'This form loads the other forms when the respective button is clicked

    Dim cat As Catalog = New Catalog()
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
    Dim con As New OleDbConnection(ConnectionString)
    Dim cmd As New OleDbCommand
    Dim reader As OleDbDataReader
    Sub btnEditStaffInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditStaffInfo.Click
        'This form is only available to admins so it checks if the user is an admin before opening the form

        If Login.AdminAccess = True Then
            Edit_Staff_Info.Show()
        Else
            MsgBox("You do not have access to this page")
        End If
    End Sub

    Private Sub btnCurrentRegAndPay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCurrentRegAndPay.Click
        Current_Reg_And_Pay.Show()
    End Sub

    Private Sub btnParentInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParentInfo.Click
        Parent_Info.Show()
    End Sub

    Private Sub btnChildInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChildInfo.Click
        Child_Info.Show()
    End Sub

    Private Sub btnPaymentHistory_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPaymentHistory.Click
        Payment_History.Show()
    End Sub
End Class

Imports ADOX
Imports System.IO
Imports System.Data.OleDb
Imports System.Text.RegularExpressions
Public Class Edit_Staff_Info
    'This form can be used by an administrator to add new staff, edit details about current staff or delete staff altogether

    Dim cat As Catalog = New Catalog()
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
    Dim con As New OleDbConnection(ConnectionString)
    Dim cmd As New OleDbCommand
    Dim SQLCommand As String = "SELECT * FROM Staff "
    Dim reader As OleDbDataReader
    Dim EditStaffID As Integer
    Dim EditStaffName, EditClub, EditPassword, EditUsername As String
    Dim EditAdmin As Boolean
    Dim PasswordCheckUCase As New Regex("(?=.*[A-Z])")
    Dim PasswordCheckNumber As New Regex("(?=.*[0-9])")


    Private Sub Edit_Staff_Info_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'When the form loads, the refresh sub is called, which populates the table

        StaffInfoRefresh()
    End Sub

    Sub btnAddInformation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddInformation.Click
        'This sub is called when the add information button is clicked. It checks if the information is in the correct form to be inserted into the database, using some regex, and also
        'checks if there is already a staff member with repeated attributes.
        'The staff ID is autoincremented when the data is inserted

        EditStaffName = txtStaffName.Text
        EditUsername = txtEditUserName.Text
        EditPassword = Login.HashPassword(txtEditPassword.Text)
        EditAdmin = chkAdmin.Checked

        'Checks if both Cronton and Farnworth are checked because the staff member cannot work at both clubs.
        If (chkCronton.Checked And chkFarnworth.Checked) = True Then
            EditClub = ""
        Else
            EditClub = ""
            If chkCronton.Checked = True Then
                EditClub = "Cronton"
            End If
            If chkFarnworth.Checked = True Then
                EditClub = "Farnworth"
            End If
        End If

        'This If statement validates that all the textboxes have been filled and that one of the clubs has been selected
        If EditStaffName.Length = 0 Or EditUsername.Length = 0 Or EditClub.Length = 0 Then
            MsgBox("Attempt failed. Please enter all the information correctly")
        Else
            If PasswordCheckUCase.IsMatch(txtEditPassword.Text) = False Then
                MsgBox("Your Password must contain at least one Capital letter and number")
            Else
                If PasswordCheckNumber.IsMatch(txtEditPassword.Text) = False Then
                    MsgBox("Your Password must contain at least one Capital letter and number")
                Else
                    If txtEditPassword.Text.Length < 6 Then
                        MsgBox("The password has to be 6 or more characters long")
                    Else

                        'Checks with the database if the username is already taken.
                        con.Open()
                        cmd.Connection = con
                        cmd.CommandText = "Select * FROM Staff WHERE Username = '" & EditUsername & "'"
                        cmd.ExecuteNonQuery()
                        reader = cmd.ExecuteReader
                        If reader.Read Then
                            MsgBox("That Username already exists")
                        Else
                            con.Close()
                            con.Open()
                            cmd.Connection = con
                            cmd.CommandText = "INSERT INTO Staff(StaffName, Username, PWord, Club, IsAdmin) VALUES ('" & EditStaffName & "','" & EditUsername & "'," &
                            "'" & EditPassword & "','" & EditClub & "', " & EditAdmin & ");"
                            cmd.ExecuteNonQuery()
                            con.Close()
                            MsgBox("Added")
                        End If
                    End If
                End If
            End If
        End If
        con.Close()
        'Refreshes the table when the information has been added
        StaffInfoRefresh()
    End Sub

    Private Sub btnDeleteStaffMember_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteStaffMember.Click
        'This sub gives runs when the delete staff member button is clicked.

        'This reads from the staff table to check if the StaffID exists
        EditStaffID = txtStaffID.Text
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & EditStaffID & ""
        cmd.ExecuteNonQuery()
        reader = cmd.ExecuteReader
        If reader.Read Then
            'This then deletes the the StaffID if it exists
            con.Close()
            con.Open()
            cmd.CommandText = "Delete FROM Staff WHERE StaffID = " & EditStaffID & ""
            cmd.ExecuteNonQuery()
            MsgBox("Deleted")
        Else
            MsgBox("StaffID doesn't exist")
        End If
        con.Close()
        StaffInfoRefresh()
    End Sub
    Private Sub btnSearchStaffMember_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchStaffMember.Click
        'This sub allows the user to search for a staff members record by using their name

        EditStaffName = txtStaffName.Text
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT * FROM Staff WHERE StaffName = '" & EditStaffName & "'"
        cmd.ExecuteNonQuery()
        reader = cmd.ExecuteReader
        grdStaffInfo.Rows.Clear()
        If reader.Read Then
            'This empties the table and repopulates it with the matching staff name
            Dim n As Integer = grdStaffInfo.Rows.Add
            grdStaffInfo.Rows.Item(n).Cells(0).Value = (reader("StaffID"))
            grdStaffInfo.Rows.Item(n).Cells(1).Value = (reader("StaffName"))
            grdStaffInfo.Rows.Item(n).Cells(2).Value = (reader("Username"))
            grdStaffInfo.Rows.Item(n).Cells(3).Value = (reader("PWord"))
            grdStaffInfo.Rows.Item(n).Cells(4).Value = (reader("Club"))
            grdStaffInfo.Rows.Item(n).Cells(5).Value = (reader("IsAdmin"))
        Else
            MsgBox("Staff Name doesn't exist")
        End If
        con.Close()
    End Sub
    Sub StaffInfoRefresh()
        'This sub is called whenever the table needs to be updated to match the database

        grdStaffInfo.Rows.Clear()
        con.Open()
        cmd.Connection = con
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader
        Do While reader.Read()
            Dim n As Integer = grdStaffInfo.Rows.Add
            grdStaffInfo.Rows.Item(n).Cells(0).Value = (reader("StaffID"))
            grdStaffInfo.Rows.Item(n).Cells(1).Value = (reader("StaffName"))
            grdStaffInfo.Rows.Item(n).Cells(2).Value = (reader("Username"))
            grdStaffInfo.Rows.Item(n).Cells(3).Value = (reader("PWord"))
            grdStaffInfo.Rows.Item(n).Cells(4).Value = (reader("Club"))
            grdStaffInfo.Rows.Item(n).Cells(5).Value = (reader("IsAdmin"))
        Loop
        con.Close()
    End Sub

    Private Sub btnEditExistingStaff_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditExistingStaff.Click
        'This sub allows the user to edit all an existing staff members details, except the StaffID

        EditStaffID = txtStaffID.Text
        EditStaffName = txtStaffName.Text
        EditUsername = txtEditUserName.Text
        EditPassword = Login.HashPassword(txtEditPassword.Text)
        EditAdmin = chkAdmin.Checked

        If (chkCronton.Checked And chkFarnworth.Checked) = True Then
            EditClub = ""
        Else
            If chkCronton.Checked = True Then
                EditClub = "Cronton"
            End If
            If chkFarnworth.Checked = True Then
                EditClub = "Farnworth"
            End If
        End If

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & EditStaffID & ""
        cmd.ExecuteNonQuery()
        reader = cmd.ExecuteReader
        If reader.Read Then
            con.Close()
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "Select * FROM Staff WHERE Username = '" & EditUsername & "'"
            cmd.ExecuteNonQuery()
            reader = cmd.ExecuteReader
            If reader.Read Then
                MsgBox("That Username already exists")
            Else
                If EditStaffName.Length = 0 Or EditUsername.Length = 0 Or EditClub.Length = 0 Or EditStaffID = 0 Then
                    MsgBox("Attempt failed. Please enter all the information correctly")
                Else
                    If PasswordCheckUCase.IsMatch(txtEditPassword.Text) = False Then
                        MsgBox("Your Password must contain at least one Capital letter and number")
                    Else
                        If PasswordCheckUCase.IsMatch(txtEditPassword.Text) = False Then
                            MsgBox("Your Password must contain at least one Capital letter and number")
                        Else

                            If txtEditPassword.Text.Length < 6 Then
                                MsgBox("The password has to be 6 or more characters long")
                            Else
                                con.Close()
                                con.Open()
                                cmd.Connection = con
                                cmd.CommandText = "UPDATE Staff SET StaffName = '" & EditStaffName & "', Username = '" & EditUsername & "', Pword = '" & EditPassword & "'," &
                                "Club = '" & EditClub & "', IsAdmin = " & EditAdmin & " WHERE StaffID = " & EditStaffID & ""
                                cmd.ExecuteNonQuery()
                                con.Close()
                                MsgBox("Edited")
                            End If
                        End If
                    End If
                End If
            End If
        Else
            MsgBox("That Staff ID doesn't exist")
        End If

        con.Close()
        StaffInfoRefresh()
    End Sub
End Class

Imports ADOX
Imports System.IO
Imports System.Data.OleDb
Public Class Child_Info
    Dim cat As Catalog = New Catalog()
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
    Dim con As New OleDbConnection(ConnectionString)
    Dim cmd As New OleDbCommand
    Dim reader As OleDbDataReader
    Dim SQLCommand As String
    Dim ChildID, ChildName, StaffID, FamilyID, Club, EditChildID, EditStaffID, EditFamilyID, EditClub, Age, EditAge As String

    Private Sub Child_Info_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'This sub calls the refresh sub when the from is loaded

        ChildInfoRefresh()
    End Sub

    Sub ChildInfoRefresh()
        'This sub populates the Child Info table when called

        grdChildInfo.Rows.Clear()
        con.Open()
        cmd.Connection = con
        SQLCommand = "SELECT * FROM ChildInformation"
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader

        Do While reader.Read()
            Dim n As Integer = grdChildInfo.Rows.Add
            grdChildInfo.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
            grdChildInfo.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
            grdChildInfo.Rows.Item(n).Cells(2).Value = (reader("Age"))
            grdChildInfo.Rows.Item(n).Cells(3).Value = (reader("FamilyID"))
            grdChildInfo.Rows.Item(n).Cells(4).Value = (reader("StaffID"))
            grdChildInfo.Rows.Item(n).Cells(5).Value = (reader("Club"))

        Loop
        con.Close()
    End Sub

    Private Sub btnLinkAddNewChild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLinkAddNewChild.Click
        'This sub shows the Add New Child form when the button is pressed. This is for if the child has no siblings currently attending.

        Add_New_Child.Show()
    End Sub


    Private Sub btnDeleteChild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteChild.Click
        'This sub deletes the child from all the tables in the database and also checks if the child is the only child related to their parent
        'and if so, deletes the parents' records too.

        Dim FamilyID As String
        Dim ParentName As String
        ChildID = txtChildID.Text
        con.Close()
        con.Open()
        cmd.Connection = con

        cmd.CommandText = "Select * FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
        cmd.ExecuteNonQuery()
        con.Close()
        con.Open()
        reader = cmd.ExecuteReader
        If reader.Read Then
            con.Close()
            con.Open()
            cmd.CommandText = "Select ChildNameOne FROM ParentLink"
            cmd.ExecuteNonQuery()
            reader = cmd.ExecuteReader
            If reader.Read Then
                con.Close()
                con.Open()
                cmd.CommandText = "Select FamilyID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
                cmd.ExecuteNonQuery()
                reader = cmd.ExecuteReader
                reader.Read()
                FamilyID = reader("FamilyID")
                cmd.CommandText = "Select ParentName FROM ParentInformation WHERE FamilyID = '" & FamilyID & "'"
                con.Close()
                con.Open()
                cmd.ExecuteNonQuery()
                reader = cmd.ExecuteReader
                reader.Read()
                ParentName = reader("ParentName")
                cmd.CommandText = "Delete FROM ParentInformation WHERE FamilyID = '" & FamilyID & "'"
                con.Close()
                con.Open()
                cmd.ExecuteNonQuery()
                cmd.CommandText = "Delete FROM PaymentLog WHERE ParentName = '" & ParentName & "'"
                con.Close()
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
                con.Open()
                cmd.CommandText = "Delete FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
                cmd.ExecuteNonQuery()
                con.Close()
                con.Open()
                cmd.CommandText = "Delete FROM Register WHERE ChildID = '" & ChildID & "'"
                cmd.ExecuteNonQuery()
                MsgBox("Deleted")
                cmd.CommandText = "Delete FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
                con.Close()
                con.Open()
                cmd.ExecuteNonQuery()
            Else
                con.Close()
                con.Open()
                cmd.CommandText = "Delete FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
                cmd.ExecuteNonQuery()
                con.Close()
                con.Open()
                cmd.CommandText = "Delete FROM Register WHERE ChildID = '" & ChildID & "'"
                cmd.ExecuteNonQuery()
                MsgBox("Deleted")
            End If
        Else
            MsgBox("ChildID doesn't exist")
        End If
        con.Close()
        ChildInfoRefresh()
    End Sub

    Private Sub btnChildInfoSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChildInfoSearch.Click
        'This sub is run when the user searches for a childs' records by their child name and displays it in the grid

        ChildName = txtChildName.Text
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT * FROM ChildInformation WHERE ChildName = '" & ChildName & "'"
        cmd.ExecuteNonQuery()
        reader = cmd.ExecuteReader
        grdChildInfo.Rows.Clear()
        If reader.Read Then
            Dim n As Integer = grdChildInfo.Rows.Add
            grdChildInfo.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
            grdChildInfo.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
            grdChildInfo.Rows.Item(n).Cells(2).Value = (reader("Age"))
            grdChildInfo.Rows.Item(n).Cells(3).Value = (reader("FamilyID"))
            grdChildInfo.Rows.Item(n).Cells(4).Value = (reader("StaffID"))
            grdChildInfo.Rows.Item(n).Cells(5).Value = (reader("Club"))
        Else
            MsgBox("Child Name doesn't exist")
        End If
        con.Close()
    End Sub

    Private Sub btnEditExistingChild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditExistingChild.Click
        'This sub allows the user to edit an already existing childs' information

        EditChildID = txtEditChildID.Text
        EditAge = txtEditAge.Text
        EditStaffID = txtEditStaffID.Text
        EditFamilyID = txtEditFamilyID.Text

        If (chkEditCrontonChild.Checked And chkEditFarnworthChild.Checked) = True Then
            EditClub = ""
        Else
            EditClub = ""
            If chkEditCrontonChild.Checked = True Then
                EditClub = "Cronton"
            End If
            If chkEditFarnworthChild.Checked = True Then
                EditClub = "Farnworth"
            End If
        End If

        If EditChildID.Length = 0 Or EditAge.ToString.Length = 0 Or EditStaffID.Length = 0 Or EditAge.ToString.Length > 2 Or EditFamilyID.Length = 0 Or EditClub.Length = 0 Then
            MsgBox("Attempt failed. Please enter all the information correctly")
        Else
            con.Close()
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "Select * FROM ChildInformation WHERE ChildID = '" & EditChildID & "'"
            cmd.ExecuteNonQuery()
            reader = cmd.ExecuteReader
            If reader.Read Then
                con.Close()
                con.Open()
                cmd.Connection = con
                cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & EditStaffID & ""
                cmd.ExecuteNonQuery()
                reader = cmd.ExecuteReader
                If reader.Read Then
                    con.Close()
                    con.Open()
                    cmd.Connection = con
                    cmd.CommandText = "Select * FROM ParentLink WHERE FamilyID = '" & EditFamilyID & "'"
                    cmd.ExecuteNonQuery()
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        con.Close()
                        con.Open()
                        cmd.Connection = con
                        cmd.CommandText = "UPDATE ChildInformation SET Age = '" & EditAge & "', Club = '" & EditClub & "', StaffID = '" & EditStaffID & "', FamilyID = '" & EditFamilyID & "'" &
                        "WHERE ChildID = '" & EditChildID & "'"
                        cmd.ExecuteNonQuery()
                        con.Close()
                        MsgBox("Edited")
                    Else
                        MsgBox("That Family ID doesn't exist")
                    End If
                Else
                    MsgBox("That Staff ID doesn't exist")
                End If

            Else
                MsgBox("That Child ID doesn't exist")
            End If
        End If
        con.Close()
        ChildInfoRefresh()
    End Sub

    Private Sub btnLinkWithCurrentFamily_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLinkWithCurrentFamily.Click
        'This sub links a child to an already existing family

        ChildID = txtChildID.Text
        ChildName = txtChildName.Text
        Age = txtAge.Text
        StaffID = txtStaffID.Text
        FamilyID = txtFamilyID.Text

        If chkCrontonChild.Checked = True And chkFarnworthChild.Checked = True Then
            Club = ""
        Else
            Club = ""
            If chkCrontonChild.Checked = True Then
                Club = "Cronton"
            End If
            If chkFarnworthChild.Checked = True Then
                Club = "Farnworth"
            End If
        End If

        If ChildID.Length = 0 Or ChildName.Length = 0 Or Age.ToString.Length = 0 Or Age.ToString.Length > 2 Or StaffID.Length = 0 Or FamilyID.Length = 0 Or Club.Length = 0 Then
            MsgBox("Attempt failed. Please enter all the information correctly")
        Else
            con.Close()
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & StaffID & ""
            cmd.ExecuteNonQuery()
            reader = cmd.ExecuteReader
            If reader.Read Then
                con.Close()
                con.Open()
                cmd.CommandText = "Select ChildNameOne FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
                cmd.ExecuteNonQuery()
                reader = cmd.ExecuteReader
                If reader.Read Then
                    con.Close()
                    con.Open()
                    cmd.CommandText = "INSERT INTO Register(ChildID, Club) VALUES ('" & ChildID & "', '" & Club & "');"
                    cmd.ExecuteNonQuery()

                    con.Close()
                    con.Open()
                    cmd.Connection = con
                    cmd.CommandText = "INSERT INTO ChildInformation(ChildID, ChildName, Age, FamilyID, StaffID, Club) VALUES ('" & ChildID & "','" & ChildName & "','" & Age & "','" & FamilyID & "'," &
                    "'" & StaffID & "','" & Club & "');"
                    cmd.ExecuteNonQuery()

                    con.Close()
                    con.Open()
                    cmd.CommandText = "Select ChildNameTwo FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
                    cmd.ExecuteNonQuery()
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        con.Close()
                        con.Open()
                        cmd.CommandText = "Select ChildNameThree FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
                        cmd.ExecuteNonQuery()
                        reader = cmd.ExecuteReader
                        If reader.Read Then
                            con.Close()
                            con.Open()
                            cmd.CommandText = "UPDATE ParentLink SET ChildNameFour = '" & ChildName & "' WHERE FamilyID = '" & FamilyID & "';"
                            cmd.ExecuteNonQuery()
                        Else
                            con.Close()
                            con.Open()
                            cmd.CommandText = "UPDATE ParentLink SET ChildNameThree = '" & ChildName & "' WHERE FamilyID = '" & FamilyID & "';"
                            cmd.ExecuteNonQuery()
                        End If
                    Else
                        con.Close()
                        con.Open()
                        cmd.CommandText = "UPDATE ParentLink SET ChildNameTwo = '" & ChildName & "' WHERE FamilyID = '" & FamilyID & "';"
                        cmd.ExecuteNonQuery()
                    End If
                    con.Close()
                    ChildInfoRefresh()
                    MsgBox("Added")
                Else
                    MsgBox("There is no current Family stored")
                End If
            Else
                MsgBox("That StaffID doesn't exist")
            End If
        End If
        con.Close()
    End Sub

    Private Sub btnStaffInfoRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStaffInfoRefresh.Click
        'This refreshes the table, for if you want the original table back after performing a search
        ChildInfoRefresh()
    End Sub

    Private Sub btnSortByName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSortByName.Click
        'This sub impliments a mergesort in order to sort the children into alphabetical order and display this in the grid

        Dim i As Integer = -1
        Dim n As Integer = 0

        SQLCommand = "SELECT ChildName FROM ChildInformation"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader

        'This counts how many children are in the table
        Do While reader.Read()
            i = i + 1
        Loop
        con.Close()

        'Creates an array the size of the table
        Dim ChildArray(i) As String

        SQLCommand = "SELECT ChildName FROM ChildInformation"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader

        'This adds the childrens names to the array
        Do While reader.Read()
            ChildArray(n) = (reader("ChildName"))
            n = n + 1
        Loop
        con.Close()

        'Mergesort - Grade A
        MergeSort(ChildArray)

        'This adds the sorted children back into the table
        For m = 0 To UBound(ChildArray)
            grdChildInfo.Rows.Item(m).Cells(1).Value = ChildArray(m)
        Next

        'Adds the rest of the informtion about the respective child back into the grid with the child name
        For j = 0 To UBound(ChildArray)
            SQLCommand = "SELECT ChildID, Age, FamilyID, StaffID, Club FROM ChildInformation WHERE ChildName = '" & ChildArray(j) & "'"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = (SQLCommand)
            reader = cmd.ExecuteReader
            Do While reader.Read()
                grdChildInfo.Rows.Item(j).Cells(0).Value = (reader("ChildID"))
                grdChildInfo.Rows.Item(j).Cells(2).Value = (reader("Age"))
                grdChildInfo.Rows.Item(j).Cells(3).Value = (reader("FamilyID"))
                grdChildInfo.Rows.Item(j).Cells(4).Value = (reader("StaffID"))
                grdChildInfo.Rows.Item(j).Cells(5).Value = (reader("Club"))
            Loop
            con.Close()
        Next

    End Sub

    Public Sub MergeSort(ByRef data As String())
        'This sub splits up the input data

        Dim currentSize As Integer
        Dim left As Integer

        currentSize = 1
        While currentSize <= data.Length - 1
            left = 0
            While left < data.Length - 1
                Dim middle As Integer = left + currentSize - 1
                Dim right As Integer = Math.Min(left + 2 * currentSize - 1, data.Length - 1)

                Merge(data, left, middle, right)
                left += 2 * currentSize
            End While
            currentSize = 2 * currentSize
        End While
    End Sub

    Private Sub Merge(ByRef data As String(), ByVal left As Integer, ByVal mid As Integer, ByVal right As Integer)
        'This sub compares and orders the values of the items

        Dim i As Integer
        Dim j As Integer
        Dim k As Integer
        Dim Number1 As String = mid - left + 1
        Dim Number2 As String = right - mid
        Dim LeftPointer As String() = New String(Number1 - 1) {}
        Dim RightPointer As String() = New String(Number2 - 1) {}

        For i = 0 To Number1 - 1
            LeftPointer(i) = data(left + i)
        Next

        For j = 0 To Number2 - 1
            RightPointer(j) = data(mid + 1 + j)
        Next

        i = 0
        j = 0
        k = left

        While i < Number1 AndAlso j < Number2
            If LeftPointer(i) <= RightPointer(j) Then
                data(k) = LeftPointer(i)
                i += 1
            Else
                data(k) = RightPointer(j)
                j += 1
            End If

            k += 1
        End While

        While i < Number1
            data(k) = LeftPointer(i)
            i += 1
            k += 1
        End While

        While j < Number2
            data(k) = RightPointer(j)
            j += 1
            k += 1
        End While
    End Sub

End Class

Imports ADOX
Imports System.IO
Imports System.Data.OleDb
Public Class Add_New_Child
    Dim cat As Catalog = New Catalog()
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
    Dim con As New OleDbConnection(ConnectionString)
    Dim cmd As New OleDbCommand
    Dim reader As OleDbDataReader
    Dim SQLCommand As String
    Dim AddFamilyID, AddClub, AddStaffID, AddChildName, AddParentName, AddAddress, AddFamilyGP, AddAge, AddContactNumber As String


    Sub btnAddNewChildToSystem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNewChildToSystem.Click
        'This sub adds a new child, along with their parent to the database

        Dim NewParentID As String
        Dim NewChildID As String
        AddChildName = txtChildName.Text
        AddAge = txtAge.Text
        AddStaffID = txtStaffID.Text
        AddFamilyID = txtFamilyID.Text
        AddParentName = txtParentName.Text
        AddAddress = txtAddress.Text
        AddFamilyGP = txtFamilyGP.Text
        AddContactNumber = txtContactNumber.Text



        If chkCrontonChild.Checked = True And chkFarnworthChild.Checked = True Then
            AddClub = ""
        Else
            AddClub = ""
            If chkCrontonChild.Checked = True Then
                AddClub = "Cronton"
            End If
            If chkFarnworthChild.Checked = True Then
                AddClub = "Farnworth"
            End If
        End If

        'Validation to check the user has inputted information correctly
        If AddChildName.Length = 0 Then
            MsgBox("Please enter the Child Name correctly")
        ElseIf AddAge.ToString.Length > 2 Then
            MsgBox("Please enter a correct age")
        ElseIf AddAge.ToString.Length = 0 Then
            MsgBox("Please enter a correct age")
        ElseIf AddStaffID.Length = 0 Then
            MsgBox("Please enter a valid Staff ID")
        ElseIf IsNumeric(AddStaffID) = False Then
            MsgBox("Please enter a valid Staff ID")
        ElseIf AddFamilyID.Length = 0 Then
            MsgBox("Please enter the Family ID correctly")
        ElseIf AddParentName.Length = 0 Then
            MsgBox("Please enter the Parent Name correctly")
        ElseIf AddAddress.Length = 0 Then
            MsgBox("Please enter the address correctly")
        ElseIf AddFamilyGP.Length = 0 Then
            MsgBox("Please enter the GP's Name correctly")
        ElseIf AddClub.Length = 0 Then
            MsgBox("Please tick one club")
        ElseIf IsNumeric(AddAge) = False Then
            MsgBox("Please enter a number for the age")
        ElseIf IsNumeric(AddContactNumber) = False Then
            MsgBox("Please eneter a correct contact number")
        ElseIf AddContactNumber.ToString.Length < 7 Then
            MsgBox("That contact number is too short")
        ElseIf AddContactNumber.ToString.Length > 11 Then
            MsgBox("That contact number is too long")
        Else
            con.Close()
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & AddStaffID & ""
            cmd.ExecuteNonQuery()
            reader = cmd.ExecuteReader
            If reader.Read Then
                con.Close()
                con.Open()
                cmd.Connection = con
                cmd.CommandText = "Select * FROM ParentLink WHERE FamilyID = '" & AddFamilyID & "'"
                cmd.ExecuteNonQuery()
                reader = cmd.ExecuteReader
                If reader.Read Then
                    MsgBox("That Family ID already exists")
                Else
                    con.Close()
                    con.Open()
                    cmd.Connection = con
                    NewChildID = GenerateChildID()
                    NewParentID = GenerateParentID()
                    cmd.CommandText = "INSERT INTO ParentLink(FamilyID, ChildNameOne) VALUES ('" & AddFamilyID & "','" & AddChildName & "');"
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = "INSERT INTO Register(ChildID, Club) VALUES ('" & NewChildID & "','" & AddClub & "');"
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = "INSERT INTO PaymentLog(ParentID, ParentName, PaymentsOwed) VALUES('" & NewParentID & "','" & AddParentName & "', '0');"
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = "INSERT INTO ChildInformation(ChildID, ChildName, Age, FamilyID, StaffID, Club) VALUES ('" & NewChildID & "','" & AddChildName & "','" & AddAge & "'," &
                    "'" & AddFamilyID & "','" & AddStaffID & "','" & AddClub & "');"
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = "INSERT INTO ParentInformation(ParentName, Address, ParentID, ContactNumber, FamilyGP, FamilyID, PaymentsOwed) VALUES ('" & AddParentName & "','" & AddAddress & "'," &
                    "'" & NewParentID & "','" & AddContactNumber & "','" & AddFamilyGP & "','" & AddFamilyID & "', '0');"
                    cmd.ExecuteNonQuery()
                    con.Close()
                    MsgBox("Added")
                End If
            Else
                MsgBox("That Staff ID doesn't exist")
            End If

        End If

        Child_Info.ChildInfoRefresh()
    End Sub
    Function GenerateParentID()
        'Generates a Unique parent ID using their name

        Dim NewParentID As String
        Dim ParentID As String
        Dim clash As Boolean = True
        Dim value As Integer
        con.Close()
        con.Open()
        ParentID = AddParentName
        value = 0
        NewParentID = ParentID & value

        'This adds a number onto the end of the parent name and checks if this is already in the database.
        'If so, it tries the next number up and so on.

        Do While clash = True
            con.Close()
            con.Open()
            value = value + 1
            NewParentID = ParentID & value
            cmd.CommandText = "Select ParentID FROM PaymentLog WHERE ParentID = '" & NewParentID & "';"
            cmd.ExecuteNonQuery()
            reader = cmd.ExecuteReader
            If reader.Read Then
            Else
                clash = False
            End If
        Loop
        con.Close()
        con.Open()
        Return NewParentID
    End Function

    Function GenerateChildID()
        'Generates a unique child ID for the child

        Dim NewChildID As String
        Dim ChildID As String
        Dim clash As Boolean = True
        Dim value As Integer
        con.Close()
        con.Open()
        ChildID = AddChildName
        value = 0
        NewChildID = ChildID & value
        Do While clash = True
            con.Close()
            con.Open()
            value = value + 1
            NewChildID = ChildID & value
            cmd.CommandText = "Select ChildID FROM Register WHERE ChildID = '" & NewChildID & "';"
            cmd.ExecuteNonQuery()
            reader = cmd.ExecuteReader
            If reader.Read Then
            Else
                clash = False
            End If
        Loop
        con.Close()
        con.Open()
        Return NewChildID
    End Function
End Class

Imports ADOX
Imports System.IO
Imports System.Data.OleDb
Public Class Parent_Info
    Dim cat As Catalog = New Catalog()
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
    Dim con As New OleDbConnection(ConnectionString)
    Dim cmd As New OleDbCommand
    Dim reader As OleDbDataReader
    Dim SQLCommand As String = "SELECT * FROM ParentInformation"
    Dim ParentName, Address, FamilyGP, FamilyID, PaymentsOwed, ContactNumber As String

    Private Sub Parent_Info_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'This calls the sub to load the table into the grid

        'This sets the payments owed column to display as a currency
        grdParentInfo.Columns(4).DefaultCellStyle.Format = "c"
        ParentInfoRefresh()
    End Sub

    Sub ParentInfoRefresh()
        'This sub populates the grid

        grdParentInfo.Rows.Clear()
        con.Open()
        cmd.Connection = con
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader

        Do While reader.Read()
            Dim n As Integer = grdParentInfo.Rows.Add
            grdParentInfo.Rows.Item(n).Cells(0).Value = (reader("ParentName"))
            grdParentInfo.Rows.Item(n).Cells(1).Value = (reader("Address"))
            grdParentInfo.Rows.Item(n).Cells(2).Value = (reader("ParentID"))
            grdParentInfo.Rows.Item(n).Cells(3).Value = (reader("ContactNumber"))
            grdParentInfo.Rows.Item(n).Cells(4).Value = (reader("PaymentsOwed"))
            grdParentInfo.Rows.Item(n).Cells(5).Value = (reader("FamilyGP"))
            grdParentInfo.Rows.Item(n).Cells(6).Value = (reader("FamilyID"))

        Loop
        con.Close()
    End Sub

    Private Sub btnEditExistingParent_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditExistingParent.Click
        'This sub allows the user to edit an existing parents details

        ParentName = txtParentName.Text
        Address = txtAddress.Text
        ContactNumber = txtContactNumber.Text
        PaymentsOwed = txtPaymentsOwed.Text
        FamilyGP = txtFamilyGP.Text


        If ParentName.Length = 0 Then
            MsgBox("Please enter Parent Name correctly")
        ElseIf Address.Length = 0 Then
            MsgBox("Please enter a valid address")
        ElseIf IsNumeric(ContactNumber) = False Then
            MsgBox("Please enter a valid number for the contact number")
        ElseIf ContactNumber.Length > 11 Then
            MsgBox("That contact number is too long")
        ElseIf ContactNumber.Length < 7 Then
            MsgBox("That contact number is too short")
        ElseIf IsNumeric(PaymentsOwed) = False Then
            MsgBox("Please enter a number for the payments owed")
        ElseIf PaymentsOwed.Length = 0 Then
            MsgBox("That payment owed is not valid")
        ElseIf FamilyGP.Length = 0 Then
            MsgBox("Please enter the family GP correctly")
        Else
            con.Close()
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "Select * FROM ParentInformation WHERE ParentName = '" & ParentName & "'"
            cmd.ExecuteNonQuery()
            reader = cmd.ExecuteReader
            If reader.Read Then
                con.Close()
                con.Open()
                cmd.Connection = con
                cmd.CommandText = "UPDATE ParentInformation SET Address = '" & Address & "', ContactNumber = '" & ContactNumber & "', PaymentsOwed = '" & PaymentsOwed & "', FamilyGP = '" & FamilyGP & "'," &
                "WHERE ParentName = '" & ParentName & "'"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "UPDATE PaymentLog SET PaymentsOwed = '" & PaymentsOwed & "' WHERE ParentName = '" & ParentName & "'"
                cmd.ExecuteNonQuery()
                con.Close()
                MsgBox("Edited")
            Else
                MsgBox("That Parent Name doesn't exist")
            End If

        End If

            con.Close()
            ParentInfoRefresh()
            Current_Reg_And_Pay.CurrentRegAndPayRefresh()
    End Sub

    Private Sub btnSearchParent_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchParent.Click
        'Allows the user to search for a parents records by their name

        ParentName = txtParentName.Text
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT * FROM ParentInformation WHERE ParentName = '" & ParentName & "'"
        cmd.ExecuteNonQuery()
        reader = cmd.ExecuteReader
        grdParentInfo.Rows.Clear()
        If reader.Read Then
            Dim n As Integer = grdParentInfo.Rows.Add
            grdParentInfo.Rows.Item(n).Cells(0).Value = (reader("ParentName"))
            grdParentInfo.Rows.Item(n).Cells(1).Value = (reader("Address"))
            grdParentInfo.Rows.Item(n).Cells(2).Value = (reader("ParentID"))
            grdParentInfo.Rows.Item(n).Cells(3).Value = (reader("ContactNumber"))
            grdParentInfo.Rows.Item(n).Cells(4).Value = (reader("PaymentsOwed"))
            grdParentInfo.Rows.Item(n).Cells(5).Value = (reader("FamilyGP"))
            grdParentInfo.Rows.Item(n).Cells(6).Value = (reader("FamilyID"))
        Else
            con.Close()
            ParentInfoRefresh()
            MsgBox("Parent Name doesn't exist")
        End If
        con.Close()
    End Sub


    Private Sub btnParentInfoRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParentInfoRefresh.Click
        'Runs the refresh sub

        ParentInfoRefresh()
    End Sub
End Class

Imports ADOX
Imports System.IO
Imports System.Data.OleDb
Public Class Current_Reg_And_Pay
    Dim cat As Catalog = New Catalog()
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
    Dim con As New OleDbConnection(ConnectionString)
    Dim cmd As New OleDbCommand
    Dim reader As OleDbDataReader
    Dim SQLCommand As String
    Dim Club As String = Login.Club
    Dim ChildID, CurrentTime, ParentName, ChildName, AmountPaid, PaymentMethod, ParentID, FamilyID, CurrentPaymentsOwed, NewPaymentsOwed, TransactionDate As String

    Sub CurrentRegAndPayRefresh()
        'This sub fills the grid with the table information

        GrdRegister.Rows.Clear()
        'This command takes the child name from child information and put it into the register table, so that the user can search for the child by their name.
        'It also only populates the register with the children which go the the club that the admin chose in the club selection screen, or whichever club the user works at
        SQLCommand = "SELECT Register.*, ChildInformation.ChildName FROM Register INNER JOIN ChildInformation ON Register.ChildID = ChildInformation.ChildID WHERE Register.Club = '" & Club & "'"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader

        Do While reader.Read()
            Dim n As Integer = GrdRegister.Rows.Add
            GrdRegister.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
            GrdRegister.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
            GrdRegister.Rows.Item(n).Cells(2).Value = (reader("TimeIn"))
            GrdRegister.Rows.Item(n).Cells(3).Value = (reader("TimeOut"))
            GrdRegister.Rows.Item(n).Cells(4).Value = (reader("Present"))
        Loop
        con.Close()

        GrdPaymentLog.Rows.Clear()
        SQLCommand = "SELECT * FROM PaymentLog "
        con.Open()
        cmd.Connection = con
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader

        Do While reader.Read()
            Dim n As Integer = GrdPaymentLog.Rows.Add
            GrdPaymentLog.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
            GrdPaymentLog.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
            GrdPaymentLog.Rows.Item(n).Cells(2).Value = (reader("PaymentsOwed"))
            GrdPaymentLog.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
            GrdPaymentLog.Rows.Item(n).Cells(4).Value = (reader("PaymentMethod"))
        Loop
        con.Close()
    End Sub

    Private Sub Current_Reg_And_Pay_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'This sub calls the refresh sub

        'Formats the data grids to show the time in the below format and also sets the money columns to display as a currency
        GrdRegister.Columns(2).DefaultCellStyle.Format = "HH:mm:ss"
        GrdRegister.Columns(3).DefaultCellStyle.Format = "HH:mm:ss"
        GrdPaymentLog.Columns(2).DefaultCellStyle.Format = "c"
        GrdPaymentLog.Columns(3).DefaultCellStyle.Format = "c"
        CurrentRegAndPayRefresh()
    End Sub

    Private Sub btnDeleteChildRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteChildRegister.Click
        'Gives the user a quick link to the child information table, if edits need to be made

        Child_Info.Show()
    End Sub

    Private Sub btnCurrentRegAndPayRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCurrentRegAndPayRefresh.Click
        'Button calls the refresh sub

        CurrentRegAndPayRefresh()
    End Sub

    Private Sub btnMarkIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMarkIn.Click
        'This sub will mark the child that is selected as present in the club and prints in the data base the time that he arrived

        ChildID = txtRegChildID.Text
        'This gets the current system time
        CurrentTime = Now.ToLongTimeString

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT ChildID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
        cmd.ExecuteNonQuery()
        reader = cmd.ExecuteReader

        If reader.Read Then
            con.Close()
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "UPDATE Register SET TimeIn = '" & CurrentTime & "', Present = True WHERE ChildID = '" & ChildID & "'"
            cmd.ExecuteNonQuery()
            con.Close()

            CurrentRegAndPayRefresh()
            MsgBox("Marked In")
        Else
            MsgBox("Child ID doesn't exist")
        End If

        con.Close()
    End Sub

    Private Sub btnMarkOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMarkOut.Click
        'This sub marks the selected child as no longer present and prints the time of depature in the table.
        'The Parent that is linked to the child has the set amount of £8.50 added to the amount owed to the club

        ChildID = txtRegChildID.Text
        CurrentTime = Now.ToLongTimeString

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT ChildID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
        cmd.ExecuteNonQuery()
        reader = cmd.ExecuteReader

        If reader.Read Then
            con.Close()
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "UPDATE Register SET TimeOut = '" & CurrentTime & "', Present = False WHERE ChildID = '" & ChildID & "'"
            cmd.ExecuteNonQuery()
            con.Close()

            con.Open()
            cmd.Connection = con
            cmd.CommandText = "SELECT FamilyID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
            cmd.ExecuteNonQuery()
            reader = cmd.ExecuteReader
            reader.Read()
            FamilyID = reader("FamilyID")
            con.Close()

            con.Open()
            cmd.Connection = con
            cmd.CommandText = "SELECT ParentID FROM ParentInformation WHERE FamilyID = '" & FamilyID & "'"
            cmd.ExecuteNonQuery()
            reader = cmd.ExecuteReader
            reader.Read()
            ParentID = reader("ParentID")
            con.Close()

            con.Open()
            cmd.Connection = con
            cmd.CommandText = "SELECT PaymentsOwed FROM PaymentLog WHERE ParentID = '" & ParentID & "'"
            cmd.ExecuteNonQuery()
            reader = cmd.ExecuteReader
            reader.Read()
            CurrentPaymentsOwed = reader("PaymentsOwed")
            con.Close()

            NewPaymentsOwed = CurrentPaymentsOwed + (8.5)

            con.Open()
            cmd.Connection = con
            cmd.CommandText = "UPDATE PaymentLog SET PaymentsOwed = '" & NewPaymentsOwed & "' WHERE ParentID = '" & ParentID & "'"
            cmd.ExecuteNonQuery()
            con.Close()

            con.Open()
            cmd.Connection = con
            cmd.CommandText = "UPDATE ParentInformation SET PaymentsOwed = '" & NewPaymentsOwed & "' WHERE ParentID = '" & ParentID & "'"
            cmd.ExecuteNonQuery()
            con.Close()

            CurrentRegAndPayRefresh()
            MsgBox("Marked Out")
        Else
            MsgBox("Child ID doesn't exist")
        End If
        con.Close()
    End Sub

    Private Sub btnPaymentLogSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPaymentLogSearch.Click
        'Allows the user to search for a parents information by their name

        ParentName = txtParentName.Text

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT * FROM PaymentLog WHERE ParentName = '" & ParentName & "'"
        cmd.ExecuteNonQuery()
        reader = cmd.ExecuteReader

        GrdPaymentLog.Rows.Clear()
        If reader.Read Then
            Dim n As Integer = GrdPaymentLog.Rows.Add
            GrdPaymentLog.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
            GrdPaymentLog.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
            GrdPaymentLog.Rows.Item(n).Cells(2).Value = (reader("PaymentsOwed"))
            GrdPaymentLog.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
            GrdPaymentLog.Rows.Item(n).Cells(4).Value = (reader("PaymentMethod"))
        Else
            MsgBox("Parent Name doesn't exist")
        End If
        con.Close()
    End Sub

    Private Sub btnRegisterSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegisterSearch.Click
        'This allows the user to search for the child information by the child name

        ChildName = txtChildName.Text

        con.Open()
        cmd.Connection = con
        'Cross table SQL - Grade A
        SQLCommand = "SELECT Register.*, ChildInformation.ChildName FROM Register INNER JOIN ChildInformation ON Register.ChildID = ChildInformation.ChildID" &
        "WHERE Register.Club = '" & Club & "' AND ChildName = '" & ChildName & "'"
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader

        GrdRegister.Rows.Clear()
        If reader.Read() Then
            Dim n As Integer = GrdRegister.Rows.Add
            GrdRegister.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
            GrdRegister.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
            GrdRegister.Rows.Item(n).Cells(2).Value = (reader("TimeIn"))
            GrdRegister.Rows.Item(n).Cells(3).Value = (reader("TimeOut"))
            GrdRegister.Rows.Item(n).Cells(4).Value = (reader("Present"))
        Else
            MsgBox("Child Name doesn't exist")
        End If
        con.Close()
    End Sub

    Private Sub btnMakePayment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMakePayment.Click
        'This sub allows the parent to pay any amount of the money the owe to the club
        'It updates the payment log, writing the new amount owed, amount paid and the date of the transaction
        'It also adds the payment to the payment history table

        ParentID = txtParentID.Text
        AmountPaid = txtAmountPaid.Text
        'Gets the current system date
        TransactionDate = String.Format("{0:dd/MM/yyyy}", DateTime.Now)

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT * FROM ParentInformation WHERE ParentID = '" & ParentID & "'"
        cmd.ExecuteNonQuery()
        reader = cmd.ExecuteReader
        If reader.Read Then

            If IsNumeric(AmountPaid) = True Then


                If (chkCard.Checked And chkCash.Checked) Or (chkCard.Checked And chkCheque.Checked) Or (chkCash.Checked And chkCheque.Checked) Or (chkCard.Checked And chkCash.Checked And chkCheque.Checked) = True Then
                    PaymentMethod = ""
                Else
                    If chkCard.Checked Or chkCash.Checked Or chkCheque.Checked = True Then
                        If chkCard.Checked = True Then
                            PaymentMethod = "Card"
                        End If
                        If chkCash.Checked = True Then
                            PaymentMethod = "Cash"
                        End If
                        If chkCheque.Checked = True Then
                            PaymentMethod = "Cheque"
                        End If
                    Else
                        PaymentMethod = ""
                    End If
                End If

                con.Close()
                con.Open()
                cmd.Connection = con
                cmd.CommandText = "SELECT * FROM PaymentHistory WHERE ParentID = '" & ParentID & "' AND TransactionDate = '" & TransactionDate & "'"
                cmd.ExecuteNonQuery()
                reader = cmd.ExecuteReader
                If reader.Read Then
                    MsgBox("You have already made a payment today")
                Else

                    If PaymentMethod.Length <> 0 Then

                        con.Close()
                        con.Open()
                        cmd.Connection = con
                        cmd.CommandText = "SELECT PaymentsOwed FROM PaymentLog WHERE ParentID = '" & ParentID & "'"
                        cmd.ExecuteNonQuery()
                        reader = cmd.ExecuteReader
                        reader.Read()
                        CurrentPaymentsOwed = reader("PaymentsOwed")
                        con.Close()

                        NewPaymentsOwed = CurrentPaymentsOwed - AmountPaid

                        con.Open()
                        cmd.Connection = con
                        cmd.CommandText = "UPDATE PaymentLog SET PaymentsOwed = '" & NewPaymentsOwed & "', AmountPaid = '" & AmountPaid & "', PaymentMethod = '" & PaymentMethod & "' WHERE ParentID = '" & ParentID & "'"
                        cmd.ExecuteNonQuery()
                        con.Close()

                        con.Open()
                        cmd.Connection = con
                        cmd.CommandText = "INSERT INTO PaymentHistory(ParentID, AmountPaid, TransactionDate) VALUES ('" & ParentID & "', '" & AmountPaid & "', '" & TransactionDate & "');"
                        cmd.ExecuteNonQuery()
                        con.Close()

                        con.Open()
                        cmd.Connection = con
                        cmd.CommandText = "UPDATE ParentInformation SET PaymentsOwed = '" & NewPaymentsOwed & "' WHERE ParentID = '" & ParentID & "'"
                        cmd.ExecuteNonQuery()
                        con.Close()

                        CurrentRegAndPayRefresh()
                        MsgBox("Payment Made")
                    Else
                        MsgBox("Please tick only one of the boxes")
                    End If

                End If
            Else
                MsgBox("Please enter a number in 'Amount Paid'")
            End If
        Else
            MsgBox("Parent ID doesn't exist")
        End If
        con.Close()
    End Sub

    Private Sub bntClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bntClear.Click
        'This button clears the register and the payment log, but leaves in the people, so it can be used again the next day

        TransactionDate = String.Format("{0:dd/MM/yyyy}", DateTime.Now)

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "UPDATE PaymentLog SET AmountPaid = NULL, PaymentMethod = NULL"
        cmd.ExecuteNonQuery()
        con.Close()

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "UPDATE Register SET TimeIn = NULL, TimeOut = NULL"
        cmd.ExecuteNonQuery()
        con.Close()

        CurrentRegAndPayRefresh()
    End Sub
End Class

Imports ADOX
Imports System.IO
Imports System.Data.OleDb
Public Class Payment_History
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
    Dim cat As Catalog = New Catalog()
    Dim SQLCommand As String
    Dim con As New OleDbConnection(ConnectionString)
    Dim cmd As New OleDbCommand
    Dim reader As OleDbDataReader
    Dim ParentName As String
    Dim ParentID As String
    Dim TotalPaid As Decimal

    Private Sub Payment_History_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Formats the columns and calls the refresh sub

        grdPaymentHistory.Columns(2).DefaultCellStyle.Format = "dd/MM/yyyy"
        grdPaymentHistory.Columns(3).DefaultCellStyle.Format = "c"
        PaymentHistoryRefresh()
    End Sub

    Sub PaymentHistoryRefresh()
        'Refreshes the grid

        grdPaymentHistory.Rows.Clear()
        SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog ON PaymentHistory.ParentID = PaymentLog.ParentID"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader

        Do While reader.Read()
            Dim n As Integer = grdPaymentHistory.Rows.Add
            grdPaymentHistory.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
            grdPaymentHistory.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
            grdPaymentHistory.Rows.Item(n).Cells(2).Value = (reader("TransactionDate"))
            grdPaymentHistory.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
        Loop
        con.Close()
    End Sub

    Private Sub btnPaymentLogSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPaymentLogSearch.Click
        'Allows the user to search for all the payments made by the parent name

        ParentName = txtParentName.Text
        grdPaymentHistory.Rows.Clear()
        SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog ON PaymentHistory.ParentID = PaymentLog.ParentID WHERE PaymentLog.ParentName = '" & ParentName & "'"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader

        If reader.Read() Then
            Dim n As Integer = grdPaymentHistory.Rows.Add
            grdPaymentHistory.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
            grdPaymentHistory.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
            grdPaymentHistory.Rows.Item(n).Cells(2).Value = (reader("TransactionDate"))
            grdPaymentHistory.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
        Else
            con.Close()
            PaymentHistoryRefresh()
            MsgBox("No transactions made by that Parent Name")
        End If
        con.Close()
    End Sub

    Private Sub btnTransactionDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSortByTransactionDate.Click
        'This sub orders the transactions by date, so the user can see all of the transactions made on a particlar day

        grdPaymentHistory.Rows.Clear()

        'Aggregate SQL function - Grade A
        SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog ON PaymentHistory.ParentID = PaymentLog.ParentID ORDER BY PaymentHistory.TransactionDate ASC"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader

        Do While reader.Read()
            Dim n As Integer = grdPaymentHistory.Rows.Add
            grdPaymentHistory.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
            grdPaymentHistory.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
            grdPaymentHistory.Rows.Item(n).Cells(2).Value = (reader("TransactionDate"))
            grdPaymentHistory.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
        Loop
        con.Close()
    End Sub

    Private Sub btnSortByCustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSortByCustomer.Click
        'Calls the refresh sub

        PaymentHistoryRefresh()
    End Sub

    Private Sub btnTotalPaid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTotalPaid.Click
        'This sub totals all the payments made by a parent ID

        ParentID = txtParentID.Text

        SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog ON PaymentHistory.ParentID = PaymentLog.ParentID WHERE PaymentLog.ParentID = '" & ParentID & "'"
        con.Close()
        con.Open()
        cmd.Connection = con
        cmd.CommandText = (SQLCommand)
        reader = cmd.ExecuteReader

        If reader.Read() Then

            'Aggregate SQL function - Grade A
            SQLCommand = "SELECT SUM(AmountPaid) FROM PaymentHistory WHERE ParentID = '" & ParentID & "'"
            con.Close()
            con.Open()
            cmd.Connection = con
            cmd.CommandText = (SQLCommand)
            reader = cmd.ExecuteReader
            reader.Read()
            'Sets the textbox to currency format
            txtTotalPaid.Text = FormatCurrency(reader.Item(0))
            con.Close()

        Else
            MsgBox("No transactions made by that Parent ID")
        End If
        con.Close()
    End Sub
End Class