UTM ver. 2.0

#!/usr/bin/perl # revision 23.12.01 aospan # e-mail:aospan@netup.ru # Copyright (c) 2001 NetUP Systems . All rights reserved. # require 5.003; use Time::Local; use CGI::FastTemplate; use DBI(); use CGI; use Env; require "/netup/utm/bin/utm_func.pm"; # INITIALIZE VARIABLES # START INITIALIZATION: VARIABLES AND SOME OTHER ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time); # OBTAIN VARIABLES FROM utm.cfg if (config()) { print "Error occured when utm.cfg processing! Stopped. \n"; exit (1); }; # Connect to the database if ($database_type eq "mysql") { $DBI_data="DBI:$database_type:database=$database;host=$base_host"; } elsif ($database_type eq "Pg") { $DBI_data="DBI:$database_type:dbname=$database"; } else { print "Unknown database $database_type! Stopped. \n"; exit (1); }; $dbh = DBI->connect("$DBI_data","$database_login","$database_password",{'RaiseError' => 1}); $aaa_pl = "aaa"; $users_pl = "users"; $stat_pl = "stat"; $time = time; ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime($time); $month = $mon + 1; $query = new CGI; $cmd = $query->param('cmd'); print "Content-Type: text/html\n\n"; # OBTAIN VARIABLES FROM dict if (dict()) { print "Error occured when dict processing! Stopped. \n"; exit (1); }; # PREPARE templates to USE my $tpl = new CGI::FastTemplate("$template_path"); $tpl->define( users_menu => "users_menu.tpl", users_adduser => "users_adduser.tpl", users_payment => "users_payment.tpl", users_payment_values => "users_payment_values.tpl", users_logs_payment => "users_logs_payment.tpl", users_logs_payment_table => "users_logs_payment_table.tpl", users_logs_payment_row => "users_logs_payment_row.tpl", users_fullview => "users_fullview.tpl", users_fullview_table => "users_fullview_table.tpl", users_fullview_row => "users_fullview_row.tpl", users_firewall => "users_firewall.tpl", users_firewall_table => "users_firewall_table.tpl", users_firewall_row => "users_firewall_row.tpl", users_sessions => "users_sessions.tpl", users_sessions_table => "users_sessions_table.tpl", users_sessions_row => "users_sessions_row.tpl", users_traffic => "users_traffic.tpl", users_traffic_table => "users_traffic_table.tpl", users_traffic_row => "users_traffic_row.tpl", users_delete => "users_delete.tpl", users_change => "users_change.tpl", users_bye => "users_bye.tpl", users_tariffs => "users_tariffs.tpl", ); $tpl->assign(TITLE => "UTM admin:$login"); if ($cmd eq "adduser_form") { adduser_form(); } elsif ($cmd eq "adduser") { adduser(); } elsif ($cmd eq "settings") { settings (); } elsif ($cmd eq "change") { change(); } elsif ($cmd eq "full_view") { full_view(); } elsif ($cmd eq "user_delete") { user_delete(); } elsif ($cmd eq "payment") { payment(); } elsif ($cmd eq "payment_form") { payment_form(); } elsif ($cmd eq "frames") { frames(); } elsif ($cmd eq "display_menu") { display_menu(); } elsif ($cmd eq "sessions") { sessions(); } elsif ($cmd eq "firewall_rules") { firewall_rules(); } elsif ($cmd eq "tariffs") { tariffs(); } elsif ($cmd eq "logs") { logs(); } elsif ($cmd eq "traffic_stat") { traffic_stat(); } elsif ($cmd eq "database_log") { database_log(); } elsif ($cmd eq "user_full_stat") { user_full_stat(); } elsif ($cmd eq "bye") { bye(); } elsif ($cmd eq "bye_message") { bye_message(); } else { print "

Who are you, baby? Who?

