YogiBook - Statistiche

getConnection(); if (!function_exists('e')) { function e($value): string { return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8'); } } function fetchAllQ(PDO $pdo, string $sql, array $params = []): array { $st = $pdo->prepare($sql); $st->execute($params); return $st->fetchAll(); } function pct($num, $den): ?float { return $den > 0 ? round($num / $den * 100, 1) : null; } function fmtPct(?float $v): string { return $v === null ? '–' : number_format($v, 0, ',', '.') . '%'; } function n($v): string { return number_format((float) $v, 0, ',', '.'); } function kpi(string $icon, string $color, string $value, string $label, string $sub = ''): string { return '

' . '

' . '

' . $value . '

' . '

' . $label . '

' . ($sub !== '' ? '

' . $sub . '

' : '') . '

'; } function fillColor(?float $p): string { if ($p === null) return '#d5d9e0'; if ($p >= 75) return '#1ebf73'; if ($p >= 45) return '#f1b40f'; return '#e67e22'; } /* =========================================================== PERIODO */ $validDate = function ($d): bool { if (!is_string($d) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $d)) return false; [$y, $m, $dd] = array_map('intval', explode('-', $d)); return checkdate($m, $dd, $y); }; $presets = [ 'month' => 'Questo mese', 'lastmonth' => 'Mese scorso', '3m' => 'Ultimi 3 mesi', '12m' => 'Ultimi 12 mesi', 'year' => "Quest'anno", ]; $preset = $_GET['preset'] ?? ''; $fromIn = $_GET['from'] ?? ''; $toIn = $_GET['to'] ?? ''; $from = $to = null; if (($preset === 'custom' || $preset === '') && $validDate($fromIn) && $validDate($toIn)) { $preset = 'custom'; $from = $fromIn; $to = $toIn; } if ($from === null) { if (!isset($presets[$preset])) $preset = '3m'; $firstThis = new DateTime('first day of this month'); switch ($preset) { case 'month': $from = $firstThis->format('Y-m-d'); $to = $firstThis->format('Y-m-t'); break; case 'lastmonth': $lm = (clone $firstThis)->modify('-1 month'); $from = $lm->format('Y-m-d'); $to = $lm->format('Y-m-t'); break; case '12m': $from = (clone $firstThis)->modify('-11 months')->format('Y-m-d'); $to = $firstThis->format('Y-m-t'); break; case 'year': $from = date('Y-01-01'); $to = date('Y-12-31'); break; default: // 3m $from = (clone $firstThis)->modify('-2 months')->format('Y-m-d'); $to = $firstThis->format('Y-m-t'); } } if ($from > $to) { [$from, $to] = [$to, $from]; } $d1 = $from . ' 00:00:00'; $d2 = date('Y-m-d', strtotime($to . ' +1 day')) . ' 00:00:00'; $now = date('Y-m-d H:i:s'); /* =========================================================== FILTRI */ $fService = (int) ($_GET['service'] ?? 0); $fCat = (int) ($_GET['category'] ?? 0); $fTeacher = (int) ($_GET['teacher'] ?? 0); $topLimit = in_array((int) ($_GET['top'] ?? 10), [10, 25, 50], true) ? (int) ($_GET['top'] ?? 10) : 10; $sortMap = [ 'attended' => ['Lezioni frequentate', 'attended DESC, upcoming DESC'], 'lost' => ['Lezioni perse', 'lost DESC, attended DESC'], 'reprog' => ['Riprogrammazioni', 'reprog DESC, attended DESC'], 'cancelled' => ['Cancellazioni', 'cancelled DESC, attended DESC'], ]; $topSort = isset($sortMap[$_GET['sort'] ?? '']) ? $_GET['sort'] : 'attended'; $sf = ''; $sp = []; if ($fService > 0) { $sf .= ' AND s.idservice = :f_service'; $sp[':f_service'] = $fService; } if ($fCat > 0) { $sf .= ' AND s.category = :f_cat'; $sp[':f_cat'] = $fCat; } if ($fTeacher > 0) { $sf .= ' AND s.idteacher = :f_teacher'; $sp[':f_teacher'] = $fTeacher; } $P = [':d1' => $d1, ':d2' => $d2] + $sp; /* =========================================================== DATI FILTRI */ $services = fetchAllQ($pdo, "SELECT idservice, servicename, day, time FROM service ORDER BY servicename, day, time"); $categories = fetchAllQ($pdo, "SELECT idservicecategory, namecategory FROM servicecategory ORDER BY namecategory"); $teachers = fetchAllQ($pdo, "SELECT DISTINCT e.idemployee, e.nameemployee, e.surnameemployee FROM employee e JOIN service s ON s.idteacher = e.idemployee ORDER BY e.surnameemployee, e.nameemployee"); /* =========================================================== ORDINI */ $orderRows = fetchAllQ($pdo, "SELECT o.order_date_created AS d, o.nticket, o.iduser, o.reprogrammed FROM orderbook o LEFT JOIN service s ON o.idservice = s.idservice WHERE o.order_date_created >= :d1 AND o.order_date_created < :d2 $sf", $P); $ordersCount = count($orderRows); $ticketsSold = 0; $reprogCounter = 0; $buyers = []; foreach ($orderRows as $o) { $ticketsSold += (int) $o['nticket']; $reprogCounter += (int) $o['reprogrammed']; if (!empty($o['iduser'])) $buyers[(int) $o['iduser']] = true; } $buyersCount = count($buyers); // Nuovi clienti: persone il cui PRIMO ordine in assoluto cade nel periodo $newCustomers = (int) (fetchAllQ($pdo, "SELECT COUNT(*) AS c FROM ( SELECT iduser, MIN(order_date_created) AS first_order FROM orderbook WHERE iduser IS NOT NULL GROUP BY iduser ) t WHERE t.first_order >= :d1 AND t.first_order < :d2", [':d1' => $d1, ':d2' => $d2])[0]['c'] ?? 0); /* =========================================================== LEZIONI (KPI) */ $lessonBase = "FROM bookingclass bc JOIN serviceschedule ss ON bc.idserviceschedule = ss.idserviceschedule JOIN service s ON ss.idservice = s.idservice WHERE ss.dateschedule >= :d1 AND ss.dateschedule < :d2 $sf"; $k = fetchAllQ($pdo, "SELECT COALESCE(SUM(bc.status = 'booked' AND bc.lostlesson = 'N'), 0) AS booked, COALESCE(SUM(bc.status = 'booked' AND bc.lostlesson = 'N' AND ss.dateschedule <= :now), 0) AS attended, COALESCE(SUM(bc.lostlesson = 'Y' AND bc.status <> 'cancelled'), 0) AS lost, COALESCE(SUM(bc.status = 'cancelled'), 0) AS cancelled, COALESCE(SUM(bc.status = 'pending'), 0) AS pending, COALESCE(SUM(bc.status = 'waiting'), 0) AS waiting, COALESCE(SUM(bc.is_reprogrammed = 'Y' AND bc.status <> 'cancelled'), 0) AS reprog, COALESCE(SUM(bc.is_gift = 'Y' AND bc.status = 'booked'), 0) AS gifts, COUNT(DISTINCT CASE WHEN bc.status = 'booked' THEN bc.iduser END) AS people $lessonBase", $P + [':now' => $now])[0]; /* =========================================================== SESSIONI (riempimento) */ $sessions = fetchAllQ($pdo, "SELECT ss.idserviceschedule, ss.dateschedule, s.idservice, s.servicename, s.colorclass, s.maxcapacity, s.day, s.time, COUNT(CASE WHEN bc.status = 'booked' THEN 1 END) AS booked, COUNT(CASE WHEN bc.status = 'booked' AND bc.lostlesson = 'N' THEN 1 END) AS present, COUNT(CASE WHEN bc.lostlesson = 'Y' AND bc.status <> 'cancelled' THEN 1 END) AS lost, COUNT(CASE WHEN bc.status = 'cancelled' THEN 1 END) AS cancelled, COUNT(CASE WHEN bc.status = 'waiting' THEN 1 END) AS waiting, COUNT(CASE WHEN bc.is_reprogrammed = 'Y' AND bc.status <> 'cancelled' THEN 1 END) AS reprog FROM serviceschedule ss JOIN service s ON ss.idservice = s.idservice LEFT JOIN bookingclass bc ON bc.idserviceschedule = ss.idserviceschedule WHERE ss.dateschedule >= :d1 AND ss.dateschedule < :d2 $sf GROUP BY ss.idserviceschedule, ss.dateschedule, s.idservice, s.servicename, s.colorclass, s.maxcapacity, s.day, s.time ORDER BY ss.dateschedule", $P); /* =========================================================== BUCKET TEMPORALI */ $rangeDays = (int) round((strtotime($to) - strtotime($from)) / 86400) + 1; $weekly = $rangeDays <= 62; // periodi brevi: per settimana, altrimenti per mese $monthsShort = ['', 'Gen', 'Feb', 'Mar', 'Apr', 'Mag', 'Giu', 'Lug', 'Ago', 'Set', 'Ott', 'Nov', 'Dic']; $bucketKey = function (string $date) use ($weekly): string { $ts = strtotime($date); if ($weekly) { $dow = (int) date('N', $ts); return date('Y-m-d', strtotime('-' . ($dow - 1) . ' days', $ts)); } return date('Y-m', $ts); }; $buckets = []; $cursor = strtotime($weekly ? $bucketKey($from) : date('Y-m-01', strtotime($from))); $endTs = strtotime($to); while ($cursor <= $endTs) { $key = $weekly ? date('Y-m-d', $cursor) : date('Y-m', $cursor); $buckets[$key] = [ 'label' => $weekly ? date('d/m', $cursor) : $monthsShort[(int) date('n', $cursor)] . ' ' . date('y', $cursor), 'orders' => 0, 'tickets' => 0, 'present' => 0, 'lost' => 0, 'cancelled' => 0, 'reprog' => 0, 'cap_past' => 0, 'booked_past' => 0, ]; $cursor = strtotime($weekly ? '+1 week' : '+1 month', $cursor); } foreach ($orderRows as $o) { $bk = $bucketKey($o['d']); if (isset($buckets[$bk])) { $buckets[$bk]['orders']++; $buckets[$bk]['tickets'] += (int) $o['nticket']; } } /* =========================================================== AGGREGAZIONE PER CLASSE */ $byClass = []; $totCapPast = $totBookedPast = $totSessions = $totPast = $totFull = 0; foreach ($sessions as $s) { $sid = (int) $s['idservice']; $isPast = $s['dateschedule'] <= $now; $cap = (int) $s['maxcapacity']; $booked = (int) $s['booked']; if (!isset($byClass[$sid])) { $byClass[$sid] = [ 'name' => $s['servicename'], 'color' => $s['colorclass'] ?: '#1ebf73', 'day' => $s['day'], 'time' => $s['time'], 'sessions' => 0, 'past' => 0, 'booked' => 0, 'booked_past' => 0, 'present_past' => 0, 'cap_past' => 0, 'full' => 0, 'lost' => 0, 'cancelled' => 0, 'reprog' => 0, 'waiting' => 0, ]; } $c = &$byClass[$sid]; $c['sessions']++; $c['booked'] += $booked; $c['lost'] += (int) $s['lost']; $c['cancelled'] += (int) $s['cancelled']; $c['reprog'] += (int) $s['reprog']; $c['waiting'] += (int) $s['waiting']; $totSessions++; if ($isPast) { $c['past']++; $c['booked_past'] += $booked; $c['present_past'] += (int) $s['present']; $totPast++; if ($cap > 0) { $c['cap_past'] += $cap; $totCapPast += $cap; $totBookedPast += $booked; if ($booked >= $cap) { $c['full']++; $totFull++; } } } unset($c); $bk = $bucketKey($s['dateschedule']); if (isset($buckets[$bk])) { $buckets[$bk]['present'] += (int) $s['present']; $buckets[$bk]['lost'] += (int) $s['lost']; $buckets[$bk]['cancelled'] += (int) $s['cancelled']; $buckets[$bk]['reprog'] += (int) $s['reprog']; if ($isPast && $cap > 0) { $buckets[$bk]['cap_past'] += $cap; $buckets[$bk]['booked_past'] += $booked; } } } foreach ($byClass as &$c) { $c['fill'] = pct($c['booked_past'], $c['cap_past']); $c['avg'] = $c['past'] > 0 ? round($c['present_past'] / $c['past'], 1) : null; } unset($c); uasort($byClass, function ($a, $b) { return ($b['fill'] ?? -1) <=> ($a['fill'] ?? -1); }); $fillTotal = pct($totBookedPast, $totCapPast); $lostRate = pct((int) $k['lost'], (int) $k['booked'] + (int) $k['lost']); /* =========================================================== CLASSIFICA UTENTI */ $topUsers = fetchAllQ($pdo, "SELECT u.id, u.first_name, u.last_name, u.email, COALESCE(SUM(bc.status = 'booked' AND bc.lostlesson = 'N' AND ss.dateschedule <= :now1), 0) AS attended, COALESCE(SUM(bc.status = 'booked' AND bc.lostlesson = 'N' AND ss.dateschedule > :now2), 0) AS upcoming, COALESCE(SUM(bc.lostlesson = 'Y' AND bc.status <> 'cancelled'), 0) AS lost, COALESCE(SUM(bc.is_reprogrammed = 'Y' AND bc.status <> 'cancelled'), 0) AS reprog, COALESCE(SUM(bc.status = 'cancelled'), 0) AS cancelled, MAX(CASE WHEN bc.status = 'booked' AND bc.lostlesson = 'N' AND ss.dateschedule <= :now3 THEN ss.dateschedule END) AS last_lesson FROM bookingclass bc JOIN serviceschedule ss ON bc.idserviceschedule = ss.idserviceschedule JOIN service s ON ss.idservice = s.idservice JOIN auth_users u ON u.id = bc.iduser WHERE ss.dateschedule >= :d1 AND ss.dateschedule < :d2 $sf GROUP BY u.id, u.first_name, u.last_name, u.email HAVING (attended + upcoming + lost + cancelled) > 0 ORDER BY " . $sortMap[$topSort][1] . ", u.last_name, u.first_name LIMIT " . $topLimit, $P + [':now1' => $now, ':now2' => $now, ':now3' => $now]); /* =========================================================== DATI GRAFICI */ $chart = [ 'labels' => [], 'orders' => [], 'tickets' => [], 'present' => [], 'lost' => [], 'cancelled' => [], 'reprog' => [], 'fill' => [], 'classLabels' => [], 'classFill' => [], 'classColors' => [], ]; foreach ($buckets as $b) { $chart['labels'][] = $b['label']; $chart['orders'][] = $b['orders']; $chart['tickets'][] = $b['tickets']; $chart['present'][] = $b['present']; $chart['lost'][] = $b['lost']; $chart['cancelled'][] = $b['cancelled']; $chart['reprog'][] = $b['reprog']; $chart['fill'][] = pct($b['booked_past'], $b['cap_past']); } foreach ($byClass as $c) { if ($c['fill'] === null) continue; $chart['classLabels'][] = $c['name'] . ($c['day'] ? ' · ' . $c['day'] . ' ' . $c['time'] : ''); $chart['classFill'][] = $c['fill']; $chart['classColors'][] = $c['color']; } /* =========================================================== LINK FILTRI */ $baseFilters = array_filter([ 'service' => $fService ?: null, 'category' => $fCat ?: null, 'teacher' => $fTeacher ?: null, 'top' => $topLimit !== 10 ? $topLimit : null, 'sort' => $topSort !== 'attended' ? $topSort : null, ]); $periodLabel = date('d/m/Y', strtotime($from)) . ' – ' . date('d/m/Y', strtotime($to)); ?> YogiBook - Statistiche

$plabel) : ?> Personalizzato

Dal

Al

Classe

Categoria

1) : ?>

