
-- SELECT * FROM prestataire WHERE libelle LIKE "%blo%"
DROP TEMPORARY TABLE IF EXISTS temp_facture_leger;
 
CREATE TEMPORARY TABLE temp_facture_leger
( INDEX(idFacture) , INDEX(idBeneficiaire) , INDEX(codePrestataire))
SELECT
	B.id AS idFacture,
	B.dateSysteme AS dateFacture,
	B.codePrestataire,
	B.idBeneficiaire,
	f.numeroBonConsultation,
	f.numeroBonOrdonnance,
	f.numeroBonHospitalisation,
	f.numeroBonOptique,
	f.numeroBonExamen,
	f.numeroBonKine,
	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 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 "2022-04-01" AND "2022-06-30")
-- AND (CASE WHEN p_garant IS NULL THEN G.codeGcAssureur IS NOT NULL ELSE G.codeGcAssureur=p_garant END)  
AND (CASE WHEN 229 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=229 END)  
-- AND (CASE WHEN p_idClient IS NULL THEN C.idClient IS NOT NULL ELSE C.idClient=p_idClient END)  
-- AND (CASE WHEN p_idCollege IS NULL THEN e.idCollege IS NOT NULL ELSE e.idCollege=p_idCollege END) 
AND G.id!='45' AND C.idClient!='49' AND B.montantApayer>0
AND B.codePrestataire IN ('PR0332')
-- AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
GROUP BY B.id,B.dateSysteme,B.codePrestataire,B.idBeneficiaire,f.numeroBonConsultation,
	f.numeroBonOrdonnance,f.numeroBonHospitalisation,f.numeroBonOptique,
	f.numeroBonExamen,f.numeroBonKine
ORDER BY B.id;


SELECT 
	A.idFacture,
	A.dateFacture,
	e.idAdherent,a.numeroAdherent AS NoAdherent,CONCAT(a.nom,'',a.prenoms)adherent,		
	e.id,e.numeroBeneficiaire AS NoBeneficiaire,CONCAT(e.nomBeneficiaire,'',e.prenomsBeneficiaire) beneficiaire,	
	PR.libelle AS prestataire,		
	A.numeroBonConsultation,
	A.numeroBonOrdonnance,
	A.numeroBonHospitalisation,
	A.numeroBonOptique,
	A.numeroBonExamen,
	A.numeroBonKine,
	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 e.idAdherent,A.idBeneficiaire,A.Arembourser;	
DROP TEMPORARY TABLE  temp_facture_leger;