row = self.add_row(*(

            self.multiline_cell(header, stylename=center_bold)

            for header in headers

        ))

        row.firstChild.setAttribute(

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

        )

        self.lock_first_row()

        self.lock_first_column()

        self.add_row()

        self.add_row(self.string_cell(

            f"Fund Report From {self.start_date.isoformat()} To {self.stop_date.isoformat()}",

            stylename=center_bold,

            numbercolumnsspanned=6,

        ))

        self.add_row()

    def end_spreadsheet(self) -> None:

        start_sheet = self.sheet

        self.set_open_sheet(self.sheet)

        self.start_spreadsheet(expanded=False)

        bal_indexes = [0, 1, 2, 4]

        totals = [core.MutableBalance() for _ in bal_indexes]

        threshold = Decimal('.5')

            if (not all(bal.clean_copy(threshold).le_zero() for bal in balances)

                and fund != UNRESTRICTED_FUND):

                self.write_balances(fund, balances)

                for total, bal in zip(totals, balances):

                    total += bal

        self.write_balances('', totals, self.merge_styles(

            self.border_style(core.Border.TOP, '.75pt'),

            self.border_style(core.Border.BOTTOM, '1.5pt', 'double'),

        ))

        self.document.spreadsheet.childNodes.reverse()

        self.sheet = start_sheet

    def _row_balances(self, accounts_map: AccountsMap) -> Iterator[core.Balance]:

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

        key_order = [core.OPENING_BALANCE_NAME, *acct_order, core.ENDING_BALANCE_NAME]

        balances: Dict[str, core.Balance] = {key: core.MutableBalance() for key in key_order}

        for acct_s, balance in core.account_balances(accounts_map, acct_order):

            if acct_s in balances:

                balances[acct_s] = balance

            else:

                acct_root, _, _ = acct_s.partition(':')

                balances[acct_root] += balance

        for key in key_order:

            if key == 'Expenses':

  project: "Alpha"

  Liabilities:UnearnedIncome  30 USD

  Income:Other               -30 USD

2019-06-09 * "Alpha prepaid expense converted"

  project: "Alpha"

  Assets:Prepaid:Expenses  -20 USD

  Expenses:Other            20 USD

2019-06-12 * "Alpha expense 2019A"

  project: "Alpha"

  Expenses:Other    3 USD

  Assets:Checking  -3 USD

2019-06-15 * "Alpha expense 2019B"

  project: "Alpha"

  Expenses:Other    3 USD

  Assets:Checking  -3 USD

2019-09-03 * "Bravo income"

  project: "Bravo"

  Income:Other    -200 USD

  Assets:Checking  200 USD

  project: "Delta"

  Income:Other    -4.60 USD

  Assets:Checking  4.60 USD

    assert retcode == 0

    assert list(FlatPosting.from_output(output)) == [

        FlatPosting.make(A_CHECKING, '10050.40'),

        FlatPosting.make(A_PREPAID, 20, project='Alpha'),

        FlatPosting.make(A_RECEIVABLE, 32, 'EUR', '1.25', '2018-03-03', 'Conservancy'),

        FlatPosting.make(A_RESTRICTED, -3060, project='Alpha'),

        FlatPosting.make(A_RESTRICTED, -1980, project='Bravo'),

        FlatPosting.make(A_RESTRICTED, -1000, project='Charlie'),

        FlatPosting.make(A_RESTRICTED, '-.40', project='Delta'),

        FlatPosting.make(A_UNRESTRICTED, -4036, project='Conservancy'),

        FlatPosting.make(A_PAYABLE, -4, project='Conservancy'),

        FlatPosting.make(A_UNEARNED, -30, project='Alpha'),

    ]

@pytest.mark.parametrize('arg', ['2020', '2020-12-31'])

def test_2020_opening(arg):

    retcode, output, errors = run_main([arg])

    assert not errors.getvalue()

    assert retcode == 0

    assert list(FlatPosting.from_output(output)) == [

        FlatPosting.make(A_CHECKING, 10281),

        FlatPosting.make(A_EUR, 32, 'EUR', '1.5', '2019-03-03'),

        FlatPosting.make(A_RESTRICTED, -3064, project='Alpha'),

        FlatPosting.make(A_RESTRICTED, -2180, project='Bravo'),

        FlatPosting.make(A_RESTRICTED, -5, project='Delta'),

    ]

START_DATE = datetime.date(2018, 3, 1)

MID_DATE = datetime.date(2019, 3, 1)

STOP_DATE = datetime.date(2020, 3, 1)

EQUITY_ROOT_ACCOUNTS = ('Expenses:', 'Equity:', 'Income:')

OPENING_BALANCES = {

    'Alpha': 3000,

    'Bravo': 2000,

    'Charlie': 1000,

    'Conservancy': 4000,

    'Delta': 0,

}

BALANCES_BY_YEAR = {

    ('Conservancy', 2018): [

        ('Income:Other', 40),

        ('Expenses:Other', -4),

        ('Assets:Receivable:Accounts', 40),

        ('Liabilities:Payable:Accounts', 4),

    ],

    ('Conservancy', 2019): [

        ('Income:Other', 42),

        ('Expenses:Other', Decimal('-4.20')),

        ('Equity:Realized:CurrencyConversion', Decimal('6.20')),

        ('Assets:Receivable:Accounts', -40),

        ('Liabilities:Payable:Accounts', -4),

    ],

    ('Alpha', 2018): [

        ('Income:Other', 60),

        ('Liabilities:UnearnedIncome', 30),

        ('Assets:Prepaid:Expenses', 20),

    ],

    ('Alpha', 2019): [

        ('Income:Other', 30),

        ('Expenses:Other', -26),

        ('Assets:Prepaid:Expenses', -20),

        ('Liabilities:UnearnedIncome', -30),

    ],

    ('Bravo', 2018): [

        ('Expenses:Other', -20),

    ],

    ('Bravo', 2019): [

        ('Income:Other', 200),

    ],

    ('Delta', 2018): [

        ('Income:Other', Decimal('.40')),

    ],

    ('Delta', 2019): [

        ('Income:Other', Decimal('4.60')),

    ],

}

