Mail extension

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

Configuration

Configuration is done via Environmental settings. 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.record - Record report output for mail delivery or to be used with a template
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.smtp.nlmt.realm - Realm for NTLM authentication type
dbr.mail.smtp.nlmt.workstation - Workstation for NTLM authentication type
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

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.record', 'begin' [, 'template_variable' ] | 'end'
select 'dbr.mail.attach', 'filename.ext', URL
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.smtp.nlmt.realm', 'realm'
select 'dbr.mail.smtp.nlmt.workstation', 'workstation'
select 'dbr.mail.encoding', 'base64'
select 'dbr.mail.debug', 1
select 'dbr.mail.log.proc', 'sp_mail_log';
select 'dbr.mail.nobr.html', 1;

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; 
select 'dbr.mail.log.proc', 'sp_mail_log';

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

Where the sp_mail_log procedure has format:

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

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';

/* 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 which require client side processing (GoogleMaps, Flash-charts)


/* 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 mails. 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');