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)
