'contract',

        'purchase-order',

    ]

    COLUMNS = [

        'Date',

        data.Metadata.human_name('entity'),

        'Invoice Amount',

        'Booked Amount',

        data.Metadata.human_name('project'),

        *(data.Metadata.human_name(key) for key in DOC_COLUMNS),

    ]

    COL_COUNT = len(COLUMNS)

    def __init__(self,

                 rt_wrapper: rtutil.RT,

                 date: datetime.date,

                 logger: logging.Logger,

    ) -> None:

        super().__init__(rt_wrapper)

        self.date = date

        self.logger = logger

    def section_key(self, row: AccrualPostings) -> Optional[data.Account]:

        if isinstance(row.account, str):

            return row.account

        else:

            return None

    def start_spreadsheet(self) -> None:

        for accrual_type in AccrualAccount:

            self.use_sheet(accrual_type.name.title())

            for index in range(self.COL_COUNT):

            self.add_row(*(

                self.string_cell(name, stylename=self.style_bold)

                for name in self.COLUMNS

            ))

            self.lock_first_row()

    def start_section(self, key: Optional[data.Account]) -> None:

        if key is None:

            return

        self.age_thresholds = list(AccrualAccount.by_account(key).value.aging_thresholds)

        self.age_balances = [core.MutableBalance() for _ in self.age_thresholds]

        accrual_date = self.date - datetime.timedelta(days=self.age_thresholds[-1])

        acct_parts = key.slice_parts()

        self.use_sheet(acct_parts[1])

        self.add_row()

        self.add_row(self.string_cell(

            f"{' '.join(acct_parts[2:])} {acct_parts[1]} Aging Report"

            f" Accrued by {accrual_date.isoformat()} Unpaid by {self.date.isoformat()}",

            stylename=self.merge_styles(self.style_bold, self.style_centertext),

            numbercolumnsspanned=self.COL_COUNT,

        ))

        self.add_row()

    def end_section(self, key: Optional[data.Account]) -> None:

        if key is None:

            return

        total_balance = core.MutableBalance()

        text_style = self.merge_styles(self.style_bold, self.style_endtext)

        text_span = 4

        last_age_text: Optional[str] = None

        self.add_row()

        for threshold, balance in zip(self.age_thresholds, self.age_balances):

        pass

    def write(self, rows: Iterable[RT]) -> None:

        prev_section: Optional[ST] = None

        self.start_spreadsheet()

        for row in rows:

            section = self.section_key(row)

            if section != prev_section:

                if prev_section is not None:

                    self.end_section(prev_section)

                self.start_section(section)

                prev_section = section

            self.write_row(row)

        try:

            should_end = section is not None

        except NameError:

            should_end = False

        if should_end:

            self.end_section(section)

        self.end_spreadsheet()

