pysql/pyweb.py

#!/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()