#perl

# TPpeoplecreate.pl
###############################################################
# Overall functionality:
#      read TPstaff.xls and TPstuds.xls 
#      read staff and student details then create TP people web page
# Usage:
#      Just doubleclick on this file if already associated with ActivePerl
#      Ensure the two input spreadsheets exist and are up to date
#   
# Structure:
#      Read in TPstaff.xls and create hash of staff 
#      Read in TPstuds.xls and create hash of staff 
#      Create create staff and student tables from these hashes
#      Create TPpeople.html
###############################################################
use strict;
use warnings;

use File::Copy;
use File::Basename;

use Cwd;

use Win32::OLE;
use Win32::OLE::Const "Microsoft Excel";

my $version = "Version 1.1 July 23 2012";
# Alan Irving

print "$version\n\n";

my $print_level=4;

sub Room;
sub Phone;
sub Comment;
sub Headrecord;
sub discard;
sub set;


my $Left=&Comment("L","L");
my $blank="&nbsp;";
my @classes=("acad","rf","hon","rec");
my %class=(
  $blank => "acad",
  "EP"   => "hon",
  "FTRP" => "rf",
  "HRA"  => "rf",
  "HRF"  => "rf",
  "HSF"  => "hon",
  "HF"   => "hon",
  "HVF"  => "hon",
  "L"    => "rec",
  "LRF"  => "rf",
  "MCRF" => "rf",
  "PDF"  => "rf",
  "RA"   => "rf",
  "RAc"  => "rf",
  "RF"   => "rf",
  "SF"   => "hon",
  "SRA"  => "rf",
  "SRF"  => "rf",
  "TA"   => "rf",
  "TL"   => "acad",
  "V"    => "rf",
  "VF"    => "rf"
);

my %classtitle=(
  "acad" => "Academic Staff",
  "rf"   => "Research Staff",
  "hon"  => "Honorary Staff",
  "rec"  => "Recent Members"
);

# create page header
my $header='<?xml version="1.0" encoding="UTF-8"?>
<!-- DO NOT TRY TO EDIT this directly: it is generated by TPpeoplecreate.pl from TPstaff.xls and TPstuds.xls, so edit these instead -->
<!DOCTYPE HTML
    PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "DTD/xhtml1-transitional.dtd">
<html><head>
<title>Theoretical Physics People</title>
<style>
p, body, td, div, ul, li {
    font-family: helvetica,arial,sans-serif;
    color: #000000;
}

h1 {
    font-size: +3;
}

h2.newsheader {
    text-align: center;
}

div {
    background-color: #f6f6f6;
    border: thin solid black;
    padding: 15px;
}

a.endlink {
    text-align: right;
}

</style>
</head>

<body bgcolor="#ffffff" text="#000000">
<table width="100%" border="0" cellpadding="0" cellspacing="20">
<tr><td align="center" valign="center">
<a href="http://www.liv.ac.uk/"><img src="http://www.liv.ac.uk/images/h1_a.png" alt="The University of Liverpool" width="185" height="46" border="0" /></a>
</td><td align="center" valign="center">
<center>


<font size="+3"><a href="http://www.liv.ac.uk/mathematical-sciences/research/theoretical-physics/">Theoretical Physics</a> People</font><br />
</td></tr></table>

<table width="100%" border="0" cellpadding="0" cellspacing="20">
<tr valign="top">
'; # end $header
#
# navigation panel on  LHS
#
my $leftpanel='
<td width="30%">
<a href="http://www.liv.ac.uk/maths">Department of Mathematical Sciences</a></font>
<hr />
<b>See below on this page:</b><br />
&raquo; <a href="#acad">Academic staff</a><br />
&raquo; <a href="#rf">Research staff</a><br />
&raquo; <a href="#hon">Honorary staff</a><br />
&raquo; <a href="#rec">Recent group members</a><br />
&raquo; <a href="#gstuds">Postgraduate students</a><br />
<hr />
</td>';

#
# Head(s) block
#
my $rightpanel.='
<td width="50%">
';
$rightpanel.="<table width='100%' border='0'><tr><td align='center'>\n";
my $TPhome="http://www.liv.ac.uk/mathematical-sciences/research/theoretical-physics/";
my $jpegsize='200 200';
my ($rows,$cols)=split /\s+/,$jpegsize;
$rightpanel.="<a href='".$TPhome."'><img src='TPlogo.jpg' width=$rows height=$cols border=0 /></a><br />\n";
#$rightpanel.="<a href='".$TPhome."'><img src='TPlogo.jpg' alt='".Theoretical Physics." home page' width=$rows height=$cols border=0 /></a><br />\n";
#  $rightpanel.="</td></tr><tr><td align='right'><div>\n";
#  $rightpanel.="<b> For other use? </b>\n"; 
#  $rightpanel.="</div>\n";
  $rightpanel.="</td></tr></table><br /><br />\n";
