#!/usr/bin/perl
$|= 1;

use strict;
use DBI;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use Config::Simple;
use chaos;
use FindBin;

Config::Simple->import_from("$FindBin::Bin/../../config/cgi-databases.ini", \my %Config);

my ($cgi) = "$cgiroot/techpapers.cgi";
my ($database) = $Config{"chaos.database"};
my ($hostname) = $Config{"chaos.hostname"};
my ($password) = $Config{"chaos.password"};
my ($username) = $Config{"chaos.username"};

# Set up the vars so that they point to the right offsets in Papers table
my ($ID)        = 0;   # 1    id           INT,              -
my ($REF_KEY)   = 1;   # 2    ref_key      VARCHAR(255),      |
my ($TYPE)      = 2;   # 3    type         VARCHAR(16),       |
my ($TITLE)     = 3;   # 4    title        VARCHAR(255),      |
my ($BOOKTITLE) = 4;   # 5    booktitle    VARCHAR(255),      |
my ($VOLUME)    = 5;   # 6    volume       VARCHAR(255),      |
my ($NUMBER)    = 6;   # 7    number       VARCHAR(255),      |
my ($PUB)       = 7;   # 8    publisher    VARCHAR(255),       > schema
my ($ORG)       = 8;   # 9    organization VARCHAR(255),      |
my ($INST)      = 9;   # 10   institution  VARCHAR(255),      |
my ($JOURNAL)   = 10;  # 11   journal      VARCHAR(255),      |
my ($YEAR)      = 11;  # 12   year         INT,               |
my ($MONTH)     = 12;  # 13   month        VARCHAR(32),       |
my ($PAGES)     = 13;  # 14   pages        VARCHAR(255),      |
my ($FNAME)     = 14;  # 15   file_name    VARCHAR(255),      |
my ($NOTE)      = 15;  # 16   note         TEXT,              |
my ($ABSTRACT)  = 16;  # 17   abstract     TEXT              -

print header;
print chaos_start;

my ($DBH) = DBI->connect("DBI:mysql:$database:$hostname",$username,$password) || die "Can't connect: $DBI::errstr\n";

if (param("lastname") or param("topic") or param("year") or param("abstract") or param("topicID")) {
  my ($lastname, $topic, $year, $abstract, $topicID);

  print "<h3>Publications";

  if (param("lastname") && (param("lastname") ne "all")) {
    $lastname = param("lastname");
    print " by ";
    print FetchFullName($lastname);
  } else {
    $lastname = "all";
  }

  if (param("topic") && (param("topic") ne "all")) {
    $topic = param("topic");
    print " concerning ";
    print param("topic");
  } else {
    $topic = "all";
  }

  if (param("year") && (param("year") ne "all")) {
    $year = param("year");
    print " during ";
    print param("year");
  } else {
    $year = "all";
  }

  $abstract = param("abstract");

  if ( param("topicID") && ( param("topicID") =~ m/^\d+$/ ) ) {
    print " concerning ";
    print FetchTopicName(param("topicID"));
  }
  $topicID = param("topicID");
  print " (<a href='#form'>New Search</a>)</h3>\n";

  ProcessQuery($lastname, $topic, $year, $abstract, $topicID);
  ShowMenu($lastname, $topic, $year, $abstract);

} else {
  print "<h3>Searchable Publication Database</h3>\n";
  ShowMenu();
}

$DBH->disconnect();

print chaos_end;

