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 cancel_order(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 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)
condition = row[2]
store_id = row[0]
total_price = row[1]
if condition == 'received' or condition == 'cancelled':
return error.error_uncancellable_order(order_id)
if condition == 'unpaid':
self.conn.execute(
"UPDATE new_order SET condition = 'cancelled',update_time=CURRENT_TIMESTAMP "
"WHERE order_id = '%s';" % (order_id))
elif condition == 'paid' or condition == 'shipped':
self.conn.execute(
"UPDATE new_order SET condition = 'cancelled',update_time=CURRENT_TIMESTAMP "
"WHERE order_id = '%s';" % (order_id))
count = 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))
if count == 0:
return error.error_not_sufficient_funds(order_id) # 卖家余额出错
self.conn.execute("UPDATE usr SET balance = balance + %d "
"WHERE user_id = '%s';" %
(total_price, user_id))
else:
return error.error_invalid_order_id(order_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 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 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):
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 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 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 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 receive_books(self, buyer_id, order_id):
order = session.query(Order).filter(Order.order_id == order_id).first()
if order is None:
return error.error_invalid_order_id(order_id)
if order.status == 0:
return error.error_order_unsent(order_id)
if order.status == 2:
return error.error_order_received(order_id)
if order.user_id != buyer_id:
return error.error_authorization_fail()
order.status = 2
session.commit()
return 200, "ok"
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 receive_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 user_id,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)
buyer_id = row['user_id']
state = row['state']
store_id = row['store_id']
if buyer_id != user_id:
return error.error_authorization_fail()
if state == '待发货':
return error.error_receive_fail()
# 收货完成，该订单记录为历史订单
cursor = self.conn.execute(
"INSERT INTO history_order(order_id,user_id,store_id) "
"VALUES(%s, %s, %s);", (order_id, user_id, store_id))
self.conn.execute(
"SELECT book_id, count, price FROM paid_order_detail WHERE order_id = %s;",
(order_id, ))
cursor = self.conn.fetchall()
total_price = 0
bookids = []
counts = []
prices = []
for row in cursor:
bookids.append(row['book_id'])
counts.append(row['count'])
prices.append(row['price'])
for i in range(len(bookids)):
cursor = self.conn.execute(
"INSERT INTO history_order_detail(order_id,book_id,count,price) "
"VALUES(%s, %s, %s, %s);",
(order_id, bookids[i], counts[i], prices[i]))
# 删除订单信息
cursor = self.conn.execute(
"DELETE FROM paid_order WHERE order_id = %s", (order_id, ))
cursor = self.conn.execute(
"DELETE FROM paid_order_detail where order_id = %s",
(order_id, ))
except pg.Error as e:
return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def delivery_book(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)
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 == 0:
return error.error_no_payment_to_deliver()
elif row.state == 2 or row.state == 3:
return error.error_already_delivered()
row.state = 2
row.delivery_time = time.time()
self.Session.commit()
row = self.Session.query(New_order).filter(
New_order.order_id == order_id).first()
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
return 200, "ok"
def send_books(self, seller_id, order_id):
order = session.query(Order).filter(Order.order_id == order_id).first()
if order is None:
return error.error_invalid_order_id(order_id)
if order.status != 0:
return 521, 'books has been sent to costumer or the order is cancelled'
# status == 4
store = session.query(Store).filter(
Store.store_id == order.store_id).first()
# 店铺主是不是seller
if seller_id != store.user_id:
return error.error_authorization_fail()
order.status = 1
session.commit()
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 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 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 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 ship(self, seller_id, store_id, order_id) -> (int, str):
try:
if not self.user_id_exist(seller_id):
return error.error_non_exist_user_id(seller_id)
if not self.store_id_exist(store_id):
return error.error_non_exist_store_id()
cursor = self.conn.execute("SELECT condition FROM new_order WHERE "
"store_id ='%s' AND order_id='%s';" %
(store_id, order_id))
row = cursor.fetchone()
if row is None:
return error.error_invalid_order_id(order_id)
if row != "paid":
return error.error_unshippable_order(order_id)
self.conn.execute(
"UPDATE new_order set condition ='shipped' WHERE order_id = ?",
(order_id, ))
self.conn.commit()
print("物品已发货")
except sqlalchemy.exc.IntegrityError as e:
return 528, "{}".format(str(e))
return 200, "ok"
def receive(self, user_id: str, password: str, order_id: str):
try:
cursor = self.session.query(User).filter_by(user_id=user_id)
row = cursor.first()
if row is None:
return error.error_non_exist_user_id()
if row.password != password:
return error.error_authorization_fail()
cursor = self.session.query(Order).filter_by(id=order_id, buyer_id=user_id, status=Order_status.delivering)
rowcount = cursor.update({Order.status: Order_status.received})
if rowcount == 0:
return error.error_invalid_order_id(user_id)
self.session.commit()
self.session.close()
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):
conn = self.conn
try:
cursor = conn.execute(
"SELECT order_id, user_id, store_id FROM new_order WHERE order_id = ?",
(order_id, ))
row = cursor.fetchone()
if row is None:
return error.error_invalid_order_id(order_id)
order_id = row[0]
buyer_id = row[1]
store_id = row[2]
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()
if row is None:
return error.error_non_exist_user_id(buyer_id)
balance = row[0]
if password != row[1]:
return error.error_authorization_fail()
cursor = conn.execute(
"SELECT store_id, user_id FROM user_store WHERE store_id = ?;",
(store_id, ))
row = cursor.fetchone()
if row is None:
return error.error_non_exist_store_id(store_id)
seller_id = row[1]
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, ))
total_price = 0
for row in cursor:
count = row[1]
price = row[2]
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))
if cursor.rowcount == 0:
return error.error_not_sufficient_funds(order_id)
cursor = conn.execute(
"UPDATE user set balance = balance + ?"
"WHERE user_id = ?", (total_price, buyer_id))
if cursor.rowcount == 0:
return error.error_non_exist_user_id(buyer_id)
cursor = conn.execute("DELETE FROM new_order WHERE order_id = ?",
(order_id, ))
if cursor.rowcount == 0:
return error.error_invalid_order_id(order_id)
cursor = conn.execute(
"DELETE FROM new_order_detail where order_id = ?",
(order_id, ))
if cursor.rowcount == 0:
return error.error_invalid_order_id(order_id)
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 payment(self, user_id: str, password: str, order_id: str) -> (int, str):
conn = self.conn
try:
cursor = conn.execute(
"SELECT order_id, user_id, 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)
order_id = row[0]
buyer_id = row[1]
store_id = row[2]
total_price = row[3]
condition = row[4]
if buyer_id != user_id:
return error.error_authorization_fail()
time1=time.time()
if condition != "unpaid":
#print(condition,order_id)
#print("oh"+str(time.time()))
return error.error_unpayable_order(order_id)
cursor = conn.execute("SELECT balance, password FROM usr WHERE user_id = '%s';" % (buyer_id))
row = cursor.fetchone()
if row is None:
return error.error_non_exist_user_id(buyer_id)
balance = row[0]
if password != row[1]:
return error.error_authorization_fail()
cursor = conn.execute("SELECT store_id, 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[1]
if not self.user_id_exist(seller_id):
return error.error_non_exist_user_id(seller_id)
if balance < total_price:
return error.error_not_sufficient_funds(order_id)
cursor = conn.execute("UPDATE usr set balance = balance - %d "
"WHERE user_id = '%s' AND balance >= %d;"
% (total_price, buyer_id, total_price))
if cursor.rowcount == 0:
return error.error_not_sufficient_funds(order_id)
cursor = conn.execute("UPDATE user_store set s_balance = s_balance + %d "
"WHERE store_id = '%s';"
% (total_price, store_id))
if cursor.rowcount == 0:
return error.error_non_exist_store_id(store_id)
cursor = conn.execute("UPDATE new_order set condition = 'paid' WHERE order_id ='%s';" % (order_id))
#print (order_id)
#time2=time.time()
#print(time2-time1)
#print("good"+str(time2))
if cursor.rowcount == 0:
return error.error_invalid_order_id(order_id)
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):
# Buyer_func.check_order(self, order_id)
order = session.query(Order).filter(Order.order_id == order_id).first()
if order is None:
return error.error_invalid_order_id(order_id)
if order.status == 4:
return error.error_order_closed(order_id)
elif order.status != 3:
return error.error_order_has_been_paid(order_id)
# order_id = order.order_id
buyer_id = order.user_id
store_id = order.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
order.status = 0 # 设置付款状态：已支付
order.paytime = datetime.now()
# ?????代码在干嘛？ 删除订单？
# cursor = conn.execute("DELETE FROM new_order WHERE order_id = ?", (order_id,))
# if cursor.rowcount == 0:
# return error.error_invalid_order_id(order_id)
#
# cursor = conn.execute("DELETE FROM new_order_detail where order_id = ?", (order_id,))
# if cursor.rowcount == 0:
# return error.error_invalid_order_id(order_id)
session.commit()
return 200, "ok"
def query(self, user_id: str, order_id: str) -> (int, str, str):
conn = self.conn
try:
# 检查订单进度（未付款、已付款、已交付）
cursor = conn.execute(
"SELECT user_id, store_id FROM new_order WHERE order_id = %s",
(order_id, ))
row_new = conn.fetchone()
cursor = conn.execute(
"SELECT user_id, store_id,state FROM paid_order WHERE order_id = %s",
(order_id, ))
row_paid = conn.fetchone()
cursor = conn.execute(
"SELECT user_id, store_id FROM history_order WHERE order_id = %s",
(order_id, ))
row_history = conn.fetchone()
if row_new is None and row_paid is None and row_history is None:
return error.error_invalid_order_id(order_id)
elif row_new is not None:
buyer_id = row_new['user_id']
store_id = row_new['store_id']
if buyer_id != user_id:
return error.error_authorization_fail()
conn.execute(
"SELECT book_id, count FROM new_order_detail WHERE order_id = %s;",
(order_id, ))
cursor = conn.fetchall()
books = []
for row in cursor:
dict = {}
dict["id"] = row['book_id']
dict["count"] = row['count']
books.append(dict)
json_text = {}
json_text["user_id"] = buyer_id
json_text["store_id"] = store_id
json_text["books"] = books
json_text["order_state"] = "unpaid"
return 200, "ok", str(json_text)
elif row_paid is not None:
buyer_id = row_paid['user_id']
store_id = row_paid['store_id']
state = row_paid['state']
if buyer_id != user_id:
return error.error_authorization_fail()
conn.execute(
"SELECT book_id, count FROM paid_order_detail WHERE order_id = %s;",
(order_id, ))
cursor = conn.fetchall()
books = []
for row in cursor:
dict = {}
dict["id"] = row['book_id']
dict["count"] = row['count']
books.append(dict)
json_text = {}
json_text["user_id"] = buyer_id
json_text["store_id"] = store_id
json_text["books"] = books
json_text["order_state"] = state
return 200, "ok", str(json_text)
elif row_history is not None:
buyer_id = row_history['user_id']
store_id = row_history['store_id']
if buyer_id != user_id:
return error.error_authorization_fail()
conn.execute(
"SELECT book_id, count FROM history_order_detail WHERE order_id = %s;",
(order_id, ))
cursor = conn.fetchall()
books = []
for row in cursor:
dict = {}
dict["id"] = row['book_id']
dict["count"] = row['count']
books.append(dict)
json_text = {}
json_text["user_id"] = buyer_id
json_text["store_id"] = store_id
json_text["books"] = books
json_text["order_state"] = "finished"
return 200, "ok", str(json_text)
except pg.Error as e:
return 528, "{}".format(str(e))
except BaseException as e:
return 530, "{}".format(str(e))
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()
#print("checkpoint1")
cursor = self.conn.execute(
"SELECT 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[2] != 'shipped':
return error.error_unreceivable_order(order_id)
#print("checkpoint2")
store_id = row[0]
total_price = row[1]
self.conn.execute(
"UPDATE new_order SET condition = 'received',update_time=CURRENT_TIMESTAMP "
"WHERE order_id ='%s';" % (order_id))
#print("checkpoint3")
cursor = self.conn.execute("SELECT user_id FROM user_store "
"WHERE store_id = '%s';" % (store_id))
#print("checkpoint4")
row = cursor.fetchone()
if row is None:
return error.error_non_exist_store_id(store_id)
seller_id = row[0]
count = 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))
if count == 0:
return error.error_not_sufficient_funds(order_id) # 卖家余额出错
#print("checkpoint5")
count = self.conn.execute("UPDATE usr SET balance = balance+ %d "
"WHERE user_id = '%s';" %
(total_price, seller_id))
#print("checkpoint6")
if count == 0:
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 cancel(self, user_id: str, order_id: str) -> (int, str):
conn = self.conn
try:
# 订单未付款或者订单付款位发货都可以取消订单
#如果已经付款，还会退款
cursor = conn.execute(
"SELECT user_id, store_id FROM new_order WHERE order_id = %s",
(order_id, ))
row_new = conn.fetchone()
cursor = conn.execute(
"SELECT user_id, store_id,state FROM paid_order WHERE order_id = %s",
(order_id, ))
row_paid = conn.fetchone()
if row_new is None and row_paid is None:
return error.error_invalid_order_id(order_id)
elif row_new is not None:
buyer_id = row_new['user_id']
store_id = row_new['store_id']
cursor = conn.execute(
"SELECT store_id, user_id FROM user_store WHERE store_id = %s;",
(store_id, ))
row = conn.fetchone()
seller_id = row['user_id']
if not self.user_id_exist(seller_id):
return error.error_non_exist_user_id(seller_id)
if buyer_id != user_id:
return error.error_authorization_fail()
# 订单未付款，接受取消操作，该订单写入历史订单
cursor = self.conn.execute(
"INSERT INTO history_order(order_id,user_id,store_id) "
"VALUES(%s, %s, %s);", (order_id, user_id, store_id))
self.conn.execute(
"SELECT book_id, count, price FROM new_order_detail WHERE order_id = %s;",
(order_id, ))
cursor = self.conn.fetchall()
bookids = []
counts = []
prices = []
for row in cursor:
bookids.append(row['book_id'])
counts.append(row['count'])
prices.append(row['price'])
for i in range(len(bookids)):
cursor = self.conn.execute(
"INSERT INTO history_order_detail(order_id,book_id,count,price) "
"VALUES(%s, %s, %s, %s);",
(order_id, bookids[i], counts[i], prices[i]))
# 删除订单信息
cursor = self.conn.execute(
"DELETE FROM new_order WHERE order_id = %s", (order_id, ))
cursor = self.conn.execute(
"DELETE FROM new_order_detail where order_id = %s",
(order_id, ))
return 200, "ok"
elif row_paid is not None:
buyer_id = row_paid['user_id']
store_id = row_paid['store_id']
state = row_paid['state']
cursor = conn.execute(
"SELECT store_id, user_id FROM user_store WHERE store_id = %s;",
(store_id, ))
row = conn.fetchone()
seller_id = row['user_id']
if not self.user_id_exist(seller_id):
return error.error_non_exist_user_id(seller_id)
if buyer_id != user_id:
return error.error_authorization_fail()
if state == "发货中":
return error.error_cancel()
# 订单未发货，接受取消操作，该订单写入历史订单，退款
cursor = self.conn.execute(
"INSERT INTO history_order(order_id,user_id,store_id) "
"VALUES(%s, %s, %s);", (order_id, user_id, store_id))
self.conn.execute(
"SELECT book_id, count, price FROM paid_order_detail WHERE order_id = %s;",
(order_id, ))
cursor = self.conn.fetchall()
total_price = 0
bookids = []
counts = []
prices = []
for row in cursor:
bookids.append(row['book_id'])
counts.append(row['count'])
prices.append(row['price'])
count = row['count']
price = row['price']
total_price = total_price + price * count
for i in range(len(bookids)):
cursor = self.conn.execute(
"INSERT INTO history_order_detail(order_id,book_id,count,price) "
"VALUES(%s, %s, %s, %s);",
(order_id, bookids[i], counts[i], prices[i]))
# 买家增加相应钱款
cursor = conn.execute(
"UPDATE users set balance = balance + %s"
"WHERE user_id = %s AND balance >= %s",
(total_price, buyer_id, total_price))
# 卖家减少相应钱款
cursor = conn.execute(
"UPDATE users set balance = balance - %s"
"WHERE user_id = %s", (total_price, seller_id))
# 删除订单信息
cursor = self.conn.execute(
"DELETE FROM paid_order WHERE order_id = %s", (order_id, ))
cursor = self.conn.execute(
"DELETE FROM paid_order_detail where order_id = %s",
(order_id, ))
return 200, "ok"
except pg.Error 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):
try: #查询订单信息中的值
#查询order_id, user_id, store_id
self.cursor = self.conn.cursor()
self.cursor.execute(
"SELECT order_id, user_id, store_id FROM new_order WHERE order_id = '%s'"
% (order_id, ))
row = self.cursor.fetchone()
if row is None:
return error.error_invalid_order_id(order_id)
order_id = row[0]
buyer_id = row[1]
store_id = row[2]
#验证buyer_id是不是相符合
if buyer_id != user_id:
return error.error_authorization_fail()
#查询buyer的账户信息，balance和password
self.cursor.execute(
"SELECT balance, password FROM usr WHERE user_id = '%s'" %
(buyer_id, ))
row = self.cursor.fetchone()
if row is None:
return error.error_non_exist_user_id(buyer_id)
balance = row[0]
#验证password是不是相符合
if password != row[1]:
return error.error_authorization_fail()
#查询user_store表，寻找到store_id和user_id的对应关系
self.cursor.execute(
"SELECT store_id, user_id FROM user_store WHERE store_id = '%s'"
% (store_id, ))
row = self.cursor.fetchone()
#如果store不存在，就返回store不存在的错误
if row is None:
return error.error_non_exist_store_id(store_id)
#即找到seller_id
seller_id = row[1]
#如果seller_id不存在，则返回seller_id不存在的错误
if not self.user_id_exist(seller_id):
return error.error_non_exist_user_id(seller_id)
#在new_order_detail中找到book_id,count,price, 以order_id为索引
self.cursor.execute(
"SELECT book_id, count, price FROM new_order_detail WHERE order_id = '%s'"
% (order_id, ))
cursor = self.cursor.fetchall()
total_price = 0
#计算总价
for row in cursor:
count = row[1]
price = row[2]
total_price = total_price + price * count
#如果账户余额小于总价，返回not_sufficent_funds错误
if balance < total_price:
return error.error_not_sufficient_funds(order_id)
#更新用户的账户余额
self.cursor.execute("UPDATE usr set balance = balance - %d"
"WHERE user_id = '%s' AND balance >= %d" %
(total_price, buyer_id, total_price))
#再次检查，如果账户余额小于总价，则返回not_sufficent_funds错误
if self.cursor.rowcount == 0:
return error.error_not_sufficient_funds(order_id)
#更新卖家的账户余额
self.cursor.execute("UPDATE usr set balance = balance+%d"
"WHERE user_id = '%s'" %
(total_price, seller_id)) ##
#检查id是否存在
if self.cursor.rowcount == 0:
return error.error_non_exist_user_id(seller_id)
#从new_order里删除这个order
self.cursor.execute("DELETE FROM new_order WHERE order_id = '%s'" %
(order_id, ))
if self.cursor.rowcount == 0:
return error.error_invalid_order_id(order_id)
#从new_order_detail中删除
self.cursor.execute(
"DELETE FROM new_order_detail where order_id = '%s'" %
(order_id, ))
if self.cursor.rowcount == 0:
return error.error_invalid_order_id(order_id)
self.conn.commit()
except psycopg2.errors.UniqueViolation:
return error.error_exist_user_id(user_id)
return 200, "ok"
def payment(self, user_id: str, password: str,
order_id: str) -> (int, str):
conn = self.conn
try:
#检查订单是否存在
cursor = conn.execute(
"SELECT order_id, user_id, store_id FROM new_order WHERE order_id = %s",
(order_id, ))
row = conn.fetchone()
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 users WHERE user_id = %s;",
(buyer_id, ))
row = conn.fetchone()
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 = %s;",
(store_id, ))
row = conn.fetchone()
if row is None:
return error.error_non_exist_store_id(store_id)
seller_id = row['user_id']
if not self.user_id_exist(seller_id):
return error.error_non_exist_user_id(seller_id)
conn.execute(
"SELECT book_id, count, price FROM new_order_detail WHERE order_id = %s;",
(order_id, ))
cursor = conn.fetchall()
total_price = 0
bookids = []
counts = []
prices = []
for row in cursor:
bookids.append(row['book_id'])
counts.append(row['count'])
prices.append(row['price'])
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 users set balance = balance - %s"
"WHERE user_id = %s AND balance >= %s",
(total_price, buyer_id, total_price))
"""if cursor.rowcount == 0:
return error.error_not_sufficient_funds(order_id)"""
#卖家增加相应钱款
cursor = conn.execute(
"UPDATE users set balance = balance + %s"
"WHERE user_id = %s", (total_price, seller_id))
"""if cursor.rowcount == 0:
return error.error_non_exist_user_id(buyer_id)"""
#删除订单信息
cursor = conn.execute("DELETE FROM new_order WHERE order_id = %s",
(order_id, ))
"""if cursor.rowcount == 0:
return error.error_invalid_order_id(order_id)"""
cursor = conn.execute(
"DELETE FROM new_order_detail where order_id = %s",
(order_id, ))
"""if cursor.rowcount == 0:
return error.error_invalid_order_id(order_id)"""
#增加状态信息
cursor = conn.execute(
"INSERT INTO paid_order(order_id,user_id,store_id,state) "
"VALUES(%s, %s, %s, '待发货');", (order_id, user_id, store_id))
for i in range(len(bookids)):
cursor = conn.execute(
"INSERT INTO paid_order_detail(order_id,book_id,count,price) "
"VALUES(%s, %s, %s, %s);",
(order_id, bookids[i], counts[i], prices[i]))
#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 payment(self, user_id: str, password: str,
order_id: str) -> (int, str):
try:
cursor = self.session.query(Order).filter_by(
id=order_id, status=Order_status.pending)
row = cursor.first()
if row is None:
return error.error_invalid_order_id(order_id)
buyer_id = row.buyer_id
store_id = row.store_id
if buyer_id != user_id:
return error.error_authorization_fail()
cursor = self.session.query(User).filter_by(user_id=buyer_id)
row = cursor.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 = self.session.query(Store).filter_by(store_id=store_id)
row = cursor.first()
if row is None:
return error.error_non_exist_store_id(store_id)
seller_id = row.owner
if self.session.query(User).filter_by(
user_id=seller_id).first() is None:
return error.error_non_exist_user_id(seller_id)
cursor = self.session.query(Order_info).filter_by(
order_id=order_id)
total_price = 0
for row in cursor.all():
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 = self.session.query(User).filter(
User.user_id == buyer_id, User.balance >= total_price)
rowcount = cursor.update(
{User.balance: User.balance - total_price})
if rowcount == 0:
return error.error_not_sufficient_funds(order_id)
cursor = self.session.query(User).filter(User.user_id == seller_id)
rowcount = cursor.update(
{User.balance: User.balance + total_price})
if rowcount == 0:
return error.error_non_exist_user_id(buyer_id)
cursor = self.session.query(Order).filter(Order.id == order_id)
rowcount = cursor.update({
Order.status: Order_status.paid,
Order.pt: datetime.datetime.now()
})
if rowcount == 0:
return error.error_invalid_order_id(order_id)
self.session.commit()
self.session.close()
except BaseException as e:
print(e)
return 530, "{}".format(str(e))
return 200, "ok"
