

-- Fiche Suivie Maladie
-- SELECT DATE_FORMAT(LAST_DAY('2022-12-13'), "%d/%m/%Y");

CALL `sp_evolution_depenseGARANT_rp` (NULL,NULL,NULL,NULL,"2022-01-01","2022-08-31")

CALL sp_evolution_depense_rp (NULL,NULL,NULL,NULL,"2022-01-01","2022-08-31")

SELECT * FROM police WHERE libellePolice LIKE '%COCO%'

SELECT 
SUM(IFNULL(prt.fraisReel,0)) AS reclamer,
SUM(IFNULL(prt.montantApayer,0)) AS Apayer
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)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-05-31' )
AND B.idPolice=192 
AND G.id!='45'  -- AND (B.codeSociete="vitalis")
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')  --  558 522 492


-- 1 --	PRNICIPAL HOSPITALISATION
SELECT 
a.id,a.numeroAdherent AS numero, CONCAT (a.nom ," ",a.prenoms) AS Adherent,
B.id,B.numeroBeneficiaire AS numero, CONCAT (B.nomBeneficiaire," ",B.prenomsBeneficiaire) AS beneficaire,
fn_libelle_prestataire(A.codePrestataire) CentreExecutant,	
SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
FROM p_prestationactes A
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 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-07-31')
AND p.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175')
 -- AND A.codeGarantieActe IN ('DEN')  
-- AND B.idPolice=192 
 AND B.numeroAdherent IN ("2290782","2290987","2291075","2291288","2291016","02291005","02291747",
"02291705","02291534","02292290","02291576","02291489","02291440","02291379",
"02291821","02291808","02291830","02291776","02291828","02291783","02291785",
"02291789","02291815","02291798","02291799","02291833","02291817","02291818",
"02292127","02292164","02291934","02292001")
AND A.montantApayer>0 
-- AND p.codePrestataire IN ('CE1376','CE0195','CE1257','CE1258','CE1382')
GROUP BY a.id,a.numeroAdherent ,a.nom,a.prenoms,
B.id,B.numeroBeneficiaire ,B.nomBeneficiaire,B.prenomsBeneficiaire,A.codePrestataire
,a.id,a.numeroAdherent,a.nom ,a.prenoms
-- HAVING SUM(IFNULL(A.montantApayer,0))>=100000
ORDER BY  a.id,A.codePrestataire,montantRembourse DESC;

-- 2 --	ETAT RECAPITUALTIF DES DEPENSES EN CONSULTATION PAR SPECIALITE
 SELECT 	CASE WHEN (F.codeMedecin='' OR F.codeMedecin IS NULL) THEN 'NON DEFINIE' ELSE fn_libelle_specialite(F.codeMedecin) END AS Specialite, 
COUNT(prt.idBeneficiaire) AS NombreActe,
SUM(IFNULL(prt.fraisReel,0)) AS montantFacture,
SUM(IFNULL(prt.montantApayer,0)) AS apresDecompte,
ROUND((SUM(IFNULL(prt.montantApayer,0))/COUNT(*)),0) AS Coutmoyen 
FROM p_prestationactes prt INNER JOIN  p_facture B ON (B.id=prt.idfacture)
INNER JOIN beneficiaire Be ON ( Be.id=prt.idBeneficiaire )
INNER JOIN  police P ON  (B.idPolice = P.id)
INNER JOIN  gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
INNER JOIN prestataire p  ON (prt.codePrestataire=p.codePrestataire)
INNER JOIN  p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-05-31')
AND prt.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175') 
AND G.id!='45' AND P.idClient!='49'    AND (B.codeSociete="vitalis") 
AND B.idPolice=192 AND prt.montantApayer>0 
GROUP BY Specialite
ORDER BY  apresDecompte DESC; 


SELECT 
-- CASE WHEN (F.codeMedecin='' OR F.codeMedecin IS NULL) THEN 'NON DEFINIE' ELSE fn_libelle_specialite(F.codeMedecin) END AS Specialite, 
SUM(IFNULL(prt.fraisReel,0)) AS montantReclame,SUM(IFNULL(prt.montantApayer,0)) AS montantRembourse
FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
INNER JOIN police C ON  (B.idPolice = C.id)
INNER JOIN gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)
INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
JOIN prestataire pr ON (B.codePrestataire=pr.codePrestataire)
JOIN typeprestataire t ON (t.codeTypePrestataire=pr.codeTypePrestataire)
 INNER JOIN  p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)	
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-05-31')
	AND B.idPolice IN (229)
AND G.id!='45' AND C.idClient!='49'    AND (B.codeSociete="vitalis")
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175','CE1276')
 

	





SELECT * FROM p_feuillemaladie

SELECT * FROM `p_medecinprestataire`
SELECT * FROM p_medecin WHERE specialite LIKE '%travail%'

-- 2 b -- DEPENSES PAR affection

