-- 1 ---EXECUTION
CALL sp_ft1_donnelemn2_rp (NULL,NULL,'156',NULL,'2022-01-01','2022-12-31',30,'AVANT');   -- FT1B :DONNEES ELEMENTAIRES  -- 30 687 688 / CHARGEMENT* -- 



CALL sp_ft1_donnelemn2_rp (NULL,NULL,'229',NULL,'2022-04-01','2023-01-31',30,'AVANT');   -- FT1B :DONNEES ELEMENTAIRES  -- 30 687 688 / CHARGEMENT* -- 






CALL sp_ft5_evolution_dep (NULL,NULL,156,NULL,'2022-01-01','2022-12-31',30,'AVANT');  -- FT5 :EVOLUTION / POLICE  --56 252 873



CALL sp_ft5_evolution_dep (NULL,NULL,229,NULL,'2022-04-01','2023-01-31',30,'AVANT'); -- FT5 :EVOLUTION / GARANT
		
		
		
-- 2 --CONTROLE
		
			DROP TEMPORARY TABLE IF EXISTS temp_prime;	 
			CREATE TEMPORARY TABLE temp_prime 
			SELECT 1 AS orde,'PRIME NETTE' AS libelle,idPolice,numeroPolice,libellePolice,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='1' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE()) THEN SUM(prime_1) ELSE '0' END AS JANVIER,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='2' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_2) ELSE '0' END AS FEVRIER,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='3' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_3) ELSE '0' END AS MARS,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='4' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_4) ELSE '0' END AS AVRIL,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='5' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_5) ELSE '0' END AS MAI,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='6' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_6) ELSE '0' END AS JUIN,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='7' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_7) ELSE '0' END AS JUILLET,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='8' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_8) ELSE '0' END AS AOUT,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='9' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_9) ELSE '0' END AS SEPTEMBRE,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='10' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_10) ELSE '0' END AS OCTOBRE,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='11' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_11) ELSE '0' END AS NOVEMBRE,
			CASE WHEN MONTH(LEFT(p_datefin,10))>='12' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_12) ELSE '0' END AS DECEMBRE,
			CASE WHEN MONTH(LEFT(p_datefin,10))='1' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_1) 
			     WHEN MONTH(LEFT(p_datefin,10))='2' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_2)+SUM(prime_1) 
			     WHEN MONTH(LEFT(p_datefin,10))='3' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_3)+ SUM(prime_2)+SUM(prime_1) 
			     WHEN MONTH(LEFT(p_datefin,10))='4' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_4)+SUM(prime_3)+ SUM(prime_2)+SUM(prime_1) 
			     WHEN MONTH(LEFT(p_datefin,10))='5' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_5)+SUM(prime_4)+SUM(prime_3)+ SUM(prime_2)+SUM(prime_1) 
			     WHEN MONTH(LEFT(p_datefin,10))='6' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_6)+SUM(prime_5)+SUM(prime_4)+SUM(prime_3)+ SUM(prime_2)+SUM(prime_1)  
			     WHEN MONTH(LEFT(p_datefin,10))='7' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_7)+SUM(prime_6)+SUM(prime_5)+SUM(prime_4)+SUM(prime_3)+ SUM(prime_2)+SUM(prime_1) 
			     WHEN MONTH(LEFT(p_datefin,10))='8' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_8)+SUM(prime_7)+SUM(prime_6)+SUM(prime_5)+SUM(prime_4)+SUM(prime_3)+ SUM(prime_2)+SUM(prime_1) 
			     WHEN MONTH(LEFT(p_datefin,10))='9' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_9)+SUM(prime_8)+SUM(prime_7)+SUM(prime_6)+SUM(prime_5)+SUM(prime_4)+SUM(prime_3)+ SUM(prime_2)+SUM(prime_1) 
			     WHEN MONTH(LEFT(p_datefin,10))='10' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_10)+SUM(prime_9)+SUM(prime_8)+SUM(prime_7)+SUM(prime_6)+SUM(prime_5)+SUM(prime_4)+SUM(prime_3)+ SUM(prime_2)+SUM(prime_1) 
			     WHEN MONTH(LEFT(p_datefin,10))='11' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_11)+SUM(prime_10)+SUM(prime_9)+SUM(prime_8)+SUM(prime_7)+SUM(prime_6)+SUM(prime_5)+SUM(prime_4)+SUM(prime_3)+ SUM(prime_2)+SUM(prime_1)
			     WHEN MONTH(LEFT(p_datefin,10))='12' AND YEAR(LEFT(p_datefin,10))<=YEAR(CURDATE())  THEN SUM(prime_12)+SUM(prime_11)+SUM(prime_10)+SUM(prime_9)+SUM(prime_8)+SUM(prime_7)+SUM(prime_6)+SUM(prime_5)+SUM(prime_4)+SUM(prime_3)+ SUM(prime_2)+SUM(prime_1) ELSE 0 END AS TOTAUX
			FROM temp_sp GROUP BY idPolice,numeroPolice,libellePolice ;
			
			
			
			

			SELECT * FROM temp_sp
	
			SELECT * FROM temp_production
			
		DROP TEMPORARY TABLE IF EXISTS temp_sp;	
		DROP TEMPORARY TABLE IF EXISTS temp_prime;
		DROP TEMPORARY TABLE IF EXISTS temp_production;