v1.0.0.0

StocketBase

← Changes All versions

96 lines · 4.9 KB

<?php
declare(strict_types=1);
if (count(get_included_files()) === 1) {
http_response_code(403);
exit;
}
require_once __DIR__ . '/../includes/currency.php';
$db = Database::site();
$isAdmin = Auth::isAdministrative();
$storeCurrency = SiteFront::settings()['store_currency'] ?? 'USD';
if ($isAdmin) {
$topProducts = $db->query(
'SELECT title, views_count, sales_count, status, (SELECT COALESCE(SUM(f.download_count), 0) FROM product_files f WHERE f.product_id = products.id) AS downloads_count FROM products WHERE deleted_at IS NULL ORDER BY sales_count DESC, views_count DESC LIMIT 10'
)->fetchAll();
$totalViews = (int) $db->query('SELECT COALESCE(SUM(views_count), 0) AS total FROM products WHERE deleted_at IS NULL')->fetch()['total'];
$totalSales = (int) $db->query('SELECT COALESCE(SUM(sales_count), 0) AS total FROM products WHERE deleted_at IS NULL')->fetch()['total'];
$totalDownloads = (int) $db->query('SELECT COALESCE(SUM(download_count), 0) AS total FROM product_files')->fetch()['total'];
} else {
$statement = $db->prepare(
'SELECT title, views_count, sales_count, status, (SELECT COALESCE(SUM(f.download_count), 0) FROM product_files f WHERE f.product_id = products.id) AS downloads_count FROM products WHERE created_by = :id AND deleted_at IS NULL ORDER BY sales_count DESC, views_count DESC LIMIT 10'
);
$statement->execute(['id' => $currentUser['id']]);
$topProducts = $statement->fetchAll();
$totalStatement = $db->prepare('SELECT COALESCE(SUM(views_count), 0) AS total FROM products WHERE created_by = :id AND deleted_at IS NULL');
$totalStatement->execute(['id' => $currentUser['id']]);
$totalViews = (int) $totalStatement->fetch()['total'];
$salesStatement = $db->prepare('SELECT COALESCE(SUM(sales_count), 0) AS total FROM products WHERE created_by = :id AND deleted_at IS NULL');
$salesStatement->execute(['id' => $currentUser['id']]);
$totalSales = (int) $salesStatement->fetch()['total'];
$downloadsStatement = $db->prepare('SELECT COALESCE(SUM(f.download_count), 0) AS total FROM product_files f JOIN products p ON p.id = f.product_id WHERE p.created_by = :id');
$downloadsStatement->execute(['id' => $currentUser['id']]);
$totalDownloads = (int) $downloadsStatement->fetch()['total'];
}
$isManager = Auth::hasRoleAtLeast(Auth::ROLE_STORE_MANAGER);
$revenueByDay = [];
if ($isManager) {
$usersDb = Database::users();
$revenueByDay = $usersDb->query(
"SELECT DATE(created_at) AS day, COALESCE(SUM(total_cents), 0) AS total
FROM orders WHERE status IN ('paid','processing','fulfilled','shipped','completed') AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at) ORDER BY day ASC"
)->fetchAll();
}
?>
<h1 class="dash-title"><?= Icons::icon("stats", "icon icon-lg") ?>Statistics</h1>
<div class="dash-cards">
<div class="dash-card"><?= Icons::icon('eye', 'icon icon-lg') ?><div class="dash-card-value"><?= number_format($totalViews) ?></div><div class="dash-card-label">Total views</div></div>
<div class="dash-card"><?= Icons::icon('cart', 'icon icon-lg') ?><div class="dash-card-value"><?= number_format($totalSales) ?></div><div class="dash-card-label">Total sales</div></div>
<div class="dash-card"><?= Icons::icon('download', 'icon icon-lg') ?><div class="dash-card-value"><?= number_format($totalDownloads) ?></div><div class="dash-card-label">Total downloads</div></div>
</div>
<?php if ($isManager && !empty($revenueByDay)): ?>
<h2 class="dash-subtitle"><?= Icons::icon('dollar', 'icon icon-sm') ?>Revenue (last 30 days)</h2>
<table class="dash-table">
<thead><tr><th><?= Icons::icon('clock', 'icon icon-sm') ?>Day</th><th><?= Icons::icon('dollar', 'icon icon-sm') ?>Revenue</th></tr></thead>
<tbody>
<?php foreach ($revenueByDay as $day): ?>
<tr>
<td><?= htmlspecialchars($day['day']) ?></td>
<td><?= htmlspecialchars(Currency::format((int) $day['total'], $storeCurrency)) ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php endif; ?>
<h2 class="dash-subtitle"><?= Icons::icon('star', 'icon icon-sm') ?>Top products</h2>
<table class="dash-table">
<thead><tr><th><?= Icons::icon('heading', 'icon icon-sm') ?>Title</th><th><?= Icons::icon('eye', 'icon icon-sm') ?>Views</th><th><?= Icons::icon('cart', 'icon icon-sm') ?>Sales</th><th><?= Icons::icon('download', 'icon icon-sm') ?>Downloads</th><th><?= Icons::icon('flag', 'icon icon-sm') ?>Status</th></tr></thead>
<tbody>
<?php foreach ($topProducts as $product): ?>
<tr>
<td><?= htmlspecialchars($product['title']) ?></td>
<td><?= number_format((int) $product['views_count']) ?></td>
<td><?= number_format((int) $product['sales_count']) ?></td>
<td><?= number_format((int) ($product['downloads_count'] ?? 0)) ?></td>
<td><span class="status-pill status-<?= htmlspecialchars($product['status']) ?>"><?= htmlspecialchars($product['status']) ?></span></td>
</tr>
<?php endforeach; ?>
<?php if (empty($topProducts)): ?>
<tr><td colspan="5">No data yet.</td></tr>
<?php endif; ?>
</tbody>
</table>