SELECT a.codeGcAssureur,a.garant,
a.idPolice,a.numeroPolice,a.libellePolice,e.dateEffet,e.dateFin,C.id AS idCollege,C.libelleCollege,u.codeProduit,

	C.codeBaseTarification AS BaseTarification,CASE WHEN C.codeBaseTarification='IND' THEN  u.primeIndividu ELSE u.primeFamille END  AS Prime,
	fn_nb_statut_police_SansCol_rp(C.idPolice,C.id,'2022-12-31','A') AS aSS,
	
		CASE WHEN C.codeBaseTarification='IND' 
		THEN (fn_nb_beneficiaire_police2_SansCol_rp(C.idPolice,C.id,'2022-12-31'))
		ELSE (fn_nb_statut_police_SansCol_rp(C.idPolice,C.id,'2022-12-31','A')) END AS decompte,
		
		CASE WHEN C.codeBaseTarification='IND' 
		THEN (fn_nb_beneficiaire_police2_SansCol_rp(C.idPolice,C.id,'2022-12-31') * u.primeIndividu )
		ELSE (fn_nb_statut_police_SansCol_rp(C.idPolice,C.id,'2022-12-31','A')* u.primeFamille) END AS primeTotale
		
	,	CASE WHEN C.codeBaseTarification='IND' 
		THEN ROUND((fn_nb_beneficiaire_police2_SansCol_rp(C.idPolice,C.id,'2022-12-31') * u.primeIndividu ) *0.1,0)
		ELSE ROUND( (fn_nb_statut_police_SansCol_rp(C.idPolice,C.id,'2022-12-31','A')* u.primeFamille)*0.1,0) END AS Commission	
		
FROM vw_infos_polices a
JOIN entetecontrat e ON (e.idPolice=a.idPolice)
JOIN college  C ON (C.idPolice=e.idPolice)
JOIN produit  u ON (u.codeProduit=C.codeProduit)
JOIN police p ON (p.id=a.idPolice) 
WHERE a.typeGarant IN (1)
AND a.idPolice=168 -- and C.id=227
-- AND a.codeGcAssureur IN ('SIDAM') 
AND e.id = fn_id_entetecontrat_police_courant(a.idPolice)
AND fn_id_entetecontrat_police(a.idPolice,CURDATE())=fn_id_entetecontrat_police_courant(a.idPolice)
AND e.exercieReference=2022 
-- AND CASE WHEN C.codeBaseTarification='IND' THEN  u.primeIndividu=0 	ELSE u.primeFamille=0 END  
AND p.codeEtatPolice NOT IN ('RE','EX','AN')
ORDER BY codeGcAssureur,idPolice,idCollege;

SELECT * FROM produit


SELECT * FROM college WHERE idPolice=168


SELECT * FROM `gc_typegarant`

-- select * from college



SELECT 
		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,A.dateSortie,
		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 primeRistourneInd,	
		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
		,TIMESTAMPDIFF(MONTH,A.dateSortie,e.dateFin) MoisRestant
		,(TIMESTAMPDIFF(MONTH,e.dateEffet,e.dateFin)+1) AS MoisDuree,e.dateEffet,e.dateFin
		
FROM beneficiaire A
JOIN college C ON (C.id=A.idCollege)
JOIN police P ON (P.id=C.idPolice)
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=168)
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-12-31') AND A.nomBeneficiaire NOT LIKE "%AVENANT%"
-- AND (A.dateSortie BETWEEN '2022-01-01' AND '2022-12-31')
GROUP BY
A.codeSociete,C.codeGcAssureur,B.libelle,C.idPolice,A.idCollege,C.libelleCollege,
C.codeProduit,C.libelleCollege,A.codeLienParente,L.libelle,C.codeBaseTarification,A.dateSortie,
C.codeBaseTarification,B.primeFamille,B.primeIndividu,e.dateEffet,e.dateFin,	
A.dateSortie
--  C.codeBaseTarification,A.idPolice,A.idCollege,A.codeLienParente,A.dateSortie
ORDER BY libelleComplet ;


-- 


	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.idAdherent)
		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)
	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)
	WHERE A.idPolice>0
	AND (CASE WHEN 184 IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice=184 END)  
	AND A.nomBeneficiaire NOT LIKE "%AVENANT%"  AND g.id!='45' AND p.idClient!='49' 
	AND p.codeEtatPolice NOT IN ('RE','EX','AN')
	AND CASE WHEN A.sorti = "1" THEN A.dateSortie >= A.dateEffet ELSE A.dateSortie IS NULL END 
	AND (A.dateEffet BETWEEN "2022-01-01" AND "2022-12-31")
	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.primeHt,
		t.primeIncorpoInd,t.nbPersonne,
		-- SUM(t.primeIncorpoInd*t.nbPersonne*MoisDuree) AS primeHtTotale,
		SUM(t.primeHt*t.nbPersonne) 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.primeHt,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,primeHt,nbPersonne,
		SUM(primeHtIncorpo) AS primeHtIncorpo,
		SUM(primeHtTotale) AS primeHtTotale	
	FROM    temp_cumul_colHT 
	GROUP BY Exercice,codeGcAssureur,idPolice,primeHt,nbPersonne,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 "2022-01-01" AND "2022-09-30")
	AND (CASE WHEN 168 IS NULL THEN a.idPolice IS NOT NULL ELSE a.idPolice=168 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.primeHtIncorpo)*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;	 

