178 lines
14 KiB
PHP
178 lines
14 KiB
PHP
<?php
|
||
|
||
declare(strict_types=1);
|
||
|
||
final class ExportService
|
||
{
|
||
private const DATASETS = [
|
||
'animals' => [
|
||
'Animaux',
|
||
'animaux.csv',
|
||
"SELECT a.id,a.internal_code code_interne,a.name nom,a.species espece,a.sex sexe,a.birth_date date_naissance,a.birth_is_estimated naissance_estimee,a.chip_id numero_identification,a.identification_type type_identification,a.identification_date date_identification,a.identification_registration_status statut_enregistrement_icad,a.adoption_availability disponibilite_adoption,a.adoption_available_from disponible_a_partir_du,a.adoption_unavailability_reason motif_indisponibilite,a.breed race,a.color robe,a.hair_type type_poils,a.compatibility_dogs compatible_chiens,a.compatibility_cats compatible_chats,a.compatibility_children compatible_enfants,a.house_trained proprete,a.status statut,a.refuge_room salle_refuge,a.care_box_key box_soin,a.fiv_status statut_fiv,a.felv_status statut_felv,a.sterilization_status statut_sterilisation,a.sterilization_date date_sterilisation,sv.name veterinaire_sterilisation,sc.name cabinet_sterilisation,a.intake_date date_entree,a.intake_type mode_entree,CASE WHEN a.intake_owner_care_home=1 THEN 'owner_care_home' ELSE a.intake_reason END motif_entree,dep.name deposant,a.intake_circumstances circonstances_entree,a.quarantine_until fin_quarantaine,a.rescue_location_name commune_origine,a.rescue_address adresse_origine,a.notes,a.created_at cree_le,a.updated_at modifie_le,a.archived_at archive_le,a.deleted_at supprime_le,a.deletion_reason motif_suppression FROM animals a LEFT JOIN directory_contacts dep ON dep.id=a.depositor_contact_id LEFT JOIN directory_contacts sv ON sv.id=a.sterilization_vet_contact_id LEFT JOIN directory_contacts sc ON sc.id=a.sterilization_clinic_contact_id ORDER BY a.id",
|
||
],
|
||
'contacts' => [
|
||
'Annuaire',
|
||
'annuaire.csv',
|
||
'SELECT c.id,c.kind type,c.name nom,GROUP_CONCAT(DISTINCT r.role) roles,c.phone telephone,c.email,c.address adresse,c.postal_code code_postal,c.city ville,c.country pays,o.name structure_liee,c.notes,c.created_at cree_le,c.updated_at modifie_le,c.deleted_at supprime_le FROM directory_contacts c LEFT JOIN directory_contact_roles r ON r.contact_id=c.id LEFT JOIN directory_contacts o ON o.id=c.organization_id GROUP BY c.id ORDER BY c.name COLLATE NOCASE',
|
||
],
|
||
'adoptions' => [
|
||
'Adoptions',
|
||
'adoptions.csv',
|
||
'SELECT ad.id,a.internal_code code_animal,a.name animal,ad.adoption_date date_adoption,ad.adopter_name adoptant,ad.adopter_phone telephone,ad.adopter_email email,ad.adopter_address adresse,ad.adopter_postal_code code_postal,ad.adopter_city ville,ad.adopter_country pays,ad.notes,ad.created_at cree_le FROM adoptions ad JOIN animals a ON a.id=ad.animal_id ORDER BY ad.adoption_date,ad.id',
|
||
],
|
||
'placements' => [
|
||
'Parcours d’accueil et retours',
|
||
'placements-retours.csv',
|
||
'SELECT ap.id,a.internal_code code_animal,a.name animal,ap.event_type type_evenement,ap.event_date date_evenement,dc.name contact,dc.city ville,ap.reason motif,ap.notes,COALESCE(u.display_name,u.username) saisi_par,ap.created_at cree_le FROM animal_placements ap JOIN animals a ON a.id=ap.animal_id LEFT JOIN directory_contacts dc ON dc.id=ap.contact_id LEFT JOIN users u ON u.id=ap.created_by ORDER BY ap.event_date,ap.id',
|
||
],
|
||
'medical' => [
|
||
'Notes médicales',
|
||
'notes-medicales.csv',
|
||
'SELECT mn.id,a.internal_code code_animal,a.name animal,mn.noted_at date_note,mn.kind type,mn.reason motif,mn.symptoms symptomes,mn.exam examen,mn.diagnosis,mn.plan conduite,mn.temperature_c temperature,mn.weight_kg poids,vet.name veterinaire,clinic.name cabinet,COALESCE(u.display_name,u.username) saisi_par,mn.created_at cree_le FROM medical_notes mn JOIN animals a ON a.id=mn.animal_id LEFT JOIN directory_contacts vet ON vet.id=mn.vet_contact_id LEFT JOIN directory_contacts clinic ON clinic.id=mn.clinic_contact_id LEFT JOIN users u ON u.id=mn.created_by ORDER BY mn.noted_at,mn.id',
|
||
],
|
||
'treatments' => [
|
||
'Traitements',
|
||
'traitements.csv',
|
||
'SELECT t.id,a.internal_code code_animal,a.name animal,m.name medicament,t.dose_text dose,t.route voie,t.start_date debut,t.end_date fin,t.ongoing en_cours,t.give_morning matin,t.give_evening soir,t.give_as_needed si_besoin,vet.name veterinaire,clinic.name cabinet,t.notes FROM treatments t JOIN animals a ON a.id=t.animal_id JOIN ref_medications m ON m.id=t.medication_id LEFT JOIN directory_contacts vet ON vet.id=t.vet_contact_id LEFT JOIN directory_contacts clinic ON clinic.id=t.clinic_contact_id ORDER BY t.start_date,t.id',
|
||
],
|
||
'vaccinations' => [
|
||
'Vaccinations',
|
||
'vaccinations.csv',
|
||
'SELECT v.id,a.internal_code code_animal,a.name animal,rv.name vaccin,v.done_date date_realisation,v.due_date prochain_rappel,v.manufacturer fabricant,v.lot,v.batch_expires_on expiration_lot,COALESCE(v.administered_by_name,au.display_name,au.username) administre_par,COALESCE(cu.display_name,cu.username) saisi_par,vet.name veterinaire,clinic.name cabinet,v.notes FROM vaccinations v JOIN animals a ON a.id=v.animal_id JOIN ref_vaccines rv ON rv.id=v.vaccine_id LEFT JOIN directory_contacts vet ON vet.id=v.vet_contact_id LEFT JOIN directory_contacts clinic ON clinic.id=v.clinic_contact_id LEFT JOIN users au ON au.id=v.administered_by_user_id LEFT JOIN users cu ON cu.id=v.created_by ORDER BY v.done_date,v.id',
|
||
],
|
||
'deaths' => [
|
||
'Décès',
|
||
'deces.csv',
|
||
'SELECT d.animal_id,a.internal_code code_animal,a.name animal,d.deceased_date date_deces,d.date_precision precision_date,d.death_age_value age_deces_valeur,d.death_age_unit age_deces_unite,d.cause_code cause,d.cause_details details_cause,d.occurred_in_care sous_responsabilite,d.place_type type_lieu,d.place_details lieu,d.euthanized euthanasie,vet.name veterinaire,crem.name crematorium,d.body_disposition devenir_corps,d.cremation_date date_cremation,d.memorial_medal_color couleur_medaille,d.memorial_medal_count nombre_medailles,d.recovered_source source_recuperation,d.notes,COALESCE(u.display_name,u.username) enregistre_par,d.created_at cree_le,d.updated_at modifie_le FROM animal_deaths d JOIN animals a ON a.id=d.animal_id LEFT JOIN directory_contacts vet ON vet.id=d.veterinarian_contact_id LEFT JOIN directory_contacts crem ON crem.id=d.crematorium_contact_id LEFT JOIN users u ON u.id=d.created_by ORDER BY d.deceased_date,d.animal_id',
|
||
],
|
||
'measurements' => [
|
||
'Mesures',
|
||
'mesures.csv',
|
||
'SELECT me.id,a.internal_code code_animal,a.name animal,me.measured_at date_mesure,me.type,me.value valeur,me.unit unite,me.notes,COALESCE(u.display_name,u.username) saisi_par FROM measurements me JOIN animals a ON a.id=me.animal_id LEFT JOIN users u ON u.id=me.created_by ORDER BY me.measured_at,me.id',
|
||
],
|
||
'litters' => [
|
||
'Portées',
|
||
'portees.csv',
|
||
'SELECT l.id,m.internal_code code_mere,m.name mere,f.internal_code code_pere,f.name pere,l.birth_date date_naissance,GROUP_CONCAT(k.internal_code) codes_chatons,GROUP_CONCAT(k.name) chatons,l.notes,l.created_at cree_le FROM litters l LEFT JOIN animals m ON m.id=l.mother_id LEFT JOIN animals f ON f.id=l.father_id LEFT JOIN litter_kittens lk ON lk.litter_id=l.id LEFT JOIN animals k ON k.id=lk.animal_id GROUP BY l.id ORDER BY l.birth_date,l.id',
|
||
],
|
||
'bonded' => [
|
||
'Groupes inséparables',
|
||
'groupes-inseparables.csv',
|
||
'SELECT g.id,g.name nom_groupe,g.active actif,GROUP_CONCAT(a.internal_code) codes_animaux,GROUP_CONCAT(a.name) animaux,g.notes,g.created_at cree_le,g.dissolved_at dissous_le FROM bonded_groups g LEFT JOIN bonded_group_members gm ON gm.group_id=g.id LEFT JOIN animals a ON a.id=gm.animal_id GROUP BY g.id ORDER BY g.id',
|
||
],
|
||
'care_rounds' => [
|
||
'Tournées',
|
||
'tournees.csv',
|
||
'SELECT r.id,r.round_date date_tournee,r.period periode,a.internal_code code_animal,a.name animal,o.food alimentation,o.water eau,o.urine,o.stool selles,o.general_state etat_general,o.comment,o.checked_at valide_le,COALESCE(u.display_name,u.username,r.performed_by) effectue_par FROM care_rounds r LEFT JOIN care_round_observations o ON o.round_id=r.id LEFT JOIN animals a ON a.id=o.animal_id LEFT JOIN users u ON u.id=r.performed_by_user_id ORDER BY r.round_date,r.period,o.checked_at',
|
||
],
|
||
'administrations' => [
|
||
'Administrations de traitements',
|
||
'administrations-traitements.csv',
|
||
'SELECT ta.id,r.round_date date_tournee,r.period periode,a.internal_code code_animal,a.name animal,m.name medicament,ta.status statut,ta.comment,ta.administered_at administre_le FROM treatment_administrations ta JOIN care_rounds r ON r.id=ta.round_id JOIN animals a ON a.id=ta.animal_id JOIN treatments t ON t.id=ta.treatment_id JOIN ref_medications m ON m.id=t.medication_id ORDER BY r.round_date,r.period,ta.id',
|
||
],
|
||
'media_index' => [
|
||
'Index des médias',
|
||
'index-medias.csv',
|
||
"SELECT 'animal' type_media,p.id,a.internal_code code_animal,a.name animal,p.filename fichier,p.original_name nom_original,p.mime,p.size_bytes taille_octets,p.is_primary principale,p.is_public publique,p.caption legende,p.care_round_id tournee_id,NULL note_medicale_id,p.created_at ajoute_le FROM animal_photos p JOIN animals a ON a.id=p.animal_id UNION ALL SELECT 'medical',mp.id,a.internal_code,a.name,mp.filename,NULL,NULL,NULL,0,0,NULL,NULL,mp.medical_note_id,mp.created_at FROM medical_photos mp JOIN animals a ON a.id=mp.animal_id ORDER BY 14,2",
|
||
],
|
||
'movements' => [
|
||
'Mouvements',
|
||
'mouvements.csv',
|
||
'SELECT m.id,a.internal_code code_animal,a.name animal,m.kind type,m.place emplacement,m.lieu motif,m.contact_name contact,m.contact_phone telephone,m.contact_email email,m.note,m.created_at date_mouvement FROM animal_movements m JOIN animals a ON a.id=m.animal_id ORDER BY m.created_at,m.id',
|
||
],
|
||
'locations' => [
|
||
'Historique des lieux de vie',
|
||
'historique-emplacements.csv',
|
||
'SELECT lh.id,a.internal_code code_animal,a.name animal,lh.from_status ancien_statut,lh.from_refuge_room ancienne_salle,lh.from_care_box_key ancien_box,lh.from_address ancienne_adresse,lh.to_status nouveau_statut,lh.to_refuge_room nouvelle_salle,lh.to_care_box_key nouveau_box,lh.to_address nouvelle_adresse,lh.reason motif,lh.source origine_action,COALESCE(u.display_name,u.username) utilisateur,lh.moved_at date_deplacement FROM animal_location_history lh JOIN animals a ON a.id=lh.animal_id LEFT JOIN users u ON u.id=lh.user_id ORDER BY lh.moved_at,lh.id',
|
||
],
|
||
'history' => [
|
||
'Historique',
|
||
'historique.csv',
|
||
'SELECT h.id,a.internal_code code_animal,a.name animal,h.type,h.label,h.details,COALESCE(u.display_name,u.username) utilisateur,h.created_at date_action FROM animal_history h JOIN animals a ON a.id=h.animal_id LEFT JOIN users u ON u.id=h.user_id ORDER BY h.created_at,h.id',
|
||
],
|
||
];
|
||
|
||
public static function catalog(): array
|
||
{
|
||
foreach (self::DATASETS as $key => [$label, $file]) {
|
||
$out[$key] = ['label' => t('export.' . $key), 'filename' => $file];
|
||
}
|
||
return $out ?? [];
|
||
}
|
||
public static function downloadCsv(string $key): void
|
||
{
|
||
[$label, $filename] = self::definition($key);
|
||
header('Content-Type: text/csv; charset=UTF-8');
|
||
header('Content-Disposition: attachment; filename="' . $filename . '"');
|
||
header('X-Content-Type-Options: nosniff');
|
||
$stream = fopen('php://output', 'wb');
|
||
self::write($stream, $key);
|
||
fclose($stream);
|
||
}
|
||
public static function downloadZip(): void
|
||
{
|
||
if (!class_exists(ZipArchive::class)) {
|
||
throw new RuntimeException(t('service.export.zip_missing'));
|
||
}
|
||
$path = tempnam(sys_get_temp_dir(), 'globinours-export-');
|
||
$zip = new ZipArchive();
|
||
if ($zip->open($path, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) {
|
||
throw new RuntimeException(t('service.export.prepare_failed'));
|
||
}
|
||
try {
|
||
foreach (self::DATASETS as $key => [, $filename]) {
|
||
$stream = fopen('php://temp', 'w+b');
|
||
self::write($stream, $key);
|
||
rewind($stream);
|
||
$zip->addFromString($filename, stream_get_contents($stream));
|
||
fclose($stream);
|
||
}
|
||
$zip->addFromString(
|
||
'LISEZ-MOI.txt',
|
||
"Export de portabilité Globinours\r\nGénéré le " .
|
||
date('d/m/Y à H:i') .
|
||
"\r\nEncodage : UTF-8 avec séparateur point-virgule.\r\nLes comptes utilisateurs, mots de passe, médias et secrets ne sont pas inclus.\r\n",
|
||
);
|
||
} finally {
|
||
$zip->close();
|
||
}
|
||
header('Content-Type: application/zip');
|
||
header('Content-Disposition: attachment; filename="globinours-export-' . date('Y-m-d-His') . '.zip"');
|
||
header('Content-Length: ' . filesize($path));
|
||
header('X-Content-Type-Options: nosniff');
|
||
readfile($path);
|
||
@unlink($path);
|
||
}
|
||
private static function write($stream, string $key): void
|
||
{
|
||
[, , $sql] = self::definition($key);
|
||
fwrite($stream, "\xEF\xBB\xBF");
|
||
$stmt = DB::pdo()->query($sql);
|
||
$headers = [];
|
||
for ($i = 0; $i < $stmt->columnCount(); $i++) {
|
||
$headers[] = (string) ($stmt->getColumnMeta($i)['name'] ?? 'colonne_' . $i);
|
||
}
|
||
fputcsv($stream, $headers, ';', '"', '\\', "\r\n");
|
||
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
|
||
fputcsv($stream, array_map([self::class, 'safeCell'], $row), ';', '"', '\\', "\r\n");
|
||
}
|
||
}
|
||
private static function safeCell(mixed $value): string
|
||
{
|
||
if (is_int($value) || is_float($value)) {
|
||
return (string) $value;
|
||
}
|
||
$value = $value === null ? '' : (string) $value;
|
||
if ($value !== '' && in_array($value[0], ['=', '+', '-', '@'], true)) {
|
||
$value = "'" . $value;
|
||
}
|
||
return $value;
|
||
}
|
||
private static function definition(string $key): array
|
||
{
|
||
if (!isset(self::DATASETS[$key])) {
|
||
throw new InvalidArgumentException(t('service.export.unknown_dataset'));
|
||
}
|
||
return self::DATASETS[$key];
|
||
}
|
||
}
|