

-- A SAUVEAGRDE DE LA TABLE EMISSION
-- 1 -- crétion d'une table à partir d'une copie
CREATE TABLE emission_save LIKE emission ;
-- 2 -- retirer D'ABORD l'auto-incrémentation (si elle existe)
ALTER TABLE emission_save MODIFY id INT(11) UNSIGNED NOT NULL;
-- 3 -- puis effacer la clé primaire sur la colonne
ALTER TABLE emission_save DROP PRIMARY KEY;
-- 4 -- Supprimer les valeurs par defauts
ALTER TABLE emission_save ALTER COLUMN dateSysteme DROP DEFAULT;
-- 5 -- Sauvegarder des bonnes information
INSERT INTO emission_save (id,codeSociete,idClient,numeroClient,idPolice,numeroPolice,numeroEmission,codeTypeAvenant,codeTypeOperation, 
numeroAvenant,dateEffet,dateEffetPolice,dateEcheance,dateEmission,primeNette,commissionGestion,commission,
commissionAutre,primeNetteStat,accessoire,accessoireGarant,primeHt,primeNetteCie,accessoireCie,assesoireApporteur,
chiffreAffaire,taxe,fraisCarte,primeTtcInitial,tauxInteret,interet,primeTtc,nbEcheance,dateCre,`user`,encaisse,
regle,nbAliment,dateSysteme,quittanceNova,libelleEmission,accessoireIntermediaire,accessoireGestion,commissionIntermediaire)

SELECT id,codeSociete,idClient,numeroClient,idPolice,numeroPolice,numeroEmission,codeTypeAvenant,codeTypeOperation, 
numeroAvenant,dateEffet,dateEffetPolice,dateEcheance,dateEmission,primeNette,commissionGestion,commission,
commissionAutre,primeNetteStat,accessoire,accessoireGarant,primeHt,primeNetteCie,accessoireCie,assesoireApporteur,
chiffreAffaire,taxe,fraisCarte,primeTtcInitial,tauxInteret,interet,primeTtc,nbEcheance,dateCre,`user`,encaisse,
regle,nbAliment,dateSysteme,quittanceNova,libelleEmission,accessoireIntermediaire,accessoireGestion,commissionIntermediaire 
FROM emission WHERE codeTypeAvenant='QTM'
AND id NOT IN (SELECT id FROM emission_save)
AND idPolice IN (SELECT id FROM police WHERE codeGcAssureur='SIDAM');
						


-- B SAUVEAGRDE DE LA TABLE QUITTANCE
-- 1 -- crétion d'une table à partir d'une copie
CREATE TABLE quittance_save LIKE quittance ;
-- 2 -- retirer D'ABORD l'auto-incrémentation (si elle existe)
ALTER TABLE quittance_save MODIFY id INT(11) UNSIGNED NOT NULL;
-- 3 -- puis effacer la clé primaire sur la colonne
ALTER TABLE quittance_save DROP PRIMARY KEY;
-- 4 -- Supprimer les valeurs par defauts
ALTER TABLE quittance_save ALTER COLUMN dateSysteme DROP DEFAULT;
-- 5 -- Sauvegarder des bonnes information
INSERT INTO quittance_save (id,idEmission,numeroEmission,codeSociete,idClient,numeroClient,idPolice,numeroPolice,numeroQuittance, 
codeTypeAvenant,codeTypeOperation,numeroAvenant,dateEffet,dateEffetPolice,dateEcheance,dateEmission,
primeTtcInitialEmission,primeTtcEmission,ordreEcheance,libelleQuittance,tauxEcheance,datePaiement,
primeNette,commissionGestion,commission,commissionAutre,primeNetteStat,accessoire,accessoireGarant, 
primeHt,primeNetteCie,accessoireCie,assesoireApporteur,chiffreAffaire,taxe,fraisCarte,primeTtcInitial, 
tauxInteret,interet,primeTtc,dateCre,`user`,encaisse,regle,nbAliment,dateSysteme,deduireComm,quittanceNova, 
primeadditionnelle,accessoireIntermediaire,accessoireGestion,commissionIntermediaire)

SELECT id,idEmission,numeroEmission,codeSociete,idClient,numeroClient,idPolice,numeroPolice,numeroQuittance, 
codeTypeAvenant,codeTypeOperation,numeroAvenant,dateEffet,dateEffetPolice,dateEcheance,dateEmission,
primeTtcInitialEmission,primeTtcEmission,ordreEcheance,libelleQuittance,tauxEcheance,datePaiement,
primeNette,commissionGestion,commission,commissionAutre,primeNetteStat,accessoire,accessoireGarant, 
primeHt,primeNetteCie,accessoireCie,assesoireApporteur,chiffreAffaire,taxe,fraisCarte,primeTtcInitial, 
tauxInteret,interet,primeTtc,dateCre,`user`,encaisse,regle,nbAliment,dateSysteme,deduireComm,quittanceNova, 
primeadditionnelle,accessoireIntermediaire,accessoireGestion,commissionIntermediaire
FROM quittance WHERE idEmission IN ( SELECT id FROM emission  WHERE codeTypeAvenant='QTM')
AND id NOT IN (SELECT id FROM quittance_save)
AND idPolice IN (SELECT id FROM police WHERE codeGcAssureur='SIDAM');


