+++ /dev/null
-#!/usr/bin/python
-
-import MySQLdb
-import ConfigParser
-import sys
-
-from pprint import pprint
-import datetime
-
-import numpy as np
-
-import scipy.signal
-
-global database
-
-def GetTables(name):
- if database:
- c = database.cursor()
- c.execute("SELECT * FROM Items WHERE ItemName like %s",[name])
- return c.fetchall()
- else:
- print "No connection to DB"
- exit()
-
-def Today():
- dt = datetime.datetime.now()
- d_truncated = datetime.date(dt.year, dt.month, dt.day)
- return d_truncated
-
-def Tomorrow():
- dt = Today()
- return dt + datetime.timedelta(days=1)
-
-def Yesterday():
- dt = Today()
- return dt - datetime.timedelta(days=1)
-
-def GetData(id,fromDate=Yesterday(),toDate=Today()):
- if database:
- c = database.cursor()
- c.execute("SELECT * FROM Item"+str(id).strip()+" WHERE Time>=%s AND Time<%s",[fromDate.strftime('%Y-%m-%d %H:%M:%S'),toDate.strftime('%Y-%m-%d %H:%M:%S')])
- return c.fetchall()
- else:
- print "No connection to DB"
- exit()
-
-def FixRecord(id,date,value):
- if database:
- c = database.cursor()
- command="UPDATE Item"+str(id).strip()+" SET Value={} WHERE Time='{}'".format(value,date.strftime('%Y-%m-%d %H:%M:%S'))
- print command
- c.execute(command)
- else:
- print "No connection to DB"
- exit()
-
-def ProcessTable(id):
-
- if not current:
- data=GetData(id)
- else:
- data=GetData(id,Today(),Tomorrow())
- sTime=[]
- sValue=[]
- for rec in data:
- sTime.append(rec[0])
- sValue.append(rec[1])
- sValue=np.array(sValue)
-
- sValueFilt=scipy.signal.medfilt(sValue,5)
-
- sValueDiff=abs(sValue-sValueFilt)
-
- avg=np.mean(sValueDiff)
-
- for i in range(0,len(sTime)-1):
- if sValueDiff[i]>avg*filterThreshold:
- print "fixing %s : %5.2f %5.2f %5.2f" % (sTime[i],sValue[i],sValueFilt[i],sValueDiff[i])
- FixRecord(id,sTime[i],sValueFilt[i])
-
- database.commit()
-
-if len(sys.argv)==2 and sys.argv[1]=='current':
- current=True
-else:
- current=False
-
-
-try:
-
- cfg = ConfigParser.RawConfigParser(allow_no_value=True)
- cfg.readfp(open('/etc/openhab-db.conf'))
- dbhost = cfg.get("mysql","host")
- dbuser = cfg.get("mysql","user")
- dbpasswd = cfg.get("mysql","passwd")
- dbdb = cfg.get("mysql","db")
-
- itemTemplate = cfg.get("openhab","template")
-
- filterWindow = int(cfg.get("filter","window"))
- filterThreshold = float(cfg.get("filter","threshold"))
-
-except:
-
- print "Error reading configuration file"
- exit()
-
-try:
-
- database = MySQLdb.connect(host=dbhost,user=dbuser,passwd=dbpasswd,db=dbdb,use_unicode=True)
- database.set_character_set('utf8')
- c = database.cursor()
- c.execute('SET NAMES utf8;')
-
- print "Connected..."
-
-except:
-
- print "Error connecting database"
- exit()
-
-tables = GetTables(itemTemplate)
-
-for id,name in tables:
-
- print "Processing: "+name
-
- ProcessTable(id)
-
-print "Processed "
-
\ No newline at end of file