"; exit(0); }; sub frames { $s_id = $query->param('sid'); if (sid_verify() ne 0){exit(0)}; print "UTM ver. 2.0"; print ""; print ""; print ""; print ""; print ""; } sub default { $s_id = $query->param('sid'); if (sid_verify() ne 0){exit(0)}; } sub adduser_form { $s_id = $query->param('sid'); if (sid_verify() ne 0){exit(0)}; $p_generated = password_generate(); # TEXT VARIABLE ASSIGN $tpl->assign(ADD_USER_M => "$ADD_USER_M"); $tpl->assign(GEN_PASSWORD_M => "$GEN_PASSWORD_M"); $tpl->assign(LOGIN_M => "$LOGIN_M"); $tpl->assign(PASSWORD_M => "$PASSWORD_M"); $tpl->assign(RET_PASSWORD_M => "$RET_PASSWORD_M"); $tpl->assign(FULL_NAME_M => "$FULL_NAME_M"); $tpl->assign(EMAIL_M => "$EMAIL_M"); $tpl->assign(IP_M => "$IP_M"); $tpl->assign(LOGIN_TYPE_M => "$LOGIN_TYPE_M"); $tpl->assign(PAYMENT_QNT_M => "$PAYMENT_QNT_M"); $tpl->assign(PRICE_M => "$PRICE_M"); $tpl->assign(CREDIT_M => "$CREDIT_M"); $tpl->assign(ADMIN_M => "$ADMIN_M"); $tpl->assign(COMMENTS_M => "$COMMENTS_M"); $tpl->assign(ADD_BUTTON_M => "$ADD_BUTTON_M"); $tpl->assign(SID => "$s_id"); $tpl->assign(USERS_PL => "$users_pl"); $tpl->assign(PASSWORD => "$p_generated"); $tpl->parse(ROWS => ["users_adduser"]); $tpl->print(); }; sub adduser { $s_id = $query->param('sid'); if (sid_verify() ne 0){exit(0)}; my $login = $query->param('login'); my $pas1 = $query->param('password1'); my $pas2 = $query->param('password2'); my $full_name = $query->param('full_name'); my $ip = $query->param('ip'); my $ip_type = $query->param('ip_type'); my $pay = $query->param('pay'); my $price = $query->param('price'); my $credit = $query->param('credit'); my $priv_level = $query->param('priv_level'); my $comments = $query->param('comments'); my $email = $query->param('email'); if ($login eq "" || $pas1 eq "" || $ip eq "") { print "Some field not filled! Go back and try again!"; exit (0)}; # Connect to the database. $ips = ""; $sth = $dbh->prepare("SELECT * FROM users WHERE login=\"$login\" OR ip=\"$ip\""); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { if ($ips ne "") {print "Error! Choose another Login or IP"; exit (0)}; }; $sth->finish(); $crypted_password=crypt($pas1,$pas1); $dbh->do("INSERT INTO users VALUES (NULL, \"$login\",\"$crypted_password\", \"$full_name\", \"$ip\", $pay, $price, $credit, \"0\", \"$comments\", $time, \"$priv_level\",NULL,0,0,0,0,\"$ip_type\",\"$email\",\"$time\",\"$time\",\"0\",\"0\",\"\",\"0\")"); $dbh->do("INSERT INTO bills_history VALUES (\"$login\", $time, $pay, \"$plogin\", \"add user\")"); print "User added successfully
"; print "You can view full table of users. Click here"; $dbh->disconnect(); }; sub full_view { $s_id = $query->param('sid'); if (sid_verify() ne 0){ exit(0) }; $sort_field = $query->param('sort_field'); if ($sort_field eq "") { $sort_field = login; }; $tpl->assign(USERS_PL => "$users_pl"); $tpl->assign(SID => "$s_id"); $tpl->assign(SORT_FIELD => "$sort_field"); $users_number=0; $sth = $dbh->prepare("SELECT * FROM users ORDER BY \"$sort_field\""); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $users_number = $users_number + 1; $norm_reg_date = localtime ($ips->{'reg_date'}); if ($ips->{'priv_level'} ne "0") { push (@admins, $ips->{'login'}) }; $tpl->assign( ID => "$ips->{'id'}", LOGIN => "$ips->{'login'}", FULL_NAME => "$ips->{'full_name'}", IP => "$ips->{'ip'}", BILL => "$ips->{'bill'}", MB_PRICE => "$ips->{'price'}", CREDIT => "$ips->{'credit'}", LOCKED => "$ips->{'block'}", COMMENTS => "$ips->{'comments'}", REG_DATE => "$norm_reg_date", PRIV_LEVEL => "$ips->{'priv_level'}", IP_TYPE => "$ips->{'ip_type'}", EMAIL => "$ips->{'email'}" ); $tpl->parse(ROWS => ".users_fullview_row"); }; $sth->finish(); $dbh->disconnect(); # TEXT VARIABLE ASSIGN $tpl->assign(FULL_USERS_LIST_M => "$FULL_USERS_LIST_M"); $tpl->assign(NUMBER_OF_USERS_M => "$NUMBER_OF_USERS_M"); $tpl->assign(ADMINS_M => "$ADMINS_M"); $tpl->assign(LOGIN_M => "$LOGIN_M"); $tpl->assign(FULL_NAME_M => "$FULL_NAME_M"); $tpl->assign(IP_M => "$IP_M"); $tpl->assign(BALANCE_M => "$BALANCE_M"); $tpl->assign(PRICE_M => "$PRICE_M"); $tpl->assign(CREDIT_M => "$CREDIT_M"); $tpl->assign(LOCKED_M => "$LOCKED_M"); $tpl->assign(COMMENTS_M => "$COMMENTS_M"); $tpl->assign(REG_DATE_M => "$REG_DATE_M"); $tpl->assign(PRIV_LEVEL_M => "$PRIV_LEVEL_M"); $tpl->assign(LOGIN_TYPE_SM => "$LOGIN_TYPE_SM"); $tpl->assign(EMAIL_M => "$EMAIL_M"); $tpl->assign(NUMBER_OF_USERS => "$users_number"); $tpl->assign(ADMINS => "@admins"); $tpl->parse(TABLE => ["users_fullview_table","users_fullview"]); $tpl->print(); exit (0); }; sub payment_form { $s_id = $query->param('sid'); $login = $query->param('login'); if (sid_verify() ne 0){exit(0)}; # GET ALL USERS LIST $sth = $dbh->prepare("SELECT login FROM users ORDER BY login"); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $tpl->assign(LOGIN => "$ips->{'login'}"); $tpl->parse(VALUES => ".users_payment_values"); }; $sth->finish(); # TEXT VARIABLE ASSIGN $tpl->assign(REC_PAYMENT_M => "$REC_PAYMENT_M"); $tpl->assign(LOGIN_M => "$LOGIN_M"); $tpl->assign(PAYMENT_QNT_M => "$PAYMENT_QNT_M"); $tpl->assign(PAY_BUTTON_M => "$PAY_BUTTON_M"); $tpl->assign(SID => "$s_id"); $tpl->assign(USERS_PL => "$users_pl"); $tpl->assign(LOGIN => "$login"); #$tpl->parse(VALUES => ["users_payment_values","users_payment"]); $tpl->parse(VALUES => ["users_payment"]); $tpl->print(); $dbh->disconnect(); }; sub payment { $s_id = $query->param('sid'); if (sid_verify() ne 0){ exit(0) }; my $login = $query->param('login'); my $pay = $query->param('pay'); $dbh->do("UPDATE users SET bill=bill+\"$pay\" WHERE login=\"$login\""); $sth = $dbh->prepare("SELECT * FROM users WHERE login=\"$login\""); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $detail = "$ips->{'id'} $ips->{'ip'} $ips->{'login'} $ips->{'bill'} $ips->{'price'} $ips->{'credit'} $ips->{'block'}"; if ($ips->{'bill'} le -$ips->{'credit'}) { $dbh->do("UPDATE users SET block=\"1\" WHERE login=\"$login\""); print "Account $login still locked
" } else { $dbh->do("UPDATE users SET block=\"0\" WHERE login=\"$login\""); print "Account $login non-locked
"; }; print "$detail
"; $dbh->do("INSERT INTO bills_history VALUES (\"$login\", $time, \"$pay\",\"$plogin\",\"payment\")"); print "Payment successfully received
"; print "Databases updated"; }; $dbh->disconnect(); }; # # VERIFY SID (depend on sessions table) AND RETURN CODE (0 - succsessfull) # sub sid_verify { # CHECK TRUE ADMIN OR JUST DEAD USER? $sth = $dbh->prepare("SELECT * FROM users WHERE ip=\"$REMOTE_ADDR\""); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { if($ips->{'priv_level'} ne 1){ print "Go away, dummy!"; return (1);}; }; $sth->finish(); # CHECK SESSION PARAMETERS $sth = $dbh->prepare("SELECT * FROM sessions WHERE s_id=\"$s_id\" AND ip=\"$REMOTE_ADDR\""); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $plogin = $ips->{'login'}; return (0); }; $sth->finish(); return (1); }; sub display_menu { $s_id = $query->param('sid'); if (sid_verify() ne 0){exit(0)}; # TEXT VARIABLE ASSIGN $tpl->assign(ADD_USER_M => "$ADD_USER_M"); $tpl->assign(REC_PAYMENT_M => "$REC_PAYMENT_M"); $tpl->assign(USERS_LIST_M => "$USERS_LIST_M"); $tpl->assign(EDIT_FIREWALL_RULES_M => "$EDIT_FIREWALL_RULES_M"); $tpl->assign(TARIFFS_M => "$TARIFFS_M"); $tpl->assign(SETTINGS_M => "$SETTINGS_M"); $tpl->assign(DELETE_USER_M => "$DELETE_USER_M"); $tpl->assign(PAYMENTS_REP_M => "$PAYMENTS_REP_M"); $tpl->assign(SESSIONS_REPORT_M => "$SESSIONS_REPORT_M"); $tpl->assign(TR_REPORT_M => "$TR_REPORT_M"); $tpl->assign(EXIT_M => "$EXIT_M"); $tpl->assign(SID => "$s_id"); $tpl->assign(USERS_PL => "$users_pl"); $tpl->parse(ROWS => ["users_menu"]); $tpl->print(); } sub bye { $s_id = $query->param('sid'); if (sid_verify() ne 0){exit(0)}; $dbh->do("UPDATE sessions SET s_id=\"0\" where s_id=\"$s_id\""); $dbh->disconnect(); print " "; }; sub bye_message { $tpl->assign(SESSION_CLOSED_M => "$SESSION_CLOSED_M"); $tpl->assign(LOGIN_TO_UTM => "$LOGIN_TO_UTM"); $tpl->parse(ROWS => ["users_bye"]); $tpl->print(); }; sub user_delete { $s_id = $query->param('sid'); $delete_form = $query->param('delete_form'); $login = $query->param('login'); if (sid_verify() ne 0){ exit(0) }; if ($delete_form eq "no" && $login ne ""){ $dbh->do("DELETE from users where login=\"$login\""); print "Deleted"; exit(0); $sth->finish(); }; # TEXT variable ASSIGN $tpl->assign(DELETE_USER_M => "$DELETE_USER_M"); $tpl->assign(BE_CAREFUL_M => "$BE_CAREFUL_M"); $tpl->assign(LOGIN_M => "$LOGIN_M"); $tpl->assign(DELETE_BUTTON_M => "$DELETE_BUTTON_M"); $tpl->assign(SID => "$s_id"); $tpl->assign(USERS_PL => "$users_pl"); $tpl->parse(ROWS => ["users_delete"]); $tpl->print(); }; sub firewall_rules { $s_id = $query->param('sid'); if (sid_verify() ne 0){exit(0)}; $firewall_cmd = $query->param('firewall_cmd'); $rule_id = $query->param('rule_id'); $login_for_add = $query->param('login_for_add'); $command_for_add = $query->param('command_for_add'); $rule_comment = $query->param('rule_comment'); # # DELETE FIREWALL RULE # if ($firewall_cmd eq "delete") { $dbh->do("DELETE from firewall where id=\"$rule_id\""); $sth->finish(); }; # # ADD FIREWALL RULE # if ($firewall_cmd eq "rule_add") { $dbh->do("INSERT INTO firewall VALUES(\"$rule_id\", \"$login_for_add\", \"$command_for_add\", \"$rule_comment\")"); }; $tpl->assign(ROWS => ""); $sth = $dbh->prepare("SELECT * FROM firewall ORDER BY id"); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $tpl->assign( ID => "$ips->{'id'}", USER => "$ips->{'login'}", FIREWALL_RULE => "$ips->{'command'}", COMMENTS => "$ips->{'comment'}", ); $tpl->parse(ROWS => ".users_firewall_row"); }; $sth->finish(); # TEXT VARIABLE ASSIGN $tpl->assign(FIREWALL_RULES_M => "$FIREWALL_RULES_M"); $tpl->assign(REQ_RULE_DELETE_M => "$REQ_RULE_DELETE_M"); $tpl->assign(DELETE_BUTTON_M => "$DELETE_BUTTON_M"); $tpl->assign(LOGIN_M => "$LOGIN_M"); $tpl->assign(REQ_RULE_M => "$REQ_RULE_M"); $tpl->assign(REQ_RULEID_M => "$REQ_RULEID_M"); $tpl->assign(COMMENTS_M => "$COMMENTS_M"); $tpl->assign(ADD_BUTTON_M => "$ADD_BUTTON_M"); $tpl->assign(FIREWALL_RULE_M => "$FIREWALL_RULE_M"); $tpl->assign(ID_M => "$ID_M"); $tpl->assign(SID => "$s_id"); $tpl->assign(USERS_PL => "$users_pl"); $tpl->parse(TABLE => ["users_firewall_table", "users_firewall"]); $tpl->print(); $dbh->disconnect(); exit(0); }; sub change { $s_id = $query->param('sid'); if (sid_verify() ne 0){ exit(0) }; $login = $query->param('login'); $change_cmd = $query->param('change_cmd'); $r_b_year = $year + 1900; if ($change_cmd eq "form") { $sth = $dbh->prepare("SELECT * FROM users WHERE login=\"$login\""); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $ip = $ips->{'ip'}; $bytes_from = sprintf("%.3f",$ips->{'bytes_from'}/1048576); $bytes_to = sprintf("%.3f",$ips->{'bytes_to'}/1048576); $reg_date = $ips->{'reg_date'}; $norm_reg_date = localtime($reg_date); $tpl->assign(SID => "$s_id"); $tpl->assign(USERS_PL => "$users_pl"); $tpl->assign(LOGIN => "$ips->{'login'}"); $tpl->assign(FULL_NAME => "$ips->{'full_name'}"); $tpl->assign(EMAIL => "$ips->{'email'}"); $tpl->assign(IP => "$ips->{'ip'}"); $tpl->assign(LOGIN_TYPE => "$ips->{'ip_type'}"); $tpl->assign(REG_DATE => "$norm_reg_date"); $tpl->assign(BILL => "$ips->{'bill'}"); $tpl->assign(PRICE => "$ips->{'price'}"); $tpl->assign(CREDIT => "$ips->{'credit'}"); $tpl->assign(BLOCK => "$ips->{'block'}"); $tpl->assign(PRIV_LEVEL => "$ips->{'priv_level'}"); $tpl->assign(COMMENTS => "$ips->{'comments'}"); $tpl->assign(SYS_MESSAGE => "$ips->{'sys_message'}"); $tpl->assign(IP_OLD => "$ips->{'ip'}"); }; $sth->finish(); $bytes_sum = $bytes_to + $bytes_from; $tpl->assign(MBYTES_TO => "$bytes_to"); $tpl->assign(MBYTES_FROM => "$bytes_from"); $tpl->assign(MBYTES_TOTAL => "$bytes_sum"); # TEXT VARIABLE ASSIGN $tpl->assign(USER_DETAILS_M => "$USER_DETAILS_M"); $tpl->assign(REC_PAYMENT_M => "$REC_PAYMENT_M"); $tpl->assign(LOGIN_M => "$LOGIN_M"); $tpl->assign(PASSWORD_M => "$PASSWORD_M"); $tpl->assign(RET_PASSWORD_M => "$RET_PASSWORD_M"); $tpl->assign(FULL_NAME_M => "$FULL_NAME_M"); $tpl->assign(EMAIL_M => "$EMAIL_M"); $tpl->assign(IP_M => "$IP_M"); $tpl->assign(LOGIN_TYPE_M => "$LOGIN_TYPE_M"); $tpl->assign(REG_DATE_M => "$REG_DATE_M"); $tpl->assign(BALANCE_M => "$BALANCE_M"); $tpl->assign(PRICE_M => "$PRICE_M"); $tpl->assign(CREDIT_M => "$CREDIT_M"); $tpl->assign(LOCKED_M => "$LOCKED_M"); $tpl->assign(ADMIN_M => "$ADMIN_M"); $tpl->assign(COMMENTS_M => "$COMMENTS_M"); $tpl->assign(SYSTEM_M_M => "$SYSTEM_M_M"); $tpl->assign(CHANGE_BUTTON => "$CHANGE_BUTTON"); $tpl->assign(TOTAL_BYTES_TO_M => "$TOTAL_BYTES_TO_M"); $tpl->assign(TOTAL_BYTES_FROM_M => "$TOTAL_BYTES_FROM_M"); $tpl->assign(MBYTES_TOTAL_M => "$MBYTES_TOTAL_M"); $tpl->assign(REPORT_PERIOD_REQ_M => "$REPORT_PERIOD_REQ_M"); $tpl->assign(PERIOD_FORMAT_M => "$PERIOD_FORMAT_M"); $tpl->assign(REPORT_BUTTON_M => "$REPORT_BUTTON_M"); $tpl->assign(B_DAY => "$mday"); $tpl->assign(B_MONTH => "$month"); $tpl->assign(B_YEAR => "$r_b_year"); $tpl->assign(L_HOUR => "$hour"); $tpl->assign(L_DAY => "$mday"); $tpl->assign(L_MONTH => "$month"); $tpl->assign(L_YEAR => "$r_b_year"); $tpl->parse(MAIN => ["users_change"]); $tpl->print(); }; if ($change_cmd eq "change") { $pas1 = $query->param('password1'); $pas2 = $query->param('password2'); $full_name = $query->param('full_name'); $email = $query->param('email'); $ip = $query->param('ip'); $ip_type = $query->param('ip_type'); $ip_old = $query->param('ip_old'); $price = $query->param('price'); $credit = $query->param('credit'); $block = $query->param('block'); $priv_level = $query->param('priv_level'); $comments = $query->param('comments'); $sys_message = $query->param('sys_message'); if ($pas1 ne $pas2) { print "Password mismatch! Go back and try again!"; exit(0); }; if ($block ne 0 && $block ne 1) { print "Block field format error (choose 0 or 1)"; exit (0) }; $crypted_password=crypt($pas1,$pas1); if ($pas1 ne "") { $dbh->do("UPDATE users SET password=\"$crypted_password\",full_name=\"$full_name\",price=$price,credit=$credit,block=\"$block\",priv_level=$priv_level,comments=\"$comments\",sys_message=\"$sys_message\",ip_type=\"$ip_type\",email=\"$email\" WHERE login=\"$login\""); } else { $dbh->do("UPDATE users SET full_name=\"$full_name\",price=$price,credit=$credit,block=\"$block\",priv_level=$priv_level,comments=\"$comments\",sys_message=\"$sys_message\",ip_type=\"$ip_type\",email=\"$email\" WHERE login=\"$login\""); }; if ($ip ne $ip_old) { $dbh->do("UPDATE users SET ip=\"$ip\" WHERE login=\"$login\""); $dbh->do("UPDATE traffic SET ip_from=\"$ip\" WHERE ip_from=\"$ip_old\""); $dbh->do("UPDATE traffic SET ip_to=\"$ip\" WHERE ip_to=\"$ip_old\""); $dbh->do("UPDATE sessions SET ip=\"$ip\" WHERE ip=\"$ip_old\""); }; print "Details for $login changed successfully.
Back to details.
"; }; $dbh->disconnect(); }; sub user_full_stat { $s_id = $query->param('sid'); if (sid_verify() ne 0){ exit(0) }; $ip = $query->param('ip'); $sort_field = $query->param('sort_field'); $b_hour = $query->param('b_hour'); $b_mday = $query->param('b_day'); $b_month = $query->param('b_month'); $b_year = $query->param('b_year'); $l_hour = $query->param('l_hour'); $l_mday = $query->param('l_day'); $l_month = $query->param('l_month'); $l_year = $query->param('l_year'); $b_time = timelocal (0,0,$b_hour,$b_mday,$b_month - 1,$b_year); $l_time = timelocal (0,0,$l_hour,$l_mday,$l_month - 1,$l_year); $norm_b_time = localtime ($b_time); $norm_l_time = localtime ($l_time); print "
Detailed traffic statistic from: $norm_b_time to: $norm_l_time
"; if ($sort_field eq "") { $sort_field = ltime; }; print ""; print ""; $sth = $dbh->prepare("SELECT * FROM traffic WHERE (ip_from = \"$ip\" OR ip_to = \"$ip\") AND ftime >=\"$b_time\" AND ltime <=\"$l_time\" ORDER BY $sort_field DESC"); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $norm_ftime = localtime ($ips->{'ftime'}); print ""; }; print "

