
	SELECT * FROM police WHERE libellePolice LIKE '%coco%'

	DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;	 
	CREATE TEMPORARY TABLE temp_beneficiaire_l
	
	SELECT A.id AS idBeneficiaire,A.dateEntree,A.dateSortie,A.sorti
	FROM beneficiaire A 
	JOIN adherent B ON (B.id=A.idAdherent)
	JOIN police C ON (C.id=A.idPolice)
	JOIN gc_assureur g ON (C.codeGcAssureur=g.codeGcAssureur)	
	WHERE (CASE WHEN YEAR('2022-04-05')=YEAR(CURDATE()) THEN (A.dateEffet BETWEEN '2022-04-05' AND '2023-04-30')
	     ELSE (A.dateEntree<='2023-04-30')  END)	
	AND A.nomBeneficiaire NOT LIKE "%AVENANT%" AND A.codeLienParente='A'
	AND g.id!='45' AND C.idClient!='49' 
	AND (CASE WHEN 'ALLIANZCI' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='ALLIANZCI' END) 
	AND (CASE WHEN 229 IS NULL THEN C.id IS NOT NULL ELSE C.id=229 END)	;
	
	DELETE FROM temp_beneficiaire_l  WHERE CASE WHEN YEAR(dateSortie)=YEAR('2022-04-05') THEN YEAR(dateSortie)<YEAR('2022-04-05')-1
					ELSE YEAR(dateSortie)<YEAR('2022-04-05') END ;
	
	 
	SELECT DISTINCT C.codeGcAssureur,C.id,C.libellePolice,A.idCollege,CL.libelleCollege,-- A.codeProduit,
		A.idAdherent,A.numeroAdherent,CONCAT(B.nom, " ", IFNULL(B.prenoms,"")) AS Adherent,
		A.id AS idBeneficiaire,A.numeroBeneficiaire,CONCAT(A.nomBeneficiaire, " ", IFNULL(A.prenomsBeneficiaire,"")) AS Beneficiaire,
		-- P.libelle AS produit,A.numeroBeneficiaire,
		A.codeLienParente,N.libelle AS naturepiece,A.sexe, 
		DATE_FORMAT(A.dateNaissance, '%d/%m/%Y') dateNaissance,
		-- CASE WHEN YEAR(A.dateEffet)>YEAR('2022-04-05') THEN DATE_FORMAT(A.dateEntree, '%d/%m/%Y') ELSE DATE_FORMAT(A.dateEffet, '%d/%m/%Y') END AS dateEffet,
		DATE_FORMAT(A.dateEntree, '%d/%m/%Y') dateEntree,
		DATE_FORMAT(A.dateSortie, '%d/%m/%Y') dateSortie,
		A.telephonePortable, M.libelle AS motifsortie,H.libelle AS etatbeneficiaire,AV.codeTypeAvenant,A.numeroPiece
	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) AND (C.codeEtatPolice NOT IN ('RE','EX','AN')))	
	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.id>0 AND A.codeLienParente='A' AND A.dateEntree >= '2022-05-01' 
	AND CASE WHEN A.sorti = "1" THEN A.dateSortie >= '2023-04-30' ELSE (A.dateSortie IS NULL OR A.dateSortie=(NULL))  END 
        ORDER BY  C.codeGcAssureur,C.id,A.idCollege,A.idAdherent,A.codeLienParente;
        
	DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;