WebSVN – eFlore/Applications.coel – Diff – /trunk/doc/bdd/migration_bdd_herbiers_vers_coel.sql



#------------------------------------------------------------------------------------------------------------------------
# SCRIPT de MIGRATION de HERBIERS vers COEL v1.0
#------------------------------------------------------------------------------------------------------------------------
# TODO : remplacer les valeurs vides par NULL -> utiliser le script remplacer_vide_par_null.php

 
SET @idm = 795;# Identifiant pour les métadonnées
SET @idm_max = 99999;# Identifiant pour les métadonnées maximum si déjà des données dans la base
#---------------------------------------------------------------------------------------------------------------------#
# Nettoyage des tables
# coel_projet : la table d''historique des lignes

	FROM tb_herbiers.EFLORE_DROIT_POSSEDER 
	ORDER BY EDP_ID_UTILISATEUR ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
	SELECT @idm_tmp := @idm_tmp+1, '114', CONCAT(EDP_ID_UTILISATEUR, '-', EDP_ID_UTILISATEUR, '-2-', IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0)))), NULL, NOW(), 
	CONCAT('Importation des droits l''utilisateur #',EDP_ID_UTILISATEUR,' de la table EFLORE_DROIT_POSSEDER.'), '1', '1', '162.38.234.1'
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER 
	ORDER BY EDP_ID_UTILISATEUR ASC;

 
#------------------------------------------------------------------------------------------------------------------------

	ORDER BY U_ID ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,  
	cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip) 
	SELECT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,
		CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela liée à EFLORE_DROIT_POSSEDER.'), '1', '1', '162.38.234.1'
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)  
	ORDER BY U_ID ASC;

 
#------------------------------------------------------------------------------------------------------------------------	
# HERBIERS_ADMINISTRER et annuaire_tela vers coel_personne
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
INSERT INTO tb_coel.coel_personne
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom, 
	 cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,
	 cp_truk_courriel, cp_truk_url, 
	 cp_description, 

		   U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'), 
		   @idm := @idm+1
	FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID) 
		LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays) 
	WHERE U_ID NOT IN (SELECT DISTINCT EDP_ID_UTILISATEUR FROM tb_herbiers.EFLORE_DROIT_POSSEDER)
	ORDER BY U_ID ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,  
	cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip) 
	SELECT DISTINCT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,
		CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela liée à HERBIERS_ADMINISTRER.'), '1', '1', '162.38.234.1'
	FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)    
	WHERE U_ID NOT IN (SELECT DISTINCT EDP_ID_UTILISATEUR FROM tb_herbiers.EFLORE_DROIT_POSSEDER)
	ORDER BY U_ID ASC;

 
