#!/usr/bin/perl

use DBI;
use CGI qw(:standard);

my $script = "/cgi-bin/vpn/vpnadmin.cgi";

my $dbh = DBI->connect("dbi:Pg:dbname=vpn", "vpn") || die "Not connected";
$action = param("action");
$user = param("user");

if($action eq "new_user") {
    show_head();
    print "<H2>Создание пользователя</h2><BR>
    <FORM ACTION='$script' method = 'POST'>
    <INPUT TYPE=HIDDEN name ='action' value='new_user_1'>
    <TABLE>
	<TR>
	    <TD ><B>Логин</B></TD>
	    <TD><INPUT TYPE=TEXT name='login' size=16 maxlength=16></TD>
	    <TD><B>Пароль</B></TD>
	    <TD><INPUT TYPE=TEXT name='passwd' size=16 maxlength=16></TD>
	</TR>
	<TR>
	    <TD><B>Описание</B></TD>
	    <TD><INPUT TYPE=TEXT name='descr' size=40 maxlength=64></TD>
	</TR>
	<TR>
	    <TD><INPUT TYPE = submit VALUE='Создать'></TD>
	</TR>
    </TABLE>
    </FORM>
    ";
    show_foot();
} elsif ($action eq "show_onl") {
    show_head();
    print "В онлайне<BR><HR>";
    $ret = $dbh->selectall_arrayref(
	"SELECT user_name,start,update,tm,inp,out,ip,frm,pid from stat where stop IS NULL");

    print "<TABLE BORDER=1><TR><TH>Login</TH><TH>Start</TH><TH>Update</TH><TH>Sec.</TH><TH>Input</TH>
	   <TH>Output</TH><TH>IP address</TH><TH>From IP</TH><TH>PID</TH></TR>";
    for($i=0;$i<@$ret;$i++) {
	print "<TR><TD>$ret->[$i][0]</TD><TD>$ret->[$i][1]</TD><TD>$ret->[$i][2]</TD>
		   <TD>$ret->[$i][3]</TD><TD>$ret->[$i][4]</TD><TD>$ret->[$i][5]</TD>
		   <TD>$ret->[$i][6]</TD><TD>$ret->[$i][7]</TD><TD>$ret->[$i][8]</TD>
		   <TD>$ret->[$i][9]</TD></TD></TR>";
    }
    print "</TABLE>";
    show_foot();

} elsif ($action eq "new_user_1") {
    $n_login = param("login");$n_passwd = param("passwd");
    $n_descr = param("descr");

    if($n_login ne '' && $n_passwd ne '') {
	my @row = $dbh->selectrow_array("SELECT user_name from users where user_name='$n_login'");
	show_head();
	if(@row > 0 ) { 
	    print "<H1>Пользователь уже заведён</H1>\n";
	} else {
	    $dbh->do("INSERT INTO users(user_name,passwd,descr,active)
	     VALUES('$n_login','$n_passwd','$n_descr',FALSE)");
	    $dbh->do("INSERT INTO users_attribute(user_name,attr,param)
	     VALUES('$n_login','Traffic-Shape','64')");

	    $dbh->do("INSERT INTO users_attribute(user_name,attr,param)
	     VALUES('$n_login','Traffic-Limit','0')");

	    show_user($n_login);
	}
	show_foot();
    } else {
    }
} elsif($action eq "show_sum") {
    show_head();
    my($tmp,$tmp,$tmp,$day,$month,$year,$tmp,$tmp,$tmp) = localtime(time);
    $year+=1900;$month+=1;
    $start_v = param("start_v");
    $stop_v = param("stop_v");

    if($month < 10 ) {$month = "0".$month;}
    if($day <10 ) {$day = "0".$day;}
    $start = "$year-$month-$day";
       print "<FORM ACTION='$script' method = 'POST'>                 
        <INPUT TYPE=HIDDEN name =\"action\" value=\"show_sum\">
        <TABLE BORDER=1><TR>
        <TD>Старт:</TD><TD><INPUT TYPE=TEXT name=\"start_v\"  VALUE = '$start' size=12 maxlength=10></TD>
        <TD>Стоп:</TD><TD><INPUT TYPE=TEXT name=\"stop_v\"  VALUE = '$start' size=12 maxlength=10></TD>
        <TD><INPUT TYPE = submit VALUE=\"Просмотр\"></TD>
        </TR></TABLE></FORM>";
    if($start_v ne "" && $stop_v ne "") {
	$start_v = $start_v." 00:00:00";
	$stop_v = $stop_v." 23:59:59";

    $ret = $dbh->selectall_arrayref(
    "SELECT user_name,sum(tm),sum(inp), sum(out) from stat where stop IS NOT NULL AND start >= '$start_v' AND start <= '$stop_v' group by user_name");
    print "<TABLE BORDER=1><TR><TH>Логин</TH><TH>Длит.</TH><TH>Входящий</TH><TH>Исходящий</TH></TR>";
    $s_i=$s_o=$s_t=0;
    for($i=0;$i<@$ret;$i++) {
	print "<TR><TD>$ret->[$i][0]</TD><TD>$ret->[$i][1]</TD><TD>$ret->[$i][2]</TD>
		   <TD>$ret->[$i][3]</TD></TR>";
	$s_t+=$ret->[$i][1];
	$s_i+=$ret->[$i][2];
	$s_o+=$ret->[$i][3];
    }
    print "<TR><TD></TD><TH>$s_t</TH><TH>$s_i</TH><TH>$s_o</TH></TR>";
    print "</TABLE>";

    }
    show_foot();


} elsif ($action eq "stat") {
    show_head();
    my($tmp,$tmp,$tmp,$day,$month,$year,$tmp,$tmp,$tmp) = localtime(time);
    $year+=1900;$month+=1;
    $start_v = param("start_v");
    $stop_v = param("stop_v");

    if($month < 10 ) {$month = "0".$month;}
    if($day <10 ) {$day = "0".$day;}
    $start = "$year-$month-$day";
       print "<FORM ACTION='$script' method = 'POST'>                 
        <INPUT TYPE=HIDDEN name =\"action\" value=\"stat\">
        <INPUT TYPE=HIDDEN name =\"user\" value=\"$user\">                      
        <TABLE BORDER=1><TR>
        <TD>Старт:</TD><TD><INPUT TYPE=TEXT name=\"start_v\"  VALUE = '$start' size=12 maxlength=10></TD>
        <TD>Стоп:</TD><TD><INPUT TYPE=TEXT name=\"stop_v\"  VALUE = '$start' size=12 maxlength=10></TD>
        <TD><INPUT TYPE = submit VALUE=\"Просмотр\"></TD>
        </TR></TABLE></FORM>";
    if($start_v ne "" && $stop_v ne "") {
	$start_v = $start_v." 00:00:00";
	$stop_v = $stop_v." 23:59:59";

    $ret = $dbh->selectall_arrayref(
    "SELECT start,tm,ip,frm,inp,out from stat where stop IS NOT NULL AND user_name = '$user' AND start >= '$start_v' AND start <= '$stop_v'");
    print "<TABLE BORDER=1><TR><TH>Начало сессии</TH><TH>Длит.</TH><TH>IP адрес</TH>
	   <TH>С IP</TH><TH>Входящий</TH><TH>Исходящий</TH></TR>";
    $s_i=$s_o=0;
    for($i=0;$i<@$ret;$i++) {
	print "<TR><TD>$ret->[$i][0]</TD><TD>$ret->[$i][1]</TD><TD>$ret->[$i][2]</TD>
		   <TD>$ret->[$i][3]</TD><TD>$ret->[$i][4]</TD><TD>$ret->[$i][5]</TD></TR>";
	$s_i+=$ret->[$i][4];
	$s_o+=$ret->[$i][5];
    }
    print "<TR><TD></TD><TD></TD><TD></TD><TD></TD><TH>$s_i</TH><TH>$s_o</TH></TR>";
    print "</TABLE>";

    }
    show_foot();
} elsif ($action eq "chg_a") {
    $dbh->do("UPDATE users SET active = NOT active WHERE user_name='$user'");
    show_head();
    show_user($user);
    #show_attr($user);
    show_foot();
} elsif ($action eq "addmb") {
    $mb = param("data");
    @row = $dbh->selectrow_array("SELECT id,param from users_attribute WHERE user_name='$user' AND attr='Traffic-Limit'");
    $mb = $row[1]+$mb;
    $dbh->do("UPDATE users_attribute SET param='$mb' WHERE id=$row[0]");
    show_head();
    show_user($user);
    #show_attr($user);
    show_foot();
} elsif ($action eq "view") {
    show_head();
    show_user($user);
    #show_attr($user);
    show_foot();
} elsif ($action eq "chdescr"){
    $descr = param("attr");
    show_head();
    if($descr eq '' ) {
	show_user($user);
	print "<FORM ACTION='$script' method = 'POST'>
	<INPUT TYPE=HIDDEN name =\"action\" value=\"chdescr\">
	<INPUT TYPE=HIDDEN name =\"user\" value=\"$user\">
	<TABLE BORDER=1><TR><TD>Изменить описание</TD>
	<TD><INPUT TYPE=TEXT name=\"attr\"  size=16 maxlength=64></TD>
	<TD><INPUT TYPE = submit VALUE=\"Изменить\"></TD>
	</TR></TABLE></FORM>";
    } else {
	$dbh->do("UPDATE users SET descr = '$descr' WHERE user_name='$user'");
	show_user($user);
    }
} elsif ($action eq "chpass"){
    $pwd = param("attr");
    show_head();
    if($pwd eq '' ) {
	show_user($user);
	print "<FORM ACTION='$script' method = 'POST'>
	<INPUT TYPE=HIDDEN name =\"action\" value=\"chpass\">
	<INPUT TYPE=HIDDEN name =\"user\" value=\"$user\">
	<TABLE BORDER=1><TR><TD>Новый пароль</TD>
	<TD><INPUT TYPE=TEXT name=\"attr\"  size=16 maxlength=16></TD>
	<TD><INPUT TYPE = submit VALUE=\"Изменить\"></TD>
	</TR></TABLE></FORM>";
    } else {
	$dbh->do("UPDATE users SET passwd = '$pwd' WHERE user_name='$user'");
	show_user($user);
    }
} elsif ($action eq "edit") {
    $attr = param("attr");
    $indx=param("ind");
#    $ind=param("ind");
    if($attr eq '') {
	show_head();show_user($user);
	my @row = $dbh->selectrow_array(
	    "SELECT attr,param FROM users_attribute WHERE id=$indx");
	print "$indx";
	print "<FORM ACTION='$script' method = 'POST'>
	<INPUT TYPE=HIDDEN name =\"action\" value=\"edit\">
	<INPUT TYPE=HIDDEN name =\"user\" value=\"$user\">
	<INPUT TYPE=HIDDEN name =\"ind\" value=\"$indx\">
	<TABLE BORDER=1><TR><TD>$row[0]</TD>
	<TD><INPUT TYPE=TEXT name=\"attr\" VALUE='$row[1]' size=16 maxlength=16></TD>
	<TD><INPUT TYPE = submit VALUE=\"Изменить\"></TD></TR></TABLE></FORM>";
    show_foot();
    } else {
    $dbh->do("UPDATE users_attribute SET param='$attr' WHERE user_name='$user' AND id = $indx");
    show_head();
    show_user($user);
    show_foot();

    }
}else {
    show_head();
    show_foot();
}
exit;

sub show_user {
    my($login) = @_;
    print "<TABLE BORDER=1><TR valign=top><TD>";
    show_user_p($login);print "</TD><TD>";show_tr_ed($login);print "</TD>";
    print "<TD>";show_attr($login);print"</TD>";
    print "</TR></TABLE>";
};

sub show_user_p {
    my($login) = @_;
    my @row = $dbh->selectrow_array(
	"SELECT passwd,descr,active from users where user_name='$login'");
    if($row[2] eq '0' ) {
	$cha = "<TD BGCOLOR='#FA0000'>Неактивен</TD>";
    } else { $cha = "<TD BGCOLOR='#99FF99'>Активен</TD>"; }

    print "<TABLE BORDER=1>
    <TR>
	<TD BGCOLOR='#CCCCCC'>Логин:</TD><TD BGCOLOR='#FFFFFF'>$login</TD>
    </TR>
    <TR>
	<TD BGCOLOR='#CCCCCC'>Пароль:</TD><TD BGCOLOR='#FFFFFF'>$row[0]</TD>
	<TD><A HREF='$script?action=chpass&user=$login'>Изменить</A></TD>
    </TR>
    <TR>
	<TD BGCOLOR='#CCCCCC'>Описание:</TD><TD BGCOLOR='#FFFFFF'>$row[1]</TD>
	<TD><A HREF='$script?action=chdescr&user=$login'>Изменить</A></TD>
    </TR>
    <TR><TD BGCOLOR='#CCCCCC'>Состояние</TD>$cha<TD><A HREF='$script?action=chg_a&user=$login'>Изменить<A/></TD></TR></TABLE>";
};
sub show_tr_ed {
    my ($login) = @_;
    print "Трафик:<TABLE frame='border'>
	<TR>
	<TD><A HREF='$script?action=addmb&user=$login&data=1000000'>+1Mb</A></TD>
	<TD><A HREF='$script?action=addmb&user=$login&data=5000000'>+5Mb</A></TD>
	<TD><A HREF='$script?action=addmb&user=$login&data=10000000'>+10Mb</A></TD>
	<TD><A HREF='$script?action=addmb&user=$login&data=20000000'>+20Mb</A></TD>
	<TD><A HREF='$script?action=addmb&user=$login&data=50000000'>+50Mb</A></TD>
	<TD><A HREF='$script?action=addmb&user=$login&data=100000000'>+100Mb</A></TD>
	</TR>
	<TR>
	<TD><A HREF='$script?action=addmb&user=$login&data=-1000000'>-1Mb</A></TD>
	<TD><A HREF='$script?action=addmb&user=$login&data=-5000000'>-5Mb</A></TD>
	<TD><A HREF='$script?action=addmb&user=$login&data=-10000000'>-10Mb</A></TD>
	<TD><A HREF='$script?action=addmb&user=$login&data=-20000000'>-20Mb</A></TD>
	<TD><A HREF='$script?action=addmb&user=$login&data=-50000000'>-50Mb</A></TD>
	<TD><A HREF='$script?action=addmb&user=$login&data=-100000000'>-100Mb</A></TD>
	</TR>
	<TR><TD><HR><A HREF='$script?action=stat&user=$login'>Статистика</A><TD>
	</TR>
    </TABLE>";
}

sub show_attr {
    my ($login) = @_;
    $ret = $dbh->selectall_arrayref(
	"SELECT id,attr,param from users_attribute where user_name = '$login' AND op IS NULL ORDER BY attr");
    print "Атрибуты:<br><TABLE>";
    for($i=0;$i<@$ret;$i++) {
	$ind = $ret->[$i][0];$attr = $ret->[$i][1];$param=$ret->[$i][2];
	print "<TR><TD><A HREF='$script?action=edit&user=$login&ind=$ind'>$attr</A></TD><TD>$param</TD></TR>";
    }
    print "</TABLE>";
};

sub show_head {
$ret = $dbh->selectall_arrayref("SELECT user_name from users order by user_name");
print "Content-type: text/html; charset=koi8-r\n\n<html>\n
<head><LINK REL='stylesheet' TYPE='text/css' HREF='/main.css'> 
<META HTTP-EQUIV='Cache-Control' content='no-cache'>\n
<META HTTP-EQUIV='Pragma' CONTENT='no-cache'></head>\n

<TABLE><TR>
<TD><A HREF='$script?action=new_user'>Создать</A></TD>
<TD><A HREF='$script?action=show_onl'>Онлайн</A></TD>
<TD><A HREF='$script?action=show_sum'>Суммарная</A></TD>
</TR></TABLE>
<HR>
<TABLE><TR valign='top'><TD><TABLE BORDER=0>";
for($i=0;$i<@$ret;$i++) {
    $id = $ret->[$i][0];$lm = $ret->[$i][1];
    print "<TR><TD><A HREF='$script?action=view&user=$id'>$id</A></TD></TR>";}
print "</TABLE></TD><TD>";
};

sub show_foot {
    $dbh->disconnect;
    print "</TD></TR></TABLE></BODY></HTML>";
};