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