def get_surficial_data(site_code=None,
marker_id=None,
data_id=None,
mo_id=None,
ts_order="asc",
end_ts=None,
start_ts=None,
limit=None,
anchor="marker_data"):
"""
Returns surficial data of a site or marker specified.
You can filter data more using start, end timestamps and a limit.
anchor (string): choose whether to return 'marker_observation'
or 'marker_data'
"""
if data_id:
filtered_query = md.query.filter(md.data_id == data_id)
elif mo_id:
filtered_query = mo.query.filter(mo.mo_id == mo_id)
else:
if anchor == "marker_observations":
base_query = mo.query
else:
base_query = md.query.join(mo)
if ts_order == "asc":
base_query = base_query.order_by(DB.asc(mo.ts))
elif ts_order == "desc":
base_query = base_query.order_by(DB.desc(mo.ts))
if marker_id:
filtered_query = base_query.filter(md.marker_id == marker_id)
if site_code:
filtered_query = base_query.join(Sites).filter(
Sites.site_code == site_code)
if end_ts:
if not isinstance(end_ts, datetime):
end_ts = datetime.strptime(end_ts, "%Y-%m-%d %H:%M:%S")
filtered_query = filtered_query.filter(mo.ts <= end_ts)
if start_ts:
if not isinstance(start_ts, datetime):
start_ts = datetime.strptime(start_ts, "%Y-%m-%d %H:%M:%S")
filtered_query = filtered_query.filter(mo.ts >= start_ts)
if limit:
filtered_query = filtered_query.limit(limit)
if limit == 1:
filtered_marker_data = filtered_query.first()
else:
filtered_marker_data = filtered_query.all()
return filtered_marker_data
def get_narratives(offset=None,
limit=None,
start=None,
end=None,
site_ids=None,
include_count=None,
search=None,
event_id=None,
raise_site=True):
"""
Returns one or more row/s of narratives.
Args:
offset (Integer) -
limit (Integer) -
start () -
end () -
site_ids (Integer) -
include_count (Boolean)
search (String)
event_id (Integer)
"""
nar = Narratives
base = nar.query
if raise_site:
base = base.options(DB.raiseload("site"))
if start is None and end is None:
pass
else:
base = base.filter(nar.timestamp.between(start, end))
if not event_id:
if site_ids:
base = base.filter(nar.site_id.in_(site_ids))
if search != "":
base = base.filter(nar.narrative.ilike("%" + search + "%"))
narratives = base.order_by(DB.desc(
nar.timestamp)).limit(limit).offset(offset).all()
DB.session.commit()
# DB.session.commit()
if include_count:
count = get_narrative_count(base)
return [narratives, count]
else:
return narratives
else:
narratives = base.order_by(DB.asc(
nar.timestamp)).filter(nar.event_id == event_id).all()
DB.session.commit()
return narratives
def get_users_categorized_by_org(site_code=None):
"""
"""
u_org = UserOrganizations
org = Organizations
base = u_org.query.join(org).order_by(DB.asc(org.scope))
if site_code:
base = base.join(Sites).filter(Sites.site_code == site_code)
users_by_org = base.all()
return users_by_org
def get_org_ids(scopes=None, org_names=None):
"""
Returns org ids as need by other APIs
"""
orgs = Organizations
base = orgs.query.order_by(DB.asc(orgs.scope))
if scopes:
base = base.filter(orgs.scope in scopes)
if org_names:
base = base.filter(orgs.org_name in org_names)
org_ids = base.all()
org_id_list = []
for item in org_ids:
org_id_list.append(item.org_id)
return org_id_list
def get_site_subsurface_columns(site_code, include_deactivated=False):
"""
Returns one or more row/s of subsurface_columns.
Edit: [190320] - no provisions for None site_code parameter.
Args:
site_id
"""
sub_col = TSMSensors
filter_var = Loggers.logger_name.like("%" + str(site_code) + "%")
query = sub_col.query.join(Loggers).options(
DB.joinedload("logger").joinedload("logger_model").raiseload("*"),
DB.joinedload("logger").joinedload(
"site", innerjoin=True).raiseload("*")
).order_by(
DB.asc(Loggers.logger_name), DB.desc(sub_col.date_activated)).filter(filter_var)
if not include_deactivated:
query = query.filter(sub_col.date_deactivated.is_(None))
sub_column = query.all()
return sub_column
