#!/usr/bin/perl
use DBI;
use conf;
use CGI qw/:standard/;

#conf open_read
$conf_name="billing.conf";
$ctest=conf->var_read($conf_name);
#vars
$main_table=$ctest->{'mysql_tables.data'};
$hourly_sum=$ctest->{'mysql_tables.hour'};
$log_table=$ctest->{'mysql_tables.slow'};
$users_log=$ctest->{'mysql_tables.traf'};
$current_nip=param(nip);
%rows2select=(
id=>			0,
flags=>			1);
%options=();
@flags=(log_flag,add_flag,del_flag,dnld_flag,upld_flag,blocked_flag);
$select_string=join(',',sort {$rows2select{$a}<=>$rows2select{$b}} keys %rows2select);
$select_string="select $select_string from $main_table where nip=$current_nip;";
$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 now();");
$ip=$db_handle->selectrow_array("SELECT inet_ntoa($current_nip);");
$dtime=$db_handle->selectrow_array("SELECT
(select time_to_sec(time) from $users_log where nip=$current_nip order by time desc limit 1)-
(select time_to_sec(time) from $users_log where nip=$current_nip order by time desc limit 1,1);");
@in=(tcp_in,udp_in,icmp_in,rest_in);
@out=(tcp_out,udp_out,icmp_out,rest_out);
@counts=(tcp_in_count,udp_in_count,icmp_in_count,rest_in_count,tcp_out_count,udp_out_count,icmp_out_count,rest_out_count);
if($ctest->{'appearence.do_rest'}==0)
{
@in=(tcp_in,udp_in,icmp_in);
@out=(tcp_out,udp_out,icmp_out);
@counts=(tcp_in_count,udp_in_count,icmp_in_count,tcp_out_count,udp_out_count,icmp_out_count);
}
#HTML
print"Content-type: text/html;charset=cp-1251;\n\n";
print"<html><HEAD><TITLE>$ip advanced user stat</TITLE>";
print"<style>td {font-family: monospace}";
print"table {border-collapse: collapse;}";
print"body {font-family: monospace;}";
print"form {margin: 0;}";
#print"td.primaryleft{border-bottom: 1px solid #ffffff; border-left: 1px solid #ffffff; border-right: 1px solid #ffffff; color:#000000; padding: 2px;background-color: #ff00ff;}";
print"td.primary{border-bottom: 1px solid #ffffff; border-right: 1px solid #ffffff; padding: 2px; background-color: #ff00ff;}";
print"td.primary_spacer{border-bottom: 1px solid #ffffff; padding: 2px; color: #00ffff;}";
#print"td.secondaryleft{border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; color: #ffffff; padding: 2px;background-color: #800080;}";
print"td.secondary{border-bottom: 1px solid #000000; border-right: 1px solid #000000; color: #ffffff; padding: 2px; background-color: #800080;}";
print"td.secondary_spacer{border-bottom: 1px solid #000000; color: #ffffff; padding: 2px; color: #00ffff;}";
print"table.main {border-top: 1px solid #ffffff; border-left: 1px solid #ffffff;}";
print"</style>";
print"</HEAD>\n";
print"<body bgcolor=#888888><CENTER>\n";
print"<hr>\n";
#var init
%input_options=(
ip=>			"size=10",
dnld_lim=>		"size=4",
upld_lim=>		"size=4",
tcp_in=>		"size=11",
udp_in=>		"size=11",
icmp_in=>		"size=11",
rest_in=>		"size=11",
tcp_out=>		"size=11",
udp_out=>		"size=11",
icmp_out=>		"size=11",
rest_out=>		"size=11",
in_lim=>		"size=11",
out_lim=>		"size=11",
ed=>			"size=16");
%rows2select=(
id=>			0,
nip=>			1,
flags=>			2,
counts=>		3,
dnld_lim=>		4,
upld_lim=>		5,
tcp_in=>		6,
udp_in=>		7,
icmp_in=>		8,
rest_in=>		9,
in_lim=>		10,
tcp_out=>		11,
udp_out=>		12,
icmp_out=>		13,
rest_out=>		14,
out_lim=>		15,
ed=>			16);
@in=(tcp_in,udp_in,icmp_in,rest_in);
@out=(tcp_out,udp_out,icmp_out,rest_out);
@counts=(tcp_in_count,udp_in_count,icmp_in_count,rest_in_count,tcp_out_count,udp_out_count,icmp_out_count,rest_out_count);
@flags=(log_flag,add_flag,del_flag);
@hidden=(dnld_lim,upld_lim,tcp_in,tcp_out,udp_in,udp_out,icmp_in,icmp_out,log_flag);
@flags_array=();
@counts_array=();
%checkbox_options=();
$class="primary";
$select_string=join(',',sort {$rows2select{$a}<=>$rows2select{$b}} keys %rows2select);
$select_string="select $select_string from $main_table where nip=$current_nip;";
$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'});

@data=$db_handle->selectrow_array("$select_string");
$current_nip=$data[$rows2select{nip}];
$ip=$db_handle->selectrow_array("SELECT inet_ntoa($current_nip);");

@counts_array=$ctest->get_binary($data[$rows2select{counts}]);
foreach $key(@counts){if($counts_array[$ctest->{"counts.$key"}]) {$checkbox_options{$key}=" checked";} else {$checkbox_options{$key}="";}}

if($flags_array[$ctest->{'flags.del_flag'}])
    {
    foreach $key(keys %checkbox_options){$checkbox_options{$key}="$checkbox_options{$key} disabled";};
    foreach $key(keys %input_options){$input_options{$key}="$input_options{$key} disabled";}
    }

print"<table class=main>\n";
print"<tr>\n";
print"<td class=$class>$ip<form action=\"/cgi-bin/edit.pl\" method=GET><input type=hidden name=nip value=$data[$rows2select{nip}] size=0>\n";
for $temp(@hidden) {print"<input type=hidden name=$temp value=\"-1\">";}
print"</td>\n";
$temp="rest_in";
print"<td class=$class><input type=text name=$temp value=$data[$rows2select{$temp}] $input_options{$temp}></td>\n";
for $temp(@in) {print"<td class=$class><input type=checkbox name=\"$temp\_count\" value=1 $checkbox_options{\"$temp\_count\"}></td>\n";}
print"</tr>\n";

print"<tr>\n";
print"<td class=$class>";
if(!$flags_array[$ctest->{'flags.del_flag'}]){print"<input type=submit name=ok value=\"ok \">\n";} else {print"&nbsp;";}
print"</td>\n";
$temp="rest_out";
print"<td class=$class><input type=text name=$temp value=$data[$rows2select{$temp}] $input_options{$temp}>\n";
print"</td>\n";
for $temp(@out) {print"<td class=$class><input type=checkbox name=\"$temp\_count\" value=1 $checkbox_options{\"$temp\_count\"}></td>\n";}
print "</form>\n";
print"</tr>\n";
print"</table><hr>";

@flags_array=$ctest->get_binary($data[$rows2select{flags}]);
foreach $key(@flags){if($flags_array[$ctest->{"flags.$key"}]) {$options{$key}=" checked";} else {$options{$key}="";}}

print"<table class=main>\n";
print"<tr>";
print"<td class=$class>$ip (id: $data[$rows2select{id}])</td>\n";
print"<td class=$class>",$db_handle->selectrow_array("SELECT hex(hwaddr) FROM $users_log where nip=$current_nip ORDER by time DESC LIMIT 1;"),"</td>\n";
print"<td class=$class><input type=checkbox disabled $options{log_flag}></td>\n";
print"<td class=$class><input type=checkbox disabled $options{dnld_flag}></td>\n";
print"<td class=$class><input type=checkbox disabled $options{add_flag}></td>\n";
print"<td class=$class>",$db_handle->selectrow_array("SELECT (select tcp_in from $users_log where nip=$current_nip order by time desc limit 1)/(1024*$dtime);"),"($dtime)</td>\n";
print"</tr>\n";

print"<tr>\n";
print"<td class=$class>",$db_handle->selectrow_array("SELECT time FROM $users_log where (nip=$current_nip AND hwaddr not like 0x0) ORDER by time DESC LIMIT 1;"),"</td>\n";
print"<td class=$class>\n",$db_handle->selectrow_array("SELECT hex(hwaddr) FROM $users_log where (nip=$current_nip AND hwaddr<>0x0) ORDER by time DESC LIMIT 1;"),"</td>\n";
print"<td class=$class><input type=checkbox disabled $options{blocked_flag}></td>\n";
print"<td class=$class><input type=checkbox disabled $options{upld_flag}></td>\n";
print"<td class=$class><input type=checkbox disabled $options{del_flag}></td>\n";
print"<td class=$class>",$db_handle->selectrow_array("SELECT (select tcp_out from $users_log where nip=$current_nip order by time desc limit 1)/(1024*$dtime);"),"($dtime)</td>\n";
print "</tr>\n";
print"</table>\n";

print"<br>\n";

print"<table class=main>\n";
$select_string="SELECT distinct hex(hwaddr) from $users_log where nip=$current_nip and hwaddr!=0x0;";
$sth=$db_handle->prepare("$select_string");
$sth->execute();
while(@data=$sth->fetchrow_array())
    {
    $i=0;
    print"<tr>";
    while(defined $data[$i]){print"<td class=$class><font face=monospace>$data[$i]</font></td>";$i++;}
    print"</tr>\n";
    }
$sth->finish;
print"</table>\n";

print"<hr>\n";
%r_month=(
'01'=>"I",
'02'=>"II",
'03'=>"III",
'04'=>"IV",
'05'=>"V",
'06'=>"VI",
'07'=>"VII",
'08'=>"VIII",
'09'=>"IX",
'10'=>"X",
'11'=>"XI",
'12'=>"XII");
@tdclass=("primary","secondary");
    $day=0;
    $state=0;
    print"<table class=$tableclass[$state]>\n";
@data=();
    $select_string="select time,tcp_in,udp_in,icmp_in,rest_in,tcp_out,udp_out,icmp_out,rest_out from $hourly_sum where nip=$current_nip order by time desc limit 17520;";
    $sth=$db_handle->prepare("$select_string");
    $sth->execute();
    while(@data=$sth->fetchrow_array())
    {
	$daychange=0;
        $i=0;
        @datetime=split(' ',$data[0]);
        @date=split('-',$datetime[0]);
        if($date[2]!=$day) {$state=1-$state;$day=$date[2];$daychange=1;}
	if($daychange eq 1) {print"<tr><td colspan=9 align=center class=\"$tdclass[$state]\_spacer\">\n$date[2]\.$r_month{$date[1]}\.$date[0]</td>\n</tr>\n";}
	print"<tr align=right>";
        while(defined $data[$i])
	{
	    if($i==0) {print"<td align=right class=$tdclass[$state]>$datetime[1]</td>";}
	    else {print"<td class=$tdclass[$state]>$data[$i]</td>";}
	    $i++;
	}
	print"</tr>\n";
    }
    print"</table>\n";
print"</table>\n";
$db_handle->do("INSERT INTO $log_table SET time_start=\"$timestamp\",time_stop=CURRENT_TIMESTAMP,users=(SELECT count(*) from $main_table),code='u';");
$db_handle->disconnect;
print"<br>";
$uip=$ENV{REMOTE_ADDR};
print"$uip\n";
print"<br><a href=/cgi-bin/about.htm>About</a>";
print"</center>\n";
print"</body>\n";
print"</html>";