| Line 60... |
Line 60... |
| 60 |
ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG;# Classement des organisations par ID croissant
|
60 |
ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG;# Classement des organisations par ID croissant
|
| 61 |
#ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION DROP NUM_COLLECTION, DROP ADRESS_TEXT, DROP TIME_ZONE ;# Suppression des champs inutiles
|
61 |
#ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION DROP NUM_COLLECTION, DROP ADRESS_TEXT, DROP TIME_ZONE ;# Suppression des champs inutiles
|
| 62 |
SET @idm_tmp = @idm;# Identifiant pour les métadonnées temporaire
|
62 |
SET @idm_tmp = @idm;# Identifiant pour les métadonnées temporaire
|
| 63 |
INSERT INTO tb_coel.coel_structure
|
63 |
INSERT INTO tb_coel.coel_structure
|
| 64 |
(cs_id_structure, cs_ce_projet, cs_ce_mere, cs_guid, cs_truk_identifiant_alternatif,
|
64 |
(cs_id_structure, cs_ce_projet, cs_ce_mere, cs_guid, cs_truk_identifiant_alternatif,
|
| 65 |
cs_nom, cs_adresse_01, cs_code_postal, cs_ville, cs_region, cs_pays, cs_telephone, cs_fax, cs_courriel,
|
65 |
cs_nom, cs_adresse_01, cs_code_postal, cs_ville,
|
| - |
|
66 |
cs_ce_truk_region,
|
| - |
|
67 |
cs_ce_truk_pays,
|
| - |
|
68 |
cs_truk_telephone,
|
| - |
|
69 |
cs_courriel,
|
| 66 |
cs_description,
|
70 |
cs_description,
|
| 67 |
cs_truk_url,
|
71 |
cs_truk_url,
|
| 68 |
cs_condition_acces,
|
72 |
cs_condition_acces,
|
| 69 |
cs_ce_meta)
|
73 |
cs_ce_meta)
|
| 70 |
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,
|
74 |
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,
|
| 71 |
REPLACE(CONVERT(INSTITUTION_NAME USING utf8), '\r\n', ''), CONVERT(ADRESS_LINE USING utf8), ZIP, CONVERT(TOWN USING utf8), CONVERT(REGION USING utf8), IF(COUNTRY_CODE = 'fr', 'FR', COUNTRY_CODE) AS PAYS, TEL, FAX, EMAIL,
|
75 |
REPLACE(CONVERT(INSTITUTION_NAME USING utf8), '\r\n', ''), CONVERT(ADRESS_LINE USING utf8), ZIP, CONVERT(TOWN USING utf8),
|
| - |
|
76 |
IF(REGION != '', CONCAT('AUTRE##',CONVERT(REGION USING utf8)), NULL),
|
| - |
|
77 |
IF(COUNTRY_CODE = 'fr', '2654', CONCAT('AUTRE##', CONVERT(COUNTRY_CODE USING utf8))) AS PAYS,
|
| - |
|
78 |
CONCAT(IF(TEL != '', CONCAT('(+33)', REPLACE(TEL,'[33]','')), NULL), IF(FAX != '', CONCAT(IF(TEL != '', ';;', ''), 'FAX##(+33)', REPLACE(FAX,'[33]','')), NULL)),
|
| - |
|
79 |
EMAIL,
|
| 72 |
IF(SOURCE_DES_DONNEES != '', CONVERT(CONCAT("Source des données : ",SOURCE_DES_DONNEES) USING utf8), NULL) AS DESCRIPTION ,
|
80 |
IF(SOURCE_DES_DONNEES != '', CONVERT(CONCAT("Source des données : ",SOURCE_DES_DONNEES) USING utf8), NULL) AS DESCRIPTION,
|
| 73 |
CONCAT(IF(HO_URL != 'http://' AND HO_URL != '', CONCAT('WEB##',HO_URL), ''),IF(HO_URL != 'http://' AND HO_URL != '' AND LOGO != '', ';;', ''), IF(LOGO != '', CONCAT('LOGO##',LOGO),'')) AS URLS,
|
81 |
CONCAT(IF(HO_URL != 'http://' AND HO_URL != '', CONCAT('WEB##',HO_URL), ''), IF(HO_URL != 'http://' AND HO_URL != '' AND LOGO != '', ';;', ''), IF(LOGO != '', CONCAT('LOGO##',LOGO),'')) AS URLS,
|
| 74 |
CONVERT(ACCESS_RESTRICTION USING utf8),
|
82 |
CONVERT(ACCESS_RESTRICTION USING utf8),
|
| 75 |
@idm := @idm+1
|
83 |
@idm := @idm+1
|
| 76 |
FROM tb_herbiers.HERBIERS_ORGANISATION
|
84 |
FROM tb_herbiers.HERBIERS_ORGANISATION
|
| 77 |
ORDER BY ID_ORG ASC;
|
85 |
ORDER BY ID_ORG ASC;
|
| 78 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
86 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
| Line 83... |
Line 91... |
| 83 |
CONCAT('Importation de l''enregistrement #',ID_ORG,' de la table HERBIERS_ORGANISATION.'),
|
91 |
CONCAT('Importation de l''enregistrement #',ID_ORG,' de la table HERBIERS_ORGANISATION.'),
|
| 84 |
IF(CE_MODIFIER_PAR = '0', '1', CE_MODIFIER_PAR), '1', '162.38.234.1'
|
92 |
IF(CE_MODIFIER_PAR = '0', '1', CE_MODIFIER_PAR), '1', '162.38.234.1'
|
| 85 |
FROM tb_herbiers.HERBIERS_ORGANISATION
|
93 |
FROM tb_herbiers.HERBIERS_ORGANISATION
|
| 86 |
ORDER BY ID_ORG ASC;
|
94 |
ORDER BY ID_ORG ASC;
|
| Line -... |
Line 95... |
| - |
|
95 |
|
| - |
|
96 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '-', '') WHERE cs_truk_telephone LIKE '%-%';
|
| - |
|
97 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '.', '') WHERE cs_truk_telephone LIKE '%.%';
|
| - |
|
98 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, ' ', '') WHERE cs_truk_telephone LIKE '% %';
|
| - |
|
99 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '2;04', '2;;FIX##(+33)04') WHERE cs_truk_telephone LIKE '%2;04%';
|
| - |
|
100 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '1;04', '1;;FIX##(+33)04') WHERE cs_truk_telephone LIKE '%1;04%';
|
| - |
|
101 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, ',', ';; (+33)') WHERE cs_truk_telephone LIKE '%,%';
|
| - |
|
102 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '(+33)689', '(+689)') WHERE cs_truk_telephone LIKE '%(+33)689%';# Polynésie
|
| - |
|
103 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '(+33)(687)', '(+687)') WHERE cs_truk_telephone LIKE '%(+33)(687)%';# Nouvelle-Calédonie
|
| - |
|
104 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '(+33)594', '(+594)0594') WHERE cs_truk_telephone LIKE '%(+33)594%';# Guyanne
|
| - |
|
105 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '(+33)(590)(0)590', '(+590)0590') WHERE cs_truk_telephone LIKE '%(+33)(590)(0)590%';# Guadeloupe
|
| - |
|
106 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '(+33)(0)', '(+33)0') WHERE cs_truk_telephone LIKE '%(+33)(0)%';
|
| - |
|
107 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, ';;0', ';; (+33)0') WHERE cs_truk_telephone LIKE '%;;0%';
|
| - |
|
108 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = IF( cs_truk_telephone LIKE '(+33)06%', CONCAT('GSM##', cs_truk_telephone), CONCAT('FIX##', cs_truk_telephone)) WHERE cs_truk_telephone LIKE '(+33)__%';
|
| - |
|
109 |
UPDATE tb_coel.coel_structure SET cs_truk_telephone = IF(cs_truk_telephone LIKE '%;; (+33)06%', REPLACE(cs_truk_telephone, ';; (+33)', ';;GSM##(+33)'), REPLACE(cs_truk_telephone, ';; (+33)', ';;FIX##(+33)')) WHERE cs_truk_telephone LIKE '%;;%';
|
| 87 |
|
110 |
|
| 88 |
#------------------------------------------------------------------------------------------------------------------------
|
111 |
#------------------------------------------------------------------------------------------------------------------------
|
| 89 |
#EFLORE_DROIT_POSSEDER vers coel_personne_a_relation
|
112 |
#EFLORE_DROIT_POSSEDER vers coel_personne_a_relation
|
| 90 |
INSERT INTO tb_coel.coel_personne_a_relation
|
113 |
INSERT INTO tb_coel.coel_personne_a_relation
|
| 91 |
(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
|
114 |
(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
|
| Line 118... |
Line 141... |
| 118 |
#------------------------------------------------------------------------------------------------------------------------
|
141 |
#------------------------------------------------------------------------------------------------------------------------
|
| 119 |
# EFLORE_DROIT_POSSEDER et annuaire_tela vers coel_personne
|
142 |
# EFLORE_DROIT_POSSEDER et annuaire_tela vers coel_personne
|
| 120 |
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
|
143 |
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
|
| 121 |
INSERT INTO tb_coel.coel_personne
|
144 |
INSERT INTO tb_coel.coel_personne
|
| 122 |
(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
|
145 |
(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
|
| 123 |
cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,
|
146 |
cp_adresse_01, cp_adresse_02, cp_ce_truk_region, cp_code_postal, cp_ville, cp_ce_truk_pays,
|
| 124 |
cp_truk_courriel, cp_truk_url,
|
147 |
cp_truk_courriel, cp_truk_url,
|
| 125 |
cp_description,
|
148 |
cp_description,
|
| 126 |
cp_ce_truk_role,
|
149 |
cp_ce_truk_role,
|
| 127 |
cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,
|
150 |
cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,
|
| 128 |
cp_ce_meta)
|
151 |
cp_ce_meta)
|
| 129 |
SELECT U_ID, '3', CONCAT(CONVERT(U_SURNAME USING utf8),' ',UPPER(CONVERT(U_NAME USING utf8))) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), UPPER(CONVERT(U_NAME USING utf8)),
|
152 |
SELECT U_ID, '3', CONCAT(CONVERT(U_SURNAME USING utf8),' ',UPPER(CONVERT(U_NAME USING utf8))) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), UPPER(CONVERT(U_NAME USING utf8)),
|
| 130 |
CONVERT(U_ADDR1 USING utf8), CONVERT(U_ADDR2 USING utf8), CONVERT(U_STATE USING utf8), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONVERT(CP.CP_Intitule_pays USING utf8),
|
153 |
CONVERT(U_ADDR1 USING utf8), CONVERT(U_ADDR2 USING utf8), CONCAT('AUTRE##', CONVERT(U_STATE USING utf8)), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONCAT('AUTRE##', CONVERT(CP.CP_Intitule_pays USING utf8)),
|
| 131 |
LOWER(U_MAIL), U_WEB,
|
154 |
LOWER(U_MAIL), U_WEB,
|
| 132 |
CONCAT('Spécialités systématiques : ', CONVERT(U_SPE USING utf8), '\nSpécialités géographiques : ', CONVERT(U_GEO USING utf8), '\nFonction : ', CONVERT(U_FONCTION USING utf8), '\nStructure : ', CONVERT(U_TITLE USING utf8)),
|
155 |
CONCAT('Spécialités systématiques : ', CONVERT(U_SPE USING utf8), '\nSpécialités géographiques : ', CONVERT(U_GEO USING utf8), '\nFonction : ', CONVERT(U_FONCTION USING utf8), '\nStructure : ', CONVERT(U_TITLE USING utf8)),
|
| 133 |
IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0))),
|
156 |
IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0))),
|
| 134 |
U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),
|
157 |
U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),
|
| 135 |
@idm := @idm+1
|
158 |
@idm := @idm+1
|
| Line 149... |
Line 172... |
| 149 |
#------------------------------------------------------------------------------------------------------------------------
|
172 |
#------------------------------------------------------------------------------------------------------------------------
|
| 150 |
# HERBIERS_ADMINISTRER et annuaire_tela vers coel_personne
|
173 |
# HERBIERS_ADMINISTRER et annuaire_tela vers coel_personne
|
| 151 |
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
|
174 |
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
|
| 152 |
INSERT INTO tb_coel.coel_personne
|
175 |
INSERT INTO tb_coel.coel_personne
|
| 153 |
(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
|
176 |
(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
|
| 154 |
cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,
|
177 |
cp_adresse_01, cp_adresse_02, cp_ce_truk_region, cp_code_postal, cp_ville, cp_ce_truk_pays,
|
| 155 |
cp_truk_courriel, cp_truk_url,
|
178 |
cp_truk_courriel, cp_truk_url,
|
| 156 |
cp_description,
|
179 |
cp_description,
|
| 157 |
cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,
|
180 |
cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,
|
| 158 |
cp_ce_meta)
|
181 |
cp_ce_meta)
|
| 159 |
SELECT DISTINCT U_ID, '3', CONCAT(CONVERT(U_SURNAME USING utf8),' ',UPPER(CONVERT(U_NAME USING utf8))) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), UPPER(CONVERT(U_NAME USING utf8)),
|
182 |
SELECT DISTINCT U_ID, '3', CONCAT(CONVERT(U_SURNAME USING utf8),' ',UPPER(CONVERT(U_NAME USING utf8))) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), UPPER(CONVERT(U_NAME USING utf8)),
|
| 160 |
CONVERT(U_ADDR1 USING utf8), CONVERT(U_ADDR2 USING utf8), CONVERT(U_STATE USING utf8), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONVERT(CP.CP_Intitule_pays USING utf8),
|
183 |
CONVERT(U_ADDR1 USING utf8), CONVERT(U_ADDR2 USING utf8), CONCAT('AUTRE##', CONVERT(U_STATE USING utf8)), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONCAT('AUTRE##', CONVERT(CP.CP_Intitule_pays USING utf8)),
|
| 161 |
LOWER(U_MAIL), U_WEB,
|
184 |
LOWER(U_MAIL), U_WEB,
|
| 162 |
CONCAT('Spécialités systématiques : ', CONVERT(U_SPE USING utf8), '\nSpécialités géographiques : ', CONVERT(U_GEO USING utf8), '\nFonction : ', CONVERT(U_FONCTION USING utf8), '\nStructure : ', CONVERT(U_TITLE USING utf8)),
|
185 |
CONCAT('Spécialités systématiques : ', CONVERT(U_SPE USING utf8), '\nSpécialités géographiques : ', CONVERT(U_GEO USING utf8), '\nFonction : ', CONVERT(U_FONCTION USING utf8), '\nStructure : ', CONVERT(U_TITLE USING utf8)),
|
| 163 |
U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),
|
186 |
U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),
|
| 164 |
@idm := @idm+1
|
187 |
@idm := @idm+1
|
| 165 |
FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA
|
188 |
FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA
|
| Line 195... |
Line 218... |
| 195 |
|
218 |
|
| 196 |
#------------------------------------------------------------------------------------------------------------------------
|
219 |
#------------------------------------------------------------------------------------------------------------------------
|
| 197 |
#HERBIERS_STAFF vers coel_personne
|
220 |
#HERBIERS_STAFF vers coel_personne
|
| 198 |
INSERT INTO tb_coel.coel_personne
|
221 |
INSERT INTO tb_coel.coel_personne
|
| 199 |
(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,
|
222 |
(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,
|
| - |
|
223 |
cp_truk_telephone,
|
| 200 |
cp_truk_telephone, cp_truk_fax, cp_ce_meta)
|
224 |
cp_ce_meta)
|
| 201 |
SELECT 100000+ID_STAFF, '2', CONCAT(CONVERT(PRENOM USING utf8),' ', UPPER(CONVERT(NOM USING utf8))) AS NOM_COMPLET, UPPER(CONVERT(NOM USING utf8)), CONVERT(PRENOM USING utf8), CONVERT(ADRESSE1 USING utf8), CONVERT(ADRESSE2 USING utf8), CP, CONVERT(VILLE USING utf8), LOWER(MAIL),
|
225 |
SELECT 100000+ID_STAFF, '2', CONCAT(CONVERT(PRENOM USING utf8),' ', UPPER(CONVERT(NOM USING utf8))) AS NOM_COMPLET, UPPER(CONVERT(NOM USING utf8)), CONVERT(PRENOM USING utf8), CONVERT(ADRESSE1 USING utf8), CONVERT(ADRESSE2 USING utf8), CP, CONVERT(VILLE USING utf8), LOWER(MAIL),
|
| - |
|
226 |
CONCAT(IF(TEL != '', CONCAT('(+33)', REPLACE(TEL,'[33]','')), NULL), IF(FAX != '', CONCAT(IF(TEL != '', ';;', ''), 'FAX##(+33)', REPLACE(FAX,'[33]','')), NULL)),
|
| 202 |
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
|
227 |
@idm := @idm+1
|
| 203 |
FROM tb_herbiers.HERBIERS_STAFF
|
228 |
FROM tb_herbiers.HERBIERS_STAFF
|
| 204 |
ORDER BY ID_STAFF ASC;
|
229 |
ORDER BY ID_STAFF ASC;
|
| 205 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
230 |
INSERT INTO tb_coel.coel_meta_historique_ligne
|
| 206 |
(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
|
231 |
(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
|
| 207 |
cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
|
232 |
cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
|
| 208 |
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()),
|
233 |
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()),
|
| 209 |
CONCAT('Importation de l''utilisateur #', 100000+ID_STAFF,' de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
|
234 |
CONCAT('Importation de l''utilisateur #', 100000+ID_STAFF,' de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
|
| 210 |
FROM tb_herbiers.HERBIERS_STAFF
|
235 |
FROM tb_herbiers.HERBIERS_STAFF
|
| Line 211... |
Line -... |
| 211 |
ORDER BY ID_STAFF ASC;
|
- |
|
| 212 |
|
236 |
ORDER BY ID_STAFF ASC;
|
| - |
|
237 |
|
| 213 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';
|
238 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ' ', '') WHERE cp_truk_telephone LIKE '% %';
|
| - |
|
239 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '-', '') WHERE cp_truk_telephone LIKE '%-%';
|
| - |
|
240 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';
|
| 214 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ' ', '') WHERE cp_truk_telephone LIKE '% %';
|
241 |
# Gérer les séparateurs ; au cas par cas
|
| 215 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '+33(0)', '') WHERE cp_truk_telephone LIKE '%+33(0)%';
|
242 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '1;04', '1;;FIX##(+33)04') WHERE cp_truk_telephone LIKE '%1;04%';
|
| 216 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;%', REPLACE(cp_truk_telephone, ';0', ';; +33'),cp_truk_telephone);
|
243 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ',', ';; (+33)') WHERE cp_truk_telephone LIKE '%,%';
|
| 217 |
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));
|
244 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)+33', '(+33)') WHERE cp_truk_telephone LIKE '%(+33)+33%';
|
| 218 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%+330%', REPLACE(cp_truk_telephone, '+330', '+33'),cp_truk_telephone);
|
- |
|
| 219 |
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 '%;;%';
|
245 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)689', '(+689)') WHERE cp_truk_telephone LIKE '%(+33)689%';# Polynésie
|
| - |
|
246 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)(687)', '(+687)') WHERE cp_truk_telephone LIKE '%(+33)(687)%';# Nouvelle-Calédonie
|
| 220 |
|
247 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)594', '(+594)0594') WHERE cp_truk_telephone LIKE '%(+33)594%';# Guyanne
|
| 221 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '.', '') WHERE cp_truk_fax LIKE '%.%';
|
248 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)(590)(0)590', '(+590)0590') WHERE cp_truk_telephone LIKE '%(+33)(590)(0)590%';# Guadeloupe
|
| 222 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '+33 (0)', '') WHERE cp_truk_fax LIKE '%+33 (0)%';
|
249 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)(0)', '(+33)0') WHERE cp_truk_telephone LIKE '%(+33)(0)%';
|
| - |
|
250 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ';;0', ';; (+33)0') WHERE cp_truk_telephone LIKE '%;;0%';
|
| Line 223... |
Line 251... |
| 223 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, ' ', '') WHERE cp_truk_fax LIKE '% %';
|
251 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF( cp_truk_telephone LIKE '(+33)06%', CONCAT('GSM##', cp_truk_telephone), CONCAT('FIX##', cp_truk_telephone)) WHERE cp_truk_telephone LIKE '(+33)__%';
|
| Line 224... |
Line 252... |
| 224 |
UPDATE tb_coel.coel_personne SET cp_truk_fax = IF(cp_truk_fax LIKE '%+330%', REPLACE(cp_truk_fax, '+330', '+33'),cp_truk_fax);
|
252 |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;; (+33)06%', REPLACE(cp_truk_telephone, ';; (+33)', ';;GSM##(+33)'), REPLACE(cp_truk_telephone, ';; (+33)', ';;FIX##(+33)')) WHERE cp_truk_telephone LIKE '%;;%';
|
| 225 |
|
253 |
|