You are here: Leader.IT>MasonSQL Web>ReportComponent (05 Apr 2017, JanezStangelj)Edit Attach

Report Component

The report.pdf component receives a command line parameters that are passed on to the Reportman or to the ODT Report library which in turn generate a report.
The decision on which utility to use is based on suffix of the report variable. If there is no suffix the utility is chosen based on value of the DefaultReportEngine variable from the Apache config file.
The report is originally passed to the printSelected() function.

On this page:

Input Arguments and Configuration Parameters

The report.pdf component prepares a command line options that are passed to a Reportman scripts and prints the produced report file to the specified output.

Argument Name or Parameter Name	Description	Default Value
`$GROUP_ID`	Is used by the Reportman to select the records from which to generate the report.	Not defined.
`$report_ids`	Is used by the ODT Reportman to select the records from which to generate the report.	Not defined.
`$base`	The directory that contains the Reportman configuration files (`.rep`).	`$r->dir_config('DataBaseUrl')`
`$cups_printer`	If defined, instead of sending the file with `$m->out(...)`, send the file to the indicated Cups printer. The Cups printer is called with the commandline: `/usr/bin/lp -d $cups_printer $PDF`	Not defined.
`$path_pdf`	The filepath of the output report file in `.pdf` format.	`$r->dir_config('TmpDir')."/report.$$.pdf"`
`$report`	The file name of the Reportman configuration file without extension.	Not defined.
`$send_pdf`	If Cups printer is not defined print the produced report file to output using `$m->out(...)` If both `$cups_printer` and `$send_pdf` are not defined the report file is produced but is not printed.	`1`
`$ARGS{PROTECT}`	If this option is defined the produced output report file is encrypted. The commandline that encrypts the report file is: `/usr/bin/qpdf --linearize --encrypt $ARGS{PROTECT} 40 --modify=n --annotate=n -- $file_orig $PDF`	Not defined.
`$ARGS`	Each of the remaining parameters is passed to the Repotman as the `-paramParamName=<param_value>` option or to the ODT Reportman as the `param_name='param_value'` option.

ODT Reportman Installation

These instructions for installation were checked on Ubuntu Precise (12.04) and Ubuntu Trusty (14.04) with downgrade to Apache 2.2

Packages

These packages are prerequisite for the ODT Report Manager:

apt-get -y install libxml-simple-perl
cpan XML::Simple::Sorted
cpan IPC::Run::SafeHandles
cpan Text::Balanced

Perlbrew

Because of ODF::lpOD incompatibility with the Perl version 5.14, used by MasonSQL in some deployments, the Perlbrew must be installed to use a newer version of Perl.
See the MasonSQL Configuration Matrix.

To install Perlbrew, with all the necessary libraries, run following perlbrew-deploy.sh script as a root user:

sudo /opt/masonsql/utility/perlbrew-deploy.sh

Libreoffice

# Proceed with root privileges.
sudo su -

# Install latest System.LibreOffice (5.0 or later).

# Ubuntu Precise (12.04)
apt-get install python-software-properties
# Ubuntu Trusty (14.04)
# apt-get install software-properties-common

sudo add-apt-repository ppa:libreoffice/ppa
sudo apt-get update
apt-get install openjdk-9-jre libreoffice-writer
# Se si utilizzano anche i file ODS
apt-get install libreoffice-calc
# Install pdftk.
apt-get install pdftk

Unoconv Daemon

Unoconv can be used by ODT Report Manager to convert .odt files to .pdf format.
The OdtConvEngine environment variable must be configured with "OdtConvEngine" as an alternative to "libreoffice".

# Proceed with root privileges.
sudo su -
# Install latest Unoconv.
cd /tmp
apt-get install git
git clone https://github.com/dagwieers/unoconv
cd unoconv/
make install
cd ../
rm -rf unoconv/

# Ubuntu Trusty (14.04) - You must use python3
sed -i~ 's/^#!\/usr\/bin\/env python$/#!\/usr\/bin\/env python3/' /usr/bin/unoconv

# Install and start Unoconv daemon.
ln -s /opt/masonsql/etc/unoconvd /etc/init.d/unoconvd
update-rc.d  unoconvd defaults
service unoconvd start

