· 8 years ago · Mar 31, 2018, 12:50 PM
1import sqlite3
2import datetime
3from time import gmtime, strftime
4
5connection = sqlite3.connect("main.db")
6cursor = connection.cursor()
7
8SCREEN_LENGTH = 80
9
10#-----------------------------------------------------------------------------
11#---------------------------CLASS DEFINITIONS---------------------------------
12#-----------------------------------------------------------------------------
13
14class Staff:
15
16 def __init__(self, forename="X", surname="X", dateofbirth="X", subject="X", gender="X"):
17 self.forename = forename
18 self.surname = surname
19 self.dateofbirth = dateofbirth
20 self.subject = subject
21 self.gender = gender
22
23 def set_date_of_birth(self, year, month, day):
24 if(len(month) == 1):
25 month = "0" + month
26 if(len(day) == 1):
27 day = "0" + day
28 self.dateofbirth = year + "-" + month + "-" + day
29
30class Pupil:
31
32 def __init__(self, forename="X", surname="X", dateofbirth="X", gender="X", year_group=0,
33 average=0.0, test_one=0, test_two=0, test_three=0, test_four=0, test_five=0):
34 self.forename = forename
35 self.surname = surname
36 self.dateofbirth = dateofbirth
37 self.gender = gender
38 self.year_group = 0
39 average = 0.0
40 test_one = 0
41 test_two = 0
42 test_three = 0
43 test_four = 0
44 test_five = 0
45
46class Login:
47
48 def __init__(self, username="X", hashed_password="X", user_type="X", external_id=0):
49 self.username = username
50 self.hashed_password = hashed_password
51 self.user_type = user_type
52 self.external_id = external_id
53
54class Test:
55
56 def __init__(self, title="X", subject="X", questions=["X"]*30):
57 self.title = title
58 self.subject = subject
59 self.date_created = strftime("%Y-%m-%d", gmtime())
60 self.questions = questions
61
62#-----------------------------------------------------------------------------
63#-----------------------NO MORE CLASS DEFINITIONS-----------------------------
64#-----------------------------------------------------------------------------
65
66#-----------------------------------------------------------------------------
67#-----------------------------USEFUL TOOLS------------------------------------
68#-----------------------------------------------------------------------------
69
70def run_program():
71 #vital preloading things run here
72 cursor.execute("""
73CREATE TABLE IF NOT EXISTS Login(
74 LOGINID INTEGER PRIMARY KEY,
75 USERNAME VARCHAR(20),
76 HASHED_PASSWORD VARCHAR(20),
77 USER_TYPE CHAR(1),
78 RELATIVE_ID INTEGER
79); """)
80 cursor.execute("""
81CREATE TABLE IF NOT EXISTS Administrator(
82 ADMINID INTEGER PRIMARY KEY,
83 FORENAME VARCHAR(20),
84 SURNAME VARCHAR(20)
85); """)
86 cursor.execute("""
87CREATE TABLE IF NOT EXISTS Staff(
88 STAFFID INTEGER PRIMARY KEY,
89 FORENAME VARCHAR(20),
90 SURNAME VARCHAR(20),
91 DATE_OF_BIRTH DATE,
92 SUBJECT VARCHAR(20),
93 GENDER CHAR(1)
94); """)
95 cursor.execute("""
96CREATE TABLE IF NOT EXISTS Pupil(
97 PUPILID INTEGER PRIMARY KEY,
98 FORENAME VARCHAR(20),
99 SURNAME VARCHAR(20),
100 DATE_OF_BIRTH DATE,
101 GENDER CHAR(1),
102 YEAR_GROUP INTEGER,
103 AVERAGE REAL,
104 TEST_ONE INTEGER,
105 TEST_TWO INTEGER,
106 TEST_THREE INTEGER,
107 TEST_FOUR INTEGER,
108 TEST_FIVE INTEGER
109); """)
110 cursor.execute("""
111CREATE TABLE IF NOT EXISTS Test(
112 TESTID INTEGER PRIMARY KEY,
113 TITLE VARCHAR(50),
114 STAFFID INTEGER,
115 DATE_CREATED DATE,
116 TEST_TYPE VARCHAR(30),
117 QUESTION_ONE VARCHAR(100),
118 ANSWER_ONE VARCHAR(100),
119 QUESTION_TWO VARCHAR(100),
120 ANSWER_TWO VARCHAR(100),
121 QUESTION_THREE VARCHAR(100),
122 ANSWER_THREE VARCHAR(100),
123 QUESTION_FOUR VARCHAR(100),
124 ANSWER_FOUR VARCHAR(100),
125 QUESTION_FIVE VARCHAR(100),
126 ANSWER_FIVE VARCHAR(100),
127 QUESTION_SIX VARCHAR(100),
128 ANSWER_SIX VARCHAR(100),
129 QUESTION_SEVEN VARCHAR(100),
130 ANSWER_SEVEN VARCHAR(100),
131 QUESTION_EIGHT VARCHAR(100),
132 ANSWER_EIGHT VARCHAR(100),
133 QUESTION_NINE VARCHAR(100),
134 ANSWER_NINE VARCHAR(100),
135 QUESTION_TEN VARCHAR(100),
136 ANSWER_TEN VARCHAR(100),
137 QUESTION_ELEVEN VARCHAR(100),
138 ANSWER_ELEVEN VARCHAR(100),
139 QUESTION_TWELVE VARCHAR(100),
140 ANSWER_TWELVE VARCHAR(100),
141 QUESTION_THIRTEEN VARCHAR(100),
142 ANSWER_THIRTEEN VARCHAR(100),
143 QUESTION_FOURTEEN VARCHAR(100),
144 ANSWER_FOURTEEN VARCHAR(100),
145 QUESTION_FIFTEEN VARCHAR(100),
146 ANSWER_FIFTEEN VARCHAR(100)
147);""")
148 connection.commit()
149 login_prompt()
150
151
152def spaces_to_center(text):
153 return (SCREEN_LENGTH - len(text))//2
154
155def create_list(rows, columns, *args):
156 column_sizes = [0]*columns
157 difference_between_columns = len(args) // columns
158
159 for i in range(len(args)):
160 if(len(args[i]) > column_sizes[i // difference_between_columns]):
161 column_sizes[i // difference_between_columns] = len(args[i]) + 3
162
163 for i in range(rows):
164 full_row = args[i]
165 for j in range(1, columns):
166 full_row += " "*(column_sizes[j-1]-len(args[i+(j-1)*difference_between_columns])) + args[i+(j*difference_between_columns)]
167 print(full_row)
168
169def logo():
170 print("\n" * 100)
171 title_text = "Clase School for English, Maths and Other Educational Pursuits"
172 print("=" * SCREEN_LENGTH + "\n")
173 print(" " * (spaces_to_center(title_text)) + title_text + "\n")
174 print("=" * SCREEN_LENGTH + "\n")
175
176def title(big_title="", subtitle=""):
177 if(subtitle == ""):
178 full_string = big_title
179 else:
180 full_string = big_title + " - " + subtitle
181 print(" " * spaces_to_center(full_string) + full_string + "\n")
182
183def password_hash(given):
184 hashed = ""
185 for i in given:
186 hashed += chr(ord(i) + 3)
187 return hashed
188
189def add_to_database(given_object, table):
190 if(table == "Staff"):
191 cursor.execute("""
192INSERT INTO STAFF(FORENAME, SURNAME, DATE_OF_BIRTH, SUBJECT, GENDER)
193VALUES(?, ?, ?, ?, ?);""", (given_object.forename, given_object.surname,
194 given_object.dateofbirth, given_object.subject,
195 given_object.gender))
196 print("Successfully added " + given_object.forename + " " + given_object.surname + " to the Staff table at ", end="")
197 connection.commit()
198 elif(table == "Login"):
199 cursor.execute("""
200INSERT INTO LOGIN(USERNAME, HASHED_PASSWORD, USER_TYPE, RELATIVE_ID) VALUES(?, ?, ?, ?);""",
201 (given_object.username, given_object.hashed_password,
202 given_object.user_type, given_object.external_id,))
203 print("Successfully added " + given_object.username + " to the Login table.")
204 connection.commit()
205
206def get_index_by_element(given_array, search_term):
207 index = 0
208 found = False
209 while not(found) and index < len(given_array):
210 if(given_array[index] == search_term):
211 found = True
212 else:
213 index += 1
214 return index
215
216#----------------------------------------------------------------------------
217#--------------------------NO MORE USEFUL TOOLS------------------------------
218#----------------------------------------------------------------------------
219
220def login_prompt():
221
222 cursor.execute("SELECT * FROM Login")
223 list_of_logins = cursor.fetchall()
224
225 logo()
226 if(list_of_logins == []):
227 title("LOG IN", "REQUIRES INITIAL SETUP!")
228 else:
229 title("LOG IN")
230
231 pupil_usernames = []
232 pupil_passwords = []
233 staff_usernames = []
234 staff_passwords = []
235 admin_usernames = []
236 admin_passwords = []
237
238 valid_username = False
239 username = -1
240
241 for i in list_of_logins:
242 if i[3] == "A":
243 admin_usernames.append(i[1])
244 admin_passwords.append(i[2])
245 elif(i[3] == "S"):
246 staff_usernames.append(i[1])
247 staff_passwords.append(i[2])
248 elif(i[3] == "P"):
249 pupil_usernames.append(i[1])
250 pupil_usernames.append(i[2])
251
252 while(not(valid_username)):
253 if(username != -1):
254 logo()
255 title("LOG IN")
256 print("\nPlease enter a valid username and password!\n\n")
257 username = input("Username: ")
258 password = input("\nPassword: ")
259 all_usernames = pupil_usernames + staff_usernames + admin_usernames
260 if(username in all_usernames):
261 valid_username = True
262 else:
263 valid_username = False
264 if(not(list_of_logins == [])):
265 if(username == "admin"):
266 if(password == "password"):
267 administrator_menu()
268 elif(username in admin_usernames):
269 given_password = admin_passwords[get_index_by_element(admin_usernames, username)]
270 if(password_hash(password) == given_password):
271 administrator_menu()
272 elif(username in staff_usernames):
273 given_password = staff_passwords[get_index_by_element(staff_usernames, username)]
274 if(password_hash(password) == given_password):
275 staff_menu()
276 elif(username in pupil_usernames):
277 given_password = pupil_passwords[get_index_by_element(pupil_usernames, username)]
278 if(password_hash(password) == given_password):
279 pupil_menu()
280 else:
281 if(username == "" and
282 password_hash(password) == password_hash("")):
283 administrator_menu()
284
285def administrator_menu():
286 logo()
287 title("ADMINISTRATOR MENU")
288 print("1. Staff manager\n")
289 print("2. Database manager\n")
290 menu_pointer = input("> ")
291 if(menu_pointer == "1"):
292 staff_manager()
293 elif(menu_pointer == "2"):
294 database_manager()
295 #no validation
296
297def staff_menu():
298 logo()
299 title("STAFF MENU")
300 print("1. Test manager\n")
301 print("2. Pupil manager\n")
302 menu_pointer = input("> ")
303 if(menu_pointer == "1"):
304 test_manager()
305 elif(menu_pointer == "2"):
306 pupil_manager()
307
308def test_manager():
309 logo()
310 title("STAFF MENU", "Test Manager")
311 print("1. Create a test\n")
312 print("2. View a test\n")
313 print("3. Set a test\n")
314 menu_pointer = input("> ")
315 if(menu_pointer == "1"):
316 new_test = Test()
317 create_test(new_test)
318 elif(menu_pointer == "2"):
319 view_test()
320 elif(menu_pointer == "3"):
321 set_test()
322
323def create_test(test_object):
324 logo()
325 title("STAFF MENU", "Create Test")
326 create_list(4, 3,
327 "1. Edit title",
328 "2. Edit subject",
329 "3. Edit questions",
330 "-", "-", "-",
331 test_object.title,
332 test_object.subject,
333 " ")
334 print("\n5. Finish")
335 #print("1. Edit name\n")
336 #print("2. Edit date of birth\n")
337 #print("3. Edit subject area\n")
338 #print("4. Edit gender\n")
339 menu_pointer = input("> ")
340 if(menu_pointer == "1"):
341 edit_title(test_object, "STAFF MENU", "Edit Title", "test")
342 elif(menu_pointer == "2"):
343 edit_subject(test_object, "ADMINISTRATOR MENU", "Edit Staff Date of Birth", "staff")
344 elif(menu_pointer == "3"):
345 edit_subject(test_object, "ADMINISTRATOR MENU", "Edit Subject Area")
346 elif(menu_pointer == "4"):
347 edit_gender(test_object, "ADMINISTRATOR MENU", "Edit Staff Gender")
348 elif(menu_pointer == "5"):
349 add_to_database(test_object, "Test")
350 print("Created Test ID " + str(cursor.lastrowid))
351 input("Continue...")
352 test_manager()
353
354def pupil_manager():
355 logo()
356 title("STAFF MENU", "Pupil Manager")
357 print("1. Add pupil\n")
358 print("2. View/edit pupil\n")
359 menu_pointer = input("> ")
360 if(menu_pointer == "1"):
361 add_pupil()
362 elif(menu_pointer == "2"):
363 view_pupil()
364
365def database_manager():
366 logo()
367 title("ADMINISTRATOR MENU", "Database Manager")
368 print("1. Staff manager\n")
369 print("2. Database manager\n")
370 menu_pointer = input("> ")
371 if(menu_pointer == "1"):
372 staff_manager()
373 elif(menu_pointer == "2"):
374 database_manager()
375
376def staff_manager():
377 logo()
378 title("ADMINISTRATOR MENU", "Staff Manager")
379 print("1. Add new member of staff\n")
380 print("2. View/edit members of staff\n")
381 print("3. Back\n")
382 menu_pointer = input("> ")
383 if(menu_pointer == "1"):
384 new_staff = Staff()
385 add_staff(new_staff)
386 elif(menu_pointer == "2"):
387 view_staff()
388 elif(menu_pointer == "3"):
389 administrator_menu()
390
391def add_staff(member_of_staff):
392 logo()
393 title("ADMINISTRATOR MENU", "Add New Member of Staff")
394 create_list(4, 3,
395 "1. Edit name",
396 "2. Edit date of birth",
397 "3. Edit subject area",
398 "4. Edit gender",
399 "-", "-", "-", "-",
400 member_of_staff.forename + " " + member_of_staff.surname,
401 member_of_staff.dateofbirth,
402 member_of_staff.subject,
403 member_of_staff.gender)
404 print("\n5. Finish")
405 #print("1. Edit name\n")
406 #print("2. Edit date of birth\n")
407 #print("3. Edit subject area\n")
408 #print("4. Edit gender\n")
409 menu_pointer = input("> ")
410 if(menu_pointer == "1"):
411 edit_name(member_of_staff, "ADMINISTRATOR MENU", "Edit Staff Name", "staff")
412 elif(menu_pointer == "2"):
413 edit_date_of_birth(member_of_staff, "ADMINISTRATOR MENU", "Edit Staff Date of Birth", "staff")
414 elif(menu_pointer == "3"):
415 edit_subject(member_of_staff, "ADMINISTRATOR MENU", "Edit Subject Area")
416 elif(menu_pointer == "4"):
417 edit_gender(member_of_staff, "ADMINISTRATOR MENU", "Edit Staff Gender")
418 elif(menu_pointer == "5"):
419 username = input("Username: ")
420 password = input("Password: ")
421 new_login = Login()
422 new_login.username = username
423 new_login.hashed_password = password_hash(password)
424 new_login.user_type = "S"
425 add_to_database(member_of_staff, "Staff")
426 new_login.external_id = cursor.lastrowid
427 add_to_database(new_login, "Login")
428 print("Staff ID " + str(cursor.lastrowid))
429 input("Continue...")
430 staff_manager()
431
432def edit_name(given_object, bigtitle="", subtitle="", redirect="staff"):
433 logo()
434 title(bigtitle, subtitle)
435 forename = input("Forename: ")
436 surname = input("Surname: ")
437 given_object.forename = forename
438 given_object.surname = surname
439 if(redirect == "staff"):
440 add_staff(given_object)
441
442def edit_date_of_birth(given_object, bigtitle="", subtitle="", redirect="staff"):
443 logo()
444 title(bigtitle, subtitle)
445 year = input("Year: ")
446 month = input("Month: ")
447 day = input("Day: ")
448 given_object.set_date_of_birth(year, month, day)
449 if(redirect == "staff"):
450 add_staff(given_object)
451
452def edit_subject(given_object, bigtitle="", subtitle=""):
453 logo()
454 title(bigtitle, subtitle)
455 subject = input("Subject: ")
456 given_object.subject = subject
457 add_staff(given_object)
458
459def edit_gender(given_object, bigtitle="", subtitle="", redirect="staff"):
460 logo()
461 title(bigtitle, subtitle)
462 gender = input("Gender: ")
463 given_object.gender = gender
464 if(redirect == "staff"):
465 add_staff(given_object)
466
467def view_staff():
468 logo()
469 title("ADMINISTRATOR MENU", "View/Edit Member of Staff")
470 print("1. Search by name\n")
471 print("2. Search by date of birth\n")
472 print("3. Search by subject area\n")
473 print("4. Search by gender\n")
474 print("5. Back\n")
475 menu_pointer = input("> ")
476 if(menu_pointer == "1"):
477 search_by_name("staff", "ADMINISTRATOR MENU", "Search Staff By Name")
478 elif(menu_pointer == "2"):
479 search_by_dateofbirth("staff")
480 elif(menu_pointer == "3"):
481 search_by_subject("staff")
482 elif(menu_pointer == "4"):
483 search_by_gender("staff")
484 elif(menu_pointer == "5"):
485 staff_manager()
486
487def search_by_name(table, bigtitle="", subtitle=""):
488 logo()
489 title(bigtitle, subtitle)
490 forename = input("Forename: ")
491 surname = input("Surname: ")
492 search_results("name", table, 1, forename, surname)
493
494def search_by_dateofbirth(table, bigtitle="", subtitle=""):
495 logo()
496 title(bigtitle, subtitle)
497 year = input("Year: ")
498 month = input("Month: ")
499 day = input("Day: ")
500 search_results("dateofbirth", table, 1, year, month, day)
501
502def search_by_subject(table, bigtitle="", subtitle=""):
503 logo()
504 title(bigtitle, subtitle)
505 subject = input("Subject: ")
506 search_results("subject", table, 1, subject)
507
508def search_by_gender(table, bigtitle="", subtitle=""):
509 logo()
510 title(bigtitle, subtitle)
511 gender = input("Gender: ")
512 search_results("Gender", table, 1, gender)
513
514def search_results(search_type, table, page=1, *args):
515 logo()
516 if(table == "staff"):
517 if(search_type == "name"):
518 if(args[0] == ""):
519 cursor.execute("""SELECT * FROM STAFF WHERE SURNAME=?""",
520 (args[1],))
521 elif(args[1] == ""):
522 cursor.execute("""SELECT * FROM STAFF WHERE FORENAME=?""",
523 (args[0],))
524 else:
525 cursor.execute("""SELECT * FROM STAFF WHERE FORENAME=? AND SURNAME=?""",
526 (args[0], args[1]))
527 if(search_type == "dateofbirth"):
528 month = args[1]
529 day = args[2]
530 if(len(args[1]) == 1):
531 month = "0" + month
532 if(len(args[2]) == 1):
533 day = "0" + day
534 dateofbirth = args[0] + "-" + month + "-" + day
535 cursor.execute("""SELECT * FROM STAFF WHERE DATE_OF_BIRTH=?""", (dateofbirth,))
536 if(search_type == "subject"):
537 cursor.execute("""SELECT * FROM STAFF WHERE SUBJECT=?""", (args[0],))
538 if(search_type == "gender"):
539 cursor.execute("""SELECT * FROM STAFF WHERE GENDER=?""", (args[0],))
540
541 results = cursor.fetchall()
542 title("ADMINISTRATOR MENU", "Search Results - Page (" + str(page) + "/" + str(1 + len(results)//4) + ")")
543 max_pages = 1+len(results)//4
544 if(len(results) < 4*page):
545 for i in range(4*page - len(results)):
546 results.append((" ", " ", " ", " ", " ", " "))
547
548 rows = 4
549 columns = 6
550 create_list(5, 6,
551 " ", "1.", "2.", "3.", "4.",
552 "FORENAME", results[4*(page-1) + 0][1], results[4*(page-1) +1][1], results[4*(page-1) +2][1], results[4*(page-1) +3][1],
553 "SURNAME", results[4*(page-1) +0][2], results[4*(page-1) +1][2], results[4*(page-1) +2][2], results[4*(page-1) +3][2],
554 "DATE OF BIRTH", results[4*(page-1) +0][3], results[4*(page-1) +1][3], results[4*(page-1) +2][3], results[4*(page-1) +3][3],
555 "SUBJECT", results[4*(page-1) +0][4], results[4*(page-1) +1][4], results[4*(page-1) +2][4], results[4*(page-1) +3][4],
556 "GENDER", results[4*(page-1) +0][5], results[4*(page-1) +1][5], results[4*(page-1) +2][5], results[4*(page-1) +3][5])
557 print("\n5. Next Page")
558 print("6. Previous Page")
559 print("7. Back")
560
561 menu_pointer = input("> ")
562 if(menu_pointer == "1"):
563 view_staff_record(4*(page-1) + 0)
564 elif(menu_pointer == "2"):
565 view_staff_record(4*(page-1) + 1)
566 elif(menu_pointer == "3"):
567 view_staff_record(4*(page-1) + 2)
568 elif(menu_pointer == "4"):
569 view_staff_record(4*(page-1) + 3)
570 elif(menu_pointer == "5"):
571 if(page < max_pages):
572 if(len(args) == 2):
573 search_results(search_type, table, page+1, args[0], args[1])
574 elif(len(args) == 1):
575 search_results(search_type, table, page+1, args[0])
576 else:
577 if(len(args) == 2):
578 search_results(search_type, table, page, args[0], args[1])
579 elif(len(args) == 1):
580 search_results(search_type, table, page, args[0])
581 elif(menu_pointer == "6"):
582 if(not(page <= 1)):
583 if(len(args) == 2):
584 search_results(search_type, table, page-1, args[0], args[1])
585 elif(len(args) == 1):
586 search_results(search_type, table, page-1, args[0])
587 else:
588 if(len(args) == 2):
589 search_results(search_type, table, page, args[0], args[1])
590 elif(len(args) == 1):
591 search_results(search_type, table, page, args[0])
592 elif(menu_pointer == "7"):
593 view_staff()
594
595
596
597
598run_program()
599connection.close()