DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_forcer_renouvellement_adherent`$$

CREATE PROCEDURE `sp_forcer_renouvellement_adherent`(
	p_idAdherent BIGINT,
	p_idEntetecontratOld BIGINT,
	p_idEntetecontratNew BIGINT
)
BEGIN

	-- fn_id_entetecontrat_beneficiaire / p_idEntetecontratOld
	
	-- fn_id_entetecontrat_beneficiaire_courant / p_idEntetecontratNew
	
	SELECT A.id, A.dateEffet, A.dateFin 
	INTO @idPolice, @dateEffet, @dateFinPolice 
	FROM police A
	JOIN adherent B ON (B.idPolice=A.id)
	WHERE B.id=p_idAdherent;
	 
	SELECT MAX(id) INTO @idAvenant FROM avenant A
	WHERE idPolice=@idPolice AND codeTypeAvenant="REN";
	 
	 
	UPDATE beneficiaire A
	SET
		A.dateEffet 		= @dateEffet,
		A.codeEtatFacturation 	= "0", 
		A.enVigueur 		="1",	
		A.ratioAvenant		= "1",
		A.primeArchive		= "0",
		sorti="0",
		dateSortie=NULL,
		codeEtatbeneficiaire="V",
		codeMotifSortie="",
		supprime="0"
	WHERE (A.idAdherent=p_idAdherent);

	UPDATE adherent A
	SET
		A.dateEffet 		= @dateEffet,
		A.enVigueur 		="1",	
		A.ratioAvenant		= "1",
		A.primeArchive		= "0",
		sorti="0",
		dateSortie=NULL,
		codeEtatAdherent="V",
		codeMotifSortie="",
		supprime="0"
	WHERE (A.id=p_idAdherent);
	
	DELETE FROM beneficiaireprime 
	WHERE (idAdherent=p_idAdherent)
	AND idEntetecontrat=p_idEntetecontratNew;
	 
	 
	INSERT INTO beneficiaireprime 
	( 
		codeSociete, 
		idPolice, 
		idAvenant, 
		dateEffet, 
		enVigueur, 
		idBeneficiaire, 
		numeroBeneficiaire, 
		primeNette, 
		primeCplFuneraire,
		commission, 
		commissionAutre, 
		primeNetteStat, 
		primeHt, 
		accessoire,
		taxe, 
		fraisCarte, 
		primeTtc, 
		dateSysteme, 
		codeUtilisateur,
		idEmission,
		numeroEmission,
		idAdherent,
		
		exercice, 
		exercieReference, 
		dateEffetPolice, 
		dateFinPolice, 
		dateEffetAdherent, 
		dateEffetBeneficiaire,
		codeProduit,
		codeLienParente,
		idCollege,
		idEntetecontrat	
	)
	
	SELECT
		codeSociete, 
		idPolice, 
		@idAvenant, 
		@dateEffet, 
		"1" enVigueur, 
		idBeneficiaire, 
		numeroBeneficiaire, 
		primeNette, 
		primeCplFuneraire,
		commission, 
		commissionAutre, 
		primeNetteStat, 
		primeHt, 
		accessoire,
		taxe, 
		fraisCarte, 
		primeTtc, 
		dateSysteme, 
		codeUtilisateur,
		"0" idEmission,
		"0" numeroEmission,
		idAdherent,
		
		YEAR(@dateEffet) AS exercice, 
		YEAR(@dateEffet) AS exercieReference, 
		YEAR(@dateEffet) AS  dateEffetPolice, 
		@dateFinPolice, 
		@dateEffetdateEffetAdherent, 
		@dateEffetdateEffetBeneficiaire,
		codeProduit,
		codeLienParente,
		idCollege,
		p_idEntetecontratNew AS idEntetecontrat	
	FROM beneficiaireprime A
	WHERE (A.idAdherent=p_idAdherent)
	AND idEntetecontrat=p_idEntetecontratOld;
	
	 
	DELETE FROM garantieadherent 
	WHERE (idAdherent=p_idAdherent)
	AND idEntetecontrat=p_idEntetecontratNew;
	
	 
	INSERT INTO garantieadherent 
	(
		codeSociete, 
		idPolice, 
		idCollege, 
		idAdherent, 
		numeroAdherent, 
		codeGarantie, 
		garantie, 
		optionnelle, 
		additionnelle, 
		ordre, 
		plafond, 
		champApplication, 
		consommation, 
		ratioAvenant, 
		plafondAnnuel, 
		plafondCollege, 
		primeParTete, 
		exercieReference,
		idEntetecontrat
	)
	SELECT
		A.codeSociete, 
		A.idPolice, 
		A.idCollege, 
		A.idAdherent, 
		A.numeroAdherent, 
		A.codeGarantie, 
		A.garantie, 
		A.optionnelle, 
		A.additionnelle, 
		A.ordre, 
		A.plafond, 
		A.champApplication, 
		0 consommation, 
		"1" ratioAvenant, 
		A.plafondAnnuel, 
		A.plafondCollege, 
		A.primeParTete, 
		YEAR(@dateEffet) AS exercieReference,
		p_idEntetecontratNew
	FROM garantieadherent A
	WHERE (A.idAdherent=p_idAdherent)
	AND idEntetecontrat=p_idEntetecontratOld;
	 
	DELETE FROM garantiebeneficiaire 
	WHERE (idAdherent=p_idAdherent)
	AND idEntetecontrat=p_idEntetecontratNew;
	 
	INSERT INTO garantiebeneficiaire 
	(
		codeSociete, 
		idPolice, 
		idCollege, 
		idAdherent, 
		numeroAdherent, 
		idBeneficiaire, 
		numeroBeneficiaire, 
		codeGarantie, 
		garantie, 
		optionnelle, 
		additionnelle, 
		ordre, 
		plafond, 
		consommation, 
		ratioAvenant, 
		plafondAnnuel, 
		plafondCollege, 
		exercieReference,
		idEntetecontrat
	)
	SELECT
		A.codeSociete, 
		A.idPolice, 
		A.idCollege, 
		A.idAdherent, 
		A.numeroAdherent, 
		A.idBeneficiaire, 
		A.numeroBeneficiaire, 
		A.codeGarantie, 
		A.garantie, 
		A.optionnelle, 
		A.additionnelle, 
		A.ordre, 
		A.plafond, 
		0 consommation, 
		A.ratioAvenant, 
		A.plafondAnnuel, 
		A.plafondCollege, 
		YEAR(@dateEffet) AS exercieReference,
		p_idEntetecontratNew
	FROM garantiebeneficiaire A
	WHERE (A.idAdherent=p_idAdherent)
	AND idEntetecontrat=p_idEntetecontratOld;
	  
	DELETE FROM limiteadherent 
	WHERE (idAdherent=p_idAdherent)
	AND idEntetecontrat=p_idEntetecontratNew;

 	INSERT INTO limiteadherent 
	(
		codeSociete, 
		idPolice,
		idAdherent, 
		numeroAdherent, 
		plafondAdherent, 
		exercieReference, 
		idEntetecontrat
	)
	
	SELECT
		codeSociete, 
		idPolice,
		idAdherent, 
		numeroAdherent, 
		A.plafondAdherent/A.ratioAvenant AS plafondAdherent,
		YEAR(@dateEffet) AS exercieReference,
		p_idEntetecontratNew
	FROM limiteadherent A
	WHERE (A.idAdherent=p_idAdherent)
	AND idEntetecontrat=p_idEntetecontratOld;

	DELETE FROM limitebeneficiaire 
	WHERE (idAdherent=p_idAdherent)
	AND idEntetecontrat=p_idEntetecontratNew;

 
 	INSERT INTO limitebeneficiaire 
	(
		codeSociete, 
		idPolice,
		idAdherent,		
		idBeneficiaire, 
		numeroBeneficiaire, 
		plafondBeneficiaire, 
		exercieReference, 
		idEntetecontrat
	)

	SELECT
		codeSociete, 
		idPolice,
		idAdherent,		
		idBeneficiaire, 
		numeroBeneficiaire, 
		A.plafondBeneficiaire/A.ratioAvenant AS plafondBeneficiaire,
		YEAR(@dateEffet) AS exercieReference,
		p_idEntetecontratNew
	FROM limitebeneficiaire A
	WHERE (A.idAdherent=p_idAdherent)
	AND idEntetecontrat=p_idEntetecontratOld;
END$$

DELIMITER ;