Mail extension

With a mail extension, you can send mail directly from the report. SMTP mail is supported as well as support for authentication. myDBR utilizes PHPMailer.

Configuration

The configuration is done via Environmental settings. The old mail configuration in user/extension_init.php is deprecated.

Commands

dbr.mail - Send mail
dbr.mail.sender - Provide sender address in the report instead of using the one from the settings (user/extension_init.php)
dbr.mail.recipient - Add multiple recipients to the mail to be sent
dbr.mail.attach - Add attachment to mail
dbr.mail.notify_successful_mail - Disable the "Mail sent" message
dbr.mail.smtp.user - Use alternate SMTP user/password
dbr.mail.smtp.server - Use alternate SMTP server
dbr.mail.smtp.authentication.type - Select SMTP authentication (default LOGIN)
dbr.mail.encoding - Set the mail encoding ("8bit", "7bit", "binary", "base64" and "quoted-printable")
dbr.mail.debug - Set debug on to get verbose error messages (1=debug on,0=debug off)
dbr.mail.log.proc - Define a procedure which will log the mail actions.
dbr.mail.nobr.html - Do no convert linefeeds to line breaks in HTML mail
dbr.mail.header_file - Override the default mail header file with your own. The file location is based on user/
dbr.mail.postprocess - Postprocess the mail output with a command line tool (default pandoc)
dbr.mail.ignore_invalid_email - Ignore invalid email addresses used in dbr.mail.recipient
dbr.mail.smtp.ssl - Define the SSL context options for the connection

Syntax

select 'dbr.mail', [optional HTML flag: 1 for HTML mail]
select 'dbr.mail.sender', sender_email, [, sender_name, [replyto_email, [replyto_name ]]]
select 'dbr.mail.recipient', recipient_email, [, recipient_name, 'cc' | 'bcc']
select 'dbr.mail.attach', 'filename.ext', URL [, ignore_ssl]
select 'dbr.mail.notify_successful_mail', 0
select 'dbr.mail.smtp.user', 'user', 'password'
select 'dbr.mail.smtp.server', 'host', 'port', {'tls'|'ssl'}
select 'dbr.mail.smtp.authentication.type', 'LOGIN' | 'PLAIN' | 'NTLM' | 'CRAM-MD5'
select 'dbr.mail.encoding', 'base64'
select 'dbr.mail.debug', 1
select 'dbr.mail.log.proc', 'sp_mail_log';
select 'dbr.mail.nobr.html', 1;
select 'dbr.mail.header_file', filename;
select 'dbr.mail.postprocess', filetype;
select 'dbr.mail.ignore_invalid_email', 1;
select 'dbr.mail.smtp.ssl', 'option', 'value';

In dbr.mail.sender, if no replyto_email is given, myDBR uses sender_email. Similarly, if replyto_name is not given sender_name is used.

Usage

Plain text mail

select 'dbr.mail';

select 'John.Doe@example.com', 'John', 'This is a subject', 'Body of the mail';

HTML mail with mail debug log

/* Send HTML mail */
select 'dbr.mail', 1; 
/* in_extra1 and in_extra2 are optional */
select 'dbr.mail.log.proc', 'sp_mail_log', 'in_extra1=extra1', 'in_extra2=extra2';

select email, name, subject, html_body, extra1, extra2
from mydb.mail_recipients;

Where the sp_mail_log procedure has four fixed parameters and you can add additional parameters to it to be used as column reference:

create procedure sp_mail_log ( 
in_msg varchar(255),
in_error text,
in_to_address varchar(255),
in_errornous_attachment_names varchar(255),
in_extra1 int,
in_extra2 int
)
begin

/* Do whatever you like with the optional extra parameters */
insert into mail_log(sent_at, message, error, to_address, errornous_attachment_names)
values ( now(), in_msg, in_error, in_to_address , in_errornous_attachment_names );

end

Same mail to multiple recipients

