384 lines
13 KiB
Python
Executable File
384 lines
13 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
|
|
from bottle import get,post,request,Bottle,run,template,static_file
|
|
import requests,threading,time,json,zlib,gnupg,socket,psutil,os,sys,pymysql,queue,_thread,matplotlib.pyplot as plt,matplotlib.cm as cm,numpy
|
|
|
|
#gpg=gnupg.GPG('/usr/bin/gpg',gnupghome='/home/pi/.gnupg')
|
|
gpg=gnupg.GPG()
|
|
|
|
pathname = os.path.dirname(sys.argv[0])
|
|
abspath=os.path.abspath(pathname)
|
|
configfile=abspath+"/config.json"
|
|
try:
|
|
cf=open(configfile,"r")
|
|
except:
|
|
cf=open(configfile+".template","r")
|
|
|
|
log_conf=json.load(cf)
|
|
cf.close()
|
|
|
|
parameter={"device":socket.gethostname(),"allowed_ip":{"127.0.0.1":"25A4CF79414F10FD"},"gpg_keyid":"25A4CF79414F10FD"}
|
|
for n in parameter:
|
|
if n in log_conf:
|
|
parameter[n]=log_conf[n]
|
|
if "sqlserver" in log_conf:
|
|
hostname="banana"
|
|
if "host" in log_conf['sqlserver']:
|
|
hostname=log_conf['sqlserver']['host']
|
|
port=24049
|
|
if "port" in log_conf['sqlserver']:
|
|
port=int(log_conf['sqlserver']['port'])
|
|
|
|
clientlist=parameter['allowed_ip']
|
|
try:
|
|
mydb=pymysql.connect(read_default_file="~/.my.cnf",database="rasolar")
|
|
except:
|
|
clientlist=parameter['allowed_ip']
|
|
else:
|
|
mycursor=mydb.cursor(pymysql.cursors.DictCursor)
|
|
mycursor.execute("select ip,keyid from clients;")
|
|
myclientlist=mycursor.fetchall()
|
|
if len(myclientlist)>0:
|
|
tcl={}
|
|
for i in myclientlist:
|
|
if len(gpg.list_keys(i['keyid']))>0:
|
|
tcl[i['ip']]=i['keyid']
|
|
if len(tcl)>0:
|
|
clientlist=tcl
|
|
|
|
#sqlinsert="insert into measures (time,id,value) values ({0:d},{1:d},{2:d})"
|
|
sqlinsert="insert into measures (time,id,value) values "
|
|
hsqlinsert="insert into hourly_measures (time,id,value) values "
|
|
dsqlinsert="insert into daily_measures (time,id,value) values "
|
|
asqlinsert="insert into last_measures (time,id,value) values "
|
|
|
|
|
|
measdata={}
|
|
|
|
_HASH="hash"
|
|
_SIGNEDGPG="signed_gpg"
|
|
_PAYLOAD="payload"
|
|
_MEASURES="measure"
|
|
_BEGINSIGNATURE="-----BEGIN PGP SIGNATURE-----"
|
|
_BEGINMESSAGE="-----BEGIN PGP SIGNED MESSAGE-----"
|
|
_BEGINHASH="Hash:"
|
|
_JSONDATA="data"
|
|
_JSONSIGNEDDATA="signed_data"
|
|
_JSONENCRYPTDATA="encrypted_data"
|
|
|
|
def analyse_jsonin(json_in,hash_id):
|
|
measdata={}
|
|
if _JSONDATA in json_in:
|
|
measdata=json_in[_JSONDATA]
|
|
if _JSONSIGNEDDATA in json_in:
|
|
vpgp=gpg.verify(json_in[_JSONSIGNEDDATA])
|
|
if hash_id != vpgp.key_id:
|
|
print("signature does not fit hash id")
|
|
else:
|
|
signed_in=json_in[_JSONSIGNEDDATA].split("\n")
|
|
signed_in[signed_in.index(_BEGINSIGNATURE):]=""
|
|
del signed_in[signed_in.index(_BEGINMESSAGE)]
|
|
del signed_in[signed_in.index("")]
|
|
for h in signed_in:
|
|
if _BEGINHASH in h:
|
|
del signed_in[signed_in.index(h)]
|
|
if len(signed_in)>0:
|
|
measdata=json.loads(signed_in[0])
|
|
if _JSONENCRYPTDATA in json_in:
|
|
dpgp=gpg.decrypt(json_in[_JSONENCRYPTDATA])
|
|
if hash_id != dpgp.key_id:
|
|
print("signature of encrypted data does not fit hash id")
|
|
else:
|
|
measdata=json.loads(dpgp.data)
|
|
if len(measdata)==0:
|
|
print("no data available")
|
|
else:
|
|
_thread.start_new_thread(insert_sql,(measdata,))
|
|
|
|
def insert_sql(measdata):
|
|
# tsi=sqlinsert
|
|
sqlu=[]
|
|
tsi=""
|
|
tsi_count=0
|
|
atsi=""
|
|
for h in measdata: # iterate over each variable stored in json
|
|
md=measdata[h]
|
|
sqlu.append("delete from last_measures where id = '"+str(h)+"';")
|
|
mmax=max(md['measures'])
|
|
atsi=atsi+'('+str(mmax)+','+h+','+str(md['measures'][mmax])+'),'
|
|
for m in md['measures']: # iterate over each measurement for given variable
|
|
try:
|
|
stime=int(m)
|
|
except:
|
|
print("wrong entry")
|
|
else:
|
|
ttsi='('+str(m)+','+h+','+str(md['measures'][m])+'),'
|
|
tsi=tsi+ttsi
|
|
tsi_count=tsi_count+1
|
|
tsi=tsi[:-1]+';'
|
|
atsi=atsi[:-1]+';'
|
|
try:
|
|
mydb=pymysql.connect(read_default_file="~/.my.cnf",database="rasolar")
|
|
except:
|
|
print("could not connect to mysql")
|
|
with open("missed.sql","a") as sqlstore:
|
|
sqlstore.write(tsi)
|
|
else:
|
|
mycursor=mydb.cursor(pymysql.cursors.DictCursor)
|
|
for h in measdata:
|
|
mycursor.execute('select id from ids where id='+h+';')
|
|
t=mycursor.fetchall()
|
|
if len(t)==0:
|
|
hsql='insert into ids (id,device,varname,sensor,sensorsub,i2c) values ('+h+','
|
|
hm=measdata[h]
|
|
ht={}
|
|
for i in ['device','varname','sensor','sensorsub']:
|
|
if i in hm:
|
|
hsql=hsql+"'"+hm[i]+"'"+','
|
|
else:
|
|
hsql=hsql+"\'\',"
|
|
if 'i2c' in hm:
|
|
try:
|
|
hsql=hsql+"'"+str(hm['i2c'])+"'"
|
|
except:
|
|
hsql=hsql+"'0'"
|
|
else:
|
|
hsql=hsql+0
|
|
hsql=hsql+');'
|
|
try:
|
|
mycursor.execute(hsql)
|
|
except:
|
|
print("could not insert new var_id")
|
|
print(hsql)
|
|
for dsql in sqlu:
|
|
mycursor.execute(dsql)
|
|
mycursor.execute(sqlinsert+tsi)
|
|
print(sqlinsert+tsi)
|
|
mycursor.execute(hsqlinsert+tsi)
|
|
mycursor.execute(dsqlinsert+tsi)
|
|
mycursor.execute(asqlinsert+atsi)
|
|
delhtime=int((time.time()-3600)*1000)
|
|
deldtime=int((time.time()-86400)*1000)
|
|
mycursor.execute("delete from hourly_measures where time < "+str(delhtime))
|
|
mycursor.execute("delete from daily_measures where time < "+str(deldtime))
|
|
print(str(tsi_count)+" new measures inserted")
|
|
mycursor.close()
|
|
mydb.commit()
|
|
mydb.close()
|
|
# print(tsi)
|
|
# print(measdata)
|
|
|
|
|
|
|
|
app=Bottle()
|
|
#bottlesqp=bottle_mysql.Plugin(read_default_file='~/.my.cnf',db="rasolar")
|
|
#app.install(plugin)
|
|
|
|
@app.get('/')
|
|
def approot():
|
|
return '''
|
|
<a href="/ids">Uebersicht</a></br>
|
|
<a href="/solar">Solarübersicht</a></br>
|
|
<a href="/cpu">CPU-Temperaturen</a></br>
|
|
'''
|
|
|
|
|
|
@app.get('/ids')
|
|
def show_ids():
|
|
mydb=pymysql.connect(read_default_file="~/.my.cnf",database="rasolar")
|
|
mycursor=mydb.cursor(pymysql.cursors.DictCursor)
|
|
starttime=time.time()
|
|
mycursor.execute('select gm.*,last_measures.value,ids.varname,ids.device,ids.sensor,ids.i2c,ids.sensorsub from (select id, max(time) as time,count(time) as count from last_measures group by id) gm join last_measures on last_measures.id=gm.id and last_measures.time=gm.time join ids on ids.id=gm.id;')
|
|
print("ids sql duration:"+str(time.time()-starttime))
|
|
row=mycursor.fetchall()
|
|
mycursor.close()
|
|
mydb.close()
|
|
return template('ids.tpl',measdata=row)
|
|
|
|
@app.get('/solar')
|
|
def show_solar():
|
|
mydb=pymysql.connect(read_default_file="~/.my.cnf",database="rasolar")
|
|
myc=mydb.cursor()
|
|
myc.execute("select gm.time,last_measures.value/1000,ids.varname from (select id, max(time) as time from last_measures group by id) gm join last_measures on last_measures.id=gm.id and last_measures.time=gm.time join ids on ids.id=gm.id where ids.sensor='tristar';")
|
|
ts=myc.fetchall()
|
|
myc.close()
|
|
mydb.close()
|
|
tt={"kwh_tot":0,"volt_bat":0,"volt_bat_sens":0,"volt_sweep_mc":0,"volt_sweep_oc":0,"amp_bat":0,"temp_heatsink":0,"temp_bat":0}
|
|
zs=0
|
|
for tims,value,varname in ts:
|
|
tt[varname]=value
|
|
if int(tims)>zs:
|
|
zs=int(tims)
|
|
return template('solar.tpl',solardata=tt,zeitstempel=time.strftime('%H:%M:%S %Y-%m-%d', time.localtime(zs/1000)))
|
|
|
|
@app.get('/temp')
|
|
def show_temperature():
|
|
mydb=pymysql.connect(read_default_file="~/.my.cnf",database="rasolar")
|
|
myc=mydb.cursor()
|
|
myc.execute("select gm.time,last_measures.value/1000,ids.varname,ids.device,short_names.short from (select id, max(time) as time from last_measures group by id) gm join last_measures on last_measures.id=gm.id and last_measures.time=gm.time join ids on ids.id=gm.id join short_names on short_names.id=gm.id;")
|
|
ts=myc.fetchall()
|
|
myc.close()
|
|
mydb.close()
|
|
tt={"temp_garten":0,"temp_dach":0,"temp_carport":0,"hum_garten":0,"hum_dach":0,"hum_carport":0,"press_garten":0,"press_dach":0,"press_carport":0,"lux_dach":0,"uv_dach":0,"temp_solar":0,"temp_bat":0,"temp_wohnen":0,"hum_wohnen":0,"press_wohnen":0}
|
|
zs=0
|
|
thp={"rasolar":{"temperature":0,"humidity":0,"pressure":0,"time":0},"ragps":{"temperature":0,"humidity":0,"pressure":0,"time":0},"ragarden":{"temperature":0,"humidity":0,"pressure":0,"time":0}}
|
|
|
|
for tims,value,varname,device,short in ts:
|
|
tt[short]=value
|
|
try:
|
|
thp[device][varname]=value
|
|
except:
|
|
thp[device]={varname:value}
|
|
try:
|
|
if thp[device]['time']<tims:
|
|
thp[device]['time']=tims
|
|
except:
|
|
thp[device]['time']=tims
|
|
if int(tims)>zs:
|
|
zs=int(tims)
|
|
return template('temperature.tpl',tempdata=tt,zeitstempel=time.strftime('%H:%M:%S %Y-%m-%d', time.localtime(zs/1000)))
|
|
|
|
@app.get('/clients')
|
|
def show_clients():
|
|
mydb=pymysql.connect(read_default_file="~/.my.cnf",database="rasolar")
|
|
myc=mydb.cursor()
|
|
no_clients=myc.execute('select distinct ids.device from ids join hourly_measures on hourly_measures.id=ids.id;')
|
|
if no_clients > 0:
|
|
t_cl=myc.fetchall()
|
|
client_list=[]
|
|
for cl in t_cl:
|
|
client_list.append(cl[0])
|
|
return template('clients.tpl',clientdata=client_list)
|
|
else:
|
|
return '''
|
|
<!DOCTYPE html><html><head>
|
|
<title>Übersicht Tristar</title>
|
|
</head><body>
|
|
<p>Aktuell keine Rechner verbunden</p>
|
|
</body></html>
|
|
'''
|
|
@app.get('/client/<client>')
|
|
def forward_client(client):
|
|
response=requests.get('http://'+client+':8080/')
|
|
print(requests.get('http://'+client+':8080/'))
|
|
if response.status_code == 200:
|
|
return response.content
|
|
else:
|
|
return '''
|
|
<!DOCTYPE html><html><head>
|
|
<title>Keine Verbindung</title>
|
|
</head><body>
|
|
<p>Kein Anschluss unter dieser Himbeere</p>
|
|
</body></html>
|
|
'''
|
|
|
|
@app.get('/cpu')
|
|
def show_ids():
|
|
starttime=time.time()
|
|
mydb=pymysql.connect(read_default_file="~/.my.cnf",database="rasolar")
|
|
myc=mydb.cursor(pymysql.cursors.DictCursor)
|
|
myc.execute('select * from ids where sensor="CPU";')
|
|
channels=myc.fetchall()
|
|
myc.close()
|
|
cpu_col=cm.rainbow(numpy.linspace(0,1,len(channels)))
|
|
plt.figure(figsize=[6,8])
|
|
mycursor=mydb.cursor()
|
|
for j in range(len(channels)):
|
|
mycursor.execute('select ((select max(time) from hourly_measures)-hourly_measures.time)/1000 as time,hourly_measures.value/1000 as value from hourly_measures join ids on ids.id=hourly_measures.id where ids.sensor="CPU" and ids.id='+str(channels[j]['id'])+';')
|
|
row=numpy.array(mycursor.fetchall())
|
|
plt.plot(row[:,0],row[:,1],color=cpu_col[j],label=channels[j]['varname']+'; '+channels[j]['device'])
|
|
plt.legend()
|
|
print(str(time.time()-starttime)+"s for fetching and display")
|
|
plt.title('CPU Verlauf')
|
|
plt.savefig("svg/cpu.svg")
|
|
mycursor.close()
|
|
mydb.close()
|
|
if len(row)>0:
|
|
return template('cputemp.tpl',measdata=row)
|
|
|
|
@app.get('/svg/<svgfile>')
|
|
def show_svg(svgfile):
|
|
return static_file("svg/"+svgfile,root=abspath)
|
|
|
|
@app.get('/graph/<mid>/<kind>')
|
|
def show_graph(mid,kind):
|
|
sqltable="hourly_measures"
|
|
if(kind=="daily"):
|
|
sqltable="daily_measures"
|
|
starttime=time.time()
|
|
mydb=pymysql.connect(read_default_file="~/.my.cnf",database="rasolar")
|
|
mycursor=mydb.cursor()
|
|
print(str(time.time()-starttime)+' sql open')
|
|
nrow=mycursor.execute('select ((select max(time) from '+sqltable+')-'+sqltable+'.time)/3600000 as time,'+sqltable+'.value/1000 as value from '+sqltable+' where id=%s order by time',str(mid))
|
|
if (nrow>0):
|
|
row=numpy.array(mycursor.fetchall())
|
|
print(str(time.time()-starttime)+' sql fetched')
|
|
nirow=mycursor.execute('select id,varname,device,sensor,i2c from ids where id='+str(mid))
|
|
gdata=mycursor.fetchall()
|
|
mycursor.close()
|
|
mydb.close()
|
|
kanal_info={"varname":"","device":"","sensor":"","i2c":0,"count":nrow}
|
|
for ids,varname,device,sensor,i2c in gdata:
|
|
kanal_info["varname"]=varname
|
|
kanal_info["device"]=device
|
|
kanal_info["sensor"]=sensor
|
|
kanal_info["i2c"]=int(i2c)
|
|
print(str(time.time()-starttime)+' sql closed')
|
|
plt.figure(figsize=[6,8])
|
|
plt.plot(row[:,0],row[:,1])
|
|
print(str(time.time()-starttime)+' picture')
|
|
plt.savefig("svg/"+mid+".svg")
|
|
print(str(time.time()-starttime)+' saved')
|
|
if len(row)>0:
|
|
return template('verlauf.tpl',measdata=row,mid=mid,kanal_info=kanal_info)
|
|
else:
|
|
return template('''
|
|
<!DOCTYPE html><html><head>
|
|
<title>Keine Daten</title>
|
|
</head><body>
|
|
<p>Die Daten verstecken sich.</p>
|
|
<a href="/graph/{{mid}}/hourly">Letzte Stunde</a></br>
|
|
<a href="/graph/{{mid}}/daily">Letzter Tag</a></br>
|
|
</body></html>
|
|
''',mid=mid)
|
|
|
|
@app.get('/graph/<mid>')
|
|
def show_graph_short(mid):
|
|
show_graph(mid,'hourly')
|
|
|
|
@app.post('/data/<hash_id>')
|
|
def dataimport(hash_id):
|
|
# print(hash_id)
|
|
timestart=time.time()
|
|
# check if request comes from allowed ip
|
|
if (request.remote_addr in clientlist):
|
|
# hash must be the used gpg key id
|
|
if (hash_id in clientlist[request.remote_addr]):
|
|
# print("correct id")
|
|
# check, if json is transmitted
|
|
try:
|
|
json_in=json.loads(request.json)
|
|
# print(json_in)
|
|
except:
|
|
print("no json")
|
|
else:
|
|
print(time.time()-timestart)
|
|
_thread.start_new_thread(analyse_jsonin,(json_in,hash_id,))
|
|
else:
|
|
print("wrong id")
|
|
else:
|
|
print("not allowed client address")
|
|
|
|
|
|
|
|
run(app,host="",port=8081)
|
|
|
|
|
|
mydb=pymysql.connect(read_default_file="~/.my.cnf",database="rasolar")
|
|
#mycursor=mydb.cursor(pymysql.cursors.DictCursor)
|
|
mycursor=mydb.cursor(pymysql.cursors.DictCursor)
|
|
mycursor.execute('select gm.*,measures.value,ids.varname,ids.device,ids.sensor,ids.i2c,ids.sensorsub from (select id, max(time) as time,count(time) as count from measures group by id) gm join measures on measures.id=gm.id and measures.time=gm.time join ids on ids.id=gm.id where gm.time>= ((select max(time) from measures)-24*6400000);')
|
|
row=mycursor.fetchall()
|
|
mycursor.close()
|
|
mydb.close()
|