



	SET @idcollege=0,@nbPersonneCollege=0,@primeHtCollege=0;

		
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_college;	 
	CREATE TEMPORARY TABLE temp_cumul_college	
	SELECT -- A.*
		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,p.numeroPolice,p.libellePolice,
		A.idCollege,C.libelleCollege,
		CONCAT(C.codeProduit, " => ", C.libelleCollege, " => ",  A.codeLienParente) AS  libelleComplet,
		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,
		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,	
		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.dateEffet,"2022-09-30")+1
		WHEN (C.codeBaseTarification='IND') THEN TIMESTAMPDIFF(MONTH,A.dateEffet,"2022-09-30")+1
		ELSE 0 END AS MoisUtilise
		,(TIMESTAMPDIFF(MONTH,A.dateEffet,p.dateFin)+1) AS MoisDuree,p.dateEffet,p.dateFin
	FROM beneficiaire A
	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)
	-- JOIN entetecontrat 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 156 IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice=156 END) 
	AND (CASE WHEN 'VEC' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='VEC' END)  
	-- AND e.id = fn_id_entetecontrat_police_courant(A.idPolice)
	AND (A.dateEffet<="2022-09-30") 
	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 "2021-12-01" AND "2022-09-30")
	GROUP BY A.idPolice,A.codeSociete,C.codeGcAssureur,B.libelle,C.idPolice,p.numeroPolice,p.libellePolice,
		A.idCollege,C.libelleCollege,C.codeProduit,C.libelleCollege,A.codeLienParente,
		L.libelle,A.codeLienParente,C.codeBaseTarification,A.dateEffet,
		C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
	ORDER BY codeLienParente,libelleComplet;
	
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHT;
	CREATE TEMPORARY TABLE temp_cumul_colHT
	SELECT 	 	
		t.Exercice,
		t.codeGcAssureur,  
		t.garant,
		t.idPolice,t.numeroPolice,t.libellePolice,
		t.idCollege,
		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_college t
	JOIN entetecontrat e ON (e.idPolice=t.idPolice)
	WHERE t.primeIncorpoInd>0
	AND e.id=fn_id_entetecontrat_police_courant(e.idPolice)
	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_colHTNEWS;
	CREATE TEMPORARY TABLE temp_cumul_colHTNEWS
	SELECT	
		Exercice,
		codeGcAssureur,
		idPolice,
		numeroPolice,
		libellePolice,idCollege,
		SUM(primeHtIncorpo) AS primeHtIncorpo,
		SUM(primeHtTotale) AS primeHtTotale	
	FROM    temp_cumul_colHT 
	GROUP BY Exercice,codeGcAssureur,idPolice,numeroPolice,libellePolice,idCollege;
	
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTNEWS3;
	CREATE TEMPORARY TABLE temp_cumul_colHTNEWS3
	SELECT 	
		Exercice,
		codeGcAssureur,
		idPolice,numeroPolice,libellePolice,idCollege,
		primeHtIncorpo,
		primeHtTotale,
		ROUND(((ROUND(primeHtIncorpo,0))*30)/100,0) AS chargement,
		ROUND(((ROUND(primeHtIncorpo,0))*10)/100,0) AS honoraire,
		(primeHtIncorpo-ROUND(((ROUND(primeHtIncorpo,0))*30)/100,0)) AS provision
	FROM temp_cumul_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
	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 "2021-12-01" AND "2022-09-30")
	AND (CASE WHEN 156 IS NULL THEN a.idPolice IS NOT NULL ELSE a.idPolice=156 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,a.idCollege ;
	
	
	SELECT 	DISTINCT h.Exercice, h.codeGcAssureur AS CODEGARANT,
		h.idPolice AS IDPOLICE,h.numeroPolice AS NUMERO,
		h.libellePolice AS POLICE,h.idCollege,fn_libelle_collegeID(h.idCollege) AS College,
		ROUND(h.primeHtTotale,0) AS PRIMETOTALE ,fn_accessoire ('vitalis',ROUND(h.primeHtTotale,0)) AS ACCESOIRE,
		ROUND(h.primeHtIncorpo,0) AS PRIMENETTE ,
		IFNULL(h.chargement,0) AS CHARGEMENT,
		IFNULL(h.honoraire,0) AS HONORAIRE,
		IFNULL(h.provision,0) AS PROVISION,
		IFNULL(s.sinistre ,0) AS SINISTRES,
		IFNULL(ROUND(h.provision-s.sinistre,0),0) AS ECART,
		IFNULL(ROUND((s.sinistre/h.provision)*100,2),0) AS  RAPPRSP
	FROM  temp_cumul_colHTNEWS3 h  
	LEFT JOIN temp_cumul_sommePolice s ON ((h.idPolice=s.idPolice) AND (h.idCollege=s.idCollege))
	WHERE h.idPolice>0
	ORDER BY h.Exercice,h.codeGcAssureur,h.idPolice,h.idCollege;	

	DROP TEMPORARY TABLE IF EXISTS temp_cumul_college;	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHT;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTNEWS;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTNEWS3;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_sommePolice;	 

