def enter_restaurant(customer_id, table_number, num_people):
# Creates an entry in the SESSION table. Num people is necessary for Crooked Cooks specifically.
# If already in restaurant, return ERROR.
print("Testing Enter Restaurant")
with ConnectionFromPool() as cursor:
cursor.execute("SELECT * FROM session "
"WHERE customer_id = {}".format(customer_id))
results = cursor.fetchall()
# Checking if already in database; if already in database, return negative
if len(results) > 0:
return -1
with ConnectionFromPool() as cursor:
if customer_id > 1000000000:
transaction_id = int(datetime.now().microsecond * customer_id /
100000000)
else:
transaction_id = int(datetime.now().microsecond * customer_id /
1000000)
print("Creating a new session, with transaction id", transaction_id)
print(transaction_id)
print(table_number)
print(customer_id)
print(num_people)
cursor.execute(
"INSERT INTO session(transaction_id,table_number,customer_id,num_people,start_time) "
"VALUES({},{},{},{},NOW())".format(transaction_id, table_number,
customer_id, num_people))
print("Creation success")
return 1
def exit_restaurant(customer_id):
print("Customer {} exiting Restaurant".format(customer_id))
with ConnectionFromPool() as cursor:
transaction_id, table_number, _, num_people, start_time = get_stats(
customer_id)
total_price, _, orders, _ = query_price(customer_id)
order_string = "'"
order_string += '{"' + '","'.join(item for item in orders) + '"}'
order_string += "'"
print(
"INSERT INTO history(transaction_id, customer_id, food_orders, start_time, end_time, total_price)"
"VALUES ({},{},{},to_timestamp('{}','YYYY-MM-DD HH24:MI:SS'),NOW(),{})"
.format(transaction_id, customer_id, order_string, start_time,
total_price))
cursor.execute(
"INSERT INTO history(transaction_id, customer_id, food_orders, start_time, end_time, total_price)"
"VALUES ({},{},{},to_timestamp('{}','YYYY-MM-DD HH24:MI:SS'),NOW(),{})"
.format(transaction_id, customer_id, order_string, start_time,
total_price))
print("Customer {} inserted into history".format(customer_id))
cursor.execute("DELETE FROM session WHERE transaction_id = {}".format(
transaction_id))
cursor.execute(
"DELETE FROM purchases WHERE transaction_id = {}".format(
transaction_id))
print("Customer ID {} deleted from session & purchases".format(
customer_id))
return True
def query_price(customer_id):
# Returns total_price, timeSpent, orders, customPrice
with ConnectionFromPool() as cursor:
transaction_id, _, _, _, _ = get_stats(customer_id)
# Can actually have this be a constantly existing VIEW in the database. I'll leave it as is for
# the moment though - not sure how costly sql accesses are
cursor.execute("DROP VIEW IF EXISTS total_transaction_cost")
cursor.execute(
"CREATE VIEW total_transaction_cost AS "
"SELECT session.transaction_id,SUM(price) FROM session "
"INNER JOIN purchases ON purchases.transaction_id = session.transaction_id "
"INNER JOIN menu ON purchases.food_id = menu.food_id "
"GROUP BY session.transaction_id")
cursor.execute("DROP VIEW IF EXISTS total_comment_cost")
cursor.execute(
"CREATE VIEW total_comment_cost AS "
"SELECT session.transaction_id,SUM(additional_price) FROM session "
"INNER JOIN purchases ON purchases.transaction_id = session.transaction_id "
"INNER JOIN menu ON purchases.food_id = menu.food_id "
"GROUP BY session.transaction_id")
cursor.execute(
"SELECT sum FROM total_transaction_cost WHERE transaction_id={}".
format(transaction_id))
# print(cursor.fetchall())
cursor_info = cursor.fetchone()
if cursor_info is not None:
item_sum = cursor_info[0]
cursor.execute(
"SELECT sum FROM total_comment_cost WHERE transaction_id={}".
format(transaction_id))
comments_sum = cursor.fetchone()[0]
if comments_sum is None:
comments_sum = 0
else:
item_sum = 0
comments_sum = 0
cursor.execute(
"SELECT (DATE_PART('day', NOW() - session.start_time) * 24 + "
"DATE_PART('hour', NOW() - session.start_time)) * 60 + "
"DATE_PART('minute', NOW() - session.start_time) as time_difference_minutes FROM session "
"WHERE transaction_id={}".format(transaction_id))
# For crooked cooks, it's $2 per hour
time_spent = int(cursor.fetchone()[0] / 60 + 1)
time_price = time_spent * 2
total_price = item_sum + time_price + comments_sum
cursor.execute(
"SELECT session.transaction_id, menu.name FROM session "
"INNER JOIN purchases ON purchases.transaction_id = session.transaction_id "
"INNER JOIN menu ON menu.food_id = purchases.food_id "
"WHERE session.transaction_id = {} "
"ORDER BY transaction_id".format(transaction_id))
order_history = cursor.fetchall()
ordered_items = [orders[1] for orders in order_history]
# Returns total_price, time_spent, orders, customPrice
print("Customer {} queried. Parameters returned: {},{},{},{}".format(
customer_id, total_price, time_spent, ordered_items, comments_sum))
return total_price, time_spent, ordered_items, comments_sum
def set_delivered(customer_id, food_id):
transaction_id, _, _, _, _ = get_stats(customer_id)
sql_command = "UPDATE purchases SET delivered = true WHERE " \
"CTID IN (SELECT CTID FROM purchases WHERE transaction_id = {} " \
"AND delivered = false AND food_id = {} LIMIT 1)".format(transaction_id, food_id)
with ConnectionFromPool() as cursor:
cursor.execute(sql_command)
return True
def get_session():
sql_command = "SELECT * FROM session"
with ConnectionFromPool() as cursor:
cursor.execute(
"SELECT column_name,data_type FROM information_schema.columns WHERE table_name = 'session'"
)
session_headers = cursor.fetchall()
cursor.execute(sql_command)
session_array = cursor.fetchall()
final_string = '{{"session":[{}]}}'.format(
jsonify_reply(session_headers, session_array))
return final_string
def make_order(customer_id, order_array, comments):
# Creates an entry in the PURCHASES table.
# Each entry requires a customer id (int), order_array (int array), and comments (text array)
# Input sanitization occurs here. "Comments" is the only category where the user has access to the database.
# Formatting as %s, (_,) allows psycopg2 to sanitize the input such that only one command passes to the execute()
# So no "Hello World!'; DROP TABLE menu;" shenanigans can occur
transaction_id, _, _, _, _ = get_stats(customer_id)
with ConnectionFromPool() as cursor:
for i in range(len(order_array)):
cursor.execute(
"INSERT INTO purchases(transaction_id,food_id,delivered,comments,additional_price) "
"VALUES({},{},false,%s,0)".format(transaction_id,
order_array[i]),
(comments[i], ))
def edit_purchase(customer_id, food_id, comment, additional_price):
transaction_id, _, _, _, _ = get_stats(customer_id)
sql_command = "WITH cte AS ( " \
"SELECT default_id " \
"FROM purchases " \
"WHERE transaction_id = {} AND food_id = {} AND comments = '{}' " \
"LIMIT 1) " \
"UPDATE purchases s " \
"SET additional_price = {} " \
"FROM cte " \
"WHERE s.default_id = cte.default_id".format(transaction_id, food_id, comment, additional_price)
print('adding additional price for custom order')
with ConnectionFromPool() as cursor:
cursor.execute(sql_command)
print("Order Price changed")
def order_satisfied(customer_id, food_id, comment):
transaction_id, _, _, _, _ = get_stats(customer_id)
sql_command = "WITH cte AS ( " \
"SELECT default_id " \
"FROM purchases " \
"WHERE transaction_id = {} AND food_id = {} AND comments = '{}' " \
"LIMIT 1) " \
"UPDATE purchases s " \
"SET delivered=true " \
"FROM cte " \
"WHERE s.default_id = cte.default_id".format(transaction_id, food_id, comment)
with ConnectionFromPool() as cursor:
for i in range(len(items)):
cursor.execute(sql_command)
print("One Order satisfied")
def get_stats(customer_id):
with ConnectionFromPool() as cursor:
cursor.execute(
"SELECT * FROM session WHERE customer_id = {}".format(customer_id))
item = cursor.fetchall()[0]
transaction_id = item[1]
table_number = item[2]
customer_id = item[3]
num_people = item[4]
start_time = item[5]
print(
"Customer {} stats queried. Parameters returned: tid {}, tnum {}, nppl {}, stime {}"
.format(customer_id, transaction_id, table_number, num_people,
start_time))
return transaction_id, table_number, customer_id, num_people, start_time
def getMenu(table_number):
restaurant_name = "Crooked Cooks"
restaurant_image = "https://i.imgur.com/VXypCYQ.png"
with ConnectionFromPool() as cursor:
cursor.execute(
"SELECT column_name,data_type FROM information_schema.columns WHERE table_name = 'menu'"
)
menu_headers = cursor.fetchall()
cursor.execute("SELECT * FROM menu")
non_parsed_array = cursor.fetchall()
final_string = jsonify_reply(menu_headers, non_parsed_array)
full_json = '{{"name":"{}","imagehyperlink":"{}","menu":[{}]}}'.format(
restaurant_name, restaurant_image, final_string)
return full_json
def replace_menu(menu_array):
with ConnectionFromPool() as cursor:
print("Replacing menu")
cursor.execute('DROP TABLE IF EXISTS menu CASCADE')
cursor.execute('DROP TYPE IF EXISTS category CASCADE')
cursor.execute("CREATE TYPE category AS ENUM('Main','Side','Veg');")
cursor.execute(
'CREATE TABLE menu (itemid SERIAL PRIMARY KEY, food_category category, food_id integer, name text, '
'description text, price numeric(4,2),currency text,image_link text,is_available boolean)'
)
for element in menu_array:
print("Hello")
element_insertion_command = "INSERT INTO menu(food_category, food_id, name, description, price, currency, "\
"image_link, is_available) VALUES('{}',{},'{}','{}',{},'{}','{}',{})"\
.format(element["food_category"], int(element["food_id"]), element["name"],
element["description"], float(element["price"]), element["currency"],
element["image_link"], element["is_available"])
print("Executing " + element_insertion_command)
cursor.execute(element_insertion_command)
return True
def get_time_and_price(customer_id, table_number):
print("Getting time and price")
transaction_id, _, _, _, _ = get_stats(customer_id)
with ConnectionFromPool() as cursor:
cursor.execute(
"SELECT DATE_PART('hour',NOW() - start_time) FROM session WHERE transaction_id = {}"
.format(transaction_id))
hours = int(cursor.fetchone()[0])
cursor.execute(
"SELECT DATE_PART('minute',NOW() - start_time) FROM session WHERE transaction_id = {}"
.format(transaction_id))
minutes = int(cursor.fetchone()[0])
if 0 < table_number < 50:
return "{" + '"time_price":{},"hours":{},"minutes":{}'.format(
2 * (hours + 1), hours, minutes) + "}"
elif 50 <= table_number < 100:
return "{" + '"time_price":{},"hours":{},"minutes":{}'.format(
0 * (hours + 1), hours, minutes) + "}"
else:
return False
def getOrders(customer_id=None):
sql_command = "SELECT session.table_number,session.start_time," \
"menu.price, purchases.food_id,purchases.delivered, purchases.comments, purchases.additional_price " \
"FROM session INNER JOIN purchases ON purchases.transaction_id = session.transaction_id" \
" INNER JOIN menu ON purchases.food_id = menu.food_id "
if customer_id is not None:
sql_command += "WHERE session.customer_id = {}".format(customer_id)
with ConnectionFromPool() as cursor:
# cursor.execute("SELECT column_name,data_type FROM information_schema.columns WHERE table_name = 'session'");
menu_headers = [("table_number", "integer"),
("start_time", "timestamp without time zone"),
("price", "numeric(4, 2)"), ("food_id", "integer"),
("delivered", "boolean"), ("comments", "text"),
("additional_price", "numeric(4,2)")]
cursor.execute(sql_command)
non_parsed_array = cursor.fetchall()
final_string = '{{"orders":[{}]}}'.format(
jsonify_reply(menu_headers, non_parsed_array))
return final_string
def createEmptyPurchases():
with ConnectionFromPool() as cursor:
cursor.execute('DROP TABLE IF EXISTS purchases CASCADE')
cursor.execute('CREATE TABLE purchases (default_id SERIAL PRIMARY KEY, transaction_id integer NOT NULL, food_id integer NOT NULL, '
'delivered boolean, comments text, additional_price numeric(4,2))')
def createEmptySession():
with ConnectionFromPool() as cursor:
cursor.execute('DROP TABLE IF EXISTS session CASCADE')
cursor.execute('CREATE TABLE session (default_id SERIAL PRIMARY KEY, transaction_id integer, table_number integer, customer_id bigint, num_people integer, start_time timestamp)')
print("Session reset!")
def createHistory():
with ConnectionFromPool() as cursor:
cursor.execute('DROP TABLE IF EXISTS history CASCADE')
cursor.execute('CREATE TABLE history (default_id SERIAL PRIMARY KEY, transaction_id integer NOT NULL, '
'customer_id bigint, food_orders text[], start_time timestamp, end_time timestamp,'
' total_price float)')
def set_availability(food_id, boolean):
sql_command = "UPDATE menu SET is_available = {} WHERE food_id = {}".format(
boolean, food_id)
with ConnectionFromPool() as cursor:
cursor.execute(sql_command)
return True
import psycopg2
from connection_pool import ConnectionFromPool
#One-off; use this template to initialize new menus
# connection = psycopg2.connect(database='Crooked Cooks', user='******', password='******', host='localhost')
with ConnectionFromPool() as cursor:
menuList = []
# Mains
menuList.append(('Main', 100, 'Cheesy Chicken Burger',
'Deep Fried Chicken Leg Patty with Nacho Cheese. ', 4.50,
"$S*", 'https://i.imgur.com/CGJm0D3.jpg', True))
menuList.append((
'Main', 101, 'Tonkatsu Pork Burger',
'Crispy Pork Loin, Sunny Side Egg, Garlic Mayo', 4.50, "$S*",
'https://i.pinimg.com/originals/76/33/2e/76332e9e816d02e49b26034a8e918d4b.jpg',
True))
menuList.append(('Main', 102, 'Sausage In A Cup',
'Veal Bratwurst Sausage, Mash & Home Made Gravy', 3.50,
"$S*", 'https://i.imgur.com/Uxlbdnl.png', True))
menuList.append(
('Main', 103, 'Mac and Cheese', 'Macaroni, Bacon, and Poached Egg',
4.00, "$S*", 'https://i.imgur.com/g2eV1a6.jpg', True))
menuList.append(('Main', 104, 'Toast Breakfast',
'Toast, Veal Sausage, Bacon, Mushroom, Scrambled Eggs',
4.90, "$S*", 'https://i.imgur.com/Q2xwatl.jpg', True))
menuList.append((
'Main', 105, 'Chicken Cutlet Aglio Olio',
'Deep Fried Chicken Leg Patty, Linguine Pasta, Chilli, and Garlic',
4.90, "$S*",
'http://2.bp.blogspot.com/--X1dUvxFmOc/Tv3P7bqfvVI/AAAAAAAAFOs/a5JDuX-lxSw/s1600/DSC_7819.jpg',