#!/usr/bin/perl -w

use DBI;
use CGI qw(:all);
use CGI::Carp qw(fatalsToBrowser); # Makes die work acceptably! Very important!!

PrintHtmlHeaders();
$db = ConnectAndDefineDB();
MakeForm();
CheckDataSubmission($db);
CheckLastnameLookup($db);
CheckEmailLookup($db);
CheckListAll($db);     




sub PrintHtmlHeaders
{
      print header(), start_html("Email Signup");
      print h1("Mailing List Signup");
      print p("Please fill out this form and you will be notified via
      email about updates and future product announcements");
}


sub ConnectAndDefineDB
{
     my ($db, $cmd);
# Connect to the database 
     $db = DBI->connect("DBI:CSV:f_dir=.") || 
           die "Cannot connect:  $DBI::errstr\n";

     if (!-e "namedata")
     {
          $cmd = $db->prepare("CREATE TABLE namedata (
                               first varchar(32) not null,
                               last varchar(32) not null,
                               email varchar(32) not null)");
          $cmd->execute() || die "Cannot create table!\n";
     }
     return $db;
}   


sub MakeForm
{
       print start_form(); 
       print p("First Name: ",textfield(-name => 'first name')),br,
             p("Last Name: ", textfield(-name => 'last name')),
             p("Email: ", textfield(-name=> 'email')), hr, 
             submit(-name => 'Submit User Data'),
             submit(-name => 'Lookup By Last Name'),
             submit(-name => 'Lookup By Email'),
             p(submit(-name => 'List All')), end_form;
}



sub CheckDataSubmission
{
      my ($db) = shift;
      my ($first, $last, $email, $rows);

      #  Did user hit the submit button?
      if (param("Submit User Data"))
      {
         $first = param('first name');
         $last  = param('last name');
         $first = FormatStr($first);
         $last  = FormatStr($last);
         $email = param('email');
         $email = lc $email;
         if (!$first || !$last || !$email)  {die "Missing Field!\n"}
      
      #  Prepare an insert statement.  Get object handle back!
         $rows = $db->prepare(
                          "INSERT INTO namedata (first, last, email)
                           VALUES            ('$first', '$last', '$email')") ;
      
      #  Run the prepared insert statement.
         $rows->execute();
      }
}


sub CheckLastnameLookup
{
     my ($db) = shift;
     my ($last, $doit, $statement);

     if (param("Lookup By Last Name"))
     {
           $last = param('last name'); 
           $last = FormatStr($last); 
           $statement = "SELECT * FROM namedata
                         WHERE last = '$last'";
           $doit = $db->prepare($statement);
           $doit->execute();
           OutputRows($doit);
           $db->disconnect() || die "Cannot disconnect\n";
     }
}


sub CheckEmailLookup
{
     my ($db) = shift;
     my ($email, $doit, $statement);

     if (param("Lookup By Email"))
     {
           $email = param('email'); 
           $email  =~ s/^\s*(.*?)\s*$/$1/; 
           $email  = lc $email;
           $statement = "SELECT * FROM namedata
                         WHERE email = '$email'";
           $doit = $db->prepare($statement);
           $doit->execute();
           OutputRows($doit);
           $db->disconnect() || die "Cannot disconnect\n";
     }
}


sub FormatStr
{
    my ($s) = shift;

    $s =~ s/^\s*(.*?)\s*$/$1/;
    $s = ucfirst(lc $s);
    return $s;
}


sub CheckListAll
{
     my ($db) = shift;
     my ($doit, $statement);
     
     #  If user hit 'List All' button, show all the rows in the database!!
     if (param('List All')) {
           $statement = "SELECT * from namedata";
           $doit = $db->prepare($statement);
           $doit->execute();
           OutputRows($doit);
           $db->disconnect() || die "Cannot disconnect\n";
     }
}



sub OutputRows
{
    my ($doit) = shift;
    my ($rowref, $output);

    while ($rowref = $doit->fetchrow_hashref)
    {
         $output = sprintf("%-20s%-10s%-40s", $rowref->{last},
                                           $rowref->{first}, $rowref->{email});
         print p($output);
    } 
}

print end_html;