8a5c0b910cddf41551520c31a46dded79ad595f6
[openhab-process.git] / filter_sensors.py
1 #!/usr/bin/python
2
3 import MySQLdb
4 import ConfigParser
5
6 from pprint import pprint
7 import datetime
8
9 import numpy as np
10
11 import scipy.signal
12
13 global database
14
15 def GetTables(name):
16   if database:
17     c = database.cursor()
18     c.execute("SELECT * FROM Items WHERE ItemName like %s",[name])
19     return c.fetchall()
20   else:
21     print "No connection to DB"
22     exit()
23
24 def Today():
25   dt = datetime.datetime.now()
26   d_truncated = datetime.date(dt.year, dt.month, dt.day)
27   return d_truncated
28   
29 def Tomorrow():
30   dt = Today()
31   return dt + datetime.timedelta(days=1)
32
33 def Yesterday():
34   dt = Today()
35   return dt - datetime.timedelta(days=1)
36
37 def GetData(id,fromDate=Yesterday(),toDate=Today()):
38   if database:
39     c = database.cursor()
40     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')])
41     return c.fetchall()
42   else:
43     print "No connection to DB"
44     exit()
45
46 def FixRecord(id,date,value):
47   if database:
48     c = database.cursor()
49     command="UPDATE Item"+str(id).strip()+" SET Value={} WHERE Time='{}'".format(value,date.strftime('%Y-%m-%d %H:%M:%S'))
50     print command
51     c.execute(command)
52   else:
53     print "No connection to DB"
54     exit()
55
56 try:
57
58   cfg = ConfigParser.RawConfigParser(allow_no_value=True)
59   cfg.readfp(open('/etc/openhab-db.conf'))
60   dbhost = cfg.get("mysql","host")
61   dbuser = cfg.get("mysql","user")
62   dbpasswd = cfg.get("mysql","passwd")
63   dbdb = cfg.get("mysql","db")
64
65   itemTemplate = cfg.get("openhab","template")
66   
67   filterWindow = int(cfg.get("filter","window"))
68   filterThreshold = float(cfg.get("filter","threshold"))
69    
70 except:
71
72   print "Error reading configuration file"
73   exit()
74
75 try:
76
77   database = MySQLdb.connect(host=dbhost,user=dbuser,passwd=dbpasswd,db=dbdb,use_unicode=True)
78   database.set_character_set('utf8')
79   c = database.cursor()
80   c.execute('SET NAMES utf8;')
81
82   print "Connected..."
83
84 except:
85
86   print "Error connecting database"
87   exit()
88
89 tables = GetTables(itemTemplate)
90
91 for id,name in tables:
92
93   print "Processing: "+name
94
95   data=GetData(id)
96   sTime=[]
97   sValue=[]
98   for rec in data:
99     sTime.append(rec[0])
100     sValue.append(rec[1])
101   sValue=np.array(sValue)
102
103   sValueFilt=scipy.signal.medfilt(sValue,5)
104
105   sValueDiff=abs(sValue-sValueFilt)
106   
107   avg=np.mean(sValueDiff)
108
109   for i in range(0,len(sTime)-1):
110     if sValueDiff[i]>avg*filterThreshold:
111       print "fixing %s : %5.2f %5.2f %5.2f" % (sTime[i],sValue[i],sValueFilt[i],sValueDiff[i])
112       FixRecord(id,sTime[i],sValueFilt[i])      
113
114   database.commit()
115
116 print "Processed "
117