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)
