Beispiel #1
1
def test_to_excel(example_ledger):
    types, rows = run_query(
        example_ledger.all_entries,
        example_ledger.options,
        'balances',
        numberify=True)
    assert excel.to_excel(types, rows, 'ods', 'balances')
Beispiel #2
0
    def test_AnyMeta(self, entries, _, options_map):
        """
        2016-11-20 *
          name: "TheName"
          address: "1 Wrong Way"
          empty: "NotEmpty"
          Assets:Banking          1 USD
            color: "Green"
            address: "1 Right Way"
            empty:
        """
        rtypes, rrows = query.run_query(entries, options_map,
                                        'SELECT ANY_META("name") as m')
        self.assertEqual([('TheName', )], rrows)

        rtypes, rrows = query.run_query(entries, options_map,
                                        'SELECT ANY_META("color") as m')
        self.assertEqual([('Green', )], rrows)

        rtypes, rrows = query.run_query(entries, options_map,
                                        'SELECT ANY_META("address") as m')
        self.assertEqual([('1 Right Way', )], rrows)

        rtypes, rrows = query.run_query(entries, options_map,
                                        'SELECT ANY_META("empty") as m')
        self.assertEqual([(None, )], rrows)
Beispiel #3
0
    def make_table(self):
        """An account tree based on matching regex patterns."""
        cash = self.ledger.all_root_account.get('Assets')
        credit = self.ledger.all_root_account.get('Liabilities')

        _, wrows = query.run_query(
            self.ledger.entries, self.ledger.options, '''
            select account,number(only("USD", sum(position)))
                from not "future" in tags
                where account ~ "^(Assets|Liabilities)"
                    and (meta('_cleared') = True or number < 0)
                group by 1''')
        _, crows = query.run_query(
            self.ledger.entries, self.ledger.options, '''
            select account,number(only("USD", sum(position)))
                from not "future" in tags
                where account ~ "^(Assets|Liabilities)"
                    and (meta('_cleared') = True)
                group by 1''')
        _, trows = query.run_query(
            self.ledger.entries, self.ledger.options, '''
            select account,number(only("USD", sum(position)))
                from not "future" in tags
                where account ~ "^(Assets|Liabilities)"
                group by 1''')

        self.wrows = dict(wrows)
        self.crows = dict(crows)
        self.trows = dict(trows)

        return [cash, credit]
Beispiel #4
0
 def _wct(self, account_name):
     if not account_name:
         account_name = '^(Assets:|Liabilities:)'
     _, wrow = query.run_query(
         self.ledger.entries, self.ledger.options, '''
         select number(only("USD", sum(position)))
             where account = "{}"
                 and (meta('_cleared') = True or number < 0)
             ''', account_name)
     if not wrow:
         wrow = [[Decimal()]]
     _, crow = query.run_query(
         self.ledger.entries, self.ledger.options, '''
         select number(only("USD", sum(position)))
             where account = "{}"
                 and (meta('_cleared') = True)
             ''', account_name)
     if not crow:
         crow = [[Decimal()]]
     _, trow = query.run_query(
         self.ledger.entries, self.ledger.options, '''
         select number(only("USD", sum(position)))
             where account = "{}"
             ''', account_name)
     if not trow:
         trow = [[Decimal()]]
     return wrow[0][0], crow[0][0], trow[0][0]
Beispiel #5
0
    def test_GrepN(self, entries, _, options_map):
        """
        2016-11-20 * "prev match in context next"
          Assets:Banking          1 USD
        """
        rtypes, rrows = query.run_query(
            entries, options_map, '''
          SELECT GREPN("in", narration, 0) as m
        ''')
        self.assertEqual([('in', )], rrows)

        rtypes, rrows = query.run_query(
            entries, options_map, '''
          SELECT GREPN("match (.*) context", narration, 1) as m
        ''')
        self.assertEqual([('in', )], rrows)

        rtypes, rrows = query.run_query(
            entries, options_map, '''
          SELECT GREPN("(.*) in (.*)", narration, 2) as m
        ''')
        self.assertEqual([('context next', )], rrows)

        rtypes, rrows = query.run_query(
            entries, options_map, '''
          SELECT GREPN("ab(at)hing", "abathing", 1) as m
        ''')
        self.assertEqual([('at', )], rrows)
