def post(self): try: _stationID = request.form['StationID'] _stationName = request.form['Name'] _stationrefTime = request.form['refTime'] _stationSettings = request.form['settings'] except KeyError: return { 'message': 'There are missing arguments in the request.' }, 400 try: db = DatabaseUtility() db.ChangeDatabase(session['username']) rowCnt = db.RunCommand( "SELECT EXISTS (SELECT 1 FROM stations WHERE StationID = %s)", (_stationID, )) if (rowCnt[0][0] == 1): db.RunCommand( "UPDATE stations SET Name=%s, refTime=%s, temperature=%s, humidity=%s, lux=%s, soil=%s, battery=%s, co2=%s WHERE StationID=%s", (_stationName, _stationrefTime, _stationSettings[0], _stationSettings[1], _stationSettings[2], _stationSettings[3], _stationSettings[4], _stationSettings[5], _stationID)) return {'message': 'OK'}, 200 else: return {'message': 'Station does not exist'}, 400 except DBException as e: return {'message': e.msg}, 400 except Exception: return {'message': 'Internal server error'}, 500
def post(self): try: _stationID = request.form['StationID'] _stationName = request.form['Name'] _stationrefTime = request.form['refTime'] _stationSettings = request.form['settings'] except KeyError: return { 'message': 'There are missing arguments in the request.' }, 400 try: db = DatabaseUtility() db.ChangeDatabase(session['username']) rowCnt = db.RunCommand( "SELECT EXISTS (SELECT 1 FROM stations WHERE StationID = %s)", (_stationID, )) if (rowCnt[0][0] == 0): db.RunCommand( "INSERT INTO stations (StationID, Name, refTime, temperature, humidity," " lux, soil, battery, co2) values ((%s),(%s),(%s),(%s),(%s),(%s),(%s),(%s),(%s))", (_stationID, _stationName, _stationrefTime, _stationSettings[0], _stationSettings[1], _stationSettings[2], _stationSettings[3], _stationSettings[4], _stationSettings[5])) return {'message': 'OK'}, 200 else: return {'message': 'Station already exist'}, 400 except DBException as e: return {'message': e.msg}, 400 except Exception: return {'message': 'Internal server error.'}, 500
def get(self): try: dateStart = request.args.get('date1', default=None, type=str) dateEnd = request.args.get('date2', default=None, type=str) station = request.args.get('station', default='1', type=str) if (dateStart is None): return {'message': 'Start date not specified'}, 404 db = DatabaseUtility() db.ChangeDatabase(session['username']) if (dateEnd is None): rows = db.RunCommand( "SELECT * from measurements WHERE measurementDate >= (%s) AND StationID = (%s)", (dateStart, station)) else: rows = db.RunCommand( "SELECT * from measurements WHERE measurementDate BETWEEN (%s) AND (%s) AND StationID = (%s)", (dateStart, dateEnd, station)) except DBException as e: return {'message': e.msg}, 502 a = [] for row in rows: message = {} message['StationID'] = str(row[0]) message['temperature'] = str(row[1]) message['humidity'] = str(row[2]) message['lux'] = str(row[3]) message['soil'] = str(row[4]) message['co2'] = str(row[5]) message['battery'] = str(row[6]) message['measurementDate'] = str(row[7]) a.append(message) resp = jsonify({'measurements': a}) resp.status_code = 200 return resp
def post(self): try: _stationID = str(request.form['stationID']) _temperature = str(request.form['temperature']) _humidity = str(request.form['humidity']) _lux = str(request.form['lux']) _soil = str(request.form['soil']) _co2 = str(request.form['co2']) _battery = str(request.form['battery']) if (int(_battery) > 100): _battery = str(int((int(_battery) - 4200) / 0.8 * 100)) except KeyError: return { 'message': 'There are missing arguments in the request.' }, 400 try: db = DatabaseUtility() db.ChangeDatabase(session['username']) db.RunCommand( "INSERT INTO measurements (StationID,temperature,humidity,lux,soil,co2,battery)" " VALUES ((%s), (%s), (%s), (%s), (%s), (%s), (%s))", (_stationID, _temperature, _humidity, _lux, _soil, _co2, _battery)) return {'message': 'OK'}, 200 except DBException as e: return {'message': e.msg}, 400 except Exception: return {'message': 'Internal server error'}, 500
def post(self): try: _stationID = request.form['StationID'] except KeyError as e: return { 'message': 'There are missing arguments in the request.' }, 400 try: db = DatabaseUtility() db.ChangeDatabase(session['username']) db.RunCommand("DELETE FROM stations WHERE StationID = (%s)", (_stationID, )) return {'message': 'OK'}, 200 except DBException as e: return {'message': e.msg}, 502
def post(self): try: _userEmail = request.form['email'] _userPassword = request.form['password'] _userName = request.form['userName'] _userPhone = request.form['phone'] except KeyError: return { 'message': 'There are missing arguments in the request.' }, 400 if (re.search("^[^\\/\?\%\*:\|\"<>\.\s]{1,64}$", _userName) is None): #username should compromise to folder naming standard return { 'message': "Username should consist only of letters and numbers" }, 400 try: db = DatabaseUtility() db.ChangeDatabase('dbUsers') data = db.RunCommand( "SELECT EXISTS (SELECT 1 FROM users WHERE Email = '{0}')". format(_userEmail)) if (data[0][0] == 1): return {'message': 'Email already taken'}, 400 data = db.RunCommand( "SELECT EXISTS (SELECT 1 FROM users WHERE UserName = '******')". format(_userName)) if (data[0][0] == 1): return {'message': 'Username already taken'}, 400 db.RunCommand( "INSERT INTO users (UserName,Email,Password,Phone) values (%s, %s, %s ,%s)", (_userName, _userEmail, _userPassword, _userPhone)) db.RunCommand("CREATE DATABASE {0}".format(_userName)) db.ChangeDatabase(_userName) db.RunCommand( "CREATE TABLE stations (StationID INT NOT NULL, Name varchar(45) NOT NULL, PRIMARY KEY(StationID))" ) db.RunCommand(( "CREATE TABLE measurements (StationID INT NOT NULL, temperature DECIMAL(3,1), humidity DECIMAL(4,1), " "lux SMALLINT UNSIGNED, soil DECIMAL(4,1), co2 SMALLINT UNSIGNED, battery DECIMAL(3,0), measurementDate " "TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, FOREIGN KEY (StationID) REFERENCES stations(StationID))" )) return {'message': 'OK'}, 200 except DBException as e: return {'message': e.msg}, 400 except: return {'message': "Internal server error"}, 500
def post(self): try: userEmail = request.form['email'] except KeyError as e: return { 'message': 'There are missing arguments in the request.' }, 400 try: db = DatabaseUtility() db.ChangeDatabase('dbUsers') data = db.RunCommand( "SELECT EXISTS (SELECT 1 FROM users WHERE Email = {0})".format( userEmail)) if (data[0][0] == 1): return {'message': 'Email already taken'}, 200 else: return {'message': 'Email available'}, 200 except DBException as e: return {'message': e.msg}, 400
def get(self): try: db = DatabaseUtility() #done db.ChangeDatabase(session['username']) rows = db.RunCommand( "select StationID,Name,refTime,temperature,humidity,lux,soil,battery,co2 from stations" ) a = [] for row in rows: message = {} message['StationID'] = str(row[0]) message['Name'] = str(row[1]) message['refTime'] = str(row[2]) message['enableSettings'] = str(row[3]) + str(row[4]) + str( row[5]) + str(row[6]) + str(row[7]) + str(row[8]) a.append(message) resp = jsonify({'stations': a}) resp.status_code = 200 return resp except DBException as e: return {'message': e.msg}, 400
def post(self): try: _userEmail = request.form['email'] _userPassword = request.form['password'] except KeyError: return { 'message': 'There are missing arguments in the request.' }, 400 try: db = DatabaseUtility() db.ChangeDatabase('dbUsers') data = db.RunCommand("SELECT * FROM users WHERE Email = %s", (_userEmail, )) except: return {'message': 'Database unreachable'}, 400 try: if (_userPassword == str(data[0][3])): session['logged_in'] = True session['username'] = str(data[0][1]) return {'message': 'OK'}, 200 except: return {'message': 'Wrong username or password'}, 400
def get(self): try: dateStart = request.args.get('date1', default=None, type = str) dateEnd = request.args.get('date2', default=None, type=str) station = request.args.get('station',default='1', type=str) db = DatabaseUtility() db.ChangeDatabase(session['username']) rows = db.RunCommand("select * from measurements where StationID = (%s) and measurementDate =" "(select max(measurementDate)from measurements where measurementDate > " "date_sub(now(), interval 1 day) and StationID = (%s))",(station,station)) #if(dateEnd is None): # rows = db.RunCommand("SELECT * from measurements WHERE measurementDate >= (%s) AND StationID = (%s)" , # (dateStart,station)) #else: # rows = db.RunCommand("SELECT * from measurements WHERE measurementDate BETWEEN (%s) AND (%s) " # "AND StationID = (%s)", (dateStart, dateEnd, station)) except DBException as e: return {'message': e.msg}, 400 try: a = [] for row in rows: message={} message['StationID']=str(row[0]) message['temperature']=str(row[1]) message['humidity'] = str(row[2]) message['lux'] = str(row[3]) message['soil'] = str(row[4]) message['co2'] = str(row[5]) message['battery'] = str(row[6]) message['measurementDate'] = str(row[7]) a.append(message) resp = jsonify({'measurements': a}) resp.status_code = 200 return resp except: return {'message':'Internal serve error'},500
def get(self): period = request.args.get('period', default='week', type=str) type = request.args.get('type', default='temperature', type=str) station = request.args.get('station', default='1', type=str) nrOfintervals = request.args.get('interval', default='100', type=str) intervals = int(nrOfintervals) try: db = DatabaseUtility() db.ChangeDatabase(session['username']) dict = { 'day': 1, 'week': 7, '3days': 3, 'month': 31, '3months': 93, 'year': 365 } az = "select StationID,avg({0}) as {0}, convert(min(measurementDate),datetime) as time from measurements where measurementDate between date_sub(now(), interval {1} day) and now() and StationID={2} group by measurementDate div (select ((unix_timestamp() - unix_timestamp())+ ({1} * 864000)) div {3} as tmp),StationID".format( type, dict[period], station, nrOfintervals) ay = "select count(StationID) from measurements where measurementDate between date_sub(now(), interval {0} day) and now() and StationID = {1}".format( dict[period], station) nr = db.RunCommand(ay) log.debug(nr[0][0]) if (int(nr[0][0]) <= int(nrOfintervals)): rows = db.RunCommand( "select StationID,{0},measurementDate from measurements where measurementDate between date_sub(now(), interval {1} day) and now() and StationID = {2}" .format(type, dict[period], station)) else: rows = db.RunCommand(az) except DBException as e: return {'message': e.msg}, 400 a = [] for row in rows: message = {} message['StationID'] = str(row[0]) message[type] = str(row[1]) message['time'] = str(row[2]) a.append(message) resp = jsonify({'measurements': a}) resp.status_code = 200 return resp
def get(self): try: period = request.args.get('period', default='week', type=str) type = request.args.get('type', default='temperature', type=str) station = request.args.get('station', default='1', type=str) nrOfintervals = request.args.get('interval', default='100', type=str) intervals = int(nrOfintervals) except: return { 'message': 'There are missing arguments in the request.' }, 400 try: db = DatabaseUtility() db.ChangeDatabase(session['username']) dict = { 'day': 1, 'week': 7, '3days': 3, 'month': 31, '3months': 93, 'year': 365 } minmax = " select timestampdiff(second,min,max) div {0} as seconds from (select min(measurementDate) as min, max(measurementDate) as max" \ " from measurements where measurementDate > DATE_SUB(NOW(), INTERVAL {1} DAY) and StationID = {2}) as temp".format(nrOfintervals, dict[period],station) response = db.RunCommand(minmax) try: seconds = int(response[0][0]) except: return {'measurements': []} az = ( "select StationID,avg({0}) as {0}, measurementDate from measurements" " where measurementDate between date_sub(now(), interval {1} day) and now() and StationID={2}" " group by measurementDate div {4},StationID").format( type, dict[period], station, nrOfintervals, seconds) ay = ( "select count(StationID) from measurements where measurementDate between date_sub(now(), interval {0} day)" " and now() and StationID = {1}").format( dict[period], station) nr = db.RunCommand(ay) if (int(nr[0][0]) <= int(nrOfintervals)): rows = db.RunCommand(( "select StationID,{0},measurementDate from measurements where measurementDate" " between date_sub(now(), interval {1} day) and now() and StationID = {2}" ).format(type, dict[period], station)) else: rows = db.RunCommand(az) a = [] for row in rows: message = {} message['StationID'] = str(row[0]) message[type] = str(row[1]) message['time'] = str(row[2]) a.append(message) resp = jsonify({'measurements': a}) resp.status_code = 200 return resp except DBException as e: return {'message': e.msg}, 400 except Exception as e: return {'message': "Internal server error"}, 500