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