#! /usr/local/bin/perl -T
use strict;
use warnings;
use DBI;
use CGI;
use CGI::Carp; This Line Isn't Really In The Actual Program
use Time::HiRes qw ( gettimeofday tv_interval);
my $now = [gettimeofday];
use Fcntl qw(:flock);
$ENV{'PATH'} = '/bin:/usr/bin';
$ENV{'IFS'} = '';
open LOG, ">>/web/webdata/w1sdm/73/database.log" or die "Cannot open log file\n$!\n";
flock (LOG, LOCK_EX());
my @days   = ("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday");
my @months = (" ", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "D
my ($sec,$min,$hour,$mday,$mon,$year,$wday) = (localtime(time))[0,1,2,3,4,5,6];
my $time = sprintf("%02d:%02d:%02d",$hour,$min,$sec);
$year += 1900;
++$mon;
my $date = "$days[$wday], $months[$mon] $mday, $year at $time";
print LOG "Date: $date\n";
my $dir = "/web/webdata/w1sdm";
our ($db_connected_flag, $dbh);
my $q = new CGI;
my $title  = lc($q->param('title'));
my $author = lc($q->param('author'));
my $form_month = $q->param('issue_month');
my $form_year = $q->param('issue_year');
my $info = "Search submission time: $date\n\n<br>\n\nBrowser: $ENV{'HTTP_USER_AGENT'}\n\n<br>\n\nIP Address: $ENV{'REMOTE_ADDR'}\n\
print LOG "Browser: $ENV{'HTTP_USER_AGENT'}\nIP Address: $ENV{'REMOTE_ADDR'}\nTitle: $title\nAuthor: $author\nMonth: $form_month\nY
my ($author_search_terms, $title_search_terms, $mysql_command, @mysql_data, @rows, $html, @search_terms);
if ($author) { $author_search_terms = clean($author); }
if ($title) { $title_search_terms = clean($title); }
$html = load_file("$dir/73/templates/results.html");
$mysql_command = "SELECT * FROM data WHERE";
if ($author_search_terms) {
   ($mysql_command, @mysql_data) = expand_terms($mysql_command, 'author', $author_search_terms, @mysql_data);
}
if ($title_search_terms) {
   ($mysql_command, @mysql_data) = expand_terms($mysql_command, 'title', $title_search_terms, @mysql_data);
}
if ($form_month) {
   ($mysql_command, @mysql_data) = append_term($mysql_command, 'month', $form_month, @mysql_data);
   $html =~ s/<option selected="selected"><\/option>/<option><\/option>/;
   $html =~ s/<option>$form_month<\/option>/<option selected="selected">$form_month<\/option>/;
}
if ($form_year) {
   ($mysql_command, @mysql_data) = append_term($mysql_command, 'year', $form_year, @mysql_data);
   $html =~ s/<option selected="selected"><\/option>/<option><\/option>/;
   $html =~ s/<option>$form_year<\/option>/<option selected="selected">$form_year<\/option>/;
}
 if (! $form_year and ! $form_month) {
  $html =~ s/<option><\/option>/<option selected="selected"><\/option>/g;
}
print LOG "MySQL Command: $mysql_command\nMySQL Data: @mysql_data\n";
my $search_start_time = [gettimeofday];
@rows = get_records($mysql_command, @mysql_data);
my $search_time = substr(sprintf ( "%.6f", tv_interval $search_start_time, [gettimeofday] ), 0, 8) . " seconds";
$html =~ s/<!--FORM_AUTHOR-->/$author/g;
$html =~ s/<!--FORM_TITLE-->/$title/g;
my $table = "<table class=\"results\">\n\n<tr class=\"row_yellow\"><th>Date</th><th>Title</th><th>Author</th><th>Page</th></tr>\n\n";
$info .= "<br>\n\nNumber of results: " . ($#rows + 1) . "\n\n";
print LOG "Results: $#rows\n";
if ($#rows < 0) {
   $table .= "<tr class=\"row_green\"><td colspan=\"4\">No results using those search terms...</td></tr>\n\n";
} else {
  my $row_color;
  foreach my $x (0..@rows) {
     $row_color = "row_blue";
     if ($x / 2 == int($x / 2)) { $row_color = "row_green"; }
     if ($rows[$x][0]) {
      $table .= "<tr class=\"$row_color\"><td class=\"col1\">$rows[$x][1] $rows[$x][2]</td><td class=\"col2\">$rows[$x][3]</td><td class=\"col>$rows[$x][4]</td><td class=\"col4\">$rows[$x][5]</td></tr>\n\n";
    }
  }
}
$table .= "</table>\n\n";
$html =~ s/<!--TABLE-->/$table/;
my $creation_time = substr(sprintf ( "%.6f", tv_interval $now, [gettimeofday] ), 0, 8) . " seconds";
my $html_size = int(length($html) / 1000) . "K bytes";
$info .= "<br>\n\nHTML file size: ~$html_size\n\n<br>\n\nDatabase search time: $search_time\n\n<br>\n\nPage creation time: $creation_time\n\n";
$html =~ s/<!--INFO-->/$info/;
print "Content-Type: text/html\n\n$html";
print LOG "Database access time: $search_time\nPage creation time: $creation_time\n" . ('*' x 75) . "\n";
flock (LOG, LOCK_UN());
sub load_file {
  my $filename = shift;
  open (IN, "<$filename") or die "Content-Type: text/plain\n\nCould not open file: $filename\n";
  return join('', <IN>);
}
sub get_records {
  my ($mysql_command, @mysql_data) = @_;
  $dbh = DBI->connect("dbi:mysql:secretstuff","secretstuff","secretstuff");
  my $sth = $dbh->prepare($mysql_command);
  $sth->execute(@mysql_data);
  my @rows = ();
  while ( my @row = $sth->fetchrow_array() ) { push @rows, [@row]; }
  return @rows;
}
sub clean {
  my $string = shift;
  $string =~ s/ /SpAcE/g;
  $string =~ s/\s*[\W]*//g;
  $string =~ s/SpAcE/ /g;
  return $string;
}
sub append_term {
  my ($this_command, $element, $term, @this_data) = @_;
  if (index($this_command, "LIKE") > -1 or index($this_command, " = ") > -1) { $this_command .= " AND "; }
  $this_command .= " $element = (?)";
  push @this_data,$term;
  return ($this_command, @this_data);
}
sub expand_terms {
  my ($this_command, $element, $search_terms, @this_data) = @_;
  @search_terms = split " ", $search_terms;
  foreach my $term (@search_terms) {
    if (index($this_command, "LIKE") > -1) { $this_command .= ' AND'; }
    $this_command .= " $element LIKE (?)";
    push @this_data, "\%$term\%";
 }
  return ($this_command, @this_data);
}