def projects_recommdation(self, conn):
"""Recommend projects for collaborator based on skill/exp/edu
Param:
conn -- database connection
Return:
recommendation projects and roles
"""
skills = self.skill_dict
edu = self.education
project_list = []
#strict matching
strict_matching_count = 0
# query on each skill/exp pair
for skill, exp in skills.items():
# match on role
query = "SELECT projectID as proj_id, ID as role_id, skill FROM project_role pr, role_skill rs WHERE pr.ID = rs.roleID and skill = " + str(
skill) + " AND experience = " + str(
exp) + " AND education = " + str(
edu) + " ORDER BY ID, projectID, skill;"
result = conn.execute(query)
for i in range(result.rowcount):
row = result.fetchone()
proj = Project.get_by_pid_rid_skill(conn, row['proj_id'],
row['role_id'],
row['skill'])
is_exist = False
# append without duplicate
for project in project_list:
if project['id'] == proj['id']: is_exist = True
if not is_exist:
project_list.append(proj)
strict_matching_count += 1
#relaxing matching
relaxing_matching_count = 0
for skill, exp in skills.items():
query = "SELECT projectID as proj_id, ID as role_id, skill FROM project_role pr, role_skill rs WHERE pr.ID = rs.roleID and skill = " + str(
skill) + " AND experience <= " + str(
exp - 1) + " AND education >= " + str(
edu - 1) + " ORDER BY ID, projectID, skill;"
result = conn.execute(query)
for i in range(result.rowcount):
row = result.fetchone()
proj = Project.get_by_pid_rid_skill(conn, row['proj_id'],
row['role_id'],
row['skill'])
is_exist = False
for project in project_list:
if project['id'] == proj['id']: is_exist = True
if not is_exist:
project_list.append(proj)
relaxing_matching_count += 1
if len(project_list) == 0: return None
return {
'projects': project_list,
'strict matching count': strict_matching_count,
'relaxing matching count': relaxing_matching_count
}
def notify_result(self, conn, smtp, accept=True):
"""Notify application result to applicant through email
Param:
conn -- database connection
smtp -- smtp server
accept -- application approved or not
Return:
smtp send result
"""
proj = Project.get_by_id(conn, self.project_id)
role = Role.get_by_id(conn, self.role_apply)
col = Collaborator.get_by_id(conn, self.applicant)
dre = Dreamer.get_by_id(conn, proj['owner'])
subject = '[DreamMatchmaker]You have an application status update'
if accept:
acceptance = 'accepted'
else:
acceptance = 'declined'
content = f'''<p>Hello {col['name']},</p>
<p> Your application to join project <b>"{proj['title']}"</b> as <b>"{role['title']}"</b></p>
<p> has been {acceptance}.</p>
<p>Dream Matchmaker Team</p>
'''
result = smtp.send_email_html(col['email'], content, subject)
return result
def notify_invitor(self, conn, smtp, accept=True):
"""Send an email to invitor for notification
Param:
conn -- database connection
smtp -- smtp server object
accept -- boolean if invitation is accepted
Return:
Smtp send result
"""
proj = Project.get_by_id(conn, self.project_id)
role = Role.get_by_id(conn, self.role_invite)
col = Collaborator.get_by_id(conn, self.invitee)
dre = Dreamer.get_by_id(conn, self.invitor)
subject = '[DreamMatchmaker]You have a invitation status update'
if accept:
acceptance = 'accepted'
else:
acceptance = 'declined'
content = f'''<p>Hello {dre['name']},</p>
<p> Your invitation to <b>{col['name']}</b> for project <b>"{proj['title']}"</b>, role <b>"{role['title']}"</b></p>
<p> has been <b>{acceptance}</b>.</p>
<p>Dream Matchmaker Team</p>
'''
result = smtp.send_email_html(dre['email'], content, subject)
return result
def create_project():
project_name = request.form["projectName"]
# TODO: check if project with this name already exists
# creates project
project = Project.create_project(project_name)
return url_for("get_project", project_name=project_name, tab="settings")
def delete_project(project_name):
log = logging.getLogger("WICA")
log.debug("deleting project {}".format(project_name))
project = Project.load_project(project_name)
if project is None:
return abort(404)
project.delete()
return url_for("get_projects")
def get_project(project_name, tab):
project = Project.load_project(project_name)
if project is None:
return abort(404)
if tab == "settings":
return render_template("project_settings.jinja", project=project)
elif tab == "replays":
return render_template("project_replays.jinja", project=project)
def get_project_replay_data(project_name, replay_name):
log = logging.getLogger("PROJECT")
log.debug("project_name = {} replay_name = {}".format(project_name, replay_name))
project = Project.load_project(project_name)
if project is None:
return abort(404)
json_data = convert_to_json(project.replay(replay_name))
return json_data
def record_replay(project_name):
project = Project.load_project(project_name)
if project is None:
return abort(404)
if "replay" in request.form:
project.record_replay()
return "recording replay"
return "wrong input data"
def evolve_weights(project_name):
project = Project.load_project(project_name)
if project is None:
return abort(404)
if "evolve" in request.form:
thread_handle = project.evolve_weights()
add_thread(thread_handle)
return "evolve thread started"
return "wrong input data"
def update_project_config(project_name):
log = logging.getLogger("WICA")
log.debug("trying update project {}".format(project_name))
project = Project.load_project(project_name)
if project is None:
return abort(404)
for option in request.form.keys():
log.debug("form[{}] = {}".format(option, request.form[option]))
project.update_config(request.form)
project.save()
return url_for("get_project", project_name=project_name, tab="settings")
def get_my_projects(conn, user_ID):
"""Get collaborator's engaged project list by user id
Param:
conn -- database connection
user_ID -- collaborator digital id
Return:
List of engaged+followed project info
"""
projects_joined = []
# query application table for active/finished project
query_1 = "SELECT projectID, role_applied FROM application WHERE applicant = " + str(
user_ID) + " AND (status = 1 or status = 9) ORDER BY projectID;"
result_1 = conn.execute(query_1)
for i in range(result_1.rowcount):
row_1 = result_1.fetchone()
# prepare fetch engaged project information
if (row_1['projectID'],
row_1['role_applied']) not in projects_joined:
projects_joined.append(
(row_1['projectID'], row_1['role_applied']))
# query invitation table for active/finished project
query_2 = "SELECT projectID, role_invited FROM invitation WHERE invitee = " + str(
user_ID) + " AND (status = 1 or status = 9) ORDER BY projectID;"
result_2 = conn.execute(query_2)
for j in range(result_2.rowcount):
row_2 = result_2.fetchone()
# prepare fetch engaged project information
if (row_2['projectID'],
row_2['role_invited']) not in projects_joined:
projects_joined.append(
(row_2['projectID'], row_2['role_invited']))
follow_list = Collaborator.get_my_followed_projects(conn, user_ID)
# merge list - remove duplicate
joined_projects = [a[0] for a in projects_joined]
# engaged project has larger priority
for proj in follow_list:
if proj['id'] in joined_projects:
follow_list.remove(proj)
myproject_list = []
# fetch project information with specific role
for k in range(len(projects_joined)):
proj = Project.get_by_pid_rid(conn, projects_joined[k][0],
projects_joined[k][1])
proj['follow'] = False
myproject_list.append(proj)
# final merge of project info list
myproject_list.extend(follow_list)
# move finished projects to bottom
myproject_list.sort(key=lambda p: p['status'])
return {'my_projects': myproject_list, 'amount': len(myproject_list)}
def remove_project_field(project_name):
log = logging.getLogger("WICA")
log.debug("trying remove {} configuration field".format(project_name))
project = Project.load_project(project_name)
if project is None:
return abort(404)
if "option" not in request.form.keys():
log.debug("removing section {}".format(request.form["section"]))
project.remove_section(request.form["section"])
else:
log.debug("removing option {}.{}".format(request.form["section"], request.form["option"]))
project.remove_option(request.form["section"], request.form["option"])
project.save()
return "successfully removed"
def info(self, conn):
"""Return invitation info
Param:
conn -- database connection
Return:
invitation info
"""
return {
'id': self.id,
'project_id': self.project_id,
'role_invite': self.role_invite,
'invitor': self.invitor,
'invitee': self.invitee,
'general_text': self.general_text,
'status': self.status,
'Role_information': Role.get_text_by_id(conn, self.role_invite),
'Project_title': Project.get_by_id(conn, self.project_id)['title']
}
def get_by_invitee(conn, user_id):
"""Get all invitations for a collaborator's
Param:
conn -- database connection
user_id -- collaborator digit id
Return:
list of invitations info
"""
# IMPORTANT: Avoid corss import
# This import cannot be moved up or it will cause infinite recursive import
from projects.role import Role
query = "SELECT * FROM invitation where invitee = " + str(
user_id) + " order by ID desc;"
result = conn.execute(query)
invitations = []
for i in range(result.rowcount):
row = result.fetchone()
if row['status'] == -1:
invitation_status = 'Pending'
if row['status'] == 0:
invitation_status = 'Declined'
if row['status'] == 1:
invitation_status = 'Approved'
if row['status'] == 9:
invitation_status = 'Finished'
invi = {
'InvitationID': row['ID'],
'projectID': row['projectID'],
'Role_invited': row['role_invited'],
'Invitor': row['invitor'],
'Invitor_name': Dreamer.get_by_id(conn,
row['invitor'])['name'],
'Invitee': row['invitee'],
'Invitation_status': invitation_status,
'General_text': row['general_text'],
'Role_information':
Role.get_text_by_id(conn, row['role_invited']),
'Project_title': Project.get_by_id(conn,
row['projectID'])['title']
}
invitations.append(invi)
return {'invitations': invitations, 'amount': result.rowcount}
def notify_invitee_auto_decline(self, conn, smtp):
"""Send an email to invitees for auto decline notification
Param:
conn -- database connection
smtp -- smtp server object
Return:
Smtp send result
"""
proj = Project.get_by_id(conn, self.project_id)
role = Role.get_by_id(conn, self.role_invite)
col = Collaborator.get_by_id(conn, self.invitee)
dre = Dreamer.get_by_id(conn, self.invitor)
subject = '[DreamMatchmaker]You have a invitation status update'
content = f'''<p>Hello {col['name']},</p>
<p> Your invitation from <b>{dre['name']}</b> for project <b>"{proj['title']}"</b>, role <b>"{role['title']}"</b></p>
<p> has been automaticlly cancelled due to "Role is fullfilled".</p>
<p>Dream Matchmaker Team</p>
'''
result = smtp.send_email_html(col['email'], content, subject)
return result
def get_followed_projects(conn, id):
"""Get projects user followed by user id
Param:
conn -- database connection
id -- dreamer digital id
Return:
followed project info list
"""
# query subscription table
query = f"SELECT * FROM subscription WHERE is_dreamer=1 AND d_subscriber={id};"
result = conn.execute(query)
project_list = []
for i in range(result.rowcount):
# fetch project information
row = result.fetchone()
pid = row['projectID']
proj_info = Project.get_by_id(conn, pid)
proj_info['follow'] = True
project_list.append(proj_info)
return project_list
def search_list(self, conn, description, category, order_by, order):
"""Search whole project list with matching of collaborator's info
Param:
conn -- database connection
description -- search for project description
category -- project category
order_by -- order item
order -- order (ASC/DESC)
Return:
project list based on limits
"""
skills = self.skill_dict
edu = self.education
project_list = []
# match with each skill-exp pair
for skill, exp in skills.items():
# query project table
if category == -1:
query = "SELECT project.ID as pID, project_role.ID as rID, project_title, project.last_update as last_update FROM project, project_role, role_skill WHERE project.ID = projectID AND description LIKE \'%%" + description + "%%\' AND skill = " + str(
skill) + " AND experience <= " + str(
exp) + " AND education <= " + str(
edu) + " ORDER BY " + order_by + " " + order + ";"
else:
query = "SELECT project.ID as pID, project_role.ID as rID, project_title, project.last_update as last_update FROM project, project_role, role_skill WHERE project.ID = projectID AND description LIKE \'%%" + description + "%%\' AND category = " + str(
category
) + " AND skill = " + str(skill) + " AND experience <= " + str(
exp) + " AND education <= " + str(
edu) + " ORDER BY " + order_by + " " + order + ";"
result = conn.execute(query)
for i in range(result.rowcount):
row = result.fetchone()
# append info without duplicate
proj = Project.get_by_id(conn, row['pID'])
is_exist = False
for project in project_list:
if project['id'] == proj['id']: is_exist = True
if not is_exist:
project_list.append(proj)
if len(project_list) == 0: return None
project_list.sort(key=lambda p: p['status'])
return {'projects': project_list, 'amount': result.rowcount}
def get_my_followed_projects(conn, user_ID):
"""Get collaborator's followed project list by user id
Param:
conn -- database connection
user_ID -- collaborator digital id
Return:
List of followed project info
"""
projects_followed = []
# query subscription tabale
query = f"SELECT * FROM subscription WHERE is_dreamer=0 AND c_subscriber={user_ID}"
result = conn.execute(query)
proj_list = []
# fetch project information
for i in range(result.rowcount):
row = result.fetchone()
proj_info = Project.get_by_proj_id(conn,
row['projectID']).text_info()
proj_info['follow'] = True
proj_list.append(proj_info)
return proj_list
def notify_invitee(self, conn, smtp):
"""Send an email to invitee for notification
Param:
conn -- database connection
smtp -- smtp server object
Return:
Smtp send result
"""
proj = Project.get_by_id(conn, self.project_id)
role = Role.get_by_id(conn, self.role_invite)
col = Collaborator.get_by_id(conn, self.invitee)
dre = Dreamer.get_by_id(conn, self.invitor)
subject = '[DreamMatchmaker]You have a new project invitation'
content = f'''<p>Hello {col['name']},</p>
<p> <b>{dre['name']}</b> has invited you to join project <b>"{proj['title']}"</b> as <b>"{role['title']}"</b>.</p>
<p> The following message is from the invitor:</p>
<p> {self.general_text}<p>
<p> You can view the invitation in your dashboard.</p>
<p>Dream Matchmaker Team</p>
'''
result = smtp.send_email_html(col['email'], content, subject)
return result
def notify_applicant(self, conn, smtp):
"""Notify application to applicant through email
Param:
conn -- database connection
smtp -- smtp server
Return:
smtp send result
"""
proj = Project.get_by_id(conn, self.project_id)
role = Role.get_by_id(conn, self.role_apply)
col = Collaborator.get_by_id(conn, self.applicant)
dre = Dreamer.get_by_id(conn, proj['owner'])
subject = '[DreamMatchmaker]You have created a new project application'
content = f'''<p>Hello {col['name']},</p>
<p> You have applied to join project <b>"{proj['title']}"</b> as <b>"{role['title']}"</b>.</p>
<p> The following message is leaved to project owner:</p>
<p> {self.general_text}<p>
<p> The project owner will view your application. The result will be notified through email.</p>
<p>Dream Matchmaker Team</p>
'''
result = smtp.send_email_html(col['email'], content, subject)
return result
def notify_owner(self, conn, smtp):
"""Notify application to project owner through email
Param:
conn -- database connection
smtp -- smtp server
Return:
smtp send result
"""
proj = Project.get_by_id(conn, self.project_id)
role = Role.get_by_id(conn, self.role_apply)
col = Collaborator.get_by_id(conn, self.applicant)
dre = Dreamer.get_by_id(conn, proj['owner'])
subject = '[DreamMatchmaker]You have a new project application'
content = f'''<p>Hello {dre['name']},</p>
<p> <b>{col['name']}</b> has applied to join your project <b>"{proj['title']}"</b> as <b>"{role['title']}"</b></p>
<p> The following message is from the applicant:</p>
<p> {self.general_text}<p>
<p> You can view and accept or decline the application on the website.</p>
<p>Dream Matchmaker Team</p>
'''
result = smtp.send_email_html(dre['email'], content, subject)
return result
def get_by_applicant(conn, user_ID):
"""Get all applications by a collaborator
Param:
conn -- database connection
user_ID -- collaborator digit id
Return:
list of all application info for the collaborator
"""
# query by applicant
query = "SELECT * FROM application where applicant = " + str(
user_ID) + " order by ID desc;"
result = conn.execute(query)
applications = []
for i in range(result.rowcount):
row = result.fetchone()
# fetch information
if row['status'] == -1:
application_status = 'Pending'
if row['status'] == 0:
application_status = 'Declined'
if row['status'] == 1:
application_status = 'Approved'
if row['status'] == 9:
application_status = 'Finished'
appli = {
'ApplicationID': row['ID'],
'projectID': row['projectID'],
'project_title': Project.get_by_id(conn,
row['projectID'])['title'],
'Role_applied': row['role_applied'],
'Role_title': Role.get_by_id(conn,
row['role_applied'])['title'],
'Applicant': row['applicant'],
'Application_status': application_status,
'General_text': row['general_text']
}
applications.append(appli)
return {'applications': applications, 'amount': result.rowcount}
def get_projects():
projects = Project.load_projects()
return render_template("projects.jinja", projects=projects)