Tables

The queries for creating the ODT tables are in the /opt/masonsql/utility/sql/odt_report_tables.mql file.

Before applying the queries the owner name postgres must be changed to the new owner name my_db_owner_name.

Configuration

ODT Report Manager uses a couple of Apache configuration variables.
Following is an example configuration in the /opt/my_app_name/etc/apache2.my_app_name-devel.my_domain_name.conf file:

# Path of this apache configuration file
PerlSetVar ConfigPath /opt/my_app_name/etc/apache2.my_app_name-devel.my_domain_name.conf

# Temporary directory
PerlSetVar TmpDir   /opt/my_app_name/tmp

# Default report engine configuration: 
# rep = old Report Manager
# odt = new ODT Report printed in odt format
# pdf = new ODT Report printed in pdf format
PerlSetVar DefaultReportEngine "pdf"

# ODT Reportman's access to DB
PerlSetVar ReportDBIconnect "dbi:Pg:dbname=my_db_name;user=report;password=MyReportPassword"

# Archive for templates
PerlSetVar InputFilesArchive "/opt/my_app_name/archive"

The InputFilesArchive is used by Files Component. The component stores its files in the sub-directories which must be created:

cd /opt/my_app_name/archive
mkdir public
chown www-data:www-data public
mkdir odt_reports
chown root:www-data odt_reports
chmod 775 odt_reports

Without correct access permissions for the application's Apache config file the ODT Report Manager returns an error. Example of correctly set permissions:

-rw-r----- 1 root www-data 10182 Aug 19 12:13  /opt/my_app_name/etc/apache2.labs.leader.it.conf

In order to apply changes, Apache configuration file must be reloaded:

/etc/init.d/apache2 reload

Functions and Permissions

Example setup of the ODT Reportman's permissions:

ODT Reportman Library

ODT Reportman library produces a MasonSQL reports using an .odt files as templates. The template files are created with the LibreOffice's application.
The reports are configured in public.odt_reports table which can be managed by users with ODT Reports web interface.

Permissions

To access a report an user must first add the report to the public.odt_reports table. For application specific reports the name of the report must also have app_schema_name. prefix.
The report permission is checked with the Permission() subroutine.

How to Correctly Configure Report Name

Once a report is prepared, it can be called from the MasonSQL application with [Stampa] or [St.sel] buttons or with customarily created buttons. The buttons must then call the report with printSelected() function, which triggers the generation of the report. The application composes the report name by using the PRINT_FORM method in combination with parameters passed to the printSelected() function. The report name is then searched in the database and report is generated from the provided input parameters. The report name is composed in the following order:

The first part is the first parameter of the PRINT_FORM.
The second part is the report parameter passed to the printSelected() function.
In case the report parameter contains a suffix that indicates the report method, the suffix is excluded from the report name.

The application additionally checks the suffix, of the report parameter, to determine which report method to use. The only possible report methods are pdf, odt and rep. In consequence the application will report an error if it finds any other suffix after the dot character; Thus in case the report parameter contains dots the suffix after the last dot must be a valid report method. The report method suffix will not get included in the report name.

Stampa and St.sel Buttons Example

When the report is named after the corresponding menu, the [Stampa] and [St.sel] buttons should automatically become enabled. The user must also have the Print and PrintSel permissions.

For example for the public.anagrafiche table it is sufficient to set the name of the report to anagrafiche:

Consequently in the Config->Anagrafiche menu has [St.sel] and [Stampa] buttons enabled:

Custom Button Example

The example button directly calls printSelected() subroutine with sel type and report name concatenated with .pdf suffix:

<button onclick="<%$Display%>.printSelected('sel', '_my_report.pdf');">Example</button>

The report name could be complete report name but also it can be part of the report name. When the PRINT_FORM is defined its first argument can additionally define schema_name/table_name:

<%method PRINT_FORM>my_schema/my_table, PORTRAIT, PARAM1=3 %></%method>

In case of such PRINT_FORM definition, the actual report name in the public.odt_reports table must be my_schema.my_report.

Reference

The ODT Report POD reference is available.

