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)}
