SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_account_clients_by_client_id]
	@client_id INT = -1,							-- id du client
	/*
	opérations des quittances: an, cr, db, ec, rmb
	pour avoir la liste complète, voir
	select * from nm_invoices_operations
	*/
	@show_invoices_an bit = 0,
	@show_invoices_cr bit = 0,
	@show_invoices_db_pos bit = 0,					-- afficher les quittances DB positives
	@show_invoices_db_neg bit = 0,					-- afficher les quittances DB négatives (ristournes)
	@show_invoices_ec bit = 0,
	@show_invoices_rmb bit = 0,
	@show_invoices_inf bit = 0,
	@show_invoices_awr bit = 0,
	@show_invoices_s bit = 0,
	
	/* autres lignes */	
	@show_a_not_taken bit = 0,						-- afficher les acomptes non reprises
	@show_a_taken bit = 0,							-- afficher les acomptes reprises
		
	@show_t bit = 0,								-- afficher les lignes de type "transaction"

	@show_cp_is bit = 0,							-- afficher les lignes de type "règlement sinistre" en code opération IS (non validées)
	@show_cp_p bit = 0,								-- afficher les lignes de type "règlement sinistre" en code opération P (payées)

	@show_cr_is bit = 0,							-- afficher les lignes de type "encaissement recours" en code opération IS (non validées)
	@show_cr_p bit = 0,								-- afficher les lignes de type "encaissement recours" en code opération P (payées)

	@show_pl bit = 0,								-- afficher les lignes perte - profit

	@return_retro bit = 0,							-- retourner la rétrocession (oui/non)


	@currency_code	ais_code_short					-- code devise (ou %)
	
	
