1833 lines
82 KiB
SQL
1833 lines
82 KiB
SQL
-- ============================================================================
|
||
-- PTA — INSTALLATION PRODUCTION COMPLÈTE, EN UN SEUL FICHIER
|
||
-- Version applicative cible : V7.30
|
||
-- MariaDB 10.6+ / MySQL 8.0.16+
|
||
-- ============================================================================
|
||
-- Ce fichier remplace, à lui seul, l'enchaînement
|
||
-- database/pta_prod_schema_mariadb.sql (socle métier)
|
||
-- + database/migration_v7_8_securite_acces.sql
|
||
-- + database/migration_v7_9_tableau_de_bord.sql
|
||
-- + database/migration_v7_9b_droit_enveloppe_formation.sql
|
||
-- + database/migration_v7_9c_retrait_formation_repartition.sql
|
||
-- + database/migration_v7_9d_faq.sql
|
||
-- + database/migration_v7_9e_dsi_comptes_journal.sql
|
||
-- + database/migration_v7_9f_mot_de_passe_admin.sql
|
||
-- + database/migration_v7_9g_management.sql
|
||
-- + database/migration_v7_9h_jetons_service_enfance.sql
|
||
--
|
||
-- Il contient TOUT ce que l'application exige pour démarrer :
|
||
-- * les tables du métier, leurs clés étrangères, index et contraintes ;
|
||
-- * la matrice des droits (rôle -> pages, rôle -> permissions) ;
|
||
-- * les comptes d'accès à jeton et le journal d'activité ;
|
||
-- * les dix vues de contrôle d'accès vérifiées au démarrage ;
|
||
-- * les vues de pilotage et les procédures SQL historiques ;
|
||
-- * les seuls référentiels techniques indispensables : postes et motifs.
|
||
--
|
||
-- Il ne contient AUCUN agent, lieu, planning, quota ni jeu de démonstration.
|
||
-- La seule donnée nominative est le compte DSI d'installation (section 14.1).
|
||
--
|
||
-- ----------------------------------------------------------------------------
|
||
-- USAGE
|
||
-- ----------------------------------------------------------------------------
|
||
-- Sur une base vide, en ligne de commande :
|
||
--
|
||
-- mariadb -u pta -p pta < database/install_complet.sql
|
||
--
|
||
-- Depuis un client SQL déjà connecté à la base :
|
||
--
|
||
-- SOURCE database/install_complet.sql;
|
||
--
|
||
-- Avec la pile Docker du projet, il n'y a RIEN à faire : le socle est joué à la
|
||
-- création du volume par docker-entrypoint-initdb.d et les migrations par le
|
||
-- service db-migrate. Ce fichier vise l'installation MANUELLE sur un serveur de
|
||
-- production existant, ou la reconstruction d'une base à l'identique.
|
||
--
|
||
-- ----------------------------------------------------------------------------
|
||
-- PORTABILITÉ MySQL / MariaDB
|
||
-- ----------------------------------------------------------------------------
|
||
-- Ce fichier est joué tel quel sur les DEUX SGBD, et testé sur les deux :
|
||
-- MariaDB 11.4 (la pile Docker du projet) et MySQL 8.0 (le serveur de
|
||
-- production). Il n'utilise donc AUCUNE extension propre à l'un des deux.
|
||
--
|
||
-- Le piège à connaître avant d'y toucher : `ALTER TABLE … ADD COLUMN
|
||
-- IF NOT EXISTS …` et `DROP COLUMN IF EXISTS` sont des extensions MARIADB, que
|
||
-- MySQL rejette par une erreur 1064 de syntaxe. Les migrations du dossier
|
||
-- `database/` les emploient — elles ne visent que la pile Docker — mais ici le
|
||
-- test d'existence passe par `information_schema` et une requête préparée (voir
|
||
-- section 14). Ne pas « simplifier » ce passage en revenant à IF NOT EXISTS.
|
||
--
|
||
-- Tous les identifiants sont entourés de backticks : certains clients SQL les
|
||
-- exigent, et cela met le schéma à l'abri d'un mot devenu réservé dans une
|
||
-- version future (`role`, `action`, `source`, `zone`… sont des noms de colonnes
|
||
-- de ce schéma).
|
||
--
|
||
-- Reste une DÉPRÉCIATION, pas une erreur : `VALUES(colonne)` dans les clauses
|
||
-- ON DUPLICATE KEY UPDATE fait émettre à MySQL 8.0.20+ l'avertissement 1287.
|
||
-- La forme de remplacement (`… VALUES (…) AS n … = n.colonne`) est refusée par
|
||
-- MySQL < 8.0.19 et MariaDB < 10.3.3 ; la forme historique est conservée, comme
|
||
-- dans le socle et les migrations.
|
||
--
|
||
-- ----------------------------------------------------------------------------
|
||
-- IDEMPOTENCE
|
||
-- ----------------------------------------------------------------------------
|
||
-- Tout est écrit en CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE VIEW et
|
||
-- INSERT ... ON DUPLICATE KEY UPDATE : le rejouer sur une base déjà installée
|
||
-- ne détruit aucune donnée et ne fait que remettre les référentiels et les
|
||
-- droits dans leur état de référence.
|
||
--
|
||
-- Corollaire : ce fichier ne renseigne PAS la table `schema_migration`. Si la
|
||
-- pile Docker démarre ensuite sur cette base, db-migrate rejouera les neuf
|
||
-- migrations — elles sont elles aussi idempotentes, l'opération est sans effet.
|
||
--
|
||
-- ----------------------------------------------------------------------------
|
||
-- APRÈS L'INSTALLATION — deux gestes obligatoires
|
||
-- ----------------------------------------------------------------------------
|
||
-- 1. Se connecter avec le jeton d'installation PTA-INSTALLATION-DSI, puis le
|
||
-- RÉGÉNÉRER immédiatement : il est public, il est écrit en clair dans ce
|
||
-- fichier versionné (section 14.1). La page DSI alerte tant que c'est le
|
||
-- cas.
|
||
-- 2. Choisir le mot de passe du compte DSI à cette première connexion : les
|
||
-- rôles SERVICE_ENFANCE et DSI exigent ce second facteur, et un compte
|
||
-- privilégié sans mot de passe ne peut ouvrir aucune session.
|
||
--
|
||
-- ----------------------------------------------------------------------------
|
||
-- NOTE 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 exécuté après coup 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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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,
|
||
-- V7.9b : tous les agents ne bénéficient pas de l'enveloppe de 14 h.
|
||
`droit_enveloppe_formation` BOOLEAN NOT NULL DEFAULT TRUE
|
||
COMMENT 'Droit à l''enveloppe annuelle de 14 h de formation',
|
||
`date_debut_contrat` DATE NULL,
|
||
`id_poste` INT UNSIGNED NULL,
|
||
`type_contrat` ENUM('PERMANENT', 'TEMPORAIRE') NOT NULL DEFAULT 'PERMANENT',
|
||
`date_fin_contrat` DATE NULL,
|
||
`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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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),
|
||
-- compte_dans_quota = FALSE : les 1 607 h sont un volume NET dont les 25
|
||
-- jours de congés payés ont déjà été retranchés. Les imputer au quota les
|
||
-- décompterait une seconde fois. Idem pour les jours fériés.
|
||
('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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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. MATRICE DES DROITS — rôle -> pages, rôle -> permissions
|
||
-- ============================================================================
|
||
-- Depuis la V7.8, AUCUNE règle de droit n'est écrite en PHP : le contrôleur
|
||
-- pose une question à la base, la base répond oui ou non. Modifier un droit se
|
||
-- fait ici, en SQL, sans redéploiement. Une permission absente vaut « refusé ».
|
||
--
|
||
-- L'ENUM porte les quatre rôles dès la création : trois rôles métier plus la
|
||
-- DSI, administration technique sans aucun droit fonctionnel (V7.11).
|
||
-- ----------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `acces_role_page` (
|
||
`role` ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL,
|
||
`page` VARCHAR(50) NOT NULL,
|
||
`ordre_affichage` SMALLINT UNSIGNED NOT NULL DEFAULT 100,
|
||
|
||
PRIMARY KEY (`role`, `page`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS `acces_role_permission` (
|
||
`role` ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL,
|
||
`permission` VARCHAR(50) NOT NULL,
|
||
`autorise` BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
||
PRIMARY KEY (`role`, `permission`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- Le MODIFY rattrape une base créée par une version antérieure de ce fichier,
|
||
-- où l'ENUM ne portait que les trois rôles métier. Sans effet sur une base
|
||
-- neuve, il rend le script rejouable sur l'existant.
|
||
ALTER TABLE `acces_role_page`
|
||
MODIFY COLUMN `role` ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL;
|
||
|
||
ALTER TABLE `acces_role_permission`
|
||
MODIFY COLUMN `role` ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL;
|
||
|
||
-- ----------------------------------------------------------------------------
|
||
-- 13.1 PAGES AUTORISÉES PAR RÔLE
|
||
-- ----------------------------------------------------------------------------
|
||
-- Une page absente de cette table est INATTEINTE : le bouton n'apparaît pas et
|
||
-- l'accès direct à l'URL renvoie vers l'accueil du rôle. Ajouter une page à
|
||
-- l'application impose donc d'ajouter sa ligne ici.
|
||
--
|
||
-- « faq » (guide d'utilisation) est ouverte aux trois rôles métier : c'est un
|
||
-- secours, pas un droit métier. Son bouton est ancré à droite de la barre, d'où
|
||
-- un ordre d'affichage purement indicatif.
|
||
--
|
||
-- La DSI n'a qu'une page, la sienne : ni saisie, ni référentiel métier.
|
||
INSERT INTO `acces_role_page` (`role`, `page`, `ordre_affichage`) VALUES
|
||
('AGENT', 'agents', 10),
|
||
('AGENT', 'structures', 20),
|
||
('AGENT', 'faq', 200),
|
||
|
||
('RESPONSABLE_STRUCTURE', 'planning', 10),
|
||
('RESPONSABLE_STRUCTURE', 'structures', 20),
|
||
('RESPONSABLE_STRUCTURE', 'agents', 30),
|
||
('RESPONSABLE_STRUCTURE', 'coverage', 40),
|
||
('RESPONSABLE_STRUCTURE', 'faq', 200),
|
||
|
||
('SERVICE_ENFANCE', 'dashboard', 5),
|
||
('SERVICE_ENFANCE', 'planning', 10),
|
||
('SERVICE_ENFANCE', 'pending', 20),
|
||
('SERVICE_ENFANCE', 'coverage', 30),
|
||
('SERVICE_ENFANCE', 'structures', 40),
|
||
('SERVICE_ENFANCE', 'agents', 50),
|
||
('SERVICE_ENFANCE', 'annual', 60),
|
||
('SERVICE_ENFANCE', 'pta', 70),
|
||
('SERVICE_ENFANCE', 'teams', 80),
|
||
('SERVICE_ENFANCE', 'vacations', 90),
|
||
-- 95 et 96 : les deux onglets du groupe « Management » (V7.14) se rangent
|
||
-- juste avant « Administration », dont ils reprennent l'essentiel.
|
||
('SERVICE_ENFANCE', 'manage_agents', 95),
|
||
('SERVICE_ENFANCE', 'manage_structures', 96),
|
||
('SERVICE_ENFANCE', 'administration', 100),
|
||
('SERVICE_ENFANCE', 'faq', 200),
|
||
|
||
('DSI', 'dsi', 10)
|
||
ON DUPLICATE KEY UPDATE `ordre_affichage` = VALUES(`ordre_affichage`);
|
||
|
||
-- Filet : une page retirée de l'application resterait sinon dans la table et
|
||
-- finirait dans le groupe de repêchage « Autres » de la barre de navigation.
|
||
-- « management » était une page unique d'une version intermédiaire de la V7.14,
|
||
-- remplacée par les deux onglets ci-dessus.
|
||
DELETE FROM `acces_role_page` WHERE `page` = 'management';
|
||
|
||
-- ----------------------------------------------------------------------------
|
||
-- 13.2 PERMISSIONS FONCTIONNELLES PAR RÔLE
|
||
-- ----------------------------------------------------------------------------
|
||
-- Les refus sont écrits explicitement plutôt que laissés absents : une ligne à
|
||
-- FALSE documente une décision, une ligne absente laisse croire à un oubli.
|
||
--
|
||
-- Deux permissions à ne pas confondre :
|
||
-- * can_manage_access : TOUS les comptes, privilégiés compris — DSI seule ;
|
||
-- * can_manage_field_access : les jetons DE TERRAIN, agents et responsables de
|
||
-- lieu uniquement — Service Enfance (V7.27).
|
||
-- Un compte SERVICE_ENFANCE créé par le Service Enfance serait une
|
||
-- auto-promotion, un compte DSI lui ouvrirait le journal de ses propres
|
||
-- actions : la borne est doublée en PHP (AccessAccountController::ROLES_DELEGUES)
|
||
-- et ne doit être élargie d'aucun côté.
|
||
INSERT INTO `acces_role_permission` (`role`, `permission`, `autorise`) VALUES
|
||
('AGENT', 'read_only', TRUE),
|
||
('AGENT', 'can_edit_draft', FALSE),
|
||
('AGENT', 'can_edit_cross_structure', FALSE),
|
||
('AGENT', 'can_validate', FALSE),
|
||
('AGENT', 'can_reopen', FALSE),
|
||
('AGENT', 'can_use_templates', FALSE),
|
||
('AGENT', 'can_copy_full_week', FALSE),
|
||
('AGENT', 'can_view_pending', FALSE),
|
||
('AGENT', 'can_manage_coverage', FALSE),
|
||
('AGENT', 'can_manage_agents', FALSE),
|
||
('AGENT', 'can_manage_structures', FALSE),
|
||
('AGENT', 'can_manage_vacations', FALSE),
|
||
('AGENT', 'can_manage_teams', FALSE),
|
||
('AGENT', 'can_manage_pta', FALSE),
|
||
('AGENT', 'can_forecast_next_year', FALSE),
|
||
('AGENT', 'can_manage_access', FALSE),
|
||
('AGENT', 'can_manage_field_access', FALSE),
|
||
('AGENT', 'can_read_journal', FALSE),
|
||
|
||
('RESPONSABLE_STRUCTURE', 'read_only', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_edit_draft', TRUE),
|
||
('RESPONSABLE_STRUCTURE', 'can_edit_cross_structure', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_validate', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_reopen', TRUE),
|
||
('RESPONSABLE_STRUCTURE', 'can_use_templates', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_copy_full_week', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_view_pending', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_manage_coverage', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_manage_agents', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_manage_structures', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_manage_vacations', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_manage_teams', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_manage_pta', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_forecast_next_year', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_manage_access', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_manage_field_access', FALSE),
|
||
('RESPONSABLE_STRUCTURE', 'can_read_journal', FALSE),
|
||
|
||
('SERVICE_ENFANCE', 'read_only', FALSE),
|
||
('SERVICE_ENFANCE', 'can_edit_draft', TRUE),
|
||
('SERVICE_ENFANCE', 'can_edit_cross_structure', TRUE),
|
||
('SERVICE_ENFANCE', 'can_validate', TRUE),
|
||
('SERVICE_ENFANCE', 'can_reopen', TRUE),
|
||
('SERVICE_ENFANCE', 'can_use_templates', TRUE),
|
||
('SERVICE_ENFANCE', 'can_copy_full_week', TRUE),
|
||
('SERVICE_ENFANCE', 'can_view_pending', TRUE),
|
||
('SERVICE_ENFANCE', 'can_manage_coverage', TRUE),
|
||
('SERVICE_ENFANCE', 'can_manage_agents', TRUE),
|
||
('SERVICE_ENFANCE', 'can_manage_structures', TRUE),
|
||
('SERVICE_ENFANCE', 'can_manage_vacations', TRUE),
|
||
('SERVICE_ENFANCE', 'can_manage_teams', TRUE),
|
||
('SERVICE_ENFANCE', 'can_manage_pta', TRUE),
|
||
('SERVICE_ENFANCE', 'can_forecast_next_year', TRUE),
|
||
('SERVICE_ENFANCE', 'can_manage_access', FALSE),
|
||
('SERVICE_ENFANCE', 'can_manage_field_access', TRUE),
|
||
('SERVICE_ENFANCE', 'can_read_journal', FALSE),
|
||
|
||
('DSI', 'read_only', FALSE),
|
||
('DSI', 'can_edit_draft', FALSE),
|
||
('DSI', 'can_edit_cross_structure', FALSE),
|
||
('DSI', 'can_validate', FALSE),
|
||
('DSI', 'can_reopen', FALSE),
|
||
('DSI', 'can_use_templates', FALSE),
|
||
('DSI', 'can_copy_full_week', FALSE),
|
||
('DSI', 'can_view_pending', FALSE),
|
||
('DSI', 'can_manage_coverage', FALSE),
|
||
('DSI', 'can_manage_agents', FALSE),
|
||
('DSI', 'can_manage_structures', FALSE),
|
||
('DSI', 'can_manage_vacations', FALSE),
|
||
('DSI', 'can_manage_teams', FALSE),
|
||
('DSI', 'can_manage_pta', FALSE),
|
||
('DSI', 'can_forecast_next_year', FALSE),
|
||
('DSI', 'can_manage_access', TRUE),
|
||
-- Inutile à la DSI : can_manage_access lui ouvre déjà tous les comptes.
|
||
('DSI', 'can_manage_field_access', FALSE),
|
||
('DSI', 'can_read_journal', TRUE)
|
||
ON DUPLICATE KEY UPDATE `autorise` = VALUES(`autorise`);
|
||
|
||
-- ============================================================================
|
||
-- 14. COMPTES D'ACCÈS ET JOURNAL D'ACTIVITÉ
|
||
-- ============================================================================
|
||
-- Le jeton complet n'est JAMAIS stocké : la base ne garde qu'une empreinte
|
||
-- SHA-256 et les huit premiers caractères, qui servent uniquement à reconnaître
|
||
-- une ligne dans la liste. Un jeton perdu ne se retrouve pas, il se régénère.
|
||
-- Une fuite de ce dump ne donne donc aucun accès.
|
||
--
|
||
-- L'empreinte n'est pas salée, et c'est délibéré : le jeton est tiré au sort sur
|
||
-- 120 bits par le serveur, il n'est pas devinable par force brute comme le
|
||
-- serait un mot de passe choisi par un humain. L'absence de sel est ce qui
|
||
-- permet de retrouver le compte en une lecture d'index à la connexion.
|
||
--
|
||
-- Le MOT DE PASSE (V7.12), lui, est haché par password_hash() (bcrypt) : choisi
|
||
-- par un humain, donc attaquable par dictionnaire, et c'est précisément ce que
|
||
-- le hachage lent rend coûteux. Il n'est exigé que des comptes SERVICE_ENFANCE
|
||
-- et DSI — ceux qui valident et ceux qui distribuent les accès. NULL signifie
|
||
-- « pas encore défini » : c'est l'état d'un compte neuf comme d'un compte
|
||
-- réinitialisé par la DSI, et un compte privilégié dans cet état ne peut pas
|
||
-- ouvrir de session.
|
||
--
|
||
-- id_agent / id_structure sont UNIQUE tout en étant NULLables : MariaDB tolère
|
||
-- autant de NULL que voulu dans un index unique. Un agent ne peut donc avoir
|
||
-- qu'un seul compte, un lieu qu'un seul compte responsable, tandis que les
|
||
-- comptes SERVICE_ENFANCE et DSI (les deux colonnes à NULL) sont libres en
|
||
-- nombre.
|
||
-- ----------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `compte_acces` (
|
||
`id_compte` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`role` ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL,
|
||
`libelle` VARCHAR(180) NOT NULL COMMENT 'Titulaire du jeton, tel qu''affiché dans la liste',
|
||
`id_agent` INT UNSIGNED NULL COMMENT 'Obligatoire pour un compte AGENT',
|
||
`id_structure` INT UNSIGNED NULL COMMENT 'Obligatoire pour un compte RESPONSABLE_STRUCTURE',
|
||
`jeton_empreinte` CHAR(64) NOT NULL COMMENT 'SHA-256 hexadécimal du jeton',
|
||
`jeton_prefixe` VARCHAR(12) NOT NULL COMMENT 'Début du jeton, pour le reconnaître sans le révéler',
|
||
`mot_de_passe_hash` VARCHAR(255) NULL
|
||
COMMENT 'password_hash() du mot de passe ; NULL = à définir à la prochaine connexion',
|
||
`date_mot_de_passe` DATETIME NULL
|
||
COMMENT 'Dernière définition ou modification du mot de passe par le titulaire',
|
||
`actif` BOOLEAN NOT NULL DEFAULT TRUE,
|
||
`commentaire` VARCHAR(255) NULL,
|
||
`date_creation` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`date_rotation` DATETIME NULL COMMENT 'Dernière régénération du jeton',
|
||
`date_derniere_connexion` DATETIME NULL,
|
||
`nombre_connexions` INT UNSIGNED NOT NULL DEFAULT 0,
|
||
|
||
CONSTRAINT `uk_compte_empreinte` UNIQUE (`jeton_empreinte`),
|
||
CONSTRAINT `uk_compte_agent` UNIQUE (`id_agent`),
|
||
CONSTRAINT `uk_compte_structure` UNIQUE (`id_structure`),
|
||
INDEX `idx_compte_role_actif` (`role`, `actif`),
|
||
|
||
CONSTRAINT `fk_compte_agent` FOREIGN KEY (`id_agent`)
|
||
REFERENCES `agent` (`id_agent`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_compte_structure` FOREIGN KEY (`id_structure`)
|
||
REFERENCES `structure` (`id_structure`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- Rattrapage d'une base créée avant la V7.12 : sans effet sur une base neuve,
|
||
-- où le CREATE TABLE ci-dessus porte déjà les deux colonnes.
|
||
--
|
||
-- La forme évidente serait `ALTER TABLE … ADD COLUMN IF NOT EXISTS …`, mais
|
||
-- `IF NOT EXISTS` sur un ADD COLUMN est une EXTENSION MARIADB : MySQL la refuse
|
||
-- avec une erreur 1064 de syntaxe. Le test d'existence passe donc par
|
||
-- information_schema et une requête préparée, comprise par les deux SGBD.
|
||
-- `DO 0` est le non-opérant : contrairement à `SELECT 1`, il ne produit pas de
|
||
-- résultat parasite dans phpMyAdmin.
|
||
SET @ajout_mot_de_passe := IF(
|
||
(SELECT COUNT(*)
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'compte_acces'
|
||
AND COLUMN_NAME = 'mot_de_passe_hash') > 0,
|
||
'DO 0',
|
||
'ALTER TABLE `compte_acces`
|
||
ADD COLUMN `mot_de_passe_hash` VARCHAR(255) NULL
|
||
COMMENT ''password_hash() du mot de passe ; NULL = à définir à la prochaine connexion'',
|
||
ADD COLUMN `date_mot_de_passe` DATETIME NULL
|
||
COMMENT ''Dernière définition ou modification du mot de passe par le titulaire'''
|
||
);
|
||
PREPARE requete_ajout_mot_de_passe FROM @ajout_mot_de_passe;
|
||
EXECUTE requete_ajout_mot_de_passe;
|
||
DEALLOCATE PREPARE requete_ajout_mot_de_passe;
|
||
SET @ajout_mot_de_passe := NULL;
|
||
|
||
-- ----------------------------------------------------------------------------
|
||
-- 14.1 COMPTE DSI D'INSTALLATION
|
||
-- ----------------------------------------------------------------------------
|
||
-- Problème de l'œuf et de la poule : les jetons ne sont distribuables que par la
|
||
-- DSI, qui a elle-même besoin d'un jeton pour entrer. Cette ligne est la porte
|
||
-- d'entrée initiale.
|
||
--
|
||
-- JETON D'INSTALLATION : PTA-INSTALLATION-DSI
|
||
--
|
||
-- Il est PUBLIC — il est écrit ici, dans un fichier versionné. La page DSI
|
||
-- affiche un avertissement rouge tant que ce compte porte encore cette
|
||
-- empreinte : LE RÉGÉNÉRER EST LA PREMIÈRE ACTION À FAIRE APRÈS L'INSTALLATION.
|
||
--
|
||
-- Son mot de passe est laissé à NULL : la DSI en choisit un à sa première
|
||
-- connexion. La DSI peut effacer un mot de passe, jamais en poser un — sans quoi
|
||
-- elle connaîtrait celui de tout le monde.
|
||
--
|
||
-- L'identifiant 1 est forcé pour qu'un rejeu de ce script ne recrée pas un
|
||
-- second compte d'installation après régénération du jeton (l'empreinte, elle,
|
||
-- aura changé). Supprimer la ligne 1 puis rejouer est d'ailleurs la procédure de
|
||
-- secours documentée en cas de perte du jeton DSI.
|
||
INSERT INTO `compte_acces`
|
||
(`id_compte`, `role`, `libelle`, `jeton_empreinte`, `jeton_prefixe`, `commentaire`)
|
||
VALUES
|
||
(1, 'DSI', 'Compte DSI d''installation',
|
||
'7f61ffc8efed4ed46d581a98286115ad03bb0122ffc70db3398584348aca431d',
|
||
'PTA-INSTALL',
|
||
'Jeton d''installation public : à régénérer immédiatement.')
|
||
ON DUPLICATE KEY UPDATE `id_compte` = `id_compte`;
|
||
|
||
-- ----------------------------------------------------------------------------
|
||
-- 14.2 JOURNAL D'ACTIVITÉ
|
||
-- ----------------------------------------------------------------------------
|
||
-- Alimenté exclusivement par App\Core\Journal, jamais par des déclencheurs SQL :
|
||
-- le journal doit nommer l'UTILISATEUR à l'origine de l'action, information que
|
||
-- la base n'a pas (la connexion MariaDB est la même pour tout le monde).
|
||
--
|
||
-- Sont tracés : les connexions et déconnexions, toutes les écritures en base et
|
||
-- tous les refus d'accès. Les lectures ne le sont pas — c'est un choix de
|
||
-- volume : elles représenteraient l'essentiel des lignes pour une valeur
|
||
-- d'audit très faible.
|
||
--
|
||
-- L'acteur est FIGÉ au moment de l'action (rôle, libellé) : renommer un agent ou
|
||
-- supprimer son compte ne doit pas réécrire l'histoire. C'est aussi pourquoi la
|
||
-- clé étrangère est en ON DELETE SET NULL et non en CASCADE.
|
||
--
|
||
-- Cette table sert également de compteur au frein anti-force-brute de la
|
||
-- connexion (JournalModel::echecsRecents()) : les actions JETON_REFUSE et
|
||
-- MOT_DE_PASSE_REFUSE sont comptées par adresse IP. Les renommer désarmerait ce
|
||
-- frein silencieusement.
|
||
CREATE TABLE IF NOT EXISTS `journal_activite` (
|
||
`id_journal` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
`date_evenement` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`categorie` ENUM('CONNEXION', 'ECRITURE', 'ACCES_REFUSE', 'ADMINISTRATION', 'SYSTEME') NOT NULL,
|
||
`action` VARCHAR(60) NOT NULL COMMENT 'Code stable de l''action, ex. PLANNING_VALIDE',
|
||
`resultat` ENUM('SUCCES', 'REFUS', 'ERREUR') NOT NULL DEFAULT 'SUCCES',
|
||
`id_compte` INT UNSIGNED NULL,
|
||
`role` VARCHAR(30) NULL COMMENT 'Rôle au moment de l''action, figé',
|
||
`acteur` VARCHAR(180) NULL COMMENT 'Libellé de l''utilisateur au moment de l''action, figé',
|
||
`cible_table` VARCHAR(64) NULL COMMENT 'Table principale touchée par l''écriture',
|
||
`cible_id` INT UNSIGNED NULL,
|
||
`description` VARCHAR(500) NULL,
|
||
`contexte` JSON NULL COMMENT 'Détail structuré, ex. semaine et lieu concernés',
|
||
`adresse_ip` VARCHAR(45) NULL,
|
||
`agent_utilisateur` VARCHAR(255) NULL,
|
||
|
||
INDEX `idx_journal_date` (`date_evenement`),
|
||
INDEX `idx_journal_categorie` (`categorie`, `date_evenement`),
|
||
INDEX `idx_journal_compte` (`id_compte`, `date_evenement`),
|
||
INDEX `idx_journal_cible` (`cible_table`, `cible_id`),
|
||
INDEX `idx_journal_ip` (`adresse_ip`, `date_evenement`),
|
||
|
||
CONSTRAINT `fk_journal_compte` FOREIGN KEY (`id_compte`)
|
||
REFERENCES `compte_acces` (`id_compte`) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
-- ============================================================================
|
||
-- 15. VUES DE PILOTAGE MÉTIER
|
||
-- ============================================================================
|
||
|
||
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`;
|
||
|
||
-- ============================================================================
|
||
-- 16. VUES DE CONTRÔLE D'ACCÈS
|
||
-- ============================================================================
|
||
-- Les dix vues ci-dessous sont vérifiées au démarrage par SchemaModel et par
|
||
-- docker/db/apply-migrations.sh : si l'une manque, l'application REFUSE de
|
||
-- démarrer plutôt que de tourner sans contrôle de périmètre ni journal. Ce
|
||
-- fail-closed est volontaire, ne pas l'assouplir.
|
||
-- ----------------------------------------------------------------------------
|
||
|
||
-- 16.1 Matrice des droits ----------------------------------------------------
|
||
CREATE OR REPLACE VIEW `v_acces_role_page` AS
|
||
SELECT `role`, `page`, `ordre_affichage`
|
||
FROM `acces_role_page`;
|
||
|
||
CREATE OR REPLACE VIEW `v_acces_role_permission` AS
|
||
SELECT `role`, `permission`, `autorise`
|
||
FROM `acces_role_permission`;
|
||
|
||
-- 16.2 Identités -------------------------------------------------------------
|
||
-- Identité complète d'un agent actif : sert à construire le bandeau de profil.
|
||
-- Un agent inactif n'apparaît jamais ici, sa session se referme donc d'elle-même
|
||
-- à la requête suivante (Access relit le compte à CHAQUE requête).
|
||
CREATE OR REPLACE VIEW `v_acces_identite_agent` AS
|
||
SELECT
|
||
`a`.`id_agent`,
|
||
`a`.`matricule`,
|
||
`a`.`nom`,
|
||
`a`.`prenom`,
|
||
`a`.`id_structure`,
|
||
`s`.`nom` AS `structure_nom`,
|
||
`s`.`code` AS `structure_code`
|
||
FROM `agent` `a`
|
||
LEFT JOIN `structure` `s`
|
||
ON `s`.`id_structure` = `a`.`id_structure`
|
||
AND `s`.`actif` = TRUE
|
||
WHERE `a`.`actif` = TRUE;
|
||
|
||
-- Lieux d'affectation portés par un compte responsable de structure.
|
||
CREATE OR REPLACE VIEW `v_acces_identite_structure` AS
|
||
SELECT
|
||
`s`.`id_structure`,
|
||
`s`.`code`,
|
||
`s`.`nom`,
|
||
`s`.`type_affectation`
|
||
FROM `structure` `s`
|
||
WHERE `s`.`actif` = TRUE;
|
||
|
||
-- 16.3 Périmètres ------------------------------------------------------------
|
||
-- Quels agents un lieu d'affectation a-t-il le droit de consulter ?
|
||
-- Deux origines : le rattachement principal, ou une planification effective sur
|
||
-- ce lieu — un agent peut être planifié ailleurs que sur son rattachement.
|
||
CREATE OR REPLACE VIEW `v_acces_agent_par_structure` AS
|
||
SELECT DISTINCT
|
||
`a`.`id_agent`,
|
||
`a`.`id_structure` AS `id_structure_autorisee`,
|
||
'RATTACHEMENT' AS `origine`
|
||
FROM `agent` `a`
|
||
WHERE `a`.`actif` = TRUE
|
||
AND `a`.`id_structure` IS NOT NULL
|
||
UNION
|
||
SELECT DISTINCT
|
||
`a`.`id_agent`,
|
||
`p`.`id_structure` AS `id_structure_autorisee`,
|
||
'PLANIFICATION' AS `origine`
|
||
FROM `agent` `a`
|
||
INNER JOIN `planning` `p` ON `p`.`id_agent` = `a`.`id_agent`
|
||
WHERE `a`.`actif` = TRUE;
|
||
|
||
-- Périmètre d'un créneau : à quel lieu et à quel planning appartient-il ?
|
||
-- Utilisé avant toute modification ou suppression d'affectation.
|
||
CREATE OR REPLACE VIEW `v_acces_perimetre_creneau` AS
|
||
SELECT
|
||
`c`.`id_creneau`,
|
||
`c`.`id_planning`,
|
||
`p`.`id_structure`,
|
||
`p`.`id_agent`,
|
||
`p`.`statut` AS `statut_planning`,
|
||
`s`.`nom` AS `structure_nom`
|
||
FROM `creneau_horaire` `c`
|
||
INNER JOIN `planning` `p` ON `p`.`id_planning` = `c`.`id_planning`
|
||
INNER JOIN `structure` `s` ON `s`.`id_structure` = `p`.`id_structure`;
|
||
|
||
-- Périmètre d'un planning : utilisé avant validation ou réouverture.
|
||
CREATE OR REPLACE VIEW `v_acces_perimetre_planning` AS
|
||
SELECT
|
||
`p`.`id_planning`,
|
||
`p`.`id_structure`,
|
||
`p`.`id_agent`,
|
||
`p`.`statut` AS `statut_planning`,
|
||
`s`.`nom` AS `structure_nom`,
|
||
`a`.`nom` AS `agent_nom`,
|
||
`a`.`prenom` AS `agent_prenom`
|
||
FROM `planning` `p`
|
||
INNER JOIN `structure` `s` ON `s`.`id_structure` = `p`.`id_structure`
|
||
INNER JOIN `agent` `a` ON `a`.`id_agent` = `p`.`id_agent`;
|
||
|
||
-- 16.4 Comptes et journal ----------------------------------------------------
|
||
-- Le PHP interroge cette vue et non la table : l'identité réelle derrière un
|
||
-- compte, son activité et la règle « qui doit avoir un mot de passe » sont
|
||
-- résolues en SQL, une seule fois, au même endroit.
|
||
CREATE OR REPLACE VIEW `v_acces_compte` AS
|
||
SELECT
|
||
`c`.`id_compte`,
|
||
`c`.`role`,
|
||
`c`.`libelle`,
|
||
`c`.`id_agent`,
|
||
`c`.`id_structure`,
|
||
`c`.`jeton_empreinte`,
|
||
`c`.`jeton_prefixe`,
|
||
`c`.`actif`,
|
||
`c`.`commentaire`,
|
||
`c`.`date_creation`,
|
||
`c`.`date_rotation`,
|
||
`c`.`date_derniere_connexion`,
|
||
`c`.`nombre_connexions`,
|
||
`c`.`mot_de_passe_hash`,
|
||
`c`.`date_mot_de_passe`,
|
||
-- Les deux rôles qui engagent l'application : celui qui valide les plannings
|
||
-- de toute la collectivité, et celui qui distribue les accès.
|
||
CASE WHEN `c`.`role` IN ('SERVICE_ENFANCE', 'DSI') THEN 1 ELSE 0 END AS `mot_de_passe_requis`,
|
||
CASE WHEN `c`.`mot_de_passe_hash` IS NULL THEN 0 ELSE 1 END AS `mot_de_passe_defini`,
|
||
`a`.`matricule`,
|
||
`a`.`nom` AS `agent_nom`,
|
||
`a`.`prenom` AS `agent_prenom`,
|
||
`s`.`code` AS `structure_code`,
|
||
`s`.`nom` AS `structure_nom`,
|
||
-- Libellé affiché : l'identité VIVE pour un agent ou un lieu (un agent
|
||
-- renommé doit apparaître sous son nouveau nom), le libellé saisi pour les
|
||
-- comptes Service Enfance et DSI, qui ne pointent vers rien.
|
||
CASE `c`.`role`
|
||
WHEN 'AGENT' THEN CONCAT(`a`.`prenom`, ' ', `a`.`nom`)
|
||
WHEN 'RESPONSABLE_STRUCTURE' THEN `s`.`nom`
|
||
ELSE `c`.`libelle`
|
||
END AS `identite_libelle`,
|
||
-- Un compte reste utilisable tant que l'identité qu'il porte est active :
|
||
-- désactiver un agent coupe son accès sans avoir à toucher à son compte.
|
||
CASE `c`.`role`
|
||
WHEN 'AGENT' THEN COALESCE(`a`.`actif`, 0)
|
||
WHEN 'RESPONSABLE_STRUCTURE' THEN COALESCE(`s`.`actif`, 0)
|
||
ELSE 1
|
||
END AS `identite_active`
|
||
FROM `compte_acces` `c`
|
||
LEFT JOIN `agent` `a` ON `a`.`id_agent` = `c`.`id_agent`
|
||
LEFT JOIN `structure` `s` ON `s`.`id_structure` = `c`.`id_structure`;
|
||
|
||
-- Identités actives encore dépourvues de compte : alimente le bouton
|
||
-- « générer les jetons manquants », qui permet d'équiper d'un coup tous les
|
||
-- agents et tous les lieux lors de la mise en service.
|
||
CREATE OR REPLACE VIEW `v_acces_compte_manquant` AS
|
||
SELECT
|
||
'AGENT' AS `role`,
|
||
`a`.`id_agent` AS `id_agent`,
|
||
CAST(NULL AS UNSIGNED) AS `id_structure`,
|
||
CONCAT(`a`.`prenom`, ' ', `a`.`nom`) AS `libelle`
|
||
FROM `agent` `a`
|
||
LEFT JOIN `compte_acces` `c` ON `c`.`id_agent` = `a`.`id_agent`
|
||
WHERE `a`.`actif` = 1 AND `c`.`id_compte` IS NULL
|
||
UNION ALL
|
||
SELECT
|
||
'RESPONSABLE_STRUCTURE',
|
||
CAST(NULL AS UNSIGNED),
|
||
`s`.`id_structure`,
|
||
`s`.`nom`
|
||
FROM `structure` `s`
|
||
LEFT JOIN `compte_acces` `c` ON `c`.`id_structure` = `s`.`id_structure`
|
||
WHERE `s`.`actif` = 1 AND `c`.`id_compte` IS NULL;
|
||
|
||
-- Journal enrichi du compte encore existant, quand il existe. Le libellé figé
|
||
-- (`acteur`) reste la source d'affichage : la jointure ne sert qu'à retrouver le
|
||
-- préfixe du jeton et à filtrer par compte.
|
||
CREATE OR REPLACE VIEW `v_journal_activite` AS
|
||
SELECT
|
||
`j`.`id_journal`,
|
||
`j`.`date_evenement`,
|
||
`j`.`categorie`,
|
||
`j`.`action`,
|
||
`j`.`resultat`,
|
||
`j`.`id_compte`,
|
||
`j`.`role`,
|
||
`j`.`acteur`,
|
||
`j`.`cible_table`,
|
||
`j`.`cible_id`,
|
||
`j`.`description`,
|
||
`j`.`contexte`,
|
||
`j`.`adresse_ip`,
|
||
`j`.`agent_utilisateur`,
|
||
`c`.`jeton_prefixe`,
|
||
`c`.`actif` AS `compte_actif`
|
||
FROM `journal_activite` `j`
|
||
LEFT JOIN `compte_acces` `c` ON `c`.`id_compte` = `j`.`id_compte`;
|
||
|
||
-- ============================================================================
|
||
-- 17. PROCÉDURES DE CONTRÔLE / VALIDATION (PATRIMOINE SQL, NON APPELÉ PAR LE PHP)
|
||
-- ============================================================================
|
||
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 ;
|
||
|
||
-- ============================================================================
|
||
-- 18. REGISTRE DES MIGRATIONS
|
||
-- ============================================================================
|
||
-- Table tenue par docker/db/apply-migrations.sh : un fichier de migration déjà
|
||
-- appliqué et inchangé est ignoré, un fichier modifié est rejoué. Elle est
|
||
-- créée ici pour qu'une base installée à la main soit strictement identique à
|
||
-- une base montée par la pile Docker.
|
||
--
|
||
-- Elle est laissée VIDE volontairement : les empreintes MD5 des fichiers de
|
||
-- migration ne peuvent pas être calculées en SQL, et les inscrire en dur ici
|
||
-- se périmerait à la première retouche d'une migration. db-migrate les rejouera
|
||
-- donc une fois, sans effet — elles sont idempotentes.
|
||
CREATE TABLE IF NOT EXISTS `schema_migration` (
|
||
`fichier` VARCHAR(190) NOT NULL,
|
||
`empreinte` CHAR(32) NOT NULL,
|
||
`applique_le` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
PRIMARY KEY (`fichier`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================================
|
||
-- FIN DE L'INSTALLATION — PTA V7.30
|
||
-- ============================================================================
|
||
-- Contrôles recommandés après import :
|
||
--
|
||
-- -- 21 tables métier + 2 tables de droits + compte_acces + journal_activite
|
||
-- SELECT COUNT(*) FROM information_schema.TABLES
|
||
-- WHERE TABLE_SCHEMA = DATABASE();
|
||
--
|
||
-- -- Les dix vues exigées au démarrage doivent toutes répondre présentes.
|
||
-- SELECT TABLE_NAME FROM information_schema.VIEWS
|
||
-- WHERE TABLE_SCHEMA = DATABASE()
|
||
-- AND TABLE_NAME IN (
|
||
-- 'v_acces_role_page', 'v_acces_role_permission',
|
||
-- 'v_acces_identite_agent', 'v_acces_identite_structure',
|
||
-- 'v_acces_agent_par_structure', 'v_acces_perimetre_creneau',
|
||
-- 'v_acces_perimetre_planning', 'v_acces_compte',
|
||
-- 'v_acces_compte_manquant', 'v_journal_activite')
|
||
-- ORDER BY TABLE_NAME;
|
||
--
|
||
-- -- Droits en place : 4 rôles côté pages, 4 rôles côté permissions.
|
||
-- SELECT role, COUNT(*) FROM acces_role_page GROUP BY role;
|
||
-- SELECT role, COUNT(*) FROM acces_role_permission GROUP BY role;
|
||
--
|
||
-- -- Référentiels techniques : 4 postes, 16 motifs.
|
||
-- SELECT COUNT(*) FROM poste_agent;
|
||
-- SELECT COUNT(*) FROM motif_planning;
|
||
--
|
||
-- -- La correction #1832 : id_motif doit être int unsigned NOT NULL.
|
||
-- SHOW CREATE TABLE creneau_horaire;
|
||
--
|
||
-- -- La porte d'entrée initiale, à régénérer AVANT toute mise en service.
|
||
-- SELECT id_compte, role, libelle, jeton_prefixe FROM compte_acces;
|
||
-- ============================================================================
|