#Get clubs from DB and email out the bounces to the secretaries.
#I'm sorry to myself and anyone who has to use this... (06/09/13 - DJC)

use DBI;
use Data::Dumper;
use Email::MIME;

[redacted DB details]

#Connect to the DB
$dbh = DBI->connect('dbi:mysql:'.$db,$user,$pass) or die "Connection Error: $DBI::errstr\n";

$sql = "SELECT ClubNo FROM Bounces Group By ClubNo";
$sql_sth = $dbh->prepare($sql);
$sql_sth->execute();

#This is a bit horrible...

while(my ($ClubID) = $sql_sth->fetchrow_array()) {
                #print "Club: $ClubID\n";
		my $sql2 = "SELECT Rotarians.MemEmail FROM clubofficeholders JOIN Rotarians ON Rotarians.RotID = clubofficeholders.RotID WHERE clubofficeholders.ClubID=$ClubID AND clubofficeholders.YrID=109 AND clubofficeholders.ClubOffID IN (1,2)";
		$sql2_sth = $dbh->prepare($sql2);
		$sql2_sth->execute() or die "SQL Error: $DBI::errstr";

		my @Emails;
		my @Members;

		#Secretary's email address
		while(my ($MemEmail) = $sql2_sth->fetchrow_array()){
		print " Club: $ClubID AND EMAIL: $MemEmail\n";
		push(@Emails, $MemEmail);

		}


		#print "Sec: ".$sec_email."\n";
		#print Dumper(@Emails);


		#Why are we doing it this way? Really? I hope an actual programmer never sees this...
		my $sql3 = "SELECT EmailAddress FROM Bounces WHERE ClubNo=$ClubID";
		$sql3_sth = $dbh->prepare($sql3);
		$sql3_sth->execute() or die "SQL Error: $DBI::errstr";

		#eq not == for some strange reason (Perl...)
		#print "\n";
		while(my ($EmailAddress) = $sql3_sth->fetchrow_array())
		{

			if ( grep( /^$EmailAddress$/, @Members ) )
			{
			#print "exists";
			}
			else
			{
			#print "Member: ".$EmailAddress."\n";
			#This bit is the actaul members email address that's bouncing
			push(@Members, $EmailAddress);
			}
			$EmailSend = $Emails[1];

		}

Dumper($Emails[1]);

#$Emails[1];

if ($EmailSend eq "")
{
$EmailSend = "postmaster\@rotary-ribi.org";
}

#print $EmailSend;

if ($Emails[1] eq "")
{
$Emails[1] = "postmaster\@rotary-ribi.org";
}

	$email_body_1 = "The following member's emails are bouncing back to the Rotary Email Server. This is a result of their email addresses being incorrect or a spam filter blocking Rotary emails. AOL and Hotmail are often culprits for being overzealous with their blocking.

Please correct the member's database record  or contact the relevant Rotarian to advise them to add rotary-ribi.org to their whitelist or use another email address for Rotary";

	$email_body_2 = join(', ', @Members);
	$email_body_2 = join "\n", split ",  ", $email_body_2;

	$email_body = $email_body_1 . $email_body_2;

	#Time to actually send it, yay!

 	my $message = Email::MIME->create(
	header_str => [
    		From    => 'postmaster@rotary-ribi.org',
    		To      => '$Emails[1]',
    		Subject => 'Rotary Email Bounces',
  		],
  	attributes => {
    		encoding => 'quoted-printable',
    		charset  => 'ISO-8859-1',
  		},
  	body_str => "$email_body",
	);

	# send the message
	use Email::Sender::Simple qw(sendmail);
	sendmail($message);
	print "Sent to $Emails[1] \n";

}