SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


-- =========================================================================================
-- Author:		Laba Catalin
-- Create date: 14/01/2011
-- Description:	Returns the products variables at a specific date. To obtain all fields you must join with table 'pr_products_variables' on prdv_product_code, prdv_variable_code and prdv_date_setup
-- =========================================================================================
CREATE function [dbo].[f_risks_list]
(
	@policy_id int,
	@risk_number ais_code_long = '%',
	@risk_id int = null,
	@risk_situation_id int = null,
	@movement_date date = null,
	@nature_code ais_code_short = '%',
	@return_obsolete bit = 0
) 
returns @situations table 
						(
							risk_id int,
							risk_situation_id int,
							movement_code ais_code_short,
							movement_date date,
							risk_last_situation_id int 
						)
as 
begin
	
	declare @risk_number_ok bit -- folosit pentru a vedea daca un risk_number e gasit in cazul in care este pasat. e folosit pentru optimizarea search-ului, nu se mai cauta, join, etc daca nu e gasit

	-- prepare @policy_id
	set @policy_id = isnull(@policy_id,0)
	
	--prepare nature_code
	set @nature_code = isnull(nullif(@nature_code, ''), '%')
	
	--prepare risk number
	set @risk_number = isnull(nullif(@risk_number, ''), '%')
	
	set @risk_number_ok = 1
	
	-- search risk_id if risk_number is not null, it's possible to have several numbers (on different situations) for each risk
	-- it is not allowed to have the same risk number on two differents flotes
	if isnull(@risk_id,0) < 1 and @risk_number != '%'
	begin
		select top 1 @risk_id = riss_risk_id 
		from ris_risks_situations 
		where	riss_policy_id = @policy_id 
				and riss_number = @risk_number
				
		if isnull(@risk_id,0) < 1
			set @risk_number_ok = 0
	end
	
	--prepare risk id
	select	@risk_id = isnull(@risk_id,0),
			@risk_situation_id = ISNULL(@risk_situation_id, 0)
	
	
	--search risks
	if @risk_number_ok = 1
	begin	

		insert into @situations(
					risk_id,		risk_situation_id,		movement_code,		movement_date,		risk_last_situation_id )
		select		riss_risk_id,	riss_risk_situation_id, riss_movement_code, riss_movement_date,	ris_last_risk_situation_id
		from ris_risks_situations a
			inner join ris_risks r on a.riss_risk_id = r.ris_risk_id
		where		( @risk_id = 0 or riss_risk_id = @risk_id)
				and ( @risk_situation_id = 0 or riss_risk_situation_id = @risk_situation_id)
				and ( @policy_id = 0 or riss_policy_id = @policy_id)
				and ( @nature_code = '%' or riss_nature_code = @nature_code)
				and ( @return_obsolete = 1 or a.riss_obsolete = 0)
				and riss_risk_situation_id =	case
												when @risk_situation_id > 0 then @risk_situation_id
												else 
														(
														select max(riss_risk_situation_id)
														from ris_risks_situations m
														where	a.riss_risk_id = m.riss_risk_id
														and 	1 = case
																		when @movement_date is null then 1 
																		when @movement_date >= m.riss_movement_date
																			 and m.riss_retire_date is null 
																			 and not exists	(	
																								select 1
																								from  ris_risks_situations t
																								where	m.riss_risk_id = t.riss_risk_id
																										and t.riss_movement_date > m.riss_movement_date
																										and t.riss_retire_date <= @movement_date	
																							) then 1	
																		else 0  
																	end
														)
												end
											
	end
	
	return;
	
end













GO
