def s_define(self, otckey, sname, settings):
''' Create a settings entry '''
if _keycheck(otckey) == False:
return None
dbconn()
sname = sname.replace(' ', '')
message = "OK"
try:
s = Settings.get(Settings.sname == sname)
s.settings = settings
s.save()
message = "Updated settings {0} in database".format(sname)
log.info(message)
except Settings.DoesNotExist:
item = {
'sname': sname,
'settings': settings,
}
try:
s = Settings(**item)
s.save()
message = "Stored Settings {0} in database".format(sname)
log.info(message)
except Exception, e:
message = "Cannot store Settings record for {0} in DB: {1}".format(
sname, str(e))
log.error(message)
def find(self, otckey, word):
''' Find a word (tid or custid) in the database and show info. '''
results = []
dbconn()
if _keycheck(otckey) == True:
# Lousy, but no other way at the moment ... See comment re JOIN above
snames = {}
query = Imeiset.select()
for q in query.naive():
snames[q.imei] = q.sname
query = Otap.select()
query = query.where((Otap.tid == word) | (Otap.custid == word))
query = query.order_by(Otap.tid.asc())
for q in query.naive():
results.append({
'imei': q.imei,
'custid': q.custid,
'tid': q.tid,
'block': q.block,
'reported': q.reported,
'deliver': q.deliver,
'sname': snames.get(q.imei, ""),
'lastcheck': utc_to_localtime(q.lastcheck),
'comment': q.comment or '',
'flags': q.flags or '',
})
return results
def setcomment(self, otckey, imei, text):
''' set a plain text comment for IMEI '''
dbconn()
res = "not set"
if _keycheck(otckey) == True:
try:
o = Otap.get(Otap.imei == imei)
operation = 'set'
if len(text) < 1:
text = None
operation = 'unset'
o.comment = text
o.save()
res = "Comment {0} for {1}".format(operation, imei)
log.info(res)
except Exception, e:
res = "Comment not set for {0}: {1}".format(imei, str(e))
log.error(res)
def setflags(self, otckey, imei, flagstring):
''' set flags on IMEI '''
dbconn()
res = "not set"
if _keycheck(otckey) == True:
try:
imei = imei.strip()
flagstring = flagstring.strip()
o = Otap.get(Otap.imei == imei)
oldflags = o.flags
custid = o.custid or ''
tid = o.tid or ''
if len(flagstring) < 1:
flagstring = None
operation = 'unset'
o.flags = flagstring
o.save()
res = "Flags on {0}/{1}/{2} changed from {3} to {4}".format(
custid, tid, imei, oldflags, flagstring)
log.info(res)
except Otap.DoesNotExist:
res = "Flags not set for {0}: IMEI does not exist".format(imei)
except Exception, e:
res = "Flags not set for {0}: {1}".format(imei, str(e))
log.error(res)
def getDBwaypoints(usertid, lat_min, lat_max, lon_min, lon_max):
waypoints = []
lat_min = float(lat_min)
lat_max = float(lat_max)
lon_min = float(lon_min)
lon_max = float(lon_max)
dbconn()
query = Waypoint.select().where((Waypoint.lat >= lat_min)
& (Waypoint.lat <= lat_max)
& (Waypoint.lon >= lon_min)
& (Waypoint.lon <= lon_max))
for q in query:
if q.rad is None:
continue
wp = {
'lat': float(q.lat),
'lon': float(q.lon),
'name': q.waypoint,
'rad': q.rad,
}
waypoints.append(wp)
return waypoints
def add_imei(self, otckey, imei, custid, tid):
''' Add to database. If IMEI exists, other fields are updated. '''
if _keycheck(otckey) == False:
return "NOP"
dbconn()
imei = imei.replace(' ', '')
custid = custid.replace(' ', '')
tid = tid.replace(' ', '')
try:
o = Otap.get(Otap.imei == imei)
o.custid = custid
o.tid = tid
o.block = 0
o.save()
except Otap.DoesNotExist:
item = {
'imei': imei,
'custid': custid,
'tid': tid,
'block': 0,
}
try:
o = Otap(**item)
o.save()
log.info("Stored OTAP IMEI {0} in database".format(imei))
except Exception, e:
log.error("Cannot store OTAP record for {0} in DB: {1}".format(
imei, str(e)))
def getDBwaypoints(usertid, lat_min, lat_max, lon_min, lon_max):
waypoints = []
lat_min = float(lat_min)
lat_max = float(lat_max)
lon_min = float(lon_min)
lon_max = float(lon_max)
dbconn()
query = Waypoint.select().where(
(Waypoint.lat >= lat_min) &
(Waypoint.lat <= lat_max) &
(Waypoint.lon >= lon_min) &
(Waypoint.lon <= lon_max)
)
for q in query:
if q.rad is None:
continue
wp = {
'lat' : float(q.lat),
'lon' : float(q.lon),
'name' : q.waypoint,
'rad' : q.rad,
}
waypoints.append(wp)
return waypoints
def getDBdata(usertid, from_date, to_date, spacing, tzname='UTC'):
track = []
from_date = normalize_date(from_date)
to_date = normalize_date(to_date)
from_date = "%s 00:00:00" % from_date
to_date = "%s 23:59:59" % to_date
from_date = utc_time(from_date, tzname)
to_date = utc_time(to_date, tzname)
log.debug("getDBdata: FROM={0}, TO={1}".format(from_date, to_date))
dbconn()
# select tst, lat, lon, l.ghash, addr from location l left join geo g on l.ghash = g.ghash where tid = 'B2';
query = (Location
.select(Location, Geo.addr.alias('addr'))
.join(Geo, JOIN_LEFT_OUTER, on=(Location.ghash == Geo.ghash)).
where(
(Location.tid == usertid) &
(Location.tst.between(from_date, to_date))
)
)
query = query.order_by(Location.tst.asc())
for l in query.naive():
dbid = l.id
lat = float(l.lat)
lon = float(l.lon)
dt = l.tst
# FIXME: add distance haversine to previous point
# FIXME: check values (vel, dist, trip...
try:
tp = {
'lat' : float(l.lat),
'lon' : float(l.lon),
'tst' : l.tst,
't' : l.t,
'vel' : int(l.vel),
'cog' : int(l.cog),
'alt' : int(l.alt),
'ghash' : l.ghash,
'cc' : l.cc,
'addr' : l.addr,
'dist' : l.dist,
'trip' : l.trip,
}
track.append(tp)
except:
pass
log.info("getDBdata: FROM={0}, TO={1} returns {2} points for {3}".format(from_date, to_date, len(track), usertid))
return track
def otap_get(custid):
device, imei = agentinfo()
dbconn()
log.info('OTAP request for cust={0} / {1} IMEI={2}'.format(
custid, device, imei))
tid = ""
deliver = None
try:
o = Otap.get(Otap.imei == imei, Otap.custid == custid)
tid = o.tid or "??"
if o.block == 0 and o.deliver is not None:
deliver = o.deliver
except Otap.DoesNotExist:
log.info("Requested OTAP cust={0}/IMEI={1} doesn't exist in database".
format(custid, imei))
return bottle.HTTPResponse(status=404, body="NOTFOUND")
except Exception, e:
log.error("Cannot get OTAP record for {0} from DB: {1}".format(
imei, str(e)))
return bottle.HTTPResponse(status=404, body="NOTFOUND")
def getDBdata(usertid, from_date, to_date, spacing, tzname='UTC'):
track = []
from_date = normalize_date(from_date)
to_date = normalize_date(to_date)
from_date = "%s 00:00:00" % from_date
to_date = "%s 23:59:59" % to_date
from_date = utc_time(from_date, tzname)
to_date = utc_time(to_date, tzname)
log.debug("getDBdata: FROM={0}, TO={1}".format(from_date, to_date))
dbconn()
# select tst, lat, lon, l.ghash, addr from location l left join geo g on l.ghash = g.ghash where tid = 'B2';
query = (Location.select(Location, Geo.addr.alias('addr')).join(
Geo, JOIN_LEFT_OUTER,
on=(Location.ghash == Geo.ghash
)).where((Location.tid == usertid)
& (Location.tst.between(from_date, to_date))))
query = query.order_by(Location.tst.asc())
for l in query.naive():
dbid = l.id
lat = float(l.lat)
lon = float(l.lon)
dt = l.tst
# FIXME: add distance haversine to previous point
# FIXME: check values (vel, dist, trip...
try:
tp = {
'lat': float(l.lat),
'lon': float(l.lon),
'tst': l.tst,
't': l.t,
'vel': int(l.vel),
'cog': int(l.cog),
'alt': int(l.alt),
'ghash': l.ghash,
'cc': l.cc,
'addr': l.addr,
'dist': l.dist,
'trip': l.trip,
}
track.append(tp)
except:
pass
log.info("getDBdata: FROM={0}, TO={1} returns {2} points for {3}".format(
from_date, to_date, len(track), usertid))
return track
def s_set(self, otckey, imei, sname, bf, once):
''' Set/unset settings SNAME on IMEI '''
if _keycheck(otckey) == False:
return "NOP"
dbconn()
imei = imei.replace(' ', '')
sname = sname.replace(' ', '')
if bf == 0:
# Unset
query = Imeiset.delete().where(Imeiset.imei == imei,
Imeiset.sname == sname)
nrows = query.execute()
message = "{0} row deleted from IMEIset for {1}/{2}".format(
nrows, imei, sname)
log.info(message)
notify('unset', message)
return message
# Assign (set), but only if the specified settings name exists
message = ""
try:
query = (Settings.select().where(Settings.sname == sname)).get()
item = {
'imei': imei,
'sname': sname,
'once': once,
}
try:
i = Imeiset(**item)
i.save()
message = "Setting {0} configured for {1}. Once={2}".format(
sname, imei, once)
except Exception, e:
message = "Cannot add Imeiset for {0}/{1}: {2}".format(
imei, sname, str(e))
log.error(message)
return message
except Settings.DoesNotExist:
message = "Cannot assign setting {0}: it does not exist".format(
sname)
log.info(message)
return message
except Exception, e:
message = "Error querying Settings {0}: {1}".format(sname, str(e))
log.error(message)
return message
def imei_getflags(imei):
''' Get the flags for IMEI and return as string or "" if no flags '''
dbconn()
flagstring = ""
try:
imei = imei.strip()
o = Otap.get(Otap.imei == imei)
flagstring = o.flags or ""
except Otap.DoesNotExist:
flagstring = ""
except Exception, e:
log.error("Cannot imei_getflags({0}): {1}".format(imei, str(e)))
pass
def imei(self, otckey, custid, tid):
''' Find a TID (and optional custid) in the database and return it's IMEI. '''
result = ""
dbconn()
if _keycheck(otckey) == True:
try:
query = Otap.select(Otap.imei).where(Otap.tid == tid)
if custid is not None:
query = query.where(Otap.custid == custid)
query = query.get()
result = query.imei
except Otap.DoesNotExist:
pass
return result
def json_batt():
result = []
dbconn()
query = (RAWdata.select(RAWdata.tst, RAWdata.payload).where(
RAWdata.topic == 'owntracks/gw/356612028111492/voltage/batt'))
query = query.order_by(RAWdata.tst.desc()).limit(190)
time_format = "%Y-%m-%d %H:%M:%S"
for q in query.naive():
tstamp = q.tst.strftime(time_format)
print tstamp, q.payload
result.append({'tst': tstamp, 'batt': float(q.payload)})
j = json.dumps(result)
print j
return j
def deliver(self, otckey, imei, version):
''' Update IMEI in database and set version to be delivered.
n.nn.nn means that version, "latest" means current highest version,
and "*" means highest version existing even if that is introduced
at a later point in time. '''
if _keycheck(otckey) == False:
return "NOP"
dbconn()
imei = imei.replace(' ', '')
version = version.replace(' ', '')
if version == 'ANY':
version = '*'
if version == 'latest':
# Get sorted versions and take highest
version = list_jars()[-1]
if version != '*':
jarfile = "{0}/{1}.jar".format(cf.jardir, version)
if not os.path.isfile(jarfile):
return "No such version here"
custid = ""
tid = ""
try:
o = Otap.get(Otap.imei == imei)
o.deliver = version
custid = o.custid or ""
tid = o.tid or ""
o.save()
except:
return "Can't find IMEI {0} in DB".format(imei)
message = "{0}/{1}/{2} will get {3} at next OTAP".format(
custid, tid, imei, version)
log.info(message)
notify('deliver', message)
return message
def block(self, otckey, imei, bl):
''' Set block in db for IMEI. If IMEI == 'ALL', then for all '''
if _keycheck(otckey) == False:
return "NOP"
dbconn()
imei = imei.replace(' ', '')
nrecs = None
try:
query = Otap.update(block=bl)
if imei != 'ALL':
query = query.where(Otap.imei == imei)
nrecs = query.execute()
except Exception, e:
s = "Cannot update db: {0}".format(str(e))
log.error(s)
return s
def users():
''' Get list of username - device pairs to populate a select box
the id in that select will be set to username|device '''
current_user = request.auth[0]
dbconn()
usertids = getusertids(current_user)
allowed_tids = []
for t in usertids:
allowed_tids.append({
'id' : t,
'name' : t,
})
log.debug("/api/userlist returns: {0}".format(json.dumps(allowed_tids)))
return dict(userlist=allowed_tids)
def users():
''' Get list of username - device pairs to populate a select box
the id in that select will be set to username|device '''
current_user = request.auth[0]
dbconn()
usertids = getusertids(current_user)
allowed_tids = []
for t in usertids:
allowed_tids.append({
'id': t,
'name': t,
})
log.debug("/api/userlist returns: {0}".format(json.dumps(allowed_tids)))
return dict(userlist=allowed_tids)
def purge(self, otckey, version):
''' purge JAR version from filesystem, ensuring that version is
not configured as 'deliver' for devices. '''
if _keycheck(otckey) == False:
return "NOP"
dbconn()
version = version.replace(' ', '')
n = None
try:
query = (Otap.select(fn.COUNT(
Otap.imei).alias('numdeliver')).where(
Otap.deliver == version).get())
n = query.numdeliver or 0
except Exception, e:
raise
def versionlog(self, otckey, count):
''' Return an array of versionlog entries '''
dbconn()
logs = []
if _keycheck(otckey) == True:
query = (Versioncheck.select().limit(count))
query = query.order_by(Versioncheck.tstamp.desc())
for q in query.naive():
logs.append({
'imei': q.imei,
'version': q.version,
'tstamp': utc_to_localtime(q.tstamp),
'upgrade': q.upgrade,
})
return logs
def show(self, otckey, imei):
''' Show content of database. If IMEI specified, then just that one. '''
if _keycheck(otckey) == False:
return "NOP"
dbconn()
results = []
# This join works in dev but not in prod; same Peewee versions (!?!)
# query = (Otap
## .select(Otap, Settings.sname.alias('sname'))
# .select(Otap, Settings.sname)
# .join(Imeiset, JOIN_LEFT_OUTER, on=(Otap.imei == Imeiset.imei))
# )
# Lousy, but no other way at the moment ...
snames = {}
query = Imeiset.select()
for q in query.naive():
snames[q.imei] = q.sname
query = Otap.select()
if imei is not None:
query = query.where(Otap.imei == imei)
query = query.order_by(Otap.tid.asc())
for q in query.naive():
results.append({
'imei': q.imei,
'custid': q.custid,
'tid': q.tid,
'block': q.block,
'reported': q.reported,
'deliver': q.deliver,
'sname': snames.get(q.imei, ""),
'lastcheck': utc_to_localtime(q.lastcheck),
'comment': q.comment or '',
'flags': q.flags or '',
})
return results
def s_undef(self, otckey, sname):
''' Destroy a settings entry '''
dbconn()
if _keycheck(otckey) == False:
return None
sname = sname.replace(' ', '')
message = "OK"
# Ensure we don't clobber a settings if it is still assigned to an IMEI
n = 0
try:
query = (Imeiset.select(
fn.COUNT(Imeiset.imei).alias('numdeliver')).where(
Imeiset.sname == sname).get())
n = query.numdeliver or 0
except Exception, e:
raise
def inventorytopics():
''' Get list of username - topic pairs to populate a select box
the id in that select will be set to tid|topic.
This is pulled in from the Inventory table. What we're trying
to accomplish is to find a TID -> topic association. '''
current_user = request.auth[0]
dbconn()
usertids = getinventorytopics(current_user)
allowed_tids = []
for t in usertids:
allowed_tids.append({
'name' : t['tid'],
'id' : t['topic'],
})
log.debug("/api/inventorytopics returns: {0}".format(json.dumps(allowed_tids)))
return dict(userlist=allowed_tids)
def showsets(self, otckey, imei=None):
''' Return an array of settings entries. If IMEI then print those '''
dbconn()
result = []
if _keycheck(otckey) == True:
if imei is None:
query = (Settings.select())
query = query.order_by(Settings.sname.asc())
for q in query.naive():
result.append({
'sname': q.sname,
'settings': q.settings,
})
else:
settings = imei_settings(imei)
result = expand_settings(settings)
return result
def json_batt():
result = []
dbconn()
query = (RAWdata.select(RAWdata.tst, RAWdata.payload)
.where(
RAWdata.topic == 'owntracks/gw/356612028111492/voltage/batt'
)
)
query = query.order_by(RAWdata.tst.desc()).limit(190)
time_format = "%Y-%m-%d %H:%M:%S"
for q in query.naive():
tstamp = q.tst.strftime(time_format)
print tstamp, q.payload
result.append( { 'tst' : tstamp, 'batt' : float(q.payload) } )
j = json.dumps(result)
print j
return j
def inventorytopics():
''' Get list of username - topic pairs to populate a select box
the id in that select will be set to tid|topic.
This is pulled in from the Inventory table. What we're trying
to accomplish is to find a TID -> topic association. '''
current_user = request.auth[0]
dbconn()
usertids = getinventorytopics(current_user)
allowed_tids = []
for t in usertids:
allowed_tids.append({
'name': t['tid'],
'id': t['topic'],
})
log.debug("/api/inventorytopics returns: {0}".format(
json.dumps(allowed_tids)))
return dict(userlist=allowed_tids)
def on_message(mosq, userdata, msg):
if (skip_retained and msg.retain == 1):
return
topic = msg.topic
if len(msg.payload) == 0:
'''
Clear out everthing we know of this vehicle?
We cannot delete our own MQTT topics, because that'll result in a loop.
'''
# FIXME: we should subscribe to topic/# to find 'em all...
# for s in ['status', 'start', 'gpio', 'voltage', 'operators', 'info']:
# mosq.publish("%s/%s" % (topic, s), None, qos=2, retain=True)
# mosq.publish(topic, None, qos=2, retain=True)
# mosq.publish(maptopic + "/" + topic, None, qos=2, retain=True)
return
types = ['location', 'waypoint']
payload = str(msg.payload)
save_rawdata(topic, msg.payload)
if len(msg.payload) < 1:
return
item = payload2location(topic, payload)
if item is None or type(item) != dict:
return
if '_type' not in item or item['_type'] not in types:
return
if 'lat' not in item or 'lon' not in item:
return
tid = item.get('tid')
tst = item.get('tst', int(time.time()))
lat = item.get('lat')
lon = item.get('lon')
trip = item.get('trip', 0)
dist = item.get('dist', 0)
vel = item.get('vel', 0)
t_ignore = cf.g('features', 't_store', [ 'p' ])
if item['t'] in t_ignore:
return
item['topic'] = topic
orig_tst = tst
tstamp = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(tst))
item['tst'] = tstamp
if storage:
try:
dbconn()
except Exception, e:
log.error("Reconnect to DB: {0}".format(str(e)))
return
def versioncheck(custid, word):
device, imei = agentinfo()
current_version = bottle.request.body.read()
dbconn()
flags = imei_getflags(imei)
upgrade = 0
settings = []
new_version = ""
tid = ""
try:
o = Otap.get(Otap.imei == imei, Otap.custid == custid)
tid = o.tid or '??'
new_version = o.deliver
if new_version == '*':
new_version = list_jars()[-1]
o.reported = current_version
if device != 'SIMU':
o.lastcheck = time.strftime('%Y-%m-%d %H:%M:%S',
time.gmtime(int(time.time())))
o.save()
if o.block == 0 and o.deliver is not None and current_version != new_version:
upgrade = 1
if o.block == 0:
# Device is not being blocked, but it may have settings we want to push. Do it
settings_str = imei_settings(imei)
if settings_str is not None:
settings = expand_settings(settings_str)
if device == 'SIMU':
log.info("NOT clearing settings-delivery because SIMUlator")
else:
# Check if settings are once-only. If so, delete the record
try:
q = Imeiset.select().where(Imeiset.imei == imei).get()
if q.once == 1:
query = Imeiset.delete().where(Imeiset.imei == imei)
nrows = query.execute()
message = "Imeiset deleted for {0} because once-only configured".format(
imei)
log.info(message)
notify('versioncheck', message)
except:
pass
except Otap.DoesNotExist:
log.info(
"Requested OTAP IMEI {0}/{1} doesn't exist in database".format(
custid, imei))
except Exception, e:
log.error("Cannot get OTAP record for {0} from DB: {1}".format(
imei, str(e)))
def on_message(mosq, userdata, msg):
if (skip_retained and msg.retain == 1):
return
topic = msg.topic
if len(msg.payload) == 0:
'''
Clear out everthing we know of this vehicle?
We cannot delete our own MQTT topics, because that'll result in a loop.
'''
# FIXME: we should subscribe to topic/# to find 'em all...
# for s in ['status', 'start', 'gpio', 'voltage', 'operators', 'info']:
# mosq.publish("%s/%s" % (topic, s), None, qos=2, retain=True)
# mosq.publish(topic, None, qos=2, retain=True)
# mosq.publish(maptopic + "/" + topic, None, qos=2, retain=True)
return
types = ['location', 'waypoint']
payload = str(msg.payload)
save_rawdata(topic, msg.payload)
if len(msg.payload) < 1:
return
item = payload2location(topic, payload)
if item is None or type(item) != dict:
return
if '_type' not in item or item['_type'] not in types:
return
if 'lat' not in item or 'lon' not in item:
return
tid = item.get('tid')
tst = item.get('tst', int(time.time()))
lat = item.get('lat')
lon = item.get('lon')
trip = item.get('trip', 0)
dist = item.get('dist', 0)
vel = item.get('vel', 0)
t_ignore = cf.g('features', 't_store', ['p'])
if item['t'] in t_ignore:
return
item['topic'] = topic
orig_tst = tst
tstamp = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(tst))
item['tst'] = tstamp
if storage:
try:
dbconn()
except Exception, e:
log.error("Reconnect to DB: {0}".format(str(e)))
return
