#!/usr/bin/perl
use DBI;
use conf;

#conf open_read
$conf_name="billing.conf";
$ctest=conf->var_read($conf_name);
$main_table=$ctest->{'mysql_tables.data'};
$log_table=$ctest->{'mysql_tables.traf'};
$log_time=$ctest->{'mysql_tables.slow'};
$hourly_sum=$ctest->{'mysql_tables.hour'};
$db_handle=DBI->connect(
"DBI:mysql:database=$ctest->{'mysql_connect.db_db'};host=$ctest->{'mysql_connect.db_host'}",
$ctest->{'mysql_connect.db_user'},
$ctest->{'mysql_connect.db_password'});

$timestamp=$db_handle->selectrow_array("SELECT date_sub(now(),interval 1 hour);");
@datetime=split(' ',$timestamp);
@time=split(':',$datetime[1]);
$start_date="$datetime[0] $time[0]:00:00";
$end_date="$datetime[0] $time[0]:59:59";
$timestamp=$db_handle->selectrow_array("SELECT now();");

$sth=$db_handle->prepare("SELECT nip FROM $main_table;");
$sth->execute;
while($current_nip=$sth->fetchrow_array())
{
$db_handle->do("INSERT INTO $hourly_sum SET
    time=\"$end_date\",
    nip=$current_nip,
    tcp_in=(SELECT sum(tcp_in) FROM $log_table where time between \"$start_date\" and \"$end_date\" and nip=$current_nip),
    udp_in=(SELECT sum(udp_in) FROM $log_table where time between \"$start_date\" and \"$end_date\" and nip=$current_nip),
    icmp_in=(SELECT sum(icmp_in) FROM $log_table where time between \"$start_date\" and \"$end_date\" and nip=$current_nip),
    rest_in=(SELECT sum(rest_in) FROM $log_table where time between \"$start_date\" and \"$end_date\" and nip=$current_nip),
    tcp_out=(SELECT sum(tcp_out) FROM $log_table where time between \"$start_date\" and \"$end_date\" and nip=$current_nip),
    udp_out=(SELECT sum(udp_out) FROM $log_table where time between \"$start_date\" and \"$end_date\" and nip=$current_nip),
    icmp_out=(SELECT sum(icmp_out) FROM $log_table where time between \"$start_date\" and \"$end_date\" and nip=$current_nip),
    rest_out=(SELECT sum(rest_out) FROM $log_table where time between \"$start_date\" and \"$end_date\" and nip=$current_nip);");
}
$sth->finish;
$db_handle->do("INSERT INTO log_arch SELECT * from $log_table where time between \"$start_date\" and \"$end_date\"");
$db_handle->do("DELETE FROM $log_table where time between \"$start_date\" and \"$end_date\"");
$db_handle->do("INSERT INTO $log_time SET time_start=\"$timestamp\",time_stop=CURRENT_TIMESTAMP,users=(SELECT count(*) from $main_table),code='h';");
$db_handle->disconnect();