-- 2-- 
SELECT 
MAX(id) INTO @idAvenant
FROM avenant
WHERE idPolice=200
AND codeTypeAvenant="QTM";

SELECT 
	id,
	dateEffet, 
	dateFin,
	YEAR(dateEffet),
	fn_id_entetecontrat_police_en_cours(id),
	libellePolice
INTO 
	@idPolice,
	@dateEffetPolice, 
	@dateFinPolice,
	@exercieReference,
	@idEntetecontrat,
	@libellePolice
FROM police 
WHERE (id=200);

SELECT 
 MAX(id) INTO @idBeneficiaire 
FROM beneficiaire
WHERE CONCAT(nomBeneficiaire, " ", prenomsBeneficiaire) LIKE '%FACTURATION%'
AND nomBeneficiaire="AVENANT" AND idPolice=@idPolice;

IF (@idAvenant>"0") THEN
	SELECT codeSociete, dateEffet, motifavenant, codeTypeAvenant, numeroAvenant, facture
	INTO @codeSociete, @dateAvenant, @motifavenant, @codeTypeAvenant, @numeroAvenant, @facture
	FROM avenant WHERE id = 1244;
	
ELSE
	SELECT codeSociete, dateEffet, motifavenant, "QTM"
	INTO @codeSociete, @dateAvenant, @motifavenant, @codeTypeAvenant
	FROM avenant_temp WHERE (`user`='lionel');
 
	UPDATE police SET 
	numeroAvenant = numeroAvenant+1, 
	codeTypeAvenant = "QTM",
	dateAvenant = @dateAvenant
	WHERE (id=@idPolice);
 
	SELECT numeroAvenant INTO @numeroAvenant FROM police WHERE (id=@idPolice);
END IF;
 
UPDATE police A
JOIN typeavenant B ON (B.codeTypeAvenant=A.codeTypeAvenant)
SET A.codeEtatPolice=B.nouvelEtatPolice,
A.codeTypeAvenantCpt=CASE WHEN B.calculerPrime="1" THEN @codeTypeAvenant ELSE A.codeTypeAvenantCpt END
WHERE (A.id=@idPolice);
 
SELECT 
	accessoire,
	accessoireGarant,
	accessoireGestion,
	accessoireIntermediaire,
	
	commissionGestion,
	commission,
	commissionAutre,
	commissionIntermediaire,
	
	primeNette,
	primeNetteStat, 
	primeHt,
	taxe,
	fraisCarte,
	primeTtcInitial,
	primeTtc
 INTO 
	@accessoire,
	@accessoireGarant,
	@accessoireGestion,
	@accessoireIntermediaire,
	
	@commissionGestion,
	@commission,
	@commissionAutre,
	@commissionIntermediaire,
	
	@primeNette,
	@primeNetteStat, 
	@primeHt,
	@taxe,
	@fraisCarte,
	@primeTtcInitial,
	@primeTtc
 FROM avenant_temp A WHERE (A.user='lionel');
 
 -- select * FROM avenant_temp A WHERE (A.user='lionel');
  
 SET @accessoire 		= IFNULL(@accessoire,0);
 SET @accessoireGarant 		= IFNULL(@accessoireGarant,0);
 SET @accessoireGestion 	= IFNULL(@accessoireGestion,0);
 SET @accessoireIntermediaire 	= IFNULL(@accessoireIntermediaire,0);
 
 SET @commission 		= IFNULL(@commission,0);
 SET @commissionAutre 		= IFNULL(@commissionAutre,0);
 SET @commissionGestion 	= IFNULL(@commissionGestion,0);
 SET @commissionIntermediaire 	= IFNULL(@commissionIntermediaire,0);
 
 
 SET @primeNette 		= IFNULL(@primeNette,0);
 SET @primeNetteStat 		= IFNULL(@primeNetteStat,0);
 SET @primeHt 			= IFNULL(@primeHt,0);
 SET @taxe 			= IFNULL(@taxe,0);
 
 SET @fraisCarte 		= IFNULL(@fraisCarte,0);
 SET @primeTtcInitial 		= IFNULL(@primeTtcInitial,0);
 SET @primeTtc 			= IFNULL(@primeTtc,0);
 

UPDATE avenant SET
	accessoire 		= @accessoire,
	accessoireGarant 	= @accessoireGarant,
	accessoireGestion 	= @accessoireGestion,
	accessoireIntermediaire = @accessoireIntermediaire,
	commission 		= @commission,
	commissionAutre 	= @commissionAutre,
	commissionGestion 	= @commissionGestion,
	commissionIntermediaire = @commissionIntermediaire,
	primeNette 		= @primeNette,
	primeNetteStat 		= @primeNetteStat,
	primeHt 		= @primeHt,
	taxe 			= @taxe,
	fraisCarte 		= @fraisCarte,
	primeTtcInitial 	= @primeTtcInitial,
	primeTtc 		= @primeTtc
WHERE id = @idAvenant;

