SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_cts_get_invoices]
	@branch_id			int,								
	@date_ref			date,
	@cts_type_code		ais_code_short,
	@currency_code		ais_code_short,
	@table_introducers	type_table_id READONLY		-- Filtre sur les apporteurs.
as
	/*
	DGE 
	Liste des quittances en situation contentieuse. 
	Le résultat de cette requête sera affiché dans la grille grid_acc_cts_bordereaux_invoices.
	cts_chains - la chaîne contentieux est paramétrée au niveau de l'entité (branch), avec des exceptions par produit et par produit-entité.
	
	Conditions en fonction de la valeur du paramètre @cts_type_code
	
	1°	PN	Premium notice Avis de prime
		ayant une date d'effet supérieure à la date de référence
	
	2°	PR	Premium recall	Appel de prime
		ayant une date d'effet inférieure à la date de référence
	
	3°	UR	Unpaid reminder	Rappel d'impayé
		ayant une date d'effet inférieure de X (X = cts_chains.ctsch_days_ur) jours à la date de référence

	Les autres types CTX fonctionnent par contrat

	4°	FN	Formal notice	Mise en demeure
		ayant une date d'effet inférieure de X (X = cts_chains.ctsch_days_fn) jours à la date de référence.
	
	5°	FNR	Formal notice reminder	Rappel mise en demeure
		ayant une date d'effet inférieure de X (X = cts_chains.ctsch_days_fnr) jours à la date de référence.
		
	6°	CS	Contentious suspension	Suspension contentieux
		ayant une date d'effet inférieure de X (X = cts_chains.ctsch_days_cs) jours à la date de référence.
		
	7°	CT	Contentious termination	Résiliation contentieux
		ayant une date d'effet inférieure de X (X = cts_chains.ctsch_days_ct) jours à la date de référence.
	
	*/

	set nocount on;

	DECLARE @bFilterInt AS bit 

	/*
	Si la table @table_introducers est vide, on ne filtre pas sur les apporteurs.
	Sinon, on renvoie uniquement les contrats des apporteurs de cette table
	*/
	
	IF NOT EXISTS ( SELECT	1
					FROM	@table_introducers )
		SET @bFilterInt = 0		-- ne pas filtrer sur les appporteurs
	ELSE
		SET @bFilterInt = 1		-- appliquer le filtre sur les apporteurs


	declare @policies_invoices table(
				policy_id int not null, 
				invoice_id int not null,
				amount ais_decimal2 null,
				primary key(policy_id, invoice_id)
				)

	insert into @policies_invoices(policy_id, invoice_id, amount)
	select	distinct
			icp.pol_policy_id, 
			a.a_account_id, 
			a.a_amount
	from	vw_acc_invoices_clients_policy icp
			inner join acc_accounts a			on	a.a_account_id = icp.inv_invoice_id

			-- recupération de la chaîne contentieux paramétrée au niveau de la branche
			inner join act_branches b			on	b.br_actor_id = a.a_branch_id
			left join cts_chains ctsb			on	ctsb.ctsch_chain_code = b.br_chain_code

			-- recupération de la chaîne contentieux paramétrée au niveau du produit
			inner join pr_products p			on	p.prd_product_code = icp.pol_product_code
			left join cts_chains ctsp			on	ctsp.ctsch_chain_code = p.prd_chain_code

			-- recupérer les paramètres par défaut de la chaîne contentieux, dans la table pr_products_branches
			left join pr_products_branches pb	on	pb.prdb_product_code = p.prd_product_code
												and	pb.prdb_branch_id = @branch_id

	where	a.a_branch_id = @branch_id 
	and		icp.pol_branch_id = @branch_id			-- par précaution
	and		a.a_currency_code = @currency_code
	and		icp.inv_operation_code__last = 'DB'
			/* 
			tester le nombre de jours par rapport à la chaîne contentieux:
				a) paramétré au niveau du pr_products_branches;
				b) paramétre au niveau du produit; 
				c) paramétré au niveau de la branche ;
				d) sinon - 0
			*/	
	and		1 =		case
					when @cts_type_code = 'pn'	and icp.inv_start_date >= @date_ref then 1
					when @cts_type_code = 'pr'	and icp.inv_start_date < @date_ref then 1
					when @cts_type_code = 'ur'	and dateadd(day, coalesce(pb.prdb_days_ur, ctsp.ctsch_days_ur, ctsb.ctsch_days_ur, 0), icp.inv_start_date) < @date_ref then 1
					when @cts_type_code = 'fn'	and dateadd(day, coalesce(pb.prdb_days_fn, ctsp.ctsch_days_fn, ctsb.ctsch_days_fn, 0), icp.inv_start_date) < @date_ref then 1
					when @cts_type_code = 'fnr' and dateadd(day, coalesce(pb.prdb_days_fnr, ctsp.ctsch_days_fnr, ctsb.ctsch_days_fnr, 0), icp.inv_start_date) < @date_ref then 1
					when @cts_type_code = 'cs'	and dateadd(day, coalesce(pb.prdb_days_cs, ctsp.ctsch_days_cs, ctsb.ctsch_days_cs, 0), icp.inv_start_date) < @date_ref then 1
					when @cts_type_code = 'ct'	and dateadd(day, coalesce(pb.prdb_days_ct, ctsp.ctsch_days_ct, ctsb.ctsch_days_ct, 0), icp.inv_start_date) < @date_ref then 1
					else 0
					end
	
	and		not exists 
			(
			select	1
			from	acc_cts_bordereaux_accounts ba
					inner join acc_cts_bordereaux b	on b.ctsb_bordereau_id = ba.ctsa_bordereau_id
			where	b.ctsb_type_code = @cts_type_code
			and		ba.ctsa_account_id = icp.inv_invoice_id)

	-- filtre sur les apporteurs
	AND		1 =		CASE
					WHEN @bFilterInt = 0 THEN 1		-- ne pas appliquer le filtre
					WHEN @bFilterInt != 0 AND EXISTS(	SELECT	1
									FROM	dbo.pol_policies_situations_introducers ppsi
											INNER JOIN @table_introducers t ON ppsi.polsi_introducer_id = t.id
									WHERE	ppsi.polsi_policy_situation_id = icp.pols_policy_situation_id ) THEN 1
					ELSE 0
					END

	


	if @cts_type_code in ('pn', 'pr', 'ur')
	begin

		-- traitement par quittance
		select			
				ctsa_bordereau_id = -1,
				ctsa_policy_id = a.policy_id,
				ctsa_account_id = a.invoice_id,
		
				icp.act_internal_reference,
				icp.adr_complete_name,
				icp.pols_number,
				icp.inv_operation_code__last, 
				icp.inv_start_date, 
				icp.inv_end_date, 
				icp.inv_invoice_id,
				icp.act_actor_id,
				icp.pols_policy_id,
				icp.inv_policy_situation_id,
				a_amount = a.amount

		from	vw_acc_invoices_clients_policy icp
				inner join @policies_invoices a on a.invoice_id = icp.inv_invoice_id
		where	-- tester si la quittance est une ristourne
				isnull(a.amount, 0) > 0

	end
	else
	begin
		-- traitement par contrat
		declare @policies_advances table(
						policy_id int not null, 
						advance_id int not null, 
						amount ais_decimal2 null,
						primary key(policy_id, advance_id))

		insert into @policies_advances(policy_id, advance_id, amount)
		select	distinct adv.adv_policy_id, adv.a_account_id, a_amount
		from	vw_acc_advances adv
				inner join @policies_invoices p on p.policy_id = isnull(adv.adv_policy_id, -1)
		where	adv.a_branch_id = @branch_id
		and		adv.a_currency_code = @currency_code
		and		adv.adv_taken = 0	
		
		declare @policies table(
						policy_id int not null primary key,
						amount_inv ais_decimal2 null,
						amount_adv ais_decimal2 null)
		
		insert into @policies(policy_id, amount_inv, amount_adv)
		select	i.policy_id, i.amount, a.amount
		from	(
				select	policy_id, amount = sum(amount)
				from	@policies_invoices
				group by policy_id
				) as i

				left join	(
							select	policy_id, amount = sum(amount)
							from	@policies_advances
							group by policy_id
							) as a on a.policy_id = i.policy_id

		where	isnull(i.amount, 0) - abs(isnull(a.amount, 0)) > 0


		-- retourner la liste de contrats
		select	
				ctsp_bordereau_id = -1,
				ctsp_policy_id = t.policy_id,
				ctsp_amount_inv = t.amount_inv,
				ctsp_amount_adv = abs(t.amount_adv),
				ctsp_amount = isnull(t.amount_inv, 0) - abs(isnull(t.amount_adv, 0)),

				c.act_internal_reference, 
				c.adr_complete_name,
				s.pols_number

		from	@policies t
				inner join pol_policies p on p.pol_policy_id = t.policy_id
				inner join vw_act_clients c on c.act_actor_id = p.pol_client_id
				inner join pol_policies_situations s on s.pols_policy_situation_id = p.pol_last_policy_situation_id

		-- retourner les invoices
		select	
		
				ctsa_bordereau_id = -1,
				ctsa_policy_id = a.policy_id,
				ctsa_account_id = a.invoice_id,
		
				icp.act_internal_reference,
				icp.adr_complete_name,
				icp.pols_number,
				icp.inv_operation_code__last, 
				icp.inv_start_date, 
				icp.inv_end_date, 
				icp.inv_invoice_id,
				icp.act_actor_id,
				icp.pols_policy_id,
				icp.inv_policy_situation_id,
				a_amount = a.amount

		from	vw_acc_invoices_clients_policy icp
				inner join @policies_invoices a on a.invoice_id = icp.inv_invoice_id
				inner join @policies p on p.policy_id = icp.pol_policy_id


		-- retourner les advances
		select	
				ctsa_bordereau_id = -1,
				ctsa_policy_id = pa.policy_id,
				ctsa_account_id = pa.advance_id,
				
				v.a_operation_utc_date, 
				a_amount = abs(v.a_amount),
				v.adv_lettering, 
				v.adv_obs
		from	vw_acc_advances v
				inner join @policies_advances pa on pa.advance_id = v.adv_advance_id
				inner join @policies p on p.policy_id = pa.policy_id


				

	end



GO
