def update_allocation_config(self, scheme_id, config):
""" Update the allocation configuration table with the new """
# Sanity check all config fields
for config_field, config_value in config.items():
if not sanity_check(config_value) and sanity_check(scheme_id):
return "Error: one of the field did not pass the sanity check"
def delete_mentees(self, scheme_id, mentor_k_number):
""" Given the mentor k-number will delete all his mentees"""
if sanity_check(scheme_id) and sanity_check(mentor_k_number):
try:
self._dao.execute(
"DELETE FROM Allocation WHERE mentor_k_number = %s AND scheme_id = %s;",
(mentor_k_number, scheme_id))
self._dao.commit()
except Exception as e:
self._log.exception("Could not delete mentees")
raise e
def get_allocation(self, scheme_id, tee_number, tor_number):
if sanity_check(tee_number) and sanity_check(tor_number):
try:
return self._dao.execute(
"SELECT * FROM Allocation WHERE mentor_k_number = %s AND mentee_k_number = %s AND scheme_id = %s;",
(tor_number, tee_number, scheme_id))
except Exception as e:
self._log.exception("Could not get allocation")
raise e
else:
return "Error: one of the field did not pass the sanity check"
def make_manual_allocation(self, scheme_id, tee_number, tor_number):
if sanity_check(tee_number) and sanity_check(tor_number):
try:
return self._dao.execute(
"INSERT INTO Allocation VALUES(%s, %s, %s);",
(scheme_id, tor_number, tee_number))
self._dao.commit()
except Exception as e:
self._log.exception("Could not make manual allocation")
raise e
else:
return abort(500)
def update_gender(self, scheme_id, k_number, gender):
if sanity_check(scheme_id) and sanity_check(k_number):
# sanity check dob
try:
self._dao.execute(
"UPDATE Student SET gender = %s WHERE k_number = %s AND scheme_id = %s;",
(gender, k_number, scheme_id))
self._dao.commit()
except Exception as e:
self._log.exception("Could not alter gender")
raise e
else:
return "Error: one of the field did not pass the sanity check"
def activateAccount(self, scheme_id, k_number):
if sanity_check(scheme_id) and sanity_check(k_number):
try:
self._dao.execute(
"UPDATE Student SET email_confirmed = True WHERE k_number = %s AND scheme_id = %s;",
(k_number, scheme_id))
self._dao.commit()
except Exception as e:
self._log.exception("Could not activate account")
raise e
else:
return "Error: one of the field did not pass the sanity check"
def update_buddy_limit(self, scheme_id, k_number, buddy_limit=1):
""" Update the buddy limit for a user """
if sanity_check(scheme_id) and sanity_check(k_number):
# sanity check dob
try:
self._dao.execute(
"UPDATE Student SET buddy_limit = %s WHERE k_number = %s AND scheme_id = %s;",
(buddy_limit, k_number, scheme_id))
self._dao.commit()
except Exception as e:
self._log.exception("Could not alter buddy_limit")
raise e
else:
return "Error: one of the field did not pass the sanity check"
def get_allocation_config(self, scheme_id):
""" Retrieve the current allocation configuration"""
if sanity_check(scheme_id):
try:
return self._dao.execute(f"SELECT age_weight, gender_weight, hobby_weight, interest_weight FROM Allocation_Config WHERE scheme_id = {to_str(scheme_id)};")[0]
except Exception as e:
self._log.exception("Could not get allocation config")
return abort(500)
def alter_admin_status(self, scheme_id, k_number, is_admin):
if sanity_check(scheme_id) and sanity_check(k_number) and sanity_check(
is_admin):
try:
self._dao.execute(
"UPDATE Student SET is_admin = %s WHERE k_number = %s AND scheme_id = %s;",
(is_admin, k_number, scheme_id))
succ = self._dao.rowcount()
self._dao.commit()
return succ
except Exception as e:
self._log.exception("Could not alter admin status")
raise e
else:
return "Error: one of the field did not pass the sanity check"
def suspend_scheme(self, scheme_id):
"""Suspends a given scheme"""
if sanity_check(scheme_id):
try:
self._dao.execute(
"UPDATE Scheme SET is_active = CASE WHEN is_active = 1 THEN 0 ELSE 1 END WHERE scheme_id = %s;", (scheme_id, ))
self._dao.commit()
except Exception as e:
self._log.exception("Could Not suspend scheme")
raise e
def get_all_students_data_basic(self, scheme_id):
if sanity_check(scheme_id):
try:
# add has matches
return self._dao.execute(
"SELECT k_number, first_name, last_name, gender, is_mentor FROM Student WHERE Student.scheme_id = %s ORDER BY last_name ASC;",
(scheme_id, ))
except Exception as e:
self._log.exception("Could not get all data for a student")
raise e
def create_allocation_config_entry(self, scheme_id):
"""Inserts an entry for new scheme into allocation_config"""
if sanity_check(scheme_id):
try:
self._dao.execute("INSERT INTO Allocation_Config VALUES(%s, 1, 10, 5, 5, 0);", (scheme_id, ))
succ = self._dao.rowcount()
self._dao.commit()
return succ
except Exception as e:
self._log.exception("Could Not Create New Scheme")
raise e
def delete_scheme(self, scheme_id):
"""Suspends a given scheme"""
if sanity_check(scheme_id):
try:
self._dao.execute("DELETE FROM Scheme WHERE scheme_id = %s;",
(scheme_id, ))
succ = self._dao.rowcount()
self._dao.commit()
return succ
except Exception as e:
self._log.exception("Could Not delete scheme")
raise e
def get_mentee_details(self, scheme_id, k_number):
if sanity_check(k_number):
try:
return self._dao.execute(
"SELECT k_number, first_name, last_name, year_study FROM Student, Allocation WHERE Student.k_number = Allocation.mentee_k_number AND Allocation.mentor_k_number = %s AND Student.scheme_id = %s;",
(k_number, scheme_id))
except Exception as e:
self._log.exception("Could not get mentee details")
raise e
else:
return abort(500)
def get_manual_allocation_matches(self, scheme_id, k_number, is_tor):
if sanity_check(k_number): # and _sanity_check(is_tor):
join_col = 'mentee_k_number' if is_tor else 'mentor_k_number'
other_col = 'mentee_k_number' if not is_tor else 'mentor_k_number'
try:
return self._dao.execute(
f"SELECT * FROM (SELECT k_number, first_name, last_name, gender, year_study, COUNT(Allocation.{join_col}) AS matches FROM Student LEFT JOIN Allocation ON Student.k_number = Allocation.{join_col} AND Student.scheme_id = Allocation.scheme_id WHERE is_mentor != %s AND k_number != %s AND Student.scheme_id = %s GROUP BY Student.k_number ORDER BY matches, k_number ASC) AS matches WHERE NOT EXISTS (SELECT NULL FROM Allocation WHERE {other_col} = %s AND {join_col} = matches.k_number AND scheme_id = %s);",
(is_tor, k_number, scheme_id, k_number, scheme_id))
except Exception as e:
self._log.exception("Could not get manual allocation matches")
raise e
else:
return abort(500)
def test_sanity():
assert helpers.sanity_check(True) == True
assert helpers.sanity_check(12) == True
assert helpers.sanity_check('abc') == True
assert helpers.sanity_check("a 4 1 bg") == True