class BaseODS(BaseSpreadsheet[RT, ST], metaclass=abc.ABCMeta):

    """Abstract base class to help write OpenDocument spreadsheets

    This class provides the very core logic to write an arbitrary set of data

    rows to an OpenDocument spreadsheet. It provides helper methods for

    building sheets, rows, and cells.

    See also the BaseSpreadsheet base class for additional documentation about

    methods you must and can define, the definition of RT and ST, etc.

    """

    def __init__(self, rt_wrapper: Optional[rtutil.RT]=None) -> None:

        self.rt_wrapper = rt_wrapper

        self.locale = babel.core.Locale.default('LC_MONETARY')

        self.currency_fmt_key = 'accounting'

        self._name_counter = itertools.count(1)

        self._currency_style_cache: MutableMapping[str, odf.style.Style] = {}

        self.document = odf.opendocument.OpenDocumentSpreadsheet()

        self.init_settings()

        self.init_styles()

        self.sheet = self.use_sheet("Report")

    ### Low-level document tree manipulation

    # The *intent* is that you only need to use these if you're adding new

    # methods to manipulate document settings or styles.

    def copy_element(self, elem: odf.element.Element) -> odf.element.Element:

        qattrs = dict(self.iter_qattributes(elem))

        retval = odf.element.Element(qname=elem.qname, qattributes=qattrs)

        try:

            orig_name = retval.getAttribute('name')

        except ValueError:

            orig_name = None

        if orig_name is not None:

            retval.setAttribute('name', f'{orig_name}{next(self._name_counter)}')

        return retval

    def ensure_child(self,

                     parent: odf.element.Element,

                     child_type: ElementType,

                     **kwargs: Any,

    ) -> odf.element.Element:

        new_child = child_type(**kwargs)

        parent.insertBefore(new_child, found_child)

        if found_child is not None:

            parent.removeChild(found_child)

        return new_child

    def set_config(self,

                   root: odf.element.Element,

                   name: str,

                   value: Union[bool, int, str],

                   config_type: Optional[str]=None,

    ) -> None:

        """Ensure ``root`` has a ``ConfigItem`` with the given name, type, and value"""

        value_s = str(value)

        if isinstance(value, bool):

            value_s = str(value).lower()

            default_type = 'boolean'

        elif isinstance(value, str):

            default_type = 'string'

        if config_type is None:

            try:

                config_type = default_type

            except NameError:

                raise ValueError(

                    f"need config_type for {type(value).__name__} value",

                ) from None

        item = self.replace_child(

            root, odf.config.ConfigItem, name=name, type=config_type,

        )

        item.addText(value_s)

    ### Styles

    def _build_currency_style(

            self,

            root: odf.element.Element,

            locale: babel.core.Locale,

            code: str,

            fmt_index: int,

            properties: Optional[odf.style.TextProperties]=None,

            *,

            fmt_key: Optional[str]=None,

            volatile: bool=False,

            minintegerdigits: int=1,

    ) -> odf.element.Element:

        if fmt_key is None:

            fmt_key = self.currency_fmt_key

        pattern = locale.currency_formats[fmt_key]

        fmts = pattern.pattern.split(';')

        try:

            fmt = fmts[fmt_index]

        except IndexError:

            fmt = fmts[0]

            grouping = pattern.grouping[0]

        else:

            grouping = pattern.grouping[fmt_index]

        zero_s = babel.numbers.format_currency(0, code, '##0.0', locale)

        try:

            decimal_index = zero_s.rindex('.') + 1

        except ValueError:

            decimalplaces = 0

        else:

            decimalplaces = len(zero_s) - decimal_index

        style = self.replace_child(

            root,

        )

        self.ensure_child(

            self.style_dividerline,

            odf.style.TableCellProperties,

            borderbottom='1pt solid #0000ff',

        )

        date_style = self.replace_child(styles, odf.number.DateStyle, name='ISODate')

        date_style.addElement(odf.number.Year(style='long'))

        date_style.addElement(odf.number.Text(text='-'))

        date_style.addElement(odf.number.Month(style='long'))

        date_style.addElement(odf.number.Text(text='-'))

        date_style.addElement(odf.number.Day(style='long'))

        self.style_date = self.ensure_child(

            styles,

            odf.style.Style,

            name=f'{date_style.getAttribute("name")}Cell',

            family='table-cell',

            datastylename=date_style,

        )

        self.style_starttext: odf.style.Style

        self.style_centertext: odf.style.Style

        self.style_endtext: odf.style.Style

        for textalign in ['start', 'center', 'end']:

            aligned_style = self.replace_child(

                styles, odf.style.Style, name=f'{textalign.title()}Text',

            )

            aligned_style.setAttribute('family', 'table-cell')

            aligned_style.addElement(odf.style.ParagraphProperties(textalign=textalign))

            setattr(self, f'style_{textalign}text', aligned_style)

    ### Rows and cells

    def add_row(self, *cells: odf.table.TableCell, **attrs: Any) -> odf.table.TableRow:

        row = odf.table.TableRow(**attrs)

        for cell in cells:

            row.addElement(cell)

        self.sheet.addElement(row)

        return row

    def balance_cell(self, balance: Balance, **attrs: Any) -> odf.table.TableCell:

        if balance.is_zero():

            return self.float_cell(0, **attrs)

        elif len(balance) == 1:

            amount = next(iter(balance.values()))

            attrs['stylename'] = self.merge_styles(

                attrs.get('stylename'), self.currency_style(amount.currency),

            )

            return self.currency_cell(amount, **attrs)

        else:

            lines = [babel.numbers.format_currency(

                number, currency, locale=self.locale, format_type=self.currency_fmt_key,

            ) for number, currency in balance.values()]

            attrs['stylename'] = self.merge_styles(

                attrs.get('stylename'), self.style_endtext,

            )

            return self.multiline_cell(lines, **attrs)

    def currency_cell(self, amount: data.Amount, **attrs: Any) -> odf.table.TableCell:

        if 'stylename' not in attrs:

            attrs['stylename'] = self.currency_style(amount.currency)

        number, currency = amount

        cell = odf.table.TableCell(valuetype='currency', value=number, **attrs)

