From f73d4c08f9b3d93ad6e4a6b35b164e4ae0f1e4be Mon Sep 17 00:00:00 2001
From: Roman Bazalevsky <rvb@rvb.name>
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