SELECT codeSociete INTO @codeSociete FROM societeuser;	 
	
DROP TEMPORARY TABLE IF EXISTS beneficiaireage_temp;	 

CREATE TEMPORARY TABLE beneficiaireage_temp
SELECT 
	A.*,
	DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(A.dateNaissance, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(A.dateNaissance, '00-%m-%d')) AS age  
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 192 IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice=192 END) ;

SELECT 
	COUNT(*) 
INTO 
	@Assuree
FROM beneficiaireage_temp
WHERE CodeLienParente='A' ;  

SELECT 
	COUNT(*) 
INTO 
	@Conjointe
FROM beneficiaireage_temp
WHERE CodeLienParente='C' ; 

SELECT 
	COUNT(*) 
INTO 
	@Enfant
FROM beneficiaireage_temp
WHERE CodeLienParente='E' ; 

SELECT 
	COUNT(*) 
INTO 
	@Ascendant
FROM beneficiaireage_temp
WHERE CodeLienParente='F' ; 

SELECT 
	COUNT(*) 
INTO 
	@AutreConjoint
FROM beneficiaireage_temp
WHERE CodeLienParente='D' ; 

SELECT 
	COUNT(*) 
INTO 
	@AutreDependant
FROM beneficiaireage_temp
WHERE CodeLienParente='O' ;

	
SELECT 
	COUNT(DISTINCT prt.idBeneficiaire ),
	SUM(prt.montantApayer)
INTO 	@assureTraite,@mtttassureTraite
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 beneficiaireage_temp f ON (f.id=prt.idBeneficiaire )	
INNER JOIN beneficiaire f ON (f.id=prt.idBeneficiaire)
INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=f.idAdherent))
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-10-31')
AND (CASE WHEN 192 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=192 END)  
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete=@codeSociete)
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276') 
AND f.CodeLienParente='A' ;--  GROUP BY idCollege;	

SELECT  
	COUNT(DISTINCT prt.idBeneficiaire ),
	SUM(prt.montantApayer)
INTO 	@enfantTraite,@mtttenfantTraite
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 beneficiaireage_temp f ON (f.id=prt.idBeneficiaire )	
INNER JOIN beneficiaire f ON (f.id=prt.idBeneficiaire)
INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=f.idAdherent))
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-10-31')
AND (CASE WHEN 192 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=192 END)  
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete=@codeSociete)
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276') 
AND f.CodeLienParente='E' ;

SELECT  
	COUNT(DISTINCT prt.idBeneficiaire ),
	SUM(prt.montantApayer)
INTO 	@conjointraite,@mtttconjTraite
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 beneficiaireage_temp f ON (f.id=prt.idBeneficiaire )	
INNER JOIN beneficiaire f ON (f.id=prt.idBeneficiaire)
INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=f.idAdherent))
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-10-31')
AND (CASE WHEN 192 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=192 END)  
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete=@codeSociete)
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276') 
AND f.CodeLienParente='C' ;


SELECT  
	COUNT(DISTINCT prt.idBeneficiaire ),
	SUM(prt.montantApayer)
INTO 	
	 @ascendTraite,	@mtttascendTraite
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 beneficiaireage_temp f ON (f.id=prt.idBeneficiaire )	
INNER JOIN beneficiaire f ON (f.id=prt.idBeneficiaire)
INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=f.idAdherent))
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-10-31')
AND (CASE WHEN 192 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=192 END)  
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete=@codeSociete)
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276') 
AND f.CodeLienParente='F' ;-- GROUP BY idCollege;

SELECT  
	COUNT(DISTINCT prt.numeroBeneficiaire ),
	SUM(prt.montantApayer)
INTO 	
	@aautrConjoiTraite,@mtttautrConjoiTraite
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 beneficiaireage_temp f ON (f.id=prt.idBeneficiaire )	
INNER JOIN beneficiaire f ON (f.id=prt.idBeneficiaire)
INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=f.idAdherent))
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-10-31')
AND (CASE WHEN 192 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=192 END)  
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete=@codeSociete)
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276') 
AND f.CodeLienParente='D' ; 

SELECT 
	COUNT(DISTINCT prt.idBeneficiaire ),
	SUM(prt.montantApayer)
INTO 	
	@aautrTraite,@mtttautrTraite
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 beneficiaireage_temp f ON (f.id=prt.idBeneficiaire )	
INNER JOIN beneficiaire f ON (f.id=prt.idBeneficiaire)
INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=f.idAdherent))
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-10-31')
AND (CASE WHEN 192 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=192 END)  
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete=@codeSociete)
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276') 
AND f.CodeLienParente='O' ; 

	
SELECT 	IFNULL(@aautrTraite,"0")
	+IFNULL(@aautrConjoiTraite,"0")
	+IFNULL(@ascendTraite,"0")
	+IFNULL(@enfantTraite,"0")
	+IFNULL(@assureTraite,"0")
	+IFNULL(@conjointraite,"0")		
INTO @Nombre;

