def getDroneDetails():
content = request.get_json()
query = '''SELECT Devices.DeviceName, Drone_Status.Status, Drone_Status.Speed, Drone_Status.FlyHeight, Drone_Status.Acceleration, date_format(Drone_Status.Timestamp, '%Y-%m-%d %T')
FROM Devices LEFT JOIN Drone_Status ON Devices.idDevice = Drone_Status.Devices_idDevice WHERE DeviceType = "drone"
ORDER BY Drone_Status.Timestamp DESC'''
data = db.sqlQuery(query)
return jsonify(data)
def getDoori(variable):
print(request.is_json)
content = request.get_json()
print(content)
query = "SELECT {} FROM doori".format(variable)
data = db.sqlQuery(query)
return jsonify(data)
def getDeviceName(devicename):
print(request.is_json)
content = request.get_json()
print(content)
query = "SELECT * FROM Testi where DeviceName = {}".format(devicename)
data = db.sqlQuery(query)
return jsonify(data)
def getImage(deviceid):
content = request.get_json()
query = '''SELECT Names FROM Images WHERE Devices_idDevice = "{}" ORDER BY Timestamp DESC LIMIT 1'''.format(
deviceid)
fileName = db.sqlQuery(query)
img_rawdata = ifh.getImage(fileName)
return jsonify(img_rawdata)
def getDeviceAll():
print(request.is_json)
content = request.get_json()
print(content)
query = "SELECT * FROM Testi"
data = db.sqlQuery(query)
return jsonify(data)
def getGopigoDetails(deviceid):
content = request.get_json()
query = '''SELECT Devices.DeviceName, Battery.BatteryStatus, Location.Segment, Devices.Connected FROM Devices
LEFT JOIN Battery ON Devices.idDevice = Battery.Devices_idDevice LEFT JOIN Location ON Devices.idDevice = Location.Devices_idDevice
WHERE idDevice = "{}" ORDER BY Battery.Timestamp DESC, Location.Timestamp DESC LIMIT 1'''.format(
deviceid)
data = db.sqlQuery(query)
return jsonify(data)
def getRuuvitagLimits(deviceid):
content = request.get_json()
query = '''SELECT Measurements_Limits.Temperature_max, Measurements_Limits.Temperature_min, Measurements_Limits.Humidity_max, Measurements_Limits.Humidity_min,
Measurements_Limits.AirPressure_max, Measurements_Limits.AirPressure_min, Measurements_Limits.Batterylimit FROM Devices
LEFT JOIN Measurements_Limits ON Measurements_Limits.Devices_idDevice = Devices.idDevice WHERE Devices.idDevice = "{}"'''.format(
deviceid)
data = db.sqlQuery(query)
return jsonify(data)
def getGopigoActives():
content = request.get_json()
query = '''SELECT Devices.idDevice, Location.Segment
FROM Door LEFT JOIN Devices ON Door.Devices_idDevice = Devices.idDevice
LEFT JOIN Door_status ON Door.idDoor = Door_status.Door_idDoor
LEFT JOIN Location ON Devices.idDevice = Location.Devices_idDevice
WHERE Door_status.OpenOrNot = "1" AND Door_status.Active = "1" GROUP BY Location.Segment'''
data = db.sqlQuery(query)
return jsonify(data)
def getStationHistory(timest):
content = request.get_json()
query = '''SELECT Devices.DeviceName, date_format(Charger_Status.Timestamp, '%Y-%m-%d %T')
FROM Devices INNER JOIN Charger_Status ON Devices.idDevice = Charger_Status.Devices_idDevice
WHERE Charger_Status.Timestamp < "{}" ORDER BY Charger_Status.Timestamp DESC LIMIT 10'''.format(
timest)
data = db.sqlQuery(
query
) # 2020-12-01 12:12:12 (yyyy-mm-dd hh:mm:ss) timestamp format as input
return jsonify(data)
def getImageSQLQueryDeviceID(deviceID):
query = '''Select Names from Images Where Devices_idDevice = "{}"'''.format(str(deviceID))
imageNames = str(db.sqlQuery(query))
imageNames = imageNames.replace("(","")
imageNames = imageNames.replace(")","")
imageNames = imageNames.replace(",","")
imageNames = imageNames.split(" ")
return imageNames
def getRuuvitagLatest(deviceid):
content = request.get_json()
query = '''SELECT Battery.BatteryStatus, Devices.DeviceName, Location.Segment, Door_status.OpenOrNot,
Measurements.Temperature, Measurements.Humidity, Measurements.AirPressure, Devices.Connected
FROM Door LEFT JOIN Devices ON Door.Devices_idDevice = Devices.idDevice
LEFT JOIN Door_status ON Door.idDoor = Door_status.Door_idDoor
LEFT JOIN Measurements ON Devices.idDevice = Measurements.Devices_idDevice
LEFT JOIN Battery ON Devices.idDevice = Battery.Devices_idDevice
LEFT JOIN Location ON Devices.idDevice = Location.Devices_idDevice
WHERE Devices.idDevice = {} AND Devices.DeviceType = "ruuvitag"
ORDER BY Measurements.Timestamp DESC, Door_status.Timestamp DESC, Battery.Timestamp DESC, Location.Timestamp DESC LIMIT 1'''.format(
deviceid)
data = db.sqlQuery(query)
return jsonify(data)
def getImageByTime(timest):
content = request.get_json()
query = '''SELECT Devices.DeviceName, Images.Names, Devices_idDevice, date_format(Images.Timestamp, '%Y-%m-%d %T') FROM Devices LEFT JOIN Images
ON Devices.idDevice = Images.Devices_idDevice WHERE Images.Timestamp > "{}"'''.format(
timest)
fileNames = db.sqlQuery(query)
img_rawdata = ifh.getMultipleImages(
fileNames) # TODO: fix internal server errors: imageFileHandler.py
try:
i = 0 # TO DO: FIX This part has issues
wholeData = []
for image in img_rawdata:
imageData = fileNames[i][:4] + (img_rawdata[i], )
wholeData.append(imageData)
i = i + 1
print(imageData)
return jsonify(wholeData)
except:
return "Image handling failed"
def getDoorMeasurements():
content = request.get_json()
query = "SELECT * FROM Measurements ORDER BY Timestamp DESC"
data = db.sqlQuery(query)
return jsonify(data)
def getImageTable():
content = request.get_json()
query = "SELECT Names, Timestamp, Devices_idDevice FROM Images ORDER BY Timestamp DESC"
data = db.sqlQuery(query)
return jsonify(data)
def getRuuvitagID():
content = request.get_json()
query = '''SELECT DISTINCT idDevice FROM Devices WHERE DeviceType = "ruuvitag"'''
data = db.sqlQuery(query)
return jsonify(data)
def getChargeStationLog():
content = request.get_json()
query = '''SELECT Devices.idDevice, Devices.DeviceName, Charger_Status.Status, Charger_Status.Timestamp
FROM Devices INNER JOIN Charger_Status ON Devices.idDevice = Charger_Status.Devices_idDevice ORDER BY Timestamp DESC'''
data = db.sqlQuery(query)
return jsonify(data)
def getChargeStationStatus():
content = request.get_json()
query = '''SELECT Status FROM Charger_Status ORDER BY Timestamp DESC LIMIT 1'''
data = db.sqlQuery(query)
return jsonify(data)
def getDeviceLatestCarAtChargeStation():
content = request.get_json()
query = '''SELECT Devices.idDevice, Location.Timestamp
FROM Devices INNER JOIN Location ON Devices.idDevice = Location.Devices_idDevice WHERE Segment = "1" ORDER BY Timestamp DESC LIMIT 1'''
data = db.sqlQuery(query)
return jsonify(data)
def getActiveMessages():
content = request.get_json()
query = '''SELECT Message.idMessage, Message.Messagetype, Message.Explanation, date_format(Message.Timestamp, '%Y-%m-%d %T'), Devices.Devicename
FROM Devices INNER JOIN Message ON Devices.idDevice = Message.Devices_idDevice WHERE Message.Active = "1"'''
data = db.sqlQuery(query)
return jsonify(data)
def getDooriID():
query = "SELECT iddoori FROM doori"
data = db.sqlQuery(query)
return jsonify(data)
def getCarIDs():
content = request.get_json()
query = '''SELECT DISTINCT idDevice FROM Devices WHERE DeviceType = "gopigo"'''
data = db.sqlQuery(query)
return jsonify(data)
def getDoorsStatus():
content = request.get_json()
query = "SELECT * FROM Door_status ORDER BY Timestamp DESC"
data = db.sqlQuery(query)
return jsonify(data)
def getDoors():
content = request.get_json()
query = "SELECT * FROM Door"
data = db.sqlQuery(query)
return jsonify(data)
def getStationStatus():
content = request.get_json()
query = '''SELECT Charger_Status.Status, Devices.DeviceName FROM Devices LEFT JOIN Charger_Status
ON Devices.idDevice = Charger_Status.Devices_idDevice ORDER BY Timestamp DESC LIMIT 1'''
data = db.sqlQuery(query)
return jsonify(data)
def doori_all():
query = "SELECT * FROM doori"
data = db.sqlQuery(query)
return jsonify(data)
def test_all():
query = "SELECT * FROM Testi"
data = db.sqlQuery(query)
return jsonify(data)
def getLocations():
content = request.get_json()
query = "SELECT * FROM Location ORDER BY Timestamp DESC"
data = db.sqlQuery(query)
return jsonify(data)
def getDeviceLocationStatus():
content = request.get_json()
query = "SELECT * FROM DeviceLocationsStatus"
data = db.sqlQuery(query)
return jsonify(data)
def getDeviceID():
query = "SELECT idTest FROM Testi"
data = db.sqlQuery(query)
return jsonify(data)
def getGopigoBatteryLimits(deviceID):
content = request.get_json()
query = '''Select Batterylimit from Measurements_Limits WHERE Devices_idDevice = "{}"'''.format(
deviceID)
data = db.sqlQuery(query)
return jsonify(data)