SELECT
	af.codeAffection AS CodeAffection,af.libelle AS Affection,
	COUNT(af.codeAffection) AS Quantite,
	SUM(IFNULL(prt.fraisReel,0)) AS montantReclame,SUM(IFNULL(prt.montantApayer,0)) AS montantRembourse
	,ROUND((SUM(IFNULL(prt.montantApayer,0))/COUNT(af.codeAffection)),0) AS Coutmoyen 
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)
INNER JOIN  p_affection af ON (F.codeAffection=af.codeAffection)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-05-31')
AND pr.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175') 
AND G.id!='45' AND P.idClient!='49' AND (B.codeSociete="vitalis") AND prt.montantApayer!=0
AND B.idPolice=192
GROUP BY  af.codeAffection,af.libelle
HAVING SUM(IFNULL(B.montantApayer,0))!='0' 
ORDER BY montantRembourse DESC ;	


-- 3 -- etat des DEPENSES PAR FAMILLE D4aSSURES
SELECT 
B.codePrestataire Compte,fn_libelle_prestataire(B.codePrestataire) AS Prestataire,
CASE WHEN (prt.codeMedecin='' OR prt.codeMedecin IS NULL) THEN 'INCONNU' ELSE CONCAT (m.nom ," ",m.prenoms)END AS MedecinTraitant,
m.specialite, 	
prt.codeActe
, CASE WHEN prt.codeActe="CSM" THEN'CONSOMMABLES'ELSE fn_libelle_acte_rp(prt.codeActe) END AS Acte	
, prt.datePrestation,F.codeAffection,
a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
n.id AS idbenef,n.numeroBeneficiaire,CONCAT(n.nomBeneficiaire,' ',n.prenomsBeneficiaire)  Beneficiaire,
CASE WHEN (prt.tiersPayant="1")  THEN 'TP' ELSE 'RD' END AS typeP,
SUM(IFNULL(prt.fraisReel,0)) AS montantReclame,SUM(IFNULL(prt.montantApayer,0)) AS montantRembourse
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 adherent a ON ((P.id=a.idPolice) AND (P.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
INNER JOIN beneficiaire n ON ((prt.idBeneficiaire=n.id))  
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
INNER JOIN prestataire pr ON (B.codePrestataire=pr.codePrestataire)
LEFT JOIN p_medecin m ON (m.codeMedecin=prt.codeMedecin)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-05-31')
-- AND pr.codePrestataire IN ('CE1376','CE0195','CE1257','CE1258','CE1382') --bethani,sisangue,tongon,miennoh
-- AND pr.codePrestataire IN ('CE1258','CE0195')  -- bethani / sisangue
-- AND pr.codePrestataire IN ('CE1257')  -- bethani / sisangue	 
-- AND prt.codeActe='C'
-- AND prt.codeGarantieActe IN ('HOS')
AND B.idPolice=192 -- 
-- AND F.codeAffection=000 
-- and m.specialite like '%travail%'
AND pr.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175') AND G.id!='45'  
AND (B.codeSociete="vitalis") AND prt.montantApayer>0  -- AND B.idPolice=168
GROUP BY B.codePrestataire,prt.codeMedecin,prt.codeActe,prt.datePrestation,prt.tiersPayant, F.codeAffection,
a.id,a.numeroAdherent, a.nom, a.prenoms,n.id,n.numeroBeneficiaire,n.nomBeneficiaire,n.prenomsBeneficiaire ,	m.specialite
ORDER BY B.codePrestataire,idAdherent,idbenef,montantRembourse  ;  -- DESC LIMIT 100
-- ORDER BY B.codePrestataire,idAdherent,idbenef,montantRembourse  ;  -- DESC LIMIT 100

-- 4 --- BENEFICIAIRES DE PLUS DE 10 BONS EN PHARMACIE

SELECT  -- Gc.codeGcAssureur,B.idPolice,
Bp.codePrestataire Compte,fn_libelle_prestataire(Bp.codePrestataire) AS Prestataire,
A.codeActe,A.datePrestation,a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire,
fn_conso_QTE_acte_beneficiaire(B.numeroBeneficiaire,'PHA','2022-01-01','2022-05-31')  AS Quantite,
-- count(B.id) AS Quantite,
SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
FROM p_prestationactes A
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-05-31')
AND p.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175')
AND A.codeGarantieActe IN ('PHA')  -- AND B.idPolice=168
AND fn_id_entetecontrat_police(B.idPolice,CURDATE())=fn_id_entetecontrat_police_courant(B.idPolice)
-- AND p.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175')
-- AND p.codePrestataire IN ('PR0278')
AND B.idPolice=192 -- 
GROUP BY -- c.codeGcAssureur,B.idPolice,
Bp.codePrestataire,A.codeMedecin,A.codeActe,A.datePrestation,A.tiersPayant, F.codeAffection,
a.id,a.numeroAdherent, a.nom, a.prenoms,B.id,B.numeroBeneficiaire,B.nomBeneficiaire,B.prenomsBeneficiaire 
 HAVING fn_conso_QTE_acte_beneficiaire(B.numeroBeneficiaire,'PHA','2022-01-01','2022-05-31')>=3
ORDER BY idAdherent,idbenef,montantRembourse DESC  ;

SELECT * FROM prestataire WHERE libelle LIKE "%tong%"
-- 5 -- DEMANDE DOC BALLEY
-- select * from p_acte where libelle like "%grat%"
SELECT 
 A.codePrestataire,fn_libelle_prestataire(A.codePrestataire) Prestataire, COUNT(B.numeroBeneficiaire) AS Quantite,
-- fn_conso_QTE_acte_beneficiaire(B.numeroBeneficiaire,'CON','2022-01-01','2022-05-31')  AS Quantite,
SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
,ROUND((SUM(IFNULL(A.montantApayer,0))/COUNT(B.numeroBeneficiaire)),0) AS Coutmoyen 
FROM p_prestationactes A
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 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-05-31')
-- AND p.codePrestataire IN ('CE0195','CE1258','CE1257',"CE1376") -- sisangue(CE0195),bethani(CE1258),miennoh(CE1257),TSP TONGON(CE1376),MBENGUE(CE1382)   --- ,'CE1382'
-- AND A.codeGarantieActe IN ('CON')  -- AND A.codeActe IN ('C',"A00000003")  -- 
AND fn_id_entetecontrat_police(B.idPolice,CURDATE())=fn_id_entetecontrat_police_courant(B.idPolice)
AND B.idPolice=229  AND A.montantApayer>0
-- AND p.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175')
GROUP BY A.codePrestataire
ORDER BY  montantRembourse DESC;  -- CE1377


SELECT * FROM police WHERE libellePolice LIKE "%WOLCIM%"

-- 6 -- DEMANDE CENTRE PRESCRIPTEURS CME TONGON 

SELECT * FROM prestataire WHERE libelle LIKE '%TSP%'

SELECT 
Bp.codePrestataire Compte,fn_libelle_prestataire(Bp.codePrestataire) AS Prestataire,
A.codeActe,A.datePrestation,B.idPolice,fn_libelle_policeID(B.idPolice) AS Police,
B.idcollege,
a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire,
-- fn_conso_QTE_acte_beneficiaire(B.numeroBeneficiaire,'PHA','2022-01-01','2022-12-31')  AS Quantite,
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_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-12-31')
-- AND p.codePrestataire IN ('PR0278')
 AND B.numeroAdherent IN ("2290782","2290987","2291075","2291288","2291016","02291005","02291747",
"02291705","02291534","02292290","02291576","02291489","02291440","02291379",
"02291821","02291808","02291830","02291776","02291828","02291783","02291785",
"02291789","02291815","02291798","02291799","02291833","02291817","02291818",
"02292127","02292164","02291934","02292001")
-- AND A.codeGarantieActe IN ('PHA') 
AND A.montantApayer>0  -- 
-- AND B.idPolice=192
GROUP BY Bp.codePrestataire,A.codeMedecin,A.codeActe,A.datePrestation,A.tiersPayant, F.codeAffection,
a.id,a.numeroAdherent, a.nom, a.prenoms,B.id,B.numeroBeneficiaire,B.nomBeneficiaire,B.prenomsBeneficiaire 
-- HAVING fn_conso_QTE_acte_beneficiaire(B.numeroBeneficiaire,'PHA','2022-01-01','2022-05-31')>=5
ORDER BY idAdherent,idbenef,datePrestation,montantRembourse DESC  ;


SELECT 
 Bp.codePrestataire Compte,fn_libelle_prestataire(Bp.codePrestataire) AS Prestataire,
-- fn_conso_QTE_acte_beneficiaire(B.numeroBeneficiaire,'PHA','2022-01-01','2022-05-31')  AS Quantite,
COUNT(DISTINCT pm.idPrestationactes)  AS Quantite,
SUM(IFNULL(pm.fraisReel,0)) AS montantReclame,SUM(IFNULL(pm.montantApayer,0)) AS montantRembourse
-- 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_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-12-31')
AND fn_id_entetecontrat_police(B.idPolice,CURDATE())=fn_id_entetecontrat_police_courant(B.idPolice)
-- AND pm.codePrestatairePrescription IN ('CE1377') 
AND B.numeroBeneficiaire IN ( "0229084002","0229083502","0229069001","0229068801","0229078801","0229077702","0229077703","0229077701","0229081701",
"0229075401","0229075501","0229071301","0229071901","0229071102","0229085903","0229087301","0229090001","0229093901",
"0229093902","0229094901","0229174501","0229171701","0229171601","0229124901","0229135402","0229137401","0229139001",
"0229143501","0229143001","0229161102","0229148603","0229149101","0229149301","0229149501","0229150404","0229151102",
"0229168701","0229151401","0229152102","0229152104","0229165201","0229161201","0229160501","0229160201","0229159903",
"0229159702","0229155701","0229154701","0229073901","0229161401	")
-- AND A.codeGarantieActe IN ('PHA') AND A.montantApayer>0  
-- AND A.codeTypePrestation IN ('PHAR') 
GROUP BY Bp.codePrestataire
ORDER BY montantRembourse DESC  ;



	SELECT
		B.codePrestataire AS Compte,
		fn_libelle_prestataire(B.codePrestataire) AS Beneficiaire,
		SUM(IFNULL(prt.fraisRetenu,0)) AS montantFacture,
		SUM(IFNULL(prt.montantApayer,0)) AS montantRembourse
		-- ,ROUND((SUM(IFNULL(prt.montantApayer,0))/COUNT(*)),0) AS Coutmoyen 
	FROM p_prestationactes prt 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 adherent a ON ((P.id=a.idPolice) AND (P.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
	INNER JOIN prestataire pr ON (B.codePrestataire=pr.codePrestataire)
	INNER JOIN typeprestataire t ON (t.codeTypePrestataire=pr.codeTypePrestataire)	
	WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-05-31')
	AND G.id!='45' AND P.idClient!='49' AND (B.codeSociete="vitalis") AND prt.montantApayer!=0
	AND B.idPolice=229 
	AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175','CE1276')
	GROUP BY  B.codePrestataire ,P.id,P.numeroPolice,P.libellePolice
	ORDER BY montantRembourse DESC ;


CALL sp_ft8_depense_FamActe_rp ('GNA',NULL,'168',NULL,'2022-01-01','2022-05-31');  -- FT8 :DEPENSE FAMILLE D'ACTES MEDICAUX


SELECT 
* -- SUM(IFNULL(fraisReel,0)) AS montantReclame,SUM(IFNULL(montantApayer,0)) AS montantRembourse
FROM p_prestationmedicaments WHERE codePrestatairePrescription IN ('CE1377') 
AND (LEFT(dateSysteme,10) BETWEEN '2022-01-01' AND '2022-05-31')

-- 7 --BENEFICIAIRE AYANT PRIS DES LUNETTES
SELECT 
Bp.codePrestataire Compte,fn_libelle_prestataire(Bp.codePrestataire) AS Prestataire,
A.codeActe,fn_libelle_acte_rp(	A.codeActe) acte,A.datePrestation,a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire
,SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
FROM p_prestationactes A
INNER JOIN `p_prestationoptiques` pm ON (pm.idPrestationactes = A.id)
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-12-31')
-- AND B.numeroBeneficiaire IN ( "0229084002","0229083502")
 AND a.numeroAdherent="0185012001"
AND fn_id_entetecontrat_police(B.idPolice,CURDATE())=fn_id_entetecontrat_police_courant(B.idPolice)
GROUP BY Bp.codePrestataire,A.codeMedecin,A.codeActe,A.datePrestation,A.tiersPayant, F.codeAffection,
a.id,a.numeroAdherent, a.nom, a.prenoms,B.id,B.numeroBeneficiaire,B.nomBeneficiaire,B.prenomsBeneficiaire 
ORDER BY idAdherent,idbenef,montantRembourse DESC  ;

	
SELECT * FROM police WHERE libellePolice LIKE '%TSP%'

-- 8 --NOUVELLE HOSPITALISATION

SELECT
	-- P.id,P.numeroPolice,P.libellePolice,
	fn_libelle_prestataire(p.codePrestataire) CentreExecutant,
	a.id,a.numeroAdherent AS numero, CONCAT (a.nom ," ",a.prenoms) AS Adherent,
	Be.id,Be.numeroBeneficiaire AS numero, CONCAT (Be.nomBeneficiaire," ",Be.prenomsBeneficiaire) AS beneficaire,
	SUM(IFNULL(prt.fraisReel,0)) AS montantReclame,
	SUM(IFNULL(prt.montantApayer,0)) AS MontantRembourse
FROM p_prestationactes prt 
INNER JOIN  p_facture B ON (B.id=prt.idfacture)
INNER JOIN beneficiaire Be ON ( Be.id=prt.idBeneficiaire )
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)
INNER JOIN  p_affection af ON (F.codeAffection=af.codeAffection)
INNER JOIN prestataire p  ON (prt.codePrestataire=p.codePrestataire)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-05-31')
-- AND prt.codeGarantieActe='HOS'
-- AND F.codeAffection=000
AND B.idPolice=192  AND prt.montantApayer>0 
AND G.id!='45' AND P.idClient!='49' AND (B.codeSociete="vitalis") AND prt.montantApayer!=0
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
GROUP BY -- P.id,P.numeroPolice,P.libellePolice,
p.codePrestataire,a.id,a.numeroAdherent,a.nom,a.prenoms
,Be.id,Be.numeroBeneficiaire,Be.nomBeneficiaire," ",Be.prenomsBeneficiaire 
HAVING SUM(IFNULL(B.montantApayer,0))!='0' 
ORDER BY CentreExecutant, MontantRembourse DESC ;



-- 10 -- depense affection eclatee

SELECT
	af.codeAffection AS CodeAffection,af.libelle AS Affection,
	COUNT(af.codeAffection) AS Quantite,
	SUM(IFNULL(prt.fraisReel,0)) AS montantReclame,SUM(IFNULL(prt.montantApayer,0)) AS montantRembourse
	,ROUND((SUM(IFNULL(prt.montantApayer,0))/COUNT(af.codeAffection)),0) AS Coutmoyen 
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)
INNER JOIN  p_affection af ON (F.codeAffection=af.codeAffection)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-06-01' AND '2022-07-31')
AND pr.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175') 
AND G.id!='45' AND P.idClient!='49' AND (B.codeSociete="vitalis") AND prt.montantApayer!=0
AND B.idPolice=168
GROUP BY  af.codeAffection,af.libelle
HAVING SUM(IFNULL(B.montantApayer,0))!='0' 
ORDER BY montantRembourse DESC ;	

SELECT 
B.codePrestataire Compte,fn_libelle_prestataire(B.codePrestataire) AS Prestataire,
CASE WHEN (prt.codeMedecin='' OR prt.codeMedecin IS NULL) THEN 'INCONNU' ELSE CONCAT (m.nom ," ",m.prenoms)END AS MedecinTraitant, 	
prt.codeActe
, CASE WHEN prt.codeActe="CSM" THEN'CONSOMMABLES'ELSE fn_libelle_acte_rp(prt.codeActe) END AS Acte	
, prt.datePrestation,F.codeAffection,
a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
n.id AS idbenef,n.numeroBeneficiaire,CONCAT(n.nomBeneficiaire,' ',n.prenomsBeneficiaire)  Beneficiaire,
CASE WHEN (prt.tiersPayant="1")  THEN 'TP' ELSE 'RD' END AS typeP,
SUM(IFNULL(prt.fraisReel,0)) AS montantReclame,SUM(IFNULL(prt.montantApayer,0)) AS montantRembourse
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 adherent a ON ((P.id=a.idPolice) AND (P.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
INNER JOIN beneficiaire n ON ((prt.idBeneficiaire=n.id))  
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
INNER JOIN prestataire pr ON (B.codePrestataire=pr.codePrestataire)
INNER JOIN  p_affection af ON (F.codeAffection=af.codeAffection)
LEFT JOIN p_medecin m ON (m.codeMedecin=prt.codeMedecin)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-12-31')
AND B.numeroBeneficiaire IN ( "0229084002","0229083502","0229069001","0229068801","0229078801","0229077702","0229077703","0229077701","0229081701",
"0229075401","0229075501","0229071301","0229071901","0229071102","0229085903","0229087301","0229090001","0229093901",
"0229093902","0229094901","0229174501","0229171701","0229171601","0229124901","0229135402","0229137401","0229139001",
"0229143501","0229143001","0229161102","0229148603","0229149101","0229149301","0229149501","0229150404","0229151102",
"0229168701","0229151401","0229152102","0229152104","0229165201","0229161201","0229160501","0229160201","0229159903",
"0229159702","0229155701","0229154701","0229073901","0229161401	")
AND pr.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175') AND G.id!='45'  
AND (B.codeSociete="vitalis") -- AND prt.montantApayer>0  AND B.idPolice=168
GROUP BY B.codePrestataire,prt.codeMedecin,prt.codeActe,prt.datePrestation,prt.tiersPayant, F.codeAffection,
a.id,a.numeroAdherent, a.nom, a.prenoms,n.id,n.numeroBeneficiaire,n.nomBeneficiaire,n.prenomsBeneficiaire 
ORDER BY B.codePrestataire,idAdherent,idbenef,montantRembourse  ;   


-- 11 DETAIL AFFECTION NON DEFINIE

SELECT 	
B.codePrestataire Compte,fn_libelle_prestataire(B.codePrestataire) AS Prestataire,
CASE WHEN (prt.codeMedecin='' OR prt.codeMedecin IS NULL) THEN 'INCONNU' ELSE CONCAT (m.nom ," ",m.prenoms)END AS MedecinTraitant,
prt.codeActe
, CASE WHEN prt.codeActe="CSM" THEN'CONSOMMABLES'ELSE fn_libelle_acte_rp(prt.codeActe) END AS Acte	
, prt.datePrestation,F.codeAffection,
a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
n.id AS idbenef,n.numeroBeneficiaire,CONCAT(n.nomBeneficiaire,' ',n.prenomsBeneficiaire)  Beneficiaire,
CASE WHEN (prt.tiersPayant="1")  THEN 'TP' ELSE 'RD' END AS typeP,
SUM(IFNULL(prt.fraisReel,0)) AS montantReclame,SUM(IFNULL(prt.montantApayer,0)) AS montantRembourse
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 beneficiaire n ON ((prt.idBeneficiaire=n.id))  
INNER JOIN  p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
INNER JOIN  p_affection af ON (F.codeAffection=af.codeAffection)
LEFT JOIN p_medecin m ON (m.codeMedecin=prt.codeMedecin)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-06-01' AND '2022-07-31')
-- AND pr.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175') 
AND G.id!='45' AND P.idClient!='49' AND (B.codeSociete="vitalis") -- AND prt.montantApayer!=0
AND pr.codePrestataire  IN ('CE0195','CE1258','CE1257',"CE1376") -- SISANGUE, BETHANIE, MIENMOH, CME TONGON
--  AND af.codeAffection='000'
AND B.idPolice=192
GROUP BY  B.codePrestataire,prt.codeMedecin,m.nom,m.prenoms,prt.codeActe,prt.datePrestation,F.codeAffection,
a.id,a.numeroAdherent,a.nom,a.prenoms,n.id,n.numeroBeneficiaire,n.nomBeneficiaire,n.prenomsBeneficiaire,
prt.tiersPayant
-- HAVING SUM(IFNULL(B.montantApayer,0))!='0' 
ORDER BY montantRembourse DESC ;


-- 12 DETAIL PHARMACIE COCODY

SELECT 
Bp.codePrestataire Compte,fn_libelle_prestataire(Bp.codePrestataire) AS Prestataire,
A.codeActe,A.datePrestation,
a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire,	
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_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-05-01' AND '2022-07-31')
-- AND pm.codePrestatairePrescription IN ('CE1377')
AND A.codeGarantieActe IN ('PHA') AND A.montantApayer>0  -- 
AND B.idPolice=192
GROUP BY Bp.codePrestataire,A.codeMedecin,A.codeActe,A.datePrestation,A.tiersPayant, F.codeAffection,
a.id,a.numeroAdherent, a.nom, a.prenoms,B.id,B.numeroBeneficiaire,B.nomBeneficiaire,B.prenomsBeneficiaire 
-- HAVING fn_conso_QTE_acte_beneficiaire(B.numeroBeneficiaire,'PHA','2022-01-01','2022-05-31')>=5
-ORDER BY idAdherent,idbenef,montantRembourse DESC  ;


-- 13 CONSOMMATION PROVISOIRE

SELECT * FROM p_acte WHERE codeActe="RBCLIMA"

SELECT 
A.codePrestataire Compte,fn_libelle_prestataire(A.codePrestataire) AS Prestataire,
A.codeActe,A.datePrestation,
a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire,	
SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
FROM p_prestationactes A
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=A.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(A.dateSysteme,10)>='2022-05-30')
WHERE (LEFT(A.dateSysteme,10) BETWEEN '2022-04-01' AND '2022-05-31')
AND A.montantApayer>0  
AND B.idPolice=229 
-- AND B.numeroBeneficiaire='0205031401'
GROUP BY A.codePrestataire,A.codeMedecin,A.codeActe,A.datePrestation,A.tiersPayant, F.codeAffection,
a.id,a.numeroAdherent, a.nom, a.prenoms,B.id,B.numeroBeneficiaire,B.nomBeneficiaire,B.prenomsBeneficiaire 
ORDER BY datePrestation,Compte,idAdherent,idbenef,montantRembourse DESC  ;


	

-- 14 MONTANT CONSULATION GENERALISTE VALIDE

SELECT   
A.codePrestataire Compte,fn_libelle_prestataire(A.codePrestataire) AS Prestataire,
COUNT(A.id)  AS Quantite ,SUM(IFNULL(A.fraisReel,0)) AS montantReclame,
SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
,ROUND((SUM(IFNULL(A.montantApayer,0))/COUNT(A.id) ),0) AS Coutmoyen 
FROM p_prestationactes A
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 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(B.dateSysteme,10) BETWEEN '2022-04-01' AND '2022-05-31' )
AND Gc.id!='45'  AND (B.codeSociete="vitalis")    -- AND A.codeActe IN ('C')
AND A.montantApayer>0 -- AND A.facture="1"
AND B.idPolice=229 
-- AND A.codePrestataire IN ('CE0195','CE1258','CE1257',"CE1376") -- SISANGUE, BETHANIE, MIENMOH, CME TONGON
GROUP BY A.codePrestataire
ORDER BY montantRembourse DESC;




SELECT   
-- CONCAT ('0',MONTH(LEFT(B.dateSysteme,10)),'/',YEAR(LEFT(B.dateSysteme,10))) AS DatePaiement, 
Po.id,Po.numeroPolice,Po.libellePolice,
a.id,a.numeroAdherent AS numero, CONCAT (a.nom ," ",a.prenoms) AS Adherent,
B.id,B.numeroBeneficiaire AS numero, CONCAT (B.nomBeneficiaire," ",B.prenomsBeneficiaire) AS beneficaire,
fn_libelle_prestataire(A.codePrestataire) CentreExecutant,	
 IFNULL(A.fraisReel,0) AS montantReclame,IFNULL(A.montantTm,0) AS montantTm,IFNULL(A.montantRedresse,0) AS montantRedresse,IFNULL(A.montantApayer,0) AS montantRembourse
FROM p_prestationactes A
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 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(B.dateSysteme,10) BETWEEN '2022-06-01' AND '2022-07-31' )
AND Gc.id!='45'  AND (B.codeSociete="vitalis")    AND A.codeActe IN ('C')
AND A.montantApayer>0 AND A.facture="1"
AND A.codePrestataire IN ('CE0195','CE1258','CE1257',"CE1376") -- SISANGUE, BETHANIE, MIENMOH, CME TONGON
ORDER BY  DatePaiement,A.codePrestataire,  Po.id,montantRembourse DESC;

SELECT * FROM prestataire WHERE libelle LIKE "%TONGON%"

-- 15 MONTANT CONSULATION GENERALISTE EN PRVISOIRE
SELECT   
CONCAT ('0',MONTH(LEFT(B.dateSysteme,10)),'/',YEAR(LEFT(B.dateSysteme,10))) AS DatePaiement,    
Po.id,Po.numeroPolice,Po.libellePolice,
a.id,a.numeroAdherent AS numero, CONCAT (a.nom ," ",a.prenoms) AS Adherent,
B.id,B.numeroBeneficiaire AS numero, CONCAT (B.nomBeneficiaire," ",B.prenomsBeneficiaire) AS beneficaire,
fn_libelle_prestataire(A.codePrestataire) CentreExecutant,	
 IFNULL(A.fraisReel,0) AS montantReclame,IFNULL(A.montantTm,0) AS montantTm,IFNULL(A.montantRedresse,0) AS montantRedresse,IFNULL(A.montantApayer,0) AS montantRembourse
FROM p_prestationactes A
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 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)
INNER JOIN entetecontrat C ON (C.idPolice=B.idPolice)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-05-31' )
AND fn_id_entetecontrat_police(B.idPolice,CURDATE())=fn_id_entetecontrat_police_courant(B.idPolice)
AND Gc.id!='45'  AND (B.codeSociete="vitalis")  --  AND A.codeActe IN ('C')
AND A.montantApayer>0 -- AND A.facture="0"
AND C.id = fn_id_entetecontrat_police_courant(B.idPolice)
-- AND p.codePrestataire NOT IN ('CE0416','PR1176','CE1685','CE1232','PR1175')
AND p.codePrestataire  IN ('CE0195','CE1258','CE1257',"CE1376") -- SISANGUE, BETHANIE, MIENMOH, CME TONGON
-- AND A.codePrestataire IN ('CE0195','CE1258','CE1257') -- SISANGUE, BETHANIE, MIENMOH.
ORDER BY  DatePaiement,A.codePrestataire,  Po.id,montantRembourse DESC;


