DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;
 
CREATE TEMPORARY TABLE temp_beneficiaire_l
SELECT A.id AS idBeneficiaire, A.dateSortie
FROM beneficiaire A 
JOIN adherent B ON (B.id=A.idAdherent)
JOIN police C ON (C.id=A.idPolice)	
INNER JOIN  avenant v ON ((v.id=A.idAvenant) AND (v.idPolice=A.idPolice))		
WHERE (A.idPolice=156)
AND (v.dateEffet<="2022-10-11") AND (A.nomBeneficiaire NOT LIKE "%AVENANT%")
-- AND (A.numeroSociete IS NULL OR A.numeroSociete='') -- SANS
AND (A.numeroSociete IS NOT NULL OR A.numeroSociete!='') -- AVEC
AND CASE WHEN A.sorti = "1" THEN A.dateSortie >= "2022-10-11" ELSE A.dateSortie IS NULL END;
 

 
SELECT	
	A.codeProduit,P.libelle AS produit,A.idCollege,CL.libelleCollege ,
	A.idAdherent,A.numeroAdherent,CONCAT(B.nom, " ", IFNULL(B.prenoms,"")) AS nomAdherent,
	A.id AS idBeneficiaire,A.numeroBeneficiaire,CONCAT(A.nomBeneficiaire, " ", IFNULL(A.prenomsBeneficiaire,"")) AS nomBeneficiaire,
	A.codeLienParente,A.numeroSociete AS departement,A.sexe AS Genre, 
	DATE_FORMAT(A.dateNaissance, '%d/%m/%Y') dateNaissance,A.telephonePortable, 
	DATE_FORMAT(A.dateEffet, '%d/%m/%Y') dateEffet,
	DATE_FORMAT(A.dateEntree, '%d/%m/%Y') dateEntree,
	DATE_FORMAT(A.dateSortie, '%d/%m/%Y') dateSortie,
	M.libelle AS motifsortie,
	H.libelle AS etatbeneficiaire	
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.id=A.idAvenant)
WHERE (A.idPolice=156) AND (A.nomBeneficiaire NOT LIKE "%AVENANT%")

ORDER BY  A.idCollege,A.idAdherent,A.codeLienParente;

-- DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;