#! /usr/bin/python

# Set PYTHONPATH (py and py/lib) and PATH (./py)
# Installed pySerial, pywin32, paramiko, pycrypto from msi, MySQL-python.1.2.3 from msi

# Reciever address:
# 13A200
# 407B2C5B
# PAN ID: 3332

from xbee import XBee
import serial
import _mysql
from datetime import datetime, date 
import logging
from time import localtime, strftime, time
import time
import struct
import sys

def main():
	initLogger()	
	initSql()
	gatherValues()

def initLogger():
	try:
		global logger 
		logger = logging.getLogger('myapp')
		hdlr = logging.FileHandler('C:/myHomeWebsite/logs/gatherValues.log')
		formatter = logging.Formatter('%(asctime)s %(levelname)s %(message)s')
		hdlr.setFormatter(formatter)
		logger.addHandler(hdlr) 
		logger.setLevel(logging.DEBUG)
	except:
		print 'ERROR: unable to init logger!'
		
def initSql():
	try:
		global mySqlCon 
		mySqlCon = _mysql.connect('localhost', 'username', 'password', 'myhome')
	except MySQLdb.Error, e:
		print "Could not init SQL %d: %s" % (e.args[0], e.args[1])
		logger.error('Could not init SQL %d: %s" % (e.args[0], e.args[1])')

def gatherValues():
	print 'Starting program'
	#logger.debug('GatherValues beginning')
	PORT = 'COM3'
	BAUD_RATE = 9600
	# Open serial port
	global ser
	ser = serial.Serial(PORT, BAUD_RATE)
	logger.debug('Initialization complete. Waiting for values...')
	
	global xbee
	xbee = XBee(ser, callback=message_received)
	
	# Set contrast and brightness
	xbee.tx(dest_addr=b"\x00\x05", data=b"\xFE\x50")
	xbee.tx(dest_addr=b"\x00\x05", data=chr(133))
	xbee.tx(dest_addr=b"\x00\x05", data=b"\xFE\x99")
	xbee.tx(dest_addr=b"\x00\x05", data=chr(100))	
	
	clearScreen()
	xbee.tx(dest_addr=b"\x00\x05", data='Waiting for data')
	setColor("green")
	
	while True:
		try:
			time.sleep(.1)
		except KeyboardInterrupt:
			logger.debug('Ending at keyboard interrupt')
			print 'Ending at keyboard interrupt'
			break
			sys.exit(1)

# *** LCD stuff ***
def doQuery(query):
	if mySqlCon.open:
		try:
			mySqlCon.query(query)
			result = mySqlCon.use_result()
			value = "%s" % result.fetch_row()[0]
			return float(value)
		except _mysql.Error, e:
			print "Error %d: %s" % (e.args[0], e.args[1])
			sys.exit(1)
	else:
		print 'doQuery: mySqlCon is not open'
def getLcdData():
	global currElUse
	global avgElUse
	global currOilUse
	global avgOilUse
	global currBedrTemp

	today = date.today()
	#print today.weekday()
	
	currElUse = int(doQuery("SELECT value FROM `myhome`.`electricity` order by _timestamp desc limit 1"))
	currentHour = strftime("%H", localtime())
	avgElUse = int(doQuery("SELECT avg(_avg) FROM `myhome`.`electricity_by_hour_view` where _hour = " +currentHour +" and weekday(_datetime) = weekday(NOW()) group by _hour"))
	currOilUse = doQuery("SELECT format((avg(value)/60),1) FROM `myhome`.`oil` order by _timestamp desc limit 3")
	avgOilUse = doQuery("SELECT format(avg(_avg)/60,1) as avgMin FROM `myhome`.`oil_by_hour_view` where _hour = "+currentHour +" and weekday(_datetime) = weekday(NOW()) group by _hour")
	currBedrTemp = doQuery("SELECT convert(temp, char) as value FROM `myhome`.`temperatures` where sensorId = 2 order by _timestamp desc limit 1	")
	
	sendToLCD()
def sendToLCD():
	#try:
	cols = 16
	clearScreen()
	moveCursor(1,1)
	upLeft = str(currElUse) + "/" +str(avgElUse)
	xbee.tx(dest_addr=b"\x00\x05", data=upLeft)
	
	downRight = str(currBedrTemp) + "'"
	moveCursor(cols-len(str(downRight))+1,2)
	xbee.tx(dest_addr=b"\x00\x05", data=downRight)
	
	upRight = strftime("%H:%M", localtime())
	moveCursor(cols-len(upRight)+1,1)
	xbee.tx(dest_addr=b"\x00\x05", data=upRight)
	
	downLeft = str(currOilUse) + "/" +str(avgOilUse)
	moveCursor(1,2)
	xbee.tx(dest_addr=b"\x00\x05", data=downLeft)
	#except:
	#	print 'Error in updating LCD'
		
	#loopColors()
	if (currElUse>avgElUse*1.2):
		setColor("red")
	elif ((currElUse<avgElUse*1.2) and (currElUse> avgElUse)):
		setColor("blue")
	else:
		setColor("green")
def moveCursor(corner,row):
	xbee.tx(dest_addr=b"\x00\x05", data=b"\xFE\x47"+chr(corner)+chr(row))
def clearScreen():
	xbee.tx(dest_addr=b"\x00\x05", data=b"\xFE\x58")
