def get_user_by_username(username):
if request.method == "GET":
try:
result = conn.execute(
"SELECT * FROM \"user\" WHERE username = \'{}\'".format(
username))
row = result.first()
if row != None:
user = {
"user_id": row["user_id"],
"email": row["email"],
"username": row["username"],
"password": row["password"],
"name": row["name"],
"latitude": row["latitude"],
"longitude": row["longitude"]
}
return jsonify({'status': 'success', 'user': user})
return jsonify({
'status': 'failed',
'message': 'No user record found!'
})
except Exception as e:
raise APIError(str(e))
elif request.method == "DELETE":
try:
result = conn.execute(
"DELETE FROM \"user\" WHERE username = \'{}\'".format(
username))
result = conn.execute(
"SELECT * FROM \"user\" WHERE username = \'{}\'".format(
username))
row = result.first()
if row != None:
return jsonify({
"status": "failed",
"message": "Failed to delete user."
})
return jsonify({
"status": "success",
"message": "Successfully deleted user!"
})
except Exception as e:
raise APIError(str(e))
else:
return jsonify({
'status': 'failed',
'message': "Endpoint /user requires GET or POST request"
})
def get_checkins_for_user(user_id):
if request.method == "GET":
try:
data = request.get_json()
result = conn.execute(
"SELECT * FROM \"checkins\" WHERE user_id = {} ORDER BY timestamp"
.format(user_id))
checkins = []
for row in result:
checkin = {}
for key in row.keys():
checkin[key] = row[key]
checkins.append(checkin)
return jsonify({'status': 'success', 'checkins': checkins})
except Exception as e:
raise APIError(str(e))
else:
return jsonify({
'status':
'failed',
'message':
"Endpoint /checkin/<user_id> requires GET or POST request"
})
def get_all_restaurant_locations(restaurant_id):
try:
query = """
SELECT locs.location_id, locs.latitude, locs.longitude, locs.restaurant_id, locs.restaurant_name, rat.avg
FROM
(SELECT location.location_id, location.latitude, location.longitude, location.restaurant_id, restaurant.restaurant_name
FROM location, restaurant
WHERE location.restaurant_id = restaurant.restaurant_id AND
restaurant.restaurant_id = {}
)AS locs,
(SELECT restaurant.restaurant_id, AVG(rating.rating) AS avg
FROM restaurant, rating
WHERE rating.restaurant_id = restaurant.restaurant_id
GROUP BY restaurant.restaurant_id
)AS rat
WHERE
locs.restaurant_id = rat.restaurant_id
""".format(restaurant_id)
result = conn.execute(query)
locations = []
for row in result:
location = {}
for key in row.keys():
if key == "avg":
location[key] = str(row[key])
else:
location[key] = row[key]
locations.append(location)
return jsonify({'status': 'success', 'locations': locations})
except Exception as e:
raise APIError(str(e))
def delete_checkins_with_id(checkin_id):
if request.method == "DELETE":
try:
result = conn.execute(
"DELETE FROM \"checkins\" WHERE checkin_id = {}".format(
checkin_id))
result = conn.execute(
"SELECT * FROM \"checkins\" WHERE checkin_id = {}".format(
checkin_id))
row = result.first()
if row != None:
return jsonify({
'status': 'failed',
'message': "Failed to delete checkin"
})
return jsonify({
'status': 'success',
'message': "Deleted checkin!"
})
except Exception as e:
raise APIError(str(e))
else:
return jsonify({
'status':
'failed',
'message':
"Endpoint /checkin/id/<checkin_id> requires DELETE request"
})
def upload_photo():
try:
data = request.get_json()
result = conn.execute("INSERT INTO photo (user_id, restaurant_id, photo_path, timestamp) VALUES ({0}, {1}, \'{2}\', \'{3}\')".format(data["user_id"], data["restaurant_id"], data["image_url"], time.strftime('%Y-%m-%d %H:%M:%S')))
return jsonify({'status' : 'success', 'message' : 'Successfully uploaded image!'})
except Exception as e:
raise APIError(str(e))
def get_location_data_given_cuisine(cuisine_id):
try:
query = """
SELECT location.location_id, location.latitude, location.longitude, location.restaurant_id, food_stuffs.restaurant_name
FROM location,
(SELECT food_places.restaurant_id, food_places.restaurant_name
FROM (SELECT DISTINCT restaurant_id, cuisine.cuisine_id, cuisine.cuisine_name
FROM serves
INNER JOIN cuisine ON serves.cuisine_id = cuisine.cuisine_id) as foods,
(SELECT DISTINCT restaurant.restaurant_id, restaurant_name
FROM restaurant
INNER JOIN serves ON restaurant.restaurant_id = serves.restaurant_id) as food_places
WHERE foods.cuisine_id = {} AND
food_places.restaurant_id = foods.restaurant_id
) AS food_stuffs
WHERE location.restaurant_id = food_stuffs.restaurant_id
""".format(cuisine_id)
result = conn.execute(query)
locations = []
for row in result:
location = {}
for key in row.keys():
location[key] = row[key]
locations.append(location)
return jsonify({'status': 'success', 'locations': locations})
except Exception as e:
raise APIError(str(e))
def get_checkin_locations_for_user(user_id):
if request.method == "GET":
try:
data = request.get_json()
query = """
SELECT location.location_id, location.longitude, location.latitude, restaurant.restaurant_name, loc.timestamp
FROM location, restaurant,
(SELECT checkins.location_id, checkins.timestamp
FROM location, checkins, "user"
WHERE "user".user_id = {0} AND
"user".user_id = checkins.user_id
) AS loc
WHERE location.location_id = loc.location_id AND
location.restaurant_id = restaurant.restaurant_id
""".format(user_id)
result = conn.execute(query)
checkins = []
for row in result:
checkin = {}
for key in row.keys():
checkin[key] = row[key]
checkins.append(checkin)
return jsonify({'status': 'success', 'locations': checkins})
except Exception as e:
raise APIError(str(e))
else:
return jsonify({
'status':
'failed',
'message':
"Endpoint /checkin/location/<user_id> requires GET or POST request"
})
def get_id_locations(location_id):
try:
# query = """
# SELECT locs.location_id, locs.latitude, locs.longitude, locs.restaurant_id, locs.restaurant_name, rat.avg
# FROM
# (SELECT location.location_id, location.latitude, location.longitude, location.restaurant_id, restaurant.restaurant_name, AVG(rating.rating)
# FROM location, restaurant
# WHERE location.restaurant_id = restaurant.restaurant_id AND
# restaurant.restaurant_id = {}
# )AS locs,
# (SELECT restaurant.restaurant_id, AVG(rating.rating) AS avg
# FROM restaurant, rating
# WHERE rating.restaurant_id = restaurant.restaurant_id
# GROUP BY restaurant.restaurant_id
# )AS rat,
# WHERE
# locs.restaurant_id = rat.restaurant_id
# """.format(restaurant_id)
query = """
SELECT DISTINCT location.location_id, location.latitude, location.longitude FROM location WHERE location_id=""" + location_id
result = conn.execute(query)
locations = []
for row in result:
location = {}
for key in row.keys():
location[key] = row[key]
locations.append(location)
return jsonify({'status': 'success', 'locations': locations})
except Exception as e:
raise APIError(str(e))
def get_restaurant_by_id(restaurant_id):
if request.method == "GET":
try:
result = conn.execute(
"SELECT * FROM \"restaurant\" WHERE restaurant_id = {}".format(
restaurant_id))
row = result.first()
if row != None:
restaurant = {
"restaurant_id": row["restaurant_id"],
"restaurant_name": row["restaurant_name"]
}
return jsonify({'status': 'success', 'restaurant': restaurant})
return jsonify({
'status': 'failed',
'message': 'No restaurant record found!'
})
except Exception as e:
raise APIError(str(e))
elif request.method == "DELETE":
try:
result = conn.execute(
"DELETE FROM \"restaurant\" WHERE restaurant_id = {}".format(
restaurant_id))
result = conn.execute(
"SELECT * FROM \"restaurant\" WHERE restaurant_id = {}".format(
restaurant_id))
row = result.first()
if row != None:
return jsonify({
"status": "failed",
"message": "Failed to delete restaurant."
})
return jsonify({
"status": "success",
"message": "Successfully deleted restaurant!"
})
except Exception as e:
raise APIError(str(e))
else:
return jsonify({
'status':
'failed',
'message':
"Endpoint /restaurant/id requires GET or POST request"
})
def delete_photo(photo_id):
try:
deletion = conn.execute("DELETE FROM photo WHERE photo_id = {}".format(photo_id))
check = conn.execute("SELECT * FROM photo WHERE photo_id = {}".format(photo_id)).first()
if check is not None:
return jsonify({'status' : 'failed', 'message' : 'Failed to delete photo'})
return jsonify({'status' : 'success', 'message' : 'Successfully deleted photo!'})
except Exception as e:
raise APIError(str(e))
def delete_location(location_id):
try:
result = conn.execute(
"DELETE FROM location WHERE location_id = {}".format(location_id))
return jsonify({
'status': 'success',
'message': 'successfully deleted location'
})
except Exception as e:
raise APIError(str(e))
def update_rating(rating_id, rating):
try:
result = conn.execute(
"UPDATE rating SET rating = {0} WHERE rating_id = {1}".format(
rating, rating_id))
return jsonify({
'status': 'success',
'message': 'Successfully updated rating!'
})
except Exception as e:
raise APIError(str(e))
def create_cuisine():
data = request.get_json()
try:
result = conn.execute(
"INSERT INTO cuisine (cuisine_name) VALUES (\'{}\')".format(
data["cuisine_name"]))
return jsonify({
'status': 'success',
'message': 'Successfully created cuisine!'
})
except Exception as e:
raise APIError(str(e))
def get_all_cuisines():
try:
result = conn.execute("SELECT * FROM cuisine")
cuisines = []
for row in result:
cuisine = {}
cuisine["cuisine_id"] = row["cuisine_id"]
cuisine["cuisine_name"] = row["cuisine_name"]
cuisines.append(cuisine)
return jsonify({'status': 'success', 'cuisines': cuisines})
except Exception as e:
raise APIError(str(e))
def create_serves_entry():
data = request.get_json()
try:
result = conn.execute(
"INSERT INTO serves (cuisine_id, restaurant_id) VALUES ({0}, {1})".
format(data["cuisine_id"], data["restaurant_id"]))
return jsonify({
'status': 'success',
'message': 'Created serves entry!'
})
except Exception as e:
raise APIError(str(e))
def update_cuisine():
data = request.get_json()
try:
result = conn.execute(
text(
"UPDATE cuisine SET cuisine_name = \'{0}\' WHERE cuisine_id = {1}"
.format(data["cuisine_name"], data["cuisine_id"])))
return jsonify({
'status': 'success',
'message': 'Successfully updated cuisine!'
})
except Exception as e:
raise APIError(str(e))
def create_rating():
data = request.get_json()
try:
result = conn.execute(
"INSERT INTO rating (user_id, restaurant_id, rating, timestamp) VALUES ({0}, {1}, {2}, \'{3}\')"
.format(data["user_id"], data["restaurant_id"], data["rating"],
time.strftime('%Y-%m-%d %H:%M:%S')))
return jsonify({
'status': 'success',
'message': 'Successfully created rating!'
})
except Exception as e:
raise APIError(str(e))
def create_location():
data = request.get_json()
try:
result = conn.execute(
"INSERT INTO location (restaurant_id, address, zipcode, city, country, latitude, longitude) VALUES ({0}, \'{1}\', \'{2}\', \'{3}\', \'{4}\', {5}, {6})"
.format(data["restaurant_id"], data["address"], data["zipcode"],
data["city"], data["country"], data["latitude"],
data["longitude"]))
return jsonify({
'status': 'success',
'message': 'Successfully added location!'
})
except Exception as e:
raise APIError(str(e))
def get_map_info(user_id):
if request.method == "GET":
try:
get_map_info = """
SELECT location.location_id, location.latitude, location.longitude, ts.agg
FROM location,
(SELECT curr_locations.location_id, STRING_AGG(TO_CHAR(curr_checkins.timestamp,'YYYY-MM-DD HH24:MI:SS.0'), ',') as agg
FROM
(SELECT *
FROM location, "user"
WHERE "user".user_id = {0} AND
(abs(location.latitude) < 1.001 * abs("user".latitude) AND
abs(location.latitude) > 0.999 * abs("user".latitude)) AND
(abs(location.longitude) < 1.001 * abs("user".longitude) AND
abs(location.longitude) > 0.999 * abs("user".longitude))
) AS curr_locations,
(SELECT *
FROM checkins
WHERE checkins.timestamp > '{1}'
) AS curr_checkins
WHERE curr_checkins.location_id = curr_locations.location_id
GROUP BY curr_locations.location_id
) AS ts
WHERE location.location_id = ts.location_id
""".format(
user_id,
datetime.datetime.now() - datetime.timedelta(days=1))
result = conn.execute(get_map_info)
locations = []
for row in result:
loc = {}
for key in row.keys():
loc['location_id'] = row['location_id']
loc['latitude'] = row['latitude']
loc['longitude'] = row['longitude']
loc['timestamp'] = row['agg'].split(",")
locations.append(loc)
return jsonify({'locations': locations, 'status': 'success'})
except Exception as e:
raise APIError(str(e))
else:
return jsonify({
'status': 'failed',
'message': "Endpoint /hot requires GET request"
})
def get_id_locations(location_id):
try:
query = """
SELECT DISTINCT location.location_id, location.latitude, location.longitude FROM location WHERE location_id=""" + location_id
result = conn.execute(query)
locations = []
for row in result:
location = {}
for key in row.keys():
location[key] = row[key]
locations.append(location)
return jsonify({'status': 'success', 'locations': locations})
except Exception as e:
raise APIError(str(e))
def get_user_photos(user_id):
try:
result = conn.execute("SELECT * FROM photo WHERE user_id = {} ORDER BY timestamp DESC".format(user_id))
photos = []
for row in result:
photo = {}
photo["user_id"] = row["user_id"]
photo["restaurant_id"] = row["restaurant_id"]
photo["photo_path"] = row["photo_path"]
photo["timestamp"] = row["timestamp"]
photos.append(photo)
if len(photos) == 0:
return jsonify({'status' : 'failed', 'message' : 'Failed to find photo.'})
return jsonify({'status' : 'success', 'photos' : photos})
except Exception as e:
raise APIError(str(e))
def get_all_user_ratings(user_id):
try:
result = conn.execute(
"SELECT * FROM rating WHERE user_id = {}".format(user_id))
user_ratings = []
for row in result:
rating = {}
rating["rating_id"] = row["rating_id"]
rating["user_id"] = row["user_id"]
rating["restaurant_id"] = row["restaurant_id"]
rating["rating"] = row["rating"]
rating["timestamp"] = row["timestamp"]
user_ratings.append(rating)
return jsonify({'status': 'success', 'ratings': user_ratings})
except Exception as e:
raise APIError(str(e))
def get_hot_restaurants(user_id):
if request.method == "GET":
try:
get_hot_restaurants = """
SELECT close_restaurants.restaurant_id, close_restaurants.restaurant_name, AVG(rating.rating), COUNT(checkins)
FROM restaurant, rating, checkins, location,
(SELECT restaurant.restaurant_id, restaurant.restaurant_name
FROM location, restaurant, "user"
WHERE "user".user_id = {0} AND
restaurant.restaurant_id = location.restaurant_id AND
(abs(location.latitude) < 1.001 * abs("user".latitude) AND
abs(location.latitude) > 0.999 * abs("user".latitude)) AND
(abs(location.longitude) < 1.001 * abs("user".longitude) AND
abs(location.longitude) > 0.999 * abs("user".longitude))
) AS close_restaurants
WHERE close_restaurants.restaurant_id = rating.restaurant_id AND
close_restaurants.restaurant_id = location.restaurant_id AND
location.location_id = checkins.location_id AND
checkins.timestamp > '{1}'
GROUP BY close_restaurants.restaurant_id, close_restaurants.restaurant_name
ORDER BY AVG(rating.rating) DESC
""".format(
user_id,
datetime.datetime.now() - datetime.timedelta(days=14))
# get_hot_restaurants = """
# SELECT restaurant.restaurant_id, restaurant.restaurant_name
# FROM location, restaurant, "user"
# WHERE "user".user_id = {0}
# """.format(user_id)
result = conn.execute(get_hot_restaurants)
restaurants = []
for row in result:
restaurant = {}
for key in row.keys():
restaurant[key] = str(row[key])
restaurants.append(restaurant)
return jsonify({'restaurants': restaurants, 'status': 'success'})
except Exception as e:
raise APIError(str(e))
else:
return jsonify({
'status': 'failed',
'message': "Endpoint /hot requires GET request"
})
def get_all_restaurants():
if request.method == "GET":
try:
result = conn.execute("SELECT * FROM \"restaurant\"")
restaurants = []
for row in result:
restaurant = {}
for key in row.keys():
restaurant[key] = row[key]
restaurants.append(restaurant)
return jsonify({'restaurants': restaurants})
except Exception as e:
raise APIError(str(e))
else:
return jsonify({
'status': 'failed',
'message': "Endpoint /restaurants requires GET request"
})
def delete_serves_entry(serves_id):
try:
result = conn.execute(
"DELETE FROM serves WHERE serves_id = {}".format(serves_id))
search = conn.execute(
"SELECT * FROM serves WHERE serves_id = {}".format(
serves_id)).first()
if search is not None:
return jsonify({
'status': 'failed',
'message': 'Failed to delete serves entry'
})
return jsonify({
'status': 'success',
'message': 'Successfully deleted serves entry.'
})
except Exception as e:
raise APIError(str(e))
def get_all_checkins():
if request.method == "GET":
try:
result = conn.execute("SELECT * FROM \"checkins\"")
checkins = []
for row in result:
checkin = {}
for key in row.keys():
checkin[key] = row[key]
checkins.append(checkin)
return jsonify({'checkins': checkins})
except Exception as e:
raise APIError(str(e))
else:
return jsonify({
'status': 'failed',
'message': "Endpoint /checkin requires GET request"
})
def create_user():
if request.method == "POST":
try:
data = request.get_json()
result = conn.execute(
"INSERT INTO \"user\" (email, username, password, name, latitude, longitude) VALUES (\'{0}\', \'{1}\', \'{2}\', \'{3}\', {4}, {5})"
.format(data["email"], data["username"], data["password"],
data["name"], data["latitude"], data["longitude"]))
return jsonify({
'status': 'success',
'message': 'Created new user!'
})
except Exception as e:
raise APIError(str(e))
else:
return jsonify({
'status': 'failed',
'message': "Endpoint /user requires GET or POST request"
})
def get_all_users():
# username = get_jwt_identity()
if request.method == "GET":
try:
result = conn.execute("SELECT * FROM \"user\"")
users = []
for row in result:
user = {}
for key in row.keys():
user[key] = row[key]
users.append(user)
return jsonify({'users': users})
except Exception as e:
raise APIError(str(e))
else:
return jsonify({
'status': 'failed',
'message': "Endpoint /users requires GET request"
})
def get_all_restaurants_by_cuisine(cuisine_id):
try:
result = conn.execute(
"SELECT restaurant.restaurant_id, restaurant.restaurant_name FROM serves, restaurant WHERE cuisine_id = {} and serves.restaurant_id = restaurant.restaurant_id"
.format(cuisine_id))
restaurants = []
for row in result:
restaurant = {
'restaurant_id': row["restaurant_id"],
'restaurant_name': row["restaurant_name"]
}
restaurants.append(restaurant)
return jsonify({
'status': 'success',
'cuisine_id': cuisine_id,
'restaurants': restaurants
})
except Exception as e:
raise APIError(str(e))
def get_all_zipcode_locations(zipcode):
try:
result = conn.execute(
"SELECT * FROM location WHERE zipcode = \'{}\'".format(zipcode))
locations = []
for row in result:
location = {}
location["location_id"] = row["location_id"]
location["restaurant_id"] = row["restaurant_id"]
location["address"] = row["address"]
location["zipcode"] = row["zipcode"]
location["city"] = row["city"]
location["country"] = row["country"]
location["latitude"] = row["latitude"]
location["longitude"] = row["longitude"]
locations.append(location)
return jsonify({'status': 'success', 'locations': locations})
except Exception as e:
raise APIError(str(e))