CALL sp_sondagePrestatationsVitalis_rp(NULL,NULL,NULL,NULL,'2023-02-01','2023-02-28',0,'PHA','ALLEBY');

-- BALLE
-- AMONDJI
-- ADJOBIA
-- ALLEBY


AND (CASE WHEN p_user='BALLE'  THEN pr.codeLocalite IN ('0106','1001','0402','0602','0713') 
	  WHEN p_user='AMONDJI'  THEN pr.codeLocalite IN ('0105','0305','0409','0202','0601')
	  WHEN p_user='ADJOBIA'  THEN pr.codeLocalite IN ('0111','1105','0102','0407','0401')  
	  WHEN p_user='ALLEBY'  THEN pr.codeLocalite IN ('0109','0110','0107','0604','0409')  
		  
		  
		  
SELECT * FROM  localite WHERE  libelle LIKE '%divo%' ;



/*IF p_user='BALLE' THEN
		SET p_codeLocalite='0105','0109','0110','0112'; 
	END IF;
	
	IF p_user='AMONDJI' THEN
		SET p_codeLocalite='0111','0305','0407';
	END IF;

	IF p_user='ADJOBIA' THEN
		SET p_codeLocalite='0106','0107','1105'; 
	END IF;*/

-- select DISTINCT codeTypePrestataire from prestataire;



/*SELECT
	DISTINCT
	CONCAT (fn_numordre(MONTH(LEFT(B.dateSysteme,10)),2) ,'/',YEAR(LEFT(B.dateSysteme,10))) AS DatePaiement,
	B.codePrestataire,
	fn_codeNormalise_prestataire_rp(B.codePrestataire) AS Compte,
	L.libelle AS localite,
	fn_libelle_prestataire(B.codePrestataire) AS prestataire,
	fn_codeNormalise_prestataire_rp(dm.codePrestatairePrescription) AS CompteC,
	fn_libelle_prestataire(dm.codePrestatairePrescription) AS CentrePrescripteur,
	dm.codeMedecin,
	fn_libelle_medecin_rp(dm.codeMedecin) AS medecinPrescipteur,	
	P.codeGcAssureur,fn_libelle_garant_rp(P.codeGcAssureur) Garant,
	P.idClient , fn_libelle_souscripteur(P.idClient) AS Souscripteur
	,P.id AS idPolice,P.numeroPolice,P.libellePolice,
	a.id AS idAdherent,
	a.numeroAdherent,
	CONCAT(TRIM(a.nom),'',TRIM(a.prenoms))  Adherent,
	B.idBeneficiaire AS idBeneficiaire,
	be.numeroBeneficiaire,
	CONCAT(TRIM(be.nomBeneficiaire),'',TRIM(be.prenomsBeneficiaire))  Beneficiaire,
	B.numeroFeuilleMaladie AS FeuilleMaladie, 
	d.numeroPrescription AS numeroPrescription,
	d.numeroBonOrdonnance AS numeroBonOrdonnance, 
	m.codeForme AS codeForme,
	dm.codeMedicament AS codeMedicament,
	m.libelle AS libelleMedicament,	
	dm.valeurActeManuel AS tarifMedicament,
	dm.quantite  AS quantite,
	dm.fraisReel AS fraisReel,
	dm.montantTm AS montantTm,
	dm.fraisExclu  AS fraisExclu,
	dm.montantArembourser  AS montantArembourser
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 gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
INNER JOIN prestataire pr ON (B.codePrestataire=pr.codePrestataire)
INNER JOIN pays AS pa ON (pa.codePays = pr.codePays)
INNER JOIN ville AS v ON (v.codeVille = pr.codeVille)
INNER JOIN localite AS L ON (L.codeLocalite = pr.codeLocalite)
INNER JOIN adherent a ON ((P.id=a.idPolice) AND (P.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
INNER JOIN beneficiaire be ON (B.idBeneficiaire=be.id) 
INNER JOIN p_detailprescription d ON (B.numeroFeuilleMaladie=d.numeroFeuilleMaladie)
INNER JOIN p_medicament m ON (m.codeMedicament=d.codeMedicament)
INNER JOIN  p_prestationmedicaments dm ON ((dm.idPrestationactes=prt.id) AND (B.numeroFeuilleMaladie=d.numeroFeuilleMaladie)AND (dm.codeMedicament=m.codeMedicament))

WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-01-31' )	
AND P.idClient!="49" AND G.id!='45'
-- AND be.numeroBeneficiaire="0082039102"
 -- AND L.codeLocalite  IN ('0105','0109','0110','0112') -- BALLE  '1105','0106','0112'
 -- AND L.codeLocalite  IN ('0111','0305','0407') -- AMONDJI  '0111','0305','0909','0409'
 AND L.codeLocalite  IN ('0106','0107','1105') -- ADJOBIA  '0105','0305','0107','0108'
AND (B.fraisRetenu!=0)  AND d.valeurActeManuel!=0 
-- AND B.numeroFeuilleMaladie=71251
-- AND B.codePrestataire='PH1424'
-- AND B.idPolice=229
-- AND B.tiersPayant ="1" 
-- AND B.codePrestataire IN ('PR1412')
AND pr.codeTypePrestataire='PHA' AND (B.codeSociete="vitalis")
ORDER BY DatePaiement ASC,Compte,a.id,idBeneficiaire,FeuilleMaladie  ASC,numeroBonOrdonnance*/