Creating a More Complex Report

The first example shows how quickly a simple report can be generated; but it also shows some of the problems that arise. Very little formatting is possible within plain-text mail, and tables, in particular, are used infrequently or are ugly in appearance. Moreover, changing the order of the entries in the report, or the layout, requires editing the program code.

One solution to the layout problem is using a template module. As with sending mail, many of these are available for Perl; for the purposes of this chapter, HTML::Template is used as a reasonable compromise between capability and ease of use. (Other modules that deserve investigation are Text::Template, at the low end of the capability scale, and Template::Toolkit, at the high end. Resist the temptation to invent your own template module. It has been done many times before, and most of the standard pitfalls have been overcome in the code that is already available.)

The template (which, in spite of the module’s name, can be any form of text, not just HTML) is stored in a separate file from the program. Interspersed with the normal layout commands are special tags, into which will be placed data that the program generates. This allows layout changes to be made without editing the main program file and (more important) allows reports in multiple formats to be generated from the same software.

As for a solution to the formatting problem, one convenient approach is the use of HTML e-mail. HTML has the advantage of allowing greater control over layout than plain text; in particular, the <table> structure is very useful.

Naturally, some of the intended audience of a report might not have HTML-enabled mail readers. It is generally a good idea to include a plain-text version of the report as well, in a multipart/alternative (HTML) structure, as we shall see. A templating system makes this very easy.

Essentially, this example produces the same report as the preceding example, but in both text and HTML formats, by use of HTML::Template.

01: #! /usr/bin/perl -w 02: # 03: # report_html.pl 04: # Chapter 13 05: # Listing 2 06: #

Lines 1–6 tell the system where to find Perl, turn warnings on, and give some comments about the program.

07: use strict; 08: use DBI; 09: use HTML::Template; 10: use MIME::Lite;

Line 7 loads the strict module.

Line 8 loads the DBI module, for database access.

Line 9 loads the HTML::Template module.

Line 10 loads the MIME::Lite module.

11: my $dbh = DBI->connect(‘DBI:mysql:UserTrack’,’user’,’password’) 12:         or die("Cannot connect: $DBI::errstr ");

Lines 11–12 create a connection to the database and store a handle in the $dbh variable. If there is a problem connecting to the database, then the die method is called and the program aborts and displays an error message.

13: my @template; 14: $template[0]=HTML::Template->new(filename => ‘template_2.txt’); 15: $template[1]=HTML::Template->new(filename => ‘template_2.html’);

Line 13 declares an array named @template.

Lines 14–15 create two separate HTML::Template objects, one for each of the formats to be used. These are based on separate files, which can be edited inde- pendently. Although the same data is put into each, different processing creates different output. (Check perldoc HTML::Template for documentation on the HTML_TEMPLATE_ROOT environment variable and for other ways of specifying the location of template files.) For simplicity, you may want to use an absolute filename. By default, though, the program looks first in the directory in which it resides.

16: my ($totalusers)= 17:   $dbh->selectrow_array("SELECT COUNT(DISTINCT user_id) 18:                          FROM session 19:                          WHERE first_used > 20:                            DATE_SUB(SYSDATE(),INTERVAL 7 DAY)");

Line 16 declares a scalar variable named $totalusers and, using the parentheses to force it into a list context, gets loaded with the value returned by the $dbh->selectrow_array method on line 17.

Lines 17–20 create and execute an SQL statement to get out report data.

21: foreach my $tmpl (@template) { 22:   $tmpl->param(totalusers => $totalusers); 23: }

Lines 21–23 extract information from the database just as before. However, the param method of HTML::Template is used to indicate that the totalusers template variable should be replaced by the value just extracted. This is done for each template.

24: my ($totalsessions)= 25:   $dbh->selectrow_array("SELECT COUNT(*) 26:                          FROM session 27:                          WHERE first_used > 28:                            DATE_SUB(SYSDATE(),INTERVAL 7 DAY)");

Line 24 declares a scalar variable named $totalsessions and stores the result of the $dbh->selectrow_array method from line 25 into it.

Lines 25–28 create and execute the SQL statement needed for this part of our report.

29: foreach my $tmpl (@template) { 30:   $tmpl->param(totalsessions => $totalsessions); 31: }

