#!/usr/bin/perl
# Fig. 15.26: fig15_26.pl
# Makes a webpage of statistics from the database.

use warnings;
use strict;
use DBI;
use DBD::mysql;
use CGI qw( :standard );

my $dbh = DBI->connect( "DBI:mysql:USERDB", "root", "", 
                                    { RaiseError => 1 } );

my $sth = $dbh->prepare( "SELECT Continent, OpSys, Hours, Rating
                             FROM Users" );
$sth->execute();

my $results = $sth->fetchall_arrayref();

my $total = scalar( @$results );

my ( $rating, $hours, %lands, %op );

foreach my $row ( @$results ) {
   $lands{ $row->[ 0 ] }++;
   $op{ $row->[ 1 ] }++;
   $hours += $row->[ 2 ];
   $rating += $row->[ 3 ];
}

$hours /= $total;
$rating /= $total;

print header, start_html( "User Stats" ),
      h1( "User Statistics" );

printf "You have a total of %d users spending an average of %.2f
        hours using your product. They rate it an average of 
        %.2f out 5.", $total, $hours, $rating;

my $landrows = Tr( th( { width => "100" }, "Continent" ), 
                   th( { width => "50" }, "Total Users" ), 
                   th( "Percent Of Users" ) );

foreach ( sort  { $lands{ $b } <=> $lands{ $a } } keys( %lands ) ) {
   my $percent = int( $lands{ $_ } * 100 / $total );
   $landrows .= Tr( td( $_ ), td( $lands{ $_ } ), 
                   td(  table( { -width => "100%" },
                          Tr( td( { -width => "$percent%",
                                   -bgcolor => "#0000FF" }, br ),
                              td( br ) ) )
                      ) );   
}

print h3( { -align => "center" }, "Users by Continent" ),
      table( { -border => 1, -width => "100%" }, $landrows );

my $oprows = Tr( th( { width => "100" }, "Operating System" ), 
                 th( { width => "50" }, "Total Users" ), 
                 th( "Percent Of Users" ) );

foreach ( sort { $op{ $b } <=> $op{ $a } } keys( %op ) ) {
   my $percent = int( $op{ $_ } * 100 / $total );
   $oprows .= Tr( td( $_ ), td( $op{ $_ } ), 
                   td(  table( { -width => "100%" },
                      Tr( td( { -width => "$percent%", 
                                -bgcolor => "#0000FF" }, br ),
                          td( br )
                        ) ) ) );   
}

print h3( { -align => "center" }, "Operating System statistics" ),
      table( { -border => 1, -width => "100%" }, $oprows );

$dbh->disconnect();


###########################################################################
#  (C) Copyright 2001 by Deitel & Associates, Inc. and Prentice Hall.     #
#  All Rights Reserved.                                                   #
#                                                                         #
#  DISCLAIMER: The authors and publisher of this book have used their     #
#  best efforts in preparing the book. These efforts include the          #
#  development, research, and testing of the theories and programs        #
#  to determine their effectiveness. The authors and publisher make       #
#  no warranty of any kind, expressed or implied, with regard to these    #
#  programs or to the documentation contained in these books. The authors #
#  and publisher shall not be liable in any event for incidental or       #
#  consequential damages in connection with, or arising out of, the       #
#  furnishing, performance, or use of these programs.                     #
###########################################################################