| 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
|
| 5 |
|
5 |
|
| 6 |
SET @idm = 761;# Identifiant pour les métadonnées
|
6 |
SET @idm = 761;# 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
|
| 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
|
16 |
# coel_structure_a_personne
|
| 17 |
DELETE FROM tb_coel.coel_structure_a_personne WHERE csap_id_role IN ('2026', '2027');
|
17 |
DELETE FROM tb_coel.coel_structure_a_personne WHERE csap_id_role IN ('2026', '2027');
|
| 18 |
# coel_personne
|
18 |
# coel_personne
|
| 19 |
DELETE FROM tb_coel.coel_personne WHERE cp_ce_projet IN ('2', '3');
|
19 |
DELETE FROM tb_coel.coel_personne WHERE cp_ce_projet IN ('2', '3');
|
| 20 |
# coel_personne_a_relation
|
20 |
# coel_personne_a_relation
|
| 21 |
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
|
22 |
# coel_collection_botanique
|
| 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'));
|
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
|
24 |
# coel_collection_a_commentaire
|
| 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'));
|
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
|
26 |
# coel_collection
|
| 27 |
DELETE FROM tb_coel.coel_collection WHERE cc_ce_projet IN ('2');
|
27 |
DELETE FROM tb_coel.coel_collection WHERE cc_ce_projet IN ('2');
|
| 28 |
# coel_commentaire
|
28 |
# coel_commentaire
|
| 29 |
DELETE FROM tb_coel.coel_commentaire WHERE ccm_ce_projet IN ('2');
|
29 |
DELETE FROM tb_coel.coel_commentaire WHERE ccm_ce_projet IN ('2');
|
| 30 |
|
30 |
|
| 31 |
#---------------------------------------------------------------------------------------------------------------------#
|
31 |
#---------------------------------------------------------------------------------------------------------------------#
|
| 32 |
# EFLORE_PROJET vers coel_projet
|
32 |
# EFLORE_PROJET vers coel_projet
|
| 33 |
# Insertion du projet Inventaire des Herbiers de France - Version 1
|
33 |
# Insertion du projet Inventaire des Herbiers de France - Version 1
|
| 34 |
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)
|
| 35 |
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
|
| 36 |
FROM tb_herbiers.EFLORE_PROJET;
|
36 |
FROM tb_herbiers.EFLORE_PROJET;
|
| 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)
|
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)
|
| 38 |
VALUES (@idm := @idm+1, '115', '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');
|
| 39 |
# Insertion du projet Utilisateur de l''application COEL - Version 1
|
39 |
# Insertion du projet Utilisateur de l''application COEL - Version 1
|
| 40 |
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)
|
| 41 |
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);
|
| 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)
|
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)
|
| 43 |
VALUES (@idm := @idm+1, '115', '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');
|
| 44 |
|
44 |
|
| 45 |
|
45 |
|
| 46 |
#------------------------------------------------------------------------------------------------------------------------
|
46 |
#------------------------------------------------------------------------------------------------------------------------
|
| 47 |
# HERBIERS_ORGANISATION vers coel_structure
|
47 |
# HERBIERS_ORGANISATION vers coel_structure
|
| 48 |
ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG;# Classement des organisations par ID croissant
|
48 |
ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG;# Classement des organisations par ID croissant
|
| 49 |
#ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION DROP NUM_COLLECTION, DROP ADRESS_TEXT, DROP TIME_ZONE ;# Suppression des champs inutiles
|
49 |
#ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION DROP NUM_COLLECTION, DROP ADRESS_TEXT, DROP TIME_ZONE ;# Suppression des champs inutiles
|
| 50 |
SET @idm_tmp = @idm;# Identifiant pour les métadonnées temporaire
|
50 |
SET @idm_tmp = @idm;# Identifiant pour les métadonnées temporaire
|
| 51 |
INSERT INTO tb_coel.coel_structure
|
51 |
INSERT INTO tb_coel.coel_structure
|
| 52 |
(cs_id_structure, cs_ce_projet, cs_ce_mere, cs_truk_identifiant_alternatif, cs_guid,
|
52 |
(cs_id_structure, cs_ce_projet, cs_ce_mere, cs_truk_identifiant_alternatif, cs_guid,
|
| 53 |
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,
|
| 54 |
cs_description,
|
54 |
cs_description,
|
| 55 |
cs_truk_url,
|
55 |
cs_truk_url,
|
| 56 |
cs_condition_acces,
|
56 |
cs_condition_acces,
|
| 57 |
cs_ce_meta)
|
57 |
cs_ce_meta)
|
| 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,
|
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,
|
| 59 |
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,
|
| 60 |
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,
|
| 61 |
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,
|
| 62 |
ACCESS_RESTRICTION,
|
62 |
ACCESS_RESTRICTION,
|
| 63 |
@idm := @idm+1
|
63 |
@idm := @idm+1
|
| 64 |
FROM tb_herbiers.HERBIERS_ORGANISATION
|
64 |
FROM tb_herbiers.HERBIERS_ORGANISATION
|
| 65 |
ORDER BY ID_ORG ASC;
|
65 |
ORDER BY ID_ORG ASC;
|
| 66 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
66 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
| 67 |
(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,
|
| 68 |
cmhl_notes,
|
68 |
cmhl_notes,
|
| 69 |
cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
|
69 |
cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
|
| 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),
|
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),
|
| 71 |
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.'),
|
| 72 |
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'
|
| 73 |
FROM tb_herbiers.HERBIERS_ORGANISATION
|
73 |
FROM tb_herbiers.HERBIERS_ORGANISATION
|
| 74 |
ORDER BY ID_ORG ASC;
|
74 |
ORDER BY ID_ORG ASC;
|
| 75 |
|
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, '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(), 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;
|
| 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, '114', 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
|
| 103 |
ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
|
103 |
ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
|
| 104 |
|
104 |
|
| 105 |
#------------------------------------------------------------------------------------------------------------------------
|
105 |
#------------------------------------------------------------------------------------------------------------------------
|
| 106 |
#annuaire_tela vers coel_personne
|
106 |
#annuaire_tela vers coel_personne
|
| 107 |
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
|
107 |
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
|
| 108 |
INSERT INTO tb_coel.coel_personne
|
108 |
INSERT INTO tb_coel.coel_personne
|
| 109 |
(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
|
109 |
(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
|
| 110 |
cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,
|
110 |
cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,
|
| 111 |
cp_truk_courriel, cp_truk_url,
|
111 |
cp_truk_courriel, cp_truk_url,
|
| 112 |
cp_description,
|
112 |
cp_description,
|
| 113 |
cp_ce_truk_role,
|
113 |
cp_ce_truk_role,
|
| 114 |
cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,
|
114 |
cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,
|
| 115 |
cp_ce_meta)
|
115 |
cp_ce_meta)
|
| 116 |
SELECT U_ID, '3', CONCAT(U_SURNAME,' ',UPPER(U_NAME)) AS NOM_COMPLET, U_SURNAME, UPPER(U_NAME),
|
116 |
SELECT U_ID, '3', CONCAT(U_SURNAME,' ',UPPER(U_NAME)) AS NOM_COMPLET, U_SURNAME, UPPER(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 tb_v4.annuaire_tela AS AT, tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP, tb_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, '113', 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 tb_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;
|
| 135 |
|
135 |
|
| 136 |
#------------------------------------------------------------------------------------------------------------------------
|
136 |
#------------------------------------------------------------------------------------------------------------------------
|
| 137 |
# HERBIERS_ADMINISTRER vers coel_structure_a_personne
|
137 |
# HERBIERS_ADMINISTRER vers coel_structure_a_personne
|
| 138 |
INSERT INTO tb_coel.coel_structure_a_personne
|
138 |
INSERT INTO tb_coel.coel_structure_a_personne
|
| 139 |
(csap_id_structure, csap_id_personne, csap_id_role, csap_ce_meta)
|
139 |
(csap_id_structure, csap_id_personne, csap_id_role, csap_ce_meta)
|
| 140 |
SELECT HA_ID_ORG, HA_ID_ANNUAIRE, '2026', @idm := @idm+1
|
140 |
SELECT HA_ID_ORG, HA_ID_ANNUAIRE, '2026', @idm := @idm+1
|
| 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, '120', 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;
|
| 150 |
|
150 |
|
| 151 |
|
151 |
|
| 152 |
#------------------------------------------------------------------------------------------------------------------------
|
152 |
#------------------------------------------------------------------------------------------------------------------------
|
| 153 |
#HERBIERS_STAFF vers coel_personne
|
153 |
#HERBIERS_STAFF vers coel_personne
|
| 154 |
INSERT INTO tb_coel.coel_personne
|
154 |
INSERT INTO tb_coel.coel_personne
|
| 155 |
(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_nom, cp_prenom, cp_adresse_01, cp_adresse_02, cp_code_postal, cp_ville, cp_truk_courriel,
|
155 |
(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_nom, cp_prenom, cp_adresse_01, cp_adresse_02, cp_code_postal, cp_ville, cp_truk_courriel,
|
| 156 |
cp_truk_telephone, cp_truk_fax, cp_ce_meta)
|
156 |
cp_truk_telephone, cp_truk_fax, cp_ce_meta)
|
| 157 |
SELECT 100000+ID_STAFF, '2', CONCAT(PRENOM,' ', UPPER(NOM)) AS NOM_COMPLET, UPPER(NOM), PRENOM, ADRESSE1, ADRESSE2, CP, VILLE, LOWER(MAIL),
|
157 |
SELECT 100000+ID_STAFF, '2', CONCAT(PRENOM,' ', UPPER(NOM)) AS NOM_COMPLET, UPPER(NOM), PRENOM, ADRESSE1, ADRESSE2, CP, VILLE, LOWER(MAIL),
|
| 158 |
IF(TEL != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(TEL,'[33]',''))), NULL), IF(FAX != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(FAX,'[33]',''))), NULL), @idm := @idm+1
|
158 |
IF(TEL != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(TEL,'[33]',''))), NULL), IF(FAX != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(FAX,'[33]',''))), NULL), @idm := @idm+1
|
| 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, '113', ID_STAFF, NULL, DATE_DERNIERE_MODIF,
|
164 |
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()),
|
| 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;
|
| 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 '%.%';
|
| 170 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ' ', '') WHERE cp_truk_telephone LIKE '% %';
|
170 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ' ', '') WHERE cp_truk_telephone LIKE '% %';
|
| 171 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '+33(0)', '') WHERE cp_truk_telephone LIKE '%+33(0)%';
|
171 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '+33(0)', '') WHERE cp_truk_telephone LIKE '%+33(0)%';
|
| 172 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;%', REPLACE(cp_truk_telephone, ';0', '; +33'),cp_truk_telephone);
|
172 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;%', REPLACE(cp_truk_telephone, ';0', '; +33'),cp_truk_telephone);
|
| 173 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '+336%', CONCAT('GSM#', cp_truk_telephone), CONCAT('FIX#', cp_truk_telephone));
|
173 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '+336%', CONCAT('GSM#', cp_truk_telephone), CONCAT('FIX#', cp_truk_telephone));
|
| 174 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%+330%', REPLACE(cp_truk_telephone, '+330', '+33'),cp_truk_telephone);
|
174 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%+330%', REPLACE(cp_truk_telephone, '+330', '+33'),cp_truk_telephone);
|
| 175 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%; +336%', REPLACE(cp_truk_telephone, '; +33', '; GSM#+33'), REPLACE(cp_truk_telephone, '; +33', '; FIX#+33')) WHERE cp_truk_telephone LIKE '%;%';
|
175 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%; +336%', REPLACE(cp_truk_telephone, '; +33', '; GSM#+33'), REPLACE(cp_truk_telephone, '; +33', '; FIX#+33')) WHERE cp_truk_telephone LIKE '%;%';
|
| 176 |
|
176 |
|
| 177 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '.', '') WHERE cp_truk_fax LIKE '%.%';
|
177 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '.', '') WHERE cp_truk_fax LIKE '%.%';
|
| 178 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '+33 (0)', '') WHERE cp_truk_fax LIKE '%+33 (0)%';
|
178 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '+33 (0)', '') WHERE cp_truk_fax LIKE '%+33 (0)%';
|
| 179 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, ' ', '') WHERE cp_truk_fax LIKE '% %';
|
179 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, ' ', '') WHERE cp_truk_fax LIKE '% %';
|
| 180 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = IF(cp_truk_fax LIKE '%+330%', REPLACE(cp_truk_fax, '+330', '+33'),cp_truk_fax);
|
180 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = IF(cp_truk_fax LIKE '%+330%', REPLACE(cp_truk_fax, '+330', '+33'),cp_truk_fax);
|
| 181 |
|
181 |
|
| 182 |
|
182 |
|
| 183 |
#------------------------------------------------------------------------------------------------------------------------
|
183 |
#------------------------------------------------------------------------------------------------------------------------
|
| 184 |
# HERBIERS_ont_un_staff vers coel_structure_a_personne
|
184 |
# HERBIERS_ont_un_staff vers coel_structure_a_personne
|
| 185 |
INSERT INTO tb_coel.coel_structure_a_personne
|
185 |
INSERT INTO tb_coel.coel_structure_a_personne
|
| 186 |
(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)
|
186 |
(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)
|
| 187 |
SELECT ID_ORG, 100000+hous.ID_STAFF, '2027', IF(LOWER(CONTACT) = 'oui', 1, 0) AS CONTACT, IF(FONCTION != '', CONCAT('AUTRE#', FONCTION), NULL) AS FONCTION, @idm := @idm+1
|
187 |
SELECT ID_ORG, 100000+hous.ID_STAFF, '2027', IF(LOWER(CONTACT) = 'oui', 1, 0) AS CONTACT, IF(FONCTION != '', CONCAT('AUTRE#', FONCTION), NULL) AS FONCTION, @idm := @idm+1
|
| 188 |
FROM tb_herbiers.HERBIERS_ont_un_staff AS hous LEFT JOIN tb_herbiers.HERBIERS_STAFF AS hs ON (hous.ID_STAFF = hs.ID_STAFF)
|
188 |
FROM tb_herbiers.HERBIERS_ont_un_staff AS hous LEFT JOIN tb_herbiers.HERBIERS_STAFF AS hs ON (hous.ID_STAFF = hs.ID_STAFF)
|
| 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, '120', 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;
|
| 199 |
|
199 |
|
| 200 |
UPDATE tb_coel.coel_structure_a_personne
|
200 |
UPDATE tb_coel.coel_structure_a_personne
|
| 201 |
SET csap_ce_truk_fonction = IF(csap_ce_truk_fonction IS NOT NULL, IF(csap_ce_truk_fonction LIKE '%irecteur%', '2028',
|
201 |
SET csap_ce_truk_fonction = IF(csap_ce_truk_fonction IS NOT NULL, IF(csap_ce_truk_fonction LIKE '%irecteur%', '2028',
|
| 202 |
IF(csap_ce_truk_fonction LIKE '%onservateur%', '2029', IF(csap_ce_truk_fonction LIKE '%echnicien%', '2030', csap_ce_truk_fonction))), NULL)
|
202 |
IF(csap_ce_truk_fonction LIKE '%onservateur%', '2029', IF(csap_ce_truk_fonction LIKE '%echnicien%', '2030', csap_ce_truk_fonction))), NULL)
|
| 203 |
WHERE csap_id_role = '2027' ;
|
203 |
WHERE csap_id_role = '2027' ;
|
| 204 |
|
204 |
|
| 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,
|
210 |
cc_description_specialiste, cc_truk_url,
|
| 211 |
cc_specimen_type_nbre,
|
211 |
cc_specimen_type_nbre,
|
| 212 |
cc_truk_preservation,
|
212 |
cc_truk_preservation,
|
| 213 |
cc_ce_meta)
|
213 |
cc_ce_meta)
|
| 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 |
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,
|
| 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,
|
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,
|
216 |
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,
|
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,
|
| 218 |
@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)
|
219 |
FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)
|
| 220 |
GROUP BY hc.ID
|
220 |
GROUP BY hc.ID
|
| 221 |
ORDER BY hc.ID ASC;
|
221 |
ORDER BY hc.ID ASC;
|
| 222 |
INSERT INTO tb_coel.coel_collection_botanique
|
222 |
INSERT INTO tb_coel.coel_collection_botanique
|
| 223 |
(ccb_id_collection, ccb_truk_unite_base,
|
223 |
(ccb_id_collection, ccb_truk_unite_base,
|
| 224 |
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,
|
| 225 |
ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
|
225 |
ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
|
| 226 |
ccb_truk_degradation_specimen,
|
226 |
ccb_truk_degradation_specimen,
|
| 227 |
ccb_truk_degradation_presentation,
|
227 |
ccb_truk_degradation_presentation,
|
| 228 |
ccb_annotation_classement)
|
228 |
ccb_annotation_classement)
|
| 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')))),
|
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')))),
|
| 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))))),
|
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))))),
|
| 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)))))),
|
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)))))),
|
| 232 |
PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', 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)))),
|
| 233 |
CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', 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))))),
|
| 234 |
CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', 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))))),
|
| 235 |
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)
|
| 236 |
FROM tb_herbiers.HERBIERS_COLLECTION
|
236 |
FROM tb_herbiers.HERBIERS_COLLECTION
|
| 237 |
ORDER BY ID ASC;
|
237 |
ORDER BY ID ASC;
|
| 238 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
238 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
| 239 |
(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,
|
| 240 |
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)
|
| 241 |
SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,
|
241 |
SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,
|
| 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')
|
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')
|
| 243 |
FROM tb_herbiers.HERBIERS_COLLECTION
|
243 |
FROM tb_herbiers.HERBIERS_COLLECTION
|
| 244 |
ORDER BY ID ASC;
|
244 |
ORDER BY ID ASC;
|
| 245 |
|
245 |
|
| 246 |
#------------------------------------------------------------------------------------------------------------------------
|
246 |
#------------------------------------------------------------------------------------------------------------------------
|
| 247 |
#HERBIERS_INDIC vers coel_commentaires
|
247 |
#HERBIERS_INDIC vers coel_commentaires
|
| 248 |
INSERT INTO tb_coel.coel_commentaire
|
248 |
INSERT INTO tb_coel.coel_commentaire
|
| 249 |
(ccm_id_commentaire, ccm_ce_projet, ccm_titre,
|
249 |
(ccm_id_commentaire, ccm_ce_projet, ccm_titre,
|
| 250 |
ccm_texte, ccm_ponderation, ccm_ce_meta)
|
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,
|
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
|
252 |
TXT_INDIC, 0, @idm := @idm+1
|
| 253 |
FROM tb_herbiers.HERBIERS_INDIC
|
253 |
FROM tb_herbiers.HERBIERS_INDIC
|
| 254 |
ORDER BY ID_INDIC ASC;
|
254 |
ORDER BY ID_INDIC ASC;
|
| 255 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
255 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
| 256 |
(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
|
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)
|
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,
|
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')
|
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')
|
| 260 |
FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_INDIC_HISTORIQUE AS hih ON (hi.ID_INDIC = hih.ID_INDIC)
|
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;
|
261 |
ORDER BY hi.ID_INDIC ASC;
|
| 262 |
|
262 |
|
| 263 |
#------------------------------------------------------------------------------------------------------------------------
|
263 |
#------------------------------------------------------------------------------------------------------------------------
|
| 264 |
# HERBIERS_A_UN_TYPE vers coel_collection_a_commentaire
|
264 |
# HERBIERS_A_UN_TYPE vers coel_collection_a_commentaire
|
| 265 |
INSERT INTO tb_coel.coel_collection_a_commentaire
|
265 |
INSERT INTO tb_coel.coel_collection_a_commentaire
|
| 266 |
(ccac_id_collection, ccac_id_commentaire,
|
266 |
(ccac_id_collection, ccac_id_commentaire,
|
| 267 |
ccac_truk_type,
|
267 |
ccac_truk_type,
|
| 268 |
ccac_ce_meta)
|
268 |
ccac_ce_meta)
|
| 269 |
SELECT hi.ID_INDIC, ID,
|
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,
|
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
|
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)
|
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
|
273 |
GROUP BY hi.ID_INDIC
|
| 274 |
ORDER BY hi.ID_INDIC ASC;
|
274 |
ORDER BY hi.ID_INDIC ASC;
|
| 275 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
275 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
| 276 |
(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
|
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)
|
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,
|
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')
|
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')
|
| 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)
|
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)
|
| 281 |
GROUP BY hi.ID_INDIC
|
281 |
GROUP BY hi.ID_INDIC
|
| 282 |
ORDER BY hi.ID_INDIC ASC;
|
282 |
ORDER BY hi.ID_INDIC ASC;
|
| 283 |
|
283 |
|
| 284 |
#------------------------------------------------------------------------------------------------------------------------
|
284 |
#------------------------------------------------------------------------------------------------------------------------
|
| 285 |
# Mise à jour des id de personne inexistant dans l''annuaire Tela
|
285 |
# Mise à jour des id de personne inexistant dans l''annuaire Tela
|
| 286 |
UPDATE tb_coel.coel_meta_historique_ligne
|
286 |
UPDATE tb_coel.coel_meta_historique_ligne
|
| 287 |
SET cmhl_ce_modifier_par = 1
|
287 |
SET cmhl_ce_modifier_par = 1
|
| 288 |
WHERE cmhl_ce_modifier_par NOT IN (SELECT U_ID FROM tb_v4.annuaire_tela);
|
288 |
WHERE cmhl_ce_modifier_par NOT IN (SELECT U_ID FROM tb_v4.annuaire_tela);
|