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')) }