# ===================================================================
# Name: ShowMenu
# Desc: Fill in a combo box with authors' names
#       the second argument will contain the TopicID if a canned qurry 
#       was made so that a default selection for the topic will be made
# ===================================================================
sub ShowMenu {
  my ($lastname, $topic, $year, $abstract) = @_;
  my ($hstmt, $i, @arr, @names, @years, @topics);

  # Generate author list
  $hstmt = $DBH->prepare("SELECT lastname FROM authors ORDER BY lastname");
  $hstmt->execute();
  while(@arr = $hstmt->fetchrow_array()) {
    push(@names, $arr[0]);
  }

  # Generate year list
  for($i = 0; $i <= GetMaxYear() - 1990; $i++) {
    push(@years, 1990 + $i);
  }

  # Generate topic list
  $hstmt = $DBH->prepare("SELECT name FROM topics ORDER BY name");
  $hstmt->execute();
  while(@arr = $hstmt->fetchrow_array()) {
    push(@topics, $arr[0]);
  }

  # Display the form
  print "<hr>";
  print "<a name='form'></a>\n";
  print "<form action='$cgi'>\n";
  print "<table width='100%'>\n";

  print "<tr><td><b>Research Topic:</b></td>\n";
  print "<td colspan='5'><select name='topic'>\n";
  print "<option value=''>- select all -</option>\n";
  for ($i = 0; $i < @topics; ++$i) {
    print "<option value='$topics[$i]'";
    if ($topics[$i] eq $topic) {
      print " selected";
      }
    print ">$topics[$i]</option>\n";
  }
  print "</select></td></tr>\n";

  print "<tr><td><b>Author Name:</b></td>\n";
  print "<td><select name='lastname'>\n";
  print "<option value=''>- select all -</option>\n";
  for ($i = 0; $i < @names; ++$i) {
    print "<option value='$names[$i]'";
    if ($names[$i] eq $lastname) {
      print " selected";
    }
    print ">$names[$i]</option>\n";
  }
  print "</select></td>\n";

  print "<td align='right'><b>Year:</b></td>\n";
  print "<td><select name='year'>\n";
  print "<option value=''>- select all -</option>\n";
  for ($i = 0; $i < @years; ++$i) {
    print "<option value='$years[$i]'";
    if ($years[$i] == $year) {
      print " selected";
    }
    print ">$years[$i]</option>\n";
  }
  print "</select></td>\n";

  print "<td align='right'><b>Show Abstracts:</b></td>\n";
  print "<td><input type='checkbox' name='abstract'";
  if ($abstract) {
    print " checked";
  }
  print "></td><tr>\n";
  print "<tr><td colspan='6' align='center'>";
  print "<input type='submit' name='Select' value='Submit'>";
  print "</td></tr>\n";
  print "</table>\n";
  print "</form>\n";
  print "<hr>\n";

  $hstmt->finish;
}

# ===================================================================
# Name: FetchTopicName
# Desc: Given a database handle and a topic id return a topic name
# Args: TopicID
# ===================================================================
sub FetchTopicName {
  my ($id) = @_;
  my ($hstmt, $sql_stmt, @arr);

  $sql_stmt = "SELECT name FROM topics WHERE id = " . $id;
  $hstmt = $DBH->prepare($sql_stmt); 
  $hstmt->execute();
  @arr = $hstmt->fetchrow_array();
  $hstmt->finish;
  return $arr[0];
}

# ===================================================================
# Name: FetchFullName
# ===================================================================
sub FetchFullName {
  my ($lastname) = @_;
  my ($hstmt, $sql_stmt, @arr);

  $sql_stmt = "SELECT fullname FROM authors WHERE lastname = '" . $lastname . "'";
  $hstmt = $DBH->prepare($sql_stmt);
  $hstmt->execute();
  @arr = $hstmt->fetchrow_array();
  return $arr[0];
}

# ===================================================================
# Name: GetMaxYear
# Desc:
# ===================================================================
sub GetMaxYear {
  my ($hstmt, $sql_stmt, @arr);

  $sql_stmt = "SELECT MAX(year) FROM papers";
  $hstmt = $DBH->prepare($sql_stmt);   
  $hstmt->execute();
  @arr = $hstmt->fetchrow_array();  
  $hstmt->finish;
  return $arr[0];
}

