def updateStatus(id):
try:
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
query = "UPDATE Core_Arduino_Outputs SET status = 'OFF' WHERE Core_Devices_id = " + str(
id)
logger.printLog(debug, "Query: " + query, str(logger.get_linenumber()))
cursor.execute(query)
cnx.commit()
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
raise
finally:
cursor.close()
cnx.close()
def on_message(mqttc, obj, msg):
hide_keyboard = {'hide_keyboard': True}
for BotID in BotIDS:
logger.printLog(debug,
"Notification message has been send to " + BotID,
str(logger.get_linenumber()))
bot.sendMessage(int(BotID),
str(msg.payload),
reply_markup=hide_keyboard)
def on_message(mqttc, obj, msg):
if "/status" in msg.topic:
logger.printLog(debug, "on_message: " + msg.topic + "/" + msg.payload,
str(logger.get_linenumber()))
tmpArduino, tmpRelais, action = msg.topic.split("/")
Arduino = tmpArduino.replace("arduino", "")
Relais = tmpRelais.replace("relais", "")
updateStatus(Arduino, Relais, msg.payload)
CheckvButton()
elif "/checkStatus" in msg.topic:
CheckvButton()
def updateFBL_Status(id, status):
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
query = "UPDATE Core_vButtonDB SET FBL_Status = '" + status + "' WHERE id = " + str(
id)
logger.printLog(debug, "Query: " + query, str(logger.get_linenumber()))
cursor.execute(query)
cnx.commit()
cursor.close()
cnx.close()
def sendHostname():
try:
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
query = ("SELECT MAC, nummer FROM Core_Devices")
logger.printLog(debug, "Query: " + query, str(logger.get_linenumber()))
cursor.execute(query)
row = cursor.fetchone()
while row is not None:
logger.printLog(
debug, "Publish: " + str(row[0]) + "/hostname/" + str(row[1]),
str(logger.get_linenumber()))
mqttc.publish(str(row[0]) + "/hostname", str(row[1]), 0, False)
row = cursor.fetchone()
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
raise
finally:
cursor.close()
cnx.close()
def updateStatus(Arduino, Relais, status):
if (status == 'H'):
status = 'ON'
Time = "'" + str(datetime.datetime.now()) + "'"
else:
status = 'OFF'
Time = "NULL"
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
query = "UPDATE Core_Arduino_Outputs SET status = '" + str(
status
) + "', status_time = " + str(
Time
) + ", notification_snooze = NULL, notification_dismiss = 0 WHERE arduino = " + str(
Arduino) + " and pin = " + str(Relais)
logger.printLog(debug, "Query: " + query, str(logger.get_linenumber()))
cursor.execute(query)
cnx.commit()
cursor.close()
cnx.close()
def on_subscribe(mqttc, obj, mid, granted_qos):
logger.printLog(debug, "Subscribed: " + str(mid) + " " + str(granted_qos),
str(logger.get_linenumber()))
def on_publish(mqttc, obj, mid):
logger.printLog(debug, "Publish: " + str(mid),
str(logger.get_linenumber()))
def on_connect(mqttc, obj, rc):
logger.printLog(debug, "rc: " + str(rc), str(logger.get_linenumber()))
def on_callback_query(msg):
query_id, from_id, data = telepot.glance(msg, flavor='callback_query')
logger.printLog(
debug, 'Callback query:' + str(query_id) + ' ' + str(from_id) + ' ' +
str(data), str(logger.get_linenumber()))
actionsArr = data.split('|')
global AllLowN
global AllLow
relais = actionsArr[2].split(';')
if actionsArr[1] == "Low":
if relais[0] == "AllLow":
tmpText = "All Lights are out"
for relais in AllLow:
topic = "arduino" + str(relais[0]) + "/relais" + str(
relais[1]) + "/action"
payload = "L"
logger.printLog(debug, "Publish: " + topic + ":" + payload,
str(logger.get_linenumber()))
mqttc.publish(topic, payload, 0, False)
elif relais[0] == "AllLowN":
tmpText = "All Lights are out"
global AllLowN
for relais in AllLowN:
topic = "arduino" + str(relais[1]) + "/relais" + str(
relais[2]) + "/action"
payload = "L"
logger.printLog(debug, "Publish: " + topic + ":" + payload,
str(logger.get_linenumber()))
mqttc.publish(topic, payload, 0, False)
else:
tmpText = relais[1] + " out"
topic = "arduino" + str(relais[2]) + "/relais" + str(
relais[3]) + "/action"
payload = "L"
logger.printLog(debug, "Publish: " + topic + ":" + payload,
str(logger.get_linenumber()))
mqttc.publish(topic, payload, 0, False)
elif actionsArr[1] == "Ignore":
try:
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
for relais in AllLowN:
query = (
"UPDATE Core_Arduino_Outputs SET notification_dismiss = 1 WHERE id="
+ str(relais[0]))
logger.printLog(debug, query, str(logger.get_linenumber()))
cursor.execute(query)
cnx.commit()
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
raise
finally:
cursor.close()
cnx.close()
tmpText = "Notification ignored"
elif actionsArr[1] == "Snooze":
Time = datetime.datetime.now() + datetime.timedelta(
seconds=int(NotificationSnooze) * 60)
try:
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
for relais in AllLowN:
query = (
"UPDATE Core_Arduino_Outputs SET notification_snooze = '" +
str(Time) + "' WHERE id=" + str(relais[0]))
logger.printLog(debug, query, str(logger.get_linenumber()))
cursor.execute(query)
cnx.commit()
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
raise
finally:
cursor.close()
cnx.close()
tmpText = "Notifications Snoozed for " + str(
NotificationSnooze) + "min"
if actionsArr[0] == 'notification':
logger.printLog(debug,
"Notification has been send to " + str(query_id),
str(logger.get_linenumber()))
bot.answerCallbackQuery(query_id, text=tmpText)
elif actionsArr[0] == 'alert':
logger.printLog(debug, "Alert has been send to " + str(query_id),
str(logger.get_linenumber()))
bot.answerCallbackQuery(query_id, text=tmpText, show_alert=True)
def checkRelay():
try:
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
query = (
"SELECT id, naam, status_time, notification, arduino, pin, notification_snooze FROM Core_Arduino_Outputs WHERE notification IS NOT NULL AND notification <> '' AND notification_dismiss = 0 AND status = 'ON' "
)
logger.printLog(debug, str(query), str(logger.get_linenumber()))
cursor.execute(query)
NotificationList = []
for (id, naam, Time_on, notification, arduino, pin, snooze) in cursor:
logger.printLog(debug, 'Found Record: %s' % naam,
str(logger.get_linenumber()))
Time = datetime.datetime.now()
time_delta = (Time - Time_on).total_seconds()
if (int(time_delta) > int(notification)):
if snooze is None:
logger.printLog(
debug,
'Add : %s to notification list NOT SNOOZED' % naam,
str(logger.get_linenumber()))
NotificationList.append([id, naam, arduino, pin])
else:
if snooze < Time:
logger.printLog(
debug,
'Add : %s to notification list SNOOZED' % naam,
str(logger.get_linenumber()))
NotificationList.append([id, naam, arduino, pin])
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
raise
finally:
cursor.close()
cnx.close()
if len(NotificationList) > 0:
KeyBoardArray = []
Message = "Following lights are on"
global AllLowN
AllLowN = []
for Notication in NotificationList:
text = str(Notication[1])
callback = NotificationMethod + '|Low|' + str(
Notication[0]) + ';' + str(Notication[1]) + ';' + str(
Notication[2]) + ';' + str(Notication[3])
KeyBoardArray.append([
InlineKeyboardButton(text=str(text),
callback_data=str(callback))
], )
AllLowN.append([Notication[0], Notication[2], Notication[3]])
if len(NotificationList) > 1:
text = "* Alles uit *"
callback = NotificationMethod + '|Low|AllLowN'
KeyBoardArray.append([
InlineKeyboardButton(text=str(text),
callback_data=str(callback))
], )
text = "* Ignore *"
callback = NotificationMethod + '|Ignore|'
KeyBoardArray.append([
InlineKeyboardButton(text=str(text), callback_data=str(callback))
], )
text = "* Snooze " + str(NotificationSnooze) + "min *"
callback = NotificationMethod + '|Snooze|'
KeyBoardArray.append([
InlineKeyboardButton(text=str(text), callback_data=str(callback))
], )
markup = InlineKeyboardMarkup(inline_keyboard=KeyBoardArray)
for BotID in BotIDS:
logger.printLog(debug,
"Notification message has been send to " + BotID,
str(logger.get_linenumber()))
bot.sendMessage(int(BotID), Message, reply_markup=markup)
Time = datetime.datetime.now() + datetime.timedelta(
seconds=int(TimeBetweenNotification) * 60)
try:
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
for relais in AllLowN:
query = (
"UPDATE Core_Arduino_Outputs SET notification_snooze = '" +
str(Time) + "' WHERE id=" + str(relais[0]))
logger.printLog(debug, query, str(logger.get_linenumber()))
cursor.execute(query)
cnx.commit()
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
raise
finally:
cursor.close()
cnx.close()
def CheckvButton():
#check on every message
logger.printLog(debug, "CheckvButton", str(logger.get_linenumber()))
try:
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
query = "SELECT id,FBL,FBL_Status,arduino,pin FROM Core_vButtonDB WHERE FBL != 'NONE' "
logger.printLog(debug, "Query: " + query, str(logger.get_linenumber()))
cursor.execute(query)
vButtonID = ''
arr_id = []
arr_vbutton = []
arr_fbl = []
arr_FBL_Status = []
arr_ON_ON = []
arr_ON_OFF = []
arr_OFF_ON = []
arr_OFF_OFF = []
arr_Toggle_ON = []
arr_Toggle_OFF = []
counter = -1
# for (id,arduino,pin,status,FBL,FBL_Status,type) in cursor:
for (id, FBL, FBL_Status, arduino, pin) in cursor:
arr_id.append(id)
arr_vbutton.append("arduino" + str(arduino) + "/vbutton" +
str(pin) + "/status")
arr_fbl.append(FBL)
arr_FBL_Status.append(FBL_Status)
vButtonID = id
counter = counter + 1
arr_ON_ON.append(0)
arr_ON_OFF.append(0)
arr_OFF_ON.append(0)
arr_OFF_OFF.append(0)
arr_Toggle_ON.append(0)
arr_Toggle_OFF.append(0)
try:
cnx2 = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor2 = cnx2.cursor()
query = "SELECT id , type FROM Core_vButtonDB_actions WHERE action_nummer = 1 and core_vButtonDB_id = " + str(
id)
logger.printLog(debug, "Query: " + query,
str(logger.get_linenumber()))
cursor2.execute(query)
for (id_Core_vButtonDB_actions, type) in cursor2:
# Hierna nog aanpassen
try:
cnx3 = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor3 = cnx3.cursor()
query = "SELECT Core_Arduino_Outputs.status FROM Core_vButtonDB_actions_Arduino INNER JOIN Core_Arduino_Outputs ON Core_Arduino_Outputs_id = Core_Arduino_Outputs.id WHERE Core_vButtonDB_actions_id = " + str(
id_Core_vButtonDB_actions)
logger.printLog(debug, "Query: " + query,
str(logger.get_linenumber()))
cursor3.execute(query)
for (status) in cursor3:
if (type == 'T'):
logger.printLog(debug, 'type: ' + str(type),
str(logger.get_linenumber()))
logger.printLog(debug,
'status: ' + str(status[0]),
str(logger.get_linenumber()))
if (status[0] == 'ON'):
arr_Toggle_ON[
counter] = arr_Toggle_ON[counter] + 1
logger.printLog(
debug, 'Toggle_ON: ' +
str(arr_Toggle_ON[counter]),
str(logger.get_linenumber()))
else:
arr_Toggle_OFF[
counter] = arr_Toggle_OFF[counter] + 1
logger.printLog(
debug, 'Toggle_OFF: ' +
str(arr_Toggle_OFF[counter]),
str(logger.get_linenumber()))
elif (type == 'ON'):
if (status[0] == 'ON'):
arr_ON_ON[counter] = arr_ON_ON[counter] + 1
logger.printLog(
debug,
'ON_ON: ' + str(arr_ON_ON[counter]),
str(logger.get_linenumber()))
else:
arr_ON_OFF[
counter] = arr_ON_OFF[counter] + 1
logger.printLog(
debug,
'ON_OFF: ' + str(arr_ON_OFF[counter]),
str(logger.get_linenumber()))
else:
if (status[0] == 'ON'):
arr_OFF_ON[
counter] = arr_OFF_ON[counter] + 1
logger.printLog(
debug,
'OFF_ON: ' + str(arr_OFF_ON[counter]),
str(logger.get_linenumber()))
else:
arr_OFF_OFF[
counter] = arr_OFF_OFF[counter] + 1
logger.printLog(
debug, 'OFF_OFF: ' +
str(arr_OFF_OFF[counter]),
str(logger.get_linenumber()))
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
finally:
cursor3.close()
cnx3.close()
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
finally:
cursor2.close()
cnx2.close()
for i in range(len(arr_id)):
if (arr_fbl[i] == 'FBL'):
if (arr_Toggle_ON[i] > 0 or arr_ON_ON[i] > 0
or arr_OFF_ON[i] > 0):
if (arr_FBL_Status[i] == "DOWN"):
mqttc.publish(arr_vbutton[i], "H", 0, True)
updateFBL_Status(arr_id[i], "UP")
PhysicalFBL(arr_id[i], 'H')
else:
if (arr_FBL_Status[i] == "UP"):
mqttc.publish(arr_vbutton[i], "L", 0, True)
PhysicalFBL(arr_id[i], 'L')
updateFBL_Status(arr_id[i], "DOWN")
elif (arr_fbl[i] == 'RFBL'):
if (arr_Toggle_OFF[i] > 0 or arr_ON_OFF[i] > 0
or arr_OFF_OFF[i] > 0):
if (arr_FBL_Status[i] == "DOWN"):
mqttc.publish(arr_vbutton[i], "H", 0, True)
PhysicalFBL(arr_id[i], 'H')
updateFBL_Status(arr_id[i], "UP")
else:
if (arr_FBL_Status[i] == "UP"):
mqttc.publish(arr_vbutton[i], "L", 0, True)
PhysicalFBL(arr_id[i], 'L')
updateFBL_Status(arr_id[i], "DOWN")
elif (arr_fbl[i] == 'CFBL'):
if (arr_ON_OFF[i] > 0 or arr_OFF_ON[i] > 0
or arr_Toggle_OFF[i] > 0):
if (arr_FBL_Status[i] == "UP"):
mqttc.publish(arr_vbutton[i], "L", 0, True)
PhysicalFBL(arr_id[i], 'L')
updateFBL_Status(arr_id[i], "DOWN")
else:
if (arr_FBL_Status[i] == "DOWN"):
mqttc.publish(arr_vbutton[i], "H", 0, True)
PhysicalFBL(arr_id[i], 'H')
updateFBL_Status(arr_id[i], "UP")
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
finally:
cursor.close()
cnx.close()
import sys
sys.path.append('/var/www/html/modules/libraries')
import mysql.connector
import paho.mqtt.client as mqtt
import datetime
import time
from inspect import currentframe
import iRulez_logging as logger
file = open('/var/www/html/config.php', 'r')
debug = "DEBUG"
info = "INFO"
alert = "ALERT"
logger.printLog(info, '**** FBL Started ****', str(logger.get_linenumber()))
for line in file:
if "db_name" in line: MySQL_database = line.split('"')[3]
elif "db_user" in line: MySQL_username = line.split('"')[3]
elif "db_password" in line: MySQL_password = line.split('"')[3]
try:
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
query = ("SELECT Setting,value FROM Settings")
print("query: " + query)
cursor.execute(query)
for (Setting, value) in cursor:
def on_message(mqttc, obj, msg):
logger.printLog(
debug, "Received message: " + str(msg.topic) + "/" + str(msg.payload),
str(logger.get_linenumber()))
if "/ip" in msg.topic:
try:
MAC, action = msg.topic.split("/")
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
query = ("SELECT nummer FROM Core_Devices WHERE MAC = '" +
str(MAC) + "'")
logger.printLog(debug, "Query: " + query,
str(logger.get_linenumber()))
cursor.execute(query)
row = cursor.fetchone()
while row is not None:
logger.printLog(
debug, "Publish: " + str(MAC) + "/hostname/" + str(row[0]),
str(logger.get_linenumber()))
mqttc.publish(str(MAC) + "/hostname", str(row[0]), 0, False)
row = cursor.fetchone()
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
raise
finally:
cursor.close()
cnx.close()
elif "/lastWill" in msg.topic:
try:
MAC, action = msg.topic.split("/")
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
query = ("SELECT id, Nummer FROM Core_Devices WHERE MAC = '" +
str(MAC) + "'")
logger.printLog(debug, "Query: " + query,
str(logger.get_linenumber()))
cursor.execute(query)
result = cursor.fetchone()
updateStatus(result[0])
topic = "FBL/checkStatus"
payload = ""
logger.printLog(debug,
"Publish: " + str(topic) + ":" + str(payload),
str(logger.get_linenumber()))
mqttc.publish(topic, payload, 0, False)
topic = "Telegram/Message"
payload = "[ALERT] - Device " + result[1] + " with MAC: " + str(
MAC) + " DOWN!"
logger.printLog(debug, "Publish: " + topic + ":" + payload,
str(logger.get_linenumber()))
mqttc.publish(topic, payload, 0, False)
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
raise
finally:
cursor.close()
cnx.close()
def on_log(mqttc, obj, level, string):
logger.printLog(debug, string, str(logger.get_linenumber()))
def on_disconnect(client, userdata, rc):
logger.printLog(info, "on_disconnect!", str(logger.get_linenumber()))
exit()
import telepot
import mysql.connector
import datetime
import iRulez_logging as logger
import paho.mqtt.client as mqtt
from telepot.namedtuple import InlineKeyboardMarkup, InlineKeyboardButton
from inspect import currentframe
file = open('/var/www/html/config.php', 'r')
debug = "DEBUG"
info = "INFO"
alert = "ALERT"
logger.printLog(info, '**** Telgram Started ****',
str(logger.get_linenumber()))
for line in file:
if "db_name" in line: MySQL_database = line.split('"')[3]
elif "db_user" in line: MySQL_username = line.split('"')[3]
elif "db_password" in line: MySQL_password = line.split('"')[3]
try:
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
query = ("SELECT Setting,value FROM Settings")
logger.printLog(debug, query, str(logger.get_linenumber()))
cursor.execute(query)
for (Setting, value) in cursor:
def handle(msg):
chat_id = msg['chat']['id']
command = msg['text']
logger.printLog(debug, 'Got command: %s' % command,
str(logger.get_linenumber()))
logger.printLog(debug, 'Got chatID from : %s' % chat_id,
str(logger.get_linenumber()))
if str(chat_id) in BotIDS:
if command == '/status':
try:
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
cursor = cnx.cursor()
query = (
"SELECT naam, arduino, pin FROM Core_Arduino_Outputs WHERE Status = 'ON' AND telegram = '1'"
)
logger.printLog(debug, str(query),
str(logger.get_linenumber()))
cursor.execute(query)
NotificationList = []
for (naam, arduino, pin) in cursor:
NotificationList.append([naam, arduino, pin])
except Exception as e:
logger.printLog(alert, e, str(logger.get_linenumber()))
raise
finally:
cursor.close()
cnx.close()
KeyBoardArray = []
if len(NotificationList) > 0:
Message = "Following lights are on"
else:
Message = "No lights are on!"
global AllLow
AllLow = []
for Notication in NotificationList:
text = str(Notication[0])
callback = NotificationMethod + '|Low|;' + str(
Notication[0]) + ';' + str(Notication[1]) + ';' + str(
Notication[2])
KeyBoardArray.append([
InlineKeyboardButton(text=str(text),
callback_data=str(callback))
], )
AllLow.append([Notication[1], Notication[2]])
if len(NotificationList) > 1:
text = "* Alles uit *"
callback = NotificationMethod + '|Low|AllLow'
KeyBoardArray.append([
InlineKeyboardButton(text=str(text),
callback_data=str(callback))
], )
markup = InlineKeyboardMarkup(inline_keyboard=KeyBoardArray)
logger.printLog(debug, "status has been send to " + str(chat_id),
str(logger.get_linenumber()))
bot.sendMessage(chat_id, Message, reply_markup=markup)
elif command == '/enroll':
hide_keyboard = {'hide_keyboard': True}
text = 'Give this ID to you iRulez Administrator: ' + str(chat_id)
logger.printLog(debug, "Enrollment has been send to " + str(chat_id),
str(logger.get_linenumber()))
bot.sendMessage(chat_id, text, reply_markup=hide_keyboard)
cursor = cnx.cursor()
query = ("SELECT Setting,value FROM Settings")
cursor.execute(query)
for (Setting, value) in cursor:
if Setting == "MQTT_ip_address":
MQTT_ip_address = value
elif Setting == "MQTT_port_python":
MQTT_port = value
cursor.close()
cnx.close()
debug = "DEBUG"
info = "INFO"
alert = "INFO"
logger.printLog(info, "Auto config Started", str(logger.get_linenumber()))
def restart():
subprocess.call(["sudo", "supervisorctl", "restart", "AutoConfig"])
def on_connect(mqttc, obj, rc):
logger.printLog(debug, "rc: " + str(rc), str(logger.get_linenumber()))
def updateStatus(id):
try:
cnx = mysql.connector.connect(user=MySQL_username,
password=MySQL_password,
database=MySQL_database)
