update-monthly-stats.php 1.62 KB
Newer Older
dmorley's avatar
dmorley committed
1 2
<?php

3 4 5 6 7 8
/**
 * Compute monthly stats.
 */

declare(strict_types=1);

dmorley's avatar
dmorley committed
9 10
use RedBeanPHP\R;

11
require_once __DIR__ . '/../boot.php';
dmorley's avatar
dmorley committed
12

dmorley's avatar
dmorley committed
13 14 15 16 17
if (!is_cli()) {
    header('HTTP/1.0 403 Forbidden');
    exit;
}

dmorley's avatar
dmorley committed
18
try {
19 20 21 22 23 24 25 26 27 28 29 30
    $monthly_totals = R::getAll("
        SELECT
            to_char(date_checked, 'yyyy-mm') AS yymm,
            sum(total_users) / count(DISTINCT to_char(date_checked, 'HH24 dd')) as users,
            sum(local_posts) / count(DISTINCT to_char(date_checked, 'HH24 dd')) as posts,
            sum(comment_counts) / count(DISTINCT to_char(date_checked, 'HH24 dd')) as comments,
            count(domain) / count(DISTINCT to_char(date_checked, 'HH24 dd')) as pods,
            count(nullif(online, false)) as uptime, 
            count(nullif(online, true)) as downtime
        FROM checks
        GROUP BY yymm
    ");
dmorley's avatar
dmorley committed
31
} catch (\RedBeanPHP\RedException $e) {
32
    die('Error in SQL query: ' . $e->getMessage());
dmorley's avatar
dmorley committed
33 34
}

35
foreach ($monthly_totals as $monthly) {
36 37
    // Format date to timestamp.
    $timestamp = $monthly['yymm'] . '-01 01:01:01-01';
dmorley's avatar
dmorley committed
38

39 40
    try {
        $p = R::findOrCreate('monthlystats', ['date_checked' => $timestamp]);
dmorley's avatar
dmorley committed
41

42 43 44 45 46 47 48 49 50
        $p['total_users']    = $monthly['users'];
        $p['total_posts']    = $monthly['posts'];
        $p['total_comments'] = $monthly['comments'];
        $p['total_pods']     = $monthly['pods'];
        if ($monthly['downtime']) {
            $p['total_uptime'] = round($monthly['downtime'] / $monthly['uptime'] * 100);
        } else {
            $p['total_uptime'] = 100;
        }
dmorley's avatar
dmorley committed
51

52 53 54 55
        R::store($p);
    } catch (\RedBeanPHP\RedException $e) {
        die('Error in SQL query: ' . $e->getMessage());
    }
dmorley's avatar
dmorley committed
56
}