#! /usr/bin/perl -w

# use strict;
use CGI;
use DBI;

my $CGI = CGI->new;

print $CGI->header();

# open the accessDB file to retrieve the database name, host name,
# user name and password
open( ACCESS_INFO, "<../..\/accessDB" )
  || die "Can't access login credentials";

my $database = <ACCESS_INFO>;
my $host     = <ACCESS_INFO>;
my $user     = <ACCESS_INFO>;
my $passwd   = <ACCESS_INFO>;

chomp( $database, $host, $user, $passwd );
close(ACCESS_INFO);

# 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');
    my $archi    = $CGI->param('archipelago');
    my $isle     = $CGI->param('island');
    my $region   = 'French Polynesia';
    my @args;

    results_table();

############## 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";
    my $psearch = "none";
    my $qregion = "Region = French Polynesia";

    #if ($superfam) {
    #    $qsearch = "Superfam $sup";
    #}
    if ($superfam) {
        $qsearch = "Superfam LIKE ?";
        push( @args, "%$superfam%" );
    }
    if ($family) {
        if ( $qsearch eq 'none' ) {
            $qsearch = "Family LIKE ?";
        }
        else {
            $qsearch .= " && Family LIKE ?";
        }
        push( @args, "%$family%" );
    }
    if ($subfam) {
        if ( $qsearch eq 'none' ) {
            $qsearch = "Subfam LIKE ?";
        }
        else {
            $qsearch .= " && Subfam LIKE ?";
        }
        push( @args, "%$subfam%" );
    }
    if ($tribe) {
        if ( $qsearch eq 'none' ) {
            $qsearch = "Tribe LIKE ?";
        }
        else {
            $qsearch .= " && Tribe LIKE ?";
        }
        push( @args, "%$tribe%" );
    }
    if ($name) {
        if ( $qsearch eq 'none' ) {
            $qsearch =
                "Name IN "
              . "(SELECT DISTINCT Name "
              . "FROM `Lepsyn` WHERE Synonym LIKE ?)";
        }
        else {
            $qsearch .= " && " 
              . "Name IN (SELECT DISTINCT Name "
              . "         FROM `Lepsyn` "
              . "         WHERE Synonym "
              . "         LIKE ?)";
        }
        push( @args, "%$name%" );
    }
    if ($author) {
        if ( $qsearch eq 'none' ) {
            $qsearch = "Author LIKE ?";
        }
        else {
            $qsearch .= " && Author LIKE ?";
        }
        push( @args, "%$author%" );
    }

    if ( $archi ne 'none' ) {
        $psearch = "Archipelago = ?";
        $qregion = "Archipelago = $archi";
        push( @args, "$archi" );
    }
    if ( $isle ne 'none' ) {
        $psearch = "Island = ?";
        $qregion = "Island = $isle";
        push( @args, "$isle" );
    }

    if ( ( $qsearch ne 'none' ) && ( $psearch ne 'none' ) ) {
        $qsearch .= " AND "
          . "Name IN (SELECT DISTINCT Name "
          . "         FROM `Lepdis` "
          . "         WHERE $psearch)";
    }
    if ( ( $qsearch eq 'none' ) && ( $psearch ne 'none' ) ) {
        if ( $archi ne 'none' and $isle ne 'none' ) {
            print '<a href="FPLeps.htm"
              target="_parent">
              Select one of Archiplago or Island, not both - NEW SEARCH</a>';

            #$qsearch = "Name IN (SELECT DISTINCT Name " .
            #               "FROM `Lepdis` WHERE Archipelago = ? " .
            #                              "AND Island = ?)";
            #push(@args,$archi);
            #push(@args,$isle);
        }
        elsif ( $archi ne 'none' ) {
            $qsearch = "Name IN (SELECT DISTINCT Name "
              . "                FROM `Lepdis` "
              . "                WHERE Archipelago = ?)";
        }
        elsif ( $isle ne 'none' ) {
            $qsearch = "Name IN (SELECT DISTINCT Name "
              . "                FROM `Lepdis` "
              . "                WHERE Island = ?)";
        }
    }
    if ( ( $qsearch ne 'none' ) && ( $psearch eq 'none' ) ) {
        $qsearch .= " AND "
          . "Name IN (SELECT DISTINCT Name "
          . "         FROM `Lepdis` "
          . "         WHERE Region = ? )";
          push( @args, $region);
    }
    if ( ( $qsearch eq 'none' ) && ( $psearch eq 'none' ) ) {
        print '<a href="FPLeps.htm"
        	target="_parent"> No search criteria entered - NEW SEARCH</a>';
    }
    else {
        print <<EOT;
	 <p align="center"><font size="+3" face="Times New Roman, Times, serif">
		   Lepidoptera of French Polynesia - Search Results</font></p>
	 <p align="center"><a
          href="FPLeps.htm"
				target="_parent">NEW SEARCH</a></p>
	 <p align="left">$qregion</p>
EOT
    }

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

    my $dbconnection = "$database:$host:3306";
    my $data_source  = "dbi:mysql";              #define a mysql database

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

    my $statement =  "SELECT Name, Author, Tribe, Subfam, "
                   . "       Family, Superfam, Photo "
                   . "FROM Leptax "
                   . "WHERE $qsearch "
                   . "ORDER BY $sort ";

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

#debugging block
#    print <<EOdebug;
#   <p>
#   \$psearch:
#   $psearch
#   <br/>
#   \$qsearch:
#   $qsearch
#   <br/>
#   \$statement:
#   $statement
#   <br/>
#   \@args:
#   @args
#   </p>
#EOdebug
#    local $" = ')(';
#    print "\@args contains: ", scalar(@args), " elements:\n";
#    print "(@args)\n";
#end debugging block

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

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

        print <<EOT;
        <tr>
            <td width="150"> <img src="$query->[6]" width="150" height="100"></td>
            <td width="100"> $query->[0] </td>
            <td width="100"> $query->[1] </td>
            <td width="75">  $query->[2] </td>
            <td width="75">  $query->[3] </td>
            <td width="75">  $query->[4] </td>
            <td width="75">  $query->[5] </td>
            <td width="50"> <a href="LepDetail.pl?
Name=$query->[0]&
Author=$query->[1]&
Tribe=$query->[2]&
Subfamily=$query->[3]&
Family=$query->[4]&
Superfamily=$query->[5]&
Photo=$query->[6]" 
                            target="_parent">full record</a></td>
        </tr>

EOT

    }

    print '</table>';
    print '</td>';
    print '</tr>';
    print "<BR>";
    print '<p><a href="FPLeps.htm" target="_parent">NEW SEARCH</a></p>';

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

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

sub results_table {
    print "<BR>";
    print '<table width="98%" border="1">';
    print '<tr>';
    print '<td width="150"><b>Photo</b></td>';
    print '<td width="100"><b>Name</b></td>';
    print '<td width="100"><b>Author</b></td>';
    print '<td width="75"><b>Tribe</b></td>';
    print '<td width="75"><b>Subfamily</b></td>';
    print '<td width="75"><b>Family</b></td>';
    print '<td width="75"><b>Superfamily<b></td>';
    print '<td width="50"><b>Full Record</b></td>';
    print '</tr>';
}
