<?php
date_default_timezone_set('Asia/Ho_Chi_Minh');
$db = new SQLite3('mikstats.db', SQLITE3_OPEN_CREATE | SQLITE3_OPEN_READWRITE);
// Tinh traffic de cho vao table
$yearly = $db->query("SELECT strftime('%Y', timestamp), sum(tx), sum(rx), sum(tx)+sum(rx) FROM traffic GROUP BY strftime('%Y', timestamp) ORDER BY timestamp DESC;");
$monthly = $db->query("SELECT strftime('%m-%Y', timestamp), sum(tx), sum(rx), sum(tx)+sum(rx) FROM traffic GROUP BY strftime('%m-%Y', timestamp) ORDER BY timestamp DESC LIMIT 12;");
$daily = $db->query("SELECT strftime('%d-%m-%Y', timestamp), sum(tx), sum(rx), sum(tx)+sum(rx) FROM traffic GROUP BY strftime('%d-%m-%Y', timestamp) ORDER BY timestamp DESC LIMIT 60;");
// Tinh traffic de ve bieu do
$yearlygraph = $db->query("SELECT strftime('%Y', timestamp), sum(tx), sum(rx), sum(tx)+sum(rx) FROM traffic GROUP BY strftime('%Y', timestamp) ORDER BY timestamp DESC;");
$monthlygraph = $db->query("SELECT strftime('%m-%Y', timestamp), sum(tx), sum(rx), sum(tx)+sum(rx) FROM traffic GROUP BY strftime('%m-%Y', timestamp) ORDER BY timestamp ASC LIMIT 12;");
$dailygraph = $db->query("SELECT strftime('%d-%m-%Y', timestamp), sum(tx), sum(rx), sum(tx)+sum(rx) FROM traffic GROUP BY strftime('%d-%m-%Y', timestamp) ORDER BY timestamp ASC LIMIT 30;");
// Tao data bieu do
$dataPoints1 = array();
$dataPoints2 = array();
$dataPoints3 = array();
$dataPoints4 = array();
while ($row1 = $monthlygraph->fetchArray(SQLITE3_NUM)) {
array_push($dataPoints1, array("label"=> $row1[0], "y"=> $row1[2]/1073741824));
};
while ($row2 = $monthlygraph->fetchArray(SQLITE3_NUM)) {
array_push($dataPoints2, array("label"=> $row2[0], "y"=> $row2[1]/1073741824));
};
while ($row3 = $dailygraph->fetchArray(SQLITE3_NUM)) {
array_push($dataPoints3, array("label"=> substr($row3[0],0,5), "y"=> $row3[2]/1073741824));
};
while ($row4 = $dailygraph->fetchArray(SQLITE3_NUM)) {
array_push($dataPoints4, array("label"=> substr($row4[0],0,5), "y"=> $row4[1]/1073741824));
};
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Traffic Stats - Thống kê lưu lượng</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<script>
window.onload = function () {
var chart = new CanvasJS.Chart("chartContainer", {
title: {
text: "Lưu lượng theo Tháng - 12 Tháng trở lại"
},
theme: "light2",
animationEnabled: true,
toolTip:{
shared: true,
reversed: true
},
axisY: {
minimum: 0,
title: "Lưu lượng",
suffix: " GB",
valueFormatString: "# ##0.##"
},
legend: {
cursor: "pointer",
itemclick: toggleDataSeries
},
data: [
{
type: "stackedBar",
name: "Download",
showInLegend: true,
yValueFormatString: "# ##0.## GB",
dataPoints: <?php echo json_encode($dataPoints1, JSON_NUMERIC_CHECK); ?>
},{
type: "stackedBar",
name: "Upload",
showInLegend: true,
yValueFormatString: "# ##0.## GB",
dataPoints: <?php echo json_encode($dataPoints2, JSON_NUMERIC_CHECK); ?>
}
]
});
var chart2 = new CanvasJS.Chart("chartContainer2", {
title: {
text: "Lưu lượng theo Ngày - 30 Ngày trở lại"
},
theme: "light2",
animationEnabled: true,
toolTip:{
shared: true,
reversed: true
},
axisY: {
minimum: 0,
title: "Lưu lượng",
suffix: " GB",
valueFormatString: "# ##0.##"
},
legend: {
cursor: "pointer",
itemclick: toggleDataSeries
},
data: [
{
type: "stackedBar",
name: "Download",
showInLegend: true,
yValueFormatString: "# ##0.## GB",
dataPoints: <?php echo json_encode($dataPoints3, JSON_NUMERIC_CHECK); ?>
},{
type: "stackedBar",
name: "Upload",
showInLegend: true,
yValueFormatString: "# ##0.## GB",
dataPoints: <?php echo json_encode($dataPoints4, JSON_NUMERIC_CHECK); ?>
}
]
});
chart.render();
chart2.render();
function toggleDataSeries(e) {
if (typeof (e.dataSeries.visible) === "undefined" || e.dataSeries.visible) {
e.dataSeries.visible = false;
} else {
e.dataSeries.visible = true;
}
e.chart.render();
}
}
</script>
<style>
.title {
font-size: 30px;
font-weight: 600;
text-align:center;
color:blue;
margin: 50px 0 50px 0;
}
.table-title {
font-size: 20px;
font-weight: 600;
text-align:center;
color:red;
}
.table{
margin-bottom: 50px;
}
.footer{
font-size:12px;
font-weight:500;
text-align:center;
}
</style>
</head>
<body>
<div class="container">
<div class="title">
<div>Hệ thống thống kê lưu lượng WAN Mikrotik</div>
<div>Router: Trương Anh Tuấn</div>
</div>
<div class="table-title">Lưu lượng theo Ngày (60 ngày)</div>
<table class="table table-striped">
<thead>
<th>Ngày</th>
<th>Tải lên (Upload)</th>
<th>Tải xuống (Download)</th>
<th>Tổng lưu lượng</th>
</thead>
<tbody>
<?php
while ($row = $daily->fetchArray(SQLITE3_ASSOC)) {
echo "<tr>";
echo "<td>".$row["strftime('%d-%m-%Y', timestamp)"]."</td>";
echo "<td>".number_format($row["sum(tx)"]/1024/1024/1024,2,'.',' ')." GB </td>";
echo "<td>".number_format($row["sum(rx)"]/1024/1024/1024,2,'.',' ')." GB </td>";
echo "<td>".number_format($row["sum(tx)+sum(rx)"]/1024/1024/1024,2,'.',' ')." GB </td>";
echo "</tr>";
}
?>
</tbody>
</table>
<div class="table-title">Lưu lượng theo Tháng (12 tháng)</div>
<table class="table table-striped">
<thead>
<th>Tháng</th>
<th>Tải lên (Upload)</th>
<th>Tải xuống (Download)</th>
<th>Tổng lưu lượng</th>
</thead>
<tbody>
<?php
while ($row = $monthly->fetchArray(SQLITE3_ASSOC)) {
echo "<tr>";
echo "<td>".$row["strftime('%m-%Y', timestamp)"]."</td>";
echo "<td>".number_format($row["sum(tx)"]/1024/1024/1024,2,'.',' ')." GB </td>";
echo "<td>".number_format($row["sum(rx)"]/1024/1024/1024,2,'.',' ')." GB </td>";
echo "<td>".number_format($row["sum(tx)+sum(rx)"]/1024/1024/1024,2,'.',' ')." GB </td>";
echo "</tr>";
}
?>
</tbody>
</table>
<div class="table-title">Lưu lượng theo Năm</div>
<table class="table table-striped">
<thead>
<th>Năm</th>
<th>Tải lên (Upload)</th>
<th>Tải xuống (Download)</th>
<th>Tổng lưu lượng</th>
</thead>
<tbody>
<?php
while ($row = $yearly->fetchArray(SQLITE3_ASSOC)) {
echo "<tr>";
echo "<td>".$row["strftime('%Y', timestamp)"]."</td>";
echo "<td>".number_format($row["sum(tx)"]/1024/1024/1024,2,'.',' ')." GB </td>";
echo "<td>".number_format($row["sum(rx)"]/1024/1024/1024,2,'.',' ')." GB </td>";
echo "<td>".number_format($row["sum(tx)+sum(rx)"]/1024/1024/1024,2,'.',' ')." GB </td>";
echo "</tr>";
}
?>
</tbody>
</table>
</div>
<div id="chartContainer2" style="height: 370px; width: 90%; margin:0 auto;"></div>
<div id="chartContainer" style="height: 370px; width: 90%; margin:0 auto;"></div>
<script src="https://canvasjs.com/assets/script/canvasjs.min.js"></script>
</body>
<footer>
<div class="footer">Thiết kế và vận hành bởi DigiEra Networks</div>
</footer>
</html>
