-- 1--plafond Individuel / liste des beneficiaires à risque GARANT/SOUSCRIPTEUR/POLICE/COLLEGE/SEUIL
CALL sp_alerte_depense_plfInd_rp(NULL,NULL,NULL,NULL,75)
-- 2--plafond FAMILLE adherent / liste des familles à risque GARANT/SOUSCRIPTEUR/POLICE/COLLEGE/SEUIL
CALL sp_alerte_depense_plfFamille_rp(NULL,NULL,NULL,NULL,60)
-- 3--plafond Garantie Individuel / liste des beneficiaires avec des garanties à risque GARANT/SOUSCRIPTEUR/POLICE/COLLEGE/SEUIL
CALL sp_alerte_depGarantie_plfFamille_rp(NULL,NULL,NULL,NULL,100)


-- 1 plafond Individuel liste des beneficiaires à risque

SELECT 
	p.codeGcAssureur AS CodeG,fn_libelle_garant(b.idPolice) AS garant,
	b.idPolice,fn_libelle_police_souscripteur(b.idPolice) AS police,
	fn_bareme_college(b.idCollege) AS bareme,
	a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	b.id AS idbenef,b.numeroBeneficiaire,CONCAT(b.nomBeneficiaire,' ',b.prenomsBeneficiaire)  Beneficiaire,
	b.plafond AS plafondIndividuel,
	fn_consommation_beneficiaire_courant(b.id) AS consoIndividuel,
	ABS(b.plafond  - fn_consommation_beneficiaire_courant(b.id)) AS depassement,
	ROUND(fn_consommation_beneficiaire_courant(b.id)/b.plafond*100,0) AS pourcentIndividuel
FROM beneficiaire b 
INNER JOIN adherent a ON ((b.codeSociete=a.codeSociete) AND ( a.id=b.idAdherent))
INNER JOIN police p ON (p.id=b.idPolice)
INNER JOIN gc_assureur g ON  (p.codeGcAssureur = g.codeGcAssureur)
WHERE b.plafond> "0" AND b.plafond!="9999999999"
AND (CASE WHEN p_garant IS NULL THEN p.codeGcAssureur IS NOT NULL ELSE p.codeGcAssureur=p_garant END)  
AND (CASE WHEN p_idClient IS NULL THEN p.idClient IS NOT NULL ELSE p.idClient=p_idClient END)  
AND (CASE WHEN p_idPolice IS NULL THEN b.idPolice IS NOT NULL ELSE b.idPolice=p_idPolice END)  	
AND (CASE WHEN p_idCollege IS NULL THEN a.idCollege IS NOT NULL ELSE a.idCollege=p_idCollege END)  
AND ROUND(fn_consommation_beneficiaire_courant(b.id)/b.plafond*100,0) >= p_seuil
AND fn_id_entetecontrat_police(b.idPolice,DATE(NOW()))=fn_id_entetecontrat_police_courant(b.idPolice)
AND g.id!='45'   AND p.idClient!='49'
ORDER BY p.codeGcAssureur,b.idPolice,a.id,b.id,pourcentIndividuel DESC ;

-- 2  plafond famille liste des aherents à risque
SELECT 
	p.codeGcAssureur AS CodeG,fn_libelle_garant(a.idPolice) AS garant,
	a.idPolice,fn_libelle_police_souscripteur(a.idPolice) AS police,
	fn_bareme_college(a.idCollege) AS bareme,
	a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	fn_plafond_famille(a.idCollege) AS plafondFamille,
	fn_consommation_famille_courante(a.id) AS consoPlafondFamille,
	ABS(fn_plafond_famille(a.idCollege)  - fn_consommation_famille_courante(a.id)) AS depassement,
	ROUND(fn_consommation_famille_courante(a.id)/fn_plafond_famille(a.idCollege)*100,0) AS pourcentFamille
