def report_products(cls, orders_ids=list(), subquery=False):
query = session().query(Product,
func.sum(OrderProduct.amount).label('total'))
query = query.select_from(OrderProduct).join(Product)
query = query.filter(OrderProduct.order_id.in_(orders_ids))
query = query.group_by(Product.id)
if subquery:
return query.subquery()
else:
return query.all()
def report_ingredients(cls, products_ids=list(), subquery=False):
query = session().query(
Ingredient,
func.sum(ProductIngredient.amount).label('total'))
query = query.select_from(ProductIngredient).join(Ingredient)
query = query.filter(ProductIngredient.product_id.in_(products_ids))
query = query.group_by(ProductIngredient.ingredient_id)
if subquery:
return query.subquery()
else:
return query.all()
def report_ingredients(cls, orders_ids=list(), subquery=False):
stmt = cls.report_products(orders_ids=orders_ids, subquery=True)
query = session().query(
Ingredient,
func.sum(stmt.c.total * ProductIngredient.amount),
)
query = query.select_from(ProductIngredient).join(Ingredient)
query = query.join(stmt, ProductIngredient.product_id == stmt.c.id)
query = query.group_by(Ingredient.id)
if subquery:
return query.subquery()
else:
return query.all()
def save(self):
if not getattr(self, 'id', None):
self.created = datetime.utcnow()
session().add(self)
else:
cls = self.__class__
query = session().query(cls)
query = query.filter(cls.id == self.id)
query.update({
column: getattr(self, column)
for column in self.__table__.columns.keys()
})
self.updated = datetime.utcnow()
session().commit()
if 'code' in self.__table__.columns.keys():
if not self.code:
self.code = friendly_code.encode(int(self.id))
session().commit()
def get_by_code(cls, code):
if getattr(cls, 'code', None):
return session().query(cls).filter(cls.code == code).first()
else:
return None
def get_by_id(cls, model_id):
if cls.id:
return session().query(cls).filter(cls.id == model_id).first()
else:
return None
def get_all(cls):
return session().query(cls).all()
def delete(self):
session().delete(self)
session().commit()
def get_by_email(cls, email):
return session().query(cls).filter(cls.email == email).first()
def get_by_product_order(cls, product_id, order_id):
# return session().query(cls).filter(cls.code == code).first()
return session().query(cls).filter(cls.product_id == product_id) \
.filter(cls.order_id == order_id).first()
def get_by_order_id(cls, order_id):
return list(session().query(cls).filter(cls.order_id == order_id))
def get_by_product_id(cls, product_id):
return list(session().query(cls).filter(cls.product_id == product_id))
def get_by_product_ingredient(cls, product_id, ingredient_id):
# return session().query(cls).filter(cls.code == code).first()
return session().query(cls).filter(cls.product_id == product_id) \
.filter(cls.ingredient_id == ingredient_id).first()
def get_by_ingredient_id(cls, ingredient_id):
return list(
session().query(cls).filter(cls.ingredient_id == ingredient_id))
