Creating a Simple Report

The simplest report is a plain-text e-mail, constructed on the basis of a few SQL queries. In this case, we report the total number of sessions in the last week, the number of distinct users, and the number of sessions each user starts.

01: #! /usr/bin/perl -w 02: # 03: # report_text.pl 04: # Chapter 13 05: # Listing 1 06: # 07: use strict; 08: use DBI; 09: use MIME::Lite; 

Line 1 tells the system where to find Perl and turns warnings on.

Lines 2–6 are comments about the program.

Line 7 loads the strict module.

Line 8 loads the DBI module, for our database access.

Line 9 loads the MIME::Lite module for our e-mail functionality.

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

Line 10 uses the DBI->connect method to create a connection to the database server. The result of the connection attempt are returned as a reference to the database connection object and stored in the $dbh variable.

Line 11 is a continuation of line 10. This line calls the die method and prints an error message if there was a problem obtaining a database connection.

Line 12 contains the $messagebody scalar variable we use to build up the body of the e-mail.

13: my ($totalusers)= 14:   $dbh->selectrow_array("SELECT COUNT(DISTINCT user_id) 15:                          FROM session 16:                          WHERE first_used > 17:                          DATE_SUB(SYSDATE(),INTERVAL 7 DAY)"); 18: $messagebody = "Total users: $totalusers\n\n";

Line 13 declares a scalar variable.

Lines 14–17 are a continuation of line 13 that create and execute the SQL statement listed on these lines. The result of the query is stored in the $totalusers variable.

There are parenthesis around the $totalusers variable to force it into list mode. By forcing the variable into list mode, the variable captures the first array element that is returned by the selectrow_array method call. If you had left off the parenthesis, the value that would get stored into $totalusers would be the number of elements in the array that the selectrow_array method call had returned, which is definitely not what we want.

Line 18 stores the text Total users: along with the value stored in $totalusers into the $messagebody variable.

So now $messagebody contains the first report—a simple count of distinct user_id values. MySQL handles the date calculations, which avoids the need for date manipulation within the Perl program; see the documentation of date functions for more detail. By looking at the SQL statement, we can see that this is a weekly report.

19: my ($totalsessions)= 20:   $dbh->selectrow_array("SELECT COUNT(*) 21:                          FROM session 22:                          WHERE first_used > 23:                          DATE_SUB(SYSDATE(),INTERVAL 7 DAY)"); 24: $messagebody .= "Total sessions: $totalsessions\n\n";

Lines 19–24 perform the same basic function as lines 13-18, but this time our SQL command searches for the total number of items that match our criteria rather than the DISTINCT user_id as before.

25: my $sth = $dbh->prepare("SELECT count(*),username 26:                        FROM session, user 27:                          WHERE first_used > 28:                            DATE_SUB(SYSDATE(),INTERVAL 7 DAY) 29:                          AND user_id=user.id 30:                        GROUP BY username");

Lines 25–30 prepare an SQL statement and store a handle to the prepared statement in the $sth variable. The result of the SQL statement for this report is a sorted list of users who have logged in during the period and the number of times each has done so.

31: $sth->execute;

Line 31 calls the execute method on the statement handle ($sth) to execute the SQL statement.

32: while (my ($count, $user) = $sth->fetchrow_array) { 33:   my $s = ($count == 1) ? ‘’ : ‘s’; 34:   $messagebody .= "$user: $count login$s\n"; 35: } 36: $sth->finish;

Line 32 begins a while loop. Each time through this while loop the $sth->fetchrow_array method is called and the result of the call is stored into the $count and $user variables.

Line 33 checks to see if $count is equal to 1. If this is true, then $s gets set to nothing. If false, then $s gets set to the letter s.

Line 34 appends the username and the number of logins that the user had onto our $messagebody variable.

Line 35 ends the while loop that began on line 32.

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

37: $dbh->disconnect;

Line 37 calls the disconnect method to disconnect us from the database.

38: my $message=MIME::Lite->new( 39:               From    => ‘[e-mail]’, 40:               To      => ‘[e-mail]’, 41:               Subject => ‘Weekly report’, 42:               Data    => $messagebody 43:             );

Lines 38–43 create and configure the MIME::Lite object. Obviously, greater complexity is possible, but this is all that is necessary to send a report.

44: $message->send;

Line 44 sends the message.