ip_from	ip_to	bytes	time
$ips->{'ip_from'}	$ips->{'ip_to'}	$ips->{'bytes_all'}	$norm_ftime

"; }; sub logs { $s_id = $query->param('sid'); if (sid_verify() ne 0){ exit(0) }; $sort_field = $query->param('sort_field'); if ($sort_field eq "") { $sort_field = date; }; $tpl->assign(ROWS => ""); $sth = $dbh->prepare("SELECT * FROM bills_history ORDER BY \"$sort_field\" DESC"); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $norm_date = localtime ($ips->{'date'}); $tpl->assign( LOGIN => "$ips->{'login'}", DATE => "$norm_date", QNT => "$ips->{'qnt'}", WHO => "$ips->{'who'}", WHAT => "$ips->{'what'}", ); $tpl->parse(ROWS => ".users_logs_payment_row"); }; $sth->finish(); # TEXT VARIABLE ASSIGN $tpl->assign(PAYMENTS_REP_M => "$PAYMENTS_REP_M"); $tpl->assign(LOGIN_M => "$LOGIN_M"); $tpl->assign(PAYMENT_DATE_M => "$PAYMENT_DATE_M"); $tpl->assign(PAYMENT_QNT_M => "$PAYMENT_QNT_M"); $tpl->assign(WHO_M => "$WHO_M"); $tpl->assign(WHAT_M => "$WHAT_M"); $tpl->assign(SID => "$s_id"); $tpl->assign(USERS_PL => "$users_pl"); $tpl->parse(TABLE => ["users_logs_payment_table", "users_logs_payment"]); $tpl->print(); $dbh->disconnect(); }; sub settings { $s_id = $query->param('sid'); if (sid_verify() ne 0){exit(0)}; print "

