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()