def get_tsm_list(tsm_name='', end=datetime.now()):
if tsm_name == '':
try:
query = "SELECT site_id, logger_id, tsm_id, tsm_name, number_of_segments, segment_length, date_activated"
query += " FROM senslopedb.tsm_sensors WHERE (date_deactivated > '%s' OR date_deactivated IS NULL)" % end
df = db.df_read(query)
df = df.sort_values(['logger_id', 'date_activated'],
ascending=[True, False])
df = df.drop_duplicates('logger_id')
# make a sensor list of loggerArray class functions
TSMdf = df.groupby('logger_id', as_index=False)
sensors = TSMdf.apply(logger_array_list)
return sensors
except:
raise ValueError('Could not get sensor list from database')
else:
try:
query = "SELECT site_id, logger_id, tsm_id, tsm_name, number_of_segments, segment_length, date_activated"
query += " FROM senslopedb.tsm_sensors WHERE (date_deactivated > '%s' OR date_deactivated IS NULL)" % end
query += " AND tsm_name = '%s'" % tsm_name
df = db.df_read(query)
df = df.sort_values(['logger_id', 'date_activated'],
ascending=[True, False])
df = df.drop_duplicates('logger_id')
# make a sensor list of loggerArray class functions
TSMdf = df.groupby('logger_id', as_index=False)
sensors = TSMdf.apply(logger_array_list)
return sensors
except:
raise ValueError('Could not get sensor list from database')
def get_recipient(curr_release, unsent=True):
query = "SELECT * FROM monshiftsched "
query += "WHERE ts < '{}' ".format(curr_release)
query += "ORDER BY ts DESC LIMIT 1"
IOMP = db.df_read(query, connection='analysis')
query = "SELECT * FROM users "
query += "WHERE first_name = 'Community' "
if unsent:
query += "OR (user_id IN (select user_fk_id user_id from user_accounts) "
query += " AND nickname in {}) ".format(
tuple(IOMP.loc[:, ['iompmt', 'iompct']].values[0]))
users = db.df_read(query, connection='common')
if len(users) == 1:
user_id_list = '(' + str(users.user_id.values[0]) + ')'
else:
user_id_list = tuple(users.user_id)
query = "SELECT mobile_id, gsm_id, status FROM "
query += " (SELECT * from user_mobiles "
query += " WHERE user_id IN {}) um".format(user_id_list)
query += "INNER JOIN mobile_numbers USING (mobile_id)"
user_mobiles = db.df_read(query, connection='gsm_pi')
return user_mobiles.loc[user_mobiles.status == 1, ['mobile_id', 'gsm_id']]
def trending_alert_gen(pos_alert, tsm_id, end):
if qdb.does_table_exist('node_alerts') == False:
#Create a node_alerts table if it doesn't exist yet
create_node_alerts()
query = "SELECT EXISTS(SELECT * FROM node_alerts"
query += " WHERE ts = '%s'" %end
query += " and tsm_id = %s and node_id = %s)" %(tsm_id, pos_alert['node_id'].values[0])
if db.df_read(query, connection='local').values[0][0] == 0:
node_alert = pos_alert[['disp_alert', 'vel_alert']]
node_alert['ts'] = end
node_alert['tsm_id'] = tsm_id
node_alert['node_id'] = pos_alert['node_id'].values[0]
data_table = sms.DataTable('node_alerts', node_alert)
db.df_write(data_table, connection='local')
query = "SELECT * FROM node_alerts WHERE tsm_id = %s and node_id = %s and ts >= '%s'" %(tsm_id, pos_alert['node_id'].values[0], end-timedelta(hours=3))
node_alert = db.df_read(query, connection='local')
node_alert['node_alert'] = np.where(node_alert['vel_alert'].values >= node_alert['disp_alert'].values,
#node alert takes the higher perceive risk between vel alert and disp alert
node_alert['vel_alert'].values,
node_alert['disp_alert'].values)
if len(node_alert[node_alert.node_alert > 0]) > 3:
trending_alert = pd.DataFrame({'node_id': [pos_alert['node_id'].values[0]], 'TNL': [max(node_alert['node_alert'].values)]})
else:
trending_alert = pd.DataFrame({'node_id': [pos_alert['node_id'].values[0]], 'TNL': [0]})
return trending_alert
def get_operational_trigger(site_id, start_monitor, end):
"""Dataframe containing alert level on each operational trigger
from start of monitoring.
Args:
site_id (dataframe): ID each site.
start_monitor (datetime): Timestamp of start of monitoring.
end (datetime): Public alert timestamp.
Returns:
dataframe: Contains timestamp range of alert, three-letter site code,
operational trigger, alert level, and alert symbol from
start of monitoring
"""
query = "SELECT op.trigger_id, op.trigger_sym_id, ts, site_id, source_id, alert_level, "
query += "alert_symbol, ts_updated FROM"
query += " (SELECT * FROM operational_triggers "
query += " WHERE site_id = %s" %site_id
query += " AND ts_updated >= '%s' AND ts <= '%s' "%(start_monitor, end)
query += " ) AS op "
query += "INNER JOIN "
query += " operational_trigger_symbols AS sym "
query += "USING (trigger_sym_id) "
query += "ORDER BY ts DESC"
op_trigger = db.df_read(query, connection='analysis')
query = "SELECT * FROM alert_status WHERE trigger_id >= {trigger_id} "
query += "AND alert_status = -1"
query = query.format(trigger_id = min(op_trigger.trigger_id))
trigger_id = db.df_read(query, connection='analysis')['trigger_id'].values
op_trigger = op_trigger.loc[~op_trigger.trigger_id.isin(trigger_id), :]
return op_trigger
def get_soms_raw(tsm_name="",
from_time="",
to_time="",
type_num="",
node_id="",
connection='analysis'):
if not tsm_name:
raise ValueError('invalid tsm_name')
query_accel = "SELECT version FROM tsm_sensors where tsm_name = '%s'" % tsm_name
df_accel = db.df_read(query_accel, connection=connection)
query = "select * from soms_%s" % tsm_name
if not from_time:
from_time = "2010-01-01"
query += " where ts > '%s'" % from_time
if to_time:
query += " and ts < '%s'" % to_time
if node_id:
query += " and node_id = '%s'" % node_id
if type_num:
query += " and type_num = '%s'" % type_num
df = db.df_read(query, connection=connection)
df.ts = pd.to_datetime(df.ts)
if ((df_accel.version[0] == 2) and (type_num == 111)):
if (tsm_name == 'nagsa'):
df.loc[:, 'mval1-n'] = (((8000000 / (df.mval1)) -
(8000000 / (df.mval2))) * 4) / 10
else:
df.loc[:, 'mval1-n'] = (((20000000 / (df.mval1)) -
(20000000 / (df.mval2))) * 4) / 10
df = df.drop('mval1', axis=1, inplace=False)
df = df.drop('mval2', axis=1, inplace=False)
df.loc[:, 'mval1'] = df['mval1-n']
df = df.drop('mval1-n', axis=1, inplace=False)
#df = df.replace("-inf", "NAN")
# df = df.drop('mval2', axis=1, inplace=False)
return df
def main():
query = 'SELECT * FROM markers'
markers = db.df_read(query)
query = "SELECT * FROM marker_observations"
mo = db.df_read(query)
query = "SELECT * FROM marker_data"
md = db.df_read(query)
query = "SELECT ma_id, ts, marker_id FROM marker_alerts"
ma = db.df_read(query)
marker_alerts = pd.merge(ma, markers, on='marker_id', validate='m:1')
marker_alerts = pd.merge(marker_alerts, mo, on=['site_id', 'ts'],
validate='m:1')
marker_alerts = pd.merge(marker_alerts, md, on=['mo_id', 'marker_id'],
validate='m:1')
marker_alerts = marker_alerts.drop_duplicates(['ts', 'marker_id'],
keep='last')
# delete marker_alerts not in marker_observations and duplicated marker_alerts
ma_id = set(ma['ma_id']) - set(marker_alerts['ma_id'])
if len(ma_id) != 0:
query = 'DELETE FROM marker_alerts WHERE ma_id in %s' %str(tuple(ma_id))
qdb.execute_query(query)
try:
query = 'ALTER TABLE marker_alerts ADD UNIQUE INDEX uq_marker_alerts (marker_id ASC, ts ASC)'
qdb.execute_query(query)
except:
pass
try:
query = "ALTER TABLE marker_alerts "
query += "ADD UNIQUE INDEX uq_marker_alerts1 (data_id ASC); "
qdb.execute_query(query)
except:
pass
try:
query = "ALTER TABLE marker_alerts "
query += "ADD CONSTRAINT fk_marker_data "
query += " FOREIGN KEY (data_id) "
query += " REFERENCES marker_data (data_id) "
query += " ON DELETE CASCADE "
query += " ON UPDATE CASCADE; "
qdb.execute_query(query)
except:
pass
data_table = sms.DataTable('marker_alerts',
marker_alerts[['ts', 'marker_id', 'data_id']])
db.df_write(data_table)
def update_memcache():
#memcached
memc = memcache.Client(['127.0.0.1:11211'], debug=1)
query_tsm = ("SELECT tsm_id, tsm_name, date_deactivated,"
" number_of_segments, version"
" FROM senslopedb.tsm_sensors")
query_accel = ("SELECT accel_id, voltage_min, voltage_max"
" FROM senslopedb.accelerometers")
memc.set('tsm', db.df_read(query_tsm))
memc.set('accel', db.df_read(query_accel))
print_out("Updated memcached with MySQL data")
def main(time_now=datetime.now()):
conn = mem.get('DICT_DB_CONNECTIONS')
query = "select site_code, ts, marker_name from "
query += " (select data_id from {analysis}.marker_data_tags "
query += " where tag_type = 0 "
query += " ) tag "
query += "inner join (select data_id, alert_level from {analysis}.marker_alerts) sub1 using (data_id) "
query += "inner join {analysis}.marker_data using (data_id) "
query += "inner join {analysis}.marker_observations mo using (mo_id) "
query += "inner join {common}.sites using (site_id) "
query += "inner join (select marker_id, marker_name from {analysis}.view_marker_history) sub2 using (marker_id)"
query += "where alert_level = 0 "
query += "and mo.ts >= '{ts}' "
query = query.format(analysis=conn['analysis']['schema'],
common=conn['common']['schema'],
ts=time_now - timedelta(1.5))
tags = db.df_read(query, resource='sensor_analysis')
tags.loc[:, 'ts'] = tags.loc[:, 'ts'].astype(str)
if len(tags) != 0:
msg = 'Validate measurements with displacement of 1cm and more:\n'
msg += '\n'.join(list(map(lambda x: ': '.join(x), tags.values)))
msg += '\n\nEdit data tag info for confirmed movement or unreliable measurement.'
msg += '\n\nFor repositioned markers, add event to marker history: reposition event with ts of marker observation above. Adding reposition event will also delete the validating data tag'
else:
msg = ''
return msg
def get_surficial_trigger(start_ts, end_ts, resource='sensor_analysis'):
conn = mem.get('DICT_DB_CONNECTIONS')
query = "SELECT trigger_id, ts, site_id, alert_status, ts_updated, "
query += "trigger_sym_id, alert_symbol, alert_level, site_code FROM "
query += " (SELECT * FROM {}.operational_triggers ".format(
conn['analysis']['schema'])
query += " WHERE ts >= '{}' ".format(start_ts)
query += " AND ts_updated <= '{}' ".format(end_ts)
query += " ) AS trig "
query += "INNER JOIN "
query += " (SELECT * FROM {}.operational_trigger_symbols ".format(
conn['analysis']['schema'])
query += " WHERE alert_level > 0 "
query += " ) AS sym "
query += "USING (trigger_sym_id) "
query += "INNER JOIN "
query += " (SELECT * FROM {}.trigger_hierarchies ".format(
conn['analysis']['schema'])
query += " WHERE trigger_source = 'surficial' "
query += " ) AS hier "
query += "USING (source_id) "
query += "INNER JOIN {}.alert_status USING (trigger_id) ".format(
conn['analysis']['schema'])
query += "INNER JOIN {}.sites USING (site_id) ".format(
conn['common']['schema'])
query += "ORDER BY ts DESC "
df = db.df_read(query, resource=resource)
return df
def get_raw_rain_data(rain_id,
gauge_name,
from_time='2010-01-01',
to_time="",
connection='analysis'):
"""Retrieves rain gauge data from the database.
Args:
gauge_name (str): Name of rain gauge to collect data from.
from_time (str): Start of data to be collected.
to_time (str): End of data to be collected. Optional.
Returns:
dataframe: Rainfall data of gauge_name from from_time [to to_time].
"""
query = "SELECT ts, rain FROM {} ".format(gauge_name)
query += "WHERE ts > '{}'".format(from_time)
if to_time:
query += "AND ts < '{}'".format(to_time)
query += "ORDER BY ts"
df = db.df_read(query, connection=connection)
if df is not None:
df.loc[:, 'ts'] = pd.to_datetime(df['ts'])
else:
df = pd.DataFrame(columns=['ts', 'rain'])
return df
def get_rain_tag(rain_id, from_time, to_time, connection='analysis'):
"""Retrieves faulty rain gauge tag from the database.
Args:
rain_id (str): ID of rain gauge.
from_time (str): Start of data tag.
to_time (str): End of data tag.
Returns:
dataframe: Rainfall data tag of rain_id from from_time to to_time.
"""
if to_time == '':
to_time = datetime.now()
query = "select * from rainfall_data_tags "
query += "where rain_id = {} ".format(rain_id)
query += "and ts_start <= '{}' ".format(to_time)
query += "and (ts_end is null or ts_end >= '{}')".format(from_time)
df = db.df_read(query, connection=connection)
if df is not None:
df.loc[df.ts_end.isnull(),
'ts_end'] = df.loc[df.ts_end.isnull(), 'ts_start'].apply(
lambda x: pd.to_datetime(x) + timedelta(1))
else:
df = pd.DataFrame()
return df
def site_alerts(curr_trig, ts, release_data_ts, connection):
df = curr_trig.drop_duplicates(
['site_id', 'trigger_source', 'alert_level'])
site_id = df['site_id'].values[0]
query = "SELECT trigger_id, MAX(ts_last_retrigger) ts_last_retrigger FROM alert_status"
query += " WHERE trigger_id IN (%s)" %(','.join(map(lambda x: str(x), \
set(df['trigger_id'].values))))
query += " GROUP BY trigger_id"
written = db.df_read(query, connection=connection)
site_curr_trig = pd.merge(df, written, how='left')
site_curr_trig = site_curr_trig.loc[
(site_curr_trig.ts_last_retrigger +
timedelta(1) < site_curr_trig.ts_updated) |
(site_curr_trig.ts_last_retrigger.isnull()), :]
if len(site_curr_trig) == 0:
qdb.print_out('no new trigger for site_id %s' % site_id)
return
alert_status = site_curr_trig[['ts_updated', 'trigger_id']]
alert_status = alert_status.rename(
columns={'ts_updated': 'ts_last_retrigger'})
alert_status['ts_set'] = datetime.now()
data_table = sms.DataTable('alert_status', alert_status)
db.df_write(data_table, connection=connection)
def get_IOMP():
shift_ts = release_time(dt.now())
query = """SELECT * FROM monshiftsched
WHERE ts = '{}'""".format(shift_ts)
df = dbio.df_read(query)
return list(df[['iompmt', 'iompct']].to_records(index=False)[0])
def get_web_releases(start, end, events):
query = "SELECT * FROM public_alert_release"
query += " WHERE data_timestamp BETWEEN '%s'AND '%s'" % (start, end)
query += " ORDER BY release_id"
releases = dbio.df_read(query)
releases = releases[releases.event_id.isin(events.event_id)]
releases['release_time'] = releases['release_time'].apply(lambda x: \
pd.to_datetime(str(x)[-8:]).time())
releases['release_timestamp'] = releases['data_timestamp'].apply(lambda x: \
x.date())
releases['release_timestamp'] = releases.apply(lambda x: \
pd.datetime.combine(x['release_timestamp'], x['release_time']), 1)
mn_releases = releases[
releases.data_timestamp > releases.release_timestamp]
mn_releases[
'release_timestamp'] = mn_releases['release_timestamp'] + timedelta(1)
releases = releases[releases.data_timestamp <= releases.release_timestamp]
releases = releases.append(mn_releases)
releases = releases.sort_values('release_id', ascending=False)
releases['target_release'] = releases['data_timestamp'] + timedelta(
hours=0.5)
return releases
def get_rain_sent(start, end, mysql=True, to_csv=False):
if mysql:
query = "SELECT ts_written, ts_sent, mobile_id, sms_msg, tag_id FROM "
query += " (SELECT outbox_id, ts_written, ts_sent, mobile_id, sms_msg FROM "
query += " {gsm_pi}.smsoutbox_users "
query += " INNER JOIN "
query += " {gsm_pi}.smsoutbox_user_status "
query += " USING (outbox_id) "
query += " ) AS msg "
query += "LEFT JOIN "
query += " (SELECT outbox_id, tag_id FROM {gsm_pi}.smsoutbox_user_tags "
query += " WHERE ts BETWEEN '{start}' AND '{end}' "
query += " AND tag_id = 21 "
query += " ORDER BY outbox_id DESC LIMIT 5000 "
query += " ) user_tags "
query += "USING (outbox_id) "
query += "WHERE sms_msg REGEXP 'Rainfall info' "
query += "AND ts_written BETWEEN '{start}' AND '{end}'"
query = query.format(start=start,
end=end,
common=conn['common']['schema'],
gsm_pi=conn['gsm_pi']['schema'])
df = db.df_read(query, resource='sms_analysis')
df.loc[:, 'sms_msg'] = df.sms_msg.str.lower().str.replace(
'city', '').str.replace('.', '')
if to_csv:
df.to_csv(output_path + '/input_output/sent.csv', index=False)
else:
df = pd.read_csv(output_path + '/input_output/sent.csv')
return df
def get_web_releases(start, end, mysql=True, to_csv=False):
if mysql:
query = "SELECT site_code, data_ts, release_time "
query += "FROM commons_db.sites "
query += "INNER JOIN ewi_db.monitoring_events USING (site_id) "
query += "INNER JOIN ewi_db.monitoring_event_alerts USING (event_id) "
query += "LEFT JOIN ewi_db.monitoring_releases USING (event_alert_id)"
query += "WHERE data_ts BETWEEN '{start}' AND '{end}' "
query += "ORDER BY site_code, data_ts desc"
query = query.format(start=start, end=end)
df = db.df_read(query=query, resource="ops")
if to_csv:
df.to_csv(output_path + 'webreleases.csv', index=False)
else:
df = pd.read_csv(output_path + 'webreleases.csv')
df.loc[:, 'data_ts'] = pd.to_datetime(df.data_ts)
df.loc[:, 'ts_release'] = df.loc[:, ['data_ts', 'release_time']].apply(
lambda row: pd.to_datetime(
str(row.data_ts.date()) + ' ' + str(row.release_time).replace(
'0 days ', '')),
axis=1)
df.loc[df.data_ts > df.ts_release,
'ts_release'] = df.loc[df.data_ts > df.ts_release,
'ts_release'] - timedelta(1)
return df
def get_rain_df(rain_gauge, start, end):
offsetstart = start - timedelta(3)
query = "SELECT * FROM rainfall_gauges WHERE gauge_name = '{}'".format(
rain_gauge.replace('rain_', ''))
df = db.df_read(query, connection='analysis')
rain_id = df.rain_id[0]
rain_df = ra.get_resampled_data(rain_id,
rain_gauge,
offsetstart,
start,
end,
check_nd=False)
rain_df = rain_df[rain_df.rain >= 0]
rain_df = rain_df.resample("30min").asfreq()
rain_df['one'] = rain_df.rain.rolling(window=48,
min_periods=1,
center=False).sum()
rain_df['one'] = np.round(rain_df.one, 2)
rain_df['three'] = rain_df.rain.rolling(window=144,
min_periods=1,
center=False).sum()
rain_df['three'] = np.round(rain_df.three, 2)
rain_df = rain_df[(rain_df.index >= start) & (rain_df.index <= end)]
rain_df = rain_df.reset_index()
return rain_df
def get_bulletin_recipients(mysql=True, to_csv=False):
if mysql:
query = "SELECT fullname, site_id, email FROM "
query += " {common}.user_emails "
query += " LEFT JOIN "
query += " (select user_id, CONCAT(first_name, ' ', last_name) AS fullname, status AS user_status, ewi_recipient from {common}.users) users "
query += " USING (user_id) "
query += "LEFT JOIN "
query += " (SELECT user_id, site_id, site_code, org_name, primary_contact FROM "
query += " {common}.user_organizations "
query += " INNER JOIN "
query += " {common}.sites "
query += " USING (site_id) "
query += " ) AS site_org "
query += "USING (user_id) "
query += "LEFT JOIN {gsm_pi}.user_ewi_restrictions USING (user_id) "
query += "where user_id not in (SELECT user_fk_id user_id FROM {common}.user_accounts) "
query += "and site_code is not null and org_name='phivolcs'"
query += "and ewi_recipient = 1 and user_status = 1 "
query += "order by site_id, fullname"
query = query.format(common=conn['common']['schema'],
gsm_pi=conn['gsm_pi']['schema'])
df = db.df_read(query, resource='sms_analysis')
if to_csv:
df.to_csv(output_path + '/input_output/ewi_recipient.csv',
index=False)
else:
df = pd.read_csv(output_path + '/input_output/ewi_recipient.csv')
return df
def get_alert_level(site_id, end):
"""Retrieves alert level.
Args:
tsm_id (int): ID of site to retrieve alert level from.
end (bool): Timestamp of alert level to be retrieved.
Returns:
dataframe: Dataframe containing alert_level.
"""
query = "SELECT alert_level FROM "
query += " (SELECT * FROM public_alerts "
query += " WHERE site_id = %s " % site_id
query += " AND ts <= '%s' " % end
query += " AND ts_updated >= '%s' " % (end - timedelta(hours=0.5))
query += " ) AS a "
query += "INNER JOIN "
query += " (SELECT pub_sym_id, alert_level FROM public_alert_symbols "
query += " ) AS s "
query += "USING(pub_sym_id)"
df = db.df_read(query)
return df
def get_trigger_sym_id(alert_level):
""" Gets the corresponding trigger sym id given the alert level.
Parameters
--------------
alert_level: int
surficial alert level
Returns
---------------
trigger_sym_id: int
generated from operational_trigger_symbols table
"""
#### query the translation table from operational_trigger_symbols table and trigger_hierarchies table
query = "SELECT trigger_sym_id, alert_level FROM "
query += " operational_trigger_symbols AS op "
query += "INNER JOIN "
query += " (SELECT source_id FROM trigger_hierarchies "
query += " WHERE trigger_source = 'surficial' "
query += " ) AS trig "
query += "USING(source_id)"
translation_table = db.df_read(query).set_index(
'alert_level').to_dict()['trigger_sym_id']
return translation_table[alert_level]
def get_raw_rain_data(gauge_name, from_time='2010-01-01', to_time=""):
"""Retrieves rain gauge data from the database.
Args:
gauge_name (str): Name of rain gauge to collect data from.
from_time (str): Start of data to be collected.
to_time (str): End of data to be collected. Optional.
Returns:
dataframe: Rainfall data of gauge_name from from_time [to to_time].
"""
query = "SELECT ts, rain FROM %s " % gauge_name
query += "WHERE ts > '%s'" % from_time
if to_time:
query += "AND ts < '%s'" % to_time
query += "ORDER BY ts"
df = db.df_read(query)
if df is not None:
df.loc[:, 'ts'] = pd.to_datetime(df['ts'])
else:
df = pd.DataFrame(columns=['ts', 'rain'])
return df
def get_smsoutbox(start, end):
query = "SELECT outbox_id, ts_written, ts_sent, site_code, org_name, "
query += "fullname, sim_num, send_status, sms_msg FROM "
query += " (SELECT outbox_id, ts_written, ts_sent, sim_num, "
query += " CONCAT(firstname, ' ', lastname) AS fullname, sms_msg, "
query += " send_status, user_id FROM "
query += " (select * FROM comms_db.smsoutbox_users "
query += " WHERE sms_msg regexp 'ang alert level' "
query += " ) AS outbox "
query += " INNER JOIN "
query += " (SELECT * FROM comms_db.smsoutbox_user_status "
query += " WHERE send_status >= 5 "
query += " AND ts_sent BETWEEN '%s' AND '%s' " % (start, end)
query += " ) AS stat "
query += " USING (outbox_id) "
query += " INNER JOIN "
query += " comms_db.user_mobile "
query += " USING (mobile_id) "
query += " INNER JOIN "
query += " comms_db.users "
query += " USING (user_id) "
query += " ) AS msg "
query += "INNER JOIN "
query += " (SELECT user_id, site_code, org_name FROM "
query += " (SELECT * FROM comms_db.user_organization "
query += " WHERE org_name in ('lewc', 'blgu', 'mlgu', 'plgu', 'pdrrmc') "
query += " ) AS org "
query += " INNER JOIN "
query += " sites "
query += " ON sites.site_id = org.fk_site_id "
query += " ) AS site_org "
query += "USING (user_id) "
query += "GROUP BY site_code, org_name, sms_msg "
query += "ORDER BY outbox_id DESC"
smsoutbox = dbio.df_read(query)
smsoutbox = smsoutbox[smsoutbox.sms_msg.str.contains('ngayong')]
smsoutbox['sms_msg'] = smsoutbox.apply(lambda row: row['sms_msg'].replace(
'(current_date)',
pd.to_datetime(row['ts_written']).strftime('%B %d, %Y')),
axis=1)
format_index = smsoutbox[smsoutbox.sms_msg.str.contains(
'\(current_date_time\)')].index
for index in format_index:
smsoutbox_row = smsoutbox[smsoutbox.index == index]
ts_date = pd.to_datetime(smsoutbox_row['ts_written'].values[0]).date()
text = smsoutbox_row['sms_msg'].values[0]
sub_text = re.findall('(?=[APMN][MN])\w+', text)[-1]
ts_time = (pd.to_datetime(
text[re.search('(?=mamayang)\w+', text).end() +
1:re.search('(?=%s)\w+' % sub_text, text).end()].replace(
'MN', 'AM').replace('NN', 'PM')) -
timedelta(hours=4)).time()
ts = pd.datetime.combine(ts_date,
ts_time).strftime('%B %d, %Y %I:%M %p')
replaced_text = text.replace('(current_date_time)', ts)
smsoutbox.loc[smsoutbox.index == index, 'sms_msg'] = replaced_text
return smsoutbox
def get_sites():
query = ("SELECT site_id, site_code, loggers.latitude, loggers.longitude, "
"province FROM loggers left join sites using (site_id) "
"where logger_name not like '%%g'")
print(query)
df = dynadb.df_read(query=query, resource="common_data")
df = df.drop_duplicates('site_id', keep='first').dropna()
return df
def get_latest_ts(table_name):
try:
query = "SELECT max(ts) FROM %s" % table_name
ts = db.df_read(query).values[0][0]
return pd.to_datetime(ts)
except:
print_out("Error in getting maximum timestamp")
return ''
def get_sms_sent(start, end, site_names, mysql=True, to_csv=False):
if mysql:
query = "SELECT outbox_id, ts_written, ts_sent, site_id, user_id, mobile_id, sms_msg FROM "
query += " (SELECT outbox_id, ts_written, ts_sent, mobile_id, sim_num, "
query += " CONCAT(first_name, ' ', last_name) AS fullname, sms_msg, "
query += " send_status, user_id FROM "
query += " {gsm_pi}.smsoutbox_users "
query += " INNER JOIN "
query += " (SELECT * FROM {gsm_pi}.smsoutbox_user_status "
# pisd trial messages for training
query += " WHERE stat_id NOT IN (1072245,1072246,1067662,1065358,1064091) "
query += " ) AS sms_stat "
query += " USING (outbox_id) "
query += " INNER JOIN "
query += " (SELECT * FROM "
query += " {gsm_pi}.user_mobiles "
query += " INNER JOIN "
query += " {gsm_pi}.mobile_numbers "
query += " USING (mobile_id) "
query += " ) mobile "
query += " USING (mobile_id) "
query += " INNER JOIN "
query += " {common}.users "
query += " USING (user_id) "
query += " ) as msg "
query += "LEFT JOIN "
query += " (SELECT * FROM "
query += " {common}.user_organizations AS org "
query += " INNER JOIN "
query += " {common}.sites "
query += " USING (site_id) "
query += " ) AS site_org "
query += "USING (user_id) "
query += "WHERE sms_msg regexp 'ang alert level' "
query += "AND ts_written between '{start}' and '{end}' "
query += "AND user_id NOT IN (31, 631, 948, 976) "
query = query.format(start=start,
end=end,
common=conn['common']['schema'],
gsm_pi=conn['gsm_pi']['schema'])
df = db.df_read(query, resource='sms_analysis')
df.loc[:, 'sms_msg'] = df.sms_msg.str.lower().str.replace(
'city', '').str.replace('.', '')
df = pd.merge(df,
site_names.loc[:, ['site_id', 'name']],
on='site_id',
how='left')
df = df.loc[~df.name.isnull(), :]
if len(df) != 0:
df = df.loc[df.apply(lambda row: len(
re.findall(row['name'], row.sms_msg)) != 0,
axis=1), :]
if to_csv:
df.to_csv(output_path + '/input_output/sent.csv', index=False)
else:
df = pd.read_csv(output_path + '/input_output/sent.csv')
return df
def system_downtime(mysql=False):
if mysql:
query = 'SELECT * FROM system_down WHERE reported = 1'
df = db.df_read(query=query, resource="sensor_data")
df.to_csv(output_path + 'downtime.csv', index=False)
else:
df = pd.read_csv(output_path + 'downtime.csv')
df.loc[:, ['start_ts', 'end_ts']] = df.loc[:, ['start_ts', 'end_ts']].apply(pd.to_datetime)
return df
def event_start(site_id, end):
"""Timestamp of start of event monitoring. Start of event is computed
by checking if event progresses from non A0 to higher alert.
Args:
site_id (int): ID of each site.
end (datetime): Current public alert timestamp.
Returns:
datetime: Timestamp of start of monitoring.
"""
query = "SELECT ts, ts_updated FROM "
query += " (SELECT * FROM public_alerts "
query += " WHERE site_id = %s " %site_id
query += " AND (ts_updated <= '%s' " %end
query += " OR (ts_updated >= '%s' " %end
query += " AND ts <= '%s')) " %end
query += " ) AS pub "
query += "INNER JOIN "
query += " (SELECT * FROM public_alert_symbols "
query += " WHERE alert_type = 'event') AS sym "
query += "USING (pub_sym_id) "
query += "ORDER BY ts DESC LIMIT 3"
# previous positive alert
prev_pub_alerts = db.df_read(query, connection='website')
if len(prev_pub_alerts) == 1:
start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[0])
# two previous positive alert
elif len(prev_pub_alerts) == 2:
# one event with two previous positive alert
if pd.to_datetime(prev_pub_alerts['ts'].values[0]) - \
pd.to_datetime(prev_pub_alerts['ts_updated'].values[1]) <= \
timedelta(hours=0.5):
start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[1])
else:
start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[0])
# three previous positive alert
else:
if pd.to_datetime(prev_pub_alerts['ts'].values[0]) - \
pd.to_datetime(prev_pub_alerts['ts_updated'].values[1]) <= \
timedelta(hours=0.5):
# one event with three previous positive alert
if pd.to_datetime(prev_pub_alerts['ts'].values[1]) - \
pd.to_datetime(prev_pub_alerts['ts_updated'].values[2]) \
<= timedelta(hours=0.5):
start_monitor = pd.to_datetime(prev_pub_alerts['timestamp']\
.values[2])
# one event with two previous positive alert
else:
start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[1])
else:
start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[0])
return start_monitor
def get_loggers_v3():
localdf = 0
query = """select logger_name, logger_id from loggers
inner join logger_models using (model_id)
where logger_type in ('gateway','arq')
and logger_name like '%%___r_%%'
or logger_name like '%%___g%%'
and logger_name not in ('madg','bulg','phig', 'bgbg','mycg','nvcg')"""
localdf = db.df_read(query, connection='common')
return localdf
def get_valid_cotriggers(site_id, public_ts_start, connection='analysis'):
query = "SELECT alert_level FROM operational_triggers "
query += "INNER JOIN operational_trigger_symbols USING (trigger_sym_id) "
query += "INNER JOIN alert_status USING (trigger_id) "
query += "WHERE ts = '{}' ".format(public_ts_start)
query += "AND site_id = {} ".format(site_id)
query += "AND alert_status in (0,1) "
query += "ORDER BY ts DESC "
df = db.df_read(query, connection=connection)
return df
def get_data(lgrname):
query = "SELECT max(ts) FROM " + 'tilt_' + lgrname + " where ts > '2010-01-01' and ts < '2023-01-01' order by ts desc limit 1 "
localdf = db.df_read(query, connection='analysis')
if (localdf is None):
localdf = pd.DataFrame(columns=["max(ts)"])
if (localdf.empty == False):
return localdf
else:
localdf = pd.DataFrame(columns=["max(ts)"])
return localdf