-- SELECT 
-- MONTH('2022-10-15'), MONTH(CURDATE()),CONCAT(DATE_ADD(LAST_DAY(CURDATE()), INTERVAL -2 MONTH),' ','12:00:00') ,
-- THEN CONCAT(DATE_ADD(LAST_DAY(CURDATE()), INTERVAL -1 MONTH),' ','12:00:00') ,
-- MONTH(A.dateConsultation)<MONTH(CURDATE()))THEN CONCAT(DATE_ADD(LAST_DAY(CURDATE()), INTERVAL -1 MONTH),' ','12:00:00') 






-- CALL  `sp_rapport_sp_police`(156,'2022-11-30','lioneL')

-- CALL  `sp_rapport_sp_police`(156,'2022-12-02','lioneL')

	SELECT 
		dateEffet,
		fn_id_entetecontrat_police(156, '2022-11-30') 
	INTO 
		@dateEffetPolice,
		@idEntetecontrat
	FROM police 
	WHERE (id=156) 
	LIMIT 1;
	
	SELECT 0 INTO @consommationPolice;
	
	SELECT YEAR('2022-11-30')  INTO @exerciceAnalyse;
	
	SELECT CONCAT(@exerciceAnalyse,"-01-01")  INTO @debutExercice;
	 
	DROP TEMPORARY TABLE IF EXISTS temp_beneficiaireprime;
	CREATE TEMPORARY TABLE temp_beneficiaireprime
	SELECT
		A.exercice,
		A.exercieReference,
		A.idEntetecontrat,		
		A.idPolice,
		A.idCollege,
		A.codeProduit,
		A.codeLienParente,
		A.idAdherent,
		A.idBeneficiaire, 
		A.dateEffet,
		A.dateEffetBeneficiaire,
		A.dateEffetAdherent,
		A.dateEffetPolice, 
		A.dateFinPolice, 
		(DATEDIFF(A.dateFinPolice, A.dateEffet)+1) AS dureePolice,
		A.primeNetteStat,
		"0" AS sorti,
		"0000-00-00" AS dateSortie,
		"0" AS ristourne
	FROM beneficiaireprime A 
	WHERE (A.idPolice=156)
	AND (A.idEntetecontrat=@idEntetecontrat)
	AND ('2022-11-30' BETWEEN A.dateEffetPolice AND A.dateFinPolice)
	AND (A.dateEffet<='2022-11-30')
	AND (A.dateEffetBeneficiaire>=A.dateEffetPolice)
	AND (A.dateEffet>=A.dateEffetBeneficiaire)
	AND (A.supprime!="1");
	 
	UPDATE temp_beneficiaireprime A
	JOIN beneficiaire B ON (B.id=A.idBeneficiaire)
	SET 
		A.sorti 	= "1", 
		A.dateSortie 	= B.dateSortie
	WHERE (B.sorti="1" AND B.dateSortie<='2022-11-30');
	  
	DROP TEMPORARY TABLE IF EXISTS temp_ristourne;
	CREATE TEMPORARY TABLE temp_ristourne
	SELECT DISTINCT idBeneficiaire FROM temp_beneficiaireprime A
	WHERE (sorti="1" AND primeNetteStat<0);
	 
	UPDATE temp_beneficiaireprime A
	JOIN temp_ristourne B ON (B.idBeneficiaire=A.idBeneficiaire)
	SET A.ristourne="1";
	 
	DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_s;	 
	CREATE TEMPORARY TABLE temp_beneficiaire_s
	(INDEX(idBeneficiaire))
	SELECT
		A.exercice,
		A.exercieReference,
		A.idEntetecontrat,
		A.idPolice,
		A.idAdherent,
		A.idBeneficiaire,
		A.dateEffetBeneficiaire AS dateEffet,
		A.dateEffetPolice, 
		A.dateFinPolice, 
		A.dureePolice,
		SUM(A.primeNetteStat) AS primeNetteStat,
		A.sorti,
		A.dateSortie,
		A.ristourne
	FROM temp_beneficiaireprime A 
	GROUP BY 
		A.exercice,
		A.exercieReference,
		A.idEntetecontrat,
		A.idPolice,
		A.idAdherent,
		A.idBeneficiaire,
		A.dateEffetBeneficiaire,
		A.dateEffetPolice, 
		A.dateFinPolice, 
		A.dureePolice,
		A.sorti,
		A.dateSortie,
		A.ristourne;
		
	UPDATE temp_beneficiaire_s A
	JOIN beneficiaire B ON (B.id=A.idBeneficiaire)
	SET 
		A.sorti 	= "1", 
		A.dateSortie 	= B.dateSortie
	WHERE (B.sorti="1" AND B.dateSortie<='2022-11-30');
	  
	DROP TEMPORARY TABLE IF EXISTS fn_production_temp;	 
	CREATE TEMPORARY TABLE fn_production_temp
	(INDEX(idBeneficiaire))
	SELECT
		C.exercice, 
		A.idPolice,
		A.idAdherent,
		A.idBeneficiaire,
		A.dateEffet,		
		C.dateEffet AS dateEffetContrat, 
		C.dateFin AS dateFinContrat, 
		C.duree AS dureeContrat,
		A.dateEffetPolice, 
		A.dateFinPolice, 
		A.dureePolice,
		A.primeNetteStat,
		"0.000000000" ratioContrat,
		CAST("0" AS UNSIGNED) dureeAnalyse,
		"0.000000000" ratioAnalyse, 
		CAST("0" AS UNSIGNED) primeContrat, 
		CAST("0" AS UNSIGNED) primeAnalyse,
		A.sorti,
		A.dateSortie,
		A.ristourne,
		A.idEntetecontrat
	FROM temp_beneficiaire_s A 
	JOIN contrat C ON (C.idPolice=A.idPolice) 
	AND (C.idEntetecontrat=A.idEntetecontrat)
	AND (C.exercice>=A.exercice) 	
	WHERE (C.dateEffet<='2022-11-30')
	AND (C.dateEffet>=A.dateEffetPolice)
	AND (C.dateEffet<='2022-11-30');
	 
	UPDATE fn_production_temp SET 
		dateEffetContrat = dateEffet
	WHERE (dateEffetContrat<dateEffet)
	AND (YEAR(dateEffetContrat)=YEAR(dateEffet));
	 
	UPDATE fn_production_temp SET dureeContrat = (DATEDIFF(dateFinContrat, dateEffetContrat)+1);
	 
	UPDATE fn_production_temp SET 
		dureeAnalyse	= dureeContrat
	WHERE ('2022-11-30'>=dateFinContrat);
	 
	UPDATE fn_production_temp SET 
		dureeAnalyse	= (DATEDIFF('2022-11-30', dateEffetContrat)+1)
	WHERE ('2022-11-30'<dateFinContrat)
	AND (sorti!="1");
	
	
	DROP TEMPORARY TABLE IF EXISTS temp_dure_conrat_sorties;
	CREATE TEMPORARY TABLE temp_dure_conrat_sorties
	SELECT 
		idBeneficiaire,
		SUM(dureeContrat) AS dureePolice
	FROM fn_production_temp A
	WHERE (sorti = "1")
	AND (ristourne="0")
	GROUP BY idBeneficiaire;
	UPDATE fn_production_temp SET 
		dureeAnalyse	= dureeContrat
	WHERE (sorti="1" AND ristourne!="1");
	UPDATE fn_production_temp A
	JOIN temp_dure_conrat_sorties B ON (B.idBeneficiaire=A.idBeneficiaire)
	SET 
		A.dureePolice	= B.dureePolice
	WHERE (sorti="1" AND ristourne!="1");
	
	
	UPDATE fn_production_temp SET 
		ratioContrat	= dureeContrat/dureePolice,
		ratioAnalyse	= dureeAnalyse/dureePolice
	WHERE (dureePolice>"0");
	UPDATE fn_production_temp SET 
		ratioAnalyse = ratioContrat
	WHERE (ristourne="1");
	
	UPDATE fn_production_temp SET 
		primeContrat=primeNetteStat*ratioContrat, 
		primeAnalyse=primeNetteStat*ratioAnalyse;
		 
	DROP TEMPORARY TABLE IF EXISTS temp_prime_famille_s;
	CREATE TEMPORARY TABLE temp_prime_famille_s
	SELECT
		A.idAdherent,
		SUM(A.primeNetteStat) AS primeNetteStat,
		SUM(A.primeNetteStat) AS primeAnnuelleFamille
	FROM temp_beneficiaire_s A 	
	
	WHERE ABS(A.primeNetteStat)!=0
	GROUP BY A.idAdherent;
	 
	DROP TEMPORARY TABLE IF EXISTS temp_rapportsp_prod;	 
	CREATE TEMPORARY TABLE temp_rapportsp_prod
	SELECT
		idPolice,
		exercice, 		
		SUM(primeContrat) AS primeContrat,
		SUM(primeAnalyse) AS primeAnalyse
	FROM fn_production_temp
	GROUP BY idPolice, exercice;
 
	
	DROP TEMPORARY TABLE IF EXISTS temp_rapportsp_prod_cumul;
	 
	CREATE TEMPORARY TABLE temp_rapportsp_prod_cumul
	SELECT
		idPolice,
		SUM(IFNULL(primeContrat, 0)) AS primeContrat,
		SUM(IFNULL(primeAnalyse, 0)) AS primeAnalyse
	FROM temp_rapportsp_prod
	GROUP BY idPolice;
	 
	
	
	DROP TEMPORARY TABLE IF EXISTS temp_rapportsp_sin_cumul;	 
	CREATE TEMPORARY TABLE temp_rapportsp_sin_cumul
	SELECT
		SUM(IFNULL(montantApayer, 0)) AS montantApayer
	FROM p_facture A
	JOIN beneficiaire Bn ON (Bn.id=A.idBeneficiaire)
	JOIN temp_rapportsp_prod B ON (B.exercice=YEAR(A.dateSysteme) AND B.idPolice=Bn.idPolice)
	WHERE (Bn.idPolice=156) 	AND (LEFT(A.dateSysteme,10) BETWEEN @dateEffetPolice AND '2022-11-30');
	
	
	SELECT SUM(IFNULL(primeAnnuelleFamille, 0) ) INTO @primestat FROM temp_prime_famille_s;
	 
	SELECT SUM(IFNULL(primeContrat, 0) ) INTO @primeContrat FROM temp_rapportsp_prod;
	SELECT SUM(IFNULL(primeAnalyse, 0) ) INTO @primeAnalyseTotal FROM temp_rapportsp_prod;
	 
	SELECT SUM(IFNULL(montantApayer, 0) ) INTO @montantApayerTotal FROM temp_rapportsp_sin_cumul;
	 
	SELECT ROUND(100*IFNULL((@montantApayerTotal/@primeAnalyseTotal), 0),2) INTO @rapportSpTotal;
	 
	SELECT ROUND(100*IFNULL((@montantApayerTotal/@primestat), 0),2) INTO @rapportSpGeneral;
	 
	UPDATE avenant_temp A SET 
		rapportSp = @rapportSpTotal
	WHERE (A.user=p_user);
	
	UPDATE emission_temp A SET 
		rapportSp = @rapportSpTotal
	WHERE (A.user=p_user);
	 
	SELECT 	
		156 AS idPolice,
		dateEffet, 
		dateEcheance, 
		(DATEDIFF(dateEcheance, dateEffet)+1) 		AS dureePolice,
		fn_duree_acquise_police(156, '2022-11-30') duree_acquise,
		fn_ratio_acquise_police(156, '2022-11-30') ratio_acquise,
		ROUND(IFNULL(@primestat, 0),0)  		AS primestat,
		ROUND(IFNULL(@primeContrat, 0),0)  		AS primeContrat,
		ROUND(IFNULL(@primeAnalyseTotal, 0),0)  	AS prime_acquise,
		ROUND(IFNULL(@montantApayerTotal, 0),0)  	AS consommation,
		IFNULL(@rapportSpTotal, 0) 			AS rapportsp,
		IFNULL(@rapportSpGeneral, 0) 			AS rapportspgeneral,
		tauxRistourne
	FROM police 
	WHERE (id=156);
	
	DROP TEMPORARY TABLE IF EXISTS temp_rapportsp_prod;	 
	DROP TEMPORARY TABLE IF EXISTS temp_rapportsp_sin_cumul;	
	DROP TEMPORARY TABLE IF EXISTS temp_rapportsp_prod_cumul;	 
	DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_s;	 
	DROP TEMPORARY TABLE IF EXISTS fn_production_temp;	 
	DROP TEMPORARY TABLE IF EXISTS temp_beneficiaireprime;	 
	DROP TEMPORARY TABLE IF EXISTS temp_ristourne;