#!/usr/bin/perl -Tw

use strict;
#use lib qw\/usr/dom/xbrie/brian/Hughes/perl\;

## set the path
#$ENV{"PATH"} = "/usr/local/bin:/usr/ucb:/bin:/usr/bin";

use CGI qw(:standard);
#use CGI::Carp;
use DBI;
my $dbh = DBI->connect("DBI:mysql:book","user", "password");

$|++; # Make the pipes piping hot!

my $cgiquery = new CGI;
my $myself = $cgiquery->self_url;


print $cgiquery->header(-expires=>'now');

print $cgiquery->start_html(-"title"=>'Database funtionality',
                            -"author"=>'brian@brie.com',
                            -"meta"=>{'keywords'=>'brians database',
                                    'copyright'=>'Copyright 1997 Big Brie'},
                            -"BGCOLOR"=>'white');

print qq{<A NAME="top">\n};
print "<H1 ALIGN=center>Database Interface</H1>\n";
print '<P ALIGN=center>Developed by the man himself<BR><BR><A HREF="http://www.brie.com/brian/">Mr. Brian Lavender</A>' , "\n";

#
# Do we have a search submitted
#
my $keyword;
if ($cgiquery->param('keyword')){ #assigns keyword to scalar
    $keyword = $cgiquery->param('keyword');
}
$keyword =~ s/(.*)/\U$1/g;

print "<P>";

# I moved search results up so I took out this link to it.
#if ($keyword) { #Make a quick link to the results
#    print qq{<A HREF="$myself#results">See Results</A> by scrolling down};
#}

# Start the Form part of html for any and all forms on page. This must go before any form elements

print $cgiquery->startform (-method=>'get');

# Search form of page

print "<H2>Search the Database</H2>\n";
print qq{Will search the <A HREF="$myself#data">&quot;approved data&quot; table</A> below<BR>\n};
print "in name and city columns only\n";
print "<P>\n";
print "Search Keyword (10 characters max): ", $cgiquery->textfield(-name=>'keyword',
                                                   -size=>10,
                                                   -maxlength=>10);
print " ",$cgiquery->submit(-name=>'add',
                            -value=>'search');
print "\n";
print "<H2>Database Server Status</H2>\n";

# &dbase_run;

# $dbh = Msql->connect;
# $dbh -> selectdb("book");



# Results of search
# only activated if a search was submitted.

if ($keyword) {
     print '<A NAME="results">' , "\n";
     print "<H2>Search results</H2>\n";
     my $query = "SELECT * FROM customers WHERE cname LIKE '\%$keyword\%' or city LIKE '\%$keyword\%'";
     my   $sth = $dbh->prepare($query);
     if ($sth -> execute) {
         #if ($Msql::db_errstr) {
         #   print "The select failed because $Msql::db_errstr <BR>\n";
         #   $Msql::db_errstr = undef;
         #   last;
         #}
         print "<TABLE BORDER CELLPADDING=5>\n"; #Make this table a real table
         print "<TR>\n";
         foreach (@{$sth->{NAME}}) { # print the Headers of the table
            print "<TH>", $_ , "</TH>\n";
         }
         print "</TR>\n";
         while (my @row = $sth->fetchrow()){ #Get each row from Msql object
            print "<TR>\n";
            foreach (@row) { # print all the elements
               print "<TD>", $_ , "</TD>\n";
            }
            print "</TR>\n";
        }
        print "</TABLE>\n";
     } else {
        print "The query failed because <BR>\n";
     }
}

print "<H2>Data Waiting for approval</H2>\n";
print '<A HREF="http://brie.com/cgi-bin/testing/entryadmin.pl">Follow this link</A>';

print "<H2>Submit data for review here</H2>\n";

print " \n\n";
print "<P>\n";

# Get data for new insert


print "Customer Number (xxxx): ", $cgiquery->textfield(-name=>'cnum',
                                                       -size=>4,
                                                       -maxlength=>4,
                                                       -override=>1);
print "<BR>\n";
print "Customer Name (10 Char max): ", $cgiquery->textfield(-name=>'cname',
                                              -size=>12,
                                              -maxlength=>10,
                                              -override=>1);
print "<BR>\n";
print "City (10 Char max): ", $cgiquery->textfield(-name=>'city',
                                     -size=>12,
                                     -maxlength=>10,
                                     -override=>1);
print "<BR>\n";
print "Rating (xxx): ", $cgiquery->textfield(-name=>'rating',
                                             -size=>3,
                                             -maxlength=>3,
                                             -override=>1);
print "<BR>\n";
print "Salesman No (xxxx): ", $cgiquery->textfield(-name=>'snum',
                                                   -size=>4,
                                                   -maxlength=>4,
                                                   -override=>1);
print "<P>\n";
print $cgiquery->submit(-name=>'add',
                     -value=>'Data Insert');



print $cgiquery->endform , "\n";
print "<P>\n";

#place all the values from the form into a scalar

my $cnum= $cgiquery->param('cnum');
my $cname= $cgiquery->param('cname');
$cname =~ s/(.*)/\U$1/g; # Uppercase this thing
my $city= $cgiquery->param('city');
$city =~ s/(.*)/\U$1/g; # I know I should use a subroutine
my $rating= $cgiquery->param('rating');
my $snum= $cgiquery->param('snum');




if ( $cgiquery->param('cnum') && ($cgiquery->param('add') eq 'Data Insert') ){
    # local ($Msql::QUIET) = 1;
    print "$cnum $cname $city $rating $snum<BR><BR>\n";
    my $query = "insert into entries values ($cnum, '$cname','$city', $rating , $snum)";
    my $sth = $dbh->prepare($query);
    if ($sth -> execute){ 
      print "<BR>It made it here<BR>\n";
    }else{
       print "The insert failed <BR>\n";
    }
}



# This is the stuff in storage

print qq{<A NAME="data">\n};
print "<H2>This is all the &quot;approved data&quot; in Storage</H2>\n";
print qq{<A HREF="$myself#top">TOP</A> \n <P> \n};
my $query = "select * from customers";
my $sth = $dbh->prepare($query);
$sth->execute;
print "<TABLE BORDER CELLPADDING=5>\n"; #Make this table a real table
print "<TR>\n";
foreach (@{$sth->{NAME}}) { # print the Headers of the table
    print "<TH>", $_ , "</TH>\n";
}
print "</TR>\n";
print "\n";
while (my @row = $sth->fetchrow()){ #Get each row from the Msql object
    print "<TR>\n";
    foreach (@row) { # print all the elements
        print "<TD>", $_ , "</TD>\n";
    }
    print "</TR>\n";
}
print "</TABLE>\n";

print q{
  <P>
  <HR>
  <FONT SIZE=-1>
  <ADDRESS>

  <A HREF="http://www.brie.com"><IMG SRC="/pictures/smllogo.gif" ALIGN=baseline WIDTH=50 BORDER=0 HEIGHT=34></A>
  (C) 1997 
  <P>

  <A HREF="http://www.brie.com"><B>Brie Web Publishing</B></A><BR>
  PO Box 19184<BR>
  Sacramento, CA  95819<BR>
  (916) 443-6195
  </ADDRESS>
  <FONT SIZE=-1>
};

print $cgiquery->end_html, "\n"; # That is the end of it
close (STDIN); close (STDERR); close (STDOUT); # Just in case these don't close automatically