1181 lines
46 KiB
SQL
1181 lines
46 KiB
SQL
-- ============================================================================
|
||
-- PTA - INSTALLATION PRODUCTION COMPLETE
|
||
-- Version applicative cible : V7.7
|
||
-- MySQL 8.0.16+
|
||
-- ============================================================================
|
||
-- Usage : installation sur une BASE VIDE.
|
||
--
|
||
-- Ce script contient :
|
||
-- * toutes les tables nécessaires à PTA V7.7 ;
|
||
-- * les clés étrangères, index et contraintes ;
|
||
-- * les vues de pilotage ;
|
||
-- * les procédures de contrôle / validation ;
|
||
-- * uniquement les référentiels techniques indispensables (postes et motifs).
|
||
--
|
||
-- Il ne contient AUCUN agent, lieu, planning, quota ou jeu de démonstration.
|
||
--
|
||
-- IMPORTANT - correction MySQL #1832 :
|
||
-- creneau_horaire.id_motif est créé directement en INT UNSIGNED NOT NULL,
|
||
-- AVANT la création de fk_creneau_motif. Aucun ALTER TABLE ... MODIFY id_motif
|
||
-- n'est donc nécessaire sur une installation neuve.
|
||
-- ============================================================================
|
||
|
||
SET NAMES utf8mb4;
|
||
SET time_zone = '+00:00';
|
||
|
||
-- Décommenter si vous souhaitez que le script crée la base lui-même.
|
||
-- CREATE DATABASE IF NOT EXISTS pta
|
||
-- CHARACTER SET utf8mb4
|
||
-- COLLATE utf8mb4_unicode_ci;
|
||
-- USE pta;
|
||
|
||
-- ============================================================================
|
||
-- 1. LIEUX D'AFFECTATION
|
||
-- ============================================================================
|
||
CREATE TABLE structure (
|
||
id_structure INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
code VARCHAR(50) NOT NULL,
|
||
nom VARCHAR(150) NOT NULL,
|
||
adresse VARCHAR(255) NULL,
|
||
type_affectation ENUM('PERISCOLAIRE', 'EXTRASCOLAIRE') NOT NULL DEFAULT 'PERISCOLAIRE',
|
||
actif BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT uk_structure_code UNIQUE (code),
|
||
INDEX idx_structure_actif_nom (actif, nom)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================================
|
||
-- 2. POSTES PTA - RÉFÉRENTIEL TECHNIQUE
|
||
-- ============================================================================
|
||
CREATE TABLE poste_agent (
|
||
id_poste INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
code VARCHAR(50) NOT NULL,
|
||
libelle VARCHAR(150) NOT NULL,
|
||
famille ENUM('ANIMATION', 'RESTAURATION_ENTRETIEN') NOT NULL,
|
||
heures_mercredi_minutes SMALLINT UNSIGNED NOT NULL,
|
||
heures_extrascolaire_minutes SMALLINT UNSIGNED NOT NULL,
|
||
autorise_preparation BOOLEAN NOT NULL DEFAULT TRUE,
|
||
peut_assurer_animation BOOLEAN NOT NULL DEFAULT TRUE,
|
||
preparation_lundi_si_100 BOOLEAN NOT NULL DEFAULT FALSE,
|
||
actif BOOLEAN NOT NULL DEFAULT TRUE,
|
||
ordre_affichage SMALLINT UNSIGNED NOT NULL DEFAULT 100,
|
||
|
||
CONSTRAINT uk_poste_agent_code UNIQUE (code),
|
||
INDEX idx_poste_agent_actif_ordre (actif, ordre_affichage)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- Référentiel indispensable au fonctionnement de l'application.
|
||
INSERT INTO poste_agent (
|
||
code, libelle, famille,
|
||
heures_mercredi_minutes, heures_extrascolaire_minutes,
|
||
autorise_preparation, peut_assurer_animation,
|
||
preparation_lundi_si_100, ordre_affichage
|
||
) VALUES
|
||
('RESPONSABLE', 'Responsable d’accueil', 'ANIMATION', 570, 570, TRUE, TRUE, TRUE, 10),
|
||
('ANIMATION_RELAIS', 'Agent d’animation relais', 'ANIMATION', 570, 570, TRUE, TRUE, TRUE, 20),
|
||
('ANIMATION', 'Agent d’animation', 'ANIMATION', 570, 570, TRUE, TRUE, TRUE, 30),
|
||
('RESTAURATION_ENTRETIEN', 'Agent de restauration collective et d’entretien', 'RESTAURATION_ENTRETIEN', 420, 420, FALSE, FALSE, FALSE, 40)
|
||
ON DUPLICATE KEY UPDATE
|
||
libelle = VALUES(libelle),
|
||
famille = VALUES(famille),
|
||
heures_mercredi_minutes = VALUES(heures_mercredi_minutes),
|
||
heures_extrascolaire_minutes = VALUES(heures_extrascolaire_minutes),
|
||
autorise_preparation = VALUES(autorise_preparation),
|
||
peut_assurer_animation = VALUES(peut_assurer_animation),
|
||
preparation_lundi_si_100 = VALUES(preparation_lundi_si_100),
|
||
ordre_affichage = VALUES(ordre_affichage),
|
||
actif = TRUE;
|
||
|
||
-- ============================================================================
|
||
-- 3. AGENTS
|
||
-- ============================================================================
|
||
CREATE TABLE agent (
|
||
id_agent INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
matricule VARCHAR(50) NOT NULL,
|
||
nom VARCHAR(100) NOT NULL,
|
||
prenom VARCHAR(100) NOT NULL,
|
||
email VARCHAR(255) NULL,
|
||
telephone VARCHAR(30) NULL,
|
||
adresse VARCHAR(255) NULL,
|
||
est_diplome BOOLEAN NOT NULL DEFAULT FALSE,
|
||
diplome_libelle VARCHAR(150) NULL,
|
||
date_debut_contrat DATE NULL,
|
||
id_poste INT UNSIGNED NULL,
|
||
type_contrat ENUM('PERMANENT', 'TEMPORAIRE') NOT NULL DEFAULT 'PERMANENT',
|
||
date_fin_contrat DATE NULL,
|
||
formation_repartition_annuelle BOOLEAN NOT NULL DEFAULT FALSE,
|
||
commentaire_pta VARCHAR(1000) NULL,
|
||
id_structure INT UNSIGNED NULL COMMENT 'Lieu principal de rattachement',
|
||
actif BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT uk_agent_matricule UNIQUE (matricule),
|
||
|
||
CONSTRAINT fk_agent_poste
|
||
FOREIGN KEY (id_poste) REFERENCES poste_agent(id_poste)
|
||
ON UPDATE CASCADE ON DELETE SET NULL,
|
||
|
||
CONSTRAINT fk_agent_structure_principale
|
||
FOREIGN KEY (id_structure) REFERENCES structure(id_structure)
|
||
ON UPDATE CASCADE ON DELETE SET NULL,
|
||
|
||
CONSTRAINT chk_agent_dates_contrat
|
||
CHECK (
|
||
date_fin_contrat IS NULL
|
||
OR date_debut_contrat IS NULL
|
||
OR date_fin_contrat >= date_debut_contrat
|
||
),
|
||
|
||
INDEX idx_agent_poste (id_poste),
|
||
INDEX idx_agent_structure_principale (id_structure),
|
||
INDEX idx_agent_type_contrat (type_contrat, actif),
|
||
INDEX idx_agent_actif_nom (actif, nom, prenom),
|
||
INDEX idx_agent_date_debut_contrat (date_debut_contrat)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- Historique des rattachements de l'agent.
|
||
CREATE TABLE agent_structure (
|
||
id_agent_structure INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_agent INT UNSIGNED NOT NULL,
|
||
id_structure INT UNSIGNED NOT NULL,
|
||
date_debut DATE NULL,
|
||
date_fin DATE NULL,
|
||
actif BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT fk_agent_structure_agent
|
||
FOREIGN KEY (id_agent) REFERENCES agent(id_agent)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT fk_agent_structure_structure
|
||
FOREIGN KEY (id_structure) REFERENCES structure(id_structure)
|
||
ON UPDATE CASCADE ON DELETE RESTRICT,
|
||
|
||
CONSTRAINT chk_agent_structure_dates
|
||
CHECK (date_fin IS NULL OR date_debut IS NULL OR date_fin >= date_debut),
|
||
|
||
CONSTRAINT uk_agent_structure_periode
|
||
UNIQUE (id_agent, id_structure, date_debut),
|
||
|
||
INDEX idx_agent_structure_agent_actif (id_agent, actif),
|
||
INDEX idx_agent_structure_structure_actif (id_structure, actif)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================================
|
||
-- 4. CAPACITÉS / TRANCHES D'ÂGE
|
||
-- ============================================================================
|
||
CREATE TABLE tranche_age (
|
||
id_tranche_age INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
libelle VARCHAR(100) NOT NULL,
|
||
age_min_mois INT UNSIGNED NULL,
|
||
age_max_mois INT UNSIGNED NULL,
|
||
|
||
CONSTRAINT chk_tranche_age
|
||
CHECK (
|
||
age_min_mois IS NULL
|
||
OR age_max_mois IS NULL
|
||
OR age_max_mois >= age_min_mois
|
||
)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE structure_capacite (
|
||
id_structure INT UNSIGNED NOT NULL,
|
||
id_tranche_age INT UNSIGNED NOT NULL,
|
||
nombre_places INT UNSIGNED NOT NULL,
|
||
|
||
PRIMARY KEY (id_structure, id_tranche_age),
|
||
|
||
CONSTRAINT fk_capacite_structure
|
||
FOREIGN KEY (id_structure) REFERENCES structure(id_structure)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT fk_capacite_tranche_age
|
||
FOREIGN KEY (id_tranche_age) REFERENCES tranche_age(id_tranche_age)
|
||
ON UPDATE CASCADE ON DELETE RESTRICT,
|
||
|
||
CONSTRAINT chk_nombre_places CHECK (nombre_places > 0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================================
|
||
-- 5. RÈGLES DE COUVERTURE DES LIEUX
|
||
-- ============================================================================
|
||
CREATE TABLE structure_couverture_horaire (
|
||
id_couverture INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_structure INT UNSIGNED NOT NULL,
|
||
jour_semaine TINYINT UNSIGNED NOT NULL COMMENT '1=lundi, 5=vendredi',
|
||
heure_debut TIME NOT NULL,
|
||
heure_fin TIME NOT NULL,
|
||
minimum_agents TINYINT UNSIGNED NOT NULL DEFAULT 1,
|
||
|
||
CONSTRAINT fk_couverture_structure
|
||
FOREIGN KEY (id_structure) REFERENCES structure(id_structure)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT chk_couverture_jour CHECK (jour_semaine BETWEEN 1 AND 5),
|
||
CONSTRAINT chk_couverture_ordre CHECK (heure_fin > heure_debut),
|
||
CONSTRAINT chk_couverture_minimum CHECK (minimum_agents BETWEEN 1 AND 50),
|
||
CONSTRAINT chk_couverture_quart_heure CHECK (
|
||
MOD(MINUTE(heure_debut), 15) = 0
|
||
AND MOD(MINUTE(heure_fin), 15) = 0
|
||
AND SECOND(heure_debut) = 0
|
||
AND SECOND(heure_fin) = 0
|
||
),
|
||
|
||
INDEX idx_couverture_structure_jour
|
||
(id_structure, jour_semaine, heure_debut, heure_fin)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- Horaires de référence utilisés pour les automatismes de préparation.
|
||
CREATE TABLE structure_creneau_reference (
|
||
id_reference INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_structure INT UNSIGNED NOT NULL,
|
||
code_plage ENUM('ALP_MATIN', 'RESTAURATION', 'ALP_SOIR') NOT NULL,
|
||
jour_semaine TINYINT UNSIGNED NOT NULL COMMENT '1=lundi ... 5=vendredi',
|
||
heure_debut TIME NOT NULL,
|
||
heure_fin TIME NOT NULL,
|
||
actif BOOLEAN NOT NULL DEFAULT TRUE,
|
||
|
||
CONSTRAINT fk_reference_structure
|
||
FOREIGN KEY (id_structure) REFERENCES structure(id_structure)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT chk_reference_jour CHECK (jour_semaine BETWEEN 1 AND 5),
|
||
CONSTRAINT chk_reference_ordre CHECK (heure_fin > heure_debut),
|
||
CONSTRAINT uk_reference_structure_plage_jour
|
||
UNIQUE (id_structure, code_plage, jour_semaine),
|
||
|
||
INDEX idx_reference_structure_jour (id_structure, jour_semaine, actif)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================================
|
||
-- 6. MOTIFS D'AFFECTATION - RÉFÉRENTIEL TECHNIQUE
|
||
-- ============================================================================
|
||
CREATE TABLE motif_planning (
|
||
id_motif INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
code VARCHAR(50) NOT NULL,
|
||
libelle VARCHAR(120) NOT NULL,
|
||
compte_dans_quota BOOLEAN NOT NULL DEFAULT FALSE,
|
||
ordre_affichage SMALLINT UNSIGNED NOT NULL DEFAULT 100,
|
||
actif BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT uk_motif_planning_code UNIQUE (code),
|
||
INDEX idx_motif_planning_actif_ordre (actif, ordre_affichage, libelle)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- Aucun jeu d'essai : uniquement les motifs nécessaires au fonctionnement.
|
||
INSERT INTO motif_planning (code, libelle, compte_dans_quota, ordre_affichage, actif)
|
||
VALUES
|
||
('TRAVAIL', 'Heure de travail', TRUE, 5, TRUE),
|
||
('ALP_MATIN', 'ALP matin', TRUE, 10, TRUE),
|
||
('RESTAURATION', 'Restauration', TRUE, 20, TRUE),
|
||
('ALP_SOIR', 'ALP soir', TRUE, 30, TRUE),
|
||
('MERCREDI_PERISCOLAIRE', 'Mercredi périscolaire', TRUE, 40, TRUE),
|
||
('EXTRASCOLAIRE', 'Temps extrascolaire', TRUE, 50, TRUE),
|
||
('FORMATION', 'Formation', TRUE, 60, TRUE),
|
||
('CONGE', 'Congé annuel (non décompté)', FALSE, 70, TRUE),
|
||
('JOUR_FRACTIONNEMENT', 'Journée de fractionnement', TRUE, 80, TRUE),
|
||
('PREPARATION_PERISCOLAIRE', 'Préparation périscolaire', TRUE, 90, TRUE),
|
||
('PREPARATION_EXTRASCOLAIRE', 'Préparation extrascolaire', TRUE, 100, TRUE),
|
||
('PREPARATION_LUNDI', 'Préparation du lundi', TRUE, 110, TRUE),
|
||
('MENAGE_FOND', 'Ménage de fond', TRUE, 120, TRUE),
|
||
('ABSENCE', 'Absence', TRUE, 130, TRUE),
|
||
('AUTRE_ABSENCE', 'Autre absence (non décomptée)', FALSE, 140, TRUE),
|
||
('JNT', 'Journée non travaillée', FALSE, 150, TRUE)
|
||
ON DUPLICATE KEY UPDATE
|
||
libelle = VALUES(libelle),
|
||
compte_dans_quota = VALUES(compte_dans_quota),
|
||
ordre_affichage = VALUES(ordre_affichage),
|
||
actif = TRUE;
|
||
|
||
-- ============================================================================
|
||
-- 7. QUOTAS ANNUELS - ANNÉE CIVILE 01/01 -> 31/12
|
||
-- ============================================================================
|
||
CREATE TABLE quota_agent_annuel (
|
||
id_quota INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_agent INT UNSIGNED NOT NULL,
|
||
annee SMALLINT UNSIGNED NOT NULL,
|
||
quotite_travail DECIMAL(5,2) NOT NULL DEFAULT 100.00,
|
||
quota_reference_minutes INT UNSIGNED NOT NULL DEFAULT 96420 COMMENT '1607 h à 100 %',
|
||
quota_cible_minutes INT UNSIGNED NOT NULL,
|
||
commentaire VARCHAR(255) NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT fk_quota_annuel_agent
|
||
FOREIGN KEY (id_agent) REFERENCES agent(id_agent)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT chk_quotite_travail CHECK (quotite_travail > 0 AND quotite_travail <= 100),
|
||
CONSTRAINT chk_quota_reference_positif CHECK (quota_reference_minutes > 0),
|
||
CONSTRAINT chk_quota_cible_positif CHECK (quota_cible_minutes > 0),
|
||
CONSTRAINT uk_quota_agent_annee UNIQUE (id_agent, annee),
|
||
|
||
INDEX idx_quota_agent_annee (id_agent, annee)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE pta_annuel (
|
||
id_pta INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_agent INT UNSIGNED NOT NULL,
|
||
date_debut DATE NOT NULL,
|
||
date_fin DATE NOT NULL,
|
||
quotite_travail DECIMAL(5,2) NOT NULL,
|
||
quota_reference_minutes INT UNSIGNED NOT NULL DEFAULT 96420,
|
||
quota_cible_minutes INT UNSIGNED NOT NULL,
|
||
enveloppe_formation_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 840,
|
||
conges_annuels_jours_cible DECIMAL(5,2) NOT NULL DEFAULT 25.00,
|
||
jours_fractionnement_cible TINYINT UNSIGNED NOT NULL DEFAULT 2,
|
||
statut ENUM('BROUILLON', 'A_CONTROLER', 'VALIDE') NOT NULL DEFAULT 'BROUILLON',
|
||
commentaire VARCHAR(1000) NULL,
|
||
date_validation DATETIME NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT fk_pta_annuel_agent
|
||
FOREIGN KEY (id_agent) REFERENCES agent(id_agent)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT chk_pta_annuel_dates CHECK (date_fin >= date_debut),
|
||
CONSTRAINT chk_pta_annuel_quotite CHECK (quotite_travail > 0 AND quotite_travail <= 100),
|
||
CONSTRAINT chk_pta_annuel_quota CHECK (quota_reference_minutes > 0 AND quota_cible_minutes > 0),
|
||
CONSTRAINT uk_pta_annuel_agent_periode UNIQUE (id_agent, date_debut, date_fin),
|
||
|
||
INDEX idx_pta_annuel_agent_statut (id_agent, statut),
|
||
INDEX idx_pta_annuel_periode (date_debut, date_fin)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================================
|
||
-- 8. SEMAINES / PLANNINGS / CRÉNEAUX
|
||
-- ============================================================================
|
||
CREATE TABLE semaine (
|
||
id_semaine INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
annee SMALLINT UNSIGNED NOT NULL COMMENT 'Année ISO de la semaine',
|
||
numero_semaine TINYINT UNSIGNED NOT NULL,
|
||
date_debut DATE NOT NULL COMMENT 'Lundi',
|
||
date_fin DATE NOT NULL COMMENT 'Dimanche',
|
||
statut ENUM('OUVERTE', 'VALIDEE', 'VERROUILLEE') NOT NULL DEFAULT 'OUVERTE',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT uk_semaine_annee_numero UNIQUE (annee, numero_semaine),
|
||
CONSTRAINT uk_semaine_dates UNIQUE (date_debut, date_fin),
|
||
CONSTRAINT chk_numero_semaine CHECK (numero_semaine BETWEEN 1 AND 53),
|
||
CONSTRAINT chk_dates_semaine CHECK (date_fin >= date_debut),
|
||
|
||
INDEX idx_semaine_dates (date_debut, date_fin)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE planning (
|
||
id_planning INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_agent INT UNSIGNED NOT NULL,
|
||
id_semaine INT UNSIGNED NOT NULL,
|
||
id_structure INT UNSIGNED NOT NULL,
|
||
statut ENUM('BROUILLON', 'VALIDE') NOT NULL DEFAULT 'BROUILLON',
|
||
commentaire VARCHAR(500) NULL,
|
||
id_planning_source INT UNSIGNED NULL,
|
||
date_creation DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
date_modification DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
date_validation DATETIME NULL,
|
||
|
||
CONSTRAINT fk_planning_agent
|
||
FOREIGN KEY (id_agent) REFERENCES agent(id_agent)
|
||
ON UPDATE CASCADE ON DELETE RESTRICT,
|
||
|
||
CONSTRAINT fk_planning_semaine
|
||
FOREIGN KEY (id_semaine) REFERENCES semaine(id_semaine)
|
||
ON UPDATE CASCADE ON DELETE RESTRICT,
|
||
|
||
CONSTRAINT fk_planning_structure
|
||
FOREIGN KEY (id_structure) REFERENCES structure(id_structure)
|
||
ON UPDATE CASCADE ON DELETE RESTRICT,
|
||
|
||
CONSTRAINT fk_planning_source
|
||
FOREIGN KEY (id_planning_source) REFERENCES planning(id_planning)
|
||
ON UPDATE CASCADE ON DELETE SET NULL,
|
||
|
||
CONSTRAINT uk_planning_agent_semaine_structure
|
||
UNIQUE (id_agent, id_semaine, id_structure),
|
||
|
||
INDEX idx_planning_agent_statut (id_agent, statut),
|
||
INDEX idx_planning_semaine_structure (id_semaine, id_structure, statut),
|
||
INDEX idx_planning_modification (date_modification)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- CORRECTION #1832 : le type de id_motif est correct dès sa création.
|
||
CREATE TABLE creneau_horaire (
|
||
id_creneau INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_planning INT UNSIGNED NOT NULL,
|
||
id_motif INT UNSIGNED NOT NULL,
|
||
date_jour DATE NOT NULL,
|
||
heure_debut TIME NOT NULL,
|
||
heure_fin TIME NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT fk_creneau_planning
|
||
FOREIGN KEY (id_planning) REFERENCES planning(id_planning)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT fk_creneau_motif
|
||
FOREIGN KEY (id_motif) REFERENCES motif_planning(id_motif)
|
||
ON UPDATE CASCADE ON DELETE RESTRICT,
|
||
|
||
CONSTRAINT chk_creneau_ordre CHECK (heure_fin > heure_debut),
|
||
CONSTRAINT chk_creneau_quart_heure CHECK (
|
||
MOD(MINUTE(heure_debut), 15) = 0
|
||
AND MOD(MINUTE(heure_fin), 15) = 0
|
||
AND SECOND(heure_debut) = 0
|
||
AND SECOND(heure_fin) = 0
|
||
),
|
||
|
||
INDEX idx_creneau_planning_date (id_planning, date_jour, heure_debut, heure_fin),
|
||
INDEX idx_creneau_motif (id_motif),
|
||
INDEX idx_creneau_date (date_jour, heure_debut, heure_fin)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================================
|
||
-- 9. SEMAINES TYPES
|
||
-- ============================================================================
|
||
CREATE TABLE modele_semaine_agent (
|
||
id_modele INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_agent INT UNSIGNED NOT NULL,
|
||
nom VARCHAR(120) NOT NULL,
|
||
date_creation DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
date_modification DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT fk_modele_semaine_agent
|
||
FOREIGN KEY (id_agent) REFERENCES agent(id_agent)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT uk_modele_semaine_agent_nom UNIQUE (id_agent, nom),
|
||
INDEX idx_modele_semaine_agent (id_agent, date_modification)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE modele_semaine_creneau (
|
||
id_modele_creneau INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_modele INT UNSIGNED NOT NULL,
|
||
jour_semaine TINYINT UNSIGNED NOT NULL COMMENT '1=lundi, 5=vendredi',
|
||
id_structure INT UNSIGNED NOT NULL,
|
||
id_motif INT UNSIGNED NOT NULL,
|
||
heure_debut TIME NOT NULL,
|
||
heure_fin TIME NOT NULL,
|
||
|
||
CONSTRAINT fk_modele_creneau_modele
|
||
FOREIGN KEY (id_modele) REFERENCES modele_semaine_agent(id_modele)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT fk_modele_creneau_structure
|
||
FOREIGN KEY (id_structure) REFERENCES structure(id_structure)
|
||
ON UPDATE CASCADE ON DELETE RESTRICT,
|
||
|
||
CONSTRAINT fk_modele_creneau_motif
|
||
FOREIGN KEY (id_motif) REFERENCES motif_planning(id_motif)
|
||
ON UPDATE CASCADE ON DELETE RESTRICT,
|
||
|
||
CONSTRAINT chk_modele_creneau_jour CHECK (jour_semaine BETWEEN 1 AND 5),
|
||
CONSTRAINT chk_modele_creneau_ordre CHECK (heure_fin > heure_debut),
|
||
CONSTRAINT chk_modele_creneau_quart_heure CHECK (
|
||
MOD(MINUTE(heure_debut), 15) = 0
|
||
AND MOD(MINUTE(heure_fin), 15) = 0
|
||
AND SECOND(heure_debut) = 0
|
||
AND SECOND(heure_fin) = 0
|
||
),
|
||
|
||
INDEX idx_modele_creneau_modele_jour (id_modele, jour_semaine, heure_debut),
|
||
INDEX idx_modele_creneau_structure (id_structure),
|
||
INDEX idx_modele_creneau_motif (id_motif)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================================
|
||
-- 10. VACANCES SCOLAIRES
|
||
-- ============================================================================
|
||
CREATE TABLE periode_vacance (
|
||
id_periode INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
libelle VARCHAR(150) NOT NULL,
|
||
date_debut DATE NOT NULL,
|
||
date_fin DATE NOT NULL COMMENT 'Date inclusive',
|
||
annee_scolaire VARCHAR(9) NOT NULL,
|
||
academie VARCHAR(100) NOT NULL DEFAULT '',
|
||
zone VARCHAR(50) NOT NULL DEFAULT '',
|
||
source ENUM('MANUEL', 'DATA_GOUV') NOT NULL DEFAULT 'MANUEL',
|
||
actif BOOLEAN NOT NULL DEFAULT TRUE,
|
||
date_creation DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
date_modification DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT chk_periode_vacance_dates CHECK (date_fin >= date_debut),
|
||
CONSTRAINT uk_periode_vacance UNIQUE (date_debut, date_fin, annee_scolaire, academie),
|
||
|
||
INDEX idx_periode_vacance_dates (date_debut, date_fin),
|
||
INDEX idx_periode_vacance_annee (annee_scolaire),
|
||
INDEX idx_periode_vacance_actif_dates (actif, date_debut, date_fin)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================================
|
||
-- 11. PRÉFÉRENCES / INTERDICTIONS / CONTRAINTES AGENT
|
||
-- ============================================================================
|
||
CREATE TABLE agent_structure_souhait (
|
||
id_souhait INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_agent INT UNSIGNED NOT NULL,
|
||
id_structure INT UNSIGNED NOT NULL,
|
||
type_souhait ENUM('PREFERENCE', 'INTERDICTION') NOT NULL,
|
||
priorite TINYINT UNSIGNED NOT NULL DEFAULT 1,
|
||
distance_km DECIMAL(7,2) NULL,
|
||
commentaire VARCHAR(500) NULL,
|
||
actif BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT fk_souhait_agent
|
||
FOREIGN KEY (id_agent) REFERENCES agent(id_agent)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT fk_souhait_structure
|
||
FOREIGN KEY (id_structure) REFERENCES structure(id_structure)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT chk_souhait_priorite CHECK (priorite BETWEEN 1 AND 5),
|
||
CONSTRAINT chk_souhait_distance CHECK (distance_km IS NULL OR distance_km >= 0),
|
||
CONSTRAINT uk_souhait_agent_structure UNIQUE (id_agent, id_structure),
|
||
|
||
INDEX idx_souhait_agent_type (id_agent, type_souhait, actif)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE agent_contrainte (
|
||
id_contrainte INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_agent INT UNSIGNED NOT NULL,
|
||
type_contrainte ENUM('MEDICALE', 'TEMPS_PARTIEL_THERAPEUTIQUE') NOT NULL,
|
||
date_debut DATE NOT NULL,
|
||
date_fin DATE NULL,
|
||
quotite_temporaire DECIMAL(5,2) NULL,
|
||
maximum_minutes_jour SMALLINT UNSIGNED NULL,
|
||
maximum_minutes_semaine SMALLINT UNSIGNED NULL,
|
||
interdit_matin BOOLEAN NOT NULL DEFAULT FALSE,
|
||
interdit_midi BOOLEAN NOT NULL DEFAULT FALSE,
|
||
interdit_soir BOOLEAN NOT NULL DEFAULT FALSE,
|
||
commentaire VARCHAR(1000) NOT NULL,
|
||
actif BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT fk_contrainte_agent
|
||
FOREIGN KEY (id_agent) REFERENCES agent(id_agent)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT chk_contrainte_dates CHECK (date_fin IS NULL OR date_fin >= date_debut),
|
||
CONSTRAINT chk_contrainte_quotite CHECK (
|
||
quotite_temporaire IS NULL OR (quotite_temporaire > 0 AND quotite_temporaire <= 100)
|
||
),
|
||
CONSTRAINT chk_contrainte_max_jour CHECK (maximum_minutes_jour IS NULL OR maximum_minutes_jour > 0),
|
||
CONSTRAINT chk_contrainte_max_semaine CHECK (maximum_minutes_semaine IS NULL OR maximum_minutes_semaine > 0),
|
||
|
||
INDEX idx_contrainte_agent_dates (id_agent, actif, date_debut, date_fin)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================================
|
||
-- 12. ÉQUIPES PTA
|
||
-- ============================================================================
|
||
CREATE TABLE equipe_pta (
|
||
id_equipe INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_structure INT UNSIGNED NOT NULL,
|
||
type_equipe ENUM('MERCREDI_PERISCOLAIRE', 'EXTRASCOLAIRE', 'PERISCOLAIRE_SEMAINE') NOT NULL,
|
||
libelle VARCHAR(180) NOT NULL,
|
||
date_debut DATE NOT NULL,
|
||
date_fin DATE NOT NULL,
|
||
nombre_enfants_moins_6 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
|
||
nombre_enfants_6_plus SMALLINT UNSIGNED NOT NULL DEFAULT 0,
|
||
ratio_moins_6 TINYINT UNSIGNED NOT NULL DEFAULT 8,
|
||
ratio_6_plus TINYINT UNSIGNED NOT NULL DEFAULT 12,
|
||
minimum_agents TINYINT UNSIGNED NOT NULL DEFAULT 1,
|
||
pourcentage_diplomes_min DECIMAL(5,2) NOT NULL DEFAULT 50.00,
|
||
statut ENUM('BROUILLON', 'A_CONTROLER', 'VALIDEE') NOT NULL DEFAULT 'BROUILLON',
|
||
commentaire VARCHAR(1000) NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT fk_equipe_pta_structure
|
||
FOREIGN KEY (id_structure) REFERENCES structure(id_structure)
|
||
ON UPDATE CASCADE ON DELETE RESTRICT,
|
||
|
||
CONSTRAINT chk_equipe_dates CHECK (date_fin >= date_debut),
|
||
CONSTRAINT chk_equipe_ratios CHECK (ratio_moins_6 > 0 AND ratio_6_plus > 0),
|
||
CONSTRAINT chk_equipe_minimum CHECK (minimum_agents > 0),
|
||
CONSTRAINT chk_equipe_diplomes CHECK (pourcentage_diplomes_min BETWEEN 0 AND 100),
|
||
|
||
INDEX idx_equipe_pta_type_dates (type_equipe, date_debut, date_fin),
|
||
INDEX idx_equipe_pta_structure_dates (id_structure, date_debut, date_fin)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE equipe_pta_membre (
|
||
id_equipe_membre INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
id_equipe INT UNSIGNED NOT NULL,
|
||
id_agent INT UNSIGNED NOT NULL,
|
||
fonction_equipe ENUM('RESPONSABLE', 'ANIMATION', 'RESTAURATION_ENTRETIEN') NOT NULL DEFAULT 'ANIMATION',
|
||
date_debut DATE NULL,
|
||
date_fin DATE NULL,
|
||
commentaire VARCHAR(500) NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT fk_equipe_membre_equipe
|
||
FOREIGN KEY (id_equipe) REFERENCES equipe_pta(id_equipe)
|
||
ON UPDATE CASCADE ON DELETE CASCADE,
|
||
|
||
CONSTRAINT fk_equipe_membre_agent
|
||
FOREIGN KEY (id_agent) REFERENCES agent(id_agent)
|
||
ON UPDATE CASCADE ON DELETE RESTRICT,
|
||
|
||
CONSTRAINT chk_equipe_membre_dates
|
||
CHECK (date_fin IS NULL OR date_debut IS NULL OR date_fin >= date_debut),
|
||
|
||
CONSTRAINT uk_equipe_membre UNIQUE (id_equipe, id_agent),
|
||
INDEX idx_equipe_membre_agent (id_agent, id_equipe)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================================
|
||
-- 13. VUES DE PILOTAGE
|
||
-- ============================================================================
|
||
|
||
CREATE OR REPLACE VIEW v_planning_agent_semaine AS
|
||
SELECT
|
||
p.id_planning,
|
||
p.id_agent,
|
||
a.matricule,
|
||
a.nom,
|
||
a.prenom,
|
||
CONCAT(a.prenom, ' ', a.nom) AS agent,
|
||
p.id_semaine,
|
||
s.annee,
|
||
s.numero_semaine,
|
||
s.date_debut,
|
||
s.date_fin,
|
||
p.id_structure,
|
||
st.code AS code_structure,
|
||
st.nom AS lieu_affectation,
|
||
st.type_affectation,
|
||
p.statut,
|
||
p.date_creation,
|
||
p.date_modification,
|
||
p.date_validation,
|
||
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 minutes_planifiees,
|
||
COALESCE(SUM(
|
||
CASE WHEN m.compte_dans_quota = TRUE THEN
|
||
TIMESTAMPDIFF(
|
||
MINUTE,
|
||
CONCAT(c.date_jour, ' ', c.heure_debut),
|
||
CONCAT(c.date_jour, ' ', c.heure_fin)
|
||
)
|
||
ELSE 0 END
|
||
), 0) AS minutes_decomptees_quota,
|
||
ROUND(COALESCE(SUM(
|
||
TIMESTAMPDIFF(
|
||
MINUTE,
|
||
CONCAT(c.date_jour, ' ', c.heure_debut),
|
||
CONCAT(c.date_jour, ' ', c.heure_fin)
|
||
)
|
||
), 0) / 60, 2) AS heures_planifiees
|
||
FROM planning p
|
||
INNER JOIN agent a ON a.id_agent = p.id_agent
|
||
INNER JOIN semaine s ON s.id_semaine = p.id_semaine
|
||
INNER JOIN structure st ON st.id_structure = p.id_structure
|
||
LEFT JOIN creneau_horaire c ON c.id_planning = p.id_planning
|
||
LEFT JOIN motif_planning m ON m.id_motif = c.id_motif
|
||
GROUP BY
|
||
p.id_planning, p.id_agent, a.matricule, a.nom, a.prenom,
|
||
p.id_semaine, s.annee, s.numero_semaine, s.date_debut, s.date_fin,
|
||
p.id_structure, st.code, st.nom, st.type_affectation,
|
||
p.statut, p.date_creation, p.date_modification, p.date_validation;
|
||
|
||
-- Quota annuel = année CIVILE.
|
||
CREATE OR REPLACE VIEW v_suivi_quota_annuel AS
|
||
SELECT
|
||
q.id_quota,
|
||
q.id_agent,
|
||
a.matricule,
|
||
a.nom,
|
||
a.prenom,
|
||
a.date_debut_contrat,
|
||
a.date_fin_contrat,
|
||
q.annee,
|
||
q.quotite_travail,
|
||
q.quota_reference_minutes,
|
||
q.quota_cible_minutes,
|
||
STR_TO_DATE(CONCAT(q.annee, '-01-01'), '%Y-%m-%d') AS date_debut_periode,
|
||
STR_TO_DATE(CONCAT(q.annee, '-12-31'), '%Y-%m-%d') AS date_fin_periode,
|
||
ROUND(q.quota_reference_minutes / 60, 2) AS quota_reference_heures,
|
||
ROUND(q.quota_cible_minutes / 60, 2) AS quota_cible_heures,
|
||
|
||
COALESCE(SUM(CASE
|
||
WHEN p.statut = 'VALIDE' AND m.compte_dans_quota = TRUE THEN
|
||
TIMESTAMPDIFF(
|
||
MINUTE,
|
||
CONCAT(c.date_jour, ' ', c.heure_debut),
|
||
CONCAT(c.date_jour, ' ', c.heure_fin)
|
||
)
|
||
ELSE 0 END
|
||
), 0) AS minutes_valides,
|
||
|
||
COALESCE(SUM(CASE
|
||
WHEN p.statut = 'BROUILLON' AND m.compte_dans_quota = TRUE THEN
|
||
TIMESTAMPDIFF(
|
||
MINUTE,
|
||
CONCAT(c.date_jour, ' ', c.heure_debut),
|
||
CONCAT(c.date_jour, ' ', c.heure_fin)
|
||
)
|
||
ELSE 0 END
|
||
), 0) AS minutes_brouillon,
|
||
|
||
COALESCE(SUM(CASE
|
||
WHEN m.compte_dans_quota = TRUE THEN
|
||
TIMESTAMPDIFF(
|
||
MINUTE,
|
||
CONCAT(c.date_jour, ' ', c.heure_debut),
|
||
CONCAT(c.date_jour, ' ', c.heure_fin)
|
||
)
|
||
ELSE 0 END
|
||
), 0) AS minutes_affectees,
|
||
|
||
q.quota_cible_minutes - COALESCE(SUM(CASE
|
||
WHEN m.compte_dans_quota = TRUE THEN
|
||
TIMESTAMPDIFF(
|
||
MINUTE,
|
||
CONCAT(c.date_jour, ' ', c.heure_debut),
|
||
CONCAT(c.date_jour, ' ', c.heure_fin)
|
||
)
|
||
ELSE 0 END
|
||
), 0) AS minutes_restantes
|
||
FROM quota_agent_annuel q
|
||
INNER JOIN agent a ON a.id_agent = q.id_agent
|
||
LEFT JOIN planning p ON p.id_agent = q.id_agent
|
||
LEFT JOIN creneau_horaire c
|
||
ON c.id_planning = p.id_planning
|
||
AND c.date_jour BETWEEN
|
||
STR_TO_DATE(CONCAT(q.annee, '-01-01'), '%Y-%m-%d')
|
||
AND STR_TO_DATE(CONCAT(q.annee, '-12-31'), '%Y-%m-%d')
|
||
LEFT JOIN motif_planning m ON m.id_motif = c.id_motif
|
||
GROUP BY
|
||
q.id_quota, q.id_agent, a.matricule, a.nom, a.prenom,
|
||
a.date_debut_contrat, a.date_fin_contrat,
|
||
q.annee, q.quotite_travail,
|
||
q.quota_reference_minutes, q.quota_cible_minutes;
|
||
|
||
CREATE OR REPLACE VIEW v_plannings_en_attente_validation AS
|
||
SELECT
|
||
p.id_planning,
|
||
p.id_agent,
|
||
a.matricule,
|
||
a.nom,
|
||
a.prenom,
|
||
CONCAT(a.prenom, ' ', a.nom) AS agent,
|
||
p.id_structure,
|
||
st.code AS code_structure,
|
||
st.nom AS lieu_affectation,
|
||
p.id_semaine,
|
||
s.annee,
|
||
s.numero_semaine,
|
||
s.date_debut,
|
||
s.date_fin,
|
||
p.date_modification,
|
||
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 minutes_totales,
|
||
COALESCE(SUM(CASE
|
||
WHEN m.compte_dans_quota = TRUE 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 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.statut = 'BROUILLON'
|
||
GROUP BY
|
||
p.id_planning, p.id_agent, a.matricule, a.nom, a.prenom,
|
||
p.id_structure, st.code, st.nom,
|
||
p.id_semaine, s.annee, s.numero_semaine, s.date_debut, s.date_fin,
|
||
p.date_modification;
|
||
|
||
-- Uniquement les PTA correspondant à une année civile complète.
|
||
CREATE OR REPLACE VIEW v_pta_annuel_synthese AS
|
||
SELECT
|
||
pa.id_pta,
|
||
pa.id_agent,
|
||
a.matricule,
|
||
a.nom,
|
||
a.prenom,
|
||
po.code AS poste_code,
|
||
po.libelle AS poste_libelle,
|
||
a.type_contrat,
|
||
pa.date_debut,
|
||
pa.date_fin,
|
||
pa.quotite_travail,
|
||
pa.quota_cible_minutes,
|
||
COALESCE(SUM(CASE
|
||
WHEN mp.compte_dans_quota = TRUE THEN
|
||
TIMESTAMPDIFF(
|
||
MINUTE,
|
||
CONCAT(ch.date_jour, ' ', ch.heure_debut),
|
||
CONCAT(ch.date_jour, ' ', ch.heure_fin)
|
||
)
|
||
ELSE 0 END
|
||
), 0) AS minutes_decomptees,
|
||
pa.quota_cible_minutes - COALESCE(SUM(CASE
|
||
WHEN mp.compte_dans_quota = TRUE THEN
|
||
TIMESTAMPDIFF(
|
||
MINUTE,
|
||
CONCAT(ch.date_jour, ' ', ch.heure_debut),
|
||
CONCAT(ch.date_jour, ' ', ch.heure_fin)
|
||
)
|
||
ELSE 0 END
|
||
), 0) AS minutes_restantes,
|
||
pa.statut
|
||
FROM pta_annuel pa
|
||
INNER JOIN agent a ON a.id_agent = pa.id_agent
|
||
LEFT JOIN poste_agent po ON po.id_poste = a.id_poste
|
||
LEFT JOIN planning p ON p.id_agent = pa.id_agent
|
||
LEFT JOIN creneau_horaire ch
|
||
ON ch.id_planning = p.id_planning
|
||
AND ch.date_jour BETWEEN pa.date_debut AND pa.date_fin
|
||
LEFT JOIN motif_planning mp ON mp.id_motif = ch.id_motif
|
||
WHERE pa.date_debut = STR_TO_DATE(CONCAT(YEAR(pa.date_debut), '-01-01'), '%Y-%m-%d')
|
||
AND pa.date_fin = STR_TO_DATE(CONCAT(YEAR(pa.date_debut), '-12-31'), '%Y-%m-%d')
|
||
GROUP BY
|
||
pa.id_pta, pa.id_agent, a.matricule, a.nom, a.prenom,
|
||
po.code, po.libelle, a.type_contrat,
|
||
pa.date_debut, pa.date_fin, pa.quotite_travail,
|
||
pa.quota_cible_minutes, pa.statut;
|
||
|
||
CREATE OR REPLACE VIEW v_equipe_pta_controle AS
|
||
SELECT
|
||
e.id_equipe,
|
||
e.libelle,
|
||
e.type_equipe,
|
||
e.id_structure,
|
||
s.nom AS structure_nom,
|
||
e.date_debut,
|
||
e.date_fin,
|
||
e.nombre_enfants_moins_6,
|
||
e.nombre_enfants_6_plus,
|
||
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,
|
||
COALESCE(SUM(CASE WHEN a.est_diplome = TRUE THEN 1 ELSE 0 END), 0) AS agents_diplomes,
|
||
CASE
|
||
WHEN COUNT(em.id_agent) = 0 THEN 0
|
||
ELSE ROUND(
|
||
100 * SUM(CASE WHEN a.est_diplome = TRUE THEN 1 ELSE 0 END)
|
||
/ COUNT(em.id_agent),
|
||
2
|
||
)
|
||
END AS pourcentage_diplomes,
|
||
e.pourcentage_diplomes_min,
|
||
COALESCE(SUM(CASE WHEN em.fonction_equipe = 'RESPONSABLE' THEN 1 ELSE 0 END), 0) AS responsables,
|
||
e.statut
|
||
FROM equipe_pta e
|
||
INNER 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
|
||
GROUP BY
|
||
e.id_equipe, e.libelle, e.type_equipe, e.id_structure, s.nom,
|
||
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;
|
||
|
||
-- ============================================================================
|
||
-- 14. PROCÉDURES DE CONTRÔLE / VALIDATION
|
||
-- ============================================================================
|
||
DELIMITER $$
|
||
|
||
DROP PROCEDURE IF EXISTS sp_preparer_controles_planning$$
|
||
CREATE PROCEDURE sp_preparer_controles_planning(
|
||
IN p_id_planning INT UNSIGNED
|
||
)
|
||
controle: BEGIN
|
||
DECLARE v_planning_existe INT DEFAULT 0;
|
||
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_controles_planning;
|
||
|
||
CREATE TEMPORARY TABLE tmp_controles_planning (
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
niveau ENUM('ERREUR', 'ALERTE', 'INFO') NOT NULL,
|
||
code VARCHAR(100) NOT NULL,
|
||
message VARCHAR(1000) NOT NULL
|
||
);
|
||
|
||
SELECT COUNT(*)
|
||
INTO v_planning_existe
|
||
FROM planning
|
||
WHERE id_planning = p_id_planning;
|
||
|
||
IF v_planning_existe = 0 THEN
|
||
INSERT INTO tmp_controles_planning(niveau, code, message)
|
||
VALUES (
|
||
'ERREUR',
|
||
'PLANNING_INTROUVABLE',
|
||
CONCAT('Le planning #', p_id_planning, ' n''existe pas.')
|
||
);
|
||
LEAVE controle;
|
||
END IF;
|
||
|
||
-- Un planning validable doit contenir au moins un créneau.
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM creneau_horaire WHERE id_planning = p_id_planning
|
||
) THEN
|
||
INSERT INTO tmp_controles_planning(niveau, code, message)
|
||
VALUES ('ERREUR', 'AUCUN_CRENEAU', 'Le planning ne contient aucun créneau horaire.');
|
||
END IF;
|
||
|
||
-- Créneaux hors de la semaine du planning.
|
||
INSERT INTO tmp_controles_planning(niveau, code, message)
|
||
SELECT
|
||
'ERREUR',
|
||
'CRENEAU_HORS_SEMAINE',
|
||
CONCAT(
|
||
'Le créneau #', c.id_creneau,
|
||
' du ', DATE_FORMAT(c.date_jour, '%d/%m/%Y'),
|
||
' est en dehors de la semaine ', s.numero_semaine,
|
||
' (', DATE_FORMAT(s.date_debut, '%d/%m/%Y'),
|
||
' au ', DATE_FORMAT(s.date_fin, '%d/%m/%Y'), ').'
|
||
)
|
||
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
|
||
WHERE p.id_planning = p_id_planning
|
||
AND c.date_jour NOT BETWEEN s.date_debut AND s.date_fin;
|
||
|
||
-- Avant le début du contrat.
|
||
INSERT INTO tmp_controles_planning(niveau, code, message)
|
||
SELECT
|
||
'ERREUR',
|
||
'AVANT_DEBUT_CONTRAT',
|
||
CONCAT(
|
||
'Le créneau #', c.id_creneau,
|
||
' du ', DATE_FORMAT(c.date_jour, '%d/%m/%Y'),
|
||
' est antérieur au début du contrat de ',
|
||
CONCAT(a.prenom, ' ', a.nom),
|
||
' (', DATE_FORMAT(a.date_debut_contrat, '%d/%m/%Y'), ').'
|
||
)
|
||
FROM planning p
|
||
INNER JOIN agent a ON a.id_agent = p.id_agent
|
||
INNER JOIN creneau_horaire c ON c.id_planning = p.id_planning
|
||
WHERE p.id_planning = p_id_planning
|
||
AND a.date_debut_contrat IS NOT NULL
|
||
AND c.date_jour < a.date_debut_contrat;
|
||
|
||
-- Après la fin du contrat, si elle existe.
|
||
INSERT INTO tmp_controles_planning(niveau, code, message)
|
||
SELECT
|
||
'ERREUR',
|
||
'APRES_FIN_CONTRAT',
|
||
CONCAT(
|
||
'Le créneau #', c.id_creneau,
|
||
' du ', DATE_FORMAT(c.date_jour, '%d/%m/%Y'),
|
||
' est postérieur à la fin du contrat de ',
|
||
CONCAT(a.prenom, ' ', a.nom),
|
||
' (', DATE_FORMAT(a.date_fin_contrat, '%d/%m/%Y'), ').'
|
||
)
|
||
FROM planning p
|
||
INNER JOIN agent a ON a.id_agent = p.id_agent
|
||
INNER JOIN creneau_horaire c ON c.id_planning = p.id_planning
|
||
WHERE p.id_planning = p_id_planning
|
||
AND a.date_fin_contrat IS NOT NULL
|
||
AND c.date_jour > a.date_fin_contrat;
|
||
|
||
-- Chevauchements internes.
|
||
INSERT INTO tmp_controles_planning(niveau, code, message)
|
||
SELECT
|
||
'ERREUR',
|
||
'CHEVAUCHEMENT_INTERNE',
|
||
CONCAT(
|
||
'Les créneaux #', c1.id_creneau,
|
||
' et #', c2.id_creneau,
|
||
' se chevauchent le ', DATE_FORMAT(c1.date_jour, '%d/%m/%Y'),
|
||
' (', TIME_FORMAT(c1.heure_debut, '%H:%i'), '-', TIME_FORMAT(c1.heure_fin, '%H:%i'),
|
||
' et ', TIME_FORMAT(c2.heure_debut, '%H:%i'), '-', TIME_FORMAT(c2.heure_fin, '%H:%i'), ').'
|
||
)
|
||
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 = p_id_planning;
|
||
|
||
-- Double affectation de l'agent, tous lieux et tous statuts confondus.
|
||
INSERT INTO tmp_controles_planning(niveau, code, message)
|
||
SELECT DISTINCT
|
||
'ERREUR',
|
||
'CHEVAUCHEMENT_AUTRE_PLANNING',
|
||
CONCAT(
|
||
'Le créneau #', c1.id_creneau,
|
||
' chevauche le créneau #', c2.id_creneau,
|
||
' du planning #', p2.id_planning,
|
||
' au lieu « ', st2.nom, ' » le ',
|
||
DATE_FORMAT(c1.date_jour, '%d/%m/%Y'),
|
||
' (', TIME_FORMAT(c2.heure_debut, '%H:%i'), '-', TIME_FORMAT(c2.heure_fin, '%H:%i'), ').'
|
||
)
|
||
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 st2 ON st2.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 = p_id_planning;
|
||
|
||
-- Dépassement du quota CIVIL de l'année concernée.
|
||
INSERT INTO tmp_controles_planning(niveau, code, message)
|
||
SELECT DISTINCT
|
||
'ALERTE',
|
||
'QUOTA_DEPASSE',
|
||
CONCAT(
|
||
'Le quota de ', ROUND(v.quota_cible_minutes / 60, 2), ' h ',
|
||
'pour l''année civile ', v.annee,
|
||
' est dépassé. Total affecté : ', ROUND(v.minutes_affectees / 60, 2), ' h.'
|
||
)
|
||
FROM planning p
|
||
INNER JOIN creneau_horaire cp ON cp.id_planning = p.id_planning
|
||
INNER JOIN v_suivi_quota_annuel v
|
||
ON v.id_agent = p.id_agent
|
||
AND YEAR(cp.date_jour) = v.annee
|
||
WHERE p.id_planning = p_id_planning
|
||
AND v.minutes_restantes < 0;
|
||
|
||
-- Aucun quota paramétré pour l'année civile touchée.
|
||
INSERT INTO tmp_controles_planning(niveau, code, message)
|
||
SELECT DISTINCT
|
||
'ALERTE',
|
||
'QUOTA_NON_PARAMETRE',
|
||
CONCAT(
|
||
'Aucun quota n''est paramétré pour l''agent sur l''année civile ',
|
||
YEAR(c.date_jour), '.'
|
||
)
|
||
FROM planning p
|
||
INNER JOIN creneau_horaire c ON c.id_planning = p.id_planning
|
||
WHERE p.id_planning = p_id_planning
|
||
AND NOT EXISTS (
|
||
SELECT 1
|
||
FROM quota_agent_annuel q
|
||
WHERE q.id_agent = p.id_agent
|
||
AND q.annee = YEAR(c.date_jour)
|
||
);
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM tmp_controles_planning) THEN
|
||
INSERT INTO tmp_controles_planning(niveau, code, message)
|
||
VALUES ('INFO', 'OK', 'Aucune anomalie détectée. Le planning peut être validé.');
|
||
END IF;
|
||
END$$
|
||
|
||
DROP PROCEDURE IF EXISTS sp_controler_planning$$
|
||
CREATE PROCEDURE sp_controler_planning(
|
||
IN p_id_planning INT UNSIGNED
|
||
)
|
||
BEGIN
|
||
CALL sp_preparer_controles_planning(p_id_planning);
|
||
|
||
SELECT niveau, code, message
|
||
FROM tmp_controles_planning
|
||
ORDER BY FIELD(niveau, 'ERREUR', 'ALERTE', 'INFO'), id;
|
||
END$$
|
||
|
||
DROP PROCEDURE IF EXISTS sp_valider_planning$$
|
||
CREATE PROCEDURE sp_valider_planning(
|
||
IN p_id_planning INT UNSIGNED,
|
||
IN p_forcer_depassement_quota BOOLEAN
|
||
)
|
||
BEGIN
|
||
DECLARE v_nb_erreurs INT DEFAULT 0;
|
||
DECLARE v_nb_alertes_quota INT DEFAULT 0;
|
||
DECLARE v_message VARCHAR(1000);
|
||
|
||
CALL sp_preparer_controles_planning(p_id_planning);
|
||
|
||
SELECT COUNT(*) INTO v_nb_erreurs
|
||
FROM tmp_controles_planning
|
||
WHERE niveau = 'ERREUR';
|
||
|
||
IF v_nb_erreurs > 0 THEN
|
||
SELECT message INTO v_message
|
||
FROM tmp_controles_planning
|
||
WHERE niveau = 'ERREUR'
|
||
ORDER BY id
|
||
LIMIT 1;
|
||
|
||
SET v_message = CONCAT(
|
||
'Validation impossible. ', v_nb_erreurs,
|
||
' erreur(s) détectée(s). Première erreur : ', v_message
|
||
);
|
||
|
||
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_message;
|
||
END IF;
|
||
|
||
SELECT COUNT(*) INTO v_nb_alertes_quota
|
||
FROM tmp_controles_planning
|
||
WHERE code = 'QUOTA_DEPASSE';
|
||
|
||
IF v_nb_alertes_quota > 0
|
||
AND COALESCE(p_forcer_depassement_quota, FALSE) = FALSE THEN
|
||
SIGNAL SQLSTATE '45000'
|
||
SET MESSAGE_TEXT = 'Validation suspendue : le quota annuel serait dépassé. Relancer en forçant le dépassement après confirmation.';
|
||
END IF;
|
||
|
||
UPDATE planning
|
||
SET statut = 'VALIDE',
|
||
date_validation = CURRENT_TIMESTAMP,
|
||
date_modification = CURRENT_TIMESTAMP
|
||
WHERE id_planning = p_id_planning;
|
||
|
||
SELECT
|
||
p.id_planning,
|
||
p.statut,
|
||
p.date_validation,
|
||
'Planning validé avec succès.' AS message
|
||
FROM planning p
|
||
WHERE p.id_planning = p_id_planning;
|
||
END$$
|
||
|
||
DELIMITER ;
|
||
|
||
-- ============================================================================
|
||
-- FIN INSTALLATION PRODUCTION PTA V7.7
|
||
-- ============================================================================
|
||
-- Vérification recommandée après import :
|
||
--
|
||
-- SHOW CREATE TABLE creneau_horaire;
|
||
--
|
||
-- Vous devez voir :
|
||
-- id_motif int unsigned NOT NULL
|
||
-- CONSTRAINT fk_creneau_motif FOREIGN KEY (id_motif)
|
||
-- REFERENCES motif_planning(id_motif)
|
||
--
|
||
-- Aucun changement de type de id_motif n'est exécuté après création de la FK.
|
||
-- ============================================================================
|