	-- 1 -- PRESCRIPTION GLOBAL MEDICAMENTEUSE GLOBALE MEDECIN	
	SELECT 	Bp.codePrestataire Compte,fn_libelle_prestataire(Bp.codePrestataire) AS Prestataire,
	fm.codeForme,fm.libelle,m.codeMedicament,m.libelle AS medicament,
	SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
	FROM p_prestationactes A
	INNER JOIN `p_prestationmedicaments` pm ON (pm.idPrestationactes = A.id)
	INNER JOIN `p_medicament` m ON (pm.codeMedicament = m.codeMedicament)
	INNER JOIN `p_formemedicament` fm ON (fm.codeForme = m.codeForme)
	INNER JOIN p_facture Bp ON (Bp.id=A.idfacture)
	INNER JOIN beneficiaire B ON ( B.id=A.idBeneficiaire )
	INNER JOIN police Po ON (Po.id=B.idPolice)
	INNER JOIN gc_assureur Gc ON  (Po.codeGcAssureur = Gc.codeGcAssureur)
	INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=Bp.numeroFeuilleMaladie)
	INNER JOIN adherent a ON ((Po.id=a.idPolice) AND (Po.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
	INNER JOIN prestataire p  ON (A.codePrestataire=p.codePrestataire)
	WHERE (LEFT(Bp.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-28-02')
	AND pm.codePrestatairePrescription IN ('CE1377')
	AND A.codeGarantieActe IN ('PHA') -- AND A.montantApayer>0  
	GROUP BY B.numeroBeneficiaire,Bp.codePrestataire,fm.codeForme,fm.libelle,m.codeMedicament,m.libelle
	ORDER BY Bp.codePrestataire,montantRembourse DESC  ; 
	
	-- 2 -- PRESCRIPTION GLOBAL DETAILLES MEDICAMENTEUSE GLOBALE MEDECIN	
	SELECT 	Bp.codePrestataire Compte,fn_libelle_prestataire(Bp.codePrestataire) AS Prestataire,
	a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire,
	fm.codeForme,fm.libelle,m.codeMedicament,m.libelle AS medicament,
	SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
	FROM p_prestationactes A
	INNER JOIN `p_prestationmedicaments` pm ON (pm.idPrestationactes = A.id)
	INNER JOIN `p_medicament` m ON (pm.codeMedicament = m.codeMedicament)
	INNER JOIN `p_formemedicament` fm ON (fm.codeForme = m.codeForme)
	INNER JOIN p_facture Bp ON (Bp.id=A.idfacture)
	INNER JOIN beneficiaire B ON ( B.id=A.idBeneficiaire )
	INNER JOIN police Po ON (Po.id=B.idPolice)
	INNER JOIN gc_assureur Gc ON  (Po.codeGcAssureur = Gc.codeGcAssureur)
	INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=Bp.numeroFeuilleMaladie)
	INNER JOIN adherent a ON ((Po.id=a.idPolice) AND (Po.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
	INNER JOIN prestataire p  ON (A.codePrestataire=p.codePrestataire)
	WHERE (LEFT(Bp.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-28-02')
	AND pm.codePrestatairePrescription IN ('CE1377')
	AND A.codeGarantieActe IN ('PHA')  AND A.montantApayer>0  
	GROUP BY B.numeroBeneficiaire,Bp.codePrestataire,fm.codeForme,fm.libelle,m.codeMedicament,m.libelle,
	a.id,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms),B.id,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)
	ORDER BY Bp.codePrestataire,a.id,montantRembourse DESC  ; 

	-- 3 -- PRESCRIPTION GLOBAL DETAILLES EXAMEN GLOBALE MEDECIN	
	SELECT 	Bp.codePrestataire Compte,fn_libelle_prestataire(Bp.codePrestataire) AS Prestataire,
	-- a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	-- B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire,
	fm.codeActe,fn_libelle_acte_rp (fm.codeActe) AS Examen ,
	SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
	FROM p_prestationactes A
	INNER JOIN `p_prestationexamens` pm ON (pm.idPrestationactes = A.id)
	INNER JOIN `p_detailexamen` fm ON (fm.numeroExamen = pm.numeroExamen)
	INNER JOIN p_facture Bp ON (Bp.id=A.idfacture)
	INNER JOIN beneficiaire B ON ( B.id=A.idBeneficiaire )
	INNER JOIN police Po ON (Po.id=B.idPolice)
	INNER JOIN gc_assureur Gc ON  (Po.codeGcAssureur = Gc.codeGcAssureur)
	INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=Bp.numeroFeuilleMaladie)
	INNER JOIN adherent a ON ((Po.id=a.idPolice) AND (Po.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
	INNER JOIN prestataire p  ON (A.codePrestataire=p.codePrestataire)
	WHERE (LEFT(Bp.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-28-02')
	AND A.montantApayer>0  
	GROUP BY B.numeroBeneficiaire,Bp.codePrestataire,fm.codeActe
	-- ,a.id,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms),B.id,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)
	ORDER BY Bp.codePrestataire,a.id,montantRembourse DESC  ; 




	-- 4 -- PRESCRIPTION GLOBAL DETAILLES EXAMEN GLOBALE MEDECIN	
	SELECT 	Bp.codePrestataire Compte,fn_libelle_prestataire(Bp.codePrestataire) AS Prestataire,
	a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire,
	fm.codeActe,fn_libelle_acte_rp (fm.codeActe) AS Examen ,
	SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
	FROM p_prestationactes A
	INNER JOIN `p_prestationexamens` pm ON (pm.idPrestationactes = A.id)
	INNER JOIN `p_detailexamen` fm ON (fm.numeroExamen = pm.numeroExamen)
	INNER JOIN p_facture Bp ON (Bp.id=A.idfacture)
	INNER JOIN beneficiaire B ON ( B.id=A.idBeneficiaire )
	INNER JOIN police Po ON (Po.id=B.idPolice)
	INNER JOIN gc_assureur Gc ON  (Po.codeGcAssureur = Gc.codeGcAssureur)
	INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=Bp.numeroFeuilleMaladie)
	INNER JOIN adherent a ON ((Po.id=a.idPolice) AND (Po.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
	INNER JOIN prestataire p  ON (A.codePrestataire=p.codePrestataire)
	WHERE (LEFT(Bp.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-28-02')
	AND A.montantApayer>0  
	GROUP BY B.numeroBeneficiaire,Bp.codePrestataire,fm.codeActe,
	a.id,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms),B.id,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)
	ORDER BY Bp.codePrestataire,a.id,montantRembourse DESC  ; 


