SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_policy_product_family_params]
	@family_code				ais_code_short,
	@formula_code				ais_code_short = '%',
	@product_code				ais_code_short = '%',
	@branch_id					int	= 0, -- the branch
	@user_code					ais_code_medium = '',		-- id de l'utilisateur connecté
	@introducer_id				int,				-- id de l'apporteur attaché à l'utilisateur connecté (ou 0)
	@except_check_product_code	ais_code_short = ''		/*	DGE 21/01/2017 

														On peut avoir les cas suivants:
														1) affaire nouvelle, devis: on veut obtenir uniquement tous les produits en cours. 
																On passe @product_code = '%' and  @except_check_product_code = null/'' 
																La requête renvoie tous les produits en période de validité.

														2) avenant - on veut un certain produit, même s'il n'est plus en période de validité. 
																On passe son code, donc @product_code = @except_check_product_code = <<prd>>.
																
														3) reprise de devis: on veut obtenir le produit en cours (même s'il n'est plus en période de validité) et les autres produits de la famille, 
																mais seulement en période de validité.
																On passe @product_code = '%', @except_check_product_code = <<prd>>.
														*/
AS
	set nocount on;
	
	if isnull(@family_code,'%') = '%' and isnull(@product_code,'%') <> '%'
	begin
		select @family_code = prd_family_code
		from pr_products 
		where prd_product_code = @product_code
	end
	
	-- the product families
	select pf.*
	from pr_families pf 
	where pf.pf_family_code = @family_code
	
	-- the variables
	select pfv.*, nmpv.*, nmpvt.*
	from pr_families_variables pfv
		inner join nm_products_variables nmpv on nmpv.nmpv_variable_code = pfv.pfv_variable_code
		inner join nm_products_variables_types nmpvt on nmpv.nmpv_type_code = nmpvt.nmpvt_type_code
	where	pfv.pfv_family_code = @family_code 
	
	
	-- the formulas
	select pff.*
	from pr_families_formulas pff
	where	pff.pff_family_code = @family_code 
			and (@formula_code = '%' or pff.pff_formula_code = @formula_code)
	
	-- the formulas variables
	select pffv.*, pfv.*, nmpv.*,nmpvt.*
	from pr_families_formulas_variables pffv
		inner join pr_families_variables pfv on pfv.pfv_family_code = pffv.pffv_family_code
												and pfv.pfv_variable_code = pffv.pffv_variable_code
		inner join nm_products_variables nmpv on nmpv.nmpv_variable_code = pffv.pffv_variable_code
		inner join nm_products_variables_types nmpvt on nmpv.nmpv_type_code = nmpvt.nmpvt_type_code
	where	pffv.pffv_family_code = @family_code 
			and (@formula_code = '%' or pffv.pffv_formula_code = @formula_code)
	

	-- the products
	
	DECLARE @zero AIS_DECIMAL2

	declare @products_table TABLE(product_code ais_code_short not null)
	



	declare @date_ref date


	select @date_ref =	case
						when len(isnull(@except_check_product_code, '')) = 0 then GETDATE()		-- filtrer uniquement les produits valides
						else null																-- la fonction f_pr_products renvoie tous les produits, sans filtre
						end																		
			

	insert into @products_table (product_code)
	select	product_code
	from	dbo.f_pr_products(@branch_id, @user_code, @introducer_id, '%', @date_ref)
			inner join pr_products prd on prd_product_code = product_code
	where	(@product_code = '%' or prd.prd_product_code = @product_code)
	and		prd.prd_family_code = @family_code

	and		1 =		case
					when len(isnull(@except_check_product_code, '')) = 0 then 1					
					when prd.prd_product_code = @except_check_product_code then 1
					-- si le produit en cours est en période de validité
					when exists ( select 1 from dbo.f_pr_products(@branch_id, @user_code, @introducer_id, '%', GETDATE()) where product_code = prd_product_code ) then 1
					else 0
					end

	

	
	select	prd.*, prd_ann_tf = @zero, prd_ann_taxes = @zero, prd_ann_ti = @zero,
			prdb_monthly_payment_yesno_code = isnull(ppb.prdb_monthly_payment_yesno_code, prd.prd_monthly_payment_yesno_code)
	from pr_products prd
		inner join @products_table t on prd.prd_product_code = t.product_code
		left join pr_products_branches ppb on		ppb.prdb_product_code = prd.prd_product_code
												and ppb.prdb_branch_id = @branch_id
	
	--products variables
	select ppv.*
	from pr_products_variables ppv
		inner join @products_table t on ppv.prdv_product_code = t.product_code
	

	select doc.*
	from pr_products_documents doc
		inner join @products_table t on doc.prdd_product_code = t.product_code
	where (@branch_id = 0 or doc.prdd_branch_id = @branch_id)
	
	-- apporteurs
	select	i.*, v.*
	from	vw_act_introducers v
			inner join pr_products_introducers i on act_actor_id = prdint_introducer_id
			inner join @products_table t on i.prdint_product_code = t.product_code
	where		i.prdint_branch_id = @branch_id
			and (@introducer_id = 0 or i.prdint_introducer_id = @introducer_id)	


	-- the policy renew_types
	select * 
	from nm_pol_renew_types
	order by nmprt_order asc
	
	-- the policy renew split
	select * 
	from nm_pol_renew_types_split
	order by nmprts_renew_type_code asc, nmprts_order asc


	select * from act_branches where br_actor_id = @branch_id





	












GO
