-- A - VISUALISATION DANS LA TABLE PRESTATTION ACTES
SET @valeurActe=8000;
UPDATE p_feuillemaladie,p_prestationactes,p_facture
SET  p_prestationactes.valeurActe=@valeurActe,
p_prestationactes.fraisReel=@valeurActe,
p_prestationactes.montantTm=CASE WHEN p_feuillemaladie.tm<100 THEN  ROUND((@valeurActe*p_feuillemaladie.tm)/100)
ELSE @valeurActe-p_feuillemaladie.tm END,
p_prestationactes.montantARembourser=p_prestationactes.fraisReel-p_prestationactes.montantTm,
p_prestationactes.baseRemboursement=@valeurActe ,
p_prestationactes.fraisRetenu=p_prestationactes.fraisReel-p_prestationactes.montantTm,
p_prestationactes.montantApayerCalc=p_prestationactes.fraisReel-p_prestationactes.montantTm,
p_prestationactes.montantApayer=p_prestationactes.fraisReel-p_prestationactes.montantTm,
p_prestationactes.prixTarif=@valeurActe,
p_prestationactes.prixBase=@valeurActe,
p_prestationactes.fraisReelBase=@valeurActe
WHERE p_prestationactes.idFacture=p_facture.id
AND  p_prestationactes.numeroFeuilleMaladie=p_feuillemaladie.numeroFeuilleMaladie
AND p_facture.id=p_feuillemaladie.idFactureFeuille 
-- AND p_feuillemaladie.numeroFeuilleMaladie=45506 
AND p_facture.codePrestataire IN ('CE0195') AND p_facture.idPolice=156 
AND p_prestationactes.codeActe='C'  -- AND p_prestationactes.fraisReel IN (12000,15000)
AND (LEFT(p_facture.dateSysteme,10) BETWEEN '2022-08-01' AND '2022-08-31');


-- B - VISUALISATION DANS LA TABLE PRESTATTION FEUILLE MALADIE
	DROP TEMPORARY TABLE IF EXISTS feuillemaladie_temp;	
	CREATE TEMPORARY TABLE feuillemaladie_temp
	SELECT
	p_feuillemaladie.id,
	SUM(p_prestationactes.fraisReel)AS fraisReel ,SUM(p_prestationactes.montantTm) montantTm ,SUM(p_prestationactes.montantApayer) montantARembourser ,
	SUM(p_prestationactes.montantApayer) fraisRetenu ,SUM(p_prestationactes.montantTm) fraisAssure ,SUM(p_prestationactes.fraisReel) prixBase,
	SUM(p_prestationactes.fraisReel)  fraisReelBase 
	FROM p_feuillemaladie,p_prestationactes,p_facture
	WHERE p_prestationactes.idFacture=p_facture.id
	AND  p_prestationactes.numeroFeuilleMaladie=p_feuillemaladie.numeroFeuilleMaladie
	AND p_facture.id=p_feuillemaladie.idFactureFeuille 
	AND p_facture.codePrestataire IN ('CE0195')
	--  AND p_facture.idPolice=156 
	AND p_prestationactes.ententePrealable NOT IN (2,9)
	AND (LEFT(p_facture.dateSysteme,10) BETWEEN '2022-08-01' AND '2022-08-31')
	GROUP BY p_feuillemaladie.id;

	UPDATE p_feuillemaladie p  
	INNER JOIN feuillemaladie_temp C ON (p.id=C.id)
	SET 
		p.fraisReel=C.fraisReel,
		p.prixBase=C.fraisReel,
		p.montantTm=C.montantTm,
		p.montantARembourser=C.fraisRetenu,
		p.prixBase=C.fraisReel,
		p.fraisRetenu=C.fraisRetenu,
		p.fraisAssure=C.montantTm		
	WHERE(LEFT(p.dateSysteme,10) BETWEEN '2022-08-01' AND '2022-08-31') ;
	DROP TEMPORARY TABLE IF EXISTS feuillemaladie_temp;	


-- C - VISUALISATION DANS LA TABLE PRESTATTION FACTURE
DROP TEMPORARY TABLE IF EXISTS depense_temp;	
CREATE TEMPORARY TABLE depense_temp
SELECT
p_facture.id,
SUM(p_prestationactes.fraisReel)AS fraisReel ,SUM(p_prestationactes.montantTm) montantTm ,SUM(p_prestationactes.montantApayer) montantARembourser ,
SUM(p_prestationactes.montantApayer) fraisRetenu ,SUM(p_prestationactes.montantTm) fraisAssure ,SUM(p_prestationactes.fraisReel) prixBase,
SUM(p_prestationactes.fraisReel)  fraisReelBase 
FROM p_feuillemaladie,p_prestationactes,p_facture
WHERE p_prestationactes.idFacture=p_facture.id
AND  p_prestationactes.numeroFeuilleMaladie=p_feuillemaladie.numeroFeuilleMaladie
AND p_facture.id=p_feuillemaladie.idFactureFeuille 
AND p_facture.codePrestataire IN ('CE0195')
--  AND p_facture.idPolice=156 
AND p_prestationactes.ententePrealable NOT IN (2,9)
AND (LEFT(p_facture.dateSysteme,10) BETWEEN '2022-08-01' AND '2022-08-31')
GROUP BY p_facture.id;


UPDATE p_facture p  
INNER JOIN depense_temp C ON (p.id=C.id)
SET 
	p.fraisReel=C.fraisReel,
	p.prixBase=C.fraisReel,
	p.fraisReelBase=C.fraisReel,
	p.fraisAssure=C.montantTm,
	p.montantARembourser=C.fraisRetenu,
	p.fraisRetenu=C.montantARembourser,
	p.montantApayerCalc=C.fraisRetenu,
	p.montantApayer=C.fraisRetenu		
WHERE(LEFT(p.dateSysteme,10) BETWEEN '2022-08-01' AND '2022-08-31') ;

-- MISE A JOUR DU RETRAITEMENT
UPDATE p_facture p  
INNER JOIN depense_temp C ON (p.id=C.id)
	SET 
	    p.dateRetraitement='2022-10-12',
	    p.userRetraitement='lionel',
	    p.montantApayerRetraitement=C.fraisRetenu	
WHERE(LEFT(p.dateSysteme,10) BETWEEN '2022-08-01' AND '2022-08-31') ;

DROP TEMPORARY TABLE IF EXISTS depense_temp;	


