SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_trn_bordereaux_select]
	@branch_id			int,					
	@payment_code		ais_code_short,
	@currency_code		ais_code_short,
	@date_from			date, 
	@date_to			date,
	@db					bit,								-- afficher les lignes de débit
	@cr					bit,								-- afficher les lignes de crédit
	@table_accounts		type_table_ais_name READONLY,		-- liste des comptes bancaires à traiter
	@table_introducers	type_table_id READONLY,				-- liste des apporteurs
	@table_companies	type_table_id READONLY,				-- liste des compagnies
	@table_departments	type_table_code_medium READONLY		-- liste de départements
	
as

	set nocount on;


	/*
	DGE 29/05/2015

	Sélectionne les transaction à inclure dans un nouveau bordereau.

	Pour les transactions liées à des compagnies / apporteurs, on sélectionne uniquement 
	les lignes appartenant aux acteurs spécifiés dans @table_introducers et @table_compagnies.

	*/

	
	select	t.*,
			a.act_internal_reference,
			adr.adr_complete_name,
			trnbd_amount_db =	case
								when a.act_type_code = 'CLI' and a_amount > 0 then a_amount
								when a.act_type_code != 'CLI' and a_amount < 0 then abs(a_amount)
								else 0
								end,
			trnbd_amount_cr =	case
								when a.act_type_code = 'CLI' and a_amount < 0 then abs(a_amount)
								when a.act_type_code != 'CLI' and a_amount > 0 then a_amount
								else 0
								end
	from	vw_acc_transactions t
			--inner join @table_accounts ta	on	ta.name = t.atrn_account_number__branch		-- filtrer le n° de compte
			inner join act_actors a			on	a.act_actor_id = t.atrn_actor_id
			inner join adr_addresses adr	on	adr.adr_address_id = a.act_address_id
			-- filtre par département
			inner join @table_departments d	on	d.code_medium = t.atrn_department_code

			INNER JOIN nm_payment_modes m	ON	m.pm_payment_code = t.atrn_payment_code
	where	a_branch_id = @branch_id
	and		atrn_payment_code = @payment_code
	and		a_currency_code = @currency_code
	and		convert(date, a_operation_utc_date) between @date_from and @date_to

	and		1 =		case
					-- débit et crédit
					when @db != 0 and @cr != 0 then 1			
					-- débit seulement
					when @db != 0 and @cr = 0 and a.act_type_code = 'CLI' and a_amount > 0 then 1
					when @db != 0 and @cr = 0 and a.act_type_code != 'CLI' and a_amount < 0 then 1
					-- crédit seulement
					when @db = 0 and @cr != 0 and a.act_type_code = 'CLI' and a_amount < 0 then 1
					when @db = 0 and @cr != 0 and a.act_type_code != 'CLI' and a_amount > 0 then 1
					-- ni débit, ni crédit
					else 0
					end


	-- à optimiser
	and		1 =		case	
					when a.act_type_code = 'CIE' and not exists ( select 1 from @table_companies tc where tc.id = t.atrn_actor_id ) then 0
					when a.act_type_code = 'INT' and not exists ( select 1 from @table_introducers ti where ti.id = t.atrn_actor_id) then 0
					else 1
					end

	-- les transactions qui ne sont pas incluses dans un autre bordereau
	and		not exists	(
						select	1
						from	acc_trn_bordereaux_details d
						where	d.trnbd_transaction_id = t.a_account_id 
						)

	-- filtrer le numéro de compte

	
	AND		(

			( m.pm_select_branch_account = 0 AND t.atrn_account_number__branch IS NULL )  -- BF, DR, IT, MP, PP - voir la contrainte de la table acc_transactions

			OR

			EXISTS	(
					SELECT	1
					FROM	@table_accounts ta
					WHERE	ta.name = t.atrn_account_number__branch
					)

			)


GO
