Creating Graphical Reports

The final refinement to e-mail reporting, which is dealt with here, is the use of dynamic graphical content. It is, of course, possible to write custom-graphing packages by using the GD module. The GD module is a powerful interface to Thomas Boutell’s gd graphics library. In most cases, however, all that is needed is a simple graph; here, the GD::Graph module becomes useful.

GD::Graph is capable of moderately complex graph generation, but operation of the basic functions is very simple: just provide an array of arrayrefs, each the same size. The first arrayref contains data-row labels, and later ones contain data (GD::Graph doesn’t support true X-Y graph plotting). Three-dimensional graphing, although provided by a separate module dependent on GD::Graph, is essentially similar.

Creating a graph consists of multiple stages:

1. Create the graph object (specifying the type of graph and the size).

2. Set the parameters of the graph (axis labels, scaling, and so on).

3. Populate the data array; then use the plot function to convert it to a GD object (an image).

4. Use one of GD’s output functions to convert the GD object to a usable image.

5. The following example generates a summary of session lengths, in both graphical and HTML table format.

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

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

07: use strict; 08: use DBI; 09: use HTML::Template; 10: use MIME::Lite; 11: use GD::Graph::bars3d;

Lines 7–10 load some common modules.

Line 11 causes the GD::Graph module to import only the graph types it is explicitly told to use. In this case, we are drawing a three-dimensional bar chart.

12: my @buckets=(0, 60, 300, 3600); 13: my @description=(‘0’, ‘60 sec’, ‘5 min’, ‘1 hour’, ‘’); 14: my ($w, $h)=(400, 300);

Lines 12–14 set various configuration options, allowing them to be conveniently changed later (should this be necessary). The @buckets array contains the boundaries between the different session durations (in seconds); @description contains the corresponding textual descriptions.

15: my $graph=new GD::Graph::bars3d($w, $h);

Line 15 creates the GD::Graph::bars3d object by using the basic parameters: pixel width and height.

16: $graph->set(x_label => ‘Session duration’, 17:             y_label => ‘Number of sessions’);

Lines 16–17 set other properties of the graph (in this case, just the axis labels). If automatic scaling produces odd results because of the nature of the data, or if colors are changed, further parameters to the set method can achieve the desired results.

18: my $dbh = DBI->connect(‘DBI:mysql:UserTrack’,’user’,’password’) 19:         or die("Cannot connect: $DBI::errstr"); 20: my $template= HTML::Template->new(filename => ‘template_3.html’); 21: my @data; 22: my @loop;

Lines 18–19 create the database connection and store a reference to the connection in $dbh. If there is a problem connecting to the database, then the die method is called and the program aborts.

Line 20 creates a new template object and stores a reference to the object in the $template variable.

Lines 21–22 declare a couple arrays named @data and @loop that we’ll be using to store data in the appropriate format for the graph and for the HTML table, respectively.

