
SELECT * FROM p_feuillemaladie
WHERE numeroFeuilleMaladie=40229

SELECT DISTINCT c.* 
FROM p_feuillemaladie f
INNER JOIN p_prestationactes p ON (f.numeroFeuilleMaladie=p.numeroFeuilleMaladie)
LEFT JOIN p_facture c ON (c.id=p.idFacture)
WHERE f.numeroFeuilleMaladie=40229


f.fraisReel=0  AND f.fraisRetenu=0 AND f.tiersPayant=1 AND f.montantArembourser=0
AND YEAR(LEFT(f.dateSysteme,10))=2022
AND MONTH(LEFT(f.dateSysteme,10))>=7
AND p.codeActe IN ("C","CS","CD","CV","CVU","CSAO","CSP","CVUS","CAG","CHF","CGP","CSPSY")
AND 
ORDER BY f.dateSysteme ;

SELECT * FROM p_prestationactes
WHERE numeroFeuilleMaladie=40229




UPDATE p_feuillemaladie,p_prestationactes
-- ,p_facture
SET p_feuillemaladie.fraisReel=SUM(p_prestationactes.fraisReel),
p_feuillemaladie.montantTm= SUM(p_prestationactes.montantTm),
p_feuillemaladie.montantARembourser=SUM(p_prestationactes.montantARembourser),
p_feuillemaladie.fraisRetenu=SUM(p_prestationactes.fraisRetenu),
p_feuillemaladie.fraisAssure=SUM(p_prestationactes.montantTm),
p_feuillemaladie.prixBase=SUM(p_prestationactes.prixBase),
p_feuillemaladie.fraisReelBase=SUM(p_prestationactes.fraisReelBase),
-- 
/*p_facture.fraisReel=SUM(p_prestationactes.fraisReel),
p_facture.montantTm= SUM(p_prestationactes.montantTm),
p_facture.montantARembourser=SUM(p_prestationactes.montantARembourser),
p_facture.montantApayerCalc=SUM(p_prestationactes.montantARembourser),
p_facture.montantApayer=SUM(p_prestationactes.montantARembourser),
p_facture.fraisRetenu=SUM(p_prestationactes.fraisRetenu),
p_facture.fraisAssure=SUM(p_prestationactes.montantTm),
p_facture.prixBase=SUM(p_prestationactes.prixBase),
p_facture.fraisReelBase=SUM(p_prestationactes.fraisReelBase)*/
WHERE p_prestationactes.numeroFeuilleMaladie=p_feuillemaladie.numeroFeuilleMaladie
--  AND p_facture.id=p_feuillemaladie.idFactureFeuille 
AND p_feuillemaladie.fraisReel=0  AND p_feuillemaladie.fraisRetenu=0 AND p_feuillemaladie.tiersPayant=1 AND p_feuillemaladie.montantArembourser=0
AND YEAR(LEFT(p_feuillemaladie.dateSysteme,10))=2022
AND MONTH(LEFT(p_feuillemaladie.dateSysteme,10))>=7
AND p_prestationactes.codeActe IN ("C","CS","CD","CV","CVU","CSAO","CSP","CVUS","CAG","CHF","CGP","CSPSY")
AND p_feuillemaladie.numeroFeuilleMaladie=40229


SELECT -- DISTINCT p_feuillemaladie.*
ententePrealable,autorisation,consultation,
p_feuillemaladie.fraisReel,p_feuillemaladie.montantTm,p_feuillemaladie.montantARembourser,
p_feuillemaladie.fraisRetenu,p_feuillemaladie.fraisAssure,p_feuillemaladie.prixBase,
p_feuillemaladie.fraisReelBase
FROM p_feuillemaladie,p_prestationactes
WHERE p_prestationactes.numeroFeuilleMaladie=p_feuillemaladie.numeroFeuilleMaladie
AND  (consultation IS NULL OR consultation="")
AND YEAR(LEFT(p_feuillemaladie.dateSysteme,10))=2022
AND MONTH(LEFT(p_feuillemaladie.dateSysteme,10))>=7
AND p_prestationactes.codeActe IN ("C","CS","CD","CV","CVU","CSAO","CSP","CVUS","CAG","CHF","CGP","CSPSY")
AND p_feuillemaladie.fraisReel=0  AND p_feuillemaladie.fraisRetenu=0 AND p_feuillemaladie.tiersPayant=1 AND p_feuillemaladie.montantArembourser=0



