includes/dashboard/counters.php

<?php
declare(strict_types=1);

/*
|--------------------------------------------------------------------------
| Dashboard counters
|--------------------------------------------------------------------------
*/

$today = 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
    WHERE $rangeSql
", $rangeParams);

if ($includeArchives) {
    $archivedToday = archiveHitsTotals(
        $rangeStart,
        $rangeEnd,
        $currentSite
    );

    foreach ($archivedToday as $key => $value) {
        $today[$key] = (int)($today[$key] ?? 0) + $value;
    }
}


/*
|--------------------------------------------------------------------------
| Trends
|--------------------------------------------------------------------------
|
| Always compares "the same amount of elapsed time" on both sides, so a
| still-running period is never measured against a fully completed one:
| - today: uses hits_hourly, only fully completed hours count on both days
| - week/month/year: the previous period is capped to the same number of
|   elapsed days as the current (possibly still running) one
|
| Also scopes the comparison to the currently selected site, like $today
| above already does (previously this always compared all sites combined,
| even when a single site was selected in the dashboard).
|
*/

$siteFilterSql = $currentSite !== '' ? ' AND site = ?' : '';
$siteFilterParams = $currentSite !== '' ? [$currentSite] : [];

$visitsChangeLabel = '';
$visitorsChange = null;
$visitsChange = null;
$pageviewsChange = null;

$previous = null;
$currentForTrend = $today;

// Minimum fully-completed hours before "today" is compared against
// yesterday at all — below that (00:00-02:59), a single hit can swing the
// percentage wildly, so the placeholder stays up a bit longer.
$minHoursForTrend = 3;

// Minimum sample size (on the smaller side of the comparison) before a
// trend percentage is shown. A small site can still cross the 3-hour mark
// with just 1 or 2 visits, which still isn't enough for a meaningful %.
// Configurable via Settings > Trends > sensitivity (settings.php).
$minSampleForTrend = brivaciaTrendMinSample();

$trendPlaceholderText = t(brivaciaTrendPlaceholderKey());

