Subversion Repositories eFlore/Applications.coel

Rev

Rev 75 | Rev 79 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 75 Rev 78
Line 11... Line 11...
11 
DELETE FROM tb_coel.coel_projet WHERE cpr_id_projet IN (2,3);
 11 
DELETE FROM tb_coel.coel_projet WHERE cpr_id_projet IN (2,3);
12 
# coel_meta_historique_ligne : la table d''historique des lignes
 12 
# coel_meta_historique_ligne : la table d''historique des lignes
13 
DELETE FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_id_historique_ligne >= @idm AND cmhl_id_historique_ligne < @idm_max;
 13 
DELETE FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_id_historique_ligne >= @idm AND cmhl_id_historique_ligne < @idm_max;
14 
# coel_structure : la table contenant les structures
 14 
# coel_structure : la table contenant les structures
15 
DELETE FROM tb_coel.coel_structure WHERE cs_id_structure IN (SELECT ID_ORG FROM tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG ASC);
 15 
DELETE FROM tb_coel.coel_structure WHERE cs_id_structure IN (SELECT ID_ORG FROM tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG ASC);
- 
 
 16 
# coel_structure_a_personne
- 
 
 17 
DELETE FROM tb_coel.coel_structure_a_personne WHERE csap_id_role IN ('2026', '2027');
- 
 
 18 
# coel_personne
- 
 
 19 
DELETE FROM tb_coel.coel_personne WHERE cp_ce_projet IN ('2', '3');
16 
# coel_personne_a_relation
20 
# coel_personne_a_relation
17 
DELETE FROM tb_coel.coel_personne_a_relation WHERE cpar_id_projet = '2';
 21 
DELETE FROM tb_coel.coel_personne_a_relation WHERE cpar_id_projet = '2';
- 
 
 22 
# coel_collection_botanique
18 
DELETE FROM tb_coel.coel_personne WHERE cp_ce_projet = '3';
 23 
DELETE FROM tb_coel.coel_collection_botanique WHERE ccb_id_collection IN (SELECT cc_id_collection FROM tb_coel.coel_collection WHERE cc_ce_projet IN ('2'));
- 
 
 24 
# coel_collection_a_commentaire
19 
DELETE FROM tb_coel.coel_structure_a_personne WHERE csap_id_role = '2026';
 25 
DELETE FROM tb_coel.coel_collection_a_commentaire WHERE ccac_id_collection IN (SELECT cc_id_collection FROM tb_coel.coel_collection WHERE cc_ce_projet IN ('2'));
- 
 
 26 
# coel_collection
20 
DELETE FROM tb_coel.coel_personne WHERE cp_ce_projet = '2';
 27 
DELETE FROM tb_coel.coel_collection WHERE cc_ce_projet IN ('2');
- 
 
 28 
# coel_commentaire
21 
DELETE FROM tb_coel.coel_structure_a_personne WHERE csap_id_role = '2027';
 29 
DELETE FROM tb_coel.coel_commentaire WHERE ccm_ce_projet IN ('2');
Line 22... Line 30...
22 
 
 30 
 
23 
#---------------------------------------------------------------------------------------------------------------------#
 31 
#---------------------------------------------------------------------------------------------------------------------#
24 
# EFLORE_PROJET vers coel_projet
 32 
# EFLORE_PROJET vers coel_projet
25 
# Insertion du projet Inventaire des Herbiers de France - Version 1
33 
# Insertion du projet Inventaire des Herbiers de France - Version 1
26 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_ce_meta)
34 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_ce_meta)
27 
	SELECT 2, EPR_INTITULE_PROJET, EPR_ABREVIATION_PROJET, EPR_DESCRIPTION_PROJET, EPR_LIEN_WEB, @idm
35 
	SELECT 2, EPR_INTITULE_PROJET, EPR_ABREVIATION_PROJET, EPR_DESCRIPTION_PROJET, EPR_LIEN_WEB, @idm
28 
	FROM tb_herbiers.EFLORE_PROJET;
 36 
	FROM tb_herbiers.EFLORE_PROJET;
29 
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)
37 
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)
30 
	VALUES	(@idm := @idm+1, '113', '2', NULL , NOW( ) , 'Ajout du projet IHF', '1', '1', '162.38.234.1');
 38 
	VALUES	(@idm := @idm+1, '115', '2', NULL , NOW( ) , 'Ajout du projet IHF', '1', '1', '162.38.234.1');
