diff --git a/conservancy_beancount/reports/query.py b/conservancy_beancount/reports/query.py index dce4779d8cbb88cb44bb3f70e8c0d5cac315f9cd..d6b006223e64eb846c3223f4cb8b4db8860208a8 100644 --- a/conservancy_beancount/reports/query.py +++ b/conservancy_beancount/reports/query.py @@ -17,6 +17,7 @@ import sys from typing import ( cast, AbstractSet, + Any, Callable, Dict, Iterable, @@ -38,6 +39,7 @@ from ..beancount_types import ( from decimal import Decimal from pathlib import Path +from beancount.core.amount import _Amount as BeancountAmount import beancount.query.numberify as bc_query_numberify import beancount.query.query_compile as bc_query_compile @@ -46,6 +48,7 @@ import beancount.query.query_execute as bc_query_execute import beancount.query.query_parser as bc_query_parser import beancount.query.query_render as bc_query_render import beancount.query.shell as bc_query_shell +import odf.table # type:ignore[import] from . import core from . import rewrite @@ -53,6 +56,7 @@ from .. import books from .. import cliutil from .. import config as configmod from .. import data +from .. import rtutil BUILTIN_FIELDS: AbstractSet[str] = frozenset(itertools.chain( bc_query_env.TargetsEnvironment.columns, # type:ignore[has-type] @@ -62,6 +66,7 @@ PROGNAME = 'query-report' QUERY_PARSER = bc_query_parser.Parser() logger = logging.getLogger('conservancy_beancount.reports.query') +CellFunc = Callable[[Any], odf.table.TableCell] RowTypes = Sequence[Tuple[str, Type]] Rows = Sequence[NamedTuple] @@ -102,7 +107,88 @@ class BooksLoader: return result +class QueryODS(core.BaseODS[NamedTuple, None]): + def is_empty(self) -> bool: + return not self.sheet.childNodes + + def section_key(self, row: NamedTuple) -> None: + return None + + def _cell_type(self, row_type: Type) -> CellFunc: + if issubclass(row_type, BeancountAmount): + return self.currency_cell + elif issubclass(row_type, (int, float, Decimal)): + return self.float_cell + elif issubclass(row_type, datetime.date): + return self.date_cell + elif issubclass(row_type, str): + return self.string_cell + else: + return self._generic_cell + + def _generic_cell(self, value: Any) -> odf.table.TableCell: + return self.string_cell('' if value is None else str(value)) + + def _link_cell(self, value: MetaValue) -> odf.table.TableCell: + if isinstance(value, str): + return self.meta_links_cell(value.split()) + else: + return self._generic_cell(value) + + def _metadata_cell(self, value: MetaValue) -> odf.table.TableCell: + return self._cell_type(type(value))(value) + + def _cell_types(self, row_types: RowTypes) -> Iterator[CellFunc]: + for name, row_type in row_types: + if row_type is object: + if name.replace('_', '-') in data.LINK_METADATA: + yield self._link_cell + else: + yield self._metadata_cell + else: + yield self._cell_type(row_type) + + def write_query(self, row_types: RowTypes, rows: Rows) -> None: + if self.is_empty(): + self.sheet.setAttribute('name', "Query 1") + else: + self.use_sheet(f"Query {len(self.document.spreadsheet.childNodes) + 1}") + for name, row_type in row_types: + if row_type is object or issubclass(row_type, str): + col_width = 2.0 + elif issubclass(row_type, BeancountAmount): + col_width = 1.5 + else: + col_width = 1.0 + col_style = self.column_style(col_width) + self.sheet.addElement(odf.table.TableColumn(stylename=col_style)) + self.add_row(*( + self.string_cell(data.Metadata.human_name(name.replace('_', '-')), + stylename=self.style_bold) + for name, _ in row_types + )) + self.lock_first_row() + cell_funcs = list(self._cell_types(row_types)) + for row in rows: + self.add_row(*( + cell_func(value) + for cell_func, value in zip(cell_funcs, row) + )) + + class BQLShell(bc_query_shell.BQLShell): + def __init__( + self, + is_interactive: bool, + loadfun: Callable[[], books.LoadResult], + outfile: TextIO, + default_format: str='text', + do_numberify: bool=False, + rt_wrapper: Optional[rtutil.RT]=None, + ) -> None: + super().__init__(is_interactive, loadfun, outfile, default_format, do_numberify) + self.ods = QueryODS(rt_wrapper) + def on_Select(self, statement: str) -> None: output_format: str = self.vars['format'] try: @@ -144,6 +230,10 @@ class BQLShell(bc_query_shell.BQLShell): self.vars['expand'], ) + def _render_ods(self, row_types: RowTypes, rows: Rows) -> None: + self.ods.write_query(row_types, rows) + logger.info("results saved in sheet %s", self.ods.sheet.getAttribute('name')) + def _render_text(self, row_types: RowTypes, rows: Rows) -> None: with contextlib.ExitStack() as stack: if self.is_interactive: @@ -173,7 +263,7 @@ class ReportFormat(enum.Enum): TEXT = 'text' TXT = TEXT CSV = 'csv' - # ODS = 'ods' + ODS = 'ods' def _date_condition( @@ -202,10 +292,9 @@ def build_query( args.join = JoinOperator.AND select = [ 'date', - 'ANY_META("entity") as entity', - 'narration', - 'position', - 'COST(position)', + 'ANY_META("entity") AS entity', + 'narration AS description', + 'COST(position) AS booked_amount', *(f'ANY_META("{field}") AS {field.replace("-", "_")}' if field not in BUILTIN_FIELDS and re.fullmatch(r'[a-z][-_A-Za-z0-9]*', field) @@ -337,7 +426,8 @@ def main(arglist: Optional[Sequence[str]]=None, try: args.report_type = ReportFormat[args.output_file.suffix[1:].upper()] except (AttributeError, KeyError): - args.report_type = ReportFormat.TEXT # if is_interactive else ReportFormat.ODS + args.report_type = ReportFormat.TEXT if is_interactive else ReportFormat.ODS + load_func = BooksLoader( config.books_loader(), args.start_date, @@ -350,6 +440,7 @@ def main(arglist: Optional[Sequence[str]]=None, stdout, args.report_type.value, args.numberify, + config.rt_wrapper(), ) shell.on_Reload() if query is None: @@ -357,6 +448,18 @@ def main(arglist: Optional[Sequence[str]]=None, else: shell.onecmd(query) + if not shell.ods.is_empty(): + shell.ods.set_common_properties(config.books_repo()) + shell.ods.set_custom_property('BeanQuery', query or '') + if args.output_file is None: + out_dir_path = config.repository_path() or Path() + args.output_file = out_dir_path / 'QueryResults_{}.ods'.format( + datetime.datetime.now().isoformat(timespec='minutes'), + ) + logger.info("Writing spreadsheet to %s", args.output_file) + ods_file = cliutil.bytes_output(args.output_file, stdout) + shell.ods.save_file(ods_file) + return cliutil.ExitCode.OK entry_point = cliutil.make_entry_point(__name__, PROGNAME) diff --git a/tests/test_reports_query.py b/tests/test_reports_query.py index 9e359e62c725a1de456948b4c09df3253f44dd95..ed2ba648460009865f8dfca45541c8b375283cee 100644 --- a/tests/test_reports_query.py +++ b/tests/test_reports_query.py @@ -14,12 +14,16 @@ import io import itertools import re +import odf.table +import odf.text import pytest from . import testutil +from beancount.core import data as bc_data from conservancy_beancount.books import FiscalYear from conservancy_beancount.reports import query as qmod +from conservancy_beancount import rtutil from decimal import Decimal @@ -38,8 +42,12 @@ class MockRewriteRuleset: def fy(): return FiscalYear(3, 1) -def pipe_main(arglist, config): - stdout = io.StringIO() +@pytest.fixture(scope='module') +def rt(): + return rtutil.RT(testutil.RTClient()) + +def pipe_main(arglist, config, stdout_type=io.StringIO): + stdout = stdout_type() stderr = io.StringIO() returncode = qmod.main(arglist, stdout, stderr, config) return returncode, stdout, stderr @@ -258,3 +266,113 @@ def test_rewrite_query(end_index): actual = frozenset(line.rstrip('\n') for line in stdout) assert expected.issubset(actual) assert frozenset(accounts).difference(expected).isdisjoint(actual) + +def test_ods_amount_formatting(): + row_types = [('amount', bc_data.Amount)] + row_source = [(testutil.Amount(12),), (testutil.Amount(1480, 'JPY'),)] + ods = qmod.QueryODS() + ods.write_query(row_types, row_source) + actual = testutil.ODSCell.from_sheet(ods.document.spreadsheet.firstChild) + assert next(actual)[0].text == 'Amount' + assert next(actual)[0].text == '$12.00' + assert next(actual)[0].text == '¥1,480' + assert next(actual, None) is None + +def test_ods_datetime_formatting(): + row_types = [('date', datetime.date)] + row_source = [(testutil.PAST_DATE,), (testutil.FUTURE_DATE,)] + ods = qmod.QueryODS() + ods.write_query(row_types, row_source) + actual = testutil.ODSCell.from_sheet(ods.document.spreadsheet.firstChild) + assert next(actual)[0].text == 'Date' + assert next(actual)[0].text == testutil.PAST_DATE.isoformat() + assert next(actual)[0].text == testutil.FUTURE_DATE.isoformat() + assert next(actual, None) is None + +@pytest.mark.parametrize('meta_key,header_text', [ + ('check', 'Check'), + ('purchase-order', 'Purchase Order'), + ('rt-id', 'Ticket'), +]) +def test_ods_link_formatting(rt, meta_key, header_text): + row_types = [(meta_key.replace('-', '_'), object)] + row_source = [('rt:1/5',), ('rt:3 Checks/9.pdf',)] + ods = qmod.QueryODS(rt) + ods.write_query(row_types, row_source) + rows = iter(ods.document.spreadsheet.firstChild.getElementsByType(odf.table.TableRow)) + assert next(rows).text == header_text + actual = iter( + [link.text for link in row.getElementsByType(odf.text.A)] + for row in rows + ) + assert next(actual) == ['photo.jpg'] + assert next(actual) == ['rt:3', '9.pdf'] + assert next(actual, None) is None + +def test_ods_meta_formatting(): + row_types = [('metadata', object)] + row_source = [(testutil.Amount(14),), (None,), ('foo bar',)] + ods = qmod.QueryODS() + ods.write_query(row_types, row_source) + actual = testutil.ODSCell.from_sheet(ods.document.spreadsheet.firstChild) + assert next(actual)[0].text == 'Metadata' + assert next(actual)[0].text == '$14.00' + assert next(actual)[0].text == '' + assert next(actual)[0].text == 'foo bar' + assert next(actual, None) is None + +def test_ods_multicolumn_write(rt): + row_types = [('date', datetime.date), ('rt-id', object), ('desc', str)] + row_source = [ + (testutil.PAST_DATE, 'rt:1', 'aaa'), + (testutil.FY_START_DATE, 'rt:2', 'bbb'), + (testutil.FUTURE_DATE, 'rt:3', 'ccc'), + ] + ods = qmod.QueryODS(rt) + ods.write_query(row_types, row_source) + actual = iter( + cell.text + for row in testutil.ODSCell.from_sheet(ods.document.spreadsheet.firstChild) + for cell in row + ) + assert next(actual) == 'Date' + assert next(actual) == 'Ticket' + assert next(actual) == 'Desc' + assert next(actual) == testutil.PAST_DATE.isoformat() + assert next(actual) == 'rt:1' + assert next(actual) == 'aaa' + assert next(actual) == testutil.FY_START_DATE.isoformat() + assert next(actual) == 'rt:2' + assert next(actual) == 'bbb' + assert next(actual) == testutil.FUTURE_DATE.isoformat() + assert next(actual) == 'rt:3' + assert next(actual) == 'ccc' + assert next(actual, None) is None + +def test_ods_is_empty(): + ods = qmod.QueryODS() + assert ods.is_empty() + ods.write_query([], []) + assert not ods.is_empty() + +@pytest.mark.parametrize('fy,account,amt_prefix', [ + (2018, 'Assets', '($'), + (2019, 'Income', '$'), +]) +def test_ods_output(fy, account, amt_prefix): + books_path = testutil.test_path(f'books/books/{fy}.beancount') + config = testutil.TestConfig(books_path=books_path) + arglist = ['-O', '-', '-f', 'ods', f'account ~ "^{account}:"'] + returncode, stdout, stderr = pipe_main(arglist, config, io.BytesIO) + assert returncode == 0 + stdout.seek(0) + ods_doc = odf.opendocument.load(stdout) + rows = iter(ods_doc.spreadsheet.firstChild.getElementsByType(odf.table.TableRow)) + next(rows) # Skip header row + amt_pattern = rf'^{re.escape(amt_prefix)}\d' + for count, row in enumerate(rows, 1): + date, entity, narration, amount = row.childNodes + assert re.fullmatch(rf'{fy}-\d{{2}}-\d{{2}}', date.text) + assert narration.text.startswith(f'{fy} ') + assert re.match(amt_pattern, amount.text) + assert count