#! /usr/bin/perl

# need_renewal - find Historical League members that need to renew
# their memberships soon.  Default value for "soon" is 30 days.  A
# different value may be specified on the command line.

# Specifying a value of 0 finds those memberships that have already
# expired.

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
# default cutoff is 30 days; reset if numeric argument given
my ($cutoff) = 30;
$cutoff = shift (@ARGV) if @ARGV && $ARGV[0] =~ /^\d+$/;

warn "Using cutoff of $cutoff days\n";

my ($sth) = $dbh->prepare (qq{
		SELECT
			member_id, email, last_name, first_name, expiration,
			TO_DAYS(expiration) - TO_DAYS(CURRENT_DATE) AS days
		FROM member
		WHERE expiration < DATE_ADD(CURRENT_DATE, INTERVAL ? DAY)
		ORDER BY expiration, last_name, first_name
});
$sth->execute ($cutoff);	# pass cutoff as placeholder value

while (my $hash_ref = $sth->fetchrow_hashref ())
{
	print join ("\t",
				$hash_ref->{member_id},
				$hash_ref->{email},
				$hash_ref->{last_name},
				$hash_ref->{first_name},
				$hash_ref->{expiration},
				$hash_ref->{days} . " days")
		. "\n";
}
$sth->finish ();
# MAIN-BODY

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