From f73d4c08f9b3d93ad6e4a6b35b164e4ae0f1e4be Mon Sep 17 00:00:00 2001 From: Roman Bazalevsky Date: Thu, 22 Sep 2016 15:21:13 +0300 Subject: [PATCH 1/1] =?utf8?q?=D0=A4=D0=B8=D0=BB=D1=8C=D1=82=D1=80=D0=B0?= =?utf8?q?=D1=86=D0=B8=D1=8F=20=D0=B2=D1=8B=D0=B1=D1=80=D0=BE=D1=81=D0=BE?= =?utf8?q?=D0=B2=20=D0=B2=20=D0=B8=D0=B7=D0=BC=D0=B5=D1=80=D0=B5=D0=BD?= =?utf8?q?=D0=B8=D1=8F=D1=85?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- filter_sensors.py | 117 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 117 insertions(+) create mode 100644 filter_sensors.py diff --git a/filter_sensors.py b/filter_sensors.py new file mode 100644 index 0000000..8a5c0b9 --- /dev/null +++ b/filter_sensors.py @@ -0,0 +1,117 @@ +#!/usr/bin/python + +import MySQLdb +import ConfigParser + +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() + +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 + + data=GetData(id) + 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() + +print "Processed " + \ No newline at end of file -- 2.34.1