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