Ty Clifford
03348cad79
Key areas: [OrderStatusService.php](/Users/tyemeclifford/Documents/GH/fatbottomgrille/app/Services/OrderStatusService.php), [AdminController.php](/Users/tyemeclifford/Documents/GH/fatbottomgrille/app/Controllers/AdminController.php), and [Database.php](/Users/tyemeclifford/Documents/GH/fatbottomgrille/app/Core/Database.php). SQLite migration applied successfully. PHP/JS syntax, integration workflows, rendered pages, database integrity, and tracker authorization all passed. Invalid tracker tokens correctly return 403.
101 lines
4.3 KiB
PHP
101 lines
4.3 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace FatBottom\Services;
|
|
|
|
use FatBottom\Core\Database;
|
|
|
|
final class StatsService
|
|
{
|
|
public function __construct(private readonly Database $db)
|
|
{
|
|
}
|
|
|
|
public function record(string $route, ?int $userId): void
|
|
{
|
|
if (str_starts_with($route, '/assets/')) {
|
|
return;
|
|
}
|
|
$ip = $_SERVER['REMOTE_ADDR'] ?? '';
|
|
$this->db->execute(
|
|
'INSERT INTO visitor_events (session_id, user_id, route, referrer, user_agent, ip_hash)
|
|
VALUES (?, ?, ?, ?, ?, ?)',
|
|
[
|
|
session_id(),
|
|
$userId,
|
|
substr($route, 0, 255),
|
|
substr((string) ($_SERVER['HTTP_REFERER'] ?? ''), 0, 500),
|
|
substr((string) ($_SERVER['HTTP_USER_AGENT'] ?? ''), 0, 500),
|
|
hash('sha256', $ip . date('Y-m-d')),
|
|
]
|
|
);
|
|
}
|
|
|
|
public function dashboard(): array
|
|
{
|
|
if (!$this->db->isSqlite()) {
|
|
return [
|
|
'today_orders' => (int) $this->db->fetch(
|
|
'SELECT COUNT(*) AS value FROM orders WHERE DATE(placed_at) = CURRENT_DATE'
|
|
)['value'],
|
|
'today_sales' => (int) $this->db->fetch(
|
|
"SELECT COALESCE(SUM(o.total_cents), 0) - COALESCE(SUM(r.refunded_cents), 0) AS value
|
|
FROM orders o
|
|
LEFT JOIN (
|
|
SELECT order_id, SUM(amount_cents) AS refunded_cents FROM refunds GROUP BY order_id
|
|
) r ON r.order_id = o.id
|
|
WHERE o.payment_status IN ('paid', 'partially_refunded', 'refunded')
|
|
AND DATE(o.placed_at) = CURRENT_DATE"
|
|
)['value'],
|
|
'open_orders' => (int) $this->db->fetch(
|
|
"SELECT COUNT(*) AS value FROM orders WHERE status IN ('pending', 'accepted', 'processing', 'ready')"
|
|
)['value'],
|
|
'abandoned_carts' => (int) $this->db->fetch(
|
|
"SELECT COUNT(*) AS value FROM carts
|
|
WHERE status = 'active' AND subtotal_cents > 0
|
|
AND last_seen_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE)"
|
|
)['value'],
|
|
'today_visitors' => (int) $this->db->fetch(
|
|
'SELECT COUNT(DISTINCT session_id) AS value FROM visitor_events
|
|
WHERE DATE(created_at) = CURRENT_DATE'
|
|
)['value'],
|
|
'newsletter_members' => (int) $this->db->fetch(
|
|
'SELECT COUNT(*) AS value FROM users WHERE newsletter_opt_in = 1 AND active = 1'
|
|
)['value'],
|
|
];
|
|
}
|
|
|
|
return [
|
|
'today_orders' => (int) $this->db->fetch(
|
|
"SELECT COUNT(*) AS value FROM orders
|
|
WHERE date(placed_at, 'localtime') = date('now', 'localtime')"
|
|
)['value'],
|
|
'today_sales' => (int) $this->db->fetch(
|
|
"SELECT COALESCE(SUM(o.total_cents), 0) - COALESCE(SUM(r.refunded_cents), 0) AS value
|
|
FROM orders o
|
|
LEFT JOIN (
|
|
SELECT order_id, SUM(amount_cents) AS refunded_cents FROM refunds GROUP BY order_id
|
|
) r ON r.order_id = o.id
|
|
WHERE o.payment_status IN ('paid', 'partially_refunded', 'refunded')
|
|
AND date(o.placed_at, 'localtime') = date('now', 'localtime')"
|
|
)['value'],
|
|
'open_orders' => (int) $this->db->fetch(
|
|
"SELECT COUNT(*) AS value FROM orders WHERE status IN ('pending', 'accepted', 'processing', 'ready')"
|
|
)['value'],
|
|
'abandoned_carts' => (int) $this->db->fetch(
|
|
"SELECT COUNT(*) AS value FROM carts
|
|
WHERE status = 'active' AND subtotal_cents > 0
|
|
AND last_seen_at < datetime('now', '-30 minutes')"
|
|
)['value'],
|
|
'today_visitors' => (int) $this->db->fetch(
|
|
"SELECT COUNT(DISTINCT session_id) AS value FROM visitor_events
|
|
WHERE date(created_at, 'localtime') = date('now', 'localtime')"
|
|
)['value'],
|
|
'newsletter_members' => (int) $this->db->fetch(
|
|
'SELECT COUNT(*) AS value FROM users WHERE newsletter_opt_in = 1 AND active = 1'
|
|
)['value'],
|
|
];
|
|
}
|
|
}
|