def germplasm_search(studyDbId, pageSize=None, page=None):
params = list()
query = "SELECT DISTINCT sites_cultivars.cultivar_id AS cultivar_id " \
"FROM experiments_sites, sites_cultivars " \
"WHERE experiments_sites.site_id = sites_cultivars.site_id "
if studyDbId:
query += "AND experiments_sites.experiment_id = %s "
params.append(studyDbId)
logging.debug(query)
# count first
count = helper.query_count(query, params)
# execute query
results = helper.query_result(query, params, pageSize, page)
# wrap result
data = []
for row in results:
entry = api.germplasm.get_result(dataonly=True, germplasmDbId=row['cultivar_id'])
if entry:
data.append(entry[0])
else:
logging.warning("Missing germplasm for " + str(row['cultivar_id']))
count -= 1
return helper.create_result({"data": data}, count)
def search(pageSize=None, page=None):
query = "SELECT DISTINCT commonname FROM species ORDER BY commonname"
count = helper.query_count(query)
res = helper.query_result(query, [], pageSize, page)
data = [r[0] for r in res]
return helper.create_result({"data": data}, count, pageSize, page)
def search(observationVariableDbId=None, traitClass=None, pageSize=None, page=None):
"""Returns information on variables
Arguments:
observationVariableDbId: specific variable to return information on
traitClass: not implemented
pageSize: the desired size of return pages
page: the number of the page to return (starting at zero)
"""
query = "SELECT id, name, units, min, max FROM variables AS v"
params = []
where_clause = " WHERE"
# add a filter on the variable ID
if observationVariableDbId:
query += where_clause + " v.id = %s "
params.append(observationVariableDbId)
where_clause = " AND"
# add a filter on the trait class - not implemented at this time
#if traitClass:
# NOTE: this code is invalid for filtering on traitClass, it's just a placeholder
# query += where_clause + " s.id = %s "
# params.append(traitClass)
# where_clause = " AND"
query += " ORDER BY v.id"
# count first
count = helper.query_count(query, params)
# execute query
result = helper.query_result(query, params, pageSize, page)
# wrap result
data = list()
for row in result:
data.append({
"name": row["name"],
"observationVariableName": row["name"],
"observationVariableDbId": str(row["id"]),
"scale": {
"name": row["units"],
"validValues": {
"min": int(round(float(row["min"]))) if "Infinity" not in row["min"] else None,
"max": int(round(float(row["max"]))) if "Infinity" not in row["max"] else None
}
}
})
return helper.create_result({"data": data}, count, pageSize, page)
def search(seasonDbId=None, season=None, year=None, pageSize=None, page=None):
"""
Return a list of all seasons. Right now this will return the seasons as the
year and month of the startdate. The database-id that is returned will be of
the format YYYYMM.
:param year: filter the seasons on the yaer
:param pageSize: number of elements to return
:param page: which page to return
:return: all seasons in the page
"""
params = list()
query = "select * from (select distinct extract(year from start_date) as year, " \
"LTRIM(RTRIM(SPLIT_PART(name, ': ', 1))) as season," \
"md5(LTRIM(RTRIM(SPLIT_PART(name, ': ', 1))))::varchar(255) as id from experiments) season_list "
# add a filter on the season ID
if seasonDbId:
query += " WHERE id = %s "
params.append(seasonDbId)
# add a filter on the year
if year:
if seasonDbId:
query += " AND year = %s"
else:
query += " WHERE year = %s"
params.append(year)
if season:
if year or seasonDbId:
query += " AND season = %s"
else:
query += " WHERE season = %s"
params.append(season)
query += " ORDER BY id"
# count first
count = helper.query_count(query, params)
# execute query
result = helper.query_result(query, params, pageSize, page)
# wrap result
data = list()
for row in result:
data.append({
"season": row["season"],
"year": str(int(row["year"])),
"seasonDbId": str(row["id"])
})
return helper.create_result({"data": data}, count, pageSize, page)
def layouts_search(studyDbId, pageSize=None, page=None):
params = list()
query = "SELECT DISTINCT experiments.id as studyDbId, " \
" experiments.name as studyName, " \
" experiments_sites.site_id as observation_unit_db_id, " \
" sites.sitename as location_abbreviation, " \
" cultivars.id as germplasmDbId, " \
" cultivars.name as germplasmName " \
"FROM experiments, experiments_sites, sites, sites_cultivars, cultivars " \
"WHERE experiments.id = experiments_sites.experiment_id " \
"AND sites.id = experiments_sites.site_id " \
"AND sites_cultivars.site_id = experiments_sites.site_id " \
"AND sites_cultivars.cultivar_id = cultivars.id "
if studyDbId:
query += "AND experiments.id = %s "
params.append(studyDbId)
logging.debug(query)
# count first
count = helper.query_count(query, params)
# execute query
results = helper.query_result(query, params, pageSize, page)
# wrap result
data = []
for row in results:
entry = dict()
entry['studyDbId'] = str(row['studydbid'])
entry['studyName'] = row['studyname']
entry['germplasmName'] = row['germplasmname']
entry['germPlasmDbId'] = str(row['germplasmdbid'])
entry['observationLevel'] = 'plot'
entry['observationUnitDbId'] = str(row['observation_unit_db_id'])
entry['observationUnitName'] = row['location_abbreviation']
data.append(entry)
return helper.create_result({"data": data}, count, pageSize, page)
def search(germplasmDbId=None,
observationVariableDbId=None,
studyDbId=None,
locationDbId=None,
trialDbId=None,
programDbId=None,
seasonDbId=None,
observationUnitDbId=None,
observationLevel=None,
observationTimeStampRangeStart=None,
observationTimeStampRangeEnd=None,
pageSize=None,
page=None):
if observationTimeStampRangeStart:
observationTimeStampRangeStart = deserialize_datetime(
observationTimeStampRangeStart)
if observationTimeStampRangeEnd:
observationTimeStampRangeEnd = deserialize_datetime(
observationTimeStampRangeEnd)
# Return observations only if a single plot is specified via observationLevel
if observationUnitDbId is not None:
query = "select v.id::text as observationVariableDbId, \
v.name as observationVariableName, \
t.id::text as observationDbId, \
t.mean::text as value, \
t.date as observationTimeStamp, \
s.sitename as observationUnitName, \
es.experiment_id::text as studyDbId, \
et.treatment_id as treatmentDbId, \
seasons.id as seasonDbId, \
tr.name as factor, \
tr.definition as modality, \
t.entity_id as replicate, \
c.author as operator, \
t.checked as quality \
from traits t, variables v, sites s, experiments e, experiments_sites es, experiments_treatments et, treatments tr, citations c, \
(select distinct extract(year from start_date) as year, LTRIM(RTRIM(SPLIT_PART(name, ': ', 1))) as season, \
md5(LTRIM(RTRIM(SPLIT_PART(name, ': ', 1))))::varchar(255) as id from experiments) seasons \
where v.id = t.variable_id \
and t.site_id = s.id and t.citation_id = c.id and t.checked > -1 \
and e.id = es.experiment_id and t.site_id = es.site_id \
and e.id = et.experiment_id and tr.id = et.treatment_id \
and seasons.season = LTRIM(RTRIM(SPLIT_PART(e.name, ': ', 1))) "
else:
query = "select s.sitename as observationUnitName, \
es.experiment_id::text as studyDbId, \
et.treatment_id as treatmentDbId, \
seasons.id as seasonDbId, \
tr.name as factor, \
tr.definition as modality \
from sites s, experiments e, experiments_sites es, experiments_treatments et, treatments tr, \
(select distinct extract(year from start_date) as year, LTRIM(RTRIM(SPLIT_PART(name, ': ', 1))) as season, \
md5(LTRIM(RTRIM(SPLIT_PART(name, ': ', 1))))::varchar(255) as id from experiments) seasons \
where e.id = es.experiment_id \
and e.id = et.experiment_id and tr.id = et.treatment_id \
and seasons.season = LTRIM(RTRIM(SPLIT_PART(e.name, ': ', 1))) "
params = []
if observationVariableDbId is not None:
query += " and v.id = %s "
params.append(observationVariableDbId)
if locationDbId is not None:
query += " and t.site_id = %s "
params.append(locationDbId)
if studyDbId is not None:
query += " and e.id = %s "
params.append(studyDbId)
if germplasmDbId is not None:
query += " and t.cultivar_id = %s "
params.append(germplasmDbId)
if seasonDbId is not None:
query += " AND seasons.id = %s "
params.append(seasonDbId)
if observationUnitDbId is not None:
query += " AND s.sitename = %s "
params.append(observationUnitDbId)
if (observationTimeStampRangeStart and observationTimeStampRangeEnd):
query += " and (date >= %s and date <= %s) "
params.append(observationTimeStampRangeStart)
params.append(observationTimeStampRangeEnd)
elif observationTimeStampRangeStart:
query += " and date >= %s "
params.append(observationTimeStampRangeStart)
elif observationTimeStampRangeEnd:
query += " and date <= %s "
params.append(observationTimeStampRangeEnd)
count = helper.query_count(query, params)
res = helper.query_result(query, params, pageSize, page)
data = _conform_data([dict(r) for r in res])
if observationUnitDbId is not None:
# Group observations together under the same ObservationUnit
grouped_data = {}
for obs in data:
obs_name = obs["observationUnitName"]
if obs_name not in grouped_data:
grouped_data[obs_name] = obs
else:
grouped_data[obs_name]["observations"] += obs["observations"]
final_data = []
for obs_name in grouped_data:
final_data.append(grouped_data[obs_name])
else:
final_data = data
# split data if needed, remembering total number
if not pageSize:
pageSize = helper.DEFAULT_PAGE_SIZE
if not page:
page = 0
return helper.create_result({"data": final_data}, count, pageSize, page)
def search(studyDbId=None,
observationUnitDbId=None,
eventDbId=None,
eventType=None,
dateRangeStart=None,
dateRangeEnd=None,
pageSize=None,
page=None):
query = "WITH event_parameters as ( select " \
"m.id as id, array_agg(array[m.level::text, m.units, null]) as events " \
"from managements m " \
"where " \
"m.id in (select m.id from experiments ex join experiments_treatments et on ex.id = et.experiment_id " \
"join treatments t on et.treatment_id = t.id " \
"join managements_treatments mt on t.id = mt.treatment_id " \
"join managements m on mt.management_id = m.id) " \
"group by m.id) " \
"select DISTINCT" \
" m.date as date," \
" m.id::text as eventDbId," \
" m.mgmttype as eventType," \
" m.notes as eventDescription, " \
" array_agg(s.id::text) as observationUnitDbIds, " \
" ex.id::text as studyDbId," \
" ev.events as events " \
"from" \
" experiments ex" \
" join experiments_treatments et on ex.id = et.experiment_id" \
" join treatments t on et.treatment_id = t.id" \
" join managements_treatments mt on t.id = mt.treatment_id" \
" join managements m on mt.management_id = m.id" \
" join experiments_sites es on ex.id = es.experiment_id" \
" join sites s on es.site_id = s.id " \
" join event_parameters ev on ev.id = m.id"
params = []
where_clause = " WHERE"
# add a filter on the study ID
if studyDbId:
query += where_clause + " ex.id = %s "
params.append(studyDbId)
where_clause = " AND"
# add a filter on the observationUnitDbId
if observationUnitDbId:
query += where_clause + " s.id = %s "
params.append(observationUnitDbId)
where_clause = " AND"
# add a filter on the eventDbId
if eventDbId:
query += where_clause + " m.id = %s "
params.append(eventDbId)
where_clause = " AND"
# add a filter on the eventType
if eventType:
query += where_clause + " m.mgmttype = %s " #could use like for, e.g. fertilizer
params.append(eventType)
where_clause = " AND"
if (dateRangeStart and dateRangeEnd):
query += where_clause + " (date > = %s and date <= %s) "
params.append(dateRangeStart)
params.append(dateRangeEnd)
elif dateRangeStart:
query += where_clause + " m.date >= %s "
params.append(dateRangeStart)
elif dateRangeEnd:
query += where_clause + " m.date <= %s "
params.append(dateRangeEnd)
query += " GROUP BY m.date, m.id, m.mgmttype, m.notes, ex.id, ev.events ORDER BY date"
# count first
count = helper.query_count(query, params)
# execute query
result = helper.query_result(query, params, pageSize, page)
# wrap result
data = list()
for row in result:
events = []
for item in row['events']:
new_item = [{
"key": "level",
"value": item[0]
}, {
"key": "units",
"value": item[1]
}]
events.append(new_item)
data.append({
"date": row["date"],
"eventDbId": row["eventdbid"],
"eventType": row["eventtype"],
"eventDescription": row["eventdescription"],
"studyDbId": row["studydbid"],
"eventParameters": events,
"observationUnitDbIds": row["observationunitdbids"]
})
return helper.create_result({"data": data}, count, pageSize, page)
def query(single_row=False, locationDbId=None, locationType=None, pageSize=None, page=None):
"""
:param single_row: return a single row back, not wrapped
:param locationDbId:
:return:
"""
# TODO map locationType to something
params = list()
# get all sitegroups and sites
query = "SELECT sitegroups.id AS locationDbId, " \
" sitegroups.name AS name, " \
" sites.country AS countryCode, " \
" sites.geometry AS geometry " \
"FROM sites, sitegroups, sitegroups_sites " \
"WHERE sitegroups_sites.site_id = sites.id " \
" AND sitegroups_sites.sitegroup_id = sitegroups.id "
if locationDbId:
query += "AND sitegroups.id = %s"
params.append(locationDbId)
# compute the bounding box
query = "SELECT locationDbId, " \
" name, " \
" countryCode, " \
" ST_Extent(geometry) AS geometry " \
"FROM (" + query + ") ss1 " \
"GROUP BY locationDbId, name, countryCode "
# compute center point
query = "SELECT DISTINCT locationDbId::text, " \
" name, " \
" countryCode, " \
" ST_X(ST_CENTROID(geometry)) AS longitude, " \
" ST_Y(ST_CENTROID(geometry)) AS latitude, " \
" ST_Z(ST_CENTROID(geometry)) AS altitude " \
"FROM (" + query + ") ss2"
# order query
query += " ORDER BY locationDbId"
# count first
if single_row:
count = 1
else:
count = helper.query_count(query, params)
# execute query
results = helper.query_result(query, params, pageSize, page)
# wrap result
data = []
for row in results:
location = {k: v for k, v in row.items() if v}
if 'countryCode' not in location:
location['countryCode'] = location.pop('countrycode', '')
if 'altitude' not in location:
location['altitude'] = 0
if 'locationDbId' not in location:
location['locationDbId'] = location.pop('locationdbid', '')
data.append(location)
if single_row:
break
if single_row:
if data:
return data[0]
return {}
else:
return helper.create_result({"data": data}, count, pageSize, page)
def search(commonCropName=None, studyTypeDbId=None, programDbId=None, locationDbId=None,
seasonDbId=None, trialDbId=None, studyDbId=None,
active=None, sortBy=None, sortOrder=None, pageSize=None, page=None):
params = list()
query = "SELECT DISTINCT experiments.id::text as studyDbId, " \
" LTRIM(RTRIM(SPLIT_PART(experiments.name, ': ', 2))) as studyName, " \
" experiments.start_date as startDate, " \
" experiments.end_date as endDate, " \
" experiments.description as studyDescription, " \
" sitegroups.id::text as location_id, " \
" seasonids.id::text as season_id " \
"FROM experiments, experiments_sites, sitegroups, sitegroups_sites, " \
"(select * from (select distinct extract(year from start_date) as year, " \
"LTRIM(RTRIM(SPLIT_PART(name, ': ', 1))) as season," \
"md5(LTRIM(RTRIM(SPLIT_PART(name, ': ', 1))))::varchar(255) as id from experiments) season_list) seasonids " \
"WHERE experiments.id = experiments_sites.experiment_id " \
"AND sitegroups_sites.site_id = experiments_sites.site_id " \
"AND sitegroups_sites.sitegroup_id = sitegroups.id " \
"AND seasonids.season = LTRIM(RTRIM(SPLIT_PART(experiments.name, ': ', 1))) "
if studyDbId:
query += " AND experiments.id = %s "
params.append(studyDbId)
if seasonDbId:
query += " AND seasonids.id = %s "
params.append(seasonDbId)
if sortBy:
if sortBy == "studyDbId":
query += " ORDER BY experiments.id"
elif sortBy == "locationDbId":
query += " ORDER BY sitegroups.id"
elif sortBy == "seasonDbId":
query += " ORDER BY seasonids.id"
elif sortBy == "studyName":
query += " ORDER BY LTRIM(RTRIM(SPLIT_PART(experiments.name, ': ', 2)))"
elif sortBy == "studyLocation":
query += " ORDER BY sitegroups.name"
else:
# programDbId, trialDbId, studyTypeDbId, programName - unsupported
pass
if sortOrder:
# do this here, params will add apostrophes around it
if sortOrder.lower() == "asc":
query += " ASC"
elif sortOrder.lower() == "desc":
query += " DESC"
logging.debug(query)
# count first
count = helper.query_count(query, params)
# execute query
results = helper.query_result(query, params, pageSize, page)
# wrap result
data = []
for row in results:
study = dict()
study['studyDbId'] = str(row_pop(row, 'studydbid', ''))
study['studyName'] = row_pop(row, 'studyname', '')
study['startDate'] = row_pop(row, 'startdate', '')
study['endDate'] = row_pop(row, 'enddate', '')
current_descrption = row_pop(row, 'studydescription', '')
current_descrption = current_descrption.replace('\n', ' ')
current_descrption = current_descrption.replace('\r', '')
study['statisticalDesign'] = {'description': current_descrption }
# get seasons data
if row.has_key('season_id'):
season = api.seasons.search(row['season_id'])
study['seasons'] = season['result']['data']
# check location ID
if row.has_key('location_id'):
if locationDbId and locationDbId != str(row['location_id']):
continue
location = api.locations.query(single_row=True, locationDbId=row['location_id'])
if location:
study['location'] = location
study['studyTypeName'] = "Phenotyping Study"
study['documentationURL'] = "https://docs.terraref.org"
data.append(study)
return helper.create_result({"data": data}, count, pageSize, page)