def receive_book(self, user_id: str, order_id: str): try: #判断该用户是否存在 if not self.user_id_exist(user_id): return error.error_non_exist_user_id(user_id) + (order_id, ) print("用户存在") #判断该订单是否存在在未收货里面 row = self.session.query(New_order_unreceived).filter_by( order_id=order_id) order = row.first() print("未收货订单", order) if order is None: return error.error_invalid_order_id(order_id) buyer_id = order.buyer_id #判断该用户是否有这个订单。。。。验证收货的人是否正确 if user_id != buyer_id: return error.error_authorization_fail() #有该订单,收货 #添加收货时间 timenow = datetime.now().strftime("%Y-%m-%d %H:%M:%S") print("******已收货") row.update({New_order_unreceived.receive_time: timenow}) self.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, password, add_value) -> (int, str): try: usr = self.session.query(Users).filter_by(user_id=user_id).first() #usr = self.session.execute("SELECT password from usr where user_id= '%s'"%(user_id,)).fetchone() #row = cursor.fetchone() if usr is None: return error.error_authorization_fail() if usr.password != password: return error.error_authorization_fail() cursor = self.session.query(Users).filter(Users.user_id == user_id) rowcount = cursor.update( {Users.balance: Users.balance + add_value}) # cursor = self.session.execute( # "UPDATE usr SET balance = balance + %d WHERE user_id = '%s'"% # (add_value, user_id)) #if cursor.rowcount == 0: if rowcount == 0: return error.error_non_exist_user_id(user_id) self.session.commit() self.session.close() except sqlite.Error as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def deliver_book(self,user_id:str,order_id:str): try: #判断该用户是否存在 if not self.user_id_exist(user_id): return error.error_non_exist_user_id(user_id) + (order_id, ) print("用户存在") #判断该订单是否存在 row=self.session.query(New_order_undelivered).filter_by(order_id=order_id) order=row.first() print("未发货订单",order) if order is None: return error.error_invalid_order_id(order_id) store_id=order.store_id #判断该用户是否有这个店铺。。。。验证发货的人是否正确 query=self.session.query(User_store).filter_by(store_id=store_id).first() seller_id=query.user_id if seller_id != user_id: return error.error_authorization_fail() #有该订单,发货 buyer_id=order.buyer_id price=order.price #删除未发货 row.delete() #添加已发货 timenow = datetime.now().strftime("%Y-%m-%d %H:%M:%S") print("******已发货") new_order=New_order_unreceived( order_id=order_id, buyer_id=buyer_id, store_id=store_id, price=price, purchase_time=timenow, receive_time=None ) self.session.add(new_order) self.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 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.session.execute("INSERT into user_store(store_id, user_id) VALUES ('%s', '%s')"% (store_id, user_id)) # self.session.commit() usr_store = User_store(store_id=store_id,user_id=user_id) self.session.add(usr_store) self.session.commit() self.session.close() except sqlite.Error 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): 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) store = self.session.query(Store).filter(store_id == store_id , book_id == book_id).first() store.stock_level += add_stock_level #self.session.execute("UPDATE store SET stock_level = stock_level + %d " #"WHERE store_id = '%s' AND book_id = %d" % (add_stock_level, store_id, int(book_id))) self.session.commit() self.session.close() except sqlite.Error as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"
def cancel(self, buyer_id: str, order_id: str): if not self.user_id_exist(buyer_id): code, mes = error.error_non_exist_user_id(buyer_id) return code, mes #是否属于未付款订单 store = self.session.query(New_order_unpaid).filter( New_order_unpaid.buyer_id == buyer_id, New_order_unpaid.commit_time != None).first() if store is not None: store_id = store.store_id price = store.price query = self.session.query(New_order_unpaid).filter( New_order_unpaid.order_id == order_id) query.delete() else: # 是否属于已付款且未发货订单 order_info = self.session.query(New_order_undelivered).filter( New_order_undelivered.buyer_id == buyer_id, New_order_undelivered.order_id == order_id, New_order_undelivered.purchase_time != None).first() if order_info is not None: store_id = order_info.store_id price = order_info.price #删除订单 query = self.session.query(New_order_undelivered).filter( New_order_undelivered.order_id == order_id, New_order_undelivered.purchase_time != None) query.delete() # 卖家减钱 #查询卖家 user_id = self.session.query(User_store).filter( User_store.store_id == store_id).first() cursor = self.session.query(Users).filter_by( user_id=user_id.user_id) rowcount = cursor.update( {Users.balance: Users.balance - price}) #买家加钱 cursor = self.session.query(Users).filter_by(user_id=buyer_id) rowcount = cursor.update( {Users.balance: Users.balance + price}) else: #已发货 无法取消 需要申请售后 #无法取消 return error.error_invalid_order_id(order_id) #加入new_order_cancel中 timenow = datetime.now().strftime("%Y-%m-%d %H:%M:%S") cancel_order = New_order_canceled(order_id=order_id, buyer_id=buyer_id, store_id=store_id, price=price, cancel_time=timenow) self.session.add(cancel_order) self.session.commit() #加库存 store = self.session.query(Store).filter_by(store_id=store_id) stores = store.first() cursor = self.session.query(New_order_detail).filter( New_order_detail.order_id == order_id, New_order_detail.book_id == stores.book_id).first() count = cursor.count # cursor = self.session.query(Store).filter(Store.book_id==book_id, Store.store_id==store_id, Store.stock_level >= count) # rowcount = cursor.update({Store.stock_level: Store.stock_level - count}) store.update({Store.stock_level: Store.stock_level + count}) # rowcount = self.session.query(Store).update({Store.stock_level: Store.stock_level+count}) self.session.commit() self.session.close() return 200, 'ok'
def search_history_status(self, buyer_id: str, flag: int): try: if not self.user_id_exist(buyer_id): #print('********') code, mes = error.error_non_exist_user_id(buyer_id) return code, mes, " " #查所有订单 if flag == 0: record_listn = self.session.query(New_order_detail).filter( New_order_detail.buyer_id == buyer_id) print(record_listn) records = [] for record in record_listn: records.append({ "order_id": record.order_id, "buyer_id": record.buyer_id, "store_id": record.store_id, "book_id": record.book_id, "count": record.count, "price": record.price }) self.session.close() #未付款 if flag == 1: record_list1 = self.session.query(New_order_unpaid).filter( New_order_unpaid.buyer_id == buyer_id, New_order_unpaid.commit_time != None) print(record_list1) records = [] for record in record_list1: record_infos = self.session.query( New_order_detail).filter_by( order_id=record.order_id).all() records.append({ "order_id": record.order_id, "buyer_id": record.buyer_id, "store_id": record.store_id, "commit_time": record.commit_time, "status": '未付款', "book_list": [{ "book_id": rei.book_id, "count": rei.count, "price": rei.price } for rei in record_infos] }) self.session.close() #已付款待发货 if flag == 2: record_list = self.session.query(New_order_undelivered).filter( New_order_undelivered.buyer_id == buyer_id, New_order_undelivered.purchase_time != None) print(record_list) records = [] for record in record_list: record_infos = self.session.query( New_order_detail).filter_by( order_id=record.order_id).all() records.append({ "order_id": record.order_id, "buyer_id": record.buyer_id, "store_id": record.store_id, "purchase_time": record.purchase_time, "status": '已付款待发货', "book_list": [{ "book_id": rei.book_id, "count": rei.count, "price": rei.price } for rei in record_infos] }) self.session.close() #已发货待收货 if flag == 3: record_list2 = self.session.query(New_order_unreceived).filter( New_order_unreceived.buyer_id == buyer_id, New_order_unreceived.purchase_time != None) print(record_list2) records = [] for record in record_list2: record_infos = self.session.query( New_order_detail).filter_by( order_id=record.order_id).all() records.append({ "order_id": record.order_id, "buyer_id": record.buyer_id, "store_id": record.store_id, "purchase_time": record.purchase_time, "status": '已发货待收货', "book_list": [{ "book_id": rei.book_id, "count": rei.count, "price": rei.price } for rei in record_infos] }) self.session.close() #已收货 if flag == 4: record_list3 = self.session.query(New_order_unreceived).filter( New_order_unreceived.buyer_id == buyer_id, New_order_unreceived.receive_time != None) print(record_list3) records = [] for record in record_list3: record_infos = self.session.query( New_order_detail).filter_by( order_id=record.order_id).all() records.append({ "order_id": record.order_id, "buyer_id": record.buyer_id, "store_id": record.store_id, "receive_time": record.receive_time, "status": '已收货', "book_list": [{ "book_id": rei.book_id, "count": rei.count, "price": rei.price } for rei in record_infos] }) self.session.close() if flag == 5: record_list4 = self.session.query(New_order_canceled).filter( New_order_canceled.buyer_id == buyer_id, New_order_canceled.cancel_time != None) print(record_list4) records = [] for record in record_list4: record_infos = self.session.query( New_order_detail).filter_by( order_id=record.order_id).all() records.append({ "order_id": record.order_id, "buyer_id": record.buyer_id, "store_id": record.store_id, "cancel_time": record.cancel_time, "status": '已取消', "book_list": [{ "book_id": rei.book_id, "count": rei.count, "price": rei.price } for rei in record_infos] }) self.session.close() except BaseException as e: return 530, "{}".format(str(e)), [] return 200, "ok", records
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 由'user_id'\_'store_id'_一个唯一标识符组成 #下单成功后将uid赋值给order_id uid = "{}_{}_{}".format(user_id, store_id, str(uuid.uuid1())) for book_id, count in id_and_count: # print(id_and_count) # print(book_id) # print(type(book_id)) # print(count) # print(type(count)) # # cursor = self.conn.execute( # # "SELECT book_id, stock_level, book_info FROM store " # # "WHERE store_id = ? AND book_id = ?;", # # (store_id, book_id) # #不加这个转换 后面用%d select时会报错 book_id = int(book_id) # print(type(book_id)) #book = self.session.execute("SELECT stock_level,price FROM store WHERE store_id = '%s'AND book_id = %d"%(store_id, book_id)).fetchone() book = self.session.query(Store).filter_by( book_id=book_id, store_id=store_id).first() #row = cursor.fetchone() if book is None: return error.error_non_exist_book_id( str(book_id)) + (order_id, ) # stock_level = book[0] # #print("stock_level:",stock_level) # price=book[1] stock_level = book.stock_level price = book.price # book_info = book[2] # book_info_json = json.loads(book_info) # price = book_info_json.get("price") if stock_level < count: return error.error_stock_level_low( str(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)) cursor = self.session.query(Store).filter( Store.book_id == book_id, Store.store_id == store_id, Store.stock_level >= count) rowcount = cursor.update( {Store.stock_level: Store.stock_level - count}) # res = self.session.execute( # "UPDATE store set stock_level = stock_level - %d WHERE store_id = '%s' and book_id = %d and stock_level >=%d" % ( # count, store_id, book_id, count)) #cursor=self.session.execute() #if res.rowcount == 0: if rowcount == 0: return error.error_stock_level_low( str(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.session.execute( # "INSERT INTO new_order_detail(order_id, book_id, count, price) VALUES('%s',%d, %d, %d);" % ( # uid, book_id, count, price)) new_order_info = New_order_detail(order_id=uid, book_id=book_id, buyer_id=user_id, store_id=store_id, count=count, price=price) self.session.add(new_order_info) # self.conn.execute( # "INSERT INTO new_order(order_id, store_id, user_id) " # "VALUES(?, ?, ?);", # (uid, store_id, user_id)) #记录下单时间 timenow = datetime.now().strftime("%Y-%m-%d %H:%M:%S") #将新订单加入待付款 # self.session.execute( # "INSERT INTO new_order_unpaid(order_id, store_id, buyer_id,price,commit_time) VALUES('%s','%s','%s',%d,'%s');" % ( # uid, store_id, user_id,price,timenow)) new_order_unpaid = New_order_unpaid(order_id=uid, store_id=store_id, buyer_id=user_id, price=price, commit_time=timenow) self.session.add(new_order_unpaid) self.session.commit() self.session.close() print("order_id", uid) #===============注释掉 r.setex(uid, 15, 'order') #先设置30s,测试好之后再改成其他时间 #=============== 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 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() #待支付中是否有该用户该订单 # row = self.session.execute( # "SELECT buyer_id,price,store_id FROM new_order_unpaid WHERE order_id = '%s'" % (order_id)).fetchone() row = self.session.query(New_order_unpaid).filter_by( order_id=order_id).first() print(row) if row is None: return error.error_invalid_order_id(order_id) # buyer_id = row[0] # price=row[1] # store_id = row[2] buyer_id = row.buyer_id price = row.price 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 = self.session.execute( # "SELECT balance, password FROM usr WHERE user_id = '%s';" % (buyer_id)).fetchone() row = self.session.query(Users).filter_by(user_id=buyer_id).first() if row is None: return error.error_non_exist_user_id(buyer_id) check_password = row.password balance = row.balance #balance = row[0] #if password != row[1]: if password != check_password: return error.error_authorization_fail() #记录卖家id # cursor = self.session.execute("SELECT store_id, user_id FROM user_store WHERE store_id = '%s';"%(store_id)) # row = cursor.fetchone() row = self.session.query(User_store).filter_by( store_id=store_id).first() if row is None: return error.error_non_exist_store_id(store_id) #seller_id = row[1] seller_id = row.user_id if not self.user_id_exist(seller_id): return error.error_non_exist_user_id(seller_id) #cursor = self.session.execute("SELECT book_id, count, price FROM new_order_detail WHERE order_id = '%s';"%(order_id)) cursor = self.session.query(New_order_detail).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.execute("UPDATE usr set balance = balance - %d WHERE user_id = '%s' AND balance >= %d"%(total_price, buyer_id, total_price)) cursor = self.session.query(Users).filter( Users.user_id == buyer_id, Users.balance >= total_price) rowcount = cursor.update( {Users.balance: Users.balance - total_price}) if rowcount == 0: return error.error_not_sufficient_funds(order_id) #卖家加钱 #cursor = self.session.execute("UPDATE usr set balance = balance + %d WHERE user_id = '%s'"%(total_price, seller_id)) cursor = self.session.query(Users).filter( Users.user_id == seller_id) rowcount = cursor.update( {Users.balance: Users.balance + total_price}) if rowcount == 0: return error.error_non_exist_user_id(seller_id) # 删除待付订单 #cursor = self.session.execute("DELETE FROM new_order_unpaid WHERE order_id = '%s';"% (order_id )) query = self.session.query(New_order_unpaid).filter( New_order_unpaid.order_id == order_id) query.delete() print("*****") rowcount = query.first() #print(cursor.rowcount) #if cursor.rowcount == 0: if rowcount == 0: return error.error_invalid_order_id(order_id) #删除订单的详细信息 #还未发货暂不删除订单详细信息 # cursor = self.session.execute("DELETE FROM new_order_detail where order_id = ?", (order_id, )) # if cursor.rowcount == 0: # return error.error_invalid_order_id(order_id) #在待发货中加入该订单 timenow = datetime.now().strftime("%Y-%m-%d %H:%M:%S") print("******") new_orde = New_order_undelivered(order_id=order_id, buyer_id=buyer_id, store_id=store_id, price=price, purchase_time=timenow) self.session.add(new_orde) # re=self.session.execute( # "INSERT INTO new_order_undelivered(order_id, buyer_id,store_id,price,purchase_time) VALUES('%s', '%s','%s',%d,'%s');" % ( # order_id, buyer_id, store_id, price,timenow)) # print(re.rowcount) #self.session.commit() self.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,price:int, book_json_str: str, stock_level: int): try: if not self.user_id_exist(user_id): #print(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) # print("*******") book = json.loads(book_json_str) # print(book) # print("******",type(book)) #这里的tag是list型的 数据库定义的是text型 但是可以正常插入 如果查找书籍时出现问题 在此修改 # thelist = [] # 由于没有列表类型,故使用将列表转为text的办法 # for tag in book.get('tags'): # if tag.strip() != "": # # book.tags.append(tag) # thelist.append(tag) # book['tags'] = str(thelist) # 解析成list请使用eval( #如果图书要插入图片 后期在此处更改 #插入bookinfo # print('tags',book['tags']) # print("type of tag:",type(book['tags'])) # print("type of binding:",type(book['binding'])) #print("type of currency unit:",type(book['currency_unit'])) ############ #由于助教给的body里没有currency_unit所以这里没有加入该值 #如需后期加入 在insert 中更改即可 # print("bookprice",book['price']) ##重要!!!!! #判断书是否已经加在书里 #row = self.session.execute("SELECT book_id FROM book WHERE book_id = '%s';" % (book_id,)).fetchone() #row=self.session.query(BookWhole).filter_by(book_id=book_id).first() ##注释了插入书的部分 #if row is None: # book = json.loads(book_json_str) # thelist = [] # 由于没有列表类型,故使用将列表转为text的办法 # for tag in book.get('tags'): # if tag.strip() != "": # # book.tags.append(tag) # thelist.append(tag) # book['tags'] = str(thelist) # self.session.execute( # "INSERT into book( book_id, title,author,publisher,original_title,translator," # "pub_year,pages,original_price,binding,isbn,author_intro,book_intro," # "content,tags) VALUES ( :book_id, :title,:author,:publisher,:original_title,:translator," # ":pub_year,:pages,:original_price,:binding,:isbn,:author_intro,:book_intro," # ":content,:tags)", # {'book_id': book['id'], 'title': book['title'], 'author': book['author'], # 'publisher': book['publisher'], 'original_title': book['original_title'], # 'translator': book['translator'], # 'pub_year': book['pub_year'], 'pages': book['pages'], 'original_price': book['price'], # 'binding': book['binding'], 'isbn': book['isbn'], 'author_intro': book['author_intro'], # 'book_intro': book['book_intro'], # 'content': book['content'], 'tags': book['tags']}) #####注意这里还未尝试插图片!!!!后期加 # books = BookWhole() # books.book_id = book['id'] # books.title = book['title'] # books.author= book['author'] # books.publisher=book['publisher'] # books.original_title= book['original_title'] # books.translator=book['translator'] # books.pub_year=book['pub_year'] # books.pages=book['pages'] # books.original_price=book['price'] # books.binding=book['binding'] # books.isbn=book['isbn'] # books.author_intro=book['author_intro'] # books.book_intro=book['book_intro'] # books.content= book['content'] # books.tags=book['tags'] # self.session.add(books) # print("book info success") # self.session.commit() store=Store() store.book_id=int(book_id) store.store_id=store_id store.stock_level=stock_level ###这里需改动 函数接口中增加price 这里为用户输入的价格 不是书的零售价 目前不影响测试 #store.price=book['price'] store.price=price self.session.add(store) #self.session.execute("INSERT into store(store_id, book_id, stock_level,price) VALUES ('%s', %d, %d,%d)"%(store_id, int(book_id), stock_level,book['price'])) self.session.commit() self.session.close() except sqlite.Error as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok"