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

$do_slow=0;
$nhours=8;
if(param())
{
    $do_slow=param("do_slow");
    $nhours=param("nhours");
    $current_nip=param(nip);
}
if(!defined $do_slow){$do_slow=0};
if(!defined $nhours){$nhours=8;}
$start="date_sub(now(),interval $nhours hour)";
$nhours-=8;$end="date_sub(now(),interval $nhours hour)";$nhours+=8;

#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'};
$arch_log=$ctest->{'mysql_tables.arch'};
$css_path=$ctest->{'environment.css_path'};
#var init
%input_options=(
rest_in=>		"size=11",
rest_out=>		"size=11");
%rows2select=(
id=>			0,
nip=>			1,
flags=>			2,
counts=>		3,
rest_in=>		4,
rest_out=>		5);
%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");
@flags=(log_flag,add_flag,del_flag,dnld_flag,upld_flag,blocked_flag);
@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,dnld_flag,upld_flag);
@hidden=(dnld_lim,upld_lim,tcp_in,tcp_out,udp_in,udp_out,icmp_in,icmp_out,log_flag);
%options=();
%checkbox_options=();
@flags_array=();
@counts_array=();
@dateteime_last=();
@date_last=();
@data=();
$rows=0;
$class='two';

$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=1;
$time1=$db_handle->selectrow_array("select time_to_sec(time) from $users_log where nip=$current_nip order by time desc limit 1");
$time2=$db_handle->selectrow_array("select time_to_sec(time) from $users_log where nip=$current_nip order by time desc limit 1,1;");
if((defined $time1) and (defined $time2)){$dtime=$time1-$time2;}
$select_string="select time,tcp_in,udp_in,icmp_in,rest_in,tcp_out,udp_out,icmp_out,rest_out from $hourly_sum where (time between $start and $end and nip=$current_nip) order by time desc;";
$sth=$db_handle->prepare("$select_string");
$sth->execute();

#HTML
print"Content-type: text/html;charset=cp-1251;\n\n";
print"<html><head><title>$ip advanced user stat</TITLE><link href=\"$css_path\" rel=\"stylesheet\" type=\"text/css\">\n</head>\n";
print"<body>";
print"<TABLE width=100% height=100%><TR><TD align=center vlaign=center>\n";
print"<table border=1 width=95%><tr><td valign=top align=center width=49%>";

$rows=0;
print"<table border=0 width=100%>\n";
    while(@data=$sth->fetchrow_array())
    {
    $i=0;
    @datetime=split(' ',$data[0]);@date=split('-',$datetime[0]);
    if($rows==0){@datetime_last=split(' ',$data[0]);@date_last=split('-',$datetime[0]);}
    print"<tr align=right>";
    while(defined $data[$i])
        {
        if($i==0) {print"<td align=left class=$class width=13%>$datetime[1]</td>";}
        elsif(($i==4)||($i==7)){if($class eq 'one'){$class='two';} else{$class='one'}}
        elsif($data[$i]!=0) {print"<td class=$class width=13%>$data[$i]</td>";}
        else {print"<td class=$class width=13%>&nbsp</td>";}
        $i++;
        if($class eq 'one'){$class='two';} else{$class='one'}
        }
    print"</tr>\n";$rows++;
    if($rows%16==0){print"</table></td>";if($rows!=32){print"<td width=2%></td><td align=center width=49%><table border=0 width=100%>";$class='two';}else{print"</tr>";}}
    }
if($rows!=32){if($rows==0){print"<tr><td align=center>keine_data&nbsp;</td></tr>";}else{print"</table></td></tr>";}}
print"</table>";

print"<table border=0><tr><td colspan=3 align=left>$date_last[2]\.$r_month{$date_last[1]}\.$date_last[0]</td><td>";
$nhours-=720;if($nhours<=0){print" [m<<] ";}else{print" <a href=/cgi-bin/ustat.pl?nip=$current_nip&nhours=$nhours>[m<<]</a> ";}$nhours+=720;
$nhours-=168;if($nhours<=0){print" [w<<] ";}else{print" <a href=/cgi-bin/ustat.pl?nip=$current_nip&nhours=$nhours>[w<<]</a> ";}$nhours+=168;
$nhours-=24;if($nhours<=0){print" [d<<] ";}else{print" <a href=/cgi-bin/ustat.pl?nip=$current_nip&nhours=$nhours>[d<<]</a> ";}$nhours+=24;
$nhours-=8;if($nhours<=0){print" [/<<] ";}else{print" <a href=/cgi-bin/ustat.pl?nip=$current_nip&nhours=$nhours>[/<<]</a> ";}$nhours+=8;
$nhours-=8;print"$nhours";$nhours+=8;
print"...$nhours";
$nhours+=8;print" <a href=/cgi-bin/ustat.pl?nip=$current_nip&nhours=$nhours>[/>>]</a> ";$nhours-=8;
$nhours+=24;print" <a href=/cgi-bin/ustat.pl?nip=$current_nip&nhours=$nhours>[d>>]</a> ";$nhours-=24;
$nhours+=168;print" <a href=/cgi-bin/ustat.pl?nip=$current_nip&nhours=$nhours>[w>>]</a> ";$nhours-=168;
$nhours+=720;print" <a href=/cgi-bin/ustat.pl?nip=$current_nip&nhours=$nhours>[m>>]</a> ";$nhours-=720;
print"</td></tr>\n";
print"</table>\n";