ODT Reportman User's Guide

In this guide it will be explained the usage of the ODT Report web form using an example report. In the example it will be described the composition of the report for the Config->Gruppi form from the MasonSQL framework. Previous Reportman report exists for this form, so the new ODT version can be compared to the old Wine version. Final report in the example will look like this: gruppi.pdf

Association With Table

In order to use the report, the Report Name field must have a correct name. In this example the report is intended for the public.gruppi table and the default PRINT_FORM is used, so its sufficient to name the report gruppi. See: How to Correctly Configure a Report Name for more comprehensive explanation.

Queries

The relationships of the MasonSQL framework will be used to compose a report that will print an authorized privileges for each of the functions belonging to the selected groups; Additionally it will print a list of users belonging to the selected groups.

Selected Groups

The command-line parameter ARGV.REPORT_ID determines the parameter used to select the data to print. It may coincide with the record ID of the table gruppi (but in other cases also with a parameter to be passed to a database function).

Here's the father query:

select 
  gruppi.* 
from gruppi
where  {{ ARGV.REPORT_ID }} > 0 and  gruppi.id = {{ ARGV.REPORT_ID }}

The command-line parameter REPORT_ID is provided by the framework although its test value can be set in the Test parameters: input field:

The result of the query contains information about selected groups which is saved to the DTL context.

 id |    nome    |        descrizione        | commento
----+------------+---------------------------+----------
  5 | Gas_admins | Gruppo amministratori GAS |
(1 riga)

Authorized Privileges

The first sub-query must provide an authorized privileges of each function belonging to the selected group. The group ID is passed to the sub-query with the DTL tag {{ G.id }}. G reference is declared in the Portion Name of the portion. The tag is rendered before the query gets executed. The query marks each authorized privilege of a function with capital X:

select distinct funzioni.nome , 
   (select distinct
      case 
        when id_autorizzazioni is not null
        then 'X' else ''
      end
    from   gruppi_funzioni gf
    where  gf.id_funzioni = gg.id_funzioni and
           gf.id_autorizzazioni = 1) as m_menu,
   (select distinct
      case 
        when id_autorizzazioni is not null
        then 'X' else ''
      end
    from   gruppi_funzioni gf
    where  gf.id_funzioni = gg.id_funzioni and
           gf.id_autorizzazioni = 2) as m_select,
   (select distinct
      case 
        when id_autorizzazioni is not null
        then 'X' else ''
      end
    from   gruppi_funzioni gf
    where  gf.id_funzioni = gg.id_funzioni and
           gf.id_autorizzazioni = 3) as m_update,
   (select distinct
      case 
        when id_autorizzazioni is not null
        then 'X' else ''
      end
    from   gruppi_funzioni gf
    where  gf.id_funzioni = gg.id_funzioni and
           gf.id_autorizzazioni = 4) as m_delete,
   (select distinct
      case 
        when id_autorizzazioni is not null
        then 'X' else ''
      end
    from   gruppi_funzioni gf
    where  gf.id_funzioni = gg.id_funzioni and
           gf.id_autorizzazioni = 5) as m_print,
   (select distinct
      case 
        when id_autorizzazioni is not null
        then 'X' else ''
      end
    from   gruppi_funzioni gf
    where  gf.id_funzioni = gg.id_funzioni and
           gf.id_autorizzazioni = 6) as m_printsel,
   (select distinct
      case 
        when id_autorizzazioni is not null
        then 'X' else ''
      end
    from   gruppi_funzioni gf
    where  gf.id_funzioni = gg.id_funzioni and
           gf.id_autorizzazioni = 7) as m_insert
from funzioni
inner join gruppi_funzioni gg on gg.id_funzioni = funzioni.id
inner join autorizzazioni on gg.id_autorizzazioni = autorizzazioni.id
where gg.id_gruppi= {{ G.id }}
order by funzioni.nome

Group Members

The second sub-query must provide a list of group members. The group ID is also the same DTL tag {{ G.id }} which is passed to the sub-query from the father query:

select 
    anagrafiche.*
from 
    anagrafiche, anagrafiche_gruppi ag
