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);

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


/*
|--------------------------------------------------------------------------
| Trends
|--------------------------------------------------------------------------
*/

$previousRangeSql = '';
$previousRangeParams = [];
$visitsChangeLabel = '';

$visitorsChange = null;
$visitsChange = null;
$pageviewsChange = null;

switch ($view) {
    case 'today':
        $previousRangeSql = 'day = ?';
        $previousRangeParams = [date('Y-m-d', strtotime($day . ' -1 day'))];
        $visitsChangeLabel = t('metric.vs.yesterday');
        break;

    case 'week':
        $previousRangeSql = 'day BETWEEN ? AND ?';
        $previousRangeParams = [
            date('Y-m-d', strtotime($rangeParams[0] . ' -7 days')),
            date('Y-m-d', strtotime($rangeParams[1] . ' -7 days')),
        ];
        $visitsChangeLabel = t('metric.vs.previous.week');
        break;

    case 'month':
        $previousMonth = date('Y-m', strtotime($month . '-01 -1 month'));

        $previousRangeSql = 'day BETWEEN ? AND ?';
        $previousRangeParams = [
            $previousMonth . '-01',
            date('Y-m-t', strtotime($previousMonth . '-01')),
        ];
        $visitsChangeLabel = t('metric.vs.previous.month');
        break;

    case 'year':
        $previousYear = (string)((int)$year - 1);

        $previousRangeSql = 'day BETWEEN ? AND ?';
        $previousRangeParams = [
            $previousYear . '-01-01',
            $previousYear . '-12-31',
        ];
        $visitsChangeLabel = t('metric.vs.previous.year');
        break;
}

if ($previousRangeSql !== '') {
    $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 $previousRangeSql
    ", $previousRangeParams);

    if (dashboardRangeNeedsArchives($previousRangeParams[0], $previousRangeParams[1] ?? $previousRangeParams[0])) {
        $archivedPrevious = archiveHitsTotals(
            $previousRangeParams[0],
            $previousRangeParams[1] ?? $previousRangeParams[0]
        );

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

    $previousVisitorsCount = (int)($previous['unique_visitors'] ?? 0);
    $currentVisitorsCount = (int)($today['unique_visitors'] ?? 0);

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

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

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

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

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

if (
    $view === 'today' &&
    $day === date('Y-m-d') &&
    (int)date('G') < 3
) {
    $visitorsChange = null;
    $visitsChange = null;
    $pageviewsChange = null;
}


/*
|--------------------------------------------------------------------------
| Totals
|--------------------------------------------------------------------------
*/

$totalSqlite = 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
');


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

$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
        GROUP BY day
        HAVING unique_visitors > 0 OR visits > 0 OR pageviews > 0
    )
');


/*
|--------------------------------------------------------------------------
| 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) 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();