#! /usr/bin/perl

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

use DBI;
use strict;

# parse connection parameters from command line if given

use Getopt::Long;
$Getopt::Long::ignorecase = 0; # options are case sensitive

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

# GetOptions doesn't seem to allow -uuser_name form, only -u user_name?
GetOptions(
	# =s means a string argument is required after the option
	"host|h=s"      => \$host_name
	,"user|u=s"      => \$user_name
	# :s means a string argument is optional after the option
	,"password|p:s"  => \$password
) or exit (1);

# solicit password if option specified without option value
if (defined ($password) && !$password)
{
	# turn off echoing but don't interfere with STDIN
	open (TTY, "/dev/tty") or die "Cannot open terminal\n";
	system ("stty -echo < /dev/tty");
	print STDERR "Enter password: ";
	chomp ($password = <TTY>);
	system ("stty echo < /dev/tty");
	close (TTY);
	print STDERR "\n";
}

# construct data source
my ($dsn) = "dbi:mysql:samp_db";
$dsn .= ":hostname=$host_name" if $host_name;
$dsn .= ";mysql_read_default_file=$ENV{HOME}/.my.cnf";

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

# MAIN-BODY
@ARGV or die "Usage: interests keyword\n";
search_members (shift (@ARGV)) while @ARGV;
# MAIN-BODY

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

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

	print "Search results for keyword: $interest\n\n";
	$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_entry ($hash_ref);
		++$count;
	}
	print "$count entries found\n\n";
}
# SEARCH_MEMBERS

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

	printf "Name: %s\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\n" if $address;
	print "Telephone: $entry_ref->{phone}\n" if $entry_ref->{phone};
	print "Email: $entry_ref->{email}\n" if $entry_ref->{email};
	print "Interests: $entry_ref->{interests}\n"
							if $entry_ref->{interests};
	print "\n";
}

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);
}