Exemplo n.º 1
0
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')
Exemplo n.º 2
0
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']]
Exemplo n.º 3
0
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
Exemplo n.º 4
0
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
Exemplo n.º 5
0
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
Exemplo n.º 6
0
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)
Exemplo n.º 7
0
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")
Exemplo n.º 8
0
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
Exemplo n.º 9
0
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
Exemplo n.º 10
0
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
Exemplo n.º 11
0
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
Exemplo n.º 12
0
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)
Exemplo n.º 13
0
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])
Exemplo n.º 14
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
Exemplo n.º 15
0
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
Exemplo n.º 16
0
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
Exemplo n.º 17
0
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
Exemplo n.º 18
0
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
Exemplo n.º 19
0
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
Exemplo n.º 20
0
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]
Exemplo n.º 21
0
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
Exemplo n.º 22
0
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
Exemplo n.º 23
0
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
Exemplo n.º 24
0
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 ''
Exemplo n.º 25
0
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
Exemplo n.º 26
0
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
Exemplo n.º 27
0
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
Exemplo n.º 29
0
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
Exemplo n.º 30
0
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