ODT (OpenDocument Text) Report Manager

---

The MasonSQL framework provides for a report generator in the ODF and PDF formats.

The data to be included in the report will be extracted from the database through queries.
A report may be assembled with more portions (text, tables, documents..) used only once or repeated based on the number of rows returned by the query.

A report is then defined by a list of portions to be assembled with the data, which are extracted in the defined order using a template as a model for the generation of content.

Report portions

A report is subdivided in portions (for instance, the header, head, detail, and footer).
Moreover, the report has a page header and a footer.

A portion of a report may use various model types, used to generate the block of ODT (text, tables, images...) objects.

A portion of a report may not define a model; useful in the case when you want to define a data query to be used in successive portions.

A portion of a report may also not define a query; in this case, the model will only be applied once.

One or more child portions can refer to a father portion.
The generation of child portions are repeated by the number of rows of the data returned by the father portion's query.
A father portion must be declared before child portions.

During the generation process, the parameters and the data resulting from the preceding processed query are visible in the model and in the query of the sucessive portion.
The data from the query of child portions are not visible from the father portion, much less from portions at the same level of the father.
Every time a row of data from a portion processed (resulting from the query), the data from the current row will be available, and not from the preceding rows.

Django Template Language

In ODT documents and in queries, DTL is used; which is a specific language for processing of templates (template processing), very versatile and widespread in various environments, such as Perl and Dojo Toolkit, which are both used in MasonSQL.

The report generator uses the Perl DTL::Fast library which implements specific parts of the DTL language.

With respect to official implementation, there are some compatibility issues and extensions that we cite only for some sophisticated users of the language.

In DTL, the characters {{ and }} are used to delimit the text to be interpreted.
For instance, in order to substitute a var variable, you would write {{ var }} in the template or in the query.

The variables present in DTL can also be regrouped in a hierarchical fashion, using hashes and arrays.
For instance, in order to refer to an element of a hash, you would write {{ myhash.mykey }} wherein myhash is the name of the hash and mykey is the name of the key.

Parameters

The report generator accepts the command line of parameters that can be used in templates and in queries as variables in DTL.

The parameters are inserted in a hash named ARGV (ARGument Variables) which will be visible in all templates and queries.

For instance, if you use the generator with the following parameters, param1=100 MyPAR='my string data', they will be available in DTL as {{ ARGV.param1 }} and {{ ARGV.MyPAR }}.

Query

Each portion of the report may define a query in SQL with which, will retrieve data from the database.

In the query, we can insert the DTL constructs, which are processed before in every execution to extract the data table.

For each row of data retrieved, the portion using the template will be generated (if present), rendering visible the data from the columns with DTL variables.

The data from each row will be visible in DTL as hash; for instance, if the portion is named HEAD, the data from the row will be of the type {{ HEAD.var }}.

In case of fields of JSON type, the field content will be converted to hash or array so it will be possible to refer to the underlying data like {{ HEAD.myhash.myfield }} {{ HEAD.myarray[5] }}

The DTL hash will have additional entry ROW_NUMBER which will tell the recordset row number, staring with 1. For example the tag {{ HEAD.ROW_NUMBER }} will be replaced by the current row number.

The hash, thusly defined, will also be visible in the successive processing of the queries and the templates of portions at the same level or higher.

Images

It will be possible to render images. The images can be uploaded to the SQL table, using bytea format.

An image in the portion will be rendered only when its name contains a DTL tag. The format of the name will be: #<portion_name>.<image_field_name>. The images without DTL tag will be ignored.

Rendered template images will be replaced with the appropriate images from the database.

Format of the image names, added to the report, will be: <portion name>_<image name>_<row number>.<suffix>.

Image MIME will be detected and added to the image attributes.
The image suffix will be determined by the MIME type of the image.
Frame size will remain unchanged after rendering.

Integrated configurator in MasonSQL

In the [Config] menu, the [ODT Reports] form is available, with which it is possible to define reports providing the templates used for assembling and defining the portions with the relative data extraction queries for printing.

Two buttons in the form are present: (Test ODT and Test PDF), with which reports are generated and then downloaded in the ODT or PDF formats using the parameters indicated in the Test parameters field.
For the parameter syntaxes, please refer to OdtReport#Parameters .
The generation in the ODT format does not include eventual portions in PDF type (files and attached reports) that are ignored.

