-- 1--- analyse depense
SELECT 
 --  B.numeroFeuilleMaladie,
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-03-31' )
 AND G.codeGcAssureur='GNA'  
AND B.idPolice=168   -- AND prt.idBeneficiaire=11898
-- and B.numeroFeuilleMaladie=7444 
AND prt.montantApayer>0
AND G.id!='45'  AND (B.codeSociete="vitalis")
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')  -- 30 690 884
GROUP BY prt.numeroFeuilleMaladie
ORDER BY apresDecompte DESC 

-- 2--- analyse depense croisé   -- 30 718 790
SELECT  
-- prt.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 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 be ON ((prt.idBeneficiaire=be.id)) -- (a.id=be.idAdherent) AND (be.idPolice=a.idPolice) AND 
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-03-31' )
AND G.codeGcAssureur='GNA'  AND B.idPolice=168
AND G.id!='45'  AND (B.codeSociete="vitalis")
-- AND (prt.tiersPayant="1")  
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
GROUP BY prt.idBeneficiaire
ORDER BY apresDecompte DESC 

-- 3--- analyse depense Lien parente
SELECT  be.CodeLienParente,
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 be ON ((prt.idBeneficiaire=be.id)) -- (a.id=be.idAdherent) AND (be.idPolice=a.idPolice) AND 
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-03-31' )
AND G.codeGcAssureur='GNA'  AND B.idPolice=168
AND G.id!='45'  AND (B.codeSociete="vitalis")
-- AND (prt.tiersPayant="1")  
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
GROUP BY be.CodeLienParente
ORDER BY CodeLienParente ASC 

-- 4-- creation de table temporaire
DROP TEMPORARY TABLE beneficiaireage_temp2
CREATE TEMPORARY TABLE beneficiaireage_temp2
SELECT 
A.*,
DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(A.dateNaissance, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(A.dateNaissance, '00-%m-%d')) AS age
FROM beneficiaire A 
INNER JOIN adherent B ON ((A.codeSociete=B.codeSociete) AND  (B.id=A.idAdherent) AND (B.idPolice=A.idPolice) AND (B.idCollege=A.idCollege ))
INNER JOIN police C ON (C.id=A.idPolice)
INNER JOIN clients t ON ((A.codeSociete=t.codeSociete) AND  (t.id=C.idClient))	
INNER JOIN gc_assureur Gc ON (Gc.codeGcAssureur=C.codeGcAssureur)	
WHERE (A.idPolice>0) AND Gc.codeGcAssureur='GNA'  AND B.idPolice=168 ; -- AND A.id=12150 ;


-- AND CASE WHEN A.sorti = "1" THEN A.dateSortie >= '2022-03-31' ELSE A.dateSortie IS NULL END;

-- 5 --recherche depenses avec temporraires

SELECT 
-- prt.idBeneficiaire ,   --   -- 30 690 884
SUM(IFNULL(prt.montantApayer,0)) AS apresDecompte
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 beneficiaireage_temp2 f ON (f.id=prt.idBeneficiaire )		
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-03-31' )
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete="vitalis")
AND G.codeGcAssureur='GNA'  AND B.idPolice=168  -- and prt.idBeneficiaire=12150
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
GROUP BY prt.idBeneficiaire
ORDER BY apresDecompte DESC 


-- 6-- recherche des infos benef
SELECT B.id,B.idPolice,B.idCollege 
FROM beneficiaire A 
INNER JOIN adherent B ON ((A.codeSociete=B.codeSociete) AND  (B.id=A.idAdherent) AND (B.idPolice=A.idPolice) AND (B.idCollege=A.idCollege ))
WHERE A.id IN (4679,3160,7050,2820,8415,11898,6827)

-- 7 --recherche des infos des prestations

SELECT DISTINCT prt.idBeneficiaire,B.idPolice -- ,idPolice 
FROM p_prestationactes prt INNER JOIN p_facture B ON (B.id=prt.idfacture)
WHERE prt.idBeneficiaire IN (4679,3160,7050,2820,8415,11898,6827)
AND (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-03-31' )
AND B.idPolice=168

AND prt.idBeneficiaire IN (10577)


SELECT DISTINCT B.idPolice,B.id -- ,idPolice 
FROM p_prestationactes prt INNER JOIN p_facture B ON (B.id=prt.idfacture)
WHERE prt.idBeneficiaire IN (4679,3160,7050,2820,8415,11898,6827)
AND (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-03-31' )
-- AND B.idPolice=168

AND prt.idBeneficiaire IN (10577)


-- UPDATE p_facture SET idPolice=168 WHERE id IN (8861)