$rightpanel.='
</td> 
</tr></table>
';



my $footer='</body></html>';  

# open TPpeople.html for writing and write the header code
open  COMMS, ">", "TPpeople.html" or error("failed to open TPpeople.html"); 
print COMMS "$header\n";
print COMMS "$leftpanel\n";
print COMMS "$rightpanel\n";


################ Excel startup #################################
#Use existing instance of Excel if running, otherwise start a new one
my $excel;
print "starting Excel..\n";
eval {$excel = Win32::OLE->GetActiveObject('Excel.Application')};
error("Excel doesn't appear to be installed on this machine.") if $@;
   unless (defined $excel) {
      $excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or error("Can't start Excel");
    }
#

###############################################################
# staff spreadsheet
my $excel_sfname = "TPstaff.xls";
my $excel_lfname = cwd() . "\/$excel_sfname";

if ($print_level > 1) {print "$excel_lfname\n";}

if (-e $excel_lfname)
    {
	print "Processing $excel_sfname (staff spreadsheet)\n";
	my $workbook = $excel->Workbooks->Open($excel_lfname);
	$workbook->{Visible} = 0;
	if ($print_level > 1) {$workbook->{Visible} = 1;}
	my $sheet = $workbook->Worksheets(1);
	my $last_row = $sheet->UsedRange->Find({What => "*", SearchDirection => &xlPrevious, SearchOrder => &xlByRows})->{Row};
	my $last_col = $sheet->UsedRange->Find({What => "*", SearchDirection => &xlPrevious, SearchOrder => &xlByColumns})->{Column};


## create hash of staff
my %staff = ();
my $nstaff = 0;
my $col =1;
for my $row (1..$last_row) {
    my $_ ||= $sheet->Cells($row, $col)->{Value};
    if ($_) {
      my $testchar=substr($_,0,1);
      if ($testchar ne "#") {
        ++$nstaff;
        if ($print_level > 2) {print "$nstaff: $_\n";}
        $staff{$nstaff}->{SNAME} =  $sheet->Cells($row,1)->{Value};
        $staff{$nstaff}->{TITLE} =  $sheet->Cells($row,2)->{Value};        
        $staff{$nstaff}->{FNAME} =  $sheet->Cells($row,3)->{Value};
        $staff{$nstaff}->{ROOM}  =  $sheet->Cells($row,4)->{Value};
        $staff{$nstaff}->{PHONE} =  $sheet->Cells($row,5)->{Value};
        $staff{$nstaff}->{EMAIL} =  $sheet->Cells($row,6)->{Value};
        $staff{$nstaff}->{WPAGE} =  $sheet->Cells($row,7)->{Value};
        $staff{$nstaff}->{STAT}  =  $sheet->Cells($row,8)->{Value};
        $staff{$nstaff}->{COMM}  =  $sheet->Cells($row,9)->{Value};
      }
    } #end empty string  
} # end $row

if ($print_level > 1) {print "There were $nstaff members of staff\n";}

#create staff html table
#
# Staff tables
#

my $stafftables="";

foreach my $c (@classes) {
  my $table="";
  foreach my $name (sort { $staff{$a}->{SNAME} cmp $staff{$b}->{SNAME} } keys %staff) {
#    my ($sur,$tit,$fst,$rm,$phn,$eml,$www,$class,$noteref)=@{$staff{$name}};
    my $aclass =  $staff{$name}->{STAT} ?  $class{$staff{$name}->{STAT}} : "acad";
    my $tit =  $staff{$name}->{TITLE} ?  $staff{$name}->{TITLE} : "Dr";
    my $sur = $staff{$name}->{SNAME};
    my $fst = $staff{$name}->{FNAME};
    my $rm  =  $staff{$name}->{ROOM} ?   $staff{$name}->{ROOM} : $blank ;  
    my $phn =  $staff{$name}->{PHONE} ?   $staff{$name}->{PHONE} : $blank ;
    $phn=&Phone($phn);  
    my $eml =  $staff{$name}->{EMAIL} ?   $staff{$name}->{EMAIL} : $blank ;
    if ($eml ne $blank) { # insert hyperlink
      $eml=~m|\@| or $eml=~s|$|\@liv.ac.uk|;
      $eml=~s/\@amtp$/\@amtp.liv.ac.uk/;
      $eml="<a href='mailto:$eml'> $eml</a>";
    }
    my $hypername=$tit.$blank.$fst.$blank.$sur;
    my $www =  $staff{$name}->{WPAGE} ?   $staff{$name}->{WPAGE} : $blank ;
    if ($www ne $blank) {
    $hypername="<a href='$www'>$hypername</a>";
    }
    $aclass eq $c or next;
    $table.="<tr>
        <td> $hypername </td>
  	<td> $rm </td>
  	<td> $phn </td>
  	<td> $eml </td></tr>";
  }; #end foreach $name

  $table ne "" 
    and my $stindex.='&raquo; <a href="#'.$c.'">'.$classtitle{$c}."</a><br />\n"
    and $stafftables.='
  <br />
  <table border="1" width="100%" bgcolor="#f6f6f6">
  <a name="'.$c.'"><caption><h2 class="newsheader"> '.$classtitle{$c}.'</h2></caption></a>
  <tr><td><b>Name</b></td>
  	<td><b>'."Room".'</b></td>
  	<td><b>'.&Comment("Phone","phones").'</b></td>
  	<td><b>Email</b></td>
   </tr>
  '.$table.'</table><br />';
} # end foreach $c

print COMMS "$stafftables\n";

$workbook->Close();
} # end -e staff.xls


