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)