31 
# Insertion du projet Utilisateur de l''application COEL - Version 1
39 
# Insertion du projet Utilisateur de l''application COEL - Version 1
32 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_ce_meta)
40 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_ce_meta)
33 
	VALUES (3, 'Utilisateurs', 'U', 'Les utilisateurs de l''application COEL.', NULL, @idm);
 41 
	VALUES (3, 'Utilisateurs', 'U', 'Les utilisateurs de l''application COEL.', NULL, @idm);
34 
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)
42 
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)
Line 35... Line 43...
35 
	VALUES	(@idm := @idm+1, '113', '2', NULL , NOW( ) , 'Ajout du projet U', '1', '1', '162.38.234.1');
 43 
	VALUES	(@idm := @idm+1, '115', '2', NULL , NOW( ) , 'Ajout du projet U', '1', '1', '162.38.234.1');
36 
 
 44 
 
37
45
Line 45... Line 53...
45 
	cs_nom, cs_adresse_01, cs_code_postal, cs_ville, cs_region, cs_pays, cs_telephone, cs_fax, cs_courriel,
 53 
	cs_nom, cs_adresse_01, cs_code_postal, cs_ville, cs_region, cs_pays, cs_telephone, cs_fax, cs_courriel,
46 
	cs_description,
54 
	cs_description,
47 
	cs_truk_url,
 55 
	cs_truk_url,
48 
	cs_condition_acces,
56 
	cs_condition_acces,
49 
	cs_ce_meta)
57 
	cs_ce_meta)
50 
	SELECT ID_ORG, 2, 0, CONCAT("URN:tela-botanica.org:ihf:str",ID_ORG) AS GUID, IF(INDEX_HERB != '', CONCAT('IH#',INDEX_HERB), NULL) AS IDENTIFIANT_ALTERNATIF,
58 
	SELECT ID_ORG, '2', '0', CONCAT("URN:tela-botanica.org:ihf:str",ID_ORG) AS GUID, IF(INDEX_HERB != '', CONCAT('IH#',INDEX_HERB), NULL) AS IDENTIFIANT_ALTERNATIF,
51 
		INSTITUTION_NAME, ADRESS_LINE, ZIP, TOWN, REGION, IF(COUNTRY_CODE = 'fr', 'France', COUNTRY_CODE) AS PAYS, TEL, FAX, EMAIL,
59 
		INSTITUTION_NAME, ADRESS_LINE, ZIP, TOWN, REGION, IF(COUNTRY_CODE = 'fr', 'France', COUNTRY_CODE) AS PAYS, TEL, FAX, EMAIL,
52 
		IF(SOURCE_DES_DONNEES != '', CONCAT("Source des données : ",SOURCE_DES_DONNEES), NULL) AS DESCRIPTION,
60 
		IF(SOURCE_DES_DONNEES != '', CONCAT("Source des données : ",SOURCE_DES_DONNEES), NULL) AS DESCRIPTION,
53 
		CONCAT(IF(HO_URL != 'http://', CONCAT('WEB#',HO_URL), ''),IF(HO_URL != 'http://' AND LOGO != '', ';', ''), IF(LOGO != '', CONCAT('LOGO#',LOGO),'')) AS URLS,
61 
		CONCAT(IF(HO_URL != 'http://', CONCAT('WEB#',HO_URL), ''),IF(HO_URL != 'http://' AND LOGO != '', ';', ''), IF(LOGO != '', CONCAT('LOGO#',LOGO),'')) AS URLS,
54 
		ACCESS_RESTRICTION,
 62 
		ACCESS_RESTRICTION,
55 
		@idm := @idm+1
63 
		@idm := @idm+1
Line 57... Line 65...
57 
	ORDER BY ID_ORG ASC;
 65 
	ORDER BY ID_ORG ASC;
58 
INSERT INTO tb_coel.coel_meta_historique_ligne
66 
INSERT INTO tb_coel.coel_meta_historique_ligne
59 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,
67 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,
60 
	cmhl_notes,
68 
	cmhl_notes,
61 
	cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
69 
	cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
