def total_sales(self):
data = self.orders.with_entities(func.Sum(Order.total), func.Count(Order.id), func.Sum(Order.items_count)) \
.filter(Order.store_id == self.id, Order.created_on >= datetime.now().date()).all()[0]
return {
'total_sales': data[0],
'total_orders': data[1],
'total_items': str(data[2])
}
def total_sales(self):
data = self.orders.with_entities(func.Sum(Order.total), func.Count(Order.id), func.Sum(Order.items_count))\
.filter(Order.retail_shop_id == self.id).all()[0]
return {
'total_sales': data[0],
'total_orders': data[1],
'total_items': str(data[2])
}
def units_sold(self):
total_sold = self.order_items.with_entities(func.Sum(Item.quantity)) \
.filter(Item.stock_id == self.id, Item.stock_adjust.isnot(True)).scalar()
if total_sold:
if total_sold >= self.units_purchased and not self.is_sold:
self.is_sold = True
db.session.commit()
return total_sold
else:
return 0
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'
stock_metrics = Stock.query \
.with_entities(cast(func.coalesce(func.Sum(self.model.purchase_amount * self.model.units_purchased), 0), Integer),
cast(func.coalesce(func.Sum(self.model.selling_amount * self.model.units_sold), 0), Integer),
cast(func.coalesce(
func.Sum((self.model.units_purchased - self.model.units_sold) * self.model.purchase_amount)
.filter(self.model.expired == True), 0), Integer),
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)).order_by('dateWeek').group_by('dateWeek').limit(100).all()
distributor_metrics = DistributorBill.query \
.with_entities(cast(func.coalesce(func.Sum(DistributorBill.bill_amount), 0), Integer), DistributorBill.distributor_name,
) \
.filter(DistributorBill.created_on.between(from_date, to_date),
DistributorBill.retail_shop_id.in_(shops)) \
.group_by(DistributorBill.distributor_id) \
.limit(100).all()
return make_response(jsonify(dict(stock_metrics=stock_metrics, distributor_metrics=distributor_metrics)), 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 bill_amount(cls):
return select([func.Sum(Stock.units_purchased * Stock.purchase_amount)]) \
.where(Stock.distributor_bill_id == cls.id).as_scalar()
def bill_amount(self):
return self.purchased_items.with_entities(func.Sum(Stock.purchase_amount * Stock.units_purchased)).scalar()
def units_sold(cls):
return select([func.coalesce(func.Sum(Item.quantity), 0)])\
.where(and_(Item.stock_id == cls.id, Item.stock_adjust.isnot(True))).as_scalar()
def available_stock(self):
return self.stocks.filter(or_(Stock.is_sold == False, Stock.is_sold == None))\
.with_entities(func.Sum(Stock.units_purchased)-func.Sum(Stock.units_sold)).scalar()
def available_stock(cls):
return select([func.coalesce(func.Sum(Stock.units_purchased), 0)-func.coalesce(func.Sum(Stock.units_sold), 0)])\
.where(and_(or_(Stock.is_sold != True), Stock.product_id == cls.id)).as_scalar()
def available_stock(self):
return self.stocks.filter(Stock.is_sold != True, Stock.expired == False)\
.with_entities(func.coalesce(func.Sum(Stock.units_purchased), 0)-func.coalesce(func.Sum(Stock.units_sold),
0)).scalar()
def amount_due(self):
return self.orders.with_entities(func.coalesce(func.Sum(Order.total), 0) -
func.coalesce(func.Sum(Order.amount_paid), 0)).scalar() - \
self.transactions.with_entities(func.coalesce(func.Sum(CustomerTransaction.amount), 0)).scalar()
def total_billing(self):
return self.orders.with_entities(
func.coalesce(func.Sum(Order.total), 0)).scalar()
def available_stock(cls):
return select([func.Sum(Stock.units_purchased)-func.Sum(Stock.units_sold)])\
.where(and_(or_(Stock.is_sold == False, Stock.is_sold == None), Stock.product_id == cls.id)).as_scalar()
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 units_sold(cls):
return select([func.coalesce(func.Sum(Item.quantity), 0)]).where(Item.stock_id == cls.id).as_scalar()