where ag.id_gruppi = {{ G.id }}
and ag.id_anagrafiche = anagrafiche.id
order by  nome;

Template

The base template document has to be defined to provide templates of the portions that will be used by the report. The DTL tags are put where the field values will be in the printed report. This newly created base template document gruppi.odt must then be attached to the report and the relative path to the document must be added in the Filename field of the corresponding base template portion:

The planned structure of the example ODT report has:

A page header;
A father portion containing information about each printed group;
A child portion containing a detailed list of an authorized privileges for the group;
And a child portion containing a list of users that belong to the group.

Page Header and Page Footer

A page header contains the text that will show on the top of each printed page. For the easier positioning of the paragraphs the table is created inside the page header. The date field is also added at the right side of the page header. Note that all of the paragraphs are rendered with the values of the command-line parameters and the values from the first row of the base template query. Except paragraphs containing the text fields - paragraphs containing the text fields are not rendered.

Father-Child Portions

Defining relations:

In order to use a field value, from the query result, in a DTL tag - the Portion Name must be added. The corresponding DTL tag can be used in the gruppi.odt as:
```
{{ my_portion_name.my_field_name }}
```
In order to use a portion content - its name in the Obj.Reference field must correspond to its section or table name in the gruppi.odt.

Because the report basically uses two different tables, it can be composed with a combination of a father section and two children tables. The father section can be without Obj.Reference since a group information can be printed in the TabellaGruppo table header. The father section prints both child portions, a pair for each group from the father query.

Here's the described father-children configuration:

Here's the content of the gruppi.odt:

It is acceptable to add notes to the document since all of them are removed from the report.

Report Fields

By looking at the previous example it may be noticed that some of the DTL tags don't fit very well to the dimensions of the cells. This can be solved by replacing misfit DTL tags with so called simple variables which are additionally mapped to rows in the Report Fields table. This mapping makes possible other practical uses as well. While the standard usage of the ODF variables in the ODT template documents remains intact.

A simple variable (opposed to an user variable) can be added to the document template by choosing:
[Insert] --> [Field] --> [More Fields] --> [Variables] --> [Set variable] or by pressing CTRL+F2.

Available types of a simple variable are:

string
float
currency
percentage
boolean
date
time

All of the available types support rendering within the ODT Reportman. With one limitation - the limitation is that the date and time types are converted internally, by the ODT Reportman, to the string type. For additional information about ODT variables see also: ODT Variables User's Guide.

A DTL tag can be utilized in a simple variable in two ways. Firstly, it can be added to the variable name. In such a case the variable content is rendered directly with DTL tag value. For example like this:

{{ AZ.m_menu }}

Using the DTL tag name as the variable name in the gruppi.odt template:

Secondly, one or multiple DTL tags can be added indirectly in the Value field of the Report Fields table. The following example shows mapping of the variable name to the Value string containing only one DTL tag:

The mapped variable added to the ODT report template document:

The above mentioned limitation is compensated with additional Formatting field in the Report Fields table. The Formatting field can be applied to date, time and even to string variable types. The field is used as a formatting string for the sprintf statement. An example of a formatting string of a string type variable is:

{% if F.d.0 %}&euro; %.2f{% endif %}

In case the Formatting field is rendered as an empty string, the variable's value is equal to the content of the Value field thus the sprintf is not used. This last fact is useful with the string type variables: When the Value and the Formatting fields are rendered empty the variable value will become empty string, which is not possible to achieve with other types of variables.

Report Manager

Use of Report Manager (Reportman in short) with MasonSQL is deprecated.

Report Manager currently supports Wine version, Kylix version is deprecated, see Reportman Configuration. To use this utility use the .rep suffix for the report name.

http://reportman.sourceforge.net/ Report Manager home page

Installation

Input Arguments and Configuration Paramerters

The report.pdf component prepares a commandline options that are passed to the Reportman and prints the produced report file to the specified output.
The component also updates SERVER, DATABASE, USER and PASSWORD values in the ADOConnectionString from the .rep configuration file with values from the Apache configuration file.

report.pdf Reference

The report.pdf POD reference is available.

Topic revision: r19 - 05 Apr 2017, JanezStangelj

MasonSQL