def register_whClient():
if request.method == 'POST':
clientUrl = request.json['clientUrl']
error = None
if not clientUrl:
error = 'Webhook client url required'
if error is not None:
return (error, 400)
else:
db = get_db()
db.execute('INSERT INTO webhook_clients (url) VALUES (?)',
(clientUrl, ))
db.commit()
if request.method == 'DELETE':
clientUrl = request.json['clientUrl']
error = None
if not clientUrl:
error = 'Webhook client url required'
if error is not None:
return (error, 400)
else:
# en caso de que no exista el registro, notificarlo
db = get_db()
db.execute('DELETE FROM webhook_clients WHERE url = ?',
(clientUrl, ))
db.commit()
return ("Client registry deleted", 200)
def manage_people():
if request.method == 'POST':
macaddr = request.json['macaddr']
name = request.json['name']
error = None
if not macaddr or not name:
error = 'Mac address and device name are required'
if error is not None:
return (error, 400)
else:
db = get_db()
db.execute(
'INSERT INTO people (macaddr, name) VALUES (?, ?)',
(macaddr, name)
)
db.commit()
return("Person successfuly registered", 200)
if request.method == 'GET':
error = None
db = get_db()
cursor = db.cursor()
cursor.execute(
'SELECT * FROM people'
)
rows = cursor.fetchall()
data = []
for row in rows:
data.append({"macaddr":row['macaddr'],
"name":row['name']
})
return json.dumps(data)
if error is not None:
return (error, 400)
if request.method == 'DELETE':
macaddr = request.json['macaddr']
name = request.json['name']
error = None
if not macaddr or not name:
error = 'Mac address and person name are required'
if error is not None:
return (error, 400)
else:
db = get_db()
db.execute(
'DELETE FROM people WHERE macaddr = ? AND name = ?',
(macaddr, name)
)
db.commit()
return("Person registry deleted", 200)
return("Server error", 500)
def delete_record(record_id):
# TODO: Sanitychecks, i.e. if recordid exists
db = get_db()
db.execute('DELETE FROM record WHERE id = ?', (record_id, ))
db.commit()
flash(u'The record has been deleted!', 'success')
return get_records()
def qsub():
db = get_db()
qsub_data = request.get_json()
db.execute('insert into job (name, command) values (?, ?)',
(qsub_data['name'], qsub_data['command']))
db.commit()
return "OK"
def create_mongo_indexes():
'''
Index the tables in Mongo to optimize query performance
'''
db = get_db()
db.testimonies.create_index('testimony_id')
db.tokens.create_index('testimony_id')
def register():
"""Register a new user.
Validates that the username is not already taken. Hashes the
password for security.
"""
if request.method == "POST":
username = request.form["username"]
password = request.form["password"]
gender = request.form["gender"]
birthday = request.form['birthday']
db = get_db()
error = None
if not username:
error = 'Username is required.'
elif not password:
error = 'Password is required.'
elif db.execute(
'SELECT UserId FROM storeowner WHERE username = ?', (username,)
).fetchone() is not None:
error = 'User {} is already registered.'.format(username)
if error is None:
db.execute(
"INSERT INTO storeowner (username, password,Gender, Birthday) VALUES (?,?,?,?)",
(username, generate_password_hash(password),gender,birthday)
)
db.commit()
return 'success'
return error
else:
return render_template("auth/register.html")
def generate_slice_report():
if request.method == 'GET':
error = None
db = get_db()
cursor = db.cursor()
try:
date_hour = request.args['date_hour']
cursor.execute('SELECT * FROM slice WHERE date_hour = ?',
(date_hour, ))
except:
cursor.execute('SELECT * FROM slice')
rows = cursor.fetchall()
data = []
for row in rows:
data.append({
"date_hour": row['date_hour'],
"floor0": row['floor0'],
"floor1": row['floor1'],
"floor2": row['floor2'],
"floor3": row['floor3']
})
return json.dumps(data)
if error is not None:
return (error, 400)
return ("Server error", 500)
def set_user_love():
if request.method == 'POST':
user_id = session.get('user_id')
store_id = request.form['storeid']
db = get_db()
res = db.execute(
'SELECT * FROM user_loves_store WHERE user_UserId=? and store_StoreId=?',
(
user_id,
store_id,
)).fetchone()
if not res:
db.execute(
'INSERT INTO user_loves_store (store_StoreId, user_UserId) VALUES(?,?)',
(
store_id,
user_id,
))
db.commit()
else:
db.execute('DELETE FROM user_loves_store WHERE ID=?',
(res['ID'], ))
db.commit()
return 'success'
return 'error'
def items():
'''Fetch tree data'''
db = get_db()
try:
return jsonify(list(db.fragments.find({}, {'_id': 0})))
except Exception: #pylint: disable=broad-except
return jsonify({'err': 'tree fragment fetch error'})
def slice_table_helper(locationdata):
db = get_db()
date_hour = datetime.datetime.now().strftime("%Y-%m-%d %H")
exists = db.execute("SELECT EXISTS(SELECT 1 FROM slice WHERE date_hour=?)",
(date_hour, ))
if (exists.fetchone()[0] == 0):
db.execute('INSERT INTO slice (date_hour)'
' VALUES (?)', (date_hour, ))
db.commit()
print("{} tiene {} dispositivos".format(
locationdata["data"]["apFloors"][0],
len(locationdata['data']['observations'])))
print(locationdata["data"]["apFloors"][0] == "Planta Baja")
if (locationdata["data"]["apFloors"][0] == "Planta Baja"):
db.execute('UPDATE slice SET floor0 = ? WHERE date_hour = ?',
(len(locationdata['data']['observations']), date_hour))
db.commit()
elif (locationdata["data"]["apFloors"][0] == "Piso 1"):
db.execute('UPDATE slice SET floor1 = ? WHERE date_hour = ?',
(len(locationdata['data']['observations']), date_hour))
db.commit()
elif (locationdata["data"]["apFloors"][0] == "Piso 2"):
db.execute('UPDATE slice SET floor2 = ? WHERE date_hour = ?',
(len(locationdata['data']['observations']), date_hour))
db.commit()
elif (locationdata["data"]["apFloors"][0] == "Piso 3"):
db.execute('UPDATE slice SET floor3 = ? WHERE date_hour = ?',
(len(locationdata['data']['observations']), date_hour))
db.commit()
else:
print("No esta registrado este piso")
return
def register():
"""Register a new user.
Validates that the username is not already taken. Hashes the
password for security.
"""
if request.method == "POST":
username = request.form["username"]
password = request.form["password"]
db = get_db()
error = None
if not username:
error = "Username is required."
elif not password:
error = "Password is required."
elif (db.execute("SELECT id FROM user WHERE username = ?",
(username, )).fetchone() is not None):
error = f"User {username} is already registered."
if error is None:
# the name is available, store it in the database and go to
# the login page
db.execute(
"INSERT INTO user (username, password) VALUES (?, ?)",
(username, generate_password_hash(password)),
)
db.commit()
return redirect(url_for("auth.login"))
flash(error)
return render_template("auth/register.html")
def filter_testimonies():
'''Get the distinct levels of each field used in filtering'''
db = get_db()
# initialize distinct value arrays
collections = set()
genders = set()
ghetto_names = set()
camp_names = set()
recording_years = set()
interviewee_names = set()
testimony_ids = set()
# build the search using any requested filters
args = get_filter_query(request.args)
# add each distinct level to the sets initialized above
for i in db.testimonies.find(args, projections['filters']):
collections.add(i['collection'])
genders.add(i['gender'])
interviewee_names.add(i['interviewee_name'])
testimony_ids.add(i['testimony_id'])
recording_years.add(i['recording_year'])
# handle list fields
for j in i['ghetto_names']:
ghetto_names.add(j)
for j in i['camp_names']:
camp_names.add(j)
return jsonify({
'collections': list(collections),
'genders': list(genders),
'ghetto_names': list(ghetto_names),
'camp_names': list(camp_names),
'recording_years': list(recording_years),
'interviewee_names': list(interviewee_names),
'testimony_ids': list(testimony_ids)
})
def generate_general_report():
if request.method == 'GET':
error = None
db = get_db()
cursor = db.cursor()
name = None
try:
name = request.args['name']
cursor.execute('SELECT * FROM visits WHERE name = ?', (name, ))
except:
cursor.execute('SELECT * FROM visits')
rows = cursor.fetchall()
data = []
for row in rows:
data.append({
"macaddr": row['macaddr'],
"name": row['name'],
"timesseen": row['timesseen'],
"maxstay": row["maxstay"],
"maxdate": row["maxdate"],
"minsstay": row["minstay"],
"mindate": row["mindate"],
"averagestay": row['averagestay']
})
return json.dumps(data)
if error is not None:
return (error, 400)
return ("Server error", 500)
def update_question(test_id, question_id, statement, option_a, option_b,
option_c, option_d, correct_option):
db = get_db()
db.execute(
"UPDATE questions SET statement = ?, option_a = ?, option_b = ?, option_c = ?, option_d = ?, correct_option = ? WHERE test_id = ? AND question_id = ?",
(statement, option_a, option_b, option_c, option_d, correct_option,
test_id, question_id))
db.commit()
def get_records():
db = get_db()
records = db.execute(
'SELECT record.id, sensor.sensorname, timepoint, temperature, humidity, pressure FROM record INNER JOIN sensor on record.sensor_id = sensor.id; '
).fetchall()
return render_template('record/recordlist.html',
records=records,
showsensorname=True)
def testimony():
'''Fetch a transcript'''
db = get_db()
args = {'testimony_id': request.args.get('testimony_id')}
result = list(db.testimonies.find(args, projections['testimony']))
if result:
return jsonify(result[0])
return jsonify([])
def index():
db = get_db()
posts = db.execute(
'SELECT p.id, title, body, created, author_id, username'
' FROM post p JOIN user u ON p.author_id = u.id'
' ORDER BY created DESC'
).fetchall()
return render_template('records/index.html', posts=posts)
def add_question(test_id, question_id, statement, option_a, option_b, option_c,
option_d, correct_option):
db = get_db()
db.execute(
"INSERT INTO questions(test_id, question_id, statement, option_a, option_b, option_c, option_d, correct_option) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
(test_id, question_id, statement, option_a, option_b, option_c,
option_d, correct_option))
db.commit()
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute('SELECT * FROM user WHERE id = ?',
(user_id, )).fetchone()
def load_logged_in_user():
user_id = session.get('user_id')
db = get_db()
if user_id is None:
g.user = None
else:
g.user = db.execute(
'SELECT * FROM storeowner WHERE UserId = ?', (user_id,)
).fetchone()
def load_logged_in_user():
"""If a user id is stored in the session, load the user object from
the database into ``g.user``."""
user_id = session.get("user_id")
if user_id is None:
g.user = None
else:
g.user = (get_db().execute("SELECT * FROM user WHERE id = ?",
(user_id, )).fetchone())
def delete_sensor(sensorname):
db = get_db()
if sensor_exists(db, sensorname):
db.execute('DELETE FROM sensor WHERE sensorname = ?', (sensorname, ))
db.commit()
flash(u'The sensor has been deleted!', 'success')
return getsensors()
else:
flash(u'The sensor does not exist!', 'error')
return getsensors()
def get_records_for_sensorname(sensorname):
db = get_db()
if sensor_exists(db, sensorname):
records = db.execute(
'SELECT id, timepoint, temperature, humidity, pressure FROM record WHERE sensor_id = ?',
(get_sensor_id(db, sensorname), )).fetchall()
return render_template('record/recordlist.html', records=records)
else:
flash(u'The sensor does not exist. Showing all records instead!',
'error')
return get_records()
def typeahead():
'''Given a field and a string, return all values
in the given field that contain the string'''
db = get_db()
field = request.args.get('field', None)
query = request.args.get('query', '')
if not field:
return jsonify(['Error: a field is required'])
search = {field: {'$regex': query}}
results = db.testimonies.find(search).distinct(field)
return jsonify(results)
def generate_dummy_data():
from secrets import choice
import random
db = get_db()
def generate_dummy_sensors():
names = ['alan', 'adam', 'collin', 'ethan', 'frank', 'homer']
rooms = ['garage', 'livingroom', 'kitchen', 'bath']
extender = ['final', 'new', 'old']
generated_sensors = []
for i in range(10):
generated_sensor = "{}_{}".format(choice(names), choice(rooms))
while generated_sensor in generated_sensors:
generated_sensor = "{}_{}".format(generated_sensor,
choice(extender))
generated_sensors.append(generated_sensor)
for sensor in generated_sensors:
db.execute(
'INSERT INTO sensor (sensorname, description) VALUES (?, "Dummy Sensor!")',
(sensor, ))
db.commit()
return generated_sensors
def generate_dummy_entries(sensors):
from time import strftime, localtime
import datetime
for sensor in sensors:
_time = datetime.datetime.now()
sensor_id = get_sensor_id(db, sensor)
temperature = random.randint(20, 35)
humidity = random.random()
pres = random.randint(900, 1500)
for i in range(10):
temperature = temperature + random.uniform(-5, 5)
humidity = humidity + random.uniform(-0.1, 0.1)
pres = pres + random.uniform(-10, 10)
_time = _time - datetime.timedelta(minutes=10)
# sanity checks
if humidity > 1:
humidity = 1
elif humidity < 0:
humidity = 0
db.execute(
'INSERT INTO record (sensor_id, temperature, humidity, pressure, timepoint) VALUES (?, ?, ?, ?, ?)',
(sensor_id, temperature, humidity, pres, _time))
db.commit()
sensors = generate_dummy_sensors()
generate_dummy_entries(sensors)
flash('The dummy entries have been generated', 'success')
return render_template('settings/default.html')
def add_link(token):
db = get_db()
# Retrieve the link
body = request.json
db.execute('INSERT INTO history (person_token, link) VALUES (?, ?)',
(token, body["link"]))
db.commit()
return "Success"
def find_user(email):
if email is None:
raise ValueError(
'Email must be provided to find_user method to make a lookup')
db = get_db()
cursor = db.cursor()
cursor.execute("SELECT * FROM users WHERE email=?", (email, ))
return cursor.fetchone()
def get_sensors(limit = 100):
db = get_db()
records = db.execute(
'SELECT timepoint, AVG(temperature) AS temperature, AVG(humidity) AS humidity, AVG(pressure) AS pressure FROM record GROUP BY strftime(\'%Y-%m-%dT%H:%M:00.000\', timepoint) ORDER BY timepoint DESC LIMIT ?',
(limit, )
).fetchall()
data = dict()
data['temperature'] = [record['temperature'] for record in records]
data['humidity'] = [record['humidity'] for record in records]
data['pressure'] = [record['pressure'] for record in records]
data['dates'] = [record['timepoint'] for record in records]
return render_template('vis/base.html', data=data, sensors = get_all_sensors(db))
def delete_user(id):
if id is None:
raise ValueError('Id must be provided to delete user')
db = get_db()
cursor = db.cursor()
cursor.execute("DELETE FROM users WHERE id=?", (id, ))
db.commit()
# test comment
return cursor.fetchone()
def artists():
db = get_db()
sightings = Table('sightings')
q = Query.from_(sightings).select(
'artist'
).where(
sightings.user_id == g.user['id']
).orderby('artist').distinct()
results = db.execute(q.get_sql()).fetchall()
return render_template('sightings/artists.html', results=results)