키오스크 개발 계획서 및 개발 일지 - 0일차 - [2023-06-14]

 

 

 

오늘 작성중이던 장바구니 클래스

import datetime
import sqlite3
from common import *

from class_db_connect import KioskDBConnector


class Basket:
    def __init__(self, conn):
        conn: KioskDBConnector
        self.now_time = datetime.datetime.now()
        self.menu_id_list = list()
        self.option_list = list()
        self.price_list = list()
        self.conn = conn

    def __repr__(self):
        # return f'Basket_obj| MENU_ID: {self.menu_id_list}\t| OPTION_LIST: {self.option_list}\t| PRICE_LIST: {self.price_list}'
        menu_name_list = list()
        for index, menu_id in enumerate(self.menu_id_list):
            name = self.conn.get_menu_name_by_menu_id(menu_id)
            item_quantity = self.option_list[index][0]
            residual_option = self.option_list[index][1:]
            menu_name_list.append((name, str(item_quantity) + '개', residual_option, str(self.price_list[index])+'원'))
        return str(menu_name_list)

    def add_item(self, menu_id, option):
        assert isinstance(menu_id, int)
        assert isinstance(option, list)
        # 들어오는 옵션의 0번째는 수량, 1번째는 세트여부, -2번째는 사이드, -1번째는 음료
        self.menu_id_list.append(menu_id)
        self.option_list.append(option)
        now_price = self.get_price(menu_id, option)
        self.price_list.append(now_price)

    def refresh_price_list(self):
        self.price_list = list()
        for menu_id, option in zip(self.menu_id_list, self.option_list):
            self.price_list.append(self.get_price(menu_id, option))

    def delete_item(self, index_):
        assert isinstance(index_, int) and 0 <= index_ < len(self.menu_id_list)
        self.menu_id_list.pop(index_)
        self.option_list.pop(index_)

    def get_basket_as_zip(self):
        return zip(self.menu_id_list, self.option_list, self.price_list)

    def get_price(self, menu_id, option):
        c = self.conn.start_conn()
        result_price = 0
        if 'set' in option:
            side_menu_id = option[-2]
            beverage_menu_id = option[-1]
            # lunch 확인
            if is_lunch(self.now_time) and self.conn.is_serving_lunch_set(menu_id):
                print('런치타임')
                menu_price = c.execute('select lunch_price from tb_menu where menu_id = (?)', (menu_id,)).fetchone()[0]
            else:
                menu_price = c.execute('select set_price from tb_menu where menu_id = (?)', (menu_id,)).fetchone()[0]
            result_price = result_price + menu_price
            additional_price_list = c.execute('select additional_price from tb_menu where menu_id in (?, ?)',
                                              (side_menu_id, beverage_menu_id,)).fetchall()
            for i in additional_price_list:
                result_price = result_price + i[0]
            # large 시 요금 추가

        else:
            menu_price = self.conn.get_ordinary_price_by_menu_id(menu_id)
            result_price = result_price + menu_price

        return result_price * option[0]

    def decrease_item(self, index):
        self.option_list[index][0] -= 1
        self.refresh_price_list()

    def increase_item(self, index):
        self.option_list[index][0] += 1
        self.refresh_price_list()


if __name__ == '__main__':
    conn = KioskDBConnector()
    basket = Basket(conn)
    print(basket.now_time)
    basket.now_time = basket.now_time.replace(hour=12)
    print(basket.now_time)
    basket.add_item(1, [1, 'set', 'large', 35, 65])
    basket.add_item(3, [2, 'set', 35, 65])
    basket.add_item(45, [2])
    basket.decrease_item(1)
    print(basket)

오늘 작성한 Singleton DB Connection

 

import sqlite3

import numpy as np
import pandas as pd
import openpyxl

import common
from class_menu import Menu


class KioskDBConnector:
    _instance = None

    def __new__(cls):
        if not isinstance(cls._instance, cls):
            cls._instance = object.__new__(cls)
        return cls._instance

    def __init__(self):
        self.conn = None

    def start_conn(self):
        self.conn = sqlite3.connect('mac_db.db')
        return self.conn.cursor()

    def end_conn(self):
        if self.conn is not None:
            self.conn.close()
            self.conn = None

    def insert_menu_value(self):
        c = self.start_conn()
        df = pd.read_excel('excel_source/table_menu.xlsx', engine='openpyxl')
        self.conn: sqlite3.Connection
        for row in df.values:
            row: pd.Series
            result_row = list()
            temp_question_list = ['?' for _ in range(35)]
            for i in row:
                if isinstance(i, int) or isinstance(i, str):
                    result_row.append(i)
                else:
                    if np.isnan(i):
                        result_row.append(None)
                    else:
                        result_row.append(int(i))
            c.execute(
                f'insert into tb_menu({", ".join(common.tb_menu_col_name)}) values ({",".join(temp_question_list)})',
                result_row)
        self.conn.commit()
        self.end_conn()

    def delete_row_menu_table(self):
        self.start_conn()
        self.conn: sqlite3.Connection
        c = self.conn.cursor()
        c.execute(f'delete from tb_menu')
        self.conn.commit()
        self.end_conn()

    def insert_menu_detail_value(self):
        self.start_conn()
        df = pd.read_excel('excel_source/table_menu_detail.xlsx', engine='openpyxl')
        self.conn: sqlite3.Connection
        c = self.conn.cursor()
        for row in df.values:
            row: pd.Series
            result_row = list()
            temp_question_list = ['?' for _ in range(20)]
            for i in row:
                if isinstance(i, float):
                    result_row.append(None)
                else:
                    result_row.append(i)
            c.execute(
                f'insert into tb_menu_detail({", ".join(constants.tb_menu_detail_col_name)}) values ({",".join(temp_question_list)})',
                result_row)
        self.conn.commit()
        self.end_conn()

    def get_menu_list(self):
        self.start_conn()
        menu_list = list()
        df = pd.read_sql('select * from tb_menu natural join tb_menu_detail', self.conn)
        df.drop(['menu_detail_id'], axis=1, inplace=True)
        for row in df.values:
            menu_list.append(Menu(*row))
        self.end_conn()
        return menu_list

    def get_ordinary_price_by_menu_id(self, menu_id):
        c = self.start_conn()
        result_price = c.execute('select ordinary_price from tb_menu where menu_id = (?)', (menu_id,)).fetchone()[0]
        self.end_conn()
        return result_price

    def get_menu_name_by_menu_id(self, menu_id):
        c = self.start_conn()
        result_name = c.execute('select name from tb_menu where menu_id = (?)', (menu_id,)).fetchone()[0]
        self.end_conn()
        return result_name

    def is_serving_lunch_set(self, menu_id):
        c = self.start_conn()
        result_bool = c.execute('select is_lunch_menu from tb_menu where menu_id = (?)', (menu_id,)).fetchone()[0]
        self.end_conn()
        if result_bool == 1:
            return True
        else:
            return False


if __name__ == '__main__':
    pd.set_option('display.max_columns', None)
    pd.set_option('display.max_rows', None)
    pd.set_option('display.width', 1000)

    connector = KioskDBConnector()

    # connector.insert_menu_value()
    # connector.delete_row_menu_table()
    # connector.insert_menu_detail_value()

 

어느정도 완성한 DB table들

 

수정 전

수정 후

 

 

(ERD가 함부로 변경되면 안되는지 알지만.. 처음부터 완벽하게 구상할 수가 없어 죄송합니다..)

 

(아직 완벽히 달성한 요구사항이 없어 개발계획서의 요구사항 리스트로 대체합니다)

 

감사합니다.