#!/usr/local/bin/perl -w

#
# Creates an RSS 2.0 feed
# from the technicalreport DB.
# Generates URLs for Research.Sun.COM



use DBI;
use XML::RSS;

require "ctime.pl";
require "/usr/local/include/perl/date.pl";
require "/usr/local/include/perl/TimeInits.pl";

$docroot=$ENV{DOCUMENT_ROOT};
$currdate=trim( `/bin/date -u "+%a, %d %b %Y %T %Z"`);  # 0.91 format
#$currdate=trim(`/bin/date "+%Y-%m-%dT%T-08:00"`);        # 1.0 format


my($database) = "technicalreport";
my($username) = "charlesj";
my($pw) = "DuanLian";

my $dsn = "DBI:mysql:database=$database;host=localhost";
my $dbh = DBI->connect($dsn, $username, $pw) or die "Can't connect";


#
# RSS Preamble Info
#

my $rss = new XML::RSS (version => '2.0');

$rss->channel  (title	=> 'Sun Labs Technical Reports',
		link	=> 'http://research.sun.com/techrep',
		language => 'en',
		description => 'Tehnical reports from Sun Labs',
		pubDate => $currdate,
		lastBuildDate => $currdate,
		docs	=> 'http://backend.userland.com/rss092'
		);

$rss->image	(title	=> 'Sun Labs',
		url	=> 'http://research.sun.com/im/logo_sun.gif',
		link	=> 'http://research.sun.com/techrep',
		width	=> 110,
		height	=> 43,
		description => 'Technical reports from Sun Labs'
		);

$rss->textinput	(title	=> "Search",
		description => "Search the site",
		name	=> "query",
		link	=> "http://research.sun.com:8081/portal/dt/"
		);


#
# Grab data from MySQL and add RSS items
#


#$sth=$dbh->prepare("SELECT report.title,report.date,report.number,name.firstname, name.lastname  from report LEFT JOIN author ON report.report_id=author.report_id LEFT JOIN name ON name.name_id=author.name_id ORDER BY date DESC");

$sth=$dbh->prepare("SELECT distinct title, date, number, report.report_id, filename, subdir from report LEFT JOIN file on report.report_id = file.report_id  WHERE published='Y' AND filename regexp '.pdf' ORDER BY date DESC LIMIT 15");


$sth->execute();

 
while (my $ref = $sth->fetchrow_hashref()){



	$title= 	$ref->{'title'};
	$TRnumber=	$ref->{'number'};
	$date=   	$ref->{'date'};
	$pubDate=   	SqlToRss($ref->{'date'});
	$id=		$ref->{'report_id'};
	$directory=	$ref->{'subdir'};

	$abstract_file= $docroot . "/techrep/" . $directory . "/abstract-" . $id . ".html";
	$abstract_file =~s|//|/|;
	if (-e $abstract_file) {
 
		$title =~s/&/&/g;
		$year=substr($date,0,4);
#print "yr=$date\n";
		$utmp=lc($TRnumber);
		$url="http://research.sun.com/techrep/$year/smli_$utmp.pdf";
	
		$sth1=$dbh->prepare("SELECT author.*, name.firstname, name.lastname from author  LEFT JOIN name ON name.name_id=author.name_id where report_id=$id");
		$sth1->execute();
	
		$author="";
			while (my $ref1 = $sth1->fetchrow_hashref()){
			$author="$author, $ref1->{'firstname'} $ref1->{'lastname'}";
			}
		$author=~s/^, //;
	
		$rss->add_item(title => "$title",
			link	=> "$url",
			description => "By $author, Published $ref->{'date'}",
			dc => {
				date    => "$pubDate",
				creator => "$author",
			}
			);
	}
}
$sth->finish();




$dbh-> disconnect;

#$rss->save("externalTR.rdf");

print "Content-type: text/xml\n\n";
print $rss->as_string;
print "\n\n";

################################################################################# 
# Trim leading and trailing blanks from input string

sub trim {
   my @out = @_;
   for (@out) {
     s/\n\s+/ /g;
     s/\n/ /sg;
     s/^\s+//;
     s/\s+$//;
   }
   return wantarray ? @out : $out[0];
}
################################################################################# 
# Convert an SQL date to an RSS date
#
# Sample SQL date is "1992-09-01"
# Sample RSS 0.91 date is "Sat, 17 Jan 2004 03:00:06 GMT"
# Sample RSS 1.0  date is "2004-01-17T03:00:06-08:00"

sub SqlToRss {
   my @out = @_;
   $DTG=$out[0];
   $DTG =~s/\s+.*//;
   @date_array=split(/-/, $DTG);

$year = $date_array[0];
$month= $date_array[1];
$day  = $date_array[2];


  $TodayJDate = &jday($month,$day,$year);
  ($junk, $junk, $junk, $weekday) = &jdate($TodayJDate);
  $monthname=&monthabbrev($month);
  $dayname=&weekday($weekday);

  $rssdate="$dayname, $day $monthname $year 15:00:00 PST";
#  $rssdate="$year-$month-$day" . "T15:00:00-08:00";

   return $rssdate;
}