<?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) {
