[people/pmueller/ipfire-2.x.git] / html / cgi-bin / logs.cgi / ovpnclients.dat

#!/usr/bin/perl
###############################################################################
#                                                                             #
# IPFire.org - A linux based firewall                                         #
# Copyright (C) 2020 IPFire Team  <info@ipfire.org>                           #
#                                                                             #
# This program is free software: you can redistribute it and/or modify        #
# it under the terms of the GNU General Public License as published by        #
# the Free Software Foundation, either version 3 of the License, or           #
# (at your option) any later version.                                         #
#                                                                             #
# This program is distributed in the hope that it will be useful,             #
# but WITHOUT ANY WARRANTY; without even the implied warranty of              #
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the               #
# GNU General Public License for more details.                                #
#                                                                             #
# You should have received a copy of the GNU General Public License           #
# along with this program.  If not, see <http://www.gnu.org/licenses/>.       #
#                                                                             #
###############################################################################

use strict;
use POSIX();
use DBI;

# enable only the following on debugging purpose
#use warnings;
#use CGI::Carp 'fatalsToBrowser';

require '/var/ipfire/general-functions.pl';
require "${General::swroot}/lang.pl";
require "${General::swroot}/header.pl";

my %color = ();
my %mainsettings = ();
&General::readhash("${General::swroot}/main/settings", \%mainsettings);
&General::readhash("/srv/web/ipfire/html/themes/".$mainsettings{'THEME'}."/include/colors.txt", \%color);

# Path and file of the OVPN connections database.
my $database = "/var/ipfire/ovpn/clients.db";

my %cgiparams=();
my %logsettings=();
my %ovpnsettings=();

my $errormessage='';

# Hash wich contains the month numbers and the translated names for easy access.
my %monthhash = (
	"1" => "$Lang::tr{'january'}",
	"2" => "$Lang::tr{'february'}",
	"3" => "$Lang::tr{'march'}",
	"4" => "$Lang::tr{'april'}",
	"5" => "$Lang::tr{'may'}",
	"6" => "$Lang::tr{'june'}",
	"7" => "$Lang::tr{'july'}",
	"8" => "$Lang::tr{'august'}",
	"9" => "$Lang::tr{'september'}",
	"10" => "$Lang::tr{'october'}",
	"11" => "$Lang::tr{'november'}",
	"12" => "$Lang::tr{'december'}"
);

# Get current time.
my ($sec,$min,$hour,$mday,$month,$year,$wday,$yday,$isdst) = localtime(time);

# Adjust month, because Jan starts as month "0".
$month = $month+1;

# Adjust year number.
$year = $year+1900;

# Assign default vaules.
$cgiparams{'FROM_DAY'} = $mday;
$cgiparams{'FROM_MONTH'} = $month;
$cgiparams{'FROM_YEAR'} = $year;
$cgiparams{'TO_DAY'} = $mday;
$cgiparams{'TO_MONTH'} = $month;
$cgiparams{'TO_YEAR'} = $year;

&Header::getcgihash(\%cgiparams);

# Read-in OpenVPN settings and connections.
&General::readhasharray("${General::swroot}/ovpn/ovpnconfig", \%ovpnsettings);

# Init DB Module and connect to the database.
my $database_handle = DBI->connect("DBI:SQLite:dbname=$database", "", "", { RaiseError => 1 });

# Generate datestrings for SQL queries.
my $from_datestring = sprintf '%04d-%02d-%02d', ($cgiparams{"FROM_YEAR"}, $cgiparams{"FROM_MONTH"}, $cgiparams{"FROM_DAY"});
my $to_datestring = sprintf '%04d-%02d-%02d', ($cgiparams{"TO_YEAR"}, $cgiparams{"TO_MONTH"}, $cgiparams{"TO_DAY"});

# Check if the to datestring is later than the from datestring.
unless ($to_datestring ge $from_datestring) {
	$errormessage = "$Lang::tr{'error the to date has to be later than the from date'}";
}

