Program Listings

Listings 13-1 to 13-3 contain the complete and uninterrupted code for the programs developed in this chapter.

Listing 13-1: Plain-text report

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; 10: my $dbh=DBI->connect(‘DBI:mysql:UserTrack’,’user’,’password’) 11:         or die("Cannot connect: $DBI::errstr"); 12: my $messagebody; 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"; 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"; 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"); 31: $sth->execute; 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; 37: $dbh->disconnect; 38: my $message=MIME::Lite->new( 39:               From    => ‘[e-mail]’, 40:               To      => ‘[e-mail]’, 41:               Subject => ‘Weekly report’, 42:               Data    => $messagebody 43:             ); 44: $message->send;

Listing 13-2: HTML report

01: #! /usr/bin/perl -w 02: # 03: # report_html.pl 04: # Chapter 13 05: # Listing 2 06: # 07: use strict; 08: use DBI; 09: use HTML::Template; 10: use MIME::Lite; 11: my $dbh=DBI->connect(‘DBI:mysql:UserTrack’,’user’,’password’) 12:         or die("Cannot connect: $DBI::errstr"); 13: my @template; 14: $template[0]=HTML::Template->new(filename => ‘template_2.txt’); 15: $template[1]=HTML::Template->new(filename => ‘template_2.html’); 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)"); 21: foreach my $tmpl (@template) { 22:   $tmpl->param(totalusers => $totalusers); 23: } 24: my ($totalsessions)= 25:   $dbh->selectrow_array("SELECT COUNT(*) 26:                          FROM session 27:                          WHERE first_used > 28:                            DATE_SUB(SYSDATE(),INTERVAL 7 DAY)"); 29: foreach my $tmpl (@template) { 30:   $tmpl->param(totalsessions => $totalsessions); 31: } 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"); 38: $sth->execute; 39: my @usersession; 40: while (my ($count,$user)=$sth->fetchrow_array) { 41:   push @usersession,{user => $user, 42:                      count => $count}; 43: } 44: foreach my $tmpl (@template) { 45:   $tmpl->param(usersession => \@usersession); 46: } 47: $sth->finish; 48: $dbh->disconnect; 49: my $message=MIME::Lite->new( 50:               From    => ‘[e-mail]’, 51:               To      => ‘[e-mail]’, 52:               Subject => ‘Weekly report’, 53:               Type    => ‘multipart/alternative’ 54:             ); 55: $message->attach(Type => ‘TEXT’, 56:                  Data => $template[0]->output); 57: $message->attach(Type => ‘text/html’, 58:                  Data => $template[1]->output); 59: $message->send;

Listing 13-3: Graphical report

01: #! /usr/bin/perl -w 02: # 03: # report_graphic.pl 04: # Chapter 13 05: # Listing 3 06: # 07: use strict; 08: use DBI; 09: use HTML::Template; 10: use MIME::Lite; 11: use GD::Graph::bars3d; 12: my @buckets     = (0, 60, 300, 3600); 13: my @description = (‘0’, ‘60 sec’, ‘5 min’, ‘1 hour’,’’); 14: my ($w, $h)     = (400, 300); 15: my $graph = new GD::Graph::bars3d($w, $h); 16: $graph->set(x_label => ‘Session duration’, 17:             y_label => ‘Number of sessions’); 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; 23: foreach my $b (0..$#buckets) { 24:   my %loopdata; 25:   my @where; 26:   my @desc; 27:   if ($b > 0) { 28:     my $low = $buckets[$b]; 29:     push @where, "(unix_timestamp(last_used)-unix_timestamp(first_used)) > $low"; 30:   } 31:   push @desc,$description[$b]; 32:   if ($b < $#buckets) { 33:     my $high = $buckets[$b+1]; 34:     push @where, "(unix_timestamp(last_used)-unix_timestamp(first_used)) <= $high"; 35:     push @desc, $description[$b+1]; 36:   } else { 37:     $desc[0] .= ‘+’; 38:   } 39:   my $desc = join(‘-’, @desc); 40:   push @{$data[0]}, $desc; 41:   $loopdata{desc} = $desc; 42:   my $where = join(‘ AND ‘, @where); 43:   my $sql = "SELECT COUNT(*) 44:            FROM session 45:            WHERE first_used > 46:             DATE_SUB(SYSDATE(),INTERVAL 7 DAY) 47:            AND $where"; 48:   my ($value) = $dbh->selectrow_array($sql); 49:   push @{$data[1]}, $value; 50:   $loopdata{count} = $value; 51:   push @loop, \%loopdata; 52: } 53: $dbh->disconnect; 54: $template->param(width => $w, 55:                  height => $h, 56:                  duration => \@loop); 57: my $gd=$graph->plot(\@data); 58: my $message=MIME::Lite->new( 59:               From    => ‘[e-mail]’, 60:               To      => ‘[e-mail]’, 61:               Subject => ‘Weekly report’, 62:               Type    => ‘multipart/mixed’ 63:             ); 64: $message->attach(Type => ‘text/html’, 65:                  Data => $template->output); 66: $message->attach(Type     => ‘image/png’, 67:                  Filename => ‘graph1.png’, 68:                  Data     => $gd->png); 69: $message->send;