DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_inscription`$$

CREATE PROCEDURE `Import_inscription`(var_line NVARCHAR(200), var_tableau_id BIGINT)
BEGIN
	
DECLARE var_insc_id BIGINT;
	DECLARE var_joue_id BIGINT;
	DECLARE var_tour_id BIGINT;
	DECLARE var_insc_nb_dossard INT;
	DECLARE var_insc_nb_rang_poule INT;
	DECLARE var_insc_nb_point_tour INT;
	DECLARE var_insc_bl_absent SMALLINT;
	DECLARE var_poul_id BIGINT;
	DECLARE var_tour_id_prec BIGINT;
	DECLARE var_insc_nb_rang_tour INT;
	DECLARE var_insc_nm_rang_tableau INT;
	DECLARE var_insc_cd_lettre CHAR(1);
	DECLARE var_tab_id BIGINT;
	DECLARE var_insc_bl_tete_serie SMALLINT;
	
	DECLARE var_test_poul BIGINT;
	
	BEGIN 
		SET var_insc_id = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_joue_id = (RTRIM(SUBSTRING(var_line, 16, 15))  );
		SET var_tour_id = (RTRIM(SUBSTRING(var_line, 31, 15))  );
		SET var_insc_nb_dossard = (RTRIM(SUBSTRING(var_line, 46, 8))  );
		SET var_insc_nb_rang_poule = (RTRIM(SUBSTRING(var_line, 54, 8))  );
		SET var_insc_nb_point_tour = (NULLIF(RTRIM(SUBSTRING(var_line, 62, 8)), '')  );
		SET var_insc_bl_absent = (RTRIM(SUBSTRING(var_line, 70, 1))  );
		SET var_poul_id = (NULLIF(RTRIM(SUBSTRING(var_line, 71, 15)), '')  );
		SET var_tour_id_prec = NULL;
		SET var_insc_nb_rang_tour = (NULLIF(RTRIM(SUBSTRING(var_line, 101, 8)), '')  );
		SET var_insc_nm_rang_tableau = (NULLIF(RTRIM(SUBSTRING(var_line, 109, 8)), '')  );
		SET var_insc_cd_lettre = NULLIF(RTRIM(SUBSTRING(var_line, 117, 1)), '');
		SET var_insc_bl_tete_serie = NULL;
		
		-- Verifier l existence de la poule (sinon inserer NULL)
		SELECT COUNT(1) INTO var_test_poul FROM POULE WHERE POUL_ID = var_poul_id;
		IF var_test_poul = 0 THEN
			SET var_poul_id = NULL;
		END IF;
		
		UPDATE INSCRIPTION SET JOUE_ID = var_joue_id,
								TOUR_ID = var_tour_id,
								INSC_NB_DOSSARD = var_insc_nb_dossard,
								INSC_NB_RANG_POULE = var_insc_nb_rang_poule,
								INSC_NB_POINT_TOUR = var_insc_nb_point_tour,
								INSC_BL_ABSENT = var_insc_bl_absent,
								POUL_ID = var_poul_id,
								TOUR_ID_PREC = var_tour_id_prec,
								INSC_NB_RANG_TOUR = var_insc_nb_rang_tour,
								INSC_NM_RANG_TABLEAU = var_insc_nm_rang_tableau,
								INSC_CD_LETTRE = var_insc_cd_lettre,
								TAB_ID = var_tableau_id,
								INSC_BL_TETE_SERIE = var_insc_bl_tete_serie
							WHERE INSC_ID = var_insc_id;
		
		IF ROW_COUNT() = 0 THEN
		
			
			INSERT INTO INSCRIPTION (INSC_ID, JOUE_ID, TOUR_ID, INSC_NB_DOSSARD, INSC_NB_RANG_POULE, INSC_NB_POINT_TOUR, INSC_BL_ABSENT, POUL_ID, TOUR_ID_PREC,
										INSC_NB_RANG_TOUR, INSC_NM_RANG_TABLEAU, INSC_CD_LETTRE, TAB_ID, INSC_BL_TETE_SERIE)
					VALUES (var_insc_id, var_joue_id, var_tour_id, var_insc_nb_dossard, var_insc_nb_rang_poule, var_insc_nb_point_tour, var_insc_bl_absent, var_poul_id, var_tour_id_prec,
										var_insc_nb_rang_tour, var_insc_nm_rang_tableau, var_insc_cd_lettre, var_tableau_id, var_insc_bl_tete_serie);
					
		END IF;
	END;
END$$

DELIMITER ;