Settings

"; }; sub sessions { $s_id = $query->param('sid'); if (sid_verify() ne 0){ exit(0); }; $sort_field = $query->param('sort_field'); if ($sort_field eq "") { $sort_field = date; }; $tpl->assign(ROWS => ""); $sth = $dbh->prepare("SELECT * FROM sessions ORDER BY \"$sort_field\" DESC LIMIT 500"); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $norm_date = localtime ($ips->{'date'}); $tpl->assign( ID => "$ips->{'id'}", LOGIN => "$ips->{'login'}", IP => "$ips->{'ip'}", DATE => "$norm_date", ); $tpl->parse(ROWS => ".users_sessions_row"); }; $sth->finish(); # TEXT VARIABLE ASSIGN $tpl->assign(SESSIONS_REPORT_M => "$SESSIONS_REPORT_M"); $tpl->assign(ID_M => "$ID_M"); $tpl->assign(LOGIN_M => "$LOGIN_M"); $tpl->assign(IP_M => "$IP_M"); $tpl->assign(DATE_M => "$DATE_M"); $tpl->assign(SID => "$s_id"); $tpl->assign(USERS_PL => "$users_pl"); $tpl->parse(TABLE => ["users_sessions_table", "users_sessions"]); $tpl->print(); $dbh->disconnect(); }; sub getusers_list { $s_id = $query->param('sid'); if (sid_verify() ne 0){exit(0)}; @ip_all = (); $sth = $dbh->prepare("SELECT * FROM users ORDER BY login"); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $temp = "$ips->{'id'} $ips->{'ip'} $ips->{'login'} $ips->{'bill'} $ips->{'price'} $ips->{'credit'} $ips->{'block'} $ips->{'reg_date'} $ips->{'priv_level'} $ips->{'sys_message'} $ips->{'bytes_from'} $ips->{'bytes_to'} $ips->{'ftime'} $ips->{'ltime'}"; push (@ip_all, $temp); }; $sth->finish(); }; sub traffic_stat { $s_id = $query->param('sid'); if (sid_verify() ne 0){ exit(0) }; $detailed = $query->param('detailed'); getusers_list(); if ($detailed ne "on") { $tpl->assign(REPORT_FROM_TO_PERIOD => ""); }else { $b_hour = $query->param('b_hour'); $b_mday = $query->param('b_day'); $b_month = $query->param('b_month'); $b_year = $query->param('b_year'); $l_hour = $query->param('l_hour'); $l_mday = $query->param('l_day'); $l_month = $query->param('l_month'); $l_year = $query->param('l_year'); $b_time = timelocal (0,0,$b_hour,$b_mday,$b_month - 1,$b_year); $l_time = timelocal (0,0,$l_hour,$l_mday,$l_month - 1,$l_year); $norm_b_time = localtime ($b_time); $norm_l_time = localtime ($l_time); $tpl->assign(REPORT_FROM_TO_PERIOD => "$norm_b_time - $norm_l_time"); }; $bytes_all_sum = 0; $average_all_users_hour = 0; $number_of_users = 0; foreach (@ip_all){ ($id, $ip, $login, $bill, $price, $credit, $block, $reg_date, $priv_level, $sys_message, $bytes_from, $bytes_to, $ftime, $ltime) = split(/ /, $_, 13); if ($detailed eq "on") { #OBTAIN TRAFFIC INFO FOR REQUESTED PERIOD $sth = $dbh->prepare("SELECT SUM\(bytes_all\) as tr_to FROM traffic WHERE ip_to=\"$ip\" AND ftime > \"$b_time\" AND ftime <\"$l_time\""); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $bytes_to = $ips->{'tr_to'}; }; $sth->finish(); $sth = $dbh->prepare("SELECT SUM\(bytes_all\) as tr_from FROM traffic WHERE ip_from=\"$ip\" AND ftime > \"$b_time\" AND ftime <\"$l_time\""); $sth->execute(); while (my $ips = $sth->fetchrow_hashref()) { $bytes_from = $ips->{'tr_from'}; }; $sth->finish(); }; # DISPLAY traffic statistic $mbytes_to = sprintf("%.3f", $bytes_to/1048576); $mbytes_from = sprintf("%.3f", $bytes_from/1048576); $total_mbytes_to += $mbytes_to; $total_mbytes_from += $mbytes_from; $price_bytes_to = sprintf("%.3f", $price*$mbytes_to); $price_bytes_from = sprintf("%.3f", $price*$mbytes_from); $total_price_bytes_to += $price_bytes_to; $total_price_bytes_from += $price_bytes_from; $total_bill += $bill; $tpl->assign( LOGIN => "$login", PRICE => "$price", MBYTES_FROM => "$mbytes_from", PRICE_FROM => "$price_bytes_from", MBYTES_TO => "$mbytes_to", PRICE_TO => "$price_bytes_to", BALANCE => "$bill", ); $tpl->parse(ROWS => ".users_traffic_row"); }; $tpl->assign( LOGIN => "$TOTAL_M", PRICE => "", MBYTES_FROM => "$total_mbytes_from", PRICE_FROM => "$total_price_bytes_from", MBYTES_TO => "$total_mbytes_to", PRICE_TO => "$total_price_bytes_to", BALANCE => "$total_bill", ); $tpl->parse(ROWS => ".users_traffic_row"); # TEXT VARIABLE ASSIGN $tpl->assign(TR_REPORT_M => "$TR_REPORT_M"); $tpl->assign(REPORT_PERIOD_REQ_M => "$REPORT_PERIOD_REQ_M"); $tpl->assign(PERIOD_FORMAT_M => "$PERIOD_FORMAT_M"); $tpl->assign(REPORT_BUTTON_M => "$REPORT_BUTTON_M"); $tpl->assign(LOGIN_M => "$LOGIN_M"); $tpl->assign(PRICE_M => "$PRICE_M"); $tpl->assign(FROM_M => "$FROM_M"); $tpl->assign(PRICE_TO_FROM_M => "$PRICE_TO_FROM_M"); $tpl->assign(TO_M => "$TO_M"); $tpl->assign(PRICE_TO_FROM_M => "$PRICE_TO_FROM_M"); $tpl->assign(BALANCE_M => "$BALANCE_M"); $r_b_year = $year + 1900; $tpl->assign(B_DAY => "$mday"); $tpl->assign(B_MONTH => "$month"); $tpl->assign(B_YEAR => "$r_b_year"); $tpl->assign(L_HOUR => "$hour"); $tpl->assign(L_DAY => "$mday"); $tpl->assign(L_MONTH => "$month"); $tpl->assign(L_YEAR => "$r_b_year"); $tpl->assign(SID => "$s_id"); $tpl->assign(USERS_PL => "$users_pl"); $tpl->parse(TABLE => ["users_traffic_table", "users_traffic"]); $tpl->print(); $dbh->disconnect(); }; sub tariffs { $tpl->parse(MAIN => ["users_tariffs"]); $tpl->print(); } sub database_log { print "

Database logs and backups

"; }; sub password_generate { @chars = ( "A" .. "Z", "a" .. "z", 0 .. 9); $p_generated = join("", @chars[ map { rand @chars } (1 .. 6) ]); return $p_generated; };