	-- DECOMPTE PRESTTAIRE
	
	DROP TEMPORARY TABLE IF EXISTS temp_facture_leger;	 
	CREATE TEMPORARY TABLE temp_facture_leger
	SELECT
			
			L.libelle AS localite,F.codePrestataire,`fn_libelle_prestataire`(F.codePrestataire) AS prestataire,
			COUNT(DISTINCT F.numeroBonHospitalisation) AS Quantite,
			SUM(IFNULL(prt.fraisReel,0)) AS montantFacture,
			SUM(IFNULL(prt.montantApayer,0)) AS Arembourser	
	FROM p_prestationactes prt 
	INNER JOIN  p_facture B ON (B.id=prt.idfacture)
	INNER JOIN  police P ON  (B.idPolice = P.id)
	INNER JOIN  prestataire pr ON (B.codePrestataire=pr.codePrestataire)
	INNER JOIN  gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
	INNER JOIN adherent a ON ((P.id=a.idPolice) AND (P.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
	INNER JOIN  p_feuillemaladie F ON ((F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)  AND (F.codePrestataire=B.codePrestataire))
	LEFT JOIN localite AS L ON (L.codeLocalite = pr.codeLocalite)	
	WHERE (LEFT(B.dateSysteme,10)   BETWEEN '2023-01-01' AND '2023-01-31' )
	AND prt.codeGarantie='HOS' AND L.codeVille='01'
	AND G.id!='45' AND P.idClient!='49' AND prt.montantApayer!=0 AND F.numeroBonHospitalisation!=0
	AND F.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
	GROUP BY L.libelle,F.codePrestataire
	-- HAVING COUNT(DISTINCT F.numeroBonHospitalisation)>=5
	ORDER BY L.libelle,Quantite DESC ;
	
	
	-- pour le detail par localite

	SELECT
		DISTINCT L.libelle AS localite,T.codePrestataire,T.prestataire,
		e.idAdherent,a.numeroAdherent AS NoAdherent,CONCAT(a.nom,'',a.prenoms) AS adherent,		
		e.id AS idBeneficiaire,e.numeroBeneficiaire AS NoBeneficiaire,CONCAT(e.nomBeneficiaire,'',e.prenomsBeneficiaire) AS beneficiaire,	
		F.numeroBonHospitalisation,COUNT(DISTINCT F.numeroBonHospitalisation) AS Quantite,		
		SUM(IFNULL(prt.fraisReel,0)) AS fraisReel,
		SUM(IFNULL(prt.montantApayer,0)) AS montantApayer,
		Arembourser AS totalPrestataire
	FROM p_prestationactes prt 
	INNER JOIN  temp_facture_leger T ON (T.codePrestataire=prt.codePrestataire)
	INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
	INNER JOIN  p_facture B ON (B.id=prt.idfacture)
	INNER JOIN  police P ON  (B.idPolice = P.id)
	INNER JOIN  prestataire pr ON (B.codePrestataire=pr.codePrestataire)
	INNER JOIN  gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
	INNER JOIN adherent a ON ((P.id=a.idPolice) AND (P.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))	
	INNER JOIN  p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
	LEFT JOIN localite AS L ON (L.codeLocalite = pr.codeLocalite)	
	WHERE (LEFT(B.dateSysteme,10)   BETWEEN '2023-01-01' AND '2023-01-31' )
	AND prt.codeGarantie='HOS' AND L.codeVille='01'
	AND G.id!='45' AND P.idClient!='49' AND prt.montantApayer!=0 AND F.numeroBonHospitalisation!=0
	AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
	GROUP BY e.idAdherent,a.numeroAdherent,a.nom,a.prenoms,e.id,e.numeroBeneficiaire ,e.nomBeneficiaire,e.prenomsBeneficiaire,	
		T.prestataire,T.Quantite,T.montantFacture,T.Arembourser,T.codePrestataire,T.prestataire,L.libelle ,
		F.numeroBonHospitalisation
	ORDER BY L.libelle,codePrestataire DESC ;
	
	