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

#conf open_read
$conf_name="billing.conf";
$ctest=conf->var_read($conf_name);
$main_table=$ctest->{'mysql_tables.data'};
$hourly_sum=$ctest->{'mysql_tables.hour'};

$ndays=7;
if(param()) {$ndays=param("ndays");}
$start="date_sub(now(),interval $ndays day)";
$ndays-=7;$end="date_sub(now(),interval $ndays day)";$ndays+=7;

%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);
%input_options=(
ip=>			"size=7",
dnld_lim=>		"size=4",
upld_lim=>		"size=4",
tcp_in=>		"size=11",
udp_in=>		"size=9",
icmp_in=>		"size=7",
rest_in=>		"size=7",
tcp_out=>		"size=11",
udp_out=>		"size=9",
icmp_out=>		"size=7",
rest_out=>		"size=7",
in_lim=>		"size=11",
out_lim=>		"size=11",
ed=>			"size=19");
%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");
@data=();
@flags_array=();
@counts_array=();
@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);
@tdclass=("one","two");
$state=1;
$select_string=join(',',sort {$rows2select{$a}<=>$rows2select{$b}} keys %rows2select);
$uip=$ENV{REMOTE_ADDR};
#HTML header
print"Content-type: text/html;charset=cp-1251;\n\n";
print"<html><head><title>$uip user stat</title>\n";
print"<style>\n";
print"body,input,td{font-family: monospace;}\n";
print"body {background-color: #a0a0a0;}\n";
print"hr {width: 80%;}\n";
print"table {border-collapse: separate;}\n";
print"input {text-align: right;}\n";

print"td.one{border: 1px solid #ffffff; color: #000000; padding: 2px; background-color: #ff00ff;}\n";
print"td.two{border-bottom: 1px solid #000000; border-right: 1px solid #000000; color: #ffffff; padding: 2px; background-color: #800080;}\n";
print"td.spacer{color: #222222; padding: 2px;}\n";

print"</style>\n";
print"</head>\n";
print"<body>\n";
#print"$start - $end\n";
print"<center>\n";
$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'});
$current_nip=$db_handle->selectrow_array("SELECT inet_aton('$uip');");
if($db_handle->selectrow_array("SELECT count(*) from $main_table WHERE nip=$current_nip;"))
{
    $select_string="select $select_string from $main_table where nip=$current_nip;";
    @data=$db_handle->selectrow_array("$select_string");
    @flags_array=$ctest->get_binary($data[$rows2select{flags}]);
    @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}="";}}
    foreach $key(keys %input_options){$input_options{$key}="$input_options{$key} disabled";}
    print"<hr>\n";

    print"<table>\n";
    print"<tr>\n";
    print"<td class=one>$uip</td>\n";
    print"<td class=one><input type=text name=dnld_lim value=$data[$rows2select{dnld_lim}] $input_options{dnld_lim}></td>\n";
    for $temp(@in) {print"<td class=one><input type=text name=$temp value=$data[$rows2select{$temp}] $input_options{$temp}></td>\n";}
    print"<td class=one align=right>",$db_handle->selectrow_array("SELECT cast(SUM(tcp_in+udp_in+icmp_in)/(1024*1024) as decimal(12,3)) FROM $main_table where nip=$current_nip;"),"</td>\n";
    print"<td class=one><input type=text name=in_lim value=$data[$rows2select{in_lim}] $input_options{in_lim}></td>\n";
    print"</tr>\n";
    print"<tr>\n";
    print"<td class=two><input type=text name=ed value=\"$data[$rows2select{ed}]\" $input_options{ed}></td>\n";
    print"<td class=two><input type=text name=upld_lim value=$data[$rows2select{upld_lim}] $input_options{upld_lim}></td>\n";
    for $temp(@out) {print"<td class=two><input type=text name=$temp value=$data[$rows2select{$temp}] $input_options{$temp}></td>\n";}
    print"<td class=two align=right>",$db_handle->selectrow_array("SELECT cast(SUM(tcp_out+udp_out+icmp_out)/(1024*1024) as decimal(12,3)) FROM $main_table where nip=$current_nip;"),"</td>\n";
    print"<td class=two><input type=text name=out_lim value=$data[$rows2select{out_lim}] $input_options{out_lim}></td>\n";
    print"</tr>\n";
    print"</table>\n";
    print"<hr>\n";
    $day=0;
    $state=1;
    print"<table>\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 (time between $start and $end and nip=$current_nip) order by time desc;";
$ndays+=7;print"<a href=/cgi-bin/stat.pl?ndays=$ndays><<</a>";$ndays-=7;
print" $ndays";$ndays-=7;print"-$ndays ";$ndays+=7;
if($ndays!=7){$ndays-=7;print"<a href=/cgi-bin/stat.pl?ndays=$ndays>>></a>";$ndays+=7;}
else{print"<a href=/cgi-bin/stat.pl?ndays=$ndays>>></a>";}
print"<hr>";
    $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=\"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>";}
	    elsif($data[$i]!=0) {print"<td class=$tdclass[$state]>$data[$i]</td>";}
	    else {print"<td class=$tdclass[$state]>&nbsp</td>";}
	    $i++;
	}
	print"</tr>\n";
    }
    print"</table>\n";
}
else {print"You're not in the list<br>\n";}
$db_handle->disconnect;
print"<hr>$uip<br><br>\n";
print"</center>\n";
print"</body>\n";
print"</html>\n";