SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_renewal]
	@policies_situations_ids type_table_id readonly,
	@product_code ais_code_short,
	@policy_number	ais_code_long,
	@renewal_debit int
	
as

set nocount on;
-- @renewal_debit = year + month

declare @bonus_management BIT
declare @family_code ais_code_short

select	@family_code = pf.pf_family_code,
		@bonus_management = pf.pf_bonus
from pr_products pp
	inner join pr_families pf on pp.prd_family_code = pf.pf_family_code
where pp.prd_product_code = @product_code

declare @debit_month int

set @debit_month = convert(int, substring(convert(varchar,@renewal_debit),5,2))

-- selecteaza datele necesare pentru terme, contracte TACIT si active
set @policy_number = isnull(nullif(@policy_number,''),'%')

--tabela de contracte
declare @policies_situations table(policy_situation_id int primary key, policy_id int, policy_next_renewal_date date, client_id int, is_principal_renewal bit, policy_is_flotte bit, pf_bonus bit, pol_start_date DATE, pf_type_pol_code ais_code_short, branch_id int)

--tabela de riscuri
declare @risks_situations table(risk_situation_id int primary key, risk_id int, is_principal_renewal bit)

--cautare contracte
insert into @policies_situations(	policy_situation_id,		policy_id,		policy_next_renewal_date,	client_id,		is_principal_renewal,
									policy_is_flotte,
									pf_bonus,					pol_start_date,	pf_type_pol_code,			branch_id)
select								pols_policy_situation_id,	pols_policy_id,	pols_next_renewal_date,		pol_client_id,	case 
																																when convert(int,substring(pols_term_date,4,2)) = @debit_month then 1 
																																else 0
																															end,	
									case 
										when pf_type_pol_code = 'F' then convert(bit,1)
										else convert(bit,0) -- 'S'
									end,
									pf_bonus,					pol_start_date,	pf_type_pol_code,			pol_branch_id
from pol_policies_situations pps
    inner join @policies_situations_ids psi on psi.id = pps.pols_policy_situation_id
	inner join pol_policies on pol_policy_id = pols_policy_id 
	inner join pr_products on pol_product_code = prd_product_code
	inner join pr_families on prd_family_code = pf_family_code
		
-- cautare riscuri
insert into @risks_situations(	risk_situation_id,			risk_id,		is_principal_renewal )
select							riss_risk_situation_id,		riss_risk_id,	is_principal_renewal
from ris_risks_situations a
	inner join ris_risks on riss_risk_id = ris_risk_id and riss_risk_situation_id = ris_last_risk_situation_id and riss_obsolete = 0
	inner join @policies_situations on riss_policy_id = policy_id
	inner join pol_policies_situations pps on pps.pols_policy_situation_id = a.riss_policy_situation_id 
	inner join pol_mvts on pmvt_mvt_code = pps.pols_mvt_code and pmvt_type_code in ('ACT','A_L') -- MVT ACTIF si ACTIF_AND_LOCKED - on prends seulement les risques qui sont sur les situations ACTIF (pas les devis, les résil,...)
where	  riss_retire_date is null -- la terme se iau totdeauna ultimele situatii ale riscurilor, nu se permite creearea unor situatii care sa depaseasca urmatorul terme
	   					
--selectie clienti
select a.*
from vw_act_clients a
	inner join (	select distinct client_id
					from @policies_situations ) as t on a.cli_actor_id = t.client_id
											

-- selectie contracte
select	p.*,
		pols_next_renewal_date = policy_next_renewal_date,
		ps.pf_type_pol_code
from pol_policies p
	inner join @policies_situations ps on pol_policy_id = policy_id

-- policies accounts for mothly payments
select ppa.*
from pol_policies_accounts ppa
	inner join @policies_situations ps on ps.policy_id = ppa.polacc_policy_id
		
-- selectie situatii contracte
select	pps.*, 
		pol_start_date, pf_bonus, pf_type_pol_code,
		usgu_bm_code = isnull(gub.usgub_bm_code, gu.usgu_bm_code),
		virvf_is_principal_renewal = is_principal_renewal
