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

BEGIN;

	SET NOCOUNT ON;



	; WITH	cte_select 
	AS	(
		
		SELECT	[Devise]					= a.a_currency_code, 
				[Département]				= p.pol_department_code,
				[Compagnie]					= cie.adr_name, 
				[Famille de produit]		= f.pf_family_code,
				[ID Quittance]				= i.inv_invoice_id,
				[Numéro de police]			= ps.pols_number,
				[ID Risque]					= r.riss_number,
				[Nom]						= cli.adr_complete_name,
				[Début d'effet]				= CAST(i.inv_start_date AS DATE),
				[Fin d'effet]				= CAST(i.inv_end_date AS DATE),
				[Date encaissement]			= CAST(ISNULL(i.inv_ec_date, i.inv_rmb_date) AS DATE),
				[Date reversement Cie]		= rcie.date_reversement_cie,
				[Prime TTC]					= a.a_amount,
				[Prime nette]				= tf.total,
				[Taxes]						= tx.total,
				[Commissions]				= cions.total,
				[Frais de gestion]			= fg.total,
				[App]						= vi.adr_complete_name,
				[Rétrocession]				= app.retrocession

		FROM	acc_invoices i
				INNER JOIN acc_accounts a					ON	a.a_account_id = i.inv_invoice_id		
				INNER JOIN pol_policies_situations ps		ON	ps.pols_policy_situation_id = i.inv_policy_situation_id
				INNER JOIN pol_policies p					ON	p.pol_policy_id = ps.pols_policy_id
				INNER JOIN pr_products pr					ON	pr.prd_product_code = p.pol_product_code
				INNER JOIN pr_families f					ON	f.pf_family_code = pr.prd_family_code
		
				INNER JOIN vw_act_cies cie					ON	cie.act_actor_id = p.pol_company_id
				INNER JOIN vw_act_clients cli				ON	cli.act_actor_id = p.pol_client_id

				-- date de reversion compagnie : rechercher la transaction qui règle le dernier bordereau cie payée
				LEFT JOIN	(
							SELECT	bd.bdrd_invoice_id ,CAST(MAX(ab2.a_operation_utc_date) AS DATE) AS date_reversement_cie
							FROM	acc_bordereaux b
									INNER JOIN acc_accounts ab					ON	ab.a_account_id = b.bdr_bordereau_id
									INNER JOIN acc_bordereaux_companies bc		ON	bc.bdrcie_bordereau_id = b.bdr_bordereau_id
									INNER JOIN acc_bordereaux_details bd		ON	bd.bdrd_bordereau_id = b.bdr_bordereau_id

									INNER JOIN acc_accounts ab2					ON	ab2.a_actor_id = ab.a_actor_id
																				AND ab2.a_lettering = ab.a_lettering

							WHERE	b.bdr_operation_code = 'P'
							GROUP BY bd.bdrd_invoice_id 
							) AS rcie											ON	rcie.bdrd_invoice_id = i.inv_invoice_id

				-- prime nette
				INNER JOIN	(
							SELECT	invt_invoice_id, SUM(invt_tf) as total
							FROM	vw_acc_invoices_totals
							GROUP BY invt_invoice_id
							) as tf												ON	tf.invt_invoice_id = i.inv_invoice_id
				-- taxes
				INNER JOIN	(
							SELECT	invt_invoice_id, SUM(invt_taxes) as total
							FROM	vw_acc_invoices_totals
							GROUP BY invt_invoice_id
							) as tx												ON	tx.invt_invoice_id = i.inv_invoice_id
				-- commissions
				INNER JOIN	(
							SELECT	invt_invoice_id, SUM(invt_commission_ti) as total
							FROM	vw_acc_invoices_totals
							GROUP BY invt_invoice_id
							) as cions											ON	cions.invt_invoice_id = i.inv_invoice_id
				-- frais de gestion (Frais courtier contrat BPC, Frais courtier risque BRC, Honoraire courtier risque BPF, Honoraire courtier contrat BRF)
				LEFT JOIN	(
							SELECT	invt_invoice_id, SUM(invt_tf) as total
							FROM	vw_acc_invoices_totals
							WHERE	invt_variable_type_code IN ('BPC', 'BRC', 'BPF', 'BRF')
							GROUP BY invt_invoice_id
							) as fg											ON	fg.invt_invoice_id = i.inv_invoice_id
							
				LEFT JOIN 	(
							SELECT	ir.invris_invoice_id, 
									CASE 
									WHEN MIN(rs.riss_number) != MAX(rs.riss_number) THEN '-'
									ELSE MIN(rs.riss_number)
									END AS riss_number
							FROM	acc_invoices_risks_situations ir
									INNER JOIN ris_risks_situations rs ON rs.riss_risk_situation_id = ir.invris_risk_situation_id
							GROUP BY ir.invris_invoice_id
							) AS r 											ON 	r.invris_invoice_id = i.inv_invoice_id

				-- rétrocession: on considère qu'il y a un seul apporteur
				OUTER APPLY	(

							SELECT TOP (1) g.introducer_id, g.retrocession
							FROM	(
									SELECT	u.invoice_id, u.introducer_id, SUM(u.retrocession) AS retrocession
									FROM	(
											SELECT	invrisvi_invoice_id AS invoice_id,
													invrisvi_introducer_id AS introducer_id,
													invrisvi_value AS retrocession
											FROM	acc_invoices_risks_variables_introducers

											UNION ALL

											SELECT	invpolvi_invoice_id,
													invpolvi_introducer_id,
													invpolvi_value
											FROM	acc_invoices_policies_variables_introducers
											) AS u
									GROUP BY u.invoice_id, u.introducer_id
									) as g
							WHERE g.invoice_id = i.inv_invoice_id
							ORDER BY g.retrocession DESC
							) as app
				LEFT JOIN vw_act_introducers vi ON vi.act_actor_id = app.introducer_id

				-- filtrer sur la date de validation du bordereau de transaction
				CROSS APPLY	(
							SELECT TOP 1 trnb_operation_utc_date AS operation_utc_date
							FROM	acc_accounts atrn 
									INNER JOIN acc_trn_bordereaux_details bd ON bd.trnbd_transaction_id = atrn.a_account_id
									INNER JOIN acc_trn_bordereaux b ON b.trnb_bordereau_id = bd.trnbd_bordereau_id and b.trnb_tmp = 0
							WHERE	atrn.a_lettering = a.a_lettering
							AND		atrn.a_type_code = 'T'
							ORDER BY b.trnb_operation_utc_date DESC
							) as t 
				
							
		WHERE	a.a_currency_code = @currency_code
		AND		inv_operation_code__last IN ('EC', 'RMB')
		AND		ISNULL(inv_ec_date, inv_rmb_date) IS NOT NULL
		AND		CAST(t.operation_utc_date AS DATE) BETWEEN ISNULL(@date_de, '01/01/1900') AND ISNULL(@date_a, '01/01/2100')
		AND		inv_an_date IS NULL
		)
	SELECT	[Devise], [Département], [Compagnie], [Famille de produit], [ID Quittance], [Numéro de police], [ID Risque], [Nom], [Début d'effet], [Fin d'effet], [Date encaissement], [Date reversement Cie], 
			[App], [Rétrocession],
			[Prime TTC] = SUM([Prime TTC]), 
			[Prime nette] = SUM([Prime nette]), 
			[Taxes] = SUM(Taxes), 
			[Commissions] = SUM(Commissions), 
			[Frais de gestion] = SUM([Frais de gestion])
	FROM	cte_select
	GROUP BY GROUPING SETS ( 
					([Devise]), 
					([Devise], [Département]), 
					([Devise], [Département], [Compagnie]), 
					([Devise], [Département], [Compagnie], [Famille de produit], [ID Quittance], [Numéro de police], [ID Risque], [Nom], [Début d'effet], [Fin d'effet], [Date encaissement], [Date reversement Cie], [App], [Rétrocession]) )
	ORDER BY	ISNULL([Département], 'ZZZZZZZZZZ'), 
				ISNULL([Compagnie], 'ZZZZZZZZZZZZZZZZ'),
				ISNULL([ID Quittance], 2147483647)



END;

GO