Beispiel #6
0
    def test_Date(self, entries, _, options_map):
        """
        2016-11-20 * "ok"
          Assets:Banking          1 USD
        """
        rtypes, rrows = query.run_query(entries, options_map,
                                        'SELECT date(2020, 1, 2) as m')
        self.assertEqual([(datetime.date(2020, 1, 2), )], rrows)

        rtypes, rrows = query.run_query(entries, options_map,
                                        'SELECT date(year, month, 1) as m')
        self.assertEqual([(datetime.date(2016, 11, 1), )], rrows)

        with self.assertRaisesRegex(ValueError,
                                    "day is out of range for month"):
            rtypes, rrows = query.run_query(entries, options_map,
                                            'SELECT date(2020, 2, 32) as m')

        rtypes, rrows = query.run_query(entries, options_map,
                                        'SELECT date("2020-01-02") as m')
        self.assertEqual([(datetime.date(2020, 1, 2), )], rrows)

        rtypes, rrows = query.run_query(entries, options_map,
                                        'SELECT date("2016/11/1") as m')
        self.assertEqual([(datetime.date(2016, 11, 1), )], rrows)
Beispiel #7
0
def test_to_csv(example_ledger):
    types, rows = run_query(example_ledger.all_entries, example_ledger.options,
                            'balances', numberify=True)
    assert excel.to_csv(types, rows)
    types, rows = run_query(example_ledger.all_entries, example_ledger.options,
                            'select account, tags, date, day', numberify=True)
    assert excel.to_csv(types, rows)
Beispiel #8
0
def test_to_csv(example_ledger):
    types, rows = run_query(
        example_ledger.all_entries,
        example_ledger.options,
        'balances',
        numberify=True)
    assert excel.to_csv(types, rows)
    types, rows = run_query(
        example_ledger.all_entries,
        example_ledger.options,
        'select account, tags, date, day',
        numberify=True)
    assert excel.to_csv(types, rows)
Beispiel #9
0
def test_to_csv(example_ledger: FavaLedger) -> None:
    types, rows = run_query(
        example_ledger.all_entries,
        example_ledger.options,
        "balances",
        numberify=True,
    )
    assert excel.to_csv(types, rows)
    types, rows = run_query(
        example_ledger.all_entries,
        example_ledger.options,
        "select account, tags, date, day",
        numberify=True,
    )
    assert excel.to_csv(types, rows)
Beispiel #10
0
    def find_funds(self, price):
        bql = "SELECT flag, filename, lineno, location, account, other_accounts, year, month, day, number, currency where account = \"{}\" and currency = \"CNY\" and number = {}".format(
            FundAccount, price)
        items = query.run_query(self.entries, self.option_map, bql)
        # length = len(items[1])
        feePrice = round(price * fee, 2)

        for item in items[1]:
            current_date = date(item.year, item.month, item.day)
            date_string = current_date.strftime("%Y%m%d")
            print('Updating ' + date_string)
            for fund_item in fund_data:
                if fund_item[0] == date_string:
                    fund_price = float(fund_item[1])
                    count = (price - feePrice) / fund_price
                    self.update_line_to_new_line(
                        item.location,
                        transactionTemplate.substitute(
                            fundAccount=FundAccount,
                            fundCount=round(count, 2),
                            fundCurrency=currency,
                            costPrice=round(fund_price, 5),
                            feeAccount=FeeAccount,
                            fee=feePrice,
                            deviationAccount=DeviationAccount,
                            otherAccount=item.other_accounts[0],
                            originalPrice=price), 1)
