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
