def remove_project(user_id, project_id, email):
query = f"""
SELECT
[creator]
FROM
[prj].[project]
WHERE
[ProjectId] = '{project_id}'
"""
conn = Database.connect()
cursor = conn.cursor()
results = Database.execute_query(query, cursor)
conn.close()
if user_id == results[0][0]:
remove_member = User.get_user_from_email(email)
if remove_member is not None:
query = """
[prj].[RemoveProject] @UserId = ?, @ProjectId = ?
"""
params = (user_id, project_id)
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': 'There is no account associated with this email'}
else:
return {'Status': 400, 'Message':'This user is not the owner of the project'}
def invite_to_project(project_info):
query = """
[prj].[InviteUserToProject] @UserId = ?, @ProjectId = ?
"""
user_id = User.get_user_from_email(project_info['Email'])
if user_id is not None:
project_id = project_info['ProjectId']
params = (user_id, project_id)
conn = Database.connect()
cursor = conn.cursor()
results = Database.execute_sproc(query, params, cursor)
if results['Status'] == 201:
cursor.commit()
conn.close()
else:
results = {'Status': 404, 'Message': 'This email does not have a registered account'}
return results
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)
