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()