def get_allocations(entries, options_map, portfolio):
    allocation_query = r"""
            SELECT currency,
                   GETITEM(CURRENCY_META(currency), "asset-class") as c,
                   GETITEM(CURRENCY_META(currency), "asset-subclass") as s,
                   account,
                   getprice(currency, "{1}", today()) as price,
                   convert(value(sum(position)), "{1}") as market_value,
                   convert(cost(sum(position)), "{1}") as book_value,
                   GETITEM(OPEN_META(account), "asset-class") as act_class,
                   GETITEM(OPEN_META(account), "asset-subclass") as act_subclass
            WHERE GETITEM(OPEN_META(account), "portfolio") = "{0}"
            GROUP BY currency, c, s, account, price, act_class, act_subclass
            """

    target_currency = options_map['operating_currency'][0]
    rtypes, rrows = query.run_query(entries,
                                    options_map,
                                    allocation_query,
                                    portfolio,
                                    target_currency,
                                    numberify=True)

    allocations = Allocations()
    for row in rrows:
        position = _position_from_row(row)
        allocations.append(position)
    return allocations
	def find_duplicate(self, entry, money, replace_account = ''):
		bql = "SELECT flag, filename, lineno, location, account, year, month, day, str(entry_meta('timestamp')) as timestamp, metas() as metas WHERE year = {} AND month = {} AND day = {} AND number = {} AND currency = 'CNY' ORDER BY timestamp ASC".format(entry.date.year, entry.date.month, entry.date.day, -money)
		items = query.run_query(self.entries, self.option_map, bql)
		length = len(items[1])
		if (length == 0):
			return False
		updated_items = []
		for item in items[1]:
			item_timestamp = item.timestamp.replace("'", '')
			if (
				(not 'timestamp' in entry.meta) or
				item_timestamp == entry.meta['timestamp'] or
				item.timestamp == 'None' or
				item.timestamp == ''
			):
				updated_items.append(item)
				if replace_account != '' and item.account in public_accounts:
					self.update_transaction_account(item.location, item.account, replace_account)
				for key, value in entry.meta.items():
					if key == 'filename' or key == 'lineno':
						continue
					if not key in item.metas:
						self.append_text_to_transaction(item.filename, item.lineno, '{}: "{}"'.format(key, value))
				if 'timestamp' in entry.meta and item_timestamp == entry.meta['timestamp']:
					break
		if len(updated_items) > 1:
			for item in updated_items:
				self.update_transaction_flag(item.location, item.flag, '!')
		return len(updated_items) > 0
Beispiel #13
0
def test_to_excel(example_ledger):
    types, rows = run_query(
        example_ledger.all_entries,
        example_ledger.options,
        "balances",
        numberify=True,
    )
    assert excel.to_excel(types, rows, "ods", "balances")
 def find_duplicate(self,
                    entry,
                    money,
                    unique_no=None,
                    replace_account='',
                    currency='CNY'):
     # 要查询的是实际付款的账户,而不是支出信息
     bql = "SELECT flag, filename, lineno, location, account, year, month, day, str(entry_meta('timestamp')) as timestamp, metas() as metas WHERE year = {} AND month = {} AND day = {} AND number(convert(units(position), '{}')) = {} ORDER BY timestamp ASC".format(
         entry.date.year, entry.date.month, entry.date.day, currency, money)
     items = query.run_query(self.entries, self.option_map, bql)
     length = len(items[1])
     if (length == 0):
         return False
     updated_items = []
     for item in items[1]:
         same_trade = False
         item_timestamp = item.timestamp.replace("'", '')
         # 如果已经被录入了,且unique_no相同,则判定为是同导入器导入的同交易,啥都不做
         if unique_no != None:
             if unique_no in entry.meta and unique_no in item.metas:
                 if item.metas[unique_no] == entry.meta[unique_no]:
                     same_trade = True
                 # unique_no存在但不同,那就绝对不是同一笔交易了
                 # 这个时候就直接返回不存在同订单
                 else:
                     continue
         if same_trade:
             return True
         # 否则,可能是不同账单的同交易,此时判断时间
         # 如果时间戳相同,或某个导入器的数据没有时间戳,则判断其为「还需进一步处理」的同笔交易
         # 例如,手工输入的交易,打上支付宝订单号。
         # 另外因为支付宝的傻逼账单,这里还需要承担支付手段更新的功能
         if ((not 'timestamp' in entry.meta)
                 or item_timestamp == entry.meta['timestamp']
                 or item.timestamp == 'None' or item.timestamp == ''):
             updated_items.append(item)
             if replace_account != '' and item.account in public_accounts:
                 self.update_transaction_account(item.location,
                                                 item.account,
                                                 replace_account)
             for key, value in entry.meta.items():
                 if key == 'filename' or key == 'lineno':
                     continue
                 if not key in item.metas:
                     self.append_text_to_transaction(
                         item.filename, item.lineno,
                         '{}: "{}"'.format(key, value))
             # 如果有时间戳,且时间戳相同,则判定为同交易
             # 100%确认是同一笔交易后,就没必要再给其他的「金额相同」的交易加信息了
             if 'timestamp' in entry.meta and item_timestamp == entry.meta[
                     'timestamp']:
                 break
     if len(updated_items) > 1:
         for item in updated_items:
             self.update_transaction_flag(item.location, item.flag, '!')
     return len(updated_items) > 0