$select_string=join(',',sort {$rows2select{$a}<=>$rows2select{$b}} keys %rows2select);
$select_string="select $select_string from $main_table where nip=$current_nip;";
@data=$db_handle->selectrow_array("$select_string");
$current_nip=$data[$rows2select{nip}];
$ip=$db_handle->selectrow_array("SELECT inet_ntoa($current_nip);");
#Form&counts
@counts_array=$ctest->get_binary($data[$rows2select{counts}]);
@flags_array=$ctest->get_binary($data[$rows2select{flags}]);
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";}
    }
#Flags&info
foreach $key(@flags){if($flags_array[$ctest->{"flags.$key"}]) {$options{$key}=" checked";} else {$options{$key}="";}}

print"<TABLE border=0><TR><TD valign=top>";
    print"<table border=0 class=main>\n";
    print"<tr><td>&nbsp;</td><td>&nbsp;</td><td>l</td><td>d</td><td>a</td><td>&nbsp;</td></tr>";
    print"<tr>";
    print"<td class=one>$ip (id: $data[$rows2select{id}])</td>\n";
    print"<td class=one>",$db_handle->selectrow_array("SELECT hwaddr FROM $users_log where nip=$current_nip ORDER by time DESC LIMIT 1;"),"</td>\n";
    print"<td class=one><input type=checkbox disabled $options{log_flag}></td>\n";
    print"<td class=one><input type=checkbox disabled $options{'dnld_flag'}></td>\n";
    print"<td class=one><input type=checkbox disabled $options{add_flag}></td>\n";
    print"<td class=one>";
$speed=0;
if($dtime!=1){$speed=$db_handle->selectrow_array("SELECT (select tcp_in from $users_log where nip=$current_nip order by time desc limit 1)/(1024*$dtime);");}
    print"$speed tcp Kbit/s($dtime)</td>\n";
    print"</tr>\n";
    print"<tr>\n";
    if($do_slow)
    {
    print"<td class=one>",$db_handle->selectrow_array("SELECT time FROM $arch_log where (nip=$current_nip AND hwaddr not like '0') ORDER by time DESC LIMIT 1;"),"</td>\n";
    print"<td class=one>\n",$db_handle->selectrow_array("SELECT hwaddr FROM $arch_log where (nip=$current_nip AND hwaddr<>0x0) ORDER by time DESC LIMIT 1;"),"</td>\n";
    }
    else{print"<td class=one></td>";print"<td class=one></td>";}
    print"<td class=one><input type=checkbox disabled $options{blocked_flag}></td>\n";
    print"<td class=one><input type=checkbox disabled $options{upld_flag}></td>\n";
    print"<td class=one><input type=checkbox disabled $options{del_flag}></td>\n";
    print"<td class=one>";
$speed=0;
if($dtime!=1){$speed=$db_handle->selectrow_array("SELECT (select tcp_out from $users_log where nip=$current_nip order by time desc limit 1)/(1024*$dtime);");}
    print"$speed tcp Kbit/s($dtime)</td>\n";
    print "</tr>\n";
    print"<tr><td>&nbsp;</td><td>&nbsp;</td><td>b</td><td>u</td><td>d</td><td>&nbsp;</td></tr>";
    print"</table>\n";
print"</TD><TD valign=top>";
    print"<table border=0 class=main>\n";
    print"<tr align=center><td>&nbsp;</td><td>&nbsp;</td><td>t</td><td>u</td><td>i</td><td>r</td><td>&nbsp;</td></tr>";
    print"<tr>\n";
    print"<td class=one>$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=one><input type=text name=$temp value=$data[$rows2select{$temp}] $input_options{$temp}></td>\n";
    for $temp(@in) {print"<td class=one><input type=checkbox name=\"$temp\_count\" value=1 $checkbox_options{\"$temp\_count\"}></td>\n";}
    print"<td>in</td>";
    print"</tr>\n";
    print"<tr>\n";
    print"<td class=one align=center>";
    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=one><input type=text name=$temp value=$data[$rows2select{$temp}] $input_options{$temp}>\n";
    print"</td>\n";
    for $temp(@out) {print"<td class=one><input type=checkbox name=\"$temp\_count\" value=1 $checkbox_options{\"$temp\_count\"}></td>\n";}
    print "</form>\n";
    print"<td>out</td>";
    print"</tr>\n";
    print"</table>";
print"</TD><TD valign=top>&nbsp;";
#Client's hwaddrs
    if($do_slow)
    {
    print"<table border=0 class=main>\n";
    $select_string="SELECT distinct hwaddr from $arch_log where nip=$current_nip and hwaddr!='0';";
    $sth=$db_handle->prepare("$select_string");
    $sth->execute();
    while(@data=$sth->fetchrow_array())
        {
        $i=0;
        print"<tr>";
        while(defined $data[$i]){print"<td class=one><font face=monospace>$data[$i]</font></td>";$i++;}
        print"</tr>\n";
        }
    $sth->finish;
    print"</table>\n";
    }
print"</TD></TR></TABLE>";

if($do_slow==0){print"<a href=/cgi-bin/ustat.pl?nip=$current_nip&nhours=$nhours&do_slow=1>Show more info (may be slow)</a>";}
else {print"<a href=/cgi-bin/ustat.pl?nip=$current_nip&nhours=$nhours&do_slow=0>Less info (faster)</a>";}

if($do_slow==0)
{$db_handle->do("INSERT INTO $log_table SET time_start=\"$timestamp\",time_stop=CURRENT_TIMESTAMP,users=(SELECT count(*) from $main_table),code='u';");}
else
{$db_handle->do("INSERT INTO $log_table SET time_start=\"$timestamp\",time_stop=CURRENT_TIMESTAMP,users=(SELECT count(*) from $main_table),code='a';");}
$db_handle->disconnect;
print"</TD></TR></TABLE>\n";
print"</body>\n";
print"</html>";