def search_book_store(self, query: str, store_id: str, first: int):
try:
if not self.store_id_exist(store_id):
code, message = error.error_non_exist_store_id(store_id)
return code, message, 0, []
page_size = 20
books = self.Session.execute(
"SELECT * FROM book_onsale where store_id='%s' and "
"posting @@ to_tsquery('public.jiebacfg', '%s');" %
(store_id, query)).fetchall()
if books is None:
return error.error_no_book()
book_list = []
print(books)
for book in books:
stock_level = self.Session.query(Store.stock_level).filter(
Store.store_id == book.store_id,
Store.book_id == book.book_id).first()[0]
this_book = {
'book_id': book.book_id,
'title': book.title,
'price': book.price,
'author': book.author,
'tags': book.tags,
'stock_level': stock_level
}
book_list.append(this_book)
pages = len(book_list) / page_size
self.Session.commit()
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e)), 0, []
except BaseException as e:
return 530, "{}".format(str(e)), 0, []
return 200, "ok", pages, book_list[first - 1:page_size - 1]
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 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 search_book_store_author(self, author: str, store_id: str, first: int):
try:
if not self.store_id_exist(store_id):
code, message = error.error_non_exist_store_id(store_id)
return code, message, 0, []
page_size = 2
books = self.Session.query(Book_Onsale).filter(
Book_Onsale.store_id == store_id,
Book_Onsale.author.like(
'%{author}%'.format(author=author))).all()
if books is None:
return error.error_no_book()
book_list = []
print(books)
for book in books:
stock_level = self.Session.query(Store.stock_level).filter(
Store.store_id == book.store_id,
Store.book_id == book.book_id).first()[0]
this_book = {
'book_id': book.book_id,
'title': book.title,
'price': book.price,
'author': book.author,
'tags': book.tags,
'stock_level': stock_level
}
book_list.append(this_book)
pages = len(book_list) / page_size
self.Session.commit()
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e)), 0, []
except BaseException as e:
return 530, "{}".format(str(e)), 0, []
return 200, "ok", pages, book_list[first - 1:page_size - 1]
def check_stock(self, user_id: str, password: str, store_id: str, book_id: 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()+({"book_id": [], "stock_level": []})
if row[0] != password:
return error.error_authorization_fail()+({"book_id": [], "stock_level": []})
cursor = self.conn.execute(
"SELECT store_id FROM user_store WHERE user_id = '%s' AND store_id = '%s';" % (user_id, store_id))
row = cursor.fetchone()
if row is None:
return error.error_non_exist_store_id(store_id)+({"book_id": [], "stock_level": []})
book_id_p=""
if book_id != "":
book_id_p = "AND book_id = '%s'" % (book_id)
cursor = self.conn.execute(
"SELECT book_id, stock_level FROM store WHERE store_id = '%s' %s;" % (
store_id, book_id_p))
book_id_list = []
stock_level_list = []
for row in cursor:
book_id_list.append(row[0])
stock_level_list.append(row[1])
msg = {"book_id": book_id_list, "stock_level": stock_level_list}
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e)),{"book_id": [], "stock_level": []}
except BaseException as e:
print(e)
return 530, "{}".format(str(e)),{"book_id": [], "stock_level": []}
return 200, "ok", msg
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 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 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 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 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 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 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 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 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 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()
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 new_order(self, user_id: str, store_id: str,
id_and_count: [(str, int)]) -> (int, str, str):
order_id = ""
try:
#检查id是否存在
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()))
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 = self.conn.fetchone()
#检查商店和书是否存在
if row is None:
return error.error_non_exist_book_id(book_id) + (
order_id, )
stock_level = row['stock_level']
book_info = row['book_info']
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 - %s "
"WHERE store_id = %s and book_id = %s and stock_level >= %s; ",
(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, %s, %s);", (uid, book_id, count, price))
self.conn.execute(
"INSERT INTO new_order(order_id, store_id, user_id) "
"VALUES(%s, %s, %s);", (uid, store_id, user_id))
#self.conn.commit()
order_id = uid
except pg.Error 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 new_order(self, user_id: str, store_id: str,
id_and_count: [(str, int)]) -> (int, str, str):
order_id = ""
try: #判断用户id和门店id是否存在
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()))
for book_id, count in id_and_count: #对每个书的id和数量
self.cursor = self.conn.cursor()
self.cursor.execute( #查询书的id，数量，信息，从store
"SELECT book_id, stock_level, book_info FROM store "
"WHERE store_id = '%s' AND book_id = '%s'" %
(store_id, book_id)) #从前端获取store_id和book_id
row = self.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]
#写为json
book_info_json = json.loads(book_info)
#从json中获取价格
price = book_info_json.get("price")
#如果存货量小于订单量，则返回错误
if stock_level < count:
return error.error_stock_level_low(book_id) + (order_id, )
#更新存货量，stocklevel - count
self.cursor.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 self.cursor.rowcount == 0:
return error.error_stock_level_low(book_id) + (order_id, )
#新增order（细节）
self.cursor.execute(
"INSERT INTO new_order_detail(order_id, book_id, count, price) "
"VALUES('%s', '%s', %d, %d)" %
(uid, book_id, count, price))
#新增order
self.cursor = self.conn.cursor()
self.cursor.execute(
"INSERT INTO new_order(order_id, store_id, user_id) "
"VALUES('%s', '%s', '%s')" % (uid, store_id, user_id))
self.conn.commit()
order_id = uid
except psycopg2.errors.UniqueViolation:
return error.error_exist_store_id(store_id)
return 200, "ok", order_id
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()))
for book_id, count in id_and_count:
cursor = self.conn.execute(
"SELECT book_id, stock_level, book_info FROM store "
"WHERE store_id = ? AND book_id = ?;", (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 - ? "
"WHERE store_id = ? and book_id = ? and stock_level >= ?; ",
(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(?, ?, ?, ?);", (uid, book_id, count, price))
self.conn.execute(
"INSERT INTO new_order(order_id, store_id, user_id) "
"VALUES(?, ?, ?);", (uid, store_id, user_id))
self.conn.commit()
order_id = uid
except sqlite.Error 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 receive(self, user_id: str, password: str, order_id: str) -> (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(
"SELECT order_id, user_id, store_id,total_price,condition FROM new_order "
"WHERE order_id = '%s'AND user_id = '%s';" % (order_id,user_id))
row = cursor.fetchone()
if row is None:
return error.error_invalid_order_id(order_id)
if row[4]!='shipped':
return error.error_unreceivable_order(order_id)
store_id=row[2]
total_price=row[3]
self.conn.execute("UPDATE new_order SET condition = 'received' "
"WHERE order_id ='%s';" % (order_id))
cursor=self.conn.execute(
"SELECT user_id FROM user_store "
"WHERE store_id = '%s';"%(store_id)
)
row = cursor.fetchone()
if row is None:
return error.error_non_exist_store_id(store_id)
seller_id=row[0]
self.conn.execute("UPDATE user_store SET s_balance = s_balance - %d "
"WHERE store_id = '%s' AND s_balance >= %d;"
%(total_price,store_id,total_price))
cursor = self.conn.execute("UPDATE usr SET balance = balance+ %d "
"WHERE user_id = %d;"%(seller_id))
row=cursor.fetchone()
if row is None:
return error.error_non_exist_user_id(seller_id)
self.conn.commit()
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def add_stock_level(self, user_id: str, store_id: str, book_id: str,
add_stock_level: int):
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 not book_id_exist(store_id, book_id):
return error.error_non_exist_book_id(book_id)
cursor = session.query(Store_detail).filter(
and_(Store_detail.store_id == store_id,
Store_detail.book_id == book_id)).first()
cursor.stock_level = cursor.stock_level + add_stock_level
session.commit()
return 200, "ok"
def search_order_detail_seller(self, user_id: str, password: str, order_id: 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()+({"condition":-1,"result_count":-1 },)
if row[0] != password:
return error.error_authorization_fail()+({"condition":-1,"result_count":-1 },)
cursor = self.conn.execute(
"SELECT store_id,total_price,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)+({"condition":-1,"result_count":-1 },)
store_id = row[0]
total_price = row[1]
condition = row[2]
cursor = self.conn.execute(
"SELECT store_id FROM user_store WHERE user_id='%s' and store_id='%s';" % (user_id, store_id))
row = cursor.fetchone()
if row is None:
return error.error_non_exist_store_id()+({"condition":-1,"result_count":-1 },)
book_id_list = []
count_list = []
price_list = []
result_count=0
cursor = self.conn.execute(
"SELECT book_id, count, price FROM new_order_detail WHERE order_id = '%s';" % (order_id)
)
for row in cursor:
book_id_list.append(row[0])
count_list.append(row[1])
price_list.append(row[2])
result_count+=1
msg = {"order_id": order_id, "store_id": store_id, "total_price": total_price, "condition": condition,
"book_id": book_id_list, "count": count_list, "price": price_list,"result_count":result_count}
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e)),{"condition":-1,"result_count":-1 },
except BaseException as e:
return 530, "{}".format(str(e)),{"condition":-1,"result_count":-1 },
return 200, "ok", msg
def delete_order(seconds):
# Session = store.get_db_conn()
cursor = Session.query(New_order).all()
print('cur')
print(cursor)
if cursor is not None:
for row in cursor:
print('row')
print(row)
order_id = row.order_id
store_id = row.store_id
state = row.state
create_time = row.create_time
delivery_time = row.delivery_time
# 若超时且状态为未付款
if time.time()-create_time >= 600 and state == 0:
# 增加库存
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
Session.query(New_order).filter(New_order.order_id == order_id).delete()
Session.query(New_order_detail).filter(New_order_detail.order_id == order_id).delete()
if time.time() - delivery_time >= 60 and state == 2:
r = Session.query(New_order).filter(New_order.order_id == order_id).first()
r.state = 3
cursor1 = 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 cursor1:
count = row1[1]
price = row1[2]
total_price = total_price + price * count
row3 = Session.query(User_store).filter(User_store.store_id == store_id).first()
if row3 is None:
return error.error_non_exist_store_id(store_id)
seller_id = row3.user_id
row5 = 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
Session.commit()
t = Timer(seconds, delete_order, (seconds,))
t.start()
def check_order(self):
order2pay = session.query(Order_to_Pay).all()
if order2pay is not []:
for i in range(len(order2pay)):
paytime = order2pay[i].paytime
time_now = datetime.now()
old_time = datetime.strptime(str(paytime),
"%Y-%m-%d %H:%M:%S.%f")
new_time = datetime.strptime(str(time_now),
"%Y-%m-%d %H:%M:%S.%f")
time_lag = (new_time - old_time).seconds # 计算时间差
# 超出15min未付款则关闭订单
if (time_lag > 15 * 60):
order_id = order2pay[i].order_id
user_id = order2pay[i].user_id
store_id = order2pay[i].store_id
# 商品退回库存
store = session.query(Store).filter(
Store.store_id == store_id).first()
if store is None:
return error.error_non_exist_store_id(store_id)
order_detail = session.query(Order_detail).filter(
Order_detail.order_id == order_id).all()
total_price = 0
for i in range(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 += price * count
book.stock_level += count
session.add(
Order(order_id=order_id,
user_id=user_id,
store_id=store_id,
paytime=paytime,
status=4))
session.delete(order2pay[i])
session.commit()
return 200, "ok", "closed"
return 200, "ok", None
def deliver(self, store_id: str, order_id: str) -> (int, str):
try:
if not self.store_id_exist(store_id):
return error.error_non_exist_store_id(store_id)
cursor = self.session.query(Order).filter_by(
id=order_id, store_id=store_id, status=Order_status.paid)
rowcount = cursor.update({Order.status: Order_status.delivering})
if rowcount == 0:
return error.error_invalid_order_id(order_id)
self.session.commit()
self.session.close()
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) -> (int, 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 ('%s', '%s', '%s', %d)" % (
store_id, book_id, book_json_str, stock_level))
self.conn.commit()
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def add_stock_level(self, user_id: str, store_id: str, book_id: str, add_stock_level: int) -> (int, 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 not self.book_id_exist(store_id, book_id):
return error.error_non_exist_book_id(book_id)
self.conn.execute(
"UPDATE store SET stock_level = stock_level +%d WHERE store_id = '%s' AND book_id = '%s'" % (
add_stock_level, store_id, book_id))
self.conn.commit()
except sqlalchemy.exc.IntegrityError 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):
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)
obj = Store(store_id=store_id,
book_id=book_id,
book_info=book_json_str,
stock_level=stock_level)
self.Session.add(obj)
this_book = self.Session.query(Book).filter(
Book.book_id == book_id).first()
# print('title长这样:', this_book.title)
# print('书长这样：', this_book.picture)
book_onsale_obj = Book_Onsale(store_id=store_id,
book_id=book_id,
title=this_book.title,
author=this_book.author,
publisher=this_book.publisher,
translator=this_book.translator,
pub_year=this_book.pub_year,
pages=this_book.pages,
price=this_book.price,
binding=this_book.binding,
isbn=this_book.isbn,
author_intro=this_book.author_intro,
book_intro=this_book.book_intro,
content=this_book.content,
tags=this_book.tags,
picture=this_book.picture)
self.Session.add(book_onsale_obj)
# self.Session.execute("INSERT into store(store_id, book_id, book_info, stock_level)"
# "VALUES (?, ?, ?, ?)", (store_id, book_id, book_json_str, stock_level))
self.Session.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"
