my %color = ();
my %mainsettings = ();
&General::readhash("${General::swroot}/main/settings", \%mainsettings);
-&General::readhash("/srv/web/ipfire/html/themes/".$mainsettings{'THEME'}."/include/colors.txt", \%color);
+&General::readhash("/srv/web/ipfire/html/themes/ipfire/include/colors.txt", \%color);
# Path and file of the OVPN connections database.
my $database = "/var/ipfire/ovpn/clients.db";
$errormessage = "$Lang::tr{'error the to date has to be later than the from date'}";
}
+# Initialise database
+my $cursor = $database_handle->prepare("
+ CREATE TABLE IF NOT EXISTS sessions(
+ common_name TEXT NOT NULL,
+ connected_at TEXT NOT NULL,
+ disconnected_at TEXT,
+ bytes_received INTEGER,
+ bytes_sent INTEGER
+ );
+
+ -- Create index for speeding up searches
+ CREATE INDEX IF NOT EXISTS sessions_common_name ON sessions(common_name);
+");
+$cursor->execute();
+
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')
+ WHEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds')
THEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime')
- ELSE DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')
+ ELSE DATETIME('$to_datestring', 'start of day', '+86399 seconds')
END
), 'utc') -
STRFTIME('%s', (
CASE
- WHEN DATETIME(connected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day')
+ WHEN DATETIME(connected_at, 'localtime') > DATETIME('$from_datestring', 'start of day')
THEN DATETIME(connected_at, 'localtime')
- ELSE DATETIME('$from_datestring', 'localtime', 'start of day')
+ ELSE DATETIME('$from_datestring', 'start of day')
END
), 'utc')
) AS duration
(
disconnected_at IS NULL
OR
- DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day')
+ DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start of day')
)
AND
- DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')
+ DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', '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 FROM sessions
+ 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')
+ DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start of day')
AND
- DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')
+ DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds')
)
ORDER BY connected_at;
);
}
-# Prepare SQL statement.
-my $statement_handle = $database_handle->prepare($database_query);
+my $statement_handle;
+my $database_return_value;
-# Execute SQL statement and get retun value if any error happened.
-my $database_return_value = $statement_handle->execute();
+# 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) {
print "<td width='40%' $col><b>$Lang::tr{'ovpn connection name'}</b></td>\n";
if ($cgiparams{'CONNECTION_NAME'}) {
- print "<td width='20%' $col><b>$Lang::tr{'connected'}</b></td>\n";
- print "<td width='20%' $col><b>$Lang::tr{'disconnected'}</b></td>\n";
- print "<td width='10%' $col><b>$Lang::tr{'recieved'}</b></td>\n";
- print "<td width='10%' $col><b>$Lang::tr{'sent'}</b></td>\n";
+ 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";
-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]);
-
- # Colorize columns.
- if ($lines % 2) {
- $col="bgcolor='$color{'color20'}'";
- } else {
- $col="bgcolor='$color{'color22'}'";
- }
+# 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";
+ print "<tr>\n";
+ print "<td width='40%' $col>$connection_name</td>\n";
- if ($cgiparams{'CONNECTION_NAME'}) {
- print "<td width='20%' $col>$connection_open_time</td>\n";
- print "<td width='20%' $col>$connection_close_time</td>\n";
- print "<td width='10%' $col>$connection_bytes_recieved</td>\n";
- print "<td width='10%' $col>$connection_bytes_sent</td>\n";
- } else {
- # Convert total connection time into human-readable format.
- my $total_time = &General::format_time($row[1]);
+ 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 "<td $col>$total_time</td>\n";
+ }
- print "</tr>\n";
+ print "</tr>\n";
- # Increase lines count.
- $lines++;
+ # 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='5' align='center'>$Lang::tr{'no entries'}</td></tr>\n";
+ print "<tr><td bgcolor='$color{'color22'}' colspan='6' align='center'>$Lang::tr{'no entries'}</td></tr>\n";
}
print "</table><br>\n";
projects / people / pmueller / ipfire-2.x.git / blobdiff