Jw0pMgS4

1Imports ADOX
2Imports System.IO
3Imports System.Data.OleDb
4
5
6Public Class Login
7    Public Club As String
8    Public AdminAccess As Boolean
9    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
10
11    Private Sub Login_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
12        '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.
13
14        Dim cat As Catalog = New Catalog()
15        Dim SQLCommand As String
16        Dim con As New OleDbConnection(ConnectionString)
17        Dim cmd As New OleDbCommand
18        Dim HashedAdminPWord As String = HashPassword("Password123")
19
20
21        'This checks if the file already exists
22        If File.Exists("N:\Whizzkids\WhizzkidsDB.accdb") = True Then
23        Else
24            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
25                        "Data Source=N:\Whizzkids\WhizzkidsDB.accdb;")
26
27            'This creates the table 'Staff' in the database
28            SQLCommand = "CREATE TABLE Staff(StaffID AUTOINCREMENT PRIMARY KEY, StaffName VarChar(255), Username VarChar(255)," &
29            "PWord VarChar(255), Club VarChar(255), IsAdmin Bit);"
30            con.Open()
31            cmd.Connection = con
32            cmd.CommandText = (SQLCommand)
33            Try
34                cmd.ExecuteNonQuery()
35            Catch ex As Exception
36            End Try
37
38            'This inserts the default information so you can log on the first time
39            cmd.CommandText = "INSERT INTO Staff(StaffName, Username, PWord, Club, IsAdmin) VALUES ('AdminName', 'Admin'," &
40            "'" & HashedAdminPWord & "', '', True);"
41            cmd.ExecuteNonQuery()
42            con.Close()
43
44            SQLCommand = "CREATE TABLE ParentLink(FamilyID VarChar(255) PRIMARY KEY, ChildNameOne VarChar(255), ChildNameTwo VarChar(255)," &
45            "ChildNameThree VarChar(255), ChildNameFour VarChar(255));"
46            con.Open()
47            cmd.Connection = con
48            cmd.CommandText = (SQLCommand)
49            Try
50                cmd.ExecuteNonQuery()
51            Catch ex As Exception
52            End Try
53            con.Close()
54
55            SQLCommand = "CREATE TABLE Register(ChildID VarChar(255) PRIMARY KEY, Present Bit, TimeIn Time, TimeOut Time, Club VarChar(255));"
56            con.Open()
57            cmd.Connection = con
58            cmd.CommandText = (SQLCommand)
59            Try
60                cmd.ExecuteNonQuery()
61            Catch ex As Exception
62            End Try
63            con.Close()
64
65            SQLCommand = "CREATE TABLE PaymentLog(ParentID VarChar(255) PRIMARY KEY, ParentName VarChar(255), PaymentsOwed Decimal(5,2)," &
66            "AmountPaid Decimal(5,2), PaymentMethod VarChar(255));"
67            con.Open()
68            cmd.Connection = con
69            cmd.CommandText = (SQLCommand)
70            Try
71                cmd.ExecuteNonQuery()
72            Catch ex As Exception
73            End Try
74            con.Close()
75
76            SQLCommand = "CREATE TABLE PaymentHistory(ParentID VarChar(255), AmountPaid Decimal(5,2), TransactionDate VarChar(255)," &
77            "PRIMARY KEY (ParentID, TransactionDate));"
78            con.Open()
79            cmd.Connection = con
80            cmd.CommandText = (SQLCommand)
81            Try
82                cmd.ExecuteNonQuery()
83            Catch ex As Exception
84            End Try
85            con.Close()
86
87            SQLCommand = "CREATE TABLE ChildInformation(ChildID VarChar(255), ChildName VarChar(255), Age SmallInt, FamilyID VarChar(255)," &
88            "StaffID Integer, Club VarChar(255), PRIMARY KEY (ChildName, FamilyID), FOREIGN KEY (StaffID) REFERENCES Staff(StaffID)," &
89            "FOREIGN KEY (FamilyID) REFERENCES ParentLink(FamilyID), FOREIGN KEY (ChildID) REFERENCES Register(ChildID));"
90            con.Open()
91            cmd.Connection = con
92            cmd.CommandText = (SQLCommand)
93            Try
94                cmd.ExecuteNonQuery()
95            Catch ex As Exception
96            End Try
97            con.Close()
98
99            SQLCommand = "CREATE TABLE ParentInformation(ParentName VarChar(255), ParentID VarChar(255), FamilyID VarChar(255)," &
100            "ContactNumber Float, Address VarChar(255), PaymentsOwed Decimal(5,2), FamilyGP VarChar(255), PRIMARY KEY (ParentName, Address)," &
101            "FOREIGN KEY (FamilyID) REFERENCES ParentLink(FamilyID), FOREIGN KEY (ParentID) REFERENCES PaymentLog(ParentID));"
102            con.Open()
103            cmd.Connection = con
104            cmd.CommandText = (SQLCommand)
105            Try
106                cmd.ExecuteNonQuery()
107            Catch ex As Exception
108            End Try
109            con.Close()
110
111
112        End If
113
114
115
116
117        'Creation of title
118        Dim lblTitle As New Label
119        With lblTitle
120            .Size = New Size(300, 50)
121            .Location = New Size(80, 20)
122            .Text = "Whizzkids Staff Login"
123            .Font = New Font("Cambria", 22)
124        End With
125        'Creation of Username Box
126        Dim txtUsername As New TextBox
127        With txtUsername
128            .Size = New Size(270, 20)
129            .Location = New Size(81, 100)
130            .Text = "Username"
131            .ForeColor = Color.DimGray
132            .Font = New Font("Calbri", 18)
133            .Name = "UsernameBox"
134        End With
135        'Creation of Password Box
136        Dim txtPassword As New TextBox
137        With txtPassword
138            .Size = New Size(270, 20)
139            .Location = New Size(81, 157)
140            .Text = "Password"
141            .ForeColor = Color.DimGray
142            .Font = New Font("Calbri", 18)
143            .Name = "PasswordBox"
144        End With
145        'Creation of Login Button
146        Dim btnLogin As New Button
147        With btnLogin
148            .Size = New Size(120, 50)
149            .Location = New Point(160, 230)
150            .Text = "Login"
151            .Name = "LoginButton"
152        End With
153
154        'Adding all the runtime objects
155        Me.Controls.Add(lblTitle)
156        Me.Controls.Add(txtUsername)
157        Me.Controls.Add(txtPassword)
158        Me.Controls.Add(btnLogin)
159
160        'Runs the subs when the boxes and buttons are clicked
161        AddHandler btnLogin.Click, AddressOf LoginClick
162        AddHandler txtUsername.Click, AddressOf ClickMouseUserName
163        AddHandler txtPassword.Click, AddressOf ClickMousePassword
164
165    End Sub
166    Sub ClickMouseUserName()
167        'This sub runs when the Username textbox is clicked. 
168
169        'This calls the class to change the password text to make it hidden
170        Dim txtpassword As New StarText
171        'If the Username textbox contains 'Username' when it is clicked, that is deleted and the text colour is set to black instead of grey
172        If Me.Controls("UsernameBox").Text = "Username" Then
173            Me.Controls("UsernameBox").Text = ""
174            Me.Controls("UsernameBox").ForeColor = Color.Black
175        End If
176        'This places the 'Password' back in the Password textbox if it is empty when the Username box is clicked
177        If Me.Controls("PasswordBox").Text = "" Then
178            Me.Controls("PasswordBox").Text = "Password"
179            Me.Controls("PasswordBox").ForeColor = Color.DimGray
180            txtpassword.UnstarPassword(Me.Controls("PasswordBox"))
181        End If
182    End Sub
183
184    Sub ClickMousePassword()
185        'This sub does the same as the sub above, but for the other respective textboxes
186
187        Dim txtpassword As New StarText
188        If Me.Controls("PasswordBox").Text = "Password" Then
189            Me.Controls("PasswordBox").Text = ""
190            Me.Controls("PasswordBox").ForeColor = Color.Black
191        End If
192        If Me.Controls("UsernameBox").Text = "" Then
193            Me.Controls("UsernameBox").Text = "Username"
194            Me.Controls("UsernameBox").ForeColor = Color.DimGray
195        End If
196        txtpassword.StarPassword(Me.Controls("PasswordBox"))
197    End Sub
198    Sub LoginClick()
199        'This sub validates the login with the Staff table 
200
201        Dim LoginUsername As String = ""
202        Dim LoginPassword As String = ""
203        Dim LoginCorrect As Boolean
204        Dim ReaderAdmin As Boolean = False
205        Dim ReaderClub As Boolean = False
206        Dim cat As Catalog = New Catalog()
207        Dim con As New OleDbConnection(ConnectionString)
208        Dim cmd As New OleDbCommand
209
210        'This searches the staff table for the correct Username and Password and gives the user, if an Admin, the option to select 
211        'which club they are at
212        'Reading and writing from files - Grade B
213        cmd.Connection = con
214        cmd.CommandText = "SELECT Username, Pword, IsAdmin, Club FROM Staff WHERE Username = '" & Me.Controls("UsernameBox").Text & "'" &
215        "AND PWord = '" & HashPassword(Me.Controls("PasswordBox").Text) & "'"
216        con.Open()
217        Dim reader As OleDbDataReader = cmd.ExecuteReader()
218        Do While reader.Read()
219            LoginUsername = reader("Username")
220            LoginPassword = reader("PWord")
221            ReaderAdmin = reader("IsAdmin")
222        Loop
223        If LoginUsername <> "" And LoginPassword <> "" Then
224            LoginCorrect = True
225            If ReaderAdmin = True Then
226                AdminAccess = True
227                Club_Selection.Show()
228            Else
229                Main_Menu.Show()
230            End If
231            Me.Hide()
232        Else
233            MsgBox("Login details incorrect. Please try again.")
234            LoginCorrect = False
235        End If
236
237
238        reader.Close()
239        con.Close()
240
241
242
243    End Sub
244    Public Shared Function HashPassword(ByVal HPassword As String) As String
245        'This sub is the hashing algorithm. It hashes the password when stored and hashes it when a login is attempted, 
246        'so the true password is never visible anywhere
247        'Hashing - Grade A
248
249        Dim HashAlgorithm As New System.Security.Cryptography.MD5CryptoServiceProvider()
250        Dim PasswordBytes() As Byte = System.Text.Encoding.ASCII.GetBytes(HPassword)
251
252        PasswordBytes = HashAlgorithm.ComputeHash(PasswordBytes)
253
254        Dim HashedPassword As String = ""
255        Dim b As Byte
256
257        For Each b In PasswordBytes
258            HashedPassword += b.ToString("x2")
259        Next
260
261        Return HashedPassword
262    End Function
263
264End Class
265Class StarText
266    'These subs make the password textbox on the login visible and hidden when called
267    Public Sub StarPassword(ByVal txtpassword As TextBox)
268        txtpassword.UseSystemPasswordChar = True
269    End Sub
270    Public Sub UnstarPassword(ByVal txtpassword As TextBox)
271        txtpassword.UseSystemPasswordChar = False
272    End Sub
273End Class
274
275Imports ADOX
276Imports System.IO
277Imports System.Data.OleDb
278Public Class Club_Selection
279    'This form is only ever run if the user is an admin 
280
281    Dim cat As Catalog = New Catalog()
282    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
283    Dim con As New OleDbConnection(ConnectionString)
284    Dim cmd As New OleDbCommand
285    Dim reader As OleDbDataReader
286    Private Sub btnChooseCronton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChooseCronton.Click
287        'When Cronton is selected the user's club is set to Cronton in the Staff table, so the system treats 
288        'the user as a Cronton employee 
289
290        Main_Menu.Show()
291        Me.Hide()
292        Login.Club = "Cronton"
293        'This is the updating of the users details in the Staff table
294        con.Open()
295        cmd.Connection = con
296        cmd.CommandText = "UPDATE Staff SET Club = '" & Login.Club & "' WHERE Username = '" & Login.Controls("UsernameBox").Text & "';"
297        cmd.ExecuteNonQuery()
298        con.Close()
299    End Sub
300
301    Private Sub btnChooseFarnworth_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChooseFarnworth.Click
302        'When Farnworth is selected the user's club is set to Farnworth in the Staff table, so the system treats 
303        'the user as a Farnworth employee 
304
305        Main_Menu.Show()
306        Me.Hide()
307        Login.Club = "Farnworth"
308        con.Open()
309        cmd.Connection = con
310        cmd.CommandText = "UPDATE Staff SET Club = '" & Login.Club & "' WHERE Username = '" & Login.Controls("UsernameBox").Text & "';"
311        cmd.ExecuteNonQuery()
312        con.Close()
313    End Sub
314End Class
315
316Imports ADOX
317Imports System.IO
318Imports System.Data.OleDb
319Public Class Main_Menu
320    'This form loads the other forms when the respective button is clicked
321
322    Dim cat As Catalog = New Catalog()
323    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
324    Dim con As New OleDbConnection(ConnectionString)
325    Dim cmd As New OleDbCommand
326    Dim reader As OleDbDataReader
327    Sub btnEditStaffInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditStaffInfo.Click
328        'This form is only available to admins so it checks if the user is an admin before opening the form
329
330        If Login.AdminAccess = True Then
331            Edit_Staff_Info.Show()
332        Else
333            MsgBox("You do not have access to this page")
334        End If
335    End Sub
336
337    Private Sub btnCurrentRegAndPay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCurrentRegAndPay.Click
338        Current_Reg_And_Pay.Show()
339    End Sub
340
341    Private Sub btnParentInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParentInfo.Click
342        Parent_Info.Show()
343    End Sub
344
345    Private Sub btnChildInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChildInfo.Click
346        Child_Info.Show()
347    End Sub
348
349    Private Sub btnPaymentHistory_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPaymentHistory.Click
350        Payment_History.Show()
351    End Sub
352End Class
353
354Imports ADOX
355Imports System.IO
356Imports System.Data.OleDb
357Imports System.Text.RegularExpressions
358Public Class Edit_Staff_Info
359    'This form can be used by an administrator to add new staff, 
360    'edit details about current staff or delete staff altogether
361
362    Dim cat As Catalog = New Catalog()
363    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
364    Dim con As New OleDbConnection(ConnectionString)
365    Dim cmd As New OleDbCommand
366    Dim SQLCommand As String = "SELECT * FROM Staff "
367    Dim reader As OleDbDataReader
368    Dim EditStaffID As Integer
369    Dim EditStaffName, EditClub, EditPassword, EditUsername As String
370    Dim EditAdmin As Boolean
371    Dim PasswordCheckUCase As New Regex("(?=.*[A-Z])")
372    Dim PasswordCheckNumber As New Regex("(?=.*[0-9])")
373
374
375    Private Sub Edit_Staff_Info_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
376        'When the form loads, the refresh sub is called, which populates the table
377
378        StaffInfoRefresh()
379    End Sub
380
381    Sub btnAddInformation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddInformation.Click
382        'This sub is called when the add information button is clicked. It checks if the information is in 
383        'the correct form to be inserted into the database, using some regex, and also 
384        'checks if there is already a staff member with repeated attributes.
385        'The staff ID is autoincremented when the data is inserted
386
387        EditStaffName = txtStaffName.Text
388        EditUsername = txtEditUserName.Text
389        EditPassword = Login.HashPassword(txtEditPassword.Text)
390        EditAdmin = chkAdmin.Checked
391
392        'Checks if both Cronton and Farnworth are checked because the staff member cannot work at both clubs.
393        If (chkCronton.Checked And chkFarnworth.Checked) = True Then
394            EditClub = ""
395        Else
396            EditClub = ""
397            If chkCronton.Checked = True Then
398                EditClub = "Cronton"
399            End If
400            If chkFarnworth.Checked = True Then
401                EditClub = "Farnworth"
402            End If
403        End If
404
405        'This If statement validates that all the textboxes have been filled and that one of the clubs has been selected
406        If EditStaffName.Length = 0 Or EditUsername.Length = 0 Or EditClub.Length = 0 Then
407            MsgBox("Attempt failed. Please enter all the information correctly")
408        Else
409            If PasswordCheckUCase.IsMatch(txtEditPassword.Text) = False Then
410                MsgBox("Your Password must contain at least one Capital letter and number")
411            Else
412                If PasswordCheckNumber.IsMatch(txtEditPassword.Text) = False Then
413                    MsgBox("Your Password must contain at least one Capital letter and number")
414                Else
415                    If txtEditPassword.Text.Length < 6 Then
416                        MsgBox("The password has to be 6 or more characters long")
417                    Else
418
419                        'Checks with the database if the username is already taken.
420                        con.Open()
421                        cmd.Connection = con
422                        cmd.CommandText = "Select * FROM Staff WHERE Username = '" & EditUsername & "'"
423                        cmd.ExecuteNonQuery()
424                        reader = cmd.ExecuteReader
425                        If reader.Read Then
426                            MsgBox("That Username already exists")
427                        Else
428                            con.Close()
429                            con.Open()
430                            cmd.Connection = con
431                            cmd.CommandText = "INSERT INTO Staff(StaffName, Username, PWord, Club, IsAdmin)" &
432                            "VALUES ('" & EditStaffName & "','" & EditUsername & "'," &
433                            "'" & EditPassword & "','" & EditClub & "', " & EditAdmin & ");"
434                            cmd.ExecuteNonQuery()
435                            con.Close()
436                            MsgBox("Added")
437                        End If
438                    End If
439                End If
440            End If
441        End If
442        con.Close()
443        'Refreshes the table when the information has been added
444        StaffInfoRefresh()
445    End Sub
446
447    Private Sub btnDeleteStaffMember_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteStaffMember.Click
448        'This sub gives runs when the delete staff member button is clicked. 
449
450        'This reads from the staff table to check if the StaffID exists 
451        EditStaffID = txtStaffID.Text
452        con.Open()
453        cmd.Connection = con
454        cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & EditStaffID & ""
455        cmd.ExecuteNonQuery()
456        reader = cmd.ExecuteReader
457        If reader.Read Then
458            'This then deletes the the StaffID if it exists
459            con.Close()
460            con.Open()
461            cmd.CommandText = "Delete FROM Staff WHERE StaffID = " & EditStaffID & ""
462            cmd.ExecuteNonQuery()
463            MsgBox("Deleted")
464        Else
465            MsgBox("StaffID doesn't exist")
466        End If
467        con.Close()
468        StaffInfoRefresh()
469    End Sub
470    Private Sub btnSearchStaffMember_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchStaffMember.Click
471        'This sub allows the user to search for a staff members record by using their name
472
473        EditStaffName = txtStaffName.Text
474        con.Open()
475        cmd.Connection = con
476        cmd.CommandText = "SELECT * FROM Staff WHERE StaffName = '" & EditStaffName & "'"
477        cmd.ExecuteNonQuery()
478        reader = cmd.ExecuteReader
479        grdStaffInfo.Rows.Clear()
480        If reader.Read Then
481            'This empties the table and repopulates it with the matching staff name
482            Dim n As Integer = grdStaffInfo.Rows.Add
483            grdStaffInfo.Rows.Item(n).Cells(0).Value = (reader("StaffID"))
484            grdStaffInfo.Rows.Item(n).Cells(1).Value = (reader("StaffName"))
485            grdStaffInfo.Rows.Item(n).Cells(2).Value = (reader("Username"))
486            grdStaffInfo.Rows.Item(n).Cells(3).Value = (reader("PWord"))
487            grdStaffInfo.Rows.Item(n).Cells(4).Value = (reader("Club"))
488            grdStaffInfo.Rows.Item(n).Cells(5).Value = (reader("IsAdmin"))
489        Else
490            MsgBox("Staff Name doesn't exist")
491        End If
492        con.Close()
493    End Sub
494    Sub StaffInfoRefresh()
495        'This sub is called whenever the table needs to be updated to match the database
496
497        grdStaffInfo.Rows.Clear()
498        con.Open()
499        cmd.Connection = con
500        cmd.CommandText = (SQLCommand)
501        reader = cmd.ExecuteReader
502        Do While reader.Read()
503            Dim n As Integer = grdStaffInfo.Rows.Add
504            grdStaffInfo.Rows.Item(n).Cells(0).Value = (reader("StaffID"))
505            grdStaffInfo.Rows.Item(n).Cells(1).Value = (reader("StaffName"))
506            grdStaffInfo.Rows.Item(n).Cells(2).Value = (reader("Username"))
507            grdStaffInfo.Rows.Item(n).Cells(3).Value = (reader("PWord"))
508            grdStaffInfo.Rows.Item(n).Cells(4).Value = (reader("Club"))
509            grdStaffInfo.Rows.Item(n).Cells(5).Value = (reader("IsAdmin"))
510        Loop
511        con.Close()
512    End Sub
513
514    Private Sub btnEditExistingStaff_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditExistingStaff.Click
515        'This sub allows the user to edit all an existing staff members details, except the StaffID
516
517        EditStaffID = txtStaffID.Text
518        EditStaffName = txtStaffName.Text
519        EditUsername = txtEditUserName.Text
520        EditPassword = Login.HashPassword(txtEditPassword.Text)
521        EditAdmin = chkAdmin.Checked
522
523        If (chkCronton.Checked And chkFarnworth.Checked) = True Then
524            EditClub = ""
525        Else
526            If chkCronton.Checked = True Then
527                EditClub = "Cronton"
528            End If
529            If chkFarnworth.Checked = True Then
530                EditClub = "Farnworth"
531            End If
532        End If
533
534        con.Open()
535        cmd.Connection = con
536        cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & EditStaffID & ""
537        cmd.ExecuteNonQuery()
538        reader = cmd.ExecuteReader
539        If reader.Read Then
540            con.Close()
541            con.Open()
542            cmd.Connection = con
543            cmd.CommandText = "Select * FROM Staff WHERE Username = '" & EditUsername & "'"
544            cmd.ExecuteNonQuery()
545            reader = cmd.ExecuteReader
546            If reader.Read Then
547                MsgBox("That Username already exists")
548            Else
549                If EditStaffName.Length = 0 Or EditUsername.Length = 0 Or EditClub.Length = 0 Or EditStaffID = 0 Then
550                    MsgBox("Attempt failed. Please enter all the information correctly")
551                Else
552                    If PasswordCheckUCase.IsMatch(txtEditPassword.Text) = False Then
553                        MsgBox("Your Password must contain at least one Capital letter and number")
554                    Else
555                        If PasswordCheckUCase.IsMatch(txtEditPassword.Text) = False Then
556                            MsgBox("Your Password must contain at least one Capital letter and number")
557                        Else
558
559                            If txtEditPassword.Text.Length < 6 Then
560                                MsgBox("The password has to be 6 or more characters long")
561                            Else
562                                con.Close()
563                                con.Open()
564                                cmd.Connection = con
565                                cmd.CommandText = "UPDATE Staff SET StaffName = '" & EditStaffName & "', Username = '" & EditUsername & "'" &
566                                ", Pword = '" & EditPassword & "'," &
567                                "Club = '" & EditClub & "', IsAdmin = " & EditAdmin & " WHERE StaffID = " & EditStaffID & ""
568                                cmd.ExecuteNonQuery()
569                                con.Close()
570                                MsgBox("Edited")
571                            End If
572                        End If
573                    End If
574                End If
575            End If
576        Else
577            MsgBox("That Staff ID doesn't exist")
578        End If
579
580        con.Close()
581        StaffInfoRefresh()
582    End Sub
583End Class
584
585Imports ADOX
586Imports System.IO
587Imports System.Data.OleDb
588Public Class Child_Info
589    Dim cat As Catalog = New Catalog()
590    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
591    Dim con As New OleDbConnection(ConnectionString)
592    Dim cmd As New OleDbCommand
593    Dim reader As OleDbDataReader
594    Dim SQLCommand As String
595    Dim ChildID, ChildName, StaffID, FamilyID, Club, EditChildID, EditStaffID, EditFamilyID, EditClub, Age, EditAge As String
596
597    Private Sub Child_Info_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
598        'This sub calls the refresh sub when the from is loaded
599
600        ChildInfoRefresh()
601    End Sub
602
603    Sub ChildInfoRefresh()
604        'This sub populates the Child Info table when called
605
606        grdChildInfo.Rows.Clear()
607        con.Open()
608        cmd.Connection = con
609        SQLCommand = "SELECT * FROM ChildInformation"
610        cmd.CommandText = (SQLCommand)
611        reader = cmd.ExecuteReader
612
613        Do While reader.Read()
614            Dim n As Integer = grdChildInfo.Rows.Add
615            grdChildInfo.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
616            grdChildInfo.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
617            grdChildInfo.Rows.Item(n).Cells(2).Value = (reader("Age"))
618            grdChildInfo.Rows.Item(n).Cells(3).Value = (reader("FamilyID"))
619            grdChildInfo.Rows.Item(n).Cells(4).Value = (reader("StaffID"))
620            grdChildInfo.Rows.Item(n).Cells(5).Value = (reader("Club"))
621
622        Loop
623        con.Close()
624    End Sub
625
626    Private Sub btnLinkAddNewChild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLinkAddNewChild.Click
627        'This sub shows the Add New Child form when the button is pressed. 
628        'This is for if the child has no siblings currently attending.
629
630        Add_New_Child.Show()
631    End Sub
632
633    
634    Private Sub btnDeleteChild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteChild.Click
635        'This sub deletes the child from all the tables in the database and also checks if the 
636        'child is the only child related to their parent
637        'and if so, deletes the parents' records too.
638
639        Dim FamilyID As String
640        Dim ParentName As String
641        ChildID = txtChildID.Text
642        con.Close()
643        con.Open()
644        cmd.Connection = con
645        
646        cmd.CommandText = "Select * FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
647        cmd.ExecuteNonQuery()
648        con.Close()
649        con.Open()
650        reader = cmd.ExecuteReader
651        If reader.Read Then
652            con.Close()
653            con.Open()
654            cmd.CommandText = "Select ChildNameOne FROM ParentLink"
655            cmd.ExecuteNonQuery()
656            reader = cmd.ExecuteReader
657            If reader.Read Then
658                con.Close()
659                con.Open()
660                cmd.CommandText = "Select FamilyID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
661                cmd.ExecuteNonQuery()
662                reader = cmd.ExecuteReader
663                reader.Read()
664                FamilyID = reader("FamilyID")
665                cmd.CommandText = "Select ParentName FROM ParentInformation WHERE FamilyID = '" & FamilyID & "'"
666                con.Close()
667                con.Open()
668                cmd.ExecuteNonQuery()
669                reader = cmd.ExecuteReader
670                reader.Read()
671                ParentName = reader("ParentName")
672                cmd.CommandText = "Delete FROM ParentInformation WHERE FamilyID = '" & FamilyID & "'"
673                con.Close()
674                con.Open()
675                cmd.ExecuteNonQuery()
676                cmd.CommandText = "Delete FROM PaymentLog WHERE ParentName = '" & ParentName & "'"
677                con.Close()
678                con.Open()
679                cmd.ExecuteNonQuery()
680                con.Close()
681                con.Open()
682                cmd.CommandText = "Delete FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
683                cmd.ExecuteNonQuery()
684                con.Close()
685                con.Open()
686                cmd.CommandText = "Delete FROM Register WHERE ChildID = '" & ChildID & "'"
687                cmd.ExecuteNonQuery()
688                MsgBox("Deleted")
689                cmd.CommandText = "Delete FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
690                con.Close()
691                con.Open()
692                cmd.ExecuteNonQuery()
693            Else
694                con.Close()
695                con.Open()
696                cmd.CommandText = "Delete FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
697                cmd.ExecuteNonQuery()
698                con.Close()
699                con.Open()
700                cmd.CommandText = "Delete FROM Register WHERE ChildID = '" & ChildID & "'"
701                cmd.ExecuteNonQuery()
702                MsgBox("Deleted")
703            End If
704        Else
705            MsgBox("ChildID doesn't exist")
706        End If
707        con.Close()
708        ChildInfoRefresh()
709    End Sub
710
711    Private Sub btnChildInfoSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChildInfoSearch.Click
712        'This sub is run when the user searches for a childs' records by their child name and displays it in the grid
713
714        ChildName = txtChildName.Text
715        con.Open()
716        cmd.Connection = con
717        cmd.CommandText = "SELECT * FROM ChildInformation WHERE ChildName = '" & ChildName & "'"
718        cmd.ExecuteNonQuery()
719        reader = cmd.ExecuteReader
720        grdChildInfo.Rows.Clear()
721        If reader.Read Then
722            Dim n As Integer = grdChildInfo.Rows.Add
723            grdChildInfo.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
724            grdChildInfo.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
725            grdChildInfo.Rows.Item(n).Cells(2).Value = (reader("Age"))
726            grdChildInfo.Rows.Item(n).Cells(3).Value = (reader("FamilyID"))
727            grdChildInfo.Rows.Item(n).Cells(4).Value = (reader("StaffID"))
728            grdChildInfo.Rows.Item(n).Cells(5).Value = (reader("Club"))
729        Else
730            MsgBox("Child Name doesn't exist")
731        End If
732        con.Close()
733    End Sub
734
735    Private Sub btnEditExistingChild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditExistingChild.Click
736        'This sub allows the user to edit an already existing childs' information
737
738        EditChildID = txtEditChildID.Text
739        EditAge = txtEditAge.Text
740        EditStaffID = txtEditStaffID.Text
741        EditFamilyID = txtEditFamilyID.Text
742
743        If (chkEditCrontonChild.Checked And chkEditFarnworthChild.Checked) = True Then
744            EditClub = ""
745        Else
746            EditClub = ""
747            If chkEditCrontonChild.Checked = True Then
748                EditClub = "Cronton"
749            End If
750            If chkEditFarnworthChild.Checked = True Then
751                EditClub = "Farnworth"
752            End If
753        End If
754
755        If EditChildID.Length = 0 Then
756            MsgBox("Please enter a valid Child ID")
757        ElseIf EditAge.ToString.Length = 0 Then
758            MsgBox("Please enter a valid age")
759        ElseIf EditStaffID.Length = 0 Then
760            MsgBox("Please enter a valid Staff ID")
761        ElseIf EditAge.ToString.Length > 2 Then
762            MsgBox("Please enter a valid age")
763        ElseIf EditFamilyID.Length = 0 Then
764            MsgBox("Please type in a valid Family ID")
765        ElseIf EditClub.Length = 0 Then
766            MsgBox("Please select a club")
767        Else
768            con.Close()
769            con.Open()
770            cmd.Connection = con
771            cmd.CommandText = "Select * FROM ChildInformation WHERE ChildID = '" & EditChildID & "'"
772            cmd.ExecuteNonQuery()
773            reader = cmd.ExecuteReader
774            If reader.Read Then
775                con.Close()
776                con.Open()
777                cmd.Connection = con
778                cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & EditStaffID & ""
779                cmd.ExecuteNonQuery()
780                reader = cmd.ExecuteReader
781                If reader.Read Then
782                    con.Close()
783                    con.Open()
784                    cmd.Connection = con
785                    cmd.CommandText = "Select * FROM ParentLink WHERE FamilyID = '" & EditFamilyID & "'"
786                    cmd.ExecuteNonQuery()
787                    reader = cmd.ExecuteReader
788                    If reader.Read Then
789                        con.Close()
790                        con.Open()
791                        cmd.Connection = con
792                        cmd.CommandText = "UPDATE ChildInformation SET Age = '" & EditAge & "', Club = '" & EditClub & "'," &
793                        "StaffID = '" & EditStaffID & "', FamilyID = '" & EditFamilyID & "'" &
794                        "WHERE ChildID = '" & EditChildID & "'"
795                        cmd.ExecuteNonQuery()
796                        con.Close()
797                        MsgBox("Edited")
798                    Else
799                        MsgBox("That Family ID doesn't exist")
800                    End If
801                Else
802                    MsgBox("That Staff ID doesn't exist")
803                End If
804
805            Else
806                MsgBox("That Child ID doesn't exist")
807            End If
808        End If
809        con.Close()
810        ChildInfoRefresh()
811    End Sub
812
813    Private Sub btnLinkWithCurrentFamily_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLinkWithCurrentFamily.Click
814        'This sub links a child to an already existing family
815
816        ChildID = txtChildID.Text
817        ChildName = txtChildName.Text
818        Age = txtAge.Text
819        StaffID = txtStaffID.Text
820        FamilyID = txtFamilyID.Text
821
822        If chkCrontonChild.Checked = True And chkFarnworthChild.Checked = True Then
823            Club = ""
824        Else
825            Club = ""
826            If chkCrontonChild.Checked = True Then
827                Club = "Cronton"
828            End If
829            If chkFarnworthChild.Checked = True Then
830                Club = "Farnworth"
831            End If
832        End If
833
834        If ChildID.Length = 0 Then
835            MsgBox("Please enter a valid Child ID")
836        ElseIf ChildName.Length = 0 Then
837            MsgBox("Please enter a valid Child Name")
838        ElseIf Age.ToString.Length = 0 Then
839            MsgBox("Please enter a valid age")
840        ElseIf Age.ToString.Length > 2 Then
841            MsgBox("Please enter a valid age")
842        ElseIf StaffID.Length = 0 Then
843            MsgBox("Please enter a valid Staff ID")
844        ElseIf Club.Length = 0 Then
845            MsgBox("Please tick a club")
846        Else
847            con.Close()
848            con.Open()
849            cmd.Connection = con
850            cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & StaffID & ""
851            cmd.ExecuteNonQuery()
852            reader = cmd.ExecuteReader
853            If reader.Read Then
854                con.Close()
855                con.Open()
856                cmd.CommandText = "Select ChildNameOne FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
857                cmd.ExecuteNonQuery()
858                reader = cmd.ExecuteReader
859                If reader.Read Then
860                    con.Close()
861                    con.Open()
862                    cmd.CommandText = "INSERT INTO Register(ChildID, Club) VALUES ('" & ChildID & "', '" & Club & "');"
863                    cmd.ExecuteNonQuery()
864
865                    con.Close()
866                    con.Open()
867                    cmd.Connection = con
868                    cmd.CommandText = "INSERT INTO ChildInformation(ChildID, ChildName, Age, FamilyID, StaffID, Club)" &
869                    "VALUES ('" & ChildID & "','" & ChildName & "','" & Age & "','" & FamilyID & "'," &
870                    "'" & StaffID & "','" & Club & "');"
871                    cmd.ExecuteNonQuery()
872
873                    con.Close()
874                    con.Open()
875                    cmd.CommandText = "Select ChildNameTwo FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
876                    cmd.ExecuteNonQuery()
877                    reader = cmd.ExecuteReader
878                    If reader.Read Then
879                        con.Close()
880                        con.Open()
881                        cmd.CommandText = "Select ChildNameThree FROM ParentLink WHERE FamilyID = '" & FamilyID & "'"
882                        cmd.ExecuteNonQuery()
883                        reader = cmd.ExecuteReader
884                        If reader.Read Then
885                            con.Close()
886                            con.Open()
887                            cmd.CommandText = "UPDATE ParentLink SET ChildNameFour = '" & ChildName & "' WHERE FamilyID = '" & FamilyID & "';"
888                            cmd.ExecuteNonQuery()
889                        Else
890                            con.Close()
891                            con.Open()
892                            cmd.CommandText = "UPDATE ParentLink SET ChildNameThree = '" & ChildName & "' WHERE FamilyID = '" & FamilyID & "';"
893                            cmd.ExecuteNonQuery()
894                        End If
895                    Else
896                        con.Close()
897                        con.Open()
898                        cmd.CommandText = "UPDATE ParentLink SET ChildNameTwo = '" & ChildName & "' WHERE FamilyID = '" & FamilyID & "';"
899                        cmd.ExecuteNonQuery()
900                    End If
901                    con.Close()
902                    ChildInfoRefresh()
903                    MsgBox("Added")
904                Else
905                    MsgBox("There is no current Family stored")
906                End If
907            Else
908                MsgBox("That StaffID doesn't exist")
909            End If
910        End If
911        con.Close()
912    End Sub
913
914    Private Sub btnStaffInfoRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStaffInfoRefresh.Click
915        'This refreshes the table, for if you want the original table back after performing a search
916        ChildInfoRefresh()
917    End Sub
918
919    Private Sub btnSortByName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSortByName.Click
920        'This sub impliments a mergesort in order to sort the children into alphabetical order and display this in the grid
921
922        Dim i As Integer = -1
923        Dim n As Integer = 0
924
925        SQLCommand = "SELECT ChildName FROM ChildInformation"
926        con.Open()
927        cmd.Connection = con
928        cmd.CommandText = (SQLCommand)
929        reader = cmd.ExecuteReader
930
931        'This counts how many children are in the table
932        Do While reader.Read()
933            i = i + 1
934        Loop
935        con.Close()
936
937        'Creates an array the size of the table
938        Dim ChildArray(i) As String
939
940        SQLCommand = "SELECT ChildName FROM ChildInformation"
941        con.Open()
942        cmd.Connection = con
943        cmd.CommandText = (SQLCommand)
944        reader = cmd.ExecuteReader
945
946        'This adds the childrens names to the array
947        Do While reader.Read()
948            ChildArray(n) = (reader("ChildName"))
949            n = n + 1
950        Loop
951        con.Close()
952
953        'Mergesort - Grade A
954        MergeSort(ChildArray)
955
956        'This adds the sorted children back into the table
957        For m = 0 To UBound(ChildArray)
958            grdChildInfo.Rows.Item(m).Cells(1).Value = ChildArray(m)
959        Next
960
961        'Adds the rest of the informtion about the respective child back into the grid with the child name
962        For j = 0 To UBound(ChildArray)
963            SQLCommand = "SELECT ChildID, Age, FamilyID, StaffID, Club FROM ChildInformation WHERE ChildName = '" & ChildArray(j) & "'"
964            con.Open()
965            cmd.Connection = con
966            cmd.CommandText = (SQLCommand)
967            reader = cmd.ExecuteReader
968            Do While reader.Read()
969                grdChildInfo.Rows.Item(j).Cells(0).Value = (reader("ChildID"))
970                grdChildInfo.Rows.Item(j).Cells(2).Value = (reader("Age"))
971                grdChildInfo.Rows.Item(j).Cells(3).Value = (reader("FamilyID"))
972                grdChildInfo.Rows.Item(j).Cells(4).Value = (reader("StaffID"))
973                grdChildInfo.Rows.Item(j).Cells(5).Value = (reader("Club"))
974            Loop
975            con.Close()
976        Next
977
978    End Sub
979
980    Public Sub MergeSort(ByRef data As String())
981        'This sub splits up the input data
982
983        Dim currentSize As Integer
984        Dim left As Integer
985
986        currentSize = 1
987        While currentSize <= data.Length - 1
988            left = 0
989            While left < data.Length - 1
990                Dim middle As Integer = left + currentSize - 1
991                Dim right As Integer = Math.Min(left + 2 * currentSize - 1, data.Length - 1)
992
993                Merge(data, left, middle, right)
994                left += 2 * currentSize
995            End While
996            currentSize = 2 * currentSize
997        End While
998    End Sub
999
1000    Private Sub Merge(ByRef data As String(), ByVal left As Integer, ByVal mid As Integer, ByVal right As Integer)
1001        'This sub compares and orders the values of the items
1002
1003        Dim i As Integer
1004        Dim j As Integer
1005        Dim k As Integer
1006        Dim Number1 As String = mid - left + 1
1007        Dim Number2 As String = right - mid
1008        Dim LeftPointer As String() = New String(Number1 - 1) {}
1009        Dim RightPointer As String() = New String(Number2 - 1) {}
1010
1011        For i = 0 To Number1 - 1
1012            LeftPointer(i) = data(left + i)
1013        Next
1014
1015        For j = 0 To Number2 - 1
1016            RightPointer(j) = data(mid + 1 + j)
1017        Next
1018
1019        i = 0
1020        j = 0
1021        k = left
1022
1023        While i < Number1 AndAlso j < Number2
1024            If LeftPointer(i) <= RightPointer(j) Then
1025                data(k) = LeftPointer(i)
1026                i += 1
1027            Else
1028                data(k) = RightPointer(j)
1029                j += 1
1030            End If
1031
1032            k += 1
1033        End While
1034
1035        While i < Number1
1036            data(k) = LeftPointer(i)
1037            i += 1
1038            k += 1
1039        End While
1040
1041        While j < Number2
1042            data(k) = RightPointer(j)
1043            j += 1
1044            k += 1
1045        End While
1046    End Sub
1047
1048End Class
1049
1050Imports ADOX
1051Imports System.IO
1052Imports System.Data.OleDb
1053Public Class Add_New_Child
1054    Dim cat As Catalog = New Catalog()
1055    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
1056    Dim con As New OleDbConnection(ConnectionString)
1057    Dim cmd As New OleDbCommand
1058    Dim reader As OleDbDataReader
1059    Dim SQLCommand As String
1060    Dim AddFamilyID, AddClub, AddStaffID, AddChildName, AddParentName, AddAddress, AddFamilyGP, AddAge, AddContactNumber As String
1061    
1062
1063    Sub btnAddNewChildToSystem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNewChildToSystem.Click
1064        'This sub adds a new child, along with their parent to the database
1065
1066        Dim NewParentID As String
1067        Dim NewChildID As String
1068        AddChildName = txtChildName.Text
1069        AddAge = txtAge.Text
1070        AddStaffID = txtStaffID.Text
1071        AddFamilyID = txtFamilyID.Text
1072        AddParentName = txtParentName.Text
1073        AddAddress = txtAddress.Text
1074        AddFamilyGP = txtFamilyGP.Text
1075        AddContactNumber = txtContactNumber.Text
1076
1077
1078
1079        If chkCrontonChild.Checked = True And chkFarnworthChild.Checked = True Then
1080            AddClub = ""
1081        Else
1082            AddClub = ""
1083            If chkCrontonChild.Checked = True Then
1084                AddClub = "Cronton"
1085            End If
1086            If chkFarnworthChild.Checked = True Then
1087                AddClub = "Farnworth"
1088            End If
1089        End If
1090
1091        'Validation to check the user has inputted information correctly
1092        If AddChildName.Length = 0 Then
1093            MsgBox("Please enter the Child Name correctly")
1094        ElseIf AddAge.ToString.Length > 2 Then
1095            MsgBox("Please enter a correct age")
1096        ElseIf AddAge.ToString.Length = 0 Then
1097            MsgBox("Please enter a correct age")
1098        ElseIf AddStaffID.Length = 0 Then
1099            MsgBox("Please enter a valid Staff ID")
1100        ElseIf IsNumeric(AddStaffID) = False Then
1101            MsgBox("Please enter a valid Staff ID")
1102        ElseIf AddFamilyID.Length = 0 Then
1103            MsgBox("Please enter the Family ID correctly")
1104        ElseIf AddParentName.Length = 0 Then
1105            MsgBox("Please enter the Parent Name correctly")
1106        ElseIf AddAddress.Length = 0 Then
1107            MsgBox("Please enter the address correctly")
1108        ElseIf AddFamilyGP.Length = 0 Then
1109            MsgBox("Please enter the GP's Name correctly")
1110        ElseIf AddClub.Length = 0 Then
1111            MsgBox("Please tick one club")
1112        ElseIf IsNumeric(AddAge) = False Then
1113            MsgBox("Please enter a number for the age")
1114        ElseIf IsNumeric(AddContactNumber) = False Then
1115            MsgBox("Please eneter a correct contact number")
1116        ElseIf AddContactNumber.ToString.Length < 7 Then
1117            MsgBox("That contact number is too short")
1118        ElseIf AddContactNumber.ToString.Length > 11 Then
1119            MsgBox("That contact number is too long")
1120        Else
1121            con.Close()
1122            con.Open()
1123            cmd.Connection = con
1124            cmd.CommandText = "Select * FROM Staff WHERE StaffID = " & AddStaffID & ""
1125            cmd.ExecuteNonQuery()
1126            reader = cmd.ExecuteReader
1127            If reader.Read Then
1128                con.Close()
1129                con.Open()
1130                cmd.Connection = con
1131                cmd.CommandText = "Select * FROM ParentLink WHERE FamilyID = '" & AddFamilyID & "'"
1132                cmd.ExecuteNonQuery()
1133                reader = cmd.ExecuteReader
1134                If reader.Read Then
1135                    MsgBox("That Family ID already exists")
1136                Else
1137                    con.Close()
1138                    con.Open()
1139                    cmd.Connection = con
1140                    NewChildID = GenerateChildID()
1141                    NewParentID = GenerateParentID()
1142                    cmd.CommandText = "INSERT INTO ParentLink(FamilyID, ChildNameOne) VALUES ('" & AddFamilyID & "','" & AddChildName & "');"
1143                    cmd.ExecuteNonQuery()
1144                    cmd.CommandText = "INSERT INTO Register(ChildID, Club) VALUES ('" & NewChildID & "','" & AddClub & "');"
1145                    cmd.ExecuteNonQuery()
1146                    cmd.CommandText = "INSERT INTO PaymentLog(ParentID, ParentName, PaymentsOwed)" &
1147                    "VALUES('" & NewParentID & "','" & AddParentName & "', '0');"
1148                    cmd.ExecuteNonQuery()
1149                    cmd.CommandText = "INSERT INTO ChildInformation(ChildID, ChildName, Age, FamilyID, StaffID, Club)" &
1150                    "VALUES ('" & NewChildID & "','" & AddChildName & "','" & AddAge & "'," &
1151                    "'" & AddFamilyID & "','" & AddStaffID & "','" & AddClub & "');"
1152                    cmd.ExecuteNonQuery()
1153                    cmd.CommandText = "INSERT INTO ParentInformation(ParentName, Address, ParentID, ContactNumber, FamilyGP, FamilyID, PaymentsOwed)" &
1154                    "VALUES ('" & AddParentName & "','" & AddAddress & "'," &
1155                    "'" & NewParentID & "','" & AddContactNumber & "','" & AddFamilyGP & "','" & AddFamilyID & "', '0');"
1156                    cmd.ExecuteNonQuery()
1157                    con.Close()
1158                    MsgBox("Added")
1159                End If
1160            Else
1161                MsgBox("That Staff ID doesn't exist")
1162            End If
1163
1164        End If
1165
1166        Child_Info.ChildInfoRefresh()
1167    End Sub
1168    Function GenerateParentID()
1169        'Generates a Unique parent ID using their name
1170
1171        Dim NewParentID As String
1172        Dim ParentID As String
1173        Dim clash As Boolean = True
1174        Dim value As Integer
1175        con.Close()
1176        con.Open()
1177        ParentID = AddParentName
1178        value = 0
1179        NewParentID = ParentID & value
1180
1181        'This adds a number onto the end of the parent name and checks if this is already in the database.
1182        'If so, it tries the next number up and so on.
1183
1184        Do While clash = True
1185            con.Close()
1186            con.Open()
1187            value = value + 1
1188            NewParentID = ParentID & value
1189            cmd.CommandText = "Select ParentID FROM PaymentLog WHERE ParentID = '" & NewParentID & "';"
1190            cmd.ExecuteNonQuery()
1191            reader = cmd.ExecuteReader
1192            If reader.Read Then
1193            Else
1194                clash = False
1195            End If
1196        Loop
1197        con.Close()
1198        con.Open()
1199        Return NewParentID
1200    End Function
1201
1202    Function GenerateChildID()
1203        'Generates a unique child ID for the child
1204
1205        Dim NewChildID As String
1206        Dim ChildID As String
1207        Dim clash As Boolean = True
1208        Dim value As Integer
1209        con.Close()
1210        con.Open()
1211        ChildID = AddChildName
1212        value = 0
1213        NewChildID = ChildID & value
1214        Do While clash = True
1215            con.Close()
1216            con.Open()
1217            value = value + 1
1218            NewChildID = ChildID & value
1219            cmd.CommandText = "Select ChildID FROM Register WHERE ChildID = '" & NewChildID & "';"
1220            cmd.ExecuteNonQuery()
1221            reader = cmd.ExecuteReader
1222            If reader.Read Then
1223            Else
1224                clash = False
1225            End If
1226        Loop
1227        con.Close()
1228        con.Open()
1229        Return NewChildID
1230    End Function
1231End Class
1232
1233Imports ADOX
1234Imports System.IO
1235Imports System.Data.OleDb
1236Public Class Parent_Info
1237    Dim cat As Catalog = New Catalog()
1238    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
1239    Dim con As New OleDbConnection(ConnectionString)
1240    Dim cmd As New OleDbCommand
1241    Dim reader As OleDbDataReader
1242    Dim SQLCommand As String = "SELECT * FROM ParentInformation"
1243    Dim ParentName, Address, FamilyGP, FamilyID, PaymentsOwed, ContactNumber As String
1244
1245    Private Sub Parent_Info_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
1246        'This calls the sub to load the table into the grid
1247
1248        'This sets the payments owed column to display as a currency
1249        grdParentInfo.Columns(4).DefaultCellStyle.Format = "c"
1250        ParentInfoRefresh()
1251    End Sub
1252
1253    Sub ParentInfoRefresh()
1254        'This sub populates the grid 
1255
1256        grdParentInfo.Rows.Clear()
1257        con.Open()
1258        cmd.Connection = con
1259        cmd.CommandText = (SQLCommand)
1260        reader = cmd.ExecuteReader
1261
1262        Do While reader.Read()
1263            Dim n As Integer = grdParentInfo.Rows.Add
1264            grdParentInfo.Rows.Item(n).Cells(0).Value = (reader("ParentName"))
1265            grdParentInfo.Rows.Item(n).Cells(1).Value = (reader("Address"))
1266            grdParentInfo.Rows.Item(n).Cells(2).Value = (reader("ParentID"))
1267            grdParentInfo.Rows.Item(n).Cells(3).Value = (reader("ContactNumber"))
1268            grdParentInfo.Rows.Item(n).Cells(4).Value = (reader("PaymentsOwed"))
1269            grdParentInfo.Rows.Item(n).Cells(5).Value = (reader("FamilyGP"))
1270            grdParentInfo.Rows.Item(n).Cells(6).Value = (reader("FamilyID"))
1271
1272        Loop
1273        con.Close()
1274    End Sub
1275
1276    Private Sub btnEditExistingParent_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditExistingParent.Click
1277        'This sub allows the user to edit an existing parents details
1278
1279        ParentName = txtParentName.Text
1280        Address = txtAddress.Text
1281        ContactNumber = txtContactNumber.Text
1282        PaymentsOwed = txtPaymentsOwed.Text
1283        FamilyGP = txtFamilyGP.Text
1284
1285
1286        If ParentName.Length = 0 Then
1287            MsgBox("Please enter Parent Name correctly")
1288        ElseIf Address.Length = 0 Then
1289            MsgBox("Please enter a valid address")
1290        ElseIf IsNumeric(ContactNumber) = False Then
1291            MsgBox("Please enter a valid number for the contact number")
1292        ElseIf ContactNumber.Length > 11 Then
1293            MsgBox("That contact number is too long")
1294        ElseIf ContactNumber.Length < 7 Then
1295            MsgBox("That contact number is too short")
1296        ElseIf IsNumeric(PaymentsOwed) = False Then
1297            MsgBox("Please enter a number for the payments owed")
1298        ElseIf PaymentsOwed.Length = 0 Then
1299            MsgBox("That payment owed is not valid")
1300        ElseIf FamilyGP.Length = 0 Then
1301            MsgBox("Please enter the family GP correctly")
1302        Else
1303            con.Close()
1304            con.Open()
1305            cmd.Connection = con
1306            cmd.CommandText = "Select * FROM ParentInformation WHERE ParentName = '" & ParentName & "'"
1307            cmd.ExecuteNonQuery()
1308            reader = cmd.ExecuteReader
1309            If reader.Read Then
1310                con.Close()
1311                con.Open()
1312                cmd.Connection = con
1313                cmd.CommandText = "UPDATE ParentInformation SET Address = '" & Address & "', ContactNumber = '" & ContactNumber & "'," &
1314                "PaymentsOwed = '" & PaymentsOwed & "', FamilyGP = '" & FamilyGP & "'," &
1315                "WHERE ParentName = '" & ParentName & "'"
1316                cmd.ExecuteNonQuery()
1317                cmd.CommandText = "UPDATE PaymentLog SET PaymentsOwed = '" & PaymentsOwed & "' WHERE ParentName = '" & ParentName & "'"
1318                cmd.ExecuteNonQuery()
1319                con.Close()
1320                MsgBox("Edited")
1321            Else
1322                MsgBox("That Parent Name doesn't exist")
1323            End If
1324           
1325        End If
1326
1327            con.Close()
1328            ParentInfoRefresh()
1329            Current_Reg_And_Pay.CurrentRegAndPayRefresh()
1330    End Sub
1331
1332    Private Sub btnSearchParent_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchParent.Click
1333        'Allows the user to search for a parents records by their name
1334
1335        ParentName = txtParentName.Text
1336        con.Open()
1337        cmd.Connection = con
1338        cmd.CommandText = "SELECT * FROM ParentInformation WHERE ParentName = '" & ParentName & "'"
1339        cmd.ExecuteNonQuery()
1340        reader = cmd.ExecuteReader
1341        grdParentInfo.Rows.Clear()
1342        If reader.Read Then
1343            Dim n As Integer = grdParentInfo.Rows.Add
1344            grdParentInfo.Rows.Item(n).Cells(0).Value = (reader("ParentName"))
1345            grdParentInfo.Rows.Item(n).Cells(1).Value = (reader("Address"))
1346            grdParentInfo.Rows.Item(n).Cells(2).Value = (reader("ParentID"))
1347            grdParentInfo.Rows.Item(n).Cells(3).Value = (reader("ContactNumber"))
1348            grdParentInfo.Rows.Item(n).Cells(4).Value = (reader("PaymentsOwed"))
1349            grdParentInfo.Rows.Item(n).Cells(5).Value = (reader("FamilyGP"))
1350            grdParentInfo.Rows.Item(n).Cells(6).Value = (reader("FamilyID"))
1351        Else
1352            con.Close()
1353            ParentInfoRefresh()
1354            MsgBox("Parent Name doesn't exist")
1355        End If
1356        con.Close()
1357    End Sub
1358
1359    
1360    Private Sub btnParentInfoRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParentInfoRefresh.Click
1361        'Runs the refresh sub
1362
1363        ParentInfoRefresh()
1364    End Sub
1365End Class
1366
1367Imports ADOX
1368Imports System.IO
1369Imports System.Data.OleDb
1370Public Class Current_Reg_And_Pay
1371    Dim cat As Catalog = New Catalog()
1372    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
1373    Dim con As New OleDbConnection(ConnectionString)
1374    Dim cmd As New OleDbCommand
1375    Dim reader As OleDbDataReader
1376    Dim SQLCommand As String
1377    Dim Club As String = Login.Club
1378    Dim ChildID, CurrentTime, ParentName, ChildName, AmountPaid, PaymentMethod As String
1379    Dim ParentID, FamilyID, CurrentPaymentsOwed, NewPaymentsOwed, TransactionDate As String
1380
1381    Sub CurrentRegAndPayRefresh()
1382        'This sub fills the grid with the table information
1383
1384        GrdRegister.Rows.Clear()
1385        'This command takes the child name from child information and put it into the register table,
1386        'so that the user can search for the child by their name.
1387        'It also only populates the register with the children which go the the club that the admin chose in
1388        'the club selection screen, or whichever club the user works at 
1389        SQLCommand = "SELECT Register.*, ChildInformation.ChildName FROM Register INNER JOIN ChildInformation" &
1390        "ON Register.ChildID = ChildInformation.ChildID WHERE Register.Club = '" & Club & "'"
1391        con.Open()
1392        cmd.Connection = con
1393        cmd.CommandText = (SQLCommand)
1394        reader = cmd.ExecuteReader
1395
1396        Do While reader.Read()
1397            Dim n As Integer = GrdRegister.Rows.Add
1398            GrdRegister.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
1399            GrdRegister.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
1400            GrdRegister.Rows.Item(n).Cells(2).Value = (reader("TimeIn"))
1401            GrdRegister.Rows.Item(n).Cells(3).Value = (reader("TimeOut"))
1402            GrdRegister.Rows.Item(n).Cells(4).Value = (reader("Present"))
1403        Loop
1404        con.Close()
1405
1406        GrdPaymentLog.Rows.Clear()
1407        SQLCommand = "SELECT * FROM PaymentLog "
1408        con.Open()
1409        cmd.Connection = con
1410        cmd.CommandText = (SQLCommand)
1411        reader = cmd.ExecuteReader
1412
1413        Do While reader.Read()
1414            Dim n As Integer = GrdPaymentLog.Rows.Add
1415            GrdPaymentLog.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
1416            GrdPaymentLog.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
1417            GrdPaymentLog.Rows.Item(n).Cells(2).Value = (reader("PaymentsOwed"))
1418            GrdPaymentLog.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
1419            GrdPaymentLog.Rows.Item(n).Cells(4).Value = (reader("PaymentMethod"))
1420        Loop
1421        con.Close()
1422    End Sub
1423
1424    Private Sub Current_Reg_And_Pay_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
1425        'This sub calls the refresh sub
1426
1427        'Formats the data grids to show the time in the below format and also sets the money columns to display as a currency 
1428        GrdRegister.Columns(2).DefaultCellStyle.Format = "HH:mm:ss"
1429        GrdRegister.Columns(3).DefaultCellStyle.Format = "HH:mm:ss"
1430        GrdPaymentLog.Columns(2).DefaultCellStyle.Format = "c"
1431        GrdPaymentLog.Columns(3).DefaultCellStyle.Format = "c"
1432        CurrentRegAndPayRefresh()
1433    End Sub
1434
1435    Private Sub btnDeleteChildRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteChildRegister.Click
1436        'Gives the user a quick link to the child information table, if edits need to be made
1437
1438        Child_Info.Show()
1439    End Sub
1440
1441    Private Sub btnCurrentRegAndPayRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCurrentRegAndPayRefresh.Click
1442        'Button calls the refresh sub
1443
1444        CurrentRegAndPayRefresh()
1445    End Sub
1446
1447    Private Sub btnMarkIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMarkIn.Click
1448        '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
1449
1450        ChildID = txtRegChildID.Text
1451        'This gets the current system time
1452        CurrentTime = Now.ToLongTimeString
1453
1454        con.Open()
1455        cmd.Connection = con
1456        cmd.CommandText = "SELECT ChildID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
1457        cmd.ExecuteNonQuery()
1458        reader = cmd.ExecuteReader
1459
1460        If reader.Read Then
1461            con.Close()
1462            con.Open()
1463            cmd.Connection = con
1464            cmd.CommandText = "UPDATE Register SET TimeIn = '" & CurrentTime & "', Present = True WHERE ChildID = '" & ChildID & "'"
1465            cmd.ExecuteNonQuery()
1466            con.Close()
1467
1468            CurrentRegAndPayRefresh()
1469            MsgBox("Marked In")
1470        Else
1471            MsgBox("Child ID doesn't exist")
1472        End If
1473
1474        con.Close()
1475    End Sub
1476
1477    Private Sub btnMarkOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMarkOut.Click
1478        'This sub marks the selected child as no longer present and prints the time of depature in the table.
1479        'The Parent that is linked to the child has the set amount of £8.50 added to the amount owed to the club
1480
1481        ChildID = txtRegChildID.Text
1482        CurrentTime = Now.ToLongTimeString
1483
1484        con.Open()
1485        cmd.Connection = con
1486        cmd.CommandText = "SELECT ChildID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
1487        cmd.ExecuteNonQuery()
1488        reader = cmd.ExecuteReader
1489
1490        If reader.Read Then
1491            con.Close()
1492            con.Open()
1493            cmd.Connection = con
1494            cmd.CommandText = "UPDATE Register SET TimeOut = '" & CurrentTime & "', Present = False WHERE ChildID = '" & ChildID & "'"
1495            cmd.ExecuteNonQuery()
1496            con.Close()
1497
1498            con.Open()
1499            cmd.Connection = con
1500            cmd.CommandText = "SELECT FamilyID FROM ChildInformation WHERE ChildID = '" & ChildID & "'"
1501            cmd.ExecuteNonQuery()
1502            reader = cmd.ExecuteReader
1503            reader.Read()
1504            FamilyID = reader("FamilyID")
1505            con.Close()
1506
1507            con.Open()
1508            cmd.Connection = con
1509            cmd.CommandText = "SELECT ParentID FROM ParentInformation WHERE FamilyID = '" & FamilyID & "'"
1510            cmd.ExecuteNonQuery()
1511            reader = cmd.ExecuteReader
1512            reader.Read()
1513            ParentID = reader("ParentID")
1514            con.Close()
1515
1516            con.Open()
1517            cmd.Connection = con
1518            cmd.CommandText = "SELECT PaymentsOwed FROM PaymentLog WHERE ParentID = '" & ParentID & "'"
1519            cmd.ExecuteNonQuery()
1520            reader = cmd.ExecuteReader
1521            reader.Read()
1522            CurrentPaymentsOwed = reader("PaymentsOwed")
1523            con.Close()
1524
1525            NewPaymentsOwed = CurrentPaymentsOwed + (8.5)
1526
1527            con.Open()
1528            cmd.Connection = con
1529            cmd.CommandText = "UPDATE PaymentLog SET PaymentsOwed = '" & NewPaymentsOwed & "' WHERE ParentID = '" & ParentID & "'"
1530            cmd.ExecuteNonQuery()
1531            con.Close()
1532
1533            con.Open()
1534            cmd.Connection = con
1535            cmd.CommandText = "UPDATE ParentInformation SET PaymentsOwed = '" & NewPaymentsOwed & "' WHERE ParentID = '" & ParentID & "'"
1536            cmd.ExecuteNonQuery()
1537            con.Close()
1538
1539            CurrentRegAndPayRefresh()
1540            MsgBox("Marked Out")
1541        Else
1542            MsgBox("Child ID doesn't exist")
1543        End If
1544        con.Close()
1545    End Sub
1546
1547    Private Sub btnPaymentLogSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPaymentLogSearch.Click
1548        'Allows the user to search for a parents information by their name
1549
1550        ParentName = txtParentName.Text
1551
1552        con.Open()
1553        cmd.Connection = con
1554        cmd.CommandText = "SELECT * FROM PaymentLog WHERE ParentName = '" & ParentName & "'"
1555        cmd.ExecuteNonQuery()
1556        reader = cmd.ExecuteReader
1557
1558        GrdPaymentLog.Rows.Clear()
1559        If reader.Read Then
1560            Dim n As Integer = GrdPaymentLog.Rows.Add
1561            GrdPaymentLog.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
1562            GrdPaymentLog.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
1563            GrdPaymentLog.Rows.Item(n).Cells(2).Value = (reader("PaymentsOwed"))
1564            GrdPaymentLog.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
1565            GrdPaymentLog.Rows.Item(n).Cells(4).Value = (reader("PaymentMethod"))
1566        Else
1567            MsgBox("Parent Name doesn't exist")
1568        End If
1569        con.Close()
1570    End Sub
1571
1572    Private Sub btnRegisterSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegisterSearch.Click
1573        'This allows the user to search for the child information by the child name
1574
1575        ChildName = txtChildName.Text
1576
1577        con.Open()
1578        cmd.Connection = con
1579        'Cross table SQL - Grade A
1580        SQLCommand = "SELECT Register.*, ChildInformation.ChildName FROM Register INNER JOIN ChildInformation" &
1581        "ON Register.ChildID = ChildInformation.ChildID" &
1582        "WHERE Register.Club = '" & Club & "' AND ChildName = '" & ChildName & "'"
1583        cmd.CommandText = (SQLCommand)
1584        reader = cmd.ExecuteReader
1585
1586        GrdRegister.Rows.Clear()
1587        If reader.Read() Then
1588            Dim n As Integer = GrdRegister.Rows.Add
1589            GrdRegister.Rows.Item(n).Cells(0).Value = (reader("ChildID"))
1590            GrdRegister.Rows.Item(n).Cells(1).Value = (reader("ChildName"))
1591            GrdRegister.Rows.Item(n).Cells(2).Value = (reader("TimeIn"))
1592            GrdRegister.Rows.Item(n).Cells(3).Value = (reader("TimeOut"))
1593            GrdRegister.Rows.Item(n).Cells(4).Value = (reader("Present"))
1594        Else
1595            MsgBox("Child Name doesn't exist")
1596        End If
1597        con.Close()
1598    End Sub
1599
1600    Private Sub btnMakePayment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMakePayment.Click
1601        'This sub allows the parent to pay any amount of the money the owe to the club
1602        'It updates the payment log, writing the new amount owed, amount paid and the date of the transaction
1603        'It also adds the payment to the payment history table
1604
1605        ParentID = txtParentID.Text
1606        AmountPaid = txtAmountPaid.Text
1607        'Gets the current system date
1608        TransactionDate = String.Format("{0:dd/MM/yyyy}", DateTime.Now)
1609
1610        con.Open()
1611        cmd.Connection = con
1612        cmd.CommandText = "SELECT * FROM ParentInformation WHERE ParentID = '" & ParentID & "'"
1613        cmd.ExecuteNonQuery()
1614        reader = cmd.ExecuteReader
1615        If reader.Read Then
1616
1617            If IsNumeric(AmountPaid) = True Then
1618
1619
1620                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
1621                    PaymentMethod = ""
1622                Else
1623                    If chkCard.Checked Or chkCash.Checked Or chkCheque.Checked = True Then
1624                        If chkCard.Checked = True Then
1625                            PaymentMethod = "Card"
1626                        End If
1627                        If chkCash.Checked = True Then
1628                            PaymentMethod = "Cash"
1629                        End If
1630                        If chkCheque.Checked = True Then
1631                            PaymentMethod = "Cheque"
1632                        End If
1633                    Else
1634                        PaymentMethod = ""
1635                    End If
1636                End If
1637
1638                con.Close()
1639                con.Open()
1640                cmd.Connection = con
1641                cmd.CommandText = "SELECT * FROM PaymentHistory WHERE ParentID = '" & ParentID & "' AND TransactionDate = '" & TransactionDate & "'"
1642                cmd.ExecuteNonQuery()
1643                reader = cmd.ExecuteReader
1644                If reader.Read Then
1645                    MsgBox("You have already made a payment today")
1646                Else
1647
1648                    If PaymentMethod.Length <> 0 Then
1649
1650                        con.Close()
1651                        con.Open()
1652                        cmd.Connection = con
1653                        cmd.CommandText = "SELECT PaymentsOwed FROM PaymentLog WHERE ParentID = '" & ParentID & "'"
1654                        cmd.ExecuteNonQuery()
1655                        reader = cmd.ExecuteReader
1656                        reader.Read()
1657                        CurrentPaymentsOwed = reader("PaymentsOwed")
1658                        con.Close()
1659
1660                        NewPaymentsOwed = CurrentPaymentsOwed - AmountPaid
1661
1662                        con.Open()
1663                        cmd.Connection = con
1664                        cmd.CommandText = "UPDATE PaymentLog SET PaymentsOwed = '" & NewPaymentsOwed & "', AmountPaid = '" & AmountPaid & "'," &
1665                        "PaymentMethod = '" & PaymentMethod & "' WHERE ParentID = '" & ParentID & "'"
1666                        cmd.ExecuteNonQuery()
1667                        con.Close()
1668
1669                        con.Open()
1670                        cmd.Connection = con
1671                        cmd.CommandText = "INSERT INTO PaymentHistory(ParentID, AmountPaid, TransactionDate) VALUES ('" & ParentID & "'," &
1672                        "'" & AmountPaid & "', '" & TransactionDate & "');"
1673                        cmd.ExecuteNonQuery()
1674                        con.Close()
1675
1676                        con.Open()
1677                        cmd.Connection = con
1678                        cmd.CommandText = "UPDATE ParentInformation SET PaymentsOwed = '" & NewPaymentsOwed & "' WHERE ParentID = '" & ParentID & "'"
1679                        cmd.ExecuteNonQuery()
1680                        con.Close()
1681
1682                        CurrentRegAndPayRefresh()
1683                        MsgBox("Payment Made")
1684                    Else
1685                        MsgBox("Please tick only one of the boxes")
1686                    End If
1687
1688                End If
1689            Else
1690                MsgBox("Please enter a number in 'Amount Paid'")
1691            End If
1692        Else
1693            MsgBox("Parent ID doesn't exist")
1694        End If
1695        con.Close()
1696    End Sub
1697
1698    Private Sub bntClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bntClear.Click
1699        'This button clears the register and the payment log, but leaves in the people, so it can be used again the next day
1700
1701        TransactionDate = String.Format("{0:dd/MM/yyyy}", DateTime.Now)
1702
1703        con.Open()
1704        cmd.Connection = con
1705        cmd.CommandText = "UPDATE PaymentLog SET AmountPaid = NULL, PaymentMethod = NULL"
1706        cmd.ExecuteNonQuery()
1707        con.Close()
1708
1709        con.Open()
1710        cmd.Connection = con
1711        cmd.CommandText = "UPDATE Register SET TimeIn = NULL, TimeOut = NULL"
1712        cmd.ExecuteNonQuery()
1713        con.Close()
1714
1715        CurrentRegAndPayRefresh()
1716    End Sub
1717End Class
1718
1719Imports ADOX
1720Imports System.IO
1721Imports System.Data.OleDb
1722Public Class Payment_History
1723    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Whizzkids\WhizzkidsDB.accdb;"
1724    Dim cat As Catalog = New Catalog()
1725    Dim SQLCommand As String
1726    Dim con As New OleDbConnection(ConnectionString)
1727    Dim cmd As New OleDbCommand
1728    Dim reader As OleDbDataReader
1729    Dim ParentName As String
1730    Dim ParentID As String
1731    Dim TotalPaid As Decimal
1732
1733    Private Sub Payment_History_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
1734        'Formats the columns and calls the refresh sub
1735
1736        grdPaymentHistory.Columns(2).DefaultCellStyle.Format = "dd/MM/yyyy"
1737        grdPaymentHistory.Columns(3).DefaultCellStyle.Format = "c"
1738        PaymentHistoryRefresh()
1739    End Sub
1740
1741    Sub PaymentHistoryRefresh()
1742        'Refreshes the grid
1743
1744        grdPaymentHistory.Rows.Clear()
1745        SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog" &
1746        "ON PaymentHistory.ParentID = PaymentLog.ParentID"
1747        con.Open()
1748        cmd.Connection = con
1749        cmd.CommandText = (SQLCommand)
1750        reader = cmd.ExecuteReader
1751
1752        Do While reader.Read()
1753            Dim n As Integer = grdPaymentHistory.Rows.Add
1754            grdPaymentHistory.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
1755            grdPaymentHistory.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
1756            grdPaymentHistory.Rows.Item(n).Cells(2).Value = (reader("TransactionDate"))
1757            grdPaymentHistory.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
1758        Loop
1759        con.Close()
1760    End Sub
1761
1762    Private Sub btnPaymentLogSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPaymentLogSearch.Click
1763        'Allows the user to search for all the payments made by the parent name
1764
1765        ParentName = txtParentName.Text
1766        grdPaymentHistory.Rows.Clear()
1767        SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog ON" &
1768        "PaymentHistory.ParentID = PaymentLog.ParentID WHERE PaymentLog.ParentName = '" & ParentName & "'"
1769        con.Open()
1770        cmd.Connection = con
1771        cmd.CommandText = (SQLCommand)
1772        reader = cmd.ExecuteReader
1773
1774        If reader.Read() Then
1775            Dim n As Integer = grdPaymentHistory.Rows.Add
1776            grdPaymentHistory.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
1777            grdPaymentHistory.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
1778            grdPaymentHistory.Rows.Item(n).Cells(2).Value = (reader("TransactionDate"))
1779            grdPaymentHistory.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
1780        Else
1781            con.Close()
1782            PaymentHistoryRefresh()
1783            MsgBox("No transactions made by that Parent Name")
1784        End If
1785        con.Close()
1786    End Sub
1787
1788    Private Sub btnTransactionDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSortByTransactionDate.Click
1789        'This sub orders the transactions by date, so the user can see all of the transactions made on a particlar day
1790
1791        grdPaymentHistory.Rows.Clear()
1792
1793        'Aggregate SQL function - Grade A
1794        SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog ON" &
1795        "PaymentHistory.ParentID = PaymentLog.ParentID ORDER BY PaymentHistory.TransactionDate ASC"
1796        con.Open()
1797        cmd.Connection = con
1798        cmd.CommandText = (SQLCommand)
1799        reader = cmd.ExecuteReader
1800
1801        Do While reader.Read()
1802            Dim n As Integer = grdPaymentHistory.Rows.Add
1803            grdPaymentHistory.Rows.Item(n).Cells(0).Value = (reader("ParentID"))
1804            grdPaymentHistory.Rows.Item(n).Cells(1).Value = (reader("ParentName"))
1805            grdPaymentHistory.Rows.Item(n).Cells(2).Value = (reader("TransactionDate"))
1806            grdPaymentHistory.Rows.Item(n).Cells(3).Value = (reader("AmountPaid"))
1807        Loop
1808        con.Close()
1809    End Sub
1810
1811    Private Sub btnSortByCustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSortByCustomer.Click
1812        'Calls the refresh sub
1813
1814        PaymentHistoryRefresh()
1815    End Sub
1816
1817    Private Sub btnTotalPaid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTotalPaid.Click
1818        'This sub totals all the payments made by a parent ID
1819
1820        ParentID = txtParentID.Text
1821
1822        SQLCommand = "SELECT PaymentHistory.*, PaymentLog.ParentName FROM PaymentHistory INNER JOIN PaymentLog ON" &
1823        "PaymentHistory.ParentID = PaymentLog.ParentID WHERE PaymentLog.ParentID = '" & ParentID & "'"
1824        con.Close()
1825        con.Open()
1826        cmd.Connection = con
1827        cmd.CommandText = (SQLCommand)
1828        reader = cmd.ExecuteReader
1829
1830        If reader.Read() Then
1831
1832            'Aggregate SQL function - Grade A
1833            SQLCommand = "SELECT SUM(AmountPaid) FROM PaymentHistory WHERE ParentID = '" & ParentID & "'"
1834            con.Close()
1835            con.Open()
1836            cmd.Connection = con
1837            cmd.CommandText = (SQLCommand)
1838            reader = cmd.ExecuteReader
1839            reader.Read()
1840            'Sets the textbox to currency format
1841            txtTotalPaid.Text = FormatCurrency(reader.Item(0))
1842            con.Close()
1843
1844        Else
1845            MsgBox("No transactions made by that Parent ID")
1846        End If
1847        con.Close()
1848    End Sub
1849End Class