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