-- Depense genérale

SELECT 
SUM(IFNULL(prt.fraisReel,0)) AS reclamer,
SUM(IFNULL(prt.montantApayer,0)) AS Apayer
/*FROM p_prestationactes prt INNER JOIN  p_facture B ON (B.id=prt.idfacture)
INNER JOIN beneficiaire Be ON ( Be.id=prt.idBeneficiaire )
INNER JOIN  police P ON  (B.idPolice = P.id)
INNER JOIN  gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
INNER JOIN prestataire p  ON (prt.codePrestataire=p.codePrestataire)
INNER JOIN  p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-06-30')
AND prt.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175') 
AND G.id!='45' AND P.idClient!='49'    AND (B.codeSociete="vitalis") 
AND B.idPolice=229 AND prt.montantApayer>0 */

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 beneficiaire Be ON ( Be.id=prt.idBeneficiaire )
INNER JOIN gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
INNER JOIN prestataire p  ON (prt.codePrestataire=p.codePrestataire)
INNER JOIN  p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-06-30' )
AND G.id!='45' AND P.idClient!='49'  AND (B.codeSociete="vitalis")
AND B.idPolice=229 AND prt.montantApayer>0 
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')  --  558 522 492*/










SELECT  
-- B.idBeneficiaire,
	-- SUM(IFNULL(prt.fraisRetenu,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 clients cl ON (cl.id=P.idClient) AND cl.id<>'49'
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 b ON ((b.codeSociete=P.codeSociete)AND (b.id=prt.idBeneficiaire)  )
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie) -- AND (F.idBeneficiaire=prt.idBeneficiaire) 
-- INNER JOIN  garantie fg ON (prt.CodeGarantie=fg.CodeGarantie) 
-- AND prt.CodeGarantie='PHA'
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-03-31' )
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
AND B.idPolice=156   -- and  B.idAdherent in (3043)
AND G.codeGcAssureur='AMSA' 
AND G.id!='45'  
AND (B.codeSociete="vitalis")
GROUP BY B.idBeneficiaire
ORDER BY apresDecompte DESC 

-- 114 468 410

-- 2--- analyse depense croisé
SELECT 
B.idBeneficiaire,
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)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-12-31' )
-- AND G.codeGcAssureur='AMSA'  AND B.idPolice=156
AND G.id!='45'  AND (B.codeSociete="vitalis")
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
-- AND prt.numeroBeneficiaire='0185003201'
GROUP BY B.idBeneficiaire
ORDER BY apresDecompte DESC 