62 
	SELECT @idm_tmp := @idm_tmp+1, 117, ID_ORG, NULL, IF(DATE_DERNIERE_MODIF = '0000-00-00 00:00:00', NOW(), DATE_DERNIERE_MODIF),
 70 
	SELECT @idm_tmp := @idm_tmp+1, '119', ID_ORG, NULL, IF(DATE_DERNIERE_MODIF = '0000-00-00 00:00:00', NOW(), DATE_DERNIERE_MODIF),
63 
		CONCAT('Importation de l''enregistrement #',ID_ORG,' de la table HERBIERS_ORGANISATION.'),
71 
		CONCAT('Importation de l''enregistrement #',ID_ORG,' de la table HERBIERS_ORGANISATION.'),
64 
		IF(CE_MODIFIER_PAR = '0', 1, CE_MODIFIER_PAR), '1', '162.38.234.1'
 72 
		IF(CE_MODIFIER_PAR = '0', '1', CE_MODIFIER_PAR), '1', '162.38.234.1'
65 
	FROM tb_herbiers.HERBIERS_ORGANISATION
73 
	FROM tb_herbiers.HERBIERS_ORGANISATION
66 
	ORDER BY ID_ORG ASC;
 74 
	ORDER BY ID_ORG ASC;
67
- 
 
68
75 
 
69
- 
 
70
- 
 
71
- 
 
72
- 
 
73
- 
 
74
- 
 
75
- 
 
76 
#------------------------------------------------------------------------------------------------------------------------
 76 
#------------------------------------------------------------------------------------------------------------------------
77 
#EFLORE_DROIT_POSSEDER vers coel_personne_a_relation
 77 
#EFLORE_DROIT_POSSEDER vers coel_personne_a_relation
78 
INSERT INTO tb_coel.coel_personne_a_relation
 78 
INSERT INTO tb_coel.coel_personne_a_relation
79 
	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
 79 
	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
80 
	SELECT 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))), @idm := @idm+1
 80 
	SELECT 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'))), @idm := @idm+1
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, '112', 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(), 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
87 
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER
88 
	ORDER BY EDP_ID_UTILISATEUR ASC;
 88 
	ORDER BY EDP_ID_UTILISATEUR ASC;
Line 89... Line 89...
89 
 
 89 
 
90 
#------------------------------------------------------------------------------------------------------------------------
 90 
#------------------------------------------------------------------------------------------------------------------------
91 
# HERBIERS_COORDONNE vers coel_personne_a_relation
 91 
# HERBIERS_COORDONNE vers coel_personne_a_relation
92 
INSERT INTO tb_coel.coel_personne_a_relation
 92 
INSERT INTO tb_coel.coel_personne_a_relation
93 
	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
 93 
	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
94 
	SELECT HC_ID_COORDINATEUR, HC_ID_REDACTEUR, 2, 2025, @idm := @idm+1
 94 
	SELECT HC_ID_COORDINATEUR, HC_ID_REDACTEUR, '2', '2025', @idm := @idm+1
95 
	FROM tb_herbiers.HERBIERS_COORDONNE
95 
	FROM tb_herbiers.HERBIERS_COORDONNE
96 
	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
 96 
	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
97 
INSERT INTO tb_coel.coel_meta_historique_ligne
97 
INSERT INTO tb_coel.coel_meta_historique_ligne
98 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
98 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
99 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
 99 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
100 
	SELECT @idm_tmp := @idm_tmp+1, '112', CONCAT(HC_ID_COORDINATEUR, '-', HC_ID_REDACTEUR, '-2-2025'), NULL,
100 
	SELECT @idm_tmp := @idm_tmp+1, '114', CONCAT(HC_ID_COORDINATEUR, '-', HC_ID_REDACTEUR, '-2-2025'), NULL,
101 
		NOW(), CONCAT('Importation des relations de coordination de l''utilisateur #',HC_ID_COORDINATEUR,' de la table HERBIER_COORDONNE.'), '1', '1', '162.38.234.1'
 101 
		NOW(), CONCAT('Importation des relations de coordination de l''utilisateur #',HC_ID_COORDINATEUR,' de la table HERBIER_COORDONNE.'), '1', '1', '162.38.234.1'
102 
	FROM tb_herbiers.HERBIERS_COORDONNE
 102 
	FROM tb_herbiers.HERBIERS_COORDONNE
Line 103... Line 103...
103 
	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
 103 
	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
Line 115... Line 115...
115 
	 cp_ce_meta)
 115 
	 cp_ce_meta)
