and send <BODY> # print start_html("Nile dot Com Transaction Report"); # # Attempt to load the driver and connect to the database # eval { $dbh = DBI->connect("DBI:$DB_type:test_nile", $DB_login, $DB_password); }; # # Check if the eval failed (driver failed to load) # if ($@) { die "Could not install driver ($DBI::err): $DBI::errstr\n"; } # # Check if the connection attempt was successful # defined($dbh) or die "Could not connect to db ($DBI::err): $DBI::errstr\n"; # # Prepare the following SQL statement and receive a statement handle # $sth = $dbh->prepare(q{ SELECT title_id, name, price FROM title }) or die "Prepare of SELECT: ", $dbh->errstr, "\n"; # # Execute the prepared SQL statement # $sth->execute or die "Execution of SELECT: ", $dbh->errstr, "\n"; # # Print out a nice big header, # print h1("Sales Report"); # # Loop over every row returned by the SELECT statement # while (($tid, $name, $price, $edition) = $sth->fetchrow_array) { $books{$tid}{'name'} = $name; $books{$tid}{'price'} = $price; $books{$tid}{'purchased'} = 0; } # # We're done with this statement, free up the resources # $sth->finish; # # Prepare the following SQL statement and receive a statement handle # $sth = $dbh->prepare(q{ SELECT title_id, number_transacted FROM transact }) or die "Prepare of SELECT: ", $dbh->errstr, "\n"; # # Execute the prepared SQL statement # $sth->execute or die "Execution of SELECT: ", $dbh->errstr, "\n"; # # Loop over every row returned by the SELECT statement # while (($tid, $num_bought) = $sth->fetchrow_array) { $books{$tid}{'purchased'} += $num_bought; } # # We're done with this statement, free up the resources # $sth->finish; # # Disconnect from the database, we're done! # $dbh->disconnect or warn "Disconnect: ", $dbh->errstr, "\n"; print "<p><table>"; print "<tr><td>Title ID<td>Title<td>Price<td>Number Sold<td>Revenue"; while (($tid, $hashref) = each %books) { $hashref->{'revenue'} = $hashref->{'price'} * $hashref->{'purchased'}; } foreach $tid (sort byrevenue keys %books) { $hashref = $books{$tid}; print "<tr>"; print "<td>$tid<td>", $hashref->{'name'}, "<td>", $hashref->{'price'}, "<td>", $hashref->{'purchased'}, "<td>", $hashref->{'revenue'}, "\n"; } print "</table>"; # # Send </BODY></HTML> # print end_html; sub byrevenue { return $books{$b}{'revenue'} <=> $books{$a}{'revenue'}; } exit 0;

#! /usr/local/bin/perl # Generating Reports Lab: Solution # # Begin loading the needed extensions # use DBI; # The DBI extension use CGI qw (:html2); # The CGI extension use CGI::Carp qw (carpout); # The extra carping routine # # The file dbtype.pl sets a variable which determines the type of database # you will use. This file will be found in either /tmp or /temp. # use lib qw(/tmp /temp); require 'dbtype.pl'; # # Set the path, unbuffer STDOUT, and redirect errors to the browser # $ENV{PATH} = join ":", qw(/usr/bin /bin /sbin /usr/sbin /etc); $| = 1; carpout(\*STDOUT); # # Declare variables # my( $dbh, $rv, $sth ); my( $tid, $name, $price, $edition ); my( @sequence, %labels ); # # Accept the CGI request # my( $q ) = new CGI; # # Send the HTTP header # print $q->header; # # Set the and send <BODY> # print start_html("Nile dot Com Transaction Report"); # # Attempt to load the driver and connect to the database # eval { $dbh = DBI->connect("DBI:$DB_type:test_nile", $DB_login, $DB_password); }; # # Check if the eval failed (driver failed to load) # if ($@) { die "Could not install driver ($DBI::err): $DBI::errstr\n"; } # # Check if the connection attempt was successful # defined($dbh) or die "Could not connect to db ($DBI::err): $DBI::errstr\n"; # # Prepare the following SQL statement and receive a statement handle # $sth = $dbh->prepare(q{ SELECT title_id, name, price FROM title }) or die "Prepare of SELECT: ", $dbh->errstr, "\n"; # # Execute the prepared SQL statement # $sth->execute or die "Execution of SELECT: ", $dbh->errstr, "\n"; # # Print out a nice big header, # print h1("Sales Report"); # # Loop over every row returned by the SELECT statement # while (($tid, $name, $price, $edition) = $sth->fetchrow_array) { $books{$tid}{'name'} = $name; $books{$tid}{'price'} = $price; $books{$tid}{'purchased'} = 0; } # # We're done with this statement, free up the resources # $sth->finish; # # Prepare the following SQL statement and receive a statement handle # $sth = $dbh->prepare(q{ SELECT title_id, number_transacted FROM transact }) or die "Prepare of SELECT: ", $dbh->errstr, "\n"; # # Execute the prepared SQL statement # $sth->execute or die "Execution of SELECT: ", $dbh->errstr, "\n"; # # Loop over every row returned by the SELECT statement # while (($tid, $num_bought) = $sth->fetchrow_array) { $books{$tid}{'purchased'} += $num_bought; } # # We're done with this statement, free up the resources # $sth->finish; # # Disconnect from the database, we're done! # $dbh->disconnect or warn "Disconnect: ", $dbh->errstr, "\n"; print "<p><table>"; print "<tr><td>Title ID<td>Title<td>Price<td>Number Sold<td>Revenue"; while (($tid, $hashref) = each %books) { $hashref->{'revenue'} = $hashref->{'price'} * $hashref->{'purchased'}; } foreach $tid (sort byrevenue keys %books) { $hashref = $books{$tid}; print "<tr>"; print "<td>$tid<td>", $hashref->{'name'}, "<td>", $hashref->{'price'}, "<td>", $hashref->{'purchased'}, "<td>", $hashref->{'revenue'}, "\n"; } print "</table>"; # # Send </BODY></HTML> # print end_html; sub byrevenue { return $books{$b}{'revenue'} <=> $books{$a}{'revenue'}; } exit 0;