MQTT_MySQL.py 4.25 KB
Newer Older
Hicham Brahimi's avatar
Hicham Brahimi committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129
import MySQLdb
import paho.mqtt.client as mqtt
import time
import sys

db = MySQLdb.connect(host="pil-09.univlehavre.lan",   
                     user="firediag",         
                     passwd="firediag$!",  
                     db="firediag");

global topics ; global frequenceSeuils; # frequenceSeuils => Map utilisee pour verifier l'effet de seuil
topics = []; frequenceSeuils = {};

topic_changeCapteur = "/system/changeCapteur";

def on_connect(client, userdata, flags, rc):
    if rc == 0:
        print("Connected to broker");
	client.subscribe(topic_changeCapteur);

        global Connected;
        Connected = True;
 
    else:
        print("Connection failed");

def on_message(client, userdata, msg):
	global topics ; global frequenceSeuils ;

	print(msg.topic + " Payload -> " +str(msg.payload));
	message_topic = msg.topic;
	message_valeur = msg.payload;

	if message_topic == topic_changeCapteur: # Mise a jour des topics auxquelles on s'abonne
		for topic in topics:
			client.unsubscribe(topic);
		
		update_capteur();
		print("Update des topics => "+" ".join(topics));
	else :					# Arrivee d'une valeur d'un capteur
		cursor = db.cursor();
		cursor.execute('SELECT topic, id, idUser, nom, idGroupe FROM capteur WHERE visible = TRUE');
		for capteur in cursor.fetchall():
	   		if capteur[0] == message_topic:
				try:
					cursor.execute("INSERT INTO `valeur` (`temps`, `valeur`, `idCapteur`) VALUES ('"+
						time.strftime('%Y-%m-%d %H:%M:%S')+"', '"+str(message_valeur)+"', '"+str(capteur[1])+"')");
					
					print("Nouvelle valeur ('"+time.strftime('%Y-%m-%d %H:%M:%S')+"', '"+str(message_valeur)+"', '"+str(capteur[1])+"')");

					if unicode(str(message_valeur).strip(), 'utf-8').isnumeric(): # Si c'est une valeur numerique
						# Gestion des notifications des utilisateurs (ajout des alertes)
						cursorNotif = db.cursor();
						cursorNotif.execute('SELECT id, nom, valeur, regle, frequence FROM seuil WHERE idCapteur = '+str(capteur[1]));
						
						for seuil in cursorNotif.fetchall():
							frequence = int(seuil[0]);
							if	(seuil[3] == "sup" and int(message_valeur) > int(seuil[2])) or \
								(seuil[3] == "inf" and int(message_valeur) < int(seuil[2])) or \
								(seuil[3] == "eq" and int(message_valeur) == int(seuil[2])):

								if frequence not in frequenceSeuils:	# on ajoute le seuil a la map 	
									frequenceSeuils[frequence] = 1;
								else: # sinon on l'incremente
									frequenceSeuils[frequence] = frequenceSeuils[frequence]+1;

								# Si l'effet de seuil est depassee, on lance l'alerte
								if frequenceSeuils[frequence] >= seuil[4]:

									print(" Alerte  \""+seuil[1]+"\" => "+message_valeur+" "+seuil[3]+" "+str(int(seuil[2])));
									titre = "Alerte sur "+str(capteur[3]);
									message = "Le seuil \""+str(seuil[1])+"\" a ete franchi ("+message_valeur ;
									if seuil[3] == "sup" : message += " > " ;
									elif seuil[3] == "inf" : message += " < " ;
									else : message += " = " ;
									message += str(int(seuil[2]))+")" ;

									cursorNotif.execute("INSERT INTO `notification` (`temps`, `titre`, `message`, `idUser`, `idCapteur`, `vu`) VALUES ('"+
										time.strftime('%Y-%m-%d %H:%M:%S')+"', '"+titre+"', '"+message+"', '"+str(capteur[2])+"', '"+str(capteur[1])+"', false)")
									
									# notification du changement
									client.publish("/system/notification/"+str(capteur[2]), str(capteur[4])+"/"+str(capteur[1]));
									
									frequenceSeuils[frequence] = 0;
							else:
								frequenceSeuils[frequence] = 0; 
							
					db.commit();
				except Exception as e:
					db.rollback();
					print("rollback ");
					print(e);
				

def update_capteur():
	global topics ;

	topics = [];
	cursor = db.cursor();
	cursor.execute('SELECT topic FROM capteur WHERE visible = TRUE');
	
	for capteur in cursor.fetchall():
		topics.append(capteur[0]);
		client.subscribe(capteur[0]);
	db.commit();
	
Connected = False;

client = mqtt.Client(transport="websockets");
client.on_connect = on_connect;
client.on_message = on_message;
client.connect("pil-09.univlehavre.lan", 9001, 60);
 
client.loop_start() ;

while Connected != True: 
    time.sleep(0.1);

update_capteur();

try:
	while True:
		time.sleep(1);
		 
except KeyboardInterrupt:
	print "exiting";
	client.disconnect();
	client.loop_stop();
	db.close();