def index():
'''Budget dashboard'''
current_user_id = session.get('logged_in_user')
# get uncategorized expenses
exp = Expenses(current_user_id)
uncategorized_expenses = exp.get_entries(category_name="Uncategorized")
# get latest expenses
exp = Expenses(current_user_id)
latest_expenses = exp.get_entries(limit=5)
# get latest income
inc = Income(current_user_id)
latest_income = inc.get_entries(limit=5)
# get accounts
acc = Accounts(current_user_id)
accounts = acc.get_accounts_and_loans()
# split, get totals
assets, liabilities, loans, assets_total, liabilities_total = [], [], [], 0, 0
for a in accounts:
if a[0].type == 'asset':
assets.append(a)
assets_total += float(a[0].balance)
elif a[0].type == 'liability':
liabilities.append(a)
liabilities_total += float(a[0].balance)
elif a[0].type == 'loan':
# if we owe someone, it is our liability
if float(a[0].balance) < 0:
liabilities.append(a)
liabilities_total += float(a[0].balance)
else:
assets.append(a)
# monthly totals
t, totals_list, highest_bar = Totals(current_user_id), [], 0.
# object to dict
for total in t.get_totals():
bar = {}
bar['month'] = total.month
if (float(total.expenses) > 0): bar['expenses'] = float(total.expenses)
if (float(total.income) > 0): bar['income'] = float(total.income)
totals_list.append(bar)
if (total.expenses > highest_bar): highest_bar = total.expenses
if (total.income > highest_bar): highest_bar = total.income
# calculate height for each bar
for total in totals_list:
if 'expenses' in total: total['expenses-height'] = (total['expenses'] / highest_bar) * 100
if 'income' in total: total['income-height'] = (total['income'] / highest_bar) * 100
return render_template('admin_dashboard.html', **locals())
def dashboard():
'''Test budget dashboard'''
u = UsersTable(u"Admin", False, u"admin", u"admin")
db_session.add(u)
db_session.commit()
current_user_id = u.id
# get uncategorized expenses
exp = Expenses(current_user_id)
uncategorized_expenses = exp.get_entries(category_name="Uncategorized")
# get latest expenses
exp = Expenses(current_user_id)
latest_expenses = exp.get_entries(limit=5)
# get accounts
acc = Accounts(current_user_id)
accounts = acc.get_accounts_and_loans()
# split, get totals
assets, liabilities, loans, assets_total, liabilities_total = [], [], [], 0, 0
for a in accounts:
if a[0].type == 'asset':
assets.append(a)
assets_total += float(a[0].balance)
elif a[0].type == 'liability':
liabilities.append(a)
liabilities_total += float(a[0].balance)
elif a[0].type == 'loan':
# if we owe someone, it is our liability
if float(a[0].balance) < 0:
liabilities.append(a)
liabilities_total += float(a[0].balance)
else:
assets.append(a)
# get the monthly totals
t = Totals(current_user_id)
totals = t.get_totals()
return render_template('admin_dashboard.html', **locals())
#class SQLiteSequenceTable(Base):
# """SQLite sequence table"""
#
# __tablename__ = 'sqlite_master'
# rowid = Column(Integer, primary_key=True)
# name = Column(String(200))
# seq = Column(Integer)
def dashboard():
'''Test budget dashboard'''
u = UsersTable(u"Admin", False, u"admin", u"admin")
db_session.add(u)
db_session.commit()
current_user_id = u.id
# get uncategorized expenses
exp = Expenses(current_user_id)
uncategorized_expenses = exp.get_entries(category_name="Uncategorized")
# get latest expenses
exp = Expenses(current_user_id)
latest_expenses = exp.get_entries(limit=5)
# get accounts
acc = Accounts(current_user_id)
accounts = acc.get_accounts_and_loans()
# split, get totals
assets, liabilities, loans, assets_total, liabilities_total = [], [], [], 0, 0
for a in accounts:
if a[0].type == 'asset':
assets.append(a)
assets_total += float(a[0].balance)
elif a[0].type == 'liability':
liabilities.append(a)
liabilities_total += float(a[0].balance)
elif a[0].type == 'loan':
# if we owe someone, it is our liability
if float(a[0].balance) < 0:
liabilities.append(a)
liabilities_total += float(a[0].balance)
else:
assets.append(a)
# get the monthly totals
t = Totals(current_user_id)
totals = t.get_totals()
return render_template('admin_dashboard.html', **locals())
#class SQLiteSequenceTable(Base):
# """SQLite sequence table"""
#
# __tablename__ = 'sqlite_master'
# rowid = Column(Integer, primary_key=True)
# name = Column(String(200))
# seq = Column(Integer)
def __init__(self, user_id):
self.user_id = user_id
self.totals = Totals(user_id)
class ExpensesBase():
user_id = None
entries = None
categories = None
entry = None # cache
totals = None
def __init__(self, user_id):
self.user_id = user_id
self.totals = Totals(user_id)
def get_entries(self,
category_id=None,
category_name=None,
date_from=None,
date_to=None,
limit=None):
if not self.entries:
self.entries = ExpensesTable.query\
.filter(ExpensesTable.user == self.user_id)\
.join(ExpenseCategoriesTable)\
.add_columns(ExpenseCategoriesTable.name, ExpenseCategoriesTable.slug)\
.order_by(desc(ExpensesTable.date)).order_by(desc(ExpensesTable.id))\
.outerjoin(ExpensesToLoansTable)\
.outerjoin((UsersTable, (UsersTable.id == ExpensesToLoansTable.shared_with)))\
.add_columns(UsersTable.name, UsersTable.slug)
# provided category id
if category_id:
self.entries = self.entries.filter(
ExpensesTable.category == category_id)
# provided category name
if category_name:
self.entries = self.entries.filter(
ExpenseCategoriesTable.name == category_name)
# provided date range
if date_from and date_to:
self.entries = self.entries.filter(
ExpensesTable.date >= date_from).filter(
ExpensesTable.date <= date_to)
# provided count
if limit:
self.entries = self.entries.limit(limit)
return self.entries
def get_categories(self):
if not self.categories:
self.categories = ExpenseCategoriesTable.query\
.filter(ExpenseCategoriesTable.user == self.user_id).order_by(ExpenseCategoriesTable.name)
return self.categories
def is_category(self, slug=None, name=None, id=None):
categories = self.get_categories()
for cat in categories:
if slug:
if cat.slug == slug:
return cat.id
break
elif name:
if cat.name == name:
return cat.id
break
elif id:
if cat.id == int(id):
return cat.id
break
def add_category(self, name):
c = ExpenseCategoriesTable(self.user_id, name)
db_session.add(c)
db_session.commit()
def add_expense(self,
date,
description,
amount,
category_id=None,
account_id=None,
pass_thru=False):
# add into uncategorized expenses if category not provided
if not category_id:
category_id = self.is_category(name="Uncategorized")
if not category_id:
# crete a new one
c = ExpenseCategoriesTable(self.user_id, u'Uncategorized')
db_session.add(c)
db_session.commit()
category_id = c.id
# find default account if not provided
if not account_id:
acc = Accounts(self.user_id)
account_id = acc.get_default_account()
if not account_id:
account_id = acc.add_default_account()
# add the actual expense
e = ExpensesTable(self.user_id, date, category_id, description,
account_id, amount, pass_thru)
db_session.add(e)
db_session.commit()
# update the totals
self.totals.update_expense(amount, date)
return e.id
def edit_expense(self,
date,
amount,
account_id,
expense_id,
description=None,
category_id=None,
pass_thru=False):
e = self.get_simple_expense(expense_id)
if e:
# update the totals
self.totals.update_expense(-float(e.amount), e.date)
# category and description not provided?
if not description: description = e.description
if not category_id: category_id = e.category
e.date, e.category, e.description, e.deduct_from, e.amount, e.pass_thru\
= date, category_id, description, account_id, amount, pass_thru
db_session.add(e)
db_session.commit()
# update the totals
self.totals.update_expense(amount, date)
return e # return so we see the updated values
def edit_pass_thru_expense(self, description, category_id, expense_id):
e = self.get_simple_expense(expense_id)
if e:
e.description, e.category = description, category_id
db_session.add(e)
db_session.commit()
def get_simple_expense(self, expense_id):
return ExpensesTable.query\
.filter(and_(ExpensesTable.user == self.user_id, ExpensesTable.id == expense_id)).first()
def get_expense(self, expense_id=None, loan_id=None):
if loan_id:
# fetch the expense through a loan
self.entry = ExpensesTable.query\
.outerjoin(ExpensesToLoansTable)\
.filter(and_(ExpensesTable.user == self.user_id, ExpensesToLoansTable.loan == loan_id))\
.add_columns(ExpensesToLoansTable.loan, ExpensesToLoansTable.shared_with, ExpensesToLoansTable.percentage,
ExpensesToLoansTable.original_amount)\
.first()
else:
# if there is no cache or cache id does not match
if not self.entry or self.entry[0].id != expense_id:
self.entry = ExpensesTable.query\
.filter(and_(ExpensesTable.user == self.user_id, ExpensesTable.id == expense_id))\
.outerjoin(ExpensesToLoansTable).add_columns(ExpensesToLoansTable.loan, ExpensesToLoansTable.shared_with,
ExpensesToLoansTable.percentage,
ExpensesToLoansTable.original_amount)\
.first()
return self.entry
def delete_expense(self, expense_id):
e = self.get_expense(expense_id=expense_id)
# update the totals
self.totals.update_expense(-float(e[0].amount), e[0].date)
# delete
ExpensesTable.query.filter(ExpensesTable.id == expense_id).delete()
db_session.commit()
# blindly obey
def link_to_loan(self, expense_id, loan_id, shared_with, percentage,
original_amount):
l = ExpensesToLoansTable(expense=expense_id,
loan=loan_id,
shared_with=shared_with,
percentage=percentage,
original_amount=original_amount)
db_session.add(l)
db_session.commit()
# blindly obey and delete records for all entries associated with this loan
def unlink_loan(self, loan_id):
ExpensesToLoansTable.query\
.filter(ExpensesToLoansTable.loan == loan_id).delete()
db_session.commit()
# blindly obey and delete records for all entries associated with this loan
def modify_loan_link(self, loan_id, percentage, original_amount):
le = ExpensesToLoansTable.query.filter(
ExpensesToLoansTable.loan == loan_id)
if le:
for i in le:
i.percentage, i.original_amount = percentage, original_amount
db_session.add(i)
db_session.commit()
def get_category_averages(self):
'''Get monthly averages of all Expense categories'''
# SQL
entries = ExpensesTable.query\
.filter(ExpensesTable.user == self.user_id)\
.join(ExpenseCategoriesTable)\
.add_columns(ExpenseCategoriesTable.name, ExpenseCategoriesTable.slug)\
.order_by(asc(ExpenseCategoriesTable.name)).order_by(asc(ExpensesTable.date))\
.outerjoin((ExpensesToLoansTable, (ExpensesTable.id == ExpensesToLoansTable.expense)))\
.add_columns(ExpensesToLoansTable.percentage)
# up until today
today = today_date()
# beautify and calculate
result, last_category = {}, None
for entry in entries:
category, slug, split = entry[1], entry[2], entry[3]
if slug not in result:
if last_category:
# calculate the average of the last category
result[last_category][
"average"] = self.__get_category_average(
result[last_category]["begin"], today,
result[last_category]["total"])
# init new one
result[slug] = {
"total": 0,
"begin": entry[0].date,
"name": category,
"slug": slug
}
last_category = slug
# deal with shared expenses
if split:
result[slug]["total"] += entry[0].amount * (split / 100)
else:
result[slug]["total"] += entry[0].amount
# do not forget to calculate the average for the very last category
if last_category:
result[last_category]["average"] = self.__get_category_average(
result[last_category]["begin"], today,
result[last_category]["total"])
# descending sort based on the average amount
return sorted(result.values(), key=lambda x: -x["average"])
def __get_category_average(self, begin, end, total):
'''Give us an average spending based on a range between dates and a total over that period'''
diff = date_difference(begin, end)
# assume 29.5 days per month
if diff: return total / (diff / 29.5)
return total
def __init__(self, user_id):
self.user_id = user_id
self.totals = Totals(user_id)
class Income():
user_id = None
entries = None
categories = None
entry = None # cache
totals = None
def __init__(self, user_id):
self.user_id = user_id
self.totals = Totals(user_id)
def get_entries(self, category_id=None, category_name=None, date_from=None, date_to=None, limit=None):
if not self.entries:
self.entries = IncomeTable.query\
.filter(IncomeTable.user == self.user_id)\
.join(IncomeCategoriesTable)\
.add_columns(IncomeCategoriesTable.name, IncomeCategoriesTable.slug)\
.order_by(desc(IncomeTable.date)).order_by(desc(IncomeTable.id))
# provided category id
if category_id:
self.entries = self.entries.filter(IncomeTable.category == category_id)
# provided category name
if category_name:
self.entries = self.entries.filter(IncomeCategoriesTable.name == category_name)
# provided date range
if date_from and date_to:
self.entries = self.entries.filter(IncomeTable.date >= date_from).filter(IncomeTable.date <= date_to)
# provided count
if limit:
self.entries = self.entries.limit(limit)
return self.entries
def get_categories(self):
if not self.categories:
self.categories = IncomeCategoriesTable.query\
.filter(IncomeCategoriesTable.user == self.user_id).order_by(IncomeCategoriesTable.name)
return self.categories
def is_category(self, slug=None, name=None, id=None):
categories = self.get_categories()
for cat in categories:
if slug:
if cat.slug == slug:
return cat.id
break
elif name:
if cat.name == name:
return cat.id
break
elif id:
if cat.id == int(id):
return cat.id
break
def add_category(self, name):
c = IncomeCategoriesTable(self.user_id, name)
db_session.add(c)
db_session.commit()
def add_income(self, account_id, category_id, date, description, amount):
i = IncomeTable(self.user_id, date, category_id, description, account_id, amount)
db_session.add(i)
db_session.commit()
# update totals
self.totals.update_income(amount, date)
def edit_income(self, income_id, account_id, category_id, date, description, amount):
i = self.get_income(income_id)
if i:
# update totals
self.totals.update_income(-float(i.amount), i.date)
i.credit_to, i.category, i.date, i.description, i.amount = account_id, category_id, date, description, amount
db_session.add(i)
db_session.commit()
# update totals
self.totals.update_income(amount, date)
def get_income(self, income_id):
# if there is no cache or cache id does not match
if not self.entry or self.entry.id != income_id:
self.entry = IncomeTable.query.filter(and_(IncomeTable.user == self.user_id, IncomeTable.id == income_id)).first()
return self.entry
def delete_income(self, income_id):
i = self.get_income(income_id=income_id)
# update the totals
self.totals.update_income(-float(i.amount), i.date)
# delete
IncomeTable.query.filter(IncomeTable.id == income_id).delete()
db_session.commit()
class Income():
user_id = None
entries = None
categories = None
entry = None # cache
totals = None
def __init__(self, user_id):
self.user_id = user_id
self.totals = Totals(user_id)
def get_entries(self,
category_id=None,
category_name=None,
date_from=None,
date_to=None,
limit=None):
if not self.entries:
self.entries = IncomeTable.query\
.filter(IncomeTable.user == self.user_id)\
.join(IncomeCategoriesTable)\
.add_columns(IncomeCategoriesTable.name, IncomeCategoriesTable.slug)\
.order_by(desc(IncomeTable.date)).order_by(desc(IncomeTable.id))
# provided category id
if category_id:
self.entries = self.entries.filter(
IncomeTable.category == category_id)
# provided category name
if category_name:
self.entries = self.entries.filter(
IncomeCategoriesTable.name == category_name)
# provided date range
if date_from and date_to:
self.entries = self.entries.filter(
IncomeTable.date >= date_from).filter(
IncomeTable.date <= date_to)
# provided count
if limit:
self.entries = self.entries.limit(limit)
return self.entries
def get_categories(self):
if not self.categories:
self.categories = IncomeCategoriesTable.query\
.filter(IncomeCategoriesTable.user == self.user_id).order_by(IncomeCategoriesTable.name)
return self.categories
def is_category(self, slug=None, name=None, id=None):
categories = self.get_categories()
for cat in categories:
if slug:
if cat.slug == slug:
return cat.id
break
elif name:
if cat.name == name:
return cat.id
break
elif id:
if cat.id == int(id):
return cat.id
break
def add_category(self, name):
c = IncomeCategoriesTable(self.user_id, name)
db_session.add(c)
db_session.commit()
def add_income(self, account_id, category_id, date, description, amount):
i = IncomeTable(self.user_id, date, category_id, description,
account_id, amount)
db_session.add(i)
db_session.commit()
# update totals
self.totals.update_income(amount, date)
def edit_income(self, income_id, account_id, category_id, date,
description, amount):
i = self.get_income(income_id)
if i:
# update totals
self.totals.update_income(-float(i.amount), i.date)
i.credit_to, i.category, i.date, i.description, i.amount = account_id, category_id, date, description, amount
db_session.add(i)
db_session.commit()
# update totals
self.totals.update_income(amount, date)
def get_income(self, income_id):
# if there is no cache or cache id does not match
if not self.entry or self.entry.id != income_id:
self.entry = IncomeTable.query.filter(
and_(IncomeTable.user == self.user_id,
IncomeTable.id == income_id)).first()
return self.entry
def delete_income(self, income_id):
i = self.get_income(income_id=income_id)
# update the totals
self.totals.update_income(-float(i.amount), i.date)
# delete
IncomeTable.query.filter(IncomeTable.id == income_id).delete()
db_session.commit()
class ExpensesBase():
user_id = None
entries = None
categories = None
entry = None # cache
totals = None
def __init__(self, user_id):
self.user_id = user_id
self.totals = Totals(user_id)
def get_entries(self, category_id=None, category_name=None, date_from=None, date_to=None, limit=None):
if not self.entries:
self.entries = ExpensesTable.query\
.filter(ExpensesTable.user == self.user_id)\
.join(ExpenseCategoriesTable)\
.add_columns(ExpenseCategoriesTable.name, ExpenseCategoriesTable.slug)\
.order_by(desc(ExpensesTable.date)).order_by(desc(ExpensesTable.id))\
.outerjoin(ExpensesToLoansTable)\
.outerjoin((UsersTable, (UsersTable.id == ExpensesToLoansTable.shared_with)))\
.add_columns(UsersTable.name, UsersTable.slug)
# provided category id
if category_id:
self.entries = self.entries.filter(ExpensesTable.category == category_id)
# provided category name
if category_name:
self.entries = self.entries.filter(ExpenseCategoriesTable.name == category_name)
# provided date range
if date_from and date_to:
self.entries = self.entries.filter(ExpensesTable.date >= date_from).filter(ExpensesTable.date <= date_to)
# provided count
if limit:
self.entries = self.entries.limit(limit)
return self.entries
def get_categories(self):
if not self.categories:
self.categories = ExpenseCategoriesTable.query\
.filter(ExpenseCategoriesTable.user == self.user_id).order_by(ExpenseCategoriesTable.name)
return self.categories
def is_category(self, slug=None, name=None, id=None):
categories = self.get_categories()
for cat in categories:
if slug:
if cat.slug == slug:
return cat.id
break
elif name:
if cat.name == name:
return cat.id
break
elif id:
if cat.id == int(id):
return cat.id
break
def add_category(self, name):
c = ExpenseCategoriesTable(self.user_id, name)
db_session.add(c)
db_session.commit()
def add_expense(self, date, description, amount, category_id=None, account_id=None, pass_thru=False):
# add into uncategorized expenses if category not provided
if not category_id:
category_id = self.is_category(name="Uncategorized")
if not category_id:
# crete a new one
c = ExpenseCategoriesTable(self.user_id, u'Uncategorized')
db_session.add(c)
db_session.commit()
category_id = c.id
# find default account if not provided
if not account_id:
acc = Accounts(self.user_id)
account_id = acc.get_default_account()
if not account_id:
account_id = acc.add_default_account()
# add the actual expense
e = ExpensesTable(self.user_id, date, category_id, description, account_id, amount, pass_thru)
db_session.add(e)
db_session.commit()
# update the totals
self.totals.update_expense(amount, date)
return e.id
def edit_expense(self, date, amount, account_id, expense_id, description=None, category_id=None, pass_thru=False):
e = self.get_simple_expense(expense_id)
if e:
# update the totals
self.totals.update_expense(-float(e.amount), e.date)
# category and description not provided?
if not description: description = e.description
if not category_id: category_id = e.category
e.date, e.category, e.description, e.deduct_from, e.amount, e.pass_thru\
= date, category_id, description, account_id, amount, pass_thru
db_session.add(e)
db_session.commit()
# update the totals
self.totals.update_expense(amount, date)
return e # return so we see the updated values
def edit_pass_thru_expense(self, description, category_id, expense_id):
e = self.get_simple_expense(expense_id)
if e:
e.description, e.category = description, category_id
db_session.add(e)
db_session.commit()
def get_simple_expense(self, expense_id):
return ExpensesTable.query\
.filter(and_(ExpensesTable.user == self.user_id, ExpensesTable.id == expense_id)).first()
def get_expense(self, expense_id=None, loan_id=None):
if loan_id:
# fetch the expense through a loan
self.entry = ExpensesTable.query\
.outerjoin(ExpensesToLoansTable)\
.filter(and_(ExpensesTable.user == self.user_id, ExpensesToLoansTable.loan == loan_id))\
.add_columns(ExpensesToLoansTable.loan, ExpensesToLoansTable.shared_with, ExpensesToLoansTable.percentage,
ExpensesToLoansTable.original_amount)\
.first()
else:
# if there is no cache or cache id does not match
if not self.entry or self.entry[0].id != expense_id:
self.entry = ExpensesTable.query\
.filter(and_(ExpensesTable.user == self.user_id, ExpensesTable.id == expense_id))\
.outerjoin(ExpensesToLoansTable).add_columns(ExpensesToLoansTable.loan, ExpensesToLoansTable.shared_with,
ExpensesToLoansTable.percentage,
ExpensesToLoansTable.original_amount)\
.first()
return self.entry
def delete_expense(self, expense_id):
e = self.get_expense(expense_id=expense_id)
# update the totals
self.totals.update_expense(-float(e[0].amount), e[0].date)
# delete
ExpensesTable.query.filter(ExpensesTable.id == expense_id).delete()
db_session.commit()
# blindly obey
def link_to_loan(self, expense_id, loan_id, shared_with, percentage, original_amount):
l = ExpensesToLoansTable(expense=expense_id, loan=loan_id, shared_with=shared_with, percentage=percentage,
original_amount=original_amount)
db_session.add(l)
db_session.commit()
# blindly obey and delete records for all entries associated with this loan
def unlink_loan(self, loan_id):
ExpensesToLoansTable.query\
.filter(ExpensesToLoansTable.loan == loan_id).delete()
db_session.commit()
# blindly obey and delete records for all entries associated with this loan
def modify_loan_link(self, loan_id, percentage, original_amount):
le = ExpensesToLoansTable.query.filter(ExpensesToLoansTable.loan == loan_id)
if le:
for i in le:
i.percentage, i.original_amount = percentage, original_amount
db_session.add(i)
db_session.commit()
def get_category_averages(self):
'''Get monthly averages of all Expense categories'''
# SQL
entries = ExpensesTable.query\
.filter(ExpensesTable.user == self.user_id)\
.join(ExpenseCategoriesTable)\
.add_columns(ExpenseCategoriesTable.name, ExpenseCategoriesTable.slug)\
.order_by(asc(ExpenseCategoriesTable.name)).order_by(asc(ExpensesTable.date))\
.outerjoin((ExpensesToLoansTable, (ExpensesTable.id == ExpensesToLoansTable.expense)))\
.add_columns(ExpensesToLoansTable.percentage)
# up until today
today = today_date()
# beautify and calculate
result, last_category = {}, None
for entry in entries:
category, slug, split = entry[1], entry[2], entry[3]
if slug not in result:
if last_category:
# calculate the average of the last category
result[last_category]["average"] = self.__get_category_average(result[last_category]["begin"],
today, result[last_category]["total"])
# init new one
result[slug] = {"total": 0, "begin": entry[0].date, "name": category, "slug": slug}
last_category = slug
# deal with shared expenses
if split:
result[slug]["total"] += entry[0].amount * (split/100)
else:
result[slug]["total"] += entry[0].amount
# do not forget to calculate the average for the very last category
if last_category:
result[last_category]["average"] = self.__get_category_average(result[last_category]["begin"],
today, result[last_category]["total"])
# descending sort based on the average amount
return sorted(result.values(), key=lambda x: -x["average"])
def __get_category_average(self, begin, end, total):
'''Give us an average spending based on a range between dates and a total over that period'''
diff = date_difference(begin, end)
# assume 29.5 days per month
if diff: return total/(diff/29.5)
return total