cache und startdatei

  * die anwendung umgestellt das sie den cache benutzt
  * web.py umgestellt dass sie das ebus.webapp modul startet

3 files changed, 95 insertions, 40 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)}
+
diff --git a/ebus/webapp/static/index.html b/ebus/webapp/static/index.html
index 754fd62..1decced 100644
--- a/ebus/webapp/static/index.html
+++ b/ebus/webapp/static/index.html
@@ -5,17 +5,33 @@
 		<script src="/static/jquery.flot.js"></script>
 		<script>
 			$(document).ready(function(){
-				var from = Math.round(new Date().getTime()/1000) - 86400;
+				var from = Math.round(new Date().getTime()/1000) - 3*24*60*60; //86400;
 				var to = Math.round(new Date().getTime()/1000);
-				$.getJSON("/sensor/heizkreisregler9.solarDaten.tempKollektor/"+from+"/"+to,
-					function(data) {
-						$.plot($("#ebusgraph"), [data], { xaxis: { mode: "time"}});
-					});
+                var dataset = []
+                var replot = function() {
+                    $.plot($("#ebusgraph"), dataset, { xaxis: { mode: "time"}});
+                };
+                var plotSensor = function(sensorname) {
+                    $.getJSON("/sensor_cached/"+escape(sensorname)+"/"+from, //+"/"+to,
+                        function(response) {
+                            if (response['data']) {
+                                data = response['data'];
+                                dataset.push({'data':data,'label':sensorname});
+                                replot();
+                            } else {
+                                alert("Fehler: " + response["error"]);
+                            }
+                        });
+                }
+                plotSensor("heizkreisregler9.solarDaten.tempKollektor");
+                plotSensor("heizkreisregler9.solarDaten.tempWarmwasserSolar");
+                plotSensor("heizkreisregler10.betriebsdatenRegler1.kesselTemperatur");
+                plotSensor("feuerungsautomat1.betriebsdatenRegler1.aussenTemperatur");
+                // TODO http://people.iola.dk/olau/flot/examples/annotating.html
 			});
 		</script>
 	</head>
 	<body>
-	<h1>TEST</h1>
 	<div id="ebusgraph"></div>
 	</body>
 </html>
diff --git a/ebus/webapp/static/main.css b/ebus/webapp/static/main.css
index f832fdd..2b28643 100644
--- a/ebus/webapp/static/main.css
+++ b/ebus/webapp/static/main.css
@@ -1,9 +1,10 @@
 body {
 	margin:0px;
 	font-family:sans;
+    padding: 10px;
 }
 
 #ebusgraph {
-	width:800px;
-	height:400px;
+	width:100%;
+	height:500px;
 }