The files of templates are attached to the reports table (public.odt_reports) and can be downloaded thusly from the form. Every report have a separate archive of files, eventually organized in subforders.
If files are saved in a sub folders, the file name reference must be complete with the relative path of the sub folder.

Definition of terms

term	description
template	generic document containing the particulars of structure or graphics in which information is entered using references to external data
print template	file in the ODT format used as basis for constructing the document
section	part of an ODT document countersigned marked with a name
portion	portion of report, generated using a section, table, or an ODT document
report	ODT formated document
DTL	Django Template Language; used to insert data in templates and in queries
data	data can be provided from the report generator as parameters (at the command line) or as result from queries
query	Instructions in the SQL format to extract data from the database. The query main contain constructs in the DTL format

Implementation details

To save the definitions and the templates of the reports, two tables are used:

table name	description
public.odt_reports	list of reports
public.odt_report_portions	list of report portions

The tables are editable using the Integrated configurator in MasonSQL.

Tables

public.odt_reports Table

field name	description
id	primary key
name	report name
description	brief report description
comment	comment/long report description
cmd_parameters	field used by the configurator to send the parameters to the generator for test prints. The format is like "param1=number param2='my string' "

public.odt_report_portions Table

field name	description
id	primary key
ord	order of processing
id_odt_report	referring to the report’s primary key
name	portion name
id_father	primary key of father portion
type	portion type
file_name	file name of template or of file where in the object used as template is present (also indicate the path where the file was saved in a subfolder or report archive)
obj_ref	reference name of object used as template
query	SQL query

Report portion types

Based on the compilation of the public.odt_report_portions table, the portions to be processed will be determined based on the following schema that stabilises the values by attributing the type, file_name, and obj_ref fields:

type	description of portion	file_name	obj_ref
base_report	document template to be used as basis	ODT file name
odt_section	ODT section	ODT file name from which the section is copied	section name
odt_table	ODT table	ODT file name from which the section is copied	table name
odt_file	file in ODT format	ODT filename
pdf_file	file in PDF format, to be attached	PDF file name
pdf_report	report to be attached, in PDF format		report name

The file_name and obj_ref fields may contain DTL constructs, so it is possible to make the sources from which to download templates and attachments parametric.

The query of the portion is valued first; then, the file_name and obj_ref fields for each row returned by the query. In such way it will be possible to change the template, row by row.

base_report portion type

This indicates an ODT document template to be used as basis for report generation.
The possible contents of the file are ignored.
The base document may contain some DTL constructs, useful for processing the page header and footer but also any other objects of the base document.
This portion must be defined before the ODT type portions (odt_section, odt_table and odt_file) because it is used as a container for them.
The portion must be omitted if the report only contains PDF type portions (pdf_file and pdf_report) .
If objects coming from other ODT documents are included, the styles applied will be those defined in the base document, defined here.

odt_section portion type

The portions correspond to an ODT section present in the file, identified with its own name.
If the odt_file field is undefined, the section will be read from the base_report template file.
The styles applied will be from those defined in the base document, and not those defined in any indicated file.

• Using Sections in Libreoffice

odt_table portion type

The portion corresponds to an ODT table present in the file, identified with its name.
If the odt_file field is undefined, the section will be read from the base_report template file.
The styles applied will be those defined in the base document, and not those defined in any indicated file.

• Inserting Tables in Libreoffice

odt_file portion type

The portion is constituted by an ODT file.
The DTL interpretation will be applied to the content.
The styles applied will be those defined in the base document, and not those defined from the indicated file.

pdf_file portion type

It is attached as a PDF file without any transformation.

pdf_report portion type

The report to be attached (of type ODT) is generated providing the fields resulting from the query of the portion (only the data from the first row) as parameters for the DTL generator.

The generated report is then converted to PDF format and attached.

Automatic RSID marks bug in Libreoffice

Unfortunately, ODT documents generated with Libreoffice suffer from a bug that causes the proliferation of SPAN elements even if different formatting attributes are not included, compared to the adjacent text.

The problem is documented here:

