-- 1-- 
DELETE FROM emission_temp WHERE (USER='lionel');
DELETE FROM avenant_temp WHERE (USER='lionel');

DELETE FROM beneficiaire_temp WHERE (USER='lionel');
DELETE FROM adherent_temp WHERE (USER='lionel');

SET @idEmission = "0";
SET @idPolice =200;

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

SELECT codeTypeAvenant, numeroAvenant, dateEffet, motifavenant
INTO @codeTypeAvenant, @numeroAvenant, @dateAvenant, @motifavenant
FROM avenant WHERE id = @idAvenant;

-- select @codeTypeAvenant, @numeroAvenant, @dateAvenant, @motifavenant;
 
SELECT 
	dateEffet, 
	dateEcheance, 
	DATEDIFF(dateEcheance, dateEffet)+1, 
	DATEDIFF(dateEcheance, @dateAvenant),
	tauxRistourne
INTO 
	@dateEffet, 
	@dateEcheance, 
	@dureePolice,
	@dureeAvenant,
	@tauxRistourne
FROM police WHERE (id=@idPolice)
LIMIT 1;

SELECT 
 id INTO @idEmission 
FROM emission WHERE idPolice=@idPolice 
AND numeroAvenant = @numeroAvenant LIMIT 1;

SELECT IFNULL(@idEmission,"0") INTO @idEmission;

-- SELECT @idEmission;