@pytest.fixture

def fund_entries():

    return copy.deepcopy(_ledger_load[0])

def split_text_lines(output):

    for line in output:

        account, amount = line.rsplit(None, 1)

        yield account.strip(), amount

def format_amount(amount, currency='USD'):

    return babel.numbers.format_currency(

        amount, currency, format_type='accounting',

    )

def check_text_balances(actual, expected, *expect_accounts):

    balance = Decimal()

def check_text_report(output, project, start_date, stop_date):

    _, _, project = project.rpartition('=')

    balance_amount = Decimal(OPENING_BALANCES[project])

    expected = collections.defaultdict(Decimal)

    for year in range(2018, stop_date.year):

        try:

            amounts = BALANCES_BY_YEAR[(project, year)]

        except KeyError:

            pass

        else:

            for account, amount in amounts:

                if year < start_date.year and account.startswith(EQUITY_ROOT_ACCOUNTS):

                    balance_amount += amount

                else:

                    expected[account] += amount

    actual = split_text_lines(output)

    next(actual); next(actual)  # Discard headers

    open_acct, open_amt = next(actual)

    assert open_acct == "{} balance as of {}".format(

        project, start_date.isoformat(),

    )

    assert open_amt == format_amount(balance_amount)

    balance_amount += check_text_balances(

        actual, expected,

        'Equity:Realized:CurrencyConversion',

        'Income:Other',

        'Expenses:Other',

    )

    end_acct, end_amt = next(actual)

    assert end_acct == "{} balance as of {}".format(

        project, stop_date.isoformat(),

    )

    assert end_amt == format_amount(balance_amount)

    balance_amount += check_text_balances(

        actual, expected,

        'Assets:Receivable:Accounts',

        'Assets:Prepaid:Expenses',

        'Liabilities:Payable:Accounts',

        'Liabilities:UnearnedIncome',

    )

    assert next(actual, None) is None

def check_cell_balance(cell, balance):

    if balance:

        assert cell.value == balance

    else:

        assert not cell.value

def check_ods_sheet(sheet, account_balances, *, full):

    if full:

        account_bals = account_balances.copy()

        account_bals['Unrestricted'] = account_bals.pop('Conservancy')

    else:

        account_bals = {

            key: balances

            for key, balances in account_balances.items()

            if key != 'Conservancy' and any(v >= .5 for v in balances.values())

        }

        totals = {key: Decimal() for key in

        for fund, balances in account_bals.items():

            for key in totals:

                totals[key] += balances[key]

        account_bals[''] = totals

    for row in itertools.islice(sheet.getElementsByType(odf.table.TableRow), 4, None):

        cells = iter(testutil.ODSCell.from_row(row))

        try:

            fund = next(cells).firstChild.text

        except (AttributeError, StopIteration):

            continue

        try:

            balances = account_bals.pop(fund)

        except KeyError:

            pytest.fail(f"report included unexpected fund {fund}")

        check_cell_balance(next(cells), balances['opening'])

        check_cell_balance(next(cells), balances['Income'])

        if full:

        check_cell_balance(next(cells), sum(balances[key] for key in [

        ]))

        if full:

            check_cell_balance(next(cells), balances['Assets:Receivable'])

            check_cell_balance(next(cells), balances['Assets:Prepaid'])

            check_cell_balance(next(cells), balances['Liabilities'])

            check_cell_balance(next(cells), balances['Liabilities:Payable'])

        assert next(cells, None) is None

    assert not account_bals, "did not see all funds in report"

def check_ods_report(ods, start_date, stop_date):

    account_bals = collections.OrderedDict((key, {

        'opening': Decimal(amount),

        'Income': Decimal(0),

        'Expenses': Decimal(0),

        'Assets:Receivable': Decimal(0),

        'Assets:Prepaid': Decimal(0),

        'Liabilities:Payable': Decimal(0),

        'Liabilities': Decimal(0),  # UnearnedIncome

    }) for key, amount in sorted(OPENING_BALANCES.items()))

    for fund, year in itertools.product(account_bals, range(2018, stop_date.year)):

        try:

            amounts = BALANCES_BY_YEAR[(fund, year)]

        except KeyError:

            pass

        else:

            for account, amount in amounts:

                else:

                    acct_key, _, _ = account.rpartition(':')

                account_bals[fund][acct_key] += amount

    sheets = iter(ods.getElementsByType(odf.table.Table))

    check_ods_sheet(next(sheets), account_bals, full=False)

    check_ods_sheet(next(sheets), account_bals, full=True)

    assert next(sheets, None) is None, "found unexpected sheet"

def run_main(out_type, arglist, config=None):

    if config is None:

        config = testutil.TestConfig(

            books_path=testutil.test_path('books/fund.beancount'),

        )

    arglist.insert(0, '--output-file=-')

    output = out_type()

    errors = io.StringIO()

    retcode = fund.main(arglist, output, errors, config)

    output.seek(0)

    return retcode, output, errors

@pytest.mark.parametrize('project,start_date,stop_date', [

    ('Conservancy', START_DATE, STOP_DATE),

    ('project=Conservancy', MID_DATE, STOP_DATE),

    ('Conservancy', START_DATE, MID_DATE),