easyStatsEvo.php

<?php

# get correct id for plugin
$thisfile = basename(__FILE__, ".php");

# register plugin
register_plugin(
    $thisfile,
    'easyStatsEvo 📊',
    '1.0',
    'CE Team',
    'https://getsimple-ce.ovh/donate',
    'This plugin shows statistics by counting only unique IP addresses on a website (Uses sqlite3 to store data) and provides export functionality.',
    'pages',
    'easyStatsView'
);

# activate filter
add_action('theme-footer', 'makeEasyStats');

# add a link in the admin tab 'theme'
add_action('pages-sidebar', 'createSideMenu', array($thisfile, 'Easy Stats Evo 📊'));

// ════════════════════════════════════════════════════════════
//  HELPERS
// ════════════════════════════════════════════════════════════

function easyStats_dbPath(): string {
    return GSDATAOTHERPATH . 'easyStats/easyStats.db';
}

function easyStats_initDb(): SQLite3 {
    $dir = GSDATAOTHERPATH . 'easyStats/';
    if (!is_dir($dir)) {
        mkdir($dir, 0755, true);
        file_put_contents($dir . '.htaccess', 'Deny from All');
    }

    $db = new SQLite3(easyStats_dbPath());
    $db->busyTimeout(5000);
    $db->exec('PRAGMA journal_mode=WAL;');
    $db->exec('PRAGMA synchronous=NORMAL;');

    $db->exec('
        CREATE TABLE IF NOT EXISTS visitors (
            id        INTEGER PRIMARY KEY AUTOINCREMENT,
            ip        TEXT    NOT NULL,
            timestamp INTEGER NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_visitors_ip ON visitors(ip);
        CREATE INDEX IF NOT EXISTS idx_visitors_ts ON visitors(timestamp);

        CREATE TABLE IF NOT EXISTS pages (
            id        INTEGER PRIMARY KEY AUTOINCREMENT,
            url       TEXT    NOT NULL,
            ip        TEXT    NOT NULL,
            timestamp INTEGER NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_pages_url ON pages(url);
        CREATE INDEX IF NOT EXISTS idx_pages_ip  ON pages(ip);
        CREATE INDEX IF NOT EXISTS idx_pages_ts  ON pages(timestamp);
    ');

    return $db;
}

// Simple session-based CSRF nonce
function easyStats_nonce(): string {
  
    if (empty($_SESSION['easyStats_nonce'])) {
        $_SESSION['easyStats_nonce'] = bin2hex(random_bytes(16));
    }
    return $_SESSION['easyStats_nonce'];
}

// ════════════════════════════════════════════════════════════
//  ADMIN VIEW
// ════════════════════════════════════════════════════════════

function easyStatsView(): void {
    $db  = easyStats_initDb();
    $now = time();

    // ── handle POST actions ──────────────────────────────────
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        $action = $_POST['easyStats_action'] ?? '';

        if ($action === 'clear' && ($_POST['easyStats_nonce'] ?? '') === easyStats_nonce()) {
            $db->exec('DELETE FROM visitors; DELETE FROM pages;');
            echo '<div class="updated"><p>✅ All statistics have been cleared.</p></div>';
        }

        if ($action === 'export') {
            $db->close();
            easyStats_exportXlsx();
            exit;
        }
    }

    // ── visitor counts ───────────────────────────────────────
    // Each visitor row = one visit (with timestamp).
    // COUNT(DISTINCT ip) within a time window = unique visitors in that window.
    $ranges = [
        'all'   => 0,
        '30d'   => $now - 30 * 86400,
        '7d'    => $now -  7 * 86400,
        '24h'   => $now -      86400,
        '5min'  => $now -        300,
    ];

    $counts = [];
    foreach ($ranges as $key => $since) {
        $stmt = $db->prepare(
            'SELECT COUNT(DISTINCT ip) AS cnt FROM visitors WHERE timestamp >= :since'
        );
        $stmt->bindValue(':since', $since, SQLITE3_INTEGER);
        $row          = $stmt->execute()->fetchArray(SQLITE3_ASSOC);
        $counts[$key] = (int)($row['cnt'] ?? 0);
    }

    // ── top 20 pages ─────────────────────────────────────────
    $pagesResult = $db->query('
        SELECT   url,
                 COUNT(*)           AS total_visits,
                 COUNT(DISTINCT ip) AS unique_visitors
        FROM     pages
        GROUP BY url
        ORDER BY unique_visitors DESC
        LIMIT    20
    ');
    $pages = [];
    while ($row = $pagesResult->fetchArray(SQLITE3_ASSOC)) {
        $pages[] = $row;
    }

    $db->close();

    $nonce = easyStats_nonce();
    global $USR;
    ?>
    <style>
        h2 { font-weight: 600; }
        .es-actions { display: flex; gap: 10px; margin: 15px 0 25px; flex-wrap: wrap; }
        .es-btn { padding: 7px 16px; border-radius: 6px; font-weight: bold; cursor: pointer; border: 1px solid transparent; font-size: 14px; display: inline-flex; align-items: center; gap: 6px; }
        .es-btn-export { background: #22c55e; color: #fff; border-color: #16a34a; }
        .es-btn-export:hover { background: #16a34a; }
        .es-btn-clear  { background: #ef4444; color: #fff; border-color: #dc2626; }
        .es-btn-clear:hover  { background: #dc2626; }
        .donateButton { box-shadow: inset 0 1px 0 #fce2c1; background: linear-gradient(to bottom, #ffc477 5%, #fb9e25 100%); background-color: #ffc477; border-radius: 15px; border: 1px solid #eeb44f; display: inline-block; cursor: pointer; color: #fff !important; font-family: Arial; font-size: 15px; font-weight: bold; padding: 5px 10px; text-decoration: none !important; text-shadow: 0 1px 0 #cc9f52; }
        .donateButton:hover { background: linear-gradient(to bottom, #fb9e25 5%, #ffc477 100%); }
        .donateButton:active { position: relative; top: 1px; }
        hr { border: 0; height: 0; border-top: 1px solid rgba(0,0,0,.1); border-bottom: 1px solid rgba(255,255,255,.3); }
    </style>

    <div style="width:100%;background:#fafafa;border:solid 1px #ddd;padding:15px;margin-bottom:20px;">
        <h3>📊 Easy Stats Evo</h3>
        <p>Statistics based on unique (hashed) IP addresses. Each visit is stored individually so all time-window counters stay accurate.</p>
    </div>

    <!-- action buttons -->
    <div class="es-actions">
        <form method="post" style="margin:0;">
            <input type="hidden" name="easyStats_action" value="export">
            <button type="submit" class="es-btn es-btn-export">
                📥 Export to Excel (.xlsx)
            </button>
        </form>

        <form method="post" style="margin:0;"
              onsubmit="return confirm('Are you sure you want to delete ALL statistics? This cannot be undone.');">
            <input type="hidden" name="easyStats_action" value="clear">
            <input type="hidden" name="easyStats_nonce"  value="<?= htmlspecialchars($nonce) ?>">
            <button type="submit" class="es-btn es-btn-clear">
                🗑️ Clear All Statistics
            </button>
        </form>
    </div>

    <!-- visitors summary -->
    <div class="bg-light border p-2">
        <h2>
            <svg xmlns="http://www.w3.org/2000/svg" style="vertical-align:middle;" width="1.5em" height="1.2em" viewBox="0 0 640 512"><rect width="640" height="512" fill="none"/><path fill="#707070" d="M96 224c35.3 0 64-28.7 64-64s-28.7-64-64-64s-64 28.7-64 64s28.7 64 64 64m448 0c35.3 0 64-28.7 64-64s-28.7-64-64-64s-64 28.7-64 64s28.7 64 64 64m32 32h-64c-17.6 0-33.5 7.1-45.1 18.6c40.3 22.1 68.9 62 75.1 109.4h66c17.7 0 32-14.3 32-32v-32c0-35.3-28.7-64-64-64m-256 0c61.9 0 112-50.1 112-112S381.9 32 320 32S208 82.1 208 144s50.1 112 112 112m76.8 32h-8.3c-20.8 10-43.9 16-68.5 16s-47.6-6-68.5-16h-8.3C179.6 288 128 339.6 128 403.2V432c0 26.5 21.5 48 48 48h288c26.5 0 48-21.5 48-48v-28.8c0-63.6-51.6-115.2-115.2-115.2m-223.7-13.4C161.5 263.1 145.6 256 128 256H64c-35.3 0-64 28.7-64 64v32c0 17.7 14.3 32 32 32h65.9c6.3-47.4 34.9-87.3 75.2-109.4"/></svg>
            Visitors Online
        </h2>
    </div>
    <table class="table">
        <tr><td>Unique visitors all time:       <b style="background:#A3A4FF;padding:0 3px;border-radius:3px;"><?= $counts['all']  ?></b></td></tr>
        <tr><td>Unique visitors last 30 days:   <b style="background:#79E6A0;padding:0 3px;border-radius:3px;"><?= $counts['30d']  ?></b></td></tr>
        <tr><td>Unique visitors last 7 days:    <b style="background:#FFDB82;padding:0 3px;border-radius:3px;"><?= $counts['7d']   ?></b></td></tr>
        <tr><td>Unique visitors last 24 hours:  <b style="background:#F9B27D;padding:0 3px;border-radius:3px;"><?= $counts['24h']  ?></b></td></tr>
        <tr><td>Unique visitors last 5 minutes: <b style="background:#FF90C8;padding:0 3px;border-radius:3px;"><?= $counts['5min'] ?></b></td></tr>
    </table>

    <!-- bar chart -->
    <canvas id="statisticsChart" style="margin:20px 0;" width="400" height="200"></canvas>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script>
      new Chart(document.getElementById('statisticsChart'), {
        type: 'bar',
        data: {
          labels: ['All time', 'Last 30 days', 'Last 7 days', 'Last 24 hours', 'Last 5 minutes'],
          datasets: [{
            label: 'Unique Visitors',
            data: [<?= $counts['all'] ?>, <?= $counts['30d'] ?>, <?= $counts['7d'] ?>, <?= $counts['24h'] ?>, <?= $counts['5min'] ?>],
            backgroundColor: ['rgba(99,102,241,.7)','rgba(34,197,94,.7)','rgba(251,191,36,.7)','rgba(249,115,22,.7)','rgba(236,72,153,.7)'],
            borderColor:     ['rgba(99,102,241,1)', 'rgba(34,197,94,1)', 'rgba(251,191,36,1)', 'rgba(249,115,22,1)', 'rgba(236,72,153,1)'],
            borderWidth: 1
          }]
        },
        options: { scales: { y: { beginAtZero: true, ticks: { precision: 0 } } } }
      });
    </script>

    <hr style="margin-bottom:30px;">

    <!-- top pages -->
    <div class="bg-light border p-2">
        <h2>
            <svg xmlns="http://www.w3.org/2000/svg" style="vertical-align:middle;" width="1.2em" height="1.2em" viewBox="0 0 24 24"><rect width="24" height="24" fill="none"/><path fill="#707070" d="M18 20.5a.5.5 0 0 0 .5-.5V10H14a2 2 0 0 1-2-2V3.5H6a.5.5 0 0 0-.5.5v5.25a.75.75 0 0 1-1.5 0V4a2 2 0 0 1 2-2h6.172c.515 0 1.047.22 1.413.586l5.829 5.828A2 2 0 0 1 20 9.828V20a2 2 0 0 1-2 2H6a2 2 0 0 1-2-2v-3.75a.75.75 0 0 1 1.5 0V20a.5.5 0 0 0 .5.5zm-.622-12L13.5 4.621V8a.5.5 0 0 0 .5.5z"/></svg>
            Most Popular Pages
        </h2>
    </div>
    <table class="table">
        <?php foreach ($pages as $page): ?>
        <tr>
            <td>
                🏠 <b><?= htmlspecialchars($page['url'], ENT_QUOTES, 'UTF-8') ?></b>
                &nbsp;–&nbsp; total visits: <b style="background:#FFD700;padding:0 3px;border-radius:3px;"><?= (int)$page['total_visits'] ?></b>
                &nbsp;–&nbsp; unique visitors: <b style="background:#8CDAF8;padding:0 3px;border-radius:3px;"><?= (int)$page['unique_visitors'] ?></b>
            </td>
        </tr>
        <?php endforeach; ?>
    </table>

    <footer id="paypal">
        <hr>
        <p style="margin:20px 0 0">Made with ❤️ especially for "<b><?= htmlspecialchars($USR) ?></b>".
        Is this plugin useful to you?
        <a href="https://getsimple-ce.ovh/donate" target="_blank" class="donateButton">Buy Us A Coffee ☕</a></p>
    </footer>
    <?php
}

// ════════════════════════════════════════════════════════════
//  EXCEL EXPORT  (pure PHP – ZipArchive + SpreadsheetML)
// ════════════════════════════════════════════════════════════

function easyStats_exportXlsx(): void {
    $db  = easyStats_initDb();
    $now = time();

    // Summary data
    $ranges = [
        'All time'       => 0,
        'Last 30 days'   => $now - 30 * 86400,
        'Last 7 days'    => $now -  7 * 86400,
        'Last 24 hours'  => $now -      86400,
        'Last 5 minutes' => $now -        300,
    ];

    $summaryRows = [];
    foreach ($ranges as $label => $since) {
        $stmt = $db->prepare('SELECT COUNT(DISTINCT ip) AS cnt FROM visitors WHERE timestamp >= :since');
        $stmt->bindValue(':since', $since, SQLITE3_INTEGER);
        $row           = $stmt->execute()->fetchArray(SQLITE3_ASSOC);
        $summaryRows[] = [$label, (int)($row['cnt'] ?? 0)];
    }

    // Pages data
    $result   = $db->query('
        SELECT   url,
                 COUNT(*)           AS total_visits,
                 COUNT(DISTINCT ip) AS unique_visitors
        FROM     pages
        GROUP BY url
        ORDER BY unique_visitors DESC
    ');
    $pageRows = [];
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $pageRows[] = [$row['url'], (int)$row['total_visits'], (int)$row['unique_visitors']];
    }
    $db->close();

    $summarySheet = easyStats_sheetXml(['Period', 'Unique Visitors'], $summaryRows);
    $pagesSheet   = easyStats_sheetXml(['URL', 'Total Visits', 'Unique Visitors'], $pageRows);

    $tmp = tempnam(sys_get_temp_dir(), 'xlsx');
    $zip = new ZipArchive();
    $zip->open($tmp, ZipArchive::OVERWRITE);

    $zip->addFromString('[Content_Types].xml', '<?xml version="1.0" encoding="UTF-8"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
  <Default Extension="xml"  ContentType="application/xml"/>
  <Override PartName="/xl/workbook.xml"          ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
  <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
  <Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
  <Override PartName="/xl/styles.xml"            ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
</Types>');

    $zip->addFromString('_rels/.rels', '<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>');

    $zip->addFromString('xl/_rels/workbook.xml.rels', '<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"  Target="worksheets/sheet1.xml"/>
  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"  Target="worksheets/sheet2.xml"/>
  <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"     Target="styles.xml"/>
</Relationships>');

    $zip->addFromString('xl/workbook.xml', '<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
          xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <sheets>
    <sheet name="Summary" sheetId="1" r:id="rId1"/>
    <sheet name="Pages"   sheetId="2" r:id="rId2"/>
  </sheets>
</workbook>');

    $zip->addFromString('xl/worksheets/sheet1.xml', $summarySheet);
    $zip->addFromString('xl/worksheets/sheet2.xml', $pagesSheet);

    $zip->addFromString('xl/styles.xml', '<?xml version="1.0" encoding="UTF-8"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <fonts count="1"><font><sz val="11"/><name val="Calibri"/></font></fonts>
  <fills count="2">
    <fill><patternFill patternType="none"/></fill>
    <fill><patternFill patternType="gray125"/></fill>
  </fills>
  <borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>
  <cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
  <cellXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/></cellXfs>
</styleSheet>');

    $zip->close();

    $filename = 'easyStats_' . date('Y-m-d_His') . '.xlsx';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Content-Length: ' . filesize($tmp));
    header('Cache-Control: max-age=0');
    readfile($tmp);
    unlink($tmp);
}

/**
 * Build a worksheet XML using inline strings (t="inlineStr") for text
 * and plain <v> for numbers. No shared strings needed.
 */
function easyStats_sheetXml(array $headers, array $rows): string {
    $cols = [];
    for ($i = 0; $i < count($headers); $i++) {
        $cols[] = chr(ord('A') + $i);
    }

    $xml  = '<?xml version="1.0" encoding="UTF-8"?>';
    $xml .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">';
    $xml .= '<sheetData>';

    // header row
    $xml .= '<row r="1">';
    foreach ($headers as $ci => $h) {
        $cell = $cols[$ci] . '1';
        $xml .= '<c r="' . $cell . '" t="inlineStr"><is><t>'
              . htmlspecialchars($h, ENT_XML1, 'UTF-8') . '</t></is></c>';
    }
    $xml .= '</row>';

    // data rows
    foreach ($rows as $ri => $row) {
        $rowNum = $ri + 2;
        $xml   .= '<row r="' . $rowNum . '">';
        foreach ($row as $ci => $val) {
            $cell = $cols[$ci] . $rowNum;
            if (is_numeric($val)) {
                $xml .= '<c r="' . $cell . '"><v>' . $val . '</v></c>';
            } else {
                $xml .= '<c r="' . $cell . '" t="inlineStr"><is><t>'
                      . htmlspecialchars((string)$val, ENT_XML1, 'UTF-8')
                      . '</t></is></c>';
            }
        }
        $xml .= '</row>';
    }

    $xml .= '</sheetData></worksheet>';
    return $xml;
}

// ════════════════════════════════════════════════════════════
//  THEME-FOOTER HOOK  (runs on every page view)
// ════════════════════════════════════════════════════════════

function makeEasyStats(): void {
    // Skip 404 pages
    if (http_response_code() === 404) return;

    $currentUrl = $_SERVER['REQUEST_URI'] ?? '';

    // Skip GetSimple internal pages
    if (strpos($currentUrl, '?search=') !== false
     || strpos($currentUrl, '?is=')     !== false) return;

    $currentUrl = filter_var($currentUrl, FILTER_SANITIZE_URL);
    $ipAddress  = hash('sha256', $_SERVER['REMOTE_ADDR'] ?? '');
    $now        = time();

    $db = easyStats_initDb();
    $db->exec('BEGIN IMMEDIATE;');

    // ── visitors table ───────────────────────────────────────
    // Store ONE row per visit (with individual timestamp).
    // This makes COUNT(DISTINCT ip) work correctly for every time window.
    //
    // Deduplication: skip if same IP visited within the last 30 minutes
    // (prevents page-refresh spam inflating counts).
    $thirtyMinsAgo = $now - 1800;
    $stmt = $db->prepare('
        SELECT id FROM visitors
        WHERE ip = :ip AND timestamp >= :since
        LIMIT 1
    ');
    $stmt->bindValue(':ip',    $ipAddress,     SQLITE3_TEXT);
    $stmt->bindValue(':since', $thirtyMinsAgo, SQLITE3_INTEGER);
    $recentVisit = $stmt->execute()->fetchArray(SQLITE3_ASSOC);

    if (!$recentVisit) {
        $stmt = $db->prepare('INSERT INTO visitors (ip, timestamp) VALUES (:ip, :ts)');
        $stmt->bindValue(':ip', $ipAddress, SQLITE3_TEXT);
        $stmt->bindValue(':ts', $now,       SQLITE3_INTEGER);
        $stmt->execute();
    }

    // Prune visitors older than 1 year, but keep the latest row per IP
    // so the "all time" unique count is never lost.
    $oneYearAgo = $now - 365 * 86400;
    $db->exec("
        DELETE FROM visitors
        WHERE timestamp < {$oneYearAgo}
        AND id NOT IN (
            SELECT MAX(id) FROM visitors GROUP BY ip
        )
    ");

    // ── pages table ──────────────────────────────────────────
    // Every page hit stored individually.
    // COUNT(*)           = total visits (including revisits by same IP)
    // COUNT(DISTINCT ip) = unique visitors per page
    $stmt = $db->prepare('INSERT INTO pages (url, ip, timestamp) VALUES (:url, :ip, :ts)');
    $stmt->bindValue(':url', $currentUrl, SQLITE3_TEXT);
    $stmt->bindValue(':ip',  $ipAddress,  SQLITE3_TEXT);
    $stmt->bindValue(':ts',  $now,        SQLITE3_INTEGER);
    $stmt->execute();

    $db->exec('COMMIT;');
    $db->close();
}