Beispiel #15
0
 def test_Lower(self, entries, _, options_map):
     """
     2016-11-20 * "I love candy"
       Assets:Banking       -1 USD
     """
     rtypes, rrows = query.run_query(
         entries, options_map, '''
       SELECT Lower(narration) as m where date = 2016-11-20
     ''')
     self.assertEqual([('i love candy', )], rrows)
Beispiel #16
0
    def test_Coalesce(self, entries, _, options_map):
        """
        2016-11-20 *
          Assets:Banking          1 USD
        """
        rtypes, rrows = query.run_query(
            entries, options_map, 'SELECT COALESCE(account, price) as m')
        self.assertEqual([('Assets:Banking', )], rrows)

        rtypes, rrows = query.run_query(
            entries, options_map, 'SELECT COALESCE(price, account) as m')
        self.assertEqual([('Assets:Banking', )], rrows)

        rtypes, rrows = query.run_query(
            entries, options_map, 'SELECT COALESCE(price, cost_number) as m')
        self.assertEqual([(None, )], rrows)

        rtypes, rrows = query.run_query(
            entries, options_map, 'SELECT COALESCE(narration, account) as m')
        self.assertEqual([('', )], rrows)
Beispiel #17
0
    def query_to_file(self, query_string, result_format):
        """Get query result as file.

        Arguments:
            query_string: A string, the query to run.
            result_format: The file format to save to.

        Returns:
            A tuple (name, data), where name is either 'query_result' or the
            name of a custom query if the query string is 'run name_of_query'.
            ``data`` contains the file contents.

        Raises:
            FavaAPIException: If the result format is not supported or the
            query failed.
        """
        name = "query_result"

        try:
            statement = self.parser.parse(query_string)
        except query_parser.ParseError as exception:
            raise FavaAPIException(str(exception))

        if statement.__class__.__name__ == "RunCustom":
            name = statement.query_name

            try:
                query = next(
                    (query for query in self.queries if query.name == name)
                )
            except StopIteration:
                raise FavaAPIException('Query "{}" not found.'.format(name))
            query_string = query.query_string

        try:
            types, rows = run_query(
                self.ledger.all_entries,
                self.ledger.options,
                query_string,
                numberify=True,
            )
        except (
            query_compile.CompilationError,
            query_parser.ParseError,
        ) as exception:
            raise FavaAPIException(str(exception))

        if result_format == "csv":
            data = to_csv(types, rows)
        else:
            if not HAVE_EXCEL:
                raise FavaAPIException("Result format not supported.")
            data = to_excel(types, rows, result_format, query_string)
        return name, data
Beispiel #18
0
    def test_DateDiffAdjust(self, entries, _, options_map):
        """
        2016-11-20 * "ok"
          Assets:Banking          -1 STOCK { 5 USD, 2016-10-30 }
        """
        rtypes, rrows = query.run_query(
            entries, options_map, 'SELECT date_diff(date, cost_date) as m')
        self.assertEqual([(21, )], rrows)

        rtypes, rrows = query.run_query(
            entries, options_map, 'SELECT date_diff(cost_date, date) as m')
        self.assertEqual([(-21, )], rrows)

        rtypes, rrows = query.run_query(entries, options_map,
                                        'SELECT date_add(date, 1) as m')
        self.assertEqual([(datetime.date(2016, 11, 21), )], rrows)

        rtypes, rrows = query.run_query(entries, options_map,
                                        'SELECT date_add(date, -1) as m')
        self.assertEqual([(datetime.date(2016, 11, 19), )], rrows)
Beispiel #19
0
def test_execute_query(example_ledger):
    query_shell = QueryShell(example_ledger)

    assert query_shell.execute_query("help exit") == (
        QueryShell.noop.__doc__ + "\n",
        None,
        None,
    )

    assert query_shell.execute_query("help")[1:] == (None, None)

    assert query_shell.execute_query("balances")[1:] == query.run_query(
        query_shell.entries, query_shell.options_map, "balances")
Beispiel #20
0
 def find_plac_actuals(self):
     actuals = []
     for account in self.accounts:
         query_str = "select COST(SUM(position)) where account ~ \'" + str(
             account) + "\' and date >= " + str(
                 self.start_date) + " and date <= " + str(
                     self.end_date) + ";"
         query_temp = query.run_query(self.entries,
                                      self.options_map,
                                      query_str,
                                      numberify=True)
         actuals.append(query_temp[1][0][0])
     return actuals
