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 on_Select(self, statement):
"""
Extract data from a query on the postings.
The general form of a SELECT statement loosely follows SQL syntax, with
some mild and idiomatic extensions:
SELECT [DISTINCT] [<targets>|*]
[FROM <from_expr> [OPEN ON <date>] [CLOSE [ON <date>]] [CLEAR]]
[WHERE <where_expr>]
[GROUP BY <groups>]
[ORDER BY <groups> [ASC|DESC]]
[LIMIT num]
Where:
targets: A list of desired output attributes from the postings, and
expressions on them. Some of the attributes of the parent transaction
directive are made available in this context as well. Simple functions
(that return a single value per row) and aggregation functions (that
return a single value per group) are available. For the complete
list of supported columns and functions, see help on "targets".
You can also provide a wildcard here, which will select a reasonable
default set of columns for rendering a journal.
from_expr: A logical expression that matches on the attributes of
the directives (not postings). This allows you to select a subset of
transactions, so the accounting equation is respected for balance
reports. For the complete list of supported columns and functions,
see help on "from".
where_expr: A logical expression that matches on the attributes of
postings. The available columns are similar to those in the targets
clause, without the aggregation functions.
OPEN clause: replace all the transactions before the given date by
summarizing entries and transfer Income and Expenses balances to
Equity.
CLOSE clause: Remove all the transactions after the given date and
CLEAR: Transfer final Income and Expenses balances to Equity.
"""
# Compile the SELECT statement.
try:
c_query = query_compile.compile(statement,
self.env_targets,
self.env_postings,
self.env_entries)
except query_compile.CompilationError as exc:
print('ERROR: {}.'.format(str(exc).rstrip('.')), file=self.outfile)
return
# Execute it to obtain the result rows.
rtypes, rrows = query_execute.execute_query(c_query,
self.entries,
self.options_map)
# Output the resulting rows.
if not rrows:
print("(empty)", file=self.outfile)
else:
output_format = self.vars['format']
if output_format == 'text':
kwds = dict(boxed=self.vars['boxed'],
spaced=self.vars['spaced'],
expand=self.vars['expand'])
if self.outfile is sys.stdout:
with self.get_pager() as file:
query_render.render_text(rtypes, rrows,
self.options_map['dcontext'],
file,
**kwds)
else:
query_render.render_text(rtypes, rrows,
self.options_map['dcontext'],
self.outfile,
**kwds)
elif output_format == 'csv':
# Numberify CSV output if requested.
if self.vars['numberify']:
dformat = self.options_map['dcontext'].build()
rtypes, rrows = numberify.numberify_results(rtypes, rrows, dformat)
query_render.render_csv(rtypes, rrows,
self.options_map['dcontext'],
self.outfile,
expand=self.vars['expand'])
else:
assert output_format not in _SUPPORTED_FORMATS
print("Unsupported output format: '{}'.".format(output_format),
file=self.outfile)