def status_change_temporarily_closed(self, seller_id, session):
update_row = session.execute(
text("""
UPDATE
sellers
SET
seller_status_id = 3
WHERE
id = :id
"""), {
'id': seller_id
}).rowcount
if update_row == 0:
raise NoAffectedRowException(
500, 'status_change_temporarily_closed update error')
history_row = session.execute(
text("""
INSERT INTO seller_status_histories (
update_time,
seller_status_id,
seller_id
) VALUES (
now(),
3,
:seller_id
)
"""), {
'seller_id': seller_id
}).rowcount
if history_row == 0:
raise NoAffectedRowException(
500, 'status_change_temporarily_closed insert error')
def update_seller_information_master(self, seller, session):
# 마스터가 셀러정보를 업데이트할 때
update_row = session.execute(
text("""
UPDATE
sellers
SET
image = :image,
background_image = :background_image,
simple_introduce = :simple_introduce,
detail_introduce = :detail_introduce,
brand_crm_open = :brand_crm_open,
brand_crm_end = :brand_crm_end,
is_brand_crm_holiday = :is_brand_crm_holiday,
zip_code = :zip_code,
address = :address,
detail_address = :detail_address,
delivery_information = :delivery_information,
refund_exchange_information = :refund_exchange_information,
seller_status_id = :seller_status_id,
brand_name_korean = :brand_name_korean,
brand_name_english = :brand_name_english,
brand_crm_number = :brand_crm_number,
seller_property_id = :seller_property_id
WHERE
id = :id
"""), seller).rowcount
# update 성공하면 해당하는 row 의 수 반환 실패하면 0 반환
if update_row == 0:
raise NoAffectedRowException(
500, 'update_seller_information seller update error')
def update_option(self, options, session):
# 옵션 업데이트 하기
delete_option = session.execute(
text("""
DELETE FROM
options
WHERE
product_id = :product_id
"""), {
'product_id': options[0]['product_id']
}).rowcount
if delete_option == 0:
raise NoAffectedRowException(500, 'update_option delete error')
for idx, option in enumerate(options):
insert_option = session.execute(
text("""
INSERT INTO options (
product_id,
color_id,
size_id,
is_inventory_manage,
count,
ordering
) VALUES (
:product_id,
:color_id,
:size_id,
:is_inventory_manage,
:count,
:ordering
)
"""), {
'product_id': option['product_id'],
'color_id': option['color_id'],
'size_id': option['size_id'],
'is_inventory_manage': option['is_inventory_manage'],
'count': option['count'],
'ordering': idx + 1
}).rowcount
if insert_option == 0:
raise NoAffectedRowException(500, 'update_option insert error')
def update_sub_image(self, image_list, session):
# 상세페이지 서브 이미지 수정
image_data = session.execute(
text("""
SELECT
*
FROM sub_images
WHERE
product_id = :product_id
"""), {
'product_id': image_list[0]['product_id']
}).fetchall()
if image_data is not None:
delete_row = session.execute(
text("""
DELETE FROM
sub_images
WHERE
product_id = :product_id
"""), {
'product_id': image_list[0]['product_id']
}).rowcount
if delete_row == 0:
raise NoAffectedRowException(500,
'update_sub_image delete error')
for image in image_list:
insert_image = session.execute(
text("""
INSERT INTO sub_images (
image,
product_id
) VALUES (
:image,
:product_id
)
"""), image).rowcount
if insert_image == 0:
raise NoAffectedRowException(500,
'update_sub_image insert error')
def update_manager_information(self, managers, seller_id, session):
# 담당자 정보 업데이트 하기
delete_row = session.execute(
text("""
DELETE FROM
manager_informations
WHERE
seller_id = :seller_id
"""), {
'seller_id': seller_id
}).rowcount
# delete 성공하면 해당하는 row 의 수 반환 실패하면 0 반환
if delete_row == 0:
raise NoAffectedRowException(
500,
'update_seller_information manager information delete error')
for manager in managers:
# 담당자 데이터 넣기
manager_row = session.execute(
text("""
INSERT INTO manager_informations(
name,
phone_number,
email,
seller_id,
ordering
) VALUES (
:name,
:phone_number,
:email,
:seller_id,
:ordering
)
"""), manager).rowcount
if manager_row == 0:
raise NoAffectedRowException(
500,
'update_seller_information manager information insert error'
)
def status_change_closed_store(self, seller_id, session):
# 셀러상태 퇴점으로 변경
update_row = session.execute(
text("""
UPDATE
sellers
SET
seller_status_id = 5,
is_delete = True
WHERE
id = :id
"""), {
'id': seller_id
}).rowcount
if update_row == 0:
raise NoAffectedRowException(
500, 'status_change_closed_store update error')
# 셀러 상태 히스토리 등록 하기
history_row = session.execute(
text("""
INSERT INTO seller_status_histories (
update_time,
seller_status_id,
seller_id
) VALUES (
now(),
5,
:seller_id
)
"""), {
'seller_id': seller_id
}).rowcount
if history_row == 0:
raise NoAffectedRowException(
500, 'status_change_closed_store insert error')
def order_status_change_complete(self, order_id, session):
# 배송완료 처리 버튼 눌러서 배송완료로 상태 바꾸기
status_row = session.execute(
text("""
UPDATE
order_details
SET
order_status_id = 3
WHERE
order_id = :order_id
"""), {
'order_id': order_id
}).rowcount
if status_row == 0:
raise NoAffectedRowException(
500, 'order_status_change_complete update error')
# 배송 상태 변화 이력 저장하기
history_row = session.execute(
text("""
INSERT INTO order_status_histories (
update_time,
order_status_id,
order_id
) VALUES (
now(),
3,
:order_id
)
"""), {
'order_id': order_id
}).rowcount
if history_row == 0:
raise NoAffectedRowException(
500, 'order_status_change_complete insert error')
def update_phone_number(self, data, session):
# 핸드폰 번호 수정하기
update_row = session.execute(
text("""
UPDATE
orders
SET
phone_number = :phone_number
WHERE
id = :order_id
"""), data).rowcount
if update_row == 0:
raise NoAffectedRowException(500,
'update_phone_number update error')
def insert_data_sub_image(self, image, session):
# 서브 이미지 등록하기
image = session.execute(
text("""
INSERT INTO sub_images (
image,
product_id
) VALUES (
:image,
:product_id
)
"""), image).rowcount
if image == 0:
raise NoAffectedRowException(500,
'insert_data_sub_image insert error')
def change_option_inventory(self, order_data, session):
# 옵션의 재고 수량 수정하기
option = session.execute(
text("""
SELECT
id,
count,
is_inventory_manage
FROM options
WHERE
size_id = :size_id
AND
color_id = :color_id
AND
product_id = :product_id
"""), order_data).fetchone()
if option is None:
raise NoDataException(500, 'change_option_inventory select error')
if option['is_inventory_manage'] is True:
# 재고관리여부가 True 이면 재고 수량 수정하기
option_row = session.execute(
text("""
UPDATE
options
SET
count = :count
WHERE
id = :id
"""), {
'id': option['id'],
'count': int(option['count']) - order_data['count']
}).rowcount
if option_row == 0:
raise NoAffectedRowException(
500, 'change_option_inventory update error')
return option['id']
def insert_data_option(self, option, session):
# 옵션 데이터 등록하기
option = session.execute(
text("""
INSERT INTO options (
product_id,
color_id,
size_id,
is_inventory_manage,
count,
ordering
) VALUES (
:product_id,
:color_id,
:size_id,
:is_inventory_manage,
:count,
:ordering
)
"""), option).rowcount
if option == 0:
raise NoAffectedRowException(500,
'insert_data_option insert error')
def insert_seller(self, seller, session):
# 셀러 정보 등록하기
seller_id = session.execute(
text("""
INSERT INTO sellers (
account,
password,
brand_name_korean,
brand_name_english,
brand_crm_number,
seller_property_id
) VALUES (
:account,
:password,
:brand_name_korean,
:brand_name_english,
:brand_crm_number,
:seller_property_id
)
"""), seller).lastrowid
if seller_id is None:
raise NoAffectedRowException(500, 'insert_seller insert error')
# 담당자 정보 입력하기
manager_row = session.execute(
text("""
INSERT INTO manager_informations (
phone_number,
seller_id,
ordering
) VALUES (
:phone_number,
:seller_id,
1
)
"""), {
'seller_id': seller_id,
'phone_number': seller['phone_number']
}).rowcount
if manager_row == 0:
raise NoAffectedRowException(
500, 'insert_seller manager information insert error')
# 계정 이력 관리
history_row = session.execute(
text("""
INSERT INTO seller_status_histories (
update_time,
seller_status_id,
seller_id
) VALUES (
now(),
1,
:seller_id
)
"""), {
'seller_id': seller_id
}).rowcount
if history_row == 0:
raise NoAffectedRowException(
500, 'insert_seller status history insert error')
return seller_id
def insert_product_data(self, product_data, session):
# 상품 등록하기
product_id = session.execute(
text("""
INSERT INTO products (
name,
seller_id,
is_sell,
is_display,
is_discount,
sub_categories_id,
price,
discount_rate,
discount_start_date,
discount_end_date,
simple_information,
main_image,
detail,
minimum_sell_count,
maximum_sell_count,
manufacturer,
manufacture_date,
origin
) VALUES (
:name,
:seller_id,
:is_sell,
:is_display,
:is_discount,
:sub_categories_id,
:price,
:discount_rate,
:discount_start_date,
:discount_end_date,
:simple_information,
:main_image,
:detail,
:minimum_sell_count,
:maximum_sell_count,
:manufacturer,
:manufacture_date,
:origin
)
"""), product_data).lastrowid
if product_id is None:
raise NoAffectedRowException(500,
'insert_product_data insert error')
# 등록한 상품 Id 를 받아서 그 상품의 code_number 등록하기
code = session.execute(
text("""
UPDATE
products
SET
code_number = :code_number
WHERE
id = :product_id
"""), {
'code_number': int(product_id) * 100,
'product_id': product_id
}).rowcount
if code == 0:
raise NoAffectedRowException(
500, 'insert_product_data code number update error')
# 상품 등록 이력관리
record = session.execute(
text("""
INSERT INTO product_records (
seller_id,
product_id,
product_name,
price,
discount_rate,
start_time,
close_time,
main_image
) VALUES (
:seller_id,
:product_id,
:name,
:price,
:discount_rate,
now(),
:close_time,
:main_image
)
"""), {
'seller_id': product_data['seller_id'],
'product_id': product_id,
'name': product_data['name'],
'price': product_data['price'],
'discount_rate': product_data['discount_rate'],
'main_image': product_data['main_image'],
'close_time': product_data['close_time']
}).rowcount
if record == 0:
raise NoAffectedRowException(
500, 'insert_product_data record insert error')
return product_id
def update_product_data(self, product_data, session):
# 상품데이터 업데이트하기
product = session.execute(
text("""
UPDATE
products
SET
name = :name,
seller_id = :seller_id,
is_sell = :is_sell,
is_display = :is_display,
is_discount = :is_discount,
sub_categories_id = :sub_categories_id,
price = :price,
discount_rate = :discount_rate,
discount_start_date = :discount_start_date,
discount_end_date = :discount_end_date,
simple_information = :simple_information,
main_image = :main_image,
detail = :detail,
minimum_sell_count = :minimum_sell_count,
maximum_sell_count = :maximum_sell_count,
manufacturer = :manufacturer,
manufacture_date = :manufacture_date,
origin = :origin
WHERE
id = :product_id
"""), product_data).rowcount
if product == 0:
raise NoAffectedRowException(500,
'update_product_data update error')
# 바로 전 이력 close_time 현재 시간으로 수정하기
update_prerecord = session.execute(
text("""
UPDATE
product_records
SET
close_time = now()
WHERE
product_id = :product_id
AND
close_time = :close_time
"""), product_data).rowcount
if update_prerecord == 0:
raise NoAffectedRowException(
500, 'update_product_data pre-record update error')
# 상품 이력관리
product_record = session.execute(
text("""
INSERT INTO product_records (
seller_id,
product_id,
product_name,
price,
discount_rate,
start_time,
close_time,
main_image
) VALUES (
:seller_id,
:product_id,
:name,
:price,
:discount_rate,
now(),
:close_time,
:main_image
)
"""), product_data).rowcount
if product_record == 0:
raise NoAffectedRowException(
500, 'update_product_data record insert error')
def insert_order_data(self, order_data, option_id, seller_id, session):
# 주문 정보 데이터에 저장하기
order_id = session.execute(
text("""
INSERT INTO orders (
user_name,
phone_number,
zip_code,
address,
detail_address
) VALUES (
:user_name,
:phone_number,
:zip_code,
:address,
:detail_address
)
"""), order_data).lastrowid
if order_id is None:
raise NoAffectedRowException(500, 'insert_order_data insert error')
# 주문 아이디 이용하여 주문 번호 등록 하기
order_number = session.execute(
text("""
UPDATE
orders
SET
number = :number
WHERE
id = :id
"""), {
'id': order_id,
'number':
datetime.today().strftime("%Y%m%d") + '%05d' % order_id
}).rowcount
if order_number == 0:
raise NoAffectedRowException(500, 'insert_order_data update error')
# 주문 상세 정보 저장하기
order_detail_row = session.execute(
text("""
INSERT INTO order_details (
order_id,
product_id,
detail_number,
count,
order_status_id,
option_id,
total_price,
seller_id
) VALUES (
:order_id,
:product_id,
:detail_number,
:count,
1,
:option_id,
:total_price,
:seller_id
)
"""), {
'order_id': order_id,
'product_id': order_data['product_id'],
'detail_number':
datetime.today().strftime("%Y%m%d") + '%06d' % order_id,
'count': order_data['count'],
'seller_id': seller_id,
'option_id': option_id,
'total_price': order_data['total_price']
}).rowcount
if order_detail_row == 0:
raise NoAffectedRowException(
500, 'insert_order_data detail insert error')
# 주문 상태 히스토리 저장하기
order_history = session.execute(
text("""
INSERT INTO order_status_histories (
update_time,
order_status_id,
order_id
) VALUES (
now(),
1,
:order_id
)
"""), {
'order_id': order_id
}).rowcount
if order_history == 0:
raise NoAffectedRowException(
500, 'insert_order_data history insert error')