def create_tag():
opt = require_json_object_body()
name = v_dict_entry(opt, 'name', vv=strv())
comment = v_dict_entry(opt, 'comment', vv=strv())
c = get_db().cursor()
c.execute("INSERT INTO tag (name, comment) VALUES (?, ?)", (name, comment))
return {
"id": c.lastrowid,
}
def create_dataset_source():
opt = require_json_object_body()
name = v_dict_entry(opt, 'name', vv=strv(min_len=1))
comment = v_dict_entry(opt, 'comment', optional='', vv=strv())
c = get_db().cursor()
# TODO Handle errors
c.execute("INSERT INTO dataset_source (name, comment) VALUES (?, ?)",
(name, comment))
return {
"id": c.lastrowid,
}
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 create_category():
opt = require_json_object_body()
name = v_dict_entry(opt, 'name', vv=strv())
target = v_dict_entry(opt, 'target', vv=intv(min_val=0), optional=True)
mode = v_dict_entry(opt, 'mode', vv=strv())
# TODO Lock table
c = get_db().cursor()
if target is None:
if mode != 'root':
raise BadRequest('Target is missing')
if c.execute("SELECT COUNT(*) FROM category").fetchone()[0] != 0:
raise BadRequest('Root already exists')
target_start = -1
mode = 'first'
else:
c.execute("SELECT set_start, set_end FROM category WHERE id = ?",
(target, ))
target_start, target_end = require_one(c.fetchall())
if mode == 'after':
shift_greater_than = target_end
elif mode == 'before':
shift_greater_than = target_start - 1
elif mode == 'first':
shift_greater_than = target_start
else:
raise BadRequest('Invalid mode')
c.execute(
"UPDATE category SET set_start = set_start + 2 WHERE set_start > ?",
(shift_greater_than, ))
c.execute("UPDATE category SET set_end = set_end + 2 WHERE set_end > ?",
(shift_greater_than, ))
# TODO Handle unique
c.execute(
"INSERT INTO category (name, comment, set_start, set_end) VALUES (?, '', ?, ?)",
(name, shift_greater_than + 1, shift_greater_than + 2))
category_id = c.lastrowid
# TODO Unlock table
return {
"id": category_id,
}
def create_dataset(**args):
source = v_dict_entry(args, 'source', vv=intv(min_val=0, parse_from_str=True))
opt = request.args
timestamp_column = v_dict_entry(opt, 'timestamp_column', vv=intv(parse_from_str=True))
timestamp_format = v_dict_entry(opt, 'timestamp_format', vv=strv())
description_column = v_dict_entry(opt, 'description_column', vv=intv(parse_from_str=True))
amount_column = v_dict_entry(opt, 'amount_column', vv=intv(parse_from_str=True))
c = get_db().cursor()
# TODO Handle errors
c.execute("INSERT INTO dataset (source, comment, created) VALUES (?, '', DATETIME('now'))", (source,))
dataset_id = c.lastrowid
for row in csv.reader(StringIO(request.get_data(as_text=True))):
raw = json.dumps(row)
malformed = False
try:
# TODO Convert to UTC
timestamp = datetime.strptime(row[timestamp_column], timestamp_format)
except (IndexError, ValueError):
# TODO Convert to UTC
timestamp = datetime.now()
malformed = True
try:
description = row[description_column]
except IndexError:
description = ''
malformed = True
try:
amount = -parse_money_amount(row[amount_column])
except (IndexError, ValueError):
amount = 0
malformed = True
c.execute(
"INSERT INTO txn (dataset, raw, comment, malformed, timestamp, description, amount) VALUES (?, ?, '', ?, ?, ?, ?)",
(dataset_id, raw, malformed, timestamp, description, amount)
)
transaction_id = c.lastrowid
if not malformed:
c.execute(
"INSERT INTO txn_part (txn, comment, amount, category) VALUES (?, '', ?, NULL)",
(transaction_id, amount)
)
return {
"id": dataset_id,
}
def create_transaction_part(**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='', vv=strv())
amount = v_dict_entry(opt, 'amount', vv=intv(min_val=0))
category = v_dict_entry(opt, 'category', optional=True, vv=intv())
c = get_db().cursor()
c.execute(
"INSERT INTO txn_part (txn, comment, amount, category) VALUES (?, ?, ?, ?)",
(transaction, comment, amount, category)
)
return {
"id": c.lastrowid,
}
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 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 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 set_name():
opt = require_json_object_body()
name = v_dict_entry(opt, 'name', vv=strv())
get_db().cursor().execute(
"UPDATE setting SET value = ? WHERE name = 'name'", (name, ))
return {}