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
status = 200
break
except Exception, e:
log.error("Can't query ACL: %s" % (str(e)))
if nrecs is None or int(nrecs) < 1:
nrecs = 50
if nrecs > 600:
nrecs = 600
track = []
if track_authorized == True:
message = "OK"
status = 200
query = (Location.select(Location).where(
(Location.topic == topic))).order_by(
Location.tst.desc()).limit(nrecs)
for l in query.naive():
dbid = l.id
lat = float(l.lat)
lon = float(l.lon)
dt = l.tst
try:
tp = {
'lat': float(l.lat),
'lon': float(l.lon),
'tst': int(dt.strftime('%s')),
}
g = geo.rev(lat, lon, api='google')
if g is not None:
item['ghash'] = g.get('ghash')
item['cc'] = g.get('cc', None)
if 'addr' in g:
event_desc = g.get('addr')
item['addr'] = g.get('addr')
addr = g.get('addr')
print "%s %s %-2s %5d %s [%s] %s,%s" % (p_tstamp, g.get('cached', -1), tid, vel, addr, item.get('ghash'), item.get('lat'), item.get('lon'))
else:
print " %-2s" % tid
if storage:
try:
loca = Location(**item)
loca.save()
except Exception, e:
log.error("Cannot INSERT location for {0} into DB: {1}".format(topic, str(e)))
# Upsert last vehicle location into Lastloc
try:
ll = Lastloc.get(Lastloc.topic == topic)
ll.tid = item.get('tid')
ll.lat = item.get('lat')
ll.lon = item.get('lon')
ll.tst = item.get('tst')
ll.vel = item.get('vel')
ll.alt = item.get('alt')
ll.cog = item.get('cog')
ll.trip = item.get('trip')
if not superuser:
query = (Acl.select(Acl). where(
(Acl.username == username)
))
sublist = [ q.topic for q in query.naive() ]
else:
sublist.append('#')
# Find distinct topic, tid combinations in Locations table and
# let Paho check if subscription matches
topiclist = []
tidlist = []
query = (Location.select(Location.tid, Location.topic)
.distinct()
.order_by(Location.tid)
)
for q in query:
for sub in sublist:
if paho.topic_matches_sub(sub, q.topic):
tidlist.append(q.tid)
topiclist.append(q.topic)
log.debug("User {0} gets tidlist={1}".format(username, ",".join(tidlist)))
return sorted(set(tidlist))
def getinventorytopics(username):
''' username is probably a logged-in user. Obtain a list of TIDs
that user is allowed to see '''
if g is not None:
item['ghash'] = g.get('ghash')
item['cc'] = g.get('cc', None)
if 'addr' in g:
event_desc = g.get('addr')
item['addr'] = g.get('addr')
addr = g.get('addr')
print "%s %s %-2s %5d %s [%s] %s,%s" % (p_tstamp, g.get(
'cached', -1), tid, vel, addr, item.get('ghash'), item.get('lat'),
item.get('lon'))
else:
print " %-2s" % tid
if storage:
try:
loca = Location(**item)
loca.save()
except Exception, e:
log.error("Cannot INSERT location for {0} into DB: {1}".format(
topic, str(e)))
# Upsert last vehicle location into Lastloc
try:
ll = Lastloc.get(Lastloc.topic == topic)
ll.tid = item.get('tid')
ll.lat = item.get('lat')
ll.lon = item.get('lon')
ll.tst = item.get('tst')
ll.vel = item.get('vel')
ll.alt = item.get('alt')
ll.cog = item.get('cog')
except Exception, e:
raise
if not superuser:
query = (Acl.select(Acl).where((Acl.username == username)))
sublist = [q.topic for q in query.naive()]
else:
sublist.append('#')
# Find distinct topic, tid combinations in Locations table and
# let Paho check if subscription matches
topiclist = []
tidlist = []
query = (Location.select(Location.tid,
Location.topic).distinct().order_by(Location.tid))
for q in query:
for sub in sublist:
if paho.topic_matches_sub(sub, q.topic):
tidlist.append(q.tid)
topiclist.append(q.topic)
log.debug("User {0} gets tidlist={1}".format(username, ",".join(tidlist)))
return sorted(set(tidlist))
def getinventorytopics(username):
''' username is probably a logged-in user. Obtain a list of TIDs
that user is allowed to see '''