###############################################################
# student spreadsheet
$excel_sfname = "TPstuds.xls";
$excel_lfname = cwd() . "\/$excel_sfname";

if ($print_level > 1) {print "$excel_lfname\n";}

if (-e $excel_lfname)
    {
	print "Processing $excel_sfname (staff spreadsheet)\n";
	my $workbook = $excel->Workbooks->Open($excel_lfname);
	$workbook->{Visible} = 0;
	if ($print_level > 1) {$workbook->{Visible} = 1;}
	my $sheet = $workbook->Worksheets(1);
	my $last_row = $sheet->UsedRange->Find({What => "*", SearchDirection => &xlPrevious, SearchOrder => &xlByRows})->{Row};
	my $last_col = $sheet->UsedRange->Find({What => "*", SearchDirection => &xlPrevious, SearchOrder => &xlByColumns})->{Column};


## create hash of students
my %studs = ();
my $nstuds = 0;
my $col =1;
for my $row (1..$last_row) {
    my $_ ||= $sheet->Cells($row, $col)->{Value};
    if ($_) {
      my $testchar=substr($_,0,1);
      if ($testchar ne "#") {
        ++$nstuds;
        if ($print_level > 2) {print "$nstuds: $_\n";}
        $studs{$nstuds}->{SNAME} =  $sheet->Cells($row,1)->{Value};
        $studs{$nstuds}->{TITLE} =  $sheet->Cells($row,2)->{Value};        
        $studs{$nstuds}->{FNAME} =  $sheet->Cells($row,3)->{Value};
        $studs{$nstuds}->{ROOM}  =  $sheet->Cells($row,4)->{Value};
        $studs{$nstuds}->{PHONE} =  $sheet->Cells($row,5)->{Value};
        $studs{$nstuds}->{EMAIL} =  $sheet->Cells($row,6)->{Value};
        $studs{$nstuds}->{WPAGE} =  $sheet->Cells($row,7)->{Value};
        $studs{$nstuds}->{SUPERV}  =  $sheet->Cells($row,8)->{Value};
        $studs{$nstuds}->{DEGREE}  =  $sheet->Cells($row,9)->{Value};
      }
    } #end empty string  
} # end $row

if ($print_level > 1) {print "There were $nstuds graduate students\n";}

#create student html table
my $studtable='<br />
  <table border="1" width="100%" bgcolor="#f6f6f6">
  <a name="gstuds"><caption><h2 class="newsheader">Postgraduate Students</h2></caption></a>
  <tr><td><b>Name</b></td>
  	<td><b>'."Room".'</b></td>
  	<td><b>'.&Comment("Phone","phones").'</b></td>
  	<td><b>Email</b></td>
  	<td><b>Supervisor</b></td>
  	<td><b>Degree/Year</b></td>
   </tr>
';
foreach my $name (sort { $studs{$a}->{SNAME} cmp $studs{$b}->{SNAME} } keys %studs) {
    my $tit =  $studs{$name}->{TITLE} ?  $studs{$name}->{TITLE} : "Dr";
    my $sur = $studs{$name}->{SNAME};
    my $fst = $studs{$name}->{FNAME};
    my $rm  =  $studs{$name}->{ROOM} ?   $studs{$name}->{ROOM} : $blank ;  
    my $phn =  $studs{$name}->{PHONE} ?   $studs{$name}->{PHONE} : $blank ;
    $phn=&Phone($phn);  
    my $eml =  $studs{$name}->{EMAIL} ?   $studs{$name}->{EMAIL} : $blank ;
    if ($eml ne $blank) { # insert hyperlink
      $eml=~m|\@| or $eml=~s|$|\@liv.ac.uk|;
      $eml=~s/\@amtp$/\@amtp.liv.ac.uk/;
      $eml="<a href='mailto:$eml'> $eml</a>";
    }
    my $hypername=$tit.$blank.$fst.$blank.$sur;
    my $www =  $studs{$name}->{WPAGE} ?   $studs{$name}->{WPAGE} : $blank ;
    if ($www ne $blank) {
    $hypername="<a href='$www'>$hypername</a>";
    }
    my $sup =  $studs{$name}->{SUPERV} ?   $studs{$name}->{SUPERV} : $blank ;
    my $deg =  $studs{$name}->{DEGREE} ?   $studs{$name}->{DEGREE} : $blank ;
    $studtable.="<tr>
        <td> $hypername </td>
  	<td> $rm </td>
  	<td> $phn </td>
  	<td> $eml </td>
  	<td> $sup </td>
  	<td> $deg </td></tr>";
} #end foreach $name
$studtable.='</table><br />';

print COMMS "$studtable\n";
print COMMS "$footer\n";
close COMMS;   # close output html file.

$workbook->Close();
} # end -e studs.xls


