Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import sqlite3
- import datetime
- from time import gmtime, strftime
- connection = sqlite3.connect("main.db")
- cursor = connection.cursor()
- SCREEN_LENGTH = 80
- #-----------------------------------------------------------------------------
- #---------------------------CLASS DEFINITIONS---------------------------------
- #-----------------------------------------------------------------------------
- class Staff:
- def __init__(self, forename="X", surname="X", dateofbirth="X", subject="X", gender="X"):
- self.forename = forename
- self.surname = surname
- self.dateofbirth = dateofbirth
- self.subject = subject
- self.gender = gender
- def set_date_of_birth(self, year, month, day):
- if(len(month) == 1):
- month = "0" + month
- if(len(day) == 1):
- day = "0" + day
- self.dateofbirth = year + "-" + month + "-" + day
- class Pupil:
- def __init__(self, forename="X", surname="X", dateofbirth="X", gender="X", year_group=0,
- average=0.0, test_one=0, test_two=0, test_three=0, test_four=0, test_five=0):
- self.forename = forename
- self.surname = surname
- self.dateofbirth = dateofbirth
- self.gender = gender
- self.year_group = 0
- average = 0.0
- test_one = 0
- test_two = 0
- test_three = 0
- test_four = 0
- test_five = 0
- class Login:
- def __init__(self, username="X", hashed_password="X", user_type="X", external_id=0):
- self.username = username
- self.hashed_password = hashed_password
- self.user_type = user_type
- self.external_id = external_id
- class Test:
- def __init__(self, title="X", subject="X", questions=["X"]*30):
- self.title = title
- self.subject = subject
- self.date_created = strftime("%Y-%m-%d", gmtime())
- self.questions = questions
- #-----------------------------------------------------------------------------
- #-----------------------NO MORE CLASS DEFINITIONS-----------------------------
- #-----------------------------------------------------------------------------
- #-----------------------------------------------------------------------------
- #-----------------------------USEFUL TOOLS------------------------------------
- #-----------------------------------------------------------------------------
- def run_program():
- #vital preloading things run here
- cursor.execute("""
- CREATE TABLE IF NOT EXISTS Login(
- LOGINID INTEGER PRIMARY KEY,
- USERNAME VARCHAR(20),
- HASHED_PASSWORD VARCHAR(20),
- USER_TYPE CHAR(1),
- RELATIVE_ID INTEGER
- ); """)
- cursor.execute("""
- CREATE TABLE IF NOT EXISTS Administrator(
- ADMINID INTEGER PRIMARY KEY,
- FORENAME VARCHAR(20),
- SURNAME VARCHAR(20)
- ); """)
- cursor.execute("""
- CREATE TABLE IF NOT EXISTS Staff(
- STAFFID INTEGER PRIMARY KEY,
- FORENAME VARCHAR(20),
- SURNAME VARCHAR(20),
- DATE_OF_BIRTH DATE,
- SUBJECT VARCHAR(20),
- GENDER CHAR(1)
- ); """)
- cursor.execute("""
- CREATE TABLE IF NOT EXISTS Pupil(
- PUPILID INTEGER PRIMARY KEY,
- FORENAME VARCHAR(20),
- SURNAME VARCHAR(20),
- DATE_OF_BIRTH DATE,
- GENDER CHAR(1),
- YEAR_GROUP INTEGER,
- AVERAGE REAL,
- TEST_ONE INTEGER,
- TEST_TWO INTEGER,
- TEST_THREE INTEGER,
- TEST_FOUR INTEGER,
- TEST_FIVE INTEGER
- ); """)
- cursor.execute("""
- CREATE TABLE IF NOT EXISTS Test(
- TESTID INTEGER PRIMARY KEY,
- TITLE VARCHAR(50),
- STAFFID INTEGER,
- DATE_CREATED DATE,
- TEST_TYPE VARCHAR(30),
- QUESTION_ONE VARCHAR(100),
- ANSWER_ONE VARCHAR(100),
- QUESTION_TWO VARCHAR(100),
- ANSWER_TWO VARCHAR(100),
- QUESTION_THREE VARCHAR(100),
- ANSWER_THREE VARCHAR(100),
- QUESTION_FOUR VARCHAR(100),
- ANSWER_FOUR VARCHAR(100),
- QUESTION_FIVE VARCHAR(100),
- ANSWER_FIVE VARCHAR(100),
- QUESTION_SIX VARCHAR(100),
- ANSWER_SIX VARCHAR(100),
- QUESTION_SEVEN VARCHAR(100),
- ANSWER_SEVEN VARCHAR(100),
- QUESTION_EIGHT VARCHAR(100),
- ANSWER_EIGHT VARCHAR(100),
- QUESTION_NINE VARCHAR(100),
- ANSWER_NINE VARCHAR(100),
- QUESTION_TEN VARCHAR(100),
- ANSWER_TEN VARCHAR(100),
- QUESTION_ELEVEN VARCHAR(100),
- ANSWER_ELEVEN VARCHAR(100),
- QUESTION_TWELVE VARCHAR(100),
- ANSWER_TWELVE VARCHAR(100),
- QUESTION_THIRTEEN VARCHAR(100),
- ANSWER_THIRTEEN VARCHAR(100),
- QUESTION_FOURTEEN VARCHAR(100),
- ANSWER_FOURTEEN VARCHAR(100),
- QUESTION_FIFTEEN VARCHAR(100),
- ANSWER_FIFTEEN VARCHAR(100)
- );""")
- connection.commit()
- login_prompt()
- def spaces_to_center(text):
- return (SCREEN_LENGTH - len(text))//2
- def create_list(rows, columns, *args):
- column_sizes = [0]*columns
- difference_between_columns = len(args) // columns
- for i in range(len(args)):
- if(len(args[i]) > column_sizes[i // difference_between_columns]):
- column_sizes[i // difference_between_columns] = len(args[i]) + 3
- for i in range(rows):
- full_row = args[i]
- for j in range(1, columns):
- full_row += " "*(column_sizes[j-1]-len(args[i+(j-1)*difference_between_columns])) + args[i+(j*difference_between_columns)]
- print(full_row)
- def logo():
- print("\n" * 100)
- title_text = "Clase School for English, Maths and Other Educational Pursuits"
- print("=" * SCREEN_LENGTH + "\n")
- print(" " * (spaces_to_center(title_text)) + title_text + "\n")
- print("=" * SCREEN_LENGTH + "\n")
- def title(big_title="", subtitle=""):
- if(subtitle == ""):
- full_string = big_title
- else:
- full_string = big_title + " - " + subtitle
- print(" " * spaces_to_center(full_string) + full_string + "\n")
- def password_hash(given):
- hashed = ""
- for i in given:
- hashed += chr(ord(i) + 3)
- return hashed
- def add_to_database(given_object, table):
- if(table == "Staff"):
- cursor.execute("""
- INSERT INTO STAFF(FORENAME, SURNAME, DATE_OF_BIRTH, SUBJECT, GENDER)
- VALUES(?, ?, ?, ?, ?);""", (given_object.forename, given_object.surname,
- given_object.dateofbirth, given_object.subject,
- given_object.gender))
- print("Successfully added " + given_object.forename + " " + given_object.surname + " to the Staff table at ", end="")
- connection.commit()
- elif(table == "Login"):
- cursor.execute("""
- INSERT INTO LOGIN(USERNAME, HASHED_PASSWORD, USER_TYPE, RELATIVE_ID) VALUES(?, ?, ?, ?);""",
- (given_object.username, given_object.hashed_password,
- given_object.user_type, given_object.external_id,))
- print("Successfully added " + given_object.username + " to the Login table.")
- connection.commit()
- def get_index_by_element(given_array, search_term):
- index = 0
- found = False
- while not(found) and index < len(given_array):
- if(given_array[index] == search_term):
- found = True
- else:
- index += 1
- return index
- #----------------------------------------------------------------------------
- #--------------------------NO MORE USEFUL TOOLS------------------------------
- #----------------------------------------------------------------------------
- def login_prompt():
- cursor.execute("SELECT * FROM Login")
- list_of_logins = cursor.fetchall()
- logo()
- if(list_of_logins == []):
- title("LOG IN", "REQUIRES INITIAL SETUP!")
- else:
- title("LOG IN")
- pupil_usernames = []
- pupil_passwords = []
- staff_usernames = []
- staff_passwords = []
- admin_usernames = []
- admin_passwords = []
- valid_username = False
- username = -1
- for i in list_of_logins:
- if i[3] == "A":
- admin_usernames.append(i[1])
- admin_passwords.append(i[2])
- elif(i[3] == "S"):
- staff_usernames.append(i[1])
- staff_passwords.append(i[2])
- elif(i[3] == "P"):
- pupil_usernames.append(i[1])
- pupil_usernames.append(i[2])
- while(not(valid_username)):
- if(username != -1):
- logo()
- title("LOG IN")
- print("\nPlease enter a valid username and password!\n\n")
- username = input("Username: ")
- password = input("\nPassword: ")
- all_usernames = pupil_usernames + staff_usernames + admin_usernames
- if(username in all_usernames):
- valid_username = True
- else:
- valid_username = False
- if(not(list_of_logins == [])):
- if(username == "admin"):
- if(password == "password"):
- administrator_menu()
- elif(username in admin_usernames):
- given_password = admin_passwords[get_index_by_element(admin_usernames, username)]
- if(password_hash(password) == given_password):
- administrator_menu()
- elif(username in staff_usernames):
- given_password = staff_passwords[get_index_by_element(staff_usernames, username)]
- if(password_hash(password) == given_password):
- staff_menu()
- elif(username in pupil_usernames):
- given_password = pupil_passwords[get_index_by_element(pupil_usernames, username)]
- if(password_hash(password) == given_password):
- pupil_menu()
- else:
- if(username == "" and
- password_hash(password) == password_hash("")):
- administrator_menu()
- def administrator_menu():
- logo()
- title("ADMINISTRATOR MENU")
- print("1. Staff manager\n")
- print("2. Database manager\n")
- menu_pointer = input("> ")
- if(menu_pointer == "1"):
- staff_manager()
- elif(menu_pointer == "2"):
- database_manager()
- #no validation
- def staff_menu():
- logo()
- title("STAFF MENU")
- print("1. Test manager\n")
- print("2. Pupil manager\n")
- menu_pointer = input("> ")
- if(menu_pointer == "1"):
- test_manager()
- elif(menu_pointer == "2"):
- pupil_manager()
- def test_manager():
- logo()
- title("STAFF MENU", "Test Manager")
- print("1. Create a test\n")
- print("2. View a test\n")
- print("3. Set a test\n")
- menu_pointer = input("> ")
- if(menu_pointer == "1"):
- new_test = Test()
- create_test(new_test)
- elif(menu_pointer == "2"):
- view_test()
- elif(menu_pointer == "3"):
- set_test()
- def create_test(test_object):
- logo()
- title("STAFF MENU", "Create Test")
- create_list(4, 3,
- "1. Edit title",
- "2. Edit subject",
- "3. Edit questions",
- "-", "-", "-",
- test_object.title,
- test_object.subject,
- " ")
- print("\n5. Finish")
- #print("1. Edit name\n")
- #print("2. Edit date of birth\n")
- #print("3. Edit subject area\n")
- #print("4. Edit gender\n")
- menu_pointer = input("> ")
- if(menu_pointer == "1"):
- edit_title(test_object, "STAFF MENU", "Edit Title", "test")
- elif(menu_pointer == "2"):
- edit_subject(test_object, "ADMINISTRATOR MENU", "Edit Staff Date of Birth", "staff")
- elif(menu_pointer == "3"):
- edit_subject(test_object, "ADMINISTRATOR MENU", "Edit Subject Area")
- elif(menu_pointer == "4"):
- edit_gender(test_object, "ADMINISTRATOR MENU", "Edit Staff Gender")
- elif(menu_pointer == "5"):
- add_to_database(test_object, "Test")
- print("Created Test ID " + str(cursor.lastrowid))
- input("Continue...")
- test_manager()
- def pupil_manager():
- logo()
- title("STAFF MENU", "Pupil Manager")
- print("1. Add pupil\n")
- print("2. View/edit pupil\n")
- menu_pointer = input("> ")
- if(menu_pointer == "1"):
- add_pupil()
- elif(menu_pointer == "2"):
- view_pupil()
- def database_manager():
- logo()
- title("ADMINISTRATOR MENU", "Database Manager")
- print("1. Staff manager\n")
- print("2. Database manager\n")
- menu_pointer = input("> ")
- if(menu_pointer == "1"):
- staff_manager()
- elif(menu_pointer == "2"):
- database_manager()
- def staff_manager():
- logo()
- title("ADMINISTRATOR MENU", "Staff Manager")
- print("1. Add new member of staff\n")
- print("2. View/edit members of staff\n")
- print("3. Back\n")
- menu_pointer = input("> ")
- if(menu_pointer == "1"):
- new_staff = Staff()
- add_staff(new_staff)
- elif(menu_pointer == "2"):
- view_staff()
- elif(menu_pointer == "3"):
- administrator_menu()
- def add_staff(member_of_staff):
- logo()
- title("ADMINISTRATOR MENU", "Add New Member of Staff")
- create_list(4, 3,
- "1. Edit name",
- "2. Edit date of birth",
- "3. Edit subject area",
- "4. Edit gender",
- "-", "-", "-", "-",
- member_of_staff.forename + " " + member_of_staff.surname,
- member_of_staff.dateofbirth,
- member_of_staff.subject,
- member_of_staff.gender)
- print("\n5. Finish")
- #print("1. Edit name\n")
- #print("2. Edit date of birth\n")
- #print("3. Edit subject area\n")
- #print("4. Edit gender\n")
- menu_pointer = input("> ")
- if(menu_pointer == "1"):
- edit_name(member_of_staff, "ADMINISTRATOR MENU", "Edit Staff Name", "staff")
- elif(menu_pointer == "2"):
- edit_date_of_birth(member_of_staff, "ADMINISTRATOR MENU", "Edit Staff Date of Birth", "staff")
- elif(menu_pointer == "3"):
- edit_subject(member_of_staff, "ADMINISTRATOR MENU", "Edit Subject Area")
- elif(menu_pointer == "4"):
- edit_gender(member_of_staff, "ADMINISTRATOR MENU", "Edit Staff Gender")
- elif(menu_pointer == "5"):
- username = input("Username: ")
- password = input("Password: ")
- new_login = Login()
- new_login.username = username
- new_login.hashed_password = password_hash(password)
- new_login.user_type = "S"
- add_to_database(member_of_staff, "Staff")
- new_login.external_id = cursor.lastrowid
- add_to_database(new_login, "Login")
- print("Staff ID " + str(cursor.lastrowid))
- input("Continue...")
- staff_manager()
- def edit_name(given_object, bigtitle="", subtitle="", redirect="staff"):
- logo()
- title(bigtitle, subtitle)
- forename = input("Forename: ")
- surname = input("Surname: ")
- given_object.forename = forename
- given_object.surname = surname
- if(redirect == "staff"):
- add_staff(given_object)
- def edit_date_of_birth(given_object, bigtitle="", subtitle="", redirect="staff"):
- logo()
- title(bigtitle, subtitle)
- year = input("Year: ")
- month = input("Month: ")
- day = input("Day: ")
- given_object.set_date_of_birth(year, month, day)
- if(redirect == "staff"):
- add_staff(given_object)
- def edit_subject(given_object, bigtitle="", subtitle=""):
- logo()
- title(bigtitle, subtitle)
- subject = input("Subject: ")
- given_object.subject = subject
- add_staff(given_object)
- def edit_gender(given_object, bigtitle="", subtitle="", redirect="staff"):
- logo()
- title(bigtitle, subtitle)
- gender = input("Gender: ")
- given_object.gender = gender
- if(redirect == "staff"):
- add_staff(given_object)
- def view_staff():
- logo()
- title("ADMINISTRATOR MENU", "View/Edit Member of Staff")
- print("1. Search by name\n")
- print("2. Search by date of birth\n")
- print("3. Search by subject area\n")
- print("4. Search by gender\n")
- print("5. Back\n")
- menu_pointer = input("> ")
- if(menu_pointer == "1"):
- search_by_name("staff", "ADMINISTRATOR MENU", "Search Staff By Name")
- elif(menu_pointer == "2"):
- search_by_dateofbirth("staff")
- elif(menu_pointer == "3"):
- search_by_subject("staff")
- elif(menu_pointer == "4"):
- search_by_gender("staff")
- elif(menu_pointer == "5"):
- staff_manager()
- def search_by_name(table, bigtitle="", subtitle=""):
- logo()
- title(bigtitle, subtitle)
- forename = input("Forename: ")
- surname = input("Surname: ")
- search_results("name", table, 1, forename, surname)
- def search_by_dateofbirth(table, bigtitle="", subtitle=""):
- logo()
- title(bigtitle, subtitle)
- year = input("Year: ")
- month = input("Month: ")
- day = input("Day: ")
- search_results("dateofbirth", table, 1, year, month, day)
- def search_by_subject(table, bigtitle="", subtitle=""):
- logo()
- title(bigtitle, subtitle)
- subject = input("Subject: ")
- search_results("subject", table, 1, subject)
- def search_by_gender(table, bigtitle="", subtitle=""):
- logo()
- title(bigtitle, subtitle)
- gender = input("Gender: ")
- search_results("Gender", table, 1, gender)
- def search_results(search_type, table, page=1, *args):
- logo()
- if(table == "staff"):
- if(search_type == "name"):
- if(args[0] == ""):
- cursor.execute("""SELECT * FROM STAFF WHERE SURNAME=?""",
- (args[1],))
- elif(args[1] == ""):
- cursor.execute("""SELECT * FROM STAFF WHERE FORENAME=?""",
- (args[0],))
- else:
- cursor.execute("""SELECT * FROM STAFF WHERE FORENAME=? AND SURNAME=?""",
- (args[0], args[1]))
- if(search_type == "dateofbirth"):
- month = args[1]
- day = args[2]
- if(len(args[1]) == 1):
- month = "0" + month
- if(len(args[2]) == 1):
- day = "0" + day
- dateofbirth = args[0] + "-" + month + "-" + day
- cursor.execute("""SELECT * FROM STAFF WHERE DATE_OF_BIRTH=?""", (dateofbirth,))
- if(search_type == "subject"):
- cursor.execute("""SELECT * FROM STAFF WHERE SUBJECT=?""", (args[0],))
- if(search_type == "gender"):
- cursor.execute("""SELECT * FROM STAFF WHERE GENDER=?""", (args[0],))
- results = cursor.fetchall()
- title("ADMINISTRATOR MENU", "Search Results - Page (" + str(page) + "/" + str(1 + len(results)//4) + ")")
- max_pages = 1+len(results)//4
- if(len(results) < 4*page):
- for i in range(4*page - len(results)):
- results.append((" ", " ", " ", " ", " ", " "))
- rows = 4
- columns = 6
- create_list(5, 6,
- " ", "1.", "2.", "3.", "4.",
- "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],
- "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],
- "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],
- "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],
- "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])
- print("\n5. Next Page")
- print("6. Previous Page")
- print("7. Back")
- menu_pointer = input("> ")
- if(menu_pointer == "1"):
- view_staff_record(4*(page-1) + 0)
- elif(menu_pointer == "2"):
- view_staff_record(4*(page-1) + 1)
- elif(menu_pointer == "3"):
- view_staff_record(4*(page-1) + 2)
- elif(menu_pointer == "4"):
- view_staff_record(4*(page-1) + 3)
- elif(menu_pointer == "5"):
- if(page < max_pages):
- if(len(args) == 2):
- search_results(search_type, table, page+1, args[0], args[1])
- elif(len(args) == 1):
- search_results(search_type, table, page+1, args[0])
- else:
- if(len(args) == 2):
- search_results(search_type, table, page, args[0], args[1])
- elif(len(args) == 1):
- search_results(search_type, table, page, args[0])
- elif(menu_pointer == "6"):
- if(not(page <= 1)):
- if(len(args) == 2):
- search_results(search_type, table, page-1, args[0], args[1])
- elif(len(args) == 1):
- search_results(search_type, table, page-1, args[0])
- else:
- if(len(args) == 2):
- search_results(search_type, table, page, args[0], args[1])
- elif(len(args) == 1):
- search_results(search_type, table, page, args[0])
- elif(menu_pointer == "7"):
- view_staff()
- run_program()
- connection.close()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement