| Line 61... |
Line 61... |
| 61 |
DROP TABLE IF EXISTS `cel_mots_cles_images_liaison` ;
|
61 |
DROP TABLE IF EXISTS `cel_mots_cles_images_liaison` ;
|
| Line 62... |
Line 62... |
| 62 |
|
62 |
|
| 63 |
CREATE TABLE IF NOT EXISTS `cel_mots_cles_images_liaison` (
|
63 |
CREATE TABLE IF NOT EXISTS `cel_mots_cles_images_liaison` (
|
| 64 |
`id_element_lie` INT NOT NULL ,
|
64 |
`id_element_lie` INT NOT NULL ,
|
| - |
|
65 |
`id_mot_cle` INT NOT NULL ,
|
| 65 |
`id_mot_cle` INT NOT NULL ,
|
66 |
`date_liaison` DATETIME DEFAULT NOW(),
|
| 66 |
PRIMARY KEY (`id_element_lie`, `id_mot_cle`) )
|
67 |
PRIMARY KEY (`id_element_lie`, `id_mot_cle`) )
|
| Line 67... |
Line 68... |
| 67 |
ENGINE = MyISAM;
|
68 |
ENGINE = MyISAM;
|
| Line 168... |
Line 169... |
| 168 |
DROP TABLE IF EXISTS `cel_mots_cles_obs_liaison` ;
|
169 |
DROP TABLE IF EXISTS `cel_mots_cles_obs_liaison` ;
|
| Line 169... |
Line 170... |
| 169 |
|
170 |
|
| 170 |
CREATE TABLE IF NOT EXISTS `cel_mots_cles_obs_liaison` (
|
171 |
CREATE TABLE IF NOT EXISTS `cel_mots_cles_obs_liaison` (
|
| 171 |
`id_element_lie` INT NOT NULL ,
|
172 |
`id_element_lie` INT NOT NULL ,
|
| - |
|
173 |
`id_mot_cle` INT NOT NULL ,
|
| 172 |
`id_mot_cle` INT NOT NULL ,
|
174 |
`date_liaison` DATETIME DEFAULT NOW(),
|
| 173 |
PRIMARY KEY (`id_element_lie`, `id_mot_cle`) )
|
175 |
PRIMARY KEY (`id_element_lie`, `id_mot_cle`) )
|
| Line 174... |
Line 176... |
| 174 |
ENGINE = MyISAM;
|
176 |
ENGINE = MyISAM;
|
| Line 275... |
Line 277... |
| 275 |
-- -----------------------------------------------------
|
277 |
-- -----------------------------------------------------
|
| 276 |
-- View `cel_utilisateurs`
|
278 |
-- View `cel_utilisateurs`
|
| 277 |
-- -----------------------------------------------------
|
279 |
-- -----------------------------------------------------
|
| 278 |
DROP VIEW IF EXISTS `cel_utilisateurs` ;
|
280 |
DROP VIEW IF EXISTS `cel_utilisateurs` ;
|
| 279 |
DROP TABLE IF EXISTS `cel_utilisateurs`;
|
281 |
DROP TABLE IF EXISTS `cel_utilisateurs`;
|
| 280 |
CREATE OR REPLACE VIEW `cel_utilisateurs` AS
|
282 |
CREATE OR REPLACE VIEW `cel_utilisateurs` AS
|
| 281 |
SELECT at.U_ID AS id_utilisateur, at.U_SURNAME AS prenom, at.U_NAME AS nom, at.U_MAIL AS courriel, at.U_PASSWD AS mot_de_passe,
|
283 |
SELECT at.U_ID AS id_utilisateur, at.U_SURNAME AS prenom, at.U_NAME AS nom, at.U_MAIL AS courriel, at.U_PASSWD AS mot_de_passe,
|
| 282 |
ui.licence_acceptee, ui.admin, ui.preferences, ui.date_premiere_utilisation
|
284 |
ui.licence_acceptee, ui.admin, ui.preferences, ui.date_premiere_utilisation
|
| 283 |
FROM tela_prod_v4.annuaire_tela AS at
|
285 |
FROM tela_prod_v4.annuaire_tela AS at
|
| 284 |
LEFT JOIN cel_utilisateurs_infos AS ui ON (ui.id_utilisateur = at.U_ID);
|
286 |
LEFT JOIN cel_utilisateurs_infos AS ui ON (ui.id_utilisateur = at.U_ID);
|
| Line 285... |
Line 287... |
| 285 |
|
287 |
|
| 286 |
-- -----------------------------------------------------
|
288 |
-- -----------------------------------------------------
|
| 287 |
-- View `cel_tapir`
|
289 |
-- View `cel_tapir`
|
| 288 |
-- -----------------------------------------------------
|
290 |
-- -----------------------------------------------------
|
| 289 |
DROP VIEW IF EXISTS `cel_tapir` ;
|
291 |
DROP VIEW IF EXISTS `cel_tapir` ;
|
| 290 |
DROP TABLE IF EXISTS `cel_tapir`;
|
292 |
DROP TABLE IF EXISTS `cel_tapir`;
|
| 291 |
CREATE OR REPLACE VIEW `cel_tapir` AS
|
293 |
CREATE OR REPLACE VIEW `cel_tapir` AS
|
| 292 |
select concat(_utf8'urn:lsid:tela-botanica.org:cel:',`o`.`id_observation`) AS `guid`,
|
294 |
select concat(_utf8'urn:lsid:tela-botanica.org:cel:',`o`.`id_observation`) AS `guid`,
|
| 293 |
`o`.`id_observation` AS `observation_id`,
|
295 |
`o`.`id_observation` AS `observation_id`,
|
| 294 |
date_format(`o`.`date_observation`,'%Y-%m-%d') AS `observation_date`,
|
296 |
date_format(`o`.`date_observation`,'%Y-%m-%d') AS `observation_date`,
|
| 295 |
`o`.`nom_sel` AS `nom_scientifique_complet`,
|
297 |
`o`.`nom_sel` AS `nom_scientifique_complet`,
|
| 296 |
`o`.`nom_sel_nn` AS `nom_num_nomenclatural`,
|
298 |
`o`.`nom_sel_nn` AS `nom_num_nomenclatural`,
|
| 297 |
`o`.`nt` AS `nom_num_taxonomique`,
|
299 |
`o`.`nt` AS `nom_num_taxonomique`,
|
| 298 |
`o`.`famille` AS `nom_famille`,
|
300 |
`o`.`famille` AS `nom_famille`,
|
| 299 |
concat(_utf8'',`zg`.`nom`,' [INSEE:',`zg`.`code`,']') AS `lieu_commune_nom_complet`,
|
301 |
concat(_utf8'',`zg`.`nom`,' [INSEE:',`zg`.`code`,']') AS `lieu_commune_nom_complet`,
|
| 300 |
`zg`.`nom` AS `lieu_commune_nom`,
|
302 |
`zg`.`nom` AS `lieu_commune_nom`,
|
| 301 |
`zg`.`code` AS `lieu_commune_code_insee`,
|
303 |
`zg`.`code` AS `lieu_commune_code_insee`,
|
| 302 |
if((`zg`.`code` <> ''), 'Lion1906 version 26-05-2008 - http://www.lion1906.com/', NULL) AS `lieu_commune_source`,
|
304 |
if((`zg`.`code` <> ''), 'Lion1906 version 26-05-2008 - http://www.lion1906.com/', NULL) AS `lieu_commune_source`,
|
| 303 |
format(if((`o`.`latitude` <> ''), `o`.`latitude`, `zg`.`wgs84_latitude`), 5) AS `lieu_latitude`,
|
305 |
format(if((`o`.`latitude` <> ''), `o`.`latitude`, `zg`.`wgs84_latitude`), 5) AS `lieu_latitude`,
|
| 304 |
format(if((`o`.`longitude` <> ''), `o`.`longitude`, `zg`.`wgs84_longitude`), 5) AS `lieu_longitude`,
|
306 |
format(if((`o`.`longitude` <> ''), `o`.`longitude`, `zg`.`wgs84_longitude`), 5) AS `lieu_longitude`,
|
| 305 |
`o`.`geodatum` AS `lieu_geodatum`,
|
307 |
`o`.`geodatum` AS `lieu_geodatum`,
|
| 306 |
if((`o`.`geodatum` <> ''), 'Coordonnées issues de l''utilisation de Google Map', NULL) AS `lieu_georeference_source`,
|
308 |
if((`o`.`geodatum` <> ''), 'Coordonnées issues de l''utilisation de Google Map', NULL) AS `lieu_georeference_source`,
|
| 307 |
`o`.`lieudit` AS `lieu_localite`,
|
309 |
`o`.`lieudit` AS `lieu_localite`,
|
| 308 |
`o`.`prenom_utilisateur` AS `observateur_prenom`,
|
310 |
`o`.`prenom_utilisateur` AS `observateur_prenom`,
|
| 309 |
`o`.`nom_utilisateur` AS `observateur_nom`,
|
311 |
`o`.`nom_utilisateur` AS `observateur_nom`,
|
| 310 |
`o`.`courriel_utilisateur` AS `observateur_courriel`,
|
312 |
`o`.`courriel_utilisateur` AS `observateur_courriel`,
|
| 311 |
concat(`o`.`prenom_utilisateur`,_utf8' ',`o`.`nom_utilisateur`) AS `observateur_nom_complet`,
|
313 |
concat(`o`.`prenom_utilisateur`,_utf8' ',`o`.`nom_utilisateur`) AS `observateur_nom_complet`,
|
| 312 |
concat_ws(' ',`o`.`prenom_utilisateur`,`o`.`nom_utilisateur`,concat('<',`o`.`courriel_utilisateur`,'>')) AS `observateur_intitule`,
|
314 |
concat_ws(' ',`o`.`prenom_utilisateur`,`o`.`nom_utilisateur`,concat('<',`o`.`courriel_utilisateur`,'>')) AS `observateur_intitule`,
|
| 313 |
`o`.`commentaire` AS `observation_commentaire`,
|
315 |
`o`.`commentaire` AS `observation_commentaire`,
|
| 314 |
concat(_utf8'nom_num_nomenclatural=',`o`.`nom_sel_nn`,'; ',
|
316 |
concat(_utf8'nom_num_nomenclatural=',`o`.`nom_sel_nn`,'; ',
|
| 315 |
'nom_ret=',encodeToDcsv(`o`.`nom_ret`),'; ',
|
317 |
'nom_ret=',encodeToDcsv(`o`.`nom_ret`),'; ',
|
| 316 |
'nom_num_ret=',`o`.`nom_ret_nn`,'; ',
|
318 |
'nom_num_ret=',`o`.`nom_ret_nn`,'; ',
|
| 317 |
'nom_num_taxonomique=',`o`.`nt`,'; ',
|
319 |
'nom_num_taxonomique=',`o`.`nt`,'; ',
|
| 318 |
'nom_referentiel=',encodeToDcsv(`o`.`nom_referentiel`),'; ',
|
320 |
'nom_referentiel=',encodeToDcsv(`o`.`nom_referentiel`),'; ',
|
| 319 |
'saisie_date_transmission=',`o`.`date_transmission`,'; ',
|
321 |
'saisie_date_transmission=',`o`.`date_transmission`,'; ',
|
| 320 |
'saisie_date_creation=',`o`.`date_creation`,'; ',
|
322 |
'saisie_date_creation=',`o`.`date_creation`,'; ',
|
| 321 |
'ordre=',`o`.`ordre`,'; ',
|
323 |
'ordre=',`o`.`ordre`,'; ',
|
| 322 |
'station=',encodeToDcsv(`o`.`station`),'; ',
|
324 |
'station=',encodeToDcsv(`o`.`station`),'; ',
|
| 323 |
'milieu=',encodeToDcsv(`o`.`milieu`),'; ',
|
325 |
'milieu=',encodeToDcsv(`o`.`milieu`),'; ',
|
| 324 |
'mots_cles=',encodeToDcsv(`o`.`mots_cles_texte`),'; ',
|
326 |
'mots_cles=',encodeToDcsv(`o`.`mots_cles_texte`),'; ',
|
| 325 |
'zg_utm_secteur=',encodeToDcsv(`zg`.`utm_secteur`),'; ',
|
327 |
'zg_utm_secteur=',encodeToDcsv(`zg`.`utm_secteur`),'; ',
|
| 326 |
'zg_date_modification=',`zg`.`date_modification`) AS `observation_information_complement`,
|
328 |
'zg_date_modification=',`zg`.`date_modification`) AS `observation_information_complement`,
|
| 327 |
`o`.`date_modification` AS `saisie_date_modification`,
|
329 |
`o`.`date_modification` AS `saisie_date_modification`,
|
| 328 |
`o`.`date_creation` AS `saisie_date_creation`
|
330 |
`o`.`date_creation` AS `saisie_date_creation`
|
| 329 |
from (`cel_obs` `o`
|
331 |
from (`cel_obs` `o`
|
| 330 |
left join `cel_zones_geo` `zg` on((`o`.`ce_zone_geo` = `zg`.`id_zone_geo`)))
|
332 |
left join `cel_zones_geo` `zg` on((`o`.`ce_zone_geo` = `zg`.`id_zone_geo`)))
|
| 331 |
where `o`.`transmission` = 1
|
333 |
where `o`.`transmission` = 1
|
| Line 332... |
Line 334... |
| 332 |
AND (`o`.`mots_cles_texte` NOT LIKE '%sensible%' OR `o`.`mots_cles_texte` IS NULL);
|
334 |
AND (`o`.`mots_cles_texte` NOT LIKE '%sensible%' OR `o`.`mots_cles_texte` IS NULL);
|
| 333 |
|
335 |
|