-- ANALYSE PRIME PAR RAPPORT AUX BENEFICIAIRES
	
	SELECT codeSociete INTO @codeSociete FROM societeuser;
	
		
	-- SELECT * FROM primecollege WHERE idPolice=303
	
	-- SELECT c.codeProduit,p.codeBaseTarification BASPRO,c.codeBaseTarification PASECO,p.* FROM college c INNER JOIN produit p ON (c.codeProduit=p.codeProduit) WHERE  c.idPolice=321
	
	-- 1 --RECHERCHE DE TOUS LES BENEFICIAIRES
	DROP TEMPORARY TABLE IF EXISTS beneficiaireage_temp;	 
	CREATE TEMPORARY TABLE beneficiaireage_temp
	SELECT 
		A.* 
	FROM beneficiaire A 
	INNER JOIN adherent B ON ((A.codeSociete=B.codeSociete) AND  (B.id=A.idAdherent) AND (B.idPolice=A.idPolice))
	INNER JOIN police C ON (C.id=A.idPolice)
	INNER JOIN clients t ON ((A.codeSociete=t.codeSociete) AND  (t.id=C.idClient))	
	INNER JOIN gc_assureur Gc ON (Gc.codeGcAssureur=C.codeGcAssureur)	
	WHERE (A.idPolice>0) AND A.nomBeneficiaire NOT LIKE "%AVENANT%"
	-- AND (CASE WHEN 'I000003' IS NULL  THEN C.codeApporteur IS NOT NULL ELSE C.codeApporteur='I000003' END)
	AND (CASE WHEN 'VS' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='VS' END)  
		AND (CASE WHEN '127' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='127' END)  
	AND (CASE WHEN '2023-03-31' IS NULL THEN A.dateEntree IS NOT NULL ELSE A.dateEntree <= '2023-03-31' END); 

	DELETE FROM beneficiaireage_temp  WHERE CASE WHEN YEAR(dateSortie)>=YEAR('2023-01-01') THEN YEAR(dateSortie)<YEAR('2023-01-01')-1
						ELSE YEAR(dateSortie)<YEAR('2023-01-01') END ;
	

	
	
	IF YEAR('2023-03-31')=YEAR(CURDATE()) THEN 
		-- ANCIEN  2022-07-01	
		-- 3--RECHERCHE DES PRIMES TOTALES	
		DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeTotale;	 
		CREATE TEMPORARY TABLE temp_cumul_collegeTotale	
		SELECT DISTINCT
			fn_exercice_police(A.idPolice) AS  Exercice,
			A.codeSociete,p.codeGcAssureur,fn_libelle_garant_rp(p.codeGcAssureur) AS garant,
			fn_libelle_produit(C.codeProduit,p.codeGcAssureur) AS produit,C.idPolice,p.numeroPolice,p.libellePolice,
			A.idCollege,C.libelleCollege,
			L.libelle AS lienparente,A.codeLienParente,
			C.codeBaseTarification,CONCAT(YEAR(A.dateEffet),"-",
			CASE WHEN LENGTH(MONTH(A.dateEffet))<2 	THEN CONCAT("0",MONTH(A.dateEffet)) ELSE MONTH(A.dateEffet) END) AS dateEntree,
			-- CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END AS primeHt,
			r.prime AS primeHt,
			-- ROUND((CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END)
			-- /(TIMESTAMPDIFF(MONTH,p.dateEffet,p.dateFin)+1),0) AS primeIncorpoInd,
			ROUND(r.prime/(TIMESTAMPDIFF(MONTH,p.dateEffet,p.dateFin)+1),0) AS primeIncorpoInd,
				
			CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN COUNT(H.id) WHEN (C.codeBaseTarification='IND') THEN COUNT(A.id) ELSE 0 END AS nbPersonne,
					
			CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN TIMESTAMPDIFF(MONTH,A.dateEffet,p.dateFin)+1
			     WHEN (C.codeBaseTarification='IND') THEN TIMESTAMPDIFF(MONTH,A.dateEffet,p.dateFin)+1 ELSE 0 END AS MoisUtilise,
			(TIMESTAMPDIFF(MONTH,A.dateEffet,p.dateFin)+1) AS MoisDuree,p.dateEffet,p.dateFin
		FROM beneficiaire A 
		JOIN beneficiaireage_temp t ON (t.id=A.id)
		JOIN adherent H ON ((A.codeSociete=H.codeSociete) AND  (H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
		JOIN police p ON (p.id=A.idPolice)
		JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
		JOIN college C ON (C.id=A.idCollege)
		-- LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit)  AND (B.codeBaseTarification=C.codeBaseTarification))
		LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
		LEFT JOIN primecollege r ON ((r.codeLienParente=A.codeLienParente) AND (r.idPolice=p.id) AND (r.idCollege=A.idCollege))
		WHERE A.idPolice>0
		AND (CASE WHEN 'I000003' IS NULL  THEN p.codeApporteur IS NOT NULL ELSE p.codeApporteur='I000003' END)
		/*AND (CASE WHEN 'VS' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='VS' END)  
		AND (CASE WHEN '127' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='127' END)  */ 		 
		AND A.nomBeneficiaire NOT LIKE "%AVENANT%"  AND g.id!='45' AND p.idClient!='49' 
		GROUP BY A.idPolice,A.codeSociete,p.codeGcAssureur,C.idPolice,p.numeroPolice,p.libellePolice,  -- ,B.libelle,
			A.idCollege,C.libelleCollege,C.codeProduit,C.libelleCollege,A.codeLienParente,
			L.libelle,A.codeLienParente,A.dateEffet,
			C.codeBaseTarification,C.primeFamille,C.primeBeneficiaire,p.dateEffet,p.dateFin	
		ORDER BY codeGcAssureur,idPolice,codeLienParente;
		
	-- 1--RECHERCHE DES BENEFICIAIRES A ENCAISERR	
		DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeEncaisse;	 
		CREATE TEMPORARY TABLE temp_cumul_collegeEncaisse	
		SELECT 
			fn_exercice_police(A.idPolice) AS  Exercice,
			A.codeSociete,p.codeGcAssureur,fn_libelle_garant_rp(p.codeGcAssureur) AS garant,
			fn_libelle_produit(C.codeProduit,p.codeGcAssureur) AS produit,C.idPolice,p.numeroPolice,p.libellePolice,
			A.idCollege,C.libelleCollege,
			L.libelle AS lienparente,A.codeLienParente,
			C.codeBaseTarification,CONCAT(YEAR(A.dateEffet),"-",
			CASE WHEN LENGTH(MONTH(A.dateEffet))<2 	THEN CONCAT("0",MONTH(A.dateEffet)) ELSE MONTH(A.dateEffet) END) AS dateEntree,
			-- CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END AS primeHt,
			r.prime AS primeHt,
			-- ROUND((CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END)
			-- /(TIMESTAMPDIFF(MONTH,p.dateEffet,p.dateFin)+1),0) AS primeIncorpoInd,
			ROUND(r.prime/(TIMESTAMPDIFF(MONTH,p.dateEffet,p.dateFin)+1),0) AS primeIncorpoInd,
			
			CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN COUNT(H.id)
			WHEN (C.codeBaseTarification='IND') THEN COUNT(A.id)
			ELSE 0 END AS nbPersonne,
			CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN TIMESTAMPDIFF(MONTH,A.dateEffet,'2023-03-31')+1
			WHEN (C.codeBaseTarification='IND') THEN TIMESTAMPDIFF(MONTH,A.dateEffet,'2023-03-31')+1
			ELSE 0 END AS MoisUtilise
			,(TIMESTAMPDIFF(MONTH,A.dateEffet,p.dateFin)+1) AS MoisDuree,p.dateEffet,p.dateFin
		FROM beneficiaire A 
		JOIN beneficiaireage_temp t ON (t.id=A.id)
		JOIN adherent H ON ((A.codeSociete=H.codeSociete) AND  (H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
		JOIN police p ON (p.id=A.idPolice)
		JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
		JOIN college C ON (C.id=A.idCollege)
		-- LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit)  AND (B.codeBaseTarification=C.codeBaseTarification))
		LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
		LEFT JOIN primecollege r ON ((r.codeLienParente=A.codeLienParente) AND (r.idPolice=p.id) AND (r.idCollege=A.idCollege))
		WHERE A.idPolice>0
				AND (CASE WHEN 'I000003' IS NULL  THEN p.codeApporteur IS NOT NULL ELSE p.codeApporteur='I000003' END)
		/*AND (CASE WHEN 'VS' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='VS' END)  
		AND (CASE WHEN '127' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='127' END)  */ 
		AND A.nomBeneficiaire NOT LIKE "%AVENANT%"    AND g.id!='45' AND p.idClient!='49' 
		AND p.codeEtatPolice NOT IN ('RE','EX','AN') 
		AND (A.dateEffet BETWEEN '2023-01-01' AND '2023-03-31')
		GROUP BY A.idPolice,A.codeSociete,p.codeGcAssureur,C.idPolice,p.numeroPolice,p.libellePolice,  -- ,B.libelle
			A.idCollege,C.libelleCollege,C.codeProduit,C.libelleCollege,A.codeLienParente,
			L.libelle,A.codeLienParente,A.dateEffet,
			C.codeBaseTarification,C.primeFamille,C.primeBeneficiaire,p.dateEffet,p.dateFin	
		ORDER BY codeGcAssureur,idPolice,codeLienParente;
		
	-- 2--RECHERCHE DES BENEFICIAIRES A RISTOURNER	
		DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeRistourne;	 
		CREATE TEMPORARY TABLE temp_cumul_collegeRistourne	
		SELECT 
			fn_exercice_police(A.idPolice) AS  Exercice,
			A.codeSociete,p.codeGcAssureur,fn_libelle_garant_rp(p.codeGcAssureur) AS garant,
			fn_libelle_produit(C.codeProduit,p.codeGcAssureur) AS produit,C.idPolice,p.numeroPolice,p.libellePolice,
			A.idCollege,C.libelleCollege,
			L.libelle AS lienparente,A.codeLienParente,
			C.codeBaseTarification,CONCAT(YEAR(A.dateSortie),"-",
			CASE WHEN LENGTH(MONTH(A.dateSortie))<2 THEN CONCAT("0",MONTH(A.dateSortie)) ELSE MONTH(A.dateSortie) END) AS dateEntree,
			-- CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END AS primeHt,
			r.prime AS primeHt,
			-- ROUND((CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END)
			-- /(TIMESTAMPDIFF(MONTH,p.dateEffet,p.dateFin)+1),0) AS primeIncorpoInd,
			ROUND(r.prime/(TIMESTAMPDIFF(MONTH,p.dateEffet,p.dateFin)+1),0) AS primeIncorpoInd,
				
			CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN COUNT(H.id)
			WHEN (C.codeBaseTarification='IND') THEN COUNT(A.id)
			ELSE 0 END AS nbPersonne,
			CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN TIMESTAMPDIFF(MONTH,A.dateSortie,'2023-03-31')
			WHEN (C.codeBaseTarification='IND') THEN TIMESTAMPDIFF(MONTH,A.dateSortie,'2023-03-31')
			ELSE 0 END AS MoisUtilise
			,(TIMESTAMPDIFF(MONTH,A.dateSortie,p.dateFin)) AS MoisDuree,p.dateEffet,p.dateFin
		FROM beneficiaire A 
		JOIN beneficiaireage_temp t ON (t.id=A.id)
		JOIN adherent H ON ((A.codeSociete=H.codeSociete) AND  (H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
		JOIN police p ON (p.id=A.idPolice)
		JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
		JOIN college C ON (C.id=A.idCollege)
		LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit)  AND (B.codeBaseTarification=C.codeBaseTarification))
		LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
		LEFT JOIN primecollege r ON ((r.codeLienParente=A.codeLienParente) AND (r.idPolice=p.id) AND (r.idCollege=A.idCollege))
		WHERE  A.id<>0  
		AND (CASE WHEN 'I000003' IS NULL  THEN p.codeApporteur IS NOT NULL ELSE p.codeApporteur='I000003' END)
		/*AND (CASE WHEN 'VS' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='VS' END)  
		AND (CASE WHEN '127' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='127' END)  */ 
		AND (CASE WHEN 'R' IS NULL THEN A.codeEtatBeneficiaire IS NOT NULL ELSE A.codeEtatBeneficiaire IN ('R','NR') END)  --  pour les retires
		AND CASE WHEN A.sorti = "1" THEN A.dateSortie <='2023-03-31' ELSE (A.dateSortie IS NULL OR A.dateSortie=(NULL)) END 	
		AND A.nomBeneficiaire NOT LIKE "%AVENANT%" AND p.codeEtatPolice NOT IN ('RE','EX','AN') AND g.id!='45' AND p.idClient!='49' 
		AND (A.dateSortie BETWEEN '2023-01-01' AND '2023-03-31')	
		-- AND A.idPolice=302
		AND  (LEFT(A.dateEffet,10) BETWEEN p.dateEffet AND p.dateFin)
		GROUP BY A.idPolice,A.codeSociete,p.codeGcAssureur,C.idPolice,p.numeroPolice,p.libellePolice,  -- ,B.libelle
			A.idCollege,C.libelleCollege,C.codeProduit,C.libelleCollege,A.codeLienParente,
			L.libelle,A.codeLienParente,A.dateSortie,
			C.codeBaseTarification,C.primeFamille,C.primeBeneficiaire,p.dateEffet,p.dateFin	
		ORDER BY codeGcAssureur,idPolice,codeLienParente;	
		
   ELSE
		
		-- GESTION DE L'HISTORIQUE DES BENEFICIAIRES
		
		SELECT  dateEffet,dateFin INTO @dateEffet,@dateFin FROM  entetecontrat A WHERE A.idPolice>0 AND A.exercice=YEAR('2023-03-31')
		AND (CASE WHEN p_idPolice IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice=p_idPolice END)  ;
			DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeTotale;	 
			CREATE TEMPORARY TABLE temp_cumul_collegeTotale	
			SELECT 
				YEAR(@dateFin) AS  Exercice,
				A.codeSociete,p.codeGcAssureur,fn_libelle_garant_rp(p.codeGcAssureur) AS garant,
				IFNULL(B.libelle, "") AS produit,C.idPolice,p.numeroPolice,p.libellePolice,
				A.idCollege,C.libelleCollege,L.libelle AS lienparente,A.codeLienParente,C.codeBaseTarification,				
				CONCAT(YEAR((CASE WHEN YEAR(A.dateEntree)<YEAR(@dateFin) THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END)),
				"-",CASE WHEN LENGTH(MONTH((CASE WHEN YEAR(A.dateEntree)<YEAR(@dateFin) THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END)))<2 
				THEN CONCAT("0",MONTH((CASE WHEN YEAR(A.dateEntree)<YEAR(@dateFin) THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END))) ELSE MONTH(A.dateEntree) END) 
				AS dateEntree,
				r.prime AS primeHt,
				ROUND(r.prime/(TIMESTAMPDIFF(MONTH,@dateEffet,@dateFin)+1),0) AS primeIncorpoInd,
				CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN COUNT(H.id) WHEN (C.codeBaseTarification='IND') THEN COUNT(A.id) ELSE 0 END AS nbPersonne,						
				CASE 
				WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') 
				THEN TIMESTAMPDIFF(MONTH,(CASE WHEN YEAR(A.dateEntree)<YEAR(@dateFin) THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END),@dateFin)+1
				WHEN (C.codeBaseTarification='IND') 
				THEN TIMESTAMPDIFF(MONTH,(CASE WHEN YEAR(A.dateEntree)<YEAR(@dateFin) THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END),@dateFin)+1 ELSE 0 END AS MoisUtilise,
				(TIMESTAMPDIFF(MONTH,@dateEffet,@dateFin)+1) AS MoisDuree,@dateEffet dateEffet,@dateFin dateFin
			FROM beneficiaire A 
			JOIN beneficiaireage_temp t ON (t.id=A.id)
			JOIN adherent H ON ((H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
			JOIN police p ON ((p.id=A.idPolice))
			JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
			JOIN college C ON (C.id=A.idCollege)
			LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit) AND (B.codeBaseTarification=C.codeBaseTarification) ) 
			LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
			LEFT JOIN primecollege r ON ((r.codeLienParente=A.codeLienParente) AND (r.idPolice=p.id) AND (r.idCollege=A.idCollege))
			WHERE A.idPolice>0
			AND (CASE WHEN p_garant IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur=p_garant END)  
			AND (CASE WHEN p_idPolice IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice=p_idPolice END)  
			AND (CASE WHEN p_codeApporteur IS NULL  THEN p.codeApporteur IS NOT NULL ELSE p.codeApporteur=p_codeApporteur END) 
			AND A.nomBeneficiaire NOT LIKE "%AVENANT%"  AND g.typeGarant!='1' AND g.id!='45' AND p.idClient!='49' 
			AND (CASE WHEN @dateFin IS NULL THEN A.dateEntree IS NOT NULL ELSE A.dateEntree<=@dateFin END)
			GROUP BY A.idPolice,A.codeSociete,p.codeGcAssureur,B.libelle,C.idPolice,p.numeroPolice,p.libellePolice,
				A.idCollege,C.libelleCollege,C.codeProduit,C.libelleCollege,A.codeLienParente,
				L.libelle,A.codeLienParente,A.dateEntree,r.prime,
				C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
			ORDER BY codeGcAssureur,idPolice,codeLienParente;
		
		-- 1--RECHERCHE DES BENEFICIAIRES A ENCAISERR	
			DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeEncaisse;	 
			CREATE TEMPORARY TABLE temp_cumul_collegeEncaisse	
			SELECT 
				YEAR('2023-03-31') AS  Exercice,
				A.codeSociete,p.codeGcAssureur,fn_libelle_garant_rp(p.codeGcAssureur) AS garant,
				IFNULL(B.libelle, "") AS produit,C.idPolice,p.numeroPolice,p.libellePolice,
				A.idCollege,C.libelleCollege,L.libelle AS lienparente,A.codeLienParente,C.codeBaseTarification,				
				CONCAT(YEAR((CASE WHEN YEAR(A.dateEntree)<YEAR('2023-03-31') THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END)),
				"-",CASE WHEN LENGTH(MONTH((CASE WHEN YEAR(A.dateEntree)<YEAR('2023-03-31') THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END)))<2 
				THEN CONCAT("0",MONTH((CASE WHEN YEAR(A.dateEntree)<YEAR('2023-03-31') THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END))) ELSE MONTH(A.dateEntree) END) 
				AS dateEntree,
				r.prime AS primeHt,
				ROUND(r.prime/(TIMESTAMPDIFF(MONTH,@dateEffet,@dateFin)+1),0) AS primeIncorpoInd,
				CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN COUNT(H.id) WHEN (C.codeBaseTarification='IND') THEN COUNT(A.id) ELSE 0 END AS nbPersonne,						
				CASE 
				WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') 
				THEN TIMESTAMPDIFF(MONTH,(CASE WHEN YEAR(A.dateEntree)<YEAR('2023-03-31') THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END),'2023-03-31')+1
				WHEN (C.codeBaseTarification='IND') 
				THEN TIMESTAMPDIFF(MONTH,(CASE WHEN YEAR(A.dateEntree)<YEAR('2023-03-31') THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END),'2023-03-31')+1 ELSE 0 END AS MoisUtilise,
				(TIMESTAMPDIFF(MONTH,@dateEffet,@dateFin)+1) AS MoisDuree,@dateEffet dateEffet,@dateFin dateFin
			FROM beneficiaire A 
			JOIN beneficiaireage_temp t ON (t.id=A.id)
			JOIN adherent H ON ((H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
			JOIN police p ON ((p.id=A.idPolice))
			JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
			JOIN college C ON (C.id=A.idCollege)
			LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit) AND (B.codeBaseTarification=C.codeBaseTarification) ) 
			LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
			LEFT JOIN primecollege r ON ((r.codeLienParente=A.codeLienParente) AND (r.idPolice=p.id) AND (r.idCollege=A.idCollege))
			WHERE A.idPolice>0
			AND (CASE WHEN p_garant IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur=p_garant END)  
			AND (CASE WHEN p_idPolice IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice=p_idPolice END)  
			AND (CASE WHEN p_codeApporteur IS NULL  THEN p.codeApporteur IS NOT NULL ELSE p.codeApporteur=p_codeApporteur END) 
			AND A.nomBeneficiaire NOT LIKE "%AVENANT%"  AND g.typeGarant!='1' AND g.id!='45' AND p.idClient!='49' 
			AND (CASE WHEN '2023-03-31' IS NULL THEN A.dateEntree IS NOT NULL ELSE A.dateEntree<='2023-03-31' END)
			GROUP BY A.idPolice,A.codeSociete,p.codeGcAssureur,B.libelle,C.idPolice,p.numeroPolice,p.libellePolice,
				A.idCollege,C.libelleCollege,C.codeProduit,C.libelleCollege,A.codeLienParente,
				L.libelle,A.codeLienParente,A.dateEntree,r.prime,
				C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
			ORDER BY codeGcAssureur,idPolice,codeLienParente;
			DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeRistourne;	 
			CREATE TEMPORARY TABLE temp_cumul_collegeRistourne	
			SELECT 
				YEAR('2023-03-31') AS  Exercice,
				A.codeSociete,p.codeGcAssureur,fn_libelle_garant_rp(p.codeGcAssureur) AS garant,
				IFNULL(B.libelle, "") AS produit,C.idPolice,p.numeroPolice,p.libellePolice,
				A.idCollege,C.libelleCollege,L.libelle AS lienparente,A.codeLienParente,C.codeBaseTarification,				
				CONCAT(YEAR((CASE WHEN YEAR(A.dateSortie)<YEAR('2023-03-31') THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateSortie END)),
				"-",CASE WHEN LENGTH(MONTH((CASE WHEN YEAR(A.dateSortie)<YEAR('2023-03-31') THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateSortie END)))<2 
				THEN CONCAT("0",MONTH((CASE WHEN YEAR(A.dateSortie)<YEAR('2023-03-31') THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateSortie END))) ELSE MONTH(A.dateEntree) END) 
				AS dateEntree,r.prime AS primeHt,
				ROUND(r.prime/(TIMESTAMPDIFF(MONTH,@dateEffet,@dateFin)+1),0) AS primeIncorpoInd,
				CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN COUNT(H.id) WHEN (C.codeBaseTarification='IND') THEN COUNT(A.id) ELSE 0 END AS nbPersonne,						
				CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') 
				THEN TIMESTAMPDIFF(MONTH,(CASE WHEN YEAR(A.dateSortie)<YEAR('2023-03-31') THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateSortie END),'2023-03-31')
				     WHEN (C.codeBaseTarification='IND') 
				THEN TIMESTAMPDIFF(MONTH,(CASE WHEN YEAR(A.dateSortie)<YEAR('2023-03-31') THEN DATE_ADD(CONCAT(YEAR('2023-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateSortie END),'2023-03-31') ELSE 0 END AS MoisUtilise,
				(TIMESTAMPDIFF(MONTH,@dateEffet,@dateFin)+1) AS MoisDuree,@dateEffet dateEffet,@dateFin dateFin				
			FROM beneficiaire A 
			JOIN beneficiaireage_temp t ON (t.id=A.id)
			JOIN adherent H ON ((H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
			JOIN police p ON ((p.id=A.idPolice))
			JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
			JOIN college C ON (C.id=A.idCollege)
			LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit) AND (B.codeBaseTarification=C.codeBaseTarification) ) 
			LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
			LEFT JOIN primecollege r ON ((r.codeLienParente=A.codeLienParente) AND (r.idPolice=p.id) AND (r.idCollege=A.idCollege))
			WHERE  A.id<>0  
			AND (CASE WHEN p_garant IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur=p_garant END)  
			AND (CASE WHEN p_idPolice IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice=p_idPolice END)  
			AND (CASE WHEN p_codeApporteur IS NULL  THEN p.codeApporteur IS NOT NULL ELSE p.codeApporteur=p_codeApporteur END) 
			AND CASE WHEN A.sorti = "1" THEN A.dateSortie <='2023-03-31' ELSE (A.dateSortie IS NULL OR A.dateSortie=(NULL)) END 	
			AND A.nomBeneficiaire NOT LIKE "%AVENANT%" AND g.id!='45' AND p.idClient!='49' 
			AND (A.dateSortie BETWEEN '2023-01-01' AND '2023-03-31')	
			AND (CASE WHEN '2023-03-31' IS NULL THEN A.dateEntree IS NOT NULL ELSE A.dateEntree<='2023-03-31' END)
			GROUP BY A.idPolice,A.codeSociete,p.codeGcAssureur,B.libelle,C.idPolice,p.numeroPolice,p.libellePolice,
				A.idCollege,C.libelleCollege,C.codeProduit,C.libelleCollege,A.codeLienParente,
				L.libelle,A.codeLienParente,A.dateSortie,A.dateEntree,r.prime,
				C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
			ORDER BY codeGcAssureur,idPolice,codeLienParente;	
	
	END IF;
	
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTTotale;
	CREATE TEMPORARY TABLE temp_cumul_colHTTotale
	SELECT 	 	
		t.Exercice,
		t.codeGcAssureur,  
		t.garant,
		t.idPolice,t.numeroPolice,t.libellePolice,
		t.idCollege,primeHt,t.codeBaseTarification,
		t.primeIncorpoInd,t.nbPersonne,
		SUM(t.primeIncorpoInd*t.nbPersonne*MoisDuree) AS primeHtTotale,
		SUM(t.primeIncorpoInd*t.nbPersonne*MoisUtilise) AS primeHtIncorpo
	FROM temp_cumul_collegeTotale t
	-- WHERE t.primeIncorpoInd>0
	-- WHERE t.idPolice=302
	GROUP BY t.Exercice,t.codeGcAssureur,t.garant,t.idPolice,t.numeroPolice,t.libellePolice,t.idCollege,t.codeBaseTarification,primeHt,	
	t.primeIncorpoInd,t.nbPersonne;
	
	
	-- select * from temp_cumul_colHTTotale  -- primeHt,t.codeBaseTarification,
	
	
	-- SELECT * FROM temp_cumul_colHTTotale t WHERE t.idPolice=302
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTEncaisse;
	CREATE TEMPORARY TABLE temp_cumul_colHTEncaisse
	SELECT 	 	
		t.Exercice,
		t.codeGcAssureur,  
		t.garant,
		t.idPolice,t.numeroPolice,t.libellePolice,
		t.idCollege,primeHt,t.codeBaseTarification,
		t.primeIncorpoInd,t.nbPersonne,
		SUM(t.primeIncorpoInd*t.nbPersonne*MoisDuree) AS primeHtTotale,
		SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise) AS primeHtIncorpo
	FROM    temp_cumul_collegeEncaisse t
	-- WHERE t.primeIncorpoInd>0
	GROUP BY t.Exercice,t.codeGcAssureur,t.garant,t.idPolice,t.numeroPolice,t.libellePolice,t.idCollege,t.codeBaseTarification,	
	t.primeIncorpoInd,t.nbPersonne;
	
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTRistourne;
	CREATE TEMPORARY TABLE temp_cumul_colHTRistourne
	SELECT 	 	
		t.Exercice,
		t.codeGcAssureur,  
		t.garant,
		t.idPolice,t.numeroPolice,t.libellePolice,
		t.idCollege,primeHt,t.codeBaseTarification,
		t.primeIncorpoInd,t.nbPersonne,
		SUM(t.primeIncorpoInd*t.nbPersonne*MoisDuree) AS primeHtTotale,
		SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise) AS primeHtIncorpo
	FROM  temp_cumul_collegeRistourne t
	-- WHERE t.primeIncorpoInd>0
	GROUP BY t.Exercice,t.codeGcAssureur,t.garant,t.idPolice,t.numeroPolice,t.libellePolice,t.idCollege,t.codeBaseTarification,	
	t.primeIncorpoInd,t.nbPersonne;
	
	-- select * from temp_cumul_colHTTotale
	DROP TEMPORARY TABLE IF EXISTS temp_cumulTotale_colHTNEWS;
	CREATE TEMPORARY TABLE temp_cumulTotale_colHTNEWS
	SELECT	
		Exercice,
		codeGcAssureur,
		idPolice,numeroPolice,libellePolice,
		idCollege,fn_libelle_collegeID(idCollege) AS college,primeHt,CodeBaseTarification,
		SUM(nbPersonne) AS beneficiaire,
		SUM(primeHtIncorpo) AS primeHtIncorpo,
		SUM(primeHtTotale) AS primeHtTotale		
	FROM    temp_cumul_colHTTotale WHERE nbPersonne>0
	GROUP BY Exercice,codeGcAssureur,idPolice,numeroPolice,libellePolice,primeHt,CodeBaseTarification,idCollege;	
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumulEncaisse_colHTNEWS;
	CREATE TEMPORARY TABLE temp_cumulEncaisse_colHTNEWS
	SELECT	
		Exercice,
		codeGcAssureur,
		idPolice,numeroPolice,libellePolice,
		idCollege,fn_libelle_collegeID(idCollege) AS college,
		SUM(nbPersonne) AS beneficiaire,
		SUM(primeHtIncorpo) AS primeHtIncorpo,
		SUM(primeHtTotale) AS primeHtTotale	
	FROM    temp_cumul_colHTEncaisse 
	GROUP BY Exercice,codeGcAssureur,idPolice,numeroPolice,libellePolice,idCollege;
	
 			
	DROP TEMPORARY TABLE IF EXISTS temp_cumulRistourne_colHTNEWS;
	CREATE TEMPORARY TABLE temp_cumulRistourne_colHTNEWS
	SELECT	
		Exercice,
		codeGcAssureur,
		idPolice,numeroPolice,libellePolice,
		idCollege,fn_libelle_collegeID(idCollege) AS college,
		SUM(nbPersonne) AS beneficiaire,
		SUM(primeHtIncorpo) AS primeHtIncorpo,
		SUM(primeHtTotale) AS primeHtTotale	
	FROM    temp_cumul_colHTRistourne 
	GROUP BY Exercice,codeGcAssureur,idPolice,numeroPolice,libellePolice,idCollege;
	
	
	/*DROP TEMPORARY TABLE IF EXISTS temp_cumulEncaisse_colHTNEWS3;
	CREATE TEMPORARY TABLE temp_cumulEncaisse_colHTNEWS3
	SELECT 	
		Exercice,
		codeGcAssureur,
		idPolice,numeroPolice,libellePolice,
		idCollege,fn_libelle_collegeID(idCollege) AS college,
		beneficiaire,
		primeHtIncorpo,
		primeHtTotale,
		ROUND(((ROUND(primeHtIncorpo,0))*30)/100,0) AS chargement,
		(primeHtIncorpo-ROUND(((ROUND(primeHtIncorpo,0))*30)/100,0)) AS provision
	FROM temp_cumulEncaisse_colHTNEWS;
	
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumulRistourne_colHTNEWS3;
	CREATE TEMPORARY TABLE temp_cumulRistourne_colHTNEWS3
	SELECT 	
		Exercice,
		codeGcAssureur,
		idPolice,numeroPolice,libellePolice,
		idCollege,fn_libelle_collegeID(idCollege) AS college,
		beneficiaire,
		primeHtIncorpo,
		primeHtTotale,
		ROUND(((ROUND(primeHtIncorpo,0))*30)/100,0) AS chargement,
		(primeHtIncorpo-ROUND(((ROUND(primeHtIncorpo,0))*30)/100,0)) AS provision
	FROM temp_cumulRistourne_colHTNEWS;*/
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_sommePolice;	 
	CREATE TEMPORARY TABLE temp_cumul_sommePolice
	SELECT 
		B.idPolice AS idPolice,a.idCollege,IFNULL(SUM(prt.montantApayer),0) AS sinistre
		-- IFNULL(SUM(prt.montantApayer),0) AS sinistre
	FROM p_prestationactes prt 
	INNER JOIN p_facture B ON (B.id=prt.idfacture)
	INNER JOIN police C ON  (B.idPolice = C.id)
	INNER JOIN gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)
	INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
	WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-03-31')
	AND (CASE WHEN 'I000003' IS NULL  THEN C.codeApporteur IS NOT NULL ELSE C.codeApporteur='I000003' END)
	/*AND (CASE WHEN 'VS' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='VS' END)  
	AND (CASE WHEN '127' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='127' END)  */
	AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete=@codeSociete )
	AND B.valide=1 AND B.okDecompte=1  -- and B.idPolice=302
	AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
	GROUP BY B.idPolice,a.idCollege ;
	
	/*SELECT IFNULL(g.gcTauxTva,0),IFNULL(p.tauxCommission,0),IFNULL(p.tauxCommissionGerant,0)
	INTO 
		@tauxTva,@tauxCourtier,@tauxHonoraire
	FROM gc_assureur g
	INNER JOIN police p ON (p.codeGcAssureur=g.codeGcAssureur)
	WHERE p.id>0
	AND (CASE WHEN 'I000003' IS NULL  THEN C.codeApporteur IS NOT NULL ELSE C.codeApporteur='I000003' END)
	AND (CASE WHEN 'VS' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='VS' END)  
	AND (CASE WHEN '127' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='127' END)      
	LIMIT 1;*/
	

	
	/*DROP TEMPORARY TABLE IF EXISTS temp_honoCourtierEncaisse;	 
	CREATE TEMPORARY TABLE temp_honoCourtierEncaisse
	SELECT h.idPolice,h.idcollege,ROUND((h.primeHtIncorpo*@tauxCourtier)/100,0) AS honoCourtierEncaisse
		  ,ROUND((h.primeHtIncorpo*@tauxHonoraire)/100,0) AS honoGestionnaireEncaisse
		  ,ROUND(((ROUND((h.primeHtIncorpo*@tauxHonoraire)/100,0))*@tauxTva)/100,0) AS montantTVAEncaisse
		  ,(ROUND((h.primeHtIncorpo*@tauxCourtier)/100,0)+ROUND((h.primeHtIncorpo*@tauxHonoraire)/100,0)+
		  ROUND(((ROUND((h.primeHtIncorpo*@tauxHonoraire)/100,0))*@tauxTva)/100,0)) AS chargementEncaisse
	FROM  temp_cumulEncaisse_colHTNEWS3 h  ;
	
	
	DROP TEMPORARY TABLE IF EXISTS temp_honoCourtierRistoune;	 
	CREATE TEMPORARY TABLE temp_honoCourtierRistoune
	SELECT h.idPolice,h.idcollege,ROUND((h.primeHtIncorpo*@tauxCourtier)/100,0) AS honoCourtierRistoune
		  ,ROUND((h.primeHtIncorpo*@tauxHonoraire)/100,0) AS honoGestionnaireRistoune
		  ,ROUND(((ROUND((h.primeHtIncorpo*@tauxHonoraire)/100,0))*@tauxTva)/100,0) AS montantTVARistoune
		  ,(ROUND((h.primeHtIncorpo*@tauxCourtier)/100,0)+ROUND((h.primeHtIncorpo*@tauxHonoraire)/100,0)+
		  ROUND(((ROUND((h.primeHtIncorpo*@tauxHonoraire)/100,0))*@tauxTva)/100,0)) AS chargementRistourne
	FROM  temp_cumulRistourne_colHTNEWS3 h  ;*/
	

	
	DROP TEMPORARY TABLE IF EXISTS temp_rsp;	 
	CREATE TEMPORARY TABLE temp_rsp 
	SELECT 	DISTINCT h.Exercice, h.codeGcAssureur AS CODEGARANT,
		h.idPolice AS IDPOLICE,h.numeroPolice AS NUMERO,
		h.libellePolice AS POLICE,
		h.idCollege AS IDCOLLEGE,`fn_libelle_collegeID`(h.idCollege) AS COLLEGE,
		t.primeHt,t.codeBaseTarification,
		t.beneficiaire AS EFFECTIF,
		IFNULL(t.primeHtTotale,0) AS PRIMETOTALE ,
		ROUND(IFNULL(h.primeHtIncorpo,0)-IFNULL(r.primeHtIncorpo,0),0) AS PRIMENETTE ,
		-- CASE WHEN 30=0 THEN 0 ELSE  IFNULL(IFNULL(o.chargementEncaisse,0)-IFNULL(i.chargementRistourne,0),0) END AS CHARGEMENT,
		-- ROUND((ROUND(IFNULL(h.primeHtIncorpo,0)-IFNULL(r.primeHtIncorpo,0),0))-(IFNULL(o.chargementEncaisse,0)-IFNULL(i.chargementRistourne,0)),0) AS PROVISION,
		IFNULL(s.sinistre,0) AS SINISTRES
		-- (ROUND((ROUND(IFNULL(h.primeHtIncorpo,0)-IFNULL(r.primeHtIncorpo,0),0) )-(IFNULL(o.chargementEncaisse,0)-IFNULL(i.chargementRistourne,0)),0)-IFNULL(s.sinistre ,0)) AS ECART
		-- IFNULL(ROUND((IFNULL(s.sinistre,0)/(ROUND(IFNULL(h.primeHtIncorpo,0)-IFNULL(r.primeHtIncorpo,0),0)))*100,2),0) AS  RAPPRSP
	FROM  temp_cumulEncaisse_colHTNEWS3 h  
	LEFT JOIN temp_cumulRistourne_colHTNEWS3 r ON ((h.idPolice=r.idPolice)AND (h.idCollege=r.idCollege))
	LEFT JOIN temp_cumul_sommePolice s ON ((h.idPolice=s.idPolice)AND (h.idCollege=s.idCollege))
	LEFT JOIN temp_cumulTotale_colHTNEWS t ON ((h.idPolice=t.idPolice)AND (h.idCollege=t.idCollege))
	-- LEFT JOIN temp_honoCourtierEncaisse o ON ((h.idPolice=o.idPolice)AND (h.idCollege=o.idCollege))
	-- LEFT JOIN temp_honoCourtierRistoune i ON ((h.idPolice=i.idPolice)AND (h.idCollege=i.idCollege))
	WHERE h.idPolice>0
	ORDER BY h.Exercice,h.codeGcAssureur,h.idPolice,h.idCollege;
	
-- SELECT * FROM temp_rsp
	
	SELECT  t.*,
		IFNULL(ROUND((IFNULL(t.SINISTRES,0)/t.PRIMENETTE)*100,2),0) AS RspAnalyse,
		IFNULL(ROUND((IFNULL(t.SINISTRES,0)/t.PRIMETOTALE)*100,2),0) AS RspAnnuel,
		IFNULL(A.tauxAjustement,0) AS AjustAnalyse,
		IFNULL(B.tauxAjustement,0) AS AjustAnnuel	
	 FROM temp_rsp t
	 LEFT JOIN tableauajustement A ON (t.CODEGARANT = A.codeGcAssureur)
	 LEFT JOIN tableauajustement B ON (t.CODEGARANT = B.codeGcAssureur)
	 WHERE IFNULL(ROUND((IFNULL(t.SINISTRES,0)/t.PRIMENETTE)*100,2),0) BETWEEN A.spMin AND A.spMax   
	 AND   IFNULL(ROUND((IFNULL(t.SINISTRES,0)/t.PRIMETOTALE)*100,2),0)    BETWEEN B.spMin AND B.spMax
	ORDER BY t.CODEGARANT;
	
-- 	select * from temp_cumulEncaisse_colHTNEWS3 where idPolice=303
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeTotale;	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTTotale;
	DROP TEMPORARY TABLE IF EXISTS temp_cumulTotale_colHTNEWS;
	DROP TEMPORARY TABLE IF EXISTS temp_cumulRistourne_colHTNEWS3;
	DROP TEMPORARY TABLE IF EXISTS temp_cumulEncaisse_colHTNEWS3;
	DROP TEMPORARY TABLE IF EXISTS temp_cumulRistourne_colHTNEWS;
	DROP TEMPORARY TABLE IF EXISTS temp_cumulEncaisse_colHTNEWS;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTRistourne;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTEncaisse;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeRistourne;	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeEncaisse;
	DROP TEMPORARY TABLE IF EXISTS temp_honoCourtierEncaisse;
	DROP TEMPORARY TABLE IF EXISTS temp_honoCourtierRistoune;	
