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