class FavoriteGamesModel(db.Model):
__tablename__ = "favorite_games"
id = db.Column(db.String(80), primary_key=True)
game_name = db.Column(db.String(80))
game_url_id = db.Column(db.BigInteger(), nullable=False)
# user relationship
user_id = db.Column(db.String(80), db.ForeignKey('users.id'))
user = db.relationship('UserModel')
def __init__(self, user_id, game_name, game_url_id):
self.id = str(uuid.uuid4())
self.user_id = user_id
self.game_name = game_name
self.game_url_id = game_url_id
def json(self):
return {"game_name": self.game_name, "game_url_id": self.game_url_id}
@classmethod
def find_favorite_games_by_user_id(cls, user_id):
return cls.query.filter_by(user_id=user_id).all()
def save_favorite_game_id(self):
db.session.add(self)
db.session.commit()
def remove_favorite_game(self):
db.session.delete(self)
db.session.commit()
class Customer(db.Model):
__tablename__ = "customers"
__table_args__ = (db.UniqueConstraint('id',
'account_id',
name='unique_account'), )
id = db.Column(db.BigInteger(), primary_key=True)
name = db.Column(db.String(64), nullable=False)
account_id = db.Column(db.BigInteger(),
db.ForeignKey("accounts.id"),
nullable=False)
_account = None
@property
def account(self) -> Account:
assert self._account is not None, "Load account using query"
return self._account
class MenusModel(db.Model):
__tablename__ = "menus"
id = db.Column(db.Integer(), primary_key=True)
item_id = db.Column(db.BigInteger())
name = db.Column(db.String(100))
restaurant_id = db.Column(db.Integer())
photos = db.Column(db.String(2000))
rating = db.Column(db.Float())
satisfy_rate = db.Column(db.Float())
rating_count = db.Column(db.Integer())
satisfy_count = db.Column(db.Integer())
original_price = db.Column(db.Float())
price = db.Column(db.Float())
sold_out = db.Column(db.Integer())
month_sales = db.Column(db.Integer())
class UserDetailsModel(db.Model):
__tablename__ = "user_details"
id = db.Column(db.String(), primary_key=True)
name = db.Column(db.String(80))
last_name = db.Column(db.String(80))
address = db.Column(db.String(80))
phone = db.Column(db.BigInteger())
email = db.Column(db.String(80))
user_id = db.Column(db.String(80), db.ForeignKey('users.id'))
#user relationship
user = db.relationship('UserModel')
def __init__(self, user_id, name, last_name, address, phone, email):
self.id = str(uuid.uuid4())
self.user_id = user_id
self.name = name
self.last_name = last_name
self.address = address
self.phone = phone
self.email = email
def json(self):
return {
"name": self.name,
"last_name": self.last_name,
"address": self.address,
"phone": self.phone,
"email": self.email
}
@classmethod
def find_by_user_id(cls, user_id):
return cls.query.filter_by(user_id=user_id).first()
def save_user_details_to_db(self):
db.session.add(self)
db.session.commit()
def delete_user_details_from_db(self):
db.session.delete(self)
db.session.commit()
class ScoresModel(db.Model):
__tablename__ = 'scores'
isbn = db.Column(db.BigInteger(),
db.ForeignKey('books.isbn'),
primary_key=True)
n_reviews = db.Column(db.Integer(), nullable=False)
score = db.Column(db.Float(), nullable=False)
def __init__(self, isbn):
self.isbn = isbn
self.n_reviews = 0
self.score = 0
def json(self):
_ignore = self.isbn # Forces execution to parse properly the class, fixing the bug of transient data
atr = self.__dict__.copy()
del atr["_sa_instance_state"]
return atr
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def find_by_isbn(cls, isbn):
return cls.query.filter_by(isbn=isbn).first()
@classmethod
def add_review(cls, review: ReviewsModel):
score = cls.find_by_isbn(review.isbn)
if score is None:
score = cls(review.isbn)
score.score = (score.n_reviews * score.score +
review.score) / (score.n_reviews + 1)
score.n_reviews += 1
score.save_to_db()
@classmethod
def remove_review(cls, review: ReviewsModel):
score = cls.find_by_isbn(review.isbn)
if score is None:
raise Exception("No review to remove.")
elif score.n_reviews == 1:
score.delete_from_db()
else:
score.score = (score.n_reviews * score.score -
review.score) / (score.n_reviews - 1)
score.n_reviews -= 1
score.save_to_db()
@classmethod
def modify_review(cls, review: ReviewsModel, previous_score: int):
score = cls.find_by_isbn(review.isbn)
if score is None:
raise Exception("No review to update.")
else:
score.score = (score.n_reviews * score.score + review.score -
previous_score) / score.n_reviews
db.session.commit()
class ReviewsModel(db.Model):
__tablename__ = 'reviews'
isbn = db.Column(db.BigInteger(),
db.ForeignKey('books.isbn'),
primary_key=True)
user_id = db.Column(db.Integer(),
db.ForeignKey('users.id'),
primary_key=True)
score = db.Column(db.Integer(), nullable=False)
review = db.Column(db.String())
def __init__(self, isbn, user_id, score, review=None):
self.isbn = isbn
self.user_id = user_id
self.score = score
self.review = review
def json(self):
"""
Returns a dictionary with pairs of string of name of attribute and it's value.
"""
_ignore = self.isbn # Forces execution to parse properly the class, fixing the bug of transient data
atr = self.__dict__.copy()
user = UsersModel.find_by_id(self.user_id)
atr['username'] = user.username if user.state else None
del atr["_sa_instance_state"]
return atr
def save_to_db(self):
if self.score < 1 or self.score > 5:
raise ValueError(
"Invalid value for score attribute: Value must be an integer from 1 to 5, both included."
)
if ReviewsModel.find_by_isbn_user_id(self.isbn,
self.user_id) is not None:
raise Exception(
f"Given user already posted a review. Did you meant to update it?"
)
if UsersModel.find_by_id(self.user_id) is None:
raise Exception("User with given id doesn't exist")
if BooksModel.find_by_isbn(self.isbn) is None:
raise Exception("Book with given isbn doesn't exist")
ScoresModel.add_review(self)
db.session.add(self)
db.session.commit()
def delete_from_db(self):
ScoresModel.remove_review(self)
db.session.delete(self)
db.session.commit()
def update_from_db(self, data: dict):
if data.get('score', None) is not None and (data['score'] < 1
or data['score'] > 5):
raise ValueError(
"Invalid value for score attribute: Value must be an integer from 1 to 5, both included."
)
# Attributes that can't be modified
data.pop('isbn', None)
data.pop('user_id', None)
previous_score = self.score
for attr, newValue in data.items():
if newValue is not None:
setattr(self, attr, newValue)
ScoresModel.modify_review(self, previous_score)
db.session.commit()
@classmethod
def find_by_isbn_user_id(cls, isbn, user_id):
return cls.query.filter_by(isbn=isbn, user_id=user_id).first()
@classmethod
def find_by_isbn(cls, isbn):
return cls.query.filter_by(isbn=isbn).all()
@classmethod
def find_by_user_id(cls, user_id):
return cls.query.filter_by(user_id=user_id).all()
class LibraryModel(db.Model):
__tablename__ = 'library'
isbn = db.Column(db.BigInteger(),
db.ForeignKey('books.isbn'),
primary_key=True)
user_id = db.Column(db.Integer(),
db.ForeignKey('users.id'),
primary_key=True)
library_type = db.Column(db.Enum(LibraryType, name='library_types'),
primary_key=True)
state = db.Column(db.Enum(State, name='state_types'), nullable=False)
visible = db.Column(db.Boolean(), nullable=False)
def __init__(self,
isbn,
user_id,
library_type=LibraryType.Bought,
state=State.Pending):
self.isbn = isbn
self.user_id = user_id
self.state = state
self.visible = True
self.library_type = library_type
def json(self):
"""
Returns a dictionary with paris of string of name of attribute and it's value. In case of Enum it just returns
the name of the enum object (Enum.name).
"""
_ignore = self.isbn # Forces execution to parse properly the class, fixing the bug of transient data
atr = self.__dict__.copy()
atr['book'] = BooksModel.find_by_isbn(self.isbn).json()
del atr['isbn']
del atr["_sa_instance_state"]
return {
atr: value if not isinstance(value, Enum) else value.name
for atr, value in atr.items()
}
def save_to_db(self):
if BooksModel.find_by_isbn(self.isbn) is None:
raise Exception("Book with isbn doesn't exist")
db.session.add(self)
db.session.commit()
def delete_from_db(self):
self.visible = False
db.session.commit()
def change_visible_db(self, state):
self.visible = state
db.session.commit()
def update_from_db(self, data):
"""
Updates through a dictionary with paris of string of name of attribute and it's value. Following same structure
as json(). In case of wanting to modify an attribute of an enum use the string name of one of the values.
Will raise Exception in case of invalid enum value if it isn't contained inside the possible values of the enum.
"""
for attr, newValue in data.items():
if newValue is not None:
cls = getattr(self, attr)
# Checks if value is of the attribute that's trying to be modified is an Enum
if isinstance(cls, Enum):
# Checks if the enum doesn't contain the newValue
if newValue not in type(cls).__members__:
raise Exception(
f"Enum {type(cls).__name__} doesn't have value: {newValue}"
)
# Gets the object of the enum with same name as newValue
setattr(self, attr, type(cls)[newValue])
else:
setattr(self, attr, newValue)
db.session.commit()
@classmethod
def find_by_id_and_isbn(cls, user_id, isbn):
return cls.query.filter_by(user_id=user_id, isbn=isbn).first()
@classmethod
def find_by_id(cls, user_id):
return cls.query.filter_by(user_id=user_id).all()
class BooksModel(db.Model):
__tablename__ = 'books'
isbn = db.Column(db.BigInteger(), nullable=False, primary_key=True)
vendible = db.Column(db.Boolean(), nullable=False)
stock = db.Column(db.Integer(), nullable=False)
precio = db.Column(db.Float, nullable=False)
titulo = db.Column(db.String(), nullable=False)
autor = db.Column(db.String())
editorial = db.Column(db.String())
sinopsis = db.Column(db.String())
url_imagen = db.Column(db.String())
fecha_de_publicacion = db.Column(db.DateTime(), nullable=False)
reviews = db.relationship('ReviewsModel', backref='book', lazy=True)
score = db.relationship('ScoresModel',
uselist=False,
backref='book',
lazy=True)
def __init__(self,
isbn,
stock,
precio,
titulo,
autor=None,
editorial=None,
sinopsis=None,
url_imagen=None,
fecha_de_publicacion=None):
self.isbn = isbn
self.vendible = True
self.stock = stock
self.precio = precio
self.titulo = titulo
self.autor = autor
self.editorial = editorial
self.sinopsis = sinopsis
self.url_imagen = url_imagen
if fecha_de_publicacion is None:
self.fecha_de_publicacion = dt.datetime.now()
else:
self.fecha_de_publicacion = fecha_de_publicacion
def json(self, reviews=False, score=False):
_ignore = self.isbn # Forces execution to parse properly the class, fixing the bug of transient data
atr = self.__dict__.copy()
del atr["_sa_instance_state"]
atr['fecha_de_publicacion'] = self.fecha_de_publicacion.strftime(
'%Y-%m-%d')
if reviews:
atr['reviews'] = [review.json() for review in self.reviews]
if score:
score = self.score
atr['score'] = score.score if score else None
return atr
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
self.vendible = False
db.session.commit()
def update_from_db(self, data):
for attr, newValue in data.items():
if newValue is not None:
setattr(self, attr, newValue)
db.session.commit()
@classmethod
def find_by_isbn(cls, isbn):
return cls.query.filter_by(isbn=isbn).first()
class TransactionsModel(db.Model):
__tablename__ = 'transactions'
it_transaction = None
id_transaction = db.Column(db.Integer(), primary_key=True)
user_id = db.Column(db.Integer(), db.ForeignKey('users.id'))
isbn = db.Column(db.BigInteger(),
db.ForeignKey('books.isbn'),
primary_key=True)
price = db.Column(db.Float, nullable=False)
quantity = db.Column(db.Integer, nullable=False)
date = db.Column(db.DateTime(), nullable=False)
def __init__(self, user_id, isbn, price, quantity, date=None):
if TransactionsModel.it_transaction is None:
aux = TransactionsModel.query.order_by(
desc('id_transaction')).first()
TransactionsModel.it_transaction = 1 if aux is None else aux.id_transaction + 1
self.id_transaction = self.it_transaction
self.isbn = isbn
self.price = price
self.user_id = user_id
self.quantity = quantity
if date is None:
self.date = dt.datetime.now()
else:
self.date = date
def json(self):
_ignore = self.isbn # Forces execution to parse properly the class, fixing the bug of transient data
atr = self.__dict__.copy()
del atr["_sa_instance_state"]
atr['date'] = self.date.strftime('%d-%m-%Y')
atr['book'] = BooksModel.find_by_isbn(atr['isbn']).json()
return atr
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
def update_from_db(self, data):
for attr, newValue in data.items():
if newValue is not None:
cls = getattr(self, attr)
if isinstance(newValue, type(cls)):
setattr(self, attr, newValue)
else:
raise Exception
db.session.commit()
def send_confirmation_mail(self):
recipient = UsersModel.find_by_id(self.user_id).email
quantity = str(self.quantity)
isbn = str(self.isbn)
subject = 'Order confirmation'
message = 'Has comprat ' + quantity + ' llibre/s amb isbn ' + isbn
send_email(recipient, subject, message)
@classmethod
def find_by_id(cls, id_transaction):
return cls.query.filter_by(id_transaction=id_transaction).all()
@classmethod
def find_by_id_and_isbn(cls, id_transaction, isbn):
return cls.query.filter_by(id_transaction=id_transaction,
isbn=isbn).first()
@classmethod
def save_transaction(cls, user_id, isbns, prices, quantities):
transactions = []
email_trans = []
for isbn, price, quantity in zip(isbns, prices, quantities):
book = BooksModel.find_by_isbn(isbn)
cls.check_stock(book, quantity)
transaction = TransactionsModel(user_id, isbn, price, quantity)
transactions.append(transaction.json())
email_trans.append(transaction.email_text())
db.session.add(transaction)
user = UsersModel.find_by_id(user_id)
book_library = LibraryModel.find_by_id_and_isbn(
user.id, transaction.isbn)
if book_library: # if the book was already in library
if book_library.library_type == LibraryType.WishList: # if it was in the wish list
book_library.library_type = LibraryType.Bought # change it to bought
book_library.state = State.Pending
else: # if it wasnt in the library, enter it
entry = LibraryModel(book.isbn, user.id, LibraryType.Bought,
State.Pending)
db.session.add(entry)
cls.it_transaction += 1
db.session.commit()
cls.send_email(user_id, email_trans)
return transactions
@classmethod
def send_email(cls, user_id, transactions):
recipient = UsersModel.find_by_id(user_id).email
msg = "Has comprat els seguents llibres:\n - " + ",\n - ".join(
transactions)
send_email(recipient, 'Confirmacio del correu', msg)
@classmethod
def check_stock(cls, book, quantity):
if book.stock - quantity >= 0:
book.stock -= quantity
else:
raise Exception('Not enough stock')
@classmethod
def best_sellers(cls):
aux = {}
for transaction in cls.query.all():
isbn = transaction.isbn
quantity = transaction.quantity
aux[isbn] = quantity + aux.get(isbn, 0)
sort_best = dict(sorted(aux.items(), key=lambda x: x[1], reverse=True))
isbns = list(sort_best.keys())
return isbns
@classmethod
def group_transactions_by_id(cls, transactions):
grouped_transactions = [[
t.json() for t in transactions if t.id_transaction == i
] for i in OrderedSet(t.id_transaction for t in transactions)]
return grouped_transactions
def email_text(self):
return f"[isbn={self.isbn}, price={self.price}, quantity={self.quantity}]"