SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_tt_accounts_invoices]
	@policies_situations_table type_table_id readonly,
	@invoices_table type_table_id readonly					-- DGE 19/06/2019 - 0000911: Ajouter button telecharger quittance dans la page details de la quittance
as 
	set nocount on;

	declare @invoices table (invoice_id int)

	--obtenir la liste des quittances
	insert into @invoices(invoice_id)
	SELECT DISTINCT invoice_id = invpolv_invoice_id
	FROM acc_invoices_policies_variables aipv 
		INNER JOIN @policies_situations_table pst ON pst.id = aipv.invpolv_policy_situation_id

	UNION 

	SELECT DISTINCT invoice_id = invris_invoice_id
	FROM acc_invoices_risks_situations airs
		INNER JOIN ris_risks_situations rrs ON rrs.riss_risk_situation_id = airs.invris_risk_situation_id
		INNER JOIN @policies_situations_table pst ON pst.id = rrs.riss_policy_situation_id


	IF EXISTS (SELECT 1 FROM @invoices_table)
	BEGIN 
		-- imprimer uniquement la quittance spécifiée (impression à partir du zoom de la quittance). 
		
		DELETE	i 
		FROM	@invoices i 
		WHERE	NOT EXISTS ( SELECT 1 FROM @invoices_table it WHERE it.id = i.invoice_id)
	END


	select	ai.*, a.*, pp.pol_currency_code, 
			pols_var_all_inv_tf,
			pols_var_all_inv_taxes , 
			pols_var_all_inv_ti,
			pols_var_fee_inv_tf, 
			pols_var_fee_inv_taxes, 
			pols_var_fee_inv_ti,
			pols_var_per_inv_tf, 
			pols_var_per_inv_taxes, 
			pols_var_per_inv_ti,
			pols_var_all_inv_commissions_tf,
			pols_var_all_inv_commissions_taxes,
			pols_var_all_inv_commissions_ti,

			pols_var_bpc_inv_tf = isnull(pols_var_bpc_inv_tf,0),
			pols_var_bpf_inv_tf = isnull(pols_var_bpf_inv_tf,0),
			pols_var_brc_inv_tf = isnull(pols_var_brc_inv_tf,0),
			pols_var_brf_inv_tf = isnull(pols_var_brf_inv_tf,0),
			pols_var_cpf_inv_tf = isnull(pols_var_cpf_inv_tf,0),
			pols_var_crf_inv_tf = isnull(pols_var_crf_inv_tf,0),
			
			pols_var_bpc_inv_taxes = isnull(pols_var_bpc_inv_taxes,0),
			pols_var_bpf_inv_taxes = isnull(pols_var_bpf_inv_taxes,0),
			pols_var_brc_inv_taxes = isnull(pols_var_brc_inv_taxes,0),
			pols_var_brf_inv_taxes = isnull(pols_var_brf_inv_taxes,0),
			pols_var_cpf_inv_taxes = isnull(pols_var_cpf_inv_taxes,0),
			pols_var_crf_inv_taxes = isnull(pols_var_crf_inv_taxes,0),
			
			pols_var_bpc_inv_ti = isnull(pols_var_bpc_inv_ti,0),
			pols_var_bpf_inv_ti = isnull(pols_var_bpf_inv_ti,0),
			pols_var_brc_inv_ti = isnull(pols_var_brc_inv_ti,0),
			pols_var_brf_inv_ti = isnull(pols_var_brf_inv_ti,0),
			pols_var_cpf_inv_ti = isnull(pols_var_cpf_inv_ti,0),
			pols_var_crf_inv_ti = isnull(pols_var_crf_inv_ti,0),

			ROW_NUMBER() OVER(PARTITION BY pps.pols_policy_situation_id ORDER BY ai.inv_start_date ASC, ai.inv_invoice_id asc) AS inv_invoice_order_by_start_date
	from @invoices i	
	   INNER JOIN acc_invoices ai ON ai.inv_invoice_id = i.invoice_id
	   inner join acc_accounts a on a.a_account_id = ai.inv_invoice_id
	   inner join pol_policies_situations pps on pps.pols_policy_situation_id = inv_policy_situation_id
	   inner join pol_policies pp on pp.pol_policy_id = pps.pols_policy_id

	   left join (

				   SELECT	sum_invoice_id,
							pols_var_all_inv_tf = sum(tf),
							pols_var_all_inv_taxes = sum(taxes), 
							pols_var_all_inv_ti = sum(ti),
							pols_var_fee_inv_tf = sum(CASE WHEN nmpvt_peril = 0 THEN tf ELSE 0 end), 
							pols_var_fee_inv_taxes = sum(CASE WHEN nmpvt_peril = 0 THEN taxes ELSE 0 end), 
							pols_var_fee_inv_ti = sum(CASE WHEN nmpvt_peril = 0 THEN ti ELSE 0 end),
							pols_var_per_inv_tf = sum(CASE WHEN nmpvt_peril = 1 THEN tf ELSE 0 end), 
							pols_var_per_inv_taxes = sum(CASE WHEN nmpvt_peril = 1 THEN taxes ELSE 0 end), 
							pols_var_per_inv_ti = sum(CASE WHEN nmpvt_peril = 1 THEN ti ELSE 0 end),
							pols_var_all_inv_commissions_tf = sum(cions_tf),
							pols_var_all_inv_commissions_taxes = sum(cions_taxes),
							pols_var_all_inv_commissions_ti = sum(cions_ti),

							pols_var_bpc_inv_tf = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_cost = 1 THEN tf ELSE 0 end),
							pols_var_bpf_inv_tf = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_fee = 1 THEN tf ELSE 0 end),
							pols_var_brc_inv_tf = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_cost = 1 THEN tf ELSE 0 end),
							pols_var_brf_inv_tf = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_fee = 1 THEN tf ELSE 0 end),
							pols_var_cpf_inv_tf = sum(CASE WHEN nmpvt_company = 1 and nmpvt_policy = 1 and nmpvt_is_company_fee = 1 THEN tf ELSE 0 end),
							pols_var_crf_inv_tf = sum(CASE WHEN nmpvt_company = 1 and nmpvt_risk = 1 and nmpvt_is_company_fee = 1 THEN tf ELSE 0 end),
							
							pols_var_bpc_inv_taxes = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_cost = 1 THEN taxes ELSE 0 end),
							pols_var_bpf_inv_taxes = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_fee = 1 THEN taxes ELSE 0 end),
							pols_var_brc_inv_taxes = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_cost = 1 THEN taxes ELSE 0 end),
							pols_var_brf_inv_taxes = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_fee = 1 THEN taxes ELSE 0 end),
							pols_var_cpf_inv_taxes = sum(CASE WHEN nmpvt_company = 1 and nmpvt_policy = 1 and nmpvt_is_company_fee = 1 THEN taxes ELSE 0 end),
							pols_var_crf_inv_taxes = sum(CASE WHEN nmpvt_company = 1 and nmpvt_risk = 1 and nmpvt_is_company_fee = 1 THEN taxes ELSE 0 end),
							

							pols_var_bpc_inv_ti = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_cost = 1 THEN ti ELSE 0 end),
							pols_var_bpf_inv_ti = sum(CASE WHEN nmpvt_policy = 1 and nmpvt_is_broker_fee = 1 THEN ti ELSE 0 end),
							pols_var_brc_inv_ti = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_cost = 1 THEN ti ELSE 0 end),
							pols_var_brf_inv_ti = sum(CASE WHEN nmpvt_risk = 1 and nmpvt_is_broker_fee = 1 THEN ti ELSE 0 end),
							pols_var_cpf_inv_ti = sum(CASE WHEN nmpvt_company = 1 and nmpvt_policy = 1 and nmpvt_is_company_fee = 1 THEN ti ELSE 0 end),
							pols_var_crf_inv_ti = sum(CASE WHEN nmpvt_company = 1 and nmpvt_risk = 1 and nmpvt_is_company_fee = 1 THEN ti ELSE 0 end)--,
							

					FROM 

						(

								select	sum_invoice_id, variable_id, tf = sum(tf), taxes = sum(taxes), ti = sum(ti), cions_tf = sum(cions_tf), cions_taxes = sum(cions_taxes), cions_ti = sum(cions_ti)
								from	(
										select	sum_invoice_id = i.invoice_id, variable_id = invpolv_variable_id, tf = invpolv_tf, taxes = invpolv_taxes, ti = invpolv_ti, cions_tf = invpolv_commission_tf, cions_taxes = invpolv_commission_taxes, cions_ti = invpolv_commission_ti
										FROM acc_invoices_policies_variables aipv
											inner join @invoices i on i.invoice_id = aipv.invpolv_invoice_id
				
										union all

										select	sum_invoice_id = i.invoice_id, variable_id = invrisv_variable_id, tf = invrisv_tf, taxes = invrisv_taxes, ti = invrisv_ti, cions_tf = invrisv_commission_tf, cions_taxes = invrisv_commission_taxes, cions_ti = invrisv_commission_ti
										FROM acc_invoices_risks_variables airv
											inner join @invoices i on i.invoice_id = airv.invrisv_invoice_id

										) as a
								group by sum_invoice_id, variable_id
						) AS t 
							INNER JOIN pr_products_variables ppv ON ppv.prdv_variable_id = t.variable_id
							INNER JOIN nm_products_variables npv ON npv.nmpv_variable_code = ppv.prdv_variable_code
							INNER JOIN nm_products_variables_types npvt ON npvt.nmpvt_type_code = npv.nmpv_type_code
					GROUP BY t.sum_invoice_id) as cumuls on cumuls.sum_invoice_id = i.invoice_id

		

	 



GO
