
	-- SELECT * FROM  prestataire where libelle like '%TSP%';

	
	DROP TEMPORARY TABLE IF EXISTS temp_facture_leger;	 
	CREATE TEMPORARY TABLE temp_facture_leger
	( INDEX(idFacture) , INDEX(idBeneficiaire) , INDEX(codePrestataire))
	SELECT
		LEFT(B.dateSysteme,7) AS DateComptable,	
		B.id AS idFacture,
		B.dateSysteme AS dateFacture,
		B.codePrestataire,
		B.idBeneficiaire,
		f.numeroBonConsultation,prt.codeActe,prt.datePrestation,
		f.numeroBonOrdonnance,
		f.numeroBonHospitalisation,
		f.numeroBonOptique,
		f.numeroBonExamen,
		f.numeroBonKine,
		-- B.fraisReel,B.montantTm AS TM,B.montantApayer AS Arembourser
		SUM(IFNULL(prt.fraisReel,0)) AS fraisReel,
		SUM(IFNULL(prt.montantTm,0)) AS TM,
		SUM(IFNULL(prt.montantApayer,0)) AS Arembourser
	FROM p_prestationactes prt 
	INNER JOIN p_facture B ON (B.id=prt.idfacture)
	INNER JOIN  police C ON  (B.idPolice = C.id)
	INNER JOIN  gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)
	INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
	INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
	INNER JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
	WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-03-31')
	AND e.idPolice=156 
	AND G.id!='45' AND C.idClient!='49' AND B.valide=1 AND B.okDecompte=1  AND prt.ententePrealable NOT IN ('2','9')
	AND prt.codePrestataire='CE1377'
	GROUP BY B.id,B.dateSysteme,B.codePrestataire,B.idBeneficiaire,f.numeroBonConsultation,
		f.numeroBonOrdonnance,f.numeroBonHospitalisation,f.numeroBonOptique,prt.codeActe,prt.datePrestation,
		f.numeroBonExamen,f.numeroBonKine
	ORDER BY B.id;
 
 
	SELECT  A.DateComptable,	
		A.idFacture,
		e.idAdherent,a.numeroAdherent AS NoAdherent,CONCAT(a.nom,'',a.prenoms)adherent,		
		e.id,e.numeroBeneficiaire AS NoBeneficiaire,CONCAT(e.nomBeneficiaire,'',e.prenomsBeneficiaire) beneficiaire,	
		A.datePrestation,A.codeActe,`fn_libelle_acte_rp`(A.codeActe)AS acte,
		A.numeroBonConsultation,
		A.numeroBonOrdonnance,
		A.numeroBonHospitalisation,
		A.numeroBonOptique,
		A.numeroBonExamen,
		A.fraisReel,
		A.TM,
		A.Arembourser
	FROM temp_facture_leger A
	LEFT JOIN prestataire PR ON (PR.codePrestataire = A.codePrestataire)
	LEFT JOIN beneficiaire e ON (e.id=A.idBeneficiaire)
	LEFT JOIN adherent a ON (a.id=e.idAdherent)	
	ORDER BY  A.DateComptable,A.datePrestation,e.idAdherent,A.idBeneficiaire,A.Arembourser;	
	DROP TEMPORARY TABLE  temp_facture_leger;