def get_alert_staff_numbers(): query = ( "select t1.user_id,t2.sim_num,t2.gsm_id from user_alert_info t1 inner join" " user_mobile t2 on t1.user_id = t2.user_id where t1.send_alert = 1;") dev_contacts = dbio.read(query=query, resource="sms_data") ts = dt.today().strftime("%Y-%m-%d %H:%M:%S") query = ("select iompmt, iompct from monshiftsched where " "ts < '{}' order by ts desc limit 1;".format(ts)) try: iomp_nicknames_tuple = dbio.read(query=query, resource="sensor_data")[0] except IndexError: print(">> Error in getting IOMP nicknames") print(">> No alert message will be sent to IOMPs") query = ("select t1.user_id, t2.sim_num, t2.gsm_id from users t1 " "inner join user_mobile t2 on t1.user_id = t2.user_id " "where t1.nickname in {}".format(iomp_nicknames_tuple)) iomp_contacts = dbio.read(query=query, resource="sms_data") return dev_contacts + iomp_contacts
def set_static_variable(name=""): """ - Description. Args: Args (str): Args. Returns: Returns. Raises: MySQLdb.OperationalError: Error in database connection. """ query = ("Select name, query, data_type, ts_updated, resource from " "static_variables") date = dt.now() date = date.strftime('%Y-%m-%d %H:%M:%S') if name != "": query += " where name = '%s'" % (name) try: variables = dbio.read(query=query, resource='common_data') except MySQLdb.ProgrammingError: print(">> static_variables table does not exist on host") return if not variables: print("Error getting static variable information") return False for data in variables: variable_info = VariableInfo(data) if variable_info.type == 'data_frame': static_output = dbio.df_read(query=variable_info.query, resource=variable_info.resource) elif variable_info.type == 'dict': static_output = dict_format(variable_info.query, variable_info) else: static_output = dbio.read(query=variable_info.query, resource=variable_info.resource) if static_output is None: warnings.warn('Query error: ' + variable_info.name, stacklevel=2) else: memory.set(variable_info.name, static_output) query_ts_update = ("UPDATE static_variables SET " " ts_updated ='%s' WHERE name ='%s'") % ( date, variable_info.name) dbio.write(query=query_ts_update, resource='common_data') print(variable_info.name, "success")
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 """ 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(number)) query = dynadb.read(query, 'check_name_of_number') if len(query) != 0: return query[0][0] else: return
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 check_alerts(): ts_now = dt.now().strftime("%Y-%m-%d %H:%M:%S") 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 < '%s' " "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 " "sites " "USING (site_id)") % (ts_now) alert_msgs = dbio.read(query=query, resource="sensor_data") print("alert messages:", alert_msgs) return alert_msgs
def send_monitoringshift_reminder(): tomorrow = dt.now() + td(days=1) tomorrow = tomorrow.strftime("%Y-%m-%d") query = "SELECT ts, iompmt, iompct, comms_db.users.user_id, sim_num, gsm_id FROM " \ "senslopedb.monshiftsched INNER JOIN " \ "comms_db.users ON monshiftsched.iompmt = comms_db.users.nickname " \ "OR monshiftsched.iompct = comms_db.users.nickname " \ "INNER JOIN comms_db.user_mobile " \ "ON comms_db.users.user_id = comms_db.user_mobile.user_id " \ "WHERE ts LIKE '%"+tomorrow+"%'" message = "Monitoring shift reminder. Good Afternoon <NICKNAME>, " \ "you are assigned to be the IOMPMT and IOMPCT respectively for <Date Time> <AM/PM>" recipients = dbio.read(query=query, resource="sensor_data") for ts, iompmt, iompct, user_id, sim_num, gsm_id in recipients: temp = message.replace("<NICKNAME>", iompmt + " and " + iompct) temp = temp.replace("<Date Time>", tomorrow) if "20:00:00" in str(ts): temp = temp.replace("<AM/PM>", "7:30 PM") else: temp = temp.replace("<AM/PM>", "7:30 AM") temp = smstables.write_outbox(message=temp, recipients=sim_num, gsm_id=gsm_id, table='users') print(temp)
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_details(site_id, start_monitor, ts_last_retrigger): query = "SELECT gauge_name FROM " query += " (SELECT * FROM rainfall_alerts " query += " WHERE site_id = %s " % site_id query += " AND ts >= '%s' " % start_monitor query += " AND ts <= '%s' " % ts_last_retrigger query += " ) AS alerts " query += "INNER JOIN " query += " rainfall_gauges " query += "USING (rain_id) " data_source_df = pd.DataFrame(list(dbio.read(query=query, resource="sensor_data")), columns=['gauge_name']) data_source = ':' + ','.join(set(data_source_df['gauge_name'])) return data_source
def monitoring_start(site_id, ts_last_retrigger): query = "SELECT ts, ts_updated FROM " query += " (SELECT * FROM public_alerts " query += " WHERE site_id = %s " % site_id query += " AND (ts_updated <= '%s' " % ts_last_retrigger query += " OR (ts_updated >= '%s' " % ts_last_retrigger query += " AND ts <= '%s')) " % ts_last_retrigger query += " ) AS pub " query += "INNER JOIN " query += " (SELECT * FROM public_alert_symbols " query += " WHERE alert_type = 'event') AS sym " query += "ON pub.pub_sym_id = sym.pub_sym_id " query += "ORDER BY ts DESC LIMIT 3" # previous positive alert prev_pub_alerts = pd.DataFrame(list( dbio.read(query=query, resource="sensor_data")), columns=['ts', 'ts_updated']) 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]) <= \ td(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]) <= \ td(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]) \ <= td(hours=0.5): start_monitor = pd.to_datetime(prev_pub_alerts['ts'].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_router_ids(): """ - The process of selecting router id from loggers table :parameter: N/A Returns: obj: list of keys and values from model_id table; """ query = ("SELECT `logger_id`,`logger_name` from `loggers` where `model_id`" " in (SELECT `model_id` FROM `logger_models` where " "`logger_type`='router') and `logger_name` is not null") nums = dbio.read(query, resource="sensor_data") nums = {key: value for (value, key) in nums} return nums
def subsurface_details(site_id, start_monitor, ts_last_retrigger): query = "SELECT node_id, tsm_name FROM " query += " (SELECT * FROM node_alerts " query += " WHERE ts >= '%s' " % (ts_last_retrigger-td(hours=4)) query += " AND ts <= '%s' " % ts_last_retrigger query += " ) AS alerts " query += "INNER JOIN " query += " (SELECT * FROM tsm_sensors " query += " WHERE site_id = '%s' " % site_id query += " ) AS sensors " query += "USING (tsm_id)" data_source_df = pd.DataFrame(list(dbio.read(query=query, resource="sensor_data")), columns=['node', 'tsm_name']) data_source_df = data_source_df.drop_duplicates(['node', 'tsm_name']) tsm_source_df = data_source_df.groupby('tsm_name', as_index=False) data_source = ':'+','.join(tsm_source_df.apply(tsm_details)) return data_source
def ops_checker(conversation_id="", ts=""): message = "" query = "SELECT link from olivia_link where link_id = 3" python_path = db.read(query, connection="gsm_pi")[0][0] file_path = os.path.dirname(__file__) test_groupchat = 'UgwcSTTEx1yRS0DrYVN4AaABAQ' if not conversation_id: conversation_id = test_groupchat if ts: try: # sms_notif = sms.main(pd.to_datetime(ts)) # bulletin_notif = bulletin.main(pd.to_datetime(ts)) # raininfo_notif = raininfo.main() ops_notif = notif.main(pd.to_datetime(ts)) except: message += "**ERROR timestamp**\nMust be in format : YYYY-mm-dd HH:MM\n\n" # sms_notif = sms.main() # bulletin_notif = bulletin.main() # raininfo_notif = raininfo.main() ops_notif = notif.main() else: # sms_notif = sms.main() # bulletin_notif = bulletin.main() # raininfo_notif = raininfo.main() ops_notif = notif.main() # message += sms_notif # message +="\n" # message += bulletin_notif # message +="\n" # message += raininfo_notif message += ops_notif print(message) cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd)
def check_id_in_table(table, gsm_id): """ - The process of selecting data from user or logger mobile with mobile id. :param table: table name of the gsm_id. :param gsm_id: gsm_id of the recipient. :type table: int :type gsm_id: int Returns: obj: recipient number (*int*) """ query = "select * from %s_mobile where mobile_id='%s' limit 80" % ( table[:-1], gsm_id) query_number = db.read(query, 'check id in table') if len(query_number.sim_num) != 0: return query_number[0][0] else: print(" >> gsm id doesn't exist")
def dict_format(query_string, variable_info): """ - Description. Args: Args (str): Args. Returns: Returns. Raises: MySQLdb.OperationalError: Error in database connection. """ query_output = dbio.read(query=query_string, resource=variable_info.resource) if query_output: dict_output = {a: b for a, b in query_output} return dict_output else: return False
def check_number_in_users(num): """ - The process of querying the mobile number to check if the number exists. :param num: Instance hostname. :type num: str Returns: tuple: Query output for success and return False if fails. Example Output:: >>> x = check_number_in_users('639263818956') ((2,),) """ query = "select user_id from user_mobile" query += "where sim_num = '%s'" % (num) query = dynadb.read(query=query, identifier='checkifexists', host='local') if len(query) != 0: return query[0][0] else: return
def surficial_details(site_id, start_monitor, ts_last_retrigger): query = "SELECT marker_name FROM " query += " (SELECT * FROM marker_alerts " query += " WHERE ts >= '%s' " % start_monitor query += " AND ts <= '%s' " % ts_last_retrigger query += " AND alert_level > 0 " query += " ) AS alerts " query += "INNER JOIN " query += " (SELECT marker_id, marker_name FROM " query += " (SELECT hist.marker_id, hist.history_id FROM " query += " (SELECT marker_id, MAX(ts) AS ts " query += " FROM marker_history " query += " WHERE event in ('add', 'rename') " query += " GROUP BY marker_id " query += " ) AS M " query += " INNER JOIN " query += " (SELECT * " query += " FROM marker_history " query += " WHERE event in ('add', 'rename') " query += " ) AS hist " query += " USING (marker_id, ts) " query += " WHERE marker_id IN ( " query += " SELECT marker_id " query += " FROM markers " query += " WHERE site_id = %s) " % site_id query += " ) AS site_hist " query += " INNER JOIN " query += " marker_names " query += " USING(history_id) " query += " ) AS names " query += "USING (marker_id) " data_source_df = pd.DataFrame(list( dbio.read(query=query, resource="sensor_data")), columns=['marker_name']) data_source = ':' + ','.join(set(data_source_df['marker_name'])) return data_source
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
def write_observation(surf_df, site_id): mo_df = surf_df.loc[:, ['site_id', 'ts', 'meas_type', 'observer_name']] mo_df.loc[:, 'data_source'] = 'ops' mo_df.loc[:, 'reliability'] = 1 mo_df.loc[:, 'weather'] = 'maaraw' mo_id = dbio.df_write(data_table=smsclass.DataTable( "marker_observations", mo_df), resource='sensor_data', last_insert=True)[0][0] if mo_id == 0: query = "SELECT marker_observations.mo_id FROM marker_observations " query += "WHERE ts = '{ts}' and site_id = '{site_id}'" query = query.format(ts=surf_df['ts'].values[0], site_id=site_id) mo_id = dbio.read(query, resource='sensor_data')[0][0] surf_df = surf_df.dropna(axis=1) md_df = surf_df.loc[:, surf_df.columns.astype(str).str.isnumeric()].transpose( ) md_df = md_df.reset_index() md_df.columns = ['marker_id', 'measurement'] md_df.loc[:, 'mo_id'] = mo_id dbio.df_write(data_table=smsclass.DataTable("marker_data", md_df), resource='sensor_data') ma.generate_surficial_alert(site_id, ts=mo_df.ts.values[0])
def check_logger_model(logger_name): """ - The process of querying the logger name to check if the logger name exists. :param logger_name: Instance hostname. :type logger_name: str Returns: str: Query output for success and return False if fails. Example Output:: >>> x = check_logger_model('agbta') 6 """ query = ("SELECT model_id FROM loggers where " "logger_name = '%s'") % logger_name query = dynadb.read(query, 'check_logger_model') if len(query) != 0: return query[0][0] else: return
def process_surficial_observation(sms): """ - Process the sms message that fits for surficial observation and save paserse message to database. :param sms: list data info of sms message . :type sms: list Returns: bool: True output for success process and return False if fails. """ mc = mem.get_handle() surf_mark = mc.get("DF_SURFICIAL_MARKERS") reply_msgs = mc.get("surficial_parser_reply_messages") sc = mem.server_config() ct_sim_num = str(sc["surficial"]["ct_sim_num"]) enable_analysis = sc["surficial"]["enable_analysis"] SEND_REPLY_TO_COMMUNITY = sc["surficial"]["send_reply_to_community"] SEND_ACK_TO_CT_PHONE = sc["surficial"]["send_ack_to_ct_phone"] resource = "sensor_data" obv = [] try: obv = parser.surficial.observation(sms.msg) except ValueError as err_val: err_val = int(str(err_val)) mc = mem.get_handle() messages = mc.get("surficial_parser_reply_messages") # print messages.iloc[err_val - 1].internal_msg # print messages.iloc[err_val - 1].external_msg sms_msg_for_operations = "{}\n\n{}".format( messages.iloc[err_val - 1].internal_msg, sms.msg) smstables.write_outbox(sms_msg_for_operations, ct_sim_num) return False site_surf_mark = surf_mark[surf_mark["site_id"] == obv["obv"]["site_id"]] df_meas = pd.DataFrame() df_meas = df_meas.from_dict(obv["markers"]["measurements"], orient='index') df_meas.columns = ["measurement"] markers = site_surf_mark.join(df_meas, on="marker_name", how="outer") # send message for unknown marker names markers_unk = markers[~(markers["marker_id"] > 0)] markers_unk = markers_unk[["marker_name", "measurement"]] markers_unk = markers_unk.set_index(["marker_name"]) markers_unk = markers_unk.to_dict() internal_msg = "DEWSL Beta:\n\n%s\n\n" % (sms.msg) if len(markers_unk["measurement"].keys()) > 0: internal_msg += "%s\n%s\n\n" % (reply_msgs.iloc[13]["internal_msg"], "\n".join(["%s = %s" % (key, value) for (key, value) in \ markers_unk["measurement"].items()])) # send message for unreported marker measurements markers_nd = markers[~(markers["measurement"] > 0)] markers_nd = markers_nd[["marker_name", "measurement"]].to_dict() if len(markers_nd["marker_name"].keys()) > 0: internal_msg += "%s\n%s" % (reply_msgs.iloc[14]["internal_msg"], ", ".join(["%s" % name for name in \ markers_nd["marker_name"].values()])) internal_msg += "\n\n" print(">> Updating observations") df_obv = pd.DataFrame(obv["obv"], index=[0]) mo_id = dbio.df_write(data_table=smsclass.DataTable( "marker_observations", df_obv), resource=resource, last_insert=True) try: mo_id = int(mo_id[0][0]) except (ValueError, TypeError): print( "Error: conversion of measurement observation id during last insert" ) internal_msg += "\n\nERROR: Resultset conversion" smstables.write_outbox(internal_msg, ct_sim_num) return False print(">> Updating marker measurements") if mo_id == 0: # Duplicate entry query = ("SELECT marker_observations.mo_id FROM marker_observations " "WHERE ts = '{}' and site_id = '{}'".format( obv["obv"]['ts'], obv["obv"]['site_id'])) mo_id = dbio.read(query, resource=resource)[0][0] markers_ok = markers[markers["marker_id"] > 0] markers_ok = markers_ok[markers_ok["measurement"] > 0] markers_ok_for_report = markers_ok[["marker_name", "measurement"]] markers_ok = markers_ok[["marker_id", "measurement"]] markers_ok["mo_id"] = mo_id markers_ok.columns = ["%s" % (str(col)) for col in markers_ok.columns] dbio.df_write(data_table=smsclass.DataTable("marker_data", markers_ok), resource=resource) # send success messages markers_ok_for_report = markers_ok_for_report.set_index(["marker_name"]) markers_ok_for_report = markers_ok_for_report.to_dict() updated_measurements_str = "\n".join(["%s = %0.2f CM" % (name, meas) \ for name, meas in markers_ok_for_report["measurement"].items()]) success_msg = "%s\n%s\n%s" % ( reply_msgs.iloc[12]["external_msg"], dt.strptime(obv["obv"]["ts"], "%Y-%m-%d %H:%M:%S").strftime("%c"), updated_measurements_str) internal_msg += "Updated measurements:\n%s" % (updated_measurements_str) # for ct phone c/o iomp-ct if SEND_ACK_TO_CT_PHONE: smstables.write_outbox(internal_msg, ct_sim_num) # for community who sent the data if SEND_REPLY_TO_COMMUNITY: smstables.write_outbox(success_msg, sms.sim_num) # spawn surficial measurement analysis if enable_analysis: obv = obv["obv"] surf_cmd_line = "python %s %d '%s' > %s 2>&1" % ( sc['fileio']['gndalert1'], obv['site_id'], obv['ts'], sc['fileio']['surfscriptlogs']) subprocess.Popen(surf_cmd_line, stdout=subprocess.PIPE, shell=True, stderr=subprocess.STDOUT) return True
def on_event(conv_event): global user_list if isinstance(conv_event, hangups.ChatMessageEvent): print('received chat message: "{}"'.format(conv_event.text)) received_msg = conv_event.text conversation_id = conv_event.conversation_id #test_groupchat if re.search("hi olivia", received_msg.lower()): # conversation_id = conv_event.conversation_id #test_groupchat message = "Hello {}".format( user_list.get_user(conv_event.user_id).full_name) cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) query = "SELECT quotations,author FROM olivia_quotes order by rand() limit 1" quote = db.read(query, connection="gsm_pi")[0] message = '"{}" -{}'.format(quote[0], quote[1]) # conversation_id = conv_event.conversation_id #test_groupchat cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif received_msg.lower() == "invalid": file = "{}/invalid.png".format(file_path) cmd = "{} {}/upload_image.py --conversation-id {} --image '{}'".format( python_path, file_path, conversation_id, file) os.system(cmd) elif re.search('ack \d+ .+', received_msg.lower()): message = "Thanks {} for the acknowledgement".format( user_list.get_user(conv_event.user_id).full_name) email = "" for e in user_list.get_user(conv_event.user_id).emails: email += '"{}",'.format(e) email = email[:-1] ts = (conv_event.timestamp + td(hours=8)).strftime("%Y-%m-%d %H:%M:%S") try: query = ( 'SELECT sim_num FROM comms_db.mobile_numbers ' 'INNER JOIN comms_db.user_mobiles ' 'ON mobile_numbers.mobile_id = user_mobiles.mobile_id ' 'INNER JOIN user_emails ' 'ON user_emails.user_id = user_mobiles.user_id ' 'WHERE email IN ({}) LIMIT 1'.format(email)) sim_num = db.read(query, connection="common") sms = smsclass.SmsInbox("", received_msg, sim_num[0][0], ts) amsg.process_ack_to_alert(sms) except: message = "wrong formatting" cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search("\A(in)*valid", received_msg.lower()): # conversation_id = conv_event.conversation_id #test_groupchat message = "Thanks {}".format( user_list.get_user(conv_event.user_id).full_name) cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) query = "SELECT quotations,author FROM olivia_quotes order by rand() limit 1" quote = db.read(query, connection="gsm_pi")[0] message = '"{}" -{}'.format(quote[0], quote[1]) # conversation_id = conv_event.conversation_id #test_groupchat cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search("olivia plot [0-9]{4}", received_msg.lower()): alert_id = received_msg.split(" ")[2] message = "wait..." # conversation_id = conv_event.conversation_id #test_groupchat cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) query = ( "SELECT stat_id, site_code,s.site_id, trigger_source, alert_symbol, " "ts_last_retrigger,source_id FROM " "(SELECT stat_id, ts_last_retrigger, site_id, trigger_source, " "alert_symbol,sym.source_id FROM " "(SELECT stat_id, ts_last_retrigger, site_id, trigger_sym_id FROM " "(SELECT * FROM alert_status WHERE " # "ts_set >= NOW()-interval 5 minute " # "and ts_ack is NULL" "stat_id={} " ") AS stat " "INNER JOIN " "operational_triggers AS op " "ON stat.trigger_id = op.trigger_id) AS trig " "INNER JOIN " "(Select * from operational_trigger_symbols where source_id in (1,2,3)) AS sym " "ON trig.trigger_sym_id = sym.trigger_sym_id " "inner join trigger_hierarchies as th " "on th.source_id=sym.source_id) AS alert " "INNER JOIN " "commons_db.sites as s " "ON s.site_id = alert.site_id".format(alert_id)) smsalert = db.df_read(query, connection="analysis") # for i in smsalert.index: try: OutputFP = main(smsalert.loc[0]) # if not OutputFP: # print ("nasend na!") # else: send_hangouts(OutputFP, smsalert.loc[0], conversation_id=conversation_id) except: message = "error no alert {}".format(alert_id) # conversation_id = conv_event.conversation_id #test_groupchat cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search("olivia ilan alert", received_msg.lower()): message = ilan_alert() cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search("olivia help", received_msg.lower()): file = "{}/olivia_help.jpg".format(file_path) # print(file) # conversation_id = conv_event.conversation_id #test_groupchat cmd = "{} {}/upload_image.py --conversation-id {} --image '{}'".format( python_path, file_path, conversation_id, file) os.system(cmd) elif (re.search( """olivia add quote "[A-Za-z0-9.,!?()'’ ]+"[-A-Za-z0-9.,!?() ]+""", received_msg.lower() ) or re.search( """olivia add quote “[A-Za-z0-9.,!?()'’ ]+”[-A-Za-z0-9.,!?() ]+""", received_msg.lower())): received_msg = received_msg.replace('“', '"') received_msg = received_msg.replace('”', '"') received_msg = received_msg.replace("’", "'") quote = received_msg.split('"') quotation = quote[1].replace("'", "") quotation = quotation.replace('"', "") author = quote[2].replace(" - ", "") author = author.replace("- ", "") author = author.replace(" -", "") author = author.replace("-", "") query = "INSERT INTO `olivia_quotes` (`quotations`, `author`) VALUES ('{}', '{}');".format( quotation, author) db.write(query, connection="gsm_pi") message = '"{}" -{} --added successfully'.format(quotation, author) # conversation_id = conv_event.conversation_id #test_groupchat cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search('olivia link', received_msg.lower()): query = "SELECT link from olivia_link where link_id = 1" message = db.read(query, connection="gsm_pi")[0][0] # message ="https://trello.com/c/YztIYZq0/8-monitoring-operations-manual-guides-and-links" # conversation_id = conv_event.conversation_id #test_groupchat cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search('olivia manual', received_msg.lower()): query = "SELECT link from olivia_link where link_id = 2" message = db.read(query, connection="gsm_pi")[0][0] # message ="https://drive.google.com/file/d/1u5cTNCkfVF--AYMaXiShOCozXE5dg7NW/view" # conversation_id = conv_event.conversation_id #test_groupchat cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search('olivia ping', received_msg.lower()): try: ipadd = received_msg.split(" ")[2] result = os.system("ping -c 1 {}".format(ipadd)) if result == 0: ping_response = subprocess.Popen( ["ping", ipadd, "-c", '1'], stdout=subprocess.PIPE).stdout.read().decode("utf-8") if (re.search('unreachable', ping_response)): message = "Unreachable network in {}".format(ipadd) else: message = "Ok network in {}".format(ipadd) else: message = "NOT ok network in {}".format(ipadd) except: message = "error input" cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search('olivia may data', received_msg.lower()): table_name = received_msg.lower().split(' ')[3] message = check_data(table_name, True) message = message[:-1] # for message in mes: cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search('olivia check site [A-Za-z]{3}', received_msg.lower()): site_code = received_msg.lower().split(' ')[3] df_sites = mem.get("DF_SITES") message = "" try: site_id = df_sites.site_id[df_sites.site_code == site_code].values[0] query_loggers = ( "SELECT * FROM (Select logger_name, model_id from commons_db.loggers " "where site_id = {} and date_deactivated is NULL and logger_id not in (141)) as l " "inner join commons_db.logger_models " "on logger_models.model_id = l.model_id".format(site_id)) site_loggers = db.df_read(query_loggers, connection="common") for i in site_loggers.index: #if has rain if site_loggers.has_rain[i] == 1: table_name = "rain_{}".format( site_loggers.logger_name[i]) add_mes = check_data(table_name) message += add_mes #if has tilt if site_loggers.has_tilt[ i] == 1 and site_loggers.logger_type[ i] != "gateway": table_name = "tilt_{}".format( site_loggers.logger_name[i]) add_mes = check_data(table_name) message += add_mes #if has soms if site_loggers.has_soms[ i] == 1 and site_loggers.logger_type[ i] != "gateway": table_name = "soms_{}".format( site_loggers.logger_name[i]) add_mes = check_data(table_name) message += add_mes #if has piezo if site_loggers.has_piezo[ i] == 1 and site_loggers.logger_type[ i] != "gateway": table_name = "piezo_{}".format( site_loggers.logger_name[i]) add_mes = check_data(table_name) message += add_mes message = message[:-1] except: message = "error site_code: {}".format(site_code) # for message in mes: cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search('olivia server number', received_msg.lower()): query = ("SELECT gsm_id, gsm_name, gsm_sim_num FROM gsm_modules " "where gsm_id between 2 and 7") server_num = db.df_read(query, resource="sms_data") message = "Server number for MIA:\nGlobe: {}\nSmart: {}\n".format( server_num.gsm_sim_num[server_num.gsm_id == 2].values[0], server_num.gsm_sim_num[server_num.gsm_id == 3].values[0]) message += "\nServer number for LOGGERS:\nGlobe: \n{}\n{}\nSmart: \n{}\n{}\n".format( server_num.gsm_sim_num[server_num.gsm_id == 4].values[0], server_num.gsm_sim_num[server_num.gsm_id == 6].values[0], server_num.gsm_sim_num[server_num.gsm_id == 5].values[0], server_num.gsm_sim_num[server_num.gsm_id == 7].values[0]) query = ("SELECT sim_num, gsm_id FROM mobile_numbers " "inner join user_mobiles " "on user_mobiles.mobile_id = mobile_numbers.mobile_id " "inner join commons_db.users " "on users.user_id = user_mobiles.user_id " "where nickname like 'CT Phone' ") ct_phone = db.df_read(query, resource="sms_data") message += "\nCT Phone:\nGlobe: {}\nSmart: {}".format( ct_phone.sim_num[ct_phone.gsm_id == 2].values[0], ct_phone.sim_num[ct_phone.gsm_id == 3].values[0]) cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search('olivia info', received_msg.lower()): file = "{}/infographics_plot.png".format(file_path) cmd = "{} {}/upload_image.py --conversation-id {} --image '{}'".format( python_path, file_path, conversation_id, file) os.system(cmd) file = "{}/infographics_invalid.png".format(file_path) cmd = "{} {}/upload_image.py --conversation-id {} --image '{}'".format( python_path, file_path, conversation_id, file) os.system(cmd) elif re.search('olivia ano number', received_msg.lower()): query_loggers = "select logger_name from loggers where date_deactivated is NULL" loggers = db.df_read( query_loggers, resource="common_data").logger_name.to_numpy() query_users = ( "SELECT nickname FROM users where nickname is not NULL " "and nickname !='' and status = 1 ") users = db.df_read( query_users, resource="common_data").nickname.str.lower().to_numpy() message = "" check_logger = re.findall(r" (?=(" + '|'.join(loggers) + r"))", received_msg.lower()) check_user = re.findall(r" (?=(" + '|'.join(users) + r"))", received_msg.lower()) if check_logger: for logger_name in check_logger: query_num = ( "SELECT sim_num FROM logger_mobile " "inner join commons_db.loggers " "on logger_mobile.logger_id = loggers.logger_id " "where logger_name = '{}'".format(logger_name)) logger_num = db.read(query_num, resource="sms_data")[0][0] message += "{} : {}\n".format(logger_name, logger_num) if check_user: for nickname in check_user: query_num = ( "SELECT sim_num FROM commons_db.users " "inner join user_mobiles " "on users.user_id = user_mobiles.user_id " "inner join mobile_numbers " "on user_mobiles.mobile_id = mobile_numbers.mobile_id " "where nickname is not NULL " "and nickname !='' " "and users.status = 1 " "and user_mobiles.status = 1 " "and nickname like '{}%%'".format(nickname)) user_num = db.read(query_num, resource="sms_data") for num in user_num: message += "{} : {}\n".format(nickname, num[0]) if not check_user and not check_logger: message += "error!!!\n" message = message[:-1] cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search('olivia kanino', received_msg.lower()): numbers = re.findall(r"\d+", received_msg) numbers = list(map(int, numbers)) message = "" for num in numbers: query_num = ("SELECT logger_name FROM logger_mobile " "inner join commons_db.loggers " "on logger_mobile.logger_id = loggers.logger_id " "where loggers.date_deactivated is null " "and logger_mobile.date_deactivated is null " "and sim_num like '%%{}'".format(num)) try: logger_name = db.read(query_num, resource="sms_data")[0][0] message += "{} : {}\n".format(num, logger_name) except IndexError: message += "{} : not a logger\n".format(num) message = message[:-1] cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search('olivia node', received_msg.lower()): query_loggers = "select tsm_name from tsm_sensors where date_deactivated is NULL" sensors = db.df_read(query_loggers, resource="sensor_data").tsm_name.to_numpy() check_tsm = re.findall(r" (?=(" + '|'.join(sensors) + r"))", received_msg.lower()) message = "" for tsm_name in check_tsm: try: query_node = ( "SELECT n_id FROM deployed_node " "inner join tsm_sensors " "on tsm_sensors.tsm_id = deployed_node.tsm_id " "where tsm_name = '{}' order by node_id".format( tsm_name)) nodes = db.df_read(query_node, resource="sensor_data").n_id message += "{} : ".format(tsm_name) for nid in nodes: message += "{},".format(nid) message = message[:-1] message += "\n" except: message += "no data for {}\n".format(tsm_name) message = message[:-1] cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) elif re.search('olivia checklist', received_msg.lower()): query = "SELECT link from olivia_link where description = 'checklist'" message = db.read(query, connection="gsm_pi")[0][0] # message ="https://drive.google.com/file/d/1u5cTNCkfVF--AYMaXiShOCozXE5dg7NW/view" # conversation_id = conv_event.conversation_id #test_groupchat cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) # smstables.write_outbox(message=message, recipients="639176321023", # gsm_id=2, table='users') elif re.search('olivia ops checker', received_msg.lower()): try: ts = re.findall(r'\d{4}-\d{2}-\d{2} \d{2}:\d{2}', received_msg)[0] except IndexError: ts = "" # received_msg.replace('olivia ops checker',"") # ts = ts.replace("'","") # ts = ts.replace('"',"") # ts = ts.replace('”',"") # ts = ts.replace('“',"") ops_checker(conversation_id, ts) ts = (conv_event.timestamp + td(hours=8)).strftime("%Y-%m-%d %H:%M:%S") email = "" for e in user_list.get_user(conv_event.user_id).emails: email += '"{}",'.format(e) email = email[:-1] try: query = 'select email_id from user_emails where email in ({}) limit 1'.format( email) email_id = db.read(query, connection="common") received_msg = received_msg.replace("'", "") received_msg = received_msg.replace('"', "") query_log = "INSERT INTO `olivia_logs` (`ts`, `conv_id`, `email_id`, `message`) VALUES ('{}', '{}', '{}', '{}');".format( ts, conversation_id, email_id[0][0], received_msg) db.write(query_log, connection="gsm_pi") except: print("error logging")
def ilan_alert(link=False): query = ( "SELECT site_code,alert_symbol, trigger_list " ",if (timestampdiff(hour, data_ts,validity)<5,'for lowering','') as stat " "FROM monitoring_events " "inner join commons_db.sites on sites.site_id = monitoring_events.site_id " "inner join monitoring_event_alerts " "on monitoring_event_alerts.event_id = monitoring_events.event_id " "inner join monitoring_releases " "on monitoring_event_alerts.event_alert_id = monitoring_releases.event_alert_id " "inner join public_alert_symbols " "on public_alert_symbols.pub_sym_id = monitoring_event_alerts.pub_sym_id " "where monitoring_event_alerts.pub_sym_id >= 2 and validity > Now() and data_ts >= NOW()-INTERVAL 4.5 hour " "order by alert_symbol desc") cur_alert = db.df_read(query, connection="website") # remove repeating site_code cur_alert = cur_alert.groupby("site_code").first().reset_index() message = "**{}** alerts\n".format(len(cur_alert)) if len(cur_alert) == 1: message = message.replace("s", "") if len(cur_alert) > 0: for i in range(0, len(cur_alert)): if "ND" in cur_alert.trigger_list[i]: message += "{} : {} {}\n".format(cur_alert.site_code[i], cur_alert.trigger_list[i], cur_alert.stat[i]) else: message += "{} : {}-{} {}\n".format(cur_alert.site_code[i], cur_alert.alert_symbol[i], cur_alert.trigger_list[i], cur_alert.stat[i]) # else: if link: if len(cur_alert) == 0: magupdate = True else: magupdate = False #if am shift if dt.now().hour < 12: #check if routine month = dt.now().strftime("%B").lower() day = dt.now().isoweekday() query = "SELECT season_group_id FROM seasons where {} = ".format( month) if day in (2, 5): #wet season query += "'w'" elif day == 3: query += "'d'" else: query += "'j'" season = db.df_read(query, connection="common") if len(season) > 0: routine = True message += "Routine Monitoring\n" else: routine = False #check if theres extended query = ( "SELECT distinct site_code FROM monitoring_events " "INNER JOIN commons_db.sites ON sites.site_id = monitoring_events.site_id " "INNER JOIN monitoring_event_alerts ON monitoring_event_alerts.event_id = monitoring_events.event_id " "INNER JOIN monitoring_releases ON monitoring_event_alerts.event_alert_id = monitoring_releases.event_alert_id " "INNER JOIN public_alert_symbols ON public_alert_symbols.pub_sym_id = monitoring_event_alerts.pub_sym_id " "WHERE alert_level = 0 and status = 2 " "AND DATE_FORMAT(ts_start, '%Y-%m-%d') > NOW() - INTERVAL 4 DAY " "AND DATE_FORMAT(validity,'%Y-%m-%d') + INTERVAL 1 DAY < NOW()" ) ext_site = db.read(query, connection="website") if len(ext_site) > 0: extended = True message += "Extended Monitoring **{}** sites:\n".format( len(ext_site)) if len(ext_site) == 1: message = message.replace("sites", "site") for site in ext_site: message += "{}\n".format(site[0]) else: extended = False if routine or extended: magupdate = False if magupdate: if dt.now().hour < 12: query = "SELECT link from olivia_link where description = 'contacts updating'" contact_link = db.read(query, connection="gsm_pi")[0][0] message += "Magupdate ng contacts\n{}\n".format(contact_link) else: message += "Matulog nang huwag masyadong mahimbing\n" message = message[:-1] return message
def get_name_of_staff(number): query = ("select t1.user_id, t2.nickname, t1.gsm_id from user_mobile t1 " "inner join users t2 on t1.user_id = t2.user_id where " "t1.sim_num = '%s';") % (number) return dbio.read(query=query, resource="sms_data")[0]
def check_logger_model(logger_name): query = ("SELECT model_id FROM loggers where " "logger_name = '%s'") % logger_name return dbio.read(query, resource="common_data")[0][0]
import asyncio import hangups from common import run_example import re import os import dynadb.db as db import time if __name__ == '__main__': query = "select link from olivia_link where link_id = 3" python_path = db.read(query, connection="gsm_pi")[0][0] file_path = os.path.dirname(__file__) brain = 'UgwySAbzw-agrDF6QAB4AaABAagBp5i4CQ' conversation_id = brain message = "olivia staying alive" cmd = "{} {}/send_message.py --conversation-id {} --message-text '{}'".format( python_path, file_path, conversation_id, message) os.system(cmd) time.sleep(5) q_log = ("SELECT TIMESTAMPDIFF(MINUTE, ts, NOW()) " "AS difference FROM olivia_logs " "where log_id = (select max(log_id) from olivia_logs)") ts_diff = db.read(q_log, connection="gsm_pi")[0][0]
def check_logger_model(logger_name): query = ("SELECT model_id FROM senslopedb.loggers where " "logger_name = '%s'") % logger_name return dbio.read(query, resource="sensor_data")[0][0]
def diagnostics(sms): """ - The process of parsing diagnostics of loggers. :param sms: Dictionary of sms info. :type sms: obj Returns: DataFrame: Dataframe output for success parsing and return False if fails. Example Input: LTESA*m* 0.70*14.12*136.80*14.04*137.70*14.05* 0.90*14.02*210308150000 Example Output:: batt_volt curr_draw stat ts 0 14.12 0.70 0 2021-03-08 15:00:00 1 14.04 136.80 1 2021-03-08 15:00:00 2 14.05 137.70 2 2021-03-08 15:00:00 3 14.02 0.90 99 2021-03-08 15:00:00 """ msg = sms.msg msg = msg.replace("DUE", "") msg = msg.replace("PZ", "") split_msg = msg.split("*") ts = split_msg[len(split_msg) - 1] pattern = '%y%m%d%H%M%S' timestamp = dt.strptime(ts, pattern).strftime('%Y-%m-%d %H:%M:00') num_of_data = (len(split_msg) - 3) / 2 outl = [] for i in range(0, int(num_of_data)): try: curr_draw = split_msg[2 + (i) * 2] batt_volt = split_msg[2 + (i) * 2 + 1] if i == int(num_of_data) - 1: stat = 99 last_str_split = batt_volt.split(">") batt_volt = last_str_split[0] try: unsent_data = int(last_str_split[1]) except IndexError: unsent_data = np.nan else: stat = i line = { "ts": timestamp, "stat": stat, "curr_draw": curr_draw, "batt_volt": batt_volt } print(i) print(stat, curr_draw, batt_volt) line = { "ts": timestamp, "stat": stat, "curr_draw": curr_draw, "batt_volt": batt_volt } outl.append(line) except: print("kulang data") tsm_name = split_msg[0].lower() name_df = "volt_{}".format(tsm_name) df = pd.DataFrame(outl) volt = smsclass.DataTable(name_df, df) #for unsent out2 = [] tsm_sensors = mem.get("DF_TSM_SENSORS") try: tsm_id = tsm_sensors[tsm_sensors.tsm_name == tsm_name].tsm_id.values[0] except: tsm_id = np.nan sender = sms.sim_num[-10:] try: query = ( "select mobile_id from logger_mobile " "where sim_num like '%{}' order by date_activated desc limit 1". format(sender)) mobile_id = db.read(query, resource="sms_data")[0][0] except: mobile_id = np.nan line2 = { "ts": timestamp, "mobile_id": mobile_id, "tsm_id": tsm_id, "unsent": unsent_data } out2.append(line2) df2 = pd.DataFrame(out2) unsent = smsclass.DataTable("unsent", df2) data = [volt, unsent] return data
def get_mobiles(table=None, host=None, reset_variables=False, resource=None): """ **Description:** -The get mobile sim nums is a function that get the number of the loggers or users in the database. :param table: loggers or users table. :param host: host name of the number and **Default** to **None** :type table: str :type host: str :returns: **mobile number** (*int*) - mobile number of user or logger """ mc = memory.get_handle() # if host is None: # raise ValueError("No host value given for mobile number") if not table: raise ValueError("No table definition") is_reset_variables = reset_variables if table == 'loggers': logger_mobile_sim_nums = mc.get('logger_mobile_sim_nums') if logger_mobile_sim_nums and not is_reset_variables: return logger_mobile_sim_nums print("Force reset logger mobiles in memory") query = ("SELECT t1.mobile_id, t1.sim_num, t1.gsm_id " "FROM logger_mobile AS t1 " "LEFT OUTER JOIN logger_mobile AS t2 " "ON t1.sim_num = t2.sim_num " "AND (t1.date_activated < t2.date_activated " "OR (t1.date_activated = t2.date_activated " "AND t1.mobile_id < t2.mobile_id)) " "WHERE t2.sim_num IS NULL and t1.sim_num is not null") nums = dbio.read(query=query, identifier='get_mobile_sim_nums', connection='gsm_pi') logger_mobile_sim_nums = { sim_num: mobile_id for (mobile_id, sim_num, gsm_id) in nums } mc.set("logger_mobile_sim_nums", logger_mobile_sim_nums) logger_mobile_def_gsm_id = { mobile_id: gsm_id for (mobile_id, sim_num, gsm_id) in nums } mc.set("logger_mobile_def_gsm_id", logger_mobile_def_gsm_id) elif table == 'users': user_mobile_sim_nums = mc.get('user_mobile_sim_nums') if user_mobile_sim_nums and not is_reset_variables: return user_mobile_sim_nums print("Force reset user mobiles in memory") query = "select mobile_id, sim_num, gsm_id from mobile_numbers" nums = dbio.read(query=query, identifier='get_mobile_sim_nums', host=host, resource=resource) user_mobile_sim_nums = { sim_num: mobile_id for (mobile_id, sim_num, gsm_id) in nums } mc.set("user_mobile_sim_nums", user_mobile_sim_nums) user_mobile_def_gsm_id = { mobile_id: gsm_id for (mobile_id, sim_num, gsm_id) in nums } mc.set("user_mobile_def_gsm_id", user_mobile_def_gsm_id) else: print('Error: table', table) sys.exit() return nums
# ts = ts.replace('”',"") # ts = ts.replace('“',"") ops_checker(conversation_id, ts) ts = (conv_event.timestamp + td(hours=8)).strftime("%Y-%m-%d %H:%M:%S") email = "" for e in user_list.get_user(conv_event.user_id).emails: email += '"{}",'.format(e) email = email[:-1] try: query = 'select email_id from user_emails where email in ({}) limit 1'.format( email) email_id = db.read(query, connection="common") received_msg = received_msg.replace("'", "") received_msg = received_msg.replace('"', "") query_log = "INSERT INTO `olivia_logs` (`ts`, `conv_id`, `email_id`, `message`) VALUES ('{}', '{}', '{}', '{}');".format( ts, conversation_id, email_id[0][0], received_msg) db.write(query_log, connection="gsm_pi") except: print("error logging") if __name__ == '__main__': query = "SELECT link from olivia_link where link_id = 3" python_path = db.read(query, connection="gsm_pi")[0][0] file_path = os.path.dirname(__file__) run_example(receive_messages)
def write_inbox(msglist='',gsm_info='',resource="sms_data"): """ **Description:** -The write raw sms to database function that write raw message in database. :param msglist: The message list. :param gsm_info: The gsm_info that being use. :type msglist: obj :type gsm_info: obj :returns: N/A """ if not msglist: raise ValueError("No msglist definition") if not gsm_info: raise ValueError("No gsm_info definition") sc = mem.server_config() mobile_nums_db = sc["resource"]["mobile_nums_db"] logger_mobile_sim_nums = static.get_mobiles(table='loggers', host=mobile_nums_db, resource="sms_data") user_mobile_sim_nums = static.get_mobiles(table='users', host=mobile_nums_db, resource="sms_data") # gsm_ids = get_gsm_modules() ts_stored = dt.today().strftime("%Y-%m-%d %H:%M:%S") gsm_id = gsm_info['id'] loggers_count = 0 users_count = 0 query_loggers = ("insert into smsinbox_loggers (ts_sms, ts_stored, mobile_id, " "sms_msg,read_status,gsm_id) values ") query_users = ("insert into smsinbox_users (ts_sms, ts_stored, mobile_id, " "sms_msg,read_status,gsm_id) values ") sms_id_ok = [] sms_id_unk = [] ts_sms = 0 # ltr_mobile_id= 0 for m in msglist: # print m.simnum, m.data, m.dt, m.num ts_sms = m.dt sms_msg = m.data read_status = 0 if m.simnum in logger_mobile_sim_nums.keys(): query_loggers += "('%s','%s',%d,'%s',%d,%d)," % (ts_sms, ts_stored, logger_mobile_sim_nums[m.simnum], sms_msg, read_status, gsm_id) # ltr_mobile_id= logger_mobile_sim_nums[m.simnum] loggers_count += 1 elif m.simnum in user_mobile_sim_nums.keys(): query_users += "('%s','%s',%d,'%s',%d,%d)," % (ts_sms, ts_stored, user_mobile_sim_nums[m.simnum], sms_msg, read_status, gsm_id) users_count += 1 else: print ('Unknown number', m.simnum) sms_id_unk.append(m) continue sms_id_ok.append(m.num) query_loggers = query_loggers[:-1] query_users = query_users[:-1] sc = mem.server_config() sms_host = sc["resource"]["smsdb"] if len(sms_id_ok)>0: if loggers_count > 0: dbio.write(query=query_loggers, host=sms_host, resource=resource) if users_count > 0: dbio.write(query=query_users, host=sms_host, resource=resource) if len(sms_id_unk)>0: for msg_details in sms_id_unk: check_if_existing = "SELECT * FROM mobile_numbers where sim_num = '%s'" % msg_details.simnum is_exist = dbio.read(query=check_if_existing, host=sms_host, resource=resource) if len(is_exist) == 0: random_id = random.randint(200,999999)*5 new_unknown_query = 'INSERT INTO users VALUES (0,"UN",'\ '"UNKNOWN_%d","UNKNOWN","UNKNOWN_%d","UNKNOWN",' \ '"1994-08-16","M","1")' % (random_id, random_id) dbio.write(query=new_unknown_query, host=sms_host, resource=resource) query_insert_mobile_details = 'insert into mobile_numbers (sim_num,gsm_id) values ' \ '("%s","%s")' % (msg_details.simnum,gsm_id) mobile_id = dbio.write(query=query_insert_mobile_details, host=sms_host, resource=resource, last_insert=True) query_insert_mobile_details = 'insert into user_mobiles (user_id,' \ 'mobile_id,priority,status) values ' \ '((SELECT user_id FROM users WHERE firstname = "UNKNOWN_%s"),"%s","%s","%s")' % (random_id,mobile_id,'1','1') dbio.write(query=query_insert_mobile_details, host=sms_host, resource=resource) user_mobile_sim_nums[mobile_id] = msg_details.simnum query_users += "('%s','%s','%s','%s',%d,%d)" \ % (msg_details.dt, ts_stored, mobile_id, msg_details.data, 0, gsm_id) dbio.write(query=query_users, host=sms_host, resource=resource) else: get_mobile_id_query = "SELECT mobile_id FROM mobile_numbers WHERE sim_num = '%s'" % msg_details.simnum mobile_id = dbio.read(query=get_mobile_id_query, host=sms_host, resource=resource) query_users += "('%s','%s','%s','%s',%d,%d)" \ % (msg_details.dt, ts_stored, mobile_id[0][0], msg_details.data, 0, gsm_id) dbio.write(query=query_users, host=sms_host, resource=resource)