Line 29 begins a foreach loop that loops through the different templates in the @template array. Each time through the loop, the current value is set in the $tmpl variable.

Line 30 passes the totalsessions parameter and it’s value to the current template referenced by $tmpl.

Line 31 ends the foreach loop.

32: my $sth = $dbh->prepare("SELECT count(*),username 33:                        FROM session, user 34:                          WHERE first_used > 35:                            DATE_SUB(SYSDATE(),INTERVAL 7 DAY) 36:                          AND user_id=user.id 37:                        GROUP BY username");

Lines 32–37 prepare the SQL statement for our next report item and store a reference to the prepared statement in the newly defined $sth variable.

38: $sth->execute; 39: my @usersession;

Line 38 executes the SQL statement we prepared above.

Line 39 declares a new array named @usersession.

40: while (my ($count, $user) = $sth->fetchrow_array) { 41:   push @usersession, {user  => $user, 42:                      count => $count}; 43: }

Line 40 begins a while loop that continues as long as the $sth->fetchrow_array method keeps returning data. Each time through the loop, the current values from the $sth->fetchrow_array method get stored in the $count and $user variables.

Lines 41–42 push a reference to an anonymous hash containing the user and the count onto the @usersession array.

{user => $user, count => $count} actually creates a hash with no name. A reference to this hash is then pushed into the @usersession array so that we can easily retrieve it.

Line 43 closes the while loop.

44: foreach my $tmpl (@template) { 45:   $tmpl->param(usersession => \@usersession); 46: }

Line 44 begins a foreach loop that loops through the different templates in the @template array. Each time through the loop, the current value is set in the $tmpl variable.

Line 45 passes the usersession parameter and a reference to an array containing it’s values to the current template referenced by $tmpl.

Line 46 ends the foreach loop.

47: $sth->finish; 48: $dbh->disconnect;

Line 47 calls the finish method to clean up the $sth statement handle.

Line 48 calls the disconnect method to disconnect from the database.

49: my $message = MIME::Lite->new( 50:               From    => ‘[e-mail]’, 51:               To      => ‘[e-mail]’, 52:               Subject => ‘Weekly report’, 53:               Type    => ‘multipart/alternative’ 54:             );

Lines 49–54 prepare the e-mail message. Rather than including text directly in the message constructor as before, here we specify that this message is to take advantage of the power of the MIME standard by setting the Type parameter to multipart/alternative (in this case, text and HTML).

55: $message->attach(Type => ‘text/plain’, 56:                  Data => $template[0]->output); 57: $message->attach(Type => ‘text/html’, 58:                  Data => $template[1]->output); 59: $message->send;

Lines 55–58 convert each of the template objects, using its output method, to a scalar containing the original template—with the interpolated data that the program adds. These are then passed to the attach method of MIME::Lite, which adds them to the message under construction.

Line 59 sends the message exactly as before.

The two template files are as follows:

template_2.txt

Total users: <tmpl_var name=totalusers> Total sessions: <tmpl_var name=totalsessions> Sessions per user: <tmpl_loop name=usersession><tmpl_var name=user>: <tmpl_var name=count> </tmpl_loop>

The <tmpl_var> tag introduces a variable to be interpolated in the final output. <tmpl_loop> introduces a loop; note that variables defined outside the loop are not automatically present within the loop and need to be defined explicitly if they are required. Since HTML::Template always passes through every new line , the usersession loop is kept on one line, avoiding extra line breaks in the final output.

template_2.html

<html> <head> <title>Weekly report</title> </head> <body> <p>Total users: <tmpl_var name=totalusers escape=html></p> <p>Total sessions: <tmpl_var name=totalsessions escape=html></p> <p>Sessions per user: <table> <tr><td>User</td><td>Logins</td></tr> <tmpl_loop name=usersession> <tr> <td><tmpl_var name=user escape=html></td> <td><tmpl_var name=count escape=html></td> </tr> </tmpl_loop> </table></p></body></html>

The preceding HTML-mode template is more complex than the plain-text template, though the HTML-mode template contains more noninterpolated text. Note the escape=html argument to the <tmpl_var> tag; this causes any body text that might be interpreted specially, such as the symbols <, >, or &, to be automatically converted to an encoded equivalent that can safely be sent to a Web browser. Although it should not be necessary in this particular case, using escape=html is a good habit to get into.