def get_or_suggest_tags():
opt = request.args
query = v_dict_entry(opt, 'query', vv=strv(min_len=1), optional=True)
if query is not None:
return {
"suggestions":
fetch_all_as_dict(
c=get_db().cursor(),
tables=(Table('tag'), ),
cols=(
Column('id'),
Column('name', 'label'),
),
where=Cond('name LIKE ?', f'{query}%'),
),
}
return {
"tags":
fetch_all_as_dict(
c=get_db().cursor(),
tables=(Table('tag'), ),
cols=(
Column('id'),
Column('name'),
Column('comment'),
),
where=Cond('TRUE'),
)
}
def get_dataset_sources():
return {
"sources":
fetch_all_as_dict(
c=get_db().cursor(),
tables=(Table('dataset_source'), ),
cols=(
Column('id'),
Column('name'),
),
where=Cond('TRUE'),
),
}
def get_or_suggest_categories():
opt = request.args
query = v_dict_entry(opt, 'query', vv=strv(min_len=1), optional=True)
if query is not None:
return {
"suggestions":
fetch_all_as_dict(
c=get_db().cursor(),
tables=(Table('category'), ),
cols=(
Column('id'),
Column('name', 'label'),
),
where=Cond('name LIKE ?', f'{query}%'),
),
}
return {
"categories":
fetch_all_as_dict(
c=get_db().cursor(),
tables=(
Table('category', 'node'),
Table('category', 'parent'),
),
cols=(
Column('node.id', 'id'),
Column('node.name', 'name'),
Column('node.comment', 'comment'),
Column('COUNT(parent.id) - 1', 'depth'),
),
where=Cond(
'node.set_start BETWEEN parent.set_start AND parent.set_end'),
group_by='node.id',
order_by='node.set_start',
)
}
def get_transaction_parts(**args):
transaction = v_dict_entry(args, 'transaction', vv=intv(min_val=0, parse_from_str=True))
parts = fetch_all_as_dict(
c=get_db().cursor(),
tables=(
Table('txn_part'),
),
cols=(
Column('txn_part.id', 'id'),
Column('txn_part.comment', 'comment'),
Column('txn_part.amount', 'amount'),
Column('category.id', 'category_id'),
Column('category.name', 'category_name'),
Column("GROUP_CONCAT(tag.id, '\1')", 'tag_ids'),
Column("GROUP_CONCAT(tag.name, '\1')", 'tag_names'),
),
joins=(
Join(JoinMethod.left, Table('category'), 'txn_part.category = category.id'),
Join(JoinMethod.left, Table('txn_part_tag'), 'txn_part.id = txn_part_tag.txn_part'),
Join(JoinMethod.left, Table('tag'), 'txn_part_tag.tag = tag.id'),
),
where=Cond('txn = ?', transaction),
group_by="txn_part.id",
)
for t in parts:
category_id = t.pop('category_id')
category_name = t.pop('category_name')
t['category'] = None if category_id is None else {
"id": category_id,
"name": category_name,
}
raw_tag_ids = t.pop('tag_ids')
raw_tag_names = t.pop('tag_names')
if not raw_tag_ids:
t['tags'] = []
else:
t['tags'] = [
{"id": tid, "name": tname}
for tid, tname in zip(
map(int, raw_tag_ids.split('\1')),
raw_tag_names.split('\1'),
)
]
return {
"parts": parts,
}
def update_transaction_part(**args):
part = v_dict_entry(args, 'part', vv=intv(min_val=0, parse_from_str=True))
opt = require_json_object_body()
comment = v_dict_entry(opt, 'comment', optional=True, vv=strv())
amount = v_dict_entry(opt, 'amount', optional=True, vv=intv())
category = v_dict_entry(opt, 'category', vv=intv(min_val=0), nullable=True, optional=True)
require_changed_row(patch_row(
c=get_db().cursor(),
table='txn_part',
values=(
('comment', comment),
('amount', amount),
('category', category),
),
cond=Cond('id = ?', part),
))
return {}
def update_transaction(**args):
transaction = v_dict_entry(args,
'transaction',
vv=intv(min_val=0, parse_from_str=True))
opt = require_json_object_body()
comment = v_dict_entry(opt, 'comment', optional=True, vv=strv())
timestamp = v_dict_entry(opt, 'timestamp', optional=True, vv=timestampv())
description = v_dict_entry(opt, 'description', optional=True, vv=strv())
amount = v_dict_entry(opt, 'amount', optional=True, vv=intv())
require_changed_row(
patch_row(
c=get_db().cursor(),
table='txn',
values=(
('malformed', False),
('comment', comment),
('timestamp', timestamp),
('description', description),
('amount', amount),
),
cond=Cond('id = ?', transaction),
))
return {}
def get_transactions():
opt = request.args
dt_from = v_dict_entry(opt,
'from',
optional=True,
vv=timestampv(parse_from_str=True))
dt_to = v_dict_entry(opt,
'to',
optional=True,
vv=timestampv(parse_from_str=True))
dataset = v_dict_entry(opt,
'dataset',
vv=intv(min_val=0, parse_from_str=True),
optional=True)
categories = v_list(opt.getlist('category'),
'categories',
vv=intv(min_val=0, parse_from_str=True))
tags = v_list(opt.getlist('tag'),
'tags',
vv=intv(min_val=0, parse_from_str=True))
cond = Cond('TRUE')
if dt_from is not None:
cond += Cond("txn.timestamp >= ?", dt_from)
if dt_to is not None:
cond += Cond("txn.timestamp <= ?", dt_to)
if dataset is not None:
cond += Cond("txn.dataset = ?", dataset)
if categories:
cond += Cond(
f"txn_part.category IN ({','.join(map(str, categories))})")
if tags:
cond += Cond(
f"txn_part.id IN (SELECT txn_part FROM txn_part_tag WHERE tag IN ({','.join(map(str, tags))}))"
)
return {
"transactions":
fetch_transactions(get_db().cursor(), cond, group_by="txn_part.txn"),
}
def get_transactions_analysis():
opt = request.args
dt_from = v_dict_entry(opt,
'from',
optional=True,
vv=timestampv(parse_from_str=True))
dt_to = v_dict_entry(opt,
'to',
optional=True,
vv=timestampv(parse_from_str=True))
split_by = v_dict_entry(opt,
'split_by',
vv=enumv(options=['category', 'none']))
time_unit = v_dict_entry(
opt, 'time_unit', vv=enumv(options=['year', 'month', 'day', 'none']))
categories = v_list(opt.getlist('category'),
'categories',
vv=intv(min_val=0, parse_from_str=True))
tags = v_list(opt.getlist('tag'),
'tags',
vv=intv(min_val=0, parse_from_str=True))
columns = [
Column('SUM(txn_part.amount)', 'combined_amount'),
]
group_by = []
if split_by == 'category':
columns.append(Column('category.name', 'category_name'))
group_by.append('category.id')
if time_unit != 'none':
if time_unit == 'year':
time_unit_fmt = '%Y'
elif time_unit == 'month':
time_unit_fmt = '%Y-%m'
elif time_unit == 'day':
time_unit_fmt = '%Y-%m-%d'
else:
assert False
columns.append(
Column(f"strftime('{time_unit_fmt}', txn.timestamp)", 'time_unit'))
group_by.append(f"strftime('{time_unit_fmt}', txn.timestamp)")
cond = Cond('txn_part.amount > 0')
if dt_from is not None:
cond += Cond("txn.timestamp >= ?", dt_from)
if dt_to is not None:
cond += Cond("txn.timestamp <= ?", dt_to)
if categories:
cond += Cond(
f"txn_part.category IN ({','.join(map(str, categories))})")
if tags:
cond += Cond(
f"txn_part.id IN (SELECT txn_part FROM txn_part_tag WHERE tag IN ({','.join(map(str, tags))}))"
)
return {
"analysis":
fetch_all_as_dict(
c=get_db().cursor(),
tables=(Table('txn_part'), ),
cols=columns,
joins=(
Join(JoinMethod.left, Table('txn'), 'txn_part.txn = txn.id'),
Join(JoinMethod.left, Table('category'),
'txn_part.category = category.id'),
),
where=cond,
group_by=','.join(group_by),
),
}
def get_dataset(**opt):
dataset = v_dict_entry(opt, 'dataset', vv=intv(min_val=0, parse_from_str=True))
return {
"datasets": fetch_datasets(get_db().cursor(), Cond('dataset.id = ?', dataset))
}
def get_datasets():
return {
"datasets": fetch_datasets(get_db().cursor(), Cond('TRUE'))
}