#!/usr/bin/perl -w
#  
#  Optimization code by Ick (up@msh.lv)
#  Sep-Oct 2000 patched by Deniss (deniss at fuckoff.com)
#  (C)2000, by RamboZa
#
#

use Pg;

sub ip2dec {
        my $ip;
        my @tmp;
        my $dig;
        $ip = shift;
        @tmp=split(/\./,$ip);
        $dig=($tmp[0]<<24)+($tmp[1]<<16)+($tmp[2]<<8)+$tmp[3];
        return $dig;
}

sub is_latvian {
    my $ip;
    my $ip_;
    my $i;
    my $m;
    $ip_ = shift;
    $ip= ip2dec($ip_);
    $m = $#latvian_nets+1;
    for($i=0; $i<$m; $i+=2) {
        if (($ip >= $latvian_nets[$i]) and ($ip <= $latvian_nets[$i+1])) {
            return 1;
        }
    }
    return 0;
}


$my_year=`/bin/date -d yesterday +%Y`;  chop($my_year); # for year !
$my_mon=`/bin/date -d yesterday +%m`;   chop($my_mon);    # for  month !
$today_mon=`/bin/date +%m`;         chop($today_mon);
$my_day=`/bin/date -d yesterday +%d`;  chop($my_day);     # for day !
#$mon_st=`/bin/date -d yesterday +%d.%b.%Y`; chop($mon_st);           # for log file
$mon_st=`date -d yesterday +%b`; chop($mon_st);           # for log file

################ !! Fill Networks Array !! ##################
open(INPUT_FILE, '/usr/local/adm/latvian_networks') || die "Error open Networks File";
while (<INPUT_FILE>) {
  tr/\r\n//d;
  s/( )+/ /gs;
  @tmp=split " ";
  $iplv = ip2dec($tmp[0]);
  $mask = ip2dec($tmp[1]);
  $latvian_nets[$#latvian_nets+1] = $iplv;
  $latvian_nets[$#latvian_nets+1] = ($iplv | (0xFFFFFFFF ^ $mask));
}
close(INPUT_FILE);
############# !! End of  Fill Networks Array !! ###############

$conn = Pg::setdbLogin("localhost","" , "", "", "accounting", "postgres","qwerty99");
if ($conn->status == PGRES_CONNECTION_BAD) {
	print "Error Connect to Database\n";
	die;
}
$result = $conn->exec("select * from users ORDER BY ip");
if ($result->resultStatus == PGRES_BAD_RESPONSE ){
	print "Oooppsss, Query Error\n";
	die;
}

my @row;
while (@row = $result->fetchrow) {

$ip =  $row[8];
$input=$output=$inputlv=$outputlv=0;

############# !! Read network data !! ###############
#$trafflogfile="/var/log/traffic/archive/net-acct.log.".$mon_st;
if ($my_mon!=$today_mon) {
        $trafflogfile="/var/log/traffic/net-acct.log.".$mon_st;
                        } else {
        $trafflogfile="/var/log/traffic/net-acct.log";
	};
open(INPUT_FILE, $trafflogfile) || die "Error open log File"; 
while (<INPUT_FILE>) {
  tr/\r\n//d; 
  s/( )+/ /gs; 
  @inputZ=split " "; 

### get Data from file values ###
($sec_f,$min_f,$hour_f,$mday_f,$mon_f,$year_f,$wday_f,$ydat_f,$isdst_f) = localtime($inputZ[0]);
$mon_f+=1;
$year_f+=1900;
$sec_f =$min_f=$hour_f =$wday_f=$ydat_f=$isdst_f=0;
###########################

if ($year_f == $my_year && $mon_f == $my_mon && $mday_f == $my_day) {
############## !! Test for International  !! ################
	if ( $inputZ[4] eq $ip ) {
            if ( is_latvian( $inputZ[2] ) ) { 
                $inputlv += $inputZ[6];
            }
            else {
                $input += $inputZ[6];
            };
        }

        if ( $inputZ[2] eq $ip ) {
            if ( is_latvian( $inputZ[4] ) ) {
                $outputlv += $inputZ[6];
            }
            else {
                $output += $inputZ[6];
            };
        }
####################################
	}

} #end of While (INPUT_FILE)
close(INPUT_FILE);
############# !! END Of Read network data !! ###############

################# FILL DATABASE VALUES ####################
$queryz = "insert into statistic values($row[0],'$my_day/$my_mon/$my_year','$row[8]',$input,$output,$inputlv,$outputlv)";
print "$queryz\n";
$resultz = $conn->exec($queryz);
if ($resultz->resultStatus == PGRES_BAD_RESPONSE ){
	print "Error insert Data into DataBase\n";
	die;
}
############# END OF  FILL DATABASE VALUES ################

} #end of PG get IP !
print "\n Well Done for $my_day-$my_mon-$my_year !!!\n";