&press_key_exit;

#####################################################################################
#    SUBROUTINES
#####################################################################################

sub Comment {my ($field,$file)=@_;
  for ($file) {s/^\s*//;s/\s*$/.html/;}
  print 
  return "<a href=\"http://www.maths.liv.ac.uk/People/$file\" target=\"popup\" onclick=\"window.open('$file','popup','width=500,height=150'); return false\">$field</a>";
}

sub Phone { (my $number)=@_;
   if ($number ne "&nbsp"){
       return $number=~/^x(.*)$/ ? &Comment("x","phones").$1 : $number;
     } else { return $number;}
}

sub Room { (my $room)=@_;
#   return $room;
  for ($room) {
    /^[2-5]/  and return $room;
    /^[G1]/  and return &Comment("TP","tpw").$room;
    /^$blank$/ || m|^n/a$| and return $room;
    warn "probably wrong room $room\n" and return $room;
  }
}
sub Headrecord {(my $propertitle)=@_;
  foreach my $name (keys my %staff) {
    my ($sur,$tit,$fst,$div,$rm,$phn,$eml,$www,$class,$noteref)=@{$staff{$name}};
    my $init=substr $fst,0,1;
    $propertitle ne "$tit $sur" and $propertitle ne "$tit $init.$sur" and next;
    discard($noteref);
    $fst=~s/\(.*\)//;
    $init=join " ", map{(substr $_,0,1)."."} split /[\.\s]+/,$fst;
    if (!defined($www) or $www eq '') {
    return "$tit$blank$init$blank<b>$sur</b>\nemail$blank$eml phone$blank".&Comment($phn,"phones")." office$blank".&Room($rm);
    } else {
    return "<a href='$www'>$tit$blank$init$blank<b>$sur</b></a>\nemail$blank$eml phone$blank".&Comment($phn,"phones")." office$blank".&Room($rm);
    }
  }
  return $propertitle;
};

sub discard { return 1;}

sub set {my %ret; foreach (@_) { $ret{$_}=1;} return %ret;}



########################################
#A more user-friendly version of die...
sub error {
    print "*** ERROR: @_\n";
    &press_key_exit;
}

# pause before exit in DOS window so one can read the STD output
sub press_key_exit {
    print "\nPress RETURN to exit\n";
    <STDIN>;
    exit;
}
# pause in DOS window so one can read the STD output
sub press_key_pause {
    print "\nPress RETURN to continue\n";
    <STDIN>;
}
