def makeDevice(subType, ID1, ID2, ID3, ID4, UnitCode, command):
print "making new device of subtype %s with IDs %s %s %s %s and UC %s. It was sent with command %s" % (
subType,
HC,
ID1,
ID2,
ID3,
UnitCode,
command,
)
cnx = mysql.connector.connect(
user="******", password="******", host="localhost", database=configHandler.getDatabaseName()
)
cursor = cnx.cursor()
add_device = "INSERT INTO Devices (Name, DeviceType, PacketType, SubType, LastKnown, Fav, Location) VALUES (%s, %s, %s, %s, %s, %s, %s)"
add_device_data = ("New Device", "Unknown", "0x11", subType, command, "0", "")
cursor.execute(add_device, add_device_data)
insertID = "%d" % (cursor.lastrowid)
add_Lighting5 = "INSERT INTO Lighting2 (DeviceID, ID1, ID2, ID3, ID4, UnitCode, Level, Filler, PacketLength) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
add_Lighting5_data = (insertID, ID1, ID2, ID3, ID4, UnitCode, "0x01", "0x00", "0x0B")
# print add_sensor_sensors % (data_sensor_sensors)
cursor.execute(add_Lighting5, add_Lighting5_data)
cnx.commit()
# Close database acces
cnx.close()
def addSensor(Packettype, SubType, ID1, ID2, Temp, Humidity, HumidityStatus, Signal, Battery):
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
currentTime = time.strftime('%Y-%m-%d %H:%M:%S')
add_sensor_devices = (
"INSERT INTO Devices (Name, DeviceType, PacketType, SubType, LastKnown, Fav, Location, DateAdded) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)")
data_sensor_devices = (
'TempName', 'Sensor', Packettype, SubType, Temp, '0', 'Unknown', currentTime)
cursor.execute(add_sensor_devices, data_sensor_devices)
insertID = "%d" % (cursor.lastrowid)
add_sensor_sensors = (
"INSERT INTO TempSensors (DeviceID, ID1, ID2, Temp, Humidity, HumidityStatus, RSSI, Battery) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)")
data_sensor_sensors = (
insertID, ID1, ID2, Temp, Humidity, HumidityStatus, Signal, Battery)
# print add_sensor_sensors % (data_sensor_sensors)
cursor.execute(add_sensor_sensors, data_sensor_sensors)
# Let the admin see it
setPermission = "INSERT INTO Permissions (UserID, Type, DeviceID, Status) VALUES (%s, %s, %s, %s)"
data_setPermission = ('1', 'DEVICE', insertID, '1')
cursor.execute(setPermission, data_setPermission)
cnx.commit()
# Close database acces
cnx.close()
def add(deviceID, command):
#Connect to the database
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
query = "INSERT INTO `History`(`DeviceID`, `Command`, `Date`) VALUES ('"+str(deviceID)+"','"+command+"','"+time.strftime('%Y-%m-%d %H:%M:%S')+"')"
#print query
cursor.execute(query)
cnx.commit()
#Close database acces
cnx.close()
def checkIfEventActive(eventID):
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
query = "SELECT Active FROM Events WHERE EventID='%s'" % (eventID)
cursor.execute(query)
for (Active) in cursor:
if Active[0] == 0:
return False
if Active[0] == 1:
return True
def updateSensor(ID1, ID2, Temp, Humidity, HumidityStatus, Signal, Battery):
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
update_sensor = (
"UPDATE TempSensors SET Temp=%s, Humidity=%s, HumidityStatus=%s, RSSI=%s, Battery=%s WHERE ID1=%s AND ID2=%s")
data_sensor = (Temp, Humidity, HumidityStatus, Signal, Battery, ID1, ID2)
cursor.execute(update_sensor, data_sensor)
cnx.commit()
cnx.close()
def checkConditionDevice(DeviceID, Cond):
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
query = "SELECT LastKnown FROM Devices WHERE DeviceID='%s'" % (DeviceID)
cursor.execute(query)
passed = False
for (LastKnown) in cursor:
if LastKnown[0] == Cond:
passed = True
return passed
def checkWhenLastUpdated(deviceID):
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
query = "SELECT DateTime FROM `SensorHistory` WHERE DeviceID = %s ORDER BY `DateTime` DESC LIMIT 1" % deviceID
cursor.execute(query)
for (DateTime) in cursor:
if DateTime[0] < (datetime.now() - timedelta(minutes=15)):
return True
else:
return False
return True
def checkSensorExists(ID1, ID2):
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
query = "SELECT COUNT(*) FROM TempSensors WHERE ID1='%s' AND ID2='%s'" % (
ID1, ID2)
cursor.execute(query)
result = cursor.fetchone()
# print "Query : %s" % (query)
# print "Result is %s" % (result)
if result[0] == 1 or result[0] == "1":
return True
else:
return False
def getDeviceID(ID1, ID2):
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
update_sensor = (
"SELECT DeviceID FROM TempSensors WHERE ID1='%s' AND ID2='%s'" % (ID1, ID2))
did = 0
cursor.execute(update_sensor)
for (DeviceID) in cursor:
did = DeviceID
cnx.commit()
cnx.close()
return did
def addSensorToHistory(deviceID, temp, humidity):
#Connect to the database
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
id = int(deviceID[0])
if checkWhenLastUpdated(id):
print "Updating Sensor"
#query = "INSERT INTO `SensorHistory`(`DeviceID`, `Temp`,`Humidity`, `DateTime`) VALUES ('"+str(deviceID)+"','"+temp+"','"+humidity+"','"+time.strftime('%Y-%m-%d %H:%M:%S')+"')"
query = "INSERT INTO SensorHistory (DeviceID, Temp,Humidity, DateTime) VALUES ('%d', '%s', '%s', '%s')" % (id,temp,humidity,time.strftime('%Y-%m-%d %H:%M:%S'))
#print query
cursor.execute(query)
cnx.commit()
else:
print "Last entry was less then 15 minutes ago"
#Close database acces
cnx.close()
def checkConditionTemp(DeviceID, Cond, Value):
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
query = "SELECT Temp FROM TempSensors WHERE DeviceID='%s'" % (DeviceID)
cursor.execute(query)
passed = False
for (Temp) in cursor:
rTemp = int(Temp[0], 16) / float(10)
if Cond == "ABOVE":
if rTemp > Value:
passed = True
if Cond == "BELOW":
if rTemp < Value:
passed = True
return passed
def findEventsFor(sID, condition):
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
query = "SELECT * FROM EventConditions WHERE DeviceID='%s'" % (sID)
cursor.execute(query)
for (ConditionID, EventID, EventType, DeviceID, Cond, Time, Value) in cursor:
eventID = EventID
if (checkIfEventActive(eventID)):
if EventType == "Device":
if Cond == condition:
if checkOtherConditions(eventID):
print "Event %s can be executed" % (eventID)
executeEvent(eventID)
else:
print "Event %s can't be executed" % (eventID)
else:
print "Event %s not active" % (eventID)
def checkOtherConditions(eventID):
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
query = "SELECT * FROM EventConditions WHERE EventID='%s'" % (eventID)
cursor.execute(query)
allPassed = True
for (ConditionID, EventID, EventType, DeviceID, Cond, Time, Value) in cursor:
if EventType == "Device":
if checkConditionDevice(DeviceID, Cond) == False:
allPassed = False
if EventType == "Temp":
if checkConditionTemp(DeviceID, Cond, Value) == False:
allPassed = False
if EventType == "Time":
if checkConditionTime(Cond, Time) == False:
allPassed = False
return allPassed
def executeEvent(eventID):
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
cursor = cnx.cursor()
subQuery = "SELECT * From EventResults WHERE EventID='%s'" % (eventID)
cursor.execute(subQuery)
for (ResultID, EventID, EventType, Value, Cond) in cursor:
print "processing action %s" % (EventType)
if EventType == "Device":
main(str(Value), str(Cond))
if EventType == "TV":
if (Cond == "Pause"):
xbmcHandler.pause()
if (Cond == "Play"):
xbmcHandler.play()
if (Cond == "VolUp"):
xbmcHandler.volUp()
if (Cond == "VolDown"):
xbmcHandler.volDown()
def handlePacket(packetArray):
# Need to keep a standard. Atm 0x0F is read as 0xF so need to add
# the 0.
ID1 = packetArray[4]
if len(ID1) == 3:
ID1 = ID1[:2] + "0" + ID1[2:]
ID2 = packetArray[5]
if len(ID2) == 3:
ID2 = ID2[:2] + "0" + ID2[2:]
ID3 = packetArray[6]
if len(ID3) == 3:
ID3 = ID3[:2] + "0" + ID3[2:]
ID4 = packetArray[7]
if len(ID3) == 3:
ID4 = ID4[:2] + "0" + ID4[2:]
UnitCode = packetArray[8]
if len(UnitCode) == 3:
UnitCode = UnitCode[:2] + "0" + UnitCode[2:]
cmdhex = packetArray[9]
if cmdhex == "0x0":
command = "OFF"
elif cmdhex == "0x1":
command = "ON"
else:
print "unknown cmd %s" % (cmdhex)
return
cnx = mysql.connector.connect(
user="******", password="******", host="localhost", database=configHandler.getDatabaseName()
)
cursor = cnx.cursor()
query = "SELECT DeviceID FROM Lighting2 WHERE ID1='%s' AND ID2='%s' AND ID3='%s' AND ID4='%s' AND UnitCode='%s'" % (
ID1,
ID2,
ID3,
ID4,
UnitCode,
)
print query
cursor.execute(query)
resultFound = False
sID = ""
for SwitchID in cursor:
sID = str(SwitchID[0])
resultFound = True
if resultFound:
query = "UPDATE Devices SET LastKnown='%s' WHERE DeviceID='%s'" % (command, sID)
cursor.execute(query)
cursor.execute("COMMIT")
# findEventsFor(sID, command)
# add(sID, command)
else:
print "no device found matching ID. Not creating new yet."
print "Finished parsing"
def parse(toParse):
print "Recieved Packet: %s" % (toParse)
if toParse == "0x0":
print "RUN AWAY"
return
packetArray = toParse.split(" ")
packetType = packetArray[1]
try:
subType = packetArray[2]
if len(subType) == 3:
subType = subType[:2] + "0" + subType[2:]
if packetType == "0x14" and subType == "0x00":
ID1 = packetArray[4]
# Need to keep a standard. Atm 0x0F is read as 0xF so need to add
# the 0.
if len(ID1) == 3:
ID1 = ID1[:2] + "0" + ID1[2:]
ID2 = packetArray[5]
if len(ID2) == 3:
ID2 = ID2[:2] + "0" + ID2[2:]
ID3 = packetArray[6]
if len(ID3) == 3:
ID3 = ID3[:2] + "0" + ID3[2:]
UnitCode = packetArray[7]
if len(UnitCode) == 3:
UnitCode = UnitCode[:2] + "0" + UnitCode[2:]
# Grab the command byte and produce what it means into command
cmdhex = packetArray[8]
if cmdhex == "0x0":
command = "OFF"
elif cmdhex == "0x1":
command = "ON"
else:
print "unknown cmd %s" % (cmdhex)
return
cnx = mysql.connector.connect(
user="******", password="******", host="localhost", database=configHandler.getDatabaseName()
)
cursor = cnx.cursor()
query = "SELECT DeviceID FROM Lighting5 WHERE ID1='%s' AND ID2='%s' AND ID3='%s' AND UnitCode='%s'" % (
ID1,
ID2,
ID3,
UnitCode,
)
# print query
cursor.execute(query)
resultFound = False
sID = ""
for SwitchID in cursor:
sID = str(SwitchID[0])
resultFound = True
if resultFound:
query = "UPDATE Devices SET LastKnown='%s' WHERE DeviceID='%s'" % (command, sID)
cursor.execute(query)
cursor.execute("COMMIT")
findEventsFor(sID, command)
add(sID, command)
else:
print "no device found matching ID. Create new remote."
Lighting5.makeDevice(subType, ID1, ID2, ID3, UnitCode, command)
print "Finished parsing"
elif packetType == "0x52":
print "Recieved Sensor Data"
weatherStation.WSparse(toParse)
elif packetType == "0x11":
print "Got Lighting2 Data"
Lighting2.handlePacket(packetArray)
elif packetType == "0x01":
print "Got status:"
elif packetType == "0x2":
print "Recieved confirmation"
else:
print "Unknown, packet type was %s" % (packetType)
except IndexError:
print "Probably got that stupid 0x0 thing"
#!/usr/bin/python
import mysql.connector
from time import gmtime, strftime, sleep
from worker import main
import threading
from checkAliveState import keepAlive
import configHandler
import deviceGraphs
cnx = mysql.connector.connect(user='******', password='******',
host='localhost',
database=configHandler.getDatabaseName())
t = threading.Thread(target=keepAlive, args=("timerLooper",))
t.setDaemon(True)
t.start()
while (strftime("%S", gmtime()) != '00'):
sleep(1)
while (True):
cnx.commit()
# if (strftime("%M", gmtime()) == '00') or (strftime("%M", gmtime()) == '15') or (strftime("%M", gmtime()) == '30') or (strftime("%M", gmtime()) == '45'):
# thread = threading.Thread(target=deviceGraphs.drawAll(), args=[])
# thread.start()
today = strftime("%a", gmtime())
print strftime("%H:%M:00", gmtime())
cursor = cnx.cursor()
