#!/usr/bin/env 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";
}

$order = param('order');

if ($order eq 'by_last_name') {
    make_page_by_last_name();
} elsif ($order eq 'by_first_name') {
    make_page_by_first_name();
} else {
    make_page_by_institution();
}
# 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";
print "<a href=\"contact_list.pl?order=by_institution\">Sort by Institution</a>";
print "&nbsp;&nbsp;&nbsp;";
print "<a href=\"contact_list.pl?order=by_last_name\">Sort by Last Name</a>";
print "&nbsp;&nbsp;&nbsp;";
print "<a href=\"contact_list.pl?order=by_first_name\">Sort by First Name</a>";
print "<br/>\n";
print "<br/>\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;
}

sub GetInputParameters {
    @names = $q->param;
    foreach $in (0 .. $#names) {
	eval "\$$names[$in] = \$q->param('$names[$in]')";
# 	print STDERR "param $in: $names[$in] = ${$names[$in]}\n"; 
    }
}

sub make_page_by_institution {
# 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};
	output_header();
	output_contacts();
	output_footer();
    }
    output_global_footer();
}

sub make_page_by_last_name {
    output_global_header();
    $sql = "SELECT nameLast, nameFirst, email, phoneWork, institution from person order by nameLast, nameFirst";
    $sth = submit_query($sql);
    print "<table border>\n";
    print "<tr><th>Last Name<th>First Name<th>Email<th>Phone<th>Institution\n";
    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><td>$$hash_ref{institution}</td></tr>\n";
    }
    print "</table>\n";
    output_global_footer();
}

sub make_page_by_first_name {
    output_global_header();
    $sql = "SELECT nameLast, nameFirst, email, phoneWork, institution from person order by nameFirst, nameLast";
    $sth = submit_query($sql);
    print "<table border>\n";
    print "<tr><th>First Name<th>Last Name<th>Email<th>Phone<th>Institution\n";
    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{nameFirst}</td><td>$$hash_ref{nameLast}</td>";
	print "<td>$email_this</td><td>$$hash_ref{phoneWork}</td><td>$$hash_ref{institution}</td></tr>\n";
    }
    print "</table>\n";
    output_global_footer();
}
