def parse_csv(filename):
"""Parses all csv-files into the list."""
export = AfasExport()
logger.info("Reading AFAS export from %s", filename)
try:
with open(filename, encoding="ISO-8859-1") as csv_file:
reader = csv.DictReader(csv_file, delimiter=';')
for row in reader:
try:
account_id = row["accountnumber"]
counter_account_id = row["counter_account_number"]
date = datetime.datetime.strptime(row["date"],
"%Y-%m-%d").date()
try:
date2 = datetime.datetime.strptime(
row["date2"], "%Y-%m-%d").date()
except ValueError:
date2 = date
amount = float(row["amount"])
if row["debit_credit"] == 'D':
amount = -amount
name = " ".join(row["name"].split())
description = " ".join(row["description"].split())
cat = row["category"]
sub_category = row["subcategory"]
try:
category = category_repository(
).get_category_by_qualified_name("Uitgaven::" + cat +
"::" + sub_category)
except:
try:
category = category_repository(
).get_category_by_qualified_name(cat + "::" +
sub_category)
except:
logger.warning("Failed to get category for %s::%s",
cat, sub_category)
raise
if category:
transaction = AfasTransaction(account_id, amount, date,
date2, name, description,
counter_account_id,
category)
export.add_transaction(transaction)
except:
logger.error("Failed to read row %s", row)
raise
except FileNotFoundError:
logger.warning(
"No AFAS.csv found in data-directory. Will not use AFAS historical data to categorize transactions."
)
return export
def get_combined_category_data_for_period(parent_id=None):
try:
start = request.args.get('start', None)
start_date = datetime.fromtimestamp(int(float(start) / 1000)).date()
except ValueError:
start_date = application.services.get_date_of_first_transaction()
try:
end = request.args.get('end', None)
end_date = datetime.fromtimestamp(int(float(end) / 1000)).date()
except ValueError:
end_date = application.services.get_date_of_last_transaction()
logger.info("Returning transaction-date from %s to %s", start_date,
end_date)
parent_category = category_repository().get_category(parent_id) or None
if parent_category:
categories = parent_category.children or [parent_category]
else:
categories = [
c for c in category_repository().get_categories() if not c.parent
]
def get_category_amount(cat):
cat_data = {
'name':
cat.name,
'value':
abs(
float(
sum([
float(t.amount) for t in
application.services.get_transactions_for_category(
start_date, end_date, cat)
])))
}
if cat.children:
cat_data['children'] = []
for child_cat in cat.children:
cat_data['children'].append(get_category_amount(child_cat))
return cat_data
data = []
for category in categories:
data.append(get_category_amount(category))
response = app.response_class(response=dumps(data, cls=CategoryEncoder),
mimetype='application/json')
return response
def import_categories(filename):
with open(filename) as csv_file:
reader = csv.DictReader(csv_file)
for row in reader:
category_id, qualified_name = row["category_id"], row[
"qualified_name"]
category = category_repository().get_category_by_qualified_name(
qualified_name)
if not category:
category = category_factory(
).create_category_from_qualified_name(qualified_name)
category.id = category_id
category_repository().save_category(category)
else:
logger.info("Skipping import of existing category %s",
category)
def init_cache(self):
logger.info("Initializing cache...")
sql = """SELECT * FROM accounts"""
account_rows = self.db.query(sql)
for row in account_rows:
account = Account(row["id"], row["name"], row["bank"])
self.save_account(account)
logger.debug("Fetching transactions for account %s", account)
for trow in self.db.query(
"SELECT * FROM transactions WHERE account = ?",
(account.id, )):
category = category_repository().get_category(trow["category"])
date_str = trow["date"].split(" ")[
0] # strip trailing characters (if any)
date = datetime.strptime(date_str, '%Y-%m-%d').date()
transaction = Transaction(trow["id"], account, trow["serial"],
date, trow["amount"], trow["name"],
trow["description"],
trow["counter_account"],
trow["balance_after"],
trow["internal"], category)
self.save_transaction(transaction)
account.add_transaction(transaction)
logger.info("Cache initialized...")
def get_combined_data():
mode = request.args.get('mode', 'monthly')
category_id = request.args.get('category', None)
category = category_repository().get_category(category_id)
date_list = application.services.get_transaction_date_range(mode=mode)
balance_list = [
application.services.get_combined_balance_at(date)
for date in date_list
]
income, expenses, profit, loss = application.services.get_income_expenses_profit_loss(
date_list, mode=mode)
category_amounts = [
application.services.get_combined_amount_for_category(
category, d, mode) for d in date_list
]
response = app.response_class(response=dumps([
Combined(date, balance, income, expenses, profit, loss,
category_amount).__dict__
for (date, balance, income, expenses, profit, loss,
category_amount) in zip(date_list, balance_list, income, expenses,
profit, loss, category_amounts)
],
cls=CategoryEncoder),
mimetype='application/json')
return response
def get_categories():
categories = [Category(0, "None", None).__dict__] + [
Category(cat.id, cat.qualified_name, cat.parent).__dict__
for cat in category_repository().get_categories()
]
response = app.response_class(dumps(categories, cls=CategoryEncoder),
mimetype='application/json')
return response
def import_transactions(filename):
if list(
itertools.chain.from_iterable([
a.get_transactions()
for a in account_repository().get_accounts()
])):
logger.error(
"Native import of transactions is only allowed on an empty transactions table!"
)
return
with open(filename) as csv_file:
reader = csv.DictReader(csv_file)
for row in reader:
transaction_id = row["transaction_id"]
amount = row["amount"]
date = datetime.datetime.strptime(row["date"], '%Y-%m-%d').date()
name = row["name"]
description = row["description"]
balance_after = row["balance_after"]
serial = row["serial"]
counter_account = row["counter_account"]
account_id = row["account"]
category_qualified_name = row["category"]
transaction = account_repository().get_transaction(transaction_id)
account = account_repository().get_account(account_id)
if not transaction:
transaction = account_factory().create_transaction(
account, date, amount, name, description, serial,
counter_account, balance_after)
transaction.id = transaction_id
if category_qualified_name:
category = category_repository(
).get_category_by_qualified_name(category_qualified_name)
if not category:
category = category_factory(
).create_category_from_qualified_name(
category_qualified_name)
category_repository().save_category(category)
transaction.update_category(category)
account_repository().save_transaction(transaction)
def __init__(self):
self.mapping = [
(category_repository().get_category_by_qualified_name(
"Uitgaven::Overige uitgaven::Overboekingen"),
category_repository().get_category_by_qualified_name(
"Overboekingen")),
(category_repository().get_category_by_qualified_name(
"Uitgaven::Telecom::Televisie"),
category_repository().get_category_by_qualified_name(
"Uitgaven::Telecom::Internet/TV")),
(category_repository().get_category_by_qualified_name(
"Uitgaven::Telecom::Internet"),
category_repository().get_category_by_qualified_name(
"Uitgaven::Telecom::Internet/TV")),
(category_repository().get_category_by_qualified_name(
"Uitgaven::Overige uitgaven::Abonnementen"),
category_repository().get_category_by_qualified_name(
"Uitgaven::Vrije tijd::Abonnementen")),
(category_repository().get_category_by_qualified_name(
"Inkomsten::Belastingtoeslagen"),
category_repository().get_category_by_qualified_name(
"Inkomsten::Belastingteruggaaf")),
]
def get_category_data(category_id):
mode = request.args.get('mode', 'monthly')
category = category_repository().get_category(category_id)
date_list = application.services.get_transaction_date_range(day_nr=1,
mode=mode)
monthly_amounts = [
application.services.get_combined_amount_for_category(
category, month, mode) for month in date_list
]
response = app.response_class(response=dumps([
Balance(date, amount).__dict__
for (date, amount) in zip(date_list, monthly_amounts)
],
cls=CategoryEncoder),
mimetype='application/json')
return response
def __init__(self, config):
self.names = {}
mapping_filename = config.get_file("mapping.csv")
logger.info("Reading mapping from %s", mapping_filename)
with open(mapping_filename, encoding="ISO-8859-1") as csv_file:
reader = csv.DictReader(csv_file, delimiter=',')
for row in reader:
name = row["Name"]
description = row["Description"]
cat_name = row["Category"]
try:
category = category_repository(
).get_category_by_qualified_name(cat_name)
except:
logger.warning("Failed to get category for %s", cat_name)
raise
self.names[(name, description)] = category
def set_category(transaction_id):
category_id = request.json.get('categoryId')
category = category_repository().get_category(category_id)
transaction = account_repository().get_transaction(transaction_id)
logger.info('Setting category of transaction id %s to %s', transaction,
category)
transaction.update_category(category)
account_repository().save_transaction(transaction)
# TODO: Fix threading issue!!! account_repository().save_transaction(transaction)
return app.response_class(response=dumps(Transaction(
transaction.id, transaction.date, transaction.account.name,
transaction.amount,
transaction.name, transaction.category and Category(
transaction.category.id, transaction.category.qualified_name,
transaction.category.parent).__dict__ or None,
transaction.description, transaction.counter_account,
transaction.internal).__dict__,
cls=CategoryEncoder),
mimetype='application/json')
def __init__(self):
self.DEFAULT_SCORE = 100
self.internal_transactions_detector = MyInternalTransactionDetector()
self.internal_transactions_category = category_repository(
).get_category_by_qualified_name("Overboekingen")
