313 lines
14 KiB
PHP
313 lines
14 KiB
PHP
<?php
|
||
|
||
declare(strict_types=1);
|
||
|
||
final class GrantService
|
||
{
|
||
public const STATUSES = [
|
||
'draft' => 'Brouillon',
|
||
'ready' => 'Prête',
|
||
'submitted' => 'Déposée',
|
||
'awarded' => 'Accordée',
|
||
'refused' => 'Refusée',
|
||
'withdrawn' => 'Abandonnée',
|
||
];
|
||
public const CATEGORIES = [
|
||
'official_form' => 'Formulaire officiel de l’organisme',
|
||
'statutes' => 'Statuts',
|
||
'prefecture' => 'Récépissé de préfecture',
|
||
'jo' => 'Publication au Journal officiel',
|
||
'rib' => 'RIB',
|
||
'insurance' => 'Attestation d’assurance',
|
||
'ag' => 'Procès-verbal d’assemblée générale',
|
||
'activity' => 'Rapport d’activité',
|
||
'accounts' => 'Comptes approuvés',
|
||
'budget' => 'Budget prévisionnel',
|
||
'other' => 'Autre',
|
||
];
|
||
public const REQUIRED = [
|
||
'official_form',
|
||
'statutes',
|
||
'prefecture',
|
||
'jo',
|
||
'rib',
|
||
'insurance',
|
||
'ag',
|
||
'activity',
|
||
'accounts',
|
||
'budget',
|
||
];
|
||
|
||
public static function statusLabel(string $key): string
|
||
{
|
||
return t('grant.status.' . $key, [], self::STATUSES[$key] ?? $key);
|
||
}
|
||
public static function categoryLabel(string $key): string
|
||
{
|
||
return t('grant.category.' . $key, [], self::CATEGORIES[$key] ?? $key);
|
||
}
|
||
|
||
public static function ensureYear(int $year): array
|
||
{
|
||
$db = DB::pdo();
|
||
$s = $db->prepare('INSERT OR IGNORE INTO grant_years(year) VALUES(?)');
|
||
$s->execute([$year]);
|
||
$s = $db->prepare('SELECT * FROM grant_years WHERE year=?');
|
||
$s->execute([$year]);
|
||
return $s->fetch() ?: [];
|
||
}
|
||
|
||
public static function statistics(int $year): array
|
||
{
|
||
$db = DB::pdo();
|
||
$start = sprintf('%04d-01-01', $year);
|
||
$end = sprintf('%04d-12-31', $year);
|
||
$scalar = static function (string $sql, array $params = []) use ($db): float {
|
||
$s = $db->prepare($sql);
|
||
$s->execute($params);
|
||
return (float) ($s->fetchColumn() ?: 0);
|
||
};
|
||
$p = [':start' => $start, ':end' => $end];
|
||
$entries = $scalar(
|
||
"SELECT COUNT(*) FROM (SELECT m.animal_id FROM animal_movements m JOIN animals a ON a.id=m.animal_id WHERE a.deleted_at IS NULL AND m.kind='entry' AND date(m.created_at) BETWEEN :start AND :end UNION SELECT a.id FROM animals a WHERE a.deleted_at IS NULL AND date(a.intake_date) BETWEEN :start AND :end)",
|
||
$p,
|
||
);
|
||
$exits = $scalar(
|
||
"SELECT (SELECT COUNT(*) FROM adoptions ad JOIN animals a ON a.id=ad.animal_id WHERE a.deleted_at IS NULL AND date(ad.adoption_date) BETWEEN :start AND :end)+(SELECT COUNT(*) FROM animal_deaths d JOIN animals a ON a.id=d.animal_id WHERE a.deleted_at IS NULL AND date(COALESCE(d.deceased_date,printf('%04d-01-01',d.death_year))) BETWEEN :start AND :end AND NOT EXISTS(SELECT 1 FROM adoptions ad WHERE ad.animal_id=d.animal_id))",
|
||
$p,
|
||
);
|
||
$adoptions = $scalar('SELECT COUNT(*) FROM adoptions WHERE date(adoption_date) BETWEEN :start AND :end', $p);
|
||
$deaths = $scalar(
|
||
"SELECT COUNT(*) FROM animal_deaths WHERE date(COALESCE(deceased_date,printf('%04d-01-01',death_year))) BETWEEN :start AND :end",
|
||
$p,
|
||
);
|
||
$births = $scalar(
|
||
"SELECT COUNT(*) FROM litter_kittens lk JOIN litters l ON l.id=lk.litter_id JOIN animals a ON a.id=lk.animal_id WHERE a.deleted_at IS NULL AND date(COALESCE(NULLIF(l.birth_date,''),NULLIF(a.birth_date,''))) BETWEEN :start AND :end",
|
||
$p,
|
||
);
|
||
$vaccinations = $scalar('SELECT COUNT(*) FROM vaccinations WHERE date(done_date) BETWEEN :start AND :end', $p);
|
||
$treatments = $scalar('SELECT COUNT(*) FROM treatments WHERE date(start_date) BETWEEN :start AND :end', $p);
|
||
$avg = $scalar(
|
||
"SELECT AVG(julianday(adoption_date)-julianday(COALESCE(NULLIF(a.intake_date,''),date(a.created_at)))) FROM adoptions ad JOIN animals a ON a.id=ad.animal_id WHERE date(adoption_date) BETWEEN :start AND :end AND date(adoption_date)>=date(COALESCE(NULLIF(a.intake_date,''),date(a.created_at)))",
|
||
$p,
|
||
);
|
||
$current = $scalar(
|
||
"SELECT COUNT(*) FROM animals WHERE deleted_at IS NULL AND archived_at IS NULL AND status NOT IN ('adopte','decede')",
|
||
);
|
||
return compact(
|
||
'entries',
|
||
'exits',
|
||
'adoptions',
|
||
'deaths',
|
||
'births',
|
||
'vaccinations',
|
||
'treatments',
|
||
'avg',
|
||
'current',
|
||
);
|
||
}
|
||
|
||
public static function detailedStatistics(int $year): array
|
||
{
|
||
$db = DB::pdo();
|
||
$start = sprintf('%04d-01-01', $year);
|
||
$end = sprintf('%04d-12-31', $year);
|
||
$p = [':start' => $start, ':end' => $end];
|
||
$rows = static function (string $sql, array $params) use ($db): array {
|
||
$s = $db->prepare($sql);
|
||
$s->execute($params);
|
||
return $s->fetchAll(PDO::FETCH_ASSOC);
|
||
};
|
||
$map = static function (array $items, string $key = 'label'): array {
|
||
$out = [];
|
||
foreach ($items as $item) {
|
||
$out[(string) $item[$key]] = (int) $item['total'];
|
||
}
|
||
return $out;
|
||
};
|
||
$base =
|
||
"WITH entry_sources AS (SELECT m.animal_id,date(m.created_at) entry_date FROM animal_movements m JOIN animals source_animal ON source_animal.id=m.animal_id WHERE source_animal.deleted_at IS NULL AND m.kind='entry' AND date(m.created_at) BETWEEN :start AND :end UNION SELECT id animal_id,date(intake_date) entry_date FROM animals WHERE deleted_at IS NULL AND date(intake_date) BETWEEN :start AND :end), entered AS (SELECT animal_id,MIN(entry_date) entry_date FROM entry_sources GROUP BY animal_id) ";
|
||
$sex = $map(
|
||
$rows(
|
||
$base .
|
||
"SELECT CASE a.sex WHEN 'F' THEN 'Femelles' WHEN 'M' THEN 'Mâles' ELSE 'Inconnu' END label,COUNT(*) total FROM entered e JOIN animals a ON a.id=e.animal_id GROUP BY a.sex ORDER BY total DESC",
|
||
$p,
|
||
),
|
||
);
|
||
$ages = $map(
|
||
$rows(
|
||
$base .
|
||
"SELECT CASE WHEN a.birth_date IS NULL THEN 'Non renseigné' WHEN (julianday(e.entry_date)-julianday(a.birth_date))/365.25<1 THEN 'Moins d’un an' WHEN (julianday(e.entry_date)-julianday(a.birth_date))/365.25<3 THEN '1 à 2 ans' WHEN (julianday(e.entry_date)-julianday(a.birth_date))/365.25<8 THEN '3 à 7 ans' ELSE '8 ans et plus' END label,COUNT(*) total FROM entered e JOIN animals a ON a.id=e.animal_id GROUP BY label ORDER BY total DESC",
|
||
$p,
|
||
),
|
||
);
|
||
$originRows = $rows(
|
||
$base . 'SELECT a.rescue_location_name,a.rescue_address FROM entered e JOIN animals a ON a.id=e.animal_id',
|
||
$p,
|
||
);
|
||
$origins = [];
|
||
foreach ($originRows as $originRow) {
|
||
$label = self::municipality((string) ($originRow['rescue_location_name'] ?: $originRow['rescue_address']));
|
||
$origins[$label] = ($origins[$label] ?? 0) + 1;
|
||
}
|
||
arsort($origins, SORT_NUMERIC);
|
||
$origins = array_slice($origins, 0, 12, true);
|
||
$deathCauses = $map(
|
||
$rows(
|
||
"SELECT cause_code label,COUNT(*) total FROM animal_deaths WHERE date(COALESCE(deceased_date,printf('%04d-01-01',death_year))) BETWEEN :start AND :end GROUP BY cause_code ORDER BY total DESC",
|
||
$p,
|
||
),
|
||
);
|
||
$stays = $map(
|
||
$rows(
|
||
"SELECT CASE WHEN julianday(ad.adoption_date)-julianday(COALESCE(NULLIF(a.intake_date,''),date(a.created_at)))<30 THEN 'Moins d’un mois' WHEN julianday(ad.adoption_date)-julianday(COALESCE(NULLIF(a.intake_date,''),date(a.created_at)))<90 THEN '1 à 3 mois' WHEN julianday(ad.adoption_date)-julianday(COALESCE(NULLIF(a.intake_date,''),date(a.created_at)))<180 THEN '3 à 6 mois' WHEN julianday(ad.adoption_date)-julianday(COALESCE(NULLIF(a.intake_date,''),date(a.created_at)))<365 THEN '6 à 12 mois' ELSE 'Plus d’un an' END label,COUNT(*) total FROM adoptions ad JOIN animals a ON a.id=ad.animal_id WHERE date(ad.adoption_date) BETWEEN :start AND :end AND date(ad.adoption_date)>=date(COALESCE(NULLIF(a.intake_date,''),date(a.created_at))) GROUP BY label ORDER BY total DESC",
|
||
$p,
|
||
),
|
||
);
|
||
return compact('sex', 'ages', 'origins', 'deathCauses', 'stays');
|
||
}
|
||
|
||
public static function comparison(int $year): array
|
||
{
|
||
$current = self::statistics($year);
|
||
$previous = self::statistics($year - 1);
|
||
$out = [];
|
||
foreach (['entries', 'exits', 'adoptions', 'deaths', 'births', 'vaccinations', 'treatments', 'avg'] as $key) {
|
||
$a = (float) $current[$key];
|
||
$b = (float) $previous[$key];
|
||
$out[$key] = [
|
||
'current' => $a,
|
||
'previous' => $b,
|
||
'difference' => $a - $b,
|
||
'percent' => $b != 0 ? (($a - $b) / $b) * 100 : null,
|
||
];
|
||
}
|
||
return $out;
|
||
}
|
||
|
||
public static function municipality(string $value): string
|
||
{
|
||
$value = trim(preg_replace('/\s+/u', ' ', $value) ?? '');
|
||
if ($value === '') {
|
||
return 'Non renseignée';
|
||
}
|
||
if (preg_match('/\b\d{5}\s+([^,;]+)(?:[,;]|$)/u', $value, $match)) {
|
||
$value = trim($match[1]);
|
||
} elseif (str_contains($value, ',')) {
|
||
$parts = array_values(array_filter(array_map('trim', explode(',', $value))));
|
||
$value = (string) end($parts);
|
||
}
|
||
$value = preg_replace('/^(?:FRANCE|FR)\s*[-–—]?\s*/iu', '', $value) ?? $value;
|
||
return $value !== '' ? mb_convert_case($value, MB_CASE_TITLE, 'UTF-8') : 'Non renseignée';
|
||
}
|
||
|
||
public static function applications(int $year): array
|
||
{
|
||
$s = DB::pdo()->prepare(
|
||
'SELECT * FROM grant_applications WHERE year=? ORDER BY COALESCE(deadline,\'9999-12-31\'), organization_name',
|
||
);
|
||
$s->execute([$year]);
|
||
return $s->fetchAll();
|
||
}
|
||
public static function documents(int $year): array
|
||
{
|
||
$s = DB::pdo()->prepare(
|
||
'SELECT d.*,u.display_name uploader,ga.organization_name application_name FROM grant_documents d LEFT JOIN users u ON u.id=d.uploaded_by LEFT JOIN grant_applications ga ON ga.id=d.application_id WHERE d.year IS NULL OR d.year=? ORDER BY d.category,d.created_at DESC',
|
||
);
|
||
$s->execute([$year]);
|
||
return $s->fetchAll();
|
||
}
|
||
public static function checklist(int $year): array
|
||
{
|
||
$applications = self::applications($year);
|
||
$documents = self::documents($year);
|
||
$today = date('Y-m-d');
|
||
$soon = date('Y-m-d', strtotime('+30 days'));
|
||
$result = [];
|
||
foreach ($applications as $application) {
|
||
$items = [];
|
||
$valid = 0;
|
||
$warnings = 0;
|
||
foreach (self::REQUIRED as $category) {
|
||
$matches = array_values(
|
||
array_filter($documents, static function (array $document) use ($category, $application): bool {
|
||
if ($document['category'] !== $category) {
|
||
return false;
|
||
}
|
||
if ($category === 'official_form') {
|
||
return (int) ($document['application_id'] ?? 0) === (int) $application['id'];
|
||
}
|
||
return empty($document['application_id']) ||
|
||
(int) $document['application_id'] === (int) $application['id'];
|
||
}),
|
||
);
|
||
usort(
|
||
$matches,
|
||
static fn(array $a, array $b): int => strcmp((string) $b['created_at'], (string) $a['created_at']),
|
||
);
|
||
$document = $matches[0] ?? null;
|
||
$state = 'missing';
|
||
if ($document) {
|
||
$expiry = (string) ($document['valid_until'] ?? '');
|
||
if ($expiry !== '' && $expiry < $today) {
|
||
$state = 'expired';
|
||
} elseif ($expiry !== '' && $expiry <= $soon) {
|
||
$state = 'expiring';
|
||
} else {
|
||
$state = 'valid';
|
||
}
|
||
}
|
||
if ($state === 'valid') {
|
||
$valid++;
|
||
} elseif ($state === 'expiring') {
|
||
$valid++;
|
||
$warnings++;
|
||
} else {
|
||
$warnings++;
|
||
}
|
||
$items[$category] = ['state' => $state, 'document' => $document];
|
||
}
|
||
$total = count(self::REQUIRED);
|
||
$result[(int) $application['id']] = [
|
||
'items' => $items,
|
||
'valid' => $valid,
|
||
'total' => $total,
|
||
'warnings' => $warnings,
|
||
'complete' => $valid === $total,
|
||
];
|
||
}
|
||
return $result;
|
||
}
|
||
public static function alerts(int $year): array
|
||
{
|
||
$applications = self::applications($year);
|
||
$checklist = self::checklist($year);
|
||
$today = date('Y-m-d');
|
||
$soon = date('Y-m-d', strtotime('+30 days'));
|
||
$deadlines = 0;
|
||
$overdue = 0;
|
||
$incomplete = 0;
|
||
$documents = 0;
|
||
foreach ($applications as $application) {
|
||
if (in_array($application['status'], ['awarded', 'refused', 'withdrawn'], true)) {
|
||
continue;
|
||
}
|
||
$deadline = (string) ($application['deadline'] ?? '');
|
||
if ($deadline !== '' && $deadline < $today) {
|
||
$overdue++;
|
||
} elseif ($deadline !== '' && $deadline <= $soon) {
|
||
$deadlines++;
|
||
}
|
||
if (!($checklist[(int) $application['id']]['complete'] ?? false)) {
|
||
$incomplete++;
|
||
}
|
||
}
|
||
foreach (self::documents($year) as $document) {
|
||
$expiry = (string) ($document['valid_until'] ?? '');
|
||
if ($expiry !== '' && $expiry <= $soon) {
|
||
$documents++;
|
||
}
|
||
}
|
||
return compact('deadlines', 'overdue', 'incomplete', 'documents');
|
||
}
|
||
}
|