from pol_policies_situations pps
	inner join @policies_situations tps on pols_policy_situation_id = policy_situation_id
	left join pol_cpl pcpl on pcpl.pcpl_policy_situation_id = pps.pols_policy_situation_id
	left join grpusg_usages gu on gu.usgu_usage_id = pcpl.pcpl_usage_id
	left join grpusg_usages_branches gub on		gu.usgu_usage_id = gub.usgub_usage_id
											and gub.usgub_branch_id = tps.branch_id 
	
-- selectie complementi contracte
select ps.*
from pol_cpl ps
	inner join @policies_situations on pcpl_policy_situation_id = policy_situation_id 
	
--selectie variabile contracte, pentru terme-urile secundare recuperam tot, nu se mai calculeaza nimic
select v.*, p.prdv_product_code, npv.nmpv_variable_code, p.prdv_variable_date_setup
from pol_variables v
	inner join @policies_situations on polv_policy_situation_id = policy_situation_id
	inner join pr_products_variables p on v.polv_variable_id = p.prdv_variable_id
	inner join nm_products_variables npv on p.prdv_variable_code = npv.nmpv_variable_code
where is_principal_renewal = 0

--selectie variabile contracte, pentru terme-urile principale ne intereseaza doar codurile, restul se calculeaza
select	v.*, 
		npv.nmpv_variable_code, 
		p.prdv_variable_date_setup
from pol_variables v
	inner join @policies_situations on polv_policy_situation_id = policy_situation_id
	inner join pr_products_variables p on v.polv_variable_id = p.prdv_variable_id
	inner join nm_products_variables npv on p.prdv_variable_code = npv.nmpv_variable_code
where is_principal_renewal = 1
	
--selectie taxe prime variabile contracte, pentru terme-urile secundare recuperam tot, nu se mai calculeaza nimic
select	t.*, p.prdv_product_code, npv.nmpv_variable_code, p.prdv_variable_date_setup, a.acctax_tax_code, a.acctax_tax_date_setup
from pol_variables_taxes t 
	inner join @policies_situations on polvt_policy_situation_id = policy_situation_id
	inner join pr_products_variables p on t.polvt_variable_id = p.prdv_variable_id
	inner join nm_products_variables npv on p.prdv_variable_code = npv.nmpv_variable_code
	inner join acc_taxes a on t.polvt_tax_id = a.acctax_tax_id
where is_principal_renewal = 0

--selectie taxe variabile contracte, pentru terme-urile principale nu luam nimic, se calculeaza in cod
	
-- selectie aportori contracte
select i.*
from pol_policies_situations_introducers i
	inner join @policies_situations on polsi_policy_situation_id = policy_situation_id 
	

-- select riscuri	
select r.*
from ris_risks r
	inner join @risks_situations on risk_id = ris_risk_id 
	
-- selectie situatii riscuri
select	rrs.*,
		usgu_bm_code = isnull(gub.usgub_bm_code, gu.usgu_bm_code),
		pf_bonus,
		pf_type_pol_code
from ris_risks_situations rrs
	inner join @risks_situations on risk_situation_id = riss_risk_situation_id
	inner join pol_policies pp on pp.pol_policy_id = rrs.riss_policy_id
	inner join pr_products on pol_product_code = prd_product_code
	inner join pr_families on prd_family_code = pf_family_code
	left join ris_cpl rcpl on rcpl.rcpl_risk_situation_id = rrs.riss_risk_situation_id
	left join grpusg_usages gu on gu.usgu_usage_id = rcpl.rcpl_usage_id
	left join grpusg_usages_branches gub on		gu.usgu_usage_id = gub.usgub_usage_id
											and gub.usgub_branch_id = pp.pol_branch_id
	
-- selectie complementi riscuri
select rs.*
from ris_cpl rs
	inner join @risks_situations on risk_situation_id = rcpl_risk_situation_id
		
