/*
select *
from pr_products_variables
where prdv_splittable = 0
select * from nm_pol_renew_types
*/

-- 0000644: Calcul ed la prime annuelle du tarif

--begin tran

declare @policies_situations table(policy_situation_id int, policy_number ais_code_long, periods_count int)

insert into @policies_situations(	policy_situation_id,		policy_number,	periods_count)
select								pols_policy_situation_id,	pols_number,	case when pols_monthly_payments = 1 then 12 else 12 / nmprts_months end
-- select	periods_count = case when pols_monthly_payments = 1 then 12 else 12 / nmprts_months end,* 
from pol_policies_situations pps
	inner join nm_pol_renew_types_split nprts on		nprts.nmprts_renew_type_code = pps.pols_renew_type_code
													and nprts.nmprts_renew_split_code = pps.pols_renew_split_code
where		pols_renew_type_code != 'FP' 
		and ( pols_renew_split_code != 'A')


--maj tf policies variables
update pv set polv_tf = polv_tf * periods_count
--select polv_tf_old = polv_tf, polv_tf_new = polv_tf * periods_count,*
from pol_variables pv
	inner join @policies_situations ps on pv.polv_policy_situation_id = ps.policy_situation_id
	inner join pr_products_variables ppv on ppv.prdv_variable_id = pv.polv_variable_id
where prdv_splittable = 0


-- update policies variables taxes
update pvt set polvt_tax_value = polvt_tax_value * periods_count
--select polvt_tax_value_old = polvt_tax_value, polvt_tax_value_new = polvt_tax_value * periods_count, *
from pol_variables_taxes pvt
	inner join pol_variables pv on		pv.polv_variable_id = pvt.polvt_variable_id
									and pv.polv_policy_situation_id = pvt.polvt_policy_situation_id
	inner join @policies_situations ps on pv.polv_policy_situation_id = ps.policy_situation_id
	inner join pr_products_variables ppv on ppv.prdv_variable_id = pv.polv_variable_id
where prdv_splittable = 0

-- update policies_variables total taxes and ti
UPDATE pv SET polv_taxes = sum_taxes, polv_ti = polv_tf + sum_taxes_not_included_in_net
--SELECT polv_taxes_old = polv_taxes, polv_taxes_new = sum_taxes, polv_ti_old = polv_ti, polv_ti_new = polv_tf + sum_taxes_not_included_in_net
FROM pol_variables pv
	INNER JOIN ( 
				select polv_policy_situation_id, polv_variable_id, sum_taxes = sum(polvt_tax_value), sum_taxes_not_included_in_net = sum(CASE WHEN polvt_tax_net_included = 0 THEN polvt_tax_value ELSE 0 end)
				FROM pol_variables pv
					INNER JOIN @policies_situations ps ON ps.policy_situation_id = pv.polv_policy_situation_id
					INNER JOIN pol_variables_taxes pvt ON polv_policy_situation_id = polvt_policy_situation_id AND polv_variable_id = polvt_variable_id
					inner join pr_products_variables ppv on ppv.prdv_variable_id = pv.polv_variable_id
				where prdv_splittable = 0
				GROUP BY polv_policy_situation_id, polv_variable_id) AS t ON t.polv_policy_situation_id = pv.polv_policy_situation_id AND t.polv_variable_id = pv.polv_variable_id


UPDATE rv SET risv_tf = risv_tf * periods_count
--SELECT risv_tf_old = risv_tf, risv_tf_new = risv_tf * periods_count, *
FROM ris_variables rv
	INNER JOIN ris_risks_situations rrs ON risv_risk_situation_id = riss_risk_situation_id
	INNER JOIN @policies_situations ps ON ps.policy_situation_id = rrs.riss_policy_situation_id
	INNER JOIN pr_products_variables ppv ON ppv.prdv_variable_id = rv.risv_variable_id
WHERE		prdv_splittable = 0
		AND (risv_tf != 0 OR rv.risv_taxes != 0)


UPDATE rvt SET risvt_tax_value = risvt_tax_value * periods_count
--SELECT risvt_tax_value_old = risvt_tax_value, risvt_tax_value_new = risvt_tax_value * periods_count, *
FROM ris_variables_taxes rvt
	INNER JOIN ris_variables rv ON risvt_risk_situation_id = risv_risk_situation_id AND risvt_variable_id = risv_variable_id
	INNER JOIN ris_risks_situations rrs ON risv_risk_situation_id = riss_risk_situation_id
	INNER JOIN @policies_situations ps ON ps.policy_situation_id = rrs.riss_policy_situation_id
	INNER JOIN pr_products_variables ppv ON ppv.prdv_variable_id = rv.risv_variable_id
WHERE		ppv.prdv_splittable = 0
		AND rvt.risvt_tax_value != 0


UPDATE rv SET risv_taxes = sum_taxes, risv_ti = risv_tf + sum_taxes_not_included_in_net
--SELECT risv_taxes_old = risv_taxes, risv_taxes_new = sum_taxes, risv_ti_old = risv_ti, risv_ti_new = risv_tf + sum_taxes_not_included_in_net
FROM ris_variables rv	
	inner JOIN (
					SELECT rv.risv_risk_situation_id, rv.risv_variable_id, sum_taxes = sum(risvt_tax_value), sum_taxes_not_included_in_net = sum(CASE WHEN risvt_tax_net_included = 0 THEN risvt_tax_value ELSE 0 end)
					FROM ris_variables rv
						inner JOIN ris_variables_taxes rvt ON rv.risv_risk_situation_id = rvt.risvt_risk_situation_id AND rv.risv_variable_id = rvt.risvt_variable_id
						inner JOIN ris_risks_situations rrs ON rv.risv_risk_situation_id = rrs.riss_risk_situation_id
						inner JOIN @policies_situations ps ON ps.policy_situation_id = rrs.riss_policy_situation_id
						inner JOIN pr_products_variables ppv ON ppv.prdv_variable_id = rv.risv_variable_id
					WHERE ppv.prdv_splittable = 0
					GROUP BY rv.risv_risk_situation_id, rv.risv_variable_id) as t ON t.risv_risk_situation_id = rv.risv_risk_situation_id AND t.risv_variable_id = rv.risv_variable_id

/*
SELECT * 
FROM ris_risks_situations rrs
	inner JOIN ( 
				SELECT rrs.riss_risk_situation_id, tf_sum = SUM(rv.risv_tf), taxes_sum = SUM(rv.risv_taxes), ti_sum = SUM(rv.risv_ti)
				FROM ris_risks_situations rrs
					inner JOIN @policies_situations ps ON ps.policy_situation_id = rrs.riss_policy_situation_id
					inner JOIN ris_variables rv ON rrs.riss_risk_situation_id = rv.risv_risk_situation_id
				GROUP BY rrs.riss_risk_situation_id ) t ON t.riss_risk_situation_id = rrs.riss_risk_situation_id 
*/



--ROLLBACK TRAN

		