115 lines
4.0 KiB
PHP
115 lines
4.0 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Models;
|
|
|
|
final class StructureModel extends BaseModel
|
|
{
|
|
public function allActive(): array
|
|
{
|
|
return $this->pdo->query(
|
|
'SELECT id_structure, code, nom, adresse, type_affectation FROM structure WHERE actif = TRUE ORDER BY nom'
|
|
)->fetchAll();
|
|
}
|
|
|
|
public function findActive(int $id): ?array
|
|
{
|
|
$stmt = $this->pdo->prepare(
|
|
'SELECT id_structure, code, nom, adresse, type_affectation FROM structure WHERE id_structure = :id AND actif = TRUE LIMIT 1'
|
|
);
|
|
$stmt->execute(['id' => $id]);
|
|
return $stmt->fetch() ?: null;
|
|
}
|
|
|
|
public function codeExists(string $code): bool
|
|
{
|
|
$stmt = $this->pdo->prepare('SELECT 1 FROM structure WHERE code = :code LIMIT 1');
|
|
$stmt->execute(['code' => $code]);
|
|
return $stmt->fetchColumn() !== false;
|
|
}
|
|
|
|
public function create(string $code, string $name, ?string $address, string $typeAffectation): array
|
|
{
|
|
$stmt = $this->pdo->prepare(
|
|
'INSERT INTO structure (code, nom, adresse, type_affectation, actif)
|
|
VALUES (:code, :nom, :adresse, :type_affectation, TRUE)'
|
|
);
|
|
$stmt->execute([
|
|
'code' => $code,
|
|
'nom' => $name,
|
|
'adresse' => $address,
|
|
'type_affectation' => $typeAffectation,
|
|
]);
|
|
|
|
return [
|
|
'id_structure' => (int) $this->pdo->lastInsertId(),
|
|
'code' => $code,
|
|
'nom' => $name,
|
|
'adresse' => $address,
|
|
'type_affectation' => $typeAffectation,
|
|
];
|
|
}
|
|
|
|
public function updateType(int $structureId, string $typeAffectation): void
|
|
{
|
|
$stmt = $this->pdo->prepare(
|
|
'UPDATE structure SET type_affectation = :type_affectation WHERE id_structure = :id_structure'
|
|
);
|
|
$stmt->execute([
|
|
'type_affectation' => $typeAffectation,
|
|
'id_structure' => $structureId,
|
|
]);
|
|
}
|
|
|
|
public function agentsForOverview(int $structureId, int $year, int $week): array
|
|
{
|
|
$stmt = $this->pdo->prepare(
|
|
"SELECT DISTINCT a.id_agent, a.matricule, a.nom, a.prenom,
|
|
a.id_structure AS id_structure_principale
|
|
FROM agent a
|
|
LEFT JOIN planning p
|
|
ON p.id_agent = a.id_agent
|
|
AND p.id_structure = :structure_id_planning
|
|
LEFT JOIN semaine sem
|
|
ON sem.id_semaine = p.id_semaine
|
|
AND sem.annee = :annee
|
|
AND sem.numero_semaine = :semaine
|
|
WHERE a.actif = TRUE
|
|
AND (a.id_structure = :structure_id_default OR sem.id_semaine IS NOT NULL)
|
|
ORDER BY a.nom, a.prenom"
|
|
);
|
|
$stmt->execute([
|
|
'structure_id_planning' => $structureId,
|
|
'structure_id_default' => $structureId,
|
|
'annee' => $year,
|
|
'semaine' => $week,
|
|
]);
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
public function entriesForOverview(int $structureId, int $year, int $week): array
|
|
{
|
|
$stmt = $this->pdo->prepare(
|
|
"SELECT p.id_agent, p.statut, c.date_jour,
|
|
TIME_FORMAT(c.heure_debut, '%H:%i') AS heure_debut,
|
|
TIME_FORMAT(c.heure_fin, '%H:%i') AS heure_fin,
|
|
m.code AS motif_code, m.libelle AS motif_libelle, m.compte_dans_quota
|
|
FROM planning p
|
|
INNER JOIN semaine s ON s.id_semaine = p.id_semaine
|
|
INNER JOIN creneau_horaire c ON c.id_planning = p.id_planning
|
|
INNER JOIN motif_planning m ON m.id_motif = c.id_motif
|
|
WHERE p.id_structure = :structure_id
|
|
AND s.annee = :annee
|
|
AND s.numero_semaine = :semaine
|
|
ORDER BY p.id_agent, c.date_jour, c.heure_debut"
|
|
);
|
|
$stmt->execute([
|
|
'structure_id' => $structureId,
|
|
'annee' => $year,
|
|
'semaine' => $week,
|
|
]);
|
|
return $stmt->fetchAll();
|
|
}
|
|
}
|