-- C -- CREATION DES TRIGGERS
-- 1 INSERT QUITTANCE
DELIMITER $$
CREATE TRIGGER after_insert_quittance
AFTER INSERT  
ON quittance
FOR EACH ROW     
BEGIN
		INSERT INTO quittance_save (id,idEmission,numeroEmission,codeSociete,idClient,numeroClient,idPolice,numeroPolice,numeroQuittance, 
	codeTypeAvenant,codeTypeOperation,numeroAvenant,dateEffet,dateEffetPolice,dateEcheance,dateEmission,
	primeTtcInitialEmission,primeTtcEmission,ordreEcheance,libelleQuittance,tauxEcheance,datePaiement,
	primeNette,commissionGestion,commission,commissionAutre,primeNetteStat,accessoire,accessoireGarant, 
	primeHt,primeNetteCie,accessoireCie,assesoireApporteur,chiffreAffaire,taxe,fraisCarte,primeTtcInitial, 
	tauxInteret,interet,primeTtc,dateCre,`user`,encaisse,regle,nbAliment,dateSysteme,deduireComm,quittanceNova, 
	primeadditionnelle,accessoireIntermediaire,accessoireGestion,commissionIntermediaire)
	VALUES (NEW.id,NEW.idEmission,NEW.numeroEmission,NEW.codeSociete,NEW.idClient,NEW.numeroClient,NEW.idPolice,NEW.numeroPolice,NEW.numeroQuittance,
	NEW.codeTypeAvenant,NEW.codeTypeOperation,NEW.numeroAvenant,NEW.dateEffet,NEW.dateEffetPolice,NEW.dateEcheance,NEW.dateEmission,
	NEW.primeTtcInitialEmission,NEW.primeTtcEmission,NEW.ordreEcheance,NEW.libelleQuittance,NEW.tauxEcheance,NEW.datePaiement,
	NEW.primeNette,NEW.commissionGestion,NEW.commission,NEW.commissionAutre,NEW.primeNetteStat,NEW.accessoire,NEW.accessoireGarant,
	NEW.primeHt,NEW.primeNetteCie,NEW.accessoireCie,NEW.assesoireApporteur,NEW.chiffreAffaire,NEW.taxe,NEW.fraisCarte,NEW.primeTtcInitial,
	NEW.tauxInteret,NEW.interet,NEW.primeTtc,NEW.dateCre,NEW.`user`,NEW.encaisse,NEW.regle,NEW.nbAliment,NEW.dateSysteme,NEW.deduireComm,NEW.quittanceNova,
	NEW.primeadditionnelle,NEW.accessoireIntermediaire,NEW.accessoireGestion,NEW.commissionIntermediaire);
	
END;
$$
DELIMITER ;

-- 2 INSERT EMISSION
DELIMITER $$
CREATE TRIGGER after_insert_emission 
AFTER INSERT  
ON emission
FOR EACH ROW     
BEGIN
	INSERT INTO emission_save (id,codeSociete,idClient,numeroClient,idPolice,numeroPolice,numeroEmission,codeTypeAvenant,codeTypeOperation, 
	numeroAvenant,dateEffet,dateEffetPolice,dateEcheance,dateEmission,primeNette,commissionGestion,commission,
	commissionAutre,primeNetteStat,accessoire,accessoireGarant,primeHt,primeNetteCie,accessoireCie,assesoireApporteur,
	chiffreAffaire,taxe,fraisCarte,primeTtcInitial,tauxInteret,interet,primeTtc,nbEcheance,dateCre,`user`,encaisse,
	regle,nbAliment,dateSysteme,quittanceNova,libelleEmission ,accessoireIntermediaire,accessoireGestion,commissionIntermediaire)
	VALUES (NEW.id,NEW.codeSociete,NEW.idClient,NEW.numeroClient,NEW.idPolice,NEW.numeroPolice,NEW.numeroEmission,NEW.codeTypeAvenant,NEW.codeTypeOperation,
	NEW.numeroAvenant,NEW.dateEffet,NEW.dateEffetPolice,NEW.dateEcheance,NEW.dateEmission,NEW.primeNette,NEW.commissionGestion,NEW.commission,
	NEW.commissionAutre,NEW.primeNetteStat,NEW.accessoire,NEW.accessoireGarant,NEW.primeHt,NEW.primeNetteCie,NEW.accessoireCie,NEW.assesoireApporteur,
	NEW.chiffreAffaire,NEW.taxe,NEW.fraisCarte,NEW.primeTtcInitial,NEW.tauxInteret,NEW.interet,NEW.primeTtc,NEW.nbEcheance,NEW.dateCre,NEW.`user`,NEW.encaisse,
	NEW.regle,NEW.nbAliment,NEW.dateSysteme,NEW.quittanceNova,NEW.libelleEmission,NEW.accessoireIntermediaire,NEW.accessoireGestion,NEW.commissionIntermediaire);	
