SELECT * FROM p_acte
WHERE libelle LIKE '%pedia%'


-- 1-- MISE A JOUR PRESTATAIONS ACTES
UPDATE p_prestationactes A
JOIN beneficiaire B ON (B.id=A.idBeneficiaire)
JOIN p_facture R ON (R.id=A.idfacture)
JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie=A.numeroFeuilleMaladie)
SET  
	A.fraisReel=12000,
	A.valeurActe=12000,
	A.montantTm=CASE WHEN f.tm<120 THEN  ROUND((12000*f.tm)/120) ELSE 12000-f.tm END,
	A.montantARembourser=A.fraisReel-A.montantTm,
	A.baseRemboursement=12000 ,
	A.fraisRetenu=A.fraisReel-A.montantTm,
	A.montantApayerCalc=A.fraisReel-A.montantTm,
	A.montantApayer=A.fraisReel-A.montantTm,
	A.prixTarif=12000,
	A.prixBase=12000,
	A.fraisReelBase=12000		
WHERE A.ententePrealable NOT IN ('2','9')
AND (LEFT(R.dateSysteme,12) BETWEEN '2022-12-01' AND '2022-12-31')
AND A.codeActe IN ('AC0001358','AC0001362') -- AND e.idPolice!=156=
AND R.valide=1 AND R.okDecompte=1   -- 15 808 695  -- 17 758 071
AND A.codePrestataire='PR1145' -- AND dateTransmission!='1900-01-01'



-- B - VISUALISATION DANS LA TABLE PRESTATTION FEUILLE MALADIE
DROP TEMPORARY TABLE IF EXISTS feuillemaladie_temp;	
CREATE TEMPORARY TABLE feuillemaladie_temp
SELECT
	 F.id,
	SUM(A.fraisReel)AS fraisReel ,SUM(A.montantTm) montantTm ,SUM(A.montantApayer) montantARembourser ,
	SUM(A.montantApayer) fraisRetenu ,SUM(A.montantTm) fraisAssure ,SUM(A.fraisReel) prixBase,
	SUM(A.fraisReel)  fraisReelBase 
FROM p_feuillemaladie F
INNER JOIN p_prestationactes A ON (A.numeroFeuilleMaladie=F.numeroFeuilleMaladie)
INNER JOIN p_facture R ON (R.id=A.idfacture)
WHERE A.ententePrealable NOT IN ('2','9')
AND (LEFT(R.dateSysteme,12) BETWEEN '2022-12-01' AND '2022-12-31')
-- AND A.codeActe IN ('AC0001358','AC0001362') -- AND e.idPolice!=156=
AND R.valide=1 AND R.okDecompte=1   -- 15 808 695  -- 17 758 071
AND A.codePrestataire='PR1145' -- AND dateTransmission!='1900-01-01'
GROUP BY F.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
INNER JOIN p_prestationactes ON p_prestationactes.numeroFeuilleMaladie=p_feuillemaladie.numeroFeuilleMaladie
INNER JOIN p_facture  ON p_prestationactes.idFacture=p_facture.id
WHERE
-- AND p_facture.id=p_feuillemaladie.idFactureFeuille 
  p_facture.codePrestataire IN ('PR1145')
AND p_prestationactes.ententePrealable NOT IN (2,9)
AND (LEFT(p_facture.dateSysteme,12) BETWEEN '2022-12-01' AND '2022-12-31')
-- AND p_prestationactes.codeActe='AC0001358' -- AND e.idPolice!=156
AND p_facture.valide=1 AND p_facture.okDecompte=1   -- 15 808 695  -- 17 758 071
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,12) BETWEEN '2022-12-01' AND '2022-12-31')
AND p.valide=1 AND p.okDecompte=1   -- 15 808 695  -- 17 758 071
AND p.codePrestataire IN ('PR1145');



-- C - VISUALISATION DANS LA TABLE PRESTATTION REGELEMENT
DROP TEMPORARY TABLE IF EXISTS depense_tempR;	
CREATE TEMPORARY TABLE depense_tempR
SELECT
	p_facture.numeroDecompte,SUM(p_prestationactes.fraisReel)AS fraisReel ,SUM(p_prestationactes.montantTm) montantTm ,SUM(p_prestationactes.montantARembourser) montantARembourser ,
	SUM(p_prestationactes.montantApayer) montantApayer ,SUM(p_prestationactes.montantTm) fraisAssure ,SUM(p_prestationactes.fraisReel) prixBase,
	SUM(p_prestationactes.fraisReel)  fraisReelBase 
