def get_latest_data_presence(group, item_name="all"):
"""
group (str): type of data group to get,
can be "rain_gauges", "tsm" or "loggers"
item_name (str): defaults to "all", specific entry to query
(e.g. logger name like "agbta" for group "loggers")
"""
is_many = True
if item_name != "all":
is_many = False
options = False
if group == "rain_gauges":
table = DataPresenceRainGauges
options = DB.joinedload("rain_gauge").raiseload("*")
schema = DataPresenceRainGaugesSchema(
many=is_many, exclude=("rain_gauge.rainfall_alerts", "rain_gauge.rainfall_priorities"))
join_table = [RainfallGauges]
order = RainfallGauges.gauge_name
filter_attr = RainfallGauges.gauge_name
elif group == "tsm":
table = DataPresenceTSM
options = DB.joinedload("tsm_sensor")
schema = DataPresenceTSMSchema(many=is_many)
join_table = [TSMSensors, Loggers]
order = Loggers.logger_name
filter_attr = Loggers.logger_name
elif group == "loggers":
table = DataPresenceLoggers
schema = DataPresenceLoggersSchema(many=is_many)
join_table = [Loggers]
order = Loggers.logger_name
filter_attr = Loggers.logger_name
elif group == "surficial":
pass
else:
return "Data group inputs for querying data presence can only be 'rain_gauges', 'surficial', 'tsm' or 'loggers'"
if group != "surficial":
query = DB.session.query(table)
if options:
query = query.options(options)
for jt in join_table:
query = query.join(jt)
if item_name != "all":
query = query.filter(filter_attr == item_name).first()
else:
query = query.order_by(order).all()
result = schema.dump(query).data
else:
result = get_surficial_data_presence()
return jsonify(result)
def get_dynaslope_users(active_only=True, return_schema_format=False):
"""
Function that gets all Dynaslope users and related data
"""
ur = UsersRelationship
query = ur.query.options(
DB.joinedload("account", innerjoin=True).raiseload("*"),
DB.raiseload("*")).order_by(ur.last_name)
if active_only:
# Note use status in users instead of is_active in UserAccounts
query = query.filter_by(status=1)
result = query.all()
if return_schema_format:
result = UsersRelationshipSchema(many=True,
exclude=[
"mobile_numbers", "organizations",
"ewi_restriction", "teams",
"landline_numbers", "emails"
]).dump(result).data
return result
def get_site_season(site_code=None, site_id=None, return_schema_format=True):
"""
"""
query = Sites.query.options(
DB.joinedload("season_months",
innerjoin=True).subqueryload("routine_schedules"),
DB.raiseload("*"))
is_many = True
if site_code or site_id:
is_many = False
if site_code:
query = query.filter_by(site_code=site_code)
if site_id:
query = query.filter_by(site_id=site_id)
result = query.first()
else:
result = query.all()
if return_schema_format:
schema = SitesSchema(many=is_many, include=["season_months"])
result = schema.dump(result).data
return result
def get_contacts_per_site(site_ids=None,
site_codes=None,
only_ewi_recipients=True,
include_ewi_restrictions=False,
org_ids=None,
return_schema_format=True,
include_inactive_numbers=False):
"""
Function that get contacts per site
"""
query = UsersRelationship.query.join(UserOrganizations).join(Sites).join(
UserMobiles).options(
DB.subqueryload("mobile_numbers").joinedload("mobile_number",
innerjoin=True),
DB.subqueryload("organizations").joinedload("site",
innerjoin=True),
DB.subqueryload("organizations").joinedload("organization",
innerjoin=True),
DB.raiseload("*"))
if site_ids:
query = query.filter(Sites.site_id.in_(site_ids))
if site_codes:
query = query.filter(Sites.site_code.in_(site_codes))
if org_ids:
query = query.join(UserOrganizations).filter(
UserOrganizations.org_id.in_(org_ids))
if only_ewi_recipients:
query = query.filter(Users.ewi_recipient == 1)
schema_exclusions = [
"emails", "teams", "landline_numbers", "ewi_restriction"
]
if include_ewi_restrictions:
# uer = UserEwiRestrictions
query = query.options(DB.joinedload("ewi_restriction"))
schema_exclusions.remove("ewi_restriction")
if not include_inactive_numbers:
query = query.filter(UserMobiles.status == 1)
user_per_site_result = query.all()
if return_schema_format:
user_per_site_result = UsersRelationshipSchema(
many=True, exclude=schema_exclusions) \
.dump(user_per_site_result).data
return user_per_site_result
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
def find_narrative_event_id(timestamp, site_id):
"""
"""
me = MonitoringEvents
mea = MonitoringEventAlerts
event_id = None
filtering = DB.or_(
DB.and_(mea.ts_start <= timestamp, timestamp <= mea.ts_end),
DB.and_(mea.ts_start <= timestamp, mea.ts_end == None))
event_alert = mea.query.options(DB.joinedload("event", innerjoin=True), DB.raiseload("*")) \
.order_by(DB.desc(mea.event_alert_id)) \
.join(me).filter(filtering).filter(me.site_id == site_id) \
.first()
if event_alert:
event_id = event_alert.event.event_id
return event_id
