summaryrefslogtreecommitdiff
path: root/heap/ebus/webapp/__init__.py
diff options
context:
space:
mode:
Diffstat (limited to 'heap/ebus/webapp/__init__.py')
-rw-r--r--heap/ebus/webapp/__init__.py191
1 files changed, 191 insertions, 0 deletions
diff --git a/heap/ebus/webapp/__init__.py b/heap/ebus/webapp/__init__.py
new file mode 100644
index 0000000..9c98ac3
--- /dev/null
+++ b/heap/ebus/webapp/__init__.py
@@ -0,0 +1,191 @@
+# -*- coding: utf-8 -*-
+import os
+import json
+import datetime
+import time
+
+import bottle
+from sqlalchemy import text
+
+app = bottle.Bottle("ebus")
+
+def maketime(dt):
+ """Rechnet CET DateTime in CET timestamp"""
+ return time.mktime(dt.timetuple())*1000 # - time.altzone)*1000
+
+def parsetime(timestamp):
+ """Machtaus CET timestamp Local DateTime"""
+ return datetime.datetime.fromtimestamp((timestamp/1000)) # + time.altzone)
+
+def now(conn):
+ return conn.execute(text("SELECT CURRENT_TIMESTAMP t")).first().t
+
+@app.route('/')
+def index_file():
+ 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"))
+
+@app.route('/sensor/:name')
+def sensor_data(soup,name):
+ try:
+ sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id
+
+ conn = soup.connection()
+ 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:
+ return {'sensor':name,'data':None, 'error':str(e)}
+
+@app.route('/sensor/:name/:startdate/:enddate')
+def sensor_data_fromto(soup,name,startdate,enddate):
+ try:
+ interval = float(enddate) - float(startdate)
+ modulo = interval / 500 #500 values
+
+ startdate = datetime.datetime.fromtimestamp(float(startdate))
+ enddate = datetime.datetime.fromtimestamp(float(enddate))
+
+ 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()
+ 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
+ WHERE timestamp > :startdate
+ AND timestamp < :enddate
+ AND sensor_id = :sensor_id
+ GROUP BY "round_timestamp"
+ ORDER BY "round_timestamp"
+ """)
+
+ 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)
+
+ return {'sensor':name,'data':values, 'error':None}
+ except Exception,e:
+ return {'sensor':name, 'data':None, 'error':str(e)}
+
+@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))
+ sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id
+
+ # select data from cache-view
+ conn = soup.connection()
+ 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)}
+
+import select
+import psycopg2
+
+@app.route('/stream')
+@app.route('/stream/:startdate')
+def stream(soup, startdate=None):
+ connection = soup.connection()
+ conn = connection.connection
+
+ time_start = startdate != None and parsetime(float(startdate)) \
+ or now(connection)
+
+
+ conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
+
+ cursor = conn.cursor()
+ cursor.execute("LISTEN evt_ebus_value_insert;")
+
+ values = []
+ fails = 0
+ while fails < 5:
+ if select.select([conn],[],[],10) == ([],[],[]):
+ time_stop = now(connection)
+ fails += 1
+ else:
+ conn.poll()
+
+ notify = conn.notifies.pop()
+ if not notify:
+ continue
+
+ time_stop = now(connection)
+ print "time_stop %s"%time_stop
+ sql = text("""SELECT sensor.name,
+ value.timestamp,
+ COALESCE(value.value_int,value.value_float) "value_real",
+ value_string
+ FROM value, sensor
+ WHERE value.sensor_id = sensor.id
+ AND timestamp >= :time_start
+ AND timestamp < :time_stop""")
+ values = map(lambda row: {
+ "name":row.name,
+ "timestamp":maketime(row.timestamp),
+ "value_real":row.value_real != None and row.value_real.__float__() or None,
+ "value_string":row.value_string
+ },
+ connection.execute(sql, time_start=time_start, time_stop=time_stop))
+
+ break
+
+ cursor.close()
+ return {'time_start' : maketime(time_start), 'time_stop':maketime(time_stop),
+ 'data':values}
+
+@app.route("/all_values")
+def all_values(soup):
+ conn = soup.connection()
+
+ sql = text("""
+ SELECT sensor.name,
+ value.timestamp,
+ COALESCE(value.value_int,value.value_float) "value_real",
+ value_string
+ FROM value, sensor, (SELECT MAX(timestamp) as timestamp,
+ sensor_id
+ FROM value
+ WHERE timestamp > CURRENT_TIMESTAMP - '15 minutes'::interval
+ GROUP BY sensor_id) last_value
+ WHERE value.timestamp = last_value.timestamp
+ AND value.sensor_id = last_value.sensor_id
+ AND value.sensor_id = sensor.id""")
+
+ time_stop = now(conn)
+ values = map(lambda row: {
+ "name":row.name,
+ "timestamp":maketime(row.timestamp),
+ "value_real":row.value_real != None and row.value_real.__float__() or None,
+ "value_string":row.value_string},
+ conn.execute(sql))
+
+ return {'data':values, 'time_stop':maketime(time_stop)}
+
+# vim: autoindent tabstop=4 shiftwidth=4 expandtab softtabstop=4 filetype=python