def fetch_info():
id = request.get_json()
print('id:', id)
sql_ingredients = \
'''
SELECT I.NAME
FROM (MENU M INNER JOIN INGRD_USE U ON M.ID=U.MENU_ID)
INNER JOIN INGREDIENT I ON U.INGRD_ID=I.ID
WHERE M.ID=?
'''
sql_desc = 'SELECT DESC FROM MENU WHERE ID=?'
sql_nutrients = \
'''
SELECT WEIGHT_G, KCAL, PROTEIN_G, SODIUM_MG, SUGAR_G, SAT_FAT_G, CAFFEINE_MG
FROM MENU
WHERE ID=?
'''
db = get_db()
db.row_factory = dict_factory
ingredients = db.execute(sql_ingredients, (id, )).fetchall()
desc = db.execute(sql_desc, (id, )).fetchall()
nutrients = db.execute(sql_nutrients, (id, )).fetchall()
print('ingredients:', ingredients)
print('desc:', desc)
print('nutrients', nutrients)
return jsonify(ingredients=ingredients, desc=desc, nutrients=nutrients)
def view_menu():
db = get_db()
c = db.cursor()
global category_tag
category_tag = 1
query = set_query(category_tag)
c.execute(query)
info = c.fetchall()
if request.method == 'GET':
if request.args.get('burger'):
category_tag = 1
c.execute(query)
info = c.fetchall()
elif request.args.get('dessert'):
category_tag = 2
query = set_query(category_tag)
c.execute(query)
info = c.fetchall()
elif request.args.get('beverage'):
category_tag = 3
query = set_query(category_tag)
c.execute(query)
info = c.fetchall()
db.close()
return render_template('/manage_menu/change_menu.html',
data=info,
menu_data=0,
name=0)
def result():
db = get_db()
c = db.cursor()
query = set_query(category_tag)
c.execute(query)
info = c.fetchall()
return render_template('/manage_menu/change_menu.html',
data=info,
menu_data=0,
name=names)
def fetch_category(menu_cat):
sql = \
'''
SELECT ID, NAME, IS_SOLDOUT
FROM MENU M INNER JOIN MENU_CATEGORY C
ON M.ID=C.MENU_ID
WHERE CATEGORY_TAG=?
'''
db = get_db()
return db.execute(sql, (menu_cat, )).fetchall()
def fetch_menu(category):
sql = \
'''
SELECT ID, NAME, IMAGE_PATH, PRICE, IS_SOLDOUT
FROM MENU M INNER JOIN MENU_CATEGORY C
ON M.ID = C.MENU_ID
WHERE CATEGORY_TAG=? AND IS_SOLDOUT=0
'''
db = get_db()
return db.execute(sql, (category, )).fetchall()
def fetch_opt(category_tag):
sql = \
'''
SELECT ID, NAME, IMAGE_PATH, OPT_PRICE
FROM MENU M INNER JOIN OPT_PRICE P
ON M.ID = P.MENU_ID
WHERE OPT_TAG = ? AND IS_SOLDOUT=0
ORDER BY OPT_PRICE
'''
db = get_db()
return db.execute(sql, (category_tag, )).fetchall()
def fetch_stock():
id = request.get_json()
sql = \
'''
SELECT I.NAME, I.STOCK, I.UNIT
FROM INGRD_USE U INNER JOIN INGREDIENT I ON U.INGRD_ID = I.ID
WHERE U.MENU_ID = ?
'''
db = get_db()
ingredients = [dict(row) for row in db.execute(sql, (id, ))]
print(ingredients, type(ingredients))
return jsonify(ingredients=ingredients)
def menu_record():
db = get_db()
c = db.cursor()
db.execute(
'CREATE TEMP VIEW [SALE_STAT_BY_ID_FILTERED] AS SELECT MENU_ID, SUM(SALE_CNT) AS SALE_TOTAL, SUM(PAY) AS PAY_TOTAL FROM (SELECT MAIN_DISH_ID as MENU_ID, QTY as SALE_CNT, MAIN_DISH_TOTAL as PAY, O.ORDERED_AT as AT FROM ORDER_ITEM I INNER JOIN ORDERS O ON I.ORDER_ID = O.ID UNION ALL SELECT OPTION_ID as MENU_ID, OPT_QTY as SALE_CNT, OPT_TOTAL as PAY, O.ORDERED_AT as AT FROM OPT_CHOICE C INNER JOIN ORDERS O ON C.ORDER_ID = O.ID) GROUP BY MENU_ID'
)
c.execute(
'SELECT M.NAME, S.SALE_TOTAL, S.PAY_TOTAL, GROUP_CONCAT(C.CATEGORY_TAG) FROM (([SALE_STAT_BY_ID_FILTERED] S INNER JOIN MENU M ON S.MENU_ID = M.ID) INNER JOIN MENU_CATEGORY C ON M.ID = C.MENU_ID) GROUP BY NAME'
)
data = c.fetchall()
db.commit()
db.close()
return render_template('/manage_sale/menu_record.html', data=data)
def add_menu():
db = get_db()
c = db.cursor()
query = set_query(category_tag)
c.execute(query)
info = c.fetchall()
if request.method == 'POST':
menu_name = request.form['name']
menu_image = str(request.form['img'])
menu_price = int(request.form['price'])
menu_desc = request.form['desc']
menu_weight = float(request.form['weight'])
menu_kcal = float(request.form['kcal'])
menu_protein_g = float(request.form['protein_g'])
menu_protein_pct = float(request.form['protein_pct'])
menu_sodium_g = float(request.form['sodium_g'])
menu_sodium_pct = float(request.form['sodium_pct'])
menu_sugar = float(request.form['sugar'])
menu_satfat_g = float(request.form['satfat_g'])
menu_satfat_pct = float(request.form['satfat_pct'])
menu_caff = float(request.form['caff'])
menu_allergy = request.form['allergy']
menu_category = request.form['category']
error = None
if db.execute('SELECT NAME FROM "MENU" WHERE name = ?',
(menu_name, )).fetchone() is not None:
error = 'Menu {} is already registered.'.format(menu_name)
if error is None:
db.execute(
'INSERT INTO MENU (NAME, IMAGE_PATH, PRICE, DESC, IS_SOLDOUT, WEIGHT_G, KCAL, PROTEIN_G, PROTEIN_PCENT, SODIUM_MG, SODIUM_PCENT, SUGAR_G, SAT_FAT_G, SAT_FAT_PCENT, CAFFEINE_MG, ALLERGY_INFO) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
(menu_name, menu_image, menu_price, menu_desc, 0, menu_weight,
menu_kcal, menu_protein_g, menu_protein_pct, menu_sodium_g,
menu_sodium_pct, menu_sugar, menu_satfat_g, menu_satfat_pct,
menu_caff, menu_allergy))
c.execute('SELECT ID FROM MENU WHERE NAME=?', (menu_name, ))
tmp = c.fetchone()
menu_id = tmp[0]
db.execute('INSERT INTO "MENU_CATEGORY" VALUES (?,?)',
(menu_id, menu_category))
db.commit()
db.close()
global name
name = menu_name
return redirect(url_for('add_menu.result', name=name))
flash(error)
db.close()
return render_template('/manage_menu/add_menu.html', data=info, name=0)
def delete_data():
if request.method == "POST":
order_id = request.form['order_id']
db = get_db()
c = db.cursor()
c.execute("DELETE FROM OPT_CHOICE WHERE ORDER_ID=?", (order_id))
c.execute("DELETE FROM ORDER_ITEM WHERE ORDER_ID=?", (order_id))
c.execute("DELETE FROM ORDERS WHERE ID=?", (order_id))
db.commit()
db.close()
return redirect(url_for('manage_sale.pay_record'))
return redirect(url_for('manage_sale.pay_record'))
def toggle_soldout():
data = request.get_json()
print(data, type(data))
sql = \
'''
UPDATE MENU
SET IS_SOLDOUT=?
WHERE ID=?
'''
db = get_db()
db.execute(sql, (data['is_soldout'], data['id']))
db.commit()
return jsonify(result='품절 여부가 업데이트 되었습니다.')
def index():
db = get_db()
c = db.cursor()
c.execute('SELECT LOGIN_ID FROM MANAGER')
username = c.fetchone()
c.execute('SELECT LOGIN_PWD FROM MANAGER')
password = c.fetchone()
username = str(username[0])
password = str(password[0])
db.commit()
db.close()
return render_template('/auth/auth.html',
username=username,
password=password)
def view_detail():
db = get_db()
c = db.cursor()
query = set_query(category_tag)
c.execute(query)
info = c.fetchall()
if request.method == 'POST':
menu_name = request.form['view_name']
c.execute(
'SELECT NAME, IMAGE_PATH, PRICE, DESC, WEIGHT_G, KCAL, PROTEIN_G, PROTEIN_PCENT, SODIUM_MG, SODIUM_PCENT, SUGAR_G, SAT_FAT_G, SAT_FAT_PCENT, CAFFEINE_MG, ALLERGY_INFO FROM MENU WHERE NAME=?',
(menu_name, ))
menu_data = c.fetchall()
db.close()
return render_template('/manage_menu/delete_menu.html',
data=info,
menu_data=menu_data)
def pay_record():
db = get_db()
c = db.cursor()
db.execute(
"CREATE TEMP VIEW [ORDER_SUM_TEMP] AS SELECT A.ORDER_ID,A.ITEM_NO,MAIN_ORDER,OPTION_ORDER FROM (select ORDER_ID,ITEM_NO,group_concat(M.NAME || QTY, '|') AS MAIN_ORDER from ORDER_ITEM O INNER JOIN MENU M ON O.MAIN_DISH_ID = M.ID group by ORDER_ID, ITEM_NO) A LEFT JOIN (select ORDER_ID,ITEM_NO,group_concat(M.NAME || OPT_QTY) AS OPTION_ORDER from OPT_CHOICE O INNER JOIN MENU M ON O.OPTION_ID = M.ID group by ORDER_ID,ITEM_NO) B ON (A.ORDER_ID, A.ITEM_NO) = (B.ORDER_ID, B.ITEM_NO)"
)
query = "SELECT ORDER_ID, O.WAIT_NO, date(O.ORDERED_AT) AS DATE, strftime('%H:%M',O.ORDERED_AT) AS TIME, O.RECEIPT_TOTAL, group_concat(MAIN_ORDER || '(' || coalesce(OPTION_ORDER,'-') || ')') AS ORDER_SUM FROM [ORDER_SUM_TEMP] S INNER JOIN ORDERS O ON S.ORDER_ID = O.ID GROUP BY ORDER_ID"
c.execute(query)
data = c.fetchall()
total_query = "SELECT SUM(RECEIPT_TOTAL) FROM ORDERS"
c.execute(total_query)
total = c.fetchall()
db.commit()
db.close()
return render_template('/manage_sale/pay_record.html',
data=data,
total=total)
def delete_menu():
db = get_db()
c = db.cursor()
query = set_query(category_tag)
c.execute(query)
info = c.fetchall()
if request.method == 'POST':
menu_name = request.form['name']
c.execute('SELECT ID FROM MENU WHERE NAME=?', (menu_name, ))
tmp = c.fetchone()
menu_id = tmp[0]
db.execute('DELETE FROM "MENU_CATEGORY" WHERE MENU_ID=?', (menu_id, ))
db.execute('DELETE FROM "MENU" WHERE NAME=?', (menu_name, ))
db.commit()
db.close()
global name
name = menu_name
return redirect(url_for('delete_menu.result', name=name))
def call(id):
error = None
if not id:
error = 'Id is required'
assert isinstance(id, int) == True
if error is not None:
flash(error)
else:
db = get_db()
mark_done = \
'''
UPDATE ORDERS
SET STATUS='SERVED', SERVED_AT=?
WHERE ID = ?
'''
db.execute(mark_done,
(datetime.datetime.now().replace(microsecond=0), id))
db.commit()
return redirect(url_for('manage_order.orders'))
def change_menu():
db = get_db()
c = db.cursor()
query = set_query(category_tag)
c.execute(query)
info = c.fetchall()
if request.method == 'POST':
menu_name = request.form['name']
#menu_image = str(request.form['img'])
menu_price = int(request.form['price'])
menu_desc = request.form['desc']
menu_weight = float(request.form['weight'])
menu_kcal = float(request.form['kcal'])
menu_protein_g = float(request.form['protein_g'])
menu_protein_pct = float(request.form['protein_pct'])
menu_sodium_g = float(request.form['sodium_g'])
menu_sodium_pct = float(request.form['sodium_pct'])
menu_sugar = float(request.form['sugar'])
menu_satfat_g = float(request.form['satfat_g'])
menu_satfat_pct = float(request.form['satfat_pct'])
menu_caff = float(request.form['caff'])
menu_allergy = request.form['allergy']
c.execute('SELECT ID FROM MENU WHERE NAME=?', (original_name, ))
tmp = c.fetchone()
menu_id = tmp[0]
db.execute(
'UPDATE MENU SET NAME=?, PRICE=?, DESC=?, WEIGHT_G=?, KCAL=?, PROTEIN_G=?, PROTEIN_PCENT=?, SODIUM_MG=?, SODIUM_PCENT=?, SUGAR_G=?, SAT_FAT_G=?, SAT_FAT_PCENT=?, CAFFEINE_MG=?, ALLERGY_INFO=? WHERE ID=?',
(menu_name, menu_price, menu_desc, menu_weight, menu_kcal,
menu_protein_g, menu_protein_pct, menu_sodium_g, menu_sodium_pct,
menu_sugar, menu_satfat_g, menu_satfat_pct, menu_caff,
menu_allergy, menu_id))
db.commit()
db.close()
global names
names = [] #변경 전 후 메뉴명 리스트
names.append(original_name)
names.append(menu_name)
return redirect(url_for('change_menu.result', names=names))
def menu_data():
db = get_db()
c = db.cursor()
if request.method == "GET":
db.execute(
'CREATE TEMP VIEW [SALE_STAT_BY_ID_FILTERED] AS SELECT MENU_ID, SUM(SALE_CNT) AS SALE_TOTAL, SUM(PAY) AS PAY_TOTAL FROM (SELECT MAIN_DISH_ID as MENU_ID, QTY as SALE_CNT, MAIN_DISH_TOTAL as PAY, O.ORDERED_AT as AT FROM ORDER_ITEM I INNER JOIN ORDERS O ON I.ORDER_ID = O.ID UNION ALL SELECT OPTION_ID as MENU_ID, OPT_QTY as SALE_CNT, OPT_TOTAL as PAY, O.ORDERED_AT as AT FROM OPT_CHOICE C INNER JOIN ORDERS O ON C.ORDER_ID = O.ID) GROUP BY MENU_ID'
)
if request.args.get('burger'):
c.execute(
'SELECT M.NAME, S.SALE_TOTAL, S.PAY_TOTAL, GROUP_CONCAT(C.CATEGORY_TAG) FROM (([SALE_STAT_BY_ID_FILTERED] S INNER JOIN MENU M ON S.MENU_ID = M.ID) INNER JOIN MENU_CATEGORY C ON M.ID = C.MENU_ID) WHERE CATEGORY_TAG LIKE "%버거%" GROUP BY NAME'
)
elif request.args.get('dessert'):
c.execute(
'SELECT M.NAME, S.SALE_TOTAL, S.PAY_TOTAL, GROUP_CONCAT(C.CATEGORY_TAG) FROM (([SALE_STAT_BY_ID_FILTERED] S INNER JOIN MENU M ON S.MENU_ID = M.ID) INNER JOIN MENU_CATEGORY C ON M.ID = C.MENU_ID) WHERE CATEGORY_TAG="디저트" OR CATEGORY_TAG="치킨" GROUP BY NAME'
)
elif request.args.get('drink'):
c.execute(
'SELECT M.NAME, S.SALE_TOTAL, S.PAY_TOTAL, GROUP_CONCAT(C.CATEGORY_TAG) FROM (([SALE_STAT_BY_ID_FILTERED] S INNER JOIN MENU M ON S.MENU_ID = M.ID) INNER JOIN MENU_CATEGORY C ON M.ID = C.MENU_ID) WHERE CATEGORY_TAG="음료" OR CATEGORY_TAG="커피" GROUP BY NAME'
)
data = c.fetchall()
db.commit()
db.close()
return render_template('/manage_sale/menu_record.html', data=data)
elif request.method == "POST":
name = request.form['menu_input']
date = request.form['date']
query = "CREATE TEMP VIEW [SALE_STAT_BY_ID_FILTERED_DATE] AS SELECT MENU_ID, SUM(SALE_CNT) AS SALE_TOTAL, SUM(PAY) AS PAY_TOTAL FROM (SELECT MAIN_DISH_ID as MENU_ID, QTY as SALE_CNT, MAIN_DISH_TOTAL as PAY, O.ORDERED_AT as AT FROM ORDER_ITEM I INNER JOIN ORDERS O ON I.ORDER_ID = O.ID UNION ALL SELECT OPTION_ID as MENU_ID, OPT_QTY as SALE_CNT, OPT_TOTAL as PAY, O.ORDERED_AT as AT FROM OPT_CHOICE C INNER JOIN ORDERS O ON C.ORDER_ID = O.ID) WHERE AT LIKE '%" + date + "%' GROUP BY MENU_ID"
db.execute(query)
c.execute(
'SELECT M.NAME, S.SALE_TOTAL, S.PAY_TOTAL, C.CATEGORY_TAG FROM (([SALE_STAT_BY_ID_FILTERED_DATE] S INNER JOIN MENU M ON S.MENU_ID = M.ID) INNER JOIN MENU_CATEGORY C ON M.ID = C.MENU_ID) WHERE M.NAME=?',
(name, ))
data = c.fetchall()
db.commit()
db.close()
return render_template('/manage_sale/menu_record.html', data=data)
def orders():
db = get_db()
cur = db.cursor()
cur_order = db.cursor()
orders = []
max_view = 8
cols = 4
fetch_waiting = 'SELECT ID, WAIT_NO, strftime("%H:%M",ORDERED_AT) AS ORDERED_AT FROM ORDERS WHERE STATUS = "WAITING"'
cur_order.execute(fetch_waiting)
wait_rows = cur_order.fetchmany(max_view)
for row in wait_rows:
order = dict(row)
fetch_items = \
'''
SELECT ITEM_NO, NAME, SUM(QTY) AS QTY
FROM (ORDER_ITEM I INNER JOIN MENU M ON I.MAIN_DISH_ID = M.ID)
INNER JOIN ORDERS O ON I.ORDER_ID = O.ID
WHERE ORDER_ID = ?
GROUP BY ORDER_ID, NAME
'''
cur.execute(fetch_items, (order["ID"], ))
order['items'] = [dict(row) for row in cur]
items = order['items']
fetch_options = \
'''
SELECT M.NAME,OPT_QTY AS QTY
FROM OPT_CHOICE O INNER JOIN MENU M ON O.OPTION_ID = M.ID
WHERE ORDER_ID = ? AND ITEM_NO = ?
'''
for item in items:
cur.execute(fetch_options, (order['ID'], item['ITEM_NO']))
item['options'] = [dict(row) for row in cur]
orders.append(order)
return render_template('manage_order/order_manage.html',
orders=orders,
len=len(orders),
max_len=max_view,
cols=cols)
def register():
data = request.get_json()
print(data, type(data))
items = data['items']
# print(items, type(items))
total = data['total']
receipt_total = total['price']
db = get_db()
# orders 테이블 반영-price
now = datetime.datetime.now().replace(
microsecond=0) # todo:wait_no, is togo, pay method
create_order = \
'''
INSERT INTO ORDERS (STATUS, ORDERED_AT, RECEIPT_TOTAL)
VALUES ( ?, ?, ?)
'''
db.execute('BEGIN TRANSACTION')
order_id = db.execute(create_order,
('WAITING', now, receipt_total)).lastrowid
print('order id:', order_id)
# ORDER_ITEM 테이블 구조에 맞는 형태로 입력을 변환한다.
raw_list = []
insert_list = []
# 형태 변환 전 1차 가공
for item in items:
print('name:', item['name'])
main_id = fetch_menu_id(item['name']) # MAIN_DISH_ID를 구한다
main_dish_total = item['price']
item['options'] = []
if item['id'] == 'set':
options = []
dessert_id = fetch_menu_id(item['dessert'][0])
drink_id = fetch_menu_id(item['drink'][0])
options.append((dessert_id, 1, item['dessert'][1]))
options.append((drink_id, 1, item['drink'][1]))
item['options'] += options
opt_total = item['dessert'][1] + item['drink'][1]
print('options:', options)
raw_list.append((order_id, main_id, item['amount'], main_dish_total,
item['options']))
print('raw_list:', raw_list)
# 2차 가공: 같은 MAIN_DISH끼리 묶어 QTY, OPTIONS를 합치고 item_no를 부여한다.
i = 1
insert_opt_list = []
raw_list = sorted(raw_list, key=lambda x: x[1])
for key, group in groupby(raw_list, lambda x: x[1]):
group_list = list(group)
print('main_id_list:', [item[1] for item in group_list])
order_id = group_list[0][0]
item_no = i
main_id = key
qty = sum([item[2] for item in group_list])
main_dish_total = sum([item[3] for item in group_list])
raw_options = []
for item in group_list:
raw_options += item[4]
raw_options = sorted(raw_options, key=lambda x: x[0])
# ORDER_ITEM 테이블 구조에 맞는 형태로 입력을 변환한다.
for key, group in groupby(raw_options, lambda x: x[0]):
group_list = list(group)
print('option_group:', group_list)
option_id = group_list[0][0]
opt_qty = sum([item[1] for item in group_list])
opt_total = sum([item[2] for item in group_list])
insert_opt_list.append(
(order_id, item_no, option_id, opt_qty, opt_total))
insert_list.append((order_id, item_no, main_id, qty, main_dish_total))
i += 1
print('insert_list:', insert_list)
insert_main = \
'''
INSERT INTO ORDER_ITEM (ORDER_ID, ITEM_NO, MAIN_DISH_ID, QTY, MAIN_DISH_TOTAL)
VALUES ( ?, ?, ?, ?, ?)
'''
db.executemany(insert_main, insert_list)
print('insert_opt_list:', insert_opt_list)
insert_opt = \
'''
INSERT INTO OPT_CHOICE (ORDER_ID, ITEM_NO, OPTION_ID, OPT_QTY, OPT_TOTAL)
VALUES (?, ?, ?, ?, ?)
'''
db.executemany(insert_opt, insert_opt_list)
db.execute('COMMIT')
return render_template('order/order_num.html', order_id=order_id)
def fetch_menu_id(name):
db = get_db()
return db.execute('SELECT ID FROM MENU WHERE NAME=?',
(name, )).fetchone()[0]