def setColor(color):
	if (color=="blue"):
		xbee.tx(dest_addr=b"\x00\x05", data=b"\xFE\xD0\x00\xB8\xB8")
	elif (color=="green"):
		xbee.tx(dest_addr=b"\x00\x05", data=b"\xFE\xD0\x00\xFF\x00")
	elif (color=="red"):
		xbee.tx(dest_addr=b"\x00\x05", data=b"\xFE\xD0\xFF\x33\x33")
	elif (color=="yellow"):
		xbee.tx(dest_addr=b"\x00\x05", data=b"\xFE\xD0\xFF\xFF\x33")
	else:
		xbee.tx(dest_addr=b"\x00\x05", data=b"\xFE\xD0\xFF\xFF\xFF")
def loopColors():
	setColor("green")
	time.sleep(2)
	setColor("yellow")
	time.sleep(2)
	setColor("blue")
	time.sleep(2)
	setColor("red")
	time.sleep(2)
# *** eo LCD stuff ***

# *** XBee packet recieved stuff ***
def message_received(data):
	#logger.debug(data)
	try:
		# Every time a packet is recieved we update the LCD. 
		# TODO: instead have LCD poll for values every 60 seconds.
		getLcdData()	# update LCD
	except:
		logger.error('Unexpected error in update LCD')
	try:
		sensorHex = data['source_addr']
		sensorId = struct.unpack(">h", sensorHex)[0] 
	except:
		logger.error('Unexpected error 0')

	if sensorId == 2:
		try:
			packet = data['rf_data']
			pulseCount = struct.unpack("B", packet)[0]
			writeElectricity(pulseCount);
		except:
			logger.error('Unexpected error before writeElectricity')
	if sensorId == 3:
		try:
			writeTemp(data)
		except:
			logger.error('Unexpected error 2')
	if sensorId == 4:
		try:
			#logger.debug(data);
			packet = data['rf_data']
			oilPer5min = struct.unpack("B", packet)[0]
			writeOil(oilPer5min);
		except:
			logger.error('Unexpected error before writeOil')
			
def writeTemp(data):
	correctionFactor = 1.1 	# seems like there is an offset of about this much...
	samplesList = data['samples'][0]
	
	sample0 = samplesList['adc-0']
	sample1 = samplesList['adc-1']
	sample2 = samplesList['adc-2']
	
	temperature0 = (float(sample0)/10)-correctionFactor	# room temp
	temperature1 = (float(sample1)/10)-correctionFactor	# heater temp
	temperature2 = (float(sample2)/10)-correctionFactor	# fireplace temp
	
	insertString0 = 'Insert into temperatures (sensorId, temp, _timestamp, _date, _time) \
	values(2, '+str(temperature0) +', NOW(), cast(NOW() as date), cast(NOW() as time))'
	insertString1 = 'Insert into temperatures (sensorId, temp, _timestamp, _date, _time) \
	values(1, '+str(temperature1) +', NOW(), cast(NOW() as date), cast(NOW() as time))'
	insertString2 = 'Insert into temperatures (sensorId, temp, _timestamp, _date, _time) \
	values(3, '+str(temperature2) +', NOW(), cast(NOW() as date), cast(NOW() as time))'
	
	#print insertString0
	#print insertString1
	#print insertString2
	if mySqlCon.open:
		try:
			mySqlCon.query(insertString0)
			mySqlCon.query(insertString1)
			mySqlCon.query(insertString2)
			logger.debug('Bedroom 1=' +str(temperature0) +'; 2=' +str(temperature1) +';3=' +str(temperature2) +'')
			print 'Bedroom 1=' +str(temperature0) +'; 2=' +str(temperature1) +';3=' +str(temperature2) +' at ' +strftime("%d.%m.%Y %H:%M:%S", localtime())
			
		except:
			print 'Could not write temp. to database' 
	else:
		print '********mySqlCon is false'
def writeOil(oilPer5min):
	print 'oilPer5min: ' +str(oilPer5min)
	if (oilPer5min == 2):
		oilPer5min = 0
	oilPerHour = oilPer5min * 12; #seconds per hour
	insertString = 'Insert into oil (sensorId, value, _timestamp, _date, _time) \
	values(4, '+str(oilPerHour) +', NOW(), cast(NOW() as date), cast(NOW() as time))'
	if mySqlCon.open:
		try:
			#print 'inserting oil'
			mySqlCon.query(insertString)
			print 'Oil usage: ' +str(oilPerHour) +' seconds per h at ' +strftime("%d.%m.%Y %H:%M:%S", localtime())
			logger.debug('Oil usage: ' +str(oilPerHour) +' seconds per h')
		except:
			print 'Could not write oil to database' 
	else:
		print 'OilUsage mySqlCon is false'
		
def writeElectricity(pulseCount):
	electricityUsage = int((pulseCount*0.8*60))
	insertString = 'Insert into electricity (sensorId, value, _timestamp, _date, _time) \
	values(2, '+str(electricityUsage) +', NOW(), cast(NOW() as date), cast(NOW() as time))'
	#print insertString
	if mySqlCon.open:
		try:
			#print 'inserting el'
			mySqlCon.query(insertString)
			print 'El.usage: ' +str(electricityUsage) +' Wh at ' +strftime("%d.%m.%Y %H:%M:%S", localtime())
			logger.debug('El.usage: ' +str(electricityUsage) +' Wh')
		except:
			print 'Could not write el. to database' 
	else:
		print 'ElUsage mySqlCon is false' 
if __name__ == '__main__':
	main()