def request_device_readings_quartiles(device_uuid):
"""
This endpoint allows clients to GET the 1st and 3rd quartile
sensor reading value for a device.
Mandatory Query Parameters:
* type -> The type of sensor value a client is looking for
* start -> The epoch start time for a sensor being created
* end -> The epoch end time for a sensor being created
# TODO: implement a better database to be able to calculate quartiles properly
#
"""
where_statement = get_where_statement(device_uuid, request.args)
where_statement = where_statement if where_statement else 'where 1'
cur, conn = get_db_cursor(app)
query = '''
select value, NTILE(4) OVER(ORDER BY value) as quartile from readings where {}
'''.format(where_statement)
cur.execute(query)
rows = cur.fetchall()
data = {}
for row in rows:
if row['quartile'] == 1:
data['quartile_1'] = row['value']
if row['quartile'] == 3:
data['quartile_3'] = row['value']
conn.close()
return jsonify(data), 200
def create_test_ticket(self):
with get_db_cursor(commit=True) as cursor:
cursor.execute("""INSERT INTO tickets (theme, text, email, status)
VALUES('test_theme', 'test_text', '*****@*****.**',
'open') RETURNING id;""")
ticket = cursor.fetchone()
return ticket["id"]
def answer():
if 'username' not in session:
return redirect(url_for("login", next="answer"))
if session['role'] != 'expert' and session['role'] != 'admin':
abort(403)
cursor = get_db_cursor()
if request.method == 'POST':
db = get_db()
cursor.execute(
"INSERT INTO answers (body, question_id, answer_owner) VALUES (%s, %s, %s)",
(request.form.get("answer"), request.args.get("id"),
session["username"]))
cursor.execute("UPDATE questions SET status='answered' WHERE id=%s",
(request.args.get("id"), ))
db.commit()
flash("Your answer was submitted successfully!", "success")
return redirect(url_for('answer'))
cursor.execute(
"SELECT * FROM questions WHERE status='not answered' and asked_username=%s",
(session["username"], ))
questions = cursor.fetchall()
return render_template("answer.html",
title="Answer Questions",
questions=questions)
def question(id):
if 'username' not in session:
return redirect(url_for("login", next="question", id=id))
cursor = get_db_cursor()
cursor.execute(
"SELECT questions.body as question, answers.body as answer,\
questions.asker_username as asker, questions.asked_username as asked,\
questions.status as status, questions.ask_time as time FROM questions JOIN answers\
ON questions.id = answers.question_id WHERE questions.id = %s and answers.answer_owner = questions.asked_username",
(id, ))
question = cursor.fetchone()
if question:
cursor.execute(
"SELECT answer_owner, body as answer FROM answers WHERE question_id = %s and answer_owner != %s",
(id, question["asked"]))
answers = cursor.fetchall()
return render_template("question.html",
title="Question",
question=question,
answers=enumerate(answers) if answers else None)
flash("There is no question in here!!", "info")
return redirect(url_for("home"))
def questions_asked_user(username):
cursor = get_db_cursor(False)
cursor.execute(
"SELECT * FROM questions WHERE asked_username = %s and status = 'answered'",
(username, ))
return jsonify(
{"questions": dictionarizeData(cursor.fetchall(), cursor.description)})
def get_ticket(self, ticket_id):
ticket = self.get_from_cache("ticket_{0}".format(ticket_id))
if ticket:
return json.dumps(ticket, default=self.datetime_converter), 200
with get_db_cursor() as cursor:
ticket = self.get_ticket_with_comments(ticket_id, cursor)
self.set_to_cache("ticket_{0}".format(ticket_id), ticket)
return json.dumps(ticket, default=self.datetime_converter), 200
def post_ticket(self, request):
data = self.prepare_request_data(request)
data = self.validator.validate_ticket_post_schema(data)
with get_db_cursor(commit=True) as cursor:
cursor.execute(self.queries.create_ticket, data)
ticket = cursor.fetchone()
ticket.update({"comments": []})
self.set_to_cache("ticket_{0}".format(ticket["id"]), ticket)
return json.dumps(ticket, default=self.datetime_converter), 201
def create_test_comment(self):
with get_db_cursor(commit=True) as cursor:
cursor.execute(
"""INSERT INTO comments (text, email, ticket_id)
VALUES('test_text', '*****@*****.**',
%s) RETURNING id;""",
(self.ticket, ),
)
comment = cursor.fetchone()
return comment["id"]
def questions():
cursor = get_db_cursor(False)
cursor.execute("SELECT questions.body as question, answers.body as answer,\
questions.asker_username as asker, questions.asked_username as asked,\
questions.status as status, questions.ask_time as time FROM questions JOIN answers\
ON questions.id = answers.question_id WHERE answers.answer_owner = questions.asked_username"
)
return jsonify(
{"questions": dictionarizeData(cursor.fetchall(), cursor.description)})
def home():
if 'username' not in session:
return redirect(url_for("login", next="home"))
cursor = get_db_cursor()
cursor.execute(
"SELECT * FROM questions Where status='answered' ORDER BY ask_time DESC, id DESC"
)
questions = cursor.fetchall()
return render_template("home.html", title="Home", questions=questions)
def unanswered():
if 'username' not in session:
return redirect(url_for("login", next="unanswered"))
cursor = get_db_cursor()
cursor.execute("SELECT * FROM questions Where status='not answered'")
questions = cursor.fetchall()
return render_template("unanswered.html",
title="Unanswered",
questions=questions)
def wrapper(*args, **kwargs):
if not request.authorization:
return jsonify({"error": "Authentication Error"}), 403
cursor = get_db_cursor()
cursor.execute("SELECT * FROM users WHERE username = %s",
(request.authorization.username, ))
user = cursor.fetchone()
if not user or not check_password_hash(user["password"],
request.authorization.password):
return jsonify({"error": "Authentication Error"}), 403
return func(*args, **kwargs)
def patch_ticket(self, ticket_id, request):
data = self.validator.validate_ticket_patch_schema(
self.prepare_request_data(request))
status = data["status"]
with get_db_cursor(commit=True) as cursor:
self.check_ticket_patch_logic(ticket_id, status, cursor)
cursor.execute(self.queries.update_ticket_status,
(status, ticket_id))
ticket = cursor.fetchone()
cursor.execute(self.queries.get_comments_by_ticket, (ticket_id, ))
comments = cursor.fetchall() or []
ticket.update({"comments": comments})
self.set_to_cache("ticket_{0}".format(ticket_id), ticket)
return json.dumps(ticket, default=self.datetime_converter), 200
def post_comment(self, request):
data = self.validator.validate_comment_post_schema(
self.prepare_request_data(request))
with get_db_cursor(commit=True) as cursor:
try:
cursor.execute(self.queries.create_comment, data)
except IntegrityError:
raise ValidationException(
"You are not allowed create comments for closed tickets")
comment = cursor.fetchone()
self.set_to_cache(
"ticket_{0}".format(comment["ticket_id"]),
self.get_ticket_with_comments(comment["ticket_id"], cursor),
)
return json.dumps(comment, default=self.datetime_converter), 201
def request_device_readings_min(device_uuid, metric):
"""
This endpoint allows clients to GET the max sensor reading for a device.
Mandatory Query Parameters:
* type -> The type of sensor value a client is looking for
Optional Query Parameters
* start -> The epoch start time for a sensor being created
* end -> The epoch end time for a sensor being created
"""
if metric not in ['min', 'max', 'mean', 'median', 'mode']:
return 'Invalid value for metric', 404
where_statement = get_where_statement(device_uuid, request.args)
cur, conn = get_db_cursor(app)
if metric == 'median':
query = '''
SELECT AVG(value) as total
FROM (
SELECT value
FROM readings where {}
ORDER BY value
LIMIT 2 - (SELECT COUNT(*) FROM readings where {}) % 2 -- odd 1, even 2
OFFSET (SELECT (COUNT(*) - 1) / 2 FROM readings where {}))
'''.format(where_statement, where_statement, where_statement)
cur.execute(query)
row = cur.fetchone()
total = row['total']
response = jsonify(dict(value=total)), 200
elif metric == 'mode':
cur.execute('select `value`, count(*) as n from readings where {} group by value order by 2 DESC limit 1'.format(where_statement))
row = cur.fetchone()
total = row['value']
response = jsonify(dict(value=total)), 200
else:
if metric == 'mean':
metric = 'avg'
cur.execute('select {}(value) as value from readings where {}'.format(metric, where_statement))
row = cur.fetchone()
# Return the JSON
response = jsonify(dict(zip(['value'], row))), 200
conn.close()
return response
def users():
if session['role'] != 'admin':
abort(403)
cursor = get_db_cursor()
if request.method == 'POST':
db = get_db()
username = request.json["username"]
role = request.json["role"]
cursor.execute("UPDATE users SET role=%s WHERE username=%s",
(role, username))
db.commit()
return jsonify({'success': 'true'})
cursor.execute("SELECT * FROM users")
users = cursor.fetchall()
return render_template("users.html", title="User Setup", users=users)
def request_device_readings_post(device_uuid):
# Grab the post parameters
create_device_reading_schema = CreateDeviceReading()
unmarshal_result = create_device_reading_schema.load(request.data)
if unmarshal_result.errors:
abort(BAD_REQUEST, message=str(unmarshal_result.errors))
post_data = unmarshal_result.data
sensor_type = post_data.get('type')
value = post_data.get('value')
date_created = post_data.get('date_created', int(time.time()))
# Insert data into db
cur, conn = get_db_cursor(app)
cur.execute('insert into readings (device_uuid,type,value,date_created) VALUES (?,?,?,?)',
(device_uuid, sensor_type, value, date_created))
conn.commit()
conn.close()
# Return success
return 'success', 201
def answer_unanswered(id):
if 'username' not in session:
return redirect(url_for("login", next="answer_unanswered", id=id))
cursor = get_db_cursor()
cursor.execute(
"SELECT * FROM questions WHERE id=%s and status='not answered'",
(id, ))
question = cursor.fetchone()
if not question:
flash("There is no an unanswered question with this id!", "info")
return redirect(url_for('unanswered'))
if request.method == 'POST':
db = get_db()
cursor.execute(
"INSERT INTO answers (body, question_id, answer_owner) VALUES (%s, %s, %s)",
(request.form.get("answer"), id, session["username"]))
# if the one who answered the question was the one who the question was led to its
# status will be changed to 'answered'
if question["asked_username"] == session["username"]:
cursor.execute(
"UPDATE questions SET status='answered' WHERE id=%s", (id, ))
db.commit()
flash("Your answer was submitted successfully!", "success")
return redirect(url_for("unanswered"))
db.commit()
flash("Your answer has been submitted", "success")
return redirect(url_for('answer_unanswered', id=id))
else:
cursor.execute(
"SELECT answer_owner, body as answer FROM answers WHERE question_id = %s",
(id, ))
answers = cursor.fetchall()
return render_template("answer_unanswered.html",
question=question,
answers=enumerate(answers) if answers else None)
def ask():
if 'username' not in session:
return redirect(url_for("login", next="ask"))
cursor = get_db_cursor()
if request.method == 'POST':
db = get_db()
cursor.execute(
"INSERT INTO questions (ask_time, body, asker_username, asked_username) VALUES (%s, %s, %s, %s)",
(datetime.strftime(datetime.now(),
DATE_FORMAT), request.form.get("question"),
session["username"], request.form.get("expert")))
db.commit()
flash(
"Your question has been submitted successfully, and soon you will be able to get the answer to it.",
"success")
return redirect(url_for("ask"))
cursor.execute("SELECT * FROM users Where role='expert' and username!=%s",
(session["username"], ))
experts = cursor.fetchall()
return render_template("ask.html", title="Ask Question", experts=experts)
def request_device_readings_get(device_uuid):
"""
This endpoint allows clients to POST or GET data specific sensor types.
POST Parameters:
* type -> The type of sensor (temperature or humidity)
* value -> The integer value of the sensor reading
* date_created -> The epoch date of the sensor reading.
If none provided, we set to now.
Optional Query Parameters:
* start -> The epoch start time for a sensor being created
* end -> The epoch end time for a sensor being created
* type -> The type of sensor value a client is looking for
"""
# Execute the query
where_statement = get_where_statement(device_uuid, request.args)
cur, conn = get_db_cursor(app)
cur.execute('select * from readings where {}'.format(where_statement))
rows = cur.fetchall()
# Return the JSON
conn.close()
return jsonify([dict(zip(['device_uuid', 'type', 'value', 'date_created'], row)) for row in rows]), 200
def login():
if 'username' in session:
return redirect(url_for("home"))
if request.method == 'POST':
cursor = get_db_cursor()
cursor.execute("SELECT * FROM users WHERE username=%s",
(request.form.get("username"), ))
user = cursor.fetchone()
if user:
if check_password_hash(user["password"],
request.form.get("password")):
flash("You have logged in successfully!", "success")
session["username"] = user["username"]
session["role"] = user["role"]
if request.args:
if request.args.get("id"):
return redirect(
url_for(request.args.get("next"),
id=request.args.get("id")))
else:
return redirect(url_for(request.args.get("next")))
else:
return redirect(url_for("home"))
else:
flash("You should check your input!!", "danger")
# in here I redirected the user to the same page to make the request method
# GET again instead of POST
return redirect(url_for("login"))
else:
flash(
"There is no user with this username, maybe you should register first!",
"danger")
return redirect(url_for("login"))
return render_template("login.html", title="Login")
def add_question():
question = request.get_json()
question_body = question.get("question")
question_asker_username = question.get("asker_username")
question_asked_username = question.get("asked_username")
db = get_db()
cursor = get_db_cursor()
cursor.execute("SELECT * FROM users WHERE username = %s",
(question_asked_username, ))
asked_user = cursor.fetchone()
if not asked_user:
return jsonify({
"error":
f"there is no expert with the username {request.authorization.username}"
})
cursor.execute(
"INSERT INTO questions (ask_time, body, asker_username, asked_username) VALUES (%s, %s, %s, %s)",
(datetime.strftime(datetime.now(), DATE_FORMAT), question_body,
request.authorization.username, asked_user["username"]))
db.commit()
return jsonify({"status": "success"}), 201
def register():
if 'username' in session:
return redirect(url_for("home"))
if request.method == 'POST':
db = get_db()
cursor = get_db_cursor()
cursor.execute("SELECT * FROM users WHERE username=%s",
(request.form.get("username"), ))
user = cursor.fetchone()
if user:
flash("This username isn't available, Try choosing another one!",
"danger")
else:
hashed_password = generate_password_hash(
request.form.get("password"))
cursor.execute(
"INSERT INTO users (username, password) values (%s, %s)",
(request.form.get("username"), hashed_password))
db.commit()
flash("You signed up successfully, You can login now", "success")
return redirect(url_for("login"))
return render_template("register.html", title="Register")
def request_readings_summary():
"""
This endpoint allows clients to GET a full summary
of all sensor data in the database per device.
Optional Query Parameters
* type -> The type of sensor value a client is looking for
* start -> The epoch start time for a sensor being created
* end -> The epoch end time for a sensor being created
"""
where_statement = get_where_statement(args=request.args)
where_statement = where_statement if where_statement else '1=1'
cur, conn = get_db_cursor(app)
query = '''
select
device_uuid,
min(value) as `min`,
max(value) as `max`,
avg(value) as `mean`,
(
select value from (select value, ntile(4) over (order by value) as q from readings where {} and device_uuid=main_readings.device_uuid) as q1 where q = 1
) as quartile_1,
(
select value from (select value, ntile(4) over (order by value) as q from readings where {} and device_uuid=main_readings.device_uuid) as q1 where q = 3
) as quartile_3,
(
SELECT AVG(value)
FROM (
SELECT value
FROM readings where {} and device_uuid=main_readings.device_uuid
ORDER BY value
LIMIT 2 - (SELECT COUNT(*) FROM readings where {}) % 2 -- odd 1, even 2
OFFSET (SELECT (COUNT(*) - 1) / 2 FROM readings where {}))
) as median,
(
select value from (select `value`, count(*) as n from readings where {} and device_uuid=main_readings.device_uuid group by value order by 2 DESC limit 1)
) as `mode`
from readings as main_readings where {} group by device_uuid
'''.format(
where_statement,
where_statement,
where_statement,
where_statement,
where_statement,
where_statement,
where_statement
)
cur.execute(query)
rows = cur.fetchall()
data = []
for row in rows:
min = row['min']
max = row['max']
mean = row['mean']
quartile_1 = row['quartile_1']
quartile_3 = row['quartile_3']
median = row['median']
mode = row['mode']
data.append(dict(
device_uuid=row['device_uuid'],
min=min,
max=max,
mean=mean,
quartile_1=quartile_1,
quartile_3=quartile_3,
median=median,
mode=mode
))
conn.close()
return jsonify(data), 200
def tearDown(self):
with get_db_cursor(commit=True) as cursor:
cursor.execute("TRUNCATE tickets CASCADE;")
cursor.execute("TRUNCATE comments;")