SELECT YEAR('2022-11-30'),YEAR(CURDATE());


	SELECT codeSociete INTO @codeSociete FROM societeuser;
	IF YEAR('2022-11-30')=YEAR(CURDATE()) THEN 
		-- ANCIEN  2022-07-01	
		-- 0--RECHERCHE DES PRIMES TOTALES	
		DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeTotale;	 
		CREATE TEMPORARY TABLE temp_cumul_collegeTotale	
		SELECT 
			fn_exercice_police(A.idPolice) 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(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 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 'TGCC' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='TGCC' END)  
		AND (CASE WHEN '193' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='193' END)  
		AND A.nomBeneficiaire NOT LIKE "%AVENANT%"  AND g.typeGarant!='1' AND g.id!='45' AND p.idClient!='49' 
		AND p.codeEtatPolice NOT IN ('RE','EX','AN')
		AND (A.dateEffet BETWEEN p.dateEffet AND p.dateFin)
		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.dateEffet,
			C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
		ORDER BY idPolice,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,
			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(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,'2022-11-30')+1
			WHEN (C.codeBaseTarification='IND') THEN TIMESTAMPDIFF(MONTH,A.dateEffet,'2022-11-30')+1
			ELSE 0 END AS MoisUtilise
			,(TIMESTAMPDIFF(MONTH,A.dateEffet,p.dateFin)+1) AS MoisDuree,p.dateEffet,p.dateFin
		FROM beneficiaire A 
		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 'TGCC' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='TGCC' END)  
		AND (CASE WHEN '193' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='193' END)  
		AND A.nomBeneficiaire NOT LIKE "%AVENANT%"  AND g.typeGarant!='1' AND g.id!='45' AND p.idClient!='49' 
		AND p.codeEtatPolice NOT IN ('RE','EX','AN')
		AND (A.dateEffet BETWEEN '2022-01-01' AND '2022-11-30')
		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.dateEffet,
			C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
		ORDER BY idPolice,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,
			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(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,'2022-11-30')
			WHEN (C.codeBaseTarification='IND') THEN TIMESTAMPDIFF(MONTH,A.dateSortie,'2022-11-30')
			ELSE 0 END AS MoisUtilise
			,(TIMESTAMPDIFF(MONTH,A.dateSortie,p.dateFin)) AS MoisDuree,p.dateEffet,p.dateFin
		FROM beneficiaire A
		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 'TGCC' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='TGCC' END)  
		AND (CASE WHEN '193' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='193' END)  
		AND (CASE WHEN 'R' IS NULL THEN A.codeEtatBeneficiaire IS NOT NULL ELSE A.codeEtatBeneficiaire='R' END)  --  pour les retires
		AND CASE WHEN A.sorti = "1" THEN A.dateSortie <='2022-11-30' ELSE (A.dateSortie IS NULL OR A.dateSortie='') 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 '2022-01-01' AND '2022-11-30')	
		AND  (LEFT(A.dateEffet,10) BETWEEN p.dateEffet AND p.dateFin)
		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,
			C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
		ORDER BY idPolice,idPolice,codeLienParente;	
		
		ELSE
		
		SELECT  dateEffet,dateFin INTO @dateEffet,@dateFin FROM  entetecontrat A WHERE A.idPolice>0 AND A.exercice=YEAR('2022-11-30')
		AND (CASE WHEN '193' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='193' 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('2022-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('2022-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('2022-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('2022-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('2022-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 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 'TGCC' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='TGCC' END)  
			AND (CASE WHEN '193' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='193' 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.dateEffet,
				C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
			ORDER BY idPolice,idPolice,codeLienParente;
		
		-- 1--RECHERCHE DES BENEFICIAIRES A ENCAISERR	
			DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeEncaisse;	 
			CREATE TEMPORARY TABLE temp_cumul_collegeEncaisse	
			SELECT 
				YEAR('2022-11-30') 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('2022-11-30') THEN DATE_ADD(CONCAT(YEAR('2022-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END)),
				"-",CASE WHEN LENGTH(MONTH((CASE WHEN YEAR(A.dateEntree)<YEAR('2022-11-30') THEN DATE_ADD(CONCAT(YEAR('2022-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END)))<2 
				THEN CONCAT("0",MONTH((CASE WHEN YEAR(A.dateEntree)<YEAR('2022-11-30') THEN DATE_ADD(CONCAT(YEAR('2022-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('2022-11-30') THEN DATE_ADD(CONCAT(YEAR('2022-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END),'2022-11-30')+1
				WHEN (C.codeBaseTarification='IND') 
				THEN TIMESTAMPDIFF(MONTH,(CASE WHEN YEAR(A.dateEntree)<YEAR('2022-11-30') THEN DATE_ADD(CONCAT(YEAR('2022-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateEntree END),'2022-11-30')+1 ELSE 0 END AS MoisUtilise,
				(TIMESTAMPDIFF(MONTH,@dateEffet,@dateFin)+1) AS MoisDuree,@dateEffet dateEffet,@dateFin dateFin
			FROM beneficiaire A 
			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 'TGCC' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='TGCC' END)  
			AND (CASE WHEN '193' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='193' END)  
			AND A.nomBeneficiaire NOT LIKE "%AVENANT%"  AND g.typeGarant!='1' AND g.id!='45' AND p.idClient!='49' 
			AND (CASE WHEN '2022-11-30' IS NULL THEN A.dateEntree IS NOT NULL ELSE A.dateEntree<='2022-11-30' 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.dateEffet,
				C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
			ORDER BY idPolice,idPolice,codeLienParente;

			DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeRistourne;	 
			CREATE TEMPORARY TABLE temp_cumul_collegeRistourne	
			SELECT 
				YEAR('2022-11-30') 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('2022-11-30') THEN DATE_ADD(CONCAT(YEAR('2022-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateSortie END)),
				"-",CASE WHEN LENGTH(MONTH((CASE WHEN YEAR(A.dateSortie)<YEAR('2022-11-30') THEN DATE_ADD(CONCAT(YEAR('2022-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateSortie END)))<2 
				THEN CONCAT("0",MONTH((CASE WHEN YEAR(A.dateSortie)<YEAR('2022-11-30') THEN DATE_ADD(CONCAT(YEAR('2022-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('2022-11-30') THEN DATE_ADD(CONCAT(YEAR('2022-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateSortie END),'2022-11-30')
				     WHEN (C.codeBaseTarification='IND') 
				THEN TIMESTAMPDIFF(MONTH,(CASE WHEN YEAR(A.dateSortie)<YEAR('2022-11-30') THEN DATE_ADD(CONCAT(YEAR('2022-01-01'),'-01-01'), INTERVAL -1 YEAR) ELSE A.dateSortie END),'2022-11-30') ELSE 0 END AS MoisUtilise,
				(TIMESTAMPDIFF(MONTH,@dateEffet,@dateFin)+1) AS MoisDuree,@dateEffet dateEffet,@dateFin dateFin				
			FROM beneficiaire A 
			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 'TGCC' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='TGCC' END)  
			AND (CASE WHEN '193' IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice='193' END)  
			-- AND (CASE WHEN 'R' IS NULL THEN A.codeEtatBeneficiaire IS NOT NULL ELSE A.codeEtatBeneficiaire='R' END)  --  pour les retires
			AND CASE WHEN A.sorti = "1" THEN A.dateSortie <='2022-11-30' ELSE (A.dateSortie IS NULL OR A.dateSortie='') END 	
			AND A.nomBeneficiaire NOT LIKE "%AVENANT%" AND g.id!='45' AND p.idClient!='49' 
			AND (A.dateSortie BETWEEN '2022-01-01' AND '2022-11-30')	
			AND (CASE WHEN '2022-11-30' IS NULL THEN A.dateEntree IS NOT NULL ELSE A.dateEntree<='2022-11-30' 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,
				C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
			ORDER BY idPolice,idPolice,codeLienParente;	
			

	END IF;

	
	
-- 3- Concilliation des encaissement et des ristournes
	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,
		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
	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_colHTEncaisse;
	CREATE TEMPORARY TABLE temp_cumul_colHTEncaisse
	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_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,
		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;
	
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumulEncaisse_colHTNEWS;
	CREATE TEMPORARY TABLE temp_cumulEncaisse_colHTNEWS
	SELECT	
		Exercice,
		codeGcAssureur,
		idPolice,
		numeroPolice,
		libellePolice,
		SUM(primeHtIncorpo) AS primeHtIncorpo,
		SUM(primeHtTotale) AS primeHtTotale	
	FROM    temp_cumul_colHTEncaisse 
	GROUP BY Exercice,codeGcAssureur,idPolice,numeroPolice,libellePolice;
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumulTotale_colHTNEWS;
	CREATE TEMPORARY TABLE temp_cumulTotale_colHTNEWS
	SELECT	
		Exercice,
		codeGcAssureur,
		idPolice,
		numeroPolice,
		libellePolice,
		SUM(primeHtIncorpo) AS primeHtIncorpo,
		SUM(primeHtTotale) AS primeHtTotale		
	FROM    temp_cumul_colHTTotale 
	GROUP BY Exercice,codeGcAssureur,idPolice,numeroPolice,libellePolice;	
			
	DROP TEMPORARY TABLE IF EXISTS temp_cumulRistourne_colHTNEWS;
	CREATE TEMPORARY TABLE temp_cumulRistourne_colHTNEWS
	SELECT	
		Exercice,
		codeGcAssureur,
		idPolice,
		numeroPolice,
		libellePolice,
		SUM(primeHtIncorpo) AS primeHtIncorpo,
		SUM(primeHtTotale) AS primeHtTotale	
	FROM    temp_cumul_colHTRistourne 
	GROUP BY Exercice,codeGcAssureur,idPolice,numeroPolice,libellePolice;
	
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumulEncaisse_colHTNEWS3;
	CREATE TEMPORARY TABLE temp_cumulEncaisse_colHTNEWS3
	SELECT 	
		Exercice,
		codeGcAssureur,
		idPolice,numeroPolice,libellePolice,
		primeHtIncorpo,
		primeHtTotale,
		ROUND(((ROUND(primeHtIncorpo,0))*20)/100,0) AS chargement,
		(primeHtIncorpo-ROUND(((ROUND(primeHtIncorpo,0))*20)/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,
		primeHtIncorpo,
		primeHtTotale,
		ROUND(((ROUND(primeHtIncorpo,0))*20)/100,0) AS chargement,
		(primeHtIncorpo-ROUND(((ROUND(primeHtIncorpo,0))*20)/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,
		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 '2022-01-01' AND '2022-11-30')
	AND (CASE WHEN 'TGCC' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='TGCC' END)  
	AND (CASE WHEN '193' IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice='193' END)     	
	AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete=@codeSociete )
	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 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 'TGCC' IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur='TGCC' END)  
	AND (CASE WHEN '193' IS NULL THEN p.id IS NOT NULL ELSE p.id='193' END)     
	LIMIT 1;
	
	
	-- encaissement
	SELECT ROUND((h.primeHtIncorpo*@tauxCourtier)/100,0)      INTO  @honoCourtierEncaisse
	FROM  temp_cumulEncaisse_colHTNEWS3 h  ;	
	
	SELECT ROUND((h.primeHtIncorpo*@tauxHonoraire)/100,0) 	  INTO  @honoGestionnaireEncaisse
	FROM  temp_cumulEncaisse_colHTNEWS3 h  ;
	      
	SELECT  ROUND((@honoGestionnaireEncaisse*@tauxTva)/100,0) INTO  @montantTVAEncaisse;
	
	-- Ristoune
	SELECT ROUND((h.primeHtIncorpo*@tauxCourtier)/100,0)      INTO  @honoCourtierRistourne
	FROM  temp_cumulRistourne_colHTNEWS3 h  ;	
	
	SELECT ROUND((h.primeHtIncorpo*@tauxHonoraire)/100,0) 	  INTO  @honoGestionnaireRistourne
	FROM  temp_cumulRistourne_colHTNEWS3 h  ;
	      
	SELECT  ROUND((@honoGestionnaireRistourne*@tauxTva)/100,0) INTO  @montantTVARistourne;
	
	
	   
	SELECT  ROUND(@honoGestionnaireEncaisse+@honoCourtierEncaisse+@montantTVAEncaisse,0)  INTO  @chargementEncaisse;
	
	SELECT  ROUND(@honoGestionnaireRistourne+@honoCourtierRistourne+@montantTVARistourne,0)  INTO  @chargementRistourne;
	
	
	SELECT * FROM temp_cumulEncaisse_colHTNEWS3
	
	-- Restitution	
	SELECT 	DISTINCT h.Exercice, h.codeGcAssureur AS CODEGARANT,
		h.idPolice AS IDPOLICE,h.numeroPolice AS NUMERO,
		h.libellePolice AS POLICE,
		IFNULL(t.primeHtTotale,0) AS PRIMETOTALE ,
		ROUND(IFNULL(h.primeHtIncorpo,0)-IFNULL(r.primeHtIncorpo,0),0) AS PRIMENETTE ,
		CASE WHEN 20=0 THEN 0 ELSE  IFNULL(ROUND(IFNULL(@chargementEncaisse,0)-IFNULL(@chargementRistourne,0),0),0) END AS CHARGEMENT,
		ROUND((ROUND(IFNULL(h.primeHtIncorpo,0)-IFNULL(r.primeHtIncorpo,0),0))-(@chargementEncaisse-@chargementRistourne),0) AS PROVISION,
		IFNULL(s.sinistre ,0) AS SINISTRES,
		(ROUND((ROUND(IFNULL(h.primeHtIncorpo,0)-IFNULL(r.primeHtIncorpo,0),0) )-(@chargementEncaisse-@chargementRistourne),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)
	LEFT JOIN temp_cumul_sommePolice s ON (h.idPolice=s.idPolice)
	LEFT JOIN temp_cumulTotale_colHTNEWS t ON (h.idPolice=t.idPolice)	
	WHERE h.idPolice>0
	ORDER BY h.Exercice,h.codeGcAssureur,h.idPolice;	
	
	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;	