IF (@facture = "0") THEN
	UPDATE emission SET
		primeNette 			= @primeNette, 
		
		commissionGestion 		= @commissionGestion,
		commission 			= @commission, 
		commissionAutre			= @commissionAutre, 
		commissionIntermediaire 	= @commissionIntermediaire, 
		
		primeNetteStat 			= @primeNetteStat,
		 
		accessoire 			= @accessoire,
		accessoireGarant 		= @accessoireGarant,
		accessoireGestion 		= @accessoireGestion,
		accessoireIntermediaire 	= @accessoireIntermediaire,
		
		primeHt 			= @primeHt, 
		taxe 				= @taxe, 
		primeTtc 			= @primeTtc
	WHERE numeroAvenant = @numeroAvenant AND codeTypeAvenant = @codeTypeAvenant;
	
	UPDATE quittance SET
		primeNette 			= @primeNette, 
		
		commissionGestion 		= @commissionGestion,
		commission 			= @commission, 
		commissionAutre			= @commissionAutre, 
		commissionIntermediaire 	= @commissionIntermediaire, 
		
		primeNetteStat 			= @primeNetteStat,
		 
		accessoire 			= @accessoire,
		accessoireGarant 		= @accessoireGarant,
		accessoireGestion 		= @accessoireGestion,
		accessoireIntermediaire 	= @accessoireIntermediaire,
		
		primeHt 			= @primeHt, 
		taxe 				= @taxe, 
		primeTtc 			= @primeTtc
	WHERE numeroAvenant = @numeroAvenant AND codeTypeAvenant = @codeTypeAvenant;
	
	--  ON DOIT AVOIR INSERT EMMISSION
	
END IF;
	
SELECT  COUNT(*) INTO @nbre 
FROM beneficiaireprime WHERE idAvenant = @idAvenant;

-- select @nbre;

IF (@nbre="0") THEN
	
	INSERT INTO beneficiaireprime 
	( 
		codeSociete, 
		idPolice, 
		idAvenant, 
		dateEffet, 
		enVigueur, 
		idBeneficiaire, 
		numeroBeneficiaire, 
		primeNette, 
		primeCplFuneraire,
		
		commissionGestion,
		commission, 
		commissionAutre, 
		commissionIntermediaire,
		
		primeNetteStat,
		 
		accessoire,
		accessoireGarant,
		accessoireGestion,
		accessoireIntermediaire,
		
		primeHt, 
		taxe, 
		fraisCarte, 
		primeTtc, 
		dateSysteme, 
		codeUtilisateur,
		idEmission,
		numeroEmission,
		idAdherent,
		exercice, 
		exercieReference, 
		dateEffetPolice, 
		dateFinPolice, 
		dateEffetAdherent, 
		dateEffetBeneficiaire,
		codeProduit,
		codeLienParente,
		idCollege,
		codeEtatFacturation,
		userFacturation,
		dateFacturation,
		idEntetecontrat
	)
	SELECT
		A.codeSociete, 
		A.idPolice, 
		@idAvenant, 
		@dateAvenant, 
		A.enVigueur, 
		A.id AS idBeneficiaire, 
		A.numeroBeneficiaire, 
		@primeNette, 
		"0" AS primeCplFuneraire,
		
		@commissionGestion,
		@commission, 
		@commissionAutre, 
		@commissionIntermediaire, 
		
		@primeNetteStat,
		 
		@accessoire,
		@accessoireGarant,
		@accessoireGestion,
		@accessoireIntermediaire,
		
		@primeHt, 
		@taxe, 
		@fraisCarte, 
		@primeTtc, 
		NOW() AS dateSysteme, 
		A.`user` AS codeUtilisateur,
		"0" idEmission,
		"0" numeroEmission,
		A.idAdherent,
		YEAR(A.dateEffet) AS exercice, 
		YEAR(B.dateEffet) AS  exercieReference, 
		B.dateEffet AS dateEffetPolice, 
		B.dateFin AS dateFinPolice,
		@dateAvenant AS dateEffetAdherent, 
		@dateAvenant AS dateEffetBeneficiaire,
		A.codeProduit,
		A.codeLienParente,
		A.idCollege,
		"0" AS codeEtatFacturation,
		CASE WHEN A.codeEtatFacturation="1" THEN A.`user` ELSE "" END AS userFacturation,
		CASE WHEN A.codeEtatFacturation="1" THEN NOW() ELSE NULL END AS dateFacturation,
		fn_id_entetecontrat_police(A.idPolice, A.dateEffet) AS idEntetecontrat
	FROM beneficiaire A
	JOIN police B ON (B.id=A.idPolice)
	JOIN adherent Ad ON (Ad.id=A.idAdherent)
	WHERE A.id = @idBeneficiaire;
	 
	 
	INSERT INTO beneficiaireavenant 
	(
		idAvenant, 
		dateEffet, 
		idBeneficiaire, 
		numeroBeneficiaire, 
		idCollege, 
		idEntetecontrat, 
		codeUtilisateur
	)
	SELECT
		idAvenant, 
		dateEffet, 
		idBeneficiaire, 
		numeroBeneficiaire, 
		idCollege, 
		idEntetecontrat, 
		codeUtilisateur
	FROM beneficiaireprime A
	WHERE (A.idPolice=@idPolice)
	AND (A.idAvenant=@idAvenant);
	
ELSE

	UPDATE beneficiaireprime SET
		primeNette 			= @primeNette, 
		
		commissionGestion 		= @commissionGestion,
		commission 			= @commission, 
		commissionAutre			= @commissionAutre, 
		commissionIntermediaire 	= @commissionIntermediaire, 
		
		primeNetteStat 			= @primeNetteStat,
		 
		accessoire 			= @accessoire,
		accessoireGarant 		= @accessoireGarant,
		accessoireGestion 		= @accessoireGestion,
		accessoireIntermediaire 	= @accessoireIntermediaire,
		
		primeHt 			= @primeHt, 
		taxe 				= @taxe, 
		primeTtc 			= @primeTtc
	WHERE idAvenant = @idAvenant;

END IF;


/*DELETE FROM emission_temp WHERE (`user`='lionel');
DELETE FROM avenant_temp WHERE (`user`='lionel');
DELETE FROM adherent_temp WHERE (`user`='lionel');
DELETE FROM beneficiaire_temp WHERE (`user`='lionel');
DELETE FROM p_facture_temp WHERE (`userA`='lionel');*/