def logout(self, user_id: str, token: str) -> (int, str):
try:
code, message = self.check_token(user_id, token)
if code != 200:
return code, message
terminal = "terminal_{}".format(str(time.time()))
dummy_token = jwt_encode(user_id, terminal)
# cursor = self.conn.execute(
# "UPDATE user SET token = ?, terminal = ? WHERE user_id=?",
# (dummy_token, terminal, user_id), )
# if cursor.rowcount == 0:
# return error.error_authorization_fail()
# self.conn.commit()
row = db_session.query(User).filter_by(user_id=user_id).first()
if row is None:
return error.error_authorization_fail()
row.token = dummy_token
row.terminal = terminal
db_session.commit()
except sqlite.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, 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 change_password(self, user_id: str, old_password: str,
new_password: str) -> (int, str):
try:
code, message = self.check_password(user_id, old_password)
if code != 200:
return code, message
terminal = "terminal_{}".format(str(time.time()))
token = jwt_encode(user_id, terminal)
# cursor = self.conn.execute(
# "UPDATE user set password = ?, token= ? , terminal = ? where user_id = ?",
# (new_password, token, terminal, user_id), )
# if cursor.rowcount == 0:
# return error.error_authorization_fail()
# self.conn.commit()
row = db_session.query(User).filter_by(user_id=user_id).first()
if row is None:
return error.error_authorization_fail()
row.password = new_password
row.token = token
row.terminal = terminal
db_session.commit()
except sqlite.Error as e:
return 528, "{}".format(str(e))
except BaseException as 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 login(self, user_id: str, password: str,
terminal: str) -> (int, str, str):
token = ""
try:
code, message = self.check_password(user_id, password)
if code != 200:
return code, message, ""
token = jwt_encode(user_id, terminal)
# cursor = self.conn.execute(
# "UPDATE user set token= ? , terminal = ? where user_id = ?",
# (token, terminal, user_id), )
# if cursor.rowcount == 0:
# return error.error_authorization_fail() + ("", )
# self.conn.commit()
row = db_session.query(User).filter_by(user_id=user_id).first()
if row is None:
return error.error_authorization_fail() + ("", )
row.token = token
row.terminal = terminal
db_session.commit()
except sqlite.Error as e:
return 528, "{}".format(str(e)), ""
except BaseException as e:
return 530, "{}".format(str(e)), ""
return 200, "ok", token
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 register(self, user_id: str, password: str):
try:
terminal = "terminal_{}".format(str(time.time()))
token = jwt_encode(user_id, terminal)
user_tmp = User(user_id, password, 0, token, terminal)
db_session.add(user_tmp)
db_session.commit()
except BaseException as e:
return error.error_exist_user_id(user_id)
return 200, "ok"
def get_book_info(self, start, size) -> [Book]:#得到爬虫爬到的db.book里的内容
conn = sqlite.connect(self.book_db)
cursor = conn.execute(
"SELECT id, title, author, "
"publisher, original_title, "
"translator, pub_year, pages, "
"price, currency_unit, binding, "
"isbn, author_intro, book_intro, "
"content, tags FROM book ORDER BY id "
"LIMIT ? OFFSET ?", (size, start))
for row in cursor:
# book = Book()
# book.id = row[0]
# book.title = row[1]
# book.author = row[2]
# book.publisher = row[3]
# book.original_title = row[4]
# book.translator = row[5]
# book.pub_year = row[6]
# book.pages = row[7]
# book.price = row[8]
#
# book.currency_unit = row[9]
# book.binding = row[10]
# book.isbn = row[11]
# book.author_intro = row[12]
# book.book_intro = row[13]
# book.content = row[14]
book_tag=[]
tags = row[15]
for tag in tags.split("\n"):
if tag.strip() != "":
book_tag.append(tag)
# book.tags.append(tag)
# for i in range(0, random.randint(0, 9)):
# if picture is not None:
# encode_str = base64.b64encode(picture).decode('utf-8')
# book_pic.append(encode_str)
# book.pictures.append(encode_str)
book_tag=str(book_tag)#要转换成string类型
book = Book(book_id=row[0], title=row[1], author=row[2], publisher=row[3], original_title=row[4],translator=row[5],pub_year=row[6], pages=row[7], price=row[8], currency_unit=row[9], binding=row[10],isbn=row[11],author_intro=row[12], book_intro=row[13], content=row[14],tags=book_tag)
db_session.add(book)
db_session.commit()
def create_store(self, user_id: str, store_id: str) -> (int, str):
try:
if not self.user_id_exist(user_id):
return error.error_non_exist_user_id(user_id)
if self.store_id_exist(store_id):
return error.error_exist_store_id(store_id)
# self.conn.execute("INSERT into user_store(store_id, user_id)"
# "VALUES (?, ?)", (store_id, user_id))
# self.conn.commit()
user_store = User_Store(user_id=user_id, store_id=store_id)
db_session.add(user_store)
db_session.commit()
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def send_stock(self, user_id: str, order_id: str) -> (int, str):
try:
print(user_id, order_id)
row = New_Order.query.filter_by(order_id=order_id).first()
if row is None:
return error.error_invalid_order_id(order_id)
row = User.query.filter_by(user_id=user_id).first()
if row is None:
return error.error_non_exist_user_id(user_id)
db_session.query(New_Order_Detail).filter(
New_Order_Detail.order_id == order_id).update(
{New_Order_Detail.state: 2})
db_session.commit()
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def auto_cancel():
cursor = New_Order_Detail.query.filter_by(state=0).all()
# 得到order_id_list
order_id_list = []
for item in cursor:
order_id_list.append(item.order_id)
order_id_list = list(set(order_id_list)) # 去重
for id in order_id_list:
row = New_Order_Detail.query.filter_by(order_id=id).first()
end_time = time.time()
print(end_time - row.time)
if (end_time - row.time >= 600): # 付款时间超过10分钟自动取消
New_Order_Detail.query.filter_by(order_id=id).update({"state": -1})
print("自动取消", id)
db_session.commit()
def unregister(self, user_id: str, password: str) -> (int, str):
try:
code, message = self.check_password(user_id, password)
if code != 200:
return code, message
# cursor = self.conn.execute("DELETE from user where user_id=?", (user_id,))
# if cursor.rowcount == 1:
# self.conn.commit()
# else:
# return error.error_authorization_fail()
row = db_session.query(User).filter_by(user_id=user_id).first()
if row is None:
return error.error_authorization_fail()
else:
db_session.delete(row)
db_session.commit()
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def confirm_stock(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()
cursor = New_Order_Detail.query.filter_by(order_id=order_id).first()
if cursor is None:
return error.error_invalid_order_id(order_id)
if cursor.state != 2:
return error.error_order_not_dispatched(order_id)
cursor = New_Order_Detail.query.filter_by(order_id=order_id).update({"state": 3}) # 订单全部更新为3
if cursor == 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 add_stock_level(self, user_id: str, store_id: str, book_id: str,
add_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 not self.book_id_exist(store_id, book_id):
return error.error_non_exist_book_id(book_id)
print(user_id, store_id, book_id)
# self.conn.execute("UPDATE store SET stock_level = stock_level + ? "
# "WHERE store_id = ? AND book_id = ?", (add_stock_level, store_id, book_id))
# self.conn.commit()
db_session.query(Store).filter(
Store.store_id == store_id, Store.book_id == book_id).update(
{Store.stock_level: Store.stock_level + add_stock_level})
db_session.commit()
# except Error as e:
# logging.info("528, {}".format(str(e)))
# return 528, "{}".format(str(e)), ""
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
from be.model.database import db_session from be.table.user import User u = User(123, 231, 30,'sa', 'dsa') db_session.add(u) db_session.commit() # from be.model.db_conn import DBConn # a = DBConn.user_id_exist(user_id=0) # print(a)
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 = Store.query.filter_by(store_id=store_id, book_id=book_id).first()
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 - ? "
# "WHERE store_id = ? and book_id = ? and stock_level >= ?; ",
# (count, store_id, book_id, count))
row = Store.query.filter_by(store_id=store_id, book_id=book_id, stock_level=stock_level).update(
{'stock_level': stock_level - count})
if row == 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))
row = New_Order_Detail(order_id=uid, book_id=book_id, count=count, price=price, state=0,
time=time.time())
db_session.add(row)
# self.conn.execute(
# "INSERT INTO new_order(order_id, store_id, user_id) "
# "VALUES(?, ?, ?);",
# (uid, store_id, user_id))
# self.conn.commit()
row = New_Order(order_id=uid, store_id=store_id, user_id=user_id)
db_session.add(row)
order_id = uid
db_session.commit()
# except sqlite.Error as e:
# logging.info("528, {}".format(str(e)))
# return 528, "{}".format(str(e)), ""
except BaseException as e:
print(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):
try:
# cursor = conn.execute("SELECT order_id, user_id, store_id FROM new_order WHERE order_id = ?", (order_id,))
# row = cursor.fetchone()
row = New_Order.query.filter_by(order_id=order_id).first()
if row is None:
return error.error_invalid_order_id(order_id)
order_id = row.order_id
buyer_id = row.user_id
store_id = row.store_id
if buyer_id != user_id:
return error.error_authorization_fail()
# cursor = conn.execute("SELECT balance, password FROM user WHERE user_id = ?;", (buyer_id,))
# row = cursor.fetchone()
row = User.query.filter_by(user_id=buyer_id).first()
if row is None:
return error.error_non_exist_user_id(buyer_id)
balance = row.balance
if password != row.password:
return error.error_authorization_fail()
# cursor = conn.execute("SELECT store_id, user_id FROM user_store WHERE store_id = ?;", (store_id,))
# row = cursor.fetchone()
# 买家
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
if not self.user_id_exist(seller_id):
return error.error_non_exist_user_id(seller_id)
# cursor = conn.execute("SELECT book_id, count, price FROM new_order_detail WHERE order_id = ?;", (order_id,))
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
# 买家付钱，钱减少
if balance < total_price:
return error.error_not_sufficient_funds(order_id)
# cursor = conn.execute("UPDATE user set balance = balance - ?"
# "WHERE user_id = ? AND balance >= ?",
# (total_price, buyer_id, total_price))
cursor = User.query.filter(User.user_id == buyer_id, User.balance >= total_price).update(
{'balance': balance - total_price})
if cursor == 0:
return error.error_not_sufficient_funds(order_id)
# cursor = conn.execute("UPDATE user set balance = balance + ?"
# "WHERE user_id = ?",
# (total_price, buyer_id))
# 卖家收钱，钱增多
cursor = User.query.filter_by(user_id=seller_id).update({'balance': seller_balance + total_price})
if cursor == 0:
return error.error_non_exist_user_id(seller_id)
# 将记录的state改为1，表示已付款
# cursor = conn.execute("DELETE FROM new_order WHERE order_id = ?", (order_id, ))
cursor = New_Order_Detail.query.filter_by(order_id=order_id).update({"state": 1})
if cursor == 0:
return error.error_invalid_order_id(order_id)
# cursor = conn.execute("DELETE FROM new_order_detail where order_id = ?", (order_id, ))
# cursor = New_Order_Detail.query.filter_by(order_id=order_id).delete()
# if cursor == 0:
# return error.error_invalid_order_id(order_id)
db_session.commit()
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
