#! /usr/bin/perl -w

use strict;
use CGI;
use DBI;

my $CGI = new CGI();
print $CGI -> header();

# display_form(); # see InsectPlant.htm

if ($CGI -> param()){
   my $superfam = $CGI -> param('superfam');
   my $family   = $CGI -> param('family');
   my $subfam   = $CGI -> param('subfam');
   my $tribe    = $CGI -> param('tribe');
   my $name     = $CGI -> param('name');
   my $author   = $CGI -> param('author');

   results_table();

#33############## Prepare query ############################
      
      # set matching criteria for query string
      my $sup = " like '%$superfam%'";
      my $fam = " like '%$family%'";
      my $sub = " like '%$subfam%'";
      my $tri = " like '%$tribe%'";
      my $nam = " like '%$name%'";
      my $aut = " like '%$author%'";
      
      # test for search criteria and create mysql search string
      my $sort = "Name";
      my $qsearch = "none";
      if ($superfam) {$qsearch = "Superfam $sup";}
      if ($family) {
         if   ($qsearch eq 'none') {$qsearch = "Family $fam";}
         else {$qsearch .= " && Family $fam";}}
      if ($subfam) {
         if   ($qsearch eq 'none') {$qsearch = "Subfam $sub";}
         else {$qsearch .= " && Subfam $sub";}}
      if ($tribe) {
         if   ($qsearch eq 'none') {$qsearch = "Tribe $tri";}
         else {$qsearch .= " && Tribe $tri";}}
      if ($name) {
         if   ($qsearch eq 'none') {$qsearch = "Name $nam";}
         else {$qsearch .= " && Name $nam";}}
      if ($author) {
         if   ($qsearch eq 'none') {$qsearch = "Author $aut";}
         else {$qsearch .= " && Author $aut";}}

      if ($qsearch eq 'none') {print "No search criteria entered";}
      else {print "Search: $qsearch sorted by $sort";}

#108##################### Access MySQL database ###############################

   my $database = "poboyski";
   my $host = "localhost";
   my $dbconnection = "$database:$host:3306";

   my $data_source = "dbi:mysql"; #define a mysql database
   my $user = "poboyski";
   my $passwd = "WillieDog";

   my $dbh = DBI -> connect("$data_source:$dbconnection", $user, $passwd) or
       die "failed to connect to $dbconnection\n";

   my $statement = qq< select Name, concat(Author,", ",Year) as Described, 
                              Synonym, Tribe, Subfam, Family, Superfam, Photo
                              from Leptax
                              where $qsearch order by $sort>; 

#131############ Process query results ################################

   my $cursor = $dbh -> prepare( $statement );
   $cursor -> execute();

   while( my $query = $cursor->fetch ) {

print <<EOT;
      <tr>
         <td width="35"> <img src="$query->[6]"> </td>
         <td width="35"> $query->[0] </td>
         <td width="35"> $query->[1] </td>
         <td width="35"> $query->[2] </td>
         <td width="35"> $query->[3] </td>
         <td width="35"> $query->[4] </td>
         <td width="35"> $query->[5] </td>
         <td width="35"> <a href="http://nature.berkeley.edu/~poboyski/">link</a> </td>
      </tr>

EOT
#          <td width="35"> <a href="LepDetail.pl?Name=$query->[1]">link</a> </td>
   }

      print '</table>';
      print '</td>';
      print '</tr>';
      print "<BR>";

   $cursor -> finish();  # close off cursor
   $dbh -> disconnect(); # close off database
}

#180############## Set up results table  #############################

sub results_table {
   print "<BR>";
   print '<table width="98%" border="1">';
   print '<tr>';
      print '<td width="35"><b>Photo</b></td>';
      print '<td width="35"><b>Current Name</b></td>';
      print '<td width="35"><b>Author</b></td>';
      print '<td width="35"><b>Search Name</b></td>';
      print '<td width="35"><b>Tribe</b></td>';
      print '<td width="35"><b>Subfamily</b></td>';
      print '<td width="35"><b>Family</b></td>';
      print '<td width="35"><b>Superfamily<b></td>';
      print '<td width="35"><b>Complete Record</b></td>';
   print '</tr>';
}
