987 lines
41 KiB
PHP
987 lines
41 KiB
PHP
<?php
|
||
|
||
declare(strict_types=1);
|
||
|
||
namespace App\Models;
|
||
|
||
use DateTimeImmutable;
|
||
use Throwable;
|
||
|
||
final class PlanningModel extends BaseModel
|
||
{
|
||
public function findByContext(int $structureId, int $agentId, int $year, int $week): ?array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT p.id_planning, p.statut, p.commentaire, s.date_debut, s.date_fin
|
||
FROM planning p
|
||
INNER JOIN semaine s ON s.id_semaine = p.id_semaine
|
||
WHERE p.id_structure = :structure_id
|
||
AND p.id_agent = :agent_id
|
||
AND s.annee = :annee
|
||
AND s.numero_semaine = :semaine
|
||
LIMIT 1"
|
||
);
|
||
$stmt->execute([
|
||
'structure_id' => $structureId,
|
||
'agent_id' => $agentId,
|
||
'annee' => $year,
|
||
'semaine' => $week,
|
||
]);
|
||
return $stmt->fetch() ?: null;
|
||
}
|
||
|
||
public function entries(int $planningId): array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT c.id_creneau, c.date_jour,
|
||
TIME_FORMAT(c.heure_debut, '%H:%i') AS heure_debut,
|
||
TIME_FORMAT(c.heure_fin, '%H:%i') AS heure_fin,
|
||
m.id_motif, m.code AS motif_code, m.libelle AS motif_libelle,
|
||
m.compte_dans_quota
|
||
FROM creneau_horaire c
|
||
INNER JOIN motif_planning m ON m.id_motif = c.id_motif
|
||
WHERE c.id_planning = :planning_id
|
||
ORDER BY c.date_jour, c.heure_debut, c.heure_fin"
|
||
);
|
||
$stmt->execute(['planning_id' => $planningId]);
|
||
return $stmt->fetchAll();
|
||
}
|
||
|
||
public function otherLocationEntries(int $agentId, int $year, int $week, int $structureId): array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT p.id_planning, p.statut,
|
||
st.id_structure, st.code AS structure_code, st.nom AS structure_nom,
|
||
st.type_affectation AS structure_type_affectation,
|
||
c.id_creneau, c.date_jour,
|
||
TIME_FORMAT(c.heure_debut, '%H:%i') AS heure_debut,
|
||
TIME_FORMAT(c.heure_fin, '%H:%i') AS heure_fin,
|
||
m.id_motif, m.code AS motif_code, m.libelle AS motif_libelle,
|
||
m.compte_dans_quota
|
||
FROM planning p
|
||
INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
|
||
INNER JOIN structure st ON st.id_structure = p.id_structure
|
||
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_agent = :agent_id
|
||
AND sem.annee = :annee
|
||
AND sem.numero_semaine = :semaine
|
||
AND p.id_structure <> :structure_id
|
||
ORDER BY c.date_jour, c.heure_debut, st.nom"
|
||
);
|
||
$stmt->execute([
|
||
'agent_id' => $agentId,
|
||
'annee' => $year,
|
||
'semaine' => $week,
|
||
'structure_id' => $structureId,
|
||
]);
|
||
return $stmt->fetchAll();
|
||
}
|
||
|
||
public function saveDraft(int $structureId, int $agentId, int $year, int $weekNumber, array $entries): int
|
||
{
|
||
$weekStart = (new DateTimeImmutable())->setISODate($year, $weekNumber, 1)->setTime(0, 0);
|
||
$weekEnd = $weekStart->modify('+6 days');
|
||
|
||
try {
|
||
$this->pdo->beginTransaction();
|
||
|
||
$weekStmt = $this->pdo->prepare(
|
||
"INSERT INTO semaine (annee, numero_semaine, date_debut, date_fin, statut)
|
||
VALUES (:annee, :numero, :date_debut, :date_fin, 'OUVERTE')
|
||
ON DUPLICATE KEY UPDATE id_semaine = LAST_INSERT_ID(id_semaine)"
|
||
);
|
||
$weekStmt->execute([
|
||
'annee' => $year,
|
||
'numero' => $weekNumber,
|
||
'date_debut' => $weekStart->format('Y-m-d'),
|
||
'date_fin' => $weekEnd->format('Y-m-d'),
|
||
]);
|
||
$weekId = (int) $this->pdo->lastInsertId();
|
||
|
||
if ($weekId === 0) {
|
||
$findWeek = $this->pdo->prepare(
|
||
'SELECT id_semaine FROM semaine WHERE annee = :annee AND numero_semaine = :numero'
|
||
);
|
||
$findWeek->execute(['annee' => $year, 'numero' => $weekNumber]);
|
||
$weekId = (int) $findWeek->fetchColumn();
|
||
}
|
||
|
||
$planningStmt = $this->pdo->prepare(
|
||
"SELECT id_planning, statut
|
||
FROM planning
|
||
WHERE id_agent = :agent_id
|
||
AND id_semaine = :semaine_id
|
||
AND id_structure = :structure_id
|
||
FOR UPDATE"
|
||
);
|
||
$planningStmt->execute([
|
||
'agent_id' => $agentId,
|
||
'semaine_id' => $weekId,
|
||
'structure_id' => $structureId,
|
||
]);
|
||
$planning = $planningStmt->fetch();
|
||
|
||
if ($planning && $planning['statut'] === 'VALIDE') {
|
||
throw new \DomainException('Ce planning est déjà validé et doit être rouvert avant modification.');
|
||
}
|
||
|
||
if ($planning) {
|
||
$planningId = (int) $planning['id_planning'];
|
||
$delete = $this->pdo->prepare('DELETE FROM creneau_horaire WHERE id_planning = :planning_id');
|
||
$delete->execute(['planning_id' => $planningId]);
|
||
} else {
|
||
$insertPlanning = $this->pdo->prepare(
|
||
"INSERT INTO planning (id_agent, id_semaine, id_structure, statut)
|
||
VALUES (:agent_id, :semaine_id, :structure_id, 'BROUILLON')"
|
||
);
|
||
$insertPlanning->execute([
|
||
'agent_id' => $agentId,
|
||
'semaine_id' => $weekId,
|
||
'structure_id' => $structureId,
|
||
]);
|
||
$planningId = (int) $this->pdo->lastInsertId();
|
||
}
|
||
|
||
$insertEntry = $this->pdo->prepare(
|
||
"INSERT INTO creneau_horaire (id_planning, id_motif, date_jour, heure_debut, heure_fin)
|
||
VALUES (:planning_id, :motif_id, :date_jour, :heure_debut, :heure_fin)"
|
||
);
|
||
|
||
foreach ($entries as $entry) {
|
||
$insertEntry->execute([
|
||
'planning_id' => $planningId,
|
||
'motif_id' => $entry['motif_id'],
|
||
'date_jour' => $entry['date'],
|
||
'heure_debut' => $entry['start'],
|
||
'heure_fin' => $entry['end'],
|
||
]);
|
||
}
|
||
|
||
$this->pdo->commit();
|
||
return $planningId;
|
||
} catch (Throwable $e) {
|
||
if ($this->pdo->inTransaction()) {
|
||
$this->pdo->rollBack();
|
||
}
|
||
throw $e;
|
||
}
|
||
}
|
||
|
||
public function pendingValidation(): array
|
||
{
|
||
$stmt = $this->pdo->query(
|
||
"SELECT p.id_planning, p.id_agent, p.id_structure, p.statut,
|
||
p.date_creation, p.date_modification,
|
||
a.matricule, a.nom AS agent_nom, a.prenom AS agent_prenom,
|
||
st.nom AS structure_nom, st.type_affectation AS structure_type_affectation,
|
||
sem.annee, sem.numero_semaine, sem.date_debut, sem.date_fin,
|
||
DATE_ADD(sem.date_debut, INTERVAL 4 DAY) AS date_fin_ouvrable,
|
||
COUNT(c.id_creneau) AS nombre_creneaux,
|
||
COALESCE(SUM(TIMESTAMPDIFF(
|
||
MINUTE,
|
||
CONCAT(c.date_jour, ' ', c.heure_debut),
|
||
CONCAT(c.date_jour, ' ', c.heure_fin)
|
||
)), 0) AS total_minutes,
|
||
COALESCE(SUM(CASE WHEN m.compte_dans_quota = 1 THEN TIMESTAMPDIFF(
|
||
MINUTE,
|
||
CONCAT(c.date_jour, ' ', c.heure_debut),
|
||
CONCAT(c.date_jour, ' ', c.heure_fin)
|
||
) ELSE 0 END), 0) AS minutes_decomptees
|
||
FROM planning p
|
||
INNER JOIN agent a ON a.id_agent = p.id_agent
|
||
INNER JOIN structure st ON st.id_structure = p.id_structure
|
||
INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
|
||
LEFT JOIN creneau_horaire c ON c.id_planning = p.id_planning
|
||
LEFT JOIN motif_planning m ON m.id_motif = c.id_motif
|
||
WHERE p.statut = 'BROUILLON'
|
||
GROUP BY p.id_planning, p.id_agent, p.id_structure, p.statut,
|
||
p.date_creation, p.date_modification,
|
||
a.matricule, a.nom, a.prenom,
|
||
st.nom, st.type_affectation,
|
||
sem.annee, sem.numero_semaine, sem.date_debut, sem.date_fin
|
||
HAVING COUNT(c.id_creneau) > 0
|
||
ORDER BY sem.date_debut ASC, a.nom ASC, a.prenom ASC, st.nom ASC"
|
||
);
|
||
|
||
return $stmt->fetchAll();
|
||
}
|
||
|
||
|
||
/**
|
||
* Plannings en brouillon de l'agent qui contiennent au moins un créneau
|
||
* dans l'année civile demandée.
|
||
*/
|
||
public function pendingValidationForAgentYear(int $agentId, int $year): array
|
||
{
|
||
$start = sprintf('%04d-01-01', $year);
|
||
$end = sprintf('%04d-12-31', $year);
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT p.id_planning, p.id_agent, p.id_structure, p.statut,
|
||
st.nom AS structure_nom,
|
||
sem.annee, sem.numero_semaine, sem.date_debut, sem.date_fin,
|
||
COUNT(c.id_creneau) AS nombre_creneaux,
|
||
COALESCE(SUM(TIMESTAMPDIFF(
|
||
MINUTE,
|
||
CONCAT(c.date_jour, ' ', c.heure_debut),
|
||
CONCAT(c.date_jour, ' ', c.heure_fin)
|
||
)), 0) AS total_minutes
|
||
FROM planning p
|
||
INNER JOIN structure st ON st.id_structure = p.id_structure
|
||
INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
|
||
LEFT JOIN creneau_horaire c ON c.id_planning = p.id_planning
|
||
WHERE p.id_agent = :agent_id
|
||
AND p.statut = 'BROUILLON'
|
||
AND EXISTS (
|
||
SELECT 1
|
||
FROM creneau_horaire cy
|
||
WHERE cy.id_planning = p.id_planning
|
||
AND cy.date_jour BETWEEN :date_debut AND :date_fin
|
||
)
|
||
GROUP BY p.id_planning, p.id_agent, p.id_structure, p.statut,
|
||
st.nom, sem.annee, sem.numero_semaine, sem.date_debut, sem.date_fin
|
||
HAVING COUNT(c.id_creneau) > 0
|
||
ORDER BY sem.date_debut, st.nom"
|
||
);
|
||
$stmt->execute([
|
||
'agent_id' => $agentId,
|
||
'date_debut' => $start,
|
||
'date_fin' => $end,
|
||
]);
|
||
return $stmt->fetchAll();
|
||
}
|
||
|
||
/**
|
||
* Chevauchements avec n'importe quel autre planning de l'agent,
|
||
* qu'il soit déjà validé ou encore en brouillon.
|
||
*/
|
||
public function externalOverlaps(int $planningId): array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT DISTINCT c1.id_creneau AS c1, c2.id_creneau AS c2,
|
||
c1.date_jour, st.nom AS structure_nom, p2.statut AS autre_statut
|
||
FROM planning p1
|
||
INNER JOIN creneau_horaire c1 ON c1.id_planning = p1.id_planning
|
||
INNER JOIN planning p2
|
||
ON p2.id_agent = p1.id_agent
|
||
AND p2.id_planning <> p1.id_planning
|
||
INNER JOIN structure st ON st.id_structure = p2.id_structure
|
||
INNER JOIN creneau_horaire c2
|
||
ON c2.id_planning = p2.id_planning
|
||
AND c2.date_jour = c1.date_jour
|
||
AND c1.heure_debut < c2.heure_fin
|
||
AND c1.heure_fin > c2.heure_debut
|
||
WHERE p1.id_planning = :planning_id"
|
||
);
|
||
$stmt->execute(['planning_id' => $planningId]);
|
||
return $stmt->fetchAll();
|
||
}
|
||
|
||
public function findDetailed(int $planningId): ?array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT p.id_planning, p.id_agent, p.id_structure, p.statut,
|
||
s.annee, s.numero_semaine, s.date_debut, s.date_fin
|
||
FROM planning p
|
||
INNER JOIN semaine s ON s.id_semaine = p.id_semaine
|
||
WHERE p.id_planning = :planning_id
|
||
LIMIT 1"
|
||
);
|
||
$stmt->execute(['planning_id' => $planningId]);
|
||
return $stmt->fetch() ?: null;
|
||
}
|
||
|
||
public function countEntries(int $planningId): int
|
||
{
|
||
$stmt = $this->pdo->prepare('SELECT COUNT(*) FROM creneau_horaire WHERE id_planning = :planning_id');
|
||
$stmt->execute(['planning_id' => $planningId]);
|
||
return (int) $stmt->fetchColumn();
|
||
}
|
||
|
||
public function entriesOutsideWeek(int $planningId, string $start, string $end): array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT id_creneau, date_jour
|
||
FROM creneau_horaire
|
||
WHERE id_planning = :planning_id
|
||
AND date_jour NOT BETWEEN :date_debut AND :date_fin"
|
||
);
|
||
$stmt->execute(['planning_id' => $planningId, 'date_debut' => $start, 'date_fin' => $end]);
|
||
return $stmt->fetchAll();
|
||
}
|
||
|
||
public function internalOverlaps(int $planningId): array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT c1.id_creneau AS c1, c2.id_creneau AS c2, c1.date_jour
|
||
FROM creneau_horaire c1
|
||
INNER JOIN creneau_horaire c2
|
||
ON c2.id_planning = c1.id_planning
|
||
AND c2.date_jour = c1.date_jour
|
||
AND c2.id_creneau > c1.id_creneau
|
||
AND c1.heure_debut < c2.heure_fin
|
||
AND c1.heure_fin > c2.heure_debut
|
||
WHERE c1.id_planning = :planning_id"
|
||
);
|
||
$stmt->execute(['planning_id' => $planningId]);
|
||
return $stmt->fetchAll();
|
||
}
|
||
|
||
public function validatedExternalOverlaps(int $planningId): array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT DISTINCT c1.id_creneau AS c1, c2.id_creneau AS c2, c1.date_jour, st.nom AS structure_nom
|
||
FROM planning p1
|
||
INNER JOIN creneau_horaire c1 ON c1.id_planning = p1.id_planning
|
||
INNER JOIN planning p2
|
||
ON p2.id_agent = p1.id_agent
|
||
AND p2.id_planning <> p1.id_planning
|
||
AND p2.statut = 'VALIDE'
|
||
INNER JOIN structure st ON st.id_structure = p2.id_structure
|
||
INNER JOIN creneau_horaire c2
|
||
ON c2.id_planning = p2.id_planning
|
||
AND c2.date_jour = c1.date_jour
|
||
AND c1.heure_debut < c2.heure_fin
|
||
AND c1.heure_fin > c2.heure_debut
|
||
WHERE p1.id_planning = :planning_id"
|
||
);
|
||
$stmt->execute(['planning_id' => $planningId]);
|
||
return $stmt->fetchAll();
|
||
}
|
||
|
||
public function findEntryDetailed(int $entryId): ?array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT c.id_creneau, c.id_planning, c.id_motif, c.date_jour,
|
||
TIME_FORMAT(c.heure_debut, '%H:%i') AS heure_debut,
|
||
TIME_FORMAT(c.heure_fin, '%H:%i') AS heure_fin,
|
||
p.id_agent, p.id_structure, p.id_semaine, p.statut,
|
||
sem.annee, sem.numero_semaine, sem.date_debut, sem.date_fin
|
||
FROM creneau_horaire c
|
||
INNER JOIN planning p ON p.id_planning = c.id_planning
|
||
INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
|
||
WHERE c.id_creneau = :entry_id
|
||
LIMIT 1"
|
||
);
|
||
$stmt->execute(['entry_id' => $entryId]);
|
||
return $stmt->fetch() ?: null;
|
||
}
|
||
|
||
/**
|
||
* Modifie un créneau depuis la vue agent, y compris vers une autre semaine.
|
||
* Le planning cible est créé si nécessaire et tout planning validé modifié
|
||
* repasse automatiquement en brouillon.
|
||
*/
|
||
public function updateEntryFromAgentView(
|
||
int $entryId,
|
||
int $targetStructureId,
|
||
int $motifId,
|
||
string $date,
|
||
string $start,
|
||
string $end
|
||
): array {
|
||
try {
|
||
$this->pdo->beginTransaction();
|
||
|
||
$entryStmt = $this->pdo->prepare(
|
||
"SELECT c.id_creneau, c.id_planning, c.id_motif, c.date_jour, c.heure_debut, c.heure_fin,
|
||
p.id_agent, p.id_structure, p.id_semaine, p.statut,
|
||
sem.annee, sem.numero_semaine
|
||
FROM creneau_horaire c
|
||
INNER JOIN planning p ON p.id_planning = c.id_planning
|
||
INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
|
||
WHERE c.id_creneau = :entry_id
|
||
FOR UPDATE"
|
||
);
|
||
$entryStmt->execute(['entry_id' => $entryId]);
|
||
$entry = $entryStmt->fetch();
|
||
if (!$entry) {
|
||
throw new \DomainException('Le créneau à modifier est introuvable.');
|
||
}
|
||
|
||
$targetDate = new DateTimeImmutable($date);
|
||
if ((int) $targetDate->format('N') > 5) {
|
||
throw new \DomainException('Le créneau doit être positionné entre le lundi et le vendredi.');
|
||
}
|
||
|
||
$conflictStmt = $this->pdo->prepare(
|
||
"SELECT c.id_creneau, p.statut, st.nom AS structure_nom,
|
||
TIME_FORMAT(c.heure_debut, '%H:%i') AS heure_debut,
|
||
TIME_FORMAT(c.heure_fin, '%H:%i') AS heure_fin
|
||
FROM creneau_horaire c
|
||
INNER JOIN planning p ON p.id_planning = c.id_planning
|
||
INNER JOIN structure st ON st.id_structure = p.id_structure
|
||
WHERE p.id_agent = :agent_id
|
||
AND c.id_creneau <> :entry_id
|
||
AND c.date_jour = :date_jour
|
||
AND c.heure_debut < :heure_fin
|
||
AND c.heure_fin > :heure_debut
|
||
LIMIT 1
|
||
FOR UPDATE"
|
||
);
|
||
$conflictStmt->execute([
|
||
'agent_id' => (int) $entry['id_agent'],
|
||
'entry_id' => $entryId,
|
||
'date_jour' => $date,
|
||
'heure_debut' => $start,
|
||
'heure_fin' => $end,
|
||
]);
|
||
$conflict = $conflictStmt->fetch();
|
||
if ($conflict) {
|
||
throw new \DomainException(sprintf(
|
||
'Double affectation impossible : un autre créneau existe déjà sur le lieu « %s » de %s à %s (%s).',
|
||
$conflict['structure_nom'],
|
||
$conflict['heure_debut'],
|
||
$conflict['heure_fin'],
|
||
strtolower((string) $conflict['statut'])
|
||
));
|
||
}
|
||
|
||
$targetYear = (int) $targetDate->format('o');
|
||
$targetWeekNumber = (int) $targetDate->format('W');
|
||
$targetWeekStart = $targetDate->modify('-' . ((int) $targetDate->format('N') - 1) . ' days')->setTime(0, 0);
|
||
$targetWeekId = $this->ensureWeek($targetYear, $targetWeekNumber, $targetWeekStart, $targetWeekStart->modify('+6 days'));
|
||
|
||
$sourcePlanningId = (int) $entry['id_planning'];
|
||
$targetPlanning = $this->findOrCreatePlanning(
|
||
(int) $entry['id_agent'],
|
||
$targetWeekId,
|
||
$targetStructureId
|
||
);
|
||
$targetPlanningId = (int) $targetPlanning['id_planning'];
|
||
|
||
$updateEntry = $this->pdo->prepare(
|
||
"UPDATE creneau_horaire
|
||
SET id_planning = :planning_id,
|
||
id_motif = :motif_id,
|
||
date_jour = :date_jour,
|
||
heure_debut = :heure_debut,
|
||
heure_fin = :heure_fin
|
||
WHERE id_creneau = :entry_id"
|
||
);
|
||
$updateEntry->execute([
|
||
'planning_id' => $targetPlanningId,
|
||
'motif_id' => $motifId,
|
||
'date_jour' => $date,
|
||
'heure_debut' => $start,
|
||
'heure_fin' => $end,
|
||
'entry_id' => $entryId,
|
||
]);
|
||
|
||
$reopenedPlanningIds = [];
|
||
if ((string) $entry['statut'] === 'VALIDE') {
|
||
$reopenedPlanningIds[] = $sourcePlanningId;
|
||
}
|
||
if ((string) $targetPlanning['statut'] === 'VALIDE') {
|
||
$reopenedPlanningIds[] = $targetPlanningId;
|
||
}
|
||
$reopenedPlanningIds = $this->reopenPlanningIds($reopenedPlanningIds);
|
||
|
||
$this->pdo->commit();
|
||
|
||
return [
|
||
'entry_id' => $entryId,
|
||
'agent_id' => (int) $entry['id_agent'],
|
||
'year' => $targetYear,
|
||
'week' => $targetWeekNumber,
|
||
'source_planning_id' => $sourcePlanningId,
|
||
'target_planning_id' => $targetPlanningId,
|
||
'reopened_planning_ids' => $reopenedPlanningIds,
|
||
];
|
||
} catch (Throwable $e) {
|
||
if ($this->pdo->inTransaction()) {
|
||
$this->pdo->rollBack();
|
||
}
|
||
throw $e;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Supprime un créneau depuis la vue par agent.
|
||
* Si le planning était validé, il repasse en brouillon afin de signaler
|
||
* que son contenu a été modifié. Le planning est conservé même s'il ne
|
||
* contient plus aucun créneau.
|
||
*/
|
||
public function deleteEntryFromAgentView(int $entryId): array
|
||
{
|
||
try {
|
||
$this->pdo->beginTransaction();
|
||
|
||
$entryStmt = $this->pdo->prepare(
|
||
"SELECT c.id_creneau, c.id_planning, c.date_jour,
|
||
p.id_agent, p.statut,
|
||
sem.annee, sem.numero_semaine
|
||
FROM creneau_horaire c
|
||
INNER JOIN planning p ON p.id_planning = c.id_planning
|
||
INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
|
||
WHERE c.id_creneau = :entry_id
|
||
FOR UPDATE"
|
||
);
|
||
$entryStmt->execute(['entry_id' => $entryId]);
|
||
$entry = $entryStmt->fetch();
|
||
|
||
if (!$entry) {
|
||
throw new \DomainException('Le créneau à supprimer est introuvable.');
|
||
}
|
||
|
||
$deleteStmt = $this->pdo->prepare(
|
||
'DELETE FROM creneau_horaire WHERE id_creneau = :entry_id'
|
||
);
|
||
$deleteStmt->execute(['entry_id' => $entryId]);
|
||
|
||
if ($deleteStmt->rowCount() !== 1) {
|
||
throw new \DomainException('Le créneau a déjà été supprimé ou modifié. Rechargez la page.');
|
||
}
|
||
|
||
$reopened = (string) $entry['statut'] === 'VALIDE';
|
||
if ($reopened) {
|
||
$reopenStmt = $this->pdo->prepare(
|
||
"UPDATE planning
|
||
SET statut = 'BROUILLON'
|
||
WHERE id_planning = :planning_id"
|
||
);
|
||
$reopenStmt->execute(['planning_id' => (int) $entry['id_planning']]);
|
||
}
|
||
|
||
$countStmt = $this->pdo->prepare(
|
||
'SELECT COUNT(*) FROM creneau_horaire WHERE id_planning = :planning_id'
|
||
);
|
||
$countStmt->execute(['planning_id' => (int) $entry['id_planning']]);
|
||
$remainingEntries = (int) $countStmt->fetchColumn();
|
||
|
||
$this->pdo->commit();
|
||
|
||
return [
|
||
'entry_id' => $entryId,
|
||
'planning_id' => (int) $entry['id_planning'],
|
||
'agent_id' => (int) $entry['id_agent'],
|
||
'year' => (int) $entry['annee'],
|
||
'week' => (int) $entry['numero_semaine'],
|
||
'date' => (string) $entry['date_jour'],
|
||
'reopened' => $reopened,
|
||
'remaining_entries' => $remainingEntries,
|
||
];
|
||
} catch (Throwable $e) {
|
||
if ($this->pdo->inTransaction()) {
|
||
$this->pdo->rollBack();
|
||
}
|
||
throw $e;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Déplace ou duplique un créneau vers n'importe quel jour ouvré.
|
||
* Le lieu, le motif et les horaires sont conservés, y compris lors d'un
|
||
* changement de semaine.
|
||
*/
|
||
public function moveOrDuplicateEntry(int $entryId, string $targetDate, bool $duplicate): array
|
||
{
|
||
try {
|
||
$this->pdo->beginTransaction();
|
||
|
||
$entryStmt = $this->pdo->prepare(
|
||
"SELECT c.id_creneau, c.id_planning, c.id_motif, c.date_jour, c.heure_debut, c.heure_fin,
|
||
p.id_agent, p.id_structure, p.id_semaine, p.statut,
|
||
sem.annee, sem.numero_semaine
|
||
FROM creneau_horaire c
|
||
INNER JOIN planning p ON p.id_planning = c.id_planning
|
||
INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
|
||
WHERE c.id_creneau = :entry_id
|
||
FOR UPDATE"
|
||
);
|
||
$entryStmt->execute(['entry_id' => $entryId]);
|
||
$entry = $entryStmt->fetch();
|
||
if (!$entry) {
|
||
throw new \DomainException('Le créneau à déplacer est introuvable.');
|
||
}
|
||
|
||
$targetDateObject = new DateTimeImmutable($targetDate);
|
||
if ((int) $targetDateObject->format('N') > 5) {
|
||
throw new \DomainException('Le jour cible doit être compris entre le lundi et le vendredi.');
|
||
}
|
||
|
||
if (!$duplicate && $targetDate === (string) $entry['date_jour']) {
|
||
$this->pdo->commit();
|
||
return [
|
||
'entry_id' => $entryId,
|
||
'created_entry_id' => null,
|
||
'agent_id' => (int) $entry['id_agent'],
|
||
'year' => (int) $entry['annee'],
|
||
'week' => (int) $entry['numero_semaine'],
|
||
'planning_id' => (int) $entry['id_planning'],
|
||
'target_planning_id' => (int) $entry['id_planning'],
|
||
'reopened_planning_ids' => [],
|
||
'action' => 'noop',
|
||
];
|
||
}
|
||
|
||
$excludeEntryId = $duplicate ? 0 : $entryId;
|
||
$conflictStmt = $this->pdo->prepare(
|
||
"SELECT c.id_creneau, p.statut, st.nom AS structure_nom,
|
||
TIME_FORMAT(c.heure_debut, '%H:%i') AS heure_debut,
|
||
TIME_FORMAT(c.heure_fin, '%H:%i') AS heure_fin
|
||
FROM creneau_horaire c
|
||
INNER JOIN planning p ON p.id_planning = c.id_planning
|
||
INNER JOIN structure st ON st.id_structure = p.id_structure
|
||
WHERE p.id_agent = :agent_id
|
||
AND (:exclude_entry_id = 0 OR c.id_creneau <> :exclude_entry_id_compare)
|
||
AND c.date_jour = :date_jour
|
||
AND c.heure_debut < :heure_fin
|
||
AND c.heure_fin > :heure_debut
|
||
LIMIT 1
|
||
FOR UPDATE"
|
||
);
|
||
$conflictStmt->execute([
|
||
'agent_id' => (int) $entry['id_agent'],
|
||
'exclude_entry_id' => $excludeEntryId,
|
||
'exclude_entry_id_compare' => $excludeEntryId,
|
||
'date_jour' => $targetDate,
|
||
'heure_fin' => $entry['heure_fin'],
|
||
'heure_debut' => $entry['heure_debut'],
|
||
]);
|
||
$conflict = $conflictStmt->fetch();
|
||
if ($conflict) {
|
||
throw new \DomainException(sprintf(
|
||
'Action impossible : l’agent possède déjà un créneau sur le lieu « %s » de %s à %s (%s).',
|
||
$conflict['structure_nom'],
|
||
$conflict['heure_debut'],
|
||
$conflict['heure_fin'],
|
||
strtolower((string) $conflict['statut'])
|
||
));
|
||
}
|
||
|
||
$targetYear = (int) $targetDateObject->format('o');
|
||
$targetWeekNumber = (int) $targetDateObject->format('W');
|
||
$targetMonday = $targetDateObject->modify('-' . ((int) $targetDateObject->format('N') - 1) . ' days')->setTime(0, 0);
|
||
$targetWeekId = $this->ensureWeek($targetYear, $targetWeekNumber, $targetMonday, $targetMonday->modify('+6 days'));
|
||
$targetPlanning = $this->findOrCreatePlanning(
|
||
(int) $entry['id_agent'],
|
||
$targetWeekId,
|
||
(int) $entry['id_structure']
|
||
);
|
||
$targetPlanningId = (int) $targetPlanning['id_planning'];
|
||
$sourcePlanningId = (int) $entry['id_planning'];
|
||
|
||
$createdEntryId = null;
|
||
if ($duplicate) {
|
||
$insertStmt = $this->pdo->prepare(
|
||
"INSERT INTO creneau_horaire (id_planning, id_motif, date_jour, heure_debut, heure_fin)
|
||
VALUES (:planning_id, :motif_id, :date_jour, :heure_debut, :heure_fin)"
|
||
);
|
||
$insertStmt->execute([
|
||
'planning_id' => $targetPlanningId,
|
||
'motif_id' => (int) $entry['id_motif'],
|
||
'date_jour' => $targetDate,
|
||
'heure_debut' => $entry['heure_debut'],
|
||
'heure_fin' => $entry['heure_fin'],
|
||
]);
|
||
$createdEntryId = (int) $this->pdo->lastInsertId();
|
||
} else {
|
||
$moveStmt = $this->pdo->prepare(
|
||
"UPDATE creneau_horaire
|
||
SET id_planning = :target_planning_id,
|
||
date_jour = :date_jour
|
||
WHERE id_creneau = :entry_id"
|
||
);
|
||
$moveStmt->execute([
|
||
'target_planning_id' => $targetPlanningId,
|
||
'date_jour' => $targetDate,
|
||
'entry_id' => $entryId,
|
||
]);
|
||
}
|
||
|
||
$reopenedPlanningIds = [];
|
||
if (!$duplicate && (string) $entry['statut'] === 'VALIDE') {
|
||
$reopenedPlanningIds[] = $sourcePlanningId;
|
||
}
|
||
if ((string) $targetPlanning['statut'] === 'VALIDE') {
|
||
$reopenedPlanningIds[] = $targetPlanningId;
|
||
}
|
||
$reopenedPlanningIds = $this->reopenPlanningIds($reopenedPlanningIds);
|
||
|
||
$this->pdo->commit();
|
||
|
||
return [
|
||
'entry_id' => $entryId,
|
||
'created_entry_id' => $createdEntryId,
|
||
'agent_id' => (int) $entry['id_agent'],
|
||
'year' => $targetYear,
|
||
'week' => $targetWeekNumber,
|
||
'planning_id' => $sourcePlanningId,
|
||
'target_planning_id' => $targetPlanningId,
|
||
'reopened_planning_ids' => $reopenedPlanningIds,
|
||
'action' => $duplicate ? 'duplicate' : 'move',
|
||
];
|
||
} catch (Throwable $e) {
|
||
if ($this->pdo->inTransaction()) {
|
||
$this->pdo->rollBack();
|
||
}
|
||
throw $e;
|
||
}
|
||
}
|
||
|
||
public function copyAgentWeek(
|
||
int $agentId,
|
||
int $sourceYear,
|
||
int $sourceWeekNumber,
|
||
int $targetYear,
|
||
int $targetWeekNumber,
|
||
string $mode
|
||
): array {
|
||
if (!in_array($mode, ['merge', 'replace'], true)) {
|
||
throw new \DomainException('Mode de duplication invalide.');
|
||
}
|
||
if ($sourceYear === $targetYear && $sourceWeekNumber === $targetWeekNumber) {
|
||
throw new \DomainException('La semaine cible doit être différente de la semaine source.');
|
||
}
|
||
|
||
$sourceStmt = $this->pdo->prepare(
|
||
"SELECT WEEKDAY(c.date_jour) + 1 AS jour_semaine,
|
||
p.id_structure, c.id_motif,
|
||
TIME_FORMAT(c.heure_debut, '%H:%i:%s') AS heure_debut,
|
||
TIME_FORMAT(c.heure_fin, '%H:%i:%s') AS heure_fin
|
||
FROM planning p
|
||
INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
|
||
INNER JOIN creneau_horaire c ON c.id_planning = p.id_planning
|
||
WHERE p.id_agent = :agent_id
|
||
AND sem.annee = :annee
|
||
AND sem.numero_semaine = :semaine
|
||
AND WEEKDAY(c.date_jour) BETWEEN 0 AND 4
|
||
ORDER BY c.date_jour, c.heure_debut"
|
||
);
|
||
$sourceStmt->execute([
|
||
'agent_id' => $agentId,
|
||
'annee' => $sourceYear,
|
||
'semaine' => $sourceWeekNumber,
|
||
]);
|
||
$sourceEntries = $sourceStmt->fetchAll();
|
||
if (!$sourceEntries) {
|
||
throw new \DomainException('La semaine source ne contient aucun créneau à dupliquer.');
|
||
}
|
||
|
||
$targetMonday = (new DateTimeImmutable())->setISODate($targetYear, $targetWeekNumber, 1)->setTime(0, 0);
|
||
$targetEntries = array_map(static function (array $entry) use ($targetMonday): array {
|
||
return [
|
||
'date_jour' => $targetMonday->modify('+' . ((int) $entry['jour_semaine'] - 1) . ' days')->format('Y-m-d'),
|
||
'id_structure' => (int) $entry['id_structure'],
|
||
'id_motif' => (int) $entry['id_motif'],
|
||
'heure_debut' => (string) $entry['heure_debut'],
|
||
'heure_fin' => (string) $entry['heure_fin'],
|
||
];
|
||
}, $sourceEntries);
|
||
|
||
try {
|
||
$this->pdo->beginTransaction();
|
||
$targetWeekId = $this->ensureWeek($targetYear, $targetWeekNumber, $targetMonday, $targetMonday->modify('+6 days'));
|
||
|
||
$existingPlanningStmt = $this->pdo->prepare(
|
||
"SELECT id_planning, id_structure, statut
|
||
FROM planning
|
||
WHERE id_agent = :agent_id AND id_semaine = :week_id
|
||
FOR UPDATE"
|
||
);
|
||
$existingPlanningStmt->execute(['agent_id' => $agentId, 'week_id' => $targetWeekId]);
|
||
$existingPlannings = $existingPlanningStmt->fetchAll();
|
||
|
||
$reopened = [];
|
||
if ($mode === 'replace') {
|
||
if ($existingPlannings) {
|
||
$ids = array_map(static fn(array $row): int => (int) $row['id_planning'], $existingPlannings);
|
||
foreach ($existingPlannings as $planning) {
|
||
if ((string) $planning['statut'] === 'VALIDE') {
|
||
$reopened[] = (int) $planning['id_planning'];
|
||
}
|
||
}
|
||
$placeholders = implode(',', array_fill(0, count($ids), '?'));
|
||
$delete = $this->pdo->prepare("DELETE FROM creneau_horaire WHERE id_planning IN ($placeholders)");
|
||
$delete->execute($ids);
|
||
}
|
||
} else {
|
||
$existingEntriesStmt = $this->pdo->prepare(
|
||
"SELECT c.date_jour, c.heure_debut, c.heure_fin, st.nom AS structure_nom
|
||
FROM planning p
|
||
INNER JOIN creneau_horaire c ON c.id_planning = p.id_planning
|
||
INNER JOIN structure st ON st.id_structure = p.id_structure
|
||
WHERE p.id_agent = :agent_id AND p.id_semaine = :week_id
|
||
FOR UPDATE"
|
||
);
|
||
$existingEntriesStmt->execute(['agent_id' => $agentId, 'week_id' => $targetWeekId]);
|
||
$existingEntries = $existingEntriesStmt->fetchAll();
|
||
foreach ($targetEntries as $candidate) {
|
||
foreach ($existingEntries as $existing) {
|
||
if ($candidate['date_jour'] === $existing['date_jour']
|
||
&& $candidate['heure_debut'] < $existing['heure_fin']
|
||
&& $candidate['heure_fin'] > $existing['heure_debut']) {
|
||
throw new \DomainException(sprintf(
|
||
'Duplication impossible : un créneau existe déjà le %s de %s à %s sur « %s ».',
|
||
$candidate['date_jour'],
|
||
substr((string) $existing['heure_debut'], 0, 5),
|
||
substr((string) $existing['heure_fin'], 0, 5),
|
||
$existing['structure_nom']
|
||
));
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
$planningCache = [];
|
||
foreach ($existingPlannings as $planning) {
|
||
$planningCache[(int) $planning['id_structure']] = [
|
||
'id_planning' => (int) $planning['id_planning'],
|
||
'statut' => (string) $planning['statut'],
|
||
];
|
||
}
|
||
|
||
$insertEntry = $this->pdo->prepare(
|
||
"INSERT INTO creneau_horaire (id_planning, id_motif, date_jour, heure_debut, heure_fin)
|
||
VALUES (:planning_id, :motif_id, :date_jour, :heure_debut, :heure_fin)"
|
||
);
|
||
|
||
foreach ($targetEntries as $candidate) {
|
||
$structureId = (int) $candidate['id_structure'];
|
||
if (!isset($planningCache[$structureId])) {
|
||
$planningCache[$structureId] = $this->findOrCreatePlanning($agentId, $targetWeekId, $structureId);
|
||
}
|
||
$planning = $planningCache[$structureId];
|
||
if ((string) $planning['statut'] === 'VALIDE') {
|
||
$reopened[] = (int) $planning['id_planning'];
|
||
}
|
||
$insertEntry->execute([
|
||
'planning_id' => (int) $planning['id_planning'],
|
||
'motif_id' => (int) $candidate['id_motif'],
|
||
'date_jour' => $candidate['date_jour'],
|
||
'heure_debut' => $candidate['heure_debut'],
|
||
'heure_fin' => $candidate['heure_fin'],
|
||
]);
|
||
}
|
||
|
||
$reopened = $this->reopenPlanningIds($reopened);
|
||
$this->pdo->commit();
|
||
|
||
return [
|
||
'agent_id' => $agentId,
|
||
'year' => $targetYear,
|
||
'week' => $targetWeekNumber,
|
||
'copied_entries' => count($targetEntries),
|
||
'reopened_planning_ids' => $reopened,
|
||
'mode' => $mode,
|
||
];
|
||
} catch (Throwable $e) {
|
||
if ($this->pdo->inTransaction()) {
|
||
$this->pdo->rollBack();
|
||
}
|
||
throw $e;
|
||
}
|
||
}
|
||
|
||
private function ensureWeek(int $year, int $weekNumber, DateTimeImmutable $weekStart, DateTimeImmutable $weekEnd): int
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"INSERT INTO semaine (annee, numero_semaine, date_debut, date_fin, statut)
|
||
VALUES (:annee, :numero, :date_debut, :date_fin, 'OUVERTE')
|
||
ON DUPLICATE KEY UPDATE id_semaine = LAST_INSERT_ID(id_semaine)"
|
||
);
|
||
$stmt->execute([
|
||
'annee' => $year,
|
||
'numero' => $weekNumber,
|
||
'date_debut' => $weekStart->format('Y-m-d'),
|
||
'date_fin' => $weekEnd->format('Y-m-d'),
|
||
]);
|
||
$weekId = (int) $this->pdo->lastInsertId();
|
||
if ($weekId > 0) {
|
||
return $weekId;
|
||
}
|
||
|
||
$find = $this->pdo->prepare('SELECT id_semaine FROM semaine WHERE annee = :annee AND numero_semaine = :numero');
|
||
$find->execute(['annee' => $year, 'numero' => $weekNumber]);
|
||
$weekId = (int) $find->fetchColumn();
|
||
if ($weekId <= 0) {
|
||
throw new \RuntimeException('Impossible de créer ou retrouver la semaine cible.');
|
||
}
|
||
return $weekId;
|
||
}
|
||
|
||
/** @return array{id_planning:int,statut:string} */
|
||
private function findOrCreatePlanning(int $agentId, int $weekId, int $structureId): array
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"SELECT id_planning, statut
|
||
FROM planning
|
||
WHERE id_agent = :agent_id AND id_semaine = :week_id AND id_structure = :structure_id
|
||
FOR UPDATE"
|
||
);
|
||
$stmt->execute([
|
||
'agent_id' => $agentId,
|
||
'week_id' => $weekId,
|
||
'structure_id' => $structureId,
|
||
]);
|
||
$planning = $stmt->fetch();
|
||
if ($planning) {
|
||
return ['id_planning' => (int) $planning['id_planning'], 'statut' => (string) $planning['statut']];
|
||
}
|
||
|
||
$insert = $this->pdo->prepare(
|
||
"INSERT INTO planning (id_agent, id_semaine, id_structure, statut)
|
||
VALUES (:agent_id, :week_id, :structure_id, 'BROUILLON')"
|
||
);
|
||
$insert->execute([
|
||
'agent_id' => $agentId,
|
||
'week_id' => $weekId,
|
||
'structure_id' => $structureId,
|
||
]);
|
||
return ['id_planning' => (int) $this->pdo->lastInsertId(), 'statut' => 'BROUILLON'];
|
||
}
|
||
|
||
/** @return int[] */
|
||
private function reopenPlanningIds(array $planningIds): array
|
||
{
|
||
$planningIds = array_values(array_unique(array_map('intval', array_filter($planningIds))));
|
||
if (!$planningIds) {
|
||
return [];
|
||
}
|
||
$placeholders = implode(',', array_fill(0, count($planningIds), '?'));
|
||
$stmt = $this->pdo->prepare(
|
||
"UPDATE planning SET statut = 'BROUILLON', date_validation = NULL WHERE id_planning IN ($placeholders)"
|
||
);
|
||
$stmt->execute($planningIds);
|
||
return $planningIds;
|
||
}
|
||
|
||
|
||
/** @param int[] $planningIds */
|
||
public function markValidatedMany(array $planningIds): int
|
||
{
|
||
$planningIds = array_values(array_unique(array_filter(array_map('intval', $planningIds), static fn(int $id): bool => $id > 0)));
|
||
if ($planningIds === []) {
|
||
return 0;
|
||
}
|
||
$placeholders = implode(',', array_fill(0, count($planningIds), '?'));
|
||
$stmt = $this->pdo->prepare(
|
||
"UPDATE planning
|
||
SET statut = 'VALIDE', date_validation = CURRENT_TIMESTAMP
|
||
WHERE statut = 'BROUILLON' AND id_planning IN ($placeholders)"
|
||
);
|
||
$stmt->execute($planningIds);
|
||
return $stmt->rowCount();
|
||
}
|
||
|
||
public function markValidated(int $planningId): void
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"UPDATE planning SET statut = 'VALIDE', date_validation = CURRENT_TIMESTAMP WHERE id_planning = :planning_id"
|
||
);
|
||
$stmt->execute(['planning_id' => $planningId]);
|
||
}
|
||
|
||
public function reopen(int $planningId): bool
|
||
{
|
||
$stmt = $this->pdo->prepare(
|
||
"UPDATE planning
|
||
SET statut = 'BROUILLON', date_validation = NULL
|
||
WHERE id_planning = :planning_id AND statut = 'VALIDE'"
|
||
);
|
||
$stmt->execute(['planning_id' => $planningId]);
|
||
return $stmt->rowCount() === 1;
|
||
}
|
||
}
|