def to_DB(df):
df = df.rename(columns={'dyna': 'alert', 'sandbox': 'alert_ref'})
if not qdb.does_table_exist('db_comparison', hostdb='sandbox'):
create_db_comparison()
query = "SELECT EXISTS (SELECT * FROM db_comparison"
query += " WHERE ts = '%s' AND site_code = '%s')" % (
df['ts'].values[0], df['site_code'].values[0])
if qdb.get_db_dataframe(query, hostdb='sandbox').values[0][0] == 0:
qdb.push_db_dataframe(df, 'db_comparison', index=False)
def main(connection='analysis'):
start_time = datetime.now()
qdb.print_out(start_time)
ts = round_data_ts(start_time)
release_data_ts = release_time(ts) - timedelta(hours=0.5)
if qdb.does_table_exist('operational_triggers') == False:
qdb.create_operational_triggers()
query = "SELECT trigger_id, ts, site_id, trigger_source, "
query += "alert_level, ts_updated FROM "
query += " (SELECT * FROM operational_triggers "
query += " WHERE ts <= '%s' " % ts
query += " AND ts_updated >= '%s' " % (ts - timedelta(2))
query += " ) AS op "
query += "INNER JOIN "
query += " (SELECT trigger_sym_id, alert_level, trigger_source FROM "
query += " (SELECT * FROM operational_trigger_symbols "
query += " WHERE alert_level > 0 "
query += " ) AS trig_sym "
query += " INNER JOIN "
query += " (SELECT * FROM trigger_hierarchies WHERE trigger_source not in ('moms', 'on demand')) AS trig "
query += " USING (source_id) "
query += " ) AS sym "
query += "USING (trigger_sym_id) "
query += "ORDER BY ts_updated DESC"
curr_trig = db.df_read(query, connection=connection)
if len(curr_trig) == 0:
qdb.print_out('no new trigger')
return
if not qdb.does_table_exist('alert_status'):
qdb.create_alert_status()
site_curr_trig = curr_trig.groupby('site_id', as_index=False)
site_curr_trig.apply(site_alerts,
ts=ts,
release_data_ts=release_data_ts,
connection=connection)
def main():
start_time = datetime.now()
qdb.print_out(start_time)
ts = pub.round_data_ts(start_time)
release_data_ts = pub.release_time(ts) - timedelta(hours=0.5)
if qdb.does_table_exist('operational_triggers') == False:
qdb.create_operational_triggers()
query = "SELECT trigger_id, ts, site_id, trigger_source, "
query += "alert_level, ts_updated FROM "
query += " (SELECT * FROM operational_triggers "
query += " WHERE ts <= '%s' " % ts
query += " AND ts_updated >= '%s' " % (ts - timedelta(1))
query += " ) AS op "
query += "INNER JOIN "
query += " (SELECT trigger_sym_id, alert_level, trigger_source FROM "
query += " (SELECT * FROM operational_trigger_symbols "
query += " WHERE alert_level > 0 "
query += " ) AS trig_sym "
query += " INNER JOIN "
query += " trigger_hierarchies AS trig "
query += " ON trig_sym.source_id = trig.source_id "
query += " ) AS sym "
query += "ON op.trigger_sym_id = sym.trigger_sym_id "
query += "ORDER BY ts_updated DESC"
curr_trig = qdb.get_db_dataframe(query)
if len(curr_trig) == 0:
qdb.print_out('no new trigger')
return
if not qdb.does_table_exist('alert_status'):
qdb.create_alert_status()
curr_trig = curr_trig.rename(columns={"ts_updated": "ts_last_retrigger"})
site_curr_trig = curr_trig.groupby('site_id', as_index=False)
site_curr_trig.apply(site_alerts, ts=ts, release_data_ts=release_data_ts)
def site_public_alert(site_props, end, public_symbols, internal_symbols,
start_time):
"""Dataframe containing necessary information for public release.
Args:
site_props (dataframe): ID and three-letter code of each site.
end (datetime): Public alert timestamp.
public_symbols (dataframe): Public alert symbols and id corresponding
to its alert level.
internal_symbols (dataframe): Internal alert symbols and id
corresponding to its alert level.
Returns:
dataframe: Contains timestamp, three-letter site code, public alert,
internal alert, validity of alert, subsurface alert,
surficial alert, rainfall alert, most recent timestamp of
alert > 0 (if any) per alert level per operational trigger.
"""
# id and three-letter code per site
site_code = site_props['site_code'].values[0]
site_id = site_props['site_id'].values[0]
# LOUIE
print(f"Site Code: {site_code.upper()}")
# Creates a public_alerts table if it doesn't exist yet
if qdb.does_table_exist('public_alerts') == False:
qdb.create_public_alerts()
# start of monitoring: start of event or 24 hours from "end"
try:
monitoring_type = get_monitoring_type(site_id, end)
except:
monitoring_type = 'routine'
if monitoring_type == 'event':
start_monitor = event_start(site_id, end)
else:
start_monitor = end - timedelta(1)
# operational triggers for monitoring at timestamp end
op_trig = get_operational_trigger(site_id, start_monitor, end)
release_op_trig = op_trig[op_trig.ts_updated >= \
release_time(end)-timedelta(hours=4)]
release_op_trig = release_op_trig.drop_duplicates(['source_id', \
'alert_level'])
subsurface_id = internal_symbols[internal_symbols.trigger_source == \
'subsurface']['source_id'].values[0]
surficial_id = internal_symbols[internal_symbols.trigger_source == \
'surficial']['source_id'].values[0]
moms_id = internal_symbols[internal_symbols.trigger_source == \
'moms']['source_id'].values[0]
release_op_trig = release_op_trig[~((release_op_trig.source_id \
== subsurface_id) & (release_op_trig.ts_updated < end))]
pos_trig = op_trig[(op_trig.alert_level > 0) & ~((op_trig.alert_level == 1) \
& (op_trig.source_id == surficial_id))]
last_pos_trig = pos_trig.drop_duplicates(['source_id', \
'alert_level'])
# public alert based on highest alert level in operational triggers
public_alert = max(list(pos_trig['alert_level'].values) + [0])
print('Public Alert %s' % public_alert)
print()
# SUBSURFACE ALERT
subsurface = get_tsm_alert(site_id, end)
# SURFICIAL ALERT
if public_alert > 0:
surficial_ts = release_time(end) - timedelta(hours=4)
else:
surficial_ts = pd.to_datetime(end.date())
try:
surficial = op_trig[(op_trig.source_id == surficial_id) & \
(op_trig.ts_updated >= surficial_ts)]['alert_level'].values[0]
except:
surficial = -1
# MOMS ALERT NOTE: Following code is redundant with above
# TODO: LOUIE
if public_alert > 0:
moms_ts = release_time(end) - timedelta(hours=4)
else:
moms_ts = pd.to_datetime(end.date())
try:
moms_alert = op_trig[(op_trig.source_id == moms_id) & \
(op_trig.ts_updated >= moms_ts)]['alert_level'].values[0]
except:
moms_alert = -1
# NOTE: LOUIE
# Get moms from database sorted by obs_ts and highest alert level
sorted_ma_df, moms_alert_level = get_site_moms_alerts(
site_id=site_id, start=moms_ts, end=release_time(end))
unresolved_moms_df, has_unresolved_moms = check_if_has_unresolved_moms_instance(
sorted_ma_df)
# RAINFALL ALERT
rainfall_id = internal_symbols[internal_symbols.trigger_source == \
'rainfall']['source_id'].values[0]
try:
rainfall = op_trig[(op_trig.source_id == rainfall_id) & \
(op_trig.ts_updated >= end - \
timedelta(hours=0.5))]['alert_level'].values[0]
except:
rainfall = -1
# INTERNAL ALERT
internal_id = internal_symbols[internal_symbols.trigger_source == \
'internal']['source_id'].values[0]
if public_alert > 0:
# validity of alert
validity = pd.to_datetime(max(pos_trig['ts_updated'].values)) \
+ timedelta(1)
validity = release_time(validity)
if public_alert == 3:
validity += timedelta(1)
# internal alert based on positive triggers and data presence
internal_df = get_internal_alert(pos_trig, release_op_trig,
internal_symbols)
# check if rainfall > 0.75% of threshold
rain75_id = internal_symbols[(internal_symbols.source_id == \
rainfall_id)&(internal_symbols.alert_level \
== -2)]['trigger_sym_id'].values[0]
if rainfall == 0 and end >= validity - timedelta(hours=0.5):
internal_df, is_x = replace_rainfall_alert_if_rx(
internal_df, internal_symbols, site_id, end, rainfall_id,
rain75_id)
if is_x == True:
rainfall = -2
internal_df = internal_df.sort_values('hierarchy_id')
internal_alert = ''.join(internal_df['alert_symbol'].values)
if public_alert > 1:
internal_alert = public_symbols[public_symbols.alert_level == \
public_alert]['alert_symbol'].values[0] + '-' + \
internal_alert
# ground data presence: subsurface, surficial, moms
if public_alert <= 1:
# if surficial == -1 and len(subsurface[subsurface.alert_level != -1]) == 0:
# NOTE: LOUIE - trial code to include moms
if surficial == -1 and moms_alert == -1 and len(
subsurface[subsurface.alert_level != -1]) == 0:
ground_alert = -1
else:
ground_alert = 0
if public_alert == 0 or ground_alert == -1:
pub_internal = internal_symbols[(internal_symbols.alert_level == \
ground_alert) & (internal_symbols.source_id == \
internal_id)]['alert_symbol'].values[0]
if public_alert == 0:
internal_alert = ''
hyphen = ''
else:
hyphen = '-'
else:
pub_internal = public_symbols[public_symbols.alert_level == \
public_alert]['alert_symbol'].values[0]
hyphen = '-'
internal_alert = pub_internal + hyphen + internal_alert
elif -1 in internal_df[
internal_df.trigger_source != 'rainfall']['alert_level'].values:
ground_alert = -1
else:
ground_alert = 0
if has_unresolved_moms:
ground_alert = -1
# PUBLIC ALERT
# check if end of validity: lower alert if with data and not rain75
is_within_alert_extension = False
if public_alert > 0:
is_release_time_run = end.time() in [
time(3, 30),
time(7, 30),
time(11, 30),
time(15, 30),
time(19, 30),
time(23, 30)
]
is_45_minute_beyond = int(start_time.strftime('%M')) > 45
is_not_yet_write_time = not (is_release_time_run
and is_45_minute_beyond)
is_within_alert_extension = validity + timedelta(3) > end + timedelta(
hours=0.5)
has_no_ground_data = ground_alert == -1
# check if end of validity: lower alert if with data and not rain75
if validity > end + timedelta(hours=0.5):
pass
elif rain75_id in internal_df['trigger_sym_id'].values \
or is_within_alert_extension and has_no_ground_data \
or is_not_yet_write_time:
# or is_not_yet_write_time \
# or is_within_alert_extension and has_unresolved_moms:
validity = release_time(end)
if is_release_time_run:
if not (is_45_minute_beyond):
do_not_write_to_db = True
else:
validity = ''
public_alert = 0
internal_alert = internal_symbols[(internal_symbols.alert_level == \
ground_alert) & (internal_symbols.source_id == \
internal_id)]['alert_symbol'].values[0]
else:
validity = ''
public_alert = 0
has_no_ground_data = ground_alert == -1
internal_alert = internal_symbols[(internal_symbols.alert_level == \
ground_alert) & (internal_symbols.source_id == \
internal_id)]['alert_symbol'].values[0]
# start of event
if monitoring_type != 'event' and len(pos_trig) != 0:
ts_onset = min(pos_trig['ts'].values)
ts_onset = pd.to_datetime(ts_onset)
# most recent retrigger of positive operational triggers
try:
#last positive retriggger/s
triggers = last_pos_trig[[
'trigger_id', 'alert_symbol', 'ts_updated', 'trigger_sym_id',
'source_id', 'alert_level'
]]
triggers = triggers.rename(columns = {'alert_symbol': 'alert', \
'ts_updated': 'ts'})
triggers['ts'] = triggers['ts'].apply(lambda x: str(x))
except:
triggers = pd.DataFrame(columns=[
'trigger_id', 'alert', 'ts', 'trigger_sym_id', 'source_id',
'alert_level'
])
#technical info for bulletin release
try:
#tech_info = pd.DataFrame(columns=['subsurface', 'surficial', 'rainfall', \
# 'earthquake', 'on demand'])
pos_trig = pd.merge(pos_trig, internal_symbols, on='trigger_sym_id')
tech_info = tech_info_maker.main(pos_trig)
except:
tech_info = pd.DataFrame()
try:
ts = max(op_trig[op_trig.alert_level != -1]['ts_updated'].values)
ts = round_data_ts(pd.to_datetime(ts))
except:
ts = end
if ts > end or (int(start_time.strftime('%M')) >= 45 \
or int(start_time.strftime('%M')) >= 15
and int(start_time.strftime('%M')) < 30) and ts != end:
ts = end
ts = str(ts)
validity = str(validity)
public_df = pd.DataFrame({
'ts': [ts],
'site_id': [site_id],
'site_code': [site_code],
'public_alert': [public_alert],
'internal_alert': [internal_alert],
'validity': [validity],
'subsurface': [subsurface],
'surficial': [surficial],
'rainfall': [rainfall],
'moms': [moms_alert],
'triggers': [triggers],
'tech_info': [tech_info],
'has_no_ground_data': [has_no_ground_data],
'is_within_alert_extension': [is_within_alert_extension],
'has_unresolved_moms': [has_unresolved_moms]
})
# writes public alert to database
pub_sym_id = public_symbols[public_symbols.alert_level == \
public_alert]['pub_sym_id'].values[0]
site_public_df = pd.DataFrame({'ts': [end], 'site_id': [site_id], \
'pub_sym_id': [pub_sym_id], 'ts_updated': [end]})
# onset trigger
try:
site_public_df['ts'] = round_data_ts(ts_onset)
except:
pass
try:
do_not_write_to_db
except:
print("")
qdb.alert_to_db(site_public_df, 'public_alerts')
return public_df