Advertisement
HoratioZEDU

Untitled

Mar 31st, 2018
164
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 20.96 KB | None | 0 0
  1. import sqlite3
  2. import datetime
  3. from time import gmtime, strftime
  4.  
  5. connection = sqlite3.connect("main.db")
  6. cursor = connection.cursor()
  7.  
  8. SCREEN_LENGTH = 80
  9.  
  10. #-----------------------------------------------------------------------------
  11. #---------------------------CLASS DEFINITIONS---------------------------------
  12. #-----------------------------------------------------------------------------
  13.  
  14. class 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.  
  30. class 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.  
  46. class 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.  
  54. class 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.  
  70. def run_program():
  71. #vital preloading things run here
  72. cursor.execute("""
  73. CREATE 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("""
  81. CREATE TABLE IF NOT EXISTS Administrator(
  82. ADMINID INTEGER PRIMARY KEY,
  83. FORENAME VARCHAR(20),
  84. SURNAME VARCHAR(20)
  85. ); """)
  86. cursor.execute("""
  87. CREATE 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("""
  96. CREATE 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("""
  111. CREATE 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.  
  152. def spaces_to_center(text):
  153. return (SCREEN_LENGTH - len(text))//2
  154.  
  155. def 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.  
  169. def 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.  
  176. def 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.  
  183. def password_hash(given):
  184. hashed = ""
  185. for i in given:
  186. hashed += chr(ord(i) + 3)
  187. return hashed
  188.  
  189. def add_to_database(given_object, table):
  190. if(table == "Staff"):
  191. cursor.execute("""
  192. INSERT INTO STAFF(FORENAME, SURNAME, DATE_OF_BIRTH, SUBJECT, GENDER)
  193. VALUES(?, ?, ?, ?, ?);""", (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("""
  200. INSERT 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.  
  206. def 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.  
  220. def 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.  
  285. def 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.  
  297. def 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.  
  308. def 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.  
  323. def 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.  
  354. def 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.  
  365. def 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.  
  376. def 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.  
  391. def 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.  
  432. def 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.  
  442. def 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.  
  452. def 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.  
  459. def 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.  
  467. def 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.  
  487. def 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.  
  494. def 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.  
  502. def search_by_subject(table, bigtitle="", subtitle=""):
  503. logo()
  504. title(bigtitle, subtitle)
  505. subject = input("Subject: ")
  506. search_results("subject", table, 1, subject)
  507.  
  508. def search_by_gender(table, bigtitle="", subtitle=""):
  509. logo()
  510. title(bigtitle, subtitle)
  511. gender = input("Gender: ")
  512. search_results("Gender", table, 1, gender)
  513.  
  514. def 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.  
  598. run_program()
  599. connection.close()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement