includes/dashboard/global.php

<?php
declare(strict_types=1);

/*
|--------------------------------------------------------------------------
| Global
|--------------------------------------------------------------------------
*/

$global = [];

$globalSiteWhere = $currentSite !== ''
    ? ' WHERE site = ?'
    : '';

$globalSiteParams = $currentSite !== ''
    ? [$currentSite]
    : [];


/*
|--------------------------------------------------------------------------
| Global totals
|--------------------------------------------------------------------------
*/

$globalSqliteTotal = one(
    $db,
    '
        SELECT
            COALESCE(SUM(unique_visitors), 0) AS unique_visitors,
            COALESCE(SUM(visits), 0) AS visits,
            COALESCE(SUM(pageviews), 0) AS pageviews,
            COALESCE(SUM(bots), 0) AS bots
        FROM hits_daily
        ' . $globalSiteWhere,
    $globalSiteParams
);

$globalArchivesTotal = $currentSite === ''
    // No site filter: archiveHitsTotals() has a fast path for exactly
    // this case, backed by the cached archives/summary.json (see
    // archiveSummary() in archive.php) instead of re-reading and
    // re-summing every single archived row on every request.
    ? archiveHitsTotals()
    : archiveHitsTotals(null, null, $currentSite);

$globalTotal = [
    'unique_visitors' =>
        (int)($globalSqliteTotal['unique_visitors'] ?? 0)
        + $globalArchivesTotal['unique_visitors'],

    'visits' =>
        (int)($globalSqliteTotal['visits'] ?? 0)
        + $globalArchivesTotal['visits'],

    'pageviews' =>
        (int)($globalSqliteTotal['pageviews'] ?? 0)
        + $globalArchivesTotal['pageviews'],

    'bots' =>
        (int)($globalSqliteTotal['bots'] ?? 0)
        + $globalArchivesTotal['bots'],
];


/*
|--------------------------------------------------------------------------
| Countries
|--------------------------------------------------------------------------
*/

$globalCountries = [];

if ($showCountries) {
    $globalCountriesRaw = fetchAll(
        $db,
        '
            SELECT country, SUM(views) AS views
            FROM countries_daily
            ' . $globalSiteWhere . '
            GROUP BY country
            ORDER BY views DESC
        ',
        $globalSiteParams
    );

    foreach ($globalCountriesRaw as $row) {
        $country = (string)($row['country'] ?? '');

        if ($country === '') {
            continue;
        }

        $globalCountries[$country] = [
            'country' => $country,
            'views' => (int)($row['views'] ?? 0),
        ];
    }

    foreach (
        $currentSite === ''
            ? archiveGroupedViews('countries', 'country')
            : archiveGroupedViews('countries', 'country', null, null, $currentSite)
        as $row
    ) {
        $country = (string)($row['country'] ?? '');

        if ($country === '') {
            continue;
        }

        $globalCountries[$country] ??= [
            'country' => $country,
            'views' => 0,
        ];

        $globalCountries[$country]['views'] += (int)($row['views'] ?? 0);
    }

    $globalCountries = array_values($globalCountries);

    usort(
        $globalCountries,
        fn ($a, $b) => $b['views'] <=> $a['views']
    );

    $globalTotal['countries'] = count($globalCountries);
}


/*
|--------------------------------------------------------------------------
| Referrers and search engines
|--------------------------------------------------------------------------
*/

$globalReferrersRaw = fetchAll(
    $db,
    '
        SELECT referrer, SUM(views) AS views
        FROM referrers_daily
        ' . $globalSiteWhere . '
        GROUP BY referrer
        ORDER BY views DESC
    ',
    $globalSiteParams
);

foreach (
    $currentSite === ''
        ? archiveGroupedViews('referrers', 'referrer')
        : archiveGroupedViews('referrers', 'referrer', null, null, $currentSite)
    as $row
) {
    $globalReferrersRaw[] = $row;
}

$globalSearchEnginesGrouped = [];
$globalReferrersGrouped = [];

foreach ($globalReferrersRaw as $row) {
    $referrer = (string)($row['referrer'] ?? '');

    if ($referrer === '') {
        continue;
    }

    $realCategory = referrerCategory($referrer);

    if (
        referrerBlocked($referrer)
        || $realCategory === BRIVACIA_CATEGORY_BLOCKED
    ) {
        $canonical = BRIVACIA_BLOCKED;
        $label = t('ui.blocked');
        $category = BRIVACIA_CATEGORY_BLOCKED;
    } elseif (strcasecmp($referrer, BRIVACIA_UNKNOWN) === 0) {
        $canonical = BRIVACIA_UNKNOWN;
        $label = t('ui.unknown');
        $category = BRIVACIA_CATEGORY_REFERRER;
    } else {
        $canonical = referrerCanonical($referrer);
        $label = referrerLabel($referrer);
        $category = $realCategory;
    }

    if ($category === BRIVACIA_CATEGORY_SEARCH) {
        $target =& $globalSearchEnginesGrouped;
    } else {
        $target =& $globalReferrersGrouped;
    }

    if (!isset($target[$canonical])) {
        $target[$canonical] = [
            'referrer' => $canonical,
            'label' => $label,
            'category' => $category,
            'views' => 0,
        ];
    }

    $target[$canonical]['views'] += (int)($row['views'] ?? 0);

    unset($target);
}

$globalSearchEngines = array_values($globalSearchEnginesGrouped);
$globalReferrers = array_values($globalReferrersGrouped);

usort(
    $globalSearchEngines,
    fn ($a, $b) => $b['views'] <=> $a['views']
);

usort(
    $globalReferrers,
    fn ($a, $b) => $b['views'] <=> $a['views']
);

$globalTotal['search_engines'] = count($globalSearchEngines);
$globalTotal['referrers'] = count($globalReferrers);