#!/usr/bin/perl

use DBI;

mailtraffic("salomon\@qasdf.ru", "", "spy\@qasdf.ru", "Traffic for Salomon", "192.168.20.1", "192.168.20.%"); 
mailtraffic("rocer\@qasdf.ru", "", "spy\@qasdf.ru", "Traffic for Rocer", "192.168.30.1","192.168.30.%");

exit;

sub mailtraffic
{

$to_addr = $_[0];
$cc_addr = $_[1];
$bcc_addr = $_[2];
$subject = $_[3];
$ip_excl = $_[4];
$ip_incl = $_[5];

$dsn = 'DBI:mysql:ipacc:localhost';
$db_user = '';
$db_pass = '';
$dbh = DBI->connect($dsn, $db_user, $db_pass);

$firstday = `date -v-1d '+%Y-%m-01'`;
chomp($firstday);
$lastday = `date -v-1d '+%Y-%m-%d'`;
chomp($lastday);

my $dt, $sent, $recv, $sent_total, $recv_total;

format HEADER =
             |     Sent (bytes) |   Recv (bytes)
.

format LINE =
--------------------------------------------------
.

format BYDAY =
@<<<<<<<<<   | @>>>>>>>>>>>>    | @>>>>>>>>>>>>
$dt, $sent, $recv
. 

format TOTAL =
Total        | @##########.# Mb | @##########.# Mb
$sent_total, $recv_total
.

my $q_excl, $q_incl;

if ($ip_excl ne "") { $q_excl = "(ip != '".$ip_excl."') AND "; };
if ($ip_incl ne "") { $q_incl = "(ip LIKE '".$ip_incl."') AND "; };   

$q = "select dt, sum(sent), sum(recv) FROM traffic where (".$q_excl.$q_incl;
$q = $q." (dt>='".$firstday."') AND (dt<='".$lastday."')) group by dt";
$sth = $dbh->prepare(qq{$q});
$sth->execute();

open (MAIL, "|/usr/local/sbin/sendmail -oi -t -odq") or die "Can't fork sendmail!!!\n";

select(MAIL); 

print "From: Traffic Administrator \n";
print "To: <$to_addr>\n";

if ($cc_addr ne "")
{
   print "Cc: <$cc_addr>\n";
};

if ($bcc_addr ne "")
{
   print "Bcc: <$bcc_addr>\n";
};

print "Subject: $subject\n";
print "MIME-Version: 1.0\n";
print "Content-type: text/html\n";
print "   charset=\"windows-1251\"\n";
print "Content-Transfer-Encoding: 8bit\n\n";

print "<PRE\>\n";

$sent_total = 0;
$recv_total = 0;

$~ = "HEADER";
write;

$~ = "LINE";
write;

while (($dt, $sent, $recv) = $sth->fetchrow_array())
{
   $sent_total = $sent_total + $sent;
   $recv_total = $recv_total + $recv;

   $~ = "BYDAY";
   write;
};

$sent_total = $sent_total / 1048576;
$recv_total = $recv_total / 1048576;

$~ = "LINE";
write;

$~ = "TOTAL";
write;

print "\n\n";
print "1 Mb = 1048576 bytes\n";

print "</PRE>\n";
$sth->finish();
close(MAIL);
}