<?php
chdir(realpath(dirname(__FILE__)) . '/..');
require 'vendor/autoload.php';
require 'config.php';
// Database
require 'db_setup.php';
// Truncate 'hours' table
Hour::truncate();
// Reprocess 'raw_hours' data by hour
$dates = RawHour::groupBy('date')->orderBy('date')->lists('date');
foreach ($dates as $date) {
// Generate current aggregated download and upload lists
$users_downloaded = RawHour::where('date', '=', $date)->where('dst_user_name', '!=', '')->groupBy('dst_user_name')->select('users.id', 'dst_user_name as user_name', 'dst_addr as ipaddr', $db->connection()->raw('sum(bytes) as downloaded'))->leftJoin('users', 'dst_user_name', '=', 'users.name')->get()->toArray();
$users_uploaded = RawHour::where('date', '=', $date)->where('src_user_name', '!=', '')->groupBy('src_user_name')->select('users.id', 'src_user_name as user_name', 'src_addr as ipaddr', $db->connection()->raw('sum(bytes) as uploaded'))->leftJoin('users', 'src_user_name', '=', 'users.name')->get()->toArray();
// Parse user data
foreach ($users_downloaded as $user_downloaded) {
// Update users table
if (!($user = User::where('name', '=', $user_downloaded['user_name'])->first())) {
$user = new User();
$user->name = $user_downloaded['user_name'];
}
$user->lastip = $user_downloaded['ipaddr'];
$user->firstseen = isset($user->firstseen) && $user->firstseen != false ? $user->firstseen : $date;
$user->lastseen = $date;
$user->save();
// Determine user usage data
$downloaded = $user_downloaded['downloaded'];
$uploaded = 0;
foreach ($users_uploaded as $user_uploaded) {
if ($user_uploaded['user_name'] == $user->name) {
}
}
// Fetch current accounting pairs into an array
foreach ($routers as $router) {
$lines = file('http://' . $router['hostname'] . '/accounting/ip.cgi');
$date = strtotime(date('Y-m-d H:00:00'));
// Iterate over lines
foreach ($lines as $line) {
$line = explode(' ', $line);
// 0 = src_addr, 1 = dst_addr, 2 = bytes, 3 = packets, 4 = src_user, 5 = dst_user
$raw_src_user = isset($ovpn_ips[$line[0]]) ? $ovpn_ips[$line[0]]['username'] : ($line[4] !== '*' ? $line[4] : NULL);
$raw_dst_user = isset($ovpn_ips[$line[1]]) ? $ovpn_ips[$line[1]]['username'] : (trim($line[5]) !== '*' ? trim($line[5]) : NULL);
if (isset($raw_src_user) || isset($raw_dst_user)) {
$raw_hourly = array('date' => $date, 'src_addr' => $line[0], 'dst_addr' => $line[1], 'bytes' => $line[2], 'packets' => $line[3], 'src_user_name' => $raw_src_user, 'dst_user_name' => $raw_dst_user, 'router_hostname' => $router['hostname']);
// Insert into DB
$raw_hour = new RawHour($raw_hourly);
$raw_hour->save();
}
}
}
// Generate current aggregated download and upload lists
$users_downloaded = RawHour::where('date', '=', $date)->where('dst_user_name', '!=', '')->groupBy('dst_user_name')->select('users.id', 'dst_user_name as user_name', 'dst_addr as ipaddr', $db->connection()->raw('sum(bytes) as downloaded'))->leftJoin('users', 'dst_user_name', '=', 'users.name')->get()->toArray();
$users_uploaded = RawHour::where('date', '=', $date)->where('src_user_name', '!=', '')->groupBy('src_user_name')->select('users.id', 'src_user_name as user_name', 'src_addr as ipaddr', $db->connection()->raw('sum(bytes) as uploaded'))->leftJoin('users', 'src_user_name', '=', 'users.name')->get()->toArray();
// Parse user data
foreach ($users_downloaded as $user_downloaded) {
// Update users table
if (!($user = User::where('name', '=', $user_downloaded['user_name'])->first())) {
$user = new User();
$user->name = $user_downloaded['user_name'];
}
$user->lastip = $user_downloaded['ipaddr'];
