SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure [dbo].[p_policy_cancel_monthly_payments_data_by_policy_id]
    @policy_id int,
	@policy_mvt_date date,   
	@return_accounts_to_cancel bit = 0,
	@return_invoices_to_cancel bit = 0,
	@return_invoices_as_the_sum_of_canceled bit = 0,
	@return_policy_and_risks_data bit = 0 

as 
	set nocount on;

	declare @pol_last_policy_situation_id int

	select	@pol_last_policy_situation_id = pol_last_policy_situation_id
	from pol_policies pp
	where pp.pol_policy_id =  @policy_id

	declare @invoices_ids TABLE
	(
		account_id int not null primary key
	)

	insert into @invoices_ids(  account_id)
	select		distinct		ai.inv_invoice_id
	from	acc_invoices ai
		inner join pol_policies_situations pps on pps.pols_policy_situation_id = ai.inv_policy_situation_id
	where		pps.pols_policy_id = @policy_id 
			and inv_operation_code__last in ('AWR', 'CR', 'DB')
			and inv_monthly_payment_status_code in ('TOCOLL', 'REPROG', 'REJECT') -- do not take invoices sent to the bank (TRANS or CONFIRM
			and (@policy_mvt_date is null or inv_end_date >= @policy_mvt_date)

	if isnull(@return_policy_and_risks_data,0) = 1
	begin

		select pp.*
		from pol_policies pp 
		where pp.pol_policy_id = @policy_id

		select pps.*
		from pol_policies_situations pps 
		where pps.pols_policy_situation_id = @pol_last_policy_situation_id

		select ppsi.*
		from pol_policies_situations_introducers ppsi 
		where ppsi.polsi_policy_situation_id = @pol_last_policy_situation_id


		select pv.*
		from pol_variables pv 
		where pv.polv_policy_situation_id = @pol_last_policy_situation_id

		select pvt.*
		from pol_variables_taxes pvt 
		where pvt.polvt_policy_situation_id = @pol_last_policy_situation_id

		select ppsr.*
		from pol_policies_situations_repartitions ppsr 
		where ppsr.polsr_policy_situation_id = @pol_last_policy_situation_id


		select *
		from pol_cpl
		where pcpl_policy_situation_id = @pol_last_policy_situation_id

		declare @risks_table table (risk_id int, risk_last_situation_id int)
		
		insert into @risks_table (	risk_id,		risk_last_situation_id	)
		select distinct				riss_risk_id,	ris_last_risk_situation_id
		from acc_invoices_risks_variables airv 
			inner join @invoices_ids ids on	ids.account_id = airv.invrisv_invoice_id
			inner join ris_risks_situations rrs on		rrs.riss_risk_situation_id = airv.invrisv_risk_situation_id
													and rrs.riss_policy_id = @policy_id
			inner join ris_risks rr on rr.ris_risk_id = rrs.riss_risk_id

		select rr.*
		from ris_risks rr
			inner join @risks_table rt on rt.risk_id = rr.ris_risk_id

		select rrs.*
		from ris_risks_situations rrs
			inner join @risks_table rt on rt.risk_last_situation_id = rrs.riss_risk_situation_id

		select rc.*
		from ris_cpl rc
			inner join @risks_table rt on rt.risk_last_situation_id = rc.rcpl_risk_situation_id

		select rv.*
		from ris_variables rv
			inner join @risks_table rt on rt.risk_last_situation_id = rv.risv_risk_situation_id

		select rvt.*
		from ris_variables_taxes rvt
			inner join @risks_table rt on rt.risk_last_situation_id = rvt.risvt_risk_situation_id

			 
	end

	if isnull(@return_accounts_to_cancel,0) = 1
    begin
	   select vaac.*	
	   from	vw_acc_account_clients vaac
			 inner join @invoices_ids ids on	ids.account_id = vaac.a_account_id
    end
		
    if isnull(@return_invoices_to_cancel,0) = 1
    begin
	   select ai.*	
	   from	acc_invoices ai
			 inner join @invoices_ids ids on	ids.account_id = ai.inv_invoice_id
    end

	if isnull(@return_invoices_as_the_sum_of_canceled,0) = 1
	begin

		select	invpolv_invoice_id = convert(int,0),
				invpolv_policy_situation_id = @pol_last_policy_situation_id,
				invpolv_variable_id,-- = max(invpolv_variable_id),
				--ppv.prdv_variable_code,
				invpolv_coef_reduction = convert(decimal(9,4),0),
				invpolv_tf = sum(invpolv_tf),
				invpolv_taxes = sum(invpolv_taxes),
				invpolv_ti = sum(invpolv_ti),
				invpolv_commission_tf = sum(invpolv_commission_tf),
				invpolv_commission_taxes = sum(invpolv_commission_taxes),
				invpolv_commission_ti = sum(invpolv_commission_ti),
				invpolv_commission_percent = CONVERT(decimal(9,4), 0)
		from acc_invoices_policies_variables aipv
			inner join @invoices_ids ii on ii.account_id = aipv.invpolv_invoice_id
			--inner join pr_products_variables ppv on ppv.prdv_variable_id = invpolv_variable_id
		group by invpolv_variable_id --ppv.prdv_variable_code

		select	invpolvc_invoice_id = convert(int,0),
				invpolvc_policy_situation_id = @pol_last_policy_situation_id,
				invpolvc_variable_id, -- = max(invpolvc_variable_id),
				--ppv.prdv_variable_code,
				invpolvc_company_id,
				invpolvc_tf = sum(invpolvc_tf),
				invpolvc_taxes = sum(invpolvc_taxes),
				invpolvc_ti = sum(invpolvc_ti),
				invpolvc_commission_tf = sum(invpolvc_commission_tf),
				invpolvc_commission_taxes = sum(invpolvc_commission_taxes),
				invpolvc_commission_ti = sum(invpolvc_commission_ti)
		from acc_invoices_policies_variables_cies aipvc
			inner join @invoices_ids ii on ii.account_id = aipvc.invpolvc_invoice_id
			--inner join pr_products_variables ppv on ppv.prdv_variable_id = aipvc.invpolvc_variable_id
		--group by ppv.prdv_variable_code, invpolvc_company_id
		group by invpolvc_variable_id, invpolvc_company_id


		select	invpolvct_invoice_id = convert(int,0),
				invpolvct_policy_situation_id = @pol_last_policy_situation_id,
				invpolvct_variable_id, -- = max(invpolvct_variable_id),
				--ppv.prdv_variable_code,
				--at.acctax_tax_code,
				invpolvct_tax_id, -- = max(invpolvct_tax_id),
				invpolvct_tax_value = sum(invpolvct_tax_value)
		from acc_invoices_policies_variables_commission_taxes aipvct
			inner join @invoices_ids ii on ii.account_id = aipvct.invpolvct_invoice_id
			--inner join pr_products_variables ppv on ppv.prdv_variable_id = aipvct.invpolvct_variable_id
			--inner join acc_taxes at on at.acctax_tax_id = invpolvct_tax_id
		--group by ppv.prdv_variable_code, at.acctax_tax_code
		group by invpolvct_variable_id, invpolvct_tax_id
			 

		select	invpolvi_invoice_id = convert(int,0),
				invpolvi_policy_situation_id = @pol_last_policy_situation_id,
				invpolvi_variable_id, -- = max(aipvi.invpolvi_variable_id),
				--ppv.prdv_variable_code,
				invpolvi_introducer_id,
				invpolvi_value = sum(invpolvi_value),
				invpolvi_commission_sharing_deduced = cast(min(invpolvi_commission_sharing_deduced + 0) as bit)
		from acc_invoices_policies_variables_introducers aipvi
			inner join @invoices_ids ii on ii.account_id = aipvi.invpolvi_invoice_id
			--inner join pr_products_variables ppv on ppv.prdv_variable_id = aipvi.invpolvi_variable_id
		--group by ppv.prdv_variable_code, aipvi.invpolvi_introducer_id
		group by invpolvi_variable_id, aipvi.invpolvi_introducer_id


		select	invpolvt_invoice_id = convert(int,0),
				invpolvt_policy_situation_id = @pol_last_policy_situation_id,
				invpolvt_variable_id, -- = max(invpolvt_variable_id),
				invpolvt_tax_id, -- = max(invpolvt_tax_id),
				invpolvt_tax_value = sum(invpolvt_tax_value)--,
				--ppv.prdv_variable_code,
				--at.acctax_tax_code
		from acc_invoices_policies_variables_taxes aipvt
			inner join @invoices_ids ii on ii.account_id = aipvt.invpolvt_invoice_id
			--inner join pr_products_variables ppv on ppv.prdv_variable_id = aipvt.invpolvt_variable_id
			--inner join acc_taxes at on at.acctax_tax_id = aipvt.invpolvt_tax_id
		group by invpolvt_variable_id, invpolvt_tax_id


		select	invrisv_invoice_id = convert(int,0),
				invrisv_risk_situation_id = rr.ris_last_risk_situation_id,
				invrisv_variable_id, -- = max(invrisv_variable_id),
				invrisv_coef_reduction = convert(decimal(9,4),0),
				invrisv_tf = sum(invrisv_tf),
				invrisv_taxes = sum(invrisv_taxes),
				invrisv_ti = sum(invrisv_ti),
				invrisv_commission_tf = sum(invrisv_commission_tf),
				invrisv_commission_taxes = sum(invrisv_commission_taxes),
				invrisv_commission_ti = sum(invrisv_commission_ti),
				invrisv_commission_percent = max(invrisv_commission_percent)
		from acc_invoices_risks_variables airv
			inner join @invoices_ids ii on ii.account_id = airv.invrisv_invoice_id
			inner join ris_risks_situations rrs on rrs.riss_risk_situation_id = invrisv_risk_situation_id
			inner join ris_risks rr on rr.ris_risk_id = rrs.riss_risk_id
			--inner join pr_products_variables ppv on ppv.prdv_variable_id = airv.invrisv_variable_id
		--group by ppv.prdv_variable_code
		group by rr.ris_last_risk_situation_id, invrisv_variable_id

		select	invrisvc_invoice_id = convert(int,0),
				invrisvc_risk_situation_id = rr.ris_last_risk_situation_id,
				invrisvc_variable_id, -- = max(invrisvc_variable_id),
				--ppv.prdv_variable_code,
				invrisvc_company_id,
				invrisvc_tf = sum(invrisvc_tf),
				invrisvc_taxes = sum(invrisvc_taxes),
				invrisvc_ti = sum(invrisvc_ti),
				invrisvc_commission_tf = sum(invrisvc_commission_tf),
				invrisvc_commission_taxes = sum(invrisvc_commission_taxes),
				invrisvc_commission_ti = sum(invrisvc_commission_ti)
		from acc_invoices_risks_variables_cies airvc
			inner join @invoices_ids ii on ii.account_id = airvc.invrisvc_invoice_id
			inner join ris_risks_situations rrs on rrs.riss_risk_situation_id = invrisvc_risk_situation_id
			inner join ris_risks rr on rr.ris_risk_id = rrs.riss_risk_id
			--inner join pr_products_variables ppv on ppv.prdv_variable_id = airvc.invrisvc_variable_id
		--group by ppv.prdv_variable_code, airvc.invrisvc_company_id
		group by rr.ris_last_risk_situation_id, invrisvc_variable_id, airvc.invrisvc_company_id

		select	invrisvct_invoice_id = convert(int, 0),
				invrisvct_risk_situation_id = rr.ris_last_risk_situation_id,
				invrisvct_variable_id, -- = max(invrisvct_variable_id),
				--ppv.prdv_variable_code,
				--at.acctax_tax_code,
				invrisvct_tax_id, -- = max(invrisvct_tax_id),
				invrisvct_tax_value = sum(invrisvct_tax_value)
		from acc_invoices_risks_variables_commission_taxes airvct
			inner join @invoices_ids ii on ii.account_id = airvct.invrisvct_invoice_id
			inner join ris_risks_situations rrs on rrs.riss_risk_situation_id = invrisvct_risk_situation_id
			inner join ris_risks rr on rr.ris_risk_id = rrs.riss_risk_id
			--inner join pr_products_variables ppv on ppv.prdv_variable_id = airvct.invrisvct_variable_id
			--inner join acc_taxes at on at.acctax_tax_id = invrisvct_tax_id 
		--group by ppv.prdv_variable_code, at.acctax_tax_code
		group by rr.ris_last_risk_situation_id,invrisvct_variable_id,invrisvct_tax_id
			 

		select	invrisvi_invoice_id = convert(int,0),
				invrisvi_risk_situation_id = rr.ris_last_risk_situation_id,
				invrisvi_variable_id,-- = max(invrisvi_variable_id),
				--ppv.prdv_variable_code,
				invrisvi_introducer_id,
				invrisvi_value = sum(invrisvi_value),
				invrisvi_commission_sharing_deduced = cast(min(invrisvi_commission_sharing_deduced + 0) as bit)
		from acc_invoices_risks_variables_introducers airvi
			inner join @invoices_ids ii on ii.account_id = airvi.invrisvi_invoice_id
			inner join ris_risks_situations rrs on rrs.riss_risk_situation_id = invrisvi_risk_situation_id
			inner join ris_risks rr on rr.ris_risk_id = rrs.riss_risk_id
			--inner join pr_products_variables ppv on ppv.prdv_variable_id = airvi.invrisvi_variable_id
		--group by ppv.prdv_variable_code, airvi.invrisvi_introducer_id
		group by rr.ris_last_risk_situation_id, invrisvi_variable_id, invrisvi_introducer_id

		select	invrisvt_invoice_id = convert(int,0),
				invrisvt_risk_situation_id = rr.ris_last_risk_situation_id,
				invrisvt_variable_id, -- = max(invrisvt_variable_id),
				invrisvt_tax_id, -- = max(invrisvt_tax_id),
				invrisvt_tax_value = sum(invrisvt_tax_value)--,
				--ppv.prdv_variable_code,
				--at.acctax_tax_code
		from acc_invoices_risks_variables_taxes airvt
			inner join @invoices_ids ii on ii.account_id = airvt.invrisvt_invoice_id
			inner join ris_risks_situations rrs on rrs.riss_risk_situation_id = invrisvt_risk_situation_id
			inner join ris_risks rr on rr.ris_risk_id = rrs.riss_risk_id
			--inner join pr_products_variables ppv on ppv.prdv_variable_id = airvt.invrisvt_variable_id
			--inner join acc_taxes at on at.acctax_tax_id = airvt.invrisvt_tax_id
		--group by ppv.prdv_variable_code, at.acctax_tax_code
		group by rr.ris_last_risk_situation_id,invrisvt_variable_id,invrisvt_tax_id

	end    



    



GO