my $database_query = qq(
	SELECT
        common_name, SUM(
                STRFTIME('%s', (
                        CASE
                                WHEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')
                                        THEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime')
                                        ELSE DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')
                        END
                ), 'utc') -
                STRFTIME('%s', (
                        CASE
                                WHEN DATETIME(connected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day')
                                        THEN DATETIME(connected_at, 'localtime')
                                        ELSE DATETIME('$from_datestring', 'localtime', 'start of day')
                        END
                ), 'utc')
        ) AS duration
        FROM sessions
        WHERE
                (
			disconnected_at IS NULL
	                OR
	                DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day')
		)
                AND
                DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')
        GROUP BY common_name
        ORDER BY common_name, duration DESC;
);

if ($cgiparams{'CONNECTION_NAME'}) {
	$database_query = qq(
		SELECT common_name, DATETIME(connected_at, 'localtime'), DATETIME(disconnected_at, 'localtime'), bytes_received, bytes_sent,
			STRFTIME('%s', DATETIME(disconnected_at)) - STRFTIME('%s', DATETIME(connected_at)) AS duration FROM sessions
		WHERE
			common_name = '$cgiparams{"CONNECTION_NAME"}'
			AND (
				DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day')
				AND
				DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')
			)
		ORDER BY connected_at;
	);
}

my $statement_handle;
my $database_return_value;

# Only process SQL actions if there is no error message.
unless ($errormessage) {
	# Prepare SQL statement.
	$statement_handle = $database_handle->prepare($database_query);

	# Execute SQL statement and get retun value if any error happened.
	$database_return_value = $statement_handle->execute();
}

# If an error has been returned, assign it to the errorstring value for displaying.
if($database_return_value < 0) {
   $errormessage = "$DBI::errstr";
}

&Header::showhttpheaders();

&Header::openpage($Lang::tr{'ovpn rw connection log'}, 1, '');

&Header::openbigbox('100%', 'left', '', $errormessage);

if ($errormessage) {
	&Header::openbox('100%', 'left', $Lang::tr{'error messages'});
	print "<font class='base'>$errormessage&nbsp;</font>\n";
	&Header::closebox();
}

&Header::openbox('100%', 'left', "$Lang::tr{'settings'}:");

print "<form method='post' action=\"$ENV{'SCRIPT_NAME'}\">\n";
print "<table width='100%'>\n";
	print "<tr>\n";
		print "<td class='base' colspan='2'><b>$Lang::tr{'from'}:</b></td>\n";
	print "</tr>\n";

	print "<tr>\n";
		print "<td class='base'>$Lang::tr{'day'}:&nbsp\;\n";
			&generate_select("FROM_DAY", "days");
		print "</td>\n";

		print "<td class='base'>$Lang::tr{'month'}:&nbsp\;\n";
			&generate_select("FROM_MONTH", "months");
		print "</td>\n";

		print "<td class='base'>$Lang::tr{'year'}:&nbsp\;\n";
			&generate_select("FROM_YEAR", "years");
		print "</td>\n";
	print "</tr>\n";

	print "<tr><td><br></td></tr>\n";

	print "<tr>\n";
		print "<td class='base' colspan='2'><b>$Lang::tr{'to'}:</b></td>\n";
	print "</tr>\n";

	print "<tr>\n";
		print "<td class='base'>$Lang::tr{'day'}:&nbsp\;\n";
			&generate_select("TO_DAY", "days");
		print "</td>\n";

		print "<td class='base'>$Lang::tr{'month'}:&nbsp\;\n";
			&generate_select("TO_MONTH", "months");
		print "</td>\n";

		print "<td class='base'>$Lang::tr{'year'}:&nbsp\;\n";
			&generate_select("TO_YEAR", "years");
		print "</td>\n";
	print "</tr>\n";

	print "<tr><td><br></td></tr>\n";

	print "<tr>\n";
		print "<td class='base'>$Lang::tr{'ovpn connection name'}:</td>\n";
		print "<td class='base' colspan='2'>\n";

			print "<select name='CONNECTION_NAME' size='1'>\n";
				print "<option value=''>$Lang::tr{'all'}</option>\n";

				# Loop through all configured OpenVPN connections and sort them by name.
				foreach my $key (sort { $ovpnsettings{$a}[2] cmp $ovpnsettings{$b}[2] } keys %ovpnsettings) {
					my $connection_name = $ovpnsettings{$key}[2];
					my $selected;

					# Skip all non roadwarrior connections.
					next unless ($ovpnsettings{"$key"}[3] eq "host");

					# Check and mark the selected one.
					if ($connection_name eq "$cgiparams{'CONNECTION_NAME'}") {
						$selected = "selected";
					}

					print "<option value='$connection_name' $selected>$connection_name</option>\n";
				}

			print "</select>\n";
		print "</td>\n";
	print "</tr>\n";

	print "<tr>\n";
		print "<td width='100%' align='right' colspan='3'><input type='submit' name='ACTION' value='$Lang::tr{'update'}'></td>\n";
	print "</tr>\n";
print "</table>\n";
print "</form>\n";

&Header::closebox();

&Header::openbox('100%', 'left', $Lang::tr{'log'});

my $lines = 0;

print "<table width='100%' class='tbl'>";

my $col = "bgcolor='$color{'color20'}'";

	print "<tr>\n";
		print "<td width='40%' $col><b>$Lang::tr{'ovpn connection name'}</b></td>\n";

	if ($cgiparams{'CONNECTION_NAME'}) {
		print "<td width='15%' $col><b>$Lang::tr{'connected'}</b></td>\n";
		print "<td width='15%' $col><b>$Lang::tr{'disconnected'}</b></td>\n";
		print "<td width='10%' align='right' $col><b>$Lang::tr{'duration'}</b></td>\n";
		print "<td width='10%' align='right' $col><b>$Lang::tr{'received'}</b></td>\n";
		print "<td width='10%' align='right' $col><b>$Lang::tr{'sent'}</b></td>\n";
	} else {
		print "<td $col><b>$Lang::tr{'total connection time'}</b>\n";
	}

	print "</tr>\n";

# Only try to fetch the DB items if there is no error message.
unless ($errormessage) {
	while(my @row = $statement_handle->fetchrow_array()) {
		# Assign some nice to read variable names for the DB fields.
		my $connection_name = $row[0];
		my $connection_open_time = $row[1];
		my $connection_close_time = $row[2];
		my $connection_bytes_recieved = &General::formatBytes($row[3]);
		my $connection_bytes_sent = &General::formatBytes($row[4]);
		my $duration = &General::format_time($row[5]);

		# Colorize columns.
		if ($lines % 2) {
			$col="bgcolor='$color{'color20'}'";
		} else {
			$col="bgcolor='$color{'color22'}'";
		}

		print "<tr>\n";
			print "<td width='40%' $col>$connection_name</td>\n";

		if ($cgiparams{'CONNECTION_NAME'}) {
			print "<td width='15%' $col>$connection_open_time</td>\n";
			print "<td width='15%' $col>$connection_close_time</td>\n";
			print "<td width='10%' align='right' $col>$duration</td>\n";
			print "<td width='10%' align='right' $col>$connection_bytes_recieved</td>\n";
			print "<td width='10%' align='right' $col>$connection_bytes_sent</td>\n";
		} else {
			# Convert total connection time into human-readable format.
			my $total_time = &General::format_time($row[1]);

			print "<td $col>$total_time</td>\n";
		}

		print "</tr>\n";

		# Increase lines count.
		$lines++;
	}
}

# If nothing has been fetched, the amount of lines is still zero.
# In this case display a hint about no data.	
unless ($lines) {
	print "<tr><td bgcolor='$color{'color22'}' colspan='6' align='center'>$Lang::tr{'no entries'}</td></tr>\n";
}

print "</table><br>\n";

&Header::closebox();

# Close database connection.
$database_handle->disconnect();

&Header::closebigbox();

&Header::closepage();

#
## Function for easy select generation.
#
sub generate_select($$) {
	my ($name, $type) = @_;

	my $start = 1;
	my $stop;

	# Adjust start and stop by the given type.
	if ($type eq "days") {
		$stop = 31;
	} elsif ($type eq "months") {
		$stop = 12;
	} elsif ($type = "years") {
		$stop = $year;
		$start = $stop - 10;
	}

	# Print select HTML tag.
	print "<select name='$name' size='1'>\n";

	# Loop through the range.
	for ( my $i = $start; $i <= $stop; $i++) {
                print "\t<option ";

		# Check and select the current processed item.
                if ($i == $cgiparams{$name}) {
                	print 'selected="selected" ';
		}

		# Check if months are processed and display the corresponding names.
		if ($type eq "months") {
			print "value='$i'>$monthhash{$i}</option>\n";
		} else {
			print "value='$i'>$i</option>\n";
		}
        }

	# Close select HTML tag.
        print "</select>\n\n";
}
Commit	Line	Data
3e10b3de SS	1	#!/usr/bin/perl
	2	###############################################################################
	3	# #
	4	# IPFire.org - A linux based firewall #
	5	# Copyright (C) 2020 IPFire Team <info@ipfire.org> #
	6	# #
	7	# This program is free software: you can redistribute it and/or modify #
	8	# it under the terms of the GNU General Public License as published by #
	9	# the Free Software Foundation, either version 3 of the License, or #
	10	# (at your option) any later version. #
	11	# #
	12	# This program is distributed in the hope that it will be useful, #
	13	# but WITHOUT ANY WARRANTY; without even the implied warranty of #
	14	# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the #
	15	# GNU General Public License for more details. #
	16	# #
	17	# You should have received a copy of the GNU General Public License #
	18	# along with this program. If not, see <http://www.gnu.org/licenses/>. #
	19	# #
	20	###############################################################################
	21
	22	use strict;
	23	use POSIX();
	24	use DBI;
	25
	26	# enable only the following on debugging purpose
	27	#use warnings;
	28	#use CGI::Carp 'fatalsToBrowser';
	29
	30	require '/var/ipfire/general-functions.pl';
	31	require "${General::swroot}/lang.pl";
	32	require "${General::swroot}/header.pl";
	33
	34	my %color = ();
	35	my %mainsettings = ();
	36	&General::readhash("${General::swroot}/main/settings", \%mainsettings);
	37	&General::readhash("/srv/web/ipfire/html/themes/".$mainsettings{'THEME'}."/include/colors.txt", \%color);
	38
	39	# Path and file of the OVPN connections database.
	40	my $database = "/var/ipfire/ovpn/clients.db";
	41
	42	my %cgiparams=();
	43	my %logsettings=();
	44	my %ovpnsettings=();
	45
	46	my $errormessage='';
	47
	48	# Hash wich contains the month numbers and the translated names for easy access.
	49	my %monthhash = (
	50	"1" => "$Lang::tr{'january'}",
	51	"2" => "$Lang::tr{'february'}",
	52	"3" => "$Lang::tr{'march'}",
	53	"4" => "$Lang::tr{'april'}",
	54	"5" => "$Lang::tr{'may'}",
	55	"6" => "$Lang::tr{'june'}",
	56	"7" => "$Lang::tr{'july'}",
	57	"8" => "$Lang::tr{'august'}",
	58	"9" => "$Lang::tr{'september'}",
	59	"10" => "$Lang::tr{'october'}",
	60	"11" => "$Lang::tr{'november'}",
	61	"12" => "$Lang::tr{'december'}"
	62	);
	63
	64	# Get current time.
65	my ($sec,$min,$hour,$mday,$month,$year,$wday,$yday,$isdst) = localtime(time);
66
67	# Adjust month, because Jan starts as month "0".
68	$month = $month+1;
69
70	# Adjust year number.
71	$year = $year+1900;
72
73	# Assign default vaules.
74	$cgiparams{'FROM_DAY'} = $mday;
75	$cgiparams{'FROM_MONTH'} = $month;
76	$cgiparams{'FROM_YEAR'} = $year;
77	$cgiparams{'TO_DAY'} = $mday;
78	$cgiparams{'TO_MONTH'} = $month;
79	$cgiparams{'TO_YEAR'} = $year;
80
81	&Header::getcgihash(\%cgiparams);
82
83	# Read-in OpenVPN settings and connections.
84	&General::readhasharray("${General::swroot}/ovpn/ovpnconfig", \%ovpnsettings);
85
86	# Init DB Module and connect to the database.
87	my $database_handle = DBI->connect("DBI:SQLite:dbname=$database", "", "", { RaiseError => 1 });
88
89	# Generate datestrings for SQL queries.
90	my $from_datestring = sprintf '%04d-%02d-%02d', ($cgiparams{"FROM_YEAR"}, $cgiparams{"FROM_MONTH"}, $cgiparams{"FROM_DAY"});
91	my $to_datestring = sprintf '%04d-%02d-%02d', ($cgiparams{"TO_YEAR"}, $cgiparams{"TO_MONTH"}, $cgiparams{"TO_DAY"});
92
0f195a53 SS	93	# Check if the to datestring is later than the from datestring.
	94	unless ($to_datestring ge $from_datestring) {
	95	$errormessage = "$Lang::tr{'error the to date has to be later than the from date'}";
	96	}
	97
3e10b3de SS	98	my $database_query = qq(
	99	SELECT
	100	common_name, SUM(
	101	STRFTIME('%s', (
	102	CASE
	103	WHEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')
	104	THEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime')
	105	ELSE DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')
	106	END
	107	), 'utc') -
	108	STRFTIME('%s', (
	109	CASE
	110	WHEN DATETIME(connected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day')
	111	THEN DATETIME(connected_at, 'localtime')
	112	ELSE DATETIME('$from_datestring', 'localtime', 'start of day')
	113	END
	114	), 'utc')
70e1d587	115	) AS duration
3e10b3de SS	116	FROM sessions
3e10b3de SS	117	WHERE
70e1d587 MT	118	(
	119	disconnected_at IS NULL
	120	OR
	121	DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day')
	122	)
	123	AND
3e10b3de SS	124	DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')
3e10b3de SS	125	GROUP BY common_name
70e1d587	126	ORDER BY common_name, duration DESC;
3e10b3de SS	127	);
	128
	129	if ($cgiparams{'CONNECTION_NAME'}) {
	130	$database_query = qq(
186c0ddd MT	131	SELECT common_name, DATETIME(connected_at, 'localtime'), DATETIME(disconnected_at, 'localtime'), bytes_received, bytes_sent,
186c0ddd MT	132	STRFTIME('%s', DATETIME(disconnected_at)) - STRFTIME('%s', DATETIME(connected_at)) AS duration FROM sessions
3e10b3de	133	WHERE
70e1d587 MT	134	common_name = '$cgiparams{"CONNECTION_NAME"}'
	135	AND (
	136	DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day')
	137	AND
	138	DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')
	139	)
	140	ORDER BY connected_at;
3e10b3de SS	141	);
	142	}
	143
e1cc1e6c SS	144	my $statement_handle;
e1cc1e6c SS	145	my $database_return_value;
3e10b3de	146
e1cc1e6c SS	147	# Only process SQL actions if there is no error message.
	148	unless ($errormessage) {
	149	# Prepare SQL statement.
	150	$statement_handle = $database_handle->prepare($database_query);
	151
	152	# Execute SQL statement and get retun value if any error happened.
	153	$database_return_value = $statement_handle->execute();
	154	}
3e10b3de SS	155
	156	# If an error has been returned, assign it to the errorstring value for displaying.
	157	if($database_return_value < 0) {
	158	$errormessage = "$DBI::errstr";
	159	}
	160
	161	&Header::showhttpheaders();
	162
	163	&Header::openpage($Lang::tr{'ovpn rw connection log'}, 1, '');
	164
	165	&Header::openbigbox('100%', 'left', '', $errormessage);
	166
	167	if ($errormessage) {
	168	&Header::openbox('100%', 'left', $Lang::tr{'error messages'});
	169	print "<font class='base'>$errormessage </font>\n";
	170	&Header::closebox();
	171	}
	172
	173	&Header::openbox('100%', 'left', "$Lang::tr{'settings'}:");
	174
	175	print "<form method='post' action=\"$ENV{'SCRIPT_NAME'}\">\n";
	176	print "<table width='100%'>\n";
	177	print "<tr>\n";
	178	print "<td class='base' colspan='2'><b>$Lang::tr{'from'}:</b></td>\n";
	179	print "</tr>\n";
	180
	181	print "<tr>\n";
	182	print "<td class='base'>$Lang::tr{'day'}:&nbsp\;\n";
	183	&generate_select("FROM_DAY", "days");
	184	print "</td>\n";
	185
	186	print "<td class='base'>$Lang::tr{'month'}:&nbsp\;\n";
	187	&generate_select("FROM_MONTH", "months");
	188	print "</td>\n";
	189
	190	print "<td class='base'>$Lang::tr{'year'}:&nbsp\;\n";
	191	&generate_select("FROM_YEAR", "years");
	192	print "</td>\n";
	193	print "</tr>\n";
	194
	195	print "<tr><td><br></td></tr>\n";
	196
	197	print "<tr>\n";
	198	print "<td class='base' colspan='2'><b>$Lang::tr{'to'}:</b></td>\n";
	199	print "</tr>\n";
	200
	201	print "<tr>\n";
	202	print "<td class='base'>$Lang::tr{'day'}:&nbsp\;\n";
	203	&generate_select("TO_DAY", "days");
	204	print "</td>\n";
	205
	206	print "<td class='base'>$Lang::tr{'month'}:&nbsp\;\n";
	207	&generate_select("TO_MONTH", "months");
	208	print "</td>\n";
	209
	210	print "<td class='base'>$Lang::tr{'year'}:&nbsp\;\n";
	211	&generate_select("TO_YEAR", "years");
	212	print "</td>\n";
	213	print "</tr>\n";
	214
	215	print "<tr><td><br></td></tr>\n";
	216
	217	print "<tr>\n";
	218	print "<td class='base'>$Lang::tr{'ovpn connection name'}:</td>\n";
219	print "<td class='base' colspan='2'>\n";
220
221	print "<select name='CONNECTION_NAME' size='1'>\n";
6317d55c	222	print "<option value=''>$Lang::tr{'all'}</option>\n";
3e10b3de SS	223
	224	# Loop through all configured OpenVPN connections and sort them by name.
	225	foreach my $key (sort { $ovpnsettings{$a}[2] cmp $ovpnsettings{$b}[2] } keys %ovpnsettings) {
	226	my $connection_name = $ovpnsettings{$key}[2];
	227	my $selected;
	228
	229	# Skip all non roadwarrior connections.
	230	next unless ($ovpnsettings{"$key"}[3] eq "host");
	231
	232	# Check and mark the selected one.
	233	if ($connection_name eq "$cgiparams{'CONNECTION_NAME'}") {
	234	$selected = "selected";
	235	}
	236
	237	print "<option value='$connection_name' $selected>$connection_name</option>\n";
	238	}
	239
	240	print "</select>\n";
	241	print "</td>\n";
	242	print "</tr>\n";
	243
	244	print "<tr>\n";
	245	print "<td width='100%' align='right' colspan='3'><input type='submit' name='ACTION' value='$Lang::tr{'update'}'></td>\n";
	246	print "</tr>\n";
	247	print "</table>\n";
	248	print "</form>\n";
	249
	250	&Header::closebox();
	251
	252	&Header::openbox('100%', 'left', $Lang::tr{'log'});
	253
	254	my $lines = 0;
	255
	256	print "<table width='100%' class='tbl'>";
	257
d5b6023c SS	258	my $col = "bgcolor='$color{'color20'}'";
	259
	260	print "<tr>\n";
	261	print "<td width='40%' $col><b>$Lang::tr{'ovpn connection name'}</b></td>\n";
	262
	263	if ($cgiparams{'CONNECTION_NAME'}) {
186c0ddd MT	264	print "<td width='15%' $col><b>$Lang::tr{'connected'}</b></td>\n";
	265	print "<td width='15%' $col><b>$Lang::tr{'disconnected'}</b></td>\n";
	266	print "<td width='10%' align='right' $col><b>$Lang::tr{'duration'}</b></td>\n";
b10612e8 SS	267	print "<td width='10%' align='right' $col><b>$Lang::tr{'received'}</b></td>\n";
b10612e8 SS	268	print "<td width='10%' align='right' $col><b>$Lang::tr{'sent'}</b></td>\n";
d5b6023c SS	269	} else {
	270	print "<td $col><b>$Lang::tr{'total connection time'}</b>\n";
	271	}
	272
	273	print "</tr>\n";
3e10b3de	274
e1cc1e6c SS	275	# Only try to fetch the DB items if there is no error message.
	276	unless ($errormessage) {
	277	while(my @row = $statement_handle->fetchrow_array()) {
	278	# Assign some nice to read variable names for the DB fields.
	279	my $connection_name = $row[0];
	280	my $connection_open_time = $row[1];
	281	my $connection_close_time = $row[2];
	282	my $connection_bytes_recieved = &General::formatBytes($row[3]);
	283	my $connection_bytes_sent = &General::formatBytes($row[4]);
186c0ddd	284	my $duration = &General::format_time($row[5]);
e1cc1e6c SS	285
	286	# Colorize columns.
	287	if ($lines % 2) {
	288	$col="bgcolor='$color{'color20'}'";
	289	} else {
	290	$col="bgcolor='$color{'color22'}'";
	291	}
	292
	293	print "<tr>\n";
	294	print "<td width='40%' $col>$connection_name</td>\n";
	295
	296	if ($cgiparams{'CONNECTION_NAME'}) {
186c0ddd MT	297	print "<td width='15%' $col>$connection_open_time</td>\n";
	298	print "<td width='15%' $col>$connection_close_time</td>\n";
	299	print "<td width='10%' align='right' $col>$duration</td>\n";
b10612e8 SS	300	print "<td width='10%' align='right' $col>$connection_bytes_recieved</td>\n";
b10612e8 SS	301	print "<td width='10%' align='right' $col>$connection_bytes_sent</td>\n";
e1cc1e6c SS	302	} else {
	303	# Convert total connection time into human-readable format.
	304	my $total_time = &General::format_time($row[1]);
	305
	306	print "<td $col>$total_time</td>\n";
	307	}
	308
	309	print "</tr>\n";
	310
	311	# Increase lines count.
	312	$lines++;
d5b6023c	313	}
c7d55d7f	314	}
3e10b3de	315
c7d55d7f	316	# If nothing has been fetched, the amount of lines is still zero.
e1cc1e6c	317	# In this case display a hint about no data.
c7d55d7f	318	unless ($lines) {
186c0ddd	319	print "<tr><td bgcolor='$color{'color22'}' colspan='6' align='center'>$Lang::tr{'no entries'}</td></tr>\n";
c7d55d7f	320	}
3e10b3de SS	321
	322	print "</table><br>\n";
	323
	324	&Header::closebox();
	325
	326	# Close database connection.
	327	$database_handle->disconnect();
	328
	329	&Header::closebigbox();
	330
	331	&Header::closepage();
	332
	333	#
	334	## Function for easy select generation.
	335	#
	336	sub generate_select($$) {
	337	my ($name, $type) = @_;
	338
	339	my $start = 1;
	340	my $stop;
	341
	342	# Adjust start and stop by the given type.
	343	if ($type eq "days") {
	344	$stop = 31;
	345	} elsif ($type eq "months") {
	346	$stop = 12;
	347	} elsif ($type = "years") {
	348	$stop = $year;
	349	$start = $stop - 10;
	350	}
	351
	352	# Print select HTML tag.
	353	print "<select name='$name' size='1'>\n";
	354
	355	# Loop through the range.
	356	for ( my $i = $start; $i <= $stop; $i++) {
	357	print "\t<option ";
	358
	359	# Check and select the current processed item.
	360	if ($i == $cgiparams{$name}) {
	361	print 'selected="selected" ';
	362	}
	363
	364	# Check if months are processed and display the corresponding names.
	365	if ($type eq "months") {
	366	print "value='$i'>$monthhash{$i}</option>\n";
	367	} else {
	368	print "value='$i'>$i</option>\n";
	369	}
	370	}
	371
	372	# Close select HTML tag.
	373	print "</select>\n\n";
	374	}