116 
	SELECT U_ID, '3', CONCAT(U_SURNAME,'',U_NAME) AS NOM_COMPLET, U_SURNAME, U_NAME,
 116 
	SELECT U_ID, '3', CONCAT(U_SURNAME,'',U_NAME) AS NOM_COMPLET, U_SURNAME, U_NAME,
117 
		   U_ADDR1, U_ADDR2, U_STATE, U_ZIP_CODE, U_CITY, CP.CP_Intitule_pays,
 117 
		   U_ADDR1, U_ADDR2, U_STATE, U_ZIP_CODE, U_CITY, CP.CP_Intitule_pays,
118 
		   LOWER(U_MAIL), U_WEB,
118 
		   LOWER(U_MAIL), U_WEB,
119 
		   CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE),
119 
		   CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE),
120 
		   IF(EDP_ID_DROIT = '1', 2022, IF(EDP_ID_DROIT = '2', 2023, IF(EDP_ID_DROIT = '3', 2024, 0))),
 120 
		   IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0))),
121 
		   U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),
121 
		   U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),
122 
		   @idm := @idm+1
 122 
		   @idm := @idm+1
123 
	FROM tela_prod_v4.annuaire_tela AS AT, tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP, tela_prod_v4.carto_PAYS AS CP
123 
	FROM tb_v4.annuaire_tela AS AT, tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP, tb_v4.carto_PAYS AS CP
124 
	WHERE AT.U_ID = EDP.EDP_ID_UTILISATEUR
124 
	WHERE AT.U_ID = EDP.EDP_ID_UTILISATEUR
125 
		AND CP.CP_ID_Pays = AT.U_COUNTRY
125 
		AND CP.CP_ID_Pays = AT.U_COUNTRY
126 
	ORDER BY U_ID ASC;
 126 
	ORDER BY U_ID ASC;
127 
INSERT INTO tb_coel.coel_meta_historique_ligne
127 
INSERT INTO tb_coel.coel_meta_historique_ligne
128 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,
128 
	(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)
129 
	cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
130 
	SELECT @idm_tmp := @idm_tmp+1, '111', U_ID, NULL, U_DATE,
 130 
	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'
 131 
		CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela.'), '1', '1', '162.38.234.1'
132 
	FROM tela_prod_v4.annuaire_tela AS AT, tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP
132 
	FROM tb_v4.annuaire_tela AS AT, tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP
133 
	WHERE AT.U_ID = EDP.EDP_ID_UTILISATEUR
133 
	WHERE AT.U_ID = EDP.EDP_ID_UTILISATEUR
134 
	ORDER BY U_ID ASC;
 134 
	ORDER BY U_ID ASC;
Line 135... Line 135...
135 
 
 135 
 
136 
#------------------------------------------------------------------------------------------------------------------------
 136 
#------------------------------------------------------------------------------------------------------------------------
Line 141... Line 141...
141 
	FROM tb_herbiers.HERBIERS_ADMINISTRER
141 
	FROM tb_herbiers.HERBIERS_ADMINISTRER
142 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
 142 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
143 
INSERT INTO tb_coel.coel_meta_historique_ligne
143 
INSERT INTO tb_coel.coel_meta_historique_ligne
144 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
144 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
145 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
 145 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
146 
	SELECT @idm_tmp := @idm_tmp+1, '118', CONCAT(HA_ID_ORG, '-', HA_ID_ANNUAIRE, '-2026'), NULL,
146 
	SELECT @idm_tmp := @idm_tmp+1, '120', CONCAT(HA_ID_ORG, '-', HA_ID_ANNUAIRE, '-2026'), NULL,
147 
		NOW(), CONCAT('Importation des de l''administrateur #',HA_ID_ANNUAIRE,' de la structure #', HA_ID_ORG,'de la table HERBIER_ADMINISTRER.'), '1', '1', '162.38.234.1'
 147 
		NOW(), CONCAT('Importation des de l''administrateur #',HA_ID_ANNUAIRE,' de la structure #', HA_ID_ORG,'de la table HERBIER_ADMINISTRER.'), '1', '1', '162.38.234.1'
148 
	FROM tb_herbiers.HERBIERS_ADMINISTRER
148 
	FROM tb_herbiers.HERBIERS_ADMINISTRER
