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