SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_invoice_unit_rate_data]
	@policy_situation_id int,
	@invoice_id	int
as
	set nocount on;

	DECLARE @inv_taxes_manual_input BIT = 0

	declare @branch_id int
	declare @policy_mvt_date DATE

	set @invoice_id = isnull(@invoice_id,0)

	IF @invoice_id > 0
	BEGIN
		SELECT @inv_taxes_manual_input = ai.inv_taxes_manual_input
		FROM acc_invoices ai
		WHERE ai.inv_invoice_id=@invoice_id
	END

	select	@branch_id = pp.pol_branch_id,
			@policy_mvt_date = pps.pols_mvt_date
	from pol_policies_situations pps 
		inner join pol_policies pp on pp.pol_policy_id = pps.pols_policy_id
	where pps.pols_policy_situation_id = @policy_situation_id

	--récuper la liste des risques pour la situation du contrat
	declare @risks_situations table(risk_situation_id int not null, policy_situation_id int not null, risk_number nvarchar(128) null)

	insert into @risks_situations (	risk_situation_id,				policy_situation_id,			risk_number)
	select							rrs.riss_risk_situation_id,		rrs.riss_policy_situation_id,	rrs.riss_number
	from ris_risks_situations rrs
	where rrs.riss_policy_situation_id = @policy_situation_id

	--récuperer les informations de la situation contrat
	select	pols_policy_situation_id, --primary key
			pols_policy_id,
			pols_mvt_date,
			pp.pol_client_id,
			pp.pol_currency_code,
			virvf_policy_number = pols_number,
			virvf_invoice_start_date = convert(date, null),
			virvf_invoice_end_date = convert(date, null),
			virvf_invoice_tf = convert(decimal(19,2), 0),
			virvf_invoice_taxes = convert(decimal(19,2), 0),
			virvf_invoice_taxes_not_included_in_net = convert(decimal(19,2), 0),
			virvf_invoice_ti = convert(decimal(19,2), 0),
			virvf_invoice_commission_tf = convert(decimal(19,2), 0),
			virvf_invoice_commission_taxes = convert(decimal(19,2), 0),
			virvf_invoice_commission_taxes_not_included_in_net = convert(decimal(19,2), 0),
			virvf_invoice_commission_ti = convert(decimal(19,2), 0),
			inv_taxes_manual_input = @inv_taxes_manual_input
	from pol_policies_situations pps
		inner join pol_policies pp on pp.pol_policy_id = pps.pols_policy_id
	where pps.pols_policy_situation_id = @policy_situation_id

	--récuperations des variables contrat
	select	polv_policy_situation_id, 
			virvf_parent_id = pv.polv_policy_situation_id, -- primary key
			virvf_variable_id = pv.polv_variable_id, -- primary key
			virvf_policy_or_risk_number = pols_number,
			virvf_risk_situation_id = convert(int, null),
			virvf_invoice_variable_tf = aipv.invpolv_tf ,
			virvf_invoice_variable_taxes = aipv.invpolv_taxes,
			virvf_invoice_variable_taxes_not_included_in_net = convert(decimal(19,2), 0),
			virvf_invoice_variable_ti = aipv.invpolv_ti,
			virvf_invoice_variable_commission_tf = aipv.invpolv_commission_tf,
			virvf_invoice_variable_commission_taxes = aipv.invpolv_commission_taxes,
			virvf_invoice_variable_commission_taxes_not_included_in_net = convert(decimal(19,2), 0),
			virvf_invoice_variable_commission_ti = aipv.invpolv_commission_ti,
			virvf_invoice_variable_commission_percent =		CASE 
																WHEN aipv.invpolv_invoice_id IS NOT NULL THEN aipv.invpolv_commission_percent
																ELSE 
																	CASE WHEN ppv.prdv_commission_fixed_value = 0 THEN ppv.prdv_commission_value
																	ELSE NULL
																	END
															END,
			nmpv_variable_code,
			nmpv_l1,
			nmpv_l2,
			nmpv_l3,
			ppv.prdv_splittable,
			ppv.prdv_commission_fixed_value,
			ppv.prdv_commission_value,
			
			prdvb_invoice_commission_tf_is_required = isnull(ppvb.prdvb_invoice_commission_tf_is_required, 0),
			prdvb_invoice_premium_tf_is_required = isnull(ppvb.prdvb_invoice_premium_tf_is_required,0),
			npvt.nmpvt_company,
			npvt.nmpvt_is_broker_cost,
			npvt.nmpvt_is_broker_fee,
			npvt.nmpvt_is_company_cost,
			npvt.nmpvt_is_company_fee,
			npvt.nmpvt_peril,
			npvt.nmpvt_policy,
			npvt.nmpvt_risk
	from pol_variables pv
		inner join pol_policies_situations pps on pps.pols_policy_situation_id = pv.polv_policy_situation_id
		inner join pol_policies pp on pp.pol_policy_id = pps.pols_policy_id
		inner join pr_products_variables ppv on ppv.prdv_variable_id = pv.polv_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
		inner join pr_products pprod on pprod.prd_product_code = ppv.prdv_product_code
		inner join pr_families_variables pfv on			pfv.pfv_family_code = pprod.prd_family_code
												 and	pfv.pfv_variable_code = ppv.prdv_variable_code 
												 and	isnull(pfv.pfv_variable_code__parent, '') = '' -- dans la quittance on ne doit pas mettre les variables secondaires
		left join pr_products_variables_branches ppvb on		ppvb.prdvb_variable_id = ppv.prdv_variable_id
															and ppvb.prdvb_branch_id = pp.pol_branch_id
		left join acc_invoices_policies_variables aipv on		aipv.invpolv_policy_situation_id = pv.polv_policy_situation_id
															and aipv.invpolv_variable_id = pv.polv_variable_id
															and aipv.invpolv_invoice_id = @invoice_id
	where		pv.polv_policy_situation_id = @policy_situation_id
			and	(@invoice_id = 0 or aipv.invpolv_invoice_id is not null) -- pour la copie d'une variable quittance précédente

	
	union all

	--récuperations des variables risques
	select	polv_policy_situation_id = rrs.policy_situation_id, 
			virvf_parent_id = -1 * rrs.risk_situation_id, -- primary key -- il est en négatif pour savoir que c'est un risque
			virvf_variable_id = rv.risv_variable_id, -- primary key
			virvf_policy_or_risk_number = risk_number,
			virvf_risk_situation_id = rrs.risk_situation_id,
			virvf_invoice_variable_tf = airv.invrisv_tf ,
			virvf_invoice_variable_taxes = airv.invrisv_taxes,
			virvf_invoice_variable_taxes_not_included_in_net = convert(decimal(19,2), 0),
			virvf_invoice_variable_ti = airv.invrisv_ti,
			virvf_invoice_variable_commission_tf = airv.invrisv_commission_tf,
			virvf_invoice_variable_commission_taxes = airv.invrisv_commission_taxes,
			virvf_invoice_variable_commission_taxes_not_included_in_net = convert(decimal(19,2), 0),
			virvf_invoice_variable_commission_ti = airv.invrisv_commission_ti,
			virvf_invoice_variable_commission_percent = CASE 
																WHEN airv.invrisv_risk_situation_id IS NOT NULL THEN airv.invrisv_commission_percent
																ELSE 
																	CASE WHEN ppv.prdv_commission_fixed_value = 0 THEN ppv.prdv_commission_value
																	ELSE NULL
																	END
															END,
			nmpv_variable_code,
			nmpv_l1,
			nmpv_l2,
			nmpv_l3,
			ppv.prdv_splittable,
			ppv.prdv_commission_fixed_value,
			ppv.prdv_commission_value,
			
			prdvb_invoice_commission_tf_is_required = isnull(ppvb.prdvb_invoice_commission_tf_is_required, 0),
			prdvb_invoice_premium_tf_is_required = isnull(ppvb.prdvb_invoice_premium_tf_is_required,0),
			npvt.nmpvt_company,
			npvt.nmpvt_is_broker_cost,
			npvt.nmpvt_is_broker_fee,
			npvt.nmpvt_is_company_cost,
			npvt.nmpvt_is_company_fee,
			npvt.nmpvt_peril,
			npvt.nmpvt_policy,
			npvt.nmpvt_risk
	from ris_variables rv
		inner join @risks_situations rrs on rrs.risk_situation_id = rv.risv_risk_situation_id
		inner join pr_products_variables ppv on ppv.prdv_variable_id = rv.risv_variable_id

		inner join pol_policies_situations pps on pps.pols_policy_situation_id = rrs.policy_situation_id
		inner join pol_policies pp on pp.pol_policy_id = pps.pols_policy_id
		left join pr_products_variables_branches ppvb on		ppvb.prdvb_variable_id = ppv.prdv_variable_id
															and ppvb.prdvb_branch_id = pp.pol_branch_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
		inner join pr_products pprod on pprod.prd_product_code = ppv.prdv_product_code
		inner join pr_families_variables pfv on			pfv.pfv_family_code = pprod.prd_family_code
												 and	pfv.pfv_variable_code = ppv.prdv_variable_code 
												 and	isnull(pfv.pfv_variable_code__parent, '') = '' -- dans la quittance on ne doit pas mettre les variables secondaires
		left join acc_invoices_risks_variables airv on		airv.invrisv_risk_situation_id = rrs.risk_situation_id
														and airv.invrisv_variable_id = rv.risv_variable_id
														and airv.invrisv_invoice_id = @invoice_id
	where	(@invoice_id = 0 or airv.invrisv_invoice_id is not null) -- pour la copie d'une variable quittance précédente

	

	
	--récuperations des taxes sur les variables contrat
	select	aipvt.invpolvt_invoice_id, 
			virvf_parent_id = pvt.polvt_policy_situation_id, -- primary key
			virvf_variable_id = pvt.polvt_variable_id, -- primary key
			virvf_tax_id  = pvt.polvt_tax_id, -- primary key
			virvf_risk_situation_id = convert(int, null),			  
			virvf_tax_value =  case 
									when aipvt.invpolvt_invoice_id is not null then aipvt.invpolvt_tax_value 
									else
										case
											when  at.acctax_fixed_value = 1 then at.acctax_tax_value
											else convert(decimal(19,2), 0)
										end
								end,
			virvf_tax_not_included_in_net_value = convert(decimal(19,2), 0),
			nmpv_variable_code,
			nmpv_l1,
			nmpv_l2,
			nmpv_l3,
			nmtax_tax_code,
			nmtax_l1,
			nmtax_l2,
			nmtax_l3,
			at.acctax_fixed_value,
			at.acctax_net_included,
			at.acctax_tax_value
	from pol_variables_taxes pvt
		inner join pr_products_variables ppv on ppv.prdv_variable_id = pvt.polvt_variable_id
		inner join nm_products_variables npv on npv.nmpv_variable_code = ppv.prdv_variable_code
		inner join acc_taxes at on at.acctax_tax_id = pvt.polvt_tax_id
		inner join nm_taxes nt on nt.nmtax_tax_code = at.acctax_tax_code
		inner join pr_products pprod on pprod.prd_product_code = ppv.prdv_product_code
		inner join pr_families_variables pfv on			pfv.pfv_family_code = pprod.prd_family_code
												 and	pfv.pfv_variable_code = ppv.prdv_variable_code 
												 and	isnull(pfv.pfv_variable_code__parent, '') = '' -- dans la quittance on ne doit pas mettre les variables secondaires
		
		left join acc_invoices_policies_variables_taxes aipvt on		aipvt.invpolvt_policy_situation_id = pvt.polvt_policy_situation_id
																	and aipvt.invpolvt_variable_id = pvt.polvt_variable_id
																	and aipvt.invpolvt_tax_id = pvt.polvt_tax_id
																	and	aipvt.invpolvt_invoice_id = @invoice_id
	where		pvt.polvt_policy_situation_id = @policy_situation_id
			and	(@invoice_id = 0 or aipvt.invpolvt_invoice_id is not null) -- pour la copie d'une variable quittance précédente

	union all 

	--récuperations des taxes sur les variables risques
	select	invpolvt_invoice_id = airvt.invrisvt_invoice_id, 
			virvf_parent_id = - 1 * rvt.risvt_risk_situation_id, -- primary key -- il est en négatif pour savoir que c'est un risque
			virvf_variable_id = rvt.risvt_variable_id, -- primary key
			virvf_tax_id = rvt.risvt_tax_id,  -- primary key
			virvf_risk_situation_id = rvt.risvt_risk_situation_id,
			virvf_tax_value = case 
									when airvt.invrisvt_invoice_id is not null then airvt.invrisvt_tax_value 
									else
										case
											when  at.acctax_fixed_value = 1 then at.acctax_tax_value
											else convert(decimal(19,2), 0)
										end
								end,
			virvf_tax_not_included_in_net_value = convert(decimal(19,2), 0),
			nmpv_variable_code,
			nmpv_l1,
			nmpv_l2,
			nmpv_l3,
			nmtax_tax_code,
			nmtax_l1,
			nmtax_l2,
			nmtax_l3,
			at.acctax_fixed_value,
			at.acctax_net_included,
			at.acctax_tax_value		
	from ris_variables_taxes rvt
		inner join @risks_situations rrs on rrs.risk_situation_id = rvt.risvt_risk_situation_id
		inner join pr_products_variables ppv on ppv.prdv_variable_id = rvt.risvt_variable_id
		inner join nm_products_variables npv on npv.nmpv_variable_code = ppv.prdv_variable_code
		inner join acc_taxes at on at.acctax_tax_id = rvt.risvt_tax_id
		inner join nm_taxes nt on nt.nmtax_tax_code = at.acctax_tax_code
		inner join pr_products pprod on pprod.prd_product_code = ppv.prdv_product_code
		inner join pr_families_variables pfv on			pfv.pfv_family_code = pprod.prd_family_code
												 and	pfv.pfv_variable_code = ppv.prdv_variable_code 
												 and	isnull(pfv.pfv_variable_code__parent, '') = '' -- dans la quittance on ne doit pas mettre les variables secondaires
		left join acc_invoices_risks_variables_taxes airvt on		airvt.invrisvt_risk_situation_id = rvt.risvt_risk_situation_id
																	and airvt.invrisvt_variable_id = rvt.risvt_variable_id
																	and airvt.invrisvt_tax_id = rvt.risvt_tax_id
																	and	airvt.invrisvt_invoice_id = @invoice_id
	where (@invoice_id = 0 or airvt.invrisvt_invoice_id is not null) -- pour la copie d'une variable quittance précédente


	
	declare @commissiontaxes table(tax_id int, tax_code ais_code_short)

	insert into @commissiontaxes(	tax_id,			tax_code)
	select							fatbd.tax_id,	fatbd.tax_code
	from f_acc_taxes_by_date(@policy_mvt_date) fatbd

	--récuperations des taxes sur les commissions des variables contrat
	select  aipvct.invpolvct_invoice_id, 
			virvf_parent_id = pv.polv_policy_situation_id, -- primary key
			virvf_variable_id = pv.polv_variable_id, -- primary key
			virvf_tax_id  = ct.tax_id, -- primary key
			virvf_risk_situation_id = convert(int, null),			  
			virvf_tax_value =	case 
									when aipvct.invpolvct_invoice_id is not null then aipvct.invpolvct_tax_value 
									else
										case
											when  at.acctax_fixed_value = 1 then at.acctax_tax_value
											else convert(decimal(19,2), 0)
										end
								end,
			virvf_tax_not_included_in_net_value = convert(decimal(19,2), 0),
			nmpv_variable_code,
			nmpv_l1,
			nmpv_l2,
			nmpv_l3,
			nmtax_tax_code,
			nmtax_l1,
			nmtax_l2
			nmtax_l3,
			at.acctax_fixed_value,
			at.acctax_net_included,
			at.acctax_tax_value,
			prdv_commission_fixed_value,
			prdv_commission_value
	from pol_variables pv
		inner join pr_products_variables ppv on ppv.prdv_variable_id = pv.polv_variable_id
		inner join nm_products_variables npv on npv.nmpv_variable_code = ppv.prdv_variable_code
		inner join pr_products_variables_commission_taxes ppvct on		ppvct.prdvct_variable_id = pv.polv_variable_id
																	and ppvct.prdvct_branch_id = @branch_id
		inner join @commissiontaxes ct on ct.tax_code = ppvct.prdvct_tax_code
		inner join acc_taxes at on at.acctax_tax_id = ct.tax_id
		inner join nm_taxes nt on nt.nmtax_tax_code = ppvct.prdvct_tax_code
		inner join pr_products pprod on pprod.prd_product_code = ppv.prdv_product_code
		inner join pr_families_variables pfv on			pfv.pfv_family_code = pprod.prd_family_code
												 and	pfv.pfv_variable_code = ppv.prdv_variable_code 
												 and	isnull(pfv.pfv_variable_code__parent, '') = '' -- dans la quittance on ne doit pas mettre les variables secondaires
		left join acc_invoices_policies_variables_commission_taxes aipvct on		aipvct.invpolvct_invoice_id = @invoice_id
																				and aipvct.invpolvct_policy_situation_id = pv.polv_policy_situation_id
																				and aipvct.invpolvct_variable_id = pv.polv_variable_id
																				and aipvct.invpolvct_tax_id = ct.tax_id
	where		pv.polv_policy_situation_id = @policy_situation_id 
			and	(@invoice_id = 0 or aipvct.invpolvct_invoice_id is not null) -- pour la copie d'une variable quittance précédente


	union all

	--récuperations des taxes sur les commissions des variables risques
	select  airvct.invrisvct_invoice_id, 
			virvf_parent_id = -1 * rv.risv_risk_situation_id, -- primary key
			virvf_variable_id = rv.risv_variable_id, -- primary key
			virvf_tax_id  = ct.tax_id, -- primary key
			virvf_risk_situation_id = rv.risv_risk_situation_id,	  
			virvf_tax_value =	case 
									when airvct.invrisvct_invoice_id is not null then airvct.invrisvct_tax_value 
									else
										case
											when  at.acctax_fixed_value = 1 then at.acctax_tax_value
											else convert(decimal(19,2), 0)
										end
								end,
			virvf_tax_not_included_in_net_value = convert(decimal(19,2), 0),
			nmpv_variable_code,
			nmpv_l1,
			nmpv_l2,
			nmpv_l3,
			nmtax_tax_code,
			nmtax_l1,
			nmtax_l2
			nmtax_l3,
			at.acctax_fixed_value,
			at.acctax_net_included,
			at.acctax_tax_value,
			prdv_commission_fixed_value,
			prdv_commission_value
	from ris_variables rv
		inner join @risks_situations rrs on rrs.risk_situation_id = rv.risv_risk_situation_id
		inner join pr_products_variables ppv on ppv.prdv_variable_id = rv.risv_variable_id
		inner join nm_products_variables npv on npv.nmpv_variable_code = ppv.prdv_variable_code
		inner join pr_products_variables_commission_taxes ppvct on		ppvct.prdvct_variable_id = rv.risv_variable_id
																	and ppvct.prdvct_branch_id = @branch_id
		inner join @commissiontaxes ct on ct.tax_code = ppvct.prdvct_tax_code
		inner join acc_taxes at on at.acctax_tax_id = ct.tax_id
		inner join nm_taxes nt on nt.nmtax_tax_code = ppvct.prdvct_tax_code
		inner join pr_products pprod on pprod.prd_product_code = ppv.prdv_product_code
		inner join pr_families_variables pfv on			pfv.pfv_family_code = pprod.prd_family_code
												 and	pfv.pfv_variable_code = ppv.prdv_variable_code 
												 and	isnull(pfv.pfv_variable_code__parent, '') = '' -- dans la quittance on ne doit pas mettre les variables secondaires
		left join acc_invoices_risks_variables_commission_taxes airvct on		airvct.invrisvct_invoice_id = @invoice_id
																		and airvct.invrisvct_risk_situation_id =  rrs.risk_situation_id
																		and airvct.invrisvct_variable_id = rv.risv_variable_id
																		and airvct.invrisvct_tax_id = ct.tax_id
	where (@invoice_id = 0 or airvct.invrisvct_invoice_id is not null) -- pour la copie d'une variable quittance précédente

GO
