def get_proposals(**args):
semester = args['semester']
partner = args['partner_code']
public = args['details']
ids = get_proposal_ids(semester, partner)
proposal_code_ids = sql_list_string(ids['ProposalCode_Ids'])
data = query_proposal_data(proposal_code_ids, semester, public=public)
return data
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_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 query_proposal_data(semester, partner_code=None, all_proposals=False):
from schema.proposal import Distribution, ProposalAllocatedTime, TacComment
ids = get_proposal_ids(semester, partner_code)
id_list = sql_list_string(
ids['all_proposals']) if all_proposals else sql_list_string(
ids['ProposalCode_Ids'])
proposals = {}
proposals_text = {}
proposal_sql = """
select *, i.FirstName as PIFname, i.Surname as PISname, i.Email as PIEmail, tsa.FirstName as SAFname,
tsa.Surname as SASname, tsa.Email as SAEmail, sau.Username as SAUsername
from ProposalGeneralInfo
join ProposalStatus using(ProposalStatus_Id)
join ProposalCode using (ProposalCode_Id)
left join P1Thesis using (ProposalCode_Id)
join ProposalContact as pc using (ProposalCode_Id)
join Investigator as i on (i.Investigator_Id = pc.Leader_Id)
left join Investigator as tsa on (tsa.Investigator_Id = pc.Astronomer_Id)
left join PiptUser as sau on (sau.Investigator_Id = pc.Astronomer_Id)
join P1ObservingConditions using (ProposalCode_Id)
join Transparency using (Transparency_Id)
join Semester using (Semester_Id)
where CONCAT(Year, '-', Semester) = '{semester}' and ProposalCode_Id IN {id_list} order by ProposalCode_Id
""".format(semester=semester, id_list=id_list)
proposals_text_sql = """
SELECT * FROM ProposalText
join ProposalCode using(ProposalCode_Id)
WHERE ProposalCode_Id in {id_list}
order by Semester_Id desc """.format(
id_list=sql_list_string(ids['ProposalCode_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()
tech_reports = {}
tech_report_sql = """
SELECT ProposalCode.Proposal_Code as Proposal_Code,
CONCAT(Semester.Year, '-', Semester.Semester) AS Semester,
FirstName, Surname, Email, Username,
TechReport, ProposalCode.ProposalCode_Id as ProposalCode_Id
FROM ProposalTechReport
JOIN ProposalCode ON ProposalTechReport.ProposalCode_Id = ProposalCode.ProposalCode_Id
JOIN Semester ON ProposalTechReport.Semester_Id = Semester.Semester_Id
LEFT JOIN Investigator ON ProposalTechReport.Astronomer_Id=Investigator.Investigator_Id
LEFT JOIN PiptUser ON Investigator.PiptUser_Id=PiptUser.PiptUser_Id
WHERE ProposalCode.ProposalCode_Id IN {id_list}
ORDER BY Semester.Year ASC, Semester.Semester ASC
""".format(id_list=id_list)
conn = sdb_connect()
for index, row in pd.read_sql(tech_report_sql, conn).iterrows():
proposal_code = row['Proposal_Code']
if proposal_code not in tech_reports:
tech_reports[proposal_code] = []
tech_reports[proposal_code].append(
dict(Semester=row['Semester'],
ReviewerFName=row['FirstName'],
ReviewerSName=row['Surname'],
ReviewerEmail=row['Email'],
ReviewerUsername=row['Username'],
Report=row['TechReport']))
conn.close()
requested_times = {}
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 {id_list}
""".format(id_list=id_list)
conn = sdb_connect()
for index, row in pd.read_sql(requested_time_sql, conn).iterrows():
proposal_code = row['Proposal_Code']
semester = str(row['Year']) + "-" + str(row['Semester'])
if proposal_code not in requested_times:
requested_times[proposal_code] = []
requested_times[proposal_code] = add_time_request(
row, requested_times[proposal_code])
conn.close()
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, ids, proposals_text, tech_reports, requested_times)
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 {id_list}
""".format(id_list=id_list)
conn = sdb_connect()
for index, row in pd.read_sql(partner_time_sql, conn).iterrows():
try:
proposal = proposals[row["Proposal_Code"]]
for p in proposal.time_requests:
if p.semester == row['CurSemester']:
p.distribution.append(
Distribution(partner_name=row['Partner_Name'],
partner_code=row['Partner_Code'],
time=int(row['TimePerPartner'])))
except KeyError:
pass
conn.close()
get_instruments(id_list, proposals)
get_targets(ids=ids, proposals=proposals)
all_time_sql = """
SELECT * FROM PriorityAlloc
join MultiPartner using (MultiPartner_Id)
join Partner using(Partner_Id)
join Semester using (Semester_Id)
join ProposalCode using (ProposalCode_Id)
left join TacProposalComment using (MultiPartner_Id)
where Concat(Year, "-", Semester) = "{semester}"
order by Proposal_Code""".format(semester=semester)
conn = sdb_connect()
prev_partner, prev_proposal = '', ''
for index, row in pd.read_sql(all_time_sql, conn).iterrows():
partner, proposal = row['Partner_Code'], row["Proposal_Code"]
pat = ProposalAllocatedTime(partner_code=row['Partner_Code'],
partner_name=row['Partner_Name'])
tac_comment = TacComment(partner_code=row['Partner_Code'],
comment=row['TacComment'])
if proposal in proposals:
if tac_comment not in proposals[proposal].tac_comment:
proposals[proposal].tac_comment.append(tac_comment)
if len(proposals[proposal].allocated_time) == 0:
proposals[proposal].allocated_time.append(
priority(row['Priority'], row['TimeAlloc'], pat))
prev_partner, prev_proposal = partner, proposal
else:
if partner == prev_partner and proposal == prev_proposal:
proposals[proposal].allocated_time[len(proposals[proposal].allocated_time) - 1] = \
priority(
row['Priority'],
row['TimeAlloc'],
proposals[proposal].allocated_time[len(proposals[proposal].allocated_time) - 1])
else:
proposals[proposal].allocated_time.append(
priority(row['Priority'], row['TimeAlloc'], pat))
prev_partner, prev_proposal = partner, proposal
conn.close()
return proposals.values()