#------------------------------------------------------------------------------------------------------------------------
# HERBIERS_ADMINISTRER vers coel_structure_a_personne

	ORDER BY ID_STAFF ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
	SELECT @idm_tmp := @idm_tmp+1, '113', 100000+ID_STAFF, NULL, IF(DATE_DERNIERE_MODIF != '0000-00-00 00:00:00', DATE_DERNIERE_MODIF, NOW()),
		CONCAT('Importation de l''utilisateur #', 100000+ID_STAFF,' de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
	FROM tb_herbiers.HERBIERS_STAFF
	ORDER BY ID_STAFF ASC;

 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';

	WHERE hous.ID_ORG != 0 
	ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
	SELECT @idm_tmp := @idm_tmp+1, '120', CONCAT(ID_ORG, '-', 100000+ID_STAFF, '-2027'), NULL, 
		NOW(), CONCAT('Importation de la personne #',100000+ID_STAFF,' de la structure #', ID_ORG,' de la table HERBIERS_ont_un_staff.'), '1', '1', '162.38.234.1'
	FROM tb_herbiers.HERBIERS_ont_un_staff
	WHERE ID_ORG != 0 
	ORDER BY ID_ORG, ID_STAFF ASC;	

	

	(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description, cc_truk_type_depot, cc_ce_specimen_type,  
	 cc_description_specialiste, cc_truk_url, 
	 cc_specimen_type_nbre, 
	 cc_truk_preservation, 
    cc_ce_meta)
	SELECT hc.ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, NOM_COLLECTION, CONVERT(DESCRIPTION USING utf8), STATUT, IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,
	   CONCAT(IF(DOC_STATE = '', '', IF(DOC_STATE IS NULL, '', CONCAT('Etat documentation : \n',DOC_STATE))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n',COLLECTEURS)))) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL, 
	   NUM_SPECIMENS, 
	   CAST(GROUP_CONCAT(DISTINCT CASE ID_PRES WHEN '1' THEN '2204' WHEN '2' THEN '2222' WHEN '3' THEN '2215' WHEN '4' THEN '2203' WHEN '5' THEN '2211' WHEN '6' THEN '2223' WHEN '7' THEN '2207' WHEN '8' THEN '2209' WHEN '9' THEN '2224' WHEN '10' THEN '2218' WHEN '12' THEN '2212' WHEN '13' THEN 'AUTRE#non précisé' ELSE ID_PRES END SEPARATOR ";") AS CHAR)  AS PRESENTATION, 
	   @idm := @idm+1
	FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID) 
	WHERE hc.ID != 4 
	GROUP BY hc.ID 
	ORDER BY hc.ID ASC;
INSERT INTO tb_coel.coel_collection_botanique 
	(ccb_id_collection, ccb_truk_unite_base, 
	ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type, 

		DATE_FIN, IF( DATE_FIN_CARAC = '1', '2321', IF (DATE_FIN_CARAC = '2', '2322', IF(DATE_FIN_CARAC = '3', '2323', IF (DATE_FIN_CARAC = '4', '2325', IF(DATE_FIN_CARAC = '5', '2324', IF (DATE_FIN_CARAC = '6', '2326', NULL)))))), 
		PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),
		CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),
		CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),
		IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', MODE_CLASSEMENT, NULL), NULL)
	FROM tb_herbiers.HERBIERS_COLLECTION
	WHERE ID != 4 
	ORDER BY ID ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip) 
	SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL, 
		IF(DATE_DERNIERE_MODIF != '0000-00-00 00:00:00', DATE_DERNIERE_MODIF, NOW()) , CONCAT('Ajout de la collection #',ID,' de la table HERBIER_COLLECTION.'), IF(CE_MODIFIER_PAR != '0', CE_MODIFIER_PAR, '1'), '1', IF(CE_MODIFIER_PAR != '0', NULL, '162.38.234.1') 	
	FROM tb_herbiers.HERBIERS_COLLECTION
	WHERE ID != 4 
	ORDER BY ID ASC;	

		
#------------------------------------------------------------------------------------------------------------------------
#HERBIERS_INDIC vers coel_commentaires

Subversion Repositories eFlore/Applications.coel

(root)/trunk/doc/bdd/migration_bdd_herbiers_vers_coel.sql @ 1417 – Rev 87 → 92

