-- SELECT * FROM typeavenant

SELECT * FROM 

-- 0 RECHERCHE DU NUMERO DE POLICE

-- SELECT * FROM police WHERE libellePolice LIKE 'mer%' ;

-- 1 AVENANT EN COURS
-- CALL sp_avenants_police(205) ;

-- 2 CREATION D'UN NOUVEAU AVENANT DE REMISE EN VIGUEUR
CALL sp_creer_avenant(205,'RVP','Remise en vigueur demandé DG, 23/09/2022','2022-09-23','charles-eric') ;


-- 4 CREATION D'UN NOUVEAU AVENANT DE SUSPENSION CIBLE
	-- 4.a creation de l'avenanant
	INSERT INTO avenant (codeSociete,idPolice,numeroPolice,codeTypeAvenant,numeroAvenant,dateEffet, 
	dateCre,`user`,dateSysteme,motifavenant)

	SELECT codeSociete,id AS idPolice,numeroPolice,codeTypeAvenant,numeroAvenant, 
	'2022-09-23' AS dateEffet,NOW() AS dateCre,'charles-eric' AS `user`, 
	NOW() AS dateSysteme,'Remise en etat de suspension par DG, du 31/08/2022' motifavenant
	FROM police WHERE (id=205);

	-- 4.b Rechercher de idAvenant
	SELECT id INTO @idAvenant
	FROM avenant WHERE (idPolice=205) ORDER BY id DESC LIMIT 1;

	-- 4.c mise à jour de la police
	UPDATE police A
	JOIN typeavenant B ON (B.codeTypeAvenant=A.codeTypeAvenant)
		SET 
			A.numeroAvenant = A.numeroAvenant+1, 
			A.codeTypeAvenant = "SUS",
			A.dateAvenant = '2022-09-23',
			A.codeEtatPolice=B.nouvelEtatPolice,
			A.codeTypeAvenantCpt=CASE WHEN B.calculerPrime="1" THEN "SUS" ELSE A.codeTypeAvenantCpt END
	WHERE (A.id=205);
 
	 -- 4.d Recherche du numero avenant
	SELECT numeroAvenant INTO @numeroAvenant 
	FROM police WHERE (id=205);
	 
	-- 4.f
	INSERT INTO etatadherent(idPolice,idAdherent,codeEtatAdherent,dateEtat,dateSysteme,idAvenant,`user`,motifEtat)
	SELECT 
		'205',id AS idAdherent,codeEtatAdherent,NOW() dateEtat,NOW() dateSysteme,@idAvenant AS idAvenant,
		'charles-eric','Remise en etat de suspension par DG, du 31/08/2022'
	FROM adherent 
	WHERE (idPolice='205') AND (codeEtatAdherent="V") -- AND idAdherent NOT IN ( );
	AND numeroAdherent NOT IN ( '02050516','02050294','02050499','02050369','02050522','02050477','02050366','02050511',
	'02050497','02050093','02050291','02050336','02050293','02050295','02050158','02050523','02050156',
	'02050155','02050367','02050308','02050520','02050151','02050376','02050356','02050284','02050307',
	'02050303','02050085','02050244','02050378','02050296','02050147','02050493','02050143','02050352',
	'02050142','02050127','02050141','02050306','02050292','02050081') ;

	-- 4.g
	UPDATE adherent SET codeEtatAdherent="SP", dateEtat=NOW() 
	WHERE (idPolice='205') AND (codeEtatAdherent="V") --  AND idAdherent NOT IN ( );
	AND numeroAdherent NOT IN ( '02050516','02050294','02050499','02050369','02050522','02050477','02050366','02050511',
	'02050497','02050093','02050291','02050336','02050293','02050295','02050158','02050523','02050156',
	'02050155','02050367','02050308','02050520','02050151','02050376','02050356','02050284','02050307',
	'02050303','02050085','02050244','02050378','02050296','02050147','02050493','02050143','02050352',
	'02050142','02050127','02050141','02050306','02050292','02050081');
	
	-- 4.h	
	INSERT INTO etatbeneficiaire(idPolice,idBeneficiaire,codeEtatBeneficiaire,dateEtat,dateSysteme,idAvenant,`user`,motifEtat)
	SELECT
		'205',id AS idBeneficiaire,codeEtatBeneficiaire,NOW() dateEtat,NOW() dateSysteme,@idAvenant AS idAvenant,
		'charles-eric','Remise en etat de suspension par DG, du 31/08/2022'
	FROM beneficiaire 
	WHERE (idPolice='205') AND (codeEtatBeneficiaire="V") -- AND idBeneficiaire NOT IN ( );
	AND numeroAdherent NOT IN ( '02050516','02050294','02050499','02050369','02050522','02050477','02050366','02050511',
	'02050497','02050093','02050291','02050336','02050293','02050295','02050158','02050523','02050156',
	'02050155','02050367','02050308','02050520','02050151','02050376','02050356','02050284','02050307',
	'02050303','02050085','02050244','02050378','02050296','02050147','02050493','02050143','02050352',
	'02050142','02050127','02050141','02050306','02050292','02050081');

	-- 4.i	
	UPDATE beneficiaire SET codeEtatBeneficiaire="SP", dateEtat=NOW()
	WHERE (idPolice='205') AND (codeEtatBeneficiaire="V") -- AND idBeneficiaire NOT IN ( );
		AND numeroAdherent NOT IN ( '02050516','02050294','02050499','02050369','02050522','02050477','02050366','02050511',
	'02050497','02050093','02050291','02050336','02050293','02050295','02050158','02050523','02050156',
	'02050155','02050367','02050308','02050520','02050151','02050376','02050356','02050284','02050307',
	'02050303','02050085','02050244','02050378','02050296','02050147','02050493','02050143','02050352',
	'02050142','02050127','02050141','02050306','02050292','02050081');
	
		
	UPDATE beneficiaire 
	SET codeEtatBeneficiaire="SA" 
	WHERE (idPolice=205)
	AND (codeEtatBeneficiaire="SP") AND dateEtat='2022-09-23';
	
	UPDATE adherent 
	SET codeEtatAdherent="SA"
	WHERE (idPolice=205)
	AND (codeEtatAdherent="SP") AND dateEtat='2022-09-23';