# ===================================================================
# Name: ProcessQuery
# Args: lastname of the author
#       Topic
#       year
#       Abstract ON/OFF
#       TopicID  used only in canned queries
# ===================================================================
sub ProcessQuery {
  my ($lastname, $topic, $year, $abstract, $topicID) = @_;
  my ($hstmt, $sql_stmt, $counter, $i, @arr);  

  $sql_stmt = "SELECT papers.id, papers.ref_key, papers.type, papers.title, " .
    "papers.booktitle, papers.volume, papers.number, papers.publisher, " .
      "papers.organization, papers.institution, papers.journal, papers.year, " .
	"papers.month, papers.pages, papers.file_name, papers.note";

  # See if abstract is selected or not
  if ($abstract) {
    $sql_stmt .= ", papers.abstract";
  }

  $sql_stmt .= " FROM papers";

  if ($lastname ne "all") {
    $sql_stmt .= ", papers_to_authors, authors";
  }

  if ($topic ne "all" || $topicID) {
    $sql_stmt .= ", topics_to_papers, topics";
  }

  $sql_stmt .= " WHERE ";

  if ($lastname ne "all") {
    $sql_stmt .= " papers.id = papers_to_authors.paper_id AND " .
      "papers_to_authors.author_id = authors.id AND " . 
	"authors.lastname = '" . $lastname ."'";
  }

  if ($topic ne "all") {
    if ($lastname ne "all") {
      $sql_stmt .= " AND";
    }
    $sql_stmt .= " papers.id = topics_to_papers.paper_id AND " .
      "topics_to_papers.topic_id = topics.id AND topics.name = '" . 
	$topic . "'";
  } elsif ($topicID) {
    $sql_stmt .= " papers.id = topics_to_papers.paper_id AND " .
      "topics_to_papers.topic_id = topics.id AND " .
	"topics.id = ". $topicID;
  }

  if ($year ne "all") {
    if ($topic ne "all" or $lastname ne "all") {
      $sql_stmt .= " AND year = " . $year;
    } else {
      $sql_stmt .= " year = " . $year;
    }
  } else {
    if ($lastname ne "all" || $topic ne "all" || $topicID) {
      $sql_stmt .= " AND year >= 1900";
    } else {
      $sql_stmt .= " year >= 1900";
    }
  }
  $sql_stmt .= " ORDER BY YEAR DESC, MONTH DESC";

  # Time to select the rows
  $hstmt = $DBH->prepare($sql_stmt) || die "Can't prepare statement\n";
  $hstmt->execute() || die "Can't execute\n";

  $counter = 0;
  print "<table width='100%'>";

  while(@arr = $hstmt->fetchrow_array()) {
    if ($counter % 2 == 0) {
      print "<tr bgcolor=#F0FFFF><td>";
    } else {
      print "<tr><td>";
    }
    $counter++;
    PrintEntry(@arr);
    print "</td></tr>";
  }

  print "</table>";
  $hstmt->finish;	
}

# ===================================================================
# Name: PrintEntry
# ===================================================================
sub PrintEntry {
  my (@arr) = @_;

  print "<table width='100%' CELLPADDING=1><tr><td>";
  PrintAuthorNames($arr[$ID]);
  if ($arr[$TYPE] eq 'A') {
    PrintArticle(@arr); 
  } elsif ($arr[$TYPE] eq 'T') {
    PrintTechReport(@arr);
  } elsif ($arr[$TYPE] eq 'C') {
    PrintCollection(@arr);
  } elsif ($arr[$TYPE] eq 'P') {
    PrintProceedings(@arr);
  }
  print "</td></tr>";
  if ($arr[$ABSTRACT] ne "") {
    print "<tr><td><b>Abstract</b></td></tr>";
    print "<tr><td><text>";
    print $arr[$ABSTRACT];
    print "</td></tr></text>";
  }
  print "</table>";
}

# ===================================================================
# Name: PrintAuthorNames
# Desc:
# Args: paper id
# ===================================================================
sub PrintAuthorNames {
  my ($id) = @_;
  my ($hstmt, $first, $counter, $name);
  my (@names, @arr);

  # get the names of all the authors and print them out
  $hstmt = $DBH->prepare( "SELECT authors.fullname FROM " .
			   "authors, papers_to_authors " .
			   "WHERE authors.id = papers_to_authors.author_id AND " .
			   "papers_to_authors.paper_id = ?"); 	

  $hstmt->execute($id);
  $first = 1;
  $counter = 0;
  while(@arr = $hstmt->fetchrow_array()) {
    push(@names, $arr[0]);
    $counter++;
  }	

  foreach $name (@names) {
    if ($first) {
      print "$name";
      $first = 0;			
    } elsif ($name eq $names[$counter - 1]) {
      print " and $name";
    } else {
      print ", $name";
    }
  }

  $hstmt->finish();
  print ".  ";
}

# ===================================================================
# Name: PrintTechReport
# Desc: Prints a Technical Report
# Args: fetched row
# ===================================================================
sub PrintTechReport {
  my (@arr) = @_;

  # Print the title
  print "$arr[$TITLE]. ";

  if ($arr[$NUMBER] ne "") {
    print "Technical Report $arr[$NUMBER]. ";
  }
  if ($arr[$INST] ne "") {
    print "$arr[$INST]. ";
  }

  # Print Month/Year
    PrintDate($arr[$MONTH], $arr[$YEAR]);
  if ($arr[$NOTE]) {
    print "<text>$arr[$NOTE].</text>";
  }

  # Print links for the papers
  PrintLinks($arr[$FNAME]);
}