select 'dbr.mail'; 
select 'dbr.mail.recipient', 'john@company.com', 'John Doe', 'CC';
select 'dbr.mail.recipient', 'Jane@company.com', 'Jane Doe', 'BCC';
select 'dbr.mail.recipient', 'father@company.com', 'Father Doe', 'TO';

/* We can skip the recipient as we already have them */
select '', '', 'Notification', 
	concat('Your report can be found in myDBR', char(10), 
		'-- ', char(10), 
		'Mary');

Mail with different sender & Reply-To

select 'dbr.mail'; 
select 'dbr.mail.sender', 'mary@company.com', 'Mary Cotton';

select 'john@company.com', 'John Doe', 'Notification', 
	concat('Your report can be found in myDBR', char(10), 
		'-- ', char(10), 
		'Mary');

Sending report output as mail

In order to send full report elements to mail, you can use the dbr.record command to gather data to be mailed. The command includes 'begin' and 'end' commands which will mark the section to be included in the mail. The dbr.record command is available in the Premium-version.

The command will:

  • Include the report objects while preserving the formatting
  • Include all table elements (basic, cross-tabulation, pageview, etc)
  • Include images (ChartDirector, Graphviz) as attachments

The command will not:

  • Include elements that require client-side JavaScript processing (like GoogleMaps, JavaScript-based elements, etc)

/* We'll start recording, everything between 'begin' and 'end' will be included into report */
select 'dbr.record', 'begin'; 

/* Include full reports */
call sp_DBR_MyReport();

/* Include additional report elements */
select data1, data2
from mydb.mydata;

select 'dbr.record', 'end'; 

/* We'll use HTML mail to preserve the formatting */
select 'dbr.mail', 1; 

/* And finally send the mail to all recipients */
select email, name, 'This is the report'
from mydb.mail_recipients;

Using attachments

You are free to add attachments to emails. An attachment can be any file accessible from the server including a PDF/Excel export from myDBR itself.

select 'dbr.mail'; 
select 'dbr.mail.sender', 'mary@company.com', 'Mary Cotton';
select 'dbr.mail.attach', 'report.pdf', 'http://myserver.com/mydbr/report.php?r=184&h=ee5f3bfee6e1384ccf52e151bcc2081aa367adeb&export=pdf';

select 'john@company.com', 'John Doe', 'Notification', 
	concat('Here is the latest report', char(10), 
		'-- ', char(10), 
		'Mary');

Postprocess the mail being sent

You can postprocess the mails using a selected command line tool. One can for example convert the HTML to aby other text based format. The default conversion tool is Pandoc. In order to use Pandoc, you need to install it to the server. The postprocessing command is defined in $defaults['mail']['postprocess_cmd'].

select 'dbr.record', 'begin';
select 'dbr.summary.options', 'skip_single_line_summary';

select film_id, title, release_year
from film
limit 2;
select 'dbr.record', 'end';

select 'dbr.mail'; 
select 'dbr.mail.postprocess', 'rst';

select 'john@company.com', 'John Doe', 'RST document';

Will produce a mail:

.. container::

======= ======================== ============
film_id title                    release_year
======= ======================== ============
1       ACADEMY DINOSAUR'abc'abc 2 007
2       AMERICAN BACON'abc       2 008
======= ======================== ============

When the postprocessing is done with HTML mail, the postprocessing is done for the plaintext version of the mail.

Bypassing certificate verification error

If the mail seding fails with certificate verification error, the best solution is to check your servers setttings. If you are using internal mail server and are willing to skip the certificate verification, you can set the SSL context options to bypass the error.

select 'dbr.mail.smtp.ssl', 'verify_peer', 'false';
select 'dbr.mail.smtp.ssl', 'verify_peer_name', 'false';
select 'dbr.mail.smtp.ssl', 'allow_self_signed', 'true';

select 'dbr.mail';

select 'John.Doe@example.com', 'John', 'This is a subject', 'Body of the mail';