def get_vote_data(rack_id, user_id):
# return db row for row with rack_id=rack_id and user_id=user_id
db = get_db()
query = "SELECT * from votes WHERE rack_id = ? AND user_id = ?"
result = db.execute(query, (rack_id, user_id)).fetchall()
result = [h.dict_from_row(row) for row in result]
return result
def get_racks():
status = request.args.get('status', type=str)
user_id = request.args.get('userId', type=str)
# make a connection to the database
db = get_db()
racks = helper.get_racks(db, status, user_id)
return racks
def get_single_rack():
# get rack based on rack_id
rack_id = request.args.get('rack_id', type=int)
if not rack_id:
return "No rack_id specified", 400
# database connection
db = get_db()
rack = helper.get_single_rack(db, rack_id)
return rack
def submit_vote():
# insert a vote into the vote db for rack with rack_id = rack_id, vote by
# user with user_id = user_id and vote_type=vote_type
rack_id = request.args.get('rack_id', type=int)
user_id = request.args.get('user_id', type=str)
new_vote = request.args.get('vote_type', type=int)
delta_up = 0
delta_down = 0
if not rack_id:
return "No rack_id specified", 400
# connect to db
db = get_db()
# check if user has voted on this rack before
row = h.get_vote_status(db, rack_id, user_id)
old_vote = row[0] if row else 0
# vote_status is an object, {vote_type: -1} for example or None
if new_vote == 0:
query = """
DELETE FROM
votes
WHERE
rack_id = ? AND user_id = ?
"""
db.execute(query, (rack_id, user_id))
else:
query = """ INSERT INTO votes (rack_id, user_id, vote_type)
VALUES (?, ?, ?)
ON CONFLICT(rack_id, user_id) DO UPDATE SET vote_type=?"""
db.execute(query, (rack_id, user_id, new_vote, new_vote))
db.commit()
if new_vote > old_vote:
# This is a new upvote (1, 0) or change from down to up (1, -1) or change from down to no vote (0, -1)
delta_up = new_vote
delta_down = old_vote
elif new_vote < old_vote:
# This is a new downvote (0, 1) or change from up to down (-1, 1) or change from up to no vote (-1, 0)
delta_up = -old_vote
delta_down = -new_vote
if delta_up or delta_down:
h.update_vote_count(db, rack_id, delta_up, delta_down)
return ('OK', 200)
def submitSuggestion():
rack_id = request.form.get("rack_id", type=float)
reason_id = request.form.get("reason_id", type=int)
user_id = request.form.get("user_id", type=str)
# connect to database
db = get_db()
query = """
INSERT INTO
suggested_removals
(rack_id, user_id, reason_id, time_stamp)
VALUES
(?, ?, ?, datetime('now'))"""
db.execute(query, (rack_id, user_id, reason_id))
db.commit()
return ('OK', 200)
def get_vote_status():
# return true if user with user_id = user_id has voted for
# rack with rack_id=rack_id, false otherwise
rack_id = request.args.get('rack_id', type=int)
user_id = request.args.get('user_id', type=str)
if not rack_id:
return "No rack_id specified", 400
# make connection the database
db = get_db()
query = "SELECT vote_type FROM votes WHERE rack_id = ? AND user_id = ?"
result = db.execute(query, (rack_id, user_id)).fetchone()
if result:
result = h.dict_from_row(result)
return jsonify(result)
def coordinates():
# get the coordinates from the request
lat = request.form.get('lat', 0, type=float)
lng = request.form.get('lng', 0, type=float)
address = request.form.get('address', '')
if not helper.validate_coordinates((lat, lng)):
return "Invalid Coordinates", 500
# if valid, save in database
# connect to database to be able to store new coordinates for temporary bikerack
db = get_db()
db.execute(
'INSERT INTO bikeracks (latitude, longitude, address) VALUES (?, ?, ?);',
(lat, lng, address))
db.commit()
# return data for the added temporary marker
bike_rack = helper.get_rack_state(db, lat, lng)
return bike_rack