1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
# -*- coding: utf-8 -*-
import os
import json
import datetime
import time
import bottle
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")
@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)}
# vim: autoindent tabstop=4 shiftwidth=4 expandtab softtabstop=4 filetype=python
|
