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

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

@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);
$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>Tech stat</title>";
print"<style>";
print"body,input,td{font-family: monospace}";
print"hr {width: 80%;}";
print"td.one{border: 1px solid #ffffff; padding: 2px; background-color: #ff00ff;}";
print"td.two{border-bottom: 1px solid #000000; border-right: 1px solid #000000; color: #ffffff; padding: 2px; background-color: purple;}";
print"</style>";
print"</head>\n";
print"<body bgcolor=#a0a0a0>\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'});
$timestamp=$db_handle->selectrow_array("SELECT now();");
$current_nip=$db_handle->selectrow_array("SELECT inet_aton('$uip');");
print"Server time: ",$db_handle->selectrow_array("SELECT now();"),"<br>\n";
print"<hr>";
print"Show table status:\n";
@data=();
$select_string="show table status;";
print"<table>\n";
$sth=$db_handle->prepare("$select_string");
$sth->execute();
while(@data=$sth->fetchrow_array())
    {
    print"<tr>";
    $i=0;
    while($i<15) {print"<td class=two>$data[$i]&nbsp;</td>";$i++;}
    print"</tr>\n";
    }
print"</table>\n";
$sth->finish();

#print"<hr>\n";
print"<br>\n";
print"Current total users:",$db_handle->selectrow_array("SELECT count(*) from $main_table;"),"<br>\n";
#print"<br>\n";
print"<table>\n";
print"<tr>";
print"<td class=one>Table name:</td>";
print"<td class=one>Total:</td>";
print"<td class=one>First entry:</td>";
print"<td class=one>Last entry:</td>";
print"</tr>\n";
print"<tr>";
print"<td class=one>log_user</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT count(*) from $users_log;"),"</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT time from $users_log order by time asc limit 1;"),"</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT time from $users_log order by time desc limit 1;"),"</td>";
print"</tr>\n";
print"<tr>";
print"<td class=one>log_hour</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT count(*) from $hourly_sum;"),"</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT time from $hourly_sum order by time asc limit 1;"),"</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT time from $hourly_sum order by time desc limit 1;"),"</td>";
print"</tr>\n";
print"<tr>";
print"<td class=one>log_edit</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT count(*) from $edit_log;"),"</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT time from $edit_log order by time asc limit 1;"),"</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT time from $edit_log order by time desc limit 1;"),"</td>";
print"</tr>\n";
print"<tr>";
print"<td class=one>log_slow</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT count(*) from $script_log;"),"</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT time_start from $script_log order by time_start asc limit 1;"),"</td>";
print"<td class=one>",$db_handle->selectrow_array("SELECT time_stop from $script_log order by time_stop desc limit 1;"),"</td>";
print"</tr>\n";
print"</table>\n";
print"<br>\n";
print"Log_slow advanced:\n";
print"<table><tr>";
print"<td class=two>\n";
print"dTime average:",$db_handle->selectrow_array("select avg(timediff(time_stop,time_start)) from $script_log where code='h';"),"<br>";
#print"dTime average:",$db_handle->selectrow_array("select avg((time_to_sec(time_stop)-time_to_sec(time_start))) from $script_log where code='h';"),"<br>";
#print"dTime last:",$db_handle->selectrow_array("select (time_to_sec(time_stop)-time_to_sec(time_start)) from $script_log where code='h' order by time_start desc limit 1;"),"<br>";
print"dTime last:",$db_handle->selectrow_array("select timediff(time_stop,time_start) from $script_log where code='h' order by time_start desc limit 1;"),"<br>";
print"h total:",$db_handle->selectrow_array("select count(*) from $script_log where code='h';"),"<br>";
print"</td>";
print"<td class=two>";
print"dTime average:",$db_handle->selectrow_array("select avg(timediff(time_stop,time_start)) from $script_log where code='c';"),"<br>";
print"dTime last:",$db_handle->selectrow_array("select timediff(time_stop,time_start) from $script_log where code='c' order by time_start desc limit 1;"),"<br>";
print"c total:",$db_handle->selectrow_array("select count(*) from $script_log where code='c';"),"<br>";
print"</td>";
print"<td class=two>\n";
print"dTime average:",$db_handle->selectrow_array("select avg(timediff(time_stop,time_start)) from $script_log where code='u';"),"<br>";
print"dTime last:",$db_handle->selectrow_array("select timediff(time_stop,time_start) from $script_log where code='u' order by time_start desc limit 1;"),"<br>";
print"u total:",$db_handle->selectrow_array("select count(*) from $script_log where code='u';"),"<br>";
print"</td>";
print"<td class=two>\n";
print"dTime average:",$db_handle->selectrow_array("select avg(timediff(time_stop,time_start)) from $script_log where code='t';"),"<br>";
print"dTime last:",$db_handle->selectrow_array("select timediff(time_stop,time_start) from $script_log where code='t' order by time_start desc limit 1;"),"<br>";
print"t total:",$db_handle->selectrow_array("select count(*) from $script_log where code='t';"),"<br>";
print"</td>";
print"</tr></table>\n";

print"<hr>";

@tdclass=("one","two");
%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");
    $day=0;
    $state=0;
    print"<table class=main>\n";
    @data=();
    $select_string="select * from $edit_log 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=100 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";
$sth->finish();
$db_handle->do("INSERT INTO $script_log SET time_start=\"$timestamp\",time_stop=CURRENT_TIMESTAMP,users=(SELECT count(*) from $main_table),code='t';");
$db_handle->disconnect;
print"<hr>$uip<br><br>\n";
print"</center>\n";
print"</body>\n";
print"</html>\n";