def get_max_index_from_table(table_name):
"""
- The process getting of max index from table is a function that get the max index of the smsinbox.
:param table: Name of the table for smsinbox
:type table: str
Returns:
int: Index id of the not yet copied data in dyna.
"""
sc = mem.server_config()
smsdb_host = sc["hosts"][sc["resource"]["smsdb"]]
user = sc["db"]["user"]
password = sc["db"]["password"]
name = sc["db"]["smsdb_name"]
command = (
"mysql -u %s -h %s -e 'select max(inbox_id) from %s.smsinbox_%s "
"where gsm_id!=1' -p%s") % (user, smsdb_host, name, table_name,
password)
p = subprocess.Popen(command,
stdout=subprocess.PIPE,
shell=True,
stderr=subprocess.STDOUT)
out, err = p.communicate()
return int(out.split('\n')[2])
def web_plotter(site_code, end, days):
"""
Created by Kevin
For integration and refactoring in the future
"""
start_time = datetime.now()
qdb.print_out(start_time)
site_code = [site_code]
dt_end = pd.to_datetime(end)
sc = mem.server_config()
rtw = sc['rainfall']['roll_window_length']
ts_end, start, offsetstart = get_rt_window(
float(days), float(rtw), end=dt_end)
gauges = rainfall_gauges()
if site_code != '':
gauges = gauges[gauges.site_code.isin(site_code)]
gauges['site_id'] = gauges['site_id'].apply(lambda x: float(x))
site_props = gauges.groupby('site_id')
plot_data = site_props.apply(rp.main, offsetstart=offsetstart,
tsn=end, save_plot=False, sc=sc,
start=start, output_path="",
end=ts_end).reset_index(drop=True)
json_plot_data = plot_data.to_json(orient="records")
qdb.print_out("runtime = %s" % (datetime.now() - start_time))
return json_plot_data
def log_runtime_status(script_name, status):
"""
- The process of logging runtime status is a function that log the runtime of script if alive or not.
:param script_name: Script file name.
:param status: script runtime status.
:type script_name: str
:type status: str
"""
sc = mem.server_config()
gsm_modules_host = sc["resource"]["smsdb"]
if (status == 'alive'):
ts = dt.today()
diff = (ts.minute % 10) * 60 + ts.second
ts = ts - td(seconds=diff)
logtimestamp = ts.strftime("%Y-%m-%d %H:%M:00")
else:
logtimestamp = dt.today().strftime("%Y-%m-%d %H:%M:00")
print(">> Logging runtime '" + status + "' at " + logtimestamp)
query = ("insert ignore into runtimelog (ts, script_name, log_details) "
"Values ('%s','%s','%s')") % (logtimestamp, script_name, status)
db.write(query, 'log_runtime_status', False, gsm_modules_host)
"""
def print_out(line):
"""Prints line.
"""
sc = mem.server_config()
if sc['print']['print_stdout']:
print(line)
def set_variables_old(reset_variables):
"""
- Description.
Args:
Args (str): Args.
Returns:
Returns.
Raises:
MySQLdb.OperationalError: Error in database connection.
"""
print(dt.today().strftime('%Y-%m-%d %H:%M:%S'))
mc = memory.get_handle()
sc = memory.server_config()
print("Reset alergenexec", )
mc.set("alertgenexec", False)
print("done")
print("Set static tables to memory", )
try:
set_mysql_tables(mc)
except KeyError:
print(">> KeyError")
print("done")
print("Set mobile numbers to memory", )
# mobiles_host = sc["resource"]["mobile_nums_db"]
get_mobiles(table="loggers",
reset_variables=reset_variables,
resource="sms_data")
get_mobiles(table="users",
reset_variables=reset_variables,
resource="sms_data")
print("done")
try:
print("Set surficial_markers to memory", )
get_surficial_markers(from_memory=False)
print("done")
print("Set surficial_parser_reply_messages", )
df = get_surficial_parser_reply_messages()
mc.set("surficial_parser_reply_messages", df)
print("done")
except sqlalchemy.exc.ProgrammingError:
print(">> Error on getting surficial information. Skipping load")
def noah_gauges():
"""Gathers information on rain gauges from NOAN or ASTI
Returns:
dataframe: available rain gauges from NOAH or ASTI
"""
sc = mem.server_config()
r = requests.get(sc['rainfall']['noah_gauges'],
auth=(sc['rainfall']['noah_user'],
sc['rainfall']['noah_password']))
noah = pd.DataFrame(r.json())
noah = noah.loc[noah['sensor_name'].str.contains('rain', case=False)]
def download_rainfall_noah(noah_id, fdate, tdate):
"""Downloads rainfall data of noah_id from fdate to tdate.
Args:
noah_id (int): Device id of noah data.
fdate (timestamp): Timestamp start of data to be downloaded.
tdate (timestamp): Timestamp end of data to be downloaded.
Returns:
dataframe: Rainfall data of noah_id from fdate to tdate if with data
else empty dataframe.
"""
#Reduce latest_ts by 1 day as a work around for GMT to local conversion
offset_date = (pd.to_datetime(fdate) - timedelta(1)).strftime("%Y-%m-%d")
sc = mem.server_config()
url = (sc['rainfall']['noah_data'] +
'/%s/from/%s/to/%s') % (noah_id, offset_date, tdate)
try:
req = requests.get(url,
auth=(sc['rainfall']['noah_user'],
sc['rainfall']['noah_password']))
except:
qdb.print_out("Can't get request. Please check internet connection")
return pd.DataFrame()
try:
df = pd.DataFrame(req.json()["data"])
except:
qdb.print_out("error: %s" % noah_id)
return pd.DataFrame()
try:
#rename dateTimeRead into ts and rain_value into rain
df = df.rename(columns={'rain_value': 'rain', 'dateTimeRead': 'ts'})
df = df.drop_duplicates('ts')
df['ts'] = df['ts'].apply(lambda x: pd.to_datetime(str(x)[0:19]))
df['rain'] = df['rain'].apply(lambda x: float(x))
df = df.sort_values('ts')
#remove the entries that are less than fdate
df = df[df.ts > fdate]
return df[['ts', 'rain']]
except:
return pd.DataFrame()
def get_last_copied_index(table_name):
"""
- The process of getting last copied index is a function that reads the
value of the index inside the user_inbox_index.tmp.
Returns:
int: Outputs index id that stored from the user_inbox_index.tmp.
"""
sc = mem.server_config()
logsdir = sc["fileio"]["logsdir"]
tmpfile = logsdir + ("%s_inbox_index.tmp" % table_name)
f_index = open(tmpfile, 'r')
max_index_last_copied = int(f_index.readline())
f_index.close()
return max_index_last_copied
def senslopedb_connect(hostdb='local'):
sc = mem.server_config()
Hostdb = sc['hosts'][hostdb]
Userdb = sc['db']['user']
Passdb = sc['db']['password']
Namedb = sc['db']['name']
while True:
try:
db = mysqlDriver.connect(host=Hostdb,
user=Userdb,
passwd=Passdb,
db=Namedb)
cur = db.cursor()
cur.execute("use " + Namedb)
return db, cur
except mysqlDriver.OperationalError:
print_out('.')
def main(mc):
print(dt.today().strftime("%c"))
sc = mem.server_config()
proc_limit = sc["io"]["proc_limit"]
while True:
alertgenlist = mc.get('alertgenlist')
print(alertgenlist)
if alertgenlist is None:
break
if len(alertgenlist) == 0:
break
alert_info = alertgenlist.pop()
mc.set('alertgenlist', [])
mc.set('alertgenlist', alertgenlist)
python_path = sc['fileio']['python_path']
alertgen = sc["fileio"]["alertgenscript"]
command = '{} {} {} "{}"'.format(python_path, alertgen,
alert_info['tsm_name'],
alert_info['ts'])
print("Running", alert_info['tsm_name'], "alertgen")
if lockscript.get_lock('alertgen for %s' % alert_info['tsm_name'],
exitifexist=False):
print('execute:', command)
run_out = subprocess.run(command,
stdout=subprocess.PIPE,
shell=True,
stderr=subprocess.STDOUT)
print(run_out)
else:
continue
while count_alert_analysis_instances() > proc_limit:
time.sleep(5)
print('.', )
def get_all_outbox_sms_from_db(table='',send_status=5,gsm_id=5,limit=10,
resource="sms_data"):
"""
**Description:**
-The function that get all outbox message that are not yet send.
:param table: Table name and **Default** to **users** table .
:param send_status: **Default** to **5**.
:param gsm_id: **Default** to **5**.
:param limit: **Default** to **10**.
:type table: str
:type send_status: str
:type gsm_id: int
:type limit: int
:returns: List of message
"""
if not table:
raise ValueError("No table definition")
sc = mem.server_config()
host = sc['resource']['smsdb']
while True:
try:
db, cur = dbio.connect(host=host, resource=resource)
query = ("select t1.stat_id,t1.mobile_id,t1.gsm_id,t1.outbox_id,"
"t2.sms_msg from "
"smsoutbox_%s_status as t1 "
"inner join (select * from smsoutbox_%s) as t2 "
"on t1.outbox_id = t2.outbox_id "
"where t1.send_status < %d "
"and t1.send_status >= 0 "
"and t1.gsm_id = %d "
"limit %d ") % (table[:-1],table,send_status,gsm_id,limit)
a = cur.execute(query)
out = []
if a:
out = cur.fetchall()
db.close()
return out
except MySQLdb.OperationalError:
print ('10.',)
time.sleep(20)
def main(mc):
print(dt.today().strftime("%c"))
sc = mem.server_config()
proc_limit = sc["io"]["proc_limit"]
while True:
alertgenlist = mc.get('alertgenlist')
print(alertgenlist)
if alertgenlist is None:
break
if len(alertgenlist) == 0:
break
alert_info = alertgenlist.pop()
mc.set('alertgenlist', [])
mc.set('alertgenlist', alertgenlist)
command = "python %s %s '%s'" % (sc["fileio"]["alertgenscript"],
alert_info['tsm_name'],
alert_info['ts'])
print("Running", alert_info['tsm_name'], "alertgen")
if lockscript.get_lock('alertgen for %s' % alert_info['tsm_name'],
exitifexist=False):
subprocess.Popen(command,
stdout=subprocess.PIPE,
shell=True,
stderr=subprocess.STDOUT)
else:
continue
while count_alert_analysis_instances() > proc_limit:
time.sleep(5)
print('.', )
def get_allowed_prefixes(network):
"""
- The process of checking network prefixes extensions in the volatile.memory funtion server_config.
:param netwok: Table name (users and loggers) and **Default** to **users** table .
:type network: str
Returns:
int: Outputs the number with extended 639 or 09 in the number.
"""
sc = mem.server_config()
if network.upper() == 'SMART':
prefix_list = sc["simprefix"]["smart"].split(',')
else:
prefix_list = sc["simprefix"]["globe"].split(',')
extended_prefix_list = []
for p in prefix_list:
extended_prefix_list.append("639" + p)
extended_prefix_list.append("09" + p)
return extended_prefix_list
def get_surficial_markers(host=None, from_memory=True):
"""
- Description.
Args:
Args (str): Args.
Returns:
Returns.
Raises:
MySQLdb.OperationalError: Error in database connection.
"""
mc = memory.get_handle()
sc = memory.server_config()
if from_memory:
return mc.get("surficial_markers")
if not host:
print("Host defaults to datadb")
host = sc["resource"]["datadb"]
query = ("select m2.marker_id, m3.marker_name, m4.site_id from "
"(select max(history_id) as history_id, "
"marker_id from marker_history as m1 "
"group by m1.marker_id "
") as m2 "
"inner join marker_names as m3 "
"on m2.history_id = m3.history_id "
"inner join markers as m4 "
"on m2.marker_id = m4.marker_id ")
engine = dbio.connect(resource="sensor_data", conn_type=0)
surficial_markers = psql.read_sql_query(query, engine)
mc.set("surficial_markers", surficial_markers)
return surficial_markers
def send_messages_from_db(gsm=None,
table='users',
send_status=0,
gsm_info=None,
limit=10,
mock_send=False):
"""
- The process getting all message unset and try to send the message again.
:param table: Table name and **Default** to **users** table .
:param send_status: the id number of the gsm message status and **Default** to **0**.
:param gsm_id: The id of the gsm that is use globe (2,4) and smart (3,5) and **Default** to **0**.
:param limit: The limit of message to get in the table and **Default** to **10**.
:type table: str
:type send_status: str
:type gsm_id: int
:type limit: int
"""
if gsm == None:
raise ValueError("No gsm instance defined")
sc = mem.server_config()
host = sc['resource']['smsdb']
allmsgs = smstables.get_all_outbox_sms_from_db(table, send_status,
gsm_info["id"], limit)
if len(allmsgs) <= 0:
return
print(">> Sending messagess from db")
table_mobile = static.get_mobiles(table=table, resource="sms_data")
inv_table_mobile = {v: k for k, v in table_mobile.iteritems()}
# print inv_table_mobile
msglist = []
error_stat_list = []
today = dt.today().strftime("%Y-%m-%d %H:%M:%S")
for stat_id, mobile_id, outbox_id, gsm_id, sms_msg in allmsgs:
try:
smsItem = modem.GsmSms(stat_id, inv_table_mobile[mobile_id],
sms_msg, '')
msglist.append([smsItem, gsm_id, outbox_id, mobile_id])
except KeyError:
print(">> Unknown mobile_id:", mobile_id)
error_stat_list.append(
(stat_id, -1, today, gsm_id, outbox_id, mobile_id))
continue
if len(error_stat_list) > 0:
print(">> Ignoring invalid messages...", )
smstables.set_send_status(table, error_stat_list)
print("done")
if len(msglist) == 0:
print(">> No valid message to send")
return
allmsgs = msglist
status_list = []
allowed_prefixes = get_allowed_prefixes(gsm_info["network"])
# # cycle through all messages
for msg in allmsgs:
try:
num_prefix = re.match("^ *((0)|(63))9\d\d", msg[0].simnum).group()
num_prefix = num_prefix.strip()
except:
print('Error getting prefix', msg[0].simnum)
continue
# check if recepient number in allowed prefixed list
if num_prefix in allowed_prefixes:
if mock_send:
ret = mock_send_msg(msg[0].data, msg[0].simnum.strip())
else:
ret = gsm.send_msg(msg[0].data, msg[0].simnum.strip())
today = dt.today().strftime("%Y-%m-%d %H:%M:%S")
if ret:
stat = msg[0].num, 1, today, msg[1], msg[2], msg[3]
else:
stat = msg[0].num, 5, today, msg[1], msg[2], msg[3]
status_list.append(stat)
else:
print("Number not in prefix list", num_prefix)
today = dt.today().strftime("%Y-%m-%d %H:%M:%S")
stat = msg[0].num, -1, today, msg[1], msg[2], msg[3]
status_list.append(stat)
continue
smstables.set_send_status(table, status_list, host)
def memcached():
# mc = memcache.Client(['127.0.0.1:11211'],debug=0)
return mem.server_config()
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 dyna_to_sandbox():
"""
- The process of dyna to sandbox is a function that process the exporting of data from dyna and importing data to sandbox by
loading the data from XML.
"""
# get latest sms_id
# print c.db["user"],c.dbhost["local"],c.db["name"],c.db["password"]
sc = mem.server_config()
user = sc["db"]["user"]
password = sc["db"]["password"]
name = sc["db"]["name"]
sb_host = sc["hosts"]["sandbox"]
gsm_host = sc["hosts"]["gsm"]
sqldumpsdir = sc["fileio"]["sqldumpsdir"]
print("Checking max sms_id in sandbox smsinbox ")
command = ("mysql -u %s -h %s -e 'select max(sms_id) "
"from %s.smsinbox' -p%s") % (user, sb_host, name, password)
# print command
p = subprocess.Popen(command,
stdout=subprocess.PIPE,
shell=True,
stderr=subprocess.STDOUT)
out, err = p.communicate()
try:
max_sms_id = out.split('\n')[2]
except IndexError:
print("Index Error")
print(out, err)
sys.exit()
# max_sms_id = 4104000
print("Max sms_id from sandbox smsinbox:", max_sms_id)
print("done\n")
# dump table entries
print("Dumping tables from gsm host to sandbox dump file ...", )
f_dump = sqldumpsdir + "mirrordump.sql"
command = (
"mysqldump -h %s --skip-add-drop-table --no-create-info --single-transaction "
"-u %s %s smsinbox --where='sms_id > %s' > %s ") % (
gsm_host, user, name, max_sms_id, f_dump)
print(command)
p = subprocess.Popen(command,
stdout=subprocess.PIPE,
shell=True,
stderr=subprocess.STDOUT)
out, err = p.communicate()
if out or err:
print(">> Error on dyna mysql > dump")
print(out, err)
else:
print(">> No errors")
print('done\n')
# write to local db
print("Dumping tables from gsm host to sandbox dump file ...", )
command = "mysql -h %s -u %s %s < %s" % (sb_host, user, name, f_dump)
print(command)
p = subprocess.Popen(command,
stdout=subprocess.PIPE,
shell=True,
stderr=subprocess.STDOUT)
out, err = p.communicate()
if out or err:
print(">> Error on sandbox mysql < dump")
print(out, err)
else:
print(">> No errors")
print('done\n')
# delete dump file
print("Deleting dump file ...")
command = "rm %s" % (f_dump)
p = subprocess.Popen(command,
stdout=subprocess.PIPE,
shell=True,
stderr=subprocess.STDOUT)
out, err = p.communicate()
print('done\n')
def import_sql_file_to_dyna(table, max_inbox_id, max_index_last_copied):
"""
- The process of importing sql file to dyna is a function that process the exporting of data from sanbox and importing data to dyna smsibox2.
This function also change the value of the index in user_inbox_index.tmp.
:param table: Name of the table for smsinbox
:param max_inbox_id: Index id of the not yet copied data in dyna
:param max_index_last_copied: Index id that stored from the user_inbox_index.tmp
:type table: str
:type max_inbox_id: int
:type max_index_last_copied: int
"""
print("importing to dyna tables")
print(table)
sc = mem.server_config()
smsdb_host = sc["resource"]["smsdb"]
smsdb2_host = sc["resource"]["smsdb2"]
host_ip = sc["hosts"][smsdb_host]
host_ip2 = sc["hosts"][smsdb2_host]
password = sc["db"]["password"]
smsdb_name = sc["db"]["smsdb_name"]
logsdir = sc["fileio"]["logsdir"]
copy_query = (
"SELECT t1.ts_sms as 'timestamp', t2.sim_num, t1.sms_msg, "
"'UNREAD' as read_status, 'W' AS web_flag FROM smsinbox_%s t1 "
"inner join (select mobile_id, sim_num from %s_mobile) t2 "
"on t1.mobile_id = t2.mobile_id where t1.gsm_id !=1 "
"and t1.inbox_id < %d and t1.inbox_id > %d") % (
table, table[:-1], max_inbox_id, max_index_last_copied)
f_dump = logsdir + ("sandbox_%s_dump.sql" % (table))
# export files from the table and dump to a file
command = ("mysql -e \"%s\" -h%s %s -upysys_local -p%s --xml >"
" %s" % (copy_query, host_ip, smsdb_name, password, f_dump))
p = subprocess.Popen(command,
stdout=subprocess.PIPE,
shell=True,
stderr=subprocess.STDOUT)
out, err = p.communicate()
print(command)
# print err
import_query = ("LOAD XML LOCAL INFILE '%s' INTO TABLE smsinbox2" %
(f_dump))
command = ("mysql -e \"%s\" -h%s senslopedb -upysys_local -p%s") % (
import_query, host_ip2, password)
p = subprocess.Popen(command,
stdout=subprocess.PIPE,
shell=True,
stderr=subprocess.STDOUT)
out, err = p.communicate()
print(command)
# write new value in max_index_last_copied
tmpfile = logsdir + ("%s_inbox_index.tmp" % table)
f_index = open(tmpfile, "wb")
f_index.write(str(max_inbox_id))
f_index.close()
# delete dump file
command = "rm %s" % (f_dump)
p = subprocess.Popen(command,
stdout=subprocess.PIPE,
shell=True,
stderr=subprocess.STDOUT)
def simulate_gsm(network='simulate'):
"""
- The process simulate is a function that runs a gsm simulation of insert,update and check status of gsm.
:param network: gsm network and **Default** to **simulate**.
:type table: str
"""
print("Simulating GSM")
sc = mem.server_config()
mobile_nums_db = sc["resource"]["mobile_nums_db"]
smsinbox_sms = []
query = ("select sms_id, timestamp, sim_num, sms_msg from smsinbox "
"where web_flag not in ('0','-1') limit 1000")
smsinbox_sms = db.read(query=query, resource="sensor_data")
logger_mobile_sim_nums = static.get_mobiles('loggers', mobile_nums_db)
user_mobile_sim_nums = static.get_mobiles('users', mobile_nums_db)
gsm_id = 1
loggers_count = 0
users_count = 0
ts_stored = dt.today().strftime("%Y-%m-%d %H:%M:%S")
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 smsinbox_sms:
ts_sms = m[1]
sms_msg = m[3]
read_status = 0
if m[2] in logger_mobile_sim_nums.keys():
query_loggers += "('%s','%s',%d,'%s',%d,%d)," % (
ts_sms, ts_stored, logger_mobile_sim_nums[m[2]], sms_msg,
read_status, gsm_id)
# ltr_mobile_id = logger_mobile_sim_nums[m[2]]
loggers_count += 1
elif m[2] in user_mobile_sim_nums.keys():
query_users += "('%s','%s',%d,'%s',%d,%d)," % (
ts_sms, ts_stored, user_mobile_sim_nums[m[2]], sms_msg,
read_status, gsm_id)
users_count += 1
else:
# print 'Unknown number', m[2]
sms_id_unk.append(m[0])
continue
sms_id_ok.append(m[0])
query_loggers = query_loggers[:-1]
query_users = query_users[:-1]
print("Copying %d loggers item and %d users item with %d "
"unknown") % (loggers_count, users_count, len(sms_id_unk))
if len(sms_id_ok) > 0:
if loggers_count > 0:
db.write(query=query_loggers, resource="sms_data")
if users_count > 0:
db.write(query=query_users, resource="sms_data")
sms_id_ok = str(sms_id_ok).replace("L", "")[1:-1]
query = ("update smsinbox set web_flag = '0' "
"where sms_id in (%s);") % (sms_id_ok)
db.write(query=query, resource="sensor_data")
if len(sms_id_unk) > 0:
# print sms_id_unk
sms_id_unk = str(sms_id_unk).replace("L", "")[1:-1]
query = ("update smsinbox set web_flag = '-1' "
"where sms_id in (%s);") % (sms_id_unk)
db.write(query=query, resource="sensor_data")
sys.exit()
def main():
"""
- The process of running the whole gsmserver by checking if the gsmserver arguement is being initialize as gsm_id, table or network with respective agruement id.
.. todo::
1. To run the script **open** a terminal or bash
2. Set your terminal/bash path to **/centraserver/gsm/** .
3. Type inside the terminal/bash ""**python gsmserver.py** **-g**
*<gsm id (1,2,3...)>* **-t** *<smsinbox table (loggers or users)>*
**-n** *<network name (smart/globe/simulate)>* "".
4. Click Enter
"""
args = get_arguments()
gsm_modules = get_gsm_modules()
args.gsm_id = args.gsm_id - 1
if args.gsm_id not in gsm_modules.keys():
print(">> Error in gsm module selection (%s)" % (args.gsm_id))
sys.exit()
if gsm_modules[args.gsm_id]["port"] is None:
print(">> Error: missing information on gsm_module")
sys.exit()
print('Running gsm server ...')
gsm_info = gsm_modules[args.gsm_id]
gsm_info["pwr_on_pin"] = int(gsm_info["pwr_on_pin"])
gsm_info["ring_pin"] = int(gsm_info["ring_pin"])
gsm_info["id"] = int(gsm_info["id"])
#
if gsm_info['name'] == 'simulate':
simulate_gsm(gsm_info['network'])
sys.exit()
if "mocksend" in gsm_info['name']:
mock_send_server(gsm=None, gsm_info=gsm_info)
sc = mem.server_config()
gsm = None
try:
gsm = modem.GsmModem(gsm_info['port'], sc["serial"]["baudrate"],
gsm_info["pwr_on_pin"], gsm_info["ring_pin"])
except serial.SerialException:
print('**NO COM PORT FOUND**')
# serverstate = 'serial'
raise ValueError(">> Error: no com port found")
try:
run_server(gsm, gsm_info)
except modem.ResetException:
print("> Resetting system because of GSM failure")
gsm.reset()
sys.exit()
def main():
args = get_arguments()
dfeq = get_unprocessed()
sym = get_alert_symbol()
sites = get_sites()
dfg = sites.groupby('site_id')
eq_a = pd.DataFrame(columns=['site_id','eq_id','distance'])
EVENTS_TABLE = 'earthquake_events'
for i in dfeq.index:
cur = dfeq.loc[i]
mag, eq_lat, eq_lon,ts = cur.magnitude, cur.latitude, cur.longitude,cur.ts
critdist = get_crit_dist(mag)
if False in np.isfinite([mag,eq_lat,eq_lon]): #has NaN value in mag, lat, or lon
query = "update %s set processed = -1 where eq_id = %s " % (EVENTS_TABLE, i)
dynadb.write(query=query, resource="sensor_data")
continue
if mag < 4:
print ("> Magnitude too small: %d" % (mag))
query = "update %s set processed = 1 where eq_id = %s " % (EVENTS_TABLE,i)
dynadb.write(query=query, resource="sensor_data")
continue
else:
print ("> Magnitude reached threshold: %d" % (mag))
# magnitude is big enough to consider
sites = dfg.apply(get_distance_to_eq,eq_lat=eq_lat,eq_lon=eq_lon)
#tanggal weird values
sites = sites[sites.latitude>1]
crits = sites[sites.dist<=critdist]
if len(crits.site_id.values) < 1:
print ("> No affected sites. ")
query = "update %s set processed = 1, critical_distance = %s where eq_id = %s" % (EVENTS_TABLE,critdist,i)
dynadb.write(query=query, resource="sensor_data")
continue
else:
#merong may trigger
print (">> Possible sites affected: %d" % (len(crits.site_id.values)))
crits['ts'] = ts
crits['source'] = 'earthquake'
crits['trigger_sym_id'] = sym
crits['ts_updated'] = ts
crits['eq_id'] = i
crits['distance'] = critdist
eq_a = crits[['eq_id','site_id','distance']]
op_trig = crits[['ts','site_id','trigger_sym_id','ts_updated']]
# write to tables
# dynadb.df_write(DataTable("operational_triggers", op_trig), resource="sensor_data")
# dynadb.df_write(DataTable("earthquake_alerts", eq_a), resource="sensor_data")
query = "update %s set processed = 1, critical_distance = %s where eq_id = %s " % (EVENTS_TABLE,critdist,i)
# dynadb.write(query=query, resource="sensor_data")
print (">> Alert iniated.\n")
if not args.to_plot:
# plot not enabled
continue
sc = server_config()
output_path = "{}{}".format(os.getenv("HOME"),sc["fileio"]["eq_path"])
if os.path.isdir(output_path):
os.makedirs(output_path)
plot_map(output_path,sites,crits,mag, eq_lat, eq_lon,ts,critdist)
def main(site_code='', end='', Print=True, write_to_db=True,
print_plot=False, save_plot=True, days='', is_command_line_run=True):
"""Computes alert and plots rainfall data.
Args:
site_code (list): Site codes to compute rainfall analysis for. Optional.
Defaults to empty string which will compute alert
and plot for all sites.
Print (bool): To print plot and summary of alerts. Optional. Defaults to
True.
end (datetime): Timestamp of alert and plot to be computed. Optional.
Defaults to current timestamp.
Returns:
str: Json format of cumulative rainfall and alert per site.
"""
start_time = datetime.now()
qdb.print_out(start_time)
if site_code == '':
if is_command_line_run:
site_code = sys.argv[1].lower()
site_code = site_code.replace(' ', '').split(',')
else:
site_code = site_code.replace(' ', '').split(',')
if end == '':
try:
end = pd.to_datetime(sys.argv[2])
except:
end = datetime.now()
else:
end = pd.to_datetime(end)
output_path = os.path.abspath(os.path.join(os.path.dirname(__file__),
'../../..'))
sc = mem.server_config()
#creates directory if it doesn't exist
if (sc['rainfall']['print_plot'] or sc['rainfall']['print_summary_alert']) and Print:
if not os.path.exists(output_path+sc['fileio']['rainfall_path']):
os.makedirs(output_path+sc['fileio']['rainfall_path'])
# setting monitoring window
if days != '':
sc['rainfall']['rt_window_length'] = days
end, start, offsetstart = get_rt_window(float(sc['rainfall']['rt_window_length']),
float(sc['rainfall']['roll_window_length']), end=end)
tsn=end.strftime("%Y-%m-%d_%H-%M-%S")
# 4 nearest rain gauges of each site with threshold and distance from site
gauges = rainfall_gauges()
if site_code != '':
gauges = gauges[gauges.site_code.isin(site_code)]
gauges['site_id'] = gauges['site_id'].apply(lambda x: float(x))
trigger_symbol = mem.get('df_trigger_symbols')
trigger_symbol = trigger_symbol[trigger_symbol.trigger_source == 'rainfall']
trigger_symbol['trigger_sym_id'] = trigger_symbol['trigger_sym_id'].apply(lambda x: float(x))
site_props = gauges.groupby('site_id')
summary = site_props.apply(ra.main, end=end, sc=sc,
trigger_symbol=trigger_symbol, write_to_db=write_to_db)
summary = summary.reset_index(drop=True)[['site_id', 'site_code',
'1D cml', 'half of 2yr max', '3D cml', '2yr max',
'DataSource', 'alert']]
if Print == True:
if sc['rainfall']['print_summary_alert']:
summary.to_csv(output_path+sc['fileio']['rainfall_path'] +
'SummaryOfRainfallAlertGenerationFor'+tsn+'.csv',
sep=',', mode='w', index=False)
if sc['rainfall']['print_plot'] or print_plot:
rain_data = site_props.apply(rp.main, offsetstart=offsetstart,
tsn=tsn, save_plot=save_plot, sc=sc,
start=start, output_path=output_path,
end=end).reset_index(drop=True)
summary = pd.merge(summary, rain_data, on='site_id',
validate='1:1')
summary_json = summary.to_json(orient="records")
qdb.print_out("runtime = %s" %(datetime.now()-start_time))
return summary_json
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)
import MySQLdb, time
from time import localtime, strftime
import pandas as pd
#import __init__
import itertools
import os
from sqlalchemy import create_engine
from dateutil.parser import parse
sys.path.append(os.path.dirname(os.path.realpath(__file__)))
import analysis.querydb as qdb
import volatile.memory as mem
columns = ['tsm_id', 'presence', 'last_data', 'ts_updated', 'diff_days']
df = pd.DataFrame(columns=columns)
sc = mem.server_config()
def get_tsm_sensors():
localdf = 0
# db = MySQLdb.connect(host = '192.168.150.253', user = '******', passwd = 'senslope', db = 'senslopedb')
query = "select tsm_id, tsm_name from senslopedb.tsm_sensors where date_deactivated is null"
# localdf = psql.read_sql(query, db)
localdf = qdb.get_db_dataframe(query)
return localdf
def get_data(lgrname):
db = MySQLdb.connect(host='192.168.150.253',
user='******',
passwd='senslope',
def write_outbox(message=None, recipients=None, table=None,
resource="sms_data", with_mobile_id=False):
"""
**Description:**
-The write outbox message to database is a function that insert message to smsoutbox with
timestamp written,message source and mobile id.
:param message: The message that will be sent to the recipients.
:param recipients: The number of the recipients.
:param gsm_id: The gsm id .
:param table: table use of the number.
:type message: str
:type recipients: str
:type recipients: int
:type table: str
:returns: N/A
"""
# if table == '':
# print "Error: No table indicated"
# raise ValueError
# return
sc = mem.server_config()
mc = mem.get_handle()
host = sc['resource']['smsdb']
tsw = dt.today().strftime("%Y-%m-%d %H:%M:%S")
if not message:
raise ValueError("No message specified for sending")
if type(recipients) == type(None):
raise ValueError("No recipients specified for sending")
elif type(recipients).__name__ == 'str':
recipients = recipients.split(",")
if not table:
table_name = check_number_in_table(recipients[0])
if not table_name:
print("No record for '%s" % (recipients[0]))
return
else:
table_name = table
query = ("insert into smsoutbox_%s (ts_written,sms_msg,source) VALUES "
"('%s','%s','central')") % (table_name,tsw,message)
outbox_id = dbio.write(query=query, identifier="womtdb",
last_insert=True, host=host, resource=resource)[0][0]
query = ("INSERT INTO smsoutbox_%s_status (outbox_id,mobile_id,gsm_id)"
" VALUES ") % (table_name[:-1])
if with_mobile_id:
recipients.loc[:, 'outbox_id'] = outbox_id
query += str(list(recipients[['outbox_id', 'mobile_id', 'gsm_id']].to_records(index=False)))[1:-1]
else:
table_mobile = static.get_mobiles(table_name, host)
def_gsm_id = mc.get(table_name[:-1] + "_mobile_def_gsm_id")
for r in recipients:
try:
mobile_id = table_mobile[r]
gsm_id = def_gsm_id[mobile_id]
query += "(%d, %d, %d)," % (outbox_id, mobile_id, gsm_id)
except KeyError:
print (">> Error: Possible key error for", r)
continue
query = query[:-1]
dbio.write(query=query, identifier="womtdb", last_insert=False, host=host,
resource=resource)
