	SELECT codeSociete INTO @codeSociete FROM societeuser;
	
	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 'SAAR' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='SAAR' END)  
	AND (CASE WHEN '257' IS NULL  THEN A.idPolice IS NOT NULL ELSE A.idPolice='257' END)  	
	AND (CASE WHEN '2023-12-31' IS NULL THEN A.dateEntree IS NOT NULL ELSE A.dateEntree <= '2023-12-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-01-01')=YEAR(CURDATE()) THEN 
		
	-- 1--RECHERCHE DES BENEFICIAIRES A ENCAISERR	
		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,
			r.prime AS primeHt,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,A.dateSortie)+1
			WHEN (C.codeBaseTarification='IND') THEN TIMESTAMPDIFF(MONTH,A.dateEffet,A.dateSortie)+1
			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 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 'SAAR' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='SAAR' END)  
		AND (CASE WHEN '257' IS NULL  THEN A.idPolice IS NOT NULL ELSE A.idPolice='257' END)  	
		AND A.codeEtatBeneficiaire IN ('R','NR')
		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-12-31' )	
		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, 
			A.idCollege,C.libelleCollege,C.codeProduit,C.libelleCollege,A.codeLienParente,
			L.libelle,A.codeLienParente,A.dateSortie,r.prime,A.dateEffet,
			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-12-31')
			AND (CASE WHEN '257' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='257' END)  ;
		
		-- PRIME A RISTOURNER
			DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeRistourne;	 
			CREATE TEMPORARY TABLE temp_cumul_collegeRistourne	
			SELECT 
				YEAR('2023-12-31') 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((CASE WHEN YEAR(A.dateSortie)<YEAR('2023-12-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-12-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-12-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-12-31') THEN DATE_ADD(CONCAT(YEAR(A.dateEffet),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateSortie END),'2023-12-31')
				     WHEN (C.codeBaseTarification='IND') 
				THEN TIMESTAMPDIFF(MONTH,(CASE WHEN YEAR(A.dateSortie)<YEAR('2023-12-31') THEN DATE_ADD(CONCAT(YEAR(A.dateEffet),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateSortie END),'2023-12-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.id<>0  
			AND (CASE WHEN 'SAAR' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='SAAR' END)  
			AND (CASE WHEN p_idClient IS NULL THEN p.idClient IS NOT NULL ELSE p.idClient=p_idClient END)  
			AND (CASE WHEN '257' IS NULL  THEN A.idPolice IS NOT NULL ELSE A.idPolice='257' END)  	
			AND (CASE WHEN p_idCollege IS NULL THEN A.idCollege IS NOT NULL ELSE A.idCollege=p_idCollege END)  
			AND (CASE WHEN p_codeApporteur IS NULL  THEN p.codeApporteur IS NOT NULL ELSE p.codeApporteur=p_codeApporteur END) 
			AND A.codeEtatBeneficiaire IN ('R','NR')	
			AND A.nomBeneficiaire NOT LIKE "%AVENANT%" AND g.id!='45' AND p.idClient!='49' 
			AND (A.dateSortie BETWEEN '2023-01-01' AND '2023-12-31')	
			AND (CASE WHEN '2023-12-31' IS NULL THEN A.dateEntree IS NOT NULL ELSE A.dateEntree<='2023-12-31' END)
			GROUP BY A.idPolice,A.codeSociete,p.codeGcAssureur,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,C.primeFamille,C.primeBeneficiaire,p.dateEffet,p.dateFin	
			ORDER BY codeGcAssureur,idPolice,codeLienParente;	
			
	END IF;	
	
	IF p_typeRepartition='A' THEN
			
			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,t.codeBaseTarification,t.primeHt AS prime,
				t.codeLienParente,
				t.nbPersonne,
				t.MoisDuree AS DureeRistournee,
				t.MoisUtilise AS DureeCouverture,
				- SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise) AS primeHtPeriode,
				- SUM(t.primeIncorpoInd*t.nbPersonne*MoisDuree) AS primeHtTotale			
			FROM    temp_cumul_collegeRistourne t
			WHERE t.idPolice>0
			AND CASE WHEN codeBaseTarification='FAM' THEN codeLienParente='A'  ELSE codeLienParente IN ('A','C','E','O','F','D') END
			GROUP BY t.Exercice,t.codeGcAssureur,t.garant,t.idPolice,t.numeroPolice,t.libellePolice,t.idCollege,t.codeBaseTarification,t.codeLienParente,
			t.MoisDuree,t.MoisUtilise,t.primeHt,t.nbPersonne;
	END IF;	
	
	IF p_typeRepartition='M' THEN
			
			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,t.codeBaseTarification,primeHt AS prime,
				t.codeLienParente,
				t.nbPersonne,
				t.MoisDuree AS DureeRistournee,
				t.MoisUtilise AS DureeCouverture,
				- SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise) AS primeHtPeriode,
				- SUM(t.primeIncorpoInd*t.nbPersonne*MoisDuree) AS primeHtTotale				
			FROM    temp_cumul_collegeRistourne t
			WHERE t.idPolice>0
			AND CASE WHEN codeBaseTarification='FAM' THEN codeLienParente='A'  ELSE codeLienParente IN ('A','C','E','O','F','D') END
			GROUP BY t.Exercice,t.codeGcAssureur,t.garant,t.idPolice,t.numeroPolice,t.libellePolice,t.idCollege,t.codeBaseTarification,t.codeLienParente,
			t.MoisDuree,t.MoisUtilise,t.nbPersonne;
	END IF;	

	
	SELECT * FROM temp_cumul_colHTRistourne;