actuatordbmod.py

# -*- coding: utf-8 -*-
"""
utility for the planning database
"""
from __future__ import print_function
from __future__ import division

from builtins import str
from builtins import range
from past.utils import old_div
import logging
import string
from datetime import datetime,date,timedelta
import databasemod
import random
import hardwaremod

# ///////////////// -- GLOBAL VARIABLES AND INIZIALIZATION --- //////////////////////////////////////////

global DBFILENAME
DBFILENAME = 'Actuatordb.db'
global TIMEFIELD
global DATAFIELD
TIMEFIELD='readtime'
DATAFIELD='data1'

# ///////////////// --- END GLOBAL VARIABLES ------


# ///////////////// -- MODULE INIZIALIZATION --- //////////////////////////////////////////

databasemod.init_db(DBFILENAME)
tablelist=hardwaremod.searchdatalist(hardwaremod.HW_INFO_IOTYPE,"output",hardwaremod.HW_INFO_NAME)
databasemod.aligndbtable(DBFILENAME, tablelist)

# ///////////////// --- END init




#-- start DB utility--------////////////////////////////////////////////////////////////////////////////////////	


def init_db():
	databasemod.init_db(DBFILENAME)


def gettablelist():
	tablelist=hardwaremod.searchdatalist(hardwaremod.HW_INFO_IOTYPE,"output",hardwaremod.HW_INFO_NAME)
	return tablelist

def gettablenameapprox(stringtofind):
	valuelist=[]
	outlist=[]
	hardwaremod.getfieldinstringvalue(hardwaremod.HW_INFO_NAME,stringtofind,valuelist)
	tablelist=gettablelist()	
	for value in valuelist:
		if 	value in tablelist:
			outlist.append(value)
	return outlist


def columninfo():
	databasemod.columninfo(DBFILENAME,DBTABLE)
	
def insertdataintable(table,datavalue):
	rowfield=[]
	rowfield.append(TIMEFIELD)
	rowfield.append(DATAFIELD)	
	rowvalue=[]
	rowvalue.append(datetime.now().replace(microsecond=0))
	rowvalue.append(datavalue)
	databasemod.insertrowfields(DBFILENAME,table,rowfield,rowvalue)

def deleterowwithfield(tablename,field,value):
	databasemod.deleterowwithfield(DBFILENAME,tablename,field,value)


#--end --------////////////////////////////////////////////////////////////////////////////////////		

#-- start specific actuator utility--------////////////////////////////////////////////////////////////////////////////////////	

def deleteallrow():
	tablelist=gettablelist()
	for tablename in tablelist :
		databasemod.deleteallrow(DBFILENAME,tablename)

def getActuatordbdata(selsensor,sensordata):
	fieldlist=[]
	fieldlist.append(TIMEFIELD)
	fieldlist.append(DATAFIELD)
	databasemod.getdatafromfields(DBFILENAME,selsensor,fieldlist,sensordata)
	
def getActuatorDataPeriod(selsensor,sensordata,enddate,pastdays):
	tdelta=0;
	num = int(pastdays)
	tdelta=timedelta(days=num)
	startdate=enddate-tdelta
	#print startdate
	#print enddate
	allsensordata=[]
	getActuatordbdata(selsensor,allsensordata)
	del sensordata[:]
	for rowdata in allsensordata:
		dateref=datetime.strptime(rowdata[0].split(".")[0],'%Y-%m-%d %H:%M:%S')
		if (dateref>=startdate)and(dateref<=enddate):
			try:
				value=float(rowdata[1])
				templist=[rowdata[0], value]
				sensordata.append(templist)
			except ValueError:
				print("Error in database reading ",rowdata)

	# sensor data --------------------------------------------


def getactuatordbdatadaysV2(selsensor,sensordata,startdate, enddate): # V2 try to optimize access to database

	fieldlist=[]
	fieldlist.append(TIMEFIELD)
	fieldlist.append(DATAFIELD)

	# no info about number of samples per day try with a number
	#samplingintervalminutes=5 # assumption
	#samplesnumber=int(days*24*60/samplingintervalminutes)
	samplesnumber=1000
	offset=0
	lastitem=2
	dateref=enddate
	rowdata=[]
	while (dateref>startdate)and(lastitem>0):
		print (selsensor, " Get Database Data : samplesnumber ", samplesnumber, " offset ", offset)
		partrowdata=databasemod.returnrowdatafromfieldslimitV2(DBFILENAME,selsensor,fieldlist,samplesnumber,offset)	
		lastitem=len(partrowdata)
		if lastitem>=samplesnumber:
			offset=offset+lastitem
			dateref=datetime.strptime(partrowdata[lastitem-1][0].split(".")[0],'%Y-%m-%d %H:%M:%S')
		else:
			lastitem=0
		#print( " dataref ", dateref)			
		#print( " rowdata ", rowdata)
		print (selsensor, " Get Database Data : dateref ", dateref, " DB part lenght ", lastitem)
		rowdata.extend(partrowdata)
		
	# check the last value of the returned array

	# return only the data in right datetime interval and in list form (instead of tuple)
	for data in rowdata:
		try:
			dateref=datetime.strptime(data[0].split(".")[0],'%Y-%m-%d %H:%M:%S')
			if (dateref>=startdate)and(dateref<=enddate):
				value=float(data[1])
				templist=[data[0], value]
				sensordata.append(templist)
				
		except:
			print("Error in database reading ",rowdata)
 




	