IF (@idEmission > "0") THEN
	INSERT INTO emission_temp (
		codeSociete, 
		idClient, 
		numeroClient, 
		idPolice, 
		numeroPolice, 
		codeTypeAvenant, 
		codeTypeOperation, 
		numeroAvenant, 
		dateEffet, 
		dateEcheance, 
		dateEmission, 
		nbEcheance, 
		dateCre, 
		`user`, 
		nbAliment, 
		dateSysteme,
		dureePolice,
		dureeAvenant,
		ratioAvenant,
		motifavenant,
		tauxRistourne,
		commissionGestion,
		commission,
		commissionAutre,
		commissionIntermediaire,
		accessoire,
		accessoireGarant,
		assesoireApporteur,
		accessoireIntermediaire,
		accessoireGestion,
		accessoireCie,
		primeNette,
		primeNetteStat, 
		primeHt,
		primeNetteCie,
		taxe,
		fraisCarte,
		primeTtcInitial,
		primeTtc
	)
	SELECT
		A.codeSociete, 
		A.idClient, 
		A.numeroClient, 
		A.idPolice, 
		A.numeroPolice, 
		A.codeTypeAvenant,
		A.codeTypeOperation, 
		A.numeroAvenant, 
		A.dateEffet, 
		A.dateEcheance, 
		A.dateEmission, 
		A.nbEcheance, 
		A.dateCre, 
		'lionel' AS `user`, 
		A.nbAliment, 
		NOW() AS dateSysteme,
		@dureePolice,
		@dureeAvenant,
		"1" AS ratioAvenant,
		@motifavenant,
		@tauxRistourne,
		A.commissionGestion,
		A.commission,
		A.commissionAutre,
		A.commissionIntermediaire,
		A.accessoire,
		A.accessoireGarant,
		A.assesoireApporteur,
		A.accessoireIntermediaire,
		A.accessoireGestion,
		A.accessoireCie,
		A.primeNette,
		A.primeNetteStat, 
		A.primeHt,
		A.primeNetteCie,
		A.taxe,
		"0" AS fraisCarte,
		A.primeTtcInitial,
		A.primeTtc
	FROM emission A
	WHERE A.id = @idEmission;
	
	-- select emission_temp
	
	UPDATE avenant A
	JOIN emission B ON (A.idPolice = B.idPolice
	AND A.codeTypeAvenant = B.codeTypeAvenant
	AND A.numeroAvenant = B.numeroAvenant) SET 
		A.commissionGestion = B.commissionGestion,
		A.commission = B.commission,
		A.commissionAutre = B.commissionAutre,
		A.commissionIntermediaire = B.commissionIntermediaire,
		A.accessoire = B.accessoire,
		A.accessoireGarant = B.accessoireGarant,
		A.assesoireApporteur = B.assesoireApporteur,
		A.accessoireIntermediaire = B.accessoireIntermediaire,
		A.accessoireGestion = B.accessoireGestion,
		-- A.accessoireCie = B.accessoireCie,
		A.primeNette = B.primeNette,
		A.primeNetteStat = B.primeNetteStat, 
		A.primeHt = B.primeHt,
		-- A.primeNetteCie = B.primeNetteCie,
		A.taxe = B.taxe,
		A.fraisCarte = B.fraisCarte,
		-- A.primeTtcInitial = B.primeTtcInitial,
		A.primeTtc = B.primeTtc
	WHERE B.id = @idEmission;
	
	
	SELECT 	A.commissionGestion,
		A.commission,
		A.commissionAutre,
		A.commissionIntermediaire,
		A.accessoire,
		A.accessoireGarant,
		A.assesoireApporteur,
		A.accessoireIntermediaire,
		A.accessoireGestion,
		A.accessoireCie,
		A.primeNette,
		A.primeNetteStat, 
		A.primeHt,
		A.primeNetteCie,
		A.taxe,
		A.fraisCarte,
		A.primeTtcInitial,
		A.primeTtc
	INTO 
		@commissionGestion,
		@commission,
		@commissionAutre,
		@commissionIntermediaire,
		@accessoire,
		@accessoireGarant,
		@assesoireApporteur,
		@accessoireIntermediaire,
		@accessoireGestion,
		@accessoireCie,
		@primeNette,
		@primeNetteStat, 
		@primeHt,
		@primeNetteCie,
		@taxe,
		@raisCarte,
		@primeTtcInitial,
		@primeTtc
	FROM emission A
	WHERE id = @idEmission;
	
	SELECT MAX(id) INTO @idBeneficiaire FROM beneficiaire
	WHERE CONCAT(nomBeneficiaire, " ", prenomsBeneficiaire) LIKE '%FACTURATION%'
	AND nomBeneficiaire="AVENANT" AND idPolice=@idPolice;
	
	UPDATE beneficiaire A SET 
		A.commissionGestion = @commissionGestion,
		A.commission = @commission,
		A.commissionAutre = @commissionAutre,
		A.commissionIntermediaire = @commissionIntermediaire,
		A.accessoire = @accessoire,
		A.accessoireGarant = @accessoireGarant,
		-- A.assesoireApporteur = @assesoireApporteur,
		A.accessoireIntermediaire = @accessoireIntermediaire,
		A.accessoireGestion = @accessoireGestion,
		-- A.accessoireCie = @accessoireCie,
		A.primeNette = @primeNette,
		A.primeNetteStat = @primeNetteStat, 
		A.primeHt = @primeHt,
		-- A.primeNetteCie = @primeNetteCie,
		A.taxe = @taxe,
		A.fraisCarte = @fraisCarte,
		-- A.primeTtcInitial = @primeTtcInitial,
		A.primeTtc = @primeTtc
	WHERE A.id = @idBeneficiaire;
				 
