#!/usr/bin/perl
use DBI;
use cool_env;
use conf;
#use Tie::File;

#vars
$nip;
@flags=();
%counts={};
$dnld_lim;
$upld_lim;
$add_flag;
$del_flag;
$ip;
%rows2select=(
    id=>	0,
    nip=>	1,
    flags=>	2,
    counts=>	3,
    dnld_lim=>	4,
    upld_lim=>	5);
@fields=();
@counts_array=();
$switch;
$key;
#$user_counter=0;
#conf read
$conf_name="billing.conf";
$ctest=conf->var_read($conf_name);
$main_table=$ctest->{'mysql_tables.data'};
$traf_log=$ctest->{'mysql_tables.traf'};
$script_log=$ctest->{'mysql_tables.slow'};
$log_timing=$ctest->{'log.slow'};
$syslogd_pid=$ctest->{'environment.syslogd_pid'};
$ipt_log_path=$ctest->{'environment.ipt_log_path'};
#db_connect
$dbh = 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'});
#reboot
$param=$ctest->{'mysql_tables.param'};
$reboot=$dbh->selectrow_array("SELECT state from $param;");
if($reboot eq "stop"){system("iptables -I FWD -j DROP");$dbh->do("UPDATE $param set state='stopd';");}
if($reboot eq "start"){system("iptables -D FWD -j DROP");$dbh->do("UPDATE $param set state='wait';");}
#log timing
if($log_timing) {$timestamp=$dbh->selectrow_array("SELECT now();");}
#env create
$test=cool_env->create(
$ctest->{'environment.inet_iface'},$ctest->{'environment.inet_band'},$ctest->{'ugly.hinet_iface'},
$ctest->{'environment.lan_iface'},$ctest->{'environment.lan_band'},$ctest->{'ugly.hlan_iface'},$ctest->{'environment.lan_gate_ip'},$ctest->{'environment.lan_ip_range'},
$ctest->{'environment.iptables'},$ctest->{'environment.tc'},$ctest->{'environment.temp_path'},$ctest->{'environment.init_path'},"/var/www/wassup");
#ppp_tc
$out_tc_ok=$test->tc_root_check("out");
$in_tc_ok=$test->tc_root_check("in");
if(!$in_tc_ok) {$switch=1 << $ctest->{"flags.dnld_flag"};$dbh->do("UPDATE $main_table SET flags=flags\|$switch;");}
if(!$out_tc_ok) {$switch=1 << $ctest->{"flags.upld_flag"};$dbh->do("UPDATE $main_table SET flags=flags\|$switch;");}
#print head
$test->print_head;
#main read
$select_string=join(',',sort {$rows2select{$a}<=>$rows2select{$b}} keys %rows2select);
$select_string="SELECT $select_string from $main_table";
$sth=$dbh->prepare("$select_string");
$sth->execute();
#main cycle
while(@fields=$sth->fetchrow_array())
{
$id=$fields[$rows2select{id}];
$nip=$fields[$rows2select{nip}];
$dnld_lim=$fields[$rows2select{dnld_lim}];
$upld_lim=$fields[$rows2select{upld_lim}];
@flags=$ctest->get_binary($fields[$rows2select{flags}]);
$add_flag=$flags[$ctest->{'flags.add_flag'}];
$del_flag=$flags[$ctest->{'flags.del_flag'}];
$blocked_flag=$flags[$ctest->{'flags.blocked_flag'}];
$log_flag=$flags[$ctest->{'flags.log_flag'}];
$dnld_flag=$flags[$ctest->{'flags.dnld_flag'}];
$upld_flag=$flags[$ctest->{'flags.upld_flag'}];
@counts_array=$ctest->get_binary($fields[$rows2select{counts}]);
$counts{'TCP_IN'}=$counts_array[$ctest->{'counts.tcp_in_count'}];
$counts{'UDP_IN'}=$counts_array[$ctest->{'counts.udp_in_count'}];
$counts{'ICMP_IN'}=$counts_array[$ctest->{'counts.icmp_in_count'}];
$counts{'TCP_OUT'}=$counts_array[$ctest->{'counts.tcp_out_count'}];
$counts{'UDP_OUT'}=$counts_array[$ctest->{'counts.udp_out_count'}];
$counts{'ICMP_OUT'}=$counts_array[$ctest->{'counts.icmp_out_count'}];
$counts{'REST_IN'}=$counts_array[$ctest->{'counts.rest_in_count'}];
$counts{'REST_OUT'}=$counts_array[$ctest->{'counts.rest_out_count'}];
for $key (keys %counts) {delete $counts{$key} if !defined $counts{$key}}
#count vars
$ip=$test->ntoa($nip);
$drate="$dnld_lim\Kbit";
$dweight=$dnld_lim/10;
$dweight="$dweight\Kbit";
$urate="$upld_lim\Kbit";
$uweight=$upld_lim/10;
$uweight="$uweight\Kbit";
#ipt_db add_user
if($add_flag)
    {
    $test->nip("build",$nip,$ip);
    $switch=1 << $ctest->{'flags.add_flag'};
    $dbh->do("UPDATE $main_table SET flags=flags\^$switch WHERE nip=$nip");
    }
#speed limit
if($dnld_flag)
    {
    $test->tc_pipe("destr",IN,$id,$ip,NULL,NULL);
    if ($dnld_lim!=0) {$test->tc_pipe("build",IN,$id,$ip,$drate,$dweight);}
    $switch=1 << $ctest->{'flags.dnld_flag'};
    $dbh->do("UPDATE $main_table SET flags=flags\^$switch WHERE nip=$nip");
    }
if($upld_flag)
    {
    $test->tc_pipe("destr",OUT,$id,$ip,NULL,NULL);
    if ($upld_lim!=0) {$test->tc_pipe("build",OUT,$id,$ip,$urate,$uweight);}
    $switch=1 << $ctest->{'flags.upld_flag'};
    $dbh->do("UPDATE $main_table SET flags=flags\^$switch WHERE nip=$nip");
    }
#ipt_read, db_update
    %ipt_delta=($test->nip("learn",$nip,NULL));
    for $key (keys %ipt_delta) {$dbh->do("UPDATE $main_table SET $key=$key+$ipt_delta{$key} WHERE nip=$nip;") if defined $ipt_delta{$key};}
#ipt del excessive
    for $key (keys %counts)
        {
	if(!$counts{$key}) {$test->chain("destr","$key\_$nip",$ip);}
	else {$test->chain("build","$key\_$nip",$ip);}
        }
#ipt block
    $block_flag=$dbh->selectrow_array("SELECT (tcp_in+udp_in+icmp_in)>=in_lim from $main_table where nip=$nip;");
    $block_flag=$block_flag||$dbh->selectrow_array("SELECT (tcp_out+udp_out+icmp_out)>=out_lim from $main_table where nip=$nip;");
    $block_flag=$block_flag*$dbh->selectrow_array("SELECT in_lim!=777 from $main_table where nip=$nip;");
    $block_flag=$block_flag*$dbh->selectrow_array("SELECT out_lim!=777 from $main_table where nip=$nip;");
    if($ctest->{'ugly.date_check'}) {$block_flag=$block_flag||$dbh->selectrow_array("SELECT ed<=now() from $main_table where nip=$nip;");}
    if(($block_flag)&&(!$blocked_flag))
	{
	$switch=1 << $ctest->{'flags.blocked_flag'};
	$dbh->do("UPDATE $main_table SET flags=flags\|$switch WHERE nip=$nip");
	$test->nip("block",$nip,NULL);
	}
    elsif((!$block_flag)&&($blocked_flag)) 
	{
	$switch=1 << $ctest->{'flags.blocked_flag'};
	$dbh->do("UPDATE $main_table SET flags=flags\|$switch WHERE nip=$nip");
	$dbh->do("UPDATE $main_table SET flags=flags\^$switch WHERE nip=$nip");
	$test->nip("unblock",$nip,NULL);
	}
#log
if($log_flag)
    {
    $hwaddr=$test->hwaddr($ip);
    $dbh->do("INSERT into $traf_log
    (nip,hwaddr,tcp_in,udp_in,icmp_in,rest_in,tcp_out,udp_out,icmp_out,rest_out)
    values
    ($nip,'$hwaddr',$ipt_delta{TCP_IN},$ipt_delta{UDP_IN},$ipt_delta{ICMP_IN},$ipt_delta{REST_IN},$ipt_delta{TCP_OUT},$ipt_delta{UDP_OUT},$ipt_delta{ICMP_OUT},$ipt_delta{REST_OUT});");
    }
#ipt_db delete_user
if($del_flag)
    {
    $test->nip("destr",$nip,$ip);
    $test->tc_pipe("destr",IN,$id,$ip,NULL,NULL);
    $test->tc_pipe("destr",OUT,$id,$ip,NULL,NULL);
    $dbh->do("DELETE FROM $main_table WHERE nip=$nip");
    }
#ipt_tc save
else
    {
    for $key (sort keys %counts) {$test->chain("print","$key\_$nip",$ip) if($counts{$key});}
    if($dnld_lim!=0){$test->tc_pipe("print",IN,$id,$ip,$drate,$dweight);}
    if($upld_lim!=0){$test->tc_pipe("print",OUT,$id,$ip,$urate,$uweight);}
    }
}
$sth->finish;
#log timig
if($log_timing){$dbh->do("INSERT INTO $script_log set time_start=\"$timestamp\",time_stop=CURRENT_TIMESTAMP,users=(SELECT count(*) from $main_table),code='c';");}
#db_ipt disconnect_reset
$dbh->disconnect;
$test->ipt_log_manage($syslogd_pid,$ipt_log_path);
$test->wassup;
$test->finish;

undef $ctest;
#undef $test;