DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Clean_inscription`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Clean_inscription`(var_line BIGINT)
BEGIN
	
DROP TABLE IF EXISTS var_inscription;
	f
CREATE TABLE var_inscription 
( 
    InscriptionId INT
);	
	
	INSERT INTO var_inscription SELECT insc_id FROM inscription WHERE tab_id = var_line;
	
	UPDATE TABLEAU_PARTIE SET INSC_ID = -101 WHERE INSC_ID IN (SELECT InscriptionId FROM var_inscription);
	UPDATE TABLEAU_PARTIE SET INS_INSC_ID = -101 WHERE INS_INSC_ID IN (SELECT InscriptionId FROM var_inscription);
	
	DELETE FROM INSCRIPTION WHERE TAB_ID = var_line;
	
	SELECT * FROM var_inscription;
	DROP TABLE IF EXISTS var_inscription;
	
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_bareme`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_bareme`(var_line NVARCHAR(200))
BEGIN
	
	DECLARE varbar_cd VARCHAR(5);
	DECLARE varbar_lb VARCHAR(32);
	DECLARE varbar_lb_court VARCHAR(5);
	DECLARE varbar_id BIGINT;
	DECLARE varorga_id BIGINT;
	DECLARE varbar_nb_joueur INT;
	
	BEGIN 
		SET varbar_cd = RTRIM(SUBSTRING(var_line, 1, 5));
		SET varbar_lb = RTRIM(SUBSTRING(var_line, 6, 32)) ;
		SET varbar_lb_court = NULLIF(RTRIM(SUBSTRING(var_line, 38, 5)), '');
		SET varbar_id = (RTRIM(SUBSTRING(var_line, 43, 15))  );
		SET varorga_id = (RTRIM(SUBSTRING(var_line, 58, 15))  );
		SET varbar_nb_joueur = (NULLIF(RTRIM(SUBSTRING(var_line, 73, 8)), '')  );
		UPDATE BAREME SET BAR_CD = varbar_cd,
								BAR_LB = varbar_lb, 
								BAR_LB_COURT = varbar_lb_court, 
								BAR_ID = varbar_id, 
								ORGA_ID = varorga_id,
								BAR_NB_JOUEUR = varbar_nb_joueur,
								BL_MAJ = 1
							WHERE BAR_ID = varbar_id;
		
		IF ROW_COUNT() = 0 THEN
		
			INSERT INTO BAREME (BAR_ID, BAR_CD, BAR_LB, BAR_LB_COURT, ORGA_ID, BAR_NB_JOUEUR)
					VALUES (varbar_id, varbar_cd, varbar_lb, varbar_lb_court, varorga_id, varbar_nb_joueur);
		END IF;
	END ;
	
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_bareme_detail`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_bareme_detail`(var_line NVARCHAR(200))
BEGIN
	
	DECLARE dbar_id_details BIGINT;
	DECLARE dbar_nb_place INT;
	DECLARE dbar_nb_point INT;
	DECLARE bar_id BIGINT;
	
	
		SET dbar_id_details = RTRIM(SUBSTRING(var_line, 1, 15))  ;
		SET dbar_nb_place = RTRIM(SUBSTRING(var_line, 16, 8)) ;
		SET dbar_nb_point = RTRIM(SUBSTRING(var_line, 24, 8)) ;
		SET bar_id = RTRIM(SUBSTRING(var_line, 32, 15)) ;
		UPDATE BAREME_DETAIL SET DBAR_NB_PLACE = dbar_nb_place,
								DBAR_NB_POINT = dbar_nb_point, 
								BAR_ID = bar_id, 
								BL_MAJ = 1
							WHERE DBAR_ID_DETAILS = dbar_id_details;
		
		
		IF ROW_COUNT() = 0 THEN
			INSERT INTO BAREME_DETAIL (DBAR_ID_DETAILS, DBAR_NB_PLACE, DBAR_NB_POINT, BAR_ID)
					VALUES (dbar_id_details, dbar_nb_place, dbar_nb_point, bar_id);
		END IF;
    END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_cat`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_cat`(var_line NVARCHAR(200))