FROM adherent a 
INNER JOIN police p ON (p.id=a.idPolice)
INNER JOIN gc_assureur g ON  (p.codeGcAssureur = g.codeGcAssureur)
WHERE fn_plafond_famille(a.idCollege) > "0" AND fn_plafond_famille(a.idCollege) !="9999999999"
AND ROUND(fn_consommation_famille_courante(a.id)/fn_plafond_famille(a.idCollege)*100,0) >= 60
AND fn_id_entetecontrat_police(a.idPolice,DATE(NOW()))=fn_id_entetecontrat_police_courant(a.idPolice)
AND g.id!='45'   AND p.idClient!='49'
ORDER BY p.codeGcAssureur,idPolice,a.id,pourcentFamille DESC 

-- -- 3 plafond GARANTIE Des beneficiaires à risque
SELECT 
	DISTINCT p.codeGcAssureur AS CodeG,fn_libelle_garant(b.idPolice) AS garant,
	b.idPolice,fn_libelle_police_souscripteur(b.idPolice) AS police,
	g.codeGarantie,g.garantie,
	a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	b.id AS idbenef,b.numeroBeneficiaire,CONCAT(b.nomBeneficiaire,' ',b.prenomsBeneficiaire)  Beneficiaire,
	g.plafondAnnuel AS plafondIndividuel,
	fn_consommation_benefGarantie_courant(b.id,g.codeGarantie) AS consoIndividuel,
	ABS(g.plafondAnnuel  - fn_consommation_benefGarantie_courant(b.id,g.codeGarantie)) AS depassement,
	ROUND(fn_consommation_benefGarantie_courant(b.id,g.codeGarantie)/g.plafondAnnuel*100,0) AS pourcentIndividuel
FROM beneficiaire b 
INNER JOIN adherent a ON ((b.codeSociete=a.codeSociete) AND ( a.id=b.idAdherent))
INNER JOIN police p ON (p.id=b.idPolice)
INNER JOIN gc_assureur c ON  (p.codeGcAssureur = c.codeGcAssureur)
INNER JOIN garantiebeneficiaire g ON ((p.id=g.idPolice) AND ( a.id=g.idAdherent)AND ( b.id=g.idBeneficiaire))
WHERE g.plafondAnnuel> "0" AND g.plafondAnnuel!="9999999999"
-- AND b.numeroBeneficiaire='0082004402'
AND ROUND(fn_consommation_benefGarantie_courant(b.id,g.codeGarantie)/g.plafondAnnuel*100,0) > 100
AND fn_id_entetecontrat_police(b.idPolice,DATE(NOW()))=fn_id_entetecontrat_police_courant(b.idPolice)
ORDER BY p.codeGcAssureur,b.idPolice,a.id,b.id,g.codeGarantie,g.garantie,pourcentIndividuel DESC ;


-- -- 4 plafond GARANTIE Des adherents à risque
/*
SELECT DISTINCT 
	p.codeGcAssureur AS CodeG,fn_libelle_garant(a.idPolice) AS garant,
	a.idPolice,fn_libelle_police_souscripteur(a.idPolice) AS police,
	g.codeGarantie,g.garantie,
	a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	g.plafondAnnuel AS plafondFamille,
	fn_consommation_familleGarantie_courant(a.id,g.codeGarantie) AS consoPlafondFamille,
	ABS(g.plafondAnnuel - fn_consommation_familleGarantie_courant(a.id,g.codeGarantie)) AS depassement,
	ROUND(fn_consommation_familleGarantie_courant(a.id,g.codeGarantie)/g.plafondAnnuel *100,0) AS pourcentFamille
FROM adherent a 
INNER JOIN police p ON (p.id=a.idPolice)
INNER JOIN garantieadherent g ON ((p.id=g.idPolice) AND ( a.id=g.idAdherent))
WHERE g.plafondAnnuel > "0" AND g.plafondAnnuel!="9999999999"
AND ROUND(fn_consommation_familleGarantie_courant(a.id,g.codeGarantie)/g.plafondAnnuel *100,0) > 100
AND fn_id_entetecontrat_police(a.idPolice,DATE(NOW()))=fn_id_entetecontrat_police_courant(a.idPolice)
-- AND b.idPolice!="62"
ORDER BY codeGcAssureur,idPolice,a.id,pourcentFamille DESC */
	 
	 