END;
$$
DELIMITER ;
/*
-- 3 UPDATE QUITTANCE
DELIMITER $$
CREATE TRIGGER after_update_quittance
AFTER UPDATE  
ON quittance
FOR EACH ROW     
BEGIN
	
	IF OLD.primeNette <> NEW.primeNette THEN
		UPDATE quittance_save
			SET 
			    dateEffet=NEW.dateEffet,dateEffetPolice=NEW.dateEffetPolice,dateEcheance=NEW.dateEcheance,dateEmission=NEW.dateEmission,
			    primeTtcInitialEmission=NEW.primeTtcInitialEmission,primeTtcEmission=NEW.primeTtcEmission,ordreEcheance=NEW.ordreEcheance,
			    libelleQuittance=NEW.libelleQuittance,tauxEcheance=NEW.tauxEcheance,datePaiement=NEW.datePaiement,
			    primeNette=NEW.primeNette,commissionGestion=NEW.commissionGestion,commission=NEW.commission,commissionAutre=NEW.commissionAutre,
			    primeNetteStat=NEW.primeNetteStat,accessoire=NEW.accessoire,accessoireGarant=NEW.accessoireGarant,primeHt=NEW.primeHt,
			    primeNetteCie=NEW.primeNetteCie,accessoireCie=NEW.accessoireCie,assesoireApporteur=NEW.assesoireApporteur,chiffreAffaire=NEW.chiffreAffaire,
			    taxe=NEW.taxe,fraisCarte=NEW.fraisCarte,primeTtcInitial=NEW.primeTtcInitial,tauxInteret=NEW.tauxInteret,interet=NEW.interet,
			    primeTtc=NEW.primeTtc,dateCre=NEW.dateCre,`user`=NEW.`user`,encaisse=NEW.encaisse,regle=NEW.regle,nbAliment=NEW.nbAliment,dateSysteme=NEW.dateSysteme,
			    deduireComm=NEW.deduireComm,quittanceNova=NEW.quittanceNova,primeadditionnelle=NEW.primeadditionnelle,accessoireIntermediaire=NEW.accessoireIntermediaire,
			    accessoireGestion=NEW.accessoireGestion,commissionIntermediaire=NEW.commissionIntermediaire
		WHERE id=OLD.id AND idEmission=OLD.idEmission;
	END IF;
END;
$$
DELIMITER ;

-- 4 UPDATE EMISSION
DELIMITER $$
CREATE TRIGGER after_update_emission
AFTER UPDATE  
ON emission
FOR EACH ROW     
BEGIN
	
	IF OLD.primeNette <> NEW.primeNette THEN
		UPDATE emission_save
			SET 
			   dateEffet=NEW.dateEffet,dateEffetPolice=NEW.dateEffetPolice,dateEcheance=NEW.dateEcheance,dateEmission=NEW.dateEmission,
			   primeNette=NEW.primeNette,commissionGestion=NEW.commissionGestion,commission=NEW.commission,commissionAutre=NEW.commissionAutre,
			   primeNetteStat=NEW.primeNetteStat,accessoire=NEW.accessoire,accessoireGarant=NEW.accessoireGarant,primeHt=NEW.primeHt,
			   primeNetteCie=NEW.primeNetteCie,accessoireCie=NEW.accessoireCie,assesoireApporteur=NEW.assesoireApporteur,
			   chiffreAffaire=NEW.chiffreAffaire,taxe=NEW.taxe,fraisCarte=NEW.fraisCarte,primeTtcInitial=NEW.primeTtcInitial,
			   tauxInteret=NEW.tauxInteret,interet=NEW.interet,primeTtc=NEW.primeTtc,nbEcheance=NEW.nbEcheance,dateCre=NEW.dateCre,`user`=NEW.`user`,
			   encaisse=NEW.encaisse,regle=NEW.regle,nbAliment=NEW.nbAliment,dateSysteme=NEW.dateSysteme,quittanceNova=NEW.quittanceNova,
			   libelleEmission=NEW.libelleEmission,accessoireIntermediaire=NEW.accessoireIntermediaire,
			   accessoireGestion=NEW.accessoireGestion,commissionIntermediaire=NEW.commissionIntermediaire
		WHERE id=OLD.id;
	END IF;
END;
$$
DELIMITER ;
*/
 
 
 SELECT * FROM quittance_save


-- 5 DELETE QUITTANCE
DELIMITER $$
CREATE TRIGGER after_delete_quittance
AFTER DELETE 
ON quittance
FOR EACH ROW     
BEGIN
		-- DELETE FROM quittance_save WHERE (id=OLD.id) AND (idEmission=OLD.idEmission);		
		UPDATE quittance_save SET deleteQuittance=1 WHERE (id=OLD.id) AND (idEmission=OLD.idEmission);
END;
$$
DELIMITER ;

-- 6 DELETE EMISSION
DELIMITER $$
CREATE TRIGGER after_delete_emission
AFTER DELETE 
ON emission
FOR EACH ROW     
BEGIN
		-- DELETE FROM emission_save WHERE (id=OLD.id) ;
		UPDATE emission_save SET deleteEmission=1 WHERE (id=OLD.id);
END;
$$
DELIMITER ;
 	

						
						