149 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
 149 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
Line 159... Line 159...
159 
	FROM tb_herbiers.HERBIERS_STAFF
 159 
	FROM tb_herbiers.HERBIERS_STAFF
160 
	ORDER BY ID_STAFF ASC;
 160 
	ORDER BY ID_STAFF ASC;
161 
INSERT INTO tb_coel.coel_meta_historique_ligne
 161 
INSERT INTO tb_coel.coel_meta_historique_ligne
162 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
162 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
163 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
 163 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
164 
	SELECT @idm_tmp := @idm_tmp+1, '111', ID_STAFF, NULL, DATE_DERNIERE_MODIF,
 164 
	SELECT @idm_tmp := @idm_tmp+1, '113', ID_STAFF, NULL, DATE_DERNIERE_MODIF,
165 
		CONCAT('Importation de l''utilisateur #', ID_STAFF,'de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
 165 
		CONCAT('Importation de l''utilisateur #', ID_STAFF,' de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
166 
	FROM tb_herbiers.HERBIERS_STAFF
 166 
	FROM tb_herbiers.HERBIERS_STAFF
167 
	ORDER BY ID_STAFF ASC;
 167 
	ORDER BY ID_STAFF ASC;
Line 168... Line 168...
168 
 
 168 
 
169 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';
 169 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';
Line 189... Line 189...
189 
	WHERE hous.ID_ORG != 0
189 
	WHERE hous.ID_ORG != 0
190 
	ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;
 190 
	ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;
191 
INSERT INTO tb_coel.coel_meta_historique_ligne
191 
INSERT INTO tb_coel.coel_meta_historique_ligne
192 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
192 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
193 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
 193 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
194 
	SELECT @idm_tmp := @idm_tmp+1, '118', CONCAT(ID_ORG, '-', ID_STAFF, '-2027'), NULL,
194 
	SELECT @idm_tmp := @idm_tmp+1, '120', CONCAT(ID_ORG, '-', ID_STAFF, '-2027'), NULL,
195 
		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'
 195 
		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'
196 
	FROM tb_herbiers.HERBIERS_ont_un_staff
 196 
	FROM tb_herbiers.HERBIERS_ont_un_staff
197 
	WHERE ID_ORG != 0
197 
	WHERE ID_ORG != 0
198 
	ORDER BY ID_ORG, ID_STAFF ASC;
198 
	ORDER BY ID_ORG, ID_STAFF ASC;
Line 199... Line 199...
199
199
Line 205... Line 205...
205
205
206 
#------------------------------------------------------------------------------------------------------------------------
 206 
#------------------------------------------------------------------------------------------------------------------------
207 
#HERBIER_COLLECTION vers coel_collection
 207 
#HERBIER_COLLECTION vers coel_collection
208 
INSERT INTO tb_coel.coel_collection
 208 
INSERT INTO tb_coel.coel_collection
209 
	(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,
209 
	(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,
210 
	 cc_description_specialiste, cc_truk_url, cc_ce_type,
210 
	 cc_description_specialiste, cc_truk_url,
- 
 
 211 
	 cc_specimen_type_nbre,
211 
	 cc_specimen_type_nbre,
212 
	 cc_truk_preservation,
212 
    cc_ce_meta)
 213 
    cc_ce_meta)
213 
	SELECT ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",ID) AS GUID, COLLECTION_CODE, NOM_COLLECTION, DESCRIPTION, STATUT, IF(INCLUDED_TYPE = '0', '2236', IF (INCLUDED_TYPE = '1', '2239', IF (INCLUDED_TYPE = '2', '2238', NULL))) ,
 214 
	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,
214 
	   CONCAT('Etat documentation : \n',DOC_STATE,'\nCollecteurs : \n',COLLECTEURS), URL, INCLUDED_TYPE,
215 
	   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,
- 
 
 216 
	   NUM_SPECIMENS,
215 
	   NUM_SPECIMENS,
217 
	   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,
216 
	   @idm := @idm+1
 218 
	   @idm := @idm+1
- 
 
 219 
	FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)
217 
	FROM tb_herbiers.HERBIERS_COLLECTION
 220 
	GROUP BY hc.ID
218 
	ORDER BY ID ASC;
 221 
	ORDER BY hc.ID ASC;
219 
INSERT INTO tb_coel.coel_collection_botanique
222 
INSERT INTO tb_coel.coel_collection_botanique
220 
	(ccb_id_collection, ccb_truk_unite_base,
223 
	(ccb_id_collection, ccb_truk_unite_base,
221 
	ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type,
224 
	ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type,
222 
	ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
 225 
	ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
223 
	ccb_truk_degradation_specimen,
226 
	ccb_truk_degradation_specimen,
224 
	ccb_truk_degradation_presentation,
 227 
	ccb_truk_degradation_presentation,
225 
	ccb_annotation_classement)
 228 
	ccb_annotation_classement)
226 
	SELECT ID, CONCAT('TOTAL#0|NULL|NULL|',NUM_SPECIMENS,'|', IF(PREC_SPECIMENS = '1', '0', IF(PREC_SPECIMENS = '2', '1', IF(PREC_SPECIMENS = '3', '?', 'NULL'))), '|', NUM_SPECIES, '|', IF(PREC_SPECIES = '1', '0', IF(PREC_SPECIES = '2', '1', IF(PREC_SPECIES = '3', '?', 'NULL')))),
229 
	SELECT ID, CONCAT('TOTAL#0|NULL|NULL|',NUM_SPECIMENS,'|', IF(PREC_SPECIMENS = '1', '0', IF(PREC_SPECIMENS = '2', '1', IF(PREC_SPECIMENS = '3', '?', 'NULL'))), '|', NUM_SPECIES, '|', IF(PREC_SPECIES = '1', '0', IF(PREC_SPECIES = '2', '1', IF(PREC_SPECIES = '3', '?', 'NULL')))),
227 
		DATE_DEBUT, IF(DATE_DEBUT_CARAC = '1', '2313', IF (DATE_DEBUT_CARAC = '2', '2314', IF (DATE_DEBUT_CARAC = '3', '2315', IF(DATE_DEBUT_CARAC = '4', '2316' IF (DATE_DEBUT_CARAC = '5', '2317', NULL))))),
230 
		DATE_DEBUT, IF(DATE_DEBUT_CARAC = '1', '2316', IF (DATE_DEBUT_CARAC = '2', '2317', IF (DATE_DEBUT_CARAC = '3', '2318', IF(DATE_DEBUT_CARAC = '4', '2319', IF (DATE_DEBUT_CARAC = '5', '2320', NULL))))),
228 
		DATE_FIN, IF( DATE_FIN_CARAC = '1', '2318', IF (DATE_FIN_CARAC = '2', '2319', IF(DATE_FIN_CARAC = '3', '2320' IF (DATE_FIN_CARAC = '4', '2322', IF(DATE_FIN_CARAC = '5', '2321', IF (DATE_FIN_CARAC = '6', '2323', NULL)))))),
231 
		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)))))),
