#!/usr/local/bin/perl -w
# Fetch XML for Whisker publications from database.
# Rudolf Cardinal, July 2008.

use strict;

use LWP::Simple;
use XML::Twig;
use DBD::ODBC; # Win32::ODBC doesn't allow parameter binding
use Acme::Comment type => 'C++'; # http://www.perl.com/lpt/a/663

my $filename = shift;
if ($filename eq "") {
	die "Syntax: fetch_whisker_publication_xml <filename>";
}

# Connect to database
my $db = "Whisker_Publications";
my $driver = "ODBC";

print "Connecting to database...\n";
my $dbh = DBI->connect('DBI:'.$driver.':'.$db) or die "Failed to connect to database: $db\n";
my $dbtype = $dbh->get_info(17); # DBI::SQL_DBMS_NAME
print "Connected to database: $driver:$db. Database type is: $dbtype\n";

my ($sec,$min,$hour,$mday,$mon,$nowyear,$wday,$yday,$isdst) = localtime(time);
my $current_year = $nowyear+1900;
my $nowtime = "$hour:$min on $mday/" . ($mon+1) . "/$current_year";

open(OUTFILE, ">$filename");

# ---------- Header. Note the use of an arbitrary string (TAIL) to delimt multi-line text.
print OUTFILE <<"TAIL"	
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
  <head>
    <!--#include file="ssi/headerheader.shtml" -->
    <title>
      Whisker - Publications
    </title>
  </head>
  <body class="publications">
    <!--#include file="ssi/header.shtml" -->
    
      <div id="pageurl">www.whiskercontrol.com/publications.shtml</div>
      <h1>Publications involving Whisker and related software</h1>
      <div class="publicationtext">
        <p>Shown below is an incomplete list of publications involving Whisker, or involving software written by Rudolf Cardinal (<a href="mailto:rudolf\@pobox.com">rudolf\@pobox.com</a>) and/or Mike Aitken (<a href="mailto:m.aitken\@psychol.cam.ac.uk">m.aitken\@psychol.cam.ac.uk</a>), the authors of Whisker. Software is listed in square brackets. (PMID: PubMed ID. PubMed IDs are valid search terms on <a href="http://www.pubmed.com/">http://www.pubmed.com<a>. DOI: digital object identifier. If the DOI is xxx, the URL is <a href="http://dx.doi.org/">http://dx.doi.org/</a>xxx.)</p>
        <p>If you discover any errors on this page, or know of any studies involving Whisker or related software that are not cited here, we would be most grateful if you would let us know by e-mailing Rudolf (<a href="mailto:rudolf\@pobox.com">rudolf\@pobox.com</a>).</p>
        <p>Publication list created at $nowtime.</p>
        <ul>
          <li><b>Cardinal RN</b> (2000). Whisker, version 1.0, computer software. URL: http://www.whiskercontrol.com. [Whisker, first version.]</li>
          <li><b>Cardinal RN, Aitken MRF</b> (2001). Whisker, version 2.2, computer software. URL: http://www.whiskercontrol.com. [Whisker, first multimedia version.]</li>
        </ul>
TAIL
;

# ---------- Articles.

$dbh->{LongReadLen} = 1000; # defaults to 80; this truncates things and gives an error
$dbh->{LongTruncOk} = 1; # OK to truncate data longer than this
my $query_sql = $dbh->prepare("SELECT Authors, PublicationYear, Title, J.FullJournal, Volume, Pages, P.PMID, DOI, WhiskerComponents " .
                              " FROM PubMedArticles P, WhiskerInformation W, JournalNames J" .
                              " WHERE P.PMID = W.PMID" .
                              " AND P.Journal = J.PubMedJournalName" .
                              " AND InvolvesWhisker" .
                              " ORDER BY LEFT(PublicationYear, 4), AuthorsBrief;");
$query_sql->execute() or die "Couldn't query database: " . $query_sql->errstr;
my $currentyear = 0;
my $itemnumber = 0;
my ($authors, $year, $title, $journal, $volume, $pages, $pmid, $doi, $whiskercomponents);
$query_sql->bind_columns(\($authors, $year, $title, $journal, $volume, $pages, $pmid, $doi, $whiskercomponents));
while ($query_sql->fetch) {
	# info on fetching long (memo) fields:
	# http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html
	# http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html
	# http://www.unix.com.ua/orelly/linux/dbi/ch06_02.htm
	++$itemnumber;
	$pmid = defined_or_blank($pmid);
	print "Processing PMID $pmid...\n";
	$authors = defined_or_blank($authors);
	$year = substr(defined_or_blank($year), 0, 4); # leftmost 4 characters only (some are e.g. "2004 Jun-Jul")
	$title = defined_or_blank($title);
	$journal = defined_or_blank($journal);
	$volume = defined_or_blank($volume);
	$pages = defined_or_blank($pages);
	$pages =~ s/-/&ndash;/;
	$doi = defined_or_blank($doi);
	my $doiurl = ($doi eq "") ? "DOI: unknown" : "DOI: <a href=\"http://dx.doi.org/$doi\">$doi</a>";
	$whiskercomponents = defined_or_blank($whiskercomponents);
	if ($year ne $currentyear && $currentyear != 0) {
	  print OUTFILE "        </ol>\n";
	}
	if ($year ne $currentyear) {
	  print OUTFILE "        <p>$year</p>\n" .
	                "        <ol>\n"; # <ol continue> is not yet widely supported in browsers, so we'll use <li value=...> below
	  $currentyear = $year;
	}
	print OUTFILE "          <li value=\"$itemnumber\"><b>$authors</b> ($year). " .
	              "$title <i>$journal</i> $volume: $pages. " .
	              "PMID: <a href=\"http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?cmd=Retrieve&amp;db=pubmed&amp;dopt=Abstract&amp;list_uids=$pmid\">$pmid</a>. " .
	              "$doiurl. " .
	              "[$whiskercomponents]</li>\n";
}
print OUTFILE "        </ol>\n";
print OUTFILE "      </div>\n";

# ---------- Journals.

print OUTFILE "      <a name=\"JournalSummary\"></a><h2>Summary of publications listed above by journal</h2>\n";
print OUTFILE "      <div class=\"publicationtext\">\n";
print OUTFILE "        <table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">\n";
print OUTFILE "           <tbody>\n";
$query_sql = $dbh->prepare("SELECT J.FullJournal, COUNT(*) AS NumPublications" .
								" FROM PubMedArticles AS P, JournalNames AS J, WhiskerInformation AS W" .
								" WHERE P.PMID = W.PMID" .
								" AND W.InvolvesWhisker" .
								" AND P.Journal = J.PubMedJournalName" .
								" GROUP BY J.FullJournal".
								" ORDER BY J.FullJournal;");
$query_sql->execute() or die "Couldn't query database: " . $query_sql->errstr;
my ($journaltitle, $publicationcount);
$query_sql->bind_columns(\($journaltitle, $publicationcount));
while ($query_sql->fetch) {
	print OUTFILE "             <tr>\n";
	print OUTFILE "               <td><div class=\"rightalign\">$publicationcount</div></td>\n";
	print OUTFILE "               <td><i>$journaltitle</i></td>\n";
	print OUTFILE "             </tr>\n";
}
print OUTFILE "           </tbody>\n";
print OUTFILE "         </table>\n";


# ---------- Footer.

print OUTFILE <<"TAIL"
      </div>

    <!--#include file="ssi/footer.shtml" -->
  </body>
</html>
TAIL
;

print "FINISHED.\n";
exit;

sub defined_or_blank {
	my $item = shift;
	if (defined $item) {
		return $item;
	}
	return "";
}