def order_product_soldout_dao(self, connection, data): """상품 결제 시점에 해당 상품이 품절되었는지 여부 체크 Args: connection : 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 조회할 data Author: 고수희 Returns: {'soldOut': true}: 상품이 품절됨 {'soldOut': false}: 상품이 품절되지 않음 Raises: 400, {'message': 'product does not exist', 'errorMessage': 'product_does_not_exist'} : 상품을 조회할 수 없음 500, {'message: server_error', 'errorMessage': 'server_error'} :서버 에러 발생 History: 2020-12-30(고수희): 초기 생성 """ sql = """ SELECT st.remain as remain FROM products as pd INNER JOIN stocks as st ON st.id = pd.id WHERE pd.id = %s ; """ try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(sql, data['product_id']) result = cursor.fetchone() if not result: raise ProductNotExist('product_does_not_exist') # 상품 재고가 주문량을 소화할 수 있는지 체크 if result['remain'] < data['quantity']: raise NotEnoughProduct('Not as many in stock as quantity') # 상품 재고가 0인지 확인하여, 상품이 품절되었는지 체크 if result['remain'] <= 0: raise CheckoutDenied('unable_to_checkout') except ProductNotExist as e: traceback.print_exc() raise e except NotEnoughProduct as e: traceback.print_exc() raise e except CheckoutDenied as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server_error')
def get_today_order_number_dao(self, connection): """주문번호 생성을 위한 당일 주문번호 채번 Args: connection: 데이터베이스 연결 객체 Author: 고수희 Returns: 당일 주문량 +1 History: 2020-12-30(고수희): 초기 생성 """ # TODO 다른 주문이 같은 시점에 주문이 될 수 있음 today_sql = """ SELECT count(*)+1 as today FROM orders WHERE date(created_at) = date(now()) ; """ try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(today_sql) result = cursor.fetchone() return result except Exception: traceback.print_exc() raise ServerError('server_error')
def get_seller_category_dao(self, connection, data): """셀러 상품 카테고리 조회 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 조회할 data Author: 고수희 Returns: [ { "main_category_id": 1, "name": "아우터" }, { "main_category_id": 2, "name": "상의" } ] History: 2021-01-01(고수희): 초기 생성 Raises: 400, {'message': 'seller category does not exist', 'errormessage': 'seller_category_not_exist'} : 셀러 카테고리 조회 실패 500, {'message': 'server error', 'errorMessage': 'server_error'}': 서버 에러 """ # TODO 상품 갯수가 많을 경우 성능 저하가 생길 수 있음 sql = """ SELECT DISTINCT pd.main_category_id , mc.name FROM products as pd INNER JOIN main_categories as mc ON mc.id = pd.main_category_id WHERE pd.seller_id = %(seller_id)s ; """ try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(sql, data) result = cursor.fetchall() # 셀러 카테고리가 없을 경우 if not result: raise SellerCategoryNotExist('seller_category_not_exist') return result except SellerCategoryNotExist as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server_error')
def get_seller_info_dao(self, connection, account_id): """셀러 정보 조회 Args: connection: 데이터베이스 연결 객체 account_id : 서비스 레이어에서 넘겨 받아 조회할 account_id Author: 고수희 Returns: { "background_image": "https://img.freepik.com/free-psd/top-view-t-shirt-concept-mock-up_23-2148809114.jpg?size=626&ext=jpg&ga=GA1.2.1060993109.1605750477", "english_name": "i am seller_2", "id": 2, "name": "나는셀러2", "profile_image": "https://img.freepik.com/free-psd/logo-mockup-white-paper_1816-82.jpg?size=626&ext=jpg&ga=GA1.2.1060993109.1605750477" } History: 2021-01-01(고수희): 초기 생성 Raises: 400, {'message': 'seller does not exist', 'errorMessage': 'seller_does_not_exist'} : 셀러 정보 조회 실패 500, {'message': 'server error', 'errorMessage': 'server_error'}': 서버 에러 """ sql = """ SELECT account_id AS id , name , english_name , profile_image_url AS profile_image , background_image_url AS background_image FROM sellers WHERE account_id = %s AND is_deleted = 0 ; """ try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(sql, account_id) result = cursor.fetchone() if not result: raise SellerNotExist('seller_not_exist') return result except SellerNotExist as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server_error')
def post_cart_item_dao(self, connection, data): """장바구니 상품 추가 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 추가할 data Author: 고수희 Returns: 생성된 cart의 id 반환 Raises: 400, {'message': 'unable to create', 'errorMessage': 'unable_to_create'} : 장바구니 상품 추가 실패 History: 2020-12-28(고수희): 초기 생성 2021-01-02(고수희): traceback 추가 """ sql = """ INSERT INTO cart_items ( product_id , stock_id , quantity , user_id , sale , original_price , discounted_price ) VALUES ( %(product_id)s , %(stock_id)s , %(quantity)s , %(user_id)s , %(sale)s , %(original_price)s , %(discounted_price)s ); """ try: with connection.cursor() as cursor: cursor.execute(sql, data) result = cursor.lastrowid if not result: raise CartItemCreateDenied('unable_to_create') return result except CartItemCreateDenied as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server_error')
def get_sender_info_dao(self, connection, data): """ 주문자 정보 조회 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 조회할 data Author: 고수희 Returns: {"name": "고수희", "phone": "01012341234", "email": "*****@*****.**", } Raises: 500 {"message": "server error", "error_message: "server_error"} 서버 에러 History: 2020-12-30(고수희): 초기 생성 2020-01-02(고수희): traceback 추가 """ sql = """ SELECT name , phone , email FROM customer_information WHERE account_id = %s ; """ try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(sql, data['user_id']) result = cursor.fetchone() if not result: result = { "name":"", "phone":"", "email":"" } return result return result except Exception: traceback.print_exc() raise ServerError('server_error')
def post_delivery_type_dao(self, connection, data): """배송 정보 추가 (배송 메모가 직접 입력일 경우) Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 추가할 data Author: 고수희 Returns: 생성된 delivery_type_id 반환 Raises: 400, {'message': 'delivery memo create denied', 'errorMessage': 'unable_to_create'} : 배송 정보 추가 실패 500, {'message: server_error', 'errorMessage': 'server_error'} :서버 에러 발생 History: 2020-12-30(고수희): 초기 생성 """ sql = """ INSERT INTO delivery_memo_types ( content ,is_default ) VALUES ( %(delivery_content)s ,0 ); """ try: with connection.cursor() as cursor: cursor.execute(sql, data) result = cursor.lastrowid if not result: raise DeliveryMemoCreateDenied('unable_to_create') return result except DeliveryMemoCreateDenied as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server_error')
def post_store_order_item_history_dao(self, connection, data): """주문 상품 정보 이력 추가 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 추가할 data Author: 고수희 Returns: None Raises: 400, {'message': 'order history create denied', 'errorMessage': 'unable_to_create'} : 주문 상품 정보 이력 추가 실패 500, {'message: server_error', 'errorMessage': 'server_error'} :서버 에러 발생 History: 2020-12-30(고수희): 초기 생성 """ sql = """ INSERT INTO order_item_histories ( order_item_id , order_item_status_type_id , updater_id ) VALUES ( %(order_item_id)s , %(order_item_status_type_id)s , %(user_id)s ); """ try: with connection.cursor() as cursor: cursor.execute(sql, data) result = cursor.lastrowid if not result: raise OrderHistoryCreateDenied('unable_to_create') except OrderHistoryCreateDenied as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server error')
def product_soldout_dao(self, connection, data): """장바구니 상품 추가 시점에 해당 상품이 품절되었는지 여부 체크 Args: connection : 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 조회할 data Author: 고수희 Returns: {'soldOut': true}: 상품이 품절됨 {'soldOut': false}: 상품이 품절되지 않음 Raises: 400, {'message': 'product does not exist', 'errorMessage': 'product_does_not_exist'} : 상품을 조회할 수 없음 History: 2020-12-29(고수희): 초기 생성 2021-01-02(고수희): traceback 추가 """ sql = """ SELECT st.remain as remain FROM products as pd INNER JOIN stocks as st ON st.id = pd.id WHERE pd.id = %s ; """ try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(sql, data['product_id']) result = cursor.fetchone() if not result: raise ProductNotExist('product_does_not_exist') #상품 재고가 0인지 확인하여, 상품이 품절되었는지 체크 if result['remain'] <= 0: return {'soldout': True} return {'soldout': False} except ProductNotExist as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server_error')
def patch_customer_information_dao(self, connection, data): """주문자 정보 추가 및 수정 주문자 정보 조회 시 주문자 정보가 있으면 수정, 없으면 추가함 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 추가할 data Author: 고수희 Returns: None Raises: 500, {'message: server_error', 'errorMessage': 'server_error'} :서버 에러 발생 History: 2020-12-31(고수희): 초기 생성 """ sql = """ INSERT INTO customer_information( account_id , name , email , phone ) VALUES ( %(user_id)s , %(sender_name)s , %(sender_email)s , %(sender_phone)s ) ON DUPLICATE KEY UPDATE name = %(sender_name)s , email = %(sender_email)s , phone = %(sender_phone)s ; """ try: with connection.cursor() as cursor: cursor.execute(sql, data) except Exception: traceback.print_exc() raise ServerError('server error')
def get_store_order_dao(self, connection, data): """상품 결제 완료 결과 조회 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 조회할 data Author: 고수희 Returns: return Raises: 400, {'message': 'order does not exist', 'errorMessage': 'order_does_not_exist'} : 결제 상품 정보 조회 실패 500, {'message: server_error', 'errorMessage': 'server_error'} :서버 에러 발생 History: 2020-12-30(고수희): 초기 생성 """ sql = """ SELECT order_number, total_price FROM orders WHERE id = %s ; """ try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(sql, data['order_id']) result = cursor.fetchone() if not result: raise OrderNotExist('order_does_not_exist') return result except OrderNotExist as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server_error')
def patch_product_remain_dao(self, connection, data): """상품 재고 감소 처리 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 추가할 data Author: 고수희 Returns: None Raises: 400, {'message': 'product remain update denied', 'errorMessage': 'unable_to_update'} : 상품 재고 업데이트 실패 500, {'message: server_error', 'errorMessage': 'server_error'} :서버 에러 발생 History: 2020-12-31(고수희): 초기 생성 """ sql = """ UPDATE stocks SET remain = remain - %(quantity)s WHERE id = %(stock_id)s ; """ try: with connection.cursor() as cursor: affected_row = cursor.execute(sql, data) if affected_row == 0: raise ProductRemainUpdateDenied('unable_to_update') except ProductRemainUpdateDenied as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server error')
def patch_is_delete_cart_item_dao(self, connection, data): """장바구니 상품 논리 삭제 처리 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 추가할 data Author: 고수희 Returns: None Raises: 400, {'message': 'invalid_delete_command_access', 'errorMessage': 'unable_to_delete'} : 논리삭제 실패 500, {'message: server_error', 'errorMessage': 'server_error'} :서버 에러 발생 History: 2020-12-31(고수희): 초기 생성 """ # TODO 주문이 복수 아이템일 경우 Update join을 사용해서 삭제 처리 sql = """ UPDATE cart_items SET is_deleted = 1 WHERE id = %(cart_id)s; """ try: with connection.cursor() as cursor: affected_row = cursor.execute(sql, data) if affected_row == 0: raise DeleteDenied('unable_to_delete') except DeleteDenied as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server_error')
def get_seller_product_search_dao(self, connection, data): """셀러 상품 검색 조회 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 조회할 data Author: 고수희 Returns: [ { "discount_rate": 0.1, "discounted_price": 9000.0, "image": "https://img.freepik.com/free-psd/simple-black-men-s-tee-mockup_53876-57893.jpg?size=338&ext=jpg&ga=GA1.2.1060993109.1605750477", "origin_price": 10000.0, "product_id": 7, "product_name": "성보의하루7", "seller_id": 4, "seller_name": "나는셀러4" }, { "discount_rate": 0.1, "discounted_price": 9000.0, "image": "https://img.freepik.com/free-psd/simple-black-men-s-tee-mockup_53876-57893.jpg?size=338&ext=jpg&ga=GA1.2.1060993109.1605750477", "origin_price": 10000.0, "product_id": 5, "product_name": "성보의하루5", "seller_id": 4, "seller_name": "나는셀러4" } ] Raises: 500, {'message': 'server error', 'errorMessage': 'server_error'}': 서버 에러 History: 2021-01-02(고수희): 초기 생성 """ # TODO like로 할 경우 성능 저하가 생길 수 있기때문에 다른 선택지를 고려해야함 sql = """ SELECT pi.image_url AS image , pd.seller_id AS seller_id , se.name AS seller_name , pd.id AS product_id , pd.name AS product_name , pd.origin_price AS origin_price , pd.discount_rate AS discount_rate , pd.discounted_price AS discounted_price FROM products AS pd INNER JOIN product_images AS pi ON pi.product_id = pd.id AND pi.order_index = 1 INNER JOIN sellers AS se ON se.account_id = pd.seller_id LEFT JOIN product_sales_volumes AS psv ON psv.product_id = pd.id WHERE pd.seller_id = %(seller_id)s AND pd.name LIKE %(keyword)s AND pd.is_deleted = 0 ORDER BY pd.id DESC LIMIT %(limit)s OFFSET %(offset)s ; """ try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: data['keyword'] = "%" + data['keyword'] + "%" cursor.execute(sql, data) results = cursor.fetchall() # 상품 검색 결과가 없을 경우 if not results: return "등록된 상품이 없습니다." return results except Exception: traceback.print_exc() raise ServerError('server_error')
def get_seller_product_list_dao(self, connection, data): """셀러 상품 리스트 조회 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 조회할 data Author: 고수희 Returns: [ { "discount_rate": 0.1, "discounted_price": 9000.0, "image": "https://img.freepik.com/free-psd/simple-black-men-s-tee-mockup_53876-57893.jpg?size=338&ext=jpg&ga=GA1.2.1060993109.1605750477", "origin_price": 10000.0, "product_id": 7, "product_name": "성보의하루7", "seller_id": 4, "seller_name": "나는셀러4" }, { "discount_rate": 0.1, "discounted_price": 9000.0, "image": "https://img.freepik.com/free-psd/simple-black-men-s-tee-mockup_53876-57893.jpg?size=338&ext=jpg&ga=GA1.2.1060993109.1605750477", "origin_price": 10000.0, "product_id": 5, "product_name": "성보의하루5", "seller_id": 4, "seller_name": "나는셀러4" } Raises: 500, {'message': 'server error', 'errorMessage': 'server_error'}': 서버 에러 History: 2021-01-02(고수희): 초기 생성 """ sql = """ SELECT pi.image_url AS image , pd.seller_id AS seller_id , se.name AS seller_name , pd.id AS product_id , pd.name AS product_name , pd.origin_price AS origin_price , pd.discount_rate AS discount_rate , pd.discounted_price AS discounted_price , psv.sales_count AS product_sales_count FROM products AS pd INNER JOIN product_images AS pi ON pi.product_id = pd.id AND pi.order_index = 1 INNER JOIN sellers AS se ON se.account_id = pd.seller_id LEFT JOIN product_sales_volumes AS psv ON psv.product_id = pd.id """ try: # TODO 쿼리 줄 맞춤 # 특정 카테고리를 선택한 경우 : sql에 포함 if data['category'] is not None: sql += """ WHERE pd.main_category_id = %(category)s AND pd.seller_id = %(seller_id)s """ # TODO 하단 정리 # 특정 카테고리를 선택하지 않은 경우 else: sql += """ WHERE pd.seller_id = %(seller_id)s """ # 최신순 정렬일 경우 if data['type'] == "latest": sql += """ AND pd.is_deleted = 0 ORDER BY pd.id DESC LIMIT %(limit)s OFFSET %(offset)s ; """ # 인기순 정렬일 경우 else: sql += """ AND pd.is_deleted = 0 ORDER BY product_sales_count DESC LIMIT %(limit)s OFFSET %(offset)s ; """ with connection.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(sql, data) results = cursor.fetchall() # 상품 검색 결과가 없을 경우 if not results: return "등록된 상품이 없습니다." return results except Exception: traceback.print_exc() raise ServerError('server_error')
def get_cart_item_dao(self, connection, data): """장바구니 상품 정보 조회 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 조회할 data Author: 고수희 Returns: "result": { "cart_item": { "color": "Black", "discounted_price": 9000.0, "id": 23, "image_url": "https://img.freepik.com/free-psd/simple-black-men-s-tee-mockup_53876-57893.jpg?size=338&ext=jpg&ga=GA1.2.1060993109.1605750477", "original_price": 14000.0, "product_id": 1, "product_name": "성보의하루1", "quantity": "1", "sale": 0.1, "seller_name": "나는셀러9", "size": "Free", "soldout": false, "stock_id": 1, "total_price": 9000.0 } } History: 2020-12-28(고수희): 초기 생성 2021-01-01(고수희): 상품 조회 시 재고도 함께 조회하는 것으로 로직 수정 2021-01-02(고수희): traceback 추가 Raises: 400, {'message': 'cart item does not exist', 'errorMessage': 'cart_item_does_not_exist'} : 장바구니 상품 정보 조회 실패 """ sql = """ SELECT ct.id , se.name AS seller_name , ct.product_id , pd.name AS product_name , pi.image_url , ct.stock_id , co.name AS color , sz.name AS size , ct.quantity , sale , ct.original_price , ct.discounted_price , st.remain AS soldout FROM cart_items as ct INNER JOIN stocks as st ON st.id = stock_id INNER JOIN colors as co ON co.id = st.color_id INNER JOIN sizes as sz ON sz.id = st.size_id INNER JOIN product_images as pi ON pi.product_id = ct.product_id AND pi.order_index = 1 INNER JOIN products as pd ON pd.id = ct.product_id INNER JOIN sellers as se ON se.account_id = pd.seller_id WHERE ct.id = %s AND ct.is_deleted = 0 ; """ try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: cursor.execute(sql, data['cart_id']) item_info = cursor.fetchone() if not item_info: raise CartItemNotExist('cart_item_does_not_exist') # 상품 재고가 0인지 확인하여, 상품이 품절되었는지 체크 if item_info['soldout'] <= 0: item_info['soldout'] = True item_info['soldout'] = False # 총 가격 계산, 할인가가 있으면 할인가가 총 가격이 됨 total_price = {"total_price": (item_info['discounted_price'] if item_info['discounted_price'] > 0 else item_info['original_price'])} #총 가격을 상품 조회 결과에 병합 item_info.update(total_price) result = {"cart_item": item_info} return result except CartItemNotExist as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server_error')
def post_store_order_dao(self, connection, data): """주문 정보 추가 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 추가할 data Author: 고수희 Returns: 생성된 order_id 반환 Raises: 400, {'message': 'order create denied', 'errorMessage': 'unable_to_create'} : 주문 정보 추가 실패 500, {'message: server_error', 'errorMessage': 'server_error'} :서버 에러 발생 History: 2020-12-30(고수희): 초기 생성 """ sql = """ INSERT INTO orders ( order_number , sender_name , sender_phone , sender_email , recipient_name , recipient_phone , address1 , address2 , post_number , user_id , delivery_memo_type_id , total_price ) VALUES ( CONCAT(DATE_FORMAT(now(), '%%Y%%m%%d'),(LPAD(%(today)s,6,0)),(LPAD(0,3,0))) , %(sender_name)s , %(sender_phone)s , %(sender_email)s , %(recipient_name)s , %(recipient_phone)s , %(address1)s , %(address2)s , %(post_number)s , %(user_id)s , %(delivery_memo_type_id)s , %(total_price)s ); """ try: with connection.cursor() as cursor: cursor.execute(sql, data) result = cursor.lastrowid if not result: raise OrderCreateDenied('unable_to_create') return result except OrderCreateDenied as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server_error')
def post_store_order_item_dao(self, connection, data): """주문 상품 추가 Args: connection: 데이터베이스 연결 객체 data : 서비스 레이어에서 넘겨 받아 추가할 data Author: 고수희 Returns: 생성된 order_item의 id 반환 Raises: 400, {'message': 'order item create denied', 'errorMessage': 'unable_to_create'} : 주문 상품 추가 실패 500, {'message: server_error', 'errorMessage': 'server_error'} :서버 에러 발생 History: 2020-12-30(고수희): 초기 생성 """ sql = """ INSERT INTO order_items ( product_id , stock_id , quantity , order_id , cart_id , order_detail_number , order_item_status_type_id , original_price , discounted_price , sale ) VALUES ( %(product_id)s , %(stock_id)s , %(quantity)s , %(order_id)s , %(cart_id)s , CONCAT('B', DATE_FORMAT(now(), '%%Y%%m%%d'),(LPAD(%(today)s,6,0)),(LPAD(1,3,0))) , %(order_item_status_type_id)s , %(original_price)s , %(discounted_price)s , %(sale)s ); """ # TODO insert로 꺼내서 넣기 try: with connection.cursor() as cursor: cursor.execute(sql, data) result = cursor.lastrowid if not result: raise OrderItemCreateDenied('unable_to_create') return result except OrderItemCreateDenied as e: traceback.print_exc() raise e except Exception: traceback.print_exc() raise ServerError('server error')