23: foreach my $b (0..$#buckets) { 24:   my %loopdata; 25:   my @where; 26:   my @desc;

Line 23 begins a foreach loop that loops from 0 to the number of the last element in the @buckets array. Each time through the loop, the current value gets stored in $b.

Lines 24–26 declare some variables that we’ll be using.

27:   if ($b > 0) { 28:     my $low = $buckets[$b]; 29:     push @where,       "(unix_timestamp(last_used)-unix_timestamp(first_used)) > $low"; 30:   }

Line 27 checks to see if $b is greater than 0 and if so, this code block is entered.

Line 28 declares a variable named $low and sets it to the value stored in $buckets[$b].

Line 29 pushes a string onto the @where array. This string will become part of the SQL command.

Line 30 closes this if block.

31:   push @desc, $description[$b];

Line 31 pushes the current value in $description[$b] into the @desc array.

32:   if ($b < $#buckets) { 33:     my $high = $buckets[$b+1]; 34:     push @where,    "(unix_timestamp(last_used)-unix_timestamp(first_used)) <= $high";

Line 32 checks to see if $b is less than $#buckets (the index of the last element in the @buckets array).

Line 33 declares a variable named $high and stores the value in $buckets[$b+1] into it.

Line 34 pushes a string into the @where array. This string will become part of the SQL command.

35:     push @desc, $description[$b+1];

Line 35 pushes the value stored in $description[$b+1] into the @desc array.

36:   } else { 37:     $desc[0] .= ‘+’; 38:   }

Line 36 closes the first part of the if block, and opens an else portion.

Line 37 appends a plus sign to the first element of the @desc array.

Line 38 closes this if..else block.

39:   my $desc = join(‘-’, @desc); 40:   push @{$data[0]}, $desc; 41:   $loopdata{desc} = $desc; 42:   my $where = join(‘ AND ‘, @where);

Line 39 declares a scalar variable named $desc and uses the join function to store all of the values in @desc array joined together with a dash into it.

Line 40 uses the push function to push the $desc string into the array referenced by $data[0].

Line 41 sets $loopdata{desc} to the value in $desc.

Line 42 declares a scalar variable named $where and uses the join function to store all of the values in the @where array joined together with ‘ AND ’. The $where variable will become our WHERE clause in the SQL statement.

So, lines 24–42 automatically generate a description of the time period from the @description list; this time period is then stored in the first @data arrayref (for the graph) and in %loopdata (for the table). Also, a sequence of restriction clauses based on the time period is assembled here, then concatenated into a form usable in the actual SELECT statement.

43:   my $sql="SELECT COUNT(*) 44:            FROM session 45:            WHERE first_used > 46:             DATE_SUB(SYSDATE(),INTERVAL 7 DAY) 47:            AND $where";

Lines 43-47 create the SQL statement needed to get the report data from the database.

48:   my ($value) = $dbh->selectrow_array($sql);

Line 48 executes the SQL statement and then returns the first row that was returned from the database. This data is then stored into the $value variable.

49:   push @{$data[1]}, $value; 50:   $loopdata{count} = $value; 51:   push @loop, \%loopdata;

Lines 49–51, once the value has been retrieved, store it in the second @data arrayref and in %loopdata. The latter is then appended to the template-loop structure.

52: } 53: $dbh->disconnect; 54: $template->param(width    => $w, 55:                  height   => $h, 56:                  duration => \@loop); 

Line 52 ends the foreach loop.

Line 53 disconnects us from the database.

Lines 54–56 send some parameters to the template so that they can be filled in for our final report.

57: my $gd = $graph->plot(\@data);

Line 57 plots the graph. $gd now holds a GD bitmap image object.

58: my $message=MIME::Lite->new( 59:               From    => ‘[e-mail]’, 60:               To      => ‘[e-mail]’, 61:               Subject => ‘Weekly report’, 62:               Type    => ‘multipart/mixed’ 63:             );

Lines 58–63 prepare the new message.

64: $message->attach(Type => ‘text/html’, 65:                  Data => $template->output);

Lines 64–65 attach the HTML file to the e-mail message.

66: $message->attach(Type     => ‘image/png’, 67:                  Filename => ‘graph1.png’, 68:                  Data     => $gd->png);

Lines 66–68 convert the GD object to raw PNG data (the GD library no longer supports GIFs due to licensing issues) and add it to the message. The filename parameter does not refer to an actual file; rather, it gives the name under which the image should be saved when it is received, which matches the name of the image-source tag given in the template.

69: $message->send;

Line 69 sends the message.

The template used for this report is very similar to that used with report HTML; the sole difference is the IMG tag, with height and width inserted automatically as a courtesy to the eventual viewer.

template_3.html

<html> <head> <title>Weekly report</title> </head> <body> <p>Sessions per user:</p> <img src="/books/2/889/1/html/2/graph1.png" alt="sessions_graph" width=<tmpl_var name=width> height=<tmpl_var name=height>> <table> <tr><td>Duration</td><td>Sessions</td></tr> <tmpl_loop name=duration> <tr> <td><tmpl_var name=desc escape=html></td> <td><tmpl_var name=count escape=html></td> </tr> </tmpl_loop> </table></body></html>

Code very similar to this can be used to generate a report on the Web site itself; the only change necessary is that both the HTML page and the image are written to files rather than incorporated into an e-mail message.

The results should look something like Figure 13-1.

Figure 13-1: Output of template3.html