class HallModel(db.Model):
__tablename__ = 'hall'
hallId = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True,
nullable=False)
name = db.Column(db.String(length=80), unique=True, nullable=True)
numOfSeats = db.Column(db.Integer)
rows = db.Column(db.Integer)
seatsPerRow = db.Column(db.Integer)
availability = db.Column(db.Boolean)
seances = db.relationship("SeanceModel", backref="hall")
seats = db.relationship("SeatModel", backref="hall", cascade="all,delete")
def __init__(self,
_id=None,
name=None,
rows=None,
seatsPerRow=None,
availability=True,
numOfSeats=None):
self.id = _id or id
self.name = name
self.numOfSeats = rows * seatsPerRow
self.rows = rows
self.seatsPerRow = seatsPerRow
self.availability = availability
class SeanceModel(db.Model):
__tablename__ = 'seance'
seanceId = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True,
nullable=False)
time = db.Column(db.Time, nullable=False)
date = db.Column(db.Date, nullable=False)
hallId = db.Column(UUID(as_uuid=True), db.ForeignKey('hall.hallId'))
movieId = db.Column(UUID(as_uuid=True), db.ForeignKey('movie.movieId'))
ticketsSold = db.Column(db.Integer, default=0)
tickets = db.relationship("TicketModel", backref="seance")
def __init__(self,
_id=None,
time=None,
date=None,
hallId=None,
movieId=None,
ticketsSold=None):
self.id = _id or id
self.time = time
self.date = date
self.hallId = hallId
self.movieId = movieId
self.ticketsSold = ticketsSold
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
content = db.Column(db.Text)
# Pour stocker l'image sous une suite de bytes BLOB. On encode l'encode en BLOB (app.py), elle est stocké dans la
# database, puis elle sera décodé en BLOB lors de son affichage (homepage.html.jinja2)
image_data = db.Column(db.BLOB)
image_size = db.Column(db.Integer)
tags = db.Column(db.Text)
# Relationship User <--> Post
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
# Relationship Comment <--> Post
# back-populates permet que lorsqu'on ajoute un commentaire il est aussi ajouté dans l'attribut comment de post
comments = db.relationship('Comment', back_populates='post')
# Relationship PostLike <--> Post
# backref <=> comment on appel l'attribut de la jointure ici comment.post
likes = db.relationship('PostLike', backref='post', lazy='dynamic')
class Post(db.Model):
id = db.Column(
db.Integer, primary_key=True
) # Post id, will also be used for unique post url and image naming as we don't
# have a dedicated server to store images for this project
user_id = db.Column(db.Integer, db.ForeignKey('user.id')) # Author id
username = db.Column(
db.String
) # Easier to display username this way (avoid circular import)
date = db.Column(db.Date, default=datetime.now())
image_name = db.Column(
db.String
) # For local storage, post_id + file_extension (.jpg, .png ...)
description = db.Column(db.Text)
hearts = db.Column(db.Integer) # The number of "likes" of the post
current_user_liked_it = db.Column(
db.Boolean
) # To know if the current user liked this post or not (no multiple likes)
comments = db.relationship('Comment', backref='article', lazy=True)
tags = db.Column(db.Text)
def __init__(self,
user_id,
username,
image_name,
description,
tags,
hearts=0):
self.user_id = user_id
self.username = username
self.image_name = image_name
self.description = description
self.hearts = hearts
self.current_user_liked_it = False
self.tags = tags
class MovieModel(db.Model):
__tablename__ = 'movie'
movieId = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True,
nullable=False)
title = db.Column(db.String(length=120))
director = db.Column(db.String(length=80))
releaseDate = db.Column(db.Date)
closeDate = db.Column(db.Date)
ageCategory = db.Column(db.String(length=40))
movieCategory = db.Column(db.String(length=80))
duration = db.Column(db.Integer)
seances = db.relationship("SeanceModel", backref="movie")
def __init__(self,
_id=None,
title=None,
director=None,
releaseDate=None,
closeDate=None,
ageCategory=None,
movieCategory=None,
duration=None):
self.id = _id or id
self.title = title
self.director = director
self.releaseDate = releaseDate
self.closeDate = closeDate
self.ageCategory = ageCategory
self.movieCategory = movieCategory
self.duration = duration
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
last_name = db.Column(db.Text)
first_name = db.Column(db.Text)
badge_number = db.Column(db.Text)
reservations = db.relationship('Reservation',
backref='user',
lazy='dynamic')
class Products(db.Model):
__tablename__='products'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text)
food_type = db.Column(db.Integer) # Either SANDWICH , DRINK or DESSERT
price = db.Column(db.Float)
stock = db.Column(db.Integer)
ingredients = db.relationship('Ingredients', backref='Items', secondary=items_ingredients_junction_table)
class Comment(db.Model):
id = db.Column(db.Integer, primary_key=True)
content = db.Column(db.Text)
# Relationship Post <--> Comment
post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
post = db.relationship('Post', back_populates='comments')
# Relationship User <--> Comment
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
class Order(db.Model):
id = db.Column(db.Integer, primary_key=True)
order_date = db.Column(db.DateTime)
delivery_date = db.Column(db.DateTime, nullable=True)
delivered = db.Column(db.Boolean)
validated = db.Column(db.Boolean)
ordered_by = db.Column(db.Integer, db.ForeignKey('users.id'))
paid = db.Column(db.Boolean)
items = db.relationship("OrderItem")
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
password_hash = db.Column(db.String(128))
# Relationship User <--> Post
posts = db.relationship('Post', backref='author', lazy='dynamic')
def __repr__(self):
return '<User {}>'.format(self.username)
class Feedback(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
detail = db.Column(db.String, nullable=False)
posted_at = db.Column(db.DateTime, default=datetime.now())
talk_id = db.Column(db.Integer, db.ForeignKey(Talk.id), nullable=False)
talks = db.relationship('Talk', lazy='select',
backref=db.backref('feedback_list', lazy='dynamic'))
def __init__(self, detail, talk_id):
self.detail = detail
self.talk_id = talk_id
def __repr__(self):
return '<detail %r>' % self.detail
class User(db.Model, UserMixin):
# db.Model is necessary to store data in the database
# UserMixin is here to use a standard model for login and sessions
# email and username must be unique and have a maximum length
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(25), unique=True)
username = db.Column(db.String(20), unique=True)
password = db.Column(db.String(100))
posts = db.relationship('Post', backref='author', lazy='dynamic')
followed = db.relationship('User',
secondary=followers,
primaryjoin=(followers.c.follower_id == id),
secondaryjoin=(followers.c.followed_id == id),
backref=db.backref('followers', lazy='dynamic'),
lazy='dynamic')
def follow(self, user):
if not self.is_following(user):
self.followed.append(user)
db.session.commit()
return self
def unfollow(self, user):
if self.is_following(user):
self.followed.remove(user)
db.session.commit()
return self
def is_following(self, user):
return self.followed.filter(
followers.c.followed_id == user.id).count() > 0
def followed_posts(self):
# To get the list of all posts made by people the current user follows
return db.session.query(Post).join(
followers, (followers.c.followed_id == Post.user_id)).filter(
followers.c.follower_id == self.id).order_by(Post.date.desc())
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text)
description = db.Column(db.Text)
available_quantity_mon = db.Column(db.Integer)
available_quantity_tue = db.Column(db.Integer)
available_quantity_wed = db.Column(db.Integer)
available_quantity_thu = db.Column(db.Integer)
available_quantity_fri = db.Column(db.Integer)
price = db.Column(db.Float)
price_in_pack = db.Column(db.Float)
image_format = db.Column(db.Text)
reservations = db.relationship('Reservation',
backref='product',
lazy='dynamic')
class SeatModel(db.Model):
__tablename__ = 'seat'
seatId = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True,
nullable=False)
number = db.Column(db.Integer)
row = db.Column(db.Integer)
hallId = db.Column(UUID(as_uuid=True), db.ForeignKey('hall.hallId'))
tickets = db.relationship("TicketModel", backref="seat")
def __init__(self, _id=None, number=None, row=None, hallId=None):
self.id = _id or id
self.number = number
self.row = row
self.hallId = hallId
class Category(db.Model):
__tablename__ = 'category'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
updated_at = db.Column(db.DateTime,
nullable=False,
default=datetime.now,
onupdate=datetime.now)
items = db.relationship('Item', backref='category')
def __init__(self, name='', category_id=1):
self.name = name
self.id = category_id
def setAttr(self, name=''):
self.name = name
def postRecord(self):
db.session.add(self)
db.session.commit()
return self
@classmethod
def getCategory(cls, category_id):
record = cls.query.filter_by(id=category_id).first()
return record
@classmethod
def addTestData(cls):
categories = []
data_lists = {1: '米・穀物', 2: '野菜', 3: '果物', 4: 'その他'}
for k, v in data_lists.items():
categories.append(cls(name=v, category_id=k))
db.session.add_all(categories)
db.session.commit()
# class CategorySchema(ma.SQLAlchemyAutoSchema):
# class Meta:
# model = Category
class RemainingFormat(db.Model):
__tablename__ = 'remaining_format'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
updated_at = db.Column(db.DateTime,
nullable=False,
default=datetime.now,
onupdate=datetime.now)
item = db.relationship('Item', backref='remaining_format')
def __init__(self, name='', format_id=1):
self.name = name
self.id = format_id
def setAttr(self, name=''):
self.name = name
def postRecord(self):
db.session.add(self)
db.session.commit()
return self
@classmethod
def getRecord(cls, category_id):
record = cls.query.filter_by(id=category_id).first()
return record
@classmethod
def addTestData(cls):
models = []
data_lists = {1: 'whole', 2: 'day', 3: 'week', 4: 'month'}
for k, v in data_lists.items():
models.append(cls(format_id=k, name=v))
db.session.add_all(models)
db.session.commit()
class Item(db.Model):
__tablename__ = 'item'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(20), nullable=False) # 商品名
description = db.Column(db.Text) # 商品説明文
period = db.Column(db.Integer, nullable=False) # 販売日数
remaining_days = db.Column(db.Integer, nullable=False)
remaining_format_id = db.Column(
db.Integer,
db.ForeignKey('remaining_format.id',
onupdate='CASCADE',
ondelete='CASCADE'))
category_id = db.Column(db.Integer,
db.ForeignKey('category.id',
onupdate='CASCADE',
ondelete='CASCADE')) # 設定したカテゴリのID
shipment = db.Column(db.Integer, nullable=False) # 配送にかかる時間（日）
price = db.Column(db.Integer, nullable=False) # 商品の値段
volume = db.Column(db.String(10), nullable=False) # 商品の1セットあたりの量
area = db.Column(db.String(50), nullable=False)
state = db.Column(db.Integer, default=1) # 商品の状態
user_id = db.Column(
db.Integer,
db.ForeignKey('user.id', onupdate='CASCADE', ondelete='CASCADE'))
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
updated_at = db.Column(db.DateTime,
nullable=False,
default=datetime.now,
onupdate=datetime.now)
images = db.relationship('ItemImage', backref='item', lazy="joined")
item_transactions = db.relationship('ItemTransaction', backref='item')
favorites = db.relationship('ItemFavorite', backref='item')
# koes = db.relationship('Koe', backref='item')
def __init__(self,
name=None,
description=None,
period=None,
remaining_days=None,
remaining_format_id=None,
category_id=None,
shipment=None,
price=None,
volume='',
area='',
state=None,
user_id=None):
self.name = name
self.description = description
self.period = period
self.remaining_days = remaining_days
self.remaining_format_id = remaining_format_id
self.category_id = category_id
self.shipment = shipment
self.price = price
self.volume = volume
self.area = area
self.state = state
self.user_id = user_id
def postRecord(self):
db.session.add(self)
db.session.commit()
@classmethod
def getProductById(cls, item_id, user_id=0):
record = db.session.query(
cls, func.count(ItemFavorite.id),
func.sum(ItemTransaction.set_count).label('remaining_set_count')
).select_from(cls).filter(cls.id == item_id).outerjoin(
ItemTransaction,
and_(
ItemTransaction.item_id == cls.id,
or_(
cls.remaining_format.has(name='whole'),
and_(cls.remaining_format.has(name='week'),
ItemTransaction.created_at >= monday,
ItemTransaction.created_at <= sunday),
and_(cls.remaining_format.has(name='month'),
ItemTransaction.created_at >= month_first,
ItemTransaction.created_at <= month_last),
and_(cls.remaining_format.has(name='day'),
ItemTransaction.created_at >= day_first,
ItemTransaction.created_at <= day_last)))).outerjoin(
ItemFavorite,
and_(ItemFavorite.item_id == cls.id,
ItemFavorite.user_id == user_id)).group_by(
cls.id).first()
return record
@classmethod
def getItemsBySearch(cls, user_id, args):
current_app.logger.debug(args)
order_by = False
records = db.session.query(
cls, func.count(ItemFavorite.id),
func.sum(ItemTransaction.set_count).label(
'remaining_set_count')).select_from(cls)
for k, v in args.items():
if (k == 'text'):
records = records.filter(
or_(cls.name.like('%' + v + '%'),
cls.description.like('%' + v + '%'),
cls.area.like('%' + v + '%')))
if (k == 'category'):
records = records.filter(cls.category_id == v)
if (k == 'order_by'):
order_by = v
if (int(v) == 1):
current_app.logger.debug(order_by)
records = records.order_by(desc(cls.updated_at))
records = records.outerjoin(
ItemTransaction,
and_(
ItemTransaction.item_id == cls.id,
or_(
cls.remaining_format.has(name='whole'),
and_(cls.remaining_format.has(name='week'),
ItemTransaction.created_at >= monday,
ItemTransaction.created_at <= sunday),
and_(cls.remaining_format.has(name='month'),
ItemTransaction.created_at >= month_first,
ItemTransaction.created_at <= month_last),
and_(cls.remaining_format.has(name='day'),
ItemTransaction.created_at >= day_first,
ItemTransaction.created_at <= day_last)))).outerjoin(
ItemFavorite,
and_(ItemFavorite.item_id == cls.id,
ItemFavorite.user_id == user_id)).group_by(
cls.id).order_by(desc(
cls.updated_at)).all()
return records
@classmethod
def getItemsByFavorite(cls, user_id=0):
records = db.session.query(
cls, func.count(ItemFavorite.id),
func.sum(ItemTransaction.set_count).label('remaining_set_count')
).select_from(cls).filter(
cls.favorites.any(user_id=user_id)).outerjoin(
ItemTransaction,
and_(
ItemTransaction.item_id == cls.id,
or_(
cls.remaining_format.has(name='whole'),
and_(cls.remaining_format.has(name='week'),
ItemTransaction.created_at >= monday,
ItemTransaction.created_at <= sunday),
and_(cls.remaining_format.has(name='month'),
ItemTransaction.created_at >= month_first,
ItemTransaction.created_at <= month_last),
and_(
cls.remaining_format.has(name='day'),
ItemTransaction.created_at >= day_first,
ItemTransaction.created_at <=
day_last)))).outerjoin(
ItemFavorite,
and_(
ItemFavorite.item_id == cls.id,
ItemFavorite.user_id == user_id)).group_by(
cls.id).order_by(desc(
cls.updated_at)).all()
return records
@classmethod
def getItemsByNew(cls, user_id=0):
records = db.session.query(
cls, func.count(ItemFavorite.id),
func.sum(ItemTransaction.set_count).label('remaining_set_count')
).select_from(cls).outerjoin(
ItemTransaction,
and_(
ItemTransaction.item_id == cls.id,
or_(
cls.remaining_format.has(name='whole'),
and_(cls.remaining_format.has(name='week'),
ItemTransaction.created_at >= monday,
ItemTransaction.created_at <= sunday),
and_(cls.remaining_format.has(name='month'),
ItemTransaction.created_at >= month_first,
ItemTransaction.created_at <= month_last),
and_(cls.remaining_format.has(name='day'),
ItemTransaction.created_at >= day_first,
ItemTransaction.created_at <= day_last)))).outerjoin(
ItemFavorite,
and_(ItemFavorite.item_id == cls.id,
ItemFavorite.user_id == user_id)).group_by(
cls.id).order_by(desc(
cls.updated_at)).all()
return records
@classmethod
def getItemsByUserId(cls, user_id=0):
records = db.session.query(
cls, func.count(ItemFavorite.id),
func.sum(ItemTransaction.set_count).label('remaining_set_count')
).select_from(cls).filter(cls.user_id == user_id).outerjoin(
ItemTransaction,
and_(
ItemTransaction.item_id == cls.id,
or_(
cls.remaining_format.has(name='whole'),
and_(cls.remaining_format.has(name='week'),
ItemTransaction.created_at >= monday,
ItemTransaction.created_at <= sunday),
and_(cls.remaining_format.has(name='month'),
ItemTransaction.created_at >= month_first,
ItemTransaction.created_at <= month_last),
and_(cls.remaining_format.has(name='day'),
ItemTransaction.created_at >= day_first,
ItemTransaction.created_at <= day_last)))).outerjoin(
ItemFavorite,
and_(ItemFavorite.item_id == cls.id,
ItemFavorite.user_id == user_id)).group_by(
cls.id).order_by(desc(
cls.updated_at)).all()
return records
class Koe(db.Model):
__tablename__ = 'koe'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
title = db.Column(db.String(25), nullable=False)
text = db.Column(db.Text, nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
updated_at = db.Column(db.DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
user_id = db.Column(db.Integer, db.ForeignKey('user.id', onupdate='CASCADE', ondelete='CASCADE'))
item_id = db.Column(db.Integer, db.ForeignKey('item.id', onupdate='CASCADE', ondelete='CASCADE'))
item = db.relationship('Item', primaryjoin='Koe.item_id==Item.id', backref='koes')
favorites = db.relationship('KoeFavorite', backref='koe')
def __init__(self, title='', text='', user_id='', item_id=''):
self.title = title
self.text = text
self.user_id = user_id
self.item_id = item_id
def postRecord(self):
db.session.add(self)
db.session.commit()
return self
@classmethod
def getRecordById(cls, koe_id):
record = cls.query.filter_by(id=koe_id).first()
return record
@classmethod
def getRecordsBySearch(cls, args):
records = cls.query
for k, v in args.items():
if (k == 'text'):
records = records.filter(or_(cls.title.like('%' + v + '%'),
cls.text.like('%' + v + '%')))
if (k == 'order_by'):
order_by = v
if (int(v) == 1):
current_app.logger.debug(order_by)
records = records.order_by(desc(cls.updated_at))
records = records.all()
return records
@classmethod
def getRecordsByItem(cls, item_id):
record = cls.query.filter_by(item_id=item_id).order_by(desc(cls.updated_at)).all()
return record
@classmethod
def getRecordsByPostUser(cls, user_id):
record = cls.query.filter_by(user_id=user_id).order_by(desc(cls.updated_at)).all()
return record
@classmethod
def getRecordsByCatchUser(cls, user_id):
record = cls.query.filter(cls.item.has(user_id=user_id)).order_by(desc(cls.updated_at)).all()
return record
@classmethod
def getRecordsByNew(cls):
record = cls.query.order_by(desc(cls.updated_at)).all()
return record
@classmethod
def addTestData(cls):
categories = []
data_lists = ['grain', 'vegetable', 'fruit', 'other']
for data in data_lists:
categories.append(cls(data))
db.session.add_all(categories)
db.session.commit()
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
password = db.Column(db.String(128))
authenticated = db.Column(db.Boolean, default=False)
# Relationship User <--> Post
posts = db.relationship('Post', backref='authorPost', lazy='dynamic')
# Relationship User <--> Post
comments = db.relationship('Comment', backref='authorComment', lazy='dynamic')
liked_posts = db.relationship('PostLike', foreign_keys='PostLike.user_id', backref='user', lazy='dynamic')
followers = db.relationship('Follow', foreign_keys=[Follow.followed_id], backref="followed", lazy='dynamic')
followed = db.relationship('Follow', foreign_keys=[Follow.follower_id], backref="follower", lazy='dynamic')
def is_active(self):
return True
def get_id(self):
return self.email
def is_authenticated(self):
return self.authenticated
def is_following(self, user):
if user.id is None:
return False
return self.followed.filter_by(followed_id=user.id).first() is not None
def is_followed_by(self, user):
if user.id is None:
return False
return self.followers.filter_by(follower_id=user.id).first() is not None
def follow(self, user):
if not self.is_following(user):
f = Follow(follower_id=self.id, followed_id=user.id)
db.session.add(f)
def unfollow(self, user):
f = self.followed.filter_by(followed_id=user.id).first()
if f:
db.session.delete(f)
def like_post(self, post):
if not self.has_liked_post(post):
like = PostLike(user_id=self.id, post_id=post.id)
db.session.add(like)
def unlike_post(self, post):
if self.has_liked_post(post):
PostLike.query.filter_by(
user_id=self.id,
post_id=post.id).delete()
def has_liked_post(self, post):
return PostLike.query.filter(
PostLike.user_id == self.id,
PostLike.post_id == post.id).count() > 0
def __repr__(self):
return '<User {}>'.format(self.username)
class Team(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text)
sport_id = db.Column(db.Integer, db.ForeignKey('sport.id'))
sport = db.relationship('Sport', backref='teams') # Sport <-> Team relationship
players = db.relationship('Player', backref='teams', secondary=junction_table) # Sport <-> Player relationship
class User(db.Model):
__tablename__ = 'user'
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
display_name = db.Column(db.String(15), nullable=False)
image = db.Column(db.String(255))
name = db.Column(db.String(50), nullable=False)
name_ruby = db.Column(db.String(50), nullable=False)
birthday = db.Column(db.Date)
payment = db.Column(db.String(255))
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
updated_at = db.Column(db.DateTime,
nullable=False,
default=datetime.now,
onupdate=datetime.now)
auth = db.relationship('Auth', backref='user')
items = db.relationship('Item', backref='user')
koes = db.relationship('Koe', backref='user')
item_favorites = db.relationship('ItemFavorite', backref='user')
koe_favorites = db.relationship('KoeFavorite', backref='user')
def __init__(self,
display_name='',
image='',
name='',
name_ruby='',
birthday='',
payment=''):
self.display_name = display_name
self.image = image
self.name = name
self.name_ruby = name_ruby
self.birthday = birthday
self.payment = payment
def setAttr(self,
display_name='',
image='',
name='',
name_ruby='',
birthday='',
payment=''):
self.display_name = display_name
self.image = image
self.name = name
self.name_ruby = name_ruby
self.birthday = birthday
self.payment = payment
def postRecord(self):
db.session.add(self)
db.session.commit()
return self
def getUserInfo(self, user_id):
record = db.session.query(self).filter_by(id=user_id).first()
return record
@classmethod
def getUserInfo(cls, user_id):
record = db.session.query(cls).filter_by(id=user_id).first()
return record
# class UserSchema(ma.SQLAlchemyAutoSchema):
# class Meta:
# model = User
class ItemTransaction(db.Model):
__tablename__ = 'item_transaction'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
item_id = db.Column(db.Integer, db.ForeignKey('item.id', onupdate='CASCADE', ondelete='CASCADE')) # 出品者id
seller_id = db.Column(db.Integer, db.ForeignKey('user.id', onupdate='CASCADE', ondelete='CASCADE')) # 出品者id
buyer_id = db.Column(db.Integer, db.ForeignKey('user.id', onupdate='CASCADE', ondelete='CASCADE')) # 購入者id
set_count = db.Column(db.Integer, nullable=False) # 販売数
state = db.Column(db.Integer, nullable=False, default=0)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
updated_at = db.Column(db.DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
seller = db.relationship(
'User',
primaryjoin="ItemTransaction.seller_id==User.id")
buyer = db.relationship(
'User',
primaryjoin="ItemTransaction.buyer_id==User.id")
def __init__(self, item_id='', seller_id='', buyer_id='', set_count='', state=0):
self.item_id = item_id
self.seller_id = seller_id
self.buyer_id = buyer_id
self.set_count = set_count
self.state = state
def postRecord(self):
db.session.add(self)
db.session.commit()
db.session.refresh(self)
@classmethod
def getRecordById(cls, transaction_id):
record = cls.query.filter_by(id=transaction_id).first()
return record
@classmethod
def getRecordsByItemId(cls, item_id):
records = cls.query.filter_by(item_id=item_id).all()
return records
@classmethod
def getRecordsBySellerIdStateSold(cls, seller_id):
records = cls.query.filter_by(seller_id=seller_id, state=2).all()
return records
@classmethod
def getRecordsBySellerId(cls, seller_id):
records = cls.query.filter_by(seller_id=seller_id).all()
return records
@classmethod
def getRecordsByBuyerId(cls, buyer_id):
records = cls.query.filter_by(buyer_id=buyer_id).all()
return records
@classmethod
def getSalesDetailBySellerId(cls, seller_id, item_model):
record = db.session.query(func.sum(item_model.price * cls.set_count)).\
select_from(cls).\
filter(and_(seller_id==seller_id, cls.state==2)).\
outerjoin(item_model, item_model.id == cls.item_id).\
group_by(cls.buyer_id).\
first()
return record