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


SELECT * FROM beneficiaire WHERE idPolice=343 AND nomBeneficiaire LIKE '%AVENANT%'

SELECT * FROM college WHERE idPolice=343  

SELECT * FROM primecollege WHERE idPolice=343  


SELECT * FROM beneficiaireprime WHERE idBeneficiaire=45809

SELECT * FROM avenant  WHERE idPolice=133 

SELECT * FROM emission  WHERE idPolice=367 AND codeTypeAvenant='QTM'  AND idPolice IN (SELECT id FROM police WHERE )
AND (LEFT(dateEmission,10)  BETWEEN '2022-04-01' AND '2023-03-31' ) 

SELECT * FROM quittance  WHERE idPolice=133 

SELECT * FROM beneficiaire WHERE idPolice=133 AND factureRistourneVitalis=0



SELECT * FROM beneficiaire WHERE dateNaissance='0000-00-00'


-- DELETE FROM avenant  WHERE idPolice=133 AND ID IN (1796)



	SELECT c.exercice,q.id AS idEmission,c.dateEffet AS dateEf,c.dateFin  AS dateF,
		(TIMESTAMPDIFF(MONTH,c.dateEffet,c.dateFin)+1)  AS  Mois,
		(TIMESTAMPDIFF(MONTH,'2022-04-01','2023-03-31')+1) AS MoisPris,
		p.id AS idPolice,p.numeroPolice,p.libellePolice,-- MONTH(LEFT(dateEmission,10)) AS codeMois,	
		(TIMESTAMPDIFF(MONTH,q.dateEmission,c.dateFin)+1)  AS  codeMois,
		q.primeNette AS prime,ROUND(q.primeNette/(TIMESTAMPDIFF(MONTH,q.dateEmission,c.dateFin)+1),0) AS primeMois,
		
		CASE WHEN 30>0 THEN ROUND(ROUND(q.primeNette/(TIMESTAMPDIFF(MONTH,q.dateEmission,c.dateFin)+1),0)*30/100,0) ELSE 0 END AS  chargeMois,
		CASE WHEN 30>0 THEN (ROUND(q.primeNette/(TIMESTAMPDIFF(MONTH,q.dateEmission,c.dateFin)+1),0)-ROUND(ROUND(q.primeNette/(TIMESTAMPDIFF(MONTH,q.dateEmission,c.dateFin)+1),0)*30/100,0))
		ELSE ROUND(q.primeNette/(TIMESTAMPDIFF(MONTH,q.dateEmission,c.dateFin)+1),0) END AS  provisionMois
	FROM emission q
	LEFT JOIN police p ON (q.idPolice=p.id)
	LEFT JOIN gc_assureur g ON (p.codeGcAssureur = g.codeGcAssureur)
	LEFT JOIN entetecontrat c ON (p.id=c.idPolice)
	WHERE g.id!='45' AND p.idClient!='49' 
	AND (CASE WHEN 229 IS NULL THEN q.idPolice IS NOT NULL ELSE q.idPolice=229 END) 	
	AND (CASE WHEN "QTM" IS NULL THEN q.codeTypeAvenant IS NOT NULL ELSE q.codeTypeAvenant="QTM" END) 
	 AND (LEFT(dateEmission,10)  BETWEEN '2022-04-01' AND '2023-03-31' ) AND c.dateEffet=q.dateEffet ;