#! /usr/bin/perl

# interests - find USHL members with a given interest by searching the
# interests field of the member table

# PREAMBLE
use DBI;
use CGI;
use strict;

# default connection  parameters - all missing
my ($host_name, $user_name, $password) = (undef, undef, undef);
my ($db_name) = "samp_db";

# construct data source
my ($dsn) = "dbi:mysql:$db_name";
$dsn .= ":hostname=$host_name" if $host_name;
$dsn .= ";mysql_read_default_file=/usr/local/apache/conf/samp_db.cnf";

# connect to server
my (%attr) = ( RaiseError => 1 );
my ($dbh) = DBI->connect ($dsn, $user_name, $password, \%attr);
# PREAMBLE

my ($cgi) = new CGI;

my ($title) = "US Historical League Interest Search";
print $cgi->header ();
print $cgi->start_html (-title => $title);
print $cgi->h1 ($title);

# MAIN-BODY
# parameter to look for
my ($interest) = $cgi->param ("interest");

# Display a keyword entry form.  In addition, if $interest is defined,
# search for and display a list of members who have that interest.
# Note that the current $interest value is displayed as the default
# value of the interest field in the form.

print $cgi->start_form (-method => "POST");
print $cgi->textfield (-name => "interest",
						-value => $interest,
						-size => 40);
print $cgi->submit (-name => "button", -value => "Search");
print $cgi->end_form ();

# run a search if a keyword was specified
search_members ($interest) if $interest;
# MAIN-BODY

print $cgi->end_html ();

$dbh->disconnect ();
exit (0);

# SEARCH_MEMBERS
sub search_members
{
my ($interest) = shift;
my ($sth, $count);

	printf "Search results for keyword: %s<BR><BR>\n",
									$cgi->escapeHTML ($interest);
	$sth = $dbh->prepare (qq{
				SELECT * FROM member WHERE interests LIKE ?
				ORDER BY last_name, first_name
			});
	# look for string anywhere in interest field
	$sth->execute ("%" . $interest . "%");
	$count = 0;
	while (my $hash_ref = $sth->fetchrow_hashref ())
	{
		format_html_entry ($hash_ref);
		++$count;
	}
	print $cgi->p ("$count entries found");
}
# SEARCH_MEMBERS

sub format_name
{
my ($entry_ref) = shift;
my ($name);

	$name = $entry_ref->{first_name} . " " . $entry_ref->{last_name};
	if ($entry_ref->{suffix})			# there is a name suffix
	{
		# no comma for suffixes of I, II, III, etc.
		$name .= "," unless $entry_ref->{suffix} =~ /^[IVX]+$/;
		$name .= " " . $entry_ref->{suffix} if $entry_ref->{suffix};
	}
	return ($name);
}

sub format_html_entry
{
my ($entry_ref) = shift;
my ($address);

	# encode characters that are special in HTML
	foreach my $key (keys (%{$entry_ref}))
	{
		$entry_ref->{$key} = $cgi->escapeHTML ($entry_ref->{$key});
	}
	printf "<STRONG>Name: %s</STRONG><BR>\n", format_name ($entry_ref);
	$address = "";
	$address .= $entry_ref->{street} if $entry_ref->{street};
	$address .= ", " . $entry_ref->{city} if $entry_ref->{city};
	$address .= ", " . $entry_ref->{state} if $entry_ref->{state};
	$address .= " " . $entry_ref->{zip} if $entry_ref->{zip};
	print "Address: $address<BR>\n" if $address;
	print "Telephone: $entry_ref->{phone}<BR>\n" if $entry_ref->{phone};
	print "Email: $entry_ref->{email}<BR>\n" if $entry_ref->{email};
	print "Interests: $entry_ref->{interests}<BR>\n"
							if $entry_ref->{interests};
	print "<BR>\n";
}