#!/usr/bin/perl -w
use strict;
use DBI;

(my $sec,my $min,my $hours,my $day,my $month,my $year)=localtime();
$month+=1;
$year+=1900;
my $date="$year-$month-$day $hours:$min:$sec";
my %traffic;

if (! -d "/var/log/traffic/") {mkdir "/var/log/traffic",0755;}
if (! -d "/var/log/traffic/$year") {mkdir "/var/log/traffic/$year",0755;}
if (! -d "/var/log/traffic/$year/$month") {mkdir "/var/log/traffic/$year/$month",0755;}
if (! -d "/var/log/traffic//$year/$month/$day") {mkdir "/var/log/traffic/$year/$month/$day",0755;}

open (LOG,">/var/log/traffic/$year/$month/$day/$date.log") or die "$!";

my @total=`/sbin/iptables -L FORWARD -vnx`;
my @ip_net=`/sbin/iptables -L ip_net_in -vnx`;
my @ru=`/sbin/iptables -L ru_in -vnx`;

for (my $i=2;$i<(@ip_net);$i++){
    (my $ip_net_byte,my $ip)=(split(/\s+/,$ip_net[$i]))[2,9];
    (my $ru_byte)=(split(/\s+/,$ru[$i]))[2];
    @{$traffic{$ip}}=($ip_net_byte,$ru_byte);
    print LOG "$ip\t$ip_net_byte\t$ru_byte\n";
}
close(LOG);

`/etc/routing/rc.firewall`;

my $dbh=DBI->connect("dbi:Pg:dbname=gateway_traffic","user","password") or die "$!";

my $sth=$dbh->prepare("select * from \"ip_list\"");
$sth->execute();

my %ip_list;

while (my $ip=$sth->fetchrow_array){
    $ip_list{$ip}=1;
    }

foreach my $ip (keys %traffic){
        if ($ip_list{$ip}) {$dbh->do("insert into \"$ip\" values(\'$date\',${$traffic{$ip}}[0],${$traffic{$ip}}[1])");}
        else {
        $dbh->do("insert into ip_list values(\'$ip\')");
        $dbh->do("create table \"$ip\" (date timestamp,ip_net_byte int8,ru_byte int8)");
        $dbh->do("insert into \"$ip\" values(\'$date\',${$traffic{$ip}}[0],${$traffic{$ip}}[1])");
        $dbh->do("GRANT SELECT ON \"$_\" TO apache");
        }
}

$dbh->disconnect;