DROP TEMPORARY TABLE IF EXISTS temp_factures;	 
CREATE TEMPORARY TABLE temp_factures
SELECT  B.codePrestataire,
COUNT(B.id) AS nbFactures,
SUM(IFNULL(prt.fraisReel,0)) AS montantReclame,SUM(IFNULL(prt.montantApayer,0)) AS montantApayer
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))
INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-08-01' AND '2022-09-30')
-- AND (prt.codeTypePrestation="CONS") 
AND prt.montantApayer!=0 
-- AND (CASE WHEN p_garant IS NULL THEN G.codeGcAssureur IS NOT NULL ELSE G.codeGcAssureur=p_garant END)  
AND (CASE WHEN 185 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=185 END)  
-- AND (CASE WHEN p_idClient IS NULL THEN C.idClient IS NOT NULL ELSE C.idClient=p_idClient END)  
-- AND (CASE WHEN p_idCollege IS NULL THEN e.idCollege IS NOT NULL ELSE e.idCollege=p_idCollege END) 
AND G.id!='45' AND C.idClient!='49' 
-- AND B.valide=1 AND B.okDecompte=1
-- AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
AND B.codePrestataire IN ('CE1376')
GROUP BY B.codePrestataire;

	DROP TEMPORARY TABLE IF EXISTS temp_facture_medu;	 
	CREATE TEMPORARY TABLE temp_facture_medu
	SELECT  
		A.idAdherent,SUM(IFNULL(A.Arembourser,0)) AS Total,COUNT(A.idBeneficiaire) AS Decompte	
	FROM temp_facture_leger A
	GROUP BY A.idAdherent
	HAVING (CASE WHEN 100000>0 THEN SUM(IFNULL(A.Arembourser,0))>1000000 ELSE SUM(IFNULL(A.Arembourser,0))>0 END )	;	
	
 
	SELECT 
		B.idPolice,B.libellePolice,B.idCollege,
		B.idAdherent,B.numeroAdherent AS NoAdherent,B.adherent,		
		B.idBeneficiaire,B.numeroBeneficiaire AS NoBeneficiaire,B.beneficiaire,	
		SUM(A.montantReclame) AS Reclame,
		SUM(A.TM) TM,
		SUM(A.Arembourser) AS Rembourser,
		IFNULL(M.Total,0) AS Total 
		-- IFNULL(M.Decompte,0) AS Decompte 
	FROM temp_facture_leger A
	LEFT JOIN vw_infos_contrat_assures B ON (B.idBeneficiaire = A.idBeneficiaire) 
	INNER JOIN temp_facture_medu M ON (M.idAdherent = A.idAdherent)
	WHERE A.Arembourser>0
	GROUP BY B.idPolice,B.libellePolice,B.idCollege,B.idAdherent,B.numeroAdherent ,B.adherent,		
		 B.idBeneficiaire,B.numeroBeneficiaire ,B.beneficiaire,M.Total,M.Decompte
	ORDER BY Total DESC