def getDevices(self):
"""
Obtain the Raspberry Pi devices.
Output: devices -> List of dictionary with the obtained devices.
"""
db = DBBroker()
sql = "SELECT * \
FROM devices t1 \
LEFT OUTER JOIN( \
SELECT idDevice, max(DateAndTime) AS LastDate \
FROM data \
GROUP BY idDevice \
) t2 ON t1.idDevice = t2.idDevice;"
devices = db.select(sql)
# Convert time to UTC - Europe/Madrid
time_now = datetime.datetime.now().replace(
tzinfo=self.server_tz).astimezone(self.local_tz)
time_now = time_now.replace(tzinfo=None)
for device in devices:
if device['LastDate'] == None:
device['status'] = False
elif time_now - device['LastDate'] < datetime.timedelta(
minutes=device['timeInterval'] * 3):
device['status'] = True
else:
device['status'] = False
return devices
def getData(self, devices, dates):
"""
Obtain sensor data stored into de DB.
Intput: devices -> List of Raspberry Pi devices from which obtain the data
dates -> Initial and finish dates
Output: data -> List of dictionaries with the obtained data.
"""
db = DBBroker()
# Prepare SQL query to obtain the data
sql = "SELECT idDevice, DateAndTime, Temperature, Humidity, Pressure, CO, LPG, VehiclesPerHour \
FROM data \
WHERE idDevice IN ("
for i in range(0, len(devices)):
sql += "'%d'" % (devices[i])
if i != len(devices) - 1:
sql += ", "
sql += ") \
AND DateAndTime BETWEEN '%s' AND '%s' \
ORDER BY DateAndTime ASC" % (dates[0], dates[1])
# Execute the SQL querry
data = db.select(sql)
if data == None:
data = []
return data
def removeNotification(self, email):
"""
Remove an existing notification.
Input: email -> Notification email
"""
db = DBBroker()
sql = "DELETE FROM notifications \
WHERE email = '%s'" % (email)
db.update(sql)
def getNotifications(self):
"""
Get all the email notifications.
Output: notifications -> List with all the notifications stored.
"""
db = DBBroker()
sql = "SELECT * FROM notifications "
notifications = db.select(sql)
return notifications
def createNotification(self, email, CO_level, LPG_level, vehicles):
"""
Create a new notification.
Input: email -> Notification email
CO_level -> CO notification level
LPG_level -> LPG notification level
vehicles -> vehicles per hour notification level
"""
db = DBBroker()
sql = "INSERT INTO notifications(email, COLimit, LPGLimit, vehiclesLimit) \
VALUES ('%s', %s, %s, %s)" % \
(email, CO_level, LPG_level, vehicles)
db.update(sql)
def updateNotification(self, email, CO_level, LPG_level, vehicles):
"""
Update and existing notifications.
Input: email -> Notification email
CO_level -> CO notification level
LPG_level -> LPG notification level
vehicles -> vehicles per hour notification level
"""
db = DBBroker()
sql = "UPDATE notifications \
SET COLimit = %s, LPGLimit = %s, vehiclesLimit = %s \
WHERE email = '%s'" % \
(CO_level, LPG_level, vehicles, email)
db.update(sql)
def sensorUpdate(event):
"""
This method is executed everytime an event arrives with new data from the
Raspberry Pi devices. The data is processed and stored into the database.
Input: event -> Event recived from IBM IoT.
"""
data = event.data
date_time = event.timestamp
db = DBBroker()
sql = "INSERT INTO data(idDevice, DateAndTime, Temperature, Humidity, Pressure, CO, LPG, VehiclesPerHour) \
VALUES ('%s', '%s', '%f', '%f', '%f', '%f', '%f', '%f')" % \
(data['idDevice'], data['date_time'], data['temperature'], data['humidity'], data['pressure'], data['CO'], data['LPG_gas'], data['vehicles_per_hour'])
db.update(sql)
print("Stored into the DB live data from %s (%s) sent at %s" %
(event.deviceId, event.deviceType, date_time))
def user_login(self, email, password):
"""
Check if the user exist and if the password is the correct one.
Intput: email -> User email
password -> User password
Output: success -> True if the email and password match
user_id -> The user identification
user_auth_token -> An identifier to check the user autenticity during
the sesion
"""
success = False
user_id = None
user_auth_token = None
db = DBBroker()
sql = "SELECT user_id, password \
FROM users \
WHERE email = '%s'" % (email)
user_data = db.select(sql)[0]
raw_pass = str(user_data['user_id']) + email
encrip_pass = AESCipher(password).encrypt(raw_pass)
if encrip_pass == user_data['password']:
success = True
user_id = user_data['user_id']
user_auth_token = str(uuid.uuid4())
db = DBBroker()
sql = "UPDATE users \
SET last_token = '%s' \
WHERE user_id = %d" % (user_auth_token, user_id)
db.update(sql)
return (success, user_id, user_auth_token)
def checkUserSession(self, user_id, user_auth_token):
"""
Verify the user session.
Intput: user_id -> The user identification
user_auth_token -> An identifier to check the user autenticity during
the sesion
Output: success -> True if the user session exists
"""
success = False
db = DBBroker()
sql = "SELECT last_token, email \
FROM users \
WHERE user_id = %d" % (user_id)
user_data = db.select(sql)[0]
if user_auth_token == user_data['last_token']:
success = True
email = user_data['email']
return (success, email)
def getLastData(self, idDevice):
"""
Obtain last data stored into de DB.
Intput: idDevice -> Raspberry Pi device from which obtain the data
Output: data -> Dictionary with the obtained data.
"""
db = DBBroker()
sql = "SELECT DateAndTime, Temperature, Humidity, Pressure, CO, LPG, VehiclesPerHour \
FROM data t\
inner join ( \
SELECT idDevice, max(DateAndTime) as LastDate \
FROM data \
GROUP BY idDevice \
) tmd on t.idDevice = tmd.idDevice and t.DateAndTime = tmd.LastDate \
WHERE t.idDevice = '%d'" % (idDevice)
data = db.select(sql)[0]
data['idDevice'] = idDevice
if data == None:
data = self.getDefaultData()
return data