SELECT p.* 
FROM p_facture f
INNER JOIN p_prestationactes p ON (f.id=p.idFacture)
INNER JOIN p_feuillemaladie e ON (e.numeroFeuilleMaladie=p.numeroFeuilleMaladie)
WHERE f.codeUtilisateur="kramo" 
AND  f.dateSysteme  BETWEEN "2022-07-29 15:40:00" AND "2022-07-29 15:52:00"
AND p.datePrestation<="2022-06-30"




-- 1 defacturer
UPDATE p_facture f
INNER JOIN p_prestationactes p ON (f.id=p.idFacture)
INNER JOIN p_feuillemaladie e ON (e.numeroFeuilleMaladie=p.numeroFeuilleMaladie)
LEFT JOIN p_prescription i ON (i.numeroFeuilleMaladie=e.numeroFeuilleMaladie)
LEFT JOIN p_examen m ON (m.numeroFeuilleMaladie=e.numeroFeuilleMaladie)
LEFT JOIN p_optique o ON (o.numeroFeuilleMaladie=e.numeroFeuilleMaladie)
LEFT JOIN p_prescriptionseance s ON (s.numeroFeuilleMaladie=e.numeroFeuilleMaladie)
LEFT JOIN p_prestationexamens es ON (es.numeroFeuilleMaladie=e.numeroFeuilleMaladie)
LEFT JOIN p_demandeententeprealable n ON ((n.numeroFeuilleMaladie=e.numeroFeuilleMaladie)
					  AND (f.codePrestataire=n.codePrestataire)
					  AND (p.codeActe=n.codeActe)AND (p.consultation="1"))
SET 
p.facture="0",p.idFacture="0" ,e.facture="0",e.userFacturation= "",es.idFacture="0",
i.facture="0",m.facture="0",e.facture="0",n.facture="0",o.facture="0",s.facture="0",
f.numeroBonSeance="0"
WHERE f.codeUtilisateur="kramo" 
AND  f.dateSysteme  BETWEEN "2022-07-29 15:40:00" AND "2022-07-29 15:52:00"
AND p.datePrestation<="2022-06-30";


-- 2 
DROP TEMPORARY TABLE IF EXISTS temp_factures;	 
CREATE TEMPORARY TABLE temp_factures
SELECT * FROM p_facture  
WHERE codeUtilisateur="kramo" 
AND  dateSysteme  
BETWEEN "2022-07-29 15:40:00" 
AND "2022-07-29 15:52:00" ;

UPDATE garantieadherent A
JOIN entetecontrat C ON (C.id=A.idEntetecontrat)
JOIN temp_factures B ON (B.idAdherent=A.idAdherent
AND B.codeGarantie=A.codeGarantie )
SET A.consommation = A.consommation-B.fraisRetenu ;

 	
UPDATE garantiebeneficiaire A
JOIN entetecontrat C ON (C.id=A.idEntetecontrat)
JOIN temp_factures B ON (B.idBeneficiaire=A.idBeneficiaire)
AND (B.codeGarantie=A.codeGarantie) 
SET A.consommation = A.consommation-B.fraisRetenu
WHERE (B.numeroFeuilleMaladie=A.numeroFeuilleMaladie) 
AND (B.codePrestataire=A.codePrestataire);

DROP TEMPORARY TABLE IF EXISTS temp_factures;	


-- 3 
DELETE FROM p_facture  WHERE codeUtilisateur="kramo" AND  dateSysteme  BETWEEN "2022-07-29 15:40:00" AND "2022-07-29 15:52:00"
