
-- SELECT @idPolice,@dateEffet,@idBeneficiaire,@idAdherent,@exercieReference  ;

-- SELECT fn_consommation_beneficiaire(2091,NOW())

-- 0 -- RECHERCHE INFOS POLICE / ENTETE / EXERCICE

SELECT 
	A.idPolice,A.dateEffet,A.id,A.idAdherent,fn_exercice_beneficiaire(@idBeneficiaire, A.dateEffet) 
INTO 
	@idPolice,@dateEffet,@idBeneficiaire,@idAdherent,@exercieReference 
FROM beneficiaire A WHERE (A.id=@idBeneficiaire) 
LIMIT 1;
 
SELECT fn_id_entetecontrat_police(@idPolice, @dateEffet) INTO @idEntetecontrat;
	

-- 1 -- RECHERCHE / MAJ SINISTRES BENEFICIAIRES
DROP TEMPORARY TABLE IF EXISTS temp_facture_smart;
CREATE TEMPORARY TABLE temp_facture_smart
SELECT  B.idBeneficiaire,prt.codeGarantie,
	SUM(IFNULL(prt.montantApayer,0)) AS consommation
FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
INNER JOIN  police C ON  (B.idPolice = C.id)
INNER JOIN  gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)
INNER JOIN  adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
INNER JOIN  beneficiaire e ON (e.id=prt.idBeneficiaire)
INNER JOIN  entetecontrat T ON (T.idPolice=B.idPolice)
WHERE (B.idBeneficiaire=@idBeneficiaire) 
AND prt.ententePrealable NOT IN ('2','9')
AND T.id = fn_id_entetecontrat_beneficiaire_courant(@idBeneficiaire)
AND G.id!='45' AND C.idClient!='49'   
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
GROUP BY B.idBeneficiaire,prt.codeGarantie;	

UPDATE garantiebeneficiaire A
JOIN temp_facture_smart B ON ((B.idBeneficiaire=A.idBeneficiaire) AND (B.codeGarantie=A.codeGarantie))
	SET A.consommation = A.consommation + B.consommation
WHERE (A.idBeneficiaire=@idBeneficiaire) 
AND (A.idEntetecontrat=@idEntetecontrat);


-- 2 -- RECHERCHE / MAJ SINISTRES AFDEHENTS
DROP TEMPORARY TABLE IF EXISTS temp_facture_smartAD;	  
CREATE TEMPORARY TABLE temp_facture_smartAD
SELECT  B.idAdherent,prt.codeGarantie,
	SUM(IFNULL(prt.montantApayer,0)) AS consommation
FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
INNER JOIN  police C ON  (B.idPolice = C.id)
INNER JOIN  gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)
INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
INNER JOIN  entetecontrat T ON (T.idPolice=B.idPolice)
WHERE (B.idAdherent=@idAdherent) 
AND T.id = fn_id_entetecontrat_adherent_courant(@idAdherent)
 AND G.id!='45' AND C.idClient!='49'   
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
GROUP BY B.idAdherent,prt.codeGarantie;		

UPDATE garantieadherent A
JOIN temp_facture_smartAD B ON (B.idAdherent=A.idAdherent) AND (B.codeGarantie=A.codeGarantie)
SET A.consommation = A.consommation + B.consommation
WHERE (A.idAdherent=@idAdherent) 
AND (A.idEntetecontrat=@idEntetecontrat);


DROP TEMPORARY TABLE IF EXISTS temp_facture_smart;
DROP TEMPORARY TABLE IF EXISTS temp_facture_smartAD;

