monthly_stats.php 1.9 KB
Newer Older
dmorley's avatar
dmorley committed
1 2
<?php

dmorley's avatar
dmorley committed
3 4 5 6 7 8 9 10 11 12 13
/**
 * Compute monthly stats.
 */

declare(strict_types=1);

if (PHP_SAPI !== 'cli') {
    header('HTTP/1.0 403 Forbidden');
    exit;
}

dmorley's avatar
dmorley committed
14 15 16 17 18 19 20 21 22 23 24 25 26
use RedBeanPHP\R;

require_once __DIR__ . '/../vendor/autoload.php';
require_once __DIR__ . '/../config.php';

define('PODUPTIME', microtime(true));

// Set up global DB connection.
R::setup("pgsql:host={$pghost};dbname={$pgdb}", $pguser, $pgpass, true);
R::testConnection() || die('Error in DB connection');
R::usePartialBeans(true);

try {
dmorley's avatar
dmorley committed
27 28 29 30 31 32 33 34 35 36 37 38
    $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
39
} catch (\RedBeanPHP\RedException $e) {
dmorley's avatar
dmorley committed
40
    die('Error in SQL query: ' . $e->getMessage());
dmorley's avatar
dmorley committed
41 42 43
}
foreach ($monthly_totals as $monthly) {

dmorley's avatar
dmorley committed
44 45
    // Format date to timestamp.
    $timestamp = $monthly['yymm'] . '-01 01:01:01-01';
dmorley's avatar
dmorley committed
46

dmorley's avatar
dmorley committed
47 48
    try {
        $p = R::findOrCreate('monthlystats', ['date_checked' => $timestamp]);
dmorley's avatar
dmorley committed
49

dmorley's avatar
dmorley committed
50 51 52 53 54 55 56 57 58
        $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
59

dmorley's avatar
dmorley committed
60 61 62 63
        R::store($p);
    } catch (\RedBeanPHP\RedException $e) {
        die('Error in SQL query: ' . $e->getMessage());
    }
dmorley's avatar
dmorley committed
64
}