-- 1 -- RECHERCHE PRESTATAIRES
	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,pm.idPrestationactes,
		prt.dateSysteme AS dateFacture,
		prt.codePrestataire,
		prt.idBeneficiaire,
		pm.numeroFeuilleMaladie,
		pm.numeroBonOrdonnance,
		fn_libelle_prestataire(pm.codePrestataire) AS Executant,
		fn_libelle_prestataire(pm.codePrestatairePrescription) AS Prescripteur,
		m.codeForme,
		m.codeMedicament,
		m.CIP1,
		m.libelle,
		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 ( a.id=e.idAdherent)
	INNER JOIN police C ON  (e.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)
	INNER JOIN  prestataire r ON (r.codePrestataire= prt.codePrestataire) 
	WHERE (LEFT(prt.dateSysteme,10)  BETWEEN '2020-10-01' AND '2022-12-31' )
	AND r.codeTypePrestataire='PHA'  -- AND r.conventionne=1
	AND prt.codePrestataire='PR0549'
	AND G.id!='45' AND C.idClient!='49' 
	ORDER BY prt.id;
	

-- 2 PRESATATIRE PLUS DE 10 BONS
DROP TEMPORARY TABLE IF EXISTS temp_facture_medPlus;	 
CREATE TEMPORARY TABLE temp_facture_medPlus
SELECT  
	A.codePrestataire,SUM(IFNULL(A.Arembourser,0)) AS Total,COUNT(idPrestationactes) AS decompte 	
FROM temp_facture_leger A
INNER JOIN  prestataire r ON (r.codePrestataire=A.codePrestataire) 
WHERE r.conventionne=1
GROUP BY A.codePrestataire
HAVING COUNT(idPrestationactes) >=10;	


-- 3 PRESATATIRE PLUS DE 10 BONS
DROP TEMPORARY TABLE IF EXISTS temp_facture_medMoins;	 
CREATE TEMPORARY TABLE temp_facture_medMoins
SELECT  
	A.codePrestataire,SUM(IFNULL(A.Arembourser,0)) AS Total,COUNT(idPrestationactes) AS decompte 	
FROM temp_facture_leger A
INNER JOIN  prestataire r ON (r.codePrestataire=A.codePrestataire) 
WHERE r.conventionne=1
GROUP BY A.codePrestataire
HAVING COUNT(idPrestationactes) <10;	


-- 4 AUCUNE INFRMATION SUR LA CONVENTION MAIS QUI EDITENT DES BONS 
DROP TEMPORARY TABLE IF EXISTS temp_facture_medAucun;	 
CREATE TEMPORARY TABLE temp_facture_medAucun
SELECT  
	A.codePrestataire,SUM(IFNULL(A.Arembourser,0)) AS Total,COUNT(idPrestationactes) AS decompte 	
FROM temp_facture_leger A
INNER JOIN  prestataire r ON (r.codePrestataire=A.codePrestataire) 
WHERE (r.conventionne=0 OR r.conventionne IS NULL)
AND A.codePrestataire='PR0549'
GROUP BY A.codePrestataire;
-- HAVING COUNT(idPrestationactes) <10;	

-- 5 -- AFFICHAGE
SELECT 
	l.libelle AS Localité ,
	prt.codePrestataire,prt.libelle AS Prestataire,adresseGeo AS Situation,
CASE WHEN (prt.telephonePortable IS NOT NULL OR prt.telephonePortable='') THEN prt.telephonePortable  ELSE prt.telephoneFixe END AS Contact
,t.Total AS MontantRembourse,t.decompte AS Quantite
FROM prestataire prt 
INNER JOIN `typeprestataire` b ON (b.codeTypePrestataire=prt.codeTypePrestataire) AND prt.actif=1
INNER JOIN localite l ON (l.codePays=prt.codePays AND l.codeVille=prt.codeVille AND l.codeLocalite=prt.codeLocalite)
-- INNER JOIN temp_facture_medPlus t ON ((prt.codePrestataire=t.codePrestataire) AND (prt.conventionne=1))
-- INNER JOIN temp_facture_medMoins t ON ((prt.codePrestataire=t.codePrestataire) AND (prt.conventionne=1))
INNER JOIN temp_facture_medAucun t ON ((prt.codePrestataire=t.codePrestataire) AND (prt.conventionne=0 OR prt.conventionne IS NULL)) --  AUNCUN
WHERE prt.libelle NOT LIKE "%HORS%" AND prt.codeTypePrestataire='PHA'  
AND prt.codePrestataire NOT IN ('PR1661','PR1176','PR1175')
ORDER BY l.codeLocalite,b.codeTypePrestataire, MontantRembourse DESC ;

DROP TEMPORARY TABLE IF EXISTS temp_facture_medPlus;	

DROP TEMPORARY TABLE IF EXISTS temp_facture_medMoins;	

DROP TEMPORARY TABLE IF EXISTS temp_facture_medAucun;	