ELSE

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

	SELECT 	A.commissionGestion,
		A.commission,
		A.commissionAutre,
		A.commissionIntermediaire,
		A.accessoire,
		A.accessoireGarant,
		A.assesoireApporteur,
		A.accessoireIntermediaire,
		A.accessoireGestion,
		A.accessoireCie,
		A.primeNette,
		A.primeNetteStat, 
		A.primeHt,
		A.primeNetteCie,
		A.taxe,
		A.fraisCarte,
		A.primeTtcInitial,
		A.primeTtc
	INTO 
		@commissionGestion,
		@commission,
		@commissionAutre,
		@commissionIntermediaire,
		@accessoire,
		@accessoireGarant,
		@assesoireApporteur,
		@accessoireIntermediaire,
		@accessoireGestion,
		@accessoireCie,
		@primeNette,
		@primeNetteStat, 
		@primeHt,
		@primeNetteCie,
		@taxe,
		@raisCarte,
		@primeTtcInitial,
		@primeTtc
	FROM avenant A
	WHERE A.id = @idAvenant;
	
	UPDATE beneficiaire A SET 
		A.commissionGestion = @commissionGestion,
		A.commission = @commission,
		A.commissionAutre = @commissionAutre,
		A.commissionIntermediaire = @commissionIntermediaire,
		A.accessoire = @accessoire,
		A.accessoireGarant = @accessoireGarant,
		-- A.assesoireApporteur = @assesoireApporteur,
		A.accessoireIntermediaire = @accessoireIntermediaire,
		A.accessoireGestion = @accessoireGestion,
		-- A.accessoireCie = @accessoireCie,
		A.primeNette = @primeNette,
		A.primeNetteStat = @primeNetteStat, 
		A.primeHt = @primeHt,
		-- A.primeNetteCie = @primeNetteCie,
		A.taxe = @taxe,
		A.fraisCarte = @fraisCarte,
		-- A.primeTtcInitial = @primeTtcInitial,
		A.primeTtc = @primeTtc
	WHERE A.id = @idBeneficiaire;
	
	
	INSERT INTO emission_temp (
		codeSociete, 
		idClient, 
		numeroClient, 
		idPolice, 
		numeroPolice, 
		codeTypeAvenant, 
		codeTypeOperation, 
		numeroAvenant, 
		dateEffet, 
		dateEcheance, 
		dateEmission, 
		nbEcheance, 
		dateCre, 
		`user`, 
		nbAliment, 
		dateSysteme,
		dureePolice,
		dureeAvenant,
		ratioAvenant,
		motifavenant,
		tauxRistourne,
		commissionGestion,
		commission,
		commissionAutre,
		commissionIntermediaire,
		accessoire,
		accessoireGarant,
		assesoireApporteur,
		accessoireIntermediaire,
		accessoireGestion,
		
		primeNette,
		primeNetteStat,
		primeNetteCie,
		primeHt,
		taxe,
		fraisCarte,
		primeTtcInitial	,	
		primeTtc
	)
	SELECT
		A.codeSociete, 
		A.idClient, 
		A.numeroClient, 
		A.id AS idPolice, 
		A.numeroPolice, 
		"QTM" AS codeTypeAvenant,
		"EMI" AS codeTypeOperation, 
		@numeroAvenant AS numeroAvenant, 
		A.dateEffet AS dateEffet, 
		A.dateEcheance, 
		CURDATE() AS dateEmission, 
		'1' AS nbEcheance, 
		CURDATE() AS dateCre, 
		'lionel' AS `user`, 
		"0" AS nbAliment, 
		NOW() AS dateSysteme,
		@dureePolice,
		@dureeAvenant,
		@ratioAvenant,
		@motifavenant,
		A.tauxRistourne,
		
		B.commissionGestion,
		B.commission,
		B.commissionAutre,
		B.commissionIntermediaire,
		B.accessoire,
		B.accessoireGarant,
		'0' AS assesoireApporteur,
		B.accessoireIntermediaire,
		B.accessoireGestion,
		
		B.primeNette,
		B.primeNetteStat,
		B.primeNette,
		B.primeHt,
		B.taxe,
		"0" AS fraisCarte,
		B.primeTtc,	
		B.primeTtc
	FROM police A
	JOIN beneficiaire B ON (B.idPolice = A.id)
	WHERE (A.id=@idPolice) AND B.id = @idBeneficiaire;
END IF;
 