# ===================================================================
# Name: PrintCollection
# Desc: Prints a Collection
# Args: fetched row
# ===================================================================
sub PrintCollection {
  my (@arr) = @_;

  # Print the title
  print "$arr[$TITLE]. ";

  # Print where it appeared
  if ($arr[$BOOKTITLE] ne "") {
    print "In <i>$arr[$BOOKTITLE].</i> ";
  }

  # Print Month/Year
  PrintDate($arr[$MONTH], $arr[$YEAR]);

  if ($arr[$PAGES] ne "") {
    print "pages $arr[$PAGES]. ";
  }
  if ($arr[$ORG] ne "" and $arr[$PUB] eq "") {
    print "$arr[$ORG]. ";
  } elsif ($arr[$PUB] ne "") {
    if ($arr[$ORG] ne "") {
      print "$arr[$ORG], ";
    }
    print "$arr[$PUB]. ";
  }
  if ($arr[$NOTE]) {
    print "<text>$arr[$NOTE].</text>";
  }

  # Print links for the papers
  PrintLinks($arr[$FNAME]);
}

# ===================================================================
# Name: PrintArticle
# Desc: Prints an Article
# Args: fetched row
# ===================================================================
sub PrintArticle {
  my (@arr) = @_;

  # Print the title
  print "$arr[$TITLE]. ";

  # Print where it appeared
  if ($arr[$JOURNAL] ne "") {
    print "<i>$arr[$JOURNAL]</i>. ";
  }

  if ($arr[$VOLUME] ne "" and $arr[$NUMBER] eq "") {
    print "$arr[$VOLUME]. ";
  } elsif ($arr[$NUMBER] ne "") {
    if ($arr[$VOLUME] ne "") {
      print "$arr[$VOLUME](";
    }
    print "$arr[$NUMBER])";
  }
  if ($arr[$PAGES]) {
    print ":$arr[$PAGES]. ";
  } else {
    print ". ";
  }

  # Print Month/Year
  PrintDate($arr[$MONTH], $arr[$YEAR]);

  if ($arr[$NOTE]) {
    print "<text>$arr[$NOTE].</text>";
  }

  # Print links for the papers
  PrintLinks($arr[$FNAME]);
}

# ===================================================================
# Name: PrintProceedings
# Desc: Prints a Proceedings
# Args: fetched row
# ===================================================================
sub PrintProceedings {
  my (@arr) = @_;

  # Print the title  
  print "$arr[$TITLE]. ";

  # Print where it appeared
  if ($arr[$BOOKTITLE] ne "") {
    print "In <i>$arr[$BOOKTITLE].</i> ";
  }

  # Print Month/Year
  PrintDate($arr[$MONTH], $arr[$YEAR]);

  if ($arr[$PAGES] ne "") {
    print "pages $arr[$PAGES]. ";
  }

  if ($arr[$ORG] ne "" and $arr[$PUB] eq "") {
    print "$arr[$ORG]. ";
  } elsif ($arr[$PUB] ne "") {
    if ($arr[$ORG] ne "") {
      print "$arr[$ORG], ";
    }
    print "$arr[$PUB]. ";
  }
  if ($arr[$NOTE]) {
    print "<text>$arr[$NOTE].</text>";
  }

  # Print links for the papers
  PrintLinks($arr[$FNAME]);
}

# ===================================================================
# Name: PrintLinks
# ===================================================================
sub PrintLinks {
  my ($fname) = @_;

  print "<div><a href='$ftproot/$fname.ps.gz'>";
  print "<img border=0 src='$urlroot/img/ps.gif'></a>";
  print " <a href='$ftproot/papers-pdf/$fname.pdf'>";
  print "<img border=0 src='$urlroot/img/pdf.gif'></a></div>";
}

# ===================================================================
# Name: PrintDate
# ===================================================================
sub PrintDate {
  my ($month, $year) = @_;

  if ($month eq "") {
    print "$year. ";
  } else {
    print "$month $year. ";
  }
}