-- 1-- debut
CALL sp_vider_college_temp('lionel');
	 
	SELECT 	
		codeProduitBase,
		fraisCarteAN, 
		fraisCarteREN, 
		fraisCarteImp,
		tauxPrimeFraisFuneraire,
		prestationParLienParente
	INTO 	
		@codeProduitBase,
		@fraisCarteAN, 
		@fraisCarteREN, 
		@fraisCarteImp,
		@tauxPrimeFraisFuneraire,
		@prestationParLienParente*
	FROM societeuser
	WHERE (codeSociete='vitalis') LIMIT 1;
	 
	INSERT INTO college_temp 
	(
		codeSociete, 
		idPolice, 
		numeroPolice, 
		`user`, 
		rachatTm, 
		deduireComm, 
		tauxRachatTm, 
		tauxPrimeRachatTm, 
		codeProduit, 
		libelleCollege, 
		fraisCarte, 
		numeroOptionTarif,
		primeFamille,
		primeMin, 
		primeMax,
		tauxPrimeFraisFuneraire,
		delaiCarenceCollege,
		plafondAdherent, 
		plafondBeneficiaire,
		primeParTete
	)
	SELECT 
		A.codeSociete, 
		A.id AS idPolice, 
		numeroPolice,
		'lionel' AS USER, 
		A.rachatTm, 
		A.deduireComm, 
		A.tauxRachatTm, 
		"2"*A.tauxRachatTm AS tauxPrimeRachatTm,
		"" AS codeProduit,
		"" AS libelleCollege,
		IFNULL(@fraisCarteAN,0) AS fraisCarte,
		"1" AS numeroOptionTarif,
		IFNULL(B.primeFamille,0) AS primeFamille,
		IFNULL(B.primeMin,0) AS primeMin,
		IFNULL(B.primeMax,0) AS primeMax,
		@tauxPrimeFraisFuneraire,
		A.delaiCarencePolice,
		IFNULL(B.plafondAdherent,0) AS plafondAdherent,
		IFNULL(B.plafondBeneficiaire,0) AS plafondBeneficiaire,
		IFNULL(B.primeParTete,"0") AS primeParTete
	FROM police A
	LEFT JOIN produit B ON (B.codeSociete=A.codeSociete) 
	AND (B.codeProduit=@codeProduitBase)	
	WHERE (A.id=212);
	 
	SELECT LAST_INSERT_ID() INTO @idCollegeTemp;
	-- @idCollegeTemp=9138

-- 2--
--	select * from college_temp
--- CALL sp_init_prime_college(@idCollegeTemp, @codeProduitBase, p_codeGcAssureur, 'lionel');
DELETE FROM primecollege_temp WHERE (idCollegeTemp=p_idCollegeTemp);
	
	INSERT INTO primecollege_temp 
	(
		codeSociete, 
		idCollege, 
		idPolice, 
		idCollegeTemp, 
		codeLienParente, 
		primeTarif,
		prime, 
		primeMin, 
		primeMax, 
		plafondBeneficiaire,
		population,
		codeUtilisateur
	)
	SELECT DISTINCT
		A.codeSociete, 
		B.idCollege, 
		B.idPolice, 
		B.id AS idCollegeTemp, 
		A.codeLienParente, 
		A.prime, 
		A.prime, 
		A.primeMin, 
		A.primeMax, 
		B.plafondBeneficiaire,
		CASE WHEN A.codeLienParente ="A" THEN "1" ELSE "0" END AS population,
		B.user AS codeUtilisateur
	FROM primeproduit A
	JOIN college_temp B ON (B.codeSociete=A.codeSociete)
	WHERE B.user='lionel' AND B.idPolice='212'
	AND(A.codeProduit='TERRABO_80')
	AND (A.codeGcAssureur='VEC') 
	AND (B.id=9138) 
	ORDER BY A.codeLienParente;
	
