class Book(db.Model):
__tablename__ = 'book'
isbn = db.Column(db.String(32), primary_key=True)
book_name = db.Column(db.String(128), nullable=False)
image_url = db.Column(db.String(1000))
author = db.Column(db.String(128))
publisher = db.Column(db.String(128))
pubdate = db.Column(db.String(32))
original_price = db.Column(db.String(32), nullable=False)
# posts = db.relationship('Post', backref='book', lazy='dynamic')
def __init__(self, isbn, book_name, image_url='', author='', publisher='', pubdate='', original_price='0'):
self.isbn = isbn
self.book_name = book_name
self.image_url = image_url
self.author = author
self.publisher = publisher
self.pubdate = pubdate
self.original_price = original_price
@classmethod
def get_by_isbn(cls, isbn):
return cls.query.filter_by(isbn=isbn).first()
@classmethod
def search_by_name(cls, name):
return cls.query.filter(cls.book_name.like('%' + name + '%')).all() if name else None
@classmethod
def get_posts(cls, isbn):
return cls.query.filter_by(isbn=isbn).first().posts
class Admin(db.Model):
__tablename__ = 'admin'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
username = db.Column(db.String(128), nullable=False)
password = db.Column(db.String(256), nullable=False)
register_time = db.Column(db.DateTime, nullable=False)
def __init__(self, username, password):
self.username = username
self.password = generate_password_hash(password)
self.register_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
def verify_password(self, password):
return check_password_hash(self.password, password)
@classmethod
def get_by_username(cls, username):
return cls.query.filter_by(username=username).first()
class OrderPost(db.Model):
"""
订单order与书本post中间关系表
"""
__tablename__ = "order_post"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
order_id = db.Column(db.String(32), db.ForeignKey('order.id'))
post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
order = db.relationship('Order', backref=db.backref(
'post_items')) # order表的反向引用,由order.posts访问order_post列表
post = db.relationship(
'Post', backref=db.backref('orders')) # 关联post表,由order_post.post访问post
def __init__(self, order_id, post_id):
self.order_id = order_id
self.post_id = post_id
class User(db.Model):
__tablename__ = 'user'
openid = db.Column(db.String(128), primary_key=True)
is_authorized = db.Column(db.Boolean, nullable=False)
name = db.Column(db.String(16), nullable=False)
student_id = db.Column(db.String(16), nullable=False)
card_image_url = db.Column(db.String(128))
major = db.Column(db.String(64))
address = db.Column(db.String(16), nullable=False)
money_paid = db.Column(db.Integer, nullable=False)
money_earned = db.Column(db.Integer, nullable=False)
token = db.Column(db.String(128), unique=True)
# posts = db.relationship('Post', backref='user', lazy='dynamic')
# cart_items = db.relationship('CartItem', backref='user')
def __init__(self,
openid,
name='无',
student_id='none',
address='none',
token='none'):
self.openid = openid
self.is_authorized = False
self.name = name
self.student_id = student_id
self.address = address
self.money_paid = 0
self.money_earned = 0
self.token = token
@classmethod
def get_by_openid(cls, openid):
return cls.query.filter_by(openid=openid).first()
@classmethod
def get_by_token(cls, token):
return cls.query.filter_by(token=token).first()
@classmethod
def get_all(cls):
return cls.query.all()
class CartItem(db.Model):
__tablename__ = 'cart_item'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
is_checked = db.Column(db.Boolean, nullable=False)
user_openid = db.Column(db.String(128), db.ForeignKey('user.openid'))
post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
user = db.relationship('User', backref=db.backref('cart'))
post = db.relationship('Post', backref=db.backref('cart_items'))
def __init__(self, openid, post_id):
self.is_checked = False
self.user_openid = openid
self.post_id = post_id
def get_cart_item_info(self):
"""获取购物车item信息"""
return dict(cartItemId=self.id,
postId=self.post.id,
bookName=self.post.book_name,
imageName=self.post.image_name,
sale=self.post.sale_price,
new=self.post.new,
addr=self.post.seller.address,
author=self.post.book.author,
publisher=self.post.book.publisher,
pubdate=self.post.book.pubdate,
originalPrice=self.post.book.original_price,
checked=self.is_checked,
valid=self.post.is_valid)
@classmethod
def get_by_id(cls, item_id):
return cls.query.filter_by(id=item_id).first()
@classmethod
def get_by_openid(cls, openid):
return cls.query.filter_by(user_openid=openid).order_by(
cls.id.desc()).all()
class ImageFile(db.Model):
__tablename__ = 'ImageFile'
id = db.Column(db.Integer, primary_key=True)
imgname = db.Column(db.String(1000), nullable=False)
filename = db.Column(db.String(1000), nullable=False)
filehash = db.Column(db.String(128), nullable=False, unique=True)
nickname = db.Column(db.String(128), nullable=False)
openid = db.Column(db.String(128), nullable=False)
dorm = db.Column(db.String(128), nullable=False)
uploadtime = db.Column(db.DateTime, nullable=False)
size = db.Column(db.Integer, nullable=False)
liked = db.Column(db.Integer, nullable=False)
def __init__(self,
imgname='',
filename='',
nickname='',
openid='',
dorm='',
size=0,
liked=0):
self.imgname = imgname
self.filename = filename
self.filehash = self._hash_filename(filename)
self.nickname = nickname
self.openid = openid
self.dorm = dorm
self.uploadtime = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
self.size = int(size)
self.liked = liked
@staticmethod
def _hash_filename(filename):
# 获得文件名后缀
_, _, suffix = filename.rpartition('.')
return '{}.{}'.format(uuid.uuid4().hex, suffix)
@property
def path(self):
return get_file_path(self.filehash)
@property
def nail_path(self):
return get_nail_path(self.filehash)
@property
def url(self):
return self.get_url()
@property
def quote_url(self):
return quote(self.url)
@classmethod
def get_all(cls):
return cls.query.all()
@classmethod
def get_by_filehash(cls, filehash, code=404):
return cls.query.filter_by(filehash=filehash).first() or abort(code)
@classmethod
def get_by_openid(cls, openid):
return cls.query.filter_by(openid=openid).all()
@classmethod
def get_hot(cls, count, code=404):
return cls.query.order_by(cls.liked.desc()).limit(count) or abort(code)
@classmethod
def like_handler(cls, filehash):
target = cls.query.filter_by(filehash=filehash).first()
target.liked += 1
return True
@classmethod
def create_by_upload_file(cls, uploaded_file, imgname, filename, nickname,
openid, dorm):
res = cls(imgname, filename, nickname, openid, dorm, 0, 0)
with open(res.path, 'wb') as f:
f.write(uploaded_file)
# uploaded_file.save(res.path)
thumb = Image.open(res.path)
thumb.thumbnail((300, 300))
thumb.save(res.nail_path)
filestat = os.stat(res.path)
res.size = filestat.st_size
return res
def get_url(self):
return 'http://{host}/image/{id}'.format(host=request.host, id=self.id)
class Post(db.Model):
__tablename__ = "post"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
book_name = db.Column(db.String(128), nullable=False)
image_name = db.Column(db.String(128), nullable=False, unique=True)
post_time = db.Column(db.DateTime, nullable=False)
sale_price = db.Column(db.Integer, nullable=False)
new = db.Column(db.SmallInteger, nullable=False) # 0为五成,1为七成,2为九成,3为全新
description = db.Column(db.String(1000))
book_isbn = db.Column(db.String(32), db.ForeignKey('book.isbn'), nullable=False)
seller_openid = db.Column(db.String(128), db.ForeignKey('user.openid'), nullable=False)
book = db.relationship('Book', backref=db.backref('posts'))
seller = db.relationship('User', backref=db.backref('posts'))
is_valid = db.Column(db.Boolean, nullable=False) # 被下架或被下单则为False
def __init__(self, isbn, openid, bookname='', price=0, new=0, description=''):
self.book_name = bookname
self.image_name = uuid.uuid4().hex # 随机hash值作为图片文件名
self.post_time = datetime.now()
self.sale_price = price
self.new = new
self.description = description
self.book_isbn = isbn
self.seller_openid = openid
self.is_valid = True
def get_post_info(self):
"""
获取该post信息
:return: {bookName, imageUrl, sale, new, addr, author, publisher}
"""
return dict(postId=self.id,
bookName=self.book_name,
imageUrl=bucket_url + self.image_name,
postTime=self.post_time.strftime("%Y-%m-%d %H:%M:%S"),
sale=self.sale_price,
new=self.new,
addr=self.seller.address,
author=self.book.author,
publisher=self.book.publisher,
pubdate=self.book.pubdate,
originalPrice=self.book.original_price)
@classmethod
def get_by_id(cls, post_id):
return cls.query.filter_by(id=post_id).first()
@classmethod
def get_valid_by_id(cls, post_id):
return cls.query.filter_by(id=post_id, is_valid=True).first()
@classmethod
def get_by_isbn(cls, isbn):
return cls.query.filter_by(book_isbn=isbn).all()
@classmethod
def search_by_name(cls, name):
return cls.query.filter(cls.book_name.like('%' + name + '%')).all() if name else None
@classmethod
def get_by_user(cls, openid, count):
return cls.query.filter_by(seller_openid=openid).order_by(cls.post_time.desc()).limit(count).all()
@classmethod
def get_random(cls, count):
return cls.query.order_by(func.random()).limit(count).all()
class Order(db.Model):
__tablename__ = "order"
id = db.Column(db.String(32), primary_key=True)
deal_time = db.Column(db.DateTime, nullable=False) # 下单时间
deadline = db.Column(db.String(32)) # 送达时限
status = db.Column(db.SmallInteger, nullable=False) # 0为已下单/待支付,1为已支付/待送,2为已送/待取,3为完成
total_price = db.Column(db.Integer, nullable=False) # 总价
# post_id = db.Column(db.Integer, db.ForeignKey('post.id'), nullable=False)
# post = db.relationship('Post', backref=db.backref('order'))
seller_openid = db.Column(db.String(128), nullable=False) # 卖方openid,冗余信息,减少查询量
buyer_openid = db.Column(db.String(128), db.ForeignKey('user.openid'), nullable=False)
buyer = db.relationship('User', backref=db.backref('bought_deals'))
prepay_id = db.Column(db.String(64))
prepay_timestamp = db.Column(db.Integer)
delivery_image_url = db.Column(db.String(128))
is_effective = db.Column(db.Boolean, nullable=False) # 取消订单则为False
def __init__(self, deadline, total_price, seller, buyer):
self.now = datetime.now()
self.id = self.now.strftime("%Y%m%d%H%M%S%f") + str(random.randint(1000, 9999)) # 毫秒时间加4位随机数
self.deal_time = self.now
self.deadline = deadline
self.status = 0
self.total_price = total_price
self.seller_openid = seller
self.buyer_openid = buyer
self.is_effective = True
@classmethod
def create_by_prepay(cls, deadline, post_list, buyer, pay_client):
"""
创建订单的同时发起预支付
:param deadline: 送达截至时间
:param post_list: 书本post_id列表
:param buyer: 买方openid
:param pay_client: WeChatPay对象
:return order数据表对象, order_post中间表对象列表
"""
post_0 = Post.get_by_id(post_list[0]) # 以第一项post为基准
if not post_0:
raise InvalidPostException('Invalid post in creating order!')
seller = post_0.seller_openid # 唯一的卖方openid
total_price = post_0.sale_price # post总价
for post_id in post_list[1:]: # 剩余的post
post = Post.get_valid_by_id(post_id)
if post and post.seller_openid == seller: # 有效的post且为同一卖方
total_price += post.sale_price
post.is_valid = False # 使post失效
else:
raise InvalidPostException('Invalid post in creating order!')
order = cls(deadline, total_price, seller, buyer) # 校验通过,创建order
order_post_list = list()
for post_id in post_list:
order_post = OrderPost(order_id=order.id, post_id=post_id) # 创建order与post中间关系
order_post_list.append(order_post)
prepay_data = pay_client.order.create( # 发起预支付
trade_type='JSAPI', body='不渴鸟BOOKBIRD-书本', notify_url='https://www.bookbird.cn/api/mp/order/notify',
total_fee=total_price, user_id=order.buyer_openid, out_trade_no=order.id,
time_start=order.now, time_expire=order.now + timedelta(hours=2))
order.prepay_id = prepay_data['prepay_id']
order.prepay_timestamp = int(mktime(order.now.timetuple()))
return order, order_post_list
def get_prepay_remain_time(self):
"""获取预支付订单剩余支付时间"""
remain = 900 - int(time()) + self.prepay_timestamp
return remain if remain > 0 else 0
def cancel(self):
"""关闭订单事务"""
self.is_effective = False
for item in self.post_items:
item.post.is_valid = True # 恢复post
def get_order_info(self):
"""获取订单信息"""
return dict(dealTime=self.deal_time.strftime("%Y-%m-%d %H:%M:%S"),
deadline=self.deadline,
status=self.status,
address=self.post_items[0].post.seller.address,
deliveryImage=self.delivery_image_url,
isEffective=self.is_effective)
def get_preview_info(self, user):
"""获取订单卡片预览信息(包括书本部分信息)"""
return dict(orderId=self.id,
orderTime=self.deal_time.strftime("%Y-%m-%d %H:%M:%S"),
bookName=self.post_items[0].post.book_name,
imageName=self.post_items[0].post.image_name,
deadline=self.deadline,
address=self.post_items[0].post.seller.address,
totalPrice=self.total_price,
identity='seller' if user == self.seller_openid else 'buyer',
status=self.status)
@classmethod
def get_by_id(cls, order_id):
return cls.query.filter_by(id=order_id).first()
@classmethod
def get_by_buyer(cls, buyer):
return cls.query.filter_by(buyer_openid=buyer).order_by(cls.deal_time.desc()).all()
@classmethod
def get_by_seller(cls, seller):
return cls.query.filter_by(seller_openid=seller).order_by(cls.deal_time.desc()).all()
'''return cls.query.join(Post, Post.id == cls.post_id)\
.filter(Post.seller_openid == seller).order_by(cls.deal_time.desc()).all()'''
@classmethod
def get_dynamics(cls, user):
return cls.query.filter(or_(cls.seller_openid == user, cls.buyer_openid == user))\
.order_by(cls.deal_time.desc()).all()
'''return cls.query.join(Post, Post.id == cls.post_id)\