def similar_products(self):
if len(self.salts):
return [i[0] for i in Product.query.with_entities(Product.id)
.join(ProductSalt, and_(ProductSalt.product_id == Product.id))
.filter(ProductSalt.salt_id.in_([i.id for i in self.salts])).group_by(Product.id)
.having(func.Count(func.Distinct(ProductSalt.salt_id)) == len(self.salts)).all()]
return []
def get(self):
shops = request.args.getlist('__retail_shop_id__in')
for shop in shops:
if not current_user.has_shop_access(shop):
return make_response(jsonify({'message': 'Access Forbidden'}),
403)
if len(shops) == 1:
shops = shops[0].split(',')
from_date = datetime.strptime(request.args['__created_on__gte'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
to_date = datetime.strptime(request.args['__created_on__lte'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
days = (to_date - from_date).days
collection_type = 'day'
if days > 28:
collection_type = 'week'
if days > 360:
collection_type = 'month'
orders = self.model.query.join(Item, and_(Item.order_id == self.model.id)) \
.filter(self.model.retail_shop_id.in_(shops), self.model.created_on.between(from_date, to_date))
total_orders, total_sales, total_items, total_quantity, total_due = \
orders.with_entities(func.Count(func.Distinct(self.model.id)), func.sum(self.model.total),
func.Count(func.Distinct(Item.product_id)), func.sum(Item.quantity),
func.Sum(self.model.amount_due)).all()[0]
orders = self.model.query\
.with_entities(func.count(func.Distinct(self.model.id)), func.sum(self.model.total),
func.avg(self.model.total),
func.cast(func.date_trunc(collection_type, func.cast(self.model.created_on, Date)), Text)
.label('dateWeek')) \
.filter(self.model.created_on.between(from_date, to_date), self.model.retail_shop_id.in_(shops)) \
.group_by('dateWeek').order_by('dateWeek').all()
items = Item.query.join(self.model, and_(self.model.id == Item.order_id)) \
.filter(self.model.retail_shop_id.in_(shops))
max_sold_items = items.join(Product, and_(Product.id == Item.product_id)) \
.with_entities(func.Sum(Item.quantity), Product.name, ) \
.filter(self.model.created_on.between(from_date, to_date)) \
.group_by(Item.product_id, Product.name).order_by(-func.Sum(Item.quantity)).limit(10).all()
max_profitable_items = items.join(Product, and_(Product.id == Item.product_id)) \
.join(Stock, and_(Stock.id == Item.stock_id)) \
.with_entities(func.Sum((Item.unit_price - Stock.purchase_amount) * Item.quantity), Product.name, ) \
.filter(self.model.created_on.between(from_date, to_date)) \
.group_by(Item.product_id, Product.name) \
.order_by(-func.Sum((Item.unit_price - Stock.purchase_amount) * Item.quantity)) \
.limit(10).all()
return make_response(
jsonify(
dict(total_orders=total_orders,
total_sales=total_sales,
total_quantity=total_quantity,
max_sold_items=max_sold_items,
max_profitable_items=max_profitable_items,
total_items=str(total_items),
orders=orders)), 200)
def get(self):
shops = request.args.getlist('__retail_shop_id__in')
if len(shops) == 1:
shops = shops[0].split(',')
from_date = datetime.strptime(request.args['__from'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
to_date = datetime.strptime(request.args['__to'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
days = (to_date - from_date).days
collection_type = 'day'
if days > 28:
collection_type = 'week'
if days > 140:
collection_type = 'month'
orders = Order.query.join(Item,
and_(Item.order_id == Order.id)).filter(
Order.retail_shop_id.in_(shops))
total_orders, total_sales, total_items, total_quantity, total_due = \
orders.with_entities(func.Count(Order.id), func.sum(Order.total),
func.Count(func.Distinct(Item.product_id)), func.sum(Item.quantity),
func.Sum(Order.amount_due)).all()[0]
orders = Order.query.with_entities(func.count(Order.id),
func.cast(func.date_trunc(collection_type,
func.cast(Order.created_on, Date)), Text)
.label('dateWeek'))\
.filter(Order.created_on.between(from_date, to_date))\
.group_by('dateWeek').all()
# new_customers = orders.join(Customer, and_(Customer.id == Order.customer_id))\
# .with_entities(func.Count(Order.customer_id)).scalar()
#
# return_customers = orders.join(Customer, and_(Customer.id == Order.customer_id))\
# .with_entities(func.Count(Order.customer_id)).scalar()
#
items = Item.query.join(Order, and_(Order.id == Item.order_id))\
.filter(Order.retail_shop_id.in_(shops))
max_sold_items = items.join(Product, and_(Product.id == Item.product_id))\
.with_entities(func.Count(Item.id), Product.name,
func.cast(func.date_trunc(collection_type, func.cast(Order.created_on, Date)), Text)
.label('dateWeek'))\
.filter(Order.created_on.between(from_date, to_date))\
.group_by(Item.product_id, Product.name, 'dateWeek').order_by(-func.Count(Item.id)).limit(10).all()
# min_sold_items = items.join(Product, and_(Product.id == Item.product_id))\
# .with_entities(func.Count(Item.id), Item.product_id, Product.name)\
# .group_by(Item.product_id, Product.name).order_by(func.Count(Item.id)).limit(10).all()
return make_response(
jsonify(
dict(total_orders=total_orders,
total_sales=total_sales,
total_quantity=total_quantity,
max_sold_items=max_sold_items,
total_items=str(total_items),
orders=orders)), 200)
def get(self):
shops = request.args.getlist('__retail_shop_id__in')
for shop in shops:
if not current_user.has_shop_access(shop):
return make_response(jsonify({'message': 'Access Forbidden'}),
403)
brand_id = request.args.get('__retail_brand_id__equal')
if len(shops) == 1:
shops = shops[0].split(',')
from_date = datetime.strptime(request.args['__created_on__gte'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
to_date = datetime.strptime(request.args['__created_on__lte'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
days = (to_date - from_date).days
collection_type = 'day'
if days > 56:
collection_type = 'week'
if days > 360:
collection_type = 'month'
new_customers = self.model.query.join(Order, and_(Order.customer_id == self.model.id)) \
.with_entities(func.Count(func.Distinct(Order.customer_id)), func.Sum(Order.total), func.avg(Order.total),
func.cast(func.date_trunc(collection_type, func.cast(self.model.created_on, Date)), Text)
.label('dateWeek')
) \
.filter(and_(self.model.created_on.between(from_date, to_date), Order.created_on.between(from_date, to_date),
Order.retail_shop_id.in_(shops))).group_by('dateWeek').order_by('dateWeek')\
.having(func.Count(func.Distinct(Order.id)) > 0).all()
return_customers = self.model.query.join(Order, and_(Order.customer_id == self.model.id)) \
.with_entities(func.Count(func.Distinct(Order.customer_id)), func.Sum(Order.total), func.avg(Order.total),
func.cast(func.date_trunc(collection_type, func.cast(self.model.created_on, Date)), Text)
.label('dateWeek')
) \
.filter(and_(self.model.created_on.between(from_date, to_date), Order.created_on.between(from_date, to_date))) \
.having(func.Count(func.Distinct(Order.id)) > 1).group_by('dateWeek').order_by('dateWeek').all()
old_customers = Order.query.join(Customer, and_(Customer.id == Order.customer_id)) \
.with_entities(func.Count(func.Distinct(Order.customer_id)), func.Sum(Order.total), func.avg(Order.total),
func.cast(func.date_trunc(collection_type, func.cast(Order.created_on, Date)), Text)
.label('dateWeek')
) \
.filter(and_(Customer.created_on <= from_date, Order.created_on.between(from_date, to_date),
Order.retail_shop_id.in_(shops), Customer.retail_brand_id == brand_id))\
.group_by('dateWeek').order_by('dateWeek') \
.having(func.Count(func.Distinct(Order.id)) > 0).all()
total_due = self.model.query.outerjoin(Order, and_(Order.customer_id == self.model.id)) \
.outerjoin(CustomerTransaction, and_(CustomerTransaction.customer_id == self.model.id)) \
.with_entities(func.coalesce(cast(func.Sum(Order.total), Float), 0.0) -
func.coalesce(cast(func.Sum(Order.amount_paid), Float), 0.0)
- func.coalesce(cast(func.Sum(CustomerTransaction.amount), Float), 0.0)) \
.filter(self.model.retail_brand_id == brand_id).scalar()
top_customers = self.model.query.outerjoin(Order, and_(Order.customer_id == self.model.id)) \
.with_entities(func.Count(func.Distinct(Order.id)), self.model.name) \
.filter(self.model.retail_brand_id == brand_id, Order.created_on.between(from_date, to_date),
Order.retail_shop_id.in_(shops)).group_by(Order.customer_id, self.model.name) \
.order_by(-func.Count(func.Distinct(Order.id))).limit(10).all()
top_billed_customers = self.model.query.outerjoin(Order, and_(Order.customer_id == self.model.id)) \
.with_entities(func.Sum(Order.total), self.model.name) \
.filter(self.model.retail_brand_id == brand_id, Order.created_on.between(from_date, to_date),
Order.retail_shop_id.in_(shops)).group_by(Order.customer_id, self.model.name) \
.order_by(-func.Sum(Order.total)).limit(10).all()
return make_response(
jsonify(
dict(new_customers=new_customers,
return_customers=return_customers,
old_customers=old_customers,
top_billed_customers=top_billed_customers,
total_due=total_due,
top_customers=top_customers)), 200)
def total_items(cls):
return select([func.Count(func.Distinct(Stock.id))]).where(Stock.distributor_bill_id == cls.id).as_scalar()