if ($view === 'today') {
    $todayDate = date('Y-m-d');
    $hourNow = (int)date('G');
    $visitsChangeLabel = t('metric.vs.yesterday');

    if ($day === $todayDate && $hourNow >= $minHoursForTrend) {
        // "Today" is still running: only compare fully completed hours,
        // on both days, so 11:30 today is never measured against a full
        // 24h yesterday.
        $yesterday = date('Y-m-d', strtotime($day . ' -1 day'));

        $currentForTrend = one($db, "
            SELECT
                COALESCE(SUM(unique_visitors), 0) AS unique_visitors,
                COALESCE(SUM(visits), 0) AS visits,
                COALESCE(SUM(pageviews), 0) AS pageviews
            FROM hits_hourly
            WHERE day = ? AND hour < ?$siteFilterSql
        ", array_merge([$day, $hourNow], $siteFilterParams));

        $previous = one($db, "
            SELECT
                COALESCE(SUM(unique_visitors), 0) AS unique_visitors,
                COALESCE(SUM(visits), 0) AS visits,
                COALESCE(SUM(pageviews), 0) AS pageviews
            FROM hits_hourly
            WHERE day = ? AND hour < ?$siteFilterSql
        ", array_merge([$yesterday, $hourNow], $siteFilterParams));
    } elseif ($day !== $todayDate) {
        // A specific past day was picked (day picker): it's fully
        // completed, so a plain whole-day comparison is already fair.
        $yesterday = date('Y-m-d', strtotime($day . ' -1 day'));

        $previous = one($db, "
            SELECT
                COALESCE(SUM(unique_visitors), 0) AS unique_visitors,
                COALESCE(SUM(visits), 0) AS visits,
                COALESCE(SUM(pageviews), 0) AS pageviews
            FROM hits_daily
            WHERE day = ?$siteFilterSql
        ", array_merge([$yesterday], $siteFilterParams));

        if (dashboardRangeNeedsArchives($yesterday, $yesterday)) {
            $archivedPrevious = archiveHitsTotals(
                $yesterday,
                $yesterday,
                $currentSite
            );

            foreach ($archivedPrevious as $key => $value) {
                $previous[$key] = (int)($previous[$key] ?? 0) + $value;
            }
        }
    }
    // else: before the 3rd fully-completed hour, there isn't enough
    // elapsed time yet to make a fair comparison, so the trend stays
    // hidden (placeholder).
} elseif (in_array($view, ['week', 'month', 'year'], true)) {
    $todayDate = date('Y-m-d');
    $elapsedEnd = $todayDate < $rangeEnd ? $todayDate : $rangeEnd;
    $elapsedDays = (int)((strtotime($elapsedEnd) - strtotime($rangeStart)) / 86400);

    switch ($view) {
        case 'week':
            $previousStart = date('Y-m-d', strtotime($rangeStart . ' -7 days'));
            $visitsChangeLabel = t('metric.vs.previous.week');
            break;

        case 'month':
            $previousStart = date('Y-m', strtotime($rangeStart . ' -1 month')) . '-01';
            $visitsChangeLabel = t('metric.vs.previous.month');
            break;

        case 'year':
            $previousStart = date('Y-m-d', strtotime($rangeStart . ' -1 year'));
            $visitsChangeLabel = t('metric.vs.previous.year');
            break;
    }

    $previousEnd = date('Y-m-d', strtotime($previousStart . " +$elapsedDays days"));

    $previous = one($db, "
        SELECT
            COALESCE(SUM(unique_visitors), 0) AS unique_visitors,
            COALESCE(SUM(visits), 0) AS visits,
            COALESCE(SUM(pageviews), 0) AS pageviews
        FROM hits_daily
        WHERE day BETWEEN ? AND ?$siteFilterSql
    ", array_merge([$previousStart, $previousEnd], $siteFilterParams));

    if (dashboardRangeNeedsArchives($previousStart, $previousEnd)) {
        $archivedPrevious = archiveHitsTotals(
            $previousStart,
            $previousEnd,
            $currentSite
        );

        foreach ($archivedPrevious as $key => $value) {
            $previous[$key] = (int)($previous[$key] ?? 0) + $value;
        }
    }
}

if ($previous !== null) {
    $previousVisitorsCount = (int)($previous['unique_visitors'] ?? 0);
    $currentVisitorsCount = (int)($currentForTrend['unique_visitors'] ?? 0);

    if (min($previousVisitorsCount, $currentVisitorsCount) >= $minSampleForTrend) {
        $visitorsChange = round((($currentVisitorsCount - $previousVisitorsCount) / $previousVisitorsCount) * 100, 1);
    }

    $previousVisitsCount = (int)($previous['visits'] ?? 0);
    $currentVisitsCount = (int)($currentForTrend['visits'] ?? 0);

    if (min($previousVisitsCount, $currentVisitsCount) >= $minSampleForTrend) {
        $visitsChange = round((($currentVisitsCount - $previousVisitsCount) / $previousVisitsCount) * 100, 1);
    }

    $previousPageviewsCount = (int)($previous['pageviews'] ?? 0);
    $currentPageviewsCount = (int)($currentForTrend['pageviews'] ?? 0);

    if (min($previousPageviewsCount, $currentPageviewsCount) >= $minSampleForTrend) {
        $pageviewsChange = round((($currentPageviewsCount - $previousPageviewsCount) / $previousPageviewsCount) * 100, 1);
    }
}


/*
|--------------------------------------------------------------------------
| Averages
|--------------------------------------------------------------------------
*/

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

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

$avgDay = one($db, '
    SELECT
        ROUND(AVG(unique_visitors), 1) AS avg_unique,
        ROUND(AVG(visits), 1) AS avg_visits,
        ROUND(AVG(pageviews), 1) AS avg_views
    FROM (
        SELECT
            day,
            SUM(unique_visitors) AS unique_visitors,
            SUM(visits) AS visits,
            SUM(pageviews) AS pageviews
        FROM hits_daily
        ' . $avgSiteWhere . '
        GROUP BY day
        HAVING unique_visitors > 0 OR visits > 0 OR pageviews > 0
    )
', $avgSiteParams);


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

$countries = [];

if ($showCountries) {
    $countriesRaw = fetchAll($db, "
        SELECT country, SUM(views) AS views
        FROM countries_daily
        WHERE $rangeSql
        GROUP BY country
        ORDER BY views DESC
    ", $rangeParams);

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

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

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

    if ($includeArchives) {
        foreach (
            archiveGroupedViews(
                'countries',
                'country',
                $rangeStart,
                $rangeEnd,
                $currentSite
            ) as $row
        ) {
            $country = (string)($row['country'] ?? '');

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

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

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

    $countries = array_values($countries);

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


/*
|--------------------------------------------------------------------------
| Archived totals
|--------------------------------------------------------------------------
*/

$archivesTotal = archiveHitsTotals();