-- 1 - RECAPITULATIF

SELECT		 CONCAT (fn_numordre(MONTH(LEFT(B.dateSysteme,10)),2) ,'/',YEAR(LEFT(B.dateSysteme,10))) AS DatePaiement,	  
		C.id,C.numeroPolice,C.libellePolice,e.dateEffet,e.dateFin,
		a.id,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
		SUM(IFNULL(prt.fraisReel,0)) AS montantReclame, -- 97 496 468
		SUM(IFNULL(prt.montantApayer,0)) AS montantRembourse
	FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
	INNER JOIN beneficiaire Be ON ( Be.id=prt.idBeneficiaire )
	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 entetecontrat e ON (e.idPolice=C.id)
	WHERE fn_id_entetecontrat_police(C.id,CURDATE())=fn_id_entetecontrat_police_courant(C.id)	
	AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete="vitalis")
	AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
	AND e.encours=1 AND C.codeGcAssureur='SIDAM'  
	-- AND (LEFT(B.dateSysteme,10) <='2022-04-30')  VALIDE
	 AND (LEFT(B.dateSysteme,10) BETWEEN '2022-05-01' AND '2022-05-31' ) -- PROVISOIRE
	-- AND a.id=564	
	GROUP BY B.codeMois,B.codeExercice,a.id,a.numeroAdherent,a.nom,a.prenoms ,C.id,C.libellePolice,C.numeroPolice,e.dateEffet,e.dateFin 
	HAVING SUM(IFNULL(prt.montantApayer,0))>0
	-- ORDER BY C.id,a.id,montantRembourse DESC
	ORDER BY C.id,DatePaiement ASC ,a.id,montantRembourse DESC
	
	-- call sp_ft2_depense_contrat('2022-01-31','2022-05-31','SIDAM' )
	-- select * from entetecontrat where idPolice=84
	
-- 2 - VERIFICATION 	
SELECT SUM(IFNULL(prt.montantApayer,0)) AS apresDecompte  -- 80 654 577 
FROM p_prestationactes prt INNER 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 entetecontrat e ON (e.idPolice=C.id)
WHERE fn_id_entetecontrat_police(C.id,CURDATE())=fn_id_entetecontrat_police_courant(C.id)
AND (LEFT(B.dateSysteme,10) <= '2022-04-30' )  -- 97611442
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete="vitalis") 
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
AND G.codeGcAssureur='SIDAM'  AND e.encours=1  

-- 3 - DETAIL DEPENSES

SELECT
	/*CONCAT (
	CASE WHEN (B.codeMois IS NULL OR B.codeMois='') THEN fn_numordre(MONTH(LEFT(B.dateSysteme,10)),2) END
	,'/',
	CASE WHEN (B.codeExercice IS NULL OR B.codeExercice='') THEN YEAR(LEFT(B.dateSysteme,10)) END) AS DatePaiement,	*/
	CONCAT (fn_numordre(MONTH(LEFT(B.dateSysteme,10)),2) ,'/',YEAR(LEFT(B.dateSysteme,10))) AS DatePaiement,	  
	C.id,C.numeroPolice,C.libellePolice,e.dateEffet,e.dateFin,
	B.codePrestataire Compte,fn_libelle_prestataire(B.codePrestataire) AS Prestataire,
	prt.codeActe,prt.datePrestation,
	a.id,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	Be.id AS idbenef,Be.numeroBeneficiaire,CONCAT(Be.nomBeneficiaire,' ',Be.prenomsBeneficiaire)  beneficiaire,
	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 beneficiaire Be ON ( Be.id=prt.idBeneficiaire )
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 entetecontrat e ON (e.idPolice=C.id)
WHERE fn_id_entetecontrat_police(C.id,CURDATE())=fn_id_entetecontrat_police_courant(C.id)	
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete="vitalis")
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
AND e.encours=1 AND C.codeGcAssureur='SIDAM'  
	-- AND (LEFT(B.dateSysteme,10) <='2022-04-30')  VALIDE
	 AND (LEFT(B.dateSysteme,10) BETWEEN '2022-05-01' AND '2022-05-31' ) -- PROVISOIRE
GROUP BY B.codeMois,B.codeExercice,
a.id,a.numeroAdherent,a.nom,a.prenoms ,C.id,C.libellePolice,C.numeroPolice,e.dateEffet,e.dateFin 
,B.codePrestataire,prt.codeActe,prt.datePrestation,Be.id,Be.numeroBeneficiaire,Be.nomBeneficiaire,Be.prenomsBeneficiaire
HAVING SUM(IFNULL(prt.montantApayer,0))>0
ORDER BY C.id,DatePaiement ASC ,a.id,montantRembourse DESC
	


