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

#conf open_read
$conf_name="billing.conf";
$ctest=conf->var_read($conf_name);
$main_table=$ctest->{'mysql_connect.db_table'};

%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);
%options=();
@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);
$select_string=join(',',sort {$rows2select{$a}<=>$rows2select{$b}} keys %rows2select);
#HTML header
print "Content-type: text/html;charset=cp-1251;\n\n";
print "<html><HEAD><TITLE>AutumnNet stat</TITLE></HEAD>\n";
print "<body bgcolor=purple><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'});

$uip=$ENV{REMOTE_ADDR};
$admin=0;
for $temp (split('=',$ctest->{'admins.security'})) {if ($temp eq $uip) {$admin=1;}}

if($admin)
{
#Add_user form
print "<form style=\"margin: 0\" action=\"/cgi-bin/edit.pl\" method=GET>
<input type=text name=ip value=\"0.0.0.0\">
<input type=submit name=add value=add></form>\n";
print"<hr>";
#Main table
print"<table border=1 cellspacing=0 cellpading=0>\n";
$select_string="select $select_string from $main_table order by id asc;";
$sth=$db_handle->prepare("$select_string");
$sth->execute();
while(@data=$sth->fetchrow_array())
{
%input_options=(
ip=>			"size=7",
dnld_lim=>		"size=3",
upld_lim=>		"size=3",
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");
$current_nip=$data[$rows2select{nip}];
$ip=$db_handle->selectrow_array("SELECT inet_ntoa($current_nip);");
@flags_array=();
@counts_array=();
@flags_array=$ctest->get_binary($data[$rows2select{flags}]);
@counts_array=$ctest->get_binary($data[$rows2select{counts}]);
%options={};
foreach $key(@counts){if($counts_array[$ctest->{"counts.$key"}]) {$options{$key}=" checked";} else {$options{$key}="";}}
foreach $key(@flags){if($flags_array[$ctest->{"flags.$key"}]) {$options{$key}=" checked";} else {$options{$key}="";}}
if($flags_array[$ctest->{'flags.del_flag'}])
{
foreach $key(keys %options){$options{$key}="$options{$key} disabled";};
foreach $key(keys %input_options){$input_options{$key}="$input_options{$key} disabled";}
}
print "<tr><form style=\"margin: 0\" action=\"/cgi-bin/edit.pl\" method=GET>";
print"<td rowspan=2>";
print"($data[$rows2select{id}])";
print"</td>\n";
print"<td><input type=text name=ip value=$ip disabled $input_options{'ip'}><input type=hidden name=nip value=$data[$rows2select{nip}]></td>\n";
print"<td><input type=checkbox name=log_flag value=1 $options{log_flag}></td>\n";
print"<td><input type=text name=dnld_lim value=$data[$rows2select{dnld_lim}] $input_options{dnld_lim}></td>\n";
print"<td rowspan=2><input type=text name=ed value=\"$data[$rows2select{ed}]\" $input_options{ed}></td>\n";
for $temp(@in)
{
    print"<td><input type=checkbox name=\"$temp\_count\" value=1 $options{\"$temp\_count\"}></td>\n";
    print"<td><input type=text name=$temp value=$data[$rows2select{$temp}] $input_options{$temp}> </td>\n";
}
print"<td>",$db_handle->selectrow_array("SELECT cast((SUM(tcp_in+udp_in+icmp_in)/(1024*1024)) as DECIMAL(12,3)) FROM users where nip=$current_nip;"),"</td>\n";
print"<td><input type=text name=in_lim value=$data[$rows2select{in_lim}] $input_options{in_lim}></td>\n";
print"\n</tr>\n<tr>\n";

if(!$flags_array[$ctest->{'flags.del_flag'}]){print"<td align=center colspan=2><input type=submit name=ok value=ok><input type=submit name=del value=del></td>\n";}
else{print"<td colspan=2>&nbsp;</td>";}
print"<td><input type=text name=upld_lim value=$data[$rows2select{upld_lim}] $input_options{upld_lim}></td>\n";
for $temp(@out)
{
    print"<td><input type=checkbox name=\"$temp\_count\" value=1 $options{\"$temp\_count\"}></td>\n";
    print"<td><input type=text name=$temp value=$data[$rows2select{$temp}] $input_options{$temp}></td>\n";
}
print"<td>",$db_handle->selectrow_array("SELECT cast((SUM(tcp_out+udp_out+icmp_out)/(1024*1024)) as decimal(12,3)) FROM users where nip=$current_nip;"),"</td>\n";
print"<td><input type=text name=out_lim value=$data[$rows2select{out_lim}] $input_options{out_lim}></td>\n";
print "</form></tr>\n";
}
print"</table><hr>\n";
print"<a href=\"ulog.pl\">More stats</a><br>";
$sth->finish;
}
else
{
$current_nip=$db_handle->selectrow_array("SELECT inet_aton('$uip');");
$ip=$uip;
$select_string="select $select_string from $main_table where nip=$current_nip;";
print"$current_nip - $uip - $select_string<br>";
$sth=$db_handle->prepare("$select_string");
$sth->execute();
@data=$sth->fetchrow_array();
print"<table border=1 cellspacing=0 cellpading=0>\n";
print"<hr>Confused?<hr>";
$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);");
foreach $key(keys %options)
{if($data[$row2select{$key}]) {$options{$key}=" checked";} else {$options{$key}=""};}
print "<tr><form style=\"margin: 0\" action=\"/cgi-bin/edit.pl\" method=GET>";
print"<td rowspan=2>";
print $db_handle->selectrow_array("SELECT time FROM users_log where (nip=$current_nip AND mac not like 0x0) ORDER by time DESC LIMIT 1;"),"<br>";
print"($data[$rows2select{id}]); ";
print $db_handle->selectrow_array("SELECT hex(mac) FROM users_log where nip=$current_nip ORDER by time DESC LIMIT 1;");
print";</td>\n";
print"<td><input	type=text		name=ip value=$ip disabled $input_options{'ip'}><input type=hidden name=nip value=$data[$rows2select{nip}]></td>\n";
print"<td><input	type=checkbox		name=log_flag		value=1					$options{log_flag}>		</td>\n";
print"<td><input	type=text		name=dnld_lim		value=$data[$rows2select{dnld_lim}]	$input_options{dnld_lim}>		</td>\n";
print"<td><input	type=checkbox		name=tcp_in_count	value=1					$options{tcp_in_count}>		</td>\n";
print"<td><input	type=text		name=tcp_in		value=$data[$rows2select{tcp_in}]	$input_options{tcp_in}>		</td>\n";
print"<td><input	type=checkbox		name=udp_in_count	value=1					$options{udp_in_count}>		</td>\n";
print"<td><input	type=text		name=udp_in		value=$data[$rows2select{udp_in}]	$input_options{udp_in}>		</td>\n";
print"<td><input	type=checkbox		name=icmp_in_count	value=1					$options{icmp_in_count}>	</td>\n";
print"<td><input	type=text		name=icmp_in		value=$data[$rows2select{icmp_in}]	$input_options{icmp_in}>		</td>\n";
print"<td>",$db_handle->selectrow_array("SELECT SUM(tcp_in+udp_in+icmp_in) FROM users where nip=$current_nip;"),"</td>\n";
print"<td><input	type=text		name=in_lim		value=$data[$rows2select{in_lim}]	$input_options{in_lim}>		</td>\n";
print"<td><input	type=checkbox		name=add_flag		value=1 disabled			$options{add_flag}>		</td>\n";
print"<td>",$db_handle->selectrow_array("SELECT (select tcp_in from users_log where nip=$current_nip order by time desc limit 1)/(1024*$dtime);"),"</td>\n";
print"\n</tr>\n<tr>\n";

print"<td align=center colspan=2><input type=submit name=ok value=ok><input type=submit name=del value=del>				</td>\n";
print"<td><input	type=text		name=upld_lim		value=$data[$rows2select{upld_lim}]	$input_options{upld_lim}>		</td>\n";
print"<td><input	type=checkbox		name=tcp_out_count	value=1					$options{tcp_out_count}>	</td>\n";
print"<td><input	type=text		name=tcp_out		value=$data[$rows2select{tcp_out}]	$input_options{tcp_out}>		</td>\n";
print"<td><input	type=checkbox		name=udp_out_count	value=1					$options{udp_out_count}>	</td>\n";
print"<td><input	type=text		name=udp_out		value=$data[$rows2select{udp_out}]	$input_options{udp_out}>		</td>\n";
print"<td><input	type=checkbox		name=icmp_out_count	value=1					$options{icmp_out_count}>	</td>\n";
print"<td><input	type=text		name=icmp_out		value=$data[$rows2select{icmp_out}]	$input_options{icmp_out}>		</td>\n";
print"<td>",$db_handle->selectrow_array("SELECT SUM(tcp_out+udp_out+icmp_out) FROM users where nip=$current_nip;"),"</td>\n";
print"<td><input	type=text		name=out_lim		value=$data[$rows2select{out_lim}]	$input_options{out_lim}>		</td>\n";
print"<td><input	type=checkbox		name=del_flag		value=1 disabled			$options{del_flag}>		</td>\n";
print"<td>",$db_handle->selectrow_array("SELECT (select tcp_out from users_log where nip=$current_nip order by time desc limit 1)/(1024*$dtime);"),"</td>\n";
print "</form></tr>\n";
print"</table><hr>\n";
}
$db_handle->disconnect;
print"$uip";
print "</body></html>";