Rev 87		Rev 92
Line 1...		Line 1...
1	`#------------------------------------------------------------------------------------------------------------------------`	1	`#------------------------------------------------------------------------------------------------------------------------`
2	`# SCRIPT de MIGRATION de HERBIERS vers COEL v1.0`	2	`# SCRIPT de MIGRATION de HERBIERS vers COEL v1.0`
3	`#------------------------------------------------------------------------------------------------------------------------`	3	`#------------------------------------------------------------------------------------------------------------------------`
4	`# TODO : remplacer les valeurs vides par NULL -> utiliser le script remplacer_vide_par_null.php`	4	`# TODO : remplacer les valeurs vides par NULL -> utiliser le script remplacer_vide_par_null.php`
Line 5...		Line 5...
5		5
6	`SET @idm = 761;# Identifiant pour les métadonnées`	6	`SET @idm = 795;# Identifiant pour les métadonnées`
7	`SET @idm_max = 99999;# Identifiant pour les métadonnées maximum si déjà des données dans la base`	7	`SET @idm_max = 99999;# Identifiant pour les métadonnées maximum si déjà des données dans la base`
8	`#---------------------------------------------------------------------------------------------------------------------#`	8	`#---------------------------------------------------------------------------------------------------------------------#`
9	`# Nettoyage des tables`	9	`# Nettoyage des tables`
10	`# coel_projet : la table d''historique des lignes`	10	`# coel_projet : la table d''historique des lignes`
Line 81...		Line 81...
81	`FROM tb_herbiers.EFLORE_DROIT_POSSEDER`	81	`FROM tb_herbiers.EFLORE_DROIT_POSSEDER`
82	`ORDER BY EDP_ID_UTILISATEUR ASC;`	82	`ORDER BY EDP_ID_UTILISATEUR ASC;`
83	`INSERT INTO tb_coel.coel_meta_historique_ligne`	83	`INSERT INTO tb_coel.coel_meta_historique_ligne`
84	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`	84	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`
85	`cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)`	85	`cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)`
86	`SELECT @idm_tmp := @idm_tmp+1, '114', CONCAT(EDP_ID_UTILISATEUR, '-', EDP_ID_UTILISATEUR, '-2-', IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0)))), NULL, NOW(), CONCAT('Importation des droits l''utilisateur #',EDP_ID_UTILISATEUR,' de la table EFLORE_DROIT_POSSEDER.'), '1', '1', '162.38.234.1'`	86	`SELECT @idm_tmp := @idm_tmp+1, '114', CONCAT(EDP_ID_UTILISATEUR, '-', EDP_ID_UTILISATEUR, '-2-', IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0)))), NULL, NOW(),`
-		87	`CONCAT('Importation des droits l''utilisateur #',EDP_ID_UTILISATEUR,' de la table EFLORE_DROIT_POSSEDER.'), '1', '1', '162.38.234.1'`
87	`FROM tb_herbiers.EFLORE_DROIT_POSSEDER`	88	`FROM tb_herbiers.EFLORE_DROIT_POSSEDER`
88	`ORDER BY EDP_ID_UTILISATEUR ASC;`	89	`ORDER BY EDP_ID_UTILISATEUR ASC;`
Line 89...		Line 90...
89		90
90	`#------------------------------------------------------------------------------------------------------------------------`	91	`#------------------------------------------------------------------------------------------------------------------------`
Line 126...		Line 127...
126	`ORDER BY U_ID ASC;`	127	`ORDER BY U_ID ASC;`
127	`INSERT INTO tb_coel.coel_meta_historique_ligne`	128	`INSERT INTO tb_coel.coel_meta_historique_ligne`
128	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,`	129	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,`
129	`cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)`	130	`cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)`
130	`SELECT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,`	131	`SELECT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,`
131	`CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela.'), '1', '1', '162.38.234.1'`	132	`CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela liée à EFLORE_DROIT_POSSEDER.'), '1', '1', '162.38.234.1'`
132	`FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP`	133	`FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP`
133	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)`	134	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)`
134	`ORDER BY U_ID ASC;`	135	`ORDER BY U_ID ASC;`
Line 135...		Line 136...
135		136
136	`#------------------------------------------------------------------------------------------------------------------------`	137	`#------------------------------------------------------------------------------------------------------------------------`
137	`# HERBIERS_ADMINISTRER et annuaire_tela vers coel_personne`	138	`# HERBIERS_ADMINISTRER et annuaire_tela vers coel_personne`
138	`# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000`	139	`# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000`
139	`INSERT IGNORE INTO tb_coel.coel_personne`	140	`INSERT INTO tb_coel.coel_personne`
140	`(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,`	141	`(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,`
141	`cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,`	142	`cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,`
142	`cp_truk_courriel, cp_truk_url,`	143	`cp_truk_courriel, cp_truk_url,`
143	`cp_description,`	144	`cp_description,`
Line 150...		Line 151...
150	`U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),`	151	`U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),`
151	`@idm := @idm+1`	152	`@idm := @idm+1`
152	`FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA`	153	`FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA`
153	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)`	154	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)`
154	`LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays)`	155	`LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays)`
155	`WHERE U_ID NOT IN (SELECT cp_id_personne FROM tb_coel.coel_personne WHERE cp_id_personne < 100000)`	156	`WHERE U_ID NOT IN (SELECT DISTINCT EDP_ID_UTILISATEUR FROM tb_herbiers.EFLORE_DROIT_POSSEDER)`
156	`ORDER BY U_ID ASC;`	157	`ORDER BY U_ID ASC;`
157	`INSERT INTO tb_coel.coel_meta_historique_ligne`	158	`INSERT INTO tb_coel.coel_meta_historique_ligne`
158	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,`	159	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,`
159	`cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)`	160	`cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)`
160	`SELECT DISTINCT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,`	161	`SELECT DISTINCT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,`
161	`CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela.'), '1', '1', '162.38.234.1'`	162	`CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela liée à HERBIERS_ADMINISTRER.'), '1', '1', '162.38.234.1'`
162	`FROM tb_v4.annuaire_tela AS AT`	163	`FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA`
163	`LEFT JOIN tb_herbiers.HERBIERS_ADMINISTRER AS HA ON (AT.U_ID = HA.HA_ID_ANNUAIRE )`	164	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)`
164	`WHERE U_ID NOT IN (SELECT cp_id_personne FROM tb_coel.coel_personne WHERE cp_id_personne < 100000)`	165	`WHERE U_ID NOT IN (SELECT DISTINCT EDP_ID_UTILISATEUR FROM tb_herbiers.EFLORE_DROIT_POSSEDER)`
165	`ORDER BY U_ID ASC;`	166	`ORDER BY U_ID ASC;`
Line 166...		Line 167...
166		167
167	`#------------------------------------------------------------------------------------------------------------------------`	168	`#------------------------------------------------------------------------------------------------------------------------`
168	`# HERBIERS_ADMINISTRER vers coel_structure_a_personne`	169	`# HERBIERS_ADMINISTRER vers coel_structure_a_personne`
Line 191...		Line 192...
191	`ORDER BY ID_STAFF ASC;`	192	`ORDER BY ID_STAFF ASC;`
192	`INSERT INTO tb_coel.coel_meta_historique_ligne`	193	`INSERT INTO tb_coel.coel_meta_historique_ligne`
193	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`	194	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`
194	`cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)`	195	`cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)`
195	`SELECT @idm_tmp := @idm_tmp+1, '113', 100000+ID_STAFF, NULL, IF(DATE_DERNIERE_MODIF != '0000-00-00 00:00:00', DATE_DERNIERE_MODIF, NOW()),`	196	`SELECT @idm_tmp := @idm_tmp+1, '113', 100000+ID_STAFF, NULL, IF(DATE_DERNIERE_MODIF != '0000-00-00 00:00:00', DATE_DERNIERE_MODIF, NOW()),`
196	`CONCAT('Importation de l''utilisateur #', ID_STAFF,' de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'`	197	`CONCAT('Importation de l''utilisateur #', 100000+ID_STAFF,' de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'`
197	`FROM tb_herbiers.HERBIERS_STAFF`	198	`FROM tb_herbiers.HERBIERS_STAFF`
198	`ORDER BY ID_STAFF ASC;`	199	`ORDER BY ID_STAFF ASC;`
Line 199...		Line 200...
199		200
200	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';`	201	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';`
Line 221...		Line 222...
221	`WHERE hous.ID_ORG != 0`	222	`WHERE hous.ID_ORG != 0`
222	`ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;`	223	`ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;`
223	`INSERT INTO tb_coel.coel_meta_historique_ligne`	224	`INSERT INTO tb_coel.coel_meta_historique_ligne`
224	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`	225	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`
225	`cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)`	226	`cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)`
226	`SELECT @idm_tmp := @idm_tmp+1, '120', CONCAT(ID_ORG, '-', ID_STAFF, '-2027'), NULL,`	227	`SELECT @idm_tmp := @idm_tmp+1, '120', CONCAT(ID_ORG, '-', 100000+ID_STAFF, '-2027'), NULL,`
227	`NOW(), CONCAT('Importation de la personne #',ID_STAFF,' de la structure #', ID_ORG,' de la table HERBIERS_ont_un_staff.'), '1', '1', '162.38.234.1'`	228	`NOW(), CONCAT('Importation de la personne #',100000+ID_STAFF,' de la structure #', ID_ORG,' de la table HERBIERS_ont_un_staff.'), '1', '1', '162.38.234.1'`
228	`FROM tb_herbiers.HERBIERS_ont_un_staff`	229	`FROM tb_herbiers.HERBIERS_ont_un_staff`
229	`WHERE ID_ORG != 0`	230	`WHERE ID_ORG != 0`
230	`ORDER BY ID_ORG, ID_STAFF ASC;`	231	`ORDER BY ID_ORG, ID_STAFF ASC;`
Line 231...		Line 232...
231		232
Line 241...		Line 242...
241	`(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description, cc_truk_type_depot, cc_ce_specimen_type,`	242	`(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description, cc_truk_type_depot, cc_ce_specimen_type,`
242	`cc_description_specialiste, cc_truk_url,`	243	`cc_description_specialiste, cc_truk_url,`
243	`cc_specimen_type_nbre,`	244	`cc_specimen_type_nbre,`
244	`cc_truk_preservation,`	245	`cc_truk_preservation,`
245	`cc_ce_meta)`	246	`cc_ce_meta)`
246	`SELECT hc.ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, NOM_COLLECTION, DESCRIPTION, STATUT, IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,`	247	`SELECT hc.ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, NOM_COLLECTION, CONVERT(DESCRIPTION USING utf8), STATUT, IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,`
247	`CONCAT(IF(DOC_STATE = '', '', IF(DOC_STATE IS NULL, '', CONCAT('Etat documentation : \n',DOC_STATE))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n',COLLECTEURS)))) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL,`	248	`CONCAT(IF(DOC_STATE = '', '', IF(DOC_STATE IS NULL, '', CONCAT('Etat documentation : \n',DOC_STATE))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n',COLLECTEURS)))) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL,`
248	`NUM_SPECIMENS,`	249	`NUM_SPECIMENS,`
249	`CAST(GROUP_CONCAT(DISTINCT CASE ID_PRES WHEN '1' THEN '2204' WHEN '2' THEN '2222' WHEN '3' THEN '2215' WHEN '4' THEN '2203' WHEN '5' THEN '2211' WHEN '6' THEN '2223' WHEN '7' THEN '2207' WHEN '8' THEN '2209' WHEN '9' THEN '2224' WHEN '10' THEN '2218' WHEN '12' THEN '2212' WHEN '13' THEN 'AUTRE#non précisé' ELSE ID_PRES END SEPARATOR ";") AS CHAR) AS PRESENTATION,`	250	`CAST(GROUP_CONCAT(DISTINCT CASE ID_PRES WHEN '1' THEN '2204' WHEN '2' THEN '2222' WHEN '3' THEN '2215' WHEN '4' THEN '2203' WHEN '5' THEN '2211' WHEN '6' THEN '2223' WHEN '7' THEN '2207' WHEN '8' THEN '2209' WHEN '9' THEN '2224' WHEN '10' THEN '2218' WHEN '12' THEN '2212' WHEN '13' THEN 'AUTRE#non précisé' ELSE ID_PRES END SEPARATOR ";") AS CHAR) AS PRESENTATION,`
250	`@idm := @idm+1`	251	`@idm := @idm+1`
251	`FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)`	252	`FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)`
-		253	`WHERE hc.ID != 4`
252	`GROUP BY hc.ID`	254	`GROUP BY hc.ID`
253	`ORDER BY hc.ID ASC;`	255	`ORDER BY hc.ID ASC;`
254	`INSERT INTO tb_coel.coel_collection_botanique`	256	`INSERT INTO tb_coel.coel_collection_botanique`
255	`(ccb_id_collection, ccb_truk_unite_base,`	257	`(ccb_id_collection, ccb_truk_unite_base,`
256	`ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type,`	258	`ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type,`
Line 263...		Line 265...
263	`DATE_FIN, IF( DATE_FIN_CARAC = '1', '2321', IF (DATE_FIN_CARAC = '2', '2322', IF(DATE_FIN_CARAC = '3', '2323', IF (DATE_FIN_CARAC = '4', '2325', IF(DATE_FIN_CARAC = '5', '2324', IF (DATE_FIN_CARAC = '6', '2326', NULL)))))),`	265	`DATE_FIN, IF( DATE_FIN_CARAC = '1', '2321', IF (DATE_FIN_CARAC = '2', '2322', IF(DATE_FIN_CARAC = '3', '2323', IF (DATE_FIN_CARAC = '4', '2325', IF(DATE_FIN_CARAC = '5', '2324', IF (DATE_FIN_CARAC = '6', '2326', NULL)))))),`
264	`PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),`	266	`PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),`
265	`CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),`	267	`CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),`
266	`CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),`	268	`CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),`
267	`IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', MODE_CLASSEMENT, NULL), NULL)`	269	`IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', MODE_CLASSEMENT, NULL), NULL)`
268	`FROM tb_herbiers.HERBIERS_COLLECTION`	270	`FROM tb_herbiers.HERBIERS_COLLECTION`
-		271	`WHERE ID != 4`
269	`ORDER BY ID ASC;`	272	`ORDER BY ID ASC;`
270	`INSERT INTO tb_coel.coel_meta_historique_ligne`	273	`INSERT INTO tb_coel.coel_meta_historique_ligne`
271	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`	274	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`
272	`cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)`	275	`cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)`
273	`SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,`	276	`SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,`
274	`IF(DATE_DERNIERE_MODIF != '0000-00-00 00:00:00', DATE_DERNIERE_MODIF, NOW()) , CONCAT('Ajout de la collection #',ID,' de la table HERBIER_COLLECTION.'), IF(CE_MODIFIER_PAR != '0', CE_MODIFIER_PAR, '1'), '1', IF(CE_MODIFIER_PAR != '0', NULL, '162.38.234.1')`	277	`IF(DATE_DERNIERE_MODIF != '0000-00-00 00:00:00', DATE_DERNIERE_MODIF, NOW()) , CONCAT('Ajout de la collection #',ID,' de la table HERBIER_COLLECTION.'), IF(CE_MODIFIER_PAR != '0', CE_MODIFIER_PAR, '1'), '1', IF(CE_MODIFIER_PAR != '0', NULL, '162.38.234.1')`
275	`FROM tb_herbiers.HERBIERS_COLLECTION`	278	`FROM tb_herbiers.HERBIERS_COLLECTION`
-		279	`WHERE ID != 4`
276	`ORDER BY ID ASC;`	280	`ORDER BY ID ASC;`
Line 277...		Line 281...
277		281
278	`#------------------------------------------------------------------------------------------------------------------------`	282	`#------------------------------------------------------------------------------------------------------------------------`
279	`#HERBIERS_INDIC vers coel_commentaires`	283	`#HERBIERS_INDIC vers coel_commentaires`