SELECT 	IFNULL(@aautrTraite,"0")
	+IFNULL(@aautrConjoiTraite,"0")
	+IFNULL(@ascendTraite,"0")
	+IFNULL(@enfantTraite,"0")
	+IFNULL(@assureTraite,"0")
	+IFNULL(@conjointraite,"0")		
INTO @BenefTraite;


SELECT 	IFNULL(@mtttautrTraite,"0")
	+IFNULL(@mtttautrConjoiTraite,"0")
	+IFNULL(@mtttascendTraite,"0")
	+IFNULL(@mtttconjTraite,"0")
	+IFNULL(@mtttenfantTraite,"0")
	+IFNULL(@mtttassureTraite,"0")
INTO @Montant;

SELECT 
	libelle, 

	CASE codeLienParente 
	  WHEN "A" THEN  IFNULL(@Assuree,"0")
	  WHEN "C" THEN  IFNULL(@Conjointe,"0")
	  WHEN "E" THEN  IFNULL(@Enfant,"0")
	  WHEN "F" THEN  IFNULL(@Ascendant,"0")
	  WHEN "D" THEN  IFNULL(@AutreConjoint,"0")
	  WHEN "O" THEN  IFNULL(@AutreDependant,"0")
	  ELSE  0
	END AS Nombre,
	
	CASE codeLienParente 
	  WHEN "A" THEN  IFNULL(@assureTraite,"0")
	  WHEN "C" THEN  IFNULL(@conjointraite,"0")
	  WHEN "E" THEN  IFNULL(@enfantTraite,"0")
	  WHEN "F" THEN  IFNULL(@ascendTraite,"0")
	  WHEN "D" THEN  IFNULL(@aautrConjoiTraite,"0")
	  WHEN "O" THEN  IFNULL(@aautrTraite,"0")
	  ELSE  0
	END AS Nombretraite,
	
	CASE codeLienParente 
	  WHEN "A" THEN  IFNULL(ROUND((@assureTraite/@BenefTraite)*100,2),"0")
	  WHEN "C" THEN  IFNULL(ROUND((@conjointraite/@BenefTraite)*100,2),"0")
	  WHEN "E" THEN  IFNULL(ROUND((@enfantTraite/@BenefTraite)*100,2),"0")
	  WHEN "F" THEN  IFNULL(ROUND((@ascendTraite/@BenefTraite)*100,2),"0")
	  WHEN "D" THEN  IFNULL(ROUND((@aautrConjoiTraite/@BenefTraite)*100,2),"0")
	  WHEN "O" THEN  IFNULL(ROUND((@aautrTraite/@BenefTraite)*100,2),"0")
	  ELSE  0
	END AS Taux_EffTraite
	
	,CASE codeLienParente 
	  WHEN "A" THEN  IFNULL(@mtttassureTraite,"0")
	  WHEN "C" THEN  IFNULL(@mtttconjTraite,"0")
	  WHEN "E" THEN  IFNULL(@mtttenfantTraite,"0")
	  WHEN "F" THEN  IFNULL(@mtttascendTraite,"0")
	  WHEN "D" THEN  IFNULL(@mtttautrConjoiTraite,"0")
	  WHEN "O" THEN  IFNULL(@mtttautrTraite,"0")
	  ELSE  0
	END AS Depenses,
	
	CASE codeLienParente 
	  WHEN "A" THEN  IFNULL(ROUND((@mtttassureTraite/@Montant)*100,2),"0")
	  WHEN "C" THEN  IFNULL(ROUND((@mtttconjTraite/@Montant)*100,2),"0")
	  WHEN "E" THEN  IFNULL(ROUND((@mtttenfantTraite/@Montant)*100,2),"0")
	  WHEN "F" THEN  IFNULL(ROUND((@mtttascendTraite/@Montant)*100,2),"0")
	  WHEN "D" THEN  IFNULL(ROUND((@mtttautrConjoiTraite/@Montant)*100,2),"0")
	  WHEN "O" THEN  IFNULL(ROUND((@mtttautrTraite/@Montant)*100,2),"0")
	  ELSE  0
	 END AS Taux_Depense,
	 
	 CASE codeLienParente 
	  WHEN "A" THEN  IFNULL(ROUND(@mtttassureTraite/NULLIF(@assureTraite,"0")),"0")
	  WHEN "C" THEN  IFNULL(ROUND(@mtttconjTraite/NULLIF(@conjointraite,"0")),"0")
	  WHEN "E" THEN  IFNULL(ROUND(@mtttenfantTraite/NULLIF(@enfantTraite,"0")),"0")
	  WHEN "F" THEN  IFNULL(ROUND(@mtttascendTraite/NULLIF(@ascendTraite,"0")),"0")
	  WHEN "D" THEN  IFNULL(ROUND(@mtttautrConjoiTraite/NULLIF(@aautrConjoiTraite,"0")),"0")
	  WHEN "O" THEN  IFNULL(ROUND(@mtttautrTraite/NULLIF(@aautrTraite,"0")),"0")
	  ELSE  0
	 END AS CoutMoyen
	 
FROM lienparente ORDER BY id ; 