BEGIN
	
	DECLARE var_cat_id BIGINT;
	DECLARE var_gcat_cd CHAR(5);
	DECLARE var_cat_lb VARCHAR(32);
	DECLARE var_cat_cd VARCHAR(5);
	DECLARE var_cat_dt_deb DATETIME;
	DECLARE var_cat_dt_fin DATETIME;
	DECLARE var_cat_fg CHAR(1);
	DECLARE var_dem_id BIGINT;
	
	BEGIN 
		SET var_cat_id = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_gcat_cd = RTRIM(SUBSTRING(var_line, 16, 5));
		SET var_cat_lb = RTRIM(SUBSTRING(var_line, 21, 32)) ;
		SET var_cat_cd = RTRIM(SUBSTRING(var_line, 53, 5));
		SET var_cat_dt_deb = STR_TO_DATE(NULLIF(RTRIM(SUBSTRING(var_line, 58, 10)), ''),'%d/%m/%Y');
		SET var_cat_dt_fin = STR_TO_DATE(NULLIF(RTRIM(SUBSTRING(var_line, 68, 10)), ''),'%d/%m/%Y');
		SET var_cat_fg = RTRIM(SUBSTRING(var_line, 78, 1));
		SET var_dem_id = (NULLIF(RTRIM(SUBSTRING(var_line, 79, 15)), '')  );
		UPDATE CAT SET GCAT_CD = var_gcat_cd,
								CAT_LB = var_cat_lb,
								CAT_CD = var_cat_cd, 
								CAT_DT_DEB = var_cat_dt_deb, 
								CAT_DT_FIN = var_cat_dt_fin, 
								CAT_FG = var_cat_fg,
								DEM_ID = var_dem_id,
								BL_MAJ = 1
							WHERE CAT_ID = var_cat_id;
		
		IF ROW_COUNT() = 0 THEN
		
			INSERT INTO CAT (CAT_ID, GCAT_CD, CAT_LB, CAT_CD, CAT_DT_DEB, CAT_DT_FIN, CAT_FG, DEM_ID)
					VALUES (var_cat_id, var_gcat_cd, var_cat_lb, var_cat_cd, var_cat_dt_deb, var_cat_dt_fin, var_cat_fg, var_dem_id);
		END IF;
	END; 
	
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_cat_age_group`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_cat_age_group`(var_line NVARCHAR(200))
BEGIN
	DECLARE var_gcat_cd CHAR(5);
	DECLARE var_gcat_lb_court VARCHAR(5);
	DECLARE var_gcat_lb VARCHAR(32);
	
	BEGIN
		SET var_gcat_cd = RTRIM(SUBSTRING(var_line, 1, 5));
		SET var_gcat_lb_court = RTRIM(SUBSTRING(var_line, 6, 5));
		SET var_gcat_lb = RTRIM(SUBSTRING(var_line, 9, 32));
		UPDATE CAT_AGE_GROUP SET GCAT_LB_COURT = var_gcat_lb_court,
								GCAT_LB = var_gcat_lb, 
								BL_MAJ = 1
							WHERE GCAT_CD = var_gcat_cd;
		
		IF ROW_COUNT() = 0 THEN
		
			INSERT INTO CAT_AGE_GROUP (GCAT_CD, GCAT_LB_COURT, GCAT_LB)
					VALUES (var_gcat_cd, var_gcat_lb_court, var_gcat_lb);
		END IF;
	END;
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_cat_sport`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_cat_sport`(var_line NVARCHAR(200))
BEGIN
	
	DECLARE var_cat_sport_id BIGINT;
	DECLARE var_cat_lb VARCHAR(32);
	DECLARE var_cat_cd VARCHAR(5);
	DECLARE var_cat_dt_deb DATETIME;
	DECLARE var_cat_dt_fin DATETIME;
	DECLARE var_cat_fg CHAR(1);
	
	BEGIN 
		SET var_cat_sport_id = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_cat_lb = RTRIM(SUBSTRING(var_line, 16, 32)) ;
		SET var_cat_cd = RTRIM(SUBSTRING(var_line, 48, 5));
		SET var_cat_dt_deb = STR_TO_DATE(NULLIF(RTRIM(SUBSTRING(var_line, 53, 10)), ''),'%d/%m/%Y');
		SET var_cat_dt_fin = STR_TO_DATE(NULLIF(RTRIM(SUBSTRING(var_line, 63, 10)), ''),'%d/%m/%Y');
		SET var_cat_fg = RTRIM(SUBSTRING(var_line, 73, 1));
		
		UPDATE CAT_SPORT SET CAT_LB = var_cat_lb,
								CAT_CD = var_cat_cd, 
								CAT_DT_DEB = var_cat_dt_deb, 
								CAT_DT_FIN = var_cat_dt_fin, 
								CAT_FG = var_cat_fg,
								BL_MAJ = 1
							WHERE CAT_SPORT_ID = var_cat_sport_id;
		
		IF ROW_COUNT() = 0 THEN
		
				INSERT INTO CAT_SPORT (CAT_SPORT_ID, CAT_LB, CAT_CD, CAT_DT_DEB, CAT_DT_FIN, CAT_FG)
					VALUES (var_cat_sport_id, var_cat_lb, var_cat_cd, var_cat_dt_deb, var_cat_dt_fin, var_cat_fg);
		END IF;
	END; 
	
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_club`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_club`(var_line NVARCHAR(200))
BEGIN
	DECLARE var_club_id BIGINT;
	DECLARE var_club_nm VARCHAR(8);
	DECLARE var_club_lb_long VARCHAR(32);
	DECLARE var_club_lb_court VARCHAR(15);
	DECLARE var_club_fg CHAR(1);
	DECLARE var_origine_club_id BIGINT;
	
	BEGIN 
		SET var_club_id = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_club_nm = RTRIM(SUBSTRING(var_line, 16, 8));
		SET var_club_lb_long = RTRIM(SUBSTRING(var_line, 24, 32));
		SET var_club_lb_court = RTRIM(SUBSTRING(var_line, 56, 15));
		SET var_club_fg = RTRIM(SUBSTRING(var_line, 71, 1));
		UPDATE CLUB SET CLUB_NM = var_club_nm, 
								CLUB_LB_LONG = var_club_lb_long, 
								CLUB_LB_COURT = var_club_lb_court, 
								CLUB_FG = var_club_fg
							WHERE CLUB_ID = var_club_id;
		
		IF ROW_COUNT() = 0 THEN
		
			INSERT INTO CLUB (CLUB_ID, CLUB_NM, CLUB_LB_LONG, CLUB_LB_COURT, CLUB_FG)
					VALUES (var_club_id, var_club_nm, var_club_lb_long, var_club_lb_court, var_club_fg);
					
			SELECT var_origine_club_id = CLUB_ID FROM CLUB WHERE CLUB_NM = var_club_nm AND CLUB_ID <> var_club_id;
			
			IF var_origine_club_id IS NOT NULL THEN
			
				
				UPDATE LICENCIE SET CLUB_ID = var_club_id WHERE CLUB_ID = var_origine_club_id;
				UPDATE LICENCIE SET CLU_CLUB_ID = var_club_id WHERE CLU_CLUB_ID = var_origine_club_id;
				DELETE FROM CLUB WHERE CLUB_ID = var_origine_club_id;
			END IF;
		END IF;
	END ;
	
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_grille_detail`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_grille_detail`(var_line NVARCHAR(200))
BEGIN
	
	DECLARE var_dgril_id_detail BIGINT;
	DECLARE var_gril_id_renc BIGINT;
	DECLARE var_dgril_nb_journee INT;
	DECLARE var_dgril_nb_equipe1 INT;
	DECLARE var_dgril_nb_equipe2 INT;
	
	BEGIN 
		SET var_dgril_id_detail = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_dgril_nb_journee = (RTRIM(SUBSTRING(var_line, 16, 8))  );
		SET var_dgril_nb_equipe1 = (RTRIM(SUBSTRING(var_line, 24, 8))  );
		SET var_dgril_nb_equipe2 = (RTRIM(SUBSTRING(var_line, 32, 8))  );
		SET var_gril_id_renc = (RTRIM(SUBSTRING(var_line, 40, 15))  );
		UPDATE GRILLE_DETAIL SET GRIL_ID_RENC = var_gril_id_renc,
								DGRIL_NB_JOURNEE = var_dgril_nb_journee, 
								DGRIL_NB_EQUIPE1 = var_dgril_nb_equipe1, 
								DGRIL_NB_EQUIPE2 = var_dgril_nb_equipe2,
								BL_MAJ = 1
							WHERE DGRIL_ID_DETAIL = var_dgril_id_detail;
		
		IF ROW_COUNT() = 0 THEN
		
			INSERT INTO GRILLE_DETAIL (DGRIL_ID_DETAIL, GRIL_ID_RENC, DGRIL_NB_JOURNEE, DGRIL_NB_EQUIPE1, DGRIL_NB_EQUIPE2)
					VALUES (var_dgril_id_detail, var_gril_id_renc, var_dgril_nb_journee, var_dgril_nb_equipe1, var_dgril_nb_equipe2);
		END IF;
	END; 
	
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_grille_rencontre`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_grille_rencontre`(var_line NVARCHAR(200))
BEGIN
	
	DECLARE var_gril_id_renc BIGINT;
	DECLARE var_orga_id BIGINT;
	DECLARE var_gril_cd_renc VARCHAR(5);
	DECLARE var_gril_lb_renc VARCHAR(32);
	DECLARE var_gril_nb_equipe INT;
	DECLARE var_gril_fg CHAR(1);
	DECLARE var_gril_lb_user_modif VARCHAR(15);
	DECLARE var_gril_dt_creation DATETIME;
	
	BEGIN 
		SET var_gril_id_renc = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_orga_id = (RTRIM(SUBSTRING(var_line, 16, 15))  );
		SET var_gril_cd_renc = RTRIM(SUBSTRING(var_line, 31, 5));
		SET var_gril_lb_renc = RTRIM(SUBSTRING(var_line, 36, 32));
		SET var_gril_nb_equipe = (RTRIM(SUBSTRING(var_line, 68, 8))  );
		SET var_gril_fg = RTRIM(SUBSTRING(var_line, 76, 1));
		SET var_gril_lb_user_modif = NULLIF(RTRIM(SUBSTRING(var_line, 82, 15)), '');
		SET var_gril_dt_creation = STR_TO_DATE(NULLIF(RTRIM(SUBSTRING(var_line, 98, 10)), ''),'%d/%m/%Y');
		UPDATE GRILLE_RENCONTRE SET ORGA_ID = var_orga_id,
								GRIL_CD_RENC = var_gril_cd_renc, 
								GRIL_LB_RENC = var_gril_lb_renc, 
								GRIL_NB_EQUIPE = var_gril_nb_equipe,
								GRIL_FG = var_gril_fg,
								GRIL_LB_USER_MODIF = var_gril_lb_user_modif,
								GRIL_DT_CREATION = var_gril_dt_creation,
								BL_MAJ = 1
							WHERE GRIL_ID_RENC = var_gril_id_renc;
		
		IF ROW_COUNT() = 0  THEN
		
			INSERT INTO GRILLE_RENCONTRE (GRIL_ID_RENC, ORGA_ID, GRIL_CD_RENC, GRIL_LB_RENC, GRIL_NB_EQUIPE, GRIL_FG, GRIL_LB_USER_MODIF, GRIL_DT_CREATION)
					VALUES (var_gril_id_renc, var_orga_id, var_gril_cd_renc, var_gril_lb_renc, var_gril_nb_equipe, var_gril_fg, var_gril_lb_user_modif, var_gril_dt_creation);
		END IF;
	END; 
	
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_inscription`$$

CREATE DEFINER=`root`@`localhost` 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;
	
	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;
		
		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 ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_joueur`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_joueur`(var_line NVARCHAR(200))
BEGIN
	
	DECLARE var_joue_id BIGINT;
	DECLARE var_eprv_id BIGINT;
	DECLARE var_lic_id BIGINT;
	DECLARE var_joue_nb_point_total VARCHAR(64);
	DECLARE var_joue_bl_forfait_gene INT;
	DECLARE var_joue_lb_tri_point VARCHAR(64);
	DECLARE var_origine_joue_id BIGINT;
	
	BEGIN 
		SET var_joue_id = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_eprv_id = (RTRIM(SUBSTRING(var_line, 16, 15))  );
		SET var_lic_id = (RTRIM(SUBSTRING(var_line, 31, 15))  );
		SET var_joue_nb_point_total = RTRIM(SUBSTRING(var_line, 46, 64));
		SET var_joue_bl_forfait_gene = (RTRIM(SUBSTRING(var_line, 110, 8))  );
		SET var_joue_lb_tri_point = RTRIM(SUBSTRING(var_line, 118, 64));
		UPDATE JOUEUR SET EPRV_ID = var_eprv_id,
								LIC_ID = var_lic_id,
								JOUE_NB_POINT_TOTAL = var_joue_nb_point_total,
								JOUE_BL_FORFAIT_GENE = var_joue_bl_forfait_gene,
								JOUE_LB_TRI_POINT = var_joue_lb_tri_point
							WHERE JOUE_ID = var_joue_id;
		
		IF ROW_COUNT() = 0 THEN
		
			INSERT INTO JOUEUR (JOUE_ID, EPRV_ID, LIC_ID, JOUE_NB_POINT_TOTAL, JOUE_BL_FORFAIT_GENE, JOUE_LB_TRI_POINT)
					VALUES (var_joue_id, var_eprv_id, var_lic_id, var_joue_nb_point_total, var_joue_bl_forfait_gene, var_joue_lb_tri_point);
					
			SELECT var_origine_joue_id = JOUE_ID FROM JOUEUR WHERE EPRV_ID = var_eprv_id AND LIC_ID = var_lic_id AND JOUE_ID <> var_joue_id;
			
			IF var_origine_joue_id IS NOT NULL THEN						
				UPDATE INSCRIPTION SET JOUE_ID = var_joue_id WHERE JOUE_ID = var_origine_joue_id;
				DELETE FROM JOUEUR WHERE JOUE_ID = var_origine_joue_id;
			END IF;
		END IF;
	END;
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_licencie`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_licencie`(var_line NVARCHAR(500))
BEGIN
		
DECLARE return_code INT;
	
DECLARE var_lic_id BIGINT;
DECLARE cat_id BIGINT;
DECLARE club_id BIGINT;
DECLARE clu_club_id BIGINT;
DECLARE tclst_id BIGINT;
DECLARE pers_lb_nom VARCHAR(64);
DECLARE pers_lb_prenom VARCHAR(15);
DECLARE pers_fg_sexe CHAR(1);
DECLARE pers_dt_naissance DATETIME;
DECLARE var_lic_nb_licence VARCHAR(8);
DECLARE lic_fg_nationalite CHAR(1);
DECLARE lic_fg CHAR(1);
DECLARE lic_fg_module CHAR(1);
DECLARE lic_fg_certificat CHAR(1);
DECLARE lic_dt_certificat DATETIME;
DECLARE lic_dt_validation DATETIME;
DECLARE lic_nb_place INT;
DECLARE lic_nb_point NUMERIC(7,3);
DECLARE lic_fg_echelon CHAR(1);
DECLARE lic_nb_point_cf_prec VARCHAR(64);
DECLARE lic_nb_point_total_cf VARCHAR(64);
DECLARE lic_nb_tri_point_cf VARCHAR(64);
DECLARE lic_nb_point_tour_prec_cf VARCHAR(64);
DECLARE lic_bl_local SMALLINT;
DECLARE lic_bl_double SMALLINT;
DECLARE lic_nb_total_point_double NUMERIC(7,3);
DECLARE origine_lic_id BIGINT;
BEGIN
SET return_code = 0;
		
	SET var_lic_id = (RTRIM(SUBSTRING(var_line, 1, 15))  );
	SET cat_id = (NULLIF(RTRIM(SUBSTRING(var_line, 16, 15)), '')  );
	SET club_id = (NULLIF(RTRIM(SUBSTRING(var_line, 31, 15)), '')  );
	SET clu_club_id = (NULLIF(RTRIM(SUBSTRING(var_line, 46, 15)), '')  );
	SET tclst_id = (NULLIF(RTRIM(SUBSTRING(var_line, 61, 15)), '')  );
	SET pers_lb_nom = NULLIF(RTRIM(SUBSTRING(var_line, 76, 64)), '');
	SET pers_lb_prenom = NULLIF(RTRIM(SUBSTRING(var_line, 140, 15)), '');
	SET pers_fg_sexe = NULLIF(RTRIM(SUBSTRING(var_line, 155, 1)), '');
	BEGIN 
		SET pers_dt_naissance = STR_TO_DATE(NULLIF(RTRIM(SUBSTRING(var_line, 156, 10)), ''),'%d/%m/%Y');
		IF pers_dt_naissance IS NULL THEN
			SET return_code = 1;
		END IF;
	END; 
	SET var_lic_nb_licence = NULLIF(RTRIM(SUBSTRING(var_line, 166, 8)), '');
	SET lic_fg_nationalite = NULLIF(RTRIM(SUBSTRING(var_line, 174, 1)), '');
	SET lic_fg = NULLIF(RTRIM(SUBSTRING(var_line, 175, 1)), '');
	SET lic_fg_module = NULLIF(RTRIM(SUBSTRING(var_line, 176, 1)), '');
	SET lic_fg_certificat = NULLIF(RTRIM(SUBSTRING(var_line, 177, 1)), '');
	BEGIN 
		SET lic_dt_certificat = STR_TO_DATE(NULLIF(RTRIM(SUBSTRING(var_line, 178, 10)), ''),'%d/%m/%Y');
		IF lic_dt_certificat IS NULL THEN
			SET return_code = 1;
		END IF;
	END; 
	BEGIN 
		SET lic_dt_validation = STR_TO_DATE(NULLIF(RTRIM(SUBSTRING(var_line, 188, 10)), ''),'%d/%m/%Y');
		IF lic_dt_validation IS NULL THEN
			SET return_code = 1;
		END IF;
	END; 
	SET lic_nb_place =  (NULLIF(RTRIM(SUBSTRING(var_line, 198, 8)), '')  );
	SET lic_nb_point = (NULLIF(RTRIM(SUBSTRING(var_line, 206, 8)), '')  );
	SET lic_fg_echelon = NULLIF(RTRIM(SUBSTRING(var_line, 214, 1)), '');
	SET lic_nb_point_cf_prec = NULLIF(RTRIM(SUBSTRING(var_line, 215, 64)), '');
	SET lic_nb_point_total_cf = NULLIF(RTRIM(SUBSTRING(var_line, 279, 64)), '');
	SET lic_nb_tri_point_cf = NULLIF(RTRIM(SUBSTRING(var_line, 343, 64)), '');
	SET lic_nb_point_tour_prec_cf = NULLIF(RTRIM(SUBSTRING(var_line, 407, 9)), '');
	SET lic_bl_local = 0;
	SET lic_bl_double = NULL;
	SET lic_nb_total_point_double = NULL;		
	UPDATE LICENCIE SET CAT_SPORT_ID = cat_id,
	CLUB_ID = club_id,
	CLU_CLUB_ID = clu_club_id,
	TCLST_ID = tclst_id,
	PERS_LB_NOM = pers_lb_nom,
	PERS_LB_PRENOM = pers_lb_prenom,
	PERS_FG_SEXE = pers_fg_sexe,
	PERS_DT_NAISSANCE = pers_dt_naissance,
	LIC_NB_LICENCE = var_lic_nb_licence,
	LIC_FG_NATIONALITE = lic_fg_nationalite,
	LIC_FG = lic_fg,
	LIC_FG_MODULE = lic_fg_module,
	LIC_FG_CERTIFICAT = lic_fg_certificat,
	LIC_DT_CERTIFICAT = lic_dt_certificat,
	LIC_DT_VALIDATION = lic_dt_validation,
	LIC_NB_PLACE = lic_nb_place,
	LIC_NB_POINT = lic_nb_point,
	LIC_FG_ECHELON = lic_fg_echelon,
	LIC_NB_POINT_CF_PREC = lic_nb_point_cf_prec,
	LIC_NB_POINT_TOTAL_CF = lic_nb_point_total_cf,
	LIC_NB_TRI_POINT_CF = lic_nb_tri_point_cf,
	LIC_NB_POINT_TOUR_PREC_CF = lic_nb_point_tour_prec_cf,
	LIC_BL_LOCAL = lic_bl_local,
	LIC_BL_DOUBLE = lic_bl_double,
	LIC_NB_TOTAL_POINT_DOUBLE = lic_nb_total_point_double
	WHERE LIC_ID = var_lic_id;
	IF ROW_COUNT() = 0 THEN
	BEGIN
	INSERT INTO LICENCIE (LIC_ID, CAT_SPORT_ID, CLUB_ID, CLU_CLUB_ID, TCLST_ID, PERS_LB_NOM, 
				PERS_LB_PRENOM, PERS_FG_SEXE, PERS_DT_NAISSANCE, LIC_NB_LICENCE, 
				LIC_FG_NATIONALITE, LIC_FG, LIC_FG_MODULE, LIC_FG_CERTIFICAT, LIC_DT_CERTIFICAT, 
				LIC_DT_VALIDATION, LIC_NB_PLACE, LIC_NB_POINT, LIC_FG_ECHELON, LIC_NB_POINT_CF_PREC, 
				LIC_NB_POINT_TOTAL_CF, LIC_NB_TRI_POINT_CF, LIC_NB_POINT_TOUR_PREC_CF, LIC_BL_LOCAL, 
				LIC_BL_DOUBLE, LIC_NB_TOTAL_POINT_DOUBLE)
			VALUES (var_lic_id, cat_id, club_id, clu_club_id, tclst_id, pers_lb_nom, 
				pers_lb_prenom, pers_fg_sexe, pers_dt_naissance, var_lic_nb_licence, 
				lic_fg_nationalite, lic_fg, lic_fg_module, lic_fg_certificat, lic_dt_certificat, 
				lic_dt_validation, lic_nb_place, lic_nb_point, lic_fg_echelon, lic_nb_point_cf_prec, 
				lic_nb_point_total_cf, lic_nb_tri_point_cf, lic_nb_point_tour_prec_cf, lic_bl_local, 
				lic_bl_double, lic_nb_total_point_double);
				
	
			  SET origine_lic_id = (SELECT LIC_ID FROM LICENCIE WHERE LIC_NB_LICENCE = var_lic_nb_licence AND LIC_ID <> var_lic_id);
		
			IF origine_lic_id IS NOT NULL THEN
		
				UPDATE PARTIE SET LIC_ID = lic_id WHERE LIC_ID = origine_lic_id;
				UPDATE PARTIE SET LIC_LIC_ID = lic_id WHERE LIC_LIC_ID = origine_lic_id;
				UPDATE JOUEUR SET LIC_ID = lic_id WHERE LIC_ID = origine_lic_id;
				UPDATE CLASSEMENT SET LIC_ID = lic_id WHERE LIC_ID = origine_lic_id;
				
				DELETE FROM LICENCIE WHERE LIC_ID = origine_lic_id;
			END IF;
	
	END;
	END IF;
	SELECT  return_code;
END;
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_niveau_ref`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_niveau_ref`(var_line NVARCHAR(200))
BEGIN
	
	DECLARE var_nivref_id BIGINT;
	DECLARE var_tabref_id BIGINT;
	DECLARE var_nivref_cd VARCHAR(5);
	DECLARE var_nivref_lb VARCHAR(32);
	DECLARE var_nivref_nb INT;
	DECLARE var_nivref_bl_depart SMALLINT;
	DECLARE var_nivref_nivref_id BIGINT;
	
	BEGIN 
		SET var_nivref_id = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_tabref_id = (RTRIM(SUBSTRING(var_line, 16, 15))  );
		SET var_nivref_cd = RTRIM(SUBSTRING(var_line, 31, 5));
		SET var_nivref_lb = RTRIM(SUBSTRING(var_line,36, 32));
		SET var_nivref_nb = (RTRIM(SUBSTRING(var_line, 68, 8))  );
		SET var_nivref_bl_depart = (NULLIF(RTRIM(SUBSTRING(var_line, 76, 1)), '') );
		SET var_nivref_nivref_id = (NULLIF(RTRIM(SUBSTRING(var_line, 77, 15)), '')  );
		UPDATE NIVEAU_REF SET TABREF_ID = var_tabref_id,
								NIVREF_CD = var_nivref_cd,
								NIVREF_LB = var_nivref_lb,
								NIVREF_NB = var_nivref_nb,
								NIVREF_BL_DEPART = var_nivref_bl_depart,
								NIVREF_NIVREF_ID = var_nivref_nivref_id,
								BL_MAJ = 1
							WHERE NIVREF_ID = var_nivref_id;
		
		IF ROW_COUNT() = 0 THEN
		
			INSERT INTO NIVEAU_REF (NIVREF_ID, TABREF_ID, NIVREF_CD, NIVREF_LB, NIVREF_NB, NIVREF_BL_DEPART, NIVREF_NIVREF_ID)
					VALUES (var_nivref_id, var_tabref_id, var_nivref_cd, var_nivref_lb, var_nivref_nb, var_nivref_bl_depart, var_nivref_nivref_id);
		END IF;
	END; 
	
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_organisme`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_organisme`(var_line NVARCHAR(200))
BEGIN
	
	DECLARE var_orga_id BIGINT;
	DECLARE var_org_orga_id BIGINT;
	DECLARE var_orga_lb VARCHAR(32);
	DECLARE var_orga_cd VARCHAR(5);
	DECLARE var_orga_fg CHAR(1);
	DECLARE var_orga_bl_module SMALLINT;
	
	BEGIN 
		SET var_orga_id = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_org_orga_id = (NULLIF(RTRIM(SUBSTRING(var_line, 16, 15)), '')  );
		SET var_orga_lb = RTRIM(SUBSTRING(var_line, 31, 32));
		SET var_orga_cd = RTRIM(SUBSTRING(var_line, 63, 5));
		SET var_orga_fg = RTRIM(SUBSTRING(var_line, 68, 1));
		SET var_orga_bl_module = (RTRIM(SUBSTRING(var_line, 69, 1))  );
		UPDATE ORGANISME SET ORG_ORGA_ID = var_org_orga_id,
								ORGA_LB = var_orga_lb, 
								ORGA_CD = var_orga_cd, 
								ORGA_FG = var_orga_fg, 
								ORGA_BL_MODULE = var_orga_bl_module,
								BL_MAJ = 1
							WHERE ORGA_ID = var_orga_id;
		
		IF ROW_COUNT() = 0 THEN
		
			INSERT INTO ORGANISME (ORGA_ID, ORG_ORGA_ID, ORGA_LB, ORGA_CD, ORGA_FG, ORGA_BL_MODULE)
					VALUES (var_orga_id, var_org_orga_id, var_orga_lb, var_orga_cd, var_orga_fg, var_orga_bl_module);
		END IF;
	END;
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_partie_ref`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_partie_ref`(var_line NVARCHAR(200))
BEGIN
	
	DECLARE var_rparti_id_partie_ref BIGINT;
	DECLARE var_nivref_id BIGINT;
	DECLARE var_par_rparti_id_partie_ref BIGINT;
	DECLARE var_par2_rparti_id_partie_ref BIGINT;
	DECLARE var_rparti_nm_rang1 INT;
	DECLARE var_rparti_nm_rang2 INT;
	DECLARE var_rparti_nm_ordre_ref INT;
	DECLARE var_rparti_nm_partie_ref INT;
	DECLARE var_rparti_fg_partie_1 CHAR(1);
	DECLARE var_rparti_nm_clst_vainqueur INT;
	DECLARE var_rparti_nm_poule1 INT;
	DECLARE var_rparti_nm_classement1 INT;
	DECLARE var_rparti_nm_poule2 INT;
	DECLARE var_rparti_nm_classement2 INT;
	DECLARE var_rparti_nm_position1 INT;
	DECLARE var_rparti_nm_poisition2 INT;
	DECLARE var_rparti_fg_partie_2 CHAR(1);
	DECLARE var_rparti_nm_clst_perdant INT;
	DECLARE var_rparti_fg_partie_ref CHAR(1);
	DECLARE var_rparti_nm_clst_ref CHAR(1);
	DECLARE var_rparti_bl_clst_nonjouee SMALLINT;
	
	BEGIN 
		SET var_rparti_id_partie_ref = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_nivref_id = (RTRIM(SUBSTRING(var_line, 16, 15))  );
		SET var_par_rparti_id_partie_ref = (NULLIF(RTRIM(SUBSTRING(var_line, 31, 15)), '')  );
		SET var_par2_rparti_id_partie_ref = (NULLIF(RTRIM(SUBSTRING(var_line, 46, 15)), '')  );
		SET var_rparti_nm_rang1 = (NULLIF(RTRIM(SUBSTRING(var_line, 61, 8)), '')  );
		SET var_rparti_nm_rang2 = (NULLIF(RTRIM(SUBSTRING(var_line, 69, 8)), '')  );
		SET var_rparti_nm_ordre_ref = (NULLIF(RTRIM(SUBSTRING(var_line, 77, 8)), '')  );
		SET var_rparti_nm_partie_ref = (NULLIF(RTRIM(SUBSTRING(var_line, 85, 8)), '')  );
		SET var_rparti_fg_partie_1 = NULLIF(RTRIM(SUBSTRING(var_line, 93, 1)), '');
		SET var_rparti_nm_clst_vainqueur = (NULLIF(RTRIM(SUBSTRING(var_line, 94, 8)), '')  );
		SET var_rparti_nm_poule1 = (NULLIF(RTRIM(SUBSTRING(var_line, 102, 8)), '')  );
		SET var_rparti_nm_classement1 = (NULLIF(RTRIM(SUBSTRING(var_line, 110, 8)), '')  );
		SET var_rparti_nm_poule2 = (NULLIF(RTRIM(SUBSTRING(var_line, 118, 8)), '')  );
		SET var_rparti_nm_classement2 = (NULLIF(RTRIM(SUBSTRING(var_line, 126, 8)), '')  );
		SET var_rparti_nm_position1 = (NULLIF(RTRIM(SUBSTRING(var_line, 134, 8)), '')  );
		SET var_rparti_nm_poisition2 = (NULLIF(RTRIM(SUBSTRING(var_line, 142, 8)), '')  );
		SET var_rparti_fg_partie_2 = NULLIF(RTRIM(SUBSTRING(var_line, 150, 1)), '');
		SET var_rparti_nm_clst_perdant = (NULLIF(RTRIM(SUBSTRING(var_line, 151, 8)), '')  );
		SET var_rparti_fg_partie_ref = NULLIF(RTRIM(SUBSTRING(var_line,159, 1)), '');
		SET var_rparti_nm_clst_ref = NULLIF(RTRIM(SUBSTRING(var_line, 160, 1)), '');
		SET var_rparti_bl_clst_nonjouee = (NULLIF(RTRIM(SUBSTRING(var_line, 168, 8)), '')  );
		UPDATE PARTIE_REF SET NIVREF_ID = var_nivref_id,
								PAR_RPARTI_ID_PARTIE_REF = var_par_rparti_id_partie_ref,
								PAR2_RPARTI_ID_PARTIE_REF = var_par2_rparti_id_partie_ref,
								RPARTI_NM_RANG1 = var_rparti_nm_rang1,
								RPARTI_NM_RANG2 = var_rparti_nm_rang2,
								RPARTI_NM_ORDRE_REF = var_rparti_nm_ordre_ref,
								RPARTI_NM_PARTIE_REF = var_rparti_nm_partie_ref,
								RPARTI_FG_PARTIE_1 = var_rparti_fg_partie_1,
								RPARTI_NM_CLST_VAINQUEUR = var_rparti_nm_clst_vainqueur,
								RPARTI_NM_POULE1 = var_rparti_nm_poule1,
								RPARTI_NM_CLASSEMENT1 = var_rparti_nm_classement1,
								RPARTI_NM_POULE2 = var_rparti_nm_poule2,
								RPARTI_NM_CLASSEMENT2 = var_rparti_nm_classement2,
								RPARTI_NM_POSITION1 = var_rparti_nm_position1,
								RPARTI_NM_POSITION2 = var_rparti_nm_poisition2,
								RPARTI_FG_PARTIE_2 = var_rparti_fg_partie_2,
								RPARTI_NM_CLST_PERDANT = var_rparti_nm_clst_perdant,
								RPARTI_FG_PARTIE_REF = var_rparti_fg_partie_ref,
								RPARTI_NM_CLST_REF = var_rparti_nm_clst_ref,
								RPARTI_BL_CLST_NONJOUEE = var_rparti_bl_clst_nonjouee,
								BL_MAJ = 1
							WHERE RPARTI_ID_PARTIE_REF = var_rparti_id_partie_ref;
		
		IF ROW_COUNT() = 0 THEN
		
			INSERT INTO PARTIE_REF (RPARTI_ID_PARTIE_REF, NIVREF_ID, PAR_RPARTI_ID_PARTIE_REF, PAR2_RPARTI_ID_PARTIE_REF, 
									RPARTI_NM_RANG1, RPARTI_NM_RANG2, RPARTI_NM_ORDRE_REF, RPARTI_NM_PARTIE_REF, 
									RPARTI_FG_PARTIE_1, RPARTI_NM_CLST_VAINQUEUR, RPARTI_NM_POULE1, RPARTI_NM_CLASSEMENT1, 
									RPARTI_NM_POULE2, RPARTI_NM_CLASSEMENT2, RPARTI_NM_POSITION1, RPARTI_NM_POSITION2, 
									RPARTI_FG_PARTIE_2, RPARTI_NM_CLST_PERDANT, RPARTI_FG_PARTIE_REF, RPARTI_NM_CLST_REF, RPARTI_BL_CLST_NONJOUEE)
					VALUES (var_rparti_id_partie_ref, var_nivref_id, var_par_rparti_id_partie_ref, var_par2_rparti_id_partie_ref, 
							var_rparti_nm_rang1, var_rparti_nm_rang2, var_rparti_nm_ordre_ref, var_rparti_nm_partie_ref, 
							var_rparti_fg_partie_1, var_rparti_nm_clst_vainqueur, var_rparti_nm_poule1, var_rparti_nm_classement1, 
							var_rparti_nm_poule2, var_rparti_nm_classement2, var_rparti_nm_position1, var_rparti_nm_poisition2, 
							var_rparti_fg_partie_2, var_rparti_nm_clst_perdant, var_rparti_fg_partie_ref, var_rparti_nm_clst_ref, var_rparti_bl_clst_nonjouee);
		END IF;
	END; 
	
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_tableau_ref`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_tableau_ref`(var_line NVARCHAR(500))
BEGIN
	
	DECLARE var_tabref_id BIGINT;
	DECLARE var_tabref_cd VARCHAR(5);
	DECLARE var_tabref_lb VARCHAR(32);
	DECLARE var_tabref_fg CHAR(2);
	DECLARE var_tabref_nb_joueurs_ref INT;
	DECLARE var_tabref_cm VARCHAR(255);
	DECLARE var_tabref_lb_user_modif VARCHAR(15);
	DECLARE var_tabref_dt_modification DATETIME;
	DECLARE var_tabref_dt_creation DATETIME;
	DECLARE var_tabref_saisie_type CHAR(1);
	
	BEGIN 
		SET var_tabref_id = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_tabref_cd = RTRIM(SUBSTRING(var_line, 16, 5));
		SET var_tabref_lb = RTRIM(SUBSTRING(var_line, 21, 32));
		SET var_tabref_fg = RTRIM(SUBSTRING(var_line, 53, 2));
		SET var_tabref_nb_joueurs_ref = (RTRIM(SUBSTRING(var_line, 55, 8))  );
		SET var_tabref_cm = NULLIF(RTRIM(SUBSTRING(var_line, 63, 255)), '');
		SET var_tabref_lb_user_modif = RTRIM(SUBSTRING(var_line, 318, 15));
		SET var_tabref_dt_modification = STR_TO_DATE(NULLIF(RTRIM(SUBSTRING(var_line, 333, 10)), ''),'%d/%m/%Y');
		SET var_tabref_dt_creation = STR_TO_DATE(NULLIF(RTRIM(SUBSTRING(var_line, 343, 10)), ''),'%d/%m/%Y');
		SET var_tabref_saisie_type = NULLIF(RTRIM(SUBSTRING(var_line, 353, 1)), '');
		
		UPDATE TABLEAU_REF SET TABREF_CD = var_tabref_cd,
								TABREF_LB = var_tabref_lb,
								TABREF_FG = var_tabref_fg,
								TABREF_NB_JOUEURS_REF = var_tabref_nb_joueurs_ref,
								TABREF_CM = var_tabref_cm,
								TABREF_LB_USER_MODIF = var_tabref_lb_user_modif,
								TABREF_DT_MODIFICATION = var_tabref_dt_modification,
								TABREF_DT_CREATION = var_tabref_dt_creation,
								TABREF_SAISIE_TYPE = var_tabref_saisie_type,
								BL_MAJ = 1
							WHERE TABREF_ID = var_tabref_id;
		
		IF ROW_COUNT() = 0 THEN
		
			INSERT INTO TABLEAU_REF (TABREF_ID, TABREF_CD, TABREF_LB, TABREF_FG, TABREF_NB_JOUEURS_REF, TABREF_CM, 
									TABREF_LB_USER_MODIF, TABREF_DT_MODIFICATION, TABREF_DT_CREATION, TABREF_SAISIE_TYPE)
					VALUES (var_tabref_id, var_tabref_cd, var_tabref_lb, var_tabref_fg, var_tabref_nb_joueurs_ref, var_tabref_cm, 
							var_tabref_lb_user_modif, var_tabref_dt_modification, var_tabref_dt_creation, var_tabref_saisie_type);
		END IF;
	END; 
	
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Import_type_classement`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Import_type_classement`(var_line NVARCHAR(200))
BEGIN
	DECLARE var_tclst_id BIGINT;
	DECLARE var_tclst_lb VARCHAR(32);
	DECLARE var_tclst_cd CHAR(2);
	DECLARE var_tclst_nb_point_min INT;
	
	BEGIN 
		SET var_tclst_id = (RTRIM(SUBSTRING(var_line, 1, 15))  );
		SET var_tclst_lb = RTRIM(SUBSTRING(var_line, 16, 32));
		SET var_tclst_cd = NULLIF(RTRIM(SUBSTRING(var_line, 48, 2)), '');
		SET var_tclst_nb_point_min = (RTRIM(SUBSTRING(var_line, 50, 8))  );
		UPDATE TYPE_CLASSEMENT SET TCLST_LB = var_tclst_lb,
								TCLST_CD = var_tclst_cd,
								TCLST_NB_POINT_MIN = var_tclst_nb_point_min,
								BL_MAJ = 1
							WHERE TCLST_ID = var_tclst_id;
		
		IF ROW_COUNT() = 0 THEN
		
			INSERT INTO TYPE_CLASSEMENT (TCLST_ID, TCLST_LB, TCLST_CD, TCLST_NB_POINT_MIN)
					VALUES (var_tclst_id, var_tclst_lb, var_tclst_cd, var_tclst_nb_point_min);
		END IF;
	END;	
END$$

DELIMITER ;

DELIMITER $$

USE `spidd`$$

DROP PROCEDURE IF EXISTS `Package_clear`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Package_clear`(var_line BIGINT)
BEGIN	
DROP TABLE IF EXISTS var_tableau;
DROP TABLE IF EXISTS var_inscription;
DROP TABLE IF EXISTS var_poule;
DROP TABLE IF EXISTS var_niveau;
DROP TABLE IF EXISTS var_tableauPartie;
DROP TABLE IF EXISTS var_partie;
CREATE TABLE var_tableau 
( 
    TableauId BIGINT
);	
INSERT INTO var_tableau SELECT TAB_ID FROM PACKAGE_TABLEAU WHERE PKG_ID = var_line;
	
