<?xml version="1.0" encoding="utf-8"?>
<AisMenuItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/AisMenuItem.xsd">
	<Action>stat</Action>
	<SecondAction></SecondAction>
	<Ico></Ico>
  <Label>
    <L1>Turnover</L1>
    <L2>Chiffre d'affaires</L2> 
  </Label>
  <Description>
    <L1>Debit notes payment assigned during the selected period whatever their date of issuance</L1>
    <L2>Etat des quittances émises, quelque soit leur date d'émission et encaissées pendant la période sélectionée</L2>
  </Description>
  <Stat>
    <FileMajorVersion>1</FileMajorVersion>
    <FileMinorVersion>0</FileMinorVersion>
    <Parameters>
      <Parameter>
        <Name>currency_code</Name>
        <Type>string</Type>
        <Label>
          <L1>Currency</L1>
          <L2>Devise</L2>
        </Label>
        <Combo>stat_params.spar_currency_code</Combo>
        <LabelWidth>300</LabelWidth>
        <TotalWidth>800</TotalWidth>
      </Parameter>
      <Parameter>
        <Name>@date_enc_de</Name>
        <Type>date</Type>
        <Label>
          <L1>Encashment date from</L1>
          <L2>Date d'encaissement de</L2>
        </Label>
        <LabelWidth>300</LabelWidth>
        <TotalWidth>600</TotalWidth>
      </Parameter>
      <Parameter>
        <Name>@date_enc_a</Name>
        <Type>date</Type>
        <Label>
          <L1>Encashment date to</L1>
          <L2>Date d'encaissement à</L2>
        </Label>
        <LabelWidth>300</LabelWidth>
        <TotalWidth>600</TotalWidth>
      </Parameter>
    </Parameters>
	
    <SQL>
	
	; 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
				
							
		WHERE	a.a_currency_code = @currency_code
		AND		inv_operation_code__last IN ('EC', 'RMB')
		AND		ISNULL(i.inv_ec_date, i.inv_rmb_date) BETWEEN ISNULL(@date_enc_de, '01/01/1900') AND ISNULL(@date_enc_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)

		
		

    </SQL>
	
 </Stat>
 
</AisMenuItem>