229 
		PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2325', IF(ETAT_CLASSEMENT = 2, '2326', IF(ETAT_CLASSEMENT = 3, '2327', IF(ETAT_CLASSEMENT = 4, '2328', NULL)))),
 232 
		PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),
230 
		CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2325', IF(ETAT_SPECIMENS = 2, '2326', IF(ETAT_SPECIMENS = 3, '2327', IF(ETAT_SPECIMENS = 4, '2328', NULL)))),
 233 
		CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),
231 
		CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2325', IF(ETAT_PRESENTATION = 2, '2326', IF(ETAT_PRESENTATION = 3, '2327', IF(ETAT_PRESENTATION = 4, '2328', NULL)))),
 234 
		CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),
232 
		IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', MODE_CLASSEMENT, NULL), NULL)
 235 
		IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', MODE_CLASSEMENT, NULL), NULL)
233 
	FROM tb_herbiers.HERBIERS_COLLECTION
 236 
	FROM tb_herbiers.HERBIERS_COLLECTION
234 
	ORDER BY ID ASC;
 237 
	ORDER BY ID ASC;
235 
INSERT INTO tb_coel.coel_meta_historique_ligne
238 
INSERT INTO tb_coel.coel_meta_historique_ligne
236 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
239 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
237 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
240 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
238 
	SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,
241 
	SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,
239 
		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');
242 
		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')
240 
	FROM tb.herbiers.HERBIERS_COLLECTION
 243 
	FROM tb_herbiers.HERBIERS_COLLECTION
241 
	ORDER BY ID ASC;
 - 
 
242 
 
 - 
 
