-- 1-- MISE A JOUR PRESTATAIONS ACTES
UPDATE p_prestationactes A
JOIN beneficiaire B ON (B.id=A.idBeneficiaire)
JOIN entetecontrat C ON (C.idPolice=B.idPolice)
JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie=A.numeroFeuilleMaladie)
SET  
	A.fraisReel=A.valeurActe,
	A.montantTm=CASE WHEN f.tm<100 THEN  ROUND((A.valeurActe*f.tm)/100) ELSE A.valeurActe-f.tm END,
	A.montantARembourser=A.fraisReel-A.montantTm,
	A.baseRemboursement=A.valeurActe ,
	A.fraisRetenu=A.fraisReel-A.montantTm,
	A.montantApayerCalc=A.fraisReel-A.montantTm,
	A.montantApayer=A.fraisReel-A.montantTm,
	A.prixTarif=A.valeurActe,
	A.prixBase=A.valeurActe,
	A.fraisReelBase=A.valeurActe		
WHERE (A.idBeneficiaire=2091
AND A.debut BETWEEN C.dateEffet AND C.dateFin
AND C.id = fn_id_entetecontrat_beneficiaire_courant(2091))
AND datePrestation='2022-10-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,entetecontrat
	WHERE p_prestationactes.numeroFeuilleMaladie=p_feuillemaladie.numeroFeuilleMaladie
	AND entetecontrat.idPolice=p_feuillemaladie.idPolice 
	AND entetecontrat.id = fn_id_entetecontrat_beneficiaire_courant(2091)
AND p_prestationactes.idBeneficiaire=2091
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;
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_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') ;


-- verifiaction


	
SELECT
	A.codeActe,A.fraisReel,A.montantTm,
	A.montantARembourser,A.baseRemboursement,A.fraisRetenu,
	A.montantApayerCalc,A.montantApayer,A.prixTarif,A.prixBase,A.fraisReelBase
FROM p_prestationactes A
JOIN beneficiaire B ON (B.id=A.idBeneficiaire)
JOIN entetecontrat C ON (C.idPolice=B.idPolice)
JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie=A.numeroFeuilleMaladie)
WHERE (A.idBeneficiaire=2091
-- AND A.ententePrealable NOT IN ('2','9')
AND A.debut BETWEEN C.dateEffet AND C.dateFin
AND C.id = fn_id_entetecontrat_beneficiaire_courant(2091))
AND datePrestation='2022-10-31'
ORDER BY datePrestation DESC;