diff options
Diffstat (limited to 'ebus')
| -rw-r--r-- | ebus/webapp/__init__.py | 87 | ||||
| -rw-r--r-- | ebus/webapp/static/ebus.js | 3 |
2 files changed, 56 insertions, 34 deletions
diff --git a/ebus/webapp/__init__.py b/ebus/webapp/__init__.py index d65e2d1..cd5de8c 100644 --- a/ebus/webapp/__init__.py +++ b/ebus/webapp/__init__.py @@ -1,4 +1,5 @@ # -*- coding: utf-8 -*- +# vim: autoindent tabstop=4 shiftwidth=4 expandtab softtabstop=4 filetype=python import bottle import sqlalchemy @@ -17,7 +18,7 @@ import time app = bottle.Bottle("ebus") bottle.debug() -engine = create_engine("postgresql://ebus:ebus@10.2.2.26/ebus", echo=True) +engine = create_engine("postgresql:///ebus", echo=True) Session = sessionmaker() Session.configure(bind=engine) @@ -32,40 +33,60 @@ def static_files(filename): return bottle.static_file(filename, root=os.path.join(os.path.dirname(__file__),"static")) @app.route('/sensor/:name') -def sensor_data_single(name): - #s = Session() - #sensorid = s.query(model.Sensor).filter(model.Sensor.name == name).first() - #values = Session().query(model.Value).add_columns("timestamp","value_float","value_string","value_int") - #values = values.filter(model.Value.sensor == sensorid) - #return json.dumps(values.first().value_int) - return sensor_data_fromto(name,None,None) +def sensor_data(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 + LIMIT 1""") + value = conn.execute(s, sensor_id=sensor_id).first() + + return {'sensor':name,'data':[maketime(value.timestamp), float(value.value_real)], 'error':None} + except Exception,e: + return {'sensor':name,'data':None, 'error':str(e)} @app.route('/sensor/:name/:startdate/:enddate') def sensor_data_fromto(name,startdate,enddate): - s = Session() - sensorid = s.query(model.Sensor).filter(model.Sensor.name == name).first() - values = Session().query(model.Value).add_columns("timestamp","value_float","value_string","value_int") - values = values.filter(model.Value.sensor == sensorid) - - if startdate == None or enddate == None: - queried_values = [] - queried_values.append(values.first()) - else: - now = datetime.datetime.now() - f = datetime.datetime.fromtimestamp(float(startdate)) - t = datetime.datetime.fromtimestamp(float(enddate)) - values = values.filter(model.Value.timestamp.between(f,t)) - queried_values = values.all() - v = [] - for value in queried_values: - if value.value_float != None: - v.append([int((time.mktime(value.timestamp.timetuple())+time.altzone*-1)*1000),value.value_float]) # time.altzone ist negativ - elif value.value_int != None: - v.append([int((time.mktime(value.timestamp.timetuple())+time.altzone*-1)*1000),value.value_int]) - elif value.value_string != None: - v.append([int((time.mktime(value.timestamp.timetuple())+time.altzone*-1)*1000),value.value_string]) - return {'data':v, 'error':None} + 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 + + + + conn = soup.connection() + s = 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 + WHERE timestamp > :startdate + AND timestamp < :enddate + AND sensor_id = :sensor_id + GROUP BY "round_timestamp" + 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) + + 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 @@ -92,7 +113,7 @@ def sensor_data_cached_fromto(name, timestamp_from): row.value_real.__float__()), values) - return {'data':values, 'error':None} + return {'sensor':name,'data':values, 'error':None} except Exception,e: - return {'data':None, 'error':str(e)} + return {'sensor':name, 'data':None, 'error':str(e)} diff --git a/ebus/webapp/static/ebus.js b/ebus/webapp/static/ebus.js index 15fe692..32e9f3a 100644 --- a/ebus/webapp/static/ebus.js +++ b/ebus/webapp/static/ebus.js @@ -99,7 +99,8 @@ $(document).ready(function(){ $("#overview").bind("plotselected", function (event, ranges) { range_from = Math.round(ranges.xaxis.from / 1000); range_to = Math.round(ranges.xaxis.to / 1000); - if (range_to - range_from > 4 * 24 * 60 * 60) { + // max selection range 14 days + if (range_to - range_from > 14 * 24 * 60 * 60) { plotOverview.setSelection({xaxis: {'from': from*1000, 'to': to*1000}}, true); return; } else { |