INSERT INTO avenant_temp 
(
	codeSociete, 
	idClient, 
	numeroClient, 
	idPolice, 
	numeroPolice, 
	numeroEmission, 
	codeTypeAvenant, 
	codeTypeOperation, 
	numeroAvenant, 
	dateEffet, 
	dateEcheance, 
	dateEmission, 
	chiffreAffaire, 
	tauxInteret, 
	interet, 
	nbEcheance, 
	dateCre, 
	`user`, 
	encaisse, 
	regle, 
	nbAliment, 
	dateSysteme, 
	dureePolice, 
	dureeAvenant, 
	ratioAvenant, 
	motifavenant, 
	aRistourner, 
	primeAcquise, 
	primeNonAcquise, 
	nbAdherent, 
	nbAyantdroit, 
	aFacturer, 
	cumulPlafond, 
	tauxRistourne, 
	rapportSp,
	accessoire,
	accessoireGarant,
	assesoireApporteur, 
	accessoireIntermediaire,
	accessoireGestion,
	accessoireCie, 
	commission, 
	commissionAutre, 
	commissionGestion,
	commissionIntermediaire,
	primeNette,
	primeNetteStat,
	primeHt,
	primeNetteCie,
	taxe,
	fraisCarte,
	primeTtcInitial,
	primeTtc
)
SELECT  DISTINCT
	codeSociete, 
	idClient, 
	numeroClient, 
	idPolice, 
	numeroPolice, 
	numeroEmission, 
	@codeTypeAvenant, 
	codeTypeOperation, 
	@numeroAvenant, 
	dateEffet, 
	dateEcheance, 
	dateEmission, 
	chiffreAffaire, 
	tauxInteret, 
	interet,
	nbEcheance, 
	dateCre, 
	`user`, 
	encaisse, 
	regle, 
	nbAliment, 
	dateSysteme, 
	dureePolice, 
	dureeAvenant, 
	ratioAvenant, 
	motifavenant, 
	aRistourner, 
	primeAcquise, 
	primeNonAcquise, 
	nbAdherent, 
	nbAyantdroit, 
	aFacturer, 
	cumulPlafond, 
	tauxRistourne, 
	rapportSp,
	accessoire,
	accessoireGarant,
	assesoireApporteur, 
	accessoireIntermediaire,
	accessoireGestion,
	accessoireCie, 
	commission, 
	commissionAutre, 
	commissionGestion,
	commissionIntermediaire,
	primeNette,
	primeNetteStat, 
	primeHt,
	primeNetteCie,
	taxe,
	"0" AS fraisCarte,
	primeTtcInitial,
	primeTtc
FROM emission_temp 
WHERE (USER='lionel');


UPDATE beneficiaire_temp A
JOIN beneficiaire B ON (B.id = A.idBeneficiaire) SET 
		A.primeNette 			= B.primeNette, 
		
		A.commissionGestion 		= B.commissionGestion,
		A.commission 			= B.commission, 
		A.commissionAutre		= B.commissionAutre, 
		A.commissionIntermediaire 	= B.commissionIntermediaire, 
		
		A.primeNetteStat 		= B.primeNetteStat,
		 
		A.accessoire 			= B.accessoire,
		A.accessoireGarant 		= B.accessoireGarant,
		A.accessoireGestion 		= B.accessoireGestion,
		A.accessoireIntermediaire 	= B.accessoireIntermediaire,
		
		A.primeHt 			= B.primeHt, 
		A.taxe 				= B.taxe, 
		A.primeTtc 			= B.primeTtc,
		A.fraisCarte 			= "0"
WHERE CONCAT(B.nomBeneficiaire, " ", B.prenomsBeneficiaire) LIKE '%FACTU%'
AND B.nomBeneficiaire="AVENANT" AND B.idPolice=@idPolice;


DROP TEMPORARY TABLE IF EXISTS temp_ben;
CREATE TEMPORARY TABLE temp_ben
( INDEX(idAdherent), INDEX(idBeneficiaire) )
SELECT
	A.idAdherent,
	A.id AS idBeneficiaire,
	A.codeLienParente
FROM beneficiaire A WHERE (A.idPolice=@idPolice) 
AND (A.enVigueur="1") AND (A.dateEffet<=@dateAvenant)
AND (A.sorti!="1");
 
INSERT INTO beneficiaire_temp (
	idBeneficiaire,
	codeSociete, 
	idPolice, 
	idCollege, 
	idAdherent,
	numeroAdherent,
	numeroBeneficiaire,
	dateEntree, 
	USER, 
	dateSysteme,
	deduireComm,
	dateEffet,
	codeLienParente
)
SELECT
	A.id AS idBeneficiaire,
	A.codeSociete, 
	A.idPolice, 
	A.idCollege, 
	A.idAdherent,
	A.numeroAdherent,
	A.numeroBeneficiaire,
	A.dateEffet AS dateEntree, 
	'lionel' AS USER, 		
	NOW() AS dateSysteme,
	A.deduireComm,
	@dateAvenant AS dateEffet,
	A.codeLienParente
FROM beneficiaire A 
JOIN temp_ben B ON (B.idBeneficiaire=A.id);
 
DROP TEMPORARY TABLE IF EXISTS temp_ben;