def getChanges(db, numberRows, timeInterleave=10):
# compute the change
timeBetween = DataInfo.dataInfo.timeBetweenSensorReads
rangeInbetween = int(timeInterleave / (timeBetween + 1))
rowCount = sqlhelper.countRows(db)
sensorCount = DataInfo.dataInfo.numberSensors
# get the last 6 rows
offset = sensorCount * rangeInbetween
rows = []
for rowIndex in range(0, numberRows):
offsetIndex = offset * rowIndex
rows.append(
sqlhelper.getRows(db, rowCount - sensorCount - offsetIndex + 1,
rowCount - offsetIndex))
# set up a return list that has change for each sensor
changeArray = []
for rowIndex in range(0, numberRows):
changeArray.append([None] * (sensorCount + 1))
changeArray[rowIndex][0] = rows[rowIndex][1][3]
for sensor in range(0, sensorCount):
sensorId = rows[rowIndex][sensor][1]
v = round(rows[rowIndex][sensorId - 1][5], 1)
changeArray[rowIndex][sensorId] = v
return changeArray
def summary():
page = "<!DOCTYPE html>"
page = page + "<html><h1>Web Thermometer Sensor Summary</h1><hr><br>"
db = sqlhelper.createConnection(sqlhelper.dbfilename)
rowCount = sqlhelper.countRows(db)
page = page + "Number of data points in table: " + str(rowCount) + '<p>'
row = sqlhelper.getRow(db, rowCount)
# display how many sensor are collecting data
sensorCount = infohelper.numberSensors(db)
page = page + "Number of sensors: " + str(sensorCount) + '<p>'
# display how many sensor are collecting data
rateOfChange = infohelper.getRateOfChange(db)
page = page + "Rate of change: " + str(rateOfChange) + '<p>'
# display time elapsed between sensor writes
timeBetweenReads = infohelper.timeBetweenSensorReads(db)
page = page + "Time between sensor reads: " + str(timeBetweenReads) + '<p>'
# display trends over past 10 mins
rows = sqlhelper.getRows(db, rowCount - 12, rowCount)
page = page + "Last 12 rows: <p>"
for r in rows:
page = page + str(r) + '<p>'
page = page + "Last data point: " + str(row) + '<p>'
db.close()
page = page + "</html>"
return page
def getTimeBetweenSensorReads(self, db):
# get the DB info
rowCount = sqlhelper.countRows(db)
row = sqlhelper.getRow(db, rowCount)
sensorCount = self.getNumberOfSensors(db)
timeStampLast = row[4]
row = sqlhelper.getRow(db, rowCount - sensorCount)
timeStampLastBefore = row[4]
date_format = "%Y-%m-%d %H:%M:%S.%f"
d1 = datetime.strptime(timeStampLastBefore, date_format)
d2 = datetime.strptime(timeStampLast, date_format)
seconds = int((d2 - d1).total_seconds())
return seconds
def getNumberOfSensors(self, db):
# get the DB info
rowCount = sqlhelper.countRows(db)
# count sensors by looking at rows
sensorCount = 0
rowidx = rowCount
row = sqlhelper.getRow(db, rowidx)
lastSensorId = row[1]
currentSensorId = -1
while (lastSensorId != currentSensorId) and (rowidx > 0):
rowidx = rowidx - 1
row = sqlhelper.getRow(db, rowidx)
currentSensorId = row[1]
sensorCount = sensorCount + 1
return sensorCount
def getChange(db, rangeInbetween=10):
# compute the change
timeBetween = DataInfo.dataInfo.timeBetweenSensorReads
rowCount = sqlhelper.countRows(db)
sensorCount = DataInfo.dataInfo.numberSensors
# get the last 6 rows
offset = sensorCount * rangeInbetween
lastRows = sqlhelper.getRows(db, rowCount - sensorCount, rowCount)
beforeRows = sqlhelper.getRows(db, rowCount - sensorCount - offset,
rowCount - offset)
# set up a return list that has change for each sensor
changeList = [None] * sensorCount
for i in range(0, sensorCount):
sensorId = lastRows[i][1]
valueLast = lastRows[sensorId][5]
valueBefore = beforeRows[sensorId][5]
changeList[sensorId - 1] = valueLast - valueBefore
return changeList
def csv():
# return all data as CSV (comma seperated values)
page = ""
db = sqlhelper.createConnection(sqlhelper.dbfilename)
numrows = sqlhelper.countRows(db)
for id in range(0, numrows):
row = sqlhelper.getRow(db, id)
#print(row)
if row != None:
channel = row[1]
if (channel == 1):
datestamp = row[2]
timestamp = row[3]
page = page + "\n"
page = page + datestamp + ' ' + timestamp
page = page + ", " + str(row[5])
return page