def update_completion_comments(semester, completion_comments):
"""
Update the database with a list of reviwes.
Parameters
----------
semester : str
Semester, such as "2018-1".
completion_comments : iterable
The list of completion_comments. Each assignment must be dictionary with a proposal code,
and a completion comment (which may be None).
"""
connection = sdb_connect()
try:
with connection.cursor() as cursor:
for comment in completion_comments:
update_completion_comment(
proposal_code=comment['proposalCode'],
semester=semester,
comment=comment['comment'],
cursor=cursor
)
connection.commit()
finally:
connection.close()
def get_user(user_id):
user = {}
sql = '''
SELECT
*,
t.PiptUser_Id AS Tac,
t.Partner_Id AS TacPartner,
a.Investigator_Id AS Astro
FROM PiptUser AS u
JOIN Investigator AS i using (Investigator_Id)
LEFT JOIN SaltAstronomers AS a using( Investigator_Id )
LEFT JOIN PiptUserTAC AS t ON (u.PiptUser_Id = t.PiptUser_Id)
WHERE u.PiptUser_Id = {user_id}
'''.format(user_id=user_id)
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
username = ''
for index, row in results.iterrows():
username = row["Username"]
if username not in user:
user[username] = User(username=row["Username"],
first_name=row["FirstName"],
last_name=row["Surname"],
email=row["Email"],
role=[])
user[username].role += get_role(row, user_id)
return user[username]
def update_reviews(semester, reviews):
"""
Update the database with a list of reviwes.
Parameters
----------
semester : str
Semester, such as "2018-1".
reviews : iterable
The list of reviwews. Each assignment must be dictionary with a proposal code,
a username (of the SALT Astronomer to be assigned as reviewer to that proposal)
and a technical report (which may be None).
"""
connection = sdb_connect()
try:
with connection.cursor() as cursor:
for review in reviews:
update_review(proposal_code=review['proposalCode'],
semester=semester,
reviewer=review['reviewer'],
report=review['report'],
cursor=cursor)
connection.commit()
finally:
connection.close()
def update_tac_members(partner, members):
"""
Add or update a list of members for a partner's TAC.
If a member is not in the database, they are added. Otherwise their details are updated in the database.
Parameters
----------
partner : str
Partner code like "RSA".
members : iterable
The list of usernames of members, like
like [
{member: 'user-1', is_chair: False},
{member: 'user-4', is_chair: True}
]
"""
connection = sdb_connect()
try:
with connection.cursor() as cursor:
for member in members:
update_tac_member(partner=partner,
member=member['member'],
is_chair=member['is_chair'],
cursor=cursor)
connection.commit()
finally:
connection.close()
def get_tac_members(partner):
sql = '''
SELECT *
FROM PiptUser
JOIN PiptUserTAC USING(PiptUser_Id)
JOIN Investigator USING(Investigator_Id)
JOIN Partner USING(Partner_Id)
'''
if partner is not None:
sql += " WHERE Partner_Code = '{partner}'".format(partner=partner)
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
tacs = []
for index, row in results.iterrows():
if row["Username"] is not None:
tacs.append(
TacMember(username=row["Username"],
first_name=row["FirstName"],
last_name=row["Surname"],
email=row["Email"],
partner=Partner(code=row["Partner_Code"],
name=row["Partner_Name"]),
is_chair=row["Chair"] == 1))
return tacs
def get_role(row, user_id):
all_partner = get_partners_for_role()
sql = "SELECT * " \
" FROM PiptUserSetting " \
" LEFT JOIN PiptUserTAC using (PiptUser_Id) " \
" WHERE PiptSetting_Id = 22 " \
" AND PiptUser_Id = {user_id}".format(user_id=user_id)
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
role = []
if not pd.isnull(row["Astro"]):
role.append(Role(type=RoleType.SALT_ASTRONOMER, partners=all_partner))
if not pd.isnull(row["Tac"]):
partner = get_partners_for_role(ids=[row["TacPartner"]])
role.append(Role(type=RoleType.TAC_MEMBER, partners=partner))
if not pd.isnull(row["Chair"]) and row["Chair"] == 1:
partner = get_partners_for_role(ids=[row["TacPartner"]])
role.append(Role(type=RoleType.TAC_CHAIR, partners=partner))
if len(results) > 0 and int(results.iloc[0]["Value"]) > 1:
role.append(Role(type=RoleType.ADMINISTRATOR, partners=all_partner))
return role
def get_role(row, user_id):
all_partner = get_partner_codes()
sql = '''
SELECT Partner_Id FROM PiptUser as pu
JOIN Investigator USING (Investigator_Id)
JOIN Institute USING (Institute_Id)
JOIN PiptUserSetting as pus ON (pu.PiptUser_Id = pus.PiptUser_Id)
JOIN PiptSetting using (PiptSetting_Id)
where pu.PiptUser_Id={user_id}
AND PiptSetting_Name ='RightBoard'
AND Value = 1
'''.format(user_id=user_id)
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
role = []
if len(results):
role.append(
Role(type=RoleType.BOARD,
partners=get_partner_codes([results.iloc[0]["Partner_Id"]])))
if not pd.isnull(row["Astro"]):
role.append(Role(type=RoleType.SALT_ASTRONOMER, partners=all_partner))
if not pd.isnull(row["Tac"]):
partner = get_partner_codes([row["TacPartner"]])
role.append(Role(type=RoleType.TAC_MEMBER, partners=partner))
if not pd.isnull(row["Chair"]) and row["Chair"] == 1:
partner = get_partner_codes([row["TacPartner"]])
role.append(Role(type=RoleType.TAC_CHAIR, partners=partner))
sql = '''
SELECT * FROM PiptUserSetting
LEFT JOIN PiptUserTAC using (PiptUser_Id)
WHERE PiptSetting_Id = 22
AND PiptUser_Id = {user_id}
'''.format(user_id=user_id)
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
if len(results) > 0 and int(results.iloc[0]["Value"]) > 1:
role.append(Role(type=RoleType.ADMINISTRATOR, partners=all_partner))
return role
def verify_user(username, password):
"""
:param username: username
:param password: password
:return: PiptUser_Id or None if not found
"""
sql = """SELECT COUNT(PiptUser_Id) AS UserCount
FROM PiptUser
WHERE Username='******' AND Password=MD5('{password}')""" \
.format(username=username, password=password)
conn = sdb_connect()
result = pd.read_sql(sql, conn)
conn.close()
if not result.iloc[0]['UserCount']:
raise InvalidUsage('Username or password wrong')
def get_salt_users():
sql = 'SELECT * FROM PiptUser JOIN Investigator USING (Investigator_Id)'
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
users = []
for index, row in results.iterrows():
if row["Username"] is not None:
users.append(
User(username=row["Username"],
first_name=row["FirstName"],
last_name=row["Surname"],
email=row["Email"],
role=[]))
return users
def current_user(user_id):
if user_id is not None:
sql = "SELECT PiptUser_Id, PiptSetting_Id, Value FROM PiptUserSetting WHERE PiptSetting_Id = 20 " \
" AND PiptUser_Id = {user_id}".format(user_id=user_id)
conn = sdb_connect()
try:
result = pd.read_sql(sql, conn)
g.user = User(
result.iloc[0]['PiptUser_Id'],
result.iloc[0]['PiptSetting_Id'],
result.iloc[0]['Value'],
)
conn.close()
except IndexError:
raise RuntimeError("User doesn't Exist")
except:
raise RuntimeError("Fail to set/get current user")
def query_id(username):
"""
:param username: username
:return: PiptUser_Id or None if not found
"""
sql = """SELECT PiptUser_Id
FROM PiptUser
WHERE Username='******'""" \
.format(username=username)
conn = sdb_connect()
try:
result = pd.read_sql(sql, conn)
conn.close()
return result.iloc[0]['PiptUser_Id']
except IndexError:
return None
def query_id(username, password):
"""
:param username: username
:param password: password
:return: PiptUser_Id or no if not found
"""
sql = "SELECT PiptUser_Id From PiptUser where Username='******' AND Password=MD5('{password}')"\
.format(username=username, password=password)
conn = sdb_connect()
try:
result = pd.read_sql(sql, conn)
conn.close()
return result.iloc[0]['PiptUser_Id']
except IndexError:
return None
except:
raise RuntimeError("Fail to get User Id")
def update_liaison_astronomers(assignments):
"""
Update the database with a list of time allocations.
Parameters
----------
assignments : iterable
The list of liaison astronomer assignments. Each assignment must be dictionary with a proposal code and
a username (of the SALT Astronomer to be assigned to that proposal).
"""
connection = sdb_connect()
try:
with connection.cursor() as cursor:
for assignment in assignments:
update_liaison_astronomer(
proposal_code=assignment['proposalCode'],
liaison_astronomer=assignment['liaisonAstronomer'],
cursor=cursor)
connection.commit()
finally:
connection.close()
def remove_tac_members(partner, members):
"""
Remove a list of members from a partner's TAC.
Parameters
----------
partner : str
Partner code like "RSA".
members : iterable
The list of usernames of members, like [{member: 'user-1'}, {member: 'user-4'}]
"""
connection = sdb_connect()
try:
with connection.cursor() as cursor:
for member in members:
remove_tac_member(partner=partner,
member=member['member'],
cursor=cursor)
connection.commit()
finally:
connection.close()
def get_user(user_id):
user = {}
sql = " select *, t.PiptUser_Id as Tac, t.Partner_Id as TacPartner, a.Investigator_Id as Astro " \
" from PiptUser as u " \
" JOIN Investigator as i using (Investigator_Id) " \
" left join SaltAstronomers as a using( Investigator_Id ) " \
" left join PiptUserTAC as t on (u.PiptUser_Id = t.PiptUser_Id) " \
" where u.PiptUser_Id = {user_id}".format(user_id=user_id)
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
username = ''
for index, row in results.iterrows():
username = row["Username"]
if username not in user:
user[username] = UserModel(username=row["Username"],
first_name=row["FirstName"],
last_name=row["Surname"],
email=row["Email"],
role=[])
user[username].role += get_role(row, user_id)
return user[username]
def get_tac_members(partner):
sql = '''select * from PiptUser
join PiptUserTAC using(PiptUser_Id)
join Investigator using(Investigator_Id)
join Partner using(Partner_Id)
'''
if partner is not None:
sql += " where Partner_Code = '{partner}'".format(partner=partner)
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
tacs = []
for index, row in results.iterrows():
if row["Username"] is not None:
tacs.append(
TacMember(username=row["Username"],
first_name=row["FirstName"],
last_name=row["Surname"],
email=row["Email"],
partner_code=row["Partner_Code"],
partner_name=row["Partner_Name"],
is_chair=row["Chair"] == 1))
return tacs