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)