SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE  PROCEDURE [dbo].[p_stat_monthly_payments_modes_movements]
(
		@currency_code VARCHAR(10) = NULL,
		@date_de DATETIME = NULL,
		@date_a DATETIME = NULL
)
AS
BEGIN

	SET NOCOUNT ON;


	CREATE TABLE	#t
	(
		account_id			INT NOT NULL PRIMARY KEY,
		group_id			INT NOT NULL
	) ;



	DECLARE @account_id		INT,
			@group_id		INT,
			@actor_id		INT,
			@lettering		INT,
			@parent_id		INT,
			@line_id		INT;



	DECLARE c_trn INSENSITIVE CURSOR
	FOR		SELECT	DISTINCT trn.atrn_transaction_id, trn.a_actor_id, trn.a_lettering, src.parent_id
			FROM	dbo.acc_trn_bordereaux	b
					INNER JOIN dbo.acc_trn_bordereaux_details bd	ON	bd.trnbd_bordereau_id = b.trnb_bordereau_id
					INNER JOIN dbo.vw_acc_transactions trn			ON	trn.atrn_transaction_id = bd.trnbd_transaction_id

					OUTER APPLY (
								SELECT	parent_id = MAX(l.alnk_account_id__src)
								FROM	acc_accounts_links l
								WHERE	l.alnk_account_id__dst = trn.a_account_id
								) AS src

			WHERE	b.trnb_tmp = 0
			AND		1 =		CASE
							WHEN ISNULL(@currency_code, '') = '' THEN 1
							WHEN b.trnb_currency_code = @currency_code THEN 1
							ELSE 0
							END
			AND		b.trnb_operation_utc_date BETWEEN ISNULL(@date_de, '01/01/1900') AND ISNULL(@date_a, '01/01/2100')


			ORDER BY trn.atrn_transaction_id ;


	OPEN c_trn ;


	FETCH NEXT FROM c_trn INTO @account_id, @actor_id, @lettering, @parent_id ;


	WHILE @@FETCH_STATUS = 0 
	BEGIN

		IF NOT EXISTS (SELECT 1 FROM #t WHERE account_id = @account_id)
		BEGIN

			INSERT INTO #t(account_id, group_id)
			VALUES (@account_id, ISNULL(@parent_id, @account_id)) ;

			-- CSD
			INSERT INTO #t(account_id, group_id)
			SELECT	a.a_account_id, ISNULL(@parent_id, @account_id)
			FROM	acc_accounts a
			WHERE	a.a_type_code = 'T'
			AND		a.a_actor_id = @actor_id
			AND		a.a_lettering = @lettering
			AND		NOT EXISTS	(	
								SELECT	1
								FROM	#t t
								WHERE	t.account_id = a.a_account_id
								) ;
		END



		FETCH NEXT FROM c_trn INTO @account_id, @actor_id, @lettering, @parent_id ;
	END


	CLOSE c_trn ;
	DEALLOCATE c_trn ;


	--SELECT	t.group_id, a.a_account_id, a.a_actor_id, a.a_lettering
	--		FROM	#t t
	--				INNER JOIN acc_accounts a ON a.a_account_id = t.account_id
	--		WHERE	a.a_actor_id = 200000318
	--		ORDER BY t.account_id ;

	--RETURN;

	DECLARE c_lines INSENSITIVE CURSOR 
	FOR		SELECT	t.group_id, a.a_account_id, a.a_actor_id, a.a_lettering
			FROM	#t t
					INNER JOIN acc_accounts a ON a.a_account_id = t.account_id
			--WHERE	a.a_actor_id = 200000318
			ORDER BY t.account_id ;


	OPEN c_lines;

	FETCH NEXT FROM c_lines INTO @group_id, @account_id, @actor_id, @lettering;


	WHILE @@FETCH_STATUS = 0 
	BEGIN


		-- traitement acomptes
		INSERT INTO #t(account_id, group_id)
		SELECT	va.a_account_id, @group_id
		FROM	vw_acc_advances va
		WHERE	va.a_actor_id = @actor_id
		AND		va.adv_lettering = @lettering
		AND		NOT EXISTS	(	
							SELECT	1
							FROM	#t t
							WHERE	t.account_id = va.a_account_id
							)

		-- traitement des autres types 
		INSERT INTO #t(account_id, group_id)
		SELECT	a.a_account_id, @group_id
		FROM	acc_accounts a
		WHERE	a.a_actor_id = @actor_id
		AND		a.a_lettering = @lettering
		AND		NOT EXISTS	(	
							SELECT	1
							FROM	#t t
							WHERE	t.account_id = a.a_account_id
							)


		FETCH NEXT FROM c_lines INTO @group_id, @account_id, @actor_id, @lettering
	END

	CLOSE c_lines;
	DEALLOCATE c_lines;





	SELECT	--t.account_id,
			--a.a_branch_id,
			[vir_no]					=	DENSE_RANK() OVER (ORDER BY gtrn.acc_bank_name__branch, gtrn.acc_account_number__branch, gpm.pm_payment_code, t.group_id),
			[vir_order]					=	ROW_NUMBER() OVER (
												PARTITION BY t.group_id ORDER BY CASE WHEN t.group_id = t.account_id THEN 0 ELSE 1 END, ISNULL(adv.adv_lettering, a.a_lettering)
											),
			[vir_bank_name]				=	CASE 
											WHEN t.group_id = t.account_id THEN gtrn.acc_bank_name__branch
											ELSE NULL
											END,
			[IBAN]						=	CASE 
											WHEN t.group_id = t.account_id THEN gtrn.acc_account_number__branch
											ELSE NULL
											END,
			[vir_payment_mode_code]		=	CASE 
											WHEN t.group_id = t.account_id THEN gpm.pm_payment_code
											ELSE pm.pm_payment_code
											END,
			[vir_payment_mode]			=	CASE 
											WHEN t.group_id = t.account_id THEN gpm.pm_l1
											ELSE pm.pm_l1
											END,
			[vir_payment_date]			=	CASE 
											WHEN t.group_id = t.account_id THEN gtrn.a_operation_utc_date
											ELSE NULL
											END,

			[Type]						=	acct.atyp_l1,
			[Type Bdx]					=	bc.bdrcie_type_code,

			[vir_lettering]				=	a.a_lettering,	
			adv.adv_lettering,

			[currency]					=	a.a_currency_code,
			[vir_amount_hs] =	CASE 
								WHEN act.act_type_code = 'CLI' AND a.a_type_code = 'A' THEN -a.a_amount
								WHEN act.act_type_code <> 'CLI' AND a.a_type_code IN ( 'P', 'CSD') THEN -a.a_amount
								ELSE NULL
								END,

			[vir_amount_db] =	CASE
								WHEN act.act_type_code = 'CLI' AND UPPER(a.a_type_code) <> 'A' and a.a_amount > 0 then a.a_amount
								WHEN act.act_type_code <> 'CLI' AND UPPER(a.a_type_code) NOT IN ('P', 'CSD') and a.a_amount < 0 then ABS(a.a_amount)
								ELSE NULL
								END,

			[vir_amount_cr] =	CASE
								WHEN act.act_type_code = 'CLI' AND UPPER(a.a_type_code) <> 'A' and a.a_amount < 0 then abs(a.a_amount)
								WHEN act.act_type_code <> 'CLI' AND UPPER(a.a_type_code) NOT IN ('P', 'CSD') and a.a_amount > 0 then a.a_amount
								ELSE NULL
								END,

			[vir_client_name] =	cli.adr_complete_name,
			[vir_department_name] = COALESCE(bd1.brdep_l1, bd2.brdep_l1, bd3.brdep_l1),

			[vir_policy_no] = COALESCE(ps.pols_number, adv_pol.pols_number, psc.pols_number),		-- contrat - quittance, contrat - acompte, contrat - sinistre


			[vir_reference]	=	CASE 
								-- Invoices bordereau - recupérer la référence de la transaction
								WHEN a.a_type_code = 'B' THEN ISNULL(bdr.bdr_reference, CAST(a.a_account_id AS VARCHAR(50)))

								-- transactions - recupérer la référence de la transaction
								WHEN a.a_type_code = 'T' THEN ISNULL(trn.atrn_reference, CAST(a.a_account_id AS VARCHAR(50)))
								ELSE CAST(a.a_account_id AS VARCHAR(50))
								END,

			[vir_cie_name]		=	COALESCE(cie_pol.adr_complete_name, cie.adr_complete_name, '-'),
			[vir_app_name]		=	COALESCE(app_pol.adr_complete_name, app.adr_complete_name, app_csd.adr_complete_name , '-'),
			[vir_taxent_name]	=	tax.adr_complete_name,


			[commissions]		=	it.cions,
			[frais gestion]		=	it.frais_courtier,
			[honoraires]		=	it.hono_courtier,
			[retro]				=	inv_retro.retro,
			
			[vir_claim_internal_reference] =	c.cl_internal_reference,

			[prime HT bdr]			=	CASE 
									WHEN bc.bdrcie_type_code IN ('P', 'PC') THEN bdr.bdr_tf
									ELSE NULL
									END,
			[cion bdr]			=	CASE 
									WHEN bc.bdrcie_type_code IN ('C', 'P', 'PC') THEN bdr.bdr_comm
									ELSE NULL
									END,
			[tx bdr]			=	CASE 
									WHEN bc.bdrcie_type_code IN ('P', 'PC') THEN bdr.bdr_taxes
									ELSE NULL
									END

	--into #dan

	FROM	#t t
			INNER JOIN acc_accounts a						ON	a.a_account_id = t.account_id
			INNER JOIN dbo.acc_accounts_types acct			ON	acct.atyp_type_code = a.a_type_code

			-- transaction 'groupe'
			INNER JOIN dbo.vw_acc_transactions gtrn			ON	gtrn.atrn_transaction_id = t.group_id
			-- mode de paiement 'groupe'
			INNER JOIN dbo.nm_payment_modes gpm				ON	gpm.pm_payment_code = gtrn.atrn_payment_code

			-- transaction 'ligne'
			LEFT JOIN dbo.vw_acc_transactions trn			ON	trn.atrn_transaction_id = t.account_id
			LEFT JOIN dbo.nm_payment_modes pm				ON	pm.pm_payment_code = trn.atrn_payment_code

			INNER JOIN dbo.act_actors act					ON	act.act_actor_id = a.a_actor_id


			-- encaissement d'une quittance ?
			LEFT JOIN dbo.acc_invoices i					ON	i.inv_invoice_id = a.a_account_id
			LEFT JOIN dbo.pol_policies_situations ps		ON	ps.pols_policy_situation_id = i.inv_policy_situation_id
			LEFT JOIN dbo.pol_policies p1					ON 	p1.pol_policy_id = ps.pols_policy_id
			LEFT JOIN dbo.vw_act_cies cie_pol 				ON	cie_pol.act_actor_id = p1.pol_company_id
			LEFT JOIN dbo.act_branches_departments bd1		ON 	bd1.brdep_branch_id = p1.pol_branch_id
															AND bd1.brdep_department_code = p1.pol_department_code	
			OUTER APPLY	(
							SELECT	TOP (1)		-- improbable, on n'a pas plusierus apporteurs / quittance
									invrisvi_invoice_id, invrisvi_introducer_id AS [introducer_id], SUM(invrisvi_value) AS [retro]
							FROM	acc_invoices_risks_variables_introducers 
							WHERE	invrisvi_invoice_id = i.inv_invoice_id
							GROUP BY invrisvi_invoice_id, invrisvi_introducer_id
						) as inv_retro
			LEFT JOIN vw_act_introducers app_pol			ON	app_pol.act_actor_id = inv_retro.introducer_id

			-- pour les lignes CSD - on veut afficher l'apporteur
			OUTER APPLY	(
							SELECT	TOP (1) a_csd.a_actor_id as [introducer_id]
							FROM	acc_commission_sharing_deduced csd
									INNER JOIN acc_accounts a_csd ON a_csd.a_account_id = csd.csd_csd_id
							WHERE	csd.csd_transaction_id = a.a_account_id
						) as t_int
			LEFT JOIN vw_act_introducers app_csd			ON	app_csd.act_actor_id = t_int.introducer_id

			OUTER APPLY (
							SELECT	invt_invoice_id, 
									frais_courtier = SUM(CASE WHEN invt_variable_type_code IN ('BPC', 'BRC') THEN invt_ti ELSE 0 END),
									hono_courtier = SUM(CASE WHEN invt_variable_type_code IN ('BPF', 'BRF') THEN invt_ti ELSE 0 END),
									cions = SUM(invt_commission_ti)
							FROM	vw_acc_invoices_totals vi
							WHERE	vi.invt_invoice_id = i.inv_invoice_id
							GROUP BY invt_invoice_id
						) as it




			LEFT JOIN dbo.vw_act_clients cli				ON	cli.cli_actor_id = a.a_actor_id

			-- acompte ? essayer de recupérer les numéro de contrat
			LEFT JOIN acc_advances adv						ON	adv.adv_advance_id = a.a_account_id
			OUTER APPLY (
						SELECT TOP(1) advps.pols_number
						FROM	dbo.pol_policies_situations advps
						WHERE	advps.pols_policy_id = adv.adv_policy_id
						ORDER BY advps.pols_policy_situation_id DESC 
						) AS adv_pol


			LEFT JOIN dbo.acc_accounts acc					ON	acc.a_account_id = a.a_account_id
			LEFT JOIN dbo.acc_bordereaux bdr				ON	bdr.bdr_bordereau_id = a.a_account_id

			LEFT JOIN dbo.pol_policies p2					ON	p2.pol_policy_id = adv.adv_policy_id
			LEFT JOIN dbo.act_branches_departments bd2		ON 	bd2.brdep_branch_id = p2.pol_branch_id
															AND bd2.brdep_department_code = p2.pol_department_code	

			-- bordereau compagnie?
			LEFT JOIN dbo.vw_act_cies cie					ON	cie.act_actor_id = acc.a_actor_id
			LEFT JOIN dbo.acc_bordereaux_companies bc		ON	bc.bdrcie_bordereau_id = a.a_account_id

			-- bordereau apporteur?
			LEFT JOIN dbo.vw_act_introducers app			ON	app.act_actor_id = acc.a_actor_id

			-- bodereaux taxes 
			LEFT JOIN dbo.vw_act_tax_entities tax			ON	tax.act_actor_id = acc.a_actor_id

			-- règlement sin ?
			LEFT JOIN dbo.claim_variables_accounts cv		ON	cv.clacc_account_id = a.a_account_id
			LEFT JOIN dbo.claim_claims c					ON	c.cl_claim_id = cv.clacc_claim_id
			LEFT JOIN dbo.pol_policies_situations psc		ON	psc.pols_policy_situation_id = c.cl_policy_situation_id
			LEFT JOIN dbo.pol_policies p3					ON	p3.pol_policy_id = psc.pols_policy_id
			LEFT JOIN dbo.act_branches_departments bd3		ON 	bd3.brdep_branch_id = p3.pol_branch_id
															AND bd3.brdep_department_code = p3.pol_department_code	

	--WHERE	a.a_actor_id = 200000318
	ORDER BY  gtrn.acc_bank_name__branch, gtrn.acc_account_number__branch, gpm.pm_payment_code, t.group_id, CASE WHEN t.group_id = t.account_id THEN 0 ELSE 1 END, ISNULL(adv.adv_lettering, a.a_lettering)
	



END


;

GO
