-- 1 DECOMPTE ADHERENT PLUS 1.000.000 FFCFA
	DROP TEMPORARY TABLE IF EXISTS temp_factures_adh;	 
	CREATE TEMPORARY TABLE temp_factures_adh	
	SELECT		
		C.id,C.numeroPolice,C.libellePolice,
		a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
		/*(SELECT COUNT(*) FROM beneficiaire  WHERE idAdherent=a.id
		AND beneficiaire.idPolice=a.idPolice 
		AND CASE WHEN beneficiaire.sorti = "1" THEN beneficiaire.dateSortie >= "2022-09-30" ELSE beneficiaire.dateSortie IS NULL END) popActive,		
		COUNT(DISTINCT prt.idBeneficiaire)  AS popTraite,*/
		 SUM(IFNULL(prt.fraisReel,0)) AS montantReclame,
		SUM(IFNULL(prt.montantApayer,0)) AS montantRembourse
	FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
	JOIN police C ON  (B.idPolice = C.id)
	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 B.idPolice IS NOT NULL ELSE B.idPolice=168 END)  
	AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete="vitalis")
	AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
	AND B.valide=1 AND B.okDecompte=1
	GROUP BY a.id,a.numeroAdherent,a.nom,a.prenoms ,C.id,C.libellePolice,C.numeroPolice 
	 HAVING 	( CASE WHEN 500000>0 THEN SUM(IFNULL(prt.montantApayer,0)) >= 500000 ELSE SUM(IFNULL(prt.montantApayer,0))>=500000 END)
	ORDER BY C.id,montantRembourse DESC,adherent;
	
	SELECT  
		SUM(IFNULL(A.montantRembourse,0)) AS TotalAdherent,COUNT(0) AS DecompteBenef
	FROM  temp_factures_adh  A;
	
	
-- 2 DECOMPTE BENEFICIAIRE PLUS 1.000.000 FFCFA
DROP TEMPORARY TABLE IF EXISTS temp_factures_benef;	 
CREATE TEMPORARY TABLE temp_factures_benef	
SELECT		
	C.id,C.numeroPolice,C.libellePolice,
	e.numeroBeneficiaire,CONCAT(e.nomBeneficiaire,' ',e.prenomsBeneficiaire)  Beneficiaire,
	COUNT(DISTINCT prt.idBeneficiaire)  AS popTraite,
	SUM(IFNULL(prt.fraisReel,0)) AS montantReclame,
	SUM(IFNULL(prt.montantApayer,0)) AS montantRembourse
FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
JOIN police C ON  (B.idPolice = C.id)
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))
INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
WHERE (LEFT(B.dateSysteme,10) BETWEEN "2022-01-01" AND "2022-09-30" )
AND (CASE WHEN 168 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=168 END)  
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete="vitalis")
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
AND B.valide=1 AND B.okDecompte=1
GROUP BY  e.numeroBeneficiaire,e.nomBeneficiaire,e.prenomsBeneficiaire,C.id,C.libellePolice,C.numeroPolice 
HAVING 	( CASE WHEN 500000>0 THEN SUM(IFNULL(prt.montantApayer,0)) >= 500000 ELSE SUM(IFNULL(prt.montantApayer,0))>=500000 END)
ORDER BY C.id,montantRembourse DESC,Beneficiaire;

SELECT  
	SUM(IFNULL(A.montantRembourse,0)) AS TotalBenef,COUNT(0) AS DecompteBenef	
FROM  temp_factures_benef  A;