# it under the terms of the GNU Affero General Public License as published by

# the Free Software Foundation, either version 3 of the License, or

# (at your option) any later version.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

# GNU Affero General Public License for more details.

#

# You should have received a copy of the GNU Affero General Public License

# along with this program.  If not, see <https://www.gnu.org/licenses/>.

import argparse

import collections

import datetime

import enum

import itertools

import operator

import logging

import sys

from typing import (

    Callable,

    Dict,

    Iterable,

    Iterator,

    List,

    Mapping,

    Optional,

    Sequence,

    TextIO,

    Tuple,

)

from pathlib import Path

import odf.table  # type:ignore[import]

from beancount.parser import printer as bc_printer

from . import core

from .. import books

from .. import cliutil

from .. import config as configmod

from .. import data

from .. import ranges

from .. import rtutil

PostTally = List[Tuple[int, data.Account]]

PROGNAME = 'ledger-report'

logger = logging.getLogger('conservancy_beancount.reports.ledger')

class LedgerODS(core.BaseODS[data.Posting, data.Account]):

    CORE_COLUMNS: Sequence[str] = [

        'Date',

        data.Metadata.human_name('entity'),

        'Description',

        'Original Amount',

        'Booked Amount',

    ]

    ACCOUNT_COLUMNS: Dict[str, Sequence[str]] = collections.OrderedDict([

        ('Income', ['project', 'rt-id', 'receipt', 'income-type']),

        ('Expenses', ['project', 'rt-id', 'receipt', 'approval', 'expense-allocation']),

        ('Equity', ['rt-id']),

        ('Assets:Receivable', ['project', 'rt-id', 'invoice', 'approval', 'contract', 'purchase-order']),

        ('Liabilities:Payable', ['project', 'rt-id', 'invoice', 'approval', 'contract', 'purchase-order']),

        ('Assets:PayPal', ['rt-id', 'paypal-id', 'receipt', 'approval']),

        ('Assets', ['rt-id', 'receipt', 'approval', 'bank-statement']),

        ('Liabilities', ['rt-id', 'receipt', 'approval', 'bank-statement']),

    ])

    # Excel 2003 was limited to 65,536 rows per worksheet.

    # While we can probably count on all our users supporting more modern

    # formats (Excel 2007 supports over 1 million rows per worksheet),

    # keeping the default limit conservative seems good to avoid running into

    # other limits (like the number of hyperlinks per worksheet), plus just

    # better for human organization and readability.

    SHEET_SIZE = 65000

    def __init__(self,

                 start_date: datetime.date,

                 stop_date: datetime.date,

                 sheet_names: Optional[Sequence[str]]=None,

                 rt_wrapper: Optional[rtutil.RT]=None,

                 sheet_size: Optional[int]=None,

    ) -> None:

        if sheet_names is None:

            sheet_names = list(self.ACCOUNT_COLUMNS)

        if sheet_size is None:

            sheet_size = self.SHEET_SIZE

        super().__init__(rt_wrapper)

        self.date_range = ranges.DateRange(start_date, stop_date)

        self.required_sheet_names = sheet_names

        self.sheet_size = sheet_size

    @classmethod

    def _group_tally(

            cls,

            tally_by_account: PostTally,

            key: Callable[[data.Account], Optional[str]],

    ) -> Dict[str, PostTally]:

        retval: Dict[str, PostTally] = collections.defaultdict(list)

        for count, account in tally_by_account:

            item_key = key(account)

            if item_key is not None:

                retval[item_key].append((count, account))

        return retval

    @classmethod

    def _split_sheet(

            cls,

            tally_by_account: PostTally,

            sheet_size: int,

            sheet_name: str,

    ) -> Iterator[str]:

        total = 0

        for index, (count, account) in enumerate(tally_by_account):

            total += count

            if total > sheet_size:

                break

        else:

            # All the accounts fit in this sheet.

            yield sheet_name

            return

        if index == 0 and len(tally_by_account) == 1:

            # With one account, we can't split any further, so warn and stop.

            logger.warning(

    def _sort_and_filter_accounts(

            accounts: Iterable[data.Account],

            order: Sequence[str],

    ) -> Iterator[Tuple[int, data.Account]]:

        index_map = {s: ii for ii, s in enumerate(order)}

        retval: Mapping[int, List[data.Account]] = collections.defaultdict(list)

        for account in accounts:

            acct_key = account.is_under(*order)

            if acct_key is not None:

                retval[index_map[acct_key]].append(account)

        for key in sorted(retval):

            acct_list = retval[key]

            acct_list.sort()

            for account in acct_list:

                yield key, account

    def section_key(self, row: data.Posting) -> data.Account:

        return row.account

    def start_sheet(self, sheet_name: str) -> None:

        self.use_sheet(sheet_name.replace(':', ' '))

        columns_key = data.Account(sheet_name).is_under(*self.ACCOUNT_COLUMNS)

        # columns_key must not be None because ACCOUNT_COLUMNS has an entry

        # for all five root accounts.

        assert columns_key is not None

        self.metadata_columns = self.ACCOUNT_COLUMNS[columns_key]

        self.sheet_columns: Sequence[str] = [

            *self.CORE_COLUMNS,

            *(data.Metadata.human_name(meta_key) for meta_key in self.metadata_columns),

        ]

        for col_name in self.sheet_columns:

            self.sheet.addElement(odf.table.TableColumn(

            ))

        self.add_row(*(

            self.string_cell(col_name, stylename=self.style_bold)

            for col_name in self.sheet_columns

        ))

        self.lock_first_row()

    def _report_section_balance(self, key: data.Account, date_key: str) -> None:

        uses_opening = key.is_under('Assets', 'Equity', 'Liabilities')

        if date_key == 'start':

            if not uses_opening:

                return

            date = self.date_range.start

            description = "Opening Balance"

        else:

            date = self.date_range.stop

            description = "Ending Balance" if uses_opening else "Period Total"

        balance = self.norm_func(

            self.account_groups[key].balance_at_cost_by_date(date)

        )

        self.add_row(

            self.date_cell(date, stylename=self.merge_styles(

                self.style_bold, self.style_date,

            )),

            odf.table.TableCell(),

            self.string_cell(description, stylename=self.style_bold),

            odf.table.TableCell(),

            self.balance_cell(balance, stylename=self.style_bold),

        )

    def start_section(self, key: data.Account) -> None:

        self.add_row()

            amount_cell,

            self.currency_cell(self.norm_func(row.at_cost())),

            *(self.meta_links_cell(row.meta.report_links(key))

              if key in data.LINK_METADATA

              else self.string_cell(row.meta.get(key, ''))

              for key in self.metadata_columns),

        )

    def _combined_balance_row(self,

                              date: datetime.date,

                              balance_accounts: Sequence[str],

    ) -> None:

        balance = -sum((

            related.balance_at_cost_by_date(date)

            for account, related in self.account_groups.items()

            if account.is_under(*balance_accounts)

        ), core.MutableBalance())

        self.add_row(

            self.string_cell(

                f"Balance as of {date.isoformat()}",

                stylename=self.merge_styles(self.style_bold, self.style_endtext),

            ),

            self.balance_cell(balance, stylename=self.style_bold),

        )

    def write_balance_sheet(self) -> None:

        balance_accounts = ['Equity', 'Income', 'Expenses']

        # FIXME: This is a hack to exclude non-project Equity accounts from

        # project reports.

        if balance_accounts[0] not in self.required_sheet_names:

            balance_accounts[0] = 'Equity:Funds'

        self.use_sheet("Balance")

        self.add_row(

            self.string_cell("Account", stylename=self.style_bold),

            self.string_cell("Balance", stylename=self.style_bold),

        )

        self.lock_first_row()

        self.add_row()

        self.add_row(self.string_cell(

            f"Ledger From {self.date_range.start.isoformat()}"

            f" To {self.date_range.stop.isoformat()}",

            stylename=self.merge_styles(self.style_centertext, self.style_bold),

            numbercolumnsspanned=2,

        ))

        self.add_row()

        self._combined_balance_row(self.date_range.start, balance_accounts)

        for _, account in self._sort_and_filter_accounts(

                self.account_groups, balance_accounts,

        ):

            related = self.account_groups[account]

            # start_bal - stop_bal == -(stop_bal - start_bal)

            balance = related.balance_at_cost_by_date(self.date_range.start)

            balance -= related.balance_at_cost_by_date(self.date_range.stop)

            if not balance.is_zero():

                self.add_row(

                    self.string_cell(account, stylename=self.style_endtext),

                    self.balance_cell(balance),

                )

        self._combined_balance_row(self.date_range.stop, balance_accounts)

    def write(self, rows: Iterable[data.Posting]) -> None:

        self.account_groups = dict(core.RelatedPostings.group_by_account(rows))

        self.write_balance_sheet()

        tally_by_account_iter = (

#!/usr/bin/env python3

from setuptools import setup

setup(

    name='conservancy_beancount',

    description="Plugin, library, and reports for reading Conservancy's books",

    author='Software Freedom Conservancy',

    author_email='info@sfconservancy.org',

    license='GNU AGPLv3+',

    install_requires=[

        'babel>=2.6',  # Debian:python3-babel

        'beancount>=2.2',  # Debian:beancount

        # 1.4.1 crashes when trying to save some documents.

        'odfpy>=1.4.0,!=1.4.1',  # Debian:python3-odf

        'PyYAML>=3.0',  # Debian:python3-yaml

        'regex',  # Debian:python3-regex

        'rt>=2.0',

    ],

    setup_requires=[

        'pytest-mypy',

        'pytest-runner',  # Debian:python3-pytest-runner

    ],

    tests_require=[

        'mypy>=0.770',  # Debian:python3-mypy

        'pytest',  # Debian:python3-pytest

    ],

    packages=[

        'conservancy_beancount',

        'conservancy_beancount.plugin',

        'conservancy_beancount.reports',

    ],

    entry_points={

        'console_scripts': [

            'accrual-report = conservancy_beancount.reports.accrual:entry_point',

            'ledger-report = conservancy_beancount.reports.ledger:entry_point',

        ],

    for name in names:

        ods_writer.use_sheet(name)

        ods_writer.write([(name,)])

    ods_writer.use_sheet('End')

    sheets = ods_writer.document.getElementsByType(odf.table.Table)

    assert len(sheets) == len(names) + 1

    for name, sheet in zip(names, sheets):

        texts = [cell.text for row in testutil.ODSCell.from_sheet(sheet)

                 for cell in row]

        assert texts == [name]

def test_ods_writer_use_sheet_returns_to_prior_sheets(ods_writer):

    names = ['One', 'Two']

    sheets = []

    for name in names:

        sheets.append(ods_writer.use_sheet(name))

        ods_writer.write([(name,)])

    for name, expected in zip(names, sheets):

        actual = ods_writer.use_sheet(name)

        assert actual is expected

        texts = [cell.text for row in testutil.ODSCell.from_sheet(actual)

                 for cell in row]

        assert texts == [name]

def test_ods_writer_use_sheet_discards_unused_sheets(ods_writer):

    ods_writer.use_sheet('Three')

    ods_writer.use_sheet('Two')

    ods_writer.use_sheet('One')

    sheets = ods_writer.document.getElementsByType(odf.table.Table)

    assert len(sheets) == 1

    assert sheets[0].getAttribute('name') == 'One'

@pytest.mark.parametrize('currency_code', [

    'USD',

    'EUR',

    'BRL',

])

def test_ods_currency_style(ods_writer, currency_code):

    style = ods_writer.currency_style(currency_code)

    assert style.getAttribute('family') == 'table-cell'

    curr_style = get_child(

        ods_writer.document.styles,

        odf.number.CurrencyStyle,

        name=style.getAttribute('datastylename'),

    )

    check_currency_style(curr_style)

    mappings = get_children(curr_style, odf.style.Map)

    assert mappings

    for mapping in mappings:

        check_currency_style(get_child(

            ods_writer.document.styles,

            odf.number.CurrencyStyle,

            name=mapping.getAttribute('applystylename'),

        ))

def test_ods_currency_style_caches(ods_writer):

    expected = ods_writer.currency_style('USD')

    _ = ods_writer.currency_style('EUR')

    actual = ods_writer.currency_style('USD')

    assert actual is expected

def test_ods_currency_style_cache_considers_properties(ods_writer):

    bold_text = odf.style.TextProperties(fontweight='bold')

    plain = ods_writer.currency_style('USD')

    bold = ods_writer.currency_style('USD', positive_properties=bold_text)

    assert plain is not bold

    assert plain.getAttribute('name') != bold.getAttribute('name')

    assert plain.getAttribute('datastylename') != bold.getAttribute('datastylename')

@pytest.mark.parametrize('attr_name,child_type,checked_attr', [

    ('style_bold', odf.style.TextProperties, 'fontweight'),

    ('style_centertext', odf.style.ParagraphProperties, 'textalign'),

    ('style_dividerline', odf.style.TableCellProperties, 'borderbottom'),

    ('style_endtext', odf.style.ParagraphProperties, 'textalign'),

    ('style_starttext', odf.style.ParagraphProperties, 'textalign'),

])

def test_ods_writer_style(ods_writer, attr_name, child_type, checked_attr):

    style = getattr(ods_writer, attr_name)

    actual = get_child(root, odf.style.Style, name=style.getAttribute('name'))

    assert actual is style

    child = get_child(actual, child_type)

    assert child.getAttribute(checked_attr)

def test_ods_writer_merge_styles(ods_writer):

    style = ods_writer.merge_styles(ods_writer.style_bold, ods_writer.style_dividerline)

    actual = get_child(

        ods_writer.document.styles,

        odf.style.Style,

        name=style.getAttribute('name'),

    )

    assert actual is style

    assert actual.getAttribute('family') == 'table-cell'

    text_props = get_child(actual, odf.style.TextProperties)

    assert text_props.getAttribute('fontweight') == 'bold'

    cell_props = get_child(actual, odf.style.TableCellProperties)

    assert cell_props.getAttribute('borderbottom')

def test_ods_writer_merge_styles_with_children_and_attributes(ods_writer):

    jpy_style = ods_writer.currency_style('JPY')

    style = ods_writer.merge_styles(ods_writer.style_bold, jpy_style)

    actual = get_child(

        ods_writer.document.styles,

        odf.style.Style,

        name=style.getAttribute('name'),

    )

    assert actual is style

    assert actual.getAttribute('family') == 'table-cell'

    assert actual.getAttribute('datastylename') == jpy_style.getAttribute('datastylename')

    text_props = get_child(actual, odf.style.TextProperties)