def add_funds(self, user_id, password, add_value) -> (int, str):
try:
cursor = self.conn.cursor()
cursor.execute(
"SELECT password from \"user\" where user_id= (%s)",
(user_id, ))
row = cursor.fetchone()
if row is None:
return error.error_non_exist_user_id(user_id)
if row[0] != password:
return error.error_authorization_fail()
cursor.execute(
"UPDATE \"user\" SET balance = balance + (%s) WHERE user_id = (%s) ",
(add_value, user_id))
if cursor.rowcount == 0:
return error.error_non_exist_user_id(user_id)
self.conn.commit()
cursor.close()
except (Exception, psycopg2.DatabaseError) as e:
return 528, "{}".format(str(e)),
except BaseException as e:
return 530, "{}".format(str(e)),
return 200, "ok"
def receive_book(self, user_id: str, order_id: str) -> (int, str):
try:
print(0)
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
if not self.order_id_exist(order_id):
return error.error_invalid_order_id(order_id)
row = self.Session.query(New_order).filter(New_order.order_id == order_id).first()
# if row is None:
# return error.error_invalid_order_id(order_id)
if row.state != 2:
return error.error_cannot_receive_book()
row.state = 3
cursor = self.Session.query(New_order_detail.book_id, New_order_detail.count,
New_order_detail.price).filter(New_order_detail.order_id == order_id).all()
total_price = 0
for row4 in cursor:
count = row4[1]
price = row4[2]
total_price = total_price + price * count
row1 = self.Session.query(New_order).filter(New_order.order_id == order_id).first()
row3 = self.Session.query(User_store).filter(User_store.store_id == row1.store_id).first()
if row3 is None:
return error.error_non_exist_store_id(row1.store_id)
seller_id = row3.user_id
row5 = self.Session.query(User).filter(User.user_id == seller_id).first()
if row5 is None:
return error.error_non_exist_user_id(seller_id)
row5.balance += total_price
self.Session.commit()
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
def payment(self, user_id: str, password: str,
order_id: str) -> (int, str):
order2pay = session.query(Order_to_Pay).filter(
Order_to_Pay.order_id == order_id).first()
if order2pay is None:
return error.error_invalid_order_id(order_id)
buyer_id = order2pay.user_id
store_id = order2pay.store_id
if buyer_id != user_id:
return error.error_authorization_fail()
buyer = session.query(User).filter(User.user_id == buyer_id).first()
if buyer is None:
return error.error_non_exist_user_id(buyer_id)
balance = buyer.balance
if (password != buyer.password):
return error.error_authorization_fail()
store = session.query(Store).filter(Store.store_id == store_id).first()
if store is None:
return error.error_non_exist_store_id(store_id)
seller_id = store.user_id
if not user_id_exist(seller_id):
return error.error_non_exist_user_id(seller_id)
order_detail = session.query(Order_detail).filter(
Order_detail.order_id == order_id).all()
total_price = 0
for i in range(0, len(order_detail)):
book_id = order_detail[i].book_id
book = session.query(Store_detail).filter(
Store_detail.store_id == store_id,
Store_detail.book_id == book_id).first()
count = order_detail[i].count
price = book.price
total_price = total_price + price * count
if balance < total_price:
return error.error_not_sufficient_funds(order_id)
buyer.balance -= total_price
seller = session.query(User).filter(User.user_id == seller_id).first()
seller.balance += total_price
session.add(
Order(order_id=order_id,
user_id=buyer_id,
store_id=store_id,
paytime=datetime.now(),
status=0))
session.delete(order2pay)
session.commit()
return 200, "ok"
def cancel_order(self, user_id: str, password: str, order_id: str) -> (int, str):
try:
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
row = User.query.filter_by(user_id=user_id).first()
if password != row.password:
return error.error_authorization_fail()
buyer_balance = row.balance
row = New_Order.query.filter(New_Order.user_id == user_id, New_Order.order_id == order_id).first()
if row is None:
return error.error_invalid_order_id(order_id)
store_id = row.store_id
# 已经发货了就不能取消订单了，因此状态码只能是1才能主动取消订单
row = New_Order_Detail.query.filter_by(order_id=order_id).first()
if row.state != 1:
return error.error_cancel_order(order_id)
# 可以取消,则买家收钱，卖家减钱,状态码改为-1，表示订单关闭
cursor = New_Order_Detail.query.filter_by(order_id=order_id).all()
total_price = 0
for row in cursor:
count = row.count
price = row.price
total_price = total_price + price * count
row = User.query.filter_by(user_id=user_id).update({"balance": buyer_balance + total_price})
if row == 0:
return error.error_non_exist_user_id(user_id)
row = User_Store.query.filter_by(store_id=store_id).first()
if row is None:
return error.error_non_exist_store_id(store_id)
seller_id = row.user_id
row = User.query.filter_by(user_id=seller_id).first()
if row is None:
return error.error_non_exist_user_id(seller_id)
seller_balance = row.balance
row = User.query.filter_by(user_id=seller_id).update({"balance": seller_balance - total_price})
if row == 0:
return error.error_not_sufficient_funds(order_id)
# 将订单状态改为-1
row = New_Order_Detail.query.filter_by(order_id=order_id).update({"state": -1})
if row == 0:
return error.error_invalid_order_id(order_id)
db_session.commit()
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def payment(self, user_id: str, password: str, order_id: str) -> (int, str):
try:
# print('payment')
row1 = self.Session.query(New_order).filter(New_order.order_id == order_id).first()
if row1 is None:
return error.error_invalid_order_id(order_id)
buyer_id = row1.user_id
if buyer_id != user_id:
return error.error_authorization_fail()
row2 = self.Session.query(User).filter(User.user_id == buyer_id).first()
if row2 is None:
return error.error_non_exist_user_id(buyer_id)
if password != row2.password:
return error.error_authorization_fail()
row3 = self.Session.query(User_store).filter(User_store.store_id == row1.store_id).first()
if row3 is None:
return error.error_non_exist_store_id(row1.store_id)
seller_id = row3.user_id
if not self.user_id_exist(seller_id):
return error.error_non_exist_user_id(seller_id)
cursor = self.Session.query(New_order_detail.book_id, New_order_detail.count, New_order_detail.price).filter(New_order_detail.order_id == order_id).all()
# print('cccc')
# print(cursor)
total_price = 0
for row4 in cursor:
count = row4[1]
price = row4[2]
total_price = total_price + price * count
# print('balance = ', row2.balance, 'price = ', total_price)
if row2.balance < total_price:
return error.error_not_sufficient_funds(order_id)
row2.balance -= total_price
# 加钱的是卖家
# row5 = self.Session.query(User).filter(User.user_id == seller_id).first()
# if row5 is None:
# return error.error_non_exist_user_id(seller_id)
# row5.balance += total_price
# 修改订单状态
row6 = self.Session.query(New_order).filter(New_order.order_id == order_id).first()
if row6 is None:
return error.error_invalid_order_id(order_id)
row6.state = 1
self.Session.commit()
# row6 = self.Session.query(New_order).filter(New_order.order_id == order_id).first()
# print('改状态：', row6.state)
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e))
except BaseException as e:
# print('出错啦:', e)
return 530, "{}".format(str(e))
return 200, "ok"
def add_funds(self, user_id: str, password: str, add_value: int) -> (int, str):
try:
# cursor = self.conn.execute("SELECT password from user where user_id=?", (user_id,))
# row = cursor.fetchone()
row = User.query.filter_by(user_id=user_id).first()
if row is None:
return error.error_authorization_fail()
if row.password != password:
return error.error_authorization_fail()
balance = row.balance
# cursor = self.conn.execute(
# "UPDATE user SET balance = balance + ? WHERE user_id = ?",
# (add_value, user_id))
cursor = User.query.filter_by(user_id=user_id).update({"balance": balance + add_value})
if cursor == 0:
return error.error_non_exist_user_id(user_id)
db_session.commit()
# self.conn.commit()
# except sqlite.Error as e:
# return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def query_history_order(self, user_id, password) -> (int, str, dict):
try:
cursor = self.conn.cursor()
cursor.execute(
"SELECT password from \"user\" where user_id= (%s)",
(user_id, ))
row = cursor.fetchone()
if row is None:
code, message = error.error_non_exist_user_id(user_id)
return code, message, {}
if row[0] != password:
code, message = error.error_authorization_fail()
return code, message, {}
################
history_orders = {}
# query = "SELECT * FROM new_order join new_order_detail on new_order.order_id=new_order_detail.order_id where new_order.\"user_id\"={}".format(user_id)
# cursor.execute(query)
cursor.execute(
"select \"new_order\".order_id, \"new_order\".store_id, \"new_order\".pay, \"new_order\".deliver, \"new_order\".receive, \"new_order_detail\".book_id, \"new_order_detail\".count, \"new_order_detail\".price from \"new_order\" inner join \"new_order_detail\" on \"new_order\".order_id=\"new_order_detail\".order_id where \"new_order\".user_id=(%s)",
(user_id, ))
rows = cursor.fetchall()
if rows is not None:
for row in rows:
order_id = row[0]
book_id = row[5]
count = row[6]
price = row[7]
if (order_id not in history_orders.keys()):
store_id = row[1]
pay = row[2]
deliver = row[3]
receive = row[4]
if (receive):
status = "已收货"
elif (deliver):
status = "已发货"
elif (pay):
status = "已付款"
else:
status = "未付款"
history_orders[order_id] = {
"store_id": store_id,
"status": status,
"books": {
book_id: [price, count]
},
"amount": int(price) * int(count)
}
else:
history_orders[order_id]["books"][book_id] = [
price, count
]
history_orders[order_id]["amount"] += price * count
except (Exception, psycopg2.DatabaseError) as e:
traceback.print_exc()
return 528, "{}".format(str(e)), {},
except BaseException as e:
return 530, "{}".format(str(e)), {},
return 200, "ok", history_orders
def receive(self, user_id, order_id) -> (int, str):
try:
# if not self.user_id_exist(user_id):
# return error.error_non_exist_user_id(user_id)
# if not self.order_id_exist(order_id):
# return error.error_and_message(522,error.error_code[522].format(order_id))
if not self.buyer_order_exist(user_id, order_id):
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
if not self.order_id_exist(order_id):
return error.error_and_message(
522, error.error_code[522].format(order_id))
return error.error_and_message(
541, error.error_code[541].format(order_id, user_id))
if not self.deliver_flag_set(order_id):
return error.error_and_message(
542, error.error_code[542].format(order_id))
cursor = self.conn.cursor()
cursor.execute(
"UPDATE \"new_order\" SET receive=1 WHERE order_id=(%s)",
(order_id, ))
self.conn.commit()
except (Exception, psycopg2.DatabaseError) as e:
return 528, "{}".format(str(e)),
except BaseException as e:
return 530, "{}".format(str(e)),
return 200, "ok"
def add_book(self, user_id: str, store_id: str, book_id: str,
book_json_str: str, stock_level: int, title: str, author: str,
content: str, tag: str):
try:
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
if not self.store_id_exist(store_id):
return error.error_non_exist_store_id(store_id)
if self.book_id_exist(store_id, book_id):
return error.error_exist_book_id(book_id)
# self.conn.execute("INSERT into store(store_id, book_id, book_info, stock_level)"
# "VALUES (?, ?, ?, ?)", (store_id, book_id, book_json_str, stock_level))
# self.conn.commit()
store_tmp = Store(store_id=store_id,
book_id=book_id,
book_info=book_json_str,
stock_level=stock_level,
tag=tag,
author=author,
content=content,
title=title)
db_session.add(store_tmp)
db_session.commit()
except BaseException as e:
print(str(e))
return 530, "{}".format(str(e))
return 200, "ok"
def new_order(self, user_id: str, store_id: str,
id_and_count: [(str, int)]) -> (int, str, str): # 箭头后表示返回类型
order_id = ""
if not user_id_exist(user_id):
return error.error_non_exist_user_id(user_id) + (order_id, )
if not store_id_exist(store_id):
return error.error_non_exist_store_id(store_id) + (order_id, )
uid = "{}_{}_{}".format(user_id, store_id, str(uuid.uuid1()))
for book_id, count in id_and_count:
store_detail = session.query(Store_detail).filter(
Store_detail.store_id == store_id,
Store_detail.book_id == book_id).first()
if (store_detail is None):
return error.error_non_exist_book_id(book_id) + (order_id, )
stock_level = store_detail.stock_level
if stock_level < count:
return error.error_stock_level_low(book_id) + (order_id, )
store_detail.stock_level -= count
session.add(
Order_detail(order_id=uid, book_id=book_id, count=count))
session.add(
Order_to_Pay(order_id=uid,
user_id=user_id,
store_id=store_id,
paytime=datetime.now()))
session.commit()
order_id = uid
return 200, "ok", order_id
def ship(self, user_id, order_id) -> (int, str):
try:
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
cursor = self.conn.execute(
"SELECT store_id,condition FROM new_order WHERE "
"order_id='%s';" % (order_id))
row = cursor.fetchone()
if row is None:
return error.error_invalid_order_id(order_id)
store_id = row[0]
condition = row[1]
cursor = self.conn.execute(
"SELECT user_id FROM user_store WHERE "
"store_id ='%s' AND user_id='%s';" % (store_id, user_id))
row = cursor.fetchone()
if row is None:
return error.error_non_exist_store_id(store_id)
if condition != "paid":
return error.error_unshippable_order(order_id)
self.conn.execute("UPDATE new_order set condition ='shipped',update_time=CURRENT_TIMESTAMP WHERE order_id = '%s';"%(order_id,))
self.conn.commit()
#execute_job(order_id, 1)
print("物品已发货")
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e))
return 200, "ok"
def deliver(self, user_id: str, order_id: str) -> (int, str):
try:
if not self.order_id_exist(order_id):
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
else:
return error.error_and_message(
522, error.error_code[522].format(order_id))
cursor = self.conn.cursor()
cursor.execute(
"select store_id,pay from \"new_order\" where order_id=(%s)",
(order_id, ))
row = cursor.fetchone()
store_id = row[0]
pay = row[1]
if not self.user_store_exist(user_id, store_id):
return error.error_and_message(
521, error.error_code[521].format(user_id, store_id))
if not pay == 1:
return error.error_and_message(523, error.error_code[523])
cursor.execute(
"UPDATE \"new_order\" SET deliver=1 WHERE order_id=(%s)",
(order_id, ))
self.conn.commit()
except (Exception, psycopg2.DatabaseError) as e:
traceback.print_exc()
return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def cancel_order(self, buyer_id: str, order_id: str) -> (int, str):
try:
# 不存在该用户
row = self.Session.query(User.password, User.balance).filter(User.user_id == buyer_id).first()
if row is None:
return error.error_non_exist_user_id(buyer_id)
# 不存在该订单
row = self.Session.query(New_order).filter(New_order.order_id == order_id,
New_order.user_id == buyer_id).first()
# store_id = cursor.store_id
print('可以')
if row is None:
# print(order_id)
return error.error_invalid_order_id(order_id)
# 用户主动删除该订单
if row.state == 2 or row.state == 3:
return error.error_already_delivered()
# 未付款，直接取消订单，加回库存
if row.state == 0 or row.state == 1:
cursor = self.Session.query(New_order).filter(New_order.order_id == order_id,
New_order.user_id == buyer_id).first()
# cursor1 = self.Session.query(New_order_detail).filter(New_order_detail.order_id == order_id).first()
# count = cursor.count
store_id = cursor.store_id
cursor1 = self.Session.query(New_order_detail).filter(New_order_detail.order_id == order_id).all()
for each_row in cursor1:
book_id = each_row.book_id
count = each_row.count
stock_level = self.Session.query(Store.stock_level).filter(Store.store_id == store_id,
Store.book_id == book_id).first()[0]
stock_level += count
if row.state == 0:
self.Session.query(New_order).filter(New_order.order_id == order_id,
New_order.user_id == buyer_id).delete()
self.Session.query(New_order_detail).filter(New_order_detail.order_id == order_id).delete()
self.Session.commit()
return 200, "ok"
if row.state == 1:
# 已付款,退款,加回库存
cursor2 = self.Session.query(New_order_detail.book_id, New_order_detail.count, New_order_detail.price)\
.filter(New_order_detail.order_id == order_id).all()
total_price = 0
for row4 in cursor2:
count = row4[1]
price = row4[2]
total_price = total_price + price * count
row5 = self.Session.query(User).filter(User.user_id == buyer_id).first()
row5.balance += total_price
self.Session.query(New_order).filter(New_order.order_id == order_id,
New_order.user_id == buyer_id).delete()
self.Session.query(New_order_detail).filter(New_order_detail.order_id == order_id).delete()
self.Session.commit()
return 200, "ok"
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e))
except BaseException as e:
print(e)
return 530, "{}".format(str(e))
def add_book(self, user_id: str, store_id: str, book_id: str, book_json_str: str, stock_level: int):
book_json = json.loads(book_json_str)
# try:
if not user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
if not store_id_exist(store_id):
return error.error_non_exist_store_id(store_id)
if book_id_exist(store_id, book_id):
return error.error_exist_book_id(book_id)
book_one = Book(book_id = book_id,title = book_json.get("title"))
if not session.query(Book).filter(Book.book_id==book_id).first():
session.add(book_one)
session.commit()
store_detail_one=Store_detail(
store_id = store_id,
book_id = book_id,
stock_level = stock_level,
price = book_json.get("price")
)
session.add(store_detail_one)
session.commit()
# except BaseException as e:
# return 530, "{}".format(str(e))
return 200, "ok"
def view_comments(self, user_id: str, store_id: str,
book_id: str) -> (int, str, [str]):
try:
comments = []
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id) + ("null", )
if not self.store_id_exist(store_id):
return error.error_non_exist_store_id(store_id) + ("null", )
self.cursor.execute(
"SELECT * FROM store "
"WHERE store_id = %s AND book_id = %s", (store_id, book_id))
row = self.cursor.fetchone()
if row is None:
return error.error_non_exist_book_in_store(store_id) + (
"null", )
self.cursor.execute(
"SELECT comment FROM book_comment "
"WHERE store_id = %s AND book_id = %s", (store_id, book_id))
row = self.cursor.fetchall()
for i in row:
comments.append(i[0])
except pymysql.Error as e:
return 528, "{}".format(str(e)), "null"
except BaseException as e:
return 530, "{}".format(str(e)), "null"
return 200, "ok", comments
def add_funds(self, user_id: str, password: str,
add_value: int) -> (int, str):
try:
cursor = self.conn.execute(
"SELECT password from usr where user_id='%s';" % (user_id, ))
row = cursor.fetchone()
if row is None:
return error.error_authorization_fail()
if row[0] != password:
return error.error_authorization_fail()
cursor = self.conn.execute(
"UPDATE usr SET balance = balance + %d WHERE user_id = '%s';" %
(add_value, user_id))
if cursor.rowcount == 0:
return error.error_non_exist_user_id(user_id)
self.conn.commit()
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e))
except BaseException as e:
# print(e)
return 530, "{}".format(str(e))
return 200, "ok"
def payment(self, user_id: str, password: str,
order_id: str) -> (int, str):
try:
self.cursor.callproc('payment',
(user_id, password, order_id, 'flag', 'msg'))
self.conn.commit()
self.cursor.execute("select @_payment_3,@_payment_4")
return_value = self.cursor.fetchone()
# print(return_value)
if return_value[0] == 0:
return 200, "ok"
if return_value[0] in [1, 5, 9]:
return error.error_invalid_order_id(return_value[1])
if return_value[0] == 2 or return_value[0] == 4:
return error.error_authorization_fail()
if return_value[0] == 3 or return_value[0] == 6:
return error.error_non_exist_user_id(return_value[1])
if return_value[0] == 7:
return error.error_not_sufficient_funds(return_value[1])
if return_value[0] == 8:
return 528, ""
except pymysql.Error as e:
return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def cancel_order(self, user_id: str, password: str,
order_id: str) -> (int, str):
try:
self.cursor.callproc('man_cancel',
(user_id, password, order_id, 'flag', 'msg'))
self.conn.commit()
self.cursor.execute("select @_man_cancel_3,@_man_cancel_4")
return_value = self.cursor.fetchone()
# print(return_value)
if return_value[0] == 0:
return 200, "ok"
if return_value[0] == 1:
return error.error_non_exist_user_id(return_value[1])
if return_value[0] == 2:
return error.error_authorization_fail()
if return_value[0] in [3, 4, 5, 6]:
return error.error_invalid_order_id(order_id)
if return_value[0] == 7:
return 528, "{}".format(return_value[1])
except pymysql.Error as e:
# print(str(e))
return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def send_item(self, user_id: str, order_id: str) -> (int, str):
try:
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
#检查发货人信息
cursor = self.conn.execute(
"SELECT store_id,state FROM paid_order WHERE order_id = %s",
(order_id, ))
row = self.conn.fetchone()
if row is None:
return error.error_invalid_order_id(order_id)
store_id = row['store_id']
state = row['state']
cursor = self.conn.execute(
"SELECT user_id FROM user_store WHERE store_id = %s",
(store_id, ))
row = self.conn.fetchone()
seller_id = row['user_id']
if seller_id != user_id:
return error.error_authorization_fail()
if state == '发货中':
return error.error_repeatsend()
self.conn.execute(
"UPDATE paid_order SET state = %s "
"WHERE order_id = %s ", ('发货中', order_id))
#self.conn.commit()
except pg.Error as e:
return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def add_book(self, user_id: str, store_id: str, book_id: str,
book_json_str: str, stock_level: int):
book_json = json.loads(book_json_str)
if not user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
if not store_id_exist(store_id):
return error.error_non_exist_store_id(store_id)
if book_id_exist(store_id, book_id):
return error.error_exist_book_id(book_id)
book_one = Book(book_id=book_id,
title=book_json.get("title"),
author=book_json.get("author"),
publisher=book_json.get("publisher"),
original_title=book_json.get("original_title"),
translator=book_json.get("translator"),
pub_year=book_json.get("pub_year"),
pages=book_json.get("pages"),
original_price=book_json.get("price"),
currency_unit=book_json.get("currency_unit"),
binding=book_json.get("binding"),
isbn=book_json.get("isbn"),
author_intro=book_json.get("author_intro"),
book_intro=book_json.get("book_intro"),
content=book_json.get("content"),
tags=book_json.get("tags"),
picture=book_json.get("picture"))
# if not session.query(Book).filter(Book.book_id == book_id).first():
# session.add(book_one)
store_detail_one = Store_detail(store_id=store_id,
book_id=book_id,
stock_level=stock_level,
price=book_json.get("price"))
session.add(store_detail_one)
session.commit()
return 200, "ok"
def new_order(self, user_id: str, store_id: str, id_and_count: [(str, int)]) -> (int, str, str):
order_id = ""
try:
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id) + (order_id, )
if not self.store_id_exist(store_id):
return error.error_non_exist_store_id(store_id) + (order_id, )
uid = "{}_{}_{}".format(user_id, store_id, str(uuid.uuid1()))
order_id = uid
for book_id, count in id_and_count:
row = self.Session.query(Store).filter(Store.store_id==store_id,Store.book_id==book_id).first()
if row is None:
return error.error_non_exist_book_id(book_id) + (order_id, )
book_info_json = json.loads(row.book_info)
price = book_info_json.get("price")
if row.stock_level < count:
return error.error_stock_level_low(book_id) + (order_id,)
else:
row.stock_level -= count
new_order = New_order_detail(order_id=uid, book_id=book_id, count=count, price=price)
self.Session.add(new_order)
# print('插入订单')
# 插入订单更新：添加两个属性
new_ord = New_order(order_id=uid, store_id=store_id, user_id=user_id, state=0, create_time=time.time(), delivery_time=0 )
self.Session.add(new_ord)
self.Session.commit()
except sqlalchemy.exc.IntegrityError as e:
logging.info("528, {}".format(str(e)))
return 528, "{}".format(str(e)), ""
except BaseException as e:
logging.info("530, {}".format(str(e)))
return 530, "{}".format(str(e)), ""
return 200, "ok", order_id
def add_funds(self, user_id, password, add_value) -> (int, str):
try:
self.cursor = self.conn.cursor()
self.cursor.execute(
"SELECT password from usr where user_id='%s'" % (user_id, ))
row = self.cursor.fetchone()
#先检验查询是不是空值
if row is None:
return error.error_authorization_fail()
#再检验密码是不是正确
if row[0] != password:
return error.error_authorization_fail()
#增加用户的balance
self.cursor.execute(
"UPDATE usr SET balance = balance + %d WHERE user_id = '%s'" %
(add_value, user_id))
#检查cursor.rowcount的行数是不是零
if self.cursor.rowcount == 0:
return error.error_non_exist_user_id(user_id)
self.conn.commit()
except psycopg2.errors.UniqueViolation:
return error.error_exist_user_id(user_id)
return 200, "ok"
def view_historical_order(self, user_id: str) -> (int, str, [{
str: {
str: str
}
}]):
try:
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id) + ([], )
self.cursor.execute(
"SELECT * FROM new_order "
"WHERE user_id = %s", user_id)
row = self.cursor.fetchall()
orders = []
for i in row:
order = {
"order_id": i[0],
"store_id": i[2],
"order_time": i[3],
"state": i[4]
}
orders.append(order)
except pymysql.Error as e:
return 528, "{}".format(str(e)), []
except BaseException as e:
return 530, "{}".format(str(e)), []
return 200, "ok", orders
def new_order(self, user_id: str, store_id: str,
id_and_count: [(str, int)]) -> (int, str, str):
order_id = ""
try:
if self.session.query(User).filter_by(
user_id=user_id).first() is None:
return error.error_non_exist_user_id(user_id) + (order_id, )
if self.session.query(Store).filter_by(
store_id=store_id).first() is None:
return error.error_non_exist_store_id(store_id) + (order_id, )
uid = "{}_{}_{}".format(user_id, store_id, str(uuid.uuid1()))
pt = datetime.datetime.now()
new_order = Order(id=uid,
status=Order_status.pending,
buyer_id=user_id,
store_id=store_id,
pt=pt)
self.session.add(new_order)
for book_id, count in id_and_count:
cursor = self.session.query(Book_info).filter_by(
id=book_id, store_id=store_id)
row = cursor.first()
if row is None:
return error.error_non_exist_book_id(book_id) + (
order_id, )
inventory_count = row.inventory_count
price = row.price
if inventory_count < count:
return error.error_stock_level_low(book_id) + (order_id, )
cursor = self.session.query(Book_info).filter(
Book_info.id == book_id, Book_info.store_id == store_id,
Book_info.inventory_count >= count)
rowcount = cursor.update({
Book_info.inventory_count:
Book_info.inventory_count - count
})
if rowcount == 0:
return error.error_stock_level_low(book_id) + (order_id, )
new_order_info = Order_info(order_id=uid,
book_id=book_id,
count=count,
price=price)
self.session.add(new_order_info)
self.session.commit()
self.session.close()
order_id = uid
except BaseException as e:
logging.info("530, {}".format(str(e)))
return 530, "{}".format(str(e)), ""
return 200, "ok", order_id
def admmit_return(self, user_id, password, order_id):
try:
cursor = self.conn.cursor()
cursor.execute(
"SELECT password from \"user\" where user_id= (%s)",
(user_id, ))
row = cursor.fetchone()
if row is None:
return error.error_non_exist_user_id(user_id)
if row[0] != password:
return error.error_authorization_fail()
if not self.order_id_exist(order_id):
return error.error_and_message(
522, error.error_code[522].format(order_id))
if not self.return_flag_set(order_id):
return error.error_and_message(
544, error.error_code[544].format(order_id))
cursor.execute(
"SELECT book_id, count, price FROM \"new_order_detail\" WHERE order_id = (%s)",
(order_id, ))
total_price = 0
rows = cursor.fetchall()
for row in rows:
count = row[1]
price = row[2]
total_price = total_price + price * count
cursor.execute(
"SELECT user_id from \"new_order\" where order_id= (%s)",
(order_id, ))
buyer_id = cursor.fetchone()[0]
cursor.execute(
"UPDATE \"user\" SET balance = balance - (%s)"
"WHERE user_id = (%s) AND balance >= (%s)",
(total_price, user_id, total_price))
cursor.execute(
"UPDATE \"user\" SET balance = balance + (%s)"
"WHERE user_id = (%s)", (
total_price,
buyer_id,
))
tool.cancel_order_tool(self.conn, order_id)
cursor.execute(
"UPDATE \"new_order\" SET refund=1"
"WHERE order_id = (%s)", (order_id, ))
self.conn.commit()
except (Exception, psycopg2.DatabaseError) as e:
traceback.print_exc()
return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def add_book(self, user_id: str, store_id: str, book_info: dict,
stock_level: int):
try:
book_id = book_info.get("id")
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
if not self.store_id_exist(store_id):
return error.error_non_exist_store_id(store_id)
if self.book_id_exist(store_id, book_id):
return error.error_exist_book_id(book_id)
book = Book_info()
book.id = book_id
book.title = book_info.get("title")
book.store_id = store_id
book.author = book_info.get("author", None)
book.publisher = book_info.get("publisher", None)
book.original_title = book_info.get("original_title", None)
book.translator = book_info.get("translator", None)
book.pub_year = book_info.get("pub_year", None)
book.pages = book_info.get("pages", 0)
book.binding = book_info.get("binding", None)
book.isbn = book_info.get("isbn", None)
book.author_intro = book_info.get("author_intro", None)
book.book_intro = book_info.get("book_intro", None)
book.content = book_info.get("content", None)
book.inventory_count = stock_level
book.price = book_info.get("price", 0)
self.session.add(book)
book.tags = book_info.get("tags", [])
for tag in book.tags:
book_tag = Book_tag()
book_tag.id = book.id
book_tag.store_id = store_id
book_tag.tag = tag
self.session.add(book_tag)
pictures = book_info.get("pictures", [])
for pic in pictures:
book_pic = Book_pic()
book_pic.book_id = book.id
book_pic.store_id = store_id
book_pic.picture = pic.encode('ascii')
self.session.add(book_pic)
self.session.commit()
self.session.close()
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def create_store(self, user_id: str, store_id: str) -> (int, str):
if not user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
if store_id_exist(store_id):
return error.error_exist_store_id(store_id)
store_one = Store(user_id=user_id, store_id=store_id)
session.add(store_one)
session.commit()
return 200, "ok"
def new_order(self, user_id: str, store_id: str, id_and_count: [(str, int)]) -> (int, str, str):
order_id = ""
try:
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id) + (order_id,)
if not self.store_id_exist(store_id):
return error.error_non_exist_store_id(store_id) + (order_id,)
uid = "{}_{}_{}".format(user_id, store_id, str(uuid.uuid1()))
total_price = 0
for book_id, count in id_and_count:
cursor = self.conn.execute(
"SELECT book_id, stock_level, book_info FROM store "
"WHERE store_id = '%s' AND book_id = '%s';" % (store_id, book_id))
row = cursor.fetchone()
if row is None:
return error.error_non_exist_book_id(book_id) + (order_id,)
stock_level = row[1]
book_info = row[2]
book_info_json = json.loads(book_info)
price = book_info_json.get("price")
if stock_level < count:
return error.error_stock_level_low(book_id) + (order_id,)
cursor = self.conn.execute(
"UPDATE store set stock_level = stock_level - %d "
"WHERE store_id = '%s' and book_id = '%s' and stock_level >= %d; "
% (count, store_id, book_id, count))
if cursor.rowcount == 0:
return error.error_stock_level_low(book_id) + (order_id,)
self.conn.execute(
"INSERT INTO new_order_detail(order_id, book_id, count, price) "
"VALUES('%s', '%s', %d, %d);"
% (uid, book_id, count, price)) # 添加状态0，该订单为支付
total_price += price
self.conn.execute(
"INSERT INTO new_order(order_id, store_id, user_id,total_price,condition) "
"VALUES('%s', '%s', '%s',%d,'unpaid');"
% (uid, store_id, user_id, total_price))
self.conn.commit()
order_id = uid
except sqlalchemy.exc.IntegrityError as e:
logging.info("528, {}".format(str(e)))
return 528, "{}".format(str(e)), ""
except BaseException as e:
logging.info("530, {}".format(str(e)))
print(e)
return 530, "{}".format(str(e)), ""
return 200, "ok", order_id
def cancel(self, user_id: str, password: str, order_id: str):
try:
if self.session.query(User).filter_by(
user_id=user_id).first() is None:
return error.error_non_exist_user_id(user_id)
cursor = self.session.query(Order).filter_by(
id=order_id, buyer_id=user_id, status=Order_status.pending)
rowcount = cursor.update({Order.status: Order_status.cancelled})
if rowcount == 0:
return error.error_non_exist_user_id(user_id)
self.session.commit()
self.session.close()
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def polling(seconds):
cursor1 = Session.query(New_order).filter(
time.time() - New_order.create_time >= 300,
New_order.state == 0).all()
cursor2 = Session.query(New_order).filter(
time.time() - New_order.delivery_time >= 3600 * 7,
New_order.delivery_time > 0, New_order.state == 2).all()
if cursor1 is not None:
for row in cursor1:
order_id = row.order_id
store_id = row.store_id
state = row.state
create_time = row.create_time
# delivery_time = row.delivery_time
# 若超时且状态为未付款
# 增加库存
cur = Session.query(New_order_detail.book_id,
New_order_detail.count).filter(
New_order_detail.order_id == order_id)
for x in cur:
book_id = x[0]
count = x[1]
stock_level = Session.query(Store.stock_level).filter(
Store.store_id == store_id,
Store.book_id == book_id).first()[0]
stock_level += count
row.state = -1
if cursor2 is not None:
for row in cursor2:
order_id = row.order_id
store_id = row.store_id
row.state = 3
cur = Session.query(
New_order_detail.book_id, New_order_detail.count,
New_order_detail.price).filter(
New_order_detail.order_id == order_id).all()
total_price = 0
for row1 in cur:
count = row1[1]
price = row1[2]
total_price = total_price + price * count
row2 = Session.query(User_store).filter(
User_store.store_id == store_id).first()
if row2 is None:
return error.error_non_exist_store_id(store_id)
seller_id = row2.user_id
row3 = Session.query(User).filter(
User.user_id == seller_id).first()
if row3 is None:
return error.error_non_exist_user_id(seller_id)
balance = row3.balance
row3.balance = balance + total_price
Session.commit()
t = Timer(seconds, polling(), (seconds, ))
t.start()
