/* _________________________
	L3 -- TP6 :: triggers
    Modèle : gestion de projets
_________________________ */

/*  modèle : 
	- personne (employé),
 	- projet (heures prévues), 
	- tache (affectation d'un employé à un projet sur une semaine et pour un nombre d'heure donnés)
	- tab_log (depassements sur projet)
*/

DROP TABLE tab_log;
DROP TABLE tache;
DROP TABLE personne;
DROP TABLE projet;

CREATE TABLE personne
(
	refpers NUMERIC(3) CONSTRAINT pk_personne PRIMARY KEY,
	nom VARCHAR(20) NOT NULL,
	prenom VARCHAR(20) NOT NULL
);

CREATE TABLE projet
(
	refproj NUMERIC(3) CONSTRAINT pk_projet PRIMARY KEY,
	titre VARCHAR(20) NOT NULL,
	nb_heures_prevues NUMERIC DEFAULT 0 NOT NULL
);

CREATE TABLE tache
(
	refproj NUMERIC(3) CONSTRAINT fk_tache_projet REFERENCES projet(refproj),
	refpers NUMERIC(3) CONSTRAINT fk_tache_personne REFERENCES personne(refpers),
	semaine NUMERIC(2) NOT NULL, 
	nb_heures NUMERIC(2) DEFAULT 0 NOT NULL,
	CONSTRAINT pk_tache PRIMARY KEY(refproj, refpers, semaine),
	CONSTRAINT ck_tache_nb_heures CHECK (nb_heures >= 0),
	CONSTRAINT ck_tache_semaine CHECK (semaine BETWEEN 1 AND 52)
);

CREATE TABLE tab_log
(
	date_pb DATE,
	refproj NUMERIC(3) CONSTRAINT fk_tab_log_projet REFERENCES projet(refproj),
	message VARCHAR(1000),
	CONSTRAINT pk_tab_log PRIMARY KEY(date_pb, refproj)
);

/* _______________________________________	*/
/* question 1		 						*/
/* _______________________________________	*/

/* trigger */

-- après insertion ou mise à jour d'une tâche :
CREATE OR REPLACE TRIGGER tr_tache_ins_upd
AFTER INSERT OR UPDATE ON tache
FOR EACH ROW

DECLARE
	nb_h_prev NUMBER;
	nb_h_real NUMBER;

BEGIN
	-- 1) recupération des heures prévues pour le projet concerné par la tâche insérée ou mise à jour
	SELECT nb_heures_prevues 
	INTO nb_h_prev 
	FROM projet 
	WHERE refproj = :new.refproj;

	-- 2) recupération des heures réalisées à ce jour sur le projet (somme des heures des tâches liées au projet)
	SELECT sum(nb_heures) 
	INTO nb_h_real 
	FROM tache 
	WHERE refproj = :new.refproj;

	-- 3) en cas de dépassement 
	IF (nb_h_real > nb_h_prev) THEN
		INSERT INTO tab_log 
		VALUES (sysdate(), :new.refproj, 'depassement heures : prévu == ' ||  nb_h_prev || ' : réalisé == ' ||  nb_h_real);
	END IF ;
END ;
/

SHOW ERRORS

/* test */

-- remplissage des tables : on crée un projet et un employé
INSERT INTO projet(refproj, titre, nb_heures_prevues) VALUES (1, 'migration Oracle', 49);
INSERT INTO personne(refpers, nom, prenom) VALUES (1, 'Dupont', 'Pierre');

-- remplissage des tables : on crée des tâches pour le projet
ALTER TRIGGER tr_tache_ins_upd DISABLE;
INSERT INTO tache(refproj, refpers, semaine, nb_heures) VALUES (1, 1, 12, 10);
INSERT INTO tache(refproj, refpers, semaine, nb_heures) VALUES (1, 1, 13, 12);
INSERT INTO tache(refproj, refpers, semaine, nb_heures) VALUES (1, 1, 14, 7);
INSERT INTO tache(refproj, refpers, semaine, nb_heures) VALUES (1, 1, 15, 11);

-- remplissage des tables : dépassement du temps prévisionnel du projet par insertion d'une tâche, pour tester le trigger
ALTER TRIGGER tr_tache_ins_upd ENABLE;
INSERT INTO tache(refproj, refpers, semaine, nb_heures) VALUES (1, 1, 16, 10);

/* 
	ERREUR � la ligne 1 :
	ORA-04091: la table TOULOUSE.tache est en mutation ; le d�clencheur ou la fonction ne peut la voir
	ORA-06512: � "TOULOUSE.TR_tache_ins_upd", ligne 6
	ORA-04088: erreur lors d'ex�cution du d�clencheur 'TOULOUSE.TR_tache_ins_upd'

	-- L'INSTRUCTION SELECT sum(nb_heures) EST RESPONSABLE DE L'ERREUR
	-- RAISON :: un trigger ligne ne peut lire ni modifier la table concernée (table mutante) car celle-ci est potentiellement inconsistante 
*/

