def get_assigment_by_id(task_id):
db = Database()
assigment_data = db.get("SELECT * FROM Assignments WHERE id=(?)",
(task_id, ))[0]
answers = db.get(
"SELECT * FROM Answers WHERE Assignment_ID=(?)",
(assigment_data[0], )) # get answers for assigment with a[0]-id
answers = [
Answer(d[0], d[1], d[2], d[3], d[4], d[5], d[6]) for d in answers
]
assigment_object = Assigment(assigment_data[0], assigment_data[1],
assigment_data[2], answers)
db.close()
return assigment_object
def submit_team_answer(cls, answer_text, student_id, assigment_id):
db = Database()
team_id = db.get("SELECT Team_ID FROM Student WHERE id=(?)",
(student_id, ))[0][0]
is_answer_exist = db.get(
"SELECT EXISTS (SELECT * FROM Answers WHERE Team_ID=(?) AND Assignment_ID=(?))",
(team_id, assigment_id))
if is_answer_exist[0][0]:
db.set(
"UPDATE Answers SET Answer_text=(?) WHERE Team_ID=(?) AND Assignment_ID=(?)",
(answer_text, team_id, assigment_id))
else:
db.set(
"INSERT INTO Answers(Answer_text, Team_ID, Assignment_ID) VALUES (?,?,?)",
(answer_text, team_id, assigment_id))
db.close()
def get_list_of_assigments():
db = Database()
assigments_data_list = db.get("SELECT * FROM Assignments")
assigments_objects_list = []
for a in assigments_data_list:
answers = db.get(
"SELECT * FROM Answers WHERE Assignment_ID=(?)",
(a[0], )) # get answers for assigment with a[0]-id
answers = [
Answer(d[0], d[1], d[2], d[3], d[4], d[5], d[6])
for d in answers
]
assigments_objects_list.append(Assigment(a[0], a[1], a[2],
answers))
db.close()
return assigments_objects_list
def get_employee_by_id(cls, idx):
db = Database()
query = """SELECT * FROM Employee WHERE id =(?)"""
person = db.get(query, (idx, ))[0]
print(person)
new_employee = Employee(person[0], person[1], person[2], person[3],
person[4], person[5], person[6], person[7])
return new_employee
def get_mentor_by_id(cls, idx):
db = Database()
query = """SELECT * FROM Mentor WHERE id =(?)"""
person = db.get(query, (idx, ))[0]
print(person)
new_mentor = Mentor(person[0], person[1], person[2], person[3],
person[4], person[5], person[6], person[7])
return new_mentor
def submit_answer(cls, answer_text, student_id, assigment_id):
db = Database()
task_type = db.get("SELECT Task_type FROM Assignments WHERE id=(?)",
(assigment_id, ))[0][0]
if task_type == "Personal":
cls.submit_personal_answer(answer_text, student_id, assigment_id)
elif task_type == "Team":
cls.submit_team_answer(answer_text, student_id, assigment_id)
db.close()
def get_student_by_id(cls, idx):
db = Database()
query = """SELECT * FROM Student WHERE id =(?)"""
person = db.get(query, (idx, ))[0]
print(person)
person_object = Student(person[0], person[1], person[2], person[3],
person[4], person[5], person[6], person[7],
person[9], person[10])
db.close()
return person_object
def get_list_of_mentors(cls):
list_of_mentors = []
db = Database()
query = """SELECT * FROM Mentor;"""
for person in db.get(query):
person_object = Mentor(person[0], person[1], person[2], person[3],
person[4], person[5], person[6], person[7])
list_of_mentors.append(person_object)
db.close()
return list_of_mentors
def get_list_of_employees(cls):
list_of_employees = []
db = Database()
query = """SELECT * FROM Employee;"""
for person in db.get(query):
person_object = Employee(person[0], person[1], person[2],
person[3], person[4], person[5],
person[6], person[7])
list_of_employees.append(person_object)
db.close()
return list_of_employees
def get_list_of_students(cls):
list_of_students = []
db = Database()
query = """SELECT * FROM Student;"""
for person in db.get(query):
person_object = Student(person[0], person[1], person[2], person[3],
person[4], person[5], person[6], person[7],
person[9], person[10])
list_of_students.append(person_object)
db.close()
return list_of_students
def login(cls, login, passw):
db = Database()
query = ("""
SELECT * from (SELECT id, name, Login, Password, 'Student' as user_type FROM Student
UNION
SELECT id, Name, Login, Password, 'Mentor' as user_type FROM Mentor
UNION
SELECT id, Name, Login, Password, 'Employee' as user_type FROM Employee
UNION
SELECT id, Name, Login, Password, 'Manager' as user_type FROM Manager)
where Login = ? and Password = ?
""")
# awesome query, I love union <3
values = ((login, passw))
user = db.get(query, values)
if not user:
return
user = db.get(query, values)[0]
db.close()
user_dict = {'id': user[0], 'name': user[1], 'type': user[4]}
return user_dict
def submit_personal_answer(cls, answer_text, student_id, assigment_id):
db = Database()
is_answer_exist = db.get(
"SELECT EXISTS (SELECT * FROM Answers WHERE Student_ID=(?) AND Assignment_ID=(?))",
(student_id, assigment_id))
if is_answer_exist[0][0]:
db.set(
"UPDATE Answers SET Answer_text=(?) WHERE Student_ID=(?) AND Assignment_ID=(?)",
(answer_text, student_id, assigment_id))
else:
db.set(
"INSERT INTO Answers(Answer_text, Student_ID, Assignment_ID) VALUES (?,?,?)",
(answer_text, student_id, assigment_id))
db.close()
def get_answer_by_id(cls, id):
db = Database()
task_data = db.get("SELECT * FROM Answers WHERE id=(?)", (id, ))[0]
return Answer(*task_data)
db.close()
