-- 1-- Tendance Detaillée
SELECT 
	-- LAST_DAY(LEFT(A.dateSysteme,10)) AS dateComptable,
	A.codePrestataire Compte,fn_libelle_prestataire(A.codePrestataire) AS Prestataire,
	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) BETWEEN '2022-01-01' AND '2022-05-31')
 AND A.montantApayer>0  
AND B.idPolice=229 
/*AND B.numeroBeneficiaire IN ( "0229084002","0229083502","0229069001","0229068801","0229078801","0229077702","0229077703","0229077701","0229081701",
"0229075401","0229075501","0229071301","0229071901","0229071102","0229085903","0229087301","0229090001","0229093901",
"0229093902","0229094901","0229174501","0229171701","0229171601","0229124901","0229135402","0229137401","0229139001",
"0229143501","0229143001","0229161102","0229148603","0229149101","0229149301","0229149501","0229150404","0229151102",
"0229168701","0229151401","0229152102","0229152104","0229165201","0229161201","0229160501","0229160201","0229159903",
"0229159702","0229155701","0229154701","0229073901","0229161401	")*/
GROUP BY A.codePrestataire
ORDER BY  montantRembourse DESC  ;



-- 2-- Tendance Detaillée
SELECT 
	-- DATE_FORMAT(LAST_DAY(A.dateSysteme), "%d/%m/%Y") AS dateComptable,
	-- A.codePrestataire Compte,fn_libelle_prestataire(A.codePrestataire) AS Prestataire,
	-- A.codeActe,A.datePrestation,
	B.idPolice,
	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) BETWEEN '2022-01-01' AND '2022-05-31')
AND A.montantApayer>0  
AND B.idPolice=229 
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 Compte,datePrestation,idAdherent,idbenef,montantRembourse DESC  ; -- dateComptable,
ORDER BY  idAdherent,montantRembourse DESC  ; -- dateComptable,