-- 1 --BENEFICIAIRE MARIRIE DE COCCODY AYANT PRIS PLUS DE 2 PAIRES LUNETTES

DROP TEMPORARY TABLE IF EXISTS temp_facture_leger;
 
CREATE TEMPORARY TABLE temp_facture_leger
( INDEX(idFacture) , INDEX(idBeneficiaire) , INDEX(codePrestataire))
SELECT
	DISTINCT prt.id AS idFacture,
	prt.dateSysteme AS dateFacture,
	prt.codePrestataire,
	fn_libelle_prestataire(pm.codePrestatairePrescription) AS Prescripteur,
	fn_libelle_prestataire(prt.codePrestataire) AS Exécutant,
	prt.idBeneficiaire,e.idAdherent,
	pm.numeroFeuilleMaladie,
	pm.numeroBonOptique,
	f.codeAffection,af.libelle AS affection, 		
	pm.tm AS tmDOC,
	IFNULL(pm.fraisReel,0)AS montantReclame,IFNULL(pm.montantTm,0) AS TM,IFNULL(pm.montantApayer,0) AS Arembourser 
FROM p_prestationactes prt 
INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
INNER JOIN adherent a ON ((e.idPolice=a.idPolice) AND ( a.id=e.idAdherent))
INNER JOIN  police C ON  (e.idPolice = C.id)
INNER JOIN  college o ON  (o.idPolice = C.id)
INNER JOIN  gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)	
INNER JOIN p_prestationoptiques pm ON (pm.idPrestationactes = prt.id)
-- INNER JOIN p_medicament m ON (pm.codeMedicament= m.codeMedicament)
LEFT JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie =prt.numeroFeuilleMaladie )
LEFT JOIN `p_affection` af ON (f.codeAffection =af.codeAffection )
WHERE C.id=229
-- AND a.id=7994
AND prt.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276') 
ORDER BY prt.id;

DROP TEMPORARY TABLE IF EXISTS temp_facture_medu;	 
CREATE TEMPORARY TABLE temp_facture_medu
SELECT  
	A.idBeneficiaire,COUNT(A.idFacture) Quantite,SUM(IFNULL(A.Arembourser,0)) AS Total 	
FROM temp_facture_leger A
GROUP BY A.idBeneficiaire
HAVING (CASE WHEN 1>0 THEN COUNT(A.idFacture)>1 ELSE COUNT(A.idFacture)>1 END )	;	


SELECT  B.garant,B.idPolice,B.numeroPolice,B.libellePolice,B.idCollege,B.codeProduit,
	-- A.idFacture,
	A.dateFacture,
	B.idAdherent,B.numeroAdherent AS NoAdherent,B.adherent,		
	B.idBeneficiaire,B.numeroBeneficiaire AS NoBeneficiaire,B.beneficiaire,			
	A.Prescripteur,
	A.Exécutant,
	A.numeroFeuilleMaladie,A.numeroBonOptique,A.codeAffection, -- A.affection, 
	A.montantReclame, tmDOC,
	A.TM,
	A.Arembourser
	,IFNULL(M.Total,0) AS Total 
FROM temp_facture_leger A
LEFT JOIN prestataire PR ON (PR.codePrestataire = A.codePrestataire)
LEFT JOIN vw_infos_contrat_assures B ON (B.idBeneficiaire = A.idBeneficiaire) 
INNER JOIN temp_facture_medu M ON (M.idBeneficiaire = A.idBeneficiaire)
WHERE A.Arembourser>0
ORDER BY B.garant,B.idPolice,B.idCollege,B.idAdherent,B.idBeneficiaire,A.Arembourser;

DROP TEMPORARY TABLE  temp_facture_leger;
DROP TEMPORARY TABLE  temp_facture_medu;


-- 2 --FAMILLE MARIRIE DE COCCODY AYANT PRIS PLUS DE 200.000 FCFA EN LUNETTE

DROP TEMPORARY TABLE IF EXISTS temp_facture_leger;
 
CREATE TEMPORARY TABLE temp_facture_leger
( INDEX(idFacture) , INDEX(idBeneficiaire) , INDEX(codePrestataire))
SELECT
	DISTINCT prt.id AS idFacture,
	prt.dateSysteme AS dateFacture,
	prt.codePrestataire,
	fn_libelle_prestataire(pm.codePrestatairePrescription) AS Prescripteur,
	fn_libelle_prestataire(prt.codePrestataire) AS Exécutant,
	prt.idBeneficiaire,e.idAdherent,
	pm.numeroFeuilleMaladie,
	pm.numeroBonOrdonnance,
	f.codeAffection,af.libelle AS affection, 		
	pm.tm AS tmDOC,
	IFNULL(pm.fraisReel,0)AS montantReclame,IFNULL(pm.montantTm,0) AS TM,IFNULL(pm.montantApayer,0) AS Arembourser 
FROM p_prestationactes prt 
INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
INNER JOIN adherent a ON ((e.idPolice=a.idPolice) AND ( a.id=e.idAdherent))
INNER JOIN  police C ON  (e.idPolice = C.id)
INNER JOIN  college o ON  (o.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)
LEFT JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie =prt.numeroFeuilleMaladie )
LEFT JOIN `p_affection` af ON (f.codeAffection =af.codeAffection )
WHERE C.codeGcAssureur='FIDRA'
-- AND a.id=7994
AND prt.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276') 
ORDER BY prt.id;

DROP TEMPORARY TABLE IF EXISTS temp_facture_medu;	 
CREATE TEMPORARY TABLE temp_facture_medu
SELECT  
	A.idBeneficiaire,SUM(IFNULL(A.Arembourser,0)) AS Total 	
FROM temp_facture_leger A
GROUP BY A.idBeneficiaire
HAVING (CASE WHEN 25000>0 THEN SUM(IFNULL(A.Arembourser,0))>25000 ELSE SUM(IFNULL(A.Arembourser,0))>25000 END );	


SELECT  B.garant,B.idPolice,B.numeroPolice,B.libellePolice,B.idCollege,B.codeProduit,
	-- A.idFacture,
	A.dateFacture,
	B.idAdherent,B.numeroAdherent AS NoAdherent,B.adherent,		
	B.idBeneficiaire,B.numeroBeneficiaire AS NoBeneficiaire,B.beneficiaire,			
	A.Prescripteur,
	A.Exécutant,
	-- A.numeroFeuilleMaladie,A.numeroBonOrdonnance,A.codeAffection, -- A.affection, 
	A.montantReclame, tmDOC,
	A.TM,
	A.Arembourser
	,IFNULL(M.Total,0) AS Total 
FROM temp_facture_leger A
LEFT JOIN prestataire PR ON (PR.codePrestataire = A.codePrestataire)
LEFT JOIN vw_infos_contrat_assures B ON (B.idBeneficiaire = A.idBeneficiaire) 
INNER JOIN temp_facture_medu M ON (M.idBeneficiaire = A.idBeneficiaire)
WHERE A.Arembourser>0
ORDER BY B.garant,B.idPolice,B.idCollege,B.idAdherent,B.idBeneficiaire,A.Arembourser;

DROP TEMPORARY TABLE  temp_facture_leger;
DROP TEMPORARY TABLE  temp_facture_medu;


