def member_leave(user_id): user = User.get_user_info(user_id) if user.get_institution() is not None: query = f""" [usr].[RemoveUserFromInstitution] ?, ? """ params = (user_id, user.get_institution()) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 200: cursor.commit() conn.close() return results else: return {'Status': 400, 'Message': 'User not part of institution'}
def institution_create(): if request.method == 'POST': response = User.get_user_from_session(request.headers.get('SessionId')) if response['Status'] == 200: user = User.get_user_info(response['Message']) if user.get_institution() is None: institution_info = request.get_json() institution_model = Institution() institution_model.set_name(institution_info['Name']) institution_model.set_desc(institution_info['Desc']) institution_model.set_owner(response['Message']) return Institution.create_institution(institution_model) return { 'Status': 400, 'Message': 'Member already part of an institution' } return json.dumps(response)
def get_institution(institution_id): query = f""" SELECT TOP 1 [InstitutionID] ,[Name] ,[Desc] ,[Owner] FROM [MetaData].[usr].[Institution] WHERE [InstitutionID] = '{institution_id}' """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) if len(results) > 0: institution = Institution() institution.set_institution_id(results[0][0]) institution.set_name(results[0][1]) institution.set_desc(results[0][2]) institution.set_owner(results[0][3]) query = f""" SELECT [UserId] ,[Role] ,[Pending] FROM [MetaData].[usr].[InstitutionMember] WHERE [InstitutionID] = '{institution_id}' """ results = Database.execute_query(query, cursor) if len(results) > 0: members = [] for row in results: member = User.get_user_info(row[0]) member.set_institution_role(row[1]) if row[2] == 0: is_pending = False else: is_pending = True member.set_institution_pending(is_pending) members.append(member) institution.set_members(members) conn.close() return institution
def invite_member(user_id, invitation_info): user = User.get_user_info(user_id) if user.get_institution() is not None: invited_user_id = User.get_user_from_email(invitation_info['Email']) if invited_user_id is not None: if user.get_institution() not in Institution.get_all_pending(invited_user_id): query = f""" [usr].[InviteUserToInstitution] ?, ?, ? """ params = (invited_user_id, user.get_institution(), invitation_info['Role']) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 201: cursor.commit() conn.close() return results else: return {'Status': 400, 'Message': 'This account has already been invited'} else: return {'Status': 400, 'Message': 'There is no account associated with this email'} else: return {'Status': 400, 'Message': 'User not part of institution'}
def remove_member(user_id, email): user = User.get_user_info(user_id) if user.get_institution() is not None: institution = Institution.get_institution(user.get_institution()) if institution.get_owner() == user_id: remove_user_id = User.get_user_from_email(email) if remove_user_id is not None: query = f""" [usr].[RemoveUserFromInstitution] ?, ? """ params = (remove_user_id, user.get_institution()) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 200: cursor.commit() conn.close() return results else: return {'Status': 400, 'Message': 'There is no account associated with this email'} else: return {'Status': 400, 'Message': 'User is not owner of institution'} else: return {'Status': 400, 'Message': 'User not part of institution'}
def get_users_projects(user_id): query = f""" SELECT p.[ProjectID] ,p.[Name] ,p.[Desc] ,u.[Email] ,p.[StartDate] ,p.[EndDate] ,p.[Public] FROM [MetaData].[prj].[Project] p INNER JOIN [MetaData].[usr].[User] u ON p.[Creator] = u.[UserID] LEFT JOIN [MetaData].[prj].[ProjectMember] pm ON p.[ProjectID] = pm.[ProjectID] WHERE pm.[Pending] = 0 AND pm.[UserID] = '{user_id}' OR p.[Creator] = '{user_id}' """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) projects = [] for row in results: project = Project() project.set_project_id(row[0]) project.set_name(row[1]) project.set_desc(row[2]) project.set_creator(row[3]) project.set_start_date(row[4]) project.set_end_date(row[5]) project.set_public(row[6]) query = f""" SELECT u.[Email], pm.[Pending] FROM [MetaData].[prj].[ProjectMember] pm INNER JOIN [MetaData].[usr].[User] u ON pm.[UserId] = u.[UserID] WHERE [ProjectID] = '{project.get_project_id()}' """ cursor = conn.cursor() results = Database.execute_query(query, cursor) members = [] for member_row in results: user_id = User.get_user_from_email(member_row[0]) user = User.get_user_info(user_id) user.user['pending'] = member_row[1] members.append(user.user) project.set_project_members(members) projects.append(project.project) conn.close() response = {'Projects': projects} return json.dumps(response)