SELECT * FROM prestataire
WHERE libelle LIKE '%ciel%'
-- WHERE codePrestataire='PR0337'


-- SUPPREPRESSION FACTURES NON TRAITEES

SELECT B.* 
FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
WHERE (LEFT(prt.dateSysteme,10)  BETWEEN '2023-03-01' AND '2023-03-31' )
AND prt.codePrestataire='PR0337'
AND B.montantApayer=0
AND B.modeSaisieFacture=0

-- 

DELETE B
FROM p_prestationactes prt  
JOIN p_facture B ON (B.id=prt.idfacture)
WHERE (LEFT(prt.dateSysteme,10)  BETWEEN '2023-03-01' AND '2023-03-31' )
AND prt.codePrestataire='PR0337'
AND B.id IN (122441);




-- 0 -- EXISTANCE INFORMATION
SELECT * FROM p_facture
WHERE codePrestataire='PR0337'
AND (LEFT(dateSysteme,10) BETWEEN '2023-03-01' AND '2023-03-31' )
AND okDecompte=9


-- ANNULATION DE p_reglement

SELECT * FROM p_reglement WHERE codePrestataire='PR0337' AND numeroDecompte=5703;

-- 1 DELETE FROM p_reglement WHERE numeroDecompte=3857;


SELECT * FROM p_facture WHERE tiersPayant="1"  AND codePrestataire='PR0337' -- AND numeroDecompte=5703
AND (LEFT(dateSysteme,10) BETWEEN '2022-09-01' AND '2022-09-30' )

-- 2 UPDATE p_facture SET numeroDecompte=0,numeroLotDecompte=0,valide=0,dateTransmission=(NULL),codeExercice="",codeMois="" WHERE tiersPayant="1"  AND numeroDecompte=3857;

SELECT id,numeroDecompte,numeroLotDecompte,valide,okDecompte,dateTransmission,montantApayer,dateSysteme,codeExercice,codeMois
FROM p_facture 
WHERE tiersPayant="1"  AND codePrestataire='PR0337' -- AND numeroDecompte=5703
AND (LEFT(dateSysteme,10) BETWEEN '2023-03-01' AND '2022-11-30' )
-- 1- REJET :valide=9, okDecompte=0,

CALL `sp_perso_rejetFacture_rp`('PR0783','2022-11-01','2022-11-30');

-- verification
SELECT codePrestataire,id,numeroDecompte,numeroLotDecompte,valide,okDecompte,regle,dateTransmission,montantApayer,dateSysteme,codeExercice,codeMois
FROM p_facture 
WHERE tiersPayant="1"  AND numeroDecompte IN (6042);

-- 2- bascule valide='0',okDecompte='0',codeExercice,B.codeMois,B.numeroLotDecompte,B.numeroDecompte 

CALL `sp_perso_BasculeFacture_rp`('PR0337','2023-03-01','2023-03-31','2022-12-09','lionel'); -- PERIODE A BASCULER, DATE BASCULE N-1

-- verification
SELECT  
-- sum(montantApayer)
numeroFeuilleMaladie,
 dateSysteme,id,numeroDecompte,numeroLotDecompte,valide,okDecompte,dateTransmission,montantApayer,dateSysteme,codeExercice,codeMois,okBascule,userBascule,dateBascule
FROM p_facture 
WHERE tiersPayant="1"  AND codePrestataire='PR0783' -- AND numeroDecompte=5703
AND (LEFT(dateSysteme,10) BETWEEN '2022-11-01' AND '2022-11-30' )
-- and numeroFeuilleMaladie=8911
ORDER BY id,dateSysteme
-- 1- REJET :valide=9, okDecompte=0,








