def signup():
dataJSON = request.get_json()
username = str(dataJSON['username'])
password = str(dataJSON['password'])
name = str(dataJSON['name'])
conn = db.connect()
query1 = 'INSERT INTO Users (name, username, password) VALUES ("{}", "{}", "{}")'.format(
name, username, password)
conn.execute(query1)
conn.close()
conn = db.connect()
query = "SELECT COUNT(*) AS isSuccessful, id AS userId FROM Users WHERE username = '******' AND password = '******' GROUP BY id;".format(
username, password)
query_results = conn.execute(query)
conn.close()
results = []
for row in query_results:
results.append(dict(row))
result_dict = {'results': results}
if (len(results) == 0):
return dict({"isSuccessful": 0, "userId": -1})
return results[0]
def get_table_data(table):
""" gets all of the data in 'table' """
conn = db.connect()
query = 'SELECT * FROM ' + str(table) + ' LIMIT 1000;'
query_results = conn.execute(query)
query_results = [entry for entry in query_results]
conn.close()
return query_results
def update(table:str, _id:int, text:str, userId:int, rating):
conn = db.connect()
if table == "Reviews":
query1 = 'UPDATE Reviews SET review = "{}", rating = "{}" WHERE id = {} and userID = {}'.format(text, rating, _id, userId)
else:
query1 = 'UPDATE Answers SET answer = "{}" WHERE id = {} and userId = {}'.format(text, _id, userId)
conn.execute(query1)
conn.close()
def getLocationStatistics():
conn = db.connect()
query = "SELECT * FROM LocationStatistics;"
query_results = conn.execute(query)
results = [dict(row) for row in query_results]
result_dict = {'results': results}
return jsonify(result_dict)
def UserVisited_Range(num:int, locationID:int):
date = datetime.now() - timedelta(int(num))
pastdate = date.strftime("%Y-%m-%d %H:%M:%S")
conn = db.connect()
query = "SELECT sum(hasCOVID = 1) as covid, sum(hasCOVID = 0) as notCovid FROM UserVisited WHERE time > '" + pastdate + "' AND locationID = " + locationID
query_results = conn.execute(query)
conn.close()
return query_results
def restaurantHoursVisited():
conn = db.connect()
query = "SELECT hour(time) as hourVisited, sum(hasCovid=1) as CovidCount, sum(hasCovid=0) as nonCovidCount FROM UserVisited WHERE locationID in (SELECT locationID FROM LocationOfType WHERE categoryID = 1) GROUP BY hour(time) ORDER BY hourVisited;"
query_results = conn.execute(query)
conn.close()
results = [dict(row) for row in query_results]
result_dict = {'results': results}
return jsonify(result_dict)
def restaurantRatings():
conn = db.connect()
query = "SELECT DISTINCT l.name, AVG(r.rating) as avg_rating FROM Reviews r JOIN Locations l ON r.locationID = l.id WHERE l.name = ANY (SELECT l.name FROM Locations l JOIN LocationOfType t on l.id = t.locationID JOIN Categories c ON t.categoryID = c.id WHERE c.name = 'Restaurant') GROUP BY l.name ORDER BY avg_rating DESC;"
query_results = conn.execute(query)
conn.close()
results = [dict(row) for row in query_results]
result_dict = {'results': results}
return jsonify(result_dict)
def locationsPositive():
conn = db.connect()
query = "SELECT DISTINCT L.name, count(distinct U.id) as NumberVisited FROM Users U JOIN UserVisited UV ON U.id = UV.userID JOIN Locations L ON UV.locationID = L.id WHERE U.hasCovid = 1 GROUP BY L.name;"
query_results = conn.execute(query)
conn.close()
results = [dict(row) for row in query_results]
result_dict = {'results': results}
return jsonify(result_dict)
def locationCategory():
conn = db.connect()
query = "SELECT Categories.name AS category_name, Locations.name AS location_name FROM LocationOfType JOIN Categories ON LocationOfType.categoryID = Categories.id JOIN Locations ON LocationOfType.locationID = Locations.id GROUP BY Categories.name, Locations.name ORDER BY Categories.name;"
query_results = conn.execute(query)
conn.close()
results = [dict(row) for row in query_results]
result_dict = {'results': results}
return jsonify(result_dict)
def search(table, column, keyword):
""" Searches in 'table' where 'column' is like 'keyword' """
conn = db.connect()
query = 'SELECT * FROM ' + str(table) + ' WHERE ' + str(column) + " LIKE '%%" + str(keyword) + "%%' LIMIT 1000;"
query_results = conn.execute(query)
conn.close()
return query_results
def userNotInDatabase(username, password):
conn = db.connect()
query = "SELECT COUNT(*) AS C FROM Users WHERE username = '******' AND password = '******';".format(username, password)
query_results = conn.execute(query)
conn.close()
results = [dict(row) for row in query_results]
result_dict = {0: results}
# print(type(results[0]['C']))
return not bool(results[0]['C'])
def checkUser(id:int, username:str, password:str):
conn = db.connect()
query = "SELECT COUNT(*) AS C FROM Users WHERE username = '******' AND password = '******' AND id = {};".format(username, password, id)
query_results = conn.execute(query)
conn.close()
results = [dict(row) for row in query_results]
result_dict = {0: results}
# print(type(results[0]['C']))
return not bool(results[0]['C'])
def getTableNames():
conn = db.connect()
query = "SELECT Table_name as TableName from information_schema.tables where table_schema = 'covid_tracker';"
query_results = conn.execute(query)
conn.close()
results = [dict(row) for row in query_results]
result_dict = {'results': results}
return jsonify(result_dict)
def delete(table, id: int, userId:int) -> None:
""" removes from 'table' the entry that matches with 'id' """
conn = db.connect()
if table == "Reviews":
query = 'DELETE FROM ' + str(table) + ' WHERE id={} AND userID={};'.format(int(id), int(userId))
else:
query = 'DELETE FROM ' + str(table) + ' WHERE id={} AND userId={};'.format(int(id), int(userId))
conn.execute(query)
conn.close()
def insert_UserVisited(_id:int, userID:int, locationID:int, time:str, hasCOVID:int):
conn = db.connect()
# weird stored procedure insert fix but it works I guess
query1 = ''
if hasCOVID == 0:
query1 = 'INSERT INTO UserVisited (id, userID, locationID, time, hasCOVID) VALUES ("{}", "{}", "{}", "{}", "{}")'.format(_id, userID, locationID, time, hasCOVID)
else:
query1 = 'CALL updateCovidStatus("{}", "{}", "{}", "{}", "{}")'.format(_id, userID, locationID, time, hasCOVID)
conn.execute(query1)
conn.close()
def getColumnsFromTable():
arg1 = str(request.args.get('table'))
conn = db.connect()
query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'covid_tracker' AND TABLE_NAME = '{}';".format(
arg1)
query_results = conn.execute(query)
conn.close()
results = [dict(row) for row in query_results]
result_dict = {'results': results}
return jsonify(result_dict)
def answer():
dataJSON = request.get_json()
username = str(dataJSON['username'])
password = str(dataJSON['password'])
if (dbase.userNotInDatabase(username, password)):
return dict({"isSuccessful": 0})
userId = str(dataJSON['userId'])
questionId = str(dataJSON['questionId'])
answer = str(dataJSON['answer'])
conn = db.connect()
query1 = 'INSERT INTO Answers (answer, questionID, userID) VALUES ("{}", "{}", "{}")'.format(
answer, questionId, userId)
conn.execute(query1)
conn.close()
return dict({"isSuccessful": 1})
def review():
dataJSON = request.get_json()
username = str(dataJSON['username'])
password = str(dataJSON['password'])
if (dbase.userNotInDatabase(username, password)):
return dict({"isSuccessful": 0})
userId = str(dataJSON['userId'])
locationId = str(dataJSON['locationId'])
review = str(dataJSON['review'])
rating = str(dataJSON['rating'])
conn = db.connect()
query1 = 'INSERT INTO Reviews (rating, userID, locationID, review) VALUES ("{}", "{}", "{}", "{}")'.format(
rating, userId, locationId, review)
conn.execute(query1)
conn.close()
return dict({"isSuccessful": 1})
def addQuestion():
dataJSON = request.get_json()
username = str(dataJSON['username'])
password = str(dataJSON['password'])
if (dbase.userNotInDatabase(username, password)):
return dict({"isSuccessful": 0})
locationId = str(dataJSON['locationId'])
question = str(dataJSON['question'])
userId = str(dataJSON['userId'])
conn = db.connect()
query1 = 'INSERT INTO Questions (question, userId, locationId) VALUES ("{}", "{}", "{}")'.format(
question, userId, locationId)
conn.execute(query1)
conn.close()
return dict({"isSuccessful": 1})
def login():
dataJSON = request.get_json()
arg1 = str(dataJSON['username'])
arg2 = str(dataJSON['password'])
conn = db.connect()
query = "SELECT COUNT(*) AS isSuccessful, id AS userId FROM Users WHERE username = '******' AND password = '******' GROUP BY id".format(
arg1, arg2)
query_results = conn.execute(query)
conn.close()
results = []
for row in query_results:
results.append(dict(row))
# results = [dict(row) for row in query_results]
if (len(results) == 0):
return dict({"isSuccessful": 0, "userId": -1})
return results[0]
def tableColumns(table):
"""Reads all tasks listed in the todo table
Returns:
A list of dictionaries
"""
conn = db.connect()
query_results = conn.execute("SHOW COLUMNS FROM Locations;")
conn.close()
#todo_list = []
# print(query_results)
# for result in query_results:
# item = {
# "id": result[0],
# "task": result[1],
# "status": result[2]
# }
# todo_list.append(item)
return query_results
def visit():
dataJSON = request.get_json()
username = str(dataJSON['username'])
password = str(dataJSON['password'])
if (dbase.userNotInDatabase(username, password)):
return dict({"isSuccessful": 0})
userId = str(dataJSON['userId'])
locationId = str(dataJSON['locationId'])
date = str(dataJSON['date'])
hasCovid = str(dataJSON['hasCovid'])
if hasCovid == "Yes":
hasCovid = 1
else:
hasCovid = 0
conn = db.connect()
query1 = 'INSERT INTO UserVisited (userID, locationID, time, hasCOVID) VALUES ("{}", "{}", "{}", "{}")'.format(
userId, locationId, date, hasCovid)
conn.execute(query1)
conn.close()
return dict({"isSuccessful": 1})
def insert_Questions(_id:int, question:str, userId:int, locationId:int):
conn = db.connect()
query1 = 'INSERT INTO Questions (_id, question, userId, locationId) VALUES ("{}", "{}", "{}", "{}")'.format(_id, question, userId, locationId)
conn.execute(query1)
conn.close()
def insert_LocationOfType(locationID:int, categoryID:int):
conn = db.connect()
query1 = 'INSERT INTO LocationOfType (locationID, categoryID) VALUES ("{}", "{}")'.format(locationID, categoryID)
conn.execute(query1)
conn.close()
def insert_Answers(_id:int, answer:str, questionID:int, userID:int):
conn = db.connect()
query1 = 'INSERT INTO Answers (id, answer, questionID, userID) VALUES ("{}", "{}", "{}", "{}")'.format(_id, answer, questionID, userID)
conn.execute(query1)
conn.close()
def insert_Reviews(_id:int, rating:int, userID:int, locationID:int, review:str):
conn = db.connect()
query1 = 'INSERT INTO Reviews (id, rating, userID, locationID, review) VALUES ("{}", "{}", "{}", "{}", "{}")'.format(_id, rating, userID, locationID, review)
conn.execute(query1)
conn.close()
def insert_User(_id:int, name:str, hasCovid:int, username:str, password:str):
conn = db.connect()
query1 = 'INSERT INTO Users (id, name, hasCovid, username, password) VALUES ("{}", "{}", "{}", "{}", "{}")'.format(_id, name, hasCovid, username, password)
conn.execute(query1)
conn.close()
def insert_Categories(_id:int, name:str):
conn = db.connect()
query1 = 'INSERT INTO Categories (id, name) VALUES ("{}", "{}")'.format(_id, name)
conn.execute(query1)
conn.close()
def insert_Locations(_id:int, name:str, longitude:int, latitude:int):
conn = db.connect()
query1 = 'INSERT INTO Locations (id, name, longitude, latitude) VALUES ("{}", "{}", "{}", "{}")'.format(_id, name, longitude, latitude)
conn.execute(query1)
conn.close()
def get_LocationId(name:int):
conn = db.connect()
query = "SELECT id FROM Locations WHERE name = '" + name + "'"
query_results = conn.execute(query)
conn.close()
return query_results
