1017 lines
45 KiB
PHP
1017 lines
45 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
trait AnimalBrowseActions
|
|
{
|
|
public static function index(): void
|
|
{
|
|
$db = DB::pdo();
|
|
|
|
$q = trim((string) ($_GET['q'] ?? ''));
|
|
$status = trim((string) ($_GET['status'] ?? 'current'));
|
|
$quality = trim((string) ($_GET['quality'] ?? ''));
|
|
$availability = trim((string) ($_GET['availability'] ?? ''));
|
|
$species = SpeciesService::normalize((string) ($_GET['species'] ?? ''));
|
|
$sort = (string) ($_GET['sort'] ?? 'updated_at');
|
|
$dir = strtolower((string) ($_GET['dir'] ?? 'desc')) === 'asc' ? 'ASC' : 'DESC';
|
|
$requestedView = (string) ($_GET['view'] ?? '');
|
|
$user = Auth::user();
|
|
$viewMode = in_array($requestedView, ['table', 'mosaic'], true)
|
|
? $requestedView
|
|
: (string) ($user['animal_list_view'] ?? 'table');
|
|
if (!in_array($viewMode, ['table', 'mosaic'], true)) {
|
|
$viewMode = 'table';
|
|
}
|
|
if ($user && $requestedView !== '' && $viewMode !== ($user['animal_list_view'] ?? 'table')) {
|
|
$db->prepare('UPDATE users SET animal_list_view=:view,updated_at=datetime(\'now\') WHERE id=:id')->execute([
|
|
':view' => $viewMode,
|
|
':id' => $user['id'],
|
|
]);
|
|
}
|
|
|
|
$allowed = [
|
|
'name' => 'a.name',
|
|
'sex' => 'a.sex',
|
|
'species' => 'a.species',
|
|
'age' => 'a.birth_date',
|
|
'status' => 'a.status',
|
|
'compatibility_dogs' => 'a.compatibility_dogs',
|
|
'compatibility_cats' => 'a.compatibility_cats',
|
|
'compatibility_children' => 'a.compatibility_children',
|
|
'house_trained' => 'a.house_trained',
|
|
'updated_at' => 'a.updated_at',
|
|
'weight' => 'wt.last_weight',
|
|
];
|
|
$orderBy = $allowed[$sort] ?? 'a.updated_at';
|
|
|
|
$where = ['a.deleted_at IS NULL', 'a.archived_at IS NULL'];
|
|
$params = [];
|
|
if (in_array($availability, ['available', 'not_available', 'unknown'], true)) {
|
|
$where[] = 'a.adoption_availability=:availability';
|
|
$params[':availability'] = $availability;
|
|
} else {
|
|
$availability = '';
|
|
}
|
|
if ($species !== '' && SpeciesService::exists($species, false)) {
|
|
$where[] = 'lower(trim(a.species))=:species';
|
|
$params[':species'] = $species;
|
|
} else {
|
|
$species = '';
|
|
}
|
|
|
|
if ($q !== '') {
|
|
$where[] = '(a.name LIKE :q OR a.internal_code LIKE :q OR a.chip_id LIKE :q)';
|
|
$params[':q'] = '%' . $q . '%';
|
|
}
|
|
if ($status === 'current') {
|
|
$where[] = "lower(a.status) NOT IN ('adopte','adopté','decede','décédé','enfui','fugue')";
|
|
} elseif ($status !== '') {
|
|
$where[] = 'a.status = :status';
|
|
$params[':status'] = $status;
|
|
}
|
|
$qualityFilters = [
|
|
'birth_date' => [t('quality.filter.birth'), "a.birth_date IS NULL OR trim(a.birth_date)=''"],
|
|
'sex' => [t('quality.filter.sex'), "a.sex IS NULL OR trim(a.sex)='' OR a.sex='U'"],
|
|
'color' => [t('quality.filter.color'), "a.color IS NULL OR trim(a.color)=''"],
|
|
'breed' => [t('quality.filter.breed'), "a.breed IS NULL OR trim(a.breed)=''"],
|
|
'origin' => [
|
|
t('quality.filter.origin'),
|
|
"(a.rescue_location_name IS NULL OR trim(a.rescue_location_name)='') AND (a.rescue_address IS NULL OR trim(a.rescue_address)='')",
|
|
],
|
|
'chip' => [
|
|
t('quality.filter.chip'),
|
|
"(a.chip_id IS NULL OR trim(a.chip_id)='') AND (a.chip IS NULL OR trim(a.chip)='')",
|
|
],
|
|
'identification_registration' => [
|
|
t('quality.filter.registration'),
|
|
"COALESCE(NULLIF(a.identification_registration_status,''),'unknown')='unknown'",
|
|
],
|
|
'adoption_availability' => [
|
|
t('quality.filter.adoption'),
|
|
"COALESCE(NULLIF(a.adoption_availability,''),'unknown')='unknown'",
|
|
],
|
|
'sterilized' => [t('quality.filter.unsterilized'), "a.sterilization_status='no'"],
|
|
'sterilization_unknown' => [
|
|
t('quality.filter.sterilization'),
|
|
"COALESCE(NULLIF(a.sterilization_status,''),'unknown')='unknown'",
|
|
],
|
|
];
|
|
if (isset($qualityFilters[$quality])) {
|
|
$where[] = '(' . $qualityFilters[$quality][1] . ')';
|
|
} else {
|
|
$quality = '';
|
|
}
|
|
|
|
$sql = "
|
|
SELECT
|
|
a.*,
|
|
ms.last_med_at,
|
|
ms.ongoing_treatments,
|
|
ms.next_vaccine_due,
|
|
wt.last_weight,
|
|
wt.prev_weight,
|
|
p.filename AS primary_photo,
|
|
(
|
|
SELECT dc.name
|
|
FROM animal_placements ap
|
|
JOIN directory_contacts dc ON dc.id=ap.contact_id AND dc.deleted_at IS NULL
|
|
WHERE ap.animal_id=a.id AND ap.event_type='foster'
|
|
ORDER BY date(ap.event_date) DESC,ap.id DESC
|
|
LIMIT 1
|
|
) AS foster_contact_name,
|
|
(
|
|
SELECT rv.name
|
|
FROM vaccinations v
|
|
JOIN ref_vaccines rv ON rv.id = v.vaccine_id
|
|
WHERE v.animal_id = a.id
|
|
AND v.due_date IS NOT NULL
|
|
AND date(v.due_date) >= date('now')
|
|
ORDER BY date(v.due_date) ASC, v.id DESC
|
|
LIMIT 1
|
|
) AS next_vaccine_name,
|
|
(
|
|
SELECT group_concat(m.name, ', ')
|
|
FROM treatments t
|
|
JOIN ref_medications m ON m.id = t.medication_id
|
|
WHERE t.animal_id = a.id AND t.ongoing = 1
|
|
) AS ongoing_treatment_names,
|
|
(
|
|
SELECT COALESCE(
|
|
NULLIF(trim(mn.diagnosis), ''),
|
|
NULLIF(trim(mn.reason), ''),
|
|
NULLIF(trim(mn.symptoms), '')
|
|
)
|
|
FROM medical_notes mn
|
|
WHERE mn.animal_id = a.id
|
|
ORDER BY datetime(mn.noted_at) DESC, mn.id DESC
|
|
LIMIT 1
|
|
) AS latest_medical_point,
|
|
(SELECT d.administered_on FROM dewormings d WHERE d.animal_id=a.id ORDER BY date(d.administered_on) DESC,d.id DESC LIMIT 1) AS last_deworming_date,
|
|
(SELECT d.next_due_date FROM dewormings d WHERE d.animal_id=a.id ORDER BY date(d.administered_on) DESC,d.id DESC LIMIT 1) AS next_deworming_due
|
|
FROM animals a
|
|
LEFT JOIN v_animals_med_summary ms ON ms.animal_id = a.id
|
|
LEFT JOIN v_animals_weight_trend wt ON wt.animal_id = a.id
|
|
LEFT JOIN animal_photos p
|
|
ON p.animal_id = a.id
|
|
AND p.is_primary = 1
|
|
";
|
|
|
|
$sql .= ' WHERE ' . implode(' AND ', $where);
|
|
|
|
if ($sort === 'age') {
|
|
$birthDirection = $dir === 'ASC' ? 'DESC' : 'ASC';
|
|
$sql .= " ORDER BY (a.birth_date IS NULL OR trim(a.birth_date) = '') ASC, date(a.birth_date) $birthDirection, a.id DESC LIMIT 500";
|
|
} elseif (
|
|
in_array(
|
|
$sort,
|
|
['compatibility_dogs', 'compatibility_cats', 'compatibility_children', 'house_trained'],
|
|
true,
|
|
)
|
|
) {
|
|
$profileColumn = $allowed[$sort];
|
|
$profileDirection = $dir === 'ASC' ? 'ASC' : 'DESC';
|
|
$sql .= " ORDER BY CASE WHEN COALESCE($profileColumn,'unknown')='unknown' THEN 1 ELSE 0 END ASC, CASE WHEN $profileColumn='yes' THEN 0 ELSE 1 END $profileDirection, a.name COLLATE NOCASE ASC, a.id DESC LIMIT 500";
|
|
} else {
|
|
$sql .= " ORDER BY $orderBy $dir, a.id DESC LIMIT 500";
|
|
}
|
|
|
|
$stmt = $db->prepare($sql);
|
|
$stmt->execute($params);
|
|
$animals = $stmt->fetchAll();
|
|
|
|
// Regroupe visuellement les chatons sous un parent présent dans le résultat.
|
|
// La mère est prioritaire quand les deux parents sont affichés afin de ne jamais dupliquer une fiche.
|
|
if ($animals) {
|
|
$rowsById = [];
|
|
foreach ($animals as $row) {
|
|
$rowsById[(int) $row['id']] = $row;
|
|
}
|
|
$relations = $db
|
|
->query(
|
|
'
|
|
SELECT lk.litter_id, lk.animal_id, lk.position, l.mother_id, l.father_id, l.birth_date
|
|
FROM litter_kittens lk
|
|
JOIN litters l ON l.id = lk.litter_id
|
|
ORDER BY l.id, lk.position
|
|
',
|
|
)
|
|
->fetchAll(PDO::FETCH_ASSOC);
|
|
$childrenByParent = [];
|
|
$groupedChildren = [];
|
|
$orphanGroups = [];
|
|
$orphanChildren = [];
|
|
$orphanGroupByChild = [];
|
|
foreach ($relations as $relation) {
|
|
$childId = (int) $relation['animal_id'];
|
|
if (!isset($rowsById[$childId])) {
|
|
continue;
|
|
}
|
|
$motherId = (int) ($relation['mother_id'] ?? 0);
|
|
$fatherId = (int) ($relation['father_id'] ?? 0);
|
|
$parentId = isset($rowsById[$motherId]) ? $motherId : (isset($rowsById[$fatherId]) ? $fatherId : 0);
|
|
if (!$parentId) {
|
|
$litterId = (int) $relation['litter_id'];
|
|
$orphanGroups[$litterId]['birth_date'] = $relation['birth_date'];
|
|
$orphanGroups[$litterId]['label'] =
|
|
!$motherId && !$fatherId ? 'Parents inconnus' : 'Parents indisponibles';
|
|
$orphanGroups[$litterId]['children'][] = $childId;
|
|
$orphanChildren[$childId] = true;
|
|
$orphanGroupByChild[$childId] = $litterId;
|
|
continue;
|
|
}
|
|
if (!$parentId || $parentId === $childId) {
|
|
continue;
|
|
}
|
|
$childrenByParent[$parentId][] = $childId;
|
|
$groupedChildren[$childId] = true;
|
|
}
|
|
$ordered = [];
|
|
$emitted = [];
|
|
$emit = function (int $id, string $branch = '') use (
|
|
&$emit,
|
|
&$ordered,
|
|
&$emitted,
|
|
&$rowsById,
|
|
&$childrenByParent,
|
|
): void {
|
|
if (isset($emitted[$id]) || !isset($rowsById[$id])) {
|
|
return;
|
|
}
|
|
$emitted[$id] = true;
|
|
$row = $rowsById[$id];
|
|
if ($branch !== '') {
|
|
$row['_litter_branch'] = $branch;
|
|
}
|
|
$ordered[] = $row;
|
|
$children = array_values(array_unique($childrenByParent[$id] ?? []));
|
|
foreach ($children as $index => $childId) {
|
|
$emit($childId, $index === count($children) - 1 ? '└' : '├');
|
|
}
|
|
};
|
|
$emittedOrphanGroups = [];
|
|
$emitOrphanGroup = function (int $litterId) use (
|
|
&$emittedOrphanGroups,
|
|
&$orphanGroups,
|
|
&$rowsById,
|
|
&$emitted,
|
|
&$emit,
|
|
): void {
|
|
if (isset($emittedOrphanGroups[$litterId]) || !isset($orphanGroups[$litterId])) {
|
|
return;
|
|
}
|
|
$emittedOrphanGroups[$litterId] = true;
|
|
$group = $orphanGroups[$litterId];
|
|
$children = array_values(array_unique($group['children'] ?? []));
|
|
$visibleChildren = array_values(
|
|
array_filter(
|
|
$children,
|
|
fn(int $childId): bool => isset($rowsById[$childId]) && !isset($emitted[$childId]),
|
|
),
|
|
);
|
|
$dateLabel = t('common.unknown_date');
|
|
if (!empty($group['birth_date'])) {
|
|
$date = DateTimeImmutable::createFromFormat('Y-m-d', (string) $group['birth_date']);
|
|
if ($date) {
|
|
$dateLabel = $date->format('d/m/Y');
|
|
}
|
|
}
|
|
foreach ($visibleChildren as $index => $childId) {
|
|
if (!isset($rowsById[$childId]) || isset($emitted[$childId])) {
|
|
continue;
|
|
}
|
|
$rowsById[$childId]['_litter_branch'] = $index === count($visibleChildren) - 1 ? '└' : '├';
|
|
if ($index === 0) {
|
|
$rowsById[$childId]['_litter_group'] = [
|
|
'label' => (string) ($group['label'] ?? t('litter.unknown_parent')),
|
|
'details' => t('litter.group_details', [
|
|
'date' => $dateLabel,
|
|
'count' => count($visibleChildren),
|
|
]),
|
|
];
|
|
}
|
|
$emit($childId, $rowsById[$childId]['_litter_branch']);
|
|
}
|
|
};
|
|
foreach ($animals as $row) {
|
|
$rowId = (int) $row['id'];
|
|
if (isset($orphanGroupByChild[$rowId])) {
|
|
$emitOrphanGroup((int) $orphanGroupByChild[$rowId]);
|
|
} elseif (!isset($groupedChildren[$rowId])) {
|
|
$emit($rowId);
|
|
}
|
|
}
|
|
foreach ($animals as $row) {
|
|
$emit((int) $row['id']);
|
|
}
|
|
$animals = $ordered;
|
|
|
|
// Les groupes inséparables sont prioritaires sur la présentation par portée.
|
|
$bondedRows = $db
|
|
->query(
|
|
'
|
|
SELECT bg.id AS group_id, bg.name AS group_name, bg.notes AS group_notes,
|
|
bgm.animal_id, bgm.position
|
|
FROM bonded_groups bg
|
|
JOIN bonded_group_members bgm ON bgm.group_id = bg.id
|
|
WHERE bg.active = 1
|
|
ORDER BY bg.id, bgm.position
|
|
',
|
|
)
|
|
->fetchAll(PDO::FETCH_ASSOC);
|
|
$bondedByAnimal = [];
|
|
$bondedMeta = [];
|
|
foreach ($bondedRows as $bondedRow) {
|
|
$animalId = (int) $bondedRow['animal_id'];
|
|
if (!isset($rowsById[$animalId])) {
|
|
continue;
|
|
}
|
|
$groupId = (int) $bondedRow['group_id'];
|
|
$bondedByAnimal[$animalId] = $groupId;
|
|
$bondedMeta[$groupId] = ['name' => $bondedRow['group_name'], 'notes' => $bondedRow['group_notes']];
|
|
}
|
|
if ($bondedByAnimal) {
|
|
$orderedById = [];
|
|
foreach ($animals as $row) {
|
|
$orderedById[(int) $row['id']] = $row;
|
|
}
|
|
$bondedOutput = [];
|
|
$emittedGroups = [];
|
|
foreach ($animals as $row) {
|
|
$animalId = (int) $row['id'];
|
|
$groupId = $bondedByAnimal[$animalId] ?? 0;
|
|
if (!$groupId) {
|
|
$bondedOutput[] = $row;
|
|
continue;
|
|
}
|
|
if (isset($emittedGroups[$groupId])) {
|
|
continue;
|
|
}
|
|
$emittedGroups[$groupId] = true;
|
|
$memberIds = [];
|
|
foreach ($animals as $candidate) {
|
|
$candidateId = (int) $candidate['id'];
|
|
if (($bondedByAnimal[$candidateId] ?? 0) === $groupId) {
|
|
$memberIds[] = $candidateId;
|
|
}
|
|
}
|
|
foreach ($memberIds as $index => $memberId) {
|
|
$member = $orderedById[$memberId];
|
|
unset($member['_litter_branch'], $member['_litter_group']);
|
|
$member['_bonded_branch'] = $index === count($memberIds) - 1 ? '└' : '├';
|
|
if ($index === 0) {
|
|
$name = trim((string) ($bondedMeta[$groupId]['name'] ?? ''));
|
|
$member['_bonded_group'] = [
|
|
'label' => $name !== '' ? $name : t('bonded.default_name'),
|
|
'details' => t('bonded.group_details', [
|
|
'count' => count($memberIds),
|
|
'state' =>
|
|
count($memberIds) > 1 ? t('bonded.adopt_together') : t('bonded.incomplete'),
|
|
]),
|
|
'notes' => trim((string) ($bondedMeta[$groupId]['notes'] ?? '')),
|
|
];
|
|
}
|
|
$bondedOutput[] = $member;
|
|
}
|
|
}
|
|
$animals = $bondedOutput;
|
|
}
|
|
}
|
|
|
|
$statuses = [
|
|
'current' => t('animals.current_care'),
|
|
'' => t('animals.all_records'),
|
|
'refuge' => t('status.refuge'),
|
|
'fa' => t('status.fa'),
|
|
'fa_permanente' => t('status.fa_permanente'),
|
|
'chat_libre' => t('status.chat_libre'),
|
|
'quarantaine' => t('status.quarantaine'),
|
|
'soin' => t('status.soin'),
|
|
'isolation' => t('status.isolation'),
|
|
'hospitalise' => t('status.hospitalise'),
|
|
'adopte' => t('status.adopte'),
|
|
'decede' => t('status.decede'),
|
|
'enfui' => t('status.enfui'),
|
|
];
|
|
|
|
render('animals_list.php', [
|
|
'title' => t('animals.title'),
|
|
'animals' => $animals,
|
|
'q' => $q,
|
|
'status' => $status,
|
|
'statuses' => $statuses,
|
|
'sort' => $sort,
|
|
'dir' => $dir,
|
|
'quality' => $quality,
|
|
'qualityLabel' => $quality !== '' ? $qualityFilters[$quality][0] : '',
|
|
'availability' => $availability,
|
|
'viewMode' => $viewMode,
|
|
'species' => $species,
|
|
'speciesOptions' => SpeciesService::all(false),
|
|
]);
|
|
}
|
|
|
|
public static function show(): void
|
|
{
|
|
$db = DB::pdo();
|
|
$id = (int) ($_GET['id'] ?? 0);
|
|
|
|
if ($id <= 0) {
|
|
http_response_code(400);
|
|
echo h(t('error.missing_id'));
|
|
return;
|
|
}
|
|
|
|
// Une fiche archivée reste consultable : seule la corbeille masque la fiche normale.
|
|
$stmt = $db->prepare('SELECT * FROM animals WHERE id = :id AND deleted_at IS NULL LIMIT 1');
|
|
$stmt->execute([':id' => $id]);
|
|
$animal = $stmt->fetch();
|
|
|
|
if (!$animal) {
|
|
http_response_code(404);
|
|
echo h(t('error.animal_not_found'));
|
|
return;
|
|
}
|
|
if (!empty($animal['intake_owner_care_home'])) {
|
|
$animal['intake_reason'] = 'owner_care_home';
|
|
}
|
|
$depositor = null;
|
|
if (!empty($animal['depositor_contact_id'])) {
|
|
$s = $db->prepare(
|
|
'SELECT id,name,phone,email,city FROM directory_contacts WHERE id=:id AND deleted_at IS NULL',
|
|
);
|
|
$s->execute([':id' => $animal['depositor_contact_id']]);
|
|
$depositor = $s->fetch(PDO::FETCH_ASSOC) ?: null;
|
|
}
|
|
$freeCatCaretaker = null;
|
|
if (!empty($animal['free_cat_caretaker_contact_id'])) {
|
|
$s = $db->prepare(
|
|
'SELECT id,name,phone,email,city FROM directory_contacts WHERE id=:id AND deleted_at IS NULL',
|
|
);
|
|
$s->execute([':id' => $animal['free_cat_caretaker_contact_id']]);
|
|
$freeCatCaretaker = $s->fetch(PDO::FETCH_ASSOC) ?: null;
|
|
}
|
|
$sterilizationContacts = [];
|
|
foreach (
|
|
[
|
|
'vet' => $animal['sterilization_vet_contact_id'] ?? null,
|
|
'clinic' => $animal['sterilization_clinic_contact_id'] ?? null,
|
|
]
|
|
as $key => $contactId
|
|
) {
|
|
if ($contactId) {
|
|
$s = $db->prepare('SELECT id,name FROM directory_contacts WHERE id=:id AND deleted_at IS NULL');
|
|
$s->execute([':id' => $contactId]);
|
|
$sterilizationContacts[$key] = $s->fetch(PDO::FETCH_ASSOC) ?: null;
|
|
}
|
|
}
|
|
|
|
// Adoptions
|
|
$stmt = $db->prepare('
|
|
SELECT adopter_name, adopter_address, adopter_postal_code, adopter_city, adopter_country, adopter_phone, adopter_email, adoption_date
|
|
FROM adoptions
|
|
WHERE animal_id = :id
|
|
ORDER BY id DESC
|
|
LIMIT 1
|
|
');
|
|
|
|
$stmt->execute([':id' => $id]);
|
|
$adoption = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
$placementStmt = $db->prepare(
|
|
'SELECT ap.*,dc.name contact_name,dc.city contact_city,COALESCE(u.display_name,u.username) actor_name FROM animal_placements ap LEFT JOIN directory_contacts dc ON dc.id=ap.contact_id LEFT JOIN users u ON u.id=ap.created_by WHERE ap.animal_id=:id ORDER BY date(ap.event_date) DESC,ap.id DESC',
|
|
);
|
|
$placementStmt->execute([':id' => $id]);
|
|
$placements = $placementStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
$placementContacts = $db
|
|
->query(
|
|
"SELECT DISTINCT dc.id,dc.name,dc.city FROM directory_contacts dc JOIN directory_contact_roles r ON r.contact_id=dc.id WHERE dc.deleted_at IS NULL AND r.role IN ('fa','adoptant') ORDER BY dc.name COLLATE NOCASE",
|
|
)
|
|
->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Notes médicales (timeline)
|
|
$stmt = $db->prepare('
|
|
SELECT mn.*, COALESCE(dc.name, c.name) AS clinic_name
|
|
FROM medical_notes mn
|
|
LEFT JOIN clinics c ON c.id = mn.clinic_id
|
|
LEFT JOIN directory_contacts dc ON dc.id = mn.clinic_contact_id
|
|
WHERE mn.animal_id = :id
|
|
ORDER BY mn.noted_at DESC
|
|
LIMIT 50
|
|
');
|
|
$stmt->execute([':id' => $id]);
|
|
$medical_notes = $stmt->fetchAll();
|
|
|
|
$prescriptionStmt = $db->prepare(
|
|
'SELECT p.*,vet.name veterinarian_name,clinic.name clinic_name,COALESCE(u.display_name,u.username) actor_name FROM medical_prescriptions p LEFT JOIN directory_contacts vet ON vet.id=p.veterinarian_contact_id LEFT JOIN directory_contacts clinic ON clinic.id=p.clinic_contact_id LEFT JOIN users u ON u.id=p.created_by WHERE p.animal_id=:id ORDER BY date(p.prescribed_on) DESC,p.id DESC',
|
|
);
|
|
$prescriptionStmt->execute([':id' => $id]);
|
|
$prescriptions = $prescriptionStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
$prescriptionTreatmentStmt = $db->prepare(
|
|
'SELECT pt.prescription_id,t.id,m.name medication_name FROM prescription_treatments pt JOIN treatments t ON t.id=pt.treatment_id LEFT JOIN ref_medications m ON m.id=t.medication_id WHERE t.animal_id=:id ORDER BY m.name COLLATE NOCASE',
|
|
);
|
|
$prescriptionTreatmentStmt->execute([':id' => $id]);
|
|
$prescriptionTreatments = [];
|
|
foreach ($prescriptionTreatmentStmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
|
$prescriptionTreatments[(int) $row['prescription_id']][] = $row;
|
|
}
|
|
|
|
$labStmt = $db->prepare(
|
|
'SELECT r.*,vet.name veterinarian_name,clinic.name clinic_name,COALESCE(u.display_name,u.username) actor_name FROM lab_reports r LEFT JOIN directory_contacts vet ON vet.id=r.veterinarian_contact_id LEFT JOIN directory_contacts clinic ON clinic.id=r.clinic_contact_id LEFT JOIN users u ON u.id=r.created_by WHERE r.animal_id=:id ORDER BY date(r.sampled_on) DESC,r.id DESC',
|
|
);
|
|
$labStmt->execute([':id' => $id]);
|
|
$labReports = $labStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
$labResultStmt = $db->prepare(
|
|
'SELECT lr.*,r.sampled_on FROM lab_results lr JOIN lab_reports r ON r.id=lr.report_id WHERE r.animal_id=:id ORDER BY date(r.sampled_on),lr.position,lr.id',
|
|
);
|
|
$labResultStmt->execute([':id' => $id]);
|
|
$labResultsByReport = [];
|
|
$labSeries = [];
|
|
foreach ($labResultStmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
|
$labResultsByReport[(int) $row['report_id']][] = $row;
|
|
$seriesKey =
|
|
mb_strtolower(trim((string) $row['parameter_name'])) .
|
|
'|' .
|
|
mb_strtolower(trim((string) ($row['unit'] ?? '')));
|
|
if (!isset($labSeries[$seriesKey])) {
|
|
$labSeries[$seriesKey] = [
|
|
'name' => $row['parameter_name'],
|
|
'unit' => $row['unit'] ?? '',
|
|
'points' => [],
|
|
];
|
|
}
|
|
$labSeries[$seriesKey]['points'][] = [
|
|
'date' => $row['sampled_on'],
|
|
'value' => (float) $row['value'],
|
|
'min' => $row['reference_min'] !== null ? (float) $row['reference_min'] : null,
|
|
'max' => $row['reference_max'] !== null ? (float) $row['reference_max'] : null,
|
|
];
|
|
}
|
|
|
|
// Traitements en cours
|
|
$stmt = $db->prepare('
|
|
SELECT t.*, m.name AS medication_name, m.molecule, m.form, COALESCE(dc.name, c.name) AS clinic_name
|
|
FROM treatments t
|
|
JOIN ref_medications m ON m.id = t.medication_id
|
|
LEFT JOIN clinics c ON c.id = t.clinic_id
|
|
LEFT JOIN directory_contacts dc ON dc.id = t.clinic_contact_id
|
|
WHERE t.animal_id = :id AND t.ongoing = 1
|
|
ORDER BY t.start_date DESC, t.id DESC
|
|
');
|
|
$stmt->execute([':id' => $id]);
|
|
$treatments = $stmt->fetchAll();
|
|
|
|
// Vaccins
|
|
$stmt = $db->prepare('
|
|
SELECT v.*, rv.name AS vaccine_name, COALESCE(dc.name, c.name) AS clinic_name,COALESCE(v.administered_by_name,au.display_name,au.username) AS administrator_name
|
|
FROM vaccinations v
|
|
JOIN ref_vaccines rv ON rv.id = v.vaccine_id
|
|
LEFT JOIN clinics c ON c.id = v.clinic_id
|
|
LEFT JOIN directory_contacts dc ON dc.id = v.clinic_contact_id
|
|
LEFT JOIN users au ON au.id=v.administered_by_user_id
|
|
WHERE v.animal_id = :id
|
|
ORDER BY v.done_date DESC, v.id DESC
|
|
LIMIT 50
|
|
');
|
|
$stmt->execute([':id' => $id]);
|
|
$vaccinations = $stmt->fetchAll();
|
|
|
|
$stmt = $db->prepare(
|
|
'SELECT d.*,m.name medication_name,COALESCE(u.display_name,u.username) actor_name FROM dewormings d LEFT JOIN ref_dewormers m ON m.id=d.dewormer_id LEFT JOIN users u ON u.id=d.created_by WHERE d.animal_id=:id ORDER BY date(d.administered_on) DESC,d.id DESC',
|
|
);
|
|
$stmt->execute([':id' => $id]);
|
|
$dewormings = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
$lastDeworming = $dewormings[0] ?? null;
|
|
|
|
$medications = $db->query('SELECT id, name FROM ref_medications ORDER BY name COLLATE NOCASE, id')->fetchAll();
|
|
$dewormers = $db
|
|
->query('SELECT id,name FROM ref_dewormers WHERE active=1 ORDER BY name COLLATE NOCASE,id')
|
|
->fetchAll(PDO::FETCH_ASSOC);
|
|
$expenseTariffs = PricingService::tariffs(true);
|
|
$expenseStmt = $db->prepare(
|
|
'SELECT ae.*,dc.name clinic_name,COALESCE(u.display_name,u.username) actor_name FROM animal_expenses ae LEFT JOIN directory_contacts dc ON dc.id=ae.clinic_contact_id LEFT JOIN users u ON u.id=ae.created_by WHERE ae.animal_id=:id ORDER BY date(ae.occurred_on) DESC,ae.id DESC',
|
|
);
|
|
$expenseStmt->execute([':id' => $id]);
|
|
$expenses = $expenseStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
$expenseTotal = array_sum(array_column($expenses, 'total_cents'));
|
|
$surgeryStmt = $db->prepare(
|
|
'SELECT s.*,c.name clinic_name,v.name veterinarian_name FROM animal_surgeries s LEFT JOIN directory_contacts c ON c.id=s.clinic_contact_id LEFT JOIN directory_contacts v ON v.id=s.veterinarian_contact_id WHERE s.animal_id=:id ORDER BY date(s.surgery_date) DESC,s.id DESC',
|
|
);
|
|
$surgeryStmt->execute([':id' => $id]);
|
|
$surgeries = $surgeryStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
$ref_med_administration = $db
|
|
->query('SELECT id, name, accro FROM ref_med_administration ORDER BY id')
|
|
->fetchAll();
|
|
$vaccines = $db->query('SELECT id, name FROM ref_vaccines ORDER BY id')->fetchAll();
|
|
$inventoryBatches = $db
|
|
->query(
|
|
'SELECT b.id,b.batch_number,b.expires_on,b.quantity,p.name,p.unit,p.category,p.reference_id FROM inventory_batches b JOIN inventory_products p ON p.id=b.product_id WHERE b.quantity>0 AND p.active=1 ORDER BY p.category,p.name COLLATE NOCASE,date(b.expires_on)',
|
|
)
|
|
->fetchAll(PDO::FETCH_ASSOC);
|
|
$activeUsers = $db
|
|
->query(
|
|
"SELECT id,COALESCE(NULLIF(display_name,''),username) name FROM users WHERE active=1 ORDER BY name COLLATE NOCASE",
|
|
)
|
|
->fetchAll(PDO::FETCH_ASSOC);
|
|
$clinics = $db->query('SELECT id, name FROM clinics ORDER BY name')->fetchAll();
|
|
$directoryClinics = $db
|
|
->query(
|
|
"
|
|
SELECT dc.id, dc.name FROM directory_contacts dc JOIN directory_contact_roles r ON r.contact_id=dc.id AND r.role='cabinet'
|
|
WHERE dc.deleted_at IS NULL ORDER BY dc.name COLLATE NOCASE
|
|
",
|
|
)
|
|
->fetchAll();
|
|
$veterinarians = $db
|
|
->query(
|
|
"
|
|
SELECT dc.id, dc.name, dc.organization_id FROM directory_contacts dc JOIN directory_contact_roles r ON r.contact_id=dc.id AND r.role='veterinaire'
|
|
WHERE dc.deleted_at IS NULL ORDER BY dc.name COLLATE NOCASE
|
|
",
|
|
)
|
|
->fetchAll();
|
|
$adopters = $db
|
|
->query(
|
|
"
|
|
SELECT dc.* FROM directory_contacts dc JOIN directory_contact_roles r ON r.contact_id=dc.id AND r.role='adoptant'
|
|
WHERE dc.deleted_at IS NULL ORDER BY dc.name COLLATE NOCASE
|
|
",
|
|
)
|
|
->fetchAll();
|
|
|
|
$deathStmt = $db->prepare(
|
|
'SELECT ad.*,vet.name veterinarian_name,crem.name crematorium_name FROM animal_deaths ad LEFT JOIN directory_contacts vet ON vet.id=ad.veterinarian_contact_id LEFT JOIN directory_contacts crem ON crem.id=ad.crematorium_contact_id WHERE ad.animal_id=:id',
|
|
);
|
|
$deathStmt->execute([':id' => $id]);
|
|
$death = $deathStmt->fetch(PDO::FETCH_ASSOC) ?: null;
|
|
|
|
// Poids
|
|
$weightsStmt = $db->prepare("
|
|
SELECT measured_at, value
|
|
FROM measurements
|
|
WHERE animal_id = :id AND type = 'weight'
|
|
ORDER BY datetime(measured_at) ASC
|
|
");
|
|
$weightsStmt->execute([':id' => $id]);
|
|
$weights = $weightsStmt->fetchAll();
|
|
|
|
$stmt = $db->prepare("
|
|
SELECT value
|
|
FROM measurements
|
|
WHERE animal_id = :id AND type='weight'
|
|
ORDER BY id DESC
|
|
LIMIT 1
|
|
");
|
|
$stmt->execute([':id' => $id]);
|
|
$lastWeight = $stmt->fetchColumn();
|
|
|
|
$stmt = $db->prepare("
|
|
SELECT value
|
|
FROM measurements
|
|
WHERE animal_id = :id AND type='weight'
|
|
AND value != (
|
|
SELECT value FROM measurements
|
|
WHERE animal_id = :id AND type='weight'
|
|
ORDER BY id DESC LIMIT 1
|
|
)
|
|
ORDER BY id DESC
|
|
LIMIT 1
|
|
");
|
|
$stmt->execute([':id' => $id]);
|
|
$prevWeight = $stmt->fetchColumn();
|
|
|
|
// PHOTOS : on récupère TOUT (public + privé)
|
|
$stmt = $db->prepare('
|
|
SELECT id, filename, is_primary, is_public, caption, care_round_id, created_at
|
|
FROM animal_photos
|
|
WHERE animal_id = :id
|
|
ORDER BY is_public DESC, is_primary DESC, id DESC
|
|
');
|
|
$stmt->execute([':id' => $id]);
|
|
$photos = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// PHOTO PRINCIPALE : seulement une publique
|
|
$stmt = $db->prepare('
|
|
SELECT filename
|
|
FROM animal_photos
|
|
WHERE animal_id = :id AND is_primary = 1 AND is_public = 1
|
|
ORDER BY id DESC
|
|
LIMIT 1
|
|
');
|
|
$stmt->execute([':id' => $id]);
|
|
$primary_photo = $stmt->fetchColumn() ?: null;
|
|
|
|
// Historique
|
|
$h = $db->prepare('
|
|
SELECT ah.type,ah.label,ah.details,ah.created_at,COALESCE(u.display_name,u.username) actor_name
|
|
FROM animal_history ah LEFT JOIN users u ON u.id=ah.user_id
|
|
WHERE ah.animal_id = :id
|
|
ORDER BY ah.created_at DESC
|
|
');
|
|
$h->execute([':id' => $id]);
|
|
$history = $h->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$locationStmt = $db->prepare('SELECT alh.*,COALESCE(u.display_name,u.username) actor_name
|
|
FROM animal_location_history alh LEFT JOIN users u ON u.id=alh.user_id
|
|
WHERE alh.animal_id=:id ORDER BY datetime(alh.moved_at) DESC,alh.id DESC');
|
|
$locationStmt->execute([':id' => $id]);
|
|
$locationHistory = $locationStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$conditionsStmt = $db->prepare("
|
|
SELECT id, name, status, diagnosed_at, resolved_at, notes
|
|
FROM animal_health_conditions
|
|
WHERE animal_id = :id
|
|
ORDER BY status = 'active' DESC, datetime(COALESCE(diagnosed_at, created_at)) DESC, id DESC
|
|
");
|
|
$conditionsStmt->execute([':id' => $id]);
|
|
$healthConditions = $conditionsStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// --- ACTIVITY (timeline unifiée) ---
|
|
$activityStmt = $db->prepare("
|
|
SELECT kind, label, details, at FROM (
|
|
-- Historique interne
|
|
SELECT
|
|
CASE WHEN type='care_round' THEN 'care_round' WHEN type='death' THEN 'death' ELSE 'history' END AS kind,
|
|
COALESCE(label,'') AS label,
|
|
COALESCE(details,'') || CASE WHEN user_id IS NOT NULL THEN '\n\nPar : ' || COALESCE((SELECT display_name FROM users WHERE id=animal_history.user_id),(SELECT username FROM users WHERE id=animal_history.user_id),'Compte inconnu') ELSE '' END AS details,
|
|
datetime(created_at) AS at
|
|
FROM animal_history
|
|
WHERE animal_id = :id
|
|
|
|
UNION ALL
|
|
|
|
-- Notes médicales
|
|
SELECT
|
|
'medical' AS kind,
|
|
CASE
|
|
WHEN COALESCE(reason,'') != '' THEN 'Note médicale : ' || reason
|
|
ELSE 'Note médicale'
|
|
END AS label,
|
|
TRIM(
|
|
COALESCE(symptoms,'') ||
|
|
CASE WHEN COALESCE(symptoms,'')!='' AND COALESCE(diagnosis,'')!='' THEN '\n\n' ELSE '' END ||
|
|
CASE WHEN COALESCE(diagnosis,'')!='' THEN 'Diagnostic : ' || diagnosis ELSE '' END ||
|
|
CASE WHEN COALESCE(plan,'')!='' THEN '\n\nPlan : ' || plan ELSE '' END
|
|
) AS details,
|
|
datetime(noted_at) AS at
|
|
FROM medical_notes
|
|
WHERE animal_id = :id
|
|
|
|
UNION ALL
|
|
|
|
-- Vaccins
|
|
SELECT
|
|
'vaccine' AS kind,
|
|
'Vaccin : ' || COALESCE(rv.name,'(inconnu)') AS label,
|
|
TRIM(
|
|
'Fait le : ' || COALESCE(v.done_date,'?') ||
|
|
CASE WHEN v.due_date IS NOT NULL THEN '\nRappel : ' || v.due_date ELSE '' END ||
|
|
CASE WHEN COALESCE(v.lot,'')!='' THEN '\nLot : ' || v.lot ELSE '' END ||
|
|
CASE WHEN COALESCE(v.manufacturer,'')!='' THEN '\nFabricant : ' || v.manufacturer ELSE '' END ||
|
|
CASE WHEN COALESCE(v.batch_expires_on,'')!='' THEN '\nExpiration du lot : ' || v.batch_expires_on ELSE '' END ||
|
|
CASE WHEN COALESCE(v.administered_by_name,'')!='' THEN '\nAdministré par : ' || v.administered_by_name WHEN v.administered_by_user_id IS NOT NULL THEN '\nAdministré par : ' || COALESCE((SELECT display_name FROM users WHERE id=v.administered_by_user_id),(SELECT username FROM users WHERE id=v.administered_by_user_id),'Compte inconnu') ELSE '' END ||
|
|
CASE WHEN COALESCE(v.notes,'')!='' THEN '\nNotes : ' || v.notes ELSE '' END
|
|
) AS details,
|
|
datetime(v.done_date || ' 12:00:00') AS at
|
|
FROM vaccinations v
|
|
LEFT JOIN ref_vaccines rv ON rv.id = v.vaccine_id
|
|
WHERE v.animal_id = :id
|
|
|
|
UNION ALL
|
|
|
|
-- Traitements (création/MAJ)
|
|
SELECT
|
|
'treatment' AS kind,
|
|
'Traitement : ' || COALESCE(m.name,'(médicament)') AS label,
|
|
TRIM(
|
|
'Dose : ' || COALESCE(t.dose_text,'?') ||
|
|
CASE WHEN COALESCE(t.route,'')!='' THEN '\nVoie : ' || t.route ELSE '' END ||
|
|
CASE WHEN COALESCE(t.start_date,'')!='' THEN '\nDébut : ' || t.start_date ELSE '' END ||
|
|
CASE WHEN COALESCE(t.end_date,'')!='' THEN '\nFin : ' || t.end_date ELSE '' END ||
|
|
CASE WHEN t.ongoing=1 THEN '\nÉtat : en cours' ELSE '\nÉtat : terminé' END ||
|
|
CASE WHEN COALESCE(t.notes,'')!='' THEN '\nNotes : ' || t.notes ELSE '' END
|
|
) AS details,
|
|
datetime(COALESCE(t.start_date, datetime('now')) || ' 12:00:00') AS at
|
|
FROM treatments t
|
|
LEFT JOIN ref_medications m ON m.id = t.medication_id
|
|
WHERE t.animal_id = :id
|
|
|
|
UNION ALL
|
|
|
|
-- Poids (measurements)
|
|
SELECT
|
|
'weight' AS kind,
|
|
'Poids : ' || printf('%.2f', value) || ' ' || COALESCE(unit,'kg') AS label,
|
|
COALESCE(notes,'') AS details,
|
|
datetime(measured_at) AS at
|
|
FROM measurements
|
|
WHERE animal_id = :id AND type='weight'
|
|
)
|
|
ORDER BY at DESC
|
|
LIMIT 200
|
|
");
|
|
$activityStmt->execute([':id' => $id]);
|
|
$activity = $activityStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// --- i-CAD (cache local) ---
|
|
require_once __DIR__ . '/../Services/IcadService.php'; // adapte si ton chemin diffère
|
|
$icadSvc = new IcadService($db);
|
|
|
|
$icadCached = $icadSvc->getCachedByAnimal($id);
|
|
$icad = [
|
|
'cached' => $icadCached,
|
|
'stale' => $icadSvc->isStale($icadCached, 86400),
|
|
];
|
|
|
|
$littersStmt = $db->prepare('
|
|
SELECT l.*, mother.name AS mother_name, father.name AS father_name
|
|
FROM litters l
|
|
LEFT JOIN animals mother ON mother.id = l.mother_id
|
|
LEFT JOIN animals father ON father.id = l.father_id
|
|
WHERE l.mother_id = :id OR l.father_id = :id
|
|
OR EXISTS (SELECT 1 FROM litter_kittens lk WHERE lk.litter_id = l.id AND lk.animal_id = :id)
|
|
ORDER BY COALESCE(l.birth_date, l.created_at) DESC, l.id DESC
|
|
');
|
|
$littersStmt->execute([':id' => $id]);
|
|
$litters = $littersStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
$kittenStmt = $db->prepare('
|
|
SELECT a.id, a.name, a.sex, a.color, a.status, a.archived_at
|
|
FROM litter_kittens lk JOIN animals a ON a.id = lk.animal_id
|
|
WHERE lk.litter_id = :litter_id AND a.deleted_at IS NULL
|
|
ORDER BY lk.position, a.id
|
|
');
|
|
foreach ($litters as &$litter) {
|
|
$kittenStmt->execute([':litter_id' => $litter['id']]);
|
|
$litter['kittens'] = $kittenStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
unset($litter);
|
|
|
|
$bondedStmt = $db->prepare('
|
|
SELECT bg.id, bg.name, bg.notes
|
|
FROM bonded_groups bg
|
|
JOIN bonded_group_members bgm ON bgm.group_id = bg.id
|
|
WHERE bg.active = 1 AND bgm.animal_id = :id
|
|
LIMIT 1
|
|
');
|
|
$bondedStmt->execute([':id' => $id]);
|
|
$bondedGroup = $bondedStmt->fetch(PDO::FETCH_ASSOC) ?: null;
|
|
$bondedMembers = [];
|
|
if ($bondedGroup) {
|
|
$stmt = $db->prepare('
|
|
SELECT a.id, a.name, a.color, a.sex, a.status
|
|
FROM bonded_group_members bgm JOIN animals a ON a.id = bgm.animal_id
|
|
WHERE bgm.group_id = :group_id AND a.deleted_at IS NULL AND a.archived_at IS NULL
|
|
ORDER BY bgm.position, a.name COLLATE NOCASE
|
|
');
|
|
$stmt->execute([':group_id' => $bondedGroup['id']]);
|
|
$bondedMembers = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
render('animal_show.php', [
|
|
'title' => 'Fiche de ' . $animal['name'],
|
|
'pageDescription' => 'Dossier et suivi de ' . $animal['name'] . ' dans Globinours.',
|
|
'animal' => $animal,
|
|
'depositor' => $depositor,
|
|
'intakeTypes' => self::intakeTypes(),
|
|
'intakeReasons' => self::intakeReasons(),
|
|
'freeCatCaretaker' => $freeCatCaretaker,
|
|
'sterilizationContacts' => $sterilizationContacts,
|
|
'medical_notes' => $medical_notes,
|
|
'prescriptions' => $prescriptions,
|
|
'prescriptionTreatments' => $prescriptionTreatments,
|
|
'labReports' => $labReports,
|
|
'labResultsByReport' => $labResultsByReport,
|
|
'labSeries' => $labSeries,
|
|
'labReferenceSets' => LabReferenceService::forAnimal((string) ($animal['species'] ?? '')),
|
|
'treatments' => $treatments,
|
|
'vaccinations' => $vaccinations,
|
|
'dewormings' => $dewormings,
|
|
'lastDeworming' => $lastDeworming,
|
|
'medications' => $medications,
|
|
'dewormers' => $dewormers,
|
|
'expenseTariffs' => $expenseTariffs,
|
|
'expenses' => $expenses,
|
|
'expenseTotal' => $expenseTotal,
|
|
'surgeries' => $surgeries,
|
|
'ref_med_administration' => $ref_med_administration,
|
|
'vaccines' => $vaccines,
|
|
'inventoryBatches' => $inventoryBatches,
|
|
'activeUsers' => $activeUsers,
|
|
'clinics' => $clinics,
|
|
'directoryClinics' => $directoryClinics,
|
|
'veterinarians' => $veterinarians,
|
|
'adopters' => $adopters,
|
|
'death' => $death,
|
|
'weights' => $weights,
|
|
'lastWeight' => $lastWeight,
|
|
'prevWeight' => $prevWeight,
|
|
'photos' => $photos,
|
|
'primary_photo' => $primary_photo,
|
|
'history' => $history,
|
|
'locationHistory' => $locationHistory,
|
|
'healthConditions' => $healthConditions,
|
|
'activity' => $activity,
|
|
'adoption' => $adoption,
|
|
'placements' => $placements,
|
|
'placementContacts' => $placementContacts,
|
|
'litters' => $litters,
|
|
'bondedGroup' => $bondedGroup,
|
|
'bondedMembers' => $bondedMembers,
|
|
|
|
'icad' => $icad,
|
|
]);
|
|
}
|
|
|
|
public static function inactiveList(string $mode): void
|
|
{
|
|
if (!in_array($mode, ['archived', 'trash'], true)) {
|
|
http_response_code(404);
|
|
return;
|
|
}
|
|
$db = DB::pdo();
|
|
$column = $mode === 'archived' ? 'archived_at' : 'deleted_at';
|
|
$q = trim((string) ($_GET['q'] ?? ''));
|
|
$status = trim((string) ($_GET['status'] ?? ''));
|
|
$sort = (string) ($_GET['sort'] ?? 'removed_at');
|
|
$dir = strtolower((string) ($_GET['dir'] ?? 'desc')) === 'asc' ? 'ASC' : 'DESC';
|
|
$allowed = [
|
|
'name' => 'a.name COLLATE NOCASE',
|
|
'sex' => 'a.sex',
|
|
'age' => 'a.birth_date',
|
|
'status' => 'a.status',
|
|
'compatibility_dogs' => 'a.compatibility_dogs',
|
|
'compatibility_cats' => 'a.compatibility_cats',
|
|
'house_trained' => 'a.house_trained',
|
|
'weight' => 'wt.last_weight',
|
|
'rescue_location_name' =>
|
|
"COALESCE(NULLIF(TRIM(a.rescue_location_name),''),a.rescue_address) COLLATE NOCASE",
|
|
'removed_at' => 'a.' . $column,
|
|
];
|
|
$where = ["a.$column IS NOT NULL"];
|
|
$params = [];
|
|
if ($q !== '') {
|
|
$where[] =
|
|
'(a.name LIKE :q OR a.internal_code LIKE :q OR a.chip_id LIKE :q OR a.rescue_location_name LIKE :q)';
|
|
$params[':q'] = '%' . $q . '%';
|
|
}
|
|
if ($status !== '') {
|
|
$where[] = 'a.status=:status';
|
|
$params[':status'] = $status;
|
|
}
|
|
$orderBy = $allowed[$sort] ?? $allowed['removed_at'];
|
|
$ageDirection = $dir === 'ASC' ? 'DESC' : 'ASC';
|
|
$orderSql =
|
|
$sort === 'age'
|
|
? "(a.birth_date IS NULL OR trim(a.birth_date)='') ASC, date(a.birth_date) $ageDirection, a.id DESC"
|
|
: "$orderBy $dir, a.id DESC";
|
|
$stmt = $db->prepare(
|
|
'
|
|
SELECT a.*, p.filename AS primary_photo, wt.last_weight, wt.prev_weight
|
|
FROM animals a
|
|
LEFT JOIN animal_photos p ON p.animal_id = a.id AND p.is_primary = 1
|
|
LEFT JOIN v_animals_weight_trend wt ON wt.animal_id=a.id
|
|
WHERE ' .
|
|
implode(' AND ', $where) .
|
|
"
|
|
ORDER BY $orderSql
|
|
LIMIT 500
|
|
",
|
|
);
|
|
$stmt->execute($params);
|
|
$statuses = ['' => t('animals.all_statuses')];
|
|
foreach (
|
|
[
|
|
'refuge',
|
|
'fa',
|
|
'fa_permanente',
|
|
'chat_libre',
|
|
'quarantaine',
|
|
'soin',
|
|
'isolation',
|
|
'hospitalise',
|
|
'adopte',
|
|
'decede',
|
|
'enfui',
|
|
]
|
|
as $statusCode
|
|
) {
|
|
$statuses[$statusCode] = t('status.' . $statusCode);
|
|
}
|
|
render('animals_inactive.php', [
|
|
'title' => $mode === 'archived' ? t('animals.archived') : t('animals.trash'),
|
|
'pageDescription' => $mode === 'archived' ? t('inactive.archive_help') : t('inactive.trash_help'),
|
|
'animals' => $stmt->fetchAll(PDO::FETCH_ASSOC),
|
|
'mode' => $mode,
|
|
'q' => $q,
|
|
'status' => $status,
|
|
'statuses' => $statuses,
|
|
'sort' => $sort,
|
|
'dir' => $dir,
|
|
]);
|
|
}
|
|
|
|
private static function redirectToAnimal(int $id): void
|
|
{
|
|
header('Location: /animal?id=' . $id);
|
|
exit();
|
|
}
|
|
}
|