SELECT

		LAST_DAY (LEFT(prt.dateSysteme,10)) DateComptable,prt.codePrestataire,`fn_libelle_prestataire`(prt.codePrestataire) AS prestataire
		,prt.idBeneficiaire,e.nomBeneficiaire,e.numeroBeneficiaire,
		e.prenomsBeneficiaire,prt.datePrestation,COUNT(0) decompte -- ,prt.montantApayer --

FROM p_prestationactes prt 
INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
INNER JOIN adherent a ON ((e.idPolice=a.idPolice) AND ( a.id=e.idAdherent))
INNER JOIN  police C ON  (e.idPolice = C.id)
INNER JOIN  gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)	
LEFT JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie =prt.numeroFeuilleMaladie )
LEFT JOIN `p_affection` af ON (f.codeAffection =af.codeAffection )
WHERE (LEFT(prt.dateSysteme,10)  BETWEEN '2023-04-01' AND '2023-04-30' )
AND G.id!='45' AND C.idClient!='49' 
-- AND f.numeroBonConsultation IN (79675,79700,79907,79926)
AND prt.codeActe='C'
-- AND B.valide=1 AND B.okDecompte=1
-- AND prt.codePrestataire IN ('CE1465') 
GROUP BY LAST_DAY (LEFT(prt.dateSysteme,10)),prt.codePrestataire,prt.idBeneficiaire,e.numeroBeneficiaire,e.nomBeneficiaire,e.prenomsBeneficiaire,prt.datePrestation -- ,prt.montantApayer
HAVING COUNT(0)>1
ORDER BY DateComptable,prt.codePrestataire,prt.datePrestation,prt.idBeneficiaire



-- SELECT * FROM p_feuillemaladie
-- WHERE numeroBonConsultation IN (79675,79700,79907,79926)



SELECT * FROM p_prestationactes
WHERE numeroFeuilleMaladie IN (79111,79146,79366,79385)


SELECT * FROM prestataire
WHERE libelle LIKE '%joseph%'



SELECT prt.* 
FROM p_prestationactes prt -- JOIN p_facture B ON (B.id=prt.idfacture)
JOIN `p_feuillemaladie` f ON (f.`numeroFeuilleMaladie`=prt.numeroFeuilleMaladie)
WHERE (LEFT(prt.dateSysteme,10)  BETWEEN '2023-02-20' AND '2023-02-20' )
AND prt.numeroBeneficiaire='0111000103'
AND prt.codeUtilisateur='ccmp318'


DELETE f,prt
FROM p_prestationactes prt -- JOIN p_facture B ON (B.id=prt.idfacture)
JOIN `p_feuillemaladie` f ON (f.`numeroFeuilleMaladie`=prt.numeroFeuilleMaladie)
WHERE (LEFT(prt.dateSysteme,10)  BETWEEN '2023-02-20' AND '2023-02-20' )
AND prt.numeroBeneficiaire='0111000103'
AND prt.codeUtilisateur='ccmp318'
 
 




-- 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-04-01' AND '2023-04-30' )
 AND prt.codePrestataire='CE1482'
AND B.montantApayer=0
AND B.modeSaisieFacture=0
AND B.montantExclues=0
AND B.montantRejete=0
-- AND B.valide=0



-- 


DELETE B,prt
FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
WHERE (LEFT(prt.dateSysteme,10)  BETWEEN '2023-04-01' AND '2023-04-30' )
 AND prt.codePrestataire='CE1482'
AND B.montantApayer=0
AND B.modeSaisieFacture=0
AND B.montantExclues=0
AND B.montantRejete=0
-- AND B.valide=0





DELETE prt 
FROM p_prestationactes prt  
WHERE (LEFT(prt.dateSysteme,10)  BETWEEN '2023-01-01' AND '2023-01-31' )
AND prt.codePrestataire='CE1482'
AND prt.idfacture IN (99326,100078,104974);



DELETE B 
FROM  p_facture B  
WHERE (LEFT(B.dateSysteme,10)  BETWEEN '2023-01-01' AND '2023-01-31' )
AND B.codePrestataire='CE1482'
AND B.id IN (99326,100078,104974);

