237 lines
9.7 KiB
PHP
237 lines
9.7 KiB
PHP
<?php
|
||
|
||
declare(strict_types=1);
|
||
|
||
namespace App\Models;
|
||
|
||
use Throwable;
|
||
|
||
final class TeamModel extends BaseModel
|
||
{
|
||
public function all(?string $startDate = null, ?string $endDate = null): array
|
||
{
|
||
$sql = "SELECT e.id_equipe, e.id_structure, s.nom AS structure_nom,
|
||
e.type_equipe, e.libelle, e.date_debut, e.date_fin,
|
||
e.nombre_enfants_moins_6, e.nombre_enfants_6_plus,
|
||
e.ratio_moins_6, e.ratio_6_plus, e.minimum_agents,
|
||
e.pourcentage_diplomes_min, e.statut, e.commentaire,
|
||
GREATEST(e.minimum_agents,
|
||
CEIL(e.nombre_enfants_moins_6 / e.ratio_moins_6)
|
||
+ CEIL(e.nombre_enfants_6_plus / e.ratio_6_plus)
|
||
) AS agents_requis,
|
||
COUNT(em.id_agent) AS agents_affectes,
|
||
SUM(CASE WHEN a.est_diplome = TRUE THEN 1 ELSE 0 END) AS agents_diplomes,
|
||
SUM(CASE WHEN em.fonction_equipe = 'RESPONSABLE' THEN 1 ELSE 0 END) AS responsables
|
||
FROM equipe_pta e
|
||
JOIN structure s ON s.id_structure = e.id_structure
|
||
LEFT JOIN equipe_pta_membre em ON em.id_equipe = e.id_equipe
|
||
LEFT JOIN agent a ON a.id_agent = em.id_agent";
|
||
$params = [];
|
||
$conditions = [];
|
||
if ($startDate !== null) {
|
||
$conditions[] = 'e.date_fin >= :date_debut';
|
||
$params['date_debut'] = $startDate;
|
||
}
|
||
if ($endDate !== null) {
|
||
$conditions[] = 'e.date_debut <= :date_fin';
|
||
$params['date_fin'] = $endDate;
|
||
}
|
||
if ($conditions !== []) {
|
||
$sql .= ' WHERE ' . implode(' AND ', $conditions);
|
||
}
|
||
$sql .= " GROUP BY e.id_equipe, e.id_structure, s.nom, e.type_equipe, e.libelle,
|
||
e.date_debut, e.date_fin, e.nombre_enfants_moins_6,
|
||
e.nombre_enfants_6_plus, e.ratio_moins_6, e.ratio_6_plus,
|
||
e.minimum_agents, e.pourcentage_diplomes_min, e.statut,
|
||
e.commentaire
|
||
ORDER BY e.date_debut, s.nom, e.libelle";
|
||
$stmt = $this->pdo->prepare($sql);
|
||
$stmt->execute($params);
|
||
$teams = $stmt->fetchAll();
|
||
foreach ($teams as &$team) {
|
||
$team['members'] = $this->members((int) $team['id_equipe']);
|
||
$team['controls'] = $this->controls($team, $team['members']);
|
||
}
|
||
unset($team);
|
||
return $teams;
|
||
}
|
||
|
||
public function find(int $teamId): ?array
|
||
{
|
||
foreach ($this->all() as $team) {
|
||
if ((int) $team['id_equipe'] === $teamId) {
|
||
return $team;
|
||
}
|
||
}
|
||
return null;
|
||
}
|
||
|
||
public function create(array $data): int
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"INSERT INTO equipe_pta (
|
||
id_structure, type_equipe, libelle, date_debut, date_fin,
|
||
nombre_enfants_moins_6, nombre_enfants_6_plus,
|
||
ratio_moins_6, ratio_6_plus, minimum_agents,
|
||
pourcentage_diplomes_min, statut, commentaire
|
||
) VALUES (
|
||
:id_structure, :type_equipe, :libelle, :date_debut, :date_fin,
|
||
:nombre_enfants_moins_6, :nombre_enfants_6_plus,
|
||
:ratio_moins_6, :ratio_6_plus, :minimum_agents,
|
||
:pourcentage_diplomes_min, 'BROUILLON', :commentaire
|
||
)"
|
||
);
|
||
$stmt->execute($data);
|
||
return (int) $this->pdo->lastInsertId();
|
||
}
|
||
|
||
public function addMember(int $teamId, int $agentId, string $function, ?string $comment): void
|
||
{
|
||
$team = $this->teamDates($teamId);
|
||
if ($team === null) {
|
||
throw new \RuntimeException('Équipe introuvable.');
|
||
}
|
||
|
||
$forbidden = $this->pdo->prepare(
|
||
"SELECT 1
|
||
FROM agent_structure_souhait
|
||
WHERE id_agent = :agent_id
|
||
AND id_structure = :structure_id
|
||
AND type_souhait = 'INTERDICTION'
|
||
AND actif = TRUE
|
||
LIMIT 1"
|
||
);
|
||
$forbidden->execute(['agent_id' => $agentId, 'structure_id' => $team['id_structure']]);
|
||
if ($forbidden->fetchColumn() !== false) {
|
||
throw new \RuntimeException('Cet agent a une interdiction active pour ce lieu.');
|
||
}
|
||
|
||
// Les contraintes médicales et thérapeutiques restent prioritaires, mais
|
||
// l’affectation n’est pas bloquée automatiquement : elles sont signalées
|
||
// dans les contrôles de l’équipe pour validation humaine.
|
||
|
||
$stmt = $this->pdo->prepare(
|
||
"INSERT INTO equipe_pta_membre (id_equipe, id_agent, fonction_equipe, commentaire)
|
||
VALUES (:id_equipe, :id_agent, :fonction_equipe, :commentaire)
|
||
ON DUPLICATE KEY UPDATE
|
||
fonction_equipe = VALUES(fonction_equipe),
|
||
commentaire = VALUES(commentaire)"
|
||
);
|
||
$stmt->execute([
|
||
'id_equipe' => $teamId,
|
||
'id_agent' => $agentId,
|
||
'fonction_equipe' => $function,
|
||
'commentaire' => $comment,
|
||
]);
|
||
}
|
||
|
||
public function removeMember(int $teamId, int $agentId): bool
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
'DELETE FROM equipe_pta_membre WHERE id_equipe = :team_id AND id_agent = :agent_id'
|
||
);
|
||
$stmt->execute(['team_id' => $teamId, 'agent_id' => $agentId]);
|
||
return $stmt->rowCount() > 0;
|
||
}
|
||
|
||
public function setStatus(int $teamId, string $status): void
|
||
{
|
||
$stmt = $this->pdo->prepare('UPDATE equipe_pta SET statut = :statut WHERE id_equipe = :id');
|
||
$stmt->execute(['statut' => $status, 'id' => $teamId]);
|
||
}
|
||
|
||
private function members(int $teamId): array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT em.id_agent, em.fonction_equipe, em.commentaire,
|
||
a.matricule, a.nom, a.prenom, a.est_diplome,
|
||
a.diplome_libelle, a.adresse, a.id_structure,
|
||
po.code AS poste_code, po.libelle AS poste_libelle,
|
||
s.nom AS structure_principale_nom,
|
||
sw.type_souhait, sw.priorite, sw.distance_km,
|
||
(SELECT COUNT(*)
|
||
FROM agent_contrainte ac
|
||
WHERE ac.id_agent = a.id_agent
|
||
AND ac.actif = TRUE
|
||
AND ac.date_debut <= e.date_fin
|
||
AND COALESCE(ac.date_fin, '9999-12-31') >= e.date_debut) AS contraintes_actives
|
||
FROM equipe_pta_membre em
|
||
JOIN agent a ON a.id_agent = em.id_agent
|
||
LEFT JOIN poste_agent po ON po.id_poste = a.id_poste
|
||
LEFT JOIN structure s ON s.id_structure = a.id_structure
|
||
LEFT JOIN equipe_pta e ON e.id_equipe = em.id_equipe
|
||
LEFT JOIN agent_structure_souhait sw
|
||
ON sw.id_agent = a.id_agent
|
||
AND sw.id_structure = e.id_structure
|
||
AND sw.actif = TRUE
|
||
WHERE em.id_equipe = :team_id
|
||
ORDER BY FIELD(em.fonction_equipe, 'RESPONSABLE', 'ANIMATION', 'RESTAURATION_ENTRETIEN'), a.nom, a.prenom"
|
||
);
|
||
$stmt->execute(['team_id' => $teamId]);
|
||
return $stmt->fetchAll();
|
||
}
|
||
|
||
private function controls(array $team, array $members): array
|
||
{
|
||
$controls = [];
|
||
$required = (int) $team['agents_requis'];
|
||
$assigned = (int) $team['agents_affectes'];
|
||
if ($assigned < $required) {
|
||
$controls[] = [
|
||
'niveau' => 'ERREUR',
|
||
'code' => 'ENCADREMENT_INSUFFISANT',
|
||
'message' => sprintf('%d agent(s) affecté(s) pour %d requis.', $assigned, $required),
|
||
];
|
||
}
|
||
|
||
$qualified = (int) $team['agents_diplomes'];
|
||
$qualifiedRate = $assigned > 0 ? round(($qualified / $assigned) * 100, 2) : 0;
|
||
if ($qualifiedRate < (float) $team['pourcentage_diplomes_min']) {
|
||
$controls[] = [
|
||
'niveau' => 'ERREUR',
|
||
'code' => 'QUALIFICATION_INSUFFISANTE',
|
||
'message' => sprintf('%.2f %% d’agents diplômés pour un minimum paramétré à %.2f %%.', $qualifiedRate, (float) $team['pourcentage_diplomes_min']),
|
||
];
|
||
}
|
||
|
||
if ((int) $team['responsables'] < 1) {
|
||
$controls[] = [
|
||
'niveau' => 'ALERTE',
|
||
'code' => 'RESPONSABLE_MANQUANT',
|
||
'message' => 'Aucun responsable n’est identifié dans l’équipe.',
|
||
];
|
||
}
|
||
|
||
foreach ($members as $member) {
|
||
if ((int) ($member['contraintes_actives'] ?? 0) > 0) {
|
||
$controls[] = [
|
||
'niveau' => 'ALERTE',
|
||
'code' => 'CONTRAINTE_PRIORITAIRE',
|
||
'message' => sprintf('%s %s possède une préconisation médicale ou un temps partiel thérapeutique actif sur la période.', $member['prenom'], $member['nom']),
|
||
];
|
||
}
|
||
if (($member['type_souhait'] ?? null) === 'PREFERENCE') {
|
||
$controls[] = [
|
||
'niveau' => 'INFO',
|
||
'code' => 'PREFERENCE_RESPECTEE',
|
||
'message' => sprintf('%s %s a exprimé une préférence pour ce lieu.', $member['prenom'], $member['nom']),
|
||
];
|
||
}
|
||
}
|
||
|
||
if ($controls === []) {
|
||
$controls[] = ['niveau' => 'INFO', 'code' => 'OK', 'message' => 'Équipe conforme aux paramètres enregistrés.'];
|
||
}
|
||
return $controls;
|
||
}
|
||
|
||
private function teamDates(int $teamId): ?array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
'SELECT id_structure, date_debut, date_fin FROM equipe_pta WHERE id_equipe = :id LIMIT 1'
|
||
);
|
||
$stmt->execute(['id' => $teamId]);
|
||
return $stmt->fetch() ?: null;
|
||
}
|
||
}
|