def income_categories(self, id): db = request.environ['sqlalchemy.session'] model = request.environ['sqlalchemy.model'] try: categories = db.execute(select([model.IncomeCategory.name, func.sum(model.BalanceChange.c.amount).label('amount_sum')], and_( model.balance_changes_table.c.balance_uid == id, model.balance_changes_table.c.occurred_on >= date(datetime.utcnow().year, datetime.utcnow().month, 1), model.balance_changes_table.c.is_income == True), from_obj=[model.income_categories_table.join(model.balance_changes_table)], group_by=[model.income_categories_table.c.name])).fetchall() maxValue = 0 for category in categories: if category[1] > maxValue: maxValue = category[1] except Exception, e: response.status_code = 400 return Messages.failedToRead(exception=e)
def current_month_expenses(self, id): db = request.environ['sqlalchemy.session'] model = request.environ['sqlalchemy.model'] try: select_sums = subquery('sums', [func.sum(model.BalanceChange.c.amount).label('sum')], and_( model.balance_changes_table.c.balance_uid == id, model.balance_changes_table.c.is_income == False), from_obj=[model.balance_changes_table], group_by=[extract('day', model.balance_changes_table.c.occurred_on)]) maxValue = db.execute(select([func.coalesce(func.max(column('sum')), 0)], from_obj=[select_sums])).fetchone()[0] expenses = db.execute(select([func.sum(model.BalanceChange.c.amount), extract('day', model.BalanceChange.c.occurred_on).label('day')], and_( model.balance_changes_table.c.balance_uid == id, model.balance_changes_table.c.occurred_on >= date(datetime.utcnow().year, datetime.utcnow().month, 1), model.balance_changes_table.c.is_income == False), from_obj=[model.balance_changes_table], order_by=['day'], group_by=['day'])).fetchall() except Exception, e: response.status_code = 400 return Messages.failedToRead(exception=e)