-- 16 RATIO SUR L'ENSEMBLE DU RESEAU VALIDE
SELECT -- DatePaiement,
Compte,Prestataire,SUM(IFNULL(Quantite,0)) AS Quantite,SUM(IFNULL(montantReclame,0))	AS montantReclame,SUM(IFNULL(montantRembourse,0))	AS montantRembourse
,ROUND((SUM(IFNULL(montantRembourse,0))/SUM(IFNULL(Quantite,0))),0) AS Coutmoyen FROM(

SELECT  CONCAT ('0',MONTH(LEFT(B.dateSysteme,10)),'/',YEAR(LEFT(B.dateSysteme,10))) AS DatePaiement,    
A.codePrestataire Compte,fn_libelle_prestataire(A.codePrestataire) AS Prestataire,
COUNT(A.idBeneficiaire) AS Quantite,SUM(IFNULL(A.fraisReel,0)) AS montantReclame
,SUM(IFNULL(A.montantApayer,0))	AS montantRembourse
 ,ROUND((SUM(IFNULL(A.montantApayer,0))/COUNT(A.idBeneficiaire)),0) AS Coutmoyen 
FROM p_prestationactes A
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 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(B.dateSysteme,10) BETWEEN '2022-04-01' AND '2022-05-31' )
AND Gc.id!='45'  AND (B.codeSociete="vitalis")  --   AND A.codeActe IN ('C')
AND B.idPolice='229'
AND A.montantApayer>0 AND A.facture="1" -- AND A.codePrestataire IN ('CE0195','CE1258','CE1257') -- SISANGUE, BETHANIE, MIENMOH.
GROUP BY    A.codePrestataire, B.dateSysteme 
) RATIO
GROUP BY -- DatePaiement,
Compte,Prestataire
ORDER BY -- DatePaiement,
montantRembourse DESC;