SELECT -- DISTINCT p_feuillemaladie.*
DISTINCT ententePrealable,autorisation,consultation,
p_feuillemaladie.fraisReel,p_feuillemaladie.montantTm,p_feuillemaladie.montantARembourser,
p_feuillemaladie.fraisRetenu,p_feuillemaladie.fraisAssure,p_feuillemaladie.prixBase,
p_feuillemaladie.fraisReelBase
FROM p_feuillemaladie,p_prestationactes
WHERE p_prestationactes.numeroFeuilleMaladie=p_feuillemaladie.numeroFeuilleMaladie
AND  p_feuillemaladie.numeroFeuilleMaladie=40229
AND (ententePrealable<"2") 
	AND (autorisation<"2")
	AND (consultation="1");	


SELECT -- ententePrealable<"2") autorisation<"2") (consultation="1");	
numeroFeuilleMaladie,p_feuillemaladie.fraisReel,p_feuillemaladie.montantTm,p_feuillemaladie.montantARembourser,
p_feuillemaladie.fraisRetenu,p_feuillemaladie.fraisAssure,p_feuillemaladie.prixBase,
p_feuillemaladie.fraisReelBase
FROM p_feuillemaladie
WHERE p_feuillemaladie.numeroFeuilleMaladie=40229
AND (ententePrealable<"2") 
	AND (autorisation<"2")
	AND (consultation="1");	


-- MISE A 
-- SANS FACTURE
UPDATE p_feuillemaladie JOIN (

SELECT numeroFeuilleMaladie,SUM(p_prestationactes.fraisReel) AS fraisReel,
SUM(p_prestationactes.montantTm) AS montantTm,SUM(p_prestationactes.montantARembourser) AS montantARembourser,
SUM(p_prestationactes.fraisRetenu) AS fraisRetenu ,SUM(p_prestationactes.prixBase) AS prixBase,
SUM(p_prestationactes.fraisReelBase) AS fraisReelBase 
FROM p_prestationactes
WHERE  p_prestationactes.codeActe IN ("C","CS","CD","CV","CVU","CSAO","CSP","CVUS","CAG","CHF","CGP","CSPSY")
AND YEAR(LEFT(p_prestationactes.dateSysteme,10))=2022 AND MONTH(LEFT(p_prestationactes.dateSysteme,10))>=7
AND tiersPayant=1 
-- AND numeroFeuilleMaladie in ()
GROUP BY numeroFeuilleMaladie ORDER BY numeroFeuilleMaladie

) p
ON p_feuillemaladie.numeroFeuilleMaladie=p.numeroFeuilleMaladie
SET p_feuillemaladie.fraisReel=p.fraisReel,p_feuillemaladie.montantTm= p.montantTm,
p_feuillemaladie.montantARembourser=p.montantARembourser,p_feuillemaladie.fraisRetenu=p.fraisRetenu,
p_feuillemaladie.fraisAssure=p.montantTm,p_feuillemaladie.prixBase=p.prixBase,
p_feuillemaladie.fraisReelBase=p.fraisReelBase
WHERE p_feuillemaladie.tiersPayant=1 
AND YEAR(LEFT(p_feuillemaladie.dateSysteme,10))=2022 AND MONTH(LEFT(p_feuillemaladie.dateSysteme,10))>=7
AND p_feuillemaladie.fraisReel=0  AND p_feuillemaladie.fraisRetenu=0 AND p_feuillemaladie.montantArembourser=0


-- AVEC FACTURE FACTURE
UPDATE p_feuillemaladie JOIN (
SELECT numeroFeuilleMaladie,SUM(p_prestationactes.fraisReel) AS fraisReel,
SUM(p_prestationactes.montantTm) AS montantTm,SUM(p_prestationactes.montantARembourser) AS montantARembourser,
SUM(p_prestationactes.fraisRetenu) AS fraisRetenu ,SUM(p_prestationactes.prixBase) AS prixBase,
SUM(p_prestationactes.fraisReelBase) AS fraisReelBase
FROM p_prestationactes
WHERE numeroFeuilleMaladie=40229 GROUP BY numeroFeuilleMaladie ORDER BY numeroFeuilleMaladie) p
ON p_feuillemaladie.numeroFeuilleMaladie=p.numeroFeuilleMaladie
LEFT JOIN p_facture ()
SET p_feuillemaladie.fraisReel=p.fraisReel,p_feuillemaladie.montantTm= p.montantTm,
p_feuillemaladie.montantARembourser=p.montantARembourser,p_feuillemaladie.fraisRetenu=p.fraisRetenu,
p_feuillemaladie.fraisAssure=p.montantTm,p_feuillemaladie.prixBase=p.prixBase,
p_feuillemaladie.fraisReelBase=p.fraisReelBase
WHERE p_feuillemaladie.tiersPayant=1 
AND YEAR(LEFT(p_feuillemaladie.dateSysteme,10))=2022NAND MONTH(LEFT(p_feuillemaladie.dateSysteme,10))>=7




