def updateSimNumTable(name, sim_num, date_activated):
db, cur = dbio.SenslopeDBConnect('local')
while True:
try:
query = """select sim_num from senslopedb.site_column_sim_nums
where name = '%s' """ % (name)
a = cur.execute(query)
if a:
out = cur.fetchall()
if (sim_num == out[0][0]):
print ">> Number already in database", name, out[0][0]
return
break
else:
print '>> Number not in database', sim_num
return
break
except MySQLdb.OperationalError:
print '1.',
raise KeyboardInterrupt
query = """INSERT INTO senslopedb.site_column_sim_nums
(name,sim_num,date_activated)
VALUES ('%s','%s','%s')""" % (name, sim_num, date_activated)
dbio.commitToDb(query, 'updateSimNumTable')
def sendAlertMessage():
# check due alert messages
ts_due = dt.today()
query = "select alert_id,alertmsg from smsalerts where ack = 'none' and ts_set <= '%s'" % (
ts_due.strftime("%Y-%m-%d %H:%M:%S"))
alertmsg = dbio.querydatabase(query, 'SendAlertMessage')
if alertmsg == None:
print 'No alertmsg set for sending'
return
message = 'Alert ID %d:\n%s\n' % (alertmsg[0][0], alertmsg[0][1])
message += 'Text "ACK <alert id> <valid/invalid> <remarks>" to acknowledge'
# send to alert staff
contacts = getAlertStaffNumbers()
for item in contacts:
# for multile contacts
for i in item[1].split(','):
server.WriteOutboxMessageToDb(message, i)
# set alert to 15 mins later
ts_due = ts_due + td(seconds=60 * 15)
query = "update smsalerts set ts_set = '%s' where alert_id = %s" % (
ts_due.strftime("%Y-%m-%d %H:%M:%S"), alertmsg[0][0])
dbio.commitToDb(query, 'checkalertmsg')
def WriteRawSmsToDb(msglist, sensor_nums):
query = "INSERT INTO smsinbox (timestamp,sim_num,sms_msg,read_status,web_flag) VALUES "
for m in msglist:
if sensor_nums.find(m.simnum[-10:]) == -1:
# if re.search(m.simnum[-10:],sensor_nums):
web_flag = 'W'
print m.data[:20]
if cfg.config().mode.script_mode == 'gsmserver':
ret = dsll.sendReceivedGSMtoDEWS(str(m.dt.replace("/", "-")),
m.simnum, m.data)
#if the SMS Message was sent successfully to the web socket server then,
# change web_flag to 'WS' which means "Websocket Server Sent"
if ret == 0:
web_flag = 'WSS'
else:
web_flag = 'S'
query += "('%s','%s','%s','UNREAD','%s')," % (str(
m.dt.replace("/", "-")), str(
m.simnum), str(m.data.replace("'", "\"")), web_flag)
# query += "('" + str(m.dt.replace("/","-")) + "','" + str(m.simnum) + "','"
# query += str(m.data.replace("'","\"")) + "','UNREAD'),"
# just to remove the trailing ','
query = query[:-1]
# print query
dbio.commitToDb(query, "WriteRawSmsToDb", instance='GSM')
def RecordManualWeather(mw_text):
# print gnd_meas
dbio.createTable("manualweather", "manualweather")
query = "INSERT IGNORE INTO manualweather (timestamp, meas_type, site_id, observer_name, weatherdesc) VALUES " + mw_text
dbio.commitToDb(query, 'RecordManualWeather')
def updateSimNumTable(name, sim_num, date_activated):
db, cur = dbio.SenslopeDBConnect('local')
query = """INSERT IGNORE INTO site_column_sim_nums (name,sim_num,date_activated)
VALUES ('%s','%s','%s')""" % (name.upper(), sim_num,
date_activated)
dbio.commitToDb(query, 'updateSimNumTable')
def ProcessStats(line, txtdatetime):
print 'Site status: ' + line
try:
msgtable = "stats"
items = re.match(
r'(\w{4})[-](\d{1,2}[.]\d{02}),(\d{01}),(\d{1,2})/(\d{1,2}),#(\d),(\d),(\d{1,2}),(\d)[*](\d{10})',
line)
site = items.group(1)
voltage = items.group(2)
chan = items.group(3)
att = items.group(4)
retVal = items.group(5)
msgs = items.group(6)
sim = items.group(7)
csq = items.group(8)
sd = items.group(9)
#getting date and time
msgdatetime = line[-10:]
print 'date & time: ' + msgdatetime
col_list = cfg.get("Misc", "AdjustColumnTimeOf").split(',')
if site in col_list:
msgdatetime = txtdatetime
print "date & time adjusted " + msgdatetime
else:
print 'date & time no change'
except IndexError and AttributeError:
print '\n>> Error: Status message format is not recognized'
print line
return
except:
print '\n>>Error: Status message format unknown ' + line
return
dbio.createTable(str(msgtable), "stats")
try:
query = """INSERT INTO %s (timestamp,site,voltage,chan,att,retVal,msgs,sim,csq,sd)
VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % (
str(msgtable), str(msgdatetime), str(site), str(voltage),
str(chan), str(att), str(retVal), str(msgs), str(sim), str(csq),
str(sd))
except:
print '>> Error writing status data to database. ' + line
return
dbio.commitToDb(query, 'ProcessStats')
print 'End of Process status data'
def processAckToAlert(msg):
try:
alert_id = re.search("(?<=K )\d+(?= )", msg.data,
re.IGNORECASE).group(0)
except:
errmsg = "Error in parsing alert id. Please try again"
server.WriteOutboxMessageToDb(errmsg, msg.simnum)
return True
# check to see if message from chatter box
try:
name = qsi.getNameofStaff(msg.simnum)
if re.search("server", name.lower()):
name = re.search("(?>=-).+(?= from)").group(0)
except:
try:
chat_footer = re.search("-[A-Za-z ]+ from .+$", msg.data).group(0)
name = re.search("(?<=-)[A-Za-z]+(?= )", chat_footer).group(0)
msg.data = msg.data.replace(chat_footer, "")
except:
errmsg = "You are not permitted to acknowledge."
server.WriteOutboxMessageToDb(errmsg, msg.simnum)
return True
try:
remarks = re.search("(?<=\d ).+(?=($|\r|\n))", msg.data,
re.IGNORECASE).group(0)
except:
errmsg = "Please put in your remarks."
server.WriteOutboxMessageToDb(errmsg, msg.simnum)
return True
try:
alert_status = re.search("(in)*valid(ating)*", remarks,
re.IGNORECASE).group(0)
remarks = remarks.replace(alert_status, "").strip()
except:
errmsg = "Please put in the alert status validity. i.e (VALID, INVALID, VALIDATING)"
server.WriteOutboxMessageToDb(errmsg, msg.simnum)
return True
query = "update smsalerts set ack = '%s', ts_ack = '%s', remarks = '%s', alertstat = '%s' where alert_id = %s" % (
name, msg.dt, remarks, alert_status, alert_id)
dbio.commitToDb(query, processAckToAlert)
contacts = getAlertStaffNumbers()
message = "Alert ID %s ACK by %s on %s\nStatus: %s\nRemarks: %s" % (
alert_id, name, msg.dt, alert_status, remarks)
tsw = dt.today().strftime("%Y-%m-%d %H:%M:%S")
for item in contacts:
message = message.replace("ALERT", "AL3RT")
server.WriteOutboxMessageToDb(message, item[1])
return True
def RecordGroundMeasurements(gnd_meas):
# print gnd_meas
# dbio.createTable("gndmeas","gndmeas")
query = "INSERT INTO gndmeas (timestamp, meas_type, site_id, observer_name, crack_id, meas, weather) VALUES " + gnd_meas
query += "ON DUPLICATE KEY UPDATE meas = values(meas), observer_name = values(observer_name), weather = values(weather);"
# print query
dbio.commitToDb(query, 'RecordGroundMeasurements')
def writeAlertToDb(alertmsg):
dbio.createTable('smsalerts', 'smsalerts')
today = dt.today().strftime("%Y-%m-%d %H:%M:%S")
query = "insert into smsalerts (ts_set,alertmsg,remarks) values ('%s','%s','none')" % (
today, alertmsg)
print query
dbio.commitToDb(query, 'writeAlertToDb', 'LOCAL')
def updateLastMsgReceivedTable(txtdatetime, name, sim_num, msg):
query = """insert into senslopedb.last_msg_received
(timestamp,name,sim_num,last_msg)
values ('%s','%s','%s','%s')
on DUPLICATE key update
timestamp = '%s',
sim_num = '%s',
last_msg = '%s'""" % (txtdatetime, name, sim_num, msg,
txtdatetime, sim_num, msg)
dbio.commitToDb(query, 'updateLastMsgReceivedTable')
def WriteOutboxMessageToDb(message, recepients, send_status='UNSENT'):
for r in recepients.split(","):
gsm_id = getGsmId(r)
if gsm_id == -1:
continue
else:
query = "INSERT INTO smsoutbox (timestamp_written,recepients,sms_msg,send_status,gsm_id) VALUES "
tsw = dt.today().strftime("%Y-%m-%d %H:%M:%S")
query += "('%s','%s','%s','%s','%s')" % (tsw, r, message,
send_status, gsm_id)
print query
dbio.commitToDb(query, "WriteOutboxMessageToDb", 'gsm')
def ProcessRain(line, sender):
#msg = message
line = re.sub("[^A-Z0-9,\/:\.\-]", "", line)
print 'Weather data: ' + line
if len(line.split(',')) > 9:
line = re.sub(",(?=$)", "", line)
line = re.sub("(?<=,)(?=(,|$))", "NULL", line)
line = re.sub("(?<=,)NULL(?=,)", "0.0", line)
print line
try:
msgtable = line.split(",")[0]
msgdatetime = re.search("\d{02}\/\d{02}\/\d{02},\d{02}:\d{02}:\d{02}",
line).group(0)
txtdatetime = dt.strptime(msgdatetime, '%m/%d/%y,%H:%M:%S')
txtdatetime = txtdatetime.strftime('%Y-%m-%d %H:%M:00')
# data = items.group(3)
data = line.split(",", 3)[3]
except IndexError and AttributeError:
print '\n>> Error: Rain message format is not recognized'
print line
return
except KeyboardInterrupt:
print '\n>>Error: Weather message format unknown ' + line
return
updateSimNumTable(msgtable, sender, txtdatetime[:10])
dbio.createTable(str(msgtable), "weather")
try:
query = """INSERT INTO %s (timestamp,name,temp,wspd,wdir,rain,batt,csq) VALUES ('%s','%s',%s)""" % (
msgtable.lower(), txtdatetime, msgtable, data)
except:
print '>> Error writing weather data to database. ' + line
return
try:
dbio.commitToDb(query, 'ProcesRain')
except MySQLdb.ProgrammingError:
print query[:-2]
dbio.commitToDb(query[:-2] + ')', 'ProcessRain')
print 'End of Process weather data'
def ProcessPiezometer(line, sender):
#msg = message
print 'Piezometer data: ' + line
try:
#PUGBPZ*13173214*1511091800
linesplit = line.split('*')
msgname = linesplit[0].lower()
msgname = re.sub("due", "", msgname)
msgname = msgname + 'pz'
print 'msg_name: ' + msgname
data = linesplit[1]
msgid = int(('0x' + data[:4]), 16)
p1 = int(('0x' + data[4:6]), 16) * 100
p2 = int(('0x' + data[6:8]), 16)
p3 = int(('0x' + data[8:10]), 16) * .01
piezodata = p1 + p2 + p3
t1 = int(('0x' + data[10:12]), 16)
t2 = int(('0x' + data[12:14]), 16) * .01
tempdata = t1 + t2
try:
txtdatetime = dt.strptime(
linesplit[2], '%y%m%d%H%M%S').strftime('%Y-%m-%d %H:%M:00')
except ValueError:
txtdatetime = dt.strptime(
linesplit[2], '%y%m%d%H%M').strftime('%Y-%m-%d %H:%M:00')
except IndexError and AttributeError:
print '\n>> Error: Piezometer message format is not recognized'
print line
return
except ValueError:
print '>> Error: Possible conversion mismatch ' + line
return
# try:
dbio.createTable(str(msgname), "piezo")
try:
query = """INSERT INTO %s(timestamp, name, msgid, freq, temp ) VALUES ('%s','%s', %s, %s, %s )""" % (
msgname, txtdatetime, msgname, str(msgid), str(piezodata),
str(tempdata))
# print query
except ValueError:
print '>> Error writing query string.',
return
dbio.commitToDb(query, 'ProcessPiezometer')
print 'End of Process Piezometer data'
def WriteTwoAccelDataToDb(dlist, msgtime):
query = """INSERT IGNORE INTO %s (timestamp,id,msgid,xvalue,yvalue,zvalue,batt) VALUES """ % str(
dlist[0][0].lower())
dbio.createTable(dlist[0][0], "sensor v2")
for item in dlist:
timetowrite = str(item[1])
query = query + """('%s',%s,%s,%s,%s,%s,%s),""" % (
timetowrite, str(item[2]), str(item[3]), str(item[4]), str(
item[5]), str(item[6]), str(item[7]))
query = query[:-1]
# print len(query)
dbio.commitToDb(query, 'WriteTwoAccelDataToDb')
def WriteSomsDataToDb(dlist, msgtime):
query = """INSERT IGNORE INTO %s (timestamp,id,msgid,mval1,mval2) VALUES """ % str(
dlist[0][0].lower())
print "site_name", str(dlist[0][0])
dbio.createTable(str(dlist[0][0]), "soms")
for item in dlist:
timetowrite = str(item[1])
query = query + """('%s',%s,%s,%s,%s),""" % (timetowrite, str(
item[2]), str(item[3]), str(item[4]), str(item[5]))
query = query[:-1]
query = query.replace("nan", "NULL")
dbio.commitToDb(query, 'WriteSomsDataToDb')
def ProcessCoordinatorMsg(coordsms, num):
print ">> Coordinator message received"
print coordsms
dbio.createTable("coordrssi", "coordrssi")
coordsms = re.sub("(?<=,)(?=(,|$))", "NULL", coordsms)
try:
datafield = coordsms.split('*')[1]
timefield = coordsms.split('*')[2]
timestamp = dt.strptime(timefield,
"%y%m%d%H%M%S").strftime("%Y-%m-%d %H:%M:%S")
smstype = datafield.split(',')[0]
# process rssi parameters
if smstype == "RSSI":
site_name = datafield.split(',')[1]
rssi_string = datafield.split(',', 2)[2]
print rssi_string
# format is
# <router name>,<rssi value>,...
query = "INSERT IGNORE INTO coordrssi (timestamp, site_name, router_name, rssi_val) VALUES ("
tuples = re.findall("[A-Z]+,\d+", rssi_string)
for item in tuples:
query += "'" + timestamp + "',"
query += "'" + site_name + "',"
query += "'" + item.split(',')[0] + "',"
query += item.split(',')[1] + "),("
query = query[:-2]
print query
dbio.commitToDb(query, 'ProcessCoordinatorMsg')
return True
else:
print ">> Processing coordinator weather"
except IndexError:
print "IndexError: list index out of range"
return False
except:
print ">> Unknown Error", coordsms
return False
def logRuntimeStatus(script_name, status):
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
(timestamp,script_name,status)
values ('%s','%s','%s')
""" % (logtimestamp, script_name, status)
dbio.commitToDb(query, 'logRuntimeStatus', cfg.config().mode.logtoinstance)
def updateShiftTags():
# remove tags to old shifts
today = dt.today().strftime("%Y-%m-%d %H:%M:%S")
print 'Updating shift tags for', today
query = "update senslopedb.dewslcontacts set grouptags = replace(grouptags,',alert-mon','') where grouptags like '%alert-mon%'"
dbio.commitToDb(query, 'updateShiftTags')
# update the tags of current shifts
query = """
update dewslcontacts as t1,
( select timestamp,iompmt,iompct,oomps,oompmt,oompct from monshiftsched
where timestamp < '%s'
order by timestamp desc limit 1
) as t2
set t1.grouptags = concat(t1.grouptags,',alert-mon')
where t1.nickname = t2.iompmt or
t1.nickname = t2.iompct or
t1.nickname = t2.oomps or
t1.nickname = t2.oompmt or
t1.nickname = t2.oompct
""" % (today)
dbio.commitToDb(query, 'updateShiftTags')
def main():
try:
if sys.argv[1] == 'test': writetodb = False
else: writetodb = True
except:
writetodb = True
c = cfg.config()
dbio.createTable("runtimelog", "runtime")
server.logRuntimeStatus("alert", "checked")
print '>> Checking for alert sms'
alertmsg = server.CheckAlertMessages()
print alertmsg
if alertmsg:
# server.WriteOutboxMessageToDb(alertmsg,c.smsalert.smartnum)
# server.WriteOutboxMessageToDb(alertmsg,c.smsalert.globenum)
query = """select nickname, numbers from dewslcontacts where grouptags like '%alert%'"""
contacts = dbio.querydatabase(query, 'checkalert')
# print contacts
query = "INSERT INTO smsoutbox (timestamp_written,recepients,sms_msg,send_status) VALUES "
tsw = dt.today().strftime("%Y-%m-%d %H:%M:%S")
for item in contacts:
message = 'SENSOR ALERT:\n%s' % (alertmsg)
message = message.replace("ALERT", "AL3RT")
query += "('%s','%s','%s','UNSENT')," % (tsw, item[1], message)
query = query[:-1]
if writetodb: dbio.commitToDb(query, 'checkalertmsg', 'GSM')
else: print query
print 'done'
else:
print '>> No alert msg read.'
def ProcessColumn(line, txtdatetime, sender):
msgtable = line[0:4]
print 'SITE: ' + msgtable
##msgdata = line[5:len(line)-11] #data is 6th char, last 10 char are date
msgdata = (line.split('*'))[1]
print 'raw data: ' + msgdata
#getting date and time
#msgdatetime = line[-10:]
try:
msgdatetime = (line.split('*'))[2][:10]
print 'date & time: ' + msgdatetime
except:
print '>> Date and time defaults to SMS not sensor data'
msgdatetime = txtdatetime
# col_list = cfg.get("Misc","AdjustColumnTimeOf").split(',')
# if msgtable == 'PUGB':
# msgdatetime = txtdatetime
# print "date & time adjusted " + msgdatetime
# else:
msgdatetime = dt.strptime(msgdatetime,
'%y%m%d%H%M').strftime('%Y-%m-%d %H:%M:00')
# print 'date & time no change'
dlen = len(msgdata) #checks if data length is divisible by 15
#print 'data length: %d' %dlen
nodenum = dlen / 15
#print 'number of nodes: %d' %nodenum
if dlen == 0:
print 'Error: There is NO data!'
return
elif ((dlen % 15) == 0):
#print 'Data has correct length!'
valid = dlen
else:
print 'Warning: Excess data will be ignored!'
valid = nodenum * 15
updateSimNumTable(msgtable, sender, msgdatetime[:10])
query = query = """INSERT IGNORE INTO %s (timestamp,id,xvalue,yvalue,zvalue,mvalue) VALUES """ % (
str(msgtable.lower()))
try:
i = 0
while i < valid:
#NODE ID
#parsed msg.data - NODE ID:
node_id = int('0x' + msgdata[i:i + 2], 16)
i = i + 2
#X VALUE
#parsed msg.data - TEMPX VALUE:
tempx = int('0x' + msgdata[i:i + 3], 16)
i = i + 3
#Y VALUE
#parsed msg.data - TEMPY VALUE:
tempy = int('0x' + msgdata[i:i + 3], 16)
i = i + 3
#Z VALUE
#parsed msg.data - ZVALUE:
tempz = int('0x' + msgdata[i:i + 3], 16)
i = i + 3
#M VALUE
#parsed msg.data - TEMPF VALUE:
tempf = int('0x' + msgdata[i:i + 4], 16)
i = i + 4
valueX = tempx
if valueX > 1024:
valueX = tempx - 4096
valueY = tempy
if valueY > 1024:
valueY = tempy - 4096
valueZ = tempz
if valueZ > 1024:
valueZ = tempz - 4096
valueF = tempf #is this the M VALUE?
query = query + """('%s',%s,%s,%s,%s,%s),""" % (
str(msgdatetime), str(node_id), str(valueX), str(valueY),
str(valueZ), str(valueF))
print "%s\t%s\t%s\t%s\t%s" % (str(node_id), str(valueX),
str(valueY), str(valueZ),
str(valueF))
query = query[:-1]
# print query
if i != 0:
dbio.createTable(str(msgtable), "sensor v1")
dbio.commitToDb(query, 'ProcessColumn')
SpawnAlertGen(msgtable)
except KeyboardInterrupt:
print '\n>>Error: Unknown'
raise KeyboardInterrupt
return
except ValueError:
print '\n>>Error: Unknown'
return
def ProcessEarthquake(msg):
line = msg.data.upper()
print "Processing earthquake data"
print line
dbio.createTable('earthquake', 'earthquake')
#find date
if re.search("\d{1,2}\w+201[6789]", line):
datestr_init = re.search("\d{1,2}\w+201[6789]", msg.data).group(0)
pattern = ["%d%B%Y", "%d%b%Y"]
datestr = None
for p in pattern:
try:
datestr = dt.strptime(datestr_init, p).strftime("%Y-%m-%d")
break
except:
print ">> Error in datetime conversion", datestr, "for pattern", p
if datestr == None:
return False
else:
print ">> No date string recognized"
return False
#find time
if re.search("\d{1,2}[:\.]\d{1,2} *[AP]M", line):
timestr = re.search("\d{1,2}[:\.]\d{1,2} *[AP]M", line).group(0)
timestr = timestr.replace(" ", "").replace(".", ":")
try:
timestr = dt.strptime(timestr, "%I:%M%p").strftime("%H:%M:00")
except:
print ">> Error in datetime conversion", timestr
return False
else:
print ">> No time string recognized"
return False
datetimestr = datestr + ' ' + timestr
#find magnitude
if re.search("((?<=M[SBLVOW]\=)|(?<=M\=)|(?<=MLV\=))\d+\.\d+(?= )", line):
magstr = re.search(
"((?<=M[SBLVOW]\=)|(?<=M\=)|(?<=MLV\=))\d+\.\d+(?= )",
line).group(0)
else:
print ">> No magnitude string recognized"
magstr = 'NULL'
#find depth
if re.search("(?<=D\=)\d+(?=K*M)", line):
depthstr = re.search("(?<=D\=)\d+(?=K*M)", line).group(0)
else:
print ">> No depth string recognized"
depthstr = 'NULL'
#find latitude
if re.search("\d+[\.\:]\d+(?=N)", line):
latstr = re.search("\d+[\.\:]\d+(?=N)", line).group(0)
else:
print ">> No latitude string recognized"
latstr = 'NULL'
#find longitude
if re.search("\d+[\.\:]\d+(?=E)", line):
longstr = re.search("\d+[\.\:]\d+(?=E)", line).group(0)
else:
print ">> No longitude string recognized"
longstr = 'NULL'
#find epicenter distance
if re.search("(?<=OR )\d+(?=KM)", line):
diststr = re.search("(?<=OR )\d+(?=KM)", line).group(0)
else:
print ">> No distance string recognized"
diststr = 'NULL'
# find heading
if re.search("[NS]\d+[EW]", line):
headstr = re.search("[NS]\d+[EW]", line).group(0)
else:
print ">> No heading string recognized"
headstr = 'NULL'
# find Municipality
if re.search("(?<=OF )[A-Z ]+(?= \()", line):
munistr = re.search("(?<=OF )[A-Z ]+(?= \()", line).group(0)
else:
print ">> No municipality string recognized"
munistr = 'NULL'
# find province
if re.search("(?<=\()[A-Z ]+(?=\))", line):
provistr = re.search("(?<=\()[A-Z ]+(?=\))", line).group(0)
else:
print ">> No province string recognized"
provistr = 'NULL'
# find issuer
if re.search("(?<=\<)[A-Z]+(?=\>)", line):
issuerstr = re.search("(?<=\<)[A-Z]+(?=\>)", line).group(0)
else:
print ">> No issuer string recognized"
issuerstr = 'NULL'
query = "INSERT INTO senslopedb.earthquake (timestamp, mag, depth, lat, longi, dist, heading, municipality, province, issuer) VALUES ('%s',%s,%s,%s,%s,%s,'%s','%s','%s','%s') ON DUPLICATE KEY UPDATE mag=mag, depth=depth, lat=lat, longi=longi, dist=dist, heading=heading, municipality=municipality, province=province, issuer=issuer;" % (
datetimestr, magstr, depthstr, latstr, longstr, diststr, headstr,
munistr, provistr, issuerstr)
print query
query.replace("'NULL'", "NULL")
dbio.commitToDb(query, 'earthquake')
# subprocess.Popen(["python",cfg.config().fileio.eqprocfile])
exec_line = "~/anaconda2/bin/python %s > ~/scriptlogs/earthquakescript.txt 2>&1" % (
cfg.config().fileio.eqprocfile)
p = subprocess.Popen(exec_line,
stdout=subprocess.PIPE,
shell=True,
stderr=subprocess.STDOUT)
return True
def ProcessARQWeather(line, sender):
#msg = message
print 'ARQ Weather data: ' + line
line = re.sub("(?<=\+) (?=\+)", "NULL", line)
try:
# ARQ+1+3+4.143+4.128+0.0632+5.072+0.060+0000+13+28.1+75.0+55+150727/160058
#table name
linesplit = line.split('+')
msgname = checkNameOfNumber(sender).lower()
if msgname:
print ">> Number registered as", msgname
msgname_contact = msgname
msgname = msgname + 'w'
# else:
# print ">> New number", sender
# msgname = ''
r15m = int(linesplit[1]) * 0.5
r24h = int(linesplit[2]) * 0.5
batv1 = linesplit[3]
batv2 = linesplit[4]
current = linesplit[5]
boostv1 = linesplit[6]
boostv2 = linesplit[7]
charge = linesplit[8]
csq = linesplit[9]
if csq == '':
csq = '0'
temp = linesplit[10]
hum = linesplit[11]
flashp = linesplit[12]
txtdatetime = dt.strptime(
linesplit[13], '%y%m%d/%H%M%S').strftime('%Y-%m-%d %H:%M:00')
updateSimNumTable(msgname_contact, sender, txtdatetime[:8])
# print str(r15m),str(r24h),batv1, batv2, current, boostv1, boostv2, charge, csq, temp, hum, flashp,txtdatetime
except IndexError and AttributeError:
print '\n>> Error: Rain message format is not recognized'
print line
return
except ValueError:
print '>> Error: Possible conversion mismatch ' + line
return
if msgname:
dbio.createTable(str(msgname), "arqweather")
else:
print ">> Error: Number does not have station name yet"
return
try:
query = """INSERT INTO %s (timestamp,name,r15m, r24h, batv1, batv2, cur, boostv1, boostv2, charge, csq, temp, hum, flashp)
VALUES ('%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""" % (
msgname, txtdatetime, msgname, r15m, r24h, batv1, batv2, current,
boostv1, boostv2, charge, csq, temp, hum, flashp)
# print query
except ValueError:
print '>> Error writing query string.',
return
dbio.commitToDb(query, 'ProcessARQWeather')
print 'End of Process ARQ weather data'
if write_to_db:
table_dict = {"smsinbox": 1, "smsoutbox": 2}
stat_dict = {"undefined": 0, "read": 1, "unread": 2, "sent": 3,
"unsent": 4, "fatal error": 5, "read-fail": 6, "read-success": 7}
col_dict = {"undefined": 0, "read_status": 1, "send_status": 2}
query = ("insert into item_count_logs (`table`, `ts_end`, `ts_start`, "
"`stat`, `stat_col`, `count`) values (%d, '%s', '%s', %d, %d, "
"%d)" % (table_dict[table], ts_end, ts_start, stat_dict[stat],
col_dict[stat_col], item_count))
if pq:
print "Write query:", query
dbio.commitToDb(query, 'item_count', dbinstance)
def get_arguments():
parser = argparse.ArgumentParser(description=("Run performance "
"monitoring routines [-options]"))
parser.add_argument("-t", "--table",
help="table to count items from")
parser.add_argument("-c", "--stat_col",
help="column of status")
parser.add_argument("-s", "--stat",
help="status of item")
parser.add_argument("-r", "--ts_start",
help="start time of count")
parser.add_argument("-e", "--ts_end",
help="end time of count")
parser.add_argument("-i", "--instance",
except KeyError, e:
prevState = 0
if (int(prevState) == int(case)):
query = """UPDATE logger_health SET ts_updated ='""" + str(
timestamp) + """' WHERE health_id=""" + prevCaseId
elif (((prevState < 5) or (case < 5))
and ((prevState != 1) or (case != 1))):
query = """UPDATE logger_health SET ts_updated = '""" + str(
timestamp) + """' , health_case= """ + str(
case) + " WHERE health_id= " + prevCaseId
else:
query = "INSERT into logger_health (health_case, logger_id, ts, ts_updated) values ('%d','%d','%s', '%s')" % (
case, logger_id, timestamp, timestamp)
dbio.commitToDb(query, 'logger_health')
def checkCaseBasedFromInbox(): #okay
global globaldf
db, cur = dbio.SenslopeDBConnect('gsm')
for i in range(0, len(globaldf)):
if (globaldf.health_case.loc[i] == 2):
s_number = str(globaldf.sim_num.loc[i])
query = "SELECT sms_msg from smsinbox where sms_id > (select max(sms_id)-10000 from smsinbox) and sim_num = " + s_number + """ and (sms_msg like "%NO DATA PARSED%" or sms_msg like "%NO DATA FROM SENSELOPE%" or sms_msg like "%MANUAL RESET%") order by sms_id desc"""
try:
mesdf = psql.read_sql(query, db)
except pd.io.sql.DatabaseError, e:
mesdf = 0
