-- beneficiaire avec les pathologies chroniques
SELECT
		
		P.id,P.numeroPolice,P.libellePolice,af.CodeTypeAffection,ta.libelle AS TypeAffection,
		af.codeAffection,af.libelle AS Affection,
		a.id,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
		prt.idBeneficiaire,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
		SUM(IFNULL(prt.fraisReel,0)) AS montantFacture,
		SUM(IFNULL(prt.montantApayer,0)) AS apresDecompte
FROM p_prestationactes prt INNER JOIN p_facture B ON (B.id=prt.idfacture)				
INNER JOIN police P ON  (B.idPolice = P.id)
INNER JOIN gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
INNER JOIN adherent a ON ((P.id=a.idPolice) AND (P.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
-- INNER JOIN beneficiaire f ON (f.id=prt.idBeneficiaire )	
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie   AND F.idPolice = P.Id)
INNER JOIN p_affection af ON (F.codeAffection=af.codeAffection)
INNER JOIN p_typeaffection ta ON (ta.CodeTypeAffection=af.CodeTypeAffection)	
/* INNER JOIN entetecontrat C ON (C.idPolice=B.idPolice)
WHERE prt.debut BETWEEN C.dateEffet AND C.dateFin
AND C.id =fn_id_entetecontrat_police_courant(B.idPolice)*/
WHERE (LEFT(B.dateSysteme,10)  BETWEEN '2022-01-01' AND '2022-12-31' )
AND af.codeAffection IN ('352','22','966','804','806','401','402','962','254','252','760','260')  
AND G.id!='45' AND P.idClient!='49' AND (B.codeSociete="vitalis") AND prt.montantApayer!=0
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
GROUP BY af.CodeTypeAffection,P.id,P.numeroPolice,P.libellePolice,ta.libelle
ORDER BY P.id,af.CodeTypeAffection,af.codeAffection,apresDecompte DESC ;
	
	
	SELECT * FROM p_affection
	WHERE codeAffection IN ('352','22','966','804','806','401','402','962','254','252','760','260')  