#!/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