  -- 0 RECHERCHE TAUX AU NIVEAU DES GARANTIES / BENEFICIAIRES  
 SELECT  codeGcAssureur,idPolice,idCollege,codeProduit,
idAdherent,numeroAdherent, Adherent,idBenef,numeroBeneficiaire,Beneficiaire,
idGarantie,idBaremePriseEncharge,codeGarantie,DelaiCarenceFamilleActe,tauxPlafond,ticketModerateur,codePeriodicite,forfaitPlafond
,Depense,ROUND(((100*Depense)/forfaitPlafond),2)tauxDepnense  FROM (  
SELECT P.codeGcAssureur,b.idPolice,b.idCollege,b.codeProduit,
a.id AS idAdherent,a.numeroAdherent AS numeroAdherent,CONCAT(a.nom ,' ',a.prenoms ) AS Adherent,
b.id idbenef,b.numeroBeneficiaire,CONCAT(b.nomBeneficiaire,' ',b.prenomsBeneficiaire) AS Beneficiaire,
f.id idGarantie,f.idBaremePriseEncharge,f.codeGarantie,f.DelaiCarenceFamilleActe,f.tauxPlafond,f.ticketModerateur,f.codePeriodicite,f.forfaitPlafond
,SUM(IFNULL(prt.montantApayer,0))  AS Depense,prt.numeroFeuilleMaladie
FROM beneficiaire b 
INNER JOIN adherent a ON ((b.codeSociete=a.codeSociete) AND  (a.id=b.idAdherent) AND (a.idPolice=b.idPolice) AND (a.idCollege=b.idCollege ))
INNER JOIN college c ON (b.idCollege=c.id)
INNER JOIN garantiesbaremepriseencharge f ON (f.idBaremePriseEnCharge=c.idBaremePriseEnCharge)
INNER JOIN p_prestationactes prt ON (prt.idBeneficiaire=b.id)
INNER JOIN p_facture B ON (B.id=prt.idfacture)
INNER JOIN police P ON  (B.idPolice = P.id)
INNER JOIN clients cl ON (cl.id=P.idClient) AND cl.id<>'49'
INNER JOIN gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-12-31' )
AND prt.CodeGarantie=f.codeGarantie AND f.forfaitPlafond NOT LIKE '%999'
AND G.id!='45'  AND (B.codeSociete="vitalis") AND prt.montantApayer>0   -- AND  prt.numeroBeneficiaire='0082043301'
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
GROUP BY P.codeGcAssureur,b.idPolice,b.idCollege,b.codeProduit,a.id,a.numeroAdherent,a.nom,a.prenoms,
b.id,b.numeroBeneficiaire,b.nomBeneficiaire,b.prenomsBeneficiaire,prt.numeroFeuilleMaladie,
f.id,f.idBaremePriseEncharge,f.codeGarantie,f.DelaiCarenceFamilleActe,f.tauxPlafond,f.ticketModerateur,f.forfaitPlafond,f.codePeriodicite
HAVING SUM(IFNULL(prt.montantApayer,0)) <f.forfaitPlafond
) plafondGarantie
HAVING tauxDepnense>=80
ORDER BY tauxDepnense DESC


 -- 1 RECHERCHE TAUX AU NIVEAU DES FAMILLES DES ACTES / BENEFICIAIRES
  SELECT  codeGcAssureur,idPolice,idCollege,
