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]