	DROP TEMPORARY TABLE IF EXISTS temp_facture_leger;
	 
	CREATE TEMPORARY TABLE temp_facture_leger
	( INDEX(idFacture) , INDEX(idBeneficiaire) , INDEX(codePrestataire))
	SELECT
		prt.id AS idFacture,
		prt.dateSysteme AS dateFacture,
		prt.codePrestataire,
		fn_libelle_prestataire(pm.codePrestatairePrescription) AS Prescripteur,
		fn_libelle_prestataire(prt.codePrestataire) AS Exécutant,
		prt.idBeneficiaire,
		pm.numeroFeuilleMaladie,
		pm.numeroBonOrdonnance,
		f.codeAffection,af.libelle AS affection, 		
		m.codeForme,
		m.codeMedicament,
		m.CIP1,
		-- m.codeForme,
		m.libelle, pm.tm AS tmDOC,
		IFNULL(pm.fraisReel,0)AS montantReclame,IFNULL(pm.montantTm,0) AS TM,IFNULL(pm.montantArembourser,0) AS Arembourser 
	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  college o ON  (o.idPolice = C.id)
	INNER JOIN  gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)	
	INNER JOIN p_prestationmedicaments pm ON (pm.idPrestationactes = prt.id)
	INNER JOIN p_medicament m ON (pm.codeMedicament= m.codeMedicament)
	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-01-26' AND '2023-01-31' )
	-- AND (B.codeTypeFacture="PHAR")
	AND (CASE WHEN 'FIDRA' IS NULL THEN G.codeGcAssureur IS NOT NULL ELSE G.codeGcAssureur='FIDRA' END)  
	AND G.id!='45' AND C.idClient!='49' 
	AND e.codeProduit LIKE '%80S'  AND pm.tm=20
	-- AND B.valide=1 AND B.okDecompte=1
	AND prt.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276') 
	ORDER BY prt.id;
	
	DROP TEMPORARY TABLE IF EXISTS temp_facture_medu;	 
	CREATE TEMPORARY TABLE temp_facture_medu
	SELECT  
		A.numeroFeuilleMaladie,	A.numeroBonOrdonnance,SUM(IFNULL(A.Arembourser,0)) AS Total 	
	FROM temp_facture_leger A
	GROUP BY A.numeroFeuilleMaladie,A.numeroBonOrdonnance
	HAVING (CASE WHEN 25000>0 THEN SUM(IFNULL(A.Arembourser,0))>25000 ELSE SUM(IFNULL(A.Arembourser,0))>25000 END )	;	
	
 
	SELECT  B.garant,B.idPolice,B.numeroPolice,B.libellePolice,B.idCollege,B.codeProduit,
		-- A.idFacture,
		A.dateFacture,
		B.idAdherent,B.numeroAdherent AS NoAdherent,B.adherent,		
		B.idBeneficiaire,B.numeroBeneficiaire AS NoBeneficiaire,B.beneficiaire,			
		A.Prescripteur,
		A.Exécutant,
		A.codeForme,
		A.codeMedicament,
		A.libelle,
		A.numeroFeuilleMaladie,A.numeroBonOrdonnance,A.codeAffection, -- A.affection, 
		A.montantReclame, tmDOC,
		A.TM,
		A.Arembourser
		,IFNULL(M.Total,0) AS Total 
	FROM temp_facture_leger A
	LEFT JOIN prestataire PR ON (PR.codePrestataire = A.codePrestataire)
	LEFT JOIN vw_infos_contrat_assures B ON (B.idBeneficiaire = A.idBeneficiaire) 
	INNER JOIN temp_facture_medu M ON ((M.numeroFeuilleMaladie = A.numeroFeuilleMaladie) AND (M.numeroBonOrdonnance = A.numeroBonOrdonnance) )
	WHERE A.Arembourser>0
	ORDER BY B.garant,B.idPolice,B.idCollege,B.idAdherent,B.idBeneficiaire,A.numeroFeuilleMaladie,A.Arembourser;
	
	DROP TEMPORARY TABLE  temp_facture_leger;
	DROP TEMPORARY TABLE  temp_facture_medu;
	


-- ACTE NON COUVERT	
DROP TEMPORARY TABLE IF EXISTS temp_facture_leger; 
CREATE TEMPORARY TABLE temp_facture_leger
( INDEX(idFacture) , INDEX(idBeneficiaire) , INDEX(codePrestataire))
SELECT
	prt.dateSysteme AS dateComptable,
	prt.id AS idFacture,		
	prt.codePrestataire,
	prt.idBeneficiaire,
	prt.datePrestation,
	f.codeAffection,
	prt.codeActe,
	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 
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  (a.idPolice = C.id)
INNER JOIN  college o ON  (o.idPolice = C.id)
INNER JOIN  gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)
INNER JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie=prt.numeroFeuilleMaladie   AND f.idPolice = C.Id)
INNER JOIN p_affection af ON (f.codeAffection=af.codeAffection)
WHERE (LEFT(prt.dateSysteme,10)>'2023-01-01')
-- AND prt.montantTm=0
AND (CASE WHEN 'FIDRA' IS NULL THEN G.codeGcAssureur IS NOT NULL ELSE G.codeGcAssureur='FIDRA' END)  
AND prt.codeActe NOT IN ( SELECT codeActe FROM  actesbaremepriseencharge 
			  WHERE idBaremepriseencharge  IN ( SELECT id FROM `baremepriseencharge`
							    WHERE libelle LIKE '%FIDRA%'
				                            AND libelle LIKE '%SILVER%'
				                            )
	                )
GROUP BY prt.id,prt.dateSysteme,B.codePrestataire,B.idBeneficiaire,f.numeroBonConsultation,prt.codeActe,
		f.numeroBonOrdonnance,f.numeroBonHospitalisation,f.numeroBonOptique,f.codeAffection,prt.datePrestation,
		f.numeroBonExamen,f.numeroBonKine
	ORDER BY B.id;		
	
	
--
	 
SELECT 
C.codeGcAssureur,SUM(prt.montantApayer) AS APAYER,SUM(ROUND((prt.montantApayer*prt.tm)/100,0)) AS montantTM,
SUM(prt.montantApayer-ROUND((prt.montantApayer*prt.tm)/100,0))AS normalRembr
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  (a.idPolice = C.id)
WHERE (LEFT(prt.dateSysteme,10)>'2023-01-01')
AND prt.montantTm=0 AND prt.tm>0
AND C.codeGcAssureur!='GLOBAL CI' 
GROUP BY C.codeGcAssureur								
HAVING SUM(prt.montantApayer)>0
				