-- 17 RATIO SUR L'ENSEMBLE DU RESEAU VALIDE
SELECT DatePaiement,Compte,Prestataire,SUM(IFNULL(Quantite,0)) AS Quantite,SUM(IFNULL(montantReclame,0))AS montantReclame,SUM(IFNULL(montantRembourse,0)) AS montantRembourse
,ROUND((SUM(IFNULL(montantRembourse,0))/SUM(IFNULL(Quantite,0))),0) AS Coutmoyen FROM(

SELECT   CONCAT ('0',MONTH(LEFT(B.dateSysteme,10)),'/',YEAR(LEFT(B.dateSysteme,10))) AS DatePaiement, 
	A.codePrestataire Compte,fn_libelle_prestataire(A.codePrestataire) AS Prestataire,	 
COUNT(A.idBeneficiaire) AS Quantite,SUM(IFNULL(A.fraisReel,0)) AS montantReclame
,SUM(IFNULL(A.montantApayer,0))	AS montantRembourse
 ,ROUND((SUM(IFNULL(A.montantApayer,0))/COUNT(A.idBeneficiaire)),0) AS Coutmoyen 
FROM p_prestationactes A
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 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(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-05-31' )
AND Gc.id!='45'  AND (B.codeSociete="vitalis")    AND A.codeActe IN ('C')
AND B.numeroBeneficiaire IN ( "0229084002","0229083502","0229069001","0229068801","0229078801","0229077702","0229077703","0229077701","0229081701",
"0229075401","0229075501","0229071301","0229071901","0229071102","0229085903","0229087301","0229090001","0229093901",
"0229093902","0229094901","0229174501","0229171701","0229171601","0229124901","0229135402","0229137401","0229139001",
"0229143501","0229143001","0229161102","0229148603","0229149101","0229149301","0229149501","0229150404","0229151102",
"0229168701","0229151401","0229152102","0229152104","0229165201","0229161201","0229160501","0229160201","0229159903",
"0229159702","0229155701","0229154701","0229073901","0229161401	")
AND A.montantApayer>0 -- AND A.facture="1" -- AND A.codePrestataire IN ('CE0195','CE1258','CE1257') -- SISANGUE, BETHANIE, MIENMOH.
GROUP BY    A.codePrestataire, B.dateSysteme 
) RATIO
GROUP BY DatePaiement,Compte,Prestataire
ORDER BY  DatePaiement,Compte;

SELECT * FROM p_acte WHERE libelle LIKE "%grat%"


-- 18 RATIO SUR L'ENSEMBLE DU RESEAU PROVISOIRE
SELECT DatePaiement,SUM(IFNULL(Quantite,0)) AS Quantite,SUM(IFNULL(montantReclame,0))	AS montantReclame,SUM(IFNULL(montantRembourse,0))	AS montantRembourse
,ROUND((SUM(IFNULL(montantRembourse,0))/SUM(IFNULL(Quantite,0))),0) AS Coutmoyen FROM(
SELECT   
 CONCAT ('0',MONTH(LEFT(B.dateSysteme,10)),'/',YEAR(LEFT(B.dateSysteme,10))) AS DatePaiement, 
COUNT(A.idBeneficiaire) AS Quantite,SUM(IFNULL(A.fraisReel,0)) AS montantReclame
,SUM(IFNULL(A.montantApayer,0))	AS montantRembourse
 ,ROUND((SUM(IFNULL(A.montantApayer,0))/COUNT(A.idBeneficiaire)),0) AS Coutmoyen 
FROM p_prestationactes A
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 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(B.dateSysteme,10) BETWEEN '2022-05-01' AND '2022-07-31' )
-- AND  B.numeroBeneficiaire="0229122301" AND A.codegarantie="DEN"
AND B.idPolice="229"
AND Gc.id!='45'  AND (B.codeSociete="vitalis")   --  AND A.codeActe IN ('C')
AND A.montantApayer>0 -- AND A.facture="0"
GROUP BY B.dateSysteme ) RATIO
GROUP BY DatePaiement
ORDER BY  DatePaiement;