Insegnante

Azzera

Periodo: · filtri attivi

Ordini

Lezioni e persone

0 ? ' · ' . n($k['gifts']) . ' omaggio' : '')); echo kpi('fas fa-chart-pie', fillColor($fillTotal), fmtPct($fillTotal), 'Riempimento medio', $totPast > 0 ? 'su ' . n($totPast) . ' lezioni svolte' : 'nessuna lezione svolta'); echo kpi('fas fa-fire', '#c0392b', n($totFull), 'Lezioni al completo', $totPast > 0 ? fmtPct(pct($totFull, $totPast)) . ' delle lezioni svolte' : ''); ?>

Riprogrammazioni, perse e cancellate

Andamento lezioni

Andamento ordini

Riempimento nel tempo lezioni svolte

Riempimento per classe lezioni svolte

Nessuna lezione svolta nel periodo.

Dettaglio per classe ordinato per riempimento

Nessuna classe programmata nel periodo.

0, 'sessions' => 0, 'booked' => 0, 'present_past' => 0, 'full' => 0, 'lost' => 0, 'cancelled' => 0, 'reprog' => 0, 'waiting' => 0]; foreach ($byClass as $c) : foreach ($sum as $sk => $_) $sum[$sk] += $c[$sk]; ?>

Classe	Lezioni svolte / tot.	Prenotazioni	Media presenti	Riempimento	Al completo	Perse	Cancellate	Riprogr.	In coda
	/
Totale	/		0 ? number_format($sum['present_past'] / $sum['past'], 1, ',', '.') : '–'; ?>

Prenotazioni = posti occupati (incluse le perse). Media presenti e riempimento considerano solo le lezioni già svolte.

Classifica utenti nel periodo

Ordina per

Nessuna prenotazione nel periodo.

$u) : $pos = $i + 1; $presence = pct((int) $u['attended'], (int) $u['attended'] + (int) $u['lost']); ?>

#	Utente	Frequentate	Prenotate future	Perse	Riprogr.	Cancellate	Presenza	Ultima lezione
				0 ? '' . n($u['lost']) . '' : '0'; ?>

Presenza = lezioni frequentate / (frequentate + perse).