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
}
