#!/usr/bin/perl
#!/apps/bin/perl

use DBI;
use CGI qw/:standard/;

# set up the database parameters

$database = 'gluexContactList';
$hostname = 'clasdb';
$user = 'gluex';
$password = '';

# connect to the database server

$dbh = DBI->connect("DBI:mysql:$database:$hostname", $user, $password);

if (defined $dbh) {
    #print "Connection successful\n";
} else {
    die "Could not connect to the database server, exiting.\n";
}

# get account information from the database

$sql_inst = "SELECT name, abbr, url, fax from institution order by sortName";
$sth_inst = submit_query($sql_inst);
output_global_header();
while ($hr_inst = $sth_inst->fetchrow_hashref) {
    $inst_name = $$hr_inst{name};
    $inst_abbr = $$hr_inst{abbr};
    if ($inst_abbr ne 'JLAB') {
	output_header();
	output_contacts();
	output_footer();
    } else {
	print "<h2><a href=\"$$hr_inst{url}\">$inst_name</a></h2>";
	print "<p>fax: $$hr_inst{fax}</p>";
	print "<p>see <a href=\"http://www.jlab.org/Hall-D/halldstaff.html\">Hall D Staff Directory</a></p>\n";
    }
}
output_global_footer();

# disconnect from the server

$rc = $dbh->disconnect;

exit;

sub output_contacts {
    my $id;
    @id = ();
    $sql = "SELECT nameLast, nameFirst, email, phoneWork from person where institution = \'$inst_abbr\' order by nameLast, nameFirst";
    $sth = submit_query($sql);
    while ($hash_ref  = $sth->fetchrow_hashref) {
	$email_this = "<span class=\"address\">" . $$hash_ref{email};
	$email_this =~ s/@/<span>_<\/span>@<span>_<\/span>/;
	$email_this =~ s/\./<span>_<\/span>\.<span>_<\/span>/g;
	print "<tr><td>$$hash_ref{nameLast}</td><td>$$hash_ref{nameFirst}</td>";
	print "<td>$email_this</td><td>$$hash_ref{phoneWork}</td></tr>\n";
    }
}

sub output_header {
    if ($$hr_inst{url} eq '') {
	print "<h2>$inst_name</h2>\n";
    } else {
	print "<h2><a href=\"$$hr_inst{url}\">$inst_name</a></h2>";
    }
    if ($$hr_inst{fax} ne '') {
	print "<p>fax: $$hr_inst{fax}</p>";
    }
    print "<table border>\n";
    print "<tr><th>Last Name</th><th>First Name</th><th>Email</th><th>Phone</th></tr>\n";
}

sub output_global_header {
print header,
    start_html(-title=>'GlueX Contact List',
	       -style=>{-verbatim=>'span.address span {display: none;}'} 
	);
print "<h1>GlueX Contact List</h1>\n";
}

sub output_footer {
    print "</table>\n";
}

sub output_global_footer {
    print "</body></html>\n";
}

sub submit_query {
    my ($sql) = @_;
    my $sth, $rv;
    $sth = $dbh->prepare($sql);
    $rv = $sth->execute;
    return $sth;
}
