def get_spend_ratio(self, start_date, end_date):
sql = """
SELECT SUM(
CASE
WHEN negate THEN -amount
ELSE amount
END) AS total
FROM item
INNER JOIN "transaction" ON "transaction".transaction_id = item.transaction_id
INNER JOIN account_assignment ON account_assignment.transaction_id = item.transaction_id
WHERE "transaction".date >= {}
AND "transaction".date <= {}
GROUP BY item.transaction_id
"""
c = self.get_connection().cursor()
totals = c.execute(sql.format(start_date, end_date))
in_sum, out_sum = 0, 0
for total, in totals:
if total < 0:
out_sum += total
else:
in_sum += total
return {"income": in_sum, "expenses": -out_sum}
def create_account(self, name, description):
"""Creates a new account.
Args:
name (string): The account name. Must be unique.
description (string): A description of the account.
Returns:
A dictionary containing the account details.
"""
sql = """
INSERT INTO "account" ("name", "description")
VALUES ("{}", "{}")
"""
connection = self.get_connection()
c = connection.cursor()
c.execute(sql.format(name, description))
connection.commit()
account_id = c.lastrowid
return {
"account_id": account_id,
"name": name,
"description": description
}
def get_account_balances(self, date=None):
"""Returns the balances of all accounts at a specified date.
Args:
date (int): Unix timestamp of date on which to retrieve the
balances. If None, returns the most recent balance.
Returns:
An array of dictionaries containing an account ID and balance.
"""
sql = """
SELECT account.account_id, SUM(
CASE
WHEN negate THEN -amount
ELSE amount
END) AS balance
FROM account
INNER JOIN account_assignment ON account_assignment.account_id = account.account_id
INNER JOIN "transaction" ON "transaction".transaction_id = account_assignment.transaction_id
INNER JOIN item ON item.transaction_id = "transaction".transaction_id
{}
GROUP BY account.account_id
"""
c = self.get_connection().cursor()
date_filter = ""
if date is not None:
date_filter = 'WHERE "transaction".date <= {}'.format(date)
balances = c.execute(sql.format(date_filter))
return [{"account_id": i, "balance": b} for i, b in balances]
def get_tag(self, tag_id):
"""Returns the details of a single tag.
Returns:
A dictionary containing the tag details.
"""
sql = """
SELECT "name", "description" FROM "tag" WHERE "tag_id" = {}
"""
c = self.get_connection().cursor()
tag = list(c.execute(sql.format(tag_id)))
if len(tag) == 0:
raise Exception("No tag has ID {}".format(tag_id))
name, description = tag[0]
return {"tag_id": tag_id, "name": name, "description": description}
def get_tag_summary(self, start_date, end_date):
sql = """
SELECT tag.tag_id, name, SUM(
CASE
WHEN negate THEN -amount
ELSE amount
END) AS value
FROM tag
INNER JOIN tag_assignment ON tag_assignment.tag_id = tag.tag_id
INNER JOIN item ON item.item_id = tag_assignment.item_id
INNER JOIN "transaction" ON "transaction".transaction_id = item.transaction_id
INNER JOIN account_assignment ON account_assignment.transaction_id = item.transaction_id
WHERE "transaction".date >= {}
AND "transaction".date <= {}
GROUP BY tag.tag_id
ORDER BY ABS(value) DESC
"""
c = self.get_connection().cursor()
tags = c.execute(sql.format(start_date, end_date))
return [{"tag_id": i, "name": n, "value": v} for i, n, v in tags]
def get_account(self, account_id):
"""Returns the details of a single account.
Returns:
A dictionary containing the account details.
"""
sql = """
SELECT "name", "description" FROM "account" WHERE "account_id" = {}
"""
c = self.get_connection().cursor()
account = list(c.execute(sql.format(account_id)))
if len(account) == 0:
raise Exception("No account has ID {}".format(account_id))
name, description = account[0]
return {
"account_id": account_id,
"name": name,
"description": description
}
def get_transactions(self, accounts, start_date=None, end_date=None):
"""Returns the transactions of a list of accounts.
Args:
accounts (list of int):
The account IDs for which to retrieve transactions.
Returns:
Dictionary containing transactions.
"""
if len(accounts) == 0:
raise Exception("No account specified")
c = self.get_connection().cursor()
# format accounts to "id_1,id_2, ... ,id_n"
account_list = "".join(str(a) + "," for a in accounts)[:-1]
# get all items along with tags
# NOTE: make sure the result is ordered by transaction id, item id, and
# then account id, or the dictionary structure won't work
sql = """
SELECT item.transaction_id, item.item_id, account_id, date, negate, amount, tag_id FROM item
INNER JOIN "transaction" ON item.transaction_id = "transaction".transaction_id
INNER JOIN account_assignment ON account_assignment.transaction_id = "transaction".transaction_id
LEFT OUTER JOIN tag_assignment ON tag_assignment.item_id = item.item_id
WHERE account_assignment.account_id IN ({})
{}
ORDER BY "transaction".date DESC, item.transaction_id DESC, item.item_id, account_id
"""
date_filter = ""
if start_date is not None:
date_filter = date_filter + " AND date >= {}".format(start_date)
if end_date is not None:
date_filter = date_filter + " AND date <= {}".format(end_date)
items = c.execute(sql.format(account_list, date_filter))
# squeeze the results down to a structured dictionary
transactions = []
cur_transaction = None
cur_item = None
prev_transaction_id = -1
prev_item_id = -1
prev_account_id = -1
for transaction_id, item_id, account_id, date, negate, amount, tag_id in items:
if transaction_id != prev_transaction_id:
cur_transaction = {
"transaction_id": transaction_id,
"date": date,
"negate": [negate],
"accounts": [account_id],
"items": [],
}
transactions.append(cur_transaction)
prev_transaction_id = transaction_id
prev_account_id = account_id
if account_id != prev_account_id:
# all items are already added, just add the new account
cur_transaction["accounts"].append(account_id)
cur_transaction["negate"].append(negate)
else:
if item_id != prev_item_id:
cur_item = {"amount": amount, "tags": []}
cur_transaction["items"].append(cur_item)
prev_item_id = item_id
if tag_id is not None:
cur_item["tags"].append(tag_id)
return transactions