SELECT * FROM apporteur WHERE libelle LIKE '%premi%'

-- 1 POLICES NON RENOUVELLEES	
	SELECT 	
		A.codeGcAssureur,fn_libelle_garant_rp (A.codeGcAssureur) AS garant,
		A.idClient AS idSouscripteur,A.numeroClient AS numeroSouscripteur,CONCAT(B.nom, " ", B.prenoms) AS Souscripteur,		
		A.id AS idPolice,A.numeroPolice,A.libellePolice,  
		A.dateEffet,A.dateFin,A.dateEcheance,A.codeEtatPolice,E.libelle AS etatPolice
		-- A.codeEtape
	FROM police A
	JOIN clients B ON (B.id=A.idClient)
	JOIN etatpolice E ON (E.codeEtatPolice=A.codeEtatPolice)
	-- WHERE (A.dateFin < '2023-01-02')
	WHERE A.codeApporteur='I000007'
	-- AND A.codeEtatPolice NOT IN ('RE','EX','AN')
	ORDER BY A.codeGcAssureur,A.idClient,A.id ;
	
	
-- 1 POLICES RENOUVELLEES	
SELECT 	
	A.codeGcAssureur,fn_libelle_garant_rp (A.codeGcAssureur) AS garant,
	A.idClient AS idSouscripteur,A.numeroClient AS numeroSouscripteur,CONCAT(B.nom, " ", B.prenoms) AS Souscripteur,		
	A.id AS idPolice,A.numeroPolice,A.libellePolice,  
		A.dateEffet,A.dateFin,A.dateEcheance,A.codeEtatPolice,E.libelle AS etatPolice
		-- A.codeEtape
FROM police A
JOIN gc_assureur G ON (G.codeGcAssureur=A.codeGcAssureur)
JOIN clients B ON (B.id=A.idClient)
JOIN etatpolice E ON (E.codeEtatPolice=A.codeEtatPolice)
WHERE (A.dateFin >= '2023-01-01')
AND G.typeGarant!='1'
-- AND A.codeApporteur='I000007'
ORDER BY A.codeGcAssureur,A.idClient,A.id ;

SELECT * FROM `gc_typegarant`

-- LISTES DES GARANTS
SELECT 	
	A.codeTypeGarant,A.libelle AS typeGarant,G.`codeGcAssureur`,G.`libelle` AS garant
FROM gc_typegarant A
JOIN gc_assureur G ON (A.id=G.typeGarant)
WHERE G.actif='1' AND A.id!='1'
ORDER BY G.codeGcAssureur;