--  detail sinistre


	DROP TEMPORARY TABLE IF EXISTS temp_facture_leger;	 
	CREATE TEMPORARY TABLE temp_facture_leger
	( INDEX(idFacture) , INDEX(idBeneficiaire))
	SELECT
		B.dateSysteme AS dateComptable,	
		prt.idfacture AS idFacture,		
		B.codePrestataire,
		B.idBeneficiaire,
		prt.datePrestation AS dateConsultation,
		prt.codeActe,
		-- f.dateConsultation,
		f.codeAffection,
		f.numeroFeuilleMaladie,
		f.numeroBonConsultation,
		f.numeroBonOrdonnance,
		f.numeroBonHospitalisation,
		f.numeroBonOptique,
		f.numeroBonExamen,
		f.numeroBonKine,
		SUM(IFNULL(prt.fraisReel,0)) AS fraisReel,
		SUM(IFNULL(prt.montantTm,0)) AS TM,
		SUM(IFNULL(prt.montantApayer,0)) AS Arembourser
	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))
	INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
	INNER JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
	WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-09-30')
	AND (CASE WHEN 156 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=156 END)  
	AND G.id!='45' AND C.idClient!='49' 
	AND B.valide=1 AND B.okDecompte=1
	AND f.codeAffection=990
	AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
	GROUP BY prt.idfacture,B.dateSysteme,B.codePrestataire,prt.codeActe,B.idBeneficiaire,f.numeroBonConsultation,
		f.numeroBonOrdonnance,f.numeroBonHospitalisation,f.numeroBonOptique,f.codeAffection,-- f.dateConsultation,
		f.numeroBonExamen,f.numeroBonKine,f.numeroFeuilleMaladie,prt.datePrestation 
	ORDER BY prt.idfacture;
  
 -- 1 detail des sinstres
	SELECT 
		
		-- LAST_DAY(A.dateComptable) AS dateComptable,
		PR.libelle AS prestataire,
		e.idAdherent,a.numeroAdherent AS NoAdherent,CONCAT(a.nom,'',a.prenoms)adherent,		
		e.id,e.numeroBeneficiaire AS NoBeneficiaire,CONCAT(e.nomBeneficiaire,'',e.prenomsBeneficiaire) beneficiaire,			
		-- A.dateConsultation,	
		A.codeAffection,
		A.numeroBonConsultation,
		A.numeroBonOrdonnance,
		A.numeroBonHospitalisation,
		A.numeroBonOptique,
		A.numeroBonExamen,
		A.numeroBonKine,
		A.fraisReel,
		A.TM,
		A.Arembourser
	FROM temp_facture_leger A
	LEFT JOIN prestataire PR ON (PR.codePrestataire = A.codePrestataire)
	LEFT JOIN beneficiaire e ON (e.id=A.idBeneficiaire)
	LEFT JOIN adherent a ON (a.id=e.idAdherent)	
	ORDER BY prestataire,e.idAdherent,A.idBeneficiaire,A.Arembourser;   -- dateConsultation DESC,	
	-- DROP TEMPORARY TABLE  temp_facture_leger;
	
	-- SELECT * FROM temp_facture_leger
	
-- 2 detail des MEDICAMENTS
	SELECT
		pm.codePrestatairePrescription AS codePrescripteur,fn_libelle_prestataire(pm.codePrestatairePrescription) AS Prescripteur,
		pm.codePrestataire AS codeExecutant,fn_libelle_prestataire(pm.codePrestataire) AS Executant,
		e.idAdherent,a.numeroAdherent AS NoAdherent,
		e.id,e.numeroBeneficiaire AS NoBeneficiaire,CONCAT(e.nomBeneficiaire,'',e.prenomsBeneficiaire) beneficiaire,
		B.numeroBonOrdonnance,
		m.codeMedicament,
		m.libelle,
		IFNULL(pm.fraisReel,0)AS montantReclame,IFNULL(pm.montantTm,0) AS TM,IFNULL(pm.montantArembourser,0) AS Arembourser 
	FROM temp_facture_leger B
	LEFT JOIN beneficiaire e ON (e.id=B.idBeneficiaire)
	LEFT JOIN adherent a ON (a.id=e.idAdherent)	
	INNER JOIN p_prestationmedicaments pm ON ((B.numeroFeuilleMaladie = pm.numeroFeuilleMaladie) AND (B.numeroBonOrdonnance = pm.numeroBonOrdonnance) )
	INNER JOIN p_medicament m ON (pm.codeMedicament= m.codeMedicament)
	WHERE pm.montantArembourse!=0
	ORDER BY pm.codePrestataire,B.idBeneficiaire,Arembourser DESC;   -- dateConsultation DESC,	
	
-- 3 details des examens

	SELECT DISTINCT
		pm.codePrestatairePrescription AS codePrescripteur,fn_libelle_prestataire(pm.codePrestatairePrescription) AS Prescripteur,
		pm.codePrestataire AS codeExecutant,fn_libelle_prestataire(pm.codePrestataire) AS Executant,
		a.numeroAdherent AS NoAdherent,
		e.numeroBeneficiaire AS NoBeneficiaire,CONCAT(e.nomBeneficiaire,'',e.prenomsBeneficiaire) beneficiaire,
		B.dateConsultation,B.numeroBonExamen,
		B.codeActe,
		fn_libelle_acte_rp(B.codeActe) AS Acte,
		IFNULL(pm.fraisReel,0)AS montantReclame,IFNULL(pm.montantTm,0) AS TM,IFNULL(pm.montantApayer,0) AS Arembourser 
	FROM temp_facture_leger B
	LEFT JOIN beneficiaire e ON (e.id=B.idBeneficiaire)
	LEFT JOIN adherent a ON (a.id=e.idAdherent)	
	INNER JOIN p_prestationexamens pm ON ((B.numeroFeuilleMaladie = pm.numeroFeuilleMaladie) AND (B.numeroBonExamen= pm.numeroBonExamen) AND (B.codeActe= pm.codeActe) )
	-- INNER JOIN p_examen m ON (pm.codeMedicament= m.codeMedicament)
	WHERE pm.montantApayer>0
	ORDER BY pm.codePrestataire,B.idBeneficiaire,B.dateConsultation,Arembourser DESC;   -- dateConsultation DESC,
	
	DROP TEMPORARY TABLE  temp_facture_leger;