Beispiel #21
0
def test_execute_query(example_ledger):
    query_shell = QueryShell(example_ledger)

    assert query_shell.execute_query('help exit') == \
        (QueryShell.noop.__doc__ + '\n', None, None)

    assert query_shell.execute_query('help')[1:] == \
        (None, None)

    assert query_shell.execute_query('balances', add_to_history=True)[1:] == \
        query.run_query(query_shell.entries, query_shell.options_map,
                        'balances')

    assert query_shell.get_history(1) == ['balances']
def test_execute_query(example_ledger):
    query_shell = QueryShell(example_ledger)

    assert query_shell.execute_query('help exit') == \
        (QueryShell.noop.__doc__ + '\n', None, None)

    assert query_shell.execute_query('help')[1:] == \
        (None, None)

    assert query_shell.execute_query('balances', add_to_history=True)[1:] == \
        query.run_query(query_shell.entries, query_shell.options_map,
                        'balances')

    assert query_shell.get_history(1) == ['balances']
Beispiel #23
0
    def query_to_file(self, query_string, result_format):
        """Get query result as file.

        Arguments:
            query_string: A string, the query to run.
            result_format: The file format to save to.

        Returns:
            A tuple (name, data), where name is either 'query_result' or the
            name of a custom query if the query string is 'run name_of_query'.
            ``data`` contains the file contents.

        Raises:
            FavaAPIException: If the result format is not supported or the
            query failed.
        """
        name = 'query_result'

        try:
            statement = self.parser.parse(query_string)
        except query_parser.ParseError as exception:
            raise FavaAPIException(str(exception))

        if statement.__class__.__name__ == 'RunCustom':
            name = statement.query_name

            try:
                query = next((query for query in self.queries
                              if query.name == name))
            except StopIteration:
                raise FavaAPIException('Query "{}" not found.'.format(name))
            query_string = query.query_string

        try:
            types, rows = run_query(
                self.ledger.all_entries,
                self.ledger.options,
                query_string,
                numberify=True)
        except (query_compile.CompilationError,
                query_parser.ParseError) as exception:
            raise FavaAPIException(str(exception))

        if result_format == 'csv':
            data = to_csv(types, rows)
        else:
            if not HAVE_EXCEL:
                raise FavaAPIException('Result format not supported.')
            data = to_excel(types, rows, result_format, query_string)
        return name, data
Beispiel #24
0
    def test_Subst(self, entries, _, options_map):
        """
        2016-11-20 * "I love candy"
          Assets:Banking       -1 USD

        2016-11-21 * "Buy thing thing"
          Assets:Cash          -1 USD
        """
        rtypes, rrows = query.run_query(
            entries, options_map, '''
          SELECT SUBST("[Cc]andy", "carrots", narration) as m where date = 2016-11-20
        ''')
        self.assertEqual([('I love carrots', )], rrows)

        rtypes, rrows = query.run_query(
            entries, options_map, '''
          SELECT SUBST("thing", "t", narration) as m where date = 2016-11-21
        ''')
        self.assertEqual([('Buy t t', )], rrows)

        rtypes, rrows = query.run_query(
            entries, options_map, '''
          SELECT SUBST("random", "t", narration) as m where date = 2016-11-21
        ''')
        self.assertEqual([('Buy thing thing', )], rrows)

        rtypes, rrows = query.run_query(
            entries, options_map, '''
          SELECT SUBST("(love)", "\\1 \\1", narration) as m where date = 2016-11-20
        ''')
        self.assertEqual([('I love love candy', )], rrows)

        rtypes, rrows = query.run_query(
            entries, options_map, '''
          SELECT SUBST("Assets:.*", "Savings", account) as a, str(sum(position)) as p
        ''')
        self.assertEqual([('Savings', '(-2 USD)')], rrows)