-- selectie variabile riscuri, pentru terme-urile secundare recuperam tot, nu se mai calculeaza nimic
select rv.*, p.prdv_product_code, npv.nmpv_variable_code, p.prdv_variable_date_setup
from ris_variables rv
	inner join @risks_situations on risk_situation_id = risv_risk_situation_id
	inner join pr_products_variables p on rv.risv_variable_id = p.prdv_variable_id
	inner join nm_products_variables npv on p.prdv_variable_code = npv.nmpv_variable_code
where is_principal_renewal = 0

-- selectie variabile riscuri, pentru terme-urile principale ne intereseaza doar codurile, restul se calculeaza
select	rv.*,
		npv.nmpv_variable_code,
		p.prdv_variable_date_setup
from ris_variables rv
	inner join @risks_situations on risk_situation_id = risv_risk_situation_id
	inner join pr_products_variables p on rv.risv_variable_id = p.prdv_variable_id
	inner join nm_products_variables npv on p.prdv_variable_code = npv.nmpv_variable_code
where is_principal_renewal = 1
	 
-- selectie taxe prime variabile riscuri
select rvt.*, p.prdv_product_code, npv.nmpv_variable_code, p.prdv_variable_date_setup, a.acctax_tax_code, a.acctax_tax_date_setup
from ris_variables_taxes rvt
	inner join @risks_situations on risk_situation_id = risvt_risk_situation_id
	inner join pr_products_variables p on rvt.risvt_variable_id = p.prdv_variable_id
	inner join nm_products_variables npv on p.prdv_variable_code = npv.nmpv_variable_code
	inner join acc_taxes a on rvt.risvt_tax_id = a.acctax_tax_id
where is_principal_renewal = 0

--selectie taxe variabile riscuri, pentru terme-urile principale nu luam nimic, se calculeaza in cod

-- selectie classier, zonier, usage - e mai rapid sa se faca aici in cazul terme-ului
declare @table_risks_groups type_risks_groups

insert INTO @table_risks_groups(	
		rcpl_risk_situation_id,	prd_product_code,
		rcpl_class_id,			rcpl_zone_id,	rcpl_usage_id)
select	riss_risk_situation_id,	prd_product_code,
		rcpl_class_id,			rcpl_zone_id,	rcpl_usage_id
from @risks_situations t
	inner join ris_risks_situations on t.risk_situation_id = riss_risk_situation_id
	inner join pol_policies_situations on riss_policy_situation_id = pols_policy_situation_id
	inner join pol_policies on pol_policy_id = pols_policy_id
	inner join pr_products on pol_product_code = prd_product_code
	left join ris_cpl rs on riss_risk_situation_id = rcpl_risk_situation_id

DECLARE @branch_id int

--only one branch for all the policies
set @branch_id = (select top 1 branch_id FROM @policies_situations ps)
	
exec p_ris_risks_situations_groups @table_risks_groups = @table_risks_groups, @branch_id = @branch_id 

