diff options
| -rw-r--r-- | ebus/webapp/__init__.py | 62 |
1 files changed, 29 insertions, 33 deletions
diff --git a/ebus/webapp/__init__.py b/ebus/webapp/__init__.py index 9870e95..b639c66 100644 --- a/ebus/webapp/__init__.py +++ b/ebus/webapp/__init__.py @@ -1,5 +1,4 @@ # -*- coding: utf-8 -*- -# vim: autoindent tabstop=4 shiftwidth=4 expandtab softtabstop=4 filetype=python import os import json import datetime @@ -10,6 +9,10 @@ from sqlalchemy import text app = bottle.Bottle("ebus") +def maketime(datetime): + """rechnet zeit so um wie Javascript es braucht XXX""" + return (time.mktime(datetime.timetuple())+time.altzone*-1)*1000 + @app.route('/') def index_file(): return static_files("index.html") @@ -21,17 +24,15 @@ def static_files(filename): @app.route('/sensor/:name') def sensor_data(soup,name): try: - # get sensor_id for name sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id - # select data from cache-view conn = soup.connection() - s = text("""SELECT timestamp, COALESCE(value_int, value_float) as "value_real" - FROM value - WHERE sensor_id = :sensor_id - ORDER BY timestamp DESC - LIMIT 1""") - value = conn.execute(s, sensor_id=sensor_id).first() + sql = text("""SELECT timestamp, COALESCE(value_int, value_float) as "value_real" + FROM value + WHERE sensor_id = :sensor_id + ORDER BY timestamp DESC + LIMIT 1""") + value = conn.execute(sql, sensor_id=sensor_id).first() return {'sensor':name,'data':[maketime(value.timestamp), float(value.value_real)], 'error':None} except Exception,e: @@ -41,19 +42,18 @@ def sensor_data(soup,name): def sensor_data_fromto(soup,name,startdate,enddate): try: interval = float(enddate) - float(startdate) - if interval <= 0: raise Exception("Invalid interval") - if interval >= 14 * 24 * 60 * 60: raise Exception("interval too big") - modulo = interval / 500 #500 values startdate = datetime.datetime.fromtimestamp(float(startdate)) enddate = datetime.datetime.fromtimestamp(float(enddate)) - sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id + if interval <= 0: raise Exception("Invalid interval") + if interval >= 14 * 24 * 60 * 60: raise Exception("interval too big") + sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id conn = soup.connection() - s = text(""" + sql = text(""" SELECT to_timestamp( extract(epoch from timestamp)::int - extract(epoch from timestamp)::int % :modulo ) "round_timestamp", AVG(COALESCE(value_int,value_float)) "value_real" FROM value @@ -64,42 +64,38 @@ def sensor_data_fromto(soup,name,startdate,enddate): ORDER BY "round_timestamp" """) - values = conn.execute(s, sensor_id=sensor_id, startdate=startdate, enddate=enddate, modulo=modulo).fetchall() - values = map(lambda row: (maketime(row.round_timestamp), - row.value_real), + values = conn.execute(sql, + sensor_id=sensor_id, + startdate=startdate, + enddate=enddate, + modulo=modulo).fetchall() + + values = map(lambda row: (maketime(row.round_timestamp), row.value_real), values) - print "Sent", len(values) return {'sensor':name,'data':values, 'error':None} except Exception,e: return {'sensor':name, 'data':None, 'error':str(e)} -# rechnet zeit so um wie Javascript -# teil es braucht XXX -def maketime(datetime): - return (time.mktime(datetime.timetuple())+time.altzone*-1)*1000 - @app.route('/sensor_cached/:name/:timestamp_from') def sensor_data_cached_fromto(soup, name, timestamp_from): try: timestamp_from = datetime.datetime.fromtimestamp(float(timestamp_from)) - - # get sensor_id for name sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id # select data from cache-view conn = soup.connection() - s = text("""SELECT timestamp, value_real AS "value_real" - FROM vi_value_cache - WHERE timestamp >= :timestamp_from - AND sensor_id = :sensor_id - ORDER BY timestamp""") - values = conn.execute(s, timestamp_from=timestamp_from, sensor_id=sensor_id).fetchall() - values = map(lambda row: (maketime(row.timestamp), - row.value_real.__float__()), + sql = text("""SELECT timestamp, value_real AS "value_real" + FROM vi_value_cache + WHERE timestamp >= :timestamp_from + AND sensor_id = :sensor_id + ORDER BY timestamp""") + values = conn.execute(sql, timestamp_from=timestamp_from, sensor_id=sensor_id).fetchall() + values = map(lambda row: (maketime(row.timestamp), row.value_real.__float__()), values) return {'sensor':name,'data':values, 'error':None} except Exception,e: return {'sensor':name, 'data':None, 'error':str(e)} +# vim: autoindent tabstop=4 shiftwidth=4 expandtab softtabstop=4 filetype=python |