SET @idcollege=0,@nbPersonneCollege=0,@primeHtCollege=0;
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_college;	 
	CREATE TEMPORARY TABLE temp_cumul_college	
	SELECT 
		fn_exercice_police(A.idPolice) AS  Exercice,
		A.codeSociete,C.codeGcAssureur,fn_libelle_garant_rp(C.codeGcAssureur) AS garant,
		IFNULL(B.libelle, "") AS produit,C.idPolice,
		A.idCollege,C.libelleCollege,
		CONCAT(C.codeProduit, " => ", C.libelleCollege, " => ",  A.codeLienParente) AS  libelleComplet,
		L.libelle AS lienparente,A.codeLienParente
		,C.codeBaseTarification,CONCAT(YEAR(A.dateEntree),"-",
		CASE WHEN LENGTH(MONTH(A.dateEntree))<2 
		THEN CONCAT("0",MONTH(A.dateEntree)) 
		ELSE MONTH(A.dateEntree) END)
		AS dateEntree
		,CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END AS primeHt,
		ROUND((CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END)
		/(TIMESTAMPDIFF(MONTH,e.dateEffet,e.dateFin)+1),0) AS primeIncorpoInd,	
		CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN COUNT(A.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.dateEntree,'2022-08-31')+1
		WHEN (C.codeBaseTarification='IND') THEN TIMESTAMPDIFF(MONTH,A.dateEntree,'2022-08-31')+1
		ELSE 0 END AS MoisRestant
		,(TIMESTAMPDIFF(MONTH,e.dateEffet,e.dateFin)+1) AS MoisDuree,e.dateEffet,e.dateFin
	FROM beneficiaire A
	JOIN police p ON (p.id=A.idPolice)
	JOIN college C ON (C.id=A.idCollege)
	JOIN entetecontrat e ON (e.idPolice=C.idPolice)
	-- JOIN contrat e ON (e.idPolice=C.idPolice)
	LEFT JOIN produit B ON (B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit)
	LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
	WHERE A.idPolice>0
	-- AND (CASE WHEN 'VS' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='VS' END)  
	-- AND (CASE WHEN p_idPolice IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice=p_idPolice END)  
	-- AND e.id = fn_id_entetecontrat_police_courant(C.idPolice)
	-- AND fn_id_entetecontrat_police(C.idPolice,CURDATE())=fn_id_entetecontrat_police_courant(C.idPolice)
	AND (A.dateEffet<='2022-08-31') 
	AND A.nomBeneficiaire NOT LIKE "%AVENANT%" AND p.codeEtatPolice NOT IN ('RE','EX','AN')
	AND (A.dateEntree BETWEEN '2022-01-01' AND '2022-08-31')
	GROUP BY C.codeBaseTarification,A.idPolice,A.idCollege,A.codeLienParente,A.dateEntree
	ORDER BY codeLienParente,libelleComplet;
	
	
	-- SELECT * FROM temp_cumul_college
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHT;
	CREATE TEMPORARY TABLE temp_cumul_colHT
	SELECT 	 codeSociete,
		codeGcAssureur,  garant,
		-- produit,
		idPolice,
		idCollege,
		libelleCollege,
		libelleComplet,
		lienparente,codeLienParente,
		codeBaseTarification,dateEntree,MoisDuree,MoisRestant,
		primeHt,
		nbPersonne,
		(primeIncorpoInd*MoisRestant) AS primeIncorpoInd,
		(primeIncorpoInd*nbPersonne*MoisRestant) AS primeHtIncorpo,
		(primeIncorpoInd*nbPersonne*MoisDuree) AS primeHtTotale	
	FROM    temp_cumul_college WHERE codeGcAssureur!='GLOBAL CI';

	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTNEWS;
	CREATE TEMPORARY TABLE temp_cumul_colHTNEWS
	SELECT
		codeGcAssureur,
		idPolice
		,SUM(primeIncorpoInd*MoisRestant) AS primeIncorpoInd
		,SUM(primeIncorpoInd*nbPersonne*MoisRestant) AS primeHtIncorpo
		,SUM(primeIncorpoInd*nbPersonne*MoisDuree) AS primeHtTotale		
	FROM    temp_cumul_college 
	WHERE codeGcAssureur!='GLOBAL CI'	
	GROUP BY codeGcAssureur,idPolice;
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTNEWS3;
	CREATE TEMPORARY TABLE temp_cumul_colHTNEWS3
	SELECT 	codeGcAssureur,
		idPolice,primeIncorpoInd,primeHtIncorpo,primeHtTotale
		,ROUND(((ROUND(primeHtIncorpo,0))*20)/100,0) AS chargement
	FROM temp_cumul_colHTNEWS
 
	 
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_sommePolice;	 
	CREATE TEMPORARY TABLE temp_cumul_sommePolice
	SELECT B.idPolice AS idPolice,
		SUM(prt.montantApayer) AS sinitre
	-- INTO 	@police,@sinistres
	FROM p_prestationactes prt 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 '2022-01-01' AND '2022-08-31')
	-- AND (CASE WHEN p_garant IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur=p_garant END)  
	-- AND (CASE WHEN p_idPolice IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=p_idPolice END)  
	AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete="vitalis")
	AND B.valide=1 AND B.okDecompte=1
	AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
	GROUP BY B.idPolice ;
	
	SELECT * FROM temp_cumul_sommePolice
	
	SELECT 	DISTINCT h.codeGcAssureur,h.idPolice,
		ROUND(h.primeHtTotale,0) AS PRIMETOTALE ,
		ROUND(h.primeHtIncorpo,0) AS PRIMENETTE ,
		h.chargement AS CHARGEMENT,
		ROUND(h.primeHtTotale-h.chargement,0) AS PROVISION
		/*,s.sinitres AS  SINISTRES,
		(ROUND(h.primeHtIncorpo-h.chargement,0)-s.sinitres) AS ECART,
		IFNULL(ROUND((s.sinitres/ROUND(h.primeHtIncorpo-h.chargemen,0))*100,2),0)  RAPPRSP*/
	-- FROM    temp_cumul_colHT t
	FROM  temp_cumul_colHTNEWS3 h -- ON (t.idPolice=h.idPolice)
	LEFT JOIN temp_cumul_sommePolice s ON (h.idPolice=s.idPolice)
	WHERE h.idPolice>0
	GROUP BY h.codeGcAssureur,h.idPolice;
	
	/*SELECT 	DISTINCT t.codeGcAssureur,t.idPolice,
		ROUND(@primeTotaleExeercice,0) AS PRIMETOTALE ,
		ROUND(@primeTotaleHT,0) AS PRIMENETTE ,
		@chargement AS CHARGEMENT,
		ROUND(@primeTotaleHT-@chargement,0) AS PROVISION
		-- ,@sinistres AS  SINISTRES,
		,sinitre AS  SINISTRES,
		-- ,@sinistres AS  SINISTRES,
		-- (ROUND(@primeTotaleHT-@chargement,0)-@sinistres) AS ECART,
		(ROUND(@primeTotaleHT-@chargement,0)-sinitre) AS ECART,
		IFNULL(ROUND((sinitre/ROUND(@primeTotaleHT-@chargement,0))*100,2),0)  RAPPRSP
-- 		IFNULL(ROUND((@sinistres/ROUND(@primeTotaleHT-@chargement,0))*100,2),0)  RAPPRSP
	FROM    temp_cumul_affichage t
	LEFT JOIN temp_cumul_sommePolice p on (p.police=t.idPolice)
	WHERE nbPersonneCol>0
	GROUP BY t.codeSociete,t.codeGcAssureur,t.idPolice;*/
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_sommePolice;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_college;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHT;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colTTC;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_affichage;