SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[f_policies_situations_active_invoices_totals]
(
	@policies_situations_table type_table_id READONLY
)
RETURNS 
@Table_Var TABLE 
(
	policy_situation_id int,
	pols_var_all_inv_tf ais_decimal2,
	pols_var_all_inv_taxes ais_decimal2, 
	pols_var_all_inv_ti ais_decimal2,
	pols_var_fee_inv_tf ais_decimal2, 
	pols_var_fee_inv_taxes ais_decimal2, 
	pols_var_fee_inv_ti ais_decimal2,
	pols_var_per_inv_tf ais_decimal2, 
	pols_var_per_inv_taxes ais_decimal2, 
	pols_var_per_inv_ti ais_decimal2,
	pols_var_all_inv_commissions_tf ais_decimal2,
	pols_var_all_inv_commissions_taxes ais_decimal2,
	pols_var_all_inv_commissions_ti ais_decimal2,

	pols_var_bpc_inv_tf ais_decimal2,
	pols_var_bpf_inv_tf ais_decimal2,
	pols_var_brc_inv_tf ais_decimal2,
	pols_var_brf_inv_tf ais_decimal2,
	pols_var_cpf_inv_tf ais_decimal2,
	pols_var_crf_inv_tf ais_decimal2,
							
	pols_var_bpc_inv_taxes ais_decimal2,
	pols_var_bpf_inv_taxes ais_decimal2,
	pols_var_brc_inv_taxes ais_decimal2,
	pols_var_brf_inv_taxes ais_decimal2,
	pols_var_cpf_inv_taxes ais_decimal2,
	pols_var_crf_inv_taxes ais_decimal2,
							

	pols_var_bpc_inv_ti ais_decimal2,
	pols_var_bpf_inv_ti ais_decimal2,
	pols_var_brc_inv_ti ais_decimal2,
	pols_var_brf_inv_ti ais_decimal2,
	pols_var_cpf_inv_ti ais_decimal2,
	pols_var_crf_inv_ti ais_decimal2
)
AS

BEGIN

		insert into @Table_Var(
								policy_situation_id,
								pols_var_all_inv_tf,
								pols_var_all_inv_taxes, 
								pols_var_all_inv_ti,
								pols_var_fee_inv_tf, 
								pols_var_fee_inv_taxes, 
								pols_var_fee_inv_ti,
								pols_var_per_inv_tf, 
								pols_var_per_inv_taxes, 
								pols_var_per_inv_ti,
								pols_var_all_inv_commissions_tf,
								pols_var_all_inv_commissions_taxes,
								pols_var_all_inv_commissions_ti,
								
								pols_var_bpc_inv_tf,
								pols_var_bpf_inv_tf,
								pols_var_brc_inv_tf,
								pols_var_brf_inv_tf,
								pols_var_cpf_inv_tf,
								pols_var_crf_inv_tf,
							
								pols_var_bpc_inv_taxes,
								pols_var_bpf_inv_taxes,
								pols_var_brc_inv_taxes,
								pols_var_brf_inv_taxes,
								pols_var_cpf_inv_taxes,
								pols_var_crf_inv_taxes,
							

								pols_var_bpc_inv_ti,
								pols_var_bpf_inv_ti,
								pols_var_brc_inv_ti,
								pols_var_brf_inv_ti,
								pols_var_cpf_inv_ti,
								pols_var_crf_inv_ti)
		SELECT	policy_situation_id,
				pols_var_all_inv_tf = sum(tf),
				pols_var_all_inv_taxes = sum(taxes), 
				pols_var_all_inv_ti = sum(ti),
				pols_var_fee_inv_tf = sum(CASE WHEN nmpvt_peril = 0 THEN tf ELSE 0 end), 
				pols_var_fee_inv_taxes = sum(CASE WHEN nmpvt_peril = 0 THEN taxes ELSE 0 end), 
				pols_var_fee_inv_ti = sum(CASE WHEN nmpvt_peril = 0 THEN ti ELSE 0 end),
				pols_var_per_inv_tf = sum(CASE WHEN nmpvt_peril = 1 THEN tf ELSE 0 end), 
				pols_var_per_inv_taxes = sum(CASE WHEN nmpvt_peril = 1 THEN taxes ELSE 0 end), 
				pols_var_per_inv_ti = sum(CASE WHEN nmpvt_peril = 1 THEN ti ELSE 0 end),
				pols_var_all_inv_commissions_tf = sum(cions_tf),
				pols_var_all_inv_commissions_taxes = sum(cions_taxes),
				pols_var_all_inv_commissions_ti = sum(cions_ti),

				pols_var_bpc_inv_tf = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_cost = 1 THEN tf ELSE 0 end),
				pols_var_bpf_inv_tf = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_fee = 1 THEN tf ELSE 0 end),
				pols_var_brc_inv_tf = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_cost = 1 THEN tf ELSE 0 end),
				pols_var_brf_inv_tf = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_fee = 1 THEN tf ELSE 0 end),
				pols_var_cpf_inv_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_inv_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_inv_taxes = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_cost = 1 THEN taxes ELSE 0 end),
				pols_var_bpf_inv_taxes = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_fee = 1 THEN taxes ELSE 0 end),
				pols_var_brc_inv_taxes = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_cost = 1 THEN taxes ELSE 0 end),
				pols_var_brf_inv_taxes = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_fee = 1 THEN taxes ELSE 0 end),
				pols_var_cpf_inv_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_inv_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_inv_ti = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_cost = 1 THEN ti ELSE 0 end),
				pols_var_bpf_inv_ti = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_fee = 1 THEN ti ELSE 0 end),
				pols_var_brc_inv_ti = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_cost = 1 THEN ti ELSE 0 end),
				pols_var_brf_inv_ti = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_fee = 1 THEN ti ELSE 0 end),
				pols_var_cpf_inv_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_inv_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), cions_tf = sum(cions_tf), cions_taxes = sum(cions_taxes), cions_ti = sum(cions_ti)
			from	(
					select	policy_situation_id = invpolv_policy_situation_id, variable_id = invpolv_variable_id, tf = invpolv_tf, taxes = invpolv_taxes, ti = invpolv_ti, cions_tf = invpolv_commission_tf, cions_taxes = invpolv_commission_taxes, cions_ti = invpolv_commission_ti
					FROM acc_invoices_policies_variables aipv
						INNER JOIN @policies_situations_table pst ON pst.id = aipv.invpolv_policy_situation_id
						INNER JOIN acc_invoices ai ON ai.inv_invoice_id = aipv.invpolv_invoice_id AND ai.inv_operation_code__last <> 'AN'
				
					union all

					select	policy_situation_id = rrs.riss_policy_situation_id, variable_id = invrisv_variable_id, tf = invrisv_tf, taxes = invrisv_taxes, ti = invrisv_ti, cions_tf = invrisv_commission_tf, cions_taxes = invrisv_commission_taxes, cions_ti = invrisv_commission_ti
					FROM acc_invoices_risks_variables airv
						INNER JOIN ris_risks_situations rrs ON rrs.riss_risk_situation_id = airv.invrisv_risk_situation_id
						INNER JOIN @policies_situations_table pst ON pst.id = rrs.riss_policy_situation_id
						INNER JOIN acc_invoices ai ON ai.inv_invoice_id = airv.invrisv_invoice_id AND ai.inv_operation_code__last <> 'AN'

					) 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_active_invoices_totals( @policies_situations_table)

*/

END


GO