if @bonus_management = 1 
begin

	-- compter les sinistres pour la révalorisation du bonus des risques lors du terme principal
	declare @risks_claims table(cl_claim_id int, cl_risk_id int, cl_risk_situation_id int, vir_item_12 int, vir_item_24 int, vir_item_36 int, vir_item_100 int)

	insert into @risks_claims (
			cl_claim_id,
			cl_risk_id,
			cl_risk_situation_id,
			vir_item_12,
			vir_item_24,
			vir_item_36,
			vir_item_100
								)
	select	cc.cl_claim_id,
			rrs.riss_risk_id,
			cc.cl_risk_situation_id,
			vir_item_12 =	case
								when		cc.cl_occurence_date between dateadd(day,+1,dateadd(month,-14,ps.policy_next_renewal_date)) 
										and dateadd(month,-2,ps.policy_next_renewal_date) then 1
								else 0
							end,
			vir_item_24 =	case
								when		cc.cl_occurence_date between dateadd(day,+1,dateadd(month,-26,ps.policy_next_renewal_date)) 
										and dateadd(month,-14,ps.policy_next_renewal_date) then 1
								else 0
							end,
			vir_item_36 =	case
								when		cc.cl_occurence_date between dateadd(day,+1,dateadd(month,-38,ps.policy_next_renewal_date)) 
										and dateadd(month,-26,ps.policy_next_renewal_date) then 1
								else 0
							end,
			vir_item_100 =	case
								when		cc.cl_occurence_date between dateadd(day,+1,dateadd(month,-14,ps.policy_next_renewal_date))
										and dateadd(month,-2,ps.policy_next_renewal_date) 
										and cc.cl_responsability_percent = 100 then 1
								else 0
							end

	from claim_claims cc
		inner join ris_risks_situations rrs on cc.cl_risk_situation_id = rrs.riss_risk_situation_id
		inner join @risks_situations rs on rs.risk_id = rrs.riss_risk_id
		inner join @policies_situations ps on		ps.policy_id = rrs.riss_policy_id
												and ps.is_principal_renewal = 1 -- on tient compte seulement des termes principaux
												and ps.pf_bonus = 1
   
	where ps.policy_is_flotte = 0

	--renvoie des sinistres risques
	select *
	from @risks_claims

	--renvoie du nombre total des sinistres par risque
	select	cl_risk_id,
			vir_total_item_12 = sum(vir_item_12),
			vir_total_item_24 = sum(vir_item_24),
			vir_total_item_36 = sum(vir_item_36),
			vir_total_item_100 = sum(vir_item_100)
	from @risks_claims rc
	group by cl_risk_id


	-- compter les sinistres pour la révalorisation du bonus des contrats flotte lors du terme principal
	declare @policies_claims table(cl_claim_id int, cl_policy_id int, cl_policy_situation_id int, vir_item_12 int, vir_item_24 int, vir_item_36 int, vir_item_100 int)

	insert into @policies_claims (
			cl_claim_id,
			cl_policy_id,
			cl_policy_situation_id,
			vir_item_12,
			vir_item_24,
			vir_item_36,
			vir_item_100
								)
	select	cc.cl_claim_id,
			policy_id,
			ps.policy_situation_id,
			vir_item_12 =	case
								when		cc.cl_occurence_date between dateadd(day,+1,dateadd(month,-14,ps.policy_next_renewal_date)) 
										and dateadd(month,-2,ps.policy_next_renewal_date) then 1
								else 0
							end,
			vir_item_24 =	case
								when		cc.cl_occurence_date between dateadd(day,+1,dateadd(month,-26,ps.policy_next_renewal_date)) 
										and dateadd(month,-14,ps.policy_next_renewal_date) then 1
								else 0
							end,
			vir_item_36 =	case
								when		cc.cl_occurence_date between dateadd(day,+1,dateadd(month,-38,ps.policy_next_renewal_date)) 
										and dateadd(month,-26,ps.policy_next_renewal_date) then 1
								else 0
							end,
			vir_item_100 =	case
								when		cc.cl_occurence_date between dateadd(day,+1,dateadd(month,-14,ps.policy_next_renewal_date))
										and dateadd(month,-2,ps.policy_next_renewal_date) 
										and cc.cl_responsability_percent = 100 then 1
								else 0
							end

	from claim_claims cc
		inner join @policies_situations ps on		cc.cl_policy_situation_id = ps.policy_situation_id
												and ps.is_principal_renewal = 1 -- on tient compte seulement des termes principaux
												and ps.pf_bonus = 1
	where ps.policy_is_flotte = 1

	--renvoie des sinistres contrats
	select *
	from @policies_claims

	--renvoie du nombre total des sinistres par contrat
	select	cl_policy_id,
			vir_total_item_12 = sum(vir_item_12),
			vir_total_item_24 = sum(vir_item_24),
			vir_total_item_36 = sum(vir_item_36),
			vir_total_item_100 = sum(vir_item_100)
	from @policies_claims pc
	group by cl_policy_id

end

select	pf.*
from pr_families pf
where pf_family_code = @family_code

















GO