243 
#------------------------------------------------------------------------------------------------------------------------
 - 
 
244 
# HERBIERS_ont_pres vers coel_collection
 - 
 
245 
UPDATE
 - 
 
246 
	cc_truk_preservation,
 - 
 
247
- 
 
248
- 
 
Line -... Line 244...
- 
 
 244 
	ORDER BY ID ASC;
- 
 
 245
- 
 
 246 
#------------------------------------------------------------------------------------------------------------------------
- 
 
 247 
#HERBIERS_INDIC vers coel_commentaires
- 
 
 248 
INSERT INTO tb_coel.coel_commentaire
- 
 
 249 
	(ccm_id_commentaire, ccm_ce_projet, ccm_titre,
- 
 
 250 
	ccm_texte, ccm_ponderation, ccm_ce_meta)
- 
 
 251 
	SELECT ID_INDIC, '2', IF(REM_INDIC IS NOT NULL, IF(REM_INDIC != '', REM_INDIC, CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...')),  CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...')) AS TITRE,
- 
 
 252 
		TXT_INDIC, 0, @idm := @idm+1
- 
 
 253 
	FROM tb_herbiers.HERBIERS_INDIC
- 
 
 254 
	ORDER BY ID_INDIC ASC;
- 
 
 255 
INSERT INTO tb_coel.coel_meta_historique_ligne
- 
 
 256 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
- 
 
 257 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
- 
 
 258 
	SELECT @idm_tmp := @idm_tmp+1, '107', hi.ID_INDIC, NULL,
- 
 
 259 
		FROM_UNIXTIME(MAJ_INDIC) , CONCAT('Ajout du commentaire #',hi.ID_INDIC,' de la table HERBIER_INDIC.'), IF(ID_INDICATEUR != '0', ID_INDICATEUR, '1'), '1', IF(ID_INDICATEUR != '0', NULL, '162.38.234.1')
Line -... Line 260...
- 
 
 260 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_INDIC_HISTORIQUE AS hih ON (hi.ID_INDIC = hih.ID_INDIC)
- 
 
 261 
	ORDER BY hi.ID_INDIC ASC;
- 
 
 262
- 
 
 263 
#------------------------------------------------------------------------------------------------------------------------
- 
 
 264 
#HERBIERS_A_UN_TYPE vers coel_collection_a_commentaire
- 
 
 265 
INSERT INTO tb_coel.coel_collection_a_commentaire
- 
 
 266 
	(ccac_id_collection, ccac_id_commentaire,
- 
 
 267 
	ccac_truk_type,
- 
 
 268 
	ccac_ce_meta)
- 
 
 269 
	SELECT hi.ID_INDIC, ID,
- 
 
 270 
		CAST(GROUP_CONCAT(DISTINCT CASE ID_TYPE WHEN '1' THEN '2368' WHEN '2' THEN '2369' WHEN '3' THEN '2370' WHEN '4' THEN '2371' WHEN '5' THEN '2372' WHEN '6' THEN '2373' ELSE NULL END SEPARATOR ";") AS CHAR)  AS TYPE,
- 
 
 271 
		@idm := @idm+1
- 
 
 272 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_A_UN_TYPE AS haut ON (hi.ID_INDIC = haut.ID_INDIC)
- 
 
 273 
	GROUP BY hi.ID_INDIC
- 
 
 274 
	ORDER BY hi.ID_INDIC ASC;
- 
 
 275 
INSERT INTO tb_coel.coel_meta_historique_ligne
- 
 
 276 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
- 
 
 277 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
- 
 
 278 
	SELECT @idm_tmp := @idm_tmp+1, '102', hi.ID_INDIC, NULL,
- 
 
 279 
		FROM_UNIXTIME(MAJ_INDIC) , CONCAT('Ajout des types du commentaire #',hi.ID_INDIC,' de la table HERBIERS_A_UN_TYPE.'), IF(ID_INDICATEUR != '0', ID_INDICATEUR, '1'), '1', IF(ID_INDICATEUR != '0', NULL, '162.38.234.1')
249
280 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_A_UN_TYPE AS haut ON (hi.ID_INDIC = haut.ID_INDIC) LEFT JOIN tb_herbiers.HERBIERS_INDIC_HISTORIQUE AS hih ON (hi.ID_INDIC = hih.ID_INDIC)