| Line 284... |
Line 284... |
| 284 |
#------------------------------------------------------------------------------------------------------------------------
|
284 |
#------------------------------------------------------------------------------------------------------------------------
|
| 285 |
#HERBIER_COLLECTION vers coel_collection
|
285 |
#HERBIER_COLLECTION vers coel_collection
|
| 286 |
INSERT INTO tb_coel.coel_collection
|
286 |
INSERT INTO tb_coel.coel_collection
|
| 287 |
(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description,
|
287 |
(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description,
|
| 288 |
cc_ce_type_depot,
|
288 |
cc_ce_type_depot,
|
| 289 |
cc_ce_specimen_type,
|
- |
|
| 290 |
cc_description_specialiste, cc_truk_url,
|
289 |
cc_description_specialiste, cc_truk_url,
|
| 291 |
cc_ce_type,
|
290 |
cc_ce_type,
|
| 292 |
cc_specimen_type_nbre,
|
291 |
cc_ce_specimen_type,
|
| 293 |
cc_truk_preservation,
|
292 |
cc_truk_preservation,
|
| 294 |
cc_ce_meta)
|
293 |
cc_ce_meta)
|
| 295 |
SELECT hc.ID, '2', PARENT_ID, CONCAT("urn:lsid:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, CONVERT(NOM_COLLECTION USING utf8), CONVERT(DESCRIPTION USING utf8),
|
294 |
SELECT hc.ID, '2', PARENT_ID, CONCAT("urn:lsid:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, CONVERT(NOM_COLLECTION USING utf8), CONVERT(DESCRIPTION USING utf8),
|
| 296 |
IF(STATUT = 1, '2159', IF(STATUT = 2, '2160', IF(STATUT = 3, '2161', IF(STATUT = 4, '2162', NULL)))),
|
295 |
IF(STATUT = 1, '2159', IF(STATUT = 2, '2160', IF(STATUT = 3, '2161', IF(STATUT = 4, '2162', NULL)))),
|
| 297 |
IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,
|
- |
|
| 298 |
CONCAT(IF(DOC_STATE = '', '', IF(DOC_STATE IS NULL, '', CONCAT('Etat documentation : \n', CONVERT(DOC_STATE USING utf8)))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n', CONVERT(COLLECTEURS USING utf8))))) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL,
|
296 |
CONCAT(IF(DOC_STATE = '', '', IF(DOC_STATE IS NULL, '', CONCAT('Etat documentation : \n', CONVERT(DOC_STATE USING utf8)))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n', CONVERT(COLLECTEURS USING utf8))))) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL,
|
| 299 |
'2157',
|
297 |
'2157',
|
| 300 |
NUM_SPECIMENS,
|
298 |
IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,
|
| 301 |
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,
|
299 |
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,
|
| 302 |
@idm := @idm+1
|
300 |
@idm := @idm+1
|
| 303 |
FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)
|
301 |
FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)
|
| 304 |
WHERE hc.ID != 4
|
302 |
WHERE hc.ID != 4
|
| 305 |
GROUP BY hc.ID
|
303 |
GROUP BY hc.ID
|
| Line 320... |
Line 318... |
| 320 |
ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
|
318 |
ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
|
| 321 |
ccb_truk_degradation_specimen,
|
319 |
ccb_truk_degradation_specimen,
|
| 322 |
ccb_truk_degradation_presentation,
|
320 |
ccb_truk_degradation_presentation,
|
| 323 |
ccb_annotation_classement,
|
321 |
ccb_annotation_classement,
|
| 324 |
ccb_ce_meta)
|
322 |
ccb_ce_meta)
|
| 325 |
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')))),
|
323 |
SELECT ID, CONCAT('2270##0|NULL|NULL|',NUM_SPECIMENS,'|', IF(PREC_SPECIMENS = '1', '30753', IF(PREC_SPECIMENS = '2', '30754', IF(PREC_SPECIMENS = '3', '30754', 'NULL'))), '|', NUM_SPECIES, '|', IF(PREC_SPECIES = '1', '30753', IF(PREC_SPECIES = '2', '30754', IF(PREC_SPECIES = '3', '30754', 'NULL')))),
|
| 326 |
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))))),
|
324 |
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))))),
|
| 327 |
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)))))),
|
325 |
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)))))),
|
| 328 |
PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),
|
326 |
PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),
|
| 329 |
CONCAT('TOTAL##',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),
|
327 |
CONCAT('Diverses##',IF(ETAT_SPECIMENS = 1, '30748', IF(ETAT_SPECIMENS = 2, '30749', IF(ETAT_SPECIMENS = 3, '30751', IF(ETAT_SPECIMENS = 4, '30752', NULL))))),
|
| 330 |
CONCAT('TOTAL##',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),
|
328 |
CONCAT('Diverses##',IF(ETAT_PRESENTATION = 1, '30748', IF(ETAT_PRESENTATION = 2, '30749', IF(ETAT_PRESENTATION = 3, '30751', IF(ETAT_PRESENTATION = 4, '30752', NULL))))),
|
| 331 |
IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL),
|
329 |
IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL),
|
| 332 |
@idm := @idm+1
|
330 |
@idm := @idm+1
|
| 333 |
FROM tb_herbiers.HERBIERS_COLLECTION
|
331 |
FROM tb_herbiers.HERBIERS_COLLECTION
|
| 334 |
WHERE ID != 4
|
332 |
WHERE ID != 4
|
| 335 |
ORDER BY ID ASC;
|
333 |
ORDER BY ID ASC;
|