-- 1--NOMBRE DE ACTES EN CONSULTATIONS DES ADHERENTS

SELECT
		a.id,a.numeroAdherent,a.nom,a.prenoms,COUNT(prt.codeActe) Decompte -- ,SUM(prt.montantApayer) Decompte
FROM p_prestationactes prt 
INNER 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 p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie   AND F.idPolice = C.Id)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-03-31')
AND (CASE WHEN 'GNA' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='GNA' END)  
AND (CASE WHEN 168 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=168 END)  
AND G.id!='45' AND C.idClient!='49'  
AND prt.montantApayer>0 -- AND F.numeroBon>0
AND prt.codeGarantie='CON'
AND B.valide=1 AND B.okDecompte=1  AND prt.ententePrealable NOT IN ('2','9')  
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
GROUP BY a.id,a.numeroAdherent,a.nom,a.prenoms
ORDER BY Decompte DESC


-- 2--NOMBRE DE ACTES EN PHARMACIE DES ADHERENTS

SELECT
		a.id,a.numeroAdherent,a.nom,a.prenoms,COUNT(DISTINCT pm.numeroBonOrdonnance) Decompte -- ,SUM(prt.montantApayer) Decompte
FROM p_prestationactes prt 
INNER 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 p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie   AND F.idPolice = C.Id)
INNER JOIN p_prestationmedicaments pm ON (pm.idPrestationactes = prt.id)
INNER JOIN p_medicament m ON (pm.codeMedicament= m.codeMedicament)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-03-31')
AND (CASE WHEN 'GNA' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='GNA' END)  
AND (CASE WHEN 168 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=168 END)  
AND G.id!='45' AND C.idClient!='49'  
AND prt.montantApayer>0 -- AND F.numeroBon>0
-- AND prt.codeGarantie='CON'
AND B.valide=1 AND B.okDecompte=1  AND prt.ententePrealable NOT IN ('2','9')  
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
GROUP BY a.id,a.numeroAdherent,a.nom,a.prenoms
ORDER BY Decompte DESC

-- 3--NOMBRE DE ACTES EN CONSULTATIONS DES BENEFICIAIRES

SELECT
		e.id AS idBeneficiaire,e.numeroAdherent,e.numeroBeneficiaire,e.nomBeneficiaire,e.prenomsBeneficiaire,COUNT(prt.codeActe) Decompte -- ,SUM(prt.montantApayer) Decompte
FROM p_prestationactes prt 
INNER 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)
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie   AND F.idPolice = C.Id)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-03-31')
AND (CASE WHEN 'GNA' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='GNA' END)  
AND (CASE WHEN 168 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=168 END)  
AND G.id!='45' AND C.idClient!='49'  
AND prt.montantApayer>0 -- AND F.numeroBon>0
AND prt.codeGarantie='CON'
AND B.valide=1 AND B.okDecompte=1  AND prt.ententePrealable NOT IN ('2','9')  
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
GROUP BY e.id,e.numeroAdherent,e.numeroBeneficiaire,e.nomBeneficiaire,e.prenomsBeneficiaire
ORDER BY Decompte DESC


-- 4--NOMBRE DE ACTES EN PHARMACIE DES BENEFICIAIRES

SELECT
		e.id AS idBeneficiaire,e.numeroAdherent,e.numeroBeneficiaire,e.nomBeneficiaire,e.prenomsBeneficiaire,COUNT(DISTINCT pm.numeroBonOrdonnance) Decompte -- ,SUM(prt.montantApayer) Decompte
FROM p_prestationactes prt 
INNER 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)
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie   AND F.idPolice = C.Id)
INNER JOIN p_prestationmedicaments pm ON (pm.idPrestationactes = prt.id)
INNER JOIN p_medicament m ON (pm.codeMedicament= m.codeMedicament)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-03-31')
AND (CASE WHEN 'GNA' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='GNA' END)  
AND (CASE WHEN 168 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=168 END)  
AND G.id!='45' AND C.idClient!='49'  
AND prt.montantApayer>0 -- AND F.numeroBon>0
-- AND prt.codeGarantie='CON'
AND B.valide=1 AND B.okDecompte=1  AND prt.ententePrealable NOT IN ('2','9')  
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
GROUP BY e.id,e.numeroAdherent,e.numeroBeneficiaire,e.nomBeneficiaire,e.prenomsBeneficiaire
ORDER BY Decompte DESC