Beispiel #25
0
def main():
    parser = argparse.ArgumentParser(description=__doc__.strip())
    parser.add_argument('filename', help='Beancount input filename')
    parser.add_argument('--from',
                        dest='dfrom',
                        action='store',
                        default='1970-01-01')
    parser.add_argument('--to',
                        dest='dto',
                        action='store',
                        default='2199-01-01')
    args = parser.parse_args()

    # Create a SQL query.
    date_from = parse(args.dfrom).date()
    date_to = parse(args.dto).date()
    sql = """
      SELECT 
        year, month, root(account, 1) as ar, sum(position) as pos
      FROM 
        date > {date_from} AND date < {date_to}
      WHERE 
         account ~ "Expenses" OR
         account ~ "Liabilities:Loans" OR
         account ~ "Income"
      GROUP BY year, month, ar 
      ORDER BY year, month, ar 
      FLATTEN
    """.format(**locals())

    # Load the file and run a query on it.
    entries, _, options_map = loader.load_file(args.filename)
    rtypes, rrows = query.run_query(entries, options_map, sql)

    # Pivot on the year/month + currency
    out = collections.defaultdict(lambda: collections.defaultdict(dict))
    for row in rrows:
        d = out['{}/{:02d}'.format(row.year, row.month)][row.pos.lot.currency]
        d[row.ar] = row.pos.number

    # Write this out to a CSV file.
    wr = csv.writer(sys.stdout)
    for month, currencies in sorted(out.items()):
        for currency, accounts in sorted(currencies.items()):
            exp = accounts.get('Expenses', ZERO)
            loans = accounts.get('Liabilities:Loans', ZERO)
            inc = accounts.get('Income', ZERO)
            wr.writerow((month, exp, loans, -inc, exp + loans - inc, currency))
Beispiel #26
0
 def test_run_query(self):
     rootdir = test_utils.find_repository_root(__file__)
     filename = path.join(rootdir, 'examples', 'example.beancount')
     entries, errors, options_map = loader.load_file(filename)
     assert not errors
     sql_query = r"""
       SELECT
         account,
         SUM(position) AS amount
       WHERE account ~ 'Expenses:'
       GROUP BY 1
       ORDER BY 2 DESC
     """
     rtypes, rrows = query.run_query(entries, options_map,
                                     sql_query, 'Martin',
                                     numberify=True)
     self.assertEqual(['account', 'amount (USD)', 'amount (IRAUSD)', 'amount (VACHR)'],
                      [rt[0] for rt in rtypes])
     self.assertEqual(len(rrows[0]), 4)
Beispiel #27
0
def test_query() -> None:
    assert run_text("help")
    assert (
        run_text("help exit") == "Doesn't do anything in Fava's query shell.")
    assert run("lex select date, balance")[0] == "\n".join([
        "LexToken(SELECT,'SELECT',1,0)",
        "LexToken(ID,'date',1,7)",
        "LexToken(COMMA,',',1,11)",
        "LexToken(ID,'balance',1,13)",
    ])

    assert run_text("run") == "custom_query\ncustom query with space"
    bal = run("balances")
    assert run("run custom_query") == bal
    assert run("run 'custom query with space'") == bal
    assert run("balances")[1:] == run_query(LEDGER.entries, LEDGER.options,
                                            "balances")
    assert (run_text("asdf") ==
            "ERROR: Syntax error near 'asdf' (at 0)\n  asdf\n  ^")
Beispiel #28
0
 def test_run_query(self):
     rootdir = test_utils.find_repository_root(__file__)
     filename = path.join(rootdir, 'examples', 'sharing',
                          'cozumel2015.beancount')
     entries, _, options_map = loader.load_file(filename)
     sql_query = r"""
       SELECT
         PARENT(account) AS account,
         SUM(position) AS amount
       WHERE account ~ 'Expenses.*\b{}'
       GROUP BY 1
       ORDER BY 2 DESC
     """
     rtypes, rrows = query.run_query(entries,
                                     options_map,
                                     sql_query,
                                     'Martin',
                                     numberify=True)
     self.assertEqual(['account', 'amount (USD)', 'amount (MXN)'],
                      [rt[0] for rt in rtypes])
     self.assertEqual(13, len(rrows))
Beispiel #29
0
def load_beancount_file(file_name):
    entries, _, opts = load_file(file_name)
    currency = opts["operating_currency"][0]
    cols, rows = run_query(entries, opts,
                           "SELECT   account,   YEAR(date) AS year,\
                                     MONTH(date) as month,\
                                     SUM(convert(position, '{}', date)) AS amount\
                            WHERE    account ~ 'Expenses'\
                            OR       account ~ 'Income'\
                            GROUP BY account, year, month\
                            ORDER BY account, year, month".format(currency)
                           )
    cols, rows = numberify_results(cols, rows)
    df = pd.DataFrame(rows, columns=[k[0] for k in cols])
    df.rename(columns={"account": "Account", "year": "Year", "month": "Month",
                       "amount ({})".format(currency): "Amount ({})".format(currency)}, inplace=True)
    df = df.astype({"Account": str, "Year": int, "Month": int, "Amount ({})".format(currency): np.float})
    df = df[["Account", "Year", "Month", "Amount ({})".format(currency)]].fillna(0)
    df["YearMonth"] = df.apply(lambda x: "{}-{:0>2d}".format(x["Year"], x["Month"]), axis=1)
    df = df[["Account", "YearMonth", "Amount ({})".format(currency)]]
    os.remove(file_name)
    return df