def getAllActuatorDataPeriodv2(enddate,pastdays):

	num = int(pastdays)
	tdelta=timedelta(days=num)
	startdate=enddate-tdelta


	outputallsensordata=[]
	usedsensorlist=[]
	sensorlist=gettablelist()

	for selsensor in sensorlist:
		sensordata=[]

		getactuatordbdatadaysV2(selsensor,sensordata,startdate, enddate)

		if len(sensordata)>0:
			outputallsensordata.append(sensordata)
			usedsensorlist.append(selsensor)

	return outputallsensordata,usedsensorlist
	# sensor data --------------------------------------------


def RemoveActuatorDataPeriod(removebeforedays, maxremovepersensor=300):
	sensordata=[]
	field=TIMEFIELD
	startdate=datetime.now()
	num = removebeforedays
	tdelta=timedelta(days=num)
	enddate=startdate-tdelta
	pastdays=364
	
	actuatorlist=gettablelist()
	for selsensor in actuatorlist:
		getActuatorDataPeriod(selsensor,sensordata,enddate,pastdays)
		#print "page ", selsensor
		#print sensordata
		itemnum=min(maxremovepersensor, len(sensordata))
		for i in range(itemnum):
			deleterowwithfield(selsensor,field,sensordata[i][0])

	# sensor data --------------------------------------------
	

	

def EvaluateDataPeriod(sensordata,startdate,enddate):
	# sensor data --------------------------------------------
	isok=False
	outputdata={}
	summa=0
	inde=0
	for data in sensordata:
		dateref=datetime.strptime(data[0],'%Y-%m-%d %H:%M:%S')
		if (dateref>=startdate)and(dateref<=enddate):
			try:
				number=float(data[1])
				if inde==0:
					mini=number
					maxi=number
				else:
					if mini>number:
						mini=number
					if maxi<number:
						maxi=number
				summa=summa+number
				inde=inde+1
			except ValueError:
				print("Evaluation : Error in database reading ",dateref , "  " ,data[1])
	
	
	if inde>0:
		average=old_div(summa,inde)
		isok=True
	else:
		average=0
		mini=0
		maxi=0
		
	outputdata["sum"]=summa		
	outputdata["average"]=average
	outputdata["min"]=mini
	outputdata["max"]=maxi	
	return isok , outputdata








	
def SumProductDataPeriod(sensordata,startdate,enddate,timeinterval):
	# sensor data --------------------------------------------
	sum=0
	for data in sensordata:
		dateref=datetime.strptime(data[0],'%Y-%m-%d %H:%M:%S')
		if (dateref>=startdate)and(dateref<=enddate):
			try:
				sum=sum+float(data[1])*timeinterval
			except ValueError:
				print(data[1])
	return sum

	
	
def insertrandomrecords(recordnumber):
	rowfield=databasemod.rowdescription(DBFILENAME,DBTABLE,1)
	for i in range(recordnumber):
		randomvalues=[]
		randomvalues.append(datetime.now().replace(microsecond=0))
		for j in range(len(rowfield)-1):
			randomvalues.append(random.randrange(1,101,1))
		# waste time
		for j in range(20000):
			a=1
		insertrowfields(rowfield,randomvalues)

#--end --------////////////////////////////////////////////////////////////////////////////////////	


def sensorsysinfomatrix():
	# first row includes headers
	matrix=[]
	row=[]
	row.append("Name")
	row.append("Use")
	row.append("Unit")
	row.append("Average 24H")	
	matrix.append(row)
	
	namelist=hardwaremod.searchdatalist(hardwaremod.HW_CTRL_CMD,"pulse",hardwaremod.HW_INFO_NAME)
	for name in namelist:
		row=[]
		row.append(name)
		row.append(hardwaremod.searchdata(hardwaremod.HW_INFO_NAME,name,hardwaremod.HW_FUNC_USEDFOR))
		row.append(hardwaremod.searchdata(hardwaremod.HW_INFO_NAME,name,hardwaremod.HW_INFO_MEASUREUNIT))

		endtime=datetime.now()
		starttime= endtime - timedelta(days=1)
		data=[]
		getActuatordbdata(name,data)
		isok, evaluateddata=EvaluateDataPeriod(data,starttime,endtime)	#set date interval for average

		row.append(str('%.1f' % (evaluateddata["sum"])))

		matrix.append(row)
	
	return matrix


def consistencycheck():
	# this routine align the database table elements with the Hardware available elements "IOtype" labelled with usedfor "input"
	tablelist=gettablelist()
	databasemod.aligndbtable(DBFILENAME, tablelist)


if __name__ == '__main__':

	DBFILENAME='Sensordb.db'
	init_db()
	insertrandomrecords(5)
	sensordata=[]
	getsensordbdata("temp1",sensordata)
	getSensorDataPeriod("temp1",sensordata,datetime.now(),1)
	print("data: ")
	print(sensordata)
	rowvalue=[]
	teperatura=10
	PHreading=10
	ECreading=10
	light=10
	rowvalue.append(datetime.now().replace(microsecond=0))
	rowvalue.append(teperatura)
	rowvalue.append(PHreading)
	rowvalue.append(ECreading)
	rowvalue.append(light)
	rowfield=[]
	rowfield.append("readtime")
	rowfield.append("temp1")
	rowfield.append("ph1")
	rowfield.append("ec1")
	rowfield.append("light1")
	insertrowfields(rowfield,rowvalue)