FROM p_feuillemaladie
INNER JOIN p_prestationactes ON p_prestationactes.numeroFeuilleMaladie=p_feuillemaladie.numeroFeuilleMaladie
INNER JOIN p_facture  ON p_prestationactes.idFacture=p_facture.id
WHERE  p_facture.codePrestataire IN ('PR1145')
AND p_prestationactes.ententePrealable NOT IN (2,9)
AND (LEFT(p_facture.dateSysteme,12) BETWEEN '2022-12-01' AND '2022-12-31')
-- AND p_prestationactes.codeActe='AC0001358' -- AND e.idPolice!=156
AND p_facture.valide=1 AND p_facture.okDecompte=1   -- 15 808 695  -- 17 758 071
GROUP BY p_facture.numeroDecompte;



UPDATE p_reglement  p  
INNER JOIN depense_tempR C ON (p.`numeroDecompte` = C.numeroDecompte)
 INNER JOIN p_facture f ON (f.`numeroDecompte` = C.numeroDecompte)
SET 
	p.fraisReel=C.fraisReel,
	p.prixBase=C.fraisReel,
	p.fraisReelBase=C.fraisReel,
	-- p.fraisAssure=C.montantTm,
	p.montantARembourser=C.montantARembourser,
	p.fraisRetenu=C.montantARembourser,
	p.montantApayerCalc=C.montantARembourser,
	p.montantApayer=C.montantApayer		
WHERE(LEFT(f.dateSysteme,12) BETWEEN '2022-12-01' AND '2022-12-31')
AND f.valide=1 AND f.okDecompte=1   -- 15 808 695  -- 17 758 071
AND f.codePrestataire IN ('PR1145');


DROP TEMPORARY TABLE IF EXISTS feuillemaladie_temp;
DROP TEMPORARY TABLE IF EXISTS depense_temp;	
DROP TEMPORARY TABLE IF EXISTS depense_tempR;	



SELECT DISTINCT p.* FROM  p_reglement  p  
INNER JOIN p_facture N ON (p.`numeroDecompte` = N.numeroDecompte)	
WHERE(LEFT(N.dateSysteme,12) BETWEEN '2022-12-01' AND '2022-12-31')
AND N.valide=1 AND N.okDecompte=1   -- 15 808 695  -- 17 758 071
AND N.codePrestataire IN ('PR1145');









SELECT -- r.*   --1 833 040
-- DISTINCT 
-- r.*
SUM(p.montantApayer)  
FROM p_prestationactes a 
INNER JOIN p_facture p  ON (p.id = a.idfacture) 
INNER JOIN p_reglement r ON (r.`numeroDecompte` = p.numeroDecompte)
WHERE(LEFT(r.dateSysteme,12) BETWEEN '2022-12-01' AND '2022-12-31')
AND p.valide=1 AND p.okDecompte=1   -- 15 808 695  -- 17 758 071
AND p.codePrestataire IN ('PR1145');

-- verifiaction  -- 


	
SELECT
	-- R.*
	/*A.codeActe,A.fraisReel,A.montantTm,
	A.montantARembourser,A.baseRemboursement,A.fraisRetenu,
	A.montantApayerCalc,A.montantApayer,A.prixTarif,A.prixBase,A.fraisReelBase*/
	SUM(N.montantApayer)  
FROM p_prestationactes A
JOIN beneficiaire B ON (B.id=A.idBeneficiaire)
JOIN p_facture R ON (R.id=A.idfacture)
LEFT JOIN p_reglement N ON (R.`numeroDecompte` = N.numeroDecompte)
JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie=A.numeroFeuilleMaladie)
AND (LEFT(R.dateSysteme,12) BETWEEN '2022-12-01' AND '2022-12-31')
-- AND A.codeActe IN ('AC0001358','AC0001362') -- AND e.idPolice!=156=
AND R.valide=1 AND R.okDecompte=1   -- 15 808 695  -- 17 758 071
AND A.codePrestataire='PR1145' -- AND dateTransmission!='1900-01-01'