--	select * from primecollege_temp	
-- 3
DELETE FROM collegelienparente_temp WHERE (codeUtilisateur='lionel');
	 
	INSERT INTO collegelienparente_temp 
	(
		idCollegeTemp,
		codeSociete, 
		codeGcAssureur,
		codeReseau,
		idPolice, 
		codeUtilisateur, 
		rachatTm, 
		
		tauxRachatTm, 
		tauxPrimeRachatTm, 
		codeProduit, 
		codeLienParente,
		fraisCarte, 
		numeroOptionTarif,
		primeFamille,
		primeMin, 
		primeMax,
		tauxPrimeFraisFuneraire,
		delaiCarenceCollege,
		
		plafondBeneficiaire,
		primeParTete,
		codeCapital
	)
	SELECT 
		A.id AS idCollegeTemp,
		A.codeSociete, 
		A.codeGcAssureur,
		A.codeReseau,
		A.idPolice, 
		A.`user`, 
		A.rachatTm, 
		
		A.tauxRachatTm, 
		A.tauxPrimeRachatTm, 
		A.codeProduit, 
		B.codeLienParente,
		A.fraisCarte, 
		A.numeroOptionTarif,
		A.primeFamille,
		A.primeMin, 
		A.primeMax,
		A.tauxPrimeFraisFuneraire,
		A.delaiCarenceCollege,
		
		A.plafondBeneficiaire,
		A.primeParTete,
		"C00" AS codeCapital
	FROM college_temp A,
	lienparente B
	WHERE A.user='lionel' AND A.idPolice='212'
	AND (A.id=9138);
	
	
	UPDATE collegelienparente_temp A
	JOIN primecollege_temp B ON (B.idCollegeTemp=A.idCollegeTemp
	AND B.codeLienParente=A.codeLienParente)
	SET A.primeFamille = B.prime, A.primeBase = B.prime
	WHERE (A.idCollegeTemp=9138);
	
-- 4 --
DELETE FROM garantielienparente_temp WHERE (idCollegeTemp=9138);

INSERT INTO garantiesproductionlienparente (codeSociete,codeProduit,codeLienParente,codeGarantie,codeUtilisateur,tauxPlafond,forfaitPlafond)
SELECT DISTINCT	A.codeSociete,'TERRABO_80','F',A.codeGarantie,'lionel',A.tauxPlafond,A.forfaitPlafond 
FROM garantie A
WHERE (A.codeSociete = 'vitalis');

	
	INSERT INTO garantielienparente_temp 
	(
		codeSociete, 
		idCollegeTemp, 
		idPolice, 
		codeLienParente,
		codeGarantie, 
		optionnelle, 
		additionnelle, 
		tauxPlafond, 
		forfaitPlafond, 
		codeUtilisateur, 
		plafond, 
		ordre, 
		plafondLettre,
		champApplication,
		primeParTete,
		calculerPrime,
		plafondModifiable
	)
	SELECT
		A.codeSociete, 
		B.id AS idCollegeTemp, 
		B.idPolice, 
		C.codeLienParente, 
		A.codeGarantie, 
		A.optionnelle, 
		A.additionnelle, 
		A.tauxPlafond, 
		A.forfaitPlafond, 
		B.user, 
		ROUND((B.primeFamille*A.tauxPlafond),0)+A.forfaitPlafond AS plafond, 
		A.ordre, A.plafondLettre,
		A.champApplication,
		A.primeParTete,
		A.calculerPrime,
		A.plafondModifiable
	FROM garantie A
	JOIN college_temp B ON (B.codeSociete=A.codeSociete)
	JOIN garantiesproductionlienparente C ON (C.codeSociete=A.codeSociete 
	AND C.codeProduit='TERRABO_80' AND C.codeGarantie=A.codeGarantie)
	WHERE (B.id=9138);
	

	UPDATE garantielienparente_temp A
	JOIN college_temp B ON (B.codeSociete=A.codeSociete AND A.idCollegeTemp=B.id)
	JOIN garantiesproductionlienparente C ON (C.codeSociete=A.codeSociete 
	AND C.codeProduit='TERRABO_80' AND C.codeLienParente=A.codeLienParente AND C.codeGarantie=A.codeGarantie)
	SET A.plafond = ROUND((B.primeFamille*C.tauxPlafond),0)+C.forfaitPlafond
	WHERE (B.id=9138) 
	AND (IFNULL(B.primeFamille*C.tauxPlafond + C.forfaitPlafond, "0") != "0");	
	
 -- 5--
 DELETE FROM prestationlienparente_temp WHERE (codeUtilisateur='lionel');
	 
	INSERT INTO prestationlienparente_temp (
		codeSociete, 
		idCollegeTemp, 
		idCollege, 
		idPolice, 
		codeLienParente,
		codeProduit,
		codeReseau, 
		tm, 
		codeUtilisateur, 
		dateSysteme, 
		tmBase, 
		rachatTm,
		tauxRachatTm,
		tauxPrimeRachatTm
	)
	SELECT	A.codeSociete, 
		B.id AS idCollegeTemp, 
		B.idCollege, 
		B.idPolice, 
		A.codeLienParente,
		'TERRABO_80',
		A.codeReseau, 
		A.tm, 
		B.user, 
		NOW() AS dateSysteme, 
		A.tm AS tmBase, 
		B.rachatTm,
		B.tauxRachatTm,
		B.tauxPrimeRachatTm
	FROM vw_reseausoin_lien_parente A
	JOIN college_temp B ON (B.codeSociete=A.codeSociete)
	WHERE (B.id=9138)
	AND (A.codeProduit='TERRABO_80');	
	
	SELECT * FROM vw_reseausoin_lien_parente