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