def index():
if request.method == 'GET':
conn, cur = Model.make_cursor()
rows = cur.execute('''
SELECT name, price, SUM(quantity)
FROM cookies, stock
WHERE name=cookie and quantity > 0
GROUP BY cookie
''')
cookies = []
for r in rows:
r = {
"name": r[0],
"price": r[1],
"count": r[2]
}
cookies.append(r)
return render_template('cookies/index.html', cookies=cookies)
if request.method == 'POST':
cookie = request.get_json()
name = cookie["name"]
price = cookie["price"]
conn, cur = Model.make_cursor()
cur.execute('''
INSERT INTO cookies(name, price)
VALUES ("{}", {})
'''.format(name, price))
conn.commit()
return "OK"
def show(buyer_id):
#find buyer with buyer_id
if request.method == 'GET':
buyer = Model.execute('SELECT first, last, id FROM buyers WHERE id={}'.format(buyer_id)).fetchone()
print("GET BUYER:", buyer)
buyer = {
"name": "{} {}".format(buyer[0], buyer[1]),
"id": buyer[2]
}
orders = []
for order in Model.execute('SELECT id, buyer, description, total FROM buyer_orders WHERE buyer={}'.format(buyer_id)):
print("ORDER:", order)
oid = order[0]
order = {
"id": order[0],
"description": order[2],
"total": order[3]
}
purchases = []
for p in Model.execute('SELECT cookie, warehouse, buyer_order, amount FROM purchases WHERE buyer_order={}'.format(oid)):
p = {
"cookie": p[0],
"warehouse": p[1],
"amount": p[3]
}
purchases.append(p)
order["purchases"] = purchases
orders.append(order)
buyer["orders"] = orders
return jsonify(**buyer)
if request.method == 'PUT':
update = request.get_json()
first, last = update["name"].split(" ")
#update buyer with this information
conn, cur = Model.make_cursor()
cur.execute('''
UPDATE buyers
SET first="{}", last="{}"
WHERE id={}
'''.format(first, last, buyer_id))
conn.commit()
return "OK"
if request.method == 'DELETE':
#delete buyer with id = buyer_id
conn, cur = Model.make_cursor()
cur.execute('DELETE FROM buyers WHERE id={}'.format(buyer_id))
conn.commit()
return "OK"
def index():
conn, cur = Model.make_cursor()
if request.method == 'GET':
wares = []
rows = cur.execute('''
SELECT name, address
FROM warehouses
''')
for r in rows:
w = {
"name": r[0],
"address": r[1]
}
wares.append(w)
return render_template('warehouses/index.html', warehouses=wares)
if request.method == 'POST':
ware = request.get_json()
name = ware["name"]
address = ware["address"]
cur.execute('''
INSERT INTO warehouses(name, address)
VALUES ("{}", "{}")
'''.format(name, address))
conn.commit()
return jsonify(**ware)
def transaction(name):
conn, cur = Model.make_cursor()
transaction = request.get_json()
cookie = transaction["cookie"]
warehouse = transaction["warehouse"]
price = transaction["price"]
amount = transaction["amount"]
cur.execute(
"""
INSERT INTO transactions(distributor, cookie, warehouse, price, amount)
VALUES ("{}", "{}", "{}",{}, {})
""".format(
name, cookie, warehouse, price, amount
)
)
cur.execute(
"""
UPDATE stock
SET quantity = quantity + {}
WHERE warehouse = "{}" and cookie = "{}"
""".format(
amount, warehouse, cookie
)
)
conn.commit()
return "OK"
def index():
conn, cur = Model.make_cursor()
if request.method == "GET":
dists = []
rows = cur.execute(
"""
SELECT name, address
FROM distributors
"""
)
for r in rows:
d = {"name": r[0], "address": r[1]}
dists.append(d)
return render_template("distributors/index.html", distributors=dists)
if request.method == "POST":
dist = request.get_json()
name = dist["name"]
address = dist["address"]
cur.execute(
"""
INSERT INTO distributors(name, address)
VALUES ("{}", "{}")
""".format(
name, address
)
)
conn.commit()
return jsonify(**dist)
def warehouse(name):
conn, cur = Model.make_cursor()
if request.method == 'GET':
warehouses = []
ware = cur.execute('''
SELECT name, address
FROM warehouses
WHERE name = "{}"
'''.format(name)).fetchone()
print("GET", ware)
ware = {
"name": ware[0],
"address" : ware[1]
}
cookies = []
rows = cur.execute('''
SELECT name, SUM(quantity)
FROM cookies, stock
WHERE name = cookie and warehouse = "{}" and quantity > 0
GROUP BY cookie
'''.format(name))
for r in rows:
print("Cookie", r)
cook = {
"name": r[0],
"count": r[1]
}
cookies.append(cook)
ware["cookies"] = cookies
return jsonify(**ware)
if request.method == 'PUT':
update = request.get_json()
new_name = update["name"]
address = update["address"]
cur.execute('''
UPDATE warehouses
SET name = "{}", address = "{}"
WHERE name = "{}"
'''.format(new_name, address, name))
cur.execute('''
UPDATE stock
SET warehouse = "{}"
WHERE warehouse = "{}"
'''.format(new_name, name))
conn.commit()
return jsonify(**update)
if request.method == 'DELETE':
cur.execute('DELETE FROM warehouses WHERE name = "{}"'.format(name))
cur.execute('DELETE FROM stock WHERE warehouse = "{}"'.format(name))
conn.commit()
return "OK"
def signin():
conn, cur = Model.make_cursor()
data = request.get_json()
response = {}
first = data["name"]
password = data["password"]
user = cur.execute('SELECT password FROM buyers WHERE first = "{}"'.format(first)).fetchone()
response["ok"] = password == user[0]
return jsonify(**response)
def dist(name):
conn, cur = Model.make_cursor()
if request.method == "GET":
dist = cur.execute('SELECT name, address FROM distributors WHERE name="{}"'.format(name)).fetchone()
dist = {"name": dist[0], "address": dist[1]}
transactions = []
rows = cur.execute(
"""
SELECT id, price, cookie, amount
FROM transactions
WHERE distributor = "{}"
""".format(
name
)
)
for r in rows:
trans = {"id": r[0], "price": r[1], "cookie": r[2], "amount": r[3]}
transactions.append(trans)
dist["transactions"] = transactions
return jsonify(**dist)
if request.method == "PUT":
update = request.get_json()
new_name = update["name"]
address = update["address"]
cur.execute(
"""
UPDATE distributors
SET name = "{}", address = "{}"
WHERE name = "{}"
""".format(
new_name, address, name
)
)
# update in transactions
cur.execute(
"""
UPDATE transactions
set distributor = "{}"
WHERE distributor = "{}"
""".format(
new_name, name
)
)
conn.commit()
return "OK"
if request.method == "DELETE":
cur.execute('DELETE FROM distributors WHERE name = "{}"'.format(name))
conn.commit()
return "OK"
def cookie(name):
conn, cur = Model.make_cursor()
if request.method == 'GET':
row = cur.execute('''
SELECT name, price
FROM cookies
WHERE name="{}"
'''.format(name)).fetchone()
cookie = {
"name": row[0],
"price": row[1]
}
return jsonify(**cookie)
if request.method == 'PUT':
#update
cookie = request.get_json()
new_name = cookie["name"]
price = cookie["price"]
cur.execute('''
UPDATE cookies
SET name = "{}", price = {}
WHERE name="{}"
'''.format(new_name, price, name))
#also update stock references
cur.execute('''
UPDATE stock
SET cookie = "{}"
WHERE cookie = "{}"
'''.format(new_name, name))
conn.commit()
return "OK"
if request.method == 'DELETE':
conn, cur = Model.make_cursor()
cur.execute('DELETE FROM cookies WHERE name = "{}"'.format(name))
conn.commit()
return "OK"
def shop():
conn, cur = Model.make_cursor()
if request.method == 'GET':
return render_template("buyers/shop.html")
if request.method == 'POST':
update = request.get_json()
total = 0
buyer = update['buyer']
del update['buyer']
print(buyer)
print(update)
buyer = cur.execute('SELECT id FROM buyers WHERE first = "{}"'.format(buyer)).fetchone()[0]
for cookie, amt in update.items():
price = float(cur.execute('SELECT price FROM cookies WHERE name = "{}"'.format(cookie)).fetchone()[0])
total += price
cur.execute('''
INSERT INTO buyer_orders(buyer, total)
VALUES ({}, {})
'''.format(buyer, total))
conn.commit()
order = cur.lastrowid
for cookie, amt in update.items():
price = float(cur.execute('SELECT price FROM cookies WHERE name = "{}"'.format(cookie)).fetchone()[0])
warehouse = cur.execute('SELECT warehouse FROM stock WHERE cookie = "{}"'.format(cookie)).fetchone()[0]
print(warehouse, price)
cur.execute('''
INSERT INTO purchases(cookie, warehouse, buyer_order, amount)
VALUES ("{}", "{}", {}, {})
'''.format(cookie, warehouse, order, amt))
cur.execute('''
UPDATE stock
SET quantity = quantity - {}
WHERE cookie = "{}" and warehouse = "{}"
'''.format(amt, cookie, warehouse))
conn.commit()
print(total)
return "OK"
def index():
conn, cur = Model.make_cursor()
stats = {}
total = cur.execute('''
SELECT SUM(quantity)
FROM stock
WHERE quantity > 0
''').fetchone()[0]
purchases = cur.execute('''
SELECT SUM(amount)
FROM purchases
WHERE amount > 0
''').fetchone()[0]
price = cur.execute('''
SELECT SUM(total)
FROM buyer_orders
''').fetchone()[0]
transactions_made = cur.execute('''
SELECT SUM(amount)
FROM transactions
''').fetchone()[0]
transactions_price = cur.execute('''
SELECT SUM(price)
FROM transactions
''').fetchone()[0]
stats["total_cookies"] = total
stats["total_purchases"] = purchases
stats["total_price"] = price
stats["transactions_made"] = transactions_made
stats["transactions_price"] = transactions_price
return render_template('index.html', stats=stats)
