#!/usr/bin/perl
#
# A script to search for all current unseen quarantined messages and send the
# user a digest.
# User ID's and final destination emails are looked up via LDAP.
# Harry Hoffman <hhoffman@ip-solutions.net>
#
use strict;
use warnings;
use DBI;
use Net::LDAP;
use Net::SMTP;

my %uid2email;
my %uidSpamReport;
my (undef,undef,undef,$day,$month,$year,undef,undef,undef) = localtime(time);
# make the month and year user friendly
$month++;
$year += 1900;



my $dbh = DBI->connect( "dbi:mysql:amavis:localhost", "user", "pass" , {
    RaiseError => 1
});

my $sth = $dbh->prepare( " SELECT msgs.time_num,msgs.from_addr,msgs.subject,msgs.spam_level,recip.email FROM msgs INNER JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id LEFT JOIN maddr AS sender ON msgs.sid=sender.id LEFT JOIN maddr AS recip ON msgrcpt.rid=recip.id WHERE (msgs.content = 'S' OR msgs.content = 'B') AND (msgrcpt.rs = '') AND NOT (msgs.quar_type = '') " );

$sth->execute();

# Grab *all* of the SPAM info.
# WARNING:
# This may become very exhaustive if there is a ton of SPAM in the database.
my $array_ref = $sth->fetchall_arrayref();
foreach my $row (@$array_ref)
{
  my ( $time, $from, $subject, $spam_level, $recip ) = @$row;
  # based off of each $recip (dest email addr in database) get the user's id
  # (ldap uid) and pull their mail attribute as a final destination.
  my ( $uid, $final_recip ) = getLDAPinfo($recip);
  # uid and mail (from ldap) go into this hash... we then use the uid as a
  # lookup for the %uidSpamReport to map $final_recip with their SPAM reports.
  $uid2email{$uid} = $final_recip if (not exists $uid2email{$uid});
  my $report = sprintf("\nDate: %s\nFrom: %s\nTo: %s\nSubject: %s\nSpamLevel: %s\n\n",
                scalar(localtime($time)),
                $from,
                $recip,
                $subject,
                $spam_level);
   $uidSpamReport{$uid} .= $report;
}

sendDigest();
exit;

sub getLDAPinfo
{
  my $email = shift;
  my $ldapserver = "ldap.your.dom";
  my $ldap = Net::LDAP->new("$ldapserver") or die "$@";
  my $basedn = "o=SOME ORG";
  my $ldapfilter = "(|(mail=$email)(mailAlternateAddress=$email))";
  $ldap->bind;
  my $mesg = $ldap->search( base => "$basedn",
                            scope => 'sub',
                            filter => "$ldapfilter",
                            attrs => ['uid','mail']
                           );
  $mesg->code && die $mesg->error;
  my $acct = $mesg->entry->get_value("uid");
  my $recip = $mesg->entry->get_value("mail");
  $mesg = $ldap->unbind;
  return ($acct,$recip);
}


sub sendDigest
{
  for my $key (keys %uid2email)
  {
     my $smtp = Net::SMTP->new('smtp.some.dom');
     my $mail_from = "postmaster\@some.dom";
     my $rcpt_to = $uid2email{$key};
     die "unable to connect to smtp server" unless $smtp;
     $smtp->mail("$mail_from");
     $smtp->to("$rcpt_to");
     $smtp->data();
     $smtp->datasend("To: $rcpt_to\n");
     $smtp->datasend("From: $mail_from\n");
     $smtp->datasend("Subject: Spam report for $day\/$month\/$year\n");
     $smtp->datasend("\n");
     $smtp->datasend("\n");
     $smtp->datasend("The following messages are thought to be SPAM.\n");
     $smtp->datasend("Please log into https://www.some.dom/mailzu/\n");
     $smtp->datasend("to delete or release these messages.\n");
     $smtp->datasend("\n");
     $smtp->datasend("==============================================================================\n");
     $smtp->datasend("\n");
     $smtp->datasend("$uidSpamReport{$key}\n");
     $smtp->dataend();
     $smtp->quit();
  }
}