-- 1 - EFFECTIF

DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;
 
CREATE TEMPORARY TABLE temp_beneficiaire_l
SELECT A.id AS idBeneficiaire, A.dateSortie,
       DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(A.dateNaissance, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(A.dateNaissance, '00-%m-%d')) AS age  	
FROM beneficiaire A 
JOIN adherent B ON (B.id=A.idAdherent)
JOIN police C ON (C.id=A.idPolice)	
WHERE (C.id='156');
-- AND (CASE WHEN '2023-04-04' IS NULL THEN A.dateEntree IS NOT NULL ELSE A.dateEntree <= '2023-04-04' END); 


DELETE FROM temp_beneficiaire_l  WHERE CASE WHEN YEAR(dateSortie)=YEAR('2023-04-04') THEN YEAR(dateSortie)<YEAR('2023-04-04')-1
					ELSE YEAR(dateSortie)<YEAR('2023-04-04') END ;

SELECT  C.codeGcAssureur AS codeGarant,`fn_libelle_garant_rp`(C.codeGcAssureur) AS Garant,
	fn_libelle_produit(CL.codeProduit,C.codeGcAssureur) AS produit,C.dateEffet,C.dateFin,
	A.numeroAdherent,CONCAT(B.nom,'',B.prenoms) AS adherent,
	A.numeroBeneficiaire,CONCAT(A.nomBeneficiaire,'',A.prenomsBeneficiaire) AS beneficiaire,
	A.sexe,L.age,A.codeLienParente,
	CASE WHEN (A.numeroSociete IS NULL OR A.numeroSociete=(NULL)) AND A.numeroMatricule<>'' THEN A.numeroMatricule
	 ELSE A.numeroSociete END AS MlleEmploye
FROM beneficiaire A 
JOIN temp_beneficiaire_l L ON (L.idBeneficiaire=A.id)
JOIN adherent B ON (B.id=A.idAdherent)
JOIN police C ON (C.id=A.idPolice)	
LEFT JOIN produit P ON (P.codeSociete=A.codeSociete AND P.codeProduit=A.codeProduit AND P.codeGcAssureur = C.codeGcAssureur)
LEFT JOIN naturepiece N ON (N.codeSociete=A.codeSociete AND N.codeNaturePiece=A.codeNaturePiece)
LEFT JOIN motifsortie M ON (M.codeMotifSortie=A.codeMotifSortie)
LEFT JOIN etatpersonne H ON (H.codeEtatPersonne=A.codeEtatBeneficiaire)
LEFT JOIN college CL ON (CL.id=A.idCollege AND CL.codeGcAssureur = C.codeGcAssureur)
	LEFT JOIN avenant AV ON ((AV.idPolice=A.idPolice) AND
	CASE WHEN A.dateSortie IS NOT NULL THEN  AV.dateEffet=A.dateSortie ELSE AV.dateEffet=A.dateEffet END )
WHERE (A.idPolice>0)
AND (A.nomBeneficiaire NOT LIKE "%AVENANT%")
AND (C.id='156')
AND CASE WHEN A.sorti = "1" THEN A.dateSortie >= '2023-04-04' ELSE (A.dateSortie IS NULL OR A.dateSortie=(NULL))  END 
AND (CASE WHEN '2023-04-04' IS NULL THEN A.dateEntree IS NOT NULL ELSE A.dateEntree <= '2023-04-04' END)
ORDER BY CL.codeProduit,A.numeroAdherent,A.codeLienParente;

DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;