SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE [dbo].[p_ext_policies_replace]
	@source_product_code ais_code_short,
	@new_product_code ais_code_short,
	@replace_date DATE,
	@cancel_source_policy BIT = 1,
	@cancel_mvt_code ais_code_short, 
	@cancel_db_invoices_effect_after_replace_date BIT = 1,
	@reimbourse_en_invoices_effect_after_replace_date BIT = 1,
	@prorate_invoices_in_effect BIT = 1

AS
	SET NOCOUNT ON;

	DECLARE @min_table_id INT
	DECLARE @max_table_id INT 
	DECLARE @table_id INT
	DECLARE @policy_id INT
	DECLARE @policy_situation_id INT 
	DECLARE @policy_number ais_code_long

	DECLARE @lot_id INT 
	DECLARE @error_code ais_code_long

	/*
	*	Création lot de contrats
	*/
	EXEC p_ext_policy_replace_create_lot @source_product_code = @source_product_code
										,@new_product_code = @new_product_code
										,@lot_id = @lot_id OUTPUT
										,@error_code = @error_code OUTPUT

	DECLARE @policies TABLE(id INT IDENTITY(1,1), policy_id INT, policy_situation_id INT, policy_number ais_code_long)
	 

	/*
	*	Recherche contrats à traiter
	*/

	INSERT INTO @policies (	policy_id, policy_situation_id,						policy_number)
	SELECT					pps.pols_policy_id, pps.pols_policy_situation_id,	pps.pols_number
	FROM pol_policies pp
		INNER JOIN pol_policies_situations pps ON pp.pol_last_policy_situation_id = pps.pols_policy_situation_id
		INNER JOIN pol_mvts pm ON pps.pols_mvt_code = pm.pmvt_mvt_code
	WHERE		pp.pol_product_code = @source_product_code
			AND pm.pmvt_type_code NOT IN ('QUOT', 'INC', 'TER')
			AND pps.pols_next_renewal_date > @replace_date

	DECLARE @debug_policies XML = (SELECT * FROM @policies FOR XML AUTO ,ROOT('r'))

	
	/*
	*	Traiter contrat par contrat
	*/
	SELECT	@min_table_id = MIN(id),
			@max_table_id = MAX(id)
	FROM @policies

	IF @min_table_id IS NULL 
	BEGIN
		--rien a faire
		PRINT 'AUCUN CONTRAT A TRAITER!!!';
		RETURN  
	END 
	ELSE
	BEGIN

		PRINT CONVERT(VARCHAR, @max_table_id - @min_table_id + 1) + ' CONTRATS A TRAITER!!!';

		SET  @table_id = @min_table_id

		WHILE @table_id <= @max_table_id
		BEGIN

			SELECT	@policy_id = p.policy_id,
					@policy_situation_id = p.policy_situation_id,
					@policy_number = p.policy_number
			FROM @policies p
			WHERE p.id = @table_id

			/*
			*	Traiter le contrat en cours
			*/

			EXEC p_ext_policy_replace @source_policy_situation_id = @policy_situation_id
									 ,@destination_product_code = @new_product_code
									 ,@replace_date = @replace_date
									 ,@cancel_source_policy = @cancel_source_policy
									 ,@cancel_endorsmentpattern_code = @cancel_mvt_code
									 ,@cancel_db_invoices_effect_after_replace_date = @cancel_db_invoices_effect_after_replace_date
									 ,@reimbourse_en_invoices_effect_after_replace_date = @reimbourse_en_invoices_effect_after_replace_date
									 ,@prorate_invoices_in_effect = @prorate_invoices_in_effect
									 ,@lot_id = @lot_id

			PRINT ' CONTRATS TRAITES: ' +  CONVERT(VARCHAR, @table_id) + '/' +  CONVERT(VARCHAR, @max_table_id - @min_table_id + 1);
			
			WAITFOR DELAY '00:00:00.100'; --wait 100ms

			SET @table_id = @table_id + 1

		END
	END 

	/*
	*	Mettre à jour le lot
	*/
	UPDATE policies_replaced_lots
	SET prl_end_date = GETDATE()
	WHERE prl_lot_id = @lot_id

	declare @count_ok INT
	declare @count_ko INT

	select	@count_ok = sum(case when prps.prls_transaction_succeded = 1 then 1 else 0 end),
			@count_ko = sum(case when prps.prls_transaction_succeded = 0 then 1 else 0 end)
	from policies_replaced_policies_situations prps
	where prps.prls_lot_id = @lot_id
	
	PRINT 'TRAITEMENT TERMINE, CONTRATS ' + convert(VARCHAR, @count_ok) + ' OK, ' + convert(varchar, @count_ko) + ' KO';

	/*

DECLARE @date_end DATE = '01/06/2018'
SELECT
	riss.riss_number
   ,pols.pols_number
   ,pol.pol_product_code
   ,pols.pols_next_principal_renewal_date
   ,*
FROM ris_risks ris
INNER JOIN ris_risks_situations riss
	ON riss.riss_risk_situation_id = ris.ris_last_risk_situation_id
INNER JOIN pol_policies_situations polsriss
	ON polsriss.pols_policy_situation_id = riss.riss_policy_situation_id
		AND polsriss.pols_mvt_code NOT IN ('QUO')
INNER JOIN pol_policies_situations pols
	ON pols.pols_policy_id = polsriss.pols_policy_id
		AND polsriss.pols_period_number = pols.pols_period_number
INNER JOIN pol_policies pol
	ON pol.pol_last_policy_situation_id = pols.pols_policy_situation_id
WHERE ISNULL(riss.riss_retire_date, DATEADD(YEAR, 1, @date_end)) > @date_end
AND pols.pols_mvt_code NOT IN ('QUO')
AND pol.pol_product_code = 'FLA_TB'
AND pols.pols_next_principal_renewal_date > @date_end
ORDER BY riss.riss_number
, pols.pols_number
*/
GO