CREATE TABLE var_inscription 
( 
    InscriptionId BIGINT
);					
INSERT INTO var_inscription SELECT INSC_ID FROM INSCRIPTION WHERE TAB_ID IN(SELECT TableauId FROM var_tableau);
CREATE TABLE var_poule 
( 
    PouleId BIGINT
);
INSERT INTO var_poule SELECT POUL_ID FROM POULE 
					WHERE TAB_ID IN(SELECT TableauId FROM var_tableau);
CREATE TABLE var_niveau 
( 
   NiveauId BIGINT
);			
INSERT INTO var_niveau SELECT NIV_ID FROM NIVEAU WHERE TAB_ID IN(SELECT TableauId FROM var_tableau);
CREATE TABLE var_tableauPartie 
( 
    TableauPartieId BIGINT, PartieId BIGINT
);
INSERT INTO var_tableauPartie SELECT TABPART_ID_TABLEAU, PARTI_ID FROM TABLEAU_PARTIE 
					WHERE NIV_ID IN(SELECT NiveauId FROM var_niveau)
					OR POUL_ID IN(SELECT PouleId FROM var_poule);
	
CREATE TABLE var_partie 
( 
    PartieId BIGINT
);
INSERT INTO var_partie SELECT PARTI_ID FROM TABLEAU_PARTIE 
					WHERE NIV_ID IN(SELECT NiveauId FROM var_niveau)
					OR POUL_ID IN(SELECT PouleId FROM var_poule);
					
	-- Suppresion des CELLULE_POT
	DELETE FROM CELLULE_POT 
		WHERE TABPART_ID_TABLEAU IN(SELECT TableauPartieId FROM var_tableauPartie);
	
	-- Suppresion des MANCHE
	DELETE FROM MANCHE 
		WHERE TABPART_ID_TABLEAU IN(SELECT TableauPartieId FROM var_tableauPartie);
	
	-- Suppresion des TABLEAU_PARTIE
	DELETE FROM TABLEAU_PARTIE 
		WHERE TABPART_ID_TABLEAU IN(SELECT TableauPartieId FROM var_tableauPartie);
	
	-- Suppresion des PARTIE					
	DELETE FROM PARTIE 
		WHERE TAB_ID IN(SELECT TableauId FROM var_tableau)
		OR PARTI_ID IN(SELECT PartieId FROM var_partie);
				
	-- Suppresion des INSCRIPTION
	DELETE FROM INSCRIPTION 
		WHERE TAB_ID IN(SELECT TableauId FROM var_tableau);
	
	-- Suppresion des CLASSEMENT
	DELETE FROM CLASSEMENT 
		WHERE POUL_ID IN(SELECT PouleId FROM var_poule)
		OR TAB_ID IN(SELECT TableauId FROM var_tableau);
	
	-- Suppresion des POULE
	DELETE FROM POULE 
		WHERE POUL_ID IN(SELECT PouleId FROM var_poule);
		
	-- Suppresion des NIVEAUX
	DELETE FROM NIVEAU 
		WHERE NIV_ID IN(SELECT NiveauId FROM var_niveau);
				
	-- Suppresion des package_tableu
	DELETE FROM PACKAGE_TABLEAU WHERE PKG_ID = var_line;
	SELECT * FROM var_tableau;
	DROP TABLE IF EXISTS var_tableau;
	
	SELECT * FROM var_inscription;
	DROP TABLE IF EXISTS var_inscription;
	
	SELECT * FROM var_poule;
	DROP TABLE IF EXISTS var_poule;
	
	SELECT * FROM var_niveau;
	DROP TABLE IF EXISTS var_niveau;
	
	SELECT * FROM var_tableauPartie;
	DROP TABLE IF EXISTS var_tableauPartie;
	
	SELECT * FROM var_partie;
	DROP TABLE IF EXISTS var_partie;
END$$

DELIMITER ;