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)