def get_joined_collaborators(conn, rID):
from users.collaborator import Collaborator
query = f"SELECT * FROM application WHERE role_applied = {rID} AND status = 1;"
result = conn.execute(query)
collabors = []
for i in range(result.rowcount):
row = result.fetchone()
collabors.append(Collaborator.get_by_id(conn, row['applicant']))
query_2 = f"SELECT * FROM invitation WHERE role_invited = {rID} AND status = 1;"
result_2 = conn.execute(query_2)
for i in range(result_2.rowcount):
row = result_2.fetchone()
collabors.append(Collaborator.get_by_id(conn, row['invitee']))
return collabors
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 get_by_pid_rid(conn, project_id, role_id):
"""Get applications in specific project and role
Param:
conn -- database connection
project_id -- project digit id
role_id -- role digit id
Return:
list of applicatior info
"""
# query application table by specific project/role
query = "SELECT * FROM application where projectID = " + str(
project_id) + " AND role_applied = " + str(role_id) + ";"
result = conn.execute(query)
all_application = {}
application_list = []
if result.rowcount == 0:
# no exist
return None
for i in range(result.rowcount):
row = result.fetchone()
# fetch applicator info
application = Collaborator.get_by_id(conn, row['applicant'])
# append application info
application['general_text'] = row['general_text']
application['apply_status'] = row['status']
application['application_id'] = row['ID']
application_list.append(application)
if len(application_list) == 0: return None
# into dict for better return format
all_application['applications'] = application_list
return all_application
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 get_by_pid(conn, project_id):
"""Get all discussions under a specific project
Param:
conn -- database connection
project_id -- project digit id
Return:
discussion list of a project
"""
query = "SELECT * FROM discussion where projectID = " + str(
project_id) + ";"
result = conn.execute(query)
all_discussion = {}
discussion_list = []
if result.rowcount == 0:
return None
for i in range(result.rowcount):
row = result.fetchone()
discussion_info = {
'discussion_id': row['ID'],
'parent_id': row['parent_discussion_ID'],
'content': row['text'],
'create_time': str(row['create_time'])
}
if row['is_dreamer'] == 2:
author = Dreamer.get_by_id(conn, row['d_author'])
discussion_info['is_owner'] = 'Yes'
discussion_info['author_id'] = author['id']
discussion_info['author_name'] = author['name']
discussion_info['author_role'] = 'dreamer'
elif row['is_dreamer'] == 1:
author = Dreamer.get_by_id(conn, row['d_author'])
discussion_info['is_owner'] = 'No'
discussion_info['author_id'] = author['id']
discussion_info['author_name'] = author['name']
discussion_info['author_role'] = 'dreamer'
else:
author = Collaborator.get_by_id(conn, row['c_author'])
discussion_info['is_owner'] = 'No'
discussion_info['author_id'] = author['id']
discussion_info['author_name'] = author['name']
discussion_info['author_role'] = 'collaborator'
discussion_list.append(discussion_info)
all_discussion['discussion_info'] = discussion_list
return all_discussion
def get_by_iid(conn, invitation_id):
"""get invitation info by id
Param:
conn -- database connection
id -- digit id
Return:
invitation info or None
"""
query = "SELECT * FROM invitation where ID = " + str(
invitation_id) + ";"
result = conn.execute(query)
if result.rowcount == 0:
return None
row = result.fetchone()
invitation = Collaborator.get_by_id(conn, row['invitee'])
if len(invitation) == 0: return None
invitation['general_text'] = row['general_text']
invitation['invite_status'] = row['status']
return invitation
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_by_pid_rid(conn, project_id, role_id):
"""get invitatees info by project/role
Param:
conn -- database connection
project_id -- project digit id
role_id -- role digit id
Return:
list of invitatees info or None
"""
query = "SELECT * FROM invitation where projectID = " + str(
project_id) + " AND role_invited = " + str(role_id) + ";"
result = conn.execute(query)
invitees = []
if result.rowcount == 0:
return None
for i in range(result.rowcount):
row = result.fetchone()
invitee = Collaborator.get_by_id(conn, row['invitee'])
invitees.append(invitee)
if len(invitees) == 0: return None
return {'invitees': invitees, 'amount': result.rowcount}
def get_by_did(conn, discussion_id):
"""Get discussion info by id
Param:
conn -- database connection
discussion_id -- discussion digit id
Return:
discussion info dict
"""
query = "SELECT * FROM discussion where ID = " + str(
discussion_id) + ";"
result = conn.execute(query)
if result.rowcount == 0:
return None
row = result.fetchone()
discussion_info = {
'discussion_id': discussion_id,
'projectID': row['projectID'],
'parent_id': row['parent_discussion_ID'],
'content': row['text'],
'create_time': str(row['create_time'])
}
if row['is_dreamer'] == 2:
author = Dreamer.get_by_id(conn, row['d_author'])
discussion_info['is_owner'] = 'Yes'
discussion_info['author_id'] = author['id']
discussion_info['author_name'] = author['name']
discussion_info['author_role'] = 'dreamer'
elif row['is_dreamer'] == 1:
author = Dreamer.get_by_id(conn, row['d_author'])
discussion_info['is_owner'] = 'No'
discussion_info['author_id'] = author['id']
discussion_info['author_name'] = author['name']
discussion_info['author_role'] = 'dreamer'
else:
author = Collaborator.get_by_id(conn, row['c_author'])
discussion_info['is_owner'] = 'No'
discussion_info['author_id'] = author['id']
discussion_info['author_name'] = author['name']
discussion_info['author_role'] = 'collaborator'
return discussion_info
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_aid(conn, application_id):
"""get application info by id
Param:
conn -- database connection
application_id -- digit id
Return:
applicator info with some application info or None
"""
query = "SELECT * FROM application where ID = " + str(
application_id) + ";"
result = conn.execute(query)
if result.rowcount == 0:
# not exist
return None
row = result.fetchone()
# use applicator info as base instead of application info
application = Collaborator.get_by_id(conn, row['applicant'])
if len(application) == 0: return None
# add application info
application['general_text'] = row['general_text']
application['apply_status'] = row['status']
application['application_id'] = row['ID']
return application
conn.execute(query)
# skill/exp table
print("rebuild skill/exp table...")
query = '''CREATE TABLE skills (
skill TINYINT DEFAULT -1,
experience TINYINT DEFAULT 0,
collaboratorID int(11) NOT NULL,
FOREIGN KEY (`collaboratorID`) REFERENCES collaborator(`ID`)) ROW_FORMAT=DYNAMIC;'''
conn.execute(query)
# Always create No.1 collaborator/dreamer/admin for system keeping to avoid foreign key error
print("create No.1 user as admin from config")
new_admin = Admin(config['Admin']['Name'], config['Admin']['Email'],
config['Admin']['Password']).commit(conn)
new_collab = Collaborator(config['Admin']['Name'], config['Admin']['Email'],
config['Admin']['Password']).commit(conn)
new_dreamer = Dreamer(config['Admin']['Name'], config['Admin']['Email'],
config['Admin']['Password']).commit(conn)
# project table
print("rebuild project table...")
query = '''CREATE TABLE project (
ID INT(11) NOT NULL AUTO_INCREMENT,
project_title VARCHAR(100) DEFAULT NULL,
description TEXT DEFAULT NULL,
category TINYINT DEFAULT -1,
dreamerID int(11) NOT NULL,FOREIGN KEY (`dreamerID`) REFERENCES dreamer(`ID`),
project_status TINYINT DEFAULT 1,
is_hidden TINYINT DEFAULT 0,
hidden_reason VARCHAR(256) DEFAULT NULL,
is_modified_after_hidden TINYINT DEFAULT 0,