class Cart(db.Model):
__table_args__ = {'extend_existing': True}
userid = db.Column(db.Integer, db.ForeignKey('user.userid'), nullable=False, primary_key=True)
productid = db.Column(db.Integer, db.ForeignKey('product.productid'), nullable=False, primary_key=True)
quantity = db.Column(db.Integer, nullable=False)
def __repr__(self):
return f"Cart('{self.userid}', '{self.productid}, '{self.quantity}')"
class ProductCategory(db.Model):
__table_args__ = {'extend_existing': True}
categoryid = db.Column(db.Integer, db.ForeignKey('category.categoryid'), nullable=False, primary_key=True)
productid = db.Column(db.Integer, db.ForeignKey('product.productid'), nullable=False, primary_key=True)
created_on = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
def __repr__(self):
return f"Product('{self.categoryid}', '{self.productid}')"
class OrderedProduct(db.Model):
__table_args__ = {'extend_existing': True}
ordproductid = db.Column(db.Integer, primary_key=True)
orderid = db.Column(db.Integer,db.ForeignKey('order.orderid'), nullable=False)
productid = db.Column(db.Integer,db.ForeignKey('product.productid'), nullable=False)
quantity = db.Column(db.Integer, nullable=False)
def __repr__(self):
return f"Order('{self.ordproductid}', '{self.orderid}','{self.productid}','{self.quantity}')"
class Cart(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
product_id = db.Column(db.Integer,
db.ForeignKey('product.id'),
nullable=False)
def __repr__(self):
return f"Cart('{self.user_id}', '{self.product_id}')"
class Order(db.Model):
id = db.Column(db.Integer, primary_key=True)
orderTime = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
# one to many relationship
cartId = db.Column(db.Integer, db.ForeignKey('cartItem.id'),nullable=True)
addressId = db.Column(db.Integer, db.ForeignKey('address.id'),nullable=True)
def __repr__(self):
return f"{self.orderTime}"
class CartItem(db.Model):
__tablename__ = 'cartItem'
id = db.Column(db.Integer, primary_key=True)
userId = db.Column(db.Integer,db.ForeignKey('user.id', ondelete='SET NULL'),nullable=True)
productId = db.Column(db.Integer, db.ForeignKey('product.id', ondelete='SET NULL'),nullable=True)
quanitity = db.Column(db.Integer,nullable=True)
order = db.relationship('Order',backref='cartItem',lazy=True)
def __reper__(self):
return f'{self.id}'
class Rating(db.Model):
__tablename__ = 'rating'
id = db.Column(db.Integer, primary_key=True)
rate = db.Column(db.Integer, nullable=True)
comments = db.Column(db.Text, nullable=True)
userId = db.Column(db.Integer,
db.ForeignKey('user.userId'),
nullable=False)
product_id = db.Column(db.Integer,
db.ForeignKey('product.id'),
nullable=False)
def __repr__(self):
return f"Post('{self.rate}', '{self.comments}')"
class Product(db.Model):
__tablename__ = 'product'
id = db.Column(db.Integer, primary_key=True)
productName = db.Column(db.Text, nullable=False)
slug = db.Column(db.Text, nullable=False, unique=True)
price = db.Column(db.Integer, nullable=False)
discount = db.Column(db.Integer, nullable=True)
stock = db.Column(db.Integer, nullable=False, default=1)
category = db.Column(db.Text, nullable=False)
brand = db.Column(db.Text, nullable=True)
color = db.Column(db.String(50), nullable=True)
size = db.Column(db.String(50), nullable=True)
weight = db.Column(db.String(50), nullable=True)
dimension = db.Column(db.String(50), nullable=True)
material = db.Column(db.String(50), nullable=True)
shortDescription = db.Column(db.Text, nullable=True)
longDescription = db.Column(db.Text, nullable=True)
imageFile = db.Column(db.Text, nullable=False, default='default.jpg')
imageGallery = db.Column(db.Text, nullable=True)
featured = db.Column(db.Boolean, nullable=False, default=False)
userId = db.Column(db.Integer,
db.ForeignKey('user.userId'),
nullable=False) # User Id
productCart = db.relationship('Cart', backref='cart_product_id', lazy=True)
productOrder = db.relationship('Order',
backref='order_product_id',
lazy=True)
productRating = db.relationship('Rating',
backref='rating_product_id',
lazy=True)
def __repr__(self):
return f"Post('{self.productName}','{self.price}','{self.discount}','{self.stock}','{self.category}','{self.brand}','{self.color}','{self.size}','{self.weight}','{self.dimension}','{self.material}','{self.shortDescription}','{self.longDescription}','{self.imageFile}','{self.imageGallery}','{self.featured}')"
class Cart(db.Model):
__tablename__ = 'cart'
id = db.Column(db.Integer, primary_key=True)
quantity = db.Column(db.Integer, nullable=False)
color = db.Column(db.String(10), nullable=True)
size = db.Column(db.String(10), nullable=True)
userId = db.Column(db.Integer,
db.ForeignKey('user.userId'),
nullable=False)
product_id = db.Column(db.Integer,
db.ForeignKey('product.id'),
nullable=False)
def __repr__(self):
return f"Post('{self.quantity}', '{self.color}', '{self.size}')"
class Room(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(20))
description = db.Column(db.Text)
address = db.Column(db.String(70), nullable=False)
available_from = db.Column(db.Date, nullable=False)
available_to = db.Column(db.Date, nullable=False)
price = db.Column(db.Float, nullable=False)
max_persons = db.Column(db.Integer, nullable=False, default=1)
owner_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
def __repr__(self):
return f"Room({self.id}, '{self.name}', '{self.address}', '{self.available_from}', '{self.available_to}', '{self.price}', '{self.max_persons}')"
def as_dict(self):
return {
'id': self.id,
'name': str(self.name),
'description': str(self.description),
'address': str(self.address),
'available_from': str(self.available_from),
'available_to': str(self.available_to),
'price': str(self.price),
'max_persons': str(self.max_persons),
'owner_id': self.owner_id
}
class Reviews(db.Model, UserMixin):
__tablename__ = "reviews"
id = db.Column(db.Integer, primary_key=True)
order_id = db.Column(db.Integer,
db.ForeignKey("orders.id"),
nullable=False)
stars = db.Column(db.Integer, default=5)
review_content = db.Column(db.String(512), default="N/A")
review_time = db.Column(db.DateTime, default=datetime.utcnow)
def __init__(self, order, stars=5, review_content="N/A"):
if order and (Reviews.query.filter(
Reviews.order_id == order.id).count() == 0):
self.order_id = order.id
self.stars = stars
self.review_time = datetime.utcnow()
if review_content:
self.review_content = review_content
else:
self.review_content = "N/A"
else:
return False
def as_list(self):
return [
self.id,
self.order_id,
self.stars,
self.review_content,
self.review_time,
]
def get_review_order(self):
print(self._sa_instance_state)
print("review id : " + str(self.id))
print(self.order_id)
try:
return Order.query.get(self.order_id)
except:
return self.order_id
def get_review_buyer(self):
order = self.get_review_order()
if type(order) == int:
buyer_id = (db.session.query(
Order.buyer_id).filter(Order.id == order).first())
return Buyer.query.get(buyer_id)
return order.get_order_buyer()
def get_review_supplier(self):
order = self.get_review_order()
return order.get_order_supplier()
def get_review_product(self):
order = self.get_review_order()
return order.get_order_product()
class Prenotation(db.Model):
room_id = db.Column(db.Integer, db.ForeignKey('room.id'), primary_key=True)
buyer_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
start_date = db.Column(db.Date, primary_key=True, nullable=False)
end_date = db.Column(db.Date, primary_key=True, nullable=False)
persons = db.Column(db.Integer, nullable=False)
price = db.Column(db.Float, nullable=False)
def as_dict(self):
return {
'room_id': self.room_id,
'buyer_id': self.buyer_id,
'start_date': self.start_date,
'end_date': self.end_date,
'persons': self.persons,
'price': self.price
}
class Order(db.Model):
__table_args__ = {'extend_existing': True}
orderid = db.Column(db.Integer, primary_key=True)
order_date = db.Column(db.DateTime, nullable=False)
total_price = db.Column(db.DECIMAL, nullable=False)
userid = db.Column(db.Integer, db.ForeignKey('user.userid'), nullable=False, primary_key=True)
def __repr__(self):
return f"Order('{self.orderid}', '{self.order_date}','{self.total_price}','{self.userid}'')"
class Order(db.Model):
__tablename__ = 'order'
id = db.Column(db.Integer, primary_key=True)
quantity = db.Column(db.Integer, nullable=False)
color = db.Column(db.String(10), nullable=True)
size = db.Column(db.String(10), nullable=True)
status = db.Column(db.String(25), nullable=False)
total = db.Column(db.Integer, nullable=False)
deliveryType = db.Column(db.String(30), nullable=False, default='COD')
orderDate = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
userId = db.Column(db.Integer,
db.ForeignKey('user.userId'),
nullable=False)
product_id = db.Column(db.Integer,
db.ForeignKey('product.id'),
nullable=False)
def __repr__(self):
return f"Post('{self.quantity}', '{self.color}', '{self.size}', '{self.status}', '{self.total}', '{self.deliveryType}', '{self.orderDate}')"
class Address(db.Model):
id = db.Column(db.Integer, primary_key=True)
country = db.Column(db.String(50), nullable=False)
city = db.Column(db.String(70), nullable=False)
street = db.Column(db.String(100), nullable=False)
Postcode = db.Column(db.Integer, nullable=False)
userId = db.Column(db.Integer, db.ForeignKey('user.id',ondelete='SET NULL'),nullable=True)
order = db.relationship('Order', backref='address',lazy=True)
def __repr__(self):
return f"{self.country},{self.city},{self.street}"
class SaleTransaction(db.Model):
__table_args__ = {'extend_existing': True}
transactionid = db.Column(db.Integer, primary_key=True)
orderid = db.Column(db.Integer,db.ForeignKey('order.orderid'), nullable=False)
transaction_date = db.Column(db.DateTime,nullable=False)
amount = db.Column(db.DECIMAL, nullable=False)
cc_number=db.Column(db.String(50), nullable=False)
cc_type = db.Column(db.String(50), nullable=False)
response = db.Column(db.String(50), nullable=False)
def __repr__(self):
return f"Order('{self.transactionid}', '{self.orderid}','{self.transactiondate}','{self.amount}', '{self.cc_number}','{self.cc_type}','{self.response}')"
class Coupon(db.Model, UserMixin):
__tablename__ = "coupon"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(256), default="N/A")
product = db.Column(db.Integer,
db.ForeignKey("products.id"),
nullable=False)
discount_amount = db.Column(db.Numeric, nullable=False)
qty = db.Column(db.Integer)
start_date = db.Column(db.DateTime)
end_date = db.Column(db.DateTime)
db.relationship("Order", backref="the_coupon", lazy="dynamic")
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
product_name = db.Column(db.String(20), nullable=False)
description = db.Column(db.Text, nullable=False)
price = db.Column(db.Float, nullable=False)
picture = db.Column(db.String(20), nullable=False, default='default.jpg')
quantity = db.Column(db.Integer, nullable=False, default=0)
category_id = db.Column(db.Integer,
db.ForeignKey('category.id'),
nullable=False)
def __repr__(self):
return f"User('{self.id}', '{self.name}', '{self.image_file}','{self.price}','{self.quantity}')"
class Shipping(db.Model):
__tablename__ = 'shipping'
id = db.Column(db.Integer, primary_key=True)
fullName = db.Column(db.Text, nullable=False)
contactNo = db.Column(db.Text, nullable=True)
phoneNo = db.Column(db.Text, nullable=False)
street = db.Column(db.Text, nullable=False)
city = db.Column(db.Text, nullable=False)
country = db.Column(db.Text, nullable=False)
userId = db.Column(db.Integer,
db.ForeignKey('user.userId'),
nullable=False)
def __repr__(self):
return f"Post('{self.fullName}', '{self.contactNo}', '{self.phoneNo}', '{self.street}', '{self.city}', '{self.country}')"
class CustomerOrder(db.Model):
id = db.Column(db.Integer, primary_key=True)
#invoice = db.Column(db.String(20), unique=True, nullable=False)
status = db.Column(db.String(20), default='Pending', nullable=False)
customer_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
unique=False,
nullable=True)
date_created = db.Column(db.DateTime,
default=datetime.utcnow,
nullable=False)
orders = db.Column(JsonEcodedDict)
#def __repr__(self):
# return'<CustomerOrder %r>' % self.invoice
def __repr__(self):
return f"CustomerOrder('{self.id}', '{self.status}','{self.customer_id}')"
class Image(db.Model):
__tablename__ = 'images'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True, nullable=False)
filename = db.Column(db.String(128), unique=True,default='default.jpg')
productId = db.Column(db.Integer, db.ForeignKey('product.id'),nullable=False)
def __repr__(self):
return self.name
@property
def url(self):
return images.url(self.filename)
@property
def filepath(self):
if self.filename is None:
return
return images.path(self.filename)
class Order(db.Model, UserMixin):
__tablename__ = "orders"
id = db.Column(db.Integer, primary_key=True)
product_id = db.Column(db.Integer,
db.ForeignKey("products.id"),
nullable=False)
buyer_id = db.Column(db.Integer,
db.ForeignKey("buyers.id"),
nullable=False)
supplier_id = db.Column(db.Integer,
db.ForeignKey("suppliers.id"),
nullable=False)
order_time = db.Column(db.DateTime, default=datetime.utcnow)
shipment_time = db.Column(db.DateTime)
fulfillment_time = db.Column(db.DateTime)
last_change_time = db.Column(db.DateTime)
qty = db.Column(db.Integer, nullable=False, default=1)
status = db.Column(db.Integer,
db.ForeignKey("order_status.id"),
nullable=False)
tracking_number = db.Column(db.Integer)
unit_price = db.Column(db.Numeric, nullable=False)
total_price = db.Column(db.Numeric, nullable=False)
reviews = db.relationship("Reviews", backref="order", lazy="dynamic")
buyer_message = db.Column(db.String(512))
cart_item_id = db.Column(db.Integer, db.ForeignKey("cart.id"), unique=True)
coupon = db.Column(db.Integer, db.ForeignKey("coupon.id"))
def __init__(self,
product_id=None,
buyer_id=None,
qty=None,
status=1,
cart_item=False):
if not cart_item and not (product_id or buyer_id or qty):
print("missing info")
return False
if cart_item:
self.product_id = cart_item.product_id
self.buyer_id = cart_item.buyer_id
self.qty = cart_item.qty
self.cart_item_id = cart_item.id
self.status = 1
if cart_item.buyer_message:
self.buyer_message = cart_item.buyer_message
else:
self.product_id = product_id
self.buyer_id = buyer_id
self.qty = qty
self.supplier_id = Supplier.query.get(
(Product.query.get(self.product_id).supplier_id)).id
self.order_time = datetime.utcnow()
self.last_change_time = datetime.utcnow()
self.status = status
self.unit_price = Product.query.get(self.product_id).price
self.total_price = float(self.qty) * float(self.unit_price)
def make_shipment(self, tracking_number):
self.status = 2
self.tracking_number = tracking_number
self.shipment_time = datetime.utcnow()
self.last_change_time = datetime.utcnow()
db.session.commit()
def finish_order(self):
self.status = 3
db.session.commit()
def as_list(self):
return [
self.id,
self.product_id,
self.buyer_id,
self.supplier_id,
self.order_time,
self.qty,
self.status,
self.unit_price,
self.total_price,
self.reviews,
]
def get_order_review(self):
review = Reviews.query.filter_by(order_id=self.id).first()
return review
def submit_review(self,
stars,
review_content=None,
return_review_object=False):
if not self.reviews.first() and self.status == 3:
review = Reviews(order=self,
stars=stars,
review_content=review_content)
db.session.add(review)
db.session.commit()
if return_review_object:
return {
"success": len(self.reviews.all()) == 1,
"review": self.reviews.first(),
}
else:
return {
"success": len(self.reviews.all()) == 1,
"review": self.reviews.first().id,
}
return False
def confirm_supplied(self):
self.status = 3
self.fulfillment_time = datetime.utcnow()
self.last_change_time = datetime.utcnow()
db.session.commit()
class Cart(db.Model, UserMixin):
__tablename__ = "cart"
id = db.Column(db.Integer, primary_key=True)
buyer_id = db.Column(db.Integer,
db.ForeignKey("buyers.id"),
nullable=False)
product_id = db.Column(db.Integer,
db.ForeignKey("products.id"),
nullable=False)
qty = db.Column(db.String(256), default=1, nullable=False)
status = db.Column(db.Integer,
db.ForeignKey("cart_status.id"),
nullable=False)
add_time = db.Column(db.DateTime)
order_time = db.Column(db.DateTime)
cancal_time = db.Column(db.DateTime)
buyer_message = db.Column(db.String(256))
purchase_way = db.Column(db.String(256), default="via cart")
order_id = db.relationship("Order", backref="cart_item", uselist=False)
def __init__(self,
buyer_id,
product_id,
qty,
status=1,
buy_now=False,
buyer_message=False):
self.buyer_id = buyer_id
self.product_id = product_id
self.qty = qty
self.status = status
self.add_time = datetime.utcnow()
if not buy_now:
self.purchase_way = "via cart"
if buy_now:
self.purchase_way = "buy now"
if buyer_message:
self.buyer_message = buyer_message
db.session.add(self)
db.session.commit()
self.stamp_ordered()
@property
def price(self):
return round(self.cart_product.price, 1)
@property
def total(self):
return round(self.cart_product.price * int(self.qty), 1)
def add_buyer_message(self, buyer_message=False):
if buyer_message:
self.buyer_message = buyer_message
else:
print("you called add_buyer_message function without a message ")
db.session.commit()
def stamp_ordered(self, buyer_message=False):
if buyer_message:
self.buyer_message = buyer_message
db.session.commit()
order = Order(cart_item=self)
db.session.add(order)
db.session.commit()
print("stamp_ordered")
print(self.id)
print(self.__dict__)
self.status = 2
self.order_time = datetime.utcnow()
db.session.commit()
return order.id
def cancal(self):
try:
self.status = 3
self.cancal_time = datetime.utcnow()
db.session.commit()
return True
except:
return False
class Product(db.Model, UserMixin):
__tablename__ = "products"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(256), nullable=False)
desc = db.Column(db.String(1024), default="N/A")
supplier_id = db.Column(db.Integer,
db.ForeignKey("suppliers.id"),
nullable=False)
category = db.Column(db.Integer, db.ForeignKey("category.id"))
brand = db.Column(db.String(256), default="N/A")
price = db.Column(db.Numeric, nullable=False)
picture = db.Column(db.String(64), default="default.jpg")
Additional_information = db.Column(db.String(1024), default="N/A")
orders = db.relationship("Order", backref="product", lazy="dynamic")
cart = db.relationship("Cart", backref="cart_product", lazy="dynamic")
add_time = db.Column(db.DateTime)
last_change_time = db.Column(db.DateTime)
coupons = db.relationship("Coupon", backref="coupons", lazy="dynamic")
def __init__(
self,
name,
supplier_id,
price,
product_sub_type="N/A",
desc="N/A",
brand="N/A",
picture="default.jpg",
Additional_information="N/A",
category=1,
):
self.name = name
self.supplier_id = supplier_id
self.price = price
self.category = category
self.product_sub_type = product_sub_type.lower()
self.desc = desc
self.brand = brand
self.picture = "/static/img/products/" + picture
self.add_time = datetime.utcnow()
self.last_change_time = datetime.utcnow()
def as_list(self):
return [
self.id,
self.name,
self.desc,
self.supplier_id,
self.brand,
float(self.price),
self.picture,
self.Additional_information,
]
def get_info(self):
response = self.__dict__
if response["_sa_instance_state"]:
del response["_sa_instance_state"]
response["price"] = float(response["price"])
response["orders"] = self.get_product_orders(get_product_orders=False,
sum_orders=True,
sum_units=True)
return response
# returns dict [get_orders_info]=array of order objects , *[order_count]-num of orders , *[units_sold]=units_sold
def get_product_orders(self,
get_product_orders=True,
count_orders=False,
count_units=False):
response = dict()
if get_product_orders:
response["get_product_orders"] = (db.session.query(Order).join(
Product, Product.id == Order.product_id).filter(
Order.product_id == self.id).all())
if count_orders:
response["count_orders"] = (db.session.query(Order).join(
Product, Product.id == Order.product_id).filter(
Order.product_id == self.id).count())
if count_units:
response["count_units"] = (db.session.query(db.func.sum(
Order.qty)).join(Product,
Product.id == Order.product_id).filter(
Order.product_id == self.id).all()[0][0])
return response
def get_review(self, get_review=True, avg=False, count=False):
response = dict()
if get_review:
response["get_review"] = (db.session.query(Reviews).join(
Order, Order.id == Reviews.order_id).filter(
Order.product_id == self.id).all())
if avg:
response["avg"] = (db.session.query(db.func.avg(
Reviews.stars)).join(
Order, Order.id == Reviews.order_id).filter(
Order.product_id == self.id).all()[0][0])
if count:
response["count"] = (db.session.query(db.func.count(
Reviews.id)).join(Order, Order.id == Reviews.order_id).filter(
Order.product_id == self.id).all()[0][0])
return response
def update_picture(self, picture):
picture_fn = save_photo(photo=picture, _dir="product")
self.picture = "/static/img/products/" + picture_fn
db.session.commit()
@property
def order_count(self):
return db.session.query(Order).filter(
Order.product_id == self.id).count()
@property
def open_order_count(self):
return (db.session.query(Order).join(Order_status).filter(
Order.product_id == self.id).filter(
Order_status.is_open == True).count())
@property
def units_sold(self):
res = (db.session.query(db.func.sum(
Order.qty)).join(Order_status).filter(
Order.product_id == self.id).first()[0])
if res:
return res
else:
return 0
@property
def open_units_sold(self):
res = (db.session.query(db.func.sum(
Order.qty)).join(Order_status).filter(
Order.product_id == self.id).filter(
Order_status.is_open == True).first()[0])
if res:
return res
else:
return 0
@property
def review_avg(self):
res = (db.session.query(db.func.avg(Reviews.stars)).join(
Order, Order.id == Reviews.order_id).filter(
Order.product_id == self.id).all()[0][0])
if res:
return round(res, 2)
else:
return False
@property
def review_count(self):
res = (db.session.query(Reviews).join(
Order, Order.id == Reviews.order_id).filter(
Order.product_id == self.id).count())
if res:
return res
else:
return False
@property
def reviews(self):
res = db.session.query(Reviews).join(Order).filter(
Order.product_id == self.id)
if res:
return res
else:
return False
@property
def revenue(self):
res = (db.session.query(db.func.sum(
Order.total_price)).filter(Order.product_id == self.id).first()[0])
if res:
return round(res, 0)
else:
return 0
@property
def open_revenue(self):
res = (db.session.query(db.func.sum(
Order.total_price)).join(Order_status).filter(
Order.product_id == self.id).filter(
Order_status.is_open == True).first()[0])
if res:
return round(res, 0)
else:
return 0
