-- beneficiaire avec les pathologies chroniques
SELECT
	-- prt.idBeneficiaire,
	 -- COUNT(DISTINCT prt.idAdherent) AS nbrAssure
	-- , COUNT(DISTINCT prt.idBeneficiaire) AS Benef
	-- SUM(IFNULL(prt.montantApayer,0)) AS Apayer
	AVG(DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(f.dateNaissance, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(f.dateNaissance, '00-%m-%d'))) AS age
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)	
WHERE (LEFT(B.dateSysteme,10)  BETWEEN '2022-01-01' AND '2022-12-31' )
AND prt.codeTypePrestation='PHAR' AND prt.id IN (SELECT `idPrestationactes` FROM `p_prestationmedicaments` )
AND f.idPolice=168
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 prt.idBeneficiaire
HAVING SUM(IFNULL(prt.montantApayer,0))>=300000
;




-- beneficiaire avec les pathologies chroniques
SELECT
	-- prt.idBeneficiaire,
	 COUNT(DISTINCT prt.idAdherent) AS nbrAssure
	-- , COUNT(DISTINCT prt.idBeneficiaire) AS Benef
	,SUM(IFNULL(prt.montantApayer,0)) AS Apayer
	-- AVG(DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(f.dateNaissance, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(f.dateNaissance, '00-%m-%d'))) AS age
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)
WHERE (LEFT(B.dateSysteme,10)  BETWEEN '2022-01-01' AND '2022-12-31' )
AND prt.id IN (SELECT `idPrestationactes` FROM `p_prestationoptiques` )
AND f.idPolice=168
-- 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 prt.idBeneficiaire
HAVING SUM(IFNULL(prt.montantApayer,0))>=3000000
;

SELECT * FROM `p_prestationoptiques`
