308 lines
15 KiB
PHP
308 lines
15 KiB
PHP
<?php
|
||
|
||
declare(strict_types=1);
|
||
|
||
final class DirectoryController
|
||
{
|
||
private static function roles(): array
|
||
{
|
||
return [
|
||
'adoptant' => t('role.adoptant'),
|
||
'benevole' => t('role.benevole'),
|
||
'cabinet' => t('role.cabinet'),
|
||
'crematorium' => t('role.crematorium'),
|
||
'deposant' => t('role.deposant'),
|
||
'dirigeant' => t('role.dirigeant'),
|
||
'fa' => t('role.fa'),
|
||
'fourriere' => t('role.fourriere'),
|
||
'pet_sitter' => t('role.pet_sitter'),
|
||
'refuge_partenaire' => t('role.refuge_partenaire'),
|
||
'toiletteur' => t('role.toiletteur'),
|
||
'professionnel_autre' => t('role.professionnel_autre'),
|
||
'veterinaire' => t('role.veterinaire'),
|
||
];
|
||
}
|
||
|
||
public static function index(): void
|
||
{
|
||
$db = DB::pdo();
|
||
$q = trim((string) ($_GET['q'] ?? ''));
|
||
$role = trim((string) ($_GET['role'] ?? ''));
|
||
$where = ['dc.deleted_at IS NULL'];
|
||
$params = [];
|
||
if ($q !== '') {
|
||
$where[] = '(dc.name LIKE :q OR dc.phone LIKE :q OR dc.email LIKE :q OR dc.city LIKE :q)';
|
||
$params[':q'] = '%' . $q . '%';
|
||
}
|
||
$roles = self::roles();
|
||
if (isset($roles[$role])) {
|
||
$where[] = 'EXISTS(SELECT 1 FROM directory_contact_roles f WHERE f.contact_id=dc.id AND f.role=:role)';
|
||
$params[':role'] = $role;
|
||
}
|
||
$stmt = $db->prepare(
|
||
"
|
||
SELECT dc.*, org.name AS organization_name, group_concat(dcr.role, ',') AS roles,
|
||
(SELECT COUNT(*) FROM (
|
||
SELECT animal_id FROM adoptions ad WHERE ad.adopter_contact_id=dc.id OR lower(trim(ad.adopter_name))=lower(trim(dc.name))
|
||
UNION SELECT animal_id FROM animal_placements ap WHERE ap.contact_id=dc.id AND ap.event_type='adoption'
|
||
UNION SELECT animal_id FROM animal_movements am WHERE am.kind='exit' AND lower(trim(am.contact_name))=lower(trim(dc.name)) AND lower(COALESCE(am.lieu,'')) LIKE 'adopt%'
|
||
)) AS adoption_count,
|
||
(SELECT COALESCE(SUM(ae.total_cents),0) FROM animal_expenses ae WHERE ae.clinic_contact_id=dc.id) AS expense_total_cents
|
||
FROM directory_contacts dc
|
||
LEFT JOIN directory_contacts org ON org.id=dc.organization_id
|
||
LEFT JOIN directory_contact_roles dcr ON dcr.contact_id=dc.id
|
||
WHERE " .
|
||
implode(' AND ', $where) .
|
||
'
|
||
GROUP BY dc.id ORDER BY dc.name COLLATE NOCASE
|
||
',
|
||
);
|
||
$stmt->execute($params);
|
||
render('directory_list.php', [
|
||
'title' => t('directory.title'),
|
||
'contacts' => $stmt->fetchAll(),
|
||
'roles' => $roles,
|
||
'q' => $q,
|
||
'role' => $role,
|
||
]);
|
||
}
|
||
|
||
public static function show(): void
|
||
{
|
||
$db = DB::pdo();
|
||
$id = (int) ($_GET['id'] ?? 0);
|
||
$stmt = $db->prepare(
|
||
"SELECT dc.*,org.name organization_name,group_concat(r.role,',') roles FROM directory_contacts dc LEFT JOIN directory_contacts org ON org.id=dc.organization_id LEFT JOIN directory_contact_roles r ON r.contact_id=dc.id WHERE dc.id=:id AND dc.deleted_at IS NULL GROUP BY dc.id",
|
||
);
|
||
$stmt->execute([':id' => $id]);
|
||
$contact = $stmt->fetch(PDO::FETCH_ASSOC);
|
||
if (!$contact) {
|
||
http_response_code(404);
|
||
echo h(t('directory.not_found'));
|
||
return;
|
||
}
|
||
$adoptions = $db->prepare("SELECT a.id,a.name,a.internal_code,a.species,a.status,x.event_date FROM animals a JOIN (SELECT animal_id,MAX(event_date) event_date FROM (
|
||
SELECT animal_id,adoption_date event_date FROM adoptions WHERE adopter_contact_id=:id1 OR lower(trim(adopter_name))=lower(trim(:name1))
|
||
UNION ALL SELECT animal_id,event_date FROM animal_placements WHERE contact_id=:id2 AND event_type='adoption'
|
||
UNION ALL SELECT animal_id,created_at event_date FROM animal_movements WHERE kind='exit' AND lower(trim(contact_name))=lower(trim(:name2)) AND lower(COALESCE(lieu,'')) LIKE 'adopt%'
|
||
) GROUP BY animal_id) x ON x.animal_id=a.id ORDER BY date(x.event_date) DESC,a.name COLLATE NOCASE");
|
||
$adoptions->execute([':id1' => $id, ':name1' => $contact['name'], ':id2' => $id, ':name2' => $contact['name']]);
|
||
$fosters = $db->prepare(
|
||
"SELECT a.id,a.name,a.internal_code,a.species,a.status,MAX(ap.event_date) event_date FROM animal_placements ap JOIN animals a ON a.id=ap.animal_id WHERE ap.contact_id=:id AND ap.event_type='foster' GROUP BY a.id ORDER BY date(event_date) DESC,a.name COLLATE NOCASE",
|
||
);
|
||
$fosters->execute([':id' => $id]);
|
||
$deposited = $db->prepare(
|
||
'SELECT id,name,internal_code,species,status,intake_date event_date FROM animals WHERE depositor_contact_id=:id ORDER BY date(intake_date) DESC,name COLLATE NOCASE',
|
||
);
|
||
$deposited->execute([':id' => $id]);
|
||
$periodRequested = array_key_exists('year', $_GET) || array_key_exists('period_type', $_GET);
|
||
$periodRaw = (string) ($_GET['year'] ?? 'all');
|
||
$periodYear =
|
||
ctype_digit($periodRaw) && (int) $periodRaw >= 2000 && (int) $periodRaw <= 2100 ? (int) $periodRaw : null;
|
||
$periodType = (string) ($_GET['period_type'] ?? ($periodYear ? 'year' : 'all'));
|
||
$periodStart = $periodEnd = null;
|
||
$periodQuery = ['period_type' => $periodType];
|
||
if ($periodType === 'year' && $periodYear !== null) {
|
||
$periodStart = sprintf('%04d-01-01', $periodYear);
|
||
$periodEnd = sprintf('%04d-12-31', $periodYear);
|
||
$periodQuery['year'] = $periodYear;
|
||
} elseif ($periodType === 'month' && preg_match('/^\d{4}-(0[1-9]|1[0-2])$/', (string) ($_GET['month'] ?? ''))) {
|
||
$periodQuery['month'] = (string) $_GET['month'];
|
||
$periodStart = $periodQuery['month'] . '-01';
|
||
$periodEnd = (new DateTimeImmutable($periodStart))->modify('last day of this month')->format('Y-m-d');
|
||
} elseif ($periodType === 'custom') {
|
||
$from = (string) ($_GET['date_from'] ?? '');
|
||
$to = (string) ($_GET['date_to'] ?? '');
|
||
if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $from) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $to)) {
|
||
[$periodStart, $periodEnd] = $from <= $to ? [$from, $to] : [$to, $from];
|
||
$periodQuery += ['date_from' => $periodStart,'date_to' => $periodEnd];
|
||
}
|
||
}
|
||
$periodLabel = $periodStart && $periodEnd
|
||
? (new DateTimeImmutable($periodStart))->format('d/m/Y') . ' – ' . (new DateTimeImmutable($periodEnd))->format('d/m/Y')
|
||
: t('statistics.all_years');
|
||
$periodMunicipality = trim((string) ($_GET['municipality'] ?? ''));
|
||
$expenseWhere = ['ae.clinic_contact_id=:id', 'a.deleted_at IS NULL'];
|
||
$expenseParams = [':id' => $id];
|
||
if ($periodStart !== null && $periodEnd !== null) {
|
||
$expenseWhere[] = 'date(ae.occurred_on) BETWEEN :expense_start AND :expense_end';
|
||
$expenseParams[':expense_start'] = $periodStart;
|
||
$expenseParams[':expense_end'] = $periodEnd;
|
||
}
|
||
$expenses = $db->prepare(
|
||
'SELECT ae.*,a.name animal_name,a.internal_code,a.rescue_location_name,a.rescue_address FROM animal_expenses ae JOIN animals a ON a.id=ae.animal_id WHERE ' .
|
||
implode(' AND ', $expenseWhere) .
|
||
' ORDER BY date(ae.occurred_on) DESC,ae.id DESC',
|
||
);
|
||
$expenses->execute($expenseParams);
|
||
$expenseRows = $expenses->fetchAll(PDO::FETCH_ASSOC);
|
||
if ($periodMunicipality !== '') {
|
||
$expenseRows = array_values(
|
||
array_filter(
|
||
$expenseRows,
|
||
static fn($row) => GrantService::municipality(
|
||
(string) ($row['rescue_location_name'] ?: $row['rescue_address']),
|
||
) === $periodMunicipality,
|
||
),
|
||
);
|
||
}
|
||
$expenseTotal = array_sum(array_map(static fn($row) => (int) $row['total_cents'], $expenseRows));
|
||
$expenseCount = count($expenseRows);
|
||
$expenseRows = array_slice($expenseRows, 0, 250);
|
||
$invoiceWhere = ['vendor_contact_id=:vendor', 'deleted_at IS NULL'];
|
||
$invoiceParams = [':vendor' => $id];
|
||
if ($periodStart !== null && $periodEnd !== null) {
|
||
$invoiceWhere[] = 'date(invoice_date) BETWEEN :invoice_start AND :invoice_end';
|
||
$invoiceParams[':invoice_start'] = $periodStart;
|
||
$invoiceParams[':invoice_end'] = $periodEnd;
|
||
}
|
||
$invoiceStmt = $db->prepare(
|
||
'SELECT * FROM accounting_invoices WHERE ' .
|
||
implode(' AND ', $invoiceWhere) .
|
||
' ORDER BY date(invoice_date) DESC,id DESC',
|
||
);
|
||
$invoiceStmt->execute($invoiceParams);
|
||
$contactInvoices = $invoiceStmt->fetchAll(PDO::FETCH_ASSOC);
|
||
$contactInvoiceTotal = array_sum(array_column($contactInvoices, 'total_ttc_cents'));
|
||
$contactInvoiceDue = array_sum(array_column($contactInvoices, 'amount_due_cents'));
|
||
$linkedPeople = [];
|
||
if ($contact['kind'] === 'organization') {
|
||
$s = $db->prepare(
|
||
"SELECT dc.id,dc.name,dc.phone,dc.email,group_concat(r.role,',') roles FROM directory_contacts dc LEFT JOIN directory_contact_roles r ON r.contact_id=dc.id WHERE dc.organization_id=:id AND dc.deleted_at IS NULL GROUP BY dc.id ORDER BY dc.name COLLATE NOCASE",
|
||
);
|
||
$s->execute([':id' => $id]);
|
||
$linkedPeople = $s->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
render('directory_show_accounting.php', [
|
||
'title' => $contact['name'],
|
||
'contact' => $contact,
|
||
'roles' => self::roles(),
|
||
'adoptions' => $adoptions->fetchAll(PDO::FETCH_ASSOC),
|
||
'fosters' => $fosters->fetchAll(PDO::FETCH_ASSOC),
|
||
'deposited' => $deposited->fetchAll(PDO::FETCH_ASSOC),
|
||
'expenses' => $expenseRows,
|
||
'expenseTotal' => $expenseTotal,
|
||
'expenseCount' => $expenseCount,
|
||
'expensePeriodRequested' => $periodRequested,
|
||
'expensePeriodYear' => $periodYear,
|
||
'expensePeriodLabel' => $periodLabel,
|
||
'expensePeriodQuery' => $periodQuery,
|
||
'expensePeriodMunicipality' => $periodMunicipality,
|
||
'contactInvoices' => $contactInvoices,
|
||
'contactInvoiceTotal' => $contactInvoiceTotal,
|
||
'contactInvoiceDue' => $contactInvoiceDue,
|
||
'linkedPeople' => $linkedPeople,
|
||
]);
|
||
}
|
||
|
||
public static function form(): void
|
||
{
|
||
$db = DB::pdo();
|
||
$id = (int) ($_GET['id'] ?? 0);
|
||
$contact = [
|
||
'id' => 0,
|
||
'kind' => 'person',
|
||
'name' => '',
|
||
'phone' => '',
|
||
'email' => '',
|
||
'address' => '',
|
||
'postal_code' => '',
|
||
'city' => '',
|
||
'country' => 'France',
|
||
'organization_id' => '',
|
||
'notes' => '',
|
||
];
|
||
$selected = [];
|
||
if ($id) {
|
||
$stmt = $db->prepare('SELECT * FROM directory_contacts WHERE id=:id AND deleted_at IS NULL');
|
||
$stmt->execute([':id' => $id]);
|
||
$contact = $stmt->fetch() ?: $contact;
|
||
$stmt = $db->prepare('SELECT role FROM directory_contact_roles WHERE contact_id=:id');
|
||
$stmt->execute([':id' => $id]);
|
||
$selected = $stmt->fetchAll(PDO::FETCH_COLUMN);
|
||
}
|
||
$organizations = $db
|
||
->query(
|
||
"SELECT id,name FROM directory_contacts WHERE kind='organization' AND deleted_at IS NULL ORDER BY name COLLATE NOCASE",
|
||
)
|
||
->fetchAll();
|
||
render('directory_form.php', [
|
||
'title' => $id ? t('directory.edit') : t('directory.add'),
|
||
'contact' => $contact,
|
||
'selectedRoles' => $selected,
|
||
'roles' => self::roles(),
|
||
'organizations' => $organizations,
|
||
]);
|
||
}
|
||
|
||
public static function save(): void
|
||
{
|
||
if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
|
||
http_response_code(405);
|
||
return;
|
||
}
|
||
$db = DB::pdo();
|
||
$id = (int) ($_POST['id'] ?? 0);
|
||
$name = trim((string) ($_POST['name'] ?? ''));
|
||
if ($name === '') {
|
||
http_response_code(400);
|
||
echo h(t('directory.name_required'));
|
||
return;
|
||
}
|
||
$kind = ($_POST['kind'] ?? 'person') === 'organization' ? 'organization' : 'person';
|
||
$roles = array_values(
|
||
array_intersect(array_keys(self::roles()), is_array($_POST['roles'] ?? null) ? $_POST['roles'] : []),
|
||
);
|
||
$organizationId = $kind === 'person' ? ((int) ($_POST['organization_id'] ?? 0) ?: null) : null;
|
||
if ($organizationId !== null) {
|
||
$check = $db->prepare(
|
||
"SELECT 1 FROM directory_contacts WHERE id=:id AND kind='organization' AND deleted_at IS NULL",
|
||
);
|
||
$check->execute([':id' => $organizationId]);
|
||
if (!$check->fetchColumn() || $organizationId === $id) {
|
||
http_response_code(400);
|
||
echo h(t('directory.invalid_organization'));
|
||
return;
|
||
}
|
||
}
|
||
$data = [
|
||
':kind' => $kind,
|
||
':name' => $name,
|
||
':phone' => trim((string) ($_POST['phone'] ?? '')) ?: null,
|
||
':email' => trim((string) ($_POST['email'] ?? '')) ?: null,
|
||
':address' => trim((string) ($_POST['address'] ?? '')) ?: null,
|
||
':postal' => trim((string) ($_POST['postal_code'] ?? '')) ?: null,
|
||
':city' => trim((string) ($_POST['city'] ?? '')) ?: null,
|
||
':country' => trim((string) ($_POST['country'] ?? 'France')) ?: 'France',
|
||
':org' => $organizationId,
|
||
':notes' => trim((string) ($_POST['notes'] ?? '')) ?: null,
|
||
];
|
||
$db->beginTransaction();
|
||
try {
|
||
if ($id) {
|
||
$data[':id'] = $id;
|
||
$db->prepare(
|
||
"UPDATE directory_contacts SET kind=:kind,name=:name,phone=:phone,email=:email,address=:address,postal_code=:postal,city=:city,country=:country,organization_id=:org,notes=:notes,updated_at=datetime('now') WHERE id=:id",
|
||
)->execute($data);
|
||
} else {
|
||
$db->prepare(
|
||
'INSERT INTO directory_contacts(kind,name,phone,email,address,postal_code,city,country,organization_id,notes) VALUES(:kind,:name,:phone,:email,:address,:postal,:city,:country,:org,:notes)',
|
||
)->execute($data);
|
||
$id = (int) $db->lastInsertId();
|
||
}
|
||
$db->prepare('DELETE FROM directory_contact_roles WHERE contact_id=:id')->execute([':id' => $id]);
|
||
$ins = $db->prepare('INSERT INTO directory_contact_roles(contact_id,role) VALUES(:id,:role)');
|
||
foreach ($roles as $r) {
|
||
$ins->execute([':id' => $id, ':role' => $r]);
|
||
}
|
||
$db->commit();
|
||
header('Location: /directory');
|
||
exit();
|
||
} catch (Throwable $e) {
|
||
if ($db->inTransaction()) {
|
||
$db->rollBack();
|
||
}
|
||
throw $e;
|
||
}
|
||
}
|
||
}
|