diff options
Diffstat (limited to 'ebus/webapp/__init__.py')
| -rw-r--r-- | ebus/webapp/__init__.py | 102 |
1 files changed, 70 insertions, 32 deletions
diff --git a/ebus/webapp/__init__.py b/ebus/webapp/__init__.py index 76cec16..bd78033 100644 --- a/ebus/webapp/__init__.py +++ b/ebus/webapp/__init__.py @@ -1,8 +1,13 @@ # -*- coding: utf-8 -*- import bottle -from sqlalchemy import create_engine +import sqlalchemy +from sqlalchemy import create_engine, select from sqlalchemy.orm import sessionmaker +from sqlalchemy.sql import text + +from sqlalchemy.ext.sqlsoup import SqlSoup +from sqlalchemy import or_, and_, desc from ebus.sql import model import os @@ -16,45 +21,78 @@ engine = create_engine("postgresql://ebus:ebus@10.2.2.26/ebus", echo=True) Session = sessionmaker() Session.configure(bind=engine) +soup = SqlSoup(engine) + @app.route('/') def index_file(): - return static_files("index.html") + return static_files("index.html") @app.route('/static/:filename') def static_files(filename): - return bottle.static_file(filename, root=os.path.join(os.path.dirname(__file__),"static")) + 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) + #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) @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 json.dumps(v) + 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 json.dumps(v) + + +# 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(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__()), + values) + + return {'data':values, 'error':None} + except Exception,e: + return {'data':None, 'error':str(e)} + |