def current_semester():
"""
the current semester
Returns
-------
semester : dict
The current semester.
"""
date = datetime.now()
sql = """
SELECT Semester_Id, Year, Semester
FROM Semester
WHERE StartSemester <= "{date}" AND EndSemester >= "{date}"
""".format(date=date)
results = pd.read_sql(sql, sdb_connect())
sdb_connect().close()
return {
"semester":
'{year}-{sem}'.format(year=results.iloc[0]["Year"],
sem=results.iloc[0]["Semester"]),
"semester_id":
int(results.iloc[0]["Semester_Id"])
}
def get_proposal_ids(semester, partner_code=None):
conn = sdb_connect()
all_partners = [
p['Partner_Code'] for i, p in pd.read_sql(
"SELECT Partner_Code FROM Partner", conn).iterrows()
]
conn.close()
sql = """
SELECT distinct
Partner.Partner_Code AS PartnerCode,
ProposalCode_Id,
Proposal_Code,
Surname,
ProposalStatus_Id ,
CONCAT(Year, '-', Semester) AS Semester
FROM ProposalCode
JOIN ProposalGeneralInfo USING(ProposalCode_Id)
JOIN MultiPartner USING(ProposalCode_Id)
JOIN ProposalContact USING(ProposalCode_Id)
JOIN Investigator ON (Leader_Id=Investigator_Id)
JOIN Semester USING(Semester_Id)
JOIN Partner ON (MultiPartner.Partner_Id = Partner.Partner_Id)
GROUP BY ProposalCode_Id, Semester_Id HAVING Semester = "{semester}"
AND ProposalStatus_Id NOT IN (9, 3)
""".format(semester=semester) # status 9 => Deleted, 3 => Rejected
conn = sdb_connect()
all_proposals = [
str(p["ProposalCode_Id"])
for i, p in pd.read_sql(sql, conn).iterrows()
]
user_partners = [
partner for partner in all_partners
if g.user.may_perform(Action.VIEW_PARTNER_PROPOSALS, partner=partner)
]
if partner_code is not None:
sql += """ AND PartnerCode IN ("{partner_codes}")
""".format(partner_codes='", "'.join([partner_code]))
else:
sql += """ AND PartnerCode IN ("{partner_codes}")
""".format(partner_codes='", "'.join(user_partners))
proposal_code_ids = []
for index, r in pd.read_sql(sql, conn).iterrows():
if g.user.may_perform(Action.VIEW_PROPOSAL,
proposal_code=str(r['Proposal_Code'])):
proposal_code_ids.append(str(r['ProposalCode_Id']))
conn.close()
return {
'ProposalCode_Ids': proposal_code_ids,
"all_proposals": all_proposals
}
def get_proposal_ids(semester, partner_code=None):
conn = sdb_connect()
all_partners = [
p['Partner_Code'] for i, p in pd.read_sql(
"SELECT Partner_Code FROM Partner", conn).iterrows()
]
conn.close()
sql = """
select distinct Partner.Partner_Code as PartnerCode, ProposalCode_Id, Proposal_Code, Surname, ProposalStatus_Id , CONCAT(Year, '-', Semester) as Semester
from ProposalCode
join ProposalGeneralInfo using (ProposalCode_Id)
join MultiPartner using (ProposalCode_Id)
join ProposalContact using (ProposalCode_Id)
join Investigator on (Leader_Id=Investigator_Id)
join Semester using (Semester_Id)
join Partner on (MultiPartner.Partner_Id = Partner.Partner_Id)
Group by ProposalCode_Id, Semester_Id having Semester = "{semester}"
and ProposalStatus_Id NOT IN (9, 3)
""".format(
semester=semester) # status 9 => Deleted, 3 => Rejected
conn = sdb_connect()
all_proposals = [
str(p["ProposalCode_Id"])
for i, p in pd.read_sql(sql, conn).iterrows()
]
user_partners = [
partner for partner in all_partners
if g.user.may_perform(Action.VIEW_PARTNER_PROPOSALS, partner=partner)
]
if partner_code is not None:
sql += """ and PartnerCode in ("{partner_codes}")
""".format(partner_codes='", "'.join([partner_code]))
else:
sql += """ and PartnerCode in ("{partner_codes}")
""".format(partner_codes='", "'.join(user_partners))
proposal_code_ids = []
for index, r in pd.read_sql(sql, conn).iterrows():
if g.user.may_perform(Action.VIEW_PROPOSAL,
proposal_code=str(r['Proposal_Code'])):
proposal_code_ids.append(str(r['ProposalCode_Id']))
conn.close()
return {
'ProposalCode_Ids': proposal_code_ids,
"all_proposals": all_proposals
}
def latest_version(proposal_code, phase=None):
"""
The latest version number of a proposal, given a proposal phase.
This function requires the environment variable PROPOSALS_DIR to exist. Its value must be the absolute file path
to the directory containing all the proposal content.
Parameters
----------
proposal_code : str
The proposal code, such as "2018-1-SCI-009".
phase : int
The proposal phase (1 or 2).
Returns
-------
version : number
The current version number.
"""
if phase is None:
sql = '''
SELECT Proposal.Submission AS Submission
FROM Proposal
JOIN ProposalCode ON Proposal.ProposalCode_Id = ProposalCode.ProposalCode_Id
WHERE ProposalCode.Proposal_Code = %s
ORDER BY Proposal.Submission DESC
LIMIT 1
'''
conn = sdb_connect()
df = pd.read_sql(sql, conn, params=(proposal_code, ))
else:
sql = '''
SELECT Proposal.Submission AS Submission
FROM Proposal
JOIN ProposalCode ON Proposal.ProposalCode_Id = ProposalCode.ProposalCode_Id
WHERE ProposalCode.Proposal_Code = %s AND Proposal.Phase = %s
ORDER BY Proposal.Submission DESC
LIMIT 1
'''
conn = sdb_connect()
df = pd.read_sql(sql, conn, params=(proposal_code, phase))
conn.close()
if not len(df['Submission']):
raise Exception(
'Proposal {proposal_code} does not have any submitted version for phase {phase}'
.format(proposal_code=proposal_code, phase=phase))
return df['Submission'][0]
def latest_semester(proposal_code):
"""
The latest semester of a proposal, given a proposal code
Parameters
----------
proposal_code : str
The proposal code, such as "2018-1-SCI-009".
Returns
-------
year-semester : string in the form of 'YEAR-SEMESTER' e.g. (2017-1)
The latest year-semester of the submitted proposal.
"""
sql = '''
SELECT Semester.Semester AS semester, Semester.Year AS year
FROM Proposal
JOIN ProposalCode ON Proposal.ProposalCode_Id = ProposalCode.ProposalCode_Id
JOIN Semester ON Proposal.Semester_Id = Semester.Semester_Id
WHERE ProposalCode.Proposal_Code = %s
ORDER BY Proposal.Semester_Id DESC
LIMIT 1
'''
conn = sdb_connect()
df = pd.read_sql(sql, conn, params=(proposal_code, ))
conn.close()
if not len(df['year']) or not len(df['semester']):
raise Exception
return '{}-{}'.format(df['year'][0], df['semester'][0])
def get_bock_visit_status_id(observation_status):
sql = '''SELECT BlockVisitStatus_Id FROM BlockVisitStatus
WHERE BlockVisitStatus.BlockVisitStatus = %s
'''
df = pd.read_sql(sql, params=(observation_status,), con=sdb_connect())
return df['BlockVisitStatus_Id'][0] or 0
def is_investigator(username, proposal_code):
"""
Check whether a user is investigator on a proposal.
Parameters
----------
username : str
The username.
proposal_code : str
The proposal code, such as "2018-1-SCI-007".
Returns
-------
isinvestigator : bool
Whether the user is an investigator on the proposal.
"""
sql = '''
SELECT COUNT(*) AS Count
FROM ProposalContact AS ProposalContact
JOIN ProposalCode ON ProposalContact.ProposalCode_Id = ProposalCode.ProposalCode_Id
JOIN Investigator ON ProposalContact.Astronomer_Id = Investigator.Investigator_Id
JOIN PiptUser ON Investigator.PiptUser_Id = PiptUser.PiptUser_Id
WHERE ProposalCode.Proposal_Code = %s AND PiptUser.Username = %s
'''
conn = sdb_connect()
df = pd.read_sql(sql, conn, params=(proposal_code, username))
conn.close()
return df['Count'][0] > 0
def time_requests(proposal_code):
"""
A proposal's time requests for all semesters and partners.
Parameters
----------
proposal_code : str
Proposal code, such as "2018-1-SCI-008".
Returns
-------
timerequests : list of TimeRequest
The time requests.
"""
sql = '''
SELECT CONCAT(semester.Year, '-', semester.Semester) AS Semester,
partner.Partner_Code AS Partner_Code,
multipartner.ReqTimeAmount * multipartner.ReqTimePercent / 100 AS Time_Request
FROM MultiPartner AS multipartner
JOIN Semester AS semester ON multipartner.Semester_Id = semester.Semester_Id
JOIN Partner AS partner ON multipartner.Partner_Id = partner.Partner_Id
JOIN ProposalCode AS proposalcode ON multipartner.ProposalCode_Id = proposalcode.ProposalCode_Id
WHERE proposalcode.Proposal_Code=%s
'''
conn = sdb_connect()
df = pd.read_sql(sql, conn, params=(proposal_code, ))
conn.close()
return [
TimeRequest(semester=row[1]['Semester'],
partner=row[1]['Partner_Code'],
time_request=row[1]['Time_Request'])
for row in df.iterrows()
]
def targets(proposal_code_ids):
"""
Basic target information that will be used for statistics.
Like, RA and DEC of the target and if target P4
:param proposal_code_ids: list
List of proposal code ids
:return: list[dict]
The basic target information
"""
params = dict()
params["proposal_code_ids"] = proposal_code_ids
sql = """
SELECT distinct RaH, RaM, RaS, DecD, DecM, DecS, DecSign, Optional
FROM Proposal
JOIN P1ProposalTarget USING (ProposalCode_Id)
JOIN Target USING (Target_Id)
JOIN TargetCoordinates USING(TargetCoordinates_Id)
WHERE ProposalCode_Id IN %(proposal_code_ids)s
"""
df = pd.read_sql(sql, con=sdb_connect(), params=params)
all_targets = []
for _, row in df.iterrows():
sign = -1 if row['DecSign'] == '-' else 1
all_targets.append(
StatisticsTarget(
is_optional=row['Optional'] == 1,
right_ascension=(row['RaH'] + row['RaM'] / 60 +
row['RaS'] / 3600) / (24 / 360),
declination=(
sign *
(row['DecD'] + row['DecM'] / 60 + row['DecS'] / 3600)),
))
return all_targets
def update_proposals_requested_time(proposal_code_ids):
"""
Query database for requested times of given proposals ids
Parameters
----------
proposal_code_ids : string
proposal code id of interest like '(23, 45, 51, 89, 94)'
Returns
-------
time_requirements : array
updated time requirements
"""
time_requirements = {}
requested_time_sql = """
SELECT * FROM MultiPartner as mp
join Semester as sm using (Semester_Id)
join ProposalCode as pc using (ProposalCode_Id)
join Partner using (Partner_Id)
left join P1MinTime as mt on (mt.Semester_Id=sm.Semester_Id and mp.ProposalCode_Id=mt.ProposalCode_Id)
where mp.ProposalCode_Id in {proposal_code_ids}
""".format(proposal_code_ids=proposal_code_ids)
conn = sdb_connect()
for index, row in pd.read_sql(requested_time_sql, conn).iterrows():
proposal_code = row['Proposal_Code']
if proposal_code not in time_requirements:
time_requirements[proposal_code] = []
time_requirements[proposal_code] = update_time_requirements(
row, time_requirements[proposal_code])
conn.close()
return time_requirements
def time_breakdown(semester):
# get the filter parameters
params = dict()
params["semester"] = semester
# query for the time breakdown
sql = """SELECT SUM(ScienceTime) AS ScienceTime, SUM(EngineeringTime) AS EngineeringTime,
SUM(TimeLostToWeather) AS TimeLostToWeather, SUM(TimeLostToProblems) AS TimeLostToProblems,
SUM(IdleTime) AS IdleTime
FROM NightInfo AS ni
JOIN Semester AS s ON (ni.Date >= s.StartSemester AND ni.Date <= s.EndSemester)
WHERE CONCAT(s.Year,"-" ,s.Semester)=%(semester)s
"""
df = pd.read_sql(sql, con=sdb_connect(), params=params)
return TimeBreakdown(
science=0 if pd.isnull(df["ScienceTime"][0]) else df["ScienceTime"][0],
engineering=0
if pd.isnull(df["EngineeringTime"][0]) else df["EngineeringTime"][0],
lost_to_weather=0 if pd.isnull(
df["TimeLostToWeather"][0]) else df["TimeLostToWeather"][0],
lost_to_problems=0 if pd.isnull(df["TimeLostToProblems"][0]) else
df["TimeLostToProblems"][0],
idle=0 if pd.isnull(df["IdleTime"][0]) else df["IdleTime"][0],
)
def get_partner_codes(only_partner_ids=None):
"""
Parameters
----------
only_partner_ids : Optional[list]
Partner ID, .
Returns
-------
Partner Code: Array
A list of active partner codes for this
"""
par = '''
SELECT Partner_Code FROM Partner
JOIN PartnerShareTimeDist USING(Partner_Id)
JOIN Semester USING(Semester_Id)
WHERE `Virtual` = 0
AND Semester_Id = %s
AND TimePercent > 0
'''
if only_partner_ids is not None:
ids = [str(i) for i in only_partner_ids]
par += ' AND Partner_Id IN ({ids})'.format(ids=", ".join(ids))
conn = sdb_connect()
results = pd.read_sql(par,
conn,
params=(current_semester()["semester_id"], ))
conn.close()
return [row["Partner_Code"] for i, row in results.iterrows()]
def query_proposal_data(proposal_code_ids, semester, public=False):
proposals = {}
proposals_text = {}
tech_reports = {}
if not public:
proposals_text = get_proposals_text(proposal_code_ids)
tech_reports = update_technical_reports(proposal_code_ids)
requested_times = update_proposals_requested_time(proposal_code_ids)
proposal_sql = proposal_query(semester, proposal_code_ids, public=public)
conn = sdb_connect()
results = pd.read_sql(proposal_sql, conn)
for index, row in results.iterrows():
if row["Proposal_Code"] not in proposals:
proposals[row["Proposal_Code"]] = make_proposal(row, public)
get_instruments(proposal_code_ids, proposals)
get_targets(proposal_code_ids=proposal_code_ids, proposals=proposals)
fill_proposal_private_data(proposals, proposals_text, tech_reports,
requested_times)
update_requested_time_per_partner(proposal_code_ids, proposals)
update_time_allocations(semester, proposals)
return proposals.values()
def total_time_allocation(proposal_code_id, semester_id, priority):
sql = '''SELECT sum(TimeAlloc) AS Total_Alloc_Time FROM MultiPartner
JOIN PriorityAlloc using(MultiPartner_Id)
WHERE ProposalCode_Id = %s
AND Semester_Id = %s
AND Priority = %s'''
df = pd.read_sql(sql, params=(proposal_code_id, semester_id, priority,), con=sdb_connect())
return df['Total_Alloc_Time'][0] or 0
def get_semester_id(semester):
year = semester.split('-')[0]
year_semester = semester.split('-')[1]
sql = '''SELECT Semester_Id FROM Semester
WHERE Semester.Year = %s
AND Semester.Semester = %s'''
df = pd.read_sql(sql, params=(year, year_semester,), con=sdb_connect())
return df['Semester_Id'][0] or 0
def query_semester_id(semester):
sql = """
SELECT Semester_Id FROM Semester WHERE CONCAT(Year, "-",Semester) = %(semester)s
"""
df = pd.read_sql(sql, con=sdb_connect(), params={"semester": semester})
if pd.isnull(df["Semester_Id"][0]):
raise ValueError(
"Semester {semester} is not known".format(semester=semester))
return int(df["Semester_Id"][0])
def get_targets(proposal_code_ids=None,
proposals=None,
semester=None,
partner_code=None):
"""
If you do not provide ids you must provide semester or vise versa. value error will be raised if none is
provide.
If both semester and ids are provide value error is raised.
The idea is to get a list of target not depending on any proposals or put targets to respective
proposal but not both at the same time
:param proposal_code_ids: Ids need to be provided by Proposals class (if you need a list of targets in a proposal)
==> Todo need to be moved
:param proposals: Dict of proposals with they code as keys (if you need a list of targets in a proposal)
:param semester: semester querying for (if you need a list of targets)
:param partner_code: partner querying for (if you need a list of targets)
:return: list of targets (used by graphQL query) (if you need a list of targets)
"""
if proposal_code_ids is not None and semester is not None:
raise ValueError(
"targets are acquired by either ids or semester but not both")
targets = []
if proposal_code_ids is None:
if semester is None:
raise ValueError(
"semester must be provided when query for Targets")
ids = get_proposal_ids(semester=semester, partner_code=partner_code)
proposal_code_ids = sql_list_string(ids['all_proposals']) if partner_code is None \
else sql_list_string(ids['ProposalCode_Ids'])
sql = """
SELECT distinct Target_Id, Proposal_Code, DecSign, Target_Name, Optional, RaH, RaM, RaS,
DecD, DecM, DecS, Epoch, RaDot, DecDot, FilterName, MinMag, MaxMag
FROM Proposal
JOIN P1ProposalTarget USING (ProposalCode_Id)
JOIN ProposalCode USING (ProposalCode_Id)
JOIN Target USING (Target_Id)
JOIN TargetCoordinates USING(TargetCoordinates_Id)
JOIN TargetMagnitudes USING(TargetMagnitudes_Id)
JOIN Bandpass USING(Bandpass_Id)
LEFT JOIN MovingTarget USING(MovingTarget_Id)
WHERE ProposalCode_Id IN {proposal_code_ids} ORDER BY ProposalCode_Id
""".format(proposal_code_ids=proposal_code_ids)
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
for i, row in results.iterrows():
try:
if proposals is None:
targets.append(target(row))
else:
proposals[row["Proposal_Code"]].targets.append(target(row))
except KeyError:
pass
return targets
def get_partners_for_role(ids=None):
par = 'select Partner_Code from Partner '
if ids is not None:
ids = [str(id) for id in ids]
par += ' where Partner_Id in ({ids})'.format(ids=", ".join(ids))
conn = sdb_connect()
results = pd.read_sql(par, conn)
conn.close()
return [row["Partner_Code"] for i, row in results.iterrows()]
def get_partners(semester, partner):
from schema.partner import Partner, TimeAllocation, Priority
sql = '''
SELECT * FROM PeriodTimeDist
JOIN Partner USING(Partner_Id)
JOIN Semester USING(Semester_Id)
WHERE concat(Year,"-", Semester) = "{semester}"
'''.format(semester=semester)
if partner is not None:
sql += ' AND Partner_Code = "{partner_code}" '.format(
partner_code=partner)
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
partners = [Partner(
id="Partner: " + str(row["Partner_Id"]),
name=row["Partner_Name"],
code=row["Partner_Code"],
time_allocation=TimeAllocation(
semester=str(row['Year']) + "-" + str(row['Semester']),
used_time=Priority(
p0_andp1=row['Used0and1'],
p2=row['Used2'],
p3=row['Used3']
),
allocated_time=Priority(
p0_andp1=row['Alloc0and1'],
p2=row['Alloc2'],
p3=row['Alloc3']
)
)) for index, row in results.iterrows()] if partner is not None else \
[Partner(
id="Partner: " + str(row["Partner_Id"]),
name=row["Partner_Name"] if g.user.may_view(Data.AVAILABLE_TIME, partner=row["Partner_Code"]) else None,
code=row["Partner_Code"] if g.user.may_view(Data.AVAILABLE_TIME, partner=row["Partner_Code"]) else None,
time_allocation=TimeAllocation(
semester=str(row['Year']) + "-" + str(row['Semester']),
used_time=Priority(
p0_andp1=row['Used0and1'],
p2=row['Used2'],
p3=row['Used3']
),
allocated_time=Priority(
p0_andp1=row['Alloc0and1'],
p2=row['Alloc2'],
p3=row['Alloc3']
)
)) for index, row in results.iterrows()]
return partners
def get_selectors_data():
from schema.selectors import Selectors
sem = 'select CONCAT(Year,"-", Semester) as Semester from Semester '
par = 'select Partner_Code from Partner'
conn = sdb_connect()
sem_results = pd.read_sql(sem, conn)
par_results = pd.read_sql(par, conn)
conn.close()
return Selectors(
semester=[row['Semester'] for index, row in sem_results.iterrows()],
partner=[row['Partner_Code'] for index, row in par_results.iterrows()])
def get_targets(ids=None, proposals=None, semester=None, partner_code=None):
"""
If you do not provide ids you must provide semester or vise versa. value error will be raised if none is
provide.
If both semester and ids are provide value error is raised.
The idea is to get a list of target not depending on any proposals or put targets to respective
proposal but not both at the same time
:param ids: Ids need to be provided by Proposals class (if you need a list of targets in a proposal)
==> Todo need to be moved
:param proposals: Dict of proposals with they code as keys (if you need a list of targets in a proposal)
:param semester: semester querying for (if you need a list of targets)
:param partner_code: partner querying for (if you need a list of targets)
:return: list of targets (used by graphQL query) (if you need a list of targets)
"""
if ids is not None and semester is not None:
raise ValueError(
"targets are acquired by either ids or semester but not both")
targets = []
if ids is None:
if semester is None:
raise ValueError(
"semester must be provided when query for Targets")
ids = get_proposal_ids(semester=semester, partner_code=partner_code)
id_list = sql_list_string(
ids['all_proposals']) if partner_code is None else sql_list_string(
ids['ProposalCode_Ids'])
sql = """
SELECT *
FROM Proposal
JOIN ProposalCode using (ProposalCode_Id)
JOIN P1ProposalTarget using (ProposalCode_Id)
JOIN Target using (Target_Id)
JOIN TargetCoordinates using(TargetCoordinates_Id)
"""
sql += " WHERE ProposalCode_Id in {id_list} order by ProposalCode_Id"\
.format(id_list=id_list)
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
for i, row in results.iterrows():
try:
if proposals is None:
targets.append(target(row))
else:
proposals[row["Proposal_Code"]].targets.append(target(row))
except KeyError:
pass
return targets
def get_semester(active=False, semester_code=None):
"""
:return:
"""
sql = 'SELECT Semester_Id, CONCAT(Year,"_", Semester) as SemesterCode, StartSemester, EndSemester ' \
' FROM Semester '
date = datetime.datetime.now().date()
date_3 = date + relativedelta(months=3)
if active:
if not pd.isnull(semester_code):
warnings.warn(
"Semester id or Semester code is provided and active=True, active semester is returned. "
"Set active=False if you need none active semester if you query for none active semester."
"Returned is active Semester")
sql = sql + ' where StartSemester <= "{date_}" and "{date_}" < EndSemester;'.format(
date_=date_3)
else:
try:
if not pd.isnull(semester_code):
int(semester_code[:4])
int(semester_code[5:])
if "_" in semester_code:
semester_code = semester_code.replace("_", "-")
if len(semester_code) != 6:
raise ValueError(
"{semester_code} is not a semester".format(
semester_code=semester_code))
sql = sql + ' WHERE (CONCAT(Year, "-", Semester) = "{semester_code}") ' \
.format(semester_code=semester_code)
else:
raise ValueError(
"Set active=True for active semester, or provide semester_id or semester like '2017_1' "
"or '2017-1'")
except ValueError:
return None
conn = sdb_connect()
try:
data = pd.read_sql(sql, conn)
conn.close()
return [
Semester().__make_semester(data=s) for i, s in data.iterrows()
][0]
except Exception as err:
log(1, err)
raise RuntimeError("Failed to get semester data")
def get_partners(semester, partner):
from schema.partner import PartnerAllocations, AllocatedTime
sql = '''
select * from PeriodTimeDist
join Partner using(Partner_Id)
join Semester using(Semester_Id)
where concat(Year,"-", Semester) = "{semester}"
'''.format(semester=semester)
if partner is not None:
sql += ' and Partner_Code = "{partner_code}" '.format(
partner_code=partner)
conn = sdb_connect()
results = pd.read_sql(sql, conn)
conn.close()
partners = [PartnerAllocations(
id="Partner: " + str(row["Partner_Id"]),
name=row["Partner_Name"],
code=row["Partner_Code"],
allocated_time=AllocatedTime(
for_semester=str(row['Year']) + "-" + str(row['Semester']),
Allocated_p0_p1=row['Alloc0and1'],
Allocated_p2=row['Alloc2'],
Allocated_p3=row['Alloc3'],
used_p0_p1=row['Used0and1'],
used_p2=row['Used2'],
used_p3=row['Used3']
)) for index, row in results.iterrows()] if partner is not None else \
[PartnerAllocations(
id="Partner: " + str(row["Partner_Id"]),
name=row["Partner_Name"] if g.user.has_role(RoleType.ADMINISTRATOR, row["Partner_Code"]) else None,
code=row["Partner_Code"] if g.user.has_role(RoleType.ADMINISTRATOR, row["Partner_Code"]) else None,
allocated_time=AllocatedTime(
for_semester=str(row['Year']) + "-" + str(row['Semester']),
Allocated_p0_p1=row['Alloc0and1'],
Allocated_p2=row['Alloc2'],
Allocated_p3=row['Alloc3'],
used_p0_p1=row['Used0and1'],
used_p2=row['Used2'],
used_p3=row['Used3']
)) for index, row in results.iterrows()]
return partners
def share_percentage(semester_id):
params = dict()
params["semester_id"] = semester_id
sql = """
SELECT Partner_Code, SharePercent
FROM PartnerShareTimeDist AS pst
JOIN Semester AS s ON pst.Semester_Id = s.Semester_Id
JOIN Partner AS partner ON pst.Partner_Id = partner.Partner_Id
WHERE s.Semester_Id=%(semester_id)s
"""
share = dict()
df = pd.read_sql(sql, con=sdb_connect(), params=params)
for _, row in df.iterrows():
share[row["Partner_Code"]] = row["SharePercent"]
return share
def summary_file(proposal_code, semester):
"""
The file path of a proposal's summary file. The summary file is the progress report (if there is
one) or the phase 1 summary. If there exists a supplementary file for the progress report, a
temporary file containing both the progress report and the supplementary file is created, and
the path to that temporary file is returned.
The semester is the one for whose time allocation the summary should be used. This is relevant
only for progress reports. Note that the progress report is returned for the semester previous to
the given one. So, for example, if '2018-1' is passed as semester, the progress report for 2017-2
is returned.
Parameters
----------
proposal_code : str
The proposal code, such as "2018-1-SCI-005".
semester : str
The semester, such as '2018-1'.
Returns
-------
filepath : str
The file path of the summary.
"""
# look for a progress report and a supplementary file
prev_sem = previous_semester(semester)
sql = '''
SELECT ProposalProgress.ReportPath AS ReportPath
FROM ProposalProgress
JOIN ProposalCode USING (ProposalCode_Id)
JOIN Semester USING (Semester_Id)
WHERE ProposalCode.Proposal_Code = %s AND CONCAT(Semester.Year, '-', Semester.Semester) = %s
'''
conn = sdb_connect()
df = pd.read_sql(sql, conn, params=(proposal_code, prev_sem))
conn.close()
if len(df['ReportPath']) > 0:
report = os.path.join(os.environ['PROPOSALS_DIR'], proposal_code,
'Included', df['ReportPath'][0])
return report
else:
summary = os.path.join(os.environ['PROPOSALS_DIR'], proposal_code,
str(latest_version(proposal_code, 1)),
'Summary.pdf')
return summary
def proposal_observed_time(proposal_code_ids, semester):
params = dict()
params["semester_id"] = query_semester_id(semester)
params["proposal_code_ids"] = proposal_code_ids
proposal_observed = dict()
sql = """
SELECT
BlockVisit_Id,
Partner_Code,
TimeAlloc,
Proposal_Code,
ObsTime,
b.Priority as BlockPriority,
pa.Priority as AllocPriority
FROM BlockVisit AS bv
JOIN Block AS b ON bv.Block_Id = b.Block_Id
JOIN ProposalCode AS pc ON b.ProposalCode_Id = pc.ProposalCode_Id
JOIN Proposal using(Proposal_Id)
JOIN MultiPartner as mp ON (pc.ProposalCode_Id = mp.ProposalCode_Id )
JOIN Partner USING(Partner_Id)
JOIN PriorityAlloc as pa USING (MultiPartner_Id)
WHERE pc.ProposalCode_Id IN %(proposal_code_ids)s
AND BlockVisitStatus_Id = 1
AND Proposal.Semester_Id = %(semester_id)s and mp.Semester_Id = %(semester_id)s
"""
df = pd.read_sql(sql, con=sdb_connect(), params=params)
for _, row in df.iterrows():
if row["Proposal_Code"] not in proposal_observed:
proposal_observed[row["Proposal_Code"]] = {
"block_visit": dict(),
"allocated_time": dict()
}
proposal_observed[row["Proposal_Code"]]["block_visit"][
row["BlockVisit_Id"]] = {
"priority": row["BlockPriority"],
"time": row["ObsTime"]
}
if row["Partner_Code"] not in proposal_observed[
row["Proposal_Code"]]["allocated_time"]:
proposal_observed[row["Proposal_Code"]]["allocated_time"][row["Partner_Code"]] = \
{0: 0, 1: 0, 2: 0, 3: 0, 4: 0}
proposal_observed[row["Proposal_Code"]]["allocated_time"][row["Partner_Code"]][row["AllocPriority"]] = \
row["TimeAlloc"]
return proposal_observed
def multipartner_ids(proposal_codes, partner, semester):
"""
Map proposal codes to multipartner ids.
Proposal codes are ignored if the proposal doesn't have a multipartner entry for the partner and semester.
Parameters
----------
proposal_codes : iterable
The proposal codes to map.
partner : str
The partner code, such as `RSA` or `IUCAA`.
semester : str
The semester, such as `2017-2` or `2018-1`.
Returns
-------
ids: dict
A dictionary of proposal codes and multipartner ids.
"""
year, sem = semester.split('-')
proposal_code_strings = [
"'{proposal_code}'".format(proposal_code=proposal_code)
for proposal_code in proposal_codes
]
sql = '''SELECT pc.Proposal_Code, mp.MultiPartner_Id
FROM MultiPartner AS mp
JOIN ProposalCode AS pc USING (ProposalCode_Id)
JOIN Partner AS p USING (Partner_Id)
JOIN Semester AS s USING (Semester_Id)
WHERE pc.Proposal_Code IN ({proposal_codes})
AND p.Partner_Code='{partner_code}'
AND (s.Year={year} AND s.Semester={semester})'''.format(
proposal_codes=', '.join(proposal_code_strings),
partner_code=partner,
year=year,
semester=sem)
connection = sdb_connect()
df = pd.read_sql(sql, connection)
connection.close()
return {
item['Proposal_Code']: item['MultiPartner_Id']
for item in df.to_dict('records')
}
def get_proposals_text(proposal_code_ids):
proposals_text = {}
proposals_text_sql = """
SELECT * FROM ProposalText
join ProposalCode using(ProposalCode_Id)
WHERE ProposalCode_Id in {proposal_code_ids}
order by Semester_Id desc """.format(
proposal_code_ids=proposal_code_ids)
conn = sdb_connect()
for index, row in pd.read_sql(proposals_text_sql, conn).iterrows():
if row["Proposal_Code"] not in proposals_text:
proposals_text[row["Proposal_Code"]] = {
"title": row["Title"],
"abstract": row["Abstract"]
}
conn.close()
return proposals_text
def query_partner_data(**args):
from schema.partner import PartnersAllocations, AllocatedTime
sql = ' select * from PeriodTimeDist ' \
' join Partner using(Partner_Id) ' \
' join Semester using(Semester_Id) '
if args['semester'] is not None:
sql = sql + ' where concat(Year,"-", Semester) = "{semester}"'.format(
semester=args['semester'])
if args['partner_code'] is not None:
if "where" in sql:
sql += " and Partner_Code = '{partner_code}'".format(
partner_code=args['partner_code'])
else:
sql += " where Partner_Code = '{partner_code}'".format(
partner_code=args['partner_code'])
conn = sdb_connect()
try:
results = pd.read_sql(sql + " order by Partner_Code", conn)
conn.close()
except:
raise RuntimeError("Fail to get partners")
partners, pc = [], []
for index, row in results.iterrows():
if row["Partner_Code"] not in pc:
partners.append(
PartnersAllocations(id="Partner: " + str(row["Partner_Id"]),
name=row["Partner_Name"],
code=row["Partner_Code"],
allocated_time=[]))
partners[len(partners) - 1].allocated_time.append(
AllocatedTime(for_semester=str(row['Year']) + "-" +
str(row['Semester']),
Allocated_p0_p1=row['Alloc0and1'],
Allocated_p2=row['Alloc2'],
Allocated_p3=row['Alloc3'],
used_p0_p1=row['Used0and1'],
used_p2=row['Used2'],
used_p3=row['Used3']))
pc.append(row["Partner_Code"])
return partners
def update_requested_time_per_partner(proposal_code_ids, proposals):
"""
Query database for requested times of all partners on the given proposals ids
and update proposals with query results
Parameters
----------
proposal_code_ids : string
proposal code id of interest like '(23, 45, 51, 89, 94)'
proposals: object
time requirements created thus far
Returns
-------
None
"""
from schema.partner import Partner
from schema.proposal import TimeRequest
partner_time_sql = """
SELECT Proposal_Code, ReqTimeAmount*ReqTimePercent/100.0 as TimePerPartner,
Partner_Id, Partner_Name, Partner_Code, concat(s.Year,'-', s.Semester) as CurSemester
FROM ProposalCode
join MultiPartner using (ProposalCode_Id)
join Semester as s using (Semester_Id)
join Partner using(Partner_Id)
WHERE ProposalCode_Id in {proposal_code_ids}
""".format(proposal_code_ids=proposal_code_ids)
conn = sdb_connect()
rq_times = pd.read_sql(partner_time_sql, conn)
for index, row in rq_times.iterrows():
try:
proposal = proposals[row["Proposal_Code"]]
for p in proposal.time_requirements:
if p.semester == row['CurSemester']:
p.time_requests.append(
TimeRequest(partner=Partner(code=row['Partner_Code'],
name=row['Partner_Name']),
time=int(row['TimePerPartner'])))
except:
pass
conn.close()
