RHw9ampc

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