Sample Lab Six

#!/usr/bin/perl

use DBI;
use CGI;

$q = new CGI;

print "Content-type: text/html\n\n";

$DB        = "DBI:mysql:a348";         # data source name (database)
$username  = "a348";                   # username
$password  = "a348AG";                 # password
$tablename = "panasch_mid_calcal";     # table we are working with

$dbh = DBI->connect($DB, $username, $password, {PrintError => 0})
  || die "Couldn't open database: ", $DBI::errstr;

$product_to_add = $q->param('product_to_add');
$energy_to_add = $q->param('energy_to_add');


#------------------- Insert/Delete an entry with &insert/delete -------------------
if ($q->param('action') =~ /^insert$/i) {
  $errmsg_NoProductName = "";
  $errmsg_NoEnergyValue = "";
  if (($product_to_add ne '') && ($energy_to_add >0)){
     &delete($product_to_add);
     &insert($product_to_add, $energy_to_add);
  } else {if ($product_to_add eq '') { $errmsg_NoProductName = " Please, specify the product name!!!";}
          if (!($energy_to_add>0))  { $errmsg_NoEnergyValue = " Please, specify the energy value!";}
  }
} else {if ($q->param('action') =~ /^Delete$/i){
           if ($product_to_add){ &delete($product_to_add);}
           else {$errmsg_NoProductName = " Please, specify the product name!";}
        }
 }
#------------------- End Insert/Delete an entry with &insert/delete -------------------

#------------------- Calculate the amount of calories --------------------------------
$select_query = 'SELECT * FROM ' . $tablename;
$sth = $dbh->prepare($select_query) || die $dbh->errstr;
$sth->execute() || die $sth->errstr;

$Total = 0;
while (my $row = $sth->fetch) {
  my($product, $energy) = @$row;
  $Total +=  $q->param($product)*$energy;
}
$sth->finish;
# ------------------ End of Calculate ------------------------------------------

# ----------------- Display the table with SELECT -----------------------

print "<body bgcolor=\"#00FF99\">";
print "<h4>To find out how many calories you consumed, please indicate the amounts<br>".
      "in the space provided to the left of product names and click \"Calculate\"\.";
$select_query = 'SELECT * FROM ' . $tablename . ' ORDER BY energy DESC';
$sth = $dbh->prepare($select_query) || die $dbh->errstr;

$sth->execute() || die $sth->errstr;

print $q->start_form(),
    "<table border cellpadding=2>",
    "<tr><th> Product Name <th> Calories/unit <th> Amount consumed</tr>";

while (my $row = $sth->fetch) {
  my($product, $energy) = @$row;
  print "<tr><td align=center>$product <td align=center>$energy
             <td>" . $q->textfield(-name=>$product, -value=>'');
}
print "<tr><td></td><td align=right valign=center><h4>TOTAL :</h4> <td align=left>" . $Total . "<td
>";
print "</table><p>" . $q->submit(value=>'Calculate');
print $q->end_form;
$sth->finish;
#------------------- End Display the table with SELECT -----------------------

&show_modification_form();


# ------------------- SUBS SECTION -------------------------------------------
#
# --------------------- Sub show_modification_form -----------------------
sub show_modification_form {
  print "<h4>To add another product, fill in the form below and click \"Proceed\"</h4>" . $q->start_form(),
        "<p>Product name: ",
        $q->textfield(-name=>'product_to_add',
                      -value=>''), "<font color=red>", $errmsg_NoProductName, "</font>",
        "<br>Energy value per unit: ",
        $q->textfield(-name=>'energy_to_add',
                      -value=>''), "<font color=red>", $errmsg_NoEnergyValue, "</font>",
        "<p> Action : ",
        $q->radio_group(-name=>'action', -default=>'-',# -linebreak=>'true',
                        -values=> ['insert', 'delete'],
                        -labels=> {'insert' => 'Insert', 'delete' => 'Delete'}),
        "<p>", $q->submit(value=>'Proceed');
  print $q->end_form;
}
# --------------------- End of Sub show_modification_form -----------------------

# ----------------------- Sub delete($catId, $catTitle)--------------------------
sub delete{
   my $product = shift;
   $delete_query = 'DELETE FROM ' . $tablename . ' WHERE product=\'' . $product . '\'';
   $sth = $dbh->prepare($delete_query) || die $dbh->errstr;
   $sth->execute() || die $sth->errstr;
   $sth->finish;
}
# --------------------- End Sub delete($catId, $catTitle)------------------------

# --------------------- Sub insert($product_to_add, $energy_to_add) ------------
sub insert{
   my $product = shift;
   my $energy = shift;
   $insert_query = 'INSERT INTO ' . $tablename . ' (product, energy) values (\'' .
     $product . '\', ' . $energy . ')';
   $sth = $dbh->prepare($insert_query) || die $dbh->errstr;
   $sth->execute || die $sth->errstr;
   $sth->finish;
}
# --------------------- End Sub insert($product_to_add, $energy_to_add) --------