300 lines
19 KiB
PHP
300 lines
19 KiB
PHP
<?php
|
||
|
||
declare(strict_types=1);
|
||
|
||
final class DashboardController
|
||
{
|
||
public static function index(): void
|
||
{
|
||
$db = DB::pdo();
|
||
|
||
// Complète les anciennes quarantaines dont la date d’entrée était absente.
|
||
$db->exec("
|
||
UPDATE animals
|
||
SET intake_date = date(updated_at)
|
||
WHERE lower(trim(status))='quarantaine'
|
||
AND deleted_at IS NULL AND archived_at IS NULL
|
||
AND (intake_date IS NULL OR trim(intake_date)='')
|
||
AND updated_at IS NOT NULL AND trim(updated_at)<>''
|
||
");
|
||
|
||
$quarantineDays = AppSettings::int('quarantine_days', 1, 90);
|
||
$stmt = $db->prepare("
|
||
UPDATE animals
|
||
SET quarantine_until = date(intake_date, '+' || :days || ' day')
|
||
WHERE lower(trim(status))='quarantaine'
|
||
AND deleted_at IS NULL AND archived_at IS NULL
|
||
AND (quarantine_until IS NULL OR trim(quarantine_until)='')
|
||
AND intake_date IS NOT NULL AND trim(intake_date)<>''
|
||
");
|
||
$stmt->execute([':days' => $quarantineDays]);
|
||
|
||
// Stats par statut (avec tolérance accents)
|
||
$stats = $db
|
||
->query(
|
||
"
|
||
SELECT
|
||
SUM(CASE WHEN lower(status) NOT IN ('adopte','adopté','decede','décédé') THEN 1 ELSE 0 END) AS total,
|
||
SUM(CASE WHEN lower(status) NOT IN ('adopte','adopté','decede','décédé') AND birth_date IS NOT NULL AND trim(birth_date) != '' AND date(birth_date) > date('now','-12 months') THEN 1 ELSE 0 END) AS kittens,
|
||
SUM(CASE WHEN lower(status) NOT IN ('adopte','adopté','decede','décédé') AND birth_date IS NOT NULL AND trim(birth_date) != '' AND date(birth_date) <= date('now','-12 months') THEN 1 ELSE 0 END) AS adults,
|
||
SUM(CASE WHEN lower(status) NOT IN ('adopte','adopté','decede','décédé') AND (birth_date IS NULL OR trim(birth_date) = '') THEN 1 ELSE 0 END) AS age_unknown,
|
||
SUM(CASE WHEN status='refuge' THEN 1 ELSE 0 END) AS refuge,
|
||
SUM(CASE WHEN status='refuge' AND refuge_room='silver' THEN 1 ELSE 0 END) AS refuge_silver,
|
||
SUM(CASE WHEN status='refuge' AND refuge_room='twix' THEN 1 ELSE 0 END) AS refuge_twix,
|
||
SUM(CASE WHEN status='refuge' AND (refuge_room IS NULL OR trim(refuge_room)='') THEN 1 ELSE 0 END) AS refuge_unassigned,
|
||
SUM(CASE WHEN status='fa' THEN 1 ELSE 0 END) AS fa,
|
||
SUM(CASE WHEN status='fa_permanente' THEN 1 ELSE 0 END) AS fa_permanente,
|
||
SUM(CASE WHEN status='quarantaine' THEN 1 ELSE 0 END) AS quarantaine,
|
||
SUM(CASE WHEN status='soin' THEN 1 ELSE 0 END) AS soin,
|
||
SUM(CASE WHEN status='isolation' THEN 1 ELSE 0 END) AS isolation,
|
||
SUM(CASE WHEN status='hospitalise' OR status='hospitalisé' THEN 1 ELSE 0 END) AS hospitalise,
|
||
SUM(CASE WHEN status='reserve' OR status='réservé' THEN 1 ELSE 0 END) AS reserve,
|
||
SUM(CASE WHEN status='adopte' OR status='adopté' THEN 1 ELSE 0 END) AS adopte,
|
||
SUM(CASE WHEN status='decede' OR status='décédé' THEN 1 ELSE 0 END) AS dece,
|
||
SUM(CASE WHEN status='enfui' THEN 1 ELSE 0 END) AS enfui
|
||
FROM animals
|
||
WHERE deleted_at IS NULL AND archived_at IS NULL
|
||
",
|
||
)
|
||
->fetch();
|
||
$shelterRoomCounts = [];
|
||
$configuredRooms = ShelterRoomService::byCode(true);
|
||
$roomAnimals = $db
|
||
->query(
|
||
"SELECT status,refuge_room FROM animals WHERE deleted_at IS NULL AND archived_at IS NULL AND status IN ('refuge','soin','quarantaine')",
|
||
)
|
||
->fetchAll(PDO::FETCH_ASSOC);
|
||
foreach ($configuredRooms as $code => $room) {
|
||
$shelterRoomCounts[$code] = [
|
||
'name' => $room['name'],
|
||
'color' => $room['color'],
|
||
'status' => $room['status_code'],
|
||
'count' => 0,
|
||
];
|
||
}
|
||
foreach ($roomAnimals as $roomAnimal) {
|
||
$code = ShelterRoomService::resolveAnimal($roomAnimal, $configuredRooms);
|
||
if (isset($shelterRoomCounts[$code])) {
|
||
$shelterRoomCounts[$code]['count']++;
|
||
}
|
||
}
|
||
|
||
$quality =
|
||
$db
|
||
->query(
|
||
"SELECT COUNT(*) total,
|
||
SUM(CASE WHEN birth_date IS NULL OR trim(birth_date)='' THEN 1 ELSE 0 END) missing_birth_date,
|
||
SUM(CASE WHEN sex IS NULL OR trim(sex)='' OR sex='U' THEN 1 ELSE 0 END) missing_sex,
|
||
SUM(CASE WHEN color IS NULL OR trim(color)='' THEN 1 ELSE 0 END) missing_color,
|
||
SUM(CASE WHEN breed IS NULL OR trim(breed)='' THEN 1 ELSE 0 END) missing_breed,
|
||
SUM(CASE WHEN (rescue_location_name IS NULL OR trim(rescue_location_name)='') AND (rescue_address IS NULL OR trim(rescue_address)='') THEN 1 ELSE 0 END) missing_origin,
|
||
SUM(CASE WHEN (chip_id IS NULL OR trim(chip_id)='') AND (chip IS NULL OR trim(chip)='') THEN 1 ELSE 0 END) missing_chip,
|
||
SUM(CASE WHEN COALESCE(NULLIF(identification_registration_status,''),'unknown')='unknown' THEN 1 ELSE 0 END) unknown_identification_registration,
|
||
SUM(CASE WHEN COALESCE(NULLIF(adoption_availability,''),'unknown')='unknown' THEN 1 ELSE 0 END) unknown_adoption_availability,
|
||
SUM(CASE WHEN sterilization_status='no' THEN 1 ELSE 0 END) unsterilized,
|
||
SUM(CASE WHEN COALESCE(NULLIF(sterilization_status,''),'unknown')='unknown' THEN 1 ELSE 0 END) unknown_sterilization,
|
||
SUM(CASE WHEN birth_date IS NULL OR trim(birth_date)='' OR sex IS NULL OR trim(sex)='' OR sex='U' OR color IS NULL OR trim(color)='' OR breed IS NULL OR trim(breed)='' OR ((rescue_location_name IS NULL OR trim(rescue_location_name)='') AND (rescue_address IS NULL OR trim(rescue_address)='')) OR ((chip_id IS NULL OR trim(chip_id)='') AND (chip IS NULL OR trim(chip)='')) OR COALESCE(NULLIF(identification_registration_status,''),'unknown')='unknown' OR COALESCE(NULLIF(adoption_availability,''),'unknown')='unknown' OR COALESCE(NULLIF(sterilization_status,''),'unknown')='unknown' THEN 1 ELSE 0 END) incomplete
|
||
FROM animals WHERE deleted_at IS NULL AND archived_at IS NULL AND lower(status) NOT IN ('adopte','adopté','decede','décédé','enfui','fugue')",
|
||
)
|
||
->fetch(PDO::FETCH_ASSOC) ?:
|
||
[];
|
||
|
||
// Activité
|
||
$activity = $db
|
||
->query(
|
||
"
|
||
SELECT
|
||
(SELECT COUNT(*) FROM medical_notes mn JOIN animals a ON a.id=mn.animal_id WHERE a.deleted_at IS NULL AND a.archived_at IS NULL AND date(mn.noted_at) >= date('now','-7 day')) AS notes_7d,
|
||
(SELECT COUNT(*) FROM vaccinations v JOIN animals a ON a.id=v.animal_id WHERE a.deleted_at IS NULL AND a.archived_at IS NULL AND v.done_date IS NOT NULL AND date(v.done_date) >= date('now','-30 day')) AS vaccines_30d,
|
||
(SELECT COUNT(*) FROM treatments t JOIN animals a ON a.id=t.animal_id WHERE a.deleted_at IS NULL AND a.archived_at IS NULL AND t.start_date IS NOT NULL AND date(t.start_date) >= date('now','-30 day')) AS treatments_30d
|
||
",
|
||
)
|
||
->fetch();
|
||
|
||
// Vaccins en retard
|
||
$stmt = $db->prepare("
|
||
SELECT a.id, a.name, a.internal_code AS code, rv.name AS vaccine_name, v.due_date
|
||
FROM vaccinations v
|
||
JOIN animals a ON a.id = v.animal_id
|
||
JOIN ref_vaccines rv ON rv.id = v.vaccine_id
|
||
WHERE v.due_date IS NOT NULL
|
||
AND a.deleted_at IS NULL AND a.archived_at IS NULL
|
||
AND lower(a.status) NOT IN ('decede','décédé')
|
||
AND date(v.due_date) < date('now')
|
||
ORDER BY date(v.due_date) ASC
|
||
LIMIT 50
|
||
");
|
||
$stmt->execute();
|
||
$vaccines_overdue = $stmt->fetchAll();
|
||
|
||
// Vaccins ≤ 7 jours
|
||
$stmt = $db->prepare("
|
||
SELECT a.id, a.name, a.internal_code AS code, rv.name AS vaccine_name, v.due_date
|
||
FROM vaccinations v
|
||
JOIN animals a ON a.id = v.animal_id
|
||
JOIN ref_vaccines rv ON rv.id = v.vaccine_id
|
||
WHERE v.due_date IS NOT NULL
|
||
AND a.deleted_at IS NULL AND a.archived_at IS NULL
|
||
AND lower(a.status) NOT IN ('decede','décédé')
|
||
AND date(v.due_date) >= date('now')
|
||
AND date(v.due_date) <= date('now', '+7 day')
|
||
ORDER BY date(v.due_date) ASC
|
||
LIMIT 50
|
||
");
|
||
$stmt->execute();
|
||
$vaccines_7d = $stmt->fetchAll();
|
||
|
||
// Vaccins ≤ 30 jours
|
||
$stmt = $db->prepare("
|
||
SELECT a.id, a.name, a.internal_code AS code, rv.name AS vaccine_name, v.due_date
|
||
FROM vaccinations v
|
||
JOIN animals a ON a.id = v.animal_id
|
||
JOIN ref_vaccines rv ON rv.id = v.vaccine_id
|
||
WHERE v.due_date IS NOT NULL
|
||
AND a.deleted_at IS NULL AND a.archived_at IS NULL
|
||
AND lower(a.status) NOT IN ('decede','décédé')
|
||
AND date(v.due_date) > date('now', '+7 day')
|
||
AND date(v.due_date) <= date('now', '+30 day')
|
||
ORDER BY date(v.due_date) ASC
|
||
LIMIT 50
|
||
");
|
||
$stmt->execute();
|
||
$vaccines_30d = $stmt->fetchAll();
|
||
|
||
// Traitements en cours
|
||
$stmt = $db->prepare('
|
||
SELECT a.id, a.name, a.internal_code AS code,
|
||
m.name AS medication_name,
|
||
t.start_date, t.end_date, t.dose_text, t.route
|
||
FROM treatments t
|
||
JOIN animals a ON a.id = t.animal_id
|
||
JOIN ref_medications m ON m.id = t.medication_id
|
||
WHERE t.ongoing = 1
|
||
AND a.deleted_at IS NULL AND a.archived_at IS NULL
|
||
ORDER BY date(t.start_date) DESC, a.name ASC
|
||
LIMIT 50
|
||
');
|
||
$stmt->execute();
|
||
$treatments = $stmt->fetchAll();
|
||
|
||
// Traitements à clôturer (fin dépassée mais ongoing=1)
|
||
$stmt = $db->prepare("
|
||
SELECT a.id, a.name, a.internal_code AS code, m.name AS medication_name, t.end_date
|
||
FROM treatments t
|
||
JOIN animals a ON a.id = t.animal_id
|
||
JOIN ref_medications m ON m.id = t.medication_id
|
||
WHERE t.ongoing = 1
|
||
AND a.deleted_at IS NULL AND a.archived_at IS NULL
|
||
AND t.end_date IS NOT NULL
|
||
AND date(t.end_date) < date('now')
|
||
ORDER BY date(t.end_date) ASC
|
||
LIMIT 50
|
||
");
|
||
$stmt->execute();
|
||
$treatments_overdue = $stmt->fetchAll();
|
||
|
||
// Dernières notes médicales
|
||
$stmt = $db->prepare('
|
||
SELECT mn.noted_at, mn.kind, mn.reason,
|
||
a.id AS animal_id, a.name, a.internal_code AS code
|
||
FROM medical_notes mn
|
||
JOIN animals a ON a.id = mn.animal_id
|
||
WHERE a.deleted_at IS NULL AND a.archived_at IS NULL
|
||
ORDER BY datetime(mn.noted_at) DESC, mn.id DESC
|
||
LIMIT 30
|
||
');
|
||
$stmt->execute();
|
||
$recent_notes = $stmt->fetchAll();
|
||
|
||
// Quarantaine : liste + J-... (si dates renseignées)
|
||
$stmt = $db->prepare("
|
||
SELECT
|
||
id, name, internal_code AS code,
|
||
intake_date,
|
||
quarantine_until,
|
||
CASE
|
||
WHEN quarantine_until IS NULL OR quarantine_until = '' THEN NULL
|
||
ELSE CAST(julianday(date(quarantine_until)) - julianday(date('now','localtime')) AS INT)
|
||
END AS days_left
|
||
FROM animals
|
||
WHERE lower(trim(status))='quarantaine'
|
||
AND deleted_at IS NULL AND archived_at IS NULL
|
||
ORDER BY
|
||
CASE
|
||
WHEN quarantine_until IS NULL OR quarantine_until = '' THEN 9999
|
||
ELSE days_left
|
||
END ASC,
|
||
name ASC
|
||
LIMIT 100
|
||
");
|
||
|
||
$stmt->execute();
|
||
$quarantine = $stmt->fetchAll();
|
||
|
||
$stmt = $db->prepare('
|
||
SELECT
|
||
a.id, a.name, a.internal_code AS code,
|
||
wa.last_weight, wa.w30_weight, wa.w30_date,
|
||
ROUND(((wa.last_weight - wa.w30_weight) / wa.w30_weight) * 100, 1) AS pct
|
||
FROM v_animals_weight_alerts wa
|
||
JOIN animals a ON a.id = wa.animal_id
|
||
WHERE wa.last_weight IS NOT NULL
|
||
AND a.deleted_at IS NULL AND a.archived_at IS NULL
|
||
AND wa.w30_weight IS NOT NULL
|
||
AND wa.w30_weight > 0
|
||
AND ABS((wa.last_weight - wa.w30_weight) / wa.w30_weight) >= 0.15
|
||
ORDER BY ABS((wa.last_weight - wa.w30_weight) / wa.w30_weight) DESC
|
||
LIMIT 50
|
||
');
|
||
$stmt->execute();
|
||
$weight_alerts = $stmt->fetchAll();
|
||
|
||
$careToday = [];
|
||
$rows = $db
|
||
->query(
|
||
"SELECT periods.period,
|
||
(SELECT COUNT(*) FROM animals a WHERE a.deleted_at IS NULL AND a.archived_at IS NULL AND lower(a.status) IN ('refuge','soin','quarantaine')) expected,
|
||
COUNT(o.id) checked,
|
||
SUM(CASE WHEN o.alert_signature IS NOT NULL THEN 1 ELSE 0 END) alerts
|
||
FROM (SELECT 'morning' period UNION ALL SELECT 'evening') periods
|
||
LEFT JOIN care_rounds r ON r.round_date=date('now','localtime') AND r.period=periods.period
|
||
LEFT JOIN care_round_observations o ON o.round_id=r.id
|
||
GROUP BY periods.period ORDER BY periods.period DESC",
|
||
)
|
||
->fetchAll();
|
||
foreach ($rows as $row) {
|
||
$careToday[$row['period']] = $row;
|
||
}
|
||
|
||
$stmt = $db->query(
|
||
"SELECT a.id,a.name,r.period,o.comment FROM care_round_observations o JOIN care_rounds r ON r.id=o.round_id JOIN animals a ON a.id=o.animal_id WHERE r.round_date=date('now','localtime') AND o.alert_signature IS NOT NULL AND a.deleted_at IS NULL ORDER BY o.checked_at DESC LIMIT 10",
|
||
);
|
||
$careAlertsToday = $stmt->fetchAll();
|
||
$grantAlerts = PermissionService::can('grants') ? GrantService::alerts((int) date('Y')) : [];
|
||
$agendaDue = PermissionService::can('agenda') ? AgendaService::due() : [];
|
||
|
||
render('dashboard.php', [
|
||
'title' => 'Dashboard',
|
||
'stats' => $stats,
|
||
'shelterRoomCounts' => $shelterRoomCounts,
|
||
'quality' => $quality,
|
||
'activity' => $activity,
|
||
|
||
'vaccines_overdue' => $vaccines_overdue,
|
||
'vaccines_7d' => $vaccines_7d,
|
||
'vaccines_30d' => $vaccines_30d,
|
||
|
||
'treatments' => $treatments,
|
||
'treatments_overdue' => $treatments_overdue,
|
||
|
||
'recent_notes' => $recent_notes,
|
||
'quarantine' => $quarantine,
|
||
|
||
'weight_alerts' => $weight_alerts,
|
||
'careToday' => $careToday,
|
||
'careAlertsToday' => $careAlertsToday,
|
||
'grantAlerts' => $grantAlerts,
|
||
'agendaDue' => $agendaDue,
|
||
]);
|
||
}
|
||
}
|