-- vérification de l'état des tables
SELECT * FROM projet;

SELECT refproj, sum(nb_heures) AS nb_heures_realisees
FROM tache 
GROUP BY refproj;


/* modification du schéma : 
	- ajout d'une colonne dans la remation 'projet'
	- suppression du trigger
*/

-- modification de la table projet
ALTER TABLE projet
	ADD
	(
		nb_heures_realisees NUMBER DEFAULT 0 NOT NULL
	);

-- suppression du trigger
DROP TRIGGER tr_tache_ins_upd;


/* _______________________________________	*/
/* question 2 		 						*/
/* _______________________________________	*/

/* 
  plusieurs projets => il faut un curseur sur 'projet' pour les parcourir
  - utilisation d'une boucle pour parcourir le curseur
  - on déplace le curseur de clef en clef (valeurs de idprojet) jusqu'à ce que le curseur soit vide
  - on fait le travail demandé pour chacun des projets
*/

/* mise à jour de la table 'projet' */

DECLARE
	-- variables
	nb_h_real NUMBER; 
	id_projet NUMBER;

	-- curseur sur 'projet' via la clé 'refproj'
	CURSOR c_projet IS SELECT refproj FROM projet;

BEGIN
	-- ouverture du curseur
	OPEN c_projet;

	-- boucle (tant que condition de sortie 'EXIT' n'est pas vérifiée)
	LOOP
		-- récupération de la ligne courante du curseur
		FETCH c_projet into id_projet;

		-- condition d'arrêt
		EXIT WHEN c_projet%NOTFOUND;

		SELECT sum(nb_heures)
		INTO nb_h_real
		FROM tache
		WHERE refproj = id_projet;

		UPDATE projet
		SET nb_heures_realisees = nb_h_real
		WHERE refproj = id_projet;
	END LOOP;

	-- fermeture du curseur
	CLOSE c_projet;
END;
/

SHOW ERRORS

/* test */
SELECT * FROM projet;

/* _______________________________________	*/
/* question 3		 						*/
/* _______________________________________	*/

/* directement par trigger */

CREATE OR REPLACE TRIGGER tr_tache_upd
AFTER UPDATE ON tache
FOR EACH ROW
BEGIN
	UPDATE projet
	SET nb_heures_realisees = (nb_heures_realisees - :old.nb_heures + :new.nb_heures)
	WHERE refproj= :new.refproj;
END;
/

SHOW ERRORS

/* test */

SELECT * FROM projet JOIN tache USING(refproj) WHERE refproj = 1;

UPDATE tache SET nb_heures = 15 WHERE refproj = 1 AND refpers = 1 AND semaine = 14;

SELECT * FROM projet JOIN tache USING(refproj) WHERE refproj = 1;

/* via une procédure */

DROP TRIGGER tr_tache_upd;

CREATE OR REPLACE PROCEDURE pr_tache_upd
(
	p_refproj IN NUMBER, -- PK
	p_refpers IN NUMBER, -- PK 
	p_semaine IN NUMBER, -- PK 
	nb_heures_new IN NUMBER -- valeur modifiée
) 

IS
	nb_heures_old NUMBER;

BEGIN
	-- récupération ancienne valeur dans tâche
	SELECT nb_heures INTO nb_heures_old
	FROM tache
	WHERE refproj= p_refproj AND refpers= p_refpers AND semaine = p_semaine;

	-- mise à jour de tache
	UPDATE tache
	SET nb_heures = nb_heures_new
	WHERE refproj= p_refproj AND refpers= p_refpers AND semaine = p_semaine;

	-- mise à jour de projet
	UPDATE projet
	SET nb_heures_realisees = nb_heures_realisees-nb_heures_old +nb_heures_new;
END;
/

SHOW ERRORS

/* test */

SELECT * FROM projet JOIN tache USING(refproj) WHERE refproj = 1;

EXECUTE pr_tache_upd(1, 1, 14, 22)
/
SHOW ERRORS

SELECT * FROM projet JOIN tache USING(refproj) WHERE refproj = 1;


/* _______________________________________	*/
/* question 4		 						*/
/* _______________________________________	*/

CREATE OR REPLACE TRIGGER tr_projet_upd_ins
AFTER INSERT OR UPDATE ON projet
FOR EACH ROW

DECLARE
	nb_h_prev NUMBER; 
	nb_h_real NUMBER; 

BEGIN
	IF (:new.nb_heures_realisees > :new.nb_heures_prevues) THEN
		INSERT INTO TAB_LOG VALUES (sysdate(), :new.refproj, 'depassement heures : prévu == ' ||  :new.nb_heures_prevues || ' : réalisé == ' ||  :new.nb_heures_realisees);
	END IF ;
END;
/

SHOW ERRORS

/* test */

SELECT * FROM projet WHERE refproj = 1;

UPDATE projet SET nb_heures_realisees = 50 WHERE refproj = 1;

SELECT * FROM projet JOIN TAB_LOG USING(refproj) WHERE refproj = 1;
