def change_education_exp(student_id, field, new_data):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "update education_exp set {} = '{}' where student_id = '{}';".format(field, new_data, student_id)
database_object.update(sql)
database_object.close()
def delete_education_exp(student_id):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "delete from education_exp where student_id = '{}';".format(student_id)
database_object.update(sql)
database_object.close()
def create_tasks(phase_uuid,
task_name,
deadline,
mark_release=None,
submit_require=0,
spec_address="None"):
task_uuid = uuid.uuid1()
dbconfig = {"dbname": "comp9323"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
if mark_release is None:
sql = "insert into tasks (task_uuid, phase_uuid, task_name, deadline, submit_require, spec_address) values \
('{}', '{}', '{}', '{}', {}, '{}');".format(task_uuid, phase_uuid,
task_name, deadline,
submit_require, spec_address)
else:
sql = "insert into tasks values \
('{}', '{}', '{}', '{}', '{}', {}, '{}');".format(task_uuid, phase_uuid,
task_name, deadline,
mark_release,
submit_require,
spec_address)
database_object.update(sql)
database_object.close()
return task_uuid
def create_education_exp(student_id, major, university, degree):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "insert into education_exp values ('{}', '{}', '{}', '{}');".format(student_id, major, university, degree)
database_object.update(sql)
database_object.close()
def delete_group(group_uuid):
dbconfig = {"dbname": "comp9323"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "delete from groups where group_uuid = '{}';".format(group_uuid)
database_object.update(sql)
database_object.close()
def get_ungroup_student(project_uuid):
dbconfig = {"dbname": "comp9323"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "select u_i.email, u_i.name from enrol_project e_p, user_info u_i where e_p.project_uuid = '{}' and e_p.email = u_i.email and e_p.user_type = 'student';".format(
project_uuid)
all_student_list = database_object.search(sql)
sql = "select g_r.email, count(*) from groups g, group_relation g_r where g.project_uuid = '{}' and g.group_uuid = g_r.group_uuid group by g_r.email;".format(
project_uuid)
ingroup_student_list = database_object.search(sql)
database_object.close()
all_student_list = convert_result_to_dict(temp_result=all_student_list,
key_list=["email", "name"])
ingroup_student_list = convert_result_to_dict(
temp_result=ingroup_student_list, key_list=["email", "count"])
all_list = [item["email"] for item in all_student_list]
all_student_list = {
item["email"]: item["name"]
for item in all_student_list
}
in_list = list()
for item in ingroup_student_list:
if item["count"] > 0:
in_list.append(item["email"])
result = list()
for email in all_list:
if email not in in_list:
result.append({'email': email, 'name': all_student_list[email]})
return result
def create_job_info(info_uuid, job_uuid, company_id, address, description = "None", salary = 0):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "insert into job_info values ('{}', '{}', '{}', '{}', '{}', '{}');".format(info_uuid, job_uuid, company_id, description, salary,address)
database_object.update(sql)
database_object.close()
def create_skill(uuid, skill_name):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "insert into skills values ('{}', '{}');".format(uuid, skill_name)
database_object.update(sql)
database_object.close()
def create_resume(resume_uuid, username, address):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "insert into resume values ('{}', '{}', '{}');".format(resume_uuid, username, address)
database_object.update(sql)
database_object.close()
def delete_resume(resume_uuid):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "delete from resume where resume_id = '{}';".format(resume_uuid)
database_object.update(sql)
database_object.close()
def delete_course_from_list(username, code):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "delete from course_list where student_id = '{}' and code = '{}';".format(username, code)
database_object.update(sql)
database_object.close()
def add_course_to_list(username, code, certificat = 1):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "insert into course_list values ('{}', '{}', {});".format(username, code, certificat)
database_object.update(sql)
database_object.close()
def create_job_and_skill_link(job_uuid, skill_uuid, relevance = 1.0):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "insert into job_and_skill values ('{}', '{}', {});".format(job_uuid, skill_uuid, relevance)
database_object.update(sql)
database_object.close()
def create_user(user_profile):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "insert into user_info values ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')';".format(user_profile["username"], user_profile.get("name", user_profile["username"]), user_profile["password"], user_profile["email"], user_profile.get("location", "Sydney"), user_profile["type"], user_profile.get("description", "Nothing to show."), user_profile.get("photo", "None"))
database_object.update(sql)
database_object.close()
def change_job_info(info_uuid, field, new_data):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "update job_info set {} = '{}' where job_info_id = '{}';".format(field, new_data, info_uuid)
database_object.update(sql)
database_object.close()
def change_user_type(username, type_code):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "update user_info set type = {} where username = '******';".format(type_code, username)
database_object.update(sql)
database_object.close()
def delete_job_info(info_uuid):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "delete from job_info where job_info_id = '{}';".format(info_uuid)
database_object.update(sql)
database_object.close()
def student_get_self_unsubmit_reminder(email, project_uuid):
dbconfig = {"dbname": "comp9323"}
result = list()
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "select * from reminder where project_uuid = '{}' and submit_check = 'yes' order by post_time desc;".format(
project_uuid)
require_submit_reminder = database_object.search(sql)
key_list = [
"reminder_uuid", "master", "project_uuid", "ass_uuid", "message",
"submit_check", "post_time"
]
require_submit_reminder = convert_result_to_dict(require_submit_reminder,
key_list)
sql = "select rem.* from reminder rem, submits s, groups g, group_relation g_r \
where rem.project_uuid = '{}' and rem.project_uuid = g.project_uuid and \
g.group_uuid = g_r.group_uuid and g_r.email = '{}' and rem.submit_check = \
'yes' order by rem.post_time desc;".format(project_uuid, email)
submit_reminder = database_object.search(sql)
submit_reminder = convert_result_to_dict(submit_reminder, key_list)
database_object.close()
submit_reminder_list = [
reminder['reminder_uuid'] for reminder in submit_reminder
]
for reminder in require_submit_reminder:
if reminder['reminder_uuid'] not in submit_reminder_list:
result.append(reminder)
return result
def send_resume(enrol_id, student_id, company_id, resume_id):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "insert into enrolment values ('{}', '{}', '{}', '{}');".format(enrol_id, student_id, company_id, resume_id)
database_object.update(sql)
database_object.close()
def random_group(project_uuid, group_data):
dbconfig = {"dbname": "comp9323"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
print(group_data)
sql = "select * from projects where project_uuid = '{}';".format(
project_uuid)
project_info = database_object.search(sql)
key_list = [
"project_uuid", "master", "project_name", "deadline", "mark_release",
"spec_address", "group_method"
]
project_info = convert_result_to_dict(project_info, key_list)[0]
for i in group_data:
group_uuid = uuid.uuid1()
group_name = "Group {}".format(i + 1)
description = "{} group {}".format(project_info["project_name"], i + 1)
sql = "insert into groups (group_uuid, group_name, project_uuid, description) values \
('{}', '{}', '{}', '{}');".format(group_uuid, group_name,
project_uuid, description)
database_object.update(sql)
for student in group_data[i]:
sql = "insert into group_relation values \
('{}', '{}', {});".format(student['email'], group_uuid, 1)
database_object.update(sql)
database_object.close()
def cancel_resume_send(enrol_id):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "delete from enrolment where enrol_id = '{}';".format(enrol_id)
database_object.update(sql)
database_object.close()
def create_projects(master,
project_name,
deadline,
mark_release=None,
spec_address="None",
group_method=0):
project_uuid = uuid.uuid1()
dbconfig = {"dbname": "comp9323"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
if mark_release is None:
sql = "insert into projects (project_uuid, master, project_name, deadline, spec_address, group_method) values \
('{}', '{}', '{}', '{}', '{}', {});".format(project_uuid, master,
project_name, deadline,
spec_address, group_method)
else:
sql = "insert into projects values \
('{}', '{}', '{}', '{}', '{}', '{}', {});".format(project_uuid, master,
project_name, deadline,
mark_release,
spec_address,
group_method)
database_object.update(sql)
database_object.close()
return project_uuid
def change_user_info(username, field, new_data):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "update user_info set {} = '{}' where username = '******';".format(field, new_data, username)
database_object.update(sql)
database_object.close()
def create_course(code, course_name):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "insert into courses values ('{}', '{}');".format(code, course_name)
database_object.update(sql)
database_object.close()
def __clear_exist_target_list(self, item_id):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "delete from target_list where item_id = '{}';".format(item_id)
database_object.update(sql)
database_object.close()
def delete_tasks(task_uuid):
dbconfig = {"dbname": "comp9323"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "delete from tasks where task_uuid = '{}';".format(task_uuid)
database_object.update(sql)
database_object.close()
def set_recommend_bonus(job_info_id, user_id, bonus_mark):
dbconfig = {"dbname": "comp9900"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "update recommend set bonus = {} where master_id = '{}' and item_id = '{}';".format(
bonus_mark, job_info_id, user_id)
database_object.update(sql)
database_object.close()
def change_reminder_info(reminder_uuid, field, new_data):
dbconfig = {"dbname": "comp9323"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "update reminder set {} = '{}' where reminder_uuid = '{}';".format(
field, new_data, reminder_uuid)
database_object.update(sql)
database_object.close()
def change_user_type(email, new_type):
dbconfig = {"dbname": "comp9323"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "update user_info set user_type = '{}' where email = '{}';".format(
new_type, email)
database_object.update(sql)
database_object.close()
def change_tasks_submit_require(task_uuid, new_submit_require):
dbconfig = {"dbname": "comp9323"}
database_object = database_lib.Database_object(dbconfig)
database_object.open()
sql = "update tasks set submit_require = {} where task_uuid = '{}';".format(
new_submit_require, task_uuid)
database_object.update(sql)
database_object.close()