def get_history_dao(self, connection, account_id):
"""셀러 상세 히스토리 조회
Args:
connection : 데이터베이스 연결 객체
account_id : 해당 셀러 ID
"""
account_id = account_id
sql = """
SELECT
history.id AS id,
history.created_at AS updated_at,
status_type.name AS seller_status,
account.username AS 'updater_name
FROM
seller_histories AS history
INNER JOIN accounts AS account
ON `history`.seller_id = `account`.id
INNER JOIN seller_status_types AS status_type
ON `history`.seller_status_type_id = `status_type`.id
WHERE
account.id = %s;
"""
# ORDER BY
# `history`.id DESC;
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
cursor.execute(sql, account_id)
result = cursor.fetchall()
if not result:
raise SellerNotExist('seller_does_not_exist')
return result
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 get_seller_info(self, connection, account_id):
""" 셀러 정보 조회
Args:
connection : 데이터베이스 연결 객체
account_id : 서비스에서 넘겨 받은 객체
Returns:
result get_seller_info
Raises:
500, {'message': 'database_error', 'errorMessage': '서버에 알 수 없는 에러가 발생했습니다.'} : 데이터베이스 에러
History:
2020-12-28(이영주): 초기 생성
"""
account_id = account_id
sql = """
SELECT
seller.`account_id` AS 'account_id',
seller.`profile_image_url` AS 'profile_image_url',
seller.`seller_status_type_id` AS 'seller_status_type_id',
seller.`seller_attribute_type_id` AS 'seller_attribute_type_id',
seller.`name` AS 'seller_name',
seller.`english_name` AS 'seller_english_name',
seller.`background_image_url` AS 'background_image_url',
seller.`seller_title` AS 'seller_title',
seller.`seller_discription` AS 'seller_discription',
seller.`service_center_number` AS 'service_center_number'
`account`.username AS 'username',
FROM
sellers AS seller
INNER JOIN accounts AS `account`
ON seller.account_id = `account`.id
WHERE
seller.is_deleted = 0 # 고정 값
AND seller.account_id = %s; # 변수 (화면 입력 값)
"""
# ,seller.`contact_phone` AS 'contact_phone'
# ,seller.`contact_name` AS 'contact_name'
# ,seller.`contact_email` AS 'contact_email'
# INNER JOIN additional_contacts AS `additional_contact`
# ON seller.account_id = `additional_contact`.seller_id
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
cursor.execute(sql, account_id)
result = cursor.fetchall()
if not result:
raise SellerNotExist('seller_does_not_exist')
return result
def patch_seller_password(self, connection, data):
""" 셀러 패스워드 변경
Args:
connection : 데이터베이스 연결 객체
data : service 에서 넘겨받은 dict 객체
"""
sql = """
UPDATE
accounts
SET
password = %(password)s
WHERE
is_deleted = 0 # 고정 값
AND accounts.id = %(account_id)s;
"""
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
result = cursor.execute(sql, data)
if result == 0:
raise SellerNotExist('unable_to_update')
return result
def Patch_seller_status(self, connection, data):
"""셀러 상태 수정
Args:
connection : 데이터베이스 연결 객체
data : service 에서 넘겨받은 dict 객체
"""
sql = """
UPDATE
sellers
SET
seller_status_type_id = %(seller_status_type_id)s
WHERE
is_deleted = 0 # 고정 값
AND sellers.account_id = %(account_id)s;
"""
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
result = cursor.execute(sql, data)
if result == 0:
raise SellerNotExist('unable_to_update')
return result
def get_add_contact_info(self, connection, account_id):
""" 추가 담당자 정보 조회
Args:
connection : 데이터베이스 연결 객체
account_id : 서비스에서 넘겨 받은 객체
Returns:
result get_add_contact_info
Raises:
500, {'message': 'database_error', 'errorMessage': '서버에 알 수 없는 에러가 발생했습니다.'} : 데이터베이스 에러
History:
2021-01-03(이영주): 초기 생성
"""
account_id = account_id
sql = """
SELECT
`id` AS 'id',
`name` AS 'additional_contact_name',
`phone` AS 'additional_contact_phone',
`email` AS 'additional_contact_email',
`seller_id` AS 'seller_id',
`order_index` AS 'order_index'
FROM
additional_contacts
WHERE
is_deleted = 0 # 고정 값
AND sellers.account_id = %(account_id)s;
"""
# ORDER BY
# order_index ASC;
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
cursor.execute(sql, account_id)
result = cursor.fetchall()
if not result:
raise SellerNotExist('seller_does_not_exist')
return result
def get_seller_list(self, connection, offset):
sql = """
SELECT
account.id AS account_id
,account.username AS username
,seller.english_name AS seller_english_name
,seller.name AS seller_name
,seller.contact_name AS contact_name
,seller.contact_phone AS contact_phone
,seller.contact_email AS contact_email
,seller_attribute_type.name AS seller_attribute_type
,seller_status_type.name AS seller_status_type
,seller.updated_at AS updated_at
FROM
sellers AS seller
INNER JOIN accounts AS `account`
ON seller.account_id = `account`.id
INNER JOIN seller_status_types AS seller_status_type
ON seller.seller_status_type_id = seller_status_type.id
INNER JOIN seller_attribute_types AS seller_attribute_type
ON seller.seller_attribute_type_id = seller_attribute_type.id
WHERE
seller.is_deleted = 0
ORDER BY account.id DESC
LIMIT 0, %s
"""
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
cursor.execute(sql, offset)
sellers = cursor.fetchall()
if not sellers:
raise SellerNotExist('seller does not exist')
return sellers
def get_seller_search(self, connection, data):
total_count_sql = """
SELECT
COUNT(*) AS total_count
"""
product_registration_number = """
SELECT
COUNT(*) AS products_count
FROM
seller AS seller
INNER JOIN products AS product
ON seller.account_id = product.seller_id
WHERE
seller.is_deleted = 0
AND account.permission_type_id = 2
"""
sql = """
SELECT
account.id AS account_id
,account.username AS username
,seller.english_name AS seller_english_name
,seller.name AS seller_name
,seller.contact_name AS contact_name
,seller.contact_phone AS contact_phone
,seller.contact_email AS contact_email
,seller_attribute_type.name AS seller_attribute_type
,seller_status_type.name AS seller_status_type
,seller.updated_at AS updated_at
"""
extra_sql = """
FROM
sellers AS seller
INNER JOIN accounts AS `account`
ON seller.account_id = `account`.id
INNER JOIN seller_status_types AS seller_status_type
ON seller.seller_status_type_id = seller_status_type.id
INNER JOIN seller_attribute_types AS seller_attribute_type
ON seller.seller_attribute_type_id = seller_attribute_type.id
WHERE
seller.is_deleted = 0
AND account.permission_type_id = 2
"""
filter_sql = ' ORDER BY `account`.id DESC LIMIT %(offset)s, %(limit)s'
if data['account_id']:
sql += ' AND account.id = %(account_id)s '
if data['username']:
sql += ' AND account.username = %(username)s '
if data['seller_english_name']:
sql += ' AND seller.english_name = %(seller_english_name)s'
if data['seller_name']:
sql += ' AND seller.name = %(seller_name)s'
if data['contact_name']:
sql += ' AND seller.contact_name = %(contact_name)s'
if data['seller_status_type_name']:
sql += ' AND seller_status_type.name = %(seller_status_type_name)s'
if data['contact_phone']:
sql += ' AND seller.contact_phone = %(contact_phone)s'
if data['contact_email']:
sql += ' AND seller.contact_email = %(contact_email)s'
if data['seller_attribute_type_name']:
sql += ' AND seller_attribute_type.name = %(seller_attribute_type_name)s'
if data['start_date'] and data['end_date']:
sql += """
AND seller.updated_at
BETWEEN CONCAT(%(start_date)s, "00:00:00")
AND CONCAT(%(end_date)s, "23:59:59")
"""
sql += extra_sql + filter_sql
total_count_sql += extra_sql
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
cursor.execute(sql, data)
sellers = cursor.fetchall()
if not sellers:
raise SellerNotExist('seller does not exist')
cursor.execute(total_count_sql, data)
count = cursor.fetchone()
return {
'seller_list': sellers,
'total_count': count['total_count']
}