
SELECT 
	A.codePrestataire Compte,fn_libelle_prestataire(A.codePrestataire) AS Prestataire,
	A.codeActe,A.datePrestation,
	a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire,	
	SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
FROM p_prestationactes A
INNER JOIN beneficiaire B ON ( B.id=A.idBeneficiaire )
INNER JOIN police Po ON (Po.id=B.idPolice)
INNER JOIN gc_assureur Gc ON  (Po.codeGcAssureur = Gc.codeGcAssureur)
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=A.numeroFeuilleMaladie)
INNER JOIN adherent a ON ((Po.id=a.idPolice) AND (Po.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
INNER JOIN prestataire p  ON (A.codePrestataire=p.codePrestataire)
-- WHERE (LEFT(A.dateSysteme,10)>='2022-05-30')
WHERE A.montantApayer>0  
-- AND B.numeroBeneficiaire='0205031401'
AND a.numeroAdherent="001850120"
GROUP BY A.codePrestataire,A.codeMedecin,A.codeActe,A.datePrestation,A.tiersPayant,F.codeAffection,
a.id,a.numeroAdherent, a.nom, a.prenoms,B.id,B.numeroBeneficiaire,B.nomBeneficiaire,B.prenomsBeneficiaire 
ORDER BY idAdherent,idbenef,montantRembourse DESC  ;


-- select * from beneficiaire where numeroBeneficiaire='0205031401'