as 
	set nocount on;

	-- impossible d'utiliser une table temporaire à cause de l'UDT ais_code_short
	declare @ids TABLE
	(
		account_id int not null primary key, 
		type_code [dbo].[ais_code_short] not null,
		lettering int null,
		adv_lettering int null
	)




	------------------------------------------------------------------------------------------
	-- traitement des quittances

	insert into @ids(account_id, type_code, lettering)
	select	a.a_account_id, a.a_type_code, a.a_lettering
	from	acc_invoices i
			inner join acc_accounts a on a.a_account_id = i.inv_invoice_id
	where	a.a_actor_id = @client_id
	and		1 = case
				when @show_invoices_an != 0 and inv_operation_code__last = 'AN' then 1
				when @show_invoices_cr != 0 and inv_operation_code__last = 'CR' then 1
				when @show_invoices_db_pos != 0 and isnull(a_amount, 0) >= 0 and inv_operation_code__last = 'DB' then 1
				when @show_invoices_db_neg != 0 and isnull(a_amount, 0) < 0 and inv_operation_code__last = 'DB' then 1
				when @show_invoices_ec != 0 and inv_operation_code__last = 'EC' then 1
				when @show_invoices_rmb != 0 and inv_operation_code__last = 'RMB' then 1
				when @show_invoices_inf != 0 and inv_operation_code__last = 'INF' then 1
				when @show_invoices_awr != 0 and inv_operation_code__last = 'AWR' then 1
				when @show_invoices_s != 0 and inv_operation_code__last = 'S' then 1
				else 0
				end

	------------------------------------------------------------------------------------------



	------------------------------------------------------------------------------------------
	/* 
		Traitement des acomptes
	*/

	insert into @ids(account_id, type_code, lettering, adv_lettering)
	select	a.a_account_id, a.a_type_code, a.a_lettering, adv.adv_lettering
	from	acc_accounts a
			inner join acc_advances adv on adv.adv_advance_id = a.a_account_id
	where	a.a_actor_id = @client_id
	and		a.a_type_code = 'A'
	and		1 = case 
				when @show_a_not_taken != 0 and adv_taken = 0 then 1
				when @show_a_taken != 0 and adv_taken != 0 then 1
				else 0
				end

	------------------------------------------------------------------------------------------




	/*
	Traitement des lignes CP - règlement sinistres
	*/

	if @show_cp_is != 0
	begin
		-- règlements sinistres IS
		insert into @ids(account_id, type_code, lettering)
		select	a.a_account_id, a.a_type_code, a.a_lettering
		from	acc_accounts a
				inner join claim_variables_accounts c on c.clacc_account_id = a.a_account_id
		where	a.a_actor_id = @client_id
		and		a.a_type_code = 'cp'
		and		c.clacc_operation_code = 'is'
		
	end

	if @show_cp_p != 0
	begin
		-- règlements sinistres P (payés)
		insert into @ids(account_id, type_code, lettering)
		select	a.a_account_id, a.a_type_code, a.a_lettering
		from	acc_accounts a
				inner join claim_variables_accounts c on c.clacc_account_id = a.a_account_id
		where	a.a_actor_id = @client_id
		and		a.a_type_code = 'cp'
		and		c.clacc_operation_code = 'p'
		
	end


	/*
	Traitement des lignes CR - encaissement des recours
	*/

	if @show_cr_is != 0
	begin
		-- encaissements recours IS
		insert into @ids(account_id, type_code, lettering)
		select	a.a_account_id, a.a_type_code, a.a_lettering
		from	acc_accounts a
				inner join claim_variables_accounts c on c.clacc_account_id = a.a_account_id
		where	a.a_actor_id = @client_id
		and		a.a_type_code = 'cr'
		and		c.clacc_operation_code = 'is'
		
	end

	if @show_cr_p != 0
	begin
		-- encaissements recours P (payés)
		insert into @ids(account_id, type_code, lettering)
		select	a.a_account_id, a.a_type_code, a.a_lettering
		from	acc_accounts a
				inner join claim_variables_accounts c on c.clacc_account_id = a.a_account_id
		where	a.a_actor_id = @client_id
		and		a.a_type_code = 'cr'
		and		c.clacc_operation_code = 'p'
		
	end

	-- traitement des lignes de perte - profit
	if @show_pl != 0 
	begin
		insert into @ids(account_id, type_code, lettering)
		select	a.a_account_id, a.a_type_code, a.a_lettering
		from	acc_accounts a
		where	a.a_actor_id = @client_id
		and		a.a_type_code = 'PL'
	end


	------------------------------------------------------------------------------------------
	/* 
		Traitement des transactions

		Règles d'affichage:
		a) si @show_t = 0 - pas d'affichage
		b) si @show_t != 0 - afficher la transaction uniquement si la ligne correspondante
			(quittance, acompte, remboursement) et affichée

	*/
	if @show_t != 0
	begin
		-- afficher les acomptes :

		insert into @ids(account_id, type_code, lettering)
		select	a.a_account_id, a.a_type_code, a.a_lettering
		from	acc_accounts a
		where	a.a_actor_id = @client_id
		and		a.a_type_code = 't'
		and		(
				
				exists	( 
						select	1
						from	@ids i
						where	i.lettering = a.a_lettering
						)

				or

				exists	(
						select	1
						from	@ids i
						where	i.adv_lettering = a.a_lettering
						)

				)
				
	end

	if (@return_retro = 0)
	begin
		SELECT	*
		from	vw_acc_account_clients v
				inner join @ids t	on	t.account_id = v.a_account_id
		where	(@currency_code = '%' or a_currency_code = @currency_code)
	end
	else
	begin

		SELECT	v.*,
				vir_amount_retro = isnull(retro.value, 0)
		from	vw_acc_account_clients v
				inner join @ids t	on	t.account_id = v.a_account_id

				left join	(
							select	invoice_id, 
									value = sum(value)
							from	(
									select	invoice_id = invrisvi_invoice_id,
											value = invrisvi_value
									from	acc_invoices_risks_variables_introducers
									where	invrisvi_commission_sharing_deduced = 0

									union 

									select	invoice_id = invpolvi_invoice_id,
											value = invpolvi_value
									from	acc_invoices_policies_variables_introducers
									where	invpolvi_commission_sharing_deduced = 0
									) as t
							group by t.invoice_id
							) as retro on retro.invoice_id = v.a_account_id


		where	(@currency_code = '%' or a_currency_code = @currency_code)
	end








GO
