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"