SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
Cette procédure renvoie le total des variables des risques des contrats sauf ceux passés en paramètres
Par exemple, un contrat a deux risques, 1 et 2, si on passe 1 la rocédure va renvoyer le cumul des montants pour 2
*/
CREATE procedure [dbo].[p_policies_risks_variables_totals_except]
	@policies_situations type_table_id READONLY,
	@risks_situations type_table_id READONLY
as

DECLARE @policies TABLE(policy_id int)

INSERT INTO @policies(policy_id)
SELECT DISTINCT pols_policy_id 
FROM pol_policies_situations pps
	inner JOIN @policies_situations ps on ps.id = pps.pols_policy_situation_id


DECLARE @ris_risks_passed TABLE(risk_id INT)

INSERT INTO @ris_risks_passed( risk_id)
SELECT DISTINCT					rrs.riss_risk_id		
FROM ris_risks_situations rrs
	INNER JOIN @risks_situations rs ON rs.id = rrs.riss_risk_situation_id

DECLARE @ris_risks_except TABLE(risk_situation_id INT)

INSERT INTO @ris_risks_except(	risk_situation_id)
SELECT DISTINCT					rr.ris_last_risk_situation_id
FROM ris_risks rr
	inner JOIN  @policies p on p.policy_id = rr.ris_policy_id
	inner JOIN ris_risks_situations rrs ON		rrs.riss_risk_situation_id = rr.ris_last_risk_situation_id
											AND rrs.riss_obsolete = 0
	left JOIN @ris_risks_passed rrpe ON rrpe.risk_id = rr.ris_risk_id
WHERE rrpe.risk_id is null

SELECT	rrs.*,
		riss_ann_tf = tf_sum,
		riss_ann_taxes = taxes_sum,
		riss_ann_ti = ti_sum	
FROM ris_risks_situations rrs
	inner JOIN (
					SELECT rv.risv_risk_situation_id, tf_sum = SUM(risv_tf), taxes_sum = SUM(rv.risv_taxes), ti_sum = SUM(rv.risv_ti)
					from ris_variables rv
						--inner JOIN ris_risks_situations rrs ON rv.risv_risk_situation_id = rrs.riss_risk_situation_id
						INNER JOIN @ris_risks_except rre ON rre.risk_situation_id = rv.risv_risk_situation_id 
					GROUP BY risv_risk_situation_id) as t on t.risv_risk_situation_id = rrs.riss_risk_situation_id





GO