• https://bugs.documentfoundation.org/show_bug.cgi?id=68183&redirected_from=fdo

The following XML code shows the problem, which prevents the parser from interpreting DTL constructs correctly:

<style:style style:name="T5" style:family="text"><style:text-properties officeooo:rsid="00260f63"/></style:style>
. . . .
<text:p text:style-name="P9">{{ <text:span text:style-name="T5">Ordini</text:span>.stato_ordine }}</text:p>

Prior to applying the DTL parser, it is then necessary to verify if there are no text:span elements with style containing only officeooo:rsid properties which will be deleted prior to proceed with the parsing.

Example

We will use the relationships of the application from the example made in the AddingChildFrame guide which uses the tables defined in NewTableWithEmptyMqlFile to compose a report that will print the list of employees with its list ordered, with the relative prices and commissions.

The said tables are demo.employees, demo.orders, and demo.commissions.

If the report is recalled with the ID_EMPLOYEE parameter, a single employee will be printed with that primary key.

Thus, we must define two queries:

• one to list the employees:

SELECT
  id,
  name,
  surname,
FROM demo.employees
WHERE {% if not defined ARGV.ID_EMPLOYEE %}true{% endif %} or id = '{{ ARGV.ID_EMPLOYEE }}'::integer;

We will limit the list in case the ARGV.ID_EMPLOYEE would be defined (variable supplied to command line) to a single employee that has the id field equal to the variable.

• and another to list the orders made by an employee:

SELECT
  price,
  type,
  percentage as perc
FROM demo.orders
INNER JOIN demo.commissions ON commissions.id = orders.commission
WHERE orders.employee = {{ EB.id }};

We will limit the list using the EB.id variable that corresponds to the id field (employee's primary key) from the first query; because we will call the report portion corresponding to the first query with the name EB (Employee body).

Assuming that a sample report in the public.odt_reports table has been defined with the following values:

id	name	description	comment	cmd_parameters
23	demoreport	example of report	no comment	ID_EMPLOYEE=3

and a file named my_templates.odt has been loaded with the templates, the portions table will assume the following values:

id	ord	id_odt_report	name	id_father	type	file_name	obj_ref	query
18	1	23			base_report	my_templates.odt
19	2	23			odt_section		employees_header{% defined ARGV.ID_EMPLOYEE %}_SINGLE{% endif %}
14	3	23	EB		odt_section		employees_body	SELECT id, name, surname, FROM demo.employees WHERE {% if not defined ARGV.ID_EMPLOYEE %}true{% endif %} or id = '{{ ARGV.ID_EMPLOYEE }}'::integer;
21	4	23		14	odt_section		orders_header
22	5	23	OB	14	odt_section		orders_body	SELECT price, type, percentage as perc FROM demo.orders INNER JOIN demo.commissions ON commissions.id = orders.commission WHERE orders.employee = {{ EB.id }};
23	6	23		14	odt_section		orders_footer
24	7	23			odt_section		employees_footer

The sections in the ODT file may be populated thusly:

employees_header section:

=================================================
       List orders divided per employee
=================================================

Sezione employees_header_SINGLE:

=================================================
           Orders of an employee
=================================================

employees_body section

  Employee {{ EB.name }} {{ EB.surname }}

orders_header section

+---------------+--------------+----------------+
|     price     | % commission |     type       |

orders_body section

+---------------+--------------+----------------+
| {% sprintf "%13.2f" OB.price %} | {% sprintf "%12.2f" OB.perc %} | {% sprintf "%13s" OB.type %}  |

orders_footer section

+---------------+--------------+----------------+
                                            [{% sprintf '%3i' EB.id %}]

employees_footer section

=================================================

To obtain a similar report to the following, assuming the generator is called with the ID_EMPLOYEE=3 parameter:

=================================================
           Orders of an employee
=================================================

  Employee Ted Danson
+---------------+--------------+----------------+
|     price     | % commission |     type       |
+---------------+--------------+----------------+
|       12.50   |      12%     |        Breads  |
+---------------+--------------+----------------+
|      230.00   |      20%     |        Drinks  |
+---------------+--------------+----------------+
|      345.33   |      10%     |      Prepared  |
+---------------+--------------+----------------+
                                            [  3]

=================================================