-- 19 ALERTE 

SELECT 
	CodeG,garant,idPolice,police,bareme,
	idAdherent,numeroAdherent,adherent,
	idbenef,numeroBeneficiaire,Beneficiaire,
	plafondIndividuel,consoIndividuel,
	ABS(plafondIndividuel - consoIndividuel) AS depassement,
	ROUND(consoIndividuel/plafondIndividuel*100,0) AS pourcentIndividuel
FROM vw_alerte_depense_plfInd_rp  
WHERE ROUND(consoIndividuel/plafondIndividuel*100,0)  >= 0
AND numeroBeneficiaire IN ( "0229084002","0229083502","0229069001","0229068801","0229078801","0229077702","0229077703","0229077701","0229081701",
"0229075401","0229075501","0229071301","0229071901","0229071102","0229085903","0229087301","0229090001","0229093901",
"0229093902","0229094901","0229174501","0229171701","0229171601","0229124901","0229135402","0229137401","0229139001",
"0229143501","0229143001","0229161102","0229148603","0229149101","0229149301","0229149501","0229150404","0229151102",
"0229168701","0229151401","0229152102","0229152104","0229165201","0229161201","0229160501","0229160201","0229159903",
"0229159702","0229155701","0229154701","0229073901","0229161401	")		 
ORDER BY CodeG,idPolice,idAdherent,idbenef,pourcentIndividuel DESC ;

