-- recherche doublon benef
/*SELECT a.nomBeneficiaire,a.prenomsBeneficiaire,a.dateNaissance,COUNT(*) AS SOUBLON
FROM beneficiaire a
INNER JOIN beneficiaire b ON ( a.id=b.id )
WHERE a.nomBeneficiaire=b.nomBeneficiaire
AND   a.prenomsBeneficiaire=b.prenomsBeneficiaire
AND   a.dateNaissance=b.dateNaissance
AND a.idPolice=82
GROUP BY a.nomBeneficiaire,a.prenomsBeneficiaire,a.dateNaissance
HAVING COUNT(*)>1*/


SELECT * FROM p_reglement WHERE codeExercice=201



SELECT * FROM p_facture WHERE codeExercice=201

UPDATE p_facture SET codeExercice=2021 WHERE codeExercice=201




DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;	 
CREATE TEMPORARY TABLE temp_beneficiaire_l
SELECT A.*
-- 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)
JOIN avenant v ON ((v.idPolice=A.idPolice) AND (v.id=A.idAvenant))	
WHERE A.idPolice>0
AND (CASE WHEN 'GNA' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur= 'GNA' END) 
AND (CASE WHEN 82 IS NULL THEN C.id IS NOT NULL ELSE C.id=82 END)	
-- AND (CASE WHEN p_idCollege IS NULL THEN A.idCollege IS NOT NULL ELSE A.idCollege=p_idCollege END)  
AND YEAR(v.dateEffet)=2021 
AND CASE WHEN A.sorti = "1" THEN A.dateSortie >= '2022-12-31' ELSE A.dateSortie IS NULL END  
AND C.codeEtatPolice NOT IN ('RE','EX','AN')
AND A.nomBeneficiaire='ADJEMIEN'
AND A.prenomsBeneficiaire='PIERRE'
AND A.nomBeneficiaire NOT LIKE "%AVENANT%";
	 
SELECT
	C.id, C.numeroPolice,C.libellePolice,A.idCollege, 
	CONCAT(" ",A.numeroAdherent) AS numeroAdherent,
	CONCAT(B.nom, " ",IFNULL(B.prenoms,"")) AS nomAdherent,
	A.id AS idBeneficiaire, 
	CONCAT(" ",A.numeroBeneficiaire) AS numeroBeneficiaire,
	A.nomBeneficiaire, 
	A.prenomsBeneficiaire, 
	A.codeLienParente, 
	A.sexe, 
	DATE_FORMAT(A.dateNaissance, '%d/%m/%Y') dateNaissance,
	A.telephonePortable, 
	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)	
JOIN clients D ON (D.id=C.idClient)	
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)
WHERE (A.idPolice=82) 
ORDER BY C.id,A.idCollege, B.nom,B.prenoms,A.codeLienParente,A.nomBeneficiaire, A.prenomsBeneficiaire;

-- DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;