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)