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)