SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[f_policies_situations_annual_totals]
(
	@policies_situations_table type_table_id READONLY
)
RETURNS 
@Table_Var TABLE 
(
	policy_situation_id int,
	pols_var_all_ann_tf ais_decimal2,
	pols_var_all_ann_taxes ais_decimal2, 
	pols_var_all_ann_ti ais_decimal2,
	pols_var_fee_ann_tf ais_decimal2, 
	pols_var_fee_ann_taxes ais_decimal2, 
	pols_var_fee_ann_ti ais_decimal2,
	pols_var_per_ann_tf ais_decimal2, 
	pols_var_per_ann_taxes ais_decimal2, 
	pols_var_per_ann_ti ais_decimal2,

	pols_var_bpc_ann_tf ais_decimal2,
	pols_var_bpf_ann_tf ais_decimal2,
	pols_var_brc_ann_tf ais_decimal2,
	pols_var_brf_ann_tf ais_decimal2,
	pols_var_cpf_ann_tf ais_decimal2,
	pols_var_crf_ann_tf ais_decimal2,
							
	pols_var_bpc_ann_taxes ais_decimal2,
	pols_var_bpf_ann_taxes ais_decimal2,
	pols_var_brc_ann_taxes ais_decimal2,
	pols_var_brf_ann_taxes ais_decimal2,
	pols_var_cpf_ann_taxes ais_decimal2,
	pols_var_crf_ann_taxes ais_decimal2,
							
	pols_var_bpc_ann_ti ais_decimal2,
	pols_var_bpf_ann_ti ais_decimal2,
	pols_var_brc_ann_ti ais_decimal2,
	pols_var_brf_ann_ti ais_decimal2,
	pols_var_cpf_ann_ti ais_decimal2,
	pols_var_crf_ann_ti ais_decimal2
)
AS

