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_eos_data_analysis(shift_start=None, event_id=None, analysis_only=True):
"""
Returns all data analysis based on a specified filter.
Args:
--
"""
return_data = ""
eosa = EndOfShiftAnalysis
base_query = eosa.query.options(DB.raiseload("*"))
first_only = False
if shift_start:
base_query = base_query.filter(eosa.shift_start == shift_start)
first_only = True
if event_id:
base_query = base_query.filter(eosa.event_id == event_id)
if first_only:
eos_data_analysis = base_query.first()
else:
eos_data_analysis = base_query.all()
return_data = eos_data_analysis
if eos_data_analysis and analysis_only:
return_data = eos_data_analysis.analysis
return return_data
def get_ewi_recipients(site_ids=None, site_codes=None, alert_level=0):
"""
Function that get ewi recipients per site
"""
query = UsersRelationship.query.join(UserOrganizations).join(
Sites).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("*")).filter(Users.ewi_recipient == 1)
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 alert_level != 0:
uer = UserEwiRestrictions
query = query.join(uer).filter(
DB.or_(uer.user_id.is_(None), uer.alert_level < alert_level))
user_per_site_query = query.all()
user_per_site_result = UsersRelationshipSchema(
many=True,
exclude=["emails", "teams", "landline_numbers",
"ewi_restriction"]).dump(user_per_site_query).data
return user_per_site_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_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_all_events_wo_relationship():
"""
Sample implementation of preventing lazy load on relationship
"""
events = MonitoringEvents.query.options(DB.raiseload(MonitoringEvents.releases)).filter(
MonitoringEvents.status == "finished").order_by(DB.desc(MonitoringEvents.event_id)).all()
event_data = MonitoringEventsSchema(
many=True, exclude=("releases", )).dump(events).data
return jsonify(event_data)
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_ids(site_codes):
"""
Function that gets site ids
"""
sites = Sites.query.options(DB.raiseload("*")).filter(
Sites.site_code.in_(site_codes)).all()
site_ids = []
for row in sites:
site_ids.append(row.site_id)
return site_ids
def get_organizations():
"""
"""
orgs = Organizations.query.options(
DB.raiseload("*")
).all()
result = OrganizationsSchema(many=True, exclude=["users"]) \
.dump(orgs).data
return jsonify(result)
def get_feature_id(feature_type):
feature = MomsFeatures.query.options(DB.raiseload("*")).filter(
MomsFeatures.feature_type == feature_type).first()
if feature is None:
if feature_type == "slide" or feature_type == "fall":
feature_id = 7 # Slope Failure
elif feature_type == "depression":
feature_id = 8
elif feature_type == "pond":
feature_id = 4
else:
feature_id = -1
else:
feature_id = feature.feature_id
return feature_id
def include_loading(include_list):
"""
Helper function that returns a list of SQLAlchemy
load relationships - either raiseload or subqueryload
depending on relationship
"""
relationship_list = []
for include_item in include_list:
prop = PROP_DICT[include_item[0]]
relationship = getattr(UsersRelationship, prop)
rel = DB.raiseload(relationship)
if include_item[1]:
rel = DB.subqueryload(relationship)
relationship_list.append(rel)
return relationship_list
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
def insert_event_alert(release, ts_start_for_next_event):
event_id = release.event_id
data_timestamp = release.data_timestamp
if ts_start_for_next_event is not None:
data_timestamp = ts_start_for_next_event
internal_alert = release.internal_alert_level
public_alert, trigger_list = get_public_alert_level(internal_alert,
return_triggers=True,
include_ND=True)
sym = PublicAlertSymbols.query.options(DB.raiseload("*")).filter(
PublicAlertSymbols.alert_symbol == public_alert).first()
new_pub_sym_id = sym.pub_sym_id
last_event_alert = get_last_event_alert(event_id)
try:
old_pub_sym_id = last_event_alert.pub_sym_id
event_alert_id = last_event_alert.event_alert_id
except:
old_pub_sym_id = 10
if old_pub_sym_id != new_pub_sym_id:
new_ea = MonitoringEventAlerts(event_id=event_id,
pub_sym_id=new_pub_sym_id,
ts_start=data_timestamp)
DB.session.add(new_ea)
DB.session.flush()
event_alert_id = new_ea.event_alert_id
try:
last_event_alert.ts_end = data_timestamp
except AttributeError:
pass
return event_alert_id, trigger_list
def get_sites_data(site_code=None, include_inactive=False, raise_load=False):
"""
Function that gets basic site data by site code
"""
final_query = Sites.query
if raise_load:
final_query = final_query.options(DB.raiseload("*"))
if site_code is None:
if not include_inactive:
final_query = final_query.filter_by(active=True)
site = final_query.all()
else:
if isinstance(site_code, (list, )):
site = final_query.filter(Sites.site_code.in_(site_code)).all()
else:
site = final_query.filter_by(site_code=site_code).first()
return site
def get_recipients_for_ground_meas(site_recipients):
"""
Function that get recipient per site
"""
feedback = []
for row in site_recipients:
site_ids = row["site_ids"]
user_per_site_result = []
if site_ids:
user_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("*")
).filter(
Users.ewi_recipient == 1, Sites.site_id.in_(site_ids),
UserOrganizations.org_id == 1, UserMobiles.status == 1
).all()
user_per_site_result = UsersRelationshipSchema(
many=True,
exclude=[
"emails", "teams", "landline_numbers", "ewi_restriction",
"mobile_numbers.mobile_number.blocked_mobile"
]).dump(user_per_site_query).data
row["recipients"] = user_per_site_result
feedback.append(row)
return feedback
def insert_to_moms_instances_table(feature):
feature_id = get_feature_id(feature.feature_type)
result = MomsInstances.query.options(DB.raiseload("*")).filter(
DB.and_(MomsInstances.site_id == feature.site_id,
MomsInstances.feature_id == feature_id,
MomsInstances.feature_name == feature.feature_name)).first()
if result is None:
# If not exists, insert new instance
moms_instance = MomsInstances(site_id=feature.site_id,
feature_id=feature_id,
feature_name=feature.feature_name)
DB.session.add(moms_instance)
DB.session.flush()
moms_instance_id = moms_instance.instance_id
else:
# If exists, return existing ID
moms_instance_id = result.instance_id
return moms_instance_id
def get_last_site_event(site_id):
return MonitoringEvents.query.options(DB.raiseload("*")) \
.filter(MonitoringEvents.site_id == site_id).order_by(
DB.desc(MonitoringEvents.event_start)).first()
def get_last_event_alert(event_id):
return MonitoringEventAlerts.query.options(DB.raiseload("*")).filter(
MonitoringEventAlerts.event_id == event_id).order_by(
DB.desc(MonitoringEventAlerts.ts_start)).first()