SELECT * FROM prestataire WHERE libelle LIKE '%BIABOU%'

-- 20 detail medicaments
SELECT  f.dateSysteme,pm.codePrestatairePrescription ComptePrescripteur,fn_libelle_prestataire(pm.codePrestatairePrescription) AS Prescripteur,
A.codePrestataire CompteExecutant,fn_libelle_prestataire(A.codePrestataire) AS Executant,
a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire,
A.codeActe,A.datePrestation,pm.numeroFeuilleMaladie,pm.numeroBonOrdonnance,
pm.numeroFeuilleMaladie,pm.numeroBonOrdonnance,
fn_libelle_prestataire(pm.codePrestatairePrescription) AS Prescripteur,m.codeForme,
m.codeMedicament,m.CIP1,m.codeForme,m.libelle,
SUM(IFNULL(pm.fraisReel,0)) AS montantReclame,SUM(IFNULL(pm.montantArembourser,0)) AS montantRembourse 
FROM p_prestationactes A
INNER JOIN p_facture f ON (f.id = A.idFacture)
INNER JOIN p_prestationmedicaments pm ON (pm.idPrestationactes = A.id)
INNER JOIN p_medicament m ON (pm.codeMedicament= m.codeMedicament)
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=A.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)
INNER JOIN entetecontrat C ON (C.idPolice=B.idPolice)
WHERE C.id = fn_id_entetecontrat_police_courant(B.idPolice)
AND (LEFT(f.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-07-31' )
 AND B.idPolice=229
-- and F.numeroFeuilleMaladie=9050 
-- AND pm.codePrestataire IN ('PR0278')  -- 
 -- AND pm.numeroBonOrdonnance IN (0110000101)
GROUP BY f.dateSysteme, A.codePrestataire,pm.numeroFeuilleMaladie,pm.numeroBonOrdonnance,m.codeForme,pm.codePrestatairePrescription
,A.codeActe,A.datePrestation,pm.numeroFeuilleMaladie,pm.numeroBonOrdonnance
,a.id,a.numeroAdherent,a.nom,a.prenoms,B.id,B.numeroBeneficiaire,B.nomBeneficiaire,B.prenomsBeneficiaire
,m.codeMedicament,m.CIP1,m.codeForme,m.libelle
ORDER BY datePrestation,idAdherent,idbenef,montantRembourse DESC  ;

SELECT * FROM p_prestationmedicaments




SELECT  SUM(IFNULL(montantArembourser,0)) AS montantRembourse FROM p_prestationmedicaments WHERE codePrestataire IN ('PR0278')   AND numeroBonOrdonnance IN (25452,25598)



SELECT SUM(IFNULL(montantArembourser,0)) AS montantRembourse FROM p_detailprescription WHERE  numeroBonOrdonnance IN (25452,25598)





