#!/biol/programs/perl/bin/perl -w
use strict;
use DBI;
use CGI qw(:standard);

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

my $cgi;

$cgi = new CGI;
use CGI::Carp qw(fatalsToBrowser);

my $username = $cgi->param('username_box');
my $password = $cgi->param('password_box');


my $dbh;


$dbh = DBI->connect("DBI:mysql:host=localhost;database=db_name", "$username","$password", {PrintError =>0, RaiseError => 1}) || die "Database connection not made";


my $plate = $cgi->param("plate");


print $cgi->start_form (-method => "POST");
print $cgi->textfield (-name => "plate",
                                                -value => $plate,
                                                -size => 40);
print $cgi->submit (-name => "button", -value=>"search");
print $cgi->end_form ();


search_members ($plate) if $plate;

my $sth;
my $sth2;

my $sql = qq{select * from plate};

$sth = $dbh->prepare ($sql);
$sth->execute();

while (my $row = $sth->fetchrow_arrayref)     {


# this prints the values in the plate table so program is definately
# connecting to database.

     print join ("\t", @$row), "<P>";




 }


# finish the statement


#  $sth->finish();
#  $sth2->finish();

$dbh->disconnect();


print $cgi->end_html ();





sub search_members



my ($plate) = shift;
my ( $sth2, $count, $count2);

print "<table width=100% bgcolor=#ffff99><tr><td>Search results for keyword:<b><i><font color=red> $plate</font></i></b> </td><tr></table>\n",
#               $cgi->escapeHTML ($plate);
                $cgi->escapeHTML;


      $sth2 = $dbh->prepare (qq{
                              SELECT * FROM plate WHERE plate_id LIKE ?
                                OR genomic_dna_id LIKE ?
                                OR sybr_green_id LIKE ?
                                OR rox_id LIKE ?
                                OR genomic_production_id LIKE ?
                                OR template_id LIKE ?
                                OR barcode_id LIKE  ?
                                OR expiry_date LIKE ?
                                OR incubation_time LIKE ?

                      });



          $sth2->execute("%" . $plate . "%","%" . $plate . "%","%" . $plate . "%","%" . $plate . "%","%" . $plate . "%","%" . $plate . "%","%" . $plate . "%", "%" .  $plate . "%","%" . $plate . "%" );


        $count = 0;
        $count2 = 0;
        # fetchrow_hashref returns a reference to a hash containing column values for the next row of the result set


        while (my $hash_ref = $sth->fetchrow_hashref ())
        {
                format_plate_entry ($hash_ref);
                ++$count;
        }

        if ($count > 1)     {

        #print $cgi->p ("$count entries found<P><HR>");
        print "<table width=400 bgcolor=#ffcc66><tr><td><b>$count</b> entries found in table PLATE<P></td></tr></table><hr>";
      }


        elsif ($count == 1)   {

            print "<table width=400 bgcolor=#ffcc66><tr><td><b>$count</b> entry found in table PLATE<P></td></tr></table><hr>";
      }


}


sub format_plate_entry
{
my ($entry_ref) = shift;
my ($address);

        # encode characters that are special in HTML
        foreach my $key (keys (%{$entry_ref}))
        {
                $entry_ref->{$key} = $cgi->escapeHTML ($entry_ref->{$key});
        }



  print "<P><table width=400 bgcolor=#ffff99><tr><td><STRONG>Table name: Plate</STRONG><BR></td></tr></table>\n";

        print "<table width=400 bgcolor=#ffffcc><tr><td><B>Plate_id:</b> $entry_ref->{plate_id}<br></td></tr></table>\n" if $entry_ref->{plate_id};
        print "<table width=400 bgcolor=#ffffcc><tr><td><b>Sybr_green_id:</b> $entry_ref->{sybr_green_id}<br></td></tr></table>\n" if $entry_ref->{sybr_green_id};
        print "<table width=400 bgcolor=#ffffcc><tr><td><b>Rox_id:</b> $entry_ref->{rox_id}<br></td></tr></table>\n" if $entry_ref->{rox_id};
        print "<table width=400 bgcolor=ffffcc><tr><td><b>Genomic_production_id:</b> $entry_ref->{genomic_production_id}<br></td></tr></table>\n" if $entry_ref->{genomic_production_id};
        print "<table width=400 bgcolor=#ffffcc><tr><td><b>Template_id:</b> $entry_ref->{template_id}<br></td></tr></table>\n" if $entry_ref->{template_id};
        print "<table width=400 bgcolor=#ffffcc><tr><td><b>Genomic_dna_id:</b> $entry_ref->{genomic_dna_id}<br></td></tr></table>\n" if $entry_ref->{genomic_dna_id};

        print "<table width=400 bgcolor=#ffffcc><tr><td><b>Expiry date:</b> $entry_ref->{expiry_date}<BR></td></tr></table>\n" if $entry_ref->{expiry_date};
        print "<table width=400 bgcolor=#ffffcc><tr><td><b>Barcode_ID: </b>$entry_ref->{barcode_id}<BR></td></tr></table>\n" if $entry_ref->{barcode_id};
        print "<table width=400 bgcolor=#ffffcc><tr><td><b>Incubation Time:</b> $entry_ref->{incubation_time}<BR></td></tr></table>\n" if $entry_ref->{incubation_time};
        print "<BR>\n";


}