BEGIN

		insert into @Table_Var(
								policy_situation_id,
								pols_var_all_ann_tf,
								pols_var_all_ann_taxes, 
								pols_var_all_ann_ti,
								pols_var_fee_ann_tf, 
								pols_var_fee_ann_taxes, 
								pols_var_fee_ann_ti,
								pols_var_per_ann_tf, 
								pols_var_per_ann_taxes, 
								pols_var_per_ann_ti,
								
								pols_var_bpc_ann_tf,
								pols_var_bpf_ann_tf,
								pols_var_brc_ann_tf,
								pols_var_brf_ann_tf,
								pols_var_cpf_ann_tf,
								pols_var_crf_ann_tf,
							
								pols_var_bpc_ann_taxes,
								pols_var_bpf_ann_taxes,
								pols_var_brc_ann_taxes,
								pols_var_brf_ann_taxes,
								pols_var_cpf_ann_taxes,
								pols_var_crf_ann_taxes,
							
								pols_var_bpc_ann_ti,
								pols_var_bpf_ann_ti,
								pols_var_brc_ann_ti,
								pols_var_brf_ann_ti,
								pols_var_cpf_ann_ti,
								pols_var_crf_ann_ti)
		SELECT	policy_situation_id,
				pols_var_all_ann_tf = sum(tf),
				pols_var_all_ann_taxes = sum(taxes), 
				pols_var_all_ann_ti = sum(ti),
				pols_var_fee_ann_tf = sum(CASE WHEN nmpvt_peril = 0 THEN tf ELSE 0 end), 
				pols_var_fee_ann_taxes = sum(CASE WHEN nmpvt_peril = 0 THEN taxes ELSE 0 end), 
				pols_var_fee_ann_ti = sum(CASE WHEN nmpvt_peril = 0 THEN ti ELSE 0 end),
				pols_var_per_ann_tf = sum(CASE WHEN nmpvt_peril = 1 THEN tf ELSE 0 end), 
				pols_var_per_ann_taxes = sum(CASE WHEN nmpvt_peril = 1 THEN taxes ELSE 0 end), 
				pols_var_per_ann_ti = sum(CASE WHEN nmpvt_peril = 1 THEN ti ELSE 0 end),

				pols_var_bpc_ann_tf = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_cost = 1 THEN tf ELSE 0 end),
				pols_var_bpf_ann_tf = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_fee = 1 THEN tf ELSE 0 end),
				pols_var_brc_ann_tf = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_cost = 1 THEN tf ELSE 0 end),
				pols_var_brf_ann_tf = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_fee = 1 THEN tf ELSE 0 end),
				pols_var_cpf_ann_tf = sum(CASE WHEN nmpvt_company = 1 and nmpvt_policy = 1 and nmpvt_is_company_fee = 1 THEN tf ELSE 0 end),
				pols_var_crf_ann_tf = sum(CASE WHEN nmpvt_company = 1 and nmpvt_risk = 1 and nmpvt_is_company_fee = 1 THEN tf ELSE 0 end),
							
				pols_var_bpc_ann_taxes = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_cost = 1 THEN taxes ELSE 0 end),
				pols_var_bpf_ann_taxes = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_fee = 1 THEN taxes ELSE 0 end),
				pols_var_brc_ann_taxes = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_cost = 1 THEN taxes ELSE 0 end),
				pols_var_brf_ann_taxes = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_fee = 1 THEN taxes ELSE 0 end),
				pols_var_cpf_ann_taxes = sum(CASE WHEN nmpvt_company = 1 and nmpvt_policy = 1 and nmpvt_is_company_fee = 1 THEN taxes ELSE 0 end),
				pols_var_crf_ann_taxes = sum(CASE WHEN nmpvt_company = 1 and nmpvt_risk = 1 and nmpvt_is_company_fee = 1 THEN taxes ELSE 0 end),
							

				pols_var_bpc_ann_ti = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_cost = 1 THEN ti ELSE 0 end),
				pols_var_bpf_ann_ti = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_fee = 1 THEN ti ELSE 0 end),
				pols_var_brc_ann_ti = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_cost = 1 THEN ti ELSE 0 end),
				pols_var_brf_ann_ti = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_fee = 1 THEN ti ELSE 0 end),
				pols_var_cpf_ann_ti = sum(CASE WHEN nmpvt_company = 1 and nmpvt_policy = 1 and nmpvt_is_company_fee = 1 THEN ti ELSE 0 end),
				pols_var_crf_ann_ti = sum(CASE WHEN nmpvt_company = 1 and nmpvt_risk = 1 and nmpvt_is_company_fee = 1 THEN ti ELSE 0 end)--,

		FROM 

		(

			select	policy_situation_id, variable_id, tf = sum(tf), taxes = sum(taxes), ti = sum(ti)
			from	(
					select	policy_situation_id = polv_policy_situation_id, variable_id = polv_variable_id, tf = polv_tf, taxes = polv_taxes, ti = polv_ti
					FROM pol_variables ppv
						INNER JOIN @policies_situations_table pst ON pst.id = ppv.polv_policy_situation_id
				
					union all

					select	policy_situation_id = rrs.riss_policy_situation_id, variable_id = risv_variable_id, tf = risv_tf, taxes = risv_taxes, ti = risv_ti
					FROM ris_variables rv
						INNER JOIN ris_risks_situations rrs ON rrs.riss_risk_situation_id = rv.risv_risk_situation_id
						INNER JOIN @policies_situations_table pst ON pst.id = rrs.riss_policy_situation_id
	
					) as a
			group by policy_situation_id, variable_id
		) AS t 
			INNER JOIN pr_products_variables ppv ON ppv.prdv_variable_id = t.variable_id
			INNER JOIN nm_products_variables npv ON npv.nmpv_variable_code = ppv.prdv_variable_code
			INNER JOIN nm_products_variables_types npvt ON npvt.nmpvt_type_code = npv.nmpv_type_code
		GROUP BY t.policy_situation_id

		return

			 
/*
	SELECT * FROM ris_risks_situations rrs

	declare @policies_situations_table type_table_id

	INSERT INTO @policies_situations_table(id) values (337)
INSERT INTO @policies_situations_table(id) values (338)
INSERT INTO @policies_situations_table(id) values (339)

select * from f_policies_situations_annual_totals( @policies_situations_table)

*/

END


GO
