class ORM:
NO_CATEGORY = {
'PARENT': '',
'NAME': '- - -',
'ID': 0
}
def __init__(self, file_name):
self.storage = Storage(file_name)
# Accounts #
def fetch_accounts_summary(self):
accounts = [Account(*a) for a in self.storage.select_accounts_summary()]
return accounts
def fetch_accounts(self):
accounts = [Account(*a) for a in self.storage.select_accounts()]
return accounts
def update_account_type(self, account, text):
self.storage.update_account_type(account.id, text)
def update_account_status(self, account, state):
self.storage.update_account_status(account.id, state)
def update_account_budget_status(self, account, state):
self.storage.update_account_budget_status(account.id, state)
def add_account(self, name):
acc = self.storage.add_account(name)
return Account(*acc)
def delete_account(self, account):
return self.storage.delete_account(account.id)
# Budget records #
def _build_record(self, query_result):
"""
Builds Record object given query result and categories dictionary.
"""
amount, category_id, budget_type, day, year, month, rowid = query_result
category = self.fetch_subcategory(category_id)
category_name = category.parent + '::' + category.name
amount = from_cents(amount)
return Record(amount, category_name, budget_type, day, year, month,
rowid, category_id)
def fetch_records(self, month, year):
if month == 0:
records = self.storage.select_records_for_year(year)
else:
records = self.storage.select_records(month, year)
records = [self._build_record(r) for r in records]
return records
def fetch_budget_for_month(self, month, year, category):
if month == 0:
budget, *_ = self.storage.select_budget_for_year(year, category.id)
else:
budget, *_ = self.storage.select_budget(month, year, category.id)
return from_cents(budget or 0)
def delete_record(self, record):
self.storage.delete_record(record.id)
def add_record(self, amount, category, budget_type, day, year, month):
result = self.storage.add_record(amount, category.id, budget_type,
day, year, month)
record = self._build_record(result)
return record
def update_record(self, amount, category, budget_type, day, year,
month, record_id):
self.storage.update_record(amount, category.id, budget_type, day, year,
month, record_id)
def fetch_budget_report_bars(self, month, year):
"""
Fetches from DB budgets and transactions for each category and turns
them into BudgetBar.
"""
subcategories = self.fetch_subcategories()
for category in subcategories.values():
budget = self.fetch_budget_for_month(month, year, category)
fact = self.fetch_summary_for_month(month, year, category)
if budget == 0 and fact == 0:
continue
elif budget >= 0 and fact >= 0: # Income
expectation = max(budget - fact, 0)
elif budget <= 0 and fact <= 0: # Spending
expectation = min(budget - fact, 0)
budget = -budget
fact = -fact
else: # budget and fact have different signs, error
expectation = 'Error'
budget = abs(budget)
fact = abs(fact)
yield BudgetBar(category, fact, budget, str(expectation))
def fetch_budget_prediction(self, month, year, transaction_date):
"""
Fetches predictions for a given period.
"""
min_period, max_period = _from_date_to_period(month, year)
min_period = min(transaction_date, min_period)
m = min_period.month
for y in range(min_period.year, max_period.year+1):
while True:
budget_records = self.fetch_records(m, y)
for record in budget_records:
for prediction in self._predict(record, transaction_date):
if prediction:
yield prediction
if (m, y) == (max_period.month, max_period.year) or m == 12:
m = 1
break
else:
# Move to the next month
m += 1
# Categories #
def fetch_parents(self):
parents = self.storage.select_parents()
return [Category(*p) for p in parents]
def fetch_subcategories_for_parent(self, category):
subs = self.storage.select_subcategories(category.name)
subs = [Category(*c) for c in subs]
return subs
def fetch_subcategories(self, full=True):
"""
Builds dictionary of subcategories.
:return: dic
"""
subs = self.storage.select_all_subcategories()
categories = dict(((rowid, Category(name, parent, rowid))
for name, parent, rowid in subs))
# Add empty category
if full:
categories[self.NO_CATEGORY['ID']] =\
Category(self.NO_CATEGORY['NAME'],
self.NO_CATEGORY['PARENT'],
self.NO_CATEGORY['ID'])
return categories
def fetch_subcategory(self, category_id):
if category_id == self.NO_CATEGORY['ID']:
return Category(self.NO_CATEGORY['NAME'],
self.NO_CATEGORY['PARENT'],
self.NO_CATEGORY['ID'])
else:
name, parent = self.storage.select_subcategory(category_id)
return Category(name, parent, category_id)
def delete_category(self, category):
if category.parent is not None:
return self.storage.delete_subcategory(category.id)
else:
return self.storage.delete_category(category.name)
def add_category(self, name, parent):
if parent == '':
return self.storage.add_category(name)
else:
return self.storage.add_subcategory(name, parent)
# Transactions #
def _build_transaction(self, query_result):
"""
Builds Transaction object given query result and categories dictionary.
"""
date, amount, info, category_id, rowid = query_result
category = self.fetch_subcategory(category_id)
category_name = category.parent + '::' + category.name
amount = from_cents(amount)
date = _from_str_to_date(date)
return Transaction(date, amount, info, category_name,
rowid, category_id)
def fetch_transactions_for_month(self, month, year, category):
f_day, l_day = _from_date_to_period(month, year)
results = self.storage.select_budget_transactions_for_category(
f_day, l_day, category.id)
transactions = [self._build_transaction(t) for t in results]
return transactions
def fetch_summary_for_month(self, month, year, category: Category):
f_day, l_day = _from_date_to_period(month, year)
total, *_ = self.storage.select_summary(f_day, l_day, category.id)
return from_cents(total or 0)
def fetch_transactions_for_period(self, month, year):
f_day, l_day = _from_date_to_period(month, year)
transactions = [self._build_transaction(t)
for t in self.storage.select_transactions_for_period(
f_day, l_day)]
return transactions
def fetch_transactions(self, account):
transactions = [self._build_transaction(t)
for t in self.storage.select_transactions(account.id)]
return transactions
def fetch_balance_to_date(self, month, year):
# Get the last transaction date
last_transaction, *_ = self.storage.select_last_date()
# Fix if transaction roll is empty
last_transaction = last_transaction or str(datetime.date.today())
last_transaction = _from_str_to_date(last_transaction)
last_transaction += relativedelta(days=1)
# Get the first day of report period
if month in (0, 1):
last_day = datetime.date(year, 1, 1)
else:
last_day = datetime.date(year, month, 1)
last_day = min(last_day, last_transaction)
balance, *_ = self.storage.select_balance_till(last_day)
return last_day, from_cents(balance or 0)
def delete_transaction(self, transaction, account):
self.storage.delete_transaction(transaction.id, account.id)
def add_transaction(self, date, amount, info, account, category):
tr = self.storage.add_transaction(
date, amount, info, account.id, category.id)
transaction = self._build_transaction(tr)
return transaction
def update_transaction(self, transaction, account, category):
self.storage.update_transaction(
transaction.id, account.id, transaction.date, transaction.amount,
transaction.info, category.id)
# Predictors
def _predict(self, record: Record, transaction_date):
funcs = {
'Monthly': self._monthly_predictor,
'Point': self._point_predictor,
'Daily': self._daily_predictor,
'Weekly': self._weekly_predictor
}
return funcs[record.type](record, transaction_date)
def _monthly_predictor(self, record, transaction_date):
category = self.fetch_subcategory(record.category_id)
_, lastday = monthrange(record.year, record.month)
last_day = datetime.date(record.year, record.month, lastday)
budget = record.amount
fact = self.fetch_summary_for_month(record.month, record.year, category)
if budget == 0 or transaction_date >= last_day:
yield None
else:
if budget > 0 and fact >= 0: # Income
expectation = max(budget - fact, 0)
elif budget < 0 and fact <= 0: # Spending
expectation = min(budget - fact, 0)
else: # budget and fact have different signs, error
# Stick to the plan
expectation = budget
yield Prediction(last_day, expectation, category)
def _point_predictor(self, record, transaction_date):
category = self.fetch_subcategory(record.category_id)
budget_date = datetime.date(record.year, record.month, record.day)
if transaction_date >= budget_date:
# Budget record has expired
yield None
else:
yield Prediction(budget_date, record.amount, category)
def _daily_predictor(self, record, transaction_date):
category = self.fetch_subcategory(record.category_id)
_, last_day = monthrange(record.year, record.month)
budget = record.amount / last_day
for i in range(1, last_day+1):
budget_date = datetime.date(record.year, record.month, i)
if transaction_date >= budget_date:
yield None
else:
yield Prediction(budget_date, budget, category)
def _weekly_predictor(self, record, transaction_date):
category = self.fetch_subcategory(record.category_id)
day = record.day - 1 # Natural day of week order to array index
# Calculate the dates of budget spending
budget_days = [datetime.date(record.year, record.month, week[day])
for week in monthcalendar(record.year, record.month)
if week[day] != 0]
budget = record.amount / len(budget_days)
for day in budget_days:
if transaction_date >= day:
yield None
else:
yield Prediction(day, budget, category)
