def main(): print("Time: %s" % (datetime.datetime.now().strftime("%X"))) try: server_id = mem.get("server_config")["gsmio"]["server_id"] except KeyError: raise ValueError(">> Unable to deteremine server_id from memory") DF_GSM_MODULES = mem.get("df_gsm_modules") df_gsm_modules_in_server = DF_GSM_MODULES[DF_GSM_MODULES["gsm_server_id"] == server_id] gsm_id_list = list(df_gsm_modules_in_server["gsm_id"]) SCREEN_PATH = "/usr/bin/screen" PYTHON_PATH = "/usr/bin/python" SERVER_PATH = "/home/pi/centralserver/gsm/gsmserver.py" process_list = [] for gsm_id in gsm_id_list: string_to_match_in_grep = "gsmserver.py -g%d" % gsm_id command_to_run = "%s -S g%d -d -m %s %s -g%d" % ( SCREEN_PATH, gsm_id, PYTHON_PATH, SERVER_PATH, gsm_id) proc = GsmProcess(string_to_match_in_grep, command_to_run) process_list.append(proc) for proc in process_list: proc_count = count_processes(proc) if proc_count <= 3: print("Execute: %s" % (proc.cmd_str)) execute_cmd(proc.cmd_str, False) else: print("Script ok:", proc.pattern_str)
def main(): """Writes in rainfall_gauges information on available rain gauges for rainfall alert analysis """ start = datetime.now() qdb.print_out(start) if qdb.does_table_exist('rainfall_gauges') == False: #Create a rainfall_gauges table if it doesn't exist yet qdb.create_rainfall_gauges() senslope = mem.get('df_dyna_rain_gauges') senslope = senslope.loc[senslope.has_rain == 1, :] senslope.loc[:, 'data_source'] = 'senslope' noah = noah_gauges() all_gauges = senslope.append(noah, sort=False) all_gauges.loc[:, 'gauge_name'] = all_gauges.loc[:, 'gauge_name'].apply( lambda x: str(x)) all_gauges.loc[:, 'date_activated'] = pd.to_datetime( all_gauges.loc[:, 'date_activated']) written_gauges = mem.get('df_rain_gauges') not_written = set(all_gauges['gauge_name']) \ - set(written_gauges['gauge_name']) new_gauges = all_gauges.loc[all_gauges.gauge_name.isin(not_written), :] new_gauges = new_gauges.loc[new_gauges.date_deactivated.isnull(), :] new_gauges = new_gauges.loc[:, [ 'gauge_name', 'data_source', 'longitude', 'latitude', 'date_activated' ]] if len(new_gauges) != 0: data_table = sms.DataTable('rainfall_gauges', new_gauges) db.df_write(data_table) deactivated = written_gauges.loc[ ~written_gauges.date_deactivated.isnull(), :] deactivated_gauges = all_gauges.loc[(~all_gauges.date_deactivated.isnull()) \ & (~all_gauges.gauge_name.isin(not_written))\ & (~all_gauges.gauge_name.isin(deactivated.gauge_name)), :] date_deactivated = pd.to_datetime( deactivated_gauges.loc[:, 'date_deactivated']) deactivated_gauges.loc[:, 'date_deactivated'] = date_deactivated deactivated_gauges = deactivated_gauges.loc[:, [ 'gauge_name', 'data_source', 'longitude', 'latitude', 'date_activated' ]] if len(deactivated_gauges) != 0: data_table = sms.DataTable('rainfall_gauges', deactivated_gauges) db.df_write(data_table) qdb.print_out('runtime = %s' % (datetime.now() - start))
def main(): tsm_details = memory.get('DF_TSM_SENSORS') accelerometers = memory.get('DF_ACCELEROMETERS') dfa = accelerometers.merge(tsm_details, how='inner', on='tsm_id') dfa = dfa[dfa.date_deactivated.isnull()] #dfa=dfa[dfa.accel_id>=1240] for i in dfa.accel_id: try: drift_detection(acc_id=i) print(i) except TypeError: pass
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_site_names(): sites = mem.get('df_sites') special = ['hin', 'mca', 'msl', 'msu'] sites.loc[~sites.site_code.isin(special), 'name'] = sites.loc[~sites.site_code.isin(special), ['barangay', 'municipality']].apply(lambda row: ', '.join(row.values).lower().replace('city', '').replace('.', '').strip(), axis=1) sites.loc[sites.site_code.isin(special[0:2]), 'name'] = sites.loc[sites.site_code.isin(special[0:2]), 'municipality'].apply(lambda x: x.lower()) sites.loc[sites.site_code.isin(special[2:4]), 'name'] = sites.loc[sites.site_code.isin(special[2:4]), 'sitio'].apply(lambda x: x.lower().replace(' ', ' | ')) return sites
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 earthquake(site_id, ts): """Insert values to earthquake_events, earthquake_alerts, and operational_triggers to (re)trigger subsurface alert. Args: site_id (int): ID of site to compute earthquake analysis for. ts (datetime): Timestamp of alert trigger. """ # writes to earthquake_events; defaults epicenter to site coordinates, depth to 0, and magnitude to 4.3 sites = eq.get_sites() earthquake_events = sites.loc[sites.site_id == site_id, ['latitude', 'longitude', 'province']] earthquake_events.loc[:, 'ts'] = ts earthquake_events.loc[:, 'magnitude'] = 4.3 earthquake_events.loc[:, 'depth'] = 0 earthquake_events.loc[:, 'critical_distance'] = np.round(eq.get_crit_dist(4.3), decimals=2) earthquake_events.loc[:, 'issuer'] = 'TOPSSOFTWAREINFRA' earthquake_events.loc[:, 'processed'] = 1 eq_id = int(db.df_write(data_table = sms.DataTable("earthquake_events", earthquake_events), resource='sensor_data', last_insert=True)[0][0]) # writes to earthquake_alerts earthquake_alerts = pd.DataFrame({'eq_id': [eq_id], 'site_id': [site_id], 'distance': [0]}) db.df_write(data_table = sms.DataTable("earthquake_alerts", earthquake_alerts), resource='sensor_data') # writes to operational_triggers trigger_symbol = mem.get('df_trigger_symbols') trigger_sym_id = trigger_symbol.loc[(trigger_symbol.trigger_source == 'earthquake') & (trigger_symbol.alert_level == 1), 'trigger_sym_id'].values[0] operational_trigger = pd.DataFrame({'site_id': [site_id], 'trigger_sym_id': [trigger_sym_id], 'ts': [ts], 'ts_updated': [ts]}) qdb.alert_to_db(operational_trigger, 'operational_triggers') # details for trigger tech info earthquake_events.loc[:, 'distance'] = 0 return earthquake_events
def check_name_of_number(number): """ - The process of querying the mobile number to check the cellphone number logger name. :param number: Cellphone number. :type number: int Returns: str: Query output for success and return False if fails. Example Output:: >>> x = check_name_of_number('639173082161') agbta """ conn = mem.get('DICT_DB_CONNECTIONS') query = ("select logger_name from {}.loggers where " "logger_id = (select logger_id from {}.logger_mobile " "where sim_num like '%{}' order by date_activated desc limit 1)". format(conn['common']['schema'], conn['gsm_pi']['schema'], number)) query = dynadb.read(query, 'check_name_of_number', resource='sms_analysis') if len(query) != 0: return query[0][0] else: return
def get_site_code(text): """ - The processing of getting data of site code. :param text: Sms line of message for soms . :type text: str Returns: list: List data output for success parsing and it break if fails. """ err_val = 0 site_id = 0 site_code_match = re.split(" ", text, maxsplit=1)[0].lower()[0:3] df_sites = mem.get('df_sites') site_code = adjust_site_code(site_code_match) try: site_id = df_sites.loc[df_sites.site_code == site_code.lower(), 'site_id'].values[0] except IndexError: print("No site_code record for %s" % (site_code)) err_val = SURFICIAL_PARSER_ERROR_VALUE["site_code"] return { "value": { 'site_id': site_id, 'site_code': site_code }, "match": str(site_code_match), "err_val": err_val }
def get_name_of_staff(number): conn = mem.get('DICT_DB_CONNECTIONS') query = "select user_id, nickname, gsm_id from {}.users ".format(conn['common']['schema']) query += "inner join {}.user_mobiles using (user_id) ".format(conn['gsm_pi']['schema']) query += "inner join {}.mobile_numbers using (mobile_id) ".format(conn['gsm_pi']['schema']) query += "where sim_num = '%s'" % (number) return dbio.read(query=query, resource="sms_data")[0]
def rainfall(site_id, ts, rain_id, rain_alert='both'): """Insert values to rainfall_alerts and operational_triggers tables to (re)trigger rainfall alert Args: site_id (int): ID of site to compute rainfall analysis for. ts (datetime): Timestamp of alert trigger. rain_id (int): ID of rain gauge to use as data source. rain_alert (str: {'a', 'b', 'x', None}, default None): Type of rainfall alert. a: 1-day cumulative rainfall threshold exceeded b: 3-day cumulative rainfall threshold exceeded x: None: both 1-day and 3-day cumulative rainfall threshold exceeded """ # 4 nearest rain gauges of each site with threshold and distance from site gauges = rain.rainfall_gauges() df = gauges.loc[(gauges.site_id == site_id) & (gauges.rain_id == rain_id), ['site_id', 'rain_id', 'threshold_value']] df = df.rename(columns={'threshold_value': 'threshold'}) df.loc[:, 'ts'] = ts # rainfall cumulative based on alert level if str(rain_alert) == '0': df.loc[:, 'rain_alert'] = 0 df.loc[:, 'cumulative'] = 0 else: if rain_alert != 'x': df.loc[:, 'rain_alert'] = 'b' df.loc[:, 'cumulative'] = 1.2 * df.loc[:, 'threshold'] else: df.loc[:, 'rain_alert'] = 'x' df.loc[:, 'cumulative'] = 0.80 * df.loc[:, 'threshold'] if rain_alert == 'a' or rain_alert == 'both': dfa = df.copy() dfa.loc[:, ['cumulative', 'threshold']] = dfa.loc[:, ['cumulative', 'threshold']].div(2) dfa.loc[:, 'rain_alert'] = 'a' if rain_alert == 'a': df = dfa.copy() else: df = df.append(dfa, ignore_index=True) qdb.write_rain_alert(df) # writes to operational_triggers trigger_symbol = mem.get('df_trigger_symbols') if str(rain_alert) in ['0', 'x']: alert_level = 0 else: alert_level = 1 trigger_sym_id = trigger_symbol.loc[(trigger_symbol.trigger_source == 'rainfall') & (trigger_symbol.alert_level == alert_level), 'trigger_sym_id'].values[0] operational_trigger = pd.DataFrame({'site_id': [site_id], 'trigger_sym_id': [trigger_sym_id], 'ts': [ts], 'ts_updated': [ts]}) qdb.alert_to_db(operational_trigger, 'operational_triggers') return df
def all_site_coord(): """Retrieves coordinates of sites from memcache Returns: dataframe: Record of coordinates of sites. """ df = mem.get('df_dyna_rain_gauges')[['site_id', 'latitude', 'longitude']] df = df.dropna() df = df.drop_duplicates('site_id') df = df.sort_values('site_id') return df
def get_event_releases(start, end, mysql=True, to_csv=False): """Gets events overlapping with timestamp range given. Args: start (datetime): Start of timestamp range. end (datetime): End of timestamp range. mysql (bool): Gets data from mysql if True, else gets data from csv. Optional. Default: False. drop (bool): Retains most recent event release if True. Optional. Default: True. Returns: DataFrame: Events overlapping with timestamp range given. Columns: site_id, site_code, event_id, validity, pub_sym_id, alert_symbol """ if mysql: start = start-timedelta(3) end = end+timedelta(3) conn = mem.get('DICT_DB_CONNECTIONS') query = "select site_id, site_code, event_id, validity, pub_sym_id, alert_symbol, data_ts, release_time " query += "from {common}.sites " query += "inner join {website}.monitoring_events using(site_id) " query += "left join {website}.monitoring_event_alerts using(event_id) " query += "left join " query += " (SELECT * FROM {website}.monitoring_releases " query += " left join {website}.monitoring_triggers using(release_id) " query += " left join {website}.internal_alert_symbols using(internal_sym_id) " query += " ) as trig " query += "using(event_alert_id) " query += "where ((ts_start >= '{start}' and ts_start <= '{end}') " query += "or (validity >= '{start}' and validity <= '{end}') " query += "or (ts_start <= '{start}' and validity >= '{end}')) " query += "and pub_sym_id != 1 " query += "and active = 1 " query += "order by event_id, data_ts" query = query.format(start=start, end=end, common=conn['common']['schema'], website=conn['website']['schema']) df = db.df_read(query, resource='ops') if len(df) != 0: df.loc[:, 'data_ts'] = df.data_ts.apply(lambda x: round_data_ts(pd.to_datetime(x))) df_grp = df.groupby('event_id', as_index=False) df = df_grp.apply(event_with_eq, mysql=mysql).reset_index(drop=True) df_grp = df.groupby(['event_id', 'pub_sym_id'], as_index=False) df = df_grp.apply(event_with_moms, mysql=mysql).reset_index(drop=True) if to_csv: df.to_csv(output_path+'/input_output/event_releases.csv', index=False) else: df = pd.read_csv(output_path+'/input_output/event_releases.csv') return df
def to_mysql(df): """Writes in rainfall_priorities the distance of 4 active nearby rain gauges from the site. Args: df (dataframe): Record of 4 nearby rain gauges with its distance from the site. """ written_df = mem.get('df_rain_priorities').loc[:, ['priority_id', 'site_id', 'rain_id']] combined = pd.merge(df, written_df, on=['site_id', 'rain_id'], how='left') if len(combined) > 0: qdb.write_rain_priorities(combined)
def main(site_code=''): """Writes in rainfall_priorities information on nearest rain gauges from the project sites for rainfall alert analysis """ start = datetime.now() qdb.print_out(start) coord = mem.get('df_sites') if site_code == '': try: site_code = sys.argv[1].lower() site_code = site_code.replace(' ', '').split(',') except: pass else: site_code = site_code.replace(' ', '').split(',') if site_code != '': coord = coord.loc[coord.site_code.isin(site_code), :] coord = coord.loc[coord.active == 1, ['site_id', 'latitude', 'longitude']] rg_coord = mem.get('df_rain_gauges') rg_coord = rg_coord[rg_coord.date_deactivated.isnull()] site_coord = coord.groupby('site_id', as_index=False) nearest_rg = site_coord.apply(get_distance, rg_coord=rg_coord) nearest_rg['distance'] = np.round(nearest_rg.distance,2) nearest_rg = nearest_rg.reset_index(drop=True) if qdb.does_table_exist('rainfall_priorities') == False: #Create a NOAH table if it doesn't exist yet qdb.create_rainfall_priorities() to_mysql(nearest_rg) qdb.print_out('runtime = %s' %(datetime.now() - start))
def main(start, end): conn = mem.get('DICT_DB_CONNECTIONS') query = "SELECT ts_sms, site_code FROM " query += " (SELECT inbox_id, ts_sms, user_id FROM " query += " (SELECT * FROM {gsm_pi}.smsinbox_users " query += " WHERE ts_sms BETWEEN '{start}' AND '{end}' " query += " ) sms " query += " INNER JOIN " query += " {gsm_pi}.user_mobiles " query += " USING (mobile_id) " query += " INNER JOIN " query += " {common}.users " query += " USING (user_id) " query += " ) AS msg " query += "INNER JOIN " query += " (SELECT user_id, site_code, org_name FROM " query += " {common}.user_organizations " query += " INNER JOIN " query += " {common}.sites " query += " USING (site_id) " query += " ) AS site_org " query += "USING (user_id) " query += "INNER JOIN " query += " (SELECT * FROM " query += " {gsm_pi}.smsinbox_user_tags " query += " INNER JOIN " query += " (SELECT tag_id, tag FROM {gsm_pi}.sms_tags " query += " WHERE tag = '#EwiResponse') ack " query += " USING (tag_id) " query += " ) AS tags " query += "USING (inbox_id) " query += "ORDER BY inbox_id DESC " query = query.format(start=start, end=end, common=conn['common']['schema'], gsm_pi=conn['gsm_pi']['schema']) ACK = db.df_read(query, resource='sms_analysis') ACK.loc[:, 'day'] = ACK.loc[:, 'ts_sms'].map(lambda x: pd.to_datetime(x).isocalendar()[2]) ACK.loc[:, 'hour'] = ACK.ts_sms.dt.hour site_hour_ACK = ACK[['ts_sms', 'hour', 'site_code']].groupby(['hour', 'site_code']).count().reset_index() hour_ACK = ACK[['ts_sms', 'hour']].groupby(['hour']).count().reset_index() site_day_ACK = ACK[['ts_sms', 'day', 'site_code']].groupby(['day', 'site_code']).count().reset_index() site_day_ACK = site_day_ACK.sort_values('day') site_day_ACK.loc[:, 'day'] = site_day_ACK.day.map({1: 'M', 2: 'T', 3: 'W', 4: 'Th', 5: 'F', 6: 'Sa', 7: 'Su'}) day_ACK = ACK[['ts_sms', 'day']].groupby(['day']).count().reset_index() day_ACK = day_ACK.sort_values('day') day_ACK.loc[:, 'day'] = day_ACK.day.map({1: 'M', 2: 'T', 3: 'W', 4: 'Th', 5: 'F', 6: 'Sa', 7: 'Su'}) return site_hour_ACK, hour_ACK, site_day_ACK, day_ACK
def get_sites_no_markers(mysql=True, to_csv=False): if mysql: conn = mem.get('DICT_DB_CONNECTIONS') query = "select site_id, site_code from {common}.sites " query += "where site_id not in " query += " (select site_id from {analysis}.site_markers " query += " where in_use = 1) " query += "and active = 1" query = query.format(common=conn['common']['schema'], analysis=conn['analysis']['schema']) df = db.df_read(query, resource='sensor_analysis') df = df.loc[df.site_code != 'msu', :] if to_csv: df.to_csv(output_path+'/input_output/site_no_marker.csv', index=False) else: df = pd.read_csv(output_path+'/input_output/site_no_marker.csv') return df
def get_gndmeas(start, end, mysql=True, to_csv=False): if mysql: conn = mem.get('DICT_DB_CONNECTIONS') query = "select site_code, ts from " query += " (select * from {analysis}.marker_observations " query += " where ts between '{start}' and '{end}' " query += " ) obs " query += "inner join {common}.sites using (site_id) " query = query.format(start=start, end=end, common=conn['common']['schema'], analysis=conn['analysis']['schema']) df = db.df_read(query, resource='sensor_analysis') if to_csv: df.to_csv(output_path+'gndmeas.csv', index=False) else: df = pd.read_csv(output_path+'gndmeas.csv') return df
def main(): """Updates data of NOAH rain gauges. """ start_time = datetime.now() qdb.print_out(start_time) #get the list of rainfall NOAH rain gauge IDs gauges = mem.get('df_rain_props') gauges = gauges[gauges.data_source == 'noah'].drop_duplicates('rain_id') noah_gauges = gauges.groupby('rain_id') noah_gauges.apply(update_single_table) qdb.print_out('runtime = %s' % (datetime.now() - start_time))
def to_mysql(df): """Writes in rainfall_priorities the distance of 4 active nearby rain gauges from the site. Args: df (dataframe): Record of 4 nearby rain gauges with its distance from the site. """ written_df = mem.get('df_rain_priorities') combined = written_df.append(df, ignore_index=True, sort=False) combined = combined.append(written_df, ignore_index=True, sort=False) combined = combined.drop_duplicates(['site_id', 'rain_id'], keep=False) if len(combined) > 0: data_table = sms.DataTable('rainfall_priorities', combined) db.df_write(data_table)
def get_gsm_modules(reset_val=False): """ - The process of getting the gsm modules information. :param reset_val: Trigger value to check the gsm information and **Default** to **False** :type table: boolean, default False Returns: obj: Gsm info output for success processing. Example Output:: {1: {'network': 'globe', 'module': 0, 'port': 'dummy', 'num': '639171234567', 'pwr_on_pin': 1, 'ring_pin': 0, 'id': 1, 'name': 'simulate'}, 2: {'network': 'globe', 'module': 1, 'port': None, 'num': '639176321023', 'pwr_on_pin': 0, 'ring_pin': 0, 'id': 2, 'name': 'globe'}, 3: {'network': 'smart', 'module': 1, 'port': None, 'num': '639988448687', 'pwr_on_pin': 0, 'ring_pin': 0, 'id': 3, 'name': 'smart'}, 4: {'network': 'globe', 'module': 2, 'port': '/dev/globeport', 'num': '639175388301', 'pwr_on_pin': 11, 'ring_pin': 15, 'id': 4, 'name': 'globe1'}, 5: {'network': 'smart', 'module': 2, 'port': '/dev/smartport', 'num': '639088125640', 'pwr_on_pin': 31, 'ring_pin': 33, 'id': 5, 'name': 'smart1'}, 6: {'network': 'globe', 'module': 2, 'port': '/dev/smartport', 'num': '639175963573', 'pwr_on_pin': 31, 'ring_pin': 33, 'id': 6, 'name': 'globe2'}, 7: {'network': 'smart', 'module': 2, 'port': '/dev/ttyUSB1', 'num': '639088125638', 'pwr_on_pin': 29, 'ring_pin': 33, 'id': 7, 'name': 'smart1'}} """ try: DF_GSM_MODULES = mem.get("DF_GSM_MODULES") except AttributeError: raise AttributeError("No DF_GSM_MODULES in memory. Initialize first.") # backwards compatibility DF_GSM_MODULES.rename(columns={ "network_type": "network", "gsm_name": "name", "gsm_sim_num": "num", "ser_port": "port", "gsm_id": "id", "module_type": "module" }, inplace=True) gsm_modules = DF_GSM_MODULES.to_dict(orient='index') return gsm_modules
def get_rain_recipients(mysql=True, to_csv=False): if mysql: conn = mem.get('DICT_DB_CONNECTIONS') query = "SELECT mobile_id, sim_num, user_id, rain_site_id, rain_site_code, fullname, province, all_sites, mm_values, percentage, date_start, date_end FROM " query += "{common}.rain_info_recipients " query += " LEFT JOIN " query += "(SELECT user_id, CONCAT(first_name, ' ', last_name) AS fullname, status AS user_status, ewi_recipient " query += "FROM {common}.users " query += ") users " query += " USING (user_id) " query += " LEFT JOIN " query += "{gsm_pi}.user_mobiles " query += " USING (user_id) " query += " LEFT JOIN " query += "{gsm_pi}.mobile_numbers " query += " USING (mobile_id) " query += " LEFT JOIN " query += "(SELECT user_id, site_id AS rain_site_id, site_code AS rain_site_code, province 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 " query += "{gsm_pi}.user_ewi_restrictions " query += " USING (user_id) " query += "WHERE user_id NOT IN ( " query += " SELECT user_fk_id user_id " query += " FROM {common}.user_accounts) " query += "AND ewi_recipient = 1 " query += "AND user_status = 1 " query += "AND status = 1 " query += "ORDER BY fullname, sim_num" 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 subsurface(site_id, ts, alert_level): """Insert values to node_alerts, tsm_alerts, and operational_triggers to (re)trigger subsurface alert. Args: site_id (int): ID of site to compute subsurface analysis for. ts (datetime): Timestamp of alert trigger. alert_level (int: {0, 2, 3}, default None): Subsurface alert level. """ # get tsm_id query = "SELECT tsm_id FROM tsm_sensors " query += "where site_id = {} ".format(site_id) query += "and (date_deactivated is null or date_deactivated > '{}')".format(ts) tsm_id = db.df_read(query, resource='sensor_data').values.flatten() tsm_id = random.choice(tsm_id) # writes to node_alerts; defaults to node 1 and vel_alert ts_list = pd.date_range(end=ts, freq='30min', periods=4) node_alerts = pd.DataFrame({'ts': ts_list, 'node_id': [1]*len(ts_list), 'tsm_id': [tsm_id]*len(ts_list), 'disp_alert': [0]*len(ts_list), 'vel_alert': [alert_level]*len(ts_list)}) db.df_write(data_table = sms.DataTable("node_alerts", node_alerts), resource='sensor_data') # writes to tsm_alerts tsm_alerts = pd.DataFrame({'ts': [ts], 'tsm_id': [tsm_id], 'alert_level': [alert_level], 'ts_updated': [ts]}) db.df_write(data_table = sms.DataTable("tsm_alerts", tsm_alerts), resource='sensor_data') # writes to operational_triggers trigger_symbol = mem.get('df_trigger_symbols') trigger_sym_id = trigger_symbol.loc[(trigger_symbol.trigger_source == 'subsurface') & (trigger_symbol.alert_level == alert_level), 'trigger_sym_id'].values[0] operational_trigger = pd.DataFrame({'site_id': [site_id], 'trigger_sym_id': [trigger_sym_id], 'ts': [ts], 'ts_updated': [ts]}) qdb.alert_to_db(operational_trigger, 'operational_triggers') # details for trigger tech info tsm_alerts.loc[:, 'node_id'] = 1 tsm_alerts.loc[:, 'disp_alert'] = 0 tsm_alerts.loc[:, 'vel_alert'] = alert_level return tsm_alerts
def get_mobile(nickname_list=''): conn = mem.get('DICT_DB_CONNECTIONS') query = "SELECT mobile_id, nickname, gsm_id FROM " query += " (SELECT user_id, nickname FROM {}.users ".format(conn['common']['schema']) query += " WHERE status = 1) u " query += "INNER JOIN " query += " (SELECT user_id, team_code FROM " query += " {}.user_team_members ".format(conn['common']['schema']) query += " INNER JOIN {}.user_teams ".format(conn['common']['schema']) query += " USING (team_id) " query += " WHERE team_code IN ('admin', 'CT', 'MT') " query += " ) team " query += "USING (user_id) " query += "INNER JOIN {}.user_mobiles ".format(conn['gsm_pi']['schema']) query += "USING (user_id) " query += "INNER JOIN {}.mobile_numbers ".format(conn['gsm_pi']['schema']) query += "USING (mobile_id)" df = db.df_read(query, resource='sms_analysis') if nickname_list != '': df = df.loc[df.nickname.isin(nickname_list)] return df
def rainfall_gauges(end=datetime.now()): """Check top 4 rain gauges to be used in rainfall analysis. Args: end (datetime): Timestamp of alert and plot to be computed. Optional. Defaults to current timestamp. Returns: dataframe: Top 4 rain gauges per site. """ gauges = mem.get('df_rain_props') gauges['gauge_name'] = np.array(','.join(gauges.data_source).replace('noah', 'rain_noah_').replace('senslope', 'rain_').split(','))+gauges.gauge_name gauges = gauges.sort_values('distance') gauges = gauges.groupby('site_id').head(4) gauges = gauges.sort_values(['site_id', 'distance']) return gauges
def logger_response(sms,log_type,log='False'): """ - The process of logging the id of the match expression on table logger_respose. :param sms: list data info of sms message . :param Log_type: list data info of sms message . :param Log: Switch on or off the logging of the response. :type sms: list :type sms: str :type sms: str, Default(False) """ conn = mem.get('DICT_DB_CONNECTIONS') if log: query = ("INSERT INTO %s.logger_response (`logger_Id`, `inbox_id`, `log_type`)" "values((Select logger_id from %s.logger_mobile where sim_num = %s order by" " date_activated desc limit 1),'%s','%s')" % (conn['analysis']['schema'],conn['common']['schema'],sms.sim_num,sms.inbox_id,log_type)) dbio.write(query, resource="sensor_analysis") print ('>> Log response') else: return False
def check_alerts(): conn = mem.get('DICT_DB_CONNECTIONS') ts_now = dt.now().strftime("%Y-%m-%d %H:%M:%S") common = conn['common']['schema'] analysis = conn['analysis']['schema'] query = ("SELECT stat_id, site_id, site_code, trigger_source, " "alert_symbol, ts_last_retrigger FROM " "(SELECT stat_id, ts_last_retrigger, site_id, " "trigger_source, alert_symbol FROM " "(SELECT stat_id, ts_last_retrigger, site_id, " "trigger_sym_id FROM " "(SELECT * FROM {}.alert_status " "WHERE ts_set < '{}' " "and ts_ack is NULL " ") AS stat " "INNER JOIN " "{}.operational_triggers AS op " "USING (trigger_id) " ") AS trig " "INNER JOIN " "(SELECT trigger_sym_id, trigger_source, " "alert_level, alert_symbol FROM " "{}.operational_trigger_symbols " "INNER JOIN " "{}.trigger_hierarchies " "USING (source_id) " ") as sym " "USING (trigger_sym_id)) AS alert " "INNER JOIN " "(SELECT * FROM {}.sites WHERE active = 1) s " "USING (site_id)").format(analysis,ts_now,analysis,analysis,analysis,common) alert_msgs = dbio.read(query=query, resource="sensor_analysis") print("alert messages:", alert_msgs) return alert_msgs
from datetime import timedelta import os import pandas as pd import re import sys sys.path.append(os.path.dirname(os.path.realpath(__file__))) import dynadb.db as db import volatile.memory as mem output_path = os.path.abspath(os.path.join(os.path.dirname(__file__), '..')) conn = mem.get('DICT_DB_CONNECTIONS') def get_sms_recipients(mysql=True, to_csv=False): if mysql: query = "SELECT mobile_id, sim_num, user_id, fullname, site_id, org_name, alert_level, start_ewi_recipient FROM " query += " {gsm_pi}.mobile_numbers " query += " LEFT JOIN " query += " {gsm_pi}.user_mobiles " query += " USING (mobile_id) " query += " LEFT JOIN " query += " (select user_id, CONCAT(first_name, ' ', last_name) AS fullname, status AS user_status, ewi_recipient, start_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 "
from scipy.signal import gaussian import matplotlib.pyplot as plt import numpy as np import os import pandas as pd import sys import time as mytime #### Import local codes sys.path.append(os.path.dirname(os.path.realpath(__file__))) import analysis.analysislib as lib import analysis.querydb as qdb import volatile.memory as mem #### Open config files sc = mem.get('server_config') #### Create directory output_path = os.path.abspath( os.path.join(os.path.dirname(__file__), '../../..')) def gaussian_weighted_average(series, sigma=3, width=39): """ Computes for rolling weighted average and variance using a gaussian signal filter. Parameters --------------- series: Array Series to be averaged sigma: Float
def main(site_code='', end='', Print=True, write_to_db=True, print_plot=False, save_plot=True, days='', is_command_line_run=True): """Computes alert and plots rainfall data. Args: site_code (list): Site codes to compute rainfall analysis for. Optional. Defaults to empty string which will compute alert and plot for all sites. Print (bool): To print plot and summary of alerts. Optional. Defaults to True. end (datetime): Timestamp of alert and plot to be computed. Optional. Defaults to current timestamp. Returns: str: Json format of cumulative rainfall and alert per site. """ start_time = datetime.now() qdb.print_out(start_time) if site_code == '': if is_command_line_run: site_code = sys.argv[1].lower() site_code = site_code.replace(' ', '').split(',') else: site_code = site_code.replace(' ', '').split(',') if end == '': try: end = pd.to_datetime(sys.argv[2]) except: end = datetime.now() else: end = pd.to_datetime(end) output_path = os.path.abspath(os.path.join(os.path.dirname(__file__), '../../..')) sc = mem.server_config() #creates directory if it doesn't exist if (sc['rainfall']['print_plot'] or sc['rainfall']['print_summary_alert']) and Print: if not os.path.exists(output_path+sc['fileio']['rainfall_path']): os.makedirs(output_path+sc['fileio']['rainfall_path']) # setting monitoring window if days != '': sc['rainfall']['rt_window_length'] = days end, start, offsetstart = get_rt_window(float(sc['rainfall']['rt_window_length']), float(sc['rainfall']['roll_window_length']), end=end) tsn=end.strftime("%Y-%m-%d_%H-%M-%S") # 4 nearest rain gauges of each site with threshold and distance from site gauges = rainfall_gauges() if site_code != '': gauges = gauges[gauges.site_code.isin(site_code)] gauges['site_id'] = gauges['site_id'].apply(lambda x: float(x)) trigger_symbol = mem.get('df_trigger_symbols') trigger_symbol = trigger_symbol[trigger_symbol.trigger_source == 'rainfall'] trigger_symbol['trigger_sym_id'] = trigger_symbol['trigger_sym_id'].apply(lambda x: float(x)) site_props = gauges.groupby('site_id') summary = site_props.apply(ra.main, end=end, sc=sc, trigger_symbol=trigger_symbol, write_to_db=write_to_db) summary = summary.reset_index(drop=True)[['site_id', 'site_code', '1D cml', 'half of 2yr max', '3D cml', '2yr max', 'DataSource', 'alert']] if Print == True: if sc['rainfall']['print_summary_alert']: summary.to_csv(output_path+sc['fileio']['rainfall_path'] + 'SummaryOfRainfallAlertGenerationFor'+tsn+'.csv', sep=',', mode='w', index=False) if sc['rainfall']['print_plot'] or print_plot: rain_data = site_props.apply(rp.main, offsetstart=offsetstart, tsn=tsn, save_plot=save_plot, sc=sc, start=start, output_path=output_path, end=end).reset_index(drop=True) summary = pd.merge(summary, rain_data, on='site_id', validate='1:1') summary_json = summary.to_json(orient="records") qdb.print_out("runtime = %s" %(datetime.now()-start_time)) return summary_json