Generating And Sending Reports By an Email On a Schedule (Any Datasource)

Overview

In this tutorial you will learn how to generate reports and send them by an email on a regular basis. MySQL database will be used as a datasource, but it is possible for all supported datasources.

The best way to achieve this goal is to use the Valentina Server, because:

It can send emails
It includes Valentina Reports engine
It is able to attach generated reports to the emails
It has a built-in event scheduler to perform all of these tasks regularly or at the fixed time in the future

During this tutorial you will go through the following steps:

Prepare the Valentina Server for email sending and event scheduling
Send email using the Valentina Server
Prepare a database
Prepare a project
Prepare a datasource
Prepare a source query
Generate a report via SQL
Create a new event which generates a report and sends it

Requirements

The following applications should be already installed:

Valentina Server
MySQL Server
Valentina Studio

Access to the SMTP server is required. It may be some third-party service or your local server, like mail server from OS X Server package, which was used to prepare this tutorial.

Step 1: Prepare the Valentina Server

Mailing and scheduling features are disabled on the server by default, so the first thing to do is to check them.

Start Valentina Studio
Connect to the Valentina Server. You can click a Bonjour connection on the Start Page or on the left panel of the Schema Editor.

Also you can create a new connection, using a dialog, shown by Connect to… button:

Click Open SQL Editor… button
Execute the following command:

SHOW PROPERTIES OF SERVER

MAILENABLE and SHEDULEENABLE should have '1' as a value, if it's true, you can move to the step 2
Execute the following commands to enable required features:

SET PROPERTY MAILENABLE OF SERVER TO '1';
SET PROPERTY SCHEDULEENABLE OF SERVER TO '1';

You can also modify these properties via the INI file, located in the Valentina Server installation folder.

Changing the SCHEDULEENABLE property of a server requires a restart. So close Valentina Studio connection and restart Valentina Server

On OS X System Preferences panel can be used for restart:

Open System Preferences application
Click Valentina Server icon
Click Stop and then Start

On Windows and Linux it is done via standard service-management interfaces.

Step 2: Send email using the Valentina Server

Valentina SQL MAIL command is intended for sending emails.

Valentina Server sends mail via external SMTP servers. Settings, used in the MAIL command, depend on the parameters of the target mail server.

You can see authentication settings for OS X mail server, used for this tutorial:

Digest-MD5 authentication mechanism is currently not supported.

Let's send an email:

Reestablish connection to the Valentina Server
Open SQL Editor and execute the following code:

__MAIL 
    __FROM 'support@example.com' 
    __TO 'client@example.com'
    __SUBJECT 'Commercial Offer'
    __BODY 'Dear Mr. Jones, ...' 
    __SMTP 'smtp://localhost' 
    __PORT 25
    __USER 'support@example.com'
    __PASSWORD '123456'
    __SSL TRUE;

You should see a log entry about result of the MAIL command:

You will receive a message shortly.

Step 3: Prepare a database

In order to generate a report, it is necessary to prepare some data for it. Example MySQL database “sakila” will be used as a datasource.

Prepare SQL dump files for loading to the MySQL server.

Connect to the MySQL server:

From the connection context menu select “Load Dump…” and input a name of target database “sakila”

Go through the wizard and load a schema file
Now load a data dump

You have a database, ready for report generation:

Step 4: Prepare a project

Select Valentina Server connection, select Projects in the drop-down list above the first column.
Unarchive “universal_report.vsp.zip”, copy project into “projects” folder of the Valentina Server directory

Return to the Valentina Studio and click refresh button at the main toolbar. New project will appear

Double-click it to register

Step 5: Prepare a datasource

We've just loaded a new database to the server and it is good idea to check, if a report and a database work well together.

Double-click a project to open it

Select “valentina_sakila” datasource and click Edit…
Select “sakila” database on MySQL server and click OK

Open a context menu of a datasource and select Rename…, give a new name “mysql_sakila”

Open “Report_PaymentsOfCustomers”, switch to Preview mode. Now data should be taken from MySQL server

Step 6: Prepare a query

Back to Design mode, expand data node in the Property Inspector

Query name is “Query_Customers”
Close a report, select “Query_Customers” in list and click Edit…

Copy the query text, it will be used in the next step

Step 7: Generate a report via SQL

To generate a report via SQL REPORT ... FROM command is used.

It requires a few important parameters:

Project and report name: “universal_report”.“Report_PaymentsOfCustomers”

A datasource string:

 mysql://host=127.0.0.1, user=root, password=root, dbname=sakila, port=3306

Query text:
```
SELECT * FROM customer
```
Output format: PDF

Now we have everything to generate a first report.

Switch to the Schema Editor, select the Valentina Server connection and click “Open SQL Editor…”
Type the following command and click “Execute…”:

REPORT "universal_report"."PaymentsOfCustomers"
FROM 'mysql://host=127.0.0.1, user=root, password=root, dbname=sakila, port=3306'
USING 'SELECT * FROM customer'
AS PDF

After some time a generated report will be returned in the result:

Make a double click on the cell to view returned data:

Save it to file and open

For attaching to the email a report must have BASE64 encoding, which is done adding ENCODE BASE64 keywords to the query:

REPORT "universal_report"."Report_PaymentsOfCustomers"
FROM 'mysql://host=127.0.0.1, user=root, password=root, dbname=sakila, port=3306'
USING 'SELECT * FROM customer'
AS PDF ENCODE BASE64

Step 8: Create a new event

Create an event and schedule it to run in 2 minutes after creation:

In the context menu of Valentina Server connection select Create→Event…
Enter a name: FutureReport, database field leave blank
Set interval to 2, units to Minute
Check Enabled checkbox
Check Preserve checkbox to save event for future work after completion

Switch to the “Action” tab

As report generation and email sending is a multiple-statement operation, let's start with BEGIN … END block:

BEGIN
END

Declare query variable in it:

DECLARE query_report VARCHAR( 256 ) DEFAULT
    'REPORT "universal_report"."Report_PaymentsOfCustomers"
    FROM \'mysql://host=127.0.0.1, user=root, password=root, dbname=sakila, port=3306\'
    USING \'SELECT * FROM customer\'
    AS PDF ENCODE BASE64';

Declare cursor

DECLARE cursor_report CURSOR FOR query_report;

Generate a report and save it into local variable:

OPEN cursor_report; 
FETCH FIRST cursor_report INTO @report_data; 
CLOSE cursor_report;

Attach generated report and send it:

__MAIL 
    __FROM 'support@example.com' 
    __TO 'client@example.com'
    __SUBJECT 'The Film List'
    __BODY 'View attachment, please.' 
    __ATTACH @report_data AS 'filmography.pdf'
    __SMTP 'smtp://localhost' 
    __PORT 25
    __USER 'support@example.com'
    __PASSWORD '123456'
    __SSL TRUE;