Beispiel #30
0
 def query_func(self, sql):
     entries, _, options_map = loader.load_file(self.f)
     rtypes, rrows = query.run_query(entries, options_map, sql)
     return rtypes, rrows
Beispiel #31
0
def save_query(title,
               participant,
               entries,
               options_map,
               sql_query,
               *format_args,
               boxed=True,
               spaced=False,
               args=None):
    """Save the multiple files for this query.

    Args:
      title: A string, the title of this particular report to render.
      participant: A string, the name of the participant under consideration.
      entries: A list of directives (as per the loader).
      options_map: A dict of options (as per the loader).
      sql_query: A string with the SQL query, possibly with some placeholders left for
        *format_args to replace.
      *format_args: A tuple of arguments to be formatted into the SQL query string.
        This is provided as a convenience.
      boxed: A boolean, true if we should render the results in a fancy-looking ASCII box.
      spaced: If true, leave an empty line between each of the rows. This is useful if the
        results have a lot of rows that render over multiple lines.
      args: A dummy object with the following attributes:
        output_text: An optional directory name, to produce a text rendering of
          the report.
        output_csv: An optional directory name, to produce a CSV rendering of
          the report.
        output_stdout: A boolean, if true, also render the output to stdout.
        currency: An optional currency (a string). If you use this, you should
          wrap query targets to be converted with the pseudo-function
          "CONV[...]" and it will get replaced to CONVERT(..., CURRENCY)
          automatically.
    """
    # Replace CONV() to convert the currencies or not; if so, replace to
    # CONVERT(..., currency).
    replacement = (r'\1' if args.currency is None else
                   r'CONVERT(\1, "{}")'.format(args.currency))
    sql_query = re.sub(r'CONV\[(.*?)\]', replacement, sql_query)

    # Run the query.
    rtypes, rrows = query.run_query(entries,
                                    options_map,
                                    sql_query,
                                    *format_args,
                                    numberify=True)

    # The base of all filenames.
    filebase = title.replace(' ', '_')

    fmtopts = dict(boxed=boxed, spaced=spaced)

    # Output the text files.
    if args.output_text:
        basedir = (path.join(args.output_text, participant)
                   if participant else args.output_text)
        os.makedirs(basedir, exist_ok=True)
        filename = path.join(basedir, filebase + '.txt')
        with open(filename, 'w') as file:
            query_render.render_text(rtypes, rrows, options_map['dcontext'],
                                     file, **fmtopts)

    # Output the CSV files.
    if args.output_csv:
        basedir = (path.join(args.output_csv, participant)
                   if participant else args.output_csv)
        os.makedirs(basedir, exist_ok=True)
        filename = path.join(basedir, filebase + '.csv')
        with open(filename, 'w') as file:
            query_render.render_csv(rtypes,
                                    rrows,
                                    options_map['dcontext'],
                                    file,
                                    expand=False)

    if args.output_stdout:
        # Write out the query to stdout.
        query_render.render_text(rtypes, rrows, options_map['dcontext'],
                                 sys.stdout, **fmtopts)
def get_spending(entries, options_map, min_date):
    query_rows = query.run_query(entries, options_map, QUERY, numberify=True)
    spending = [(datetime.date(n[0], n[1], 1), float(n[2])) for n in query_rows[1]]
    filtered = [n for n in spending if n[0] >= min_date]
    return pandas.Series(dict(filtered))
Beispiel #33
0
 def query_func(self, sql):
     rtypes, rrows = query.run_query(self.entries, self.options_map, sql)
     return rtypes, rrows
Beispiel #34
0
 def query(self, bql_query_string):
     return query.run_query(self.entries, self.options, bql_query_string)
Beispiel #35
0
 def query(self, query_string, numberify=False):
     return query.run_query(self.all_entries, self.options,
                            query_string, numberify=numberify)
Beispiel #36
0
 def query(self, bql_query_string):
     return query.run_query(self.entries, self.options, bql_query_string)