idAdherent,numeroAdherent, Adherent,idBenef,numeroBeneficiaire,Beneficiaire,
idGarantie,idBaremePriseEncharge,codeGarantie,codeProduit,codeFamilleActe,libelle,tauxPlafond,ticketModerateur,codePeriodicite,forfaitPlafond
,Depense,ROUND(((100*Depense)/forfaitPlafond),2)tauxDepnense  FROM (  
SELECT P.codeGcAssureur,b.idPolice,b.idCollege,
a.id AS idAdherent,a.numeroAdherent AS numeroAdherent,CONCAT(a.nom ,' ',a.prenoms ) AS Adherent,
b.id AS idbenef,b.numeroBeneficiaire,CONCAT(b.nomBeneficiaire,' ',b.prenomsBeneficiaire) AS Beneficiaire,
f.id AS idGarantie,f.idBaremePriseEncharge,f.codeGarantie,f.CodeProduit,f.codeFamilleActe,f.libelle,f.tauxPlafond,f.ticketModerateur,f.codePeriodicite ,f.forfaitPlafond  
,SUM(IFNULL(prt.montantApayer,0)) AS Depense,prt.numeroFeuilleMaladie
FROM beneficiaire b 
INNER JOIN adherent a ON ((b.codeSociete=a.codeSociete) AND  (a.id=b.idAdherent) AND (a.idPolice=b.idPolice) AND (a.idCollege=b.idCollege ))
INNER JOIN college c ON (b.idCollege=c.id)
INNER JOIN familleactebaremepriseencharge f ON (f.idBaremePriseEnCharge=c.idBaremePriseEnCharge)
INNER JOIN p_prestationactes prt ON (prt.idBeneficiaire=b.id)
INNER JOIN p_facture B ON (B.id=prt.idfacture)
INNER JOIN police P ON  (B.idPolice = P.id)
INNER JOIN clients cl ON (cl.id=P.idClient) AND cl.id<>'49'
INNER JOIN gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-12-31' )
AND prt.CodeGarantie=f.codeGarantie AND f.forfaitPlafond NOT LIKE '%999'
AND G.id!='45'  AND (B.codeSociete="vitalis")
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
GROUP BY P.codeGcAssureur,b.idPolice,b.idCollege,b.codeProduit,a.id,a.numeroAdherent,a.nom,a.prenoms,
b.id,b.numeroBeneficiaire,b.nomBeneficiaire,b.prenomsBeneficiaire,prt.numeroFeuilleMaladie,
f.id,f.idBaremePriseEncharge,f.codeGarantie,f.CodeProduit,f.codeFamilleActe,f.libelle,f.tauxPlafond,f.ticketModerateur,f.forfaitPlafond,f.codePeriodicite   
HAVING SUM(IFNULL(prt.montantApayer,0))>0 AND SUM(IFNULL(prt.montantApayer,0))<f.forfaitPlafond
) plafondGarantie
HAVING tauxDepnense>=80
ORDER BY tauxDepnense DESC
 
  -- 2 RECHERCHE TAUX AU NIVEAU DES ACTES	
 SELECT  codeGcAssureur,idPolice,idCollege,codeProduit,
idAdherent,numeroAdherent, Adherent,idBenef,numeroBeneficiaire,Beneficiaire,
idGarantie,idBaremePriseEncharge,codeGarantie,codeProduit,codeFamilleActe,libelle,tauxPlafond,ticketModerateur,codePeriodicite,forfaitPlafond
,Depense,ROUND(((100*Depense)/forfaitPlafond),2)tauxDepnense  FROM (  

SELECT P.codeGcAssureur,b.idPolice,b.idCollege,b.codeProduit,
a.id AS idAdherent,a.numeroAdherent AS numeroAdherent,CONCAT(a.nom ,' ',a.prenoms ) AS Adherent,
b.id AS idBenef,b.numeroBeneficiaire,CONCAT(b.nomBeneficiaire,' ',b.prenomsBeneficiaire) AS Beneficiaire,
f.id AS idGarantie,f.idBaremePriseEncharge,f.codeGarantie,f.CodeProduit,f.codeFamilleActe,f.codeActe
,f.libelle,f.tauxPlafond,f.ticketModerateur,f.ententePrealable,f.codePeriodicite,f.forfaitPlafond 
,SUM(IFNULL(prt.montantApayer,0)) AS Depense,prt.numeroFeuilleMaladie
FROM beneficiaire b 
INNER JOIN adherent a ON ((b.codeSociete=a.codeSociete) AND  (a.id=b.idAdherent) AND (a.idPolice=b.idPolice) AND (a.idCollege=b.idCollege ))
INNER JOIN college c ON (b.idCollege=c.id)
INNER JOIN actesbaremepriseencharge f ON (f.idBaremePriseEnCharge=c.idBaremePriseEnCharge)
INNER JOIN p_prestationactes prt ON (prt.idBeneficiaire=b.id)
INNER JOIN p_facture B ON (B.id=prt.idfacture)
INNER JOIN police P ON  (B.idPolice = P.id)
INNER JOIN clients cl ON (cl.id=P.idClient) AND cl.id<>'49'
INNER JOIN gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-12-31' )
AND prt.CodeGarantie=f.codeGarantie AND f.forfaitPlafond NOT LIKE '%999'
AND G.id!='45'  AND (B.codeSociete="vitalis")
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
GROUP BY P.codeGcAssureur,b.idPolice,b.idCollege,b.codeProduit,a.id,a.numeroAdherent,a.nom,a.prenoms,
b.id,b.numeroBeneficiaire,b.nomBeneficiaire,b.prenomsBeneficiaire,
f.id,f.idBaremePriseEncharge,f.codeGarantie,f.CodeProduit,f.codeFamilleActe,f.libelle,f.tauxPlafond,f.ticketModerateur,f.forfaitPlafond,f.codePeriodicite   
HAVING SUM(IFNULL(prt.montantApayer,0))>0 AND SUM(IFNULL(prt.montantApayer,0))<f.forfaitPlafond

) plafondGarantie
HAVING tauxDepnense>=80
ORDER BY tauxDepnense DESC
