Subversion Repositories eFlore/Applications.coel

Compare Revisions

Ignore whitespace Rev 87 → Rev 92

/trunk/doc/bdd/migration_bdd_herbiers_vers_coel.sql
3,7 → 3,7
#------------------------------------------------------------------------------------------------------------------------
# TODO : remplacer les valeurs vides par NULL -> utiliser le script remplacer_vide_par_null.php
 
SET @idm = 761;# Identifiant pour les métadonnées
SET @idm = 795;# Identifiant pour les métadonnées
SET @idm_max = 99999;# Identifiant pour les métadonnées maximum si déjà des données dans la base
#---------------------------------------------------------------------------------------------------------------------#
# Nettoyage des tables
83,7 → 83,8
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)
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'
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'
FROM tb_herbiers.EFLORE_DROIT_POSSEDER
ORDER BY EDP_ID_UTILISATEUR ASC;
 
128,7 → 129,7
(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)
SELECT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,
CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela.'), '1', '1', '162.38.234.1'
CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela liée à EFLORE_DROIT_POSSEDER.'), '1', '1', '162.38.234.1'
FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP
LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)
ORDER BY U_ID ASC;
136,7 → 137,7
#------------------------------------------------------------------------------------------------------------------------
# HERBIERS_ADMINISTRER et annuaire_tela vers coel_personne
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
INSERT IGNORE INTO tb_coel.coel_personne
INSERT INTO tb_coel.coel_personne
(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,
cp_truk_courriel, cp_truk_url,
152,16 → 153,16
FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA
LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)
LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays)
WHERE U_ID NOT IN (SELECT cp_id_personne FROM tb_coel.coel_personne WHERE cp_id_personne < 100000)
WHERE U_ID NOT IN (SELECT DISTINCT EDP_ID_UTILISATEUR FROM tb_herbiers.EFLORE_DROIT_POSSEDER)
ORDER BY U_ID ASC;
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)
SELECT DISTINCT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,
CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela.'), '1', '1', '162.38.234.1'
FROM tb_v4.annuaire_tela AS AT
LEFT JOIN tb_herbiers.HERBIERS_ADMINISTRER AS HA ON (AT.U_ID = HA.HA_ID_ANNUAIRE )
WHERE U_ID NOT IN (SELECT cp_id_personne FROM tb_coel.coel_personne WHERE cp_id_personne < 100000)
CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela liée à HERBIERS_ADMINISTRER.'), '1', '1', '162.38.234.1'
FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA
LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)
WHERE U_ID NOT IN (SELECT DISTINCT EDP_ID_UTILISATEUR FROM tb_herbiers.EFLORE_DROIT_POSSEDER)
ORDER BY U_ID ASC;
 
#------------------------------------------------------------------------------------------------------------------------
193,7 → 194,7
(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)
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()),
CONCAT('Importation de l''utilisateur #', ID_STAFF,' de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
CONCAT('Importation de l''utilisateur #', 100000+ID_STAFF,' de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
FROM tb_herbiers.HERBIERS_STAFF
ORDER BY ID_STAFF ASC;
 
223,8 → 224,8
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)
SELECT @idm_tmp := @idm_tmp+1, '120', CONCAT(ID_ORG, '-', ID_STAFF, '-2027'), NULL,
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'
SELECT @idm_tmp := @idm_tmp+1, '120', CONCAT(ID_ORG, '-', 100000+ID_STAFF, '-2027'), NULL,
NOW(), CONCAT('Importation de la personne #',100000+ID_STAFF,' de la structure #', ID_ORG,' de la table HERBIERS_ont_un_staff.'), '1', '1', '162.38.234.1'
FROM tb_herbiers.HERBIERS_ont_un_staff
WHERE ID_ORG != 0
ORDER BY ID_ORG, ID_STAFF ASC;
243,12 → 244,13
cc_specimen_type_nbre,
cc_truk_preservation,
cc_ce_meta)
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,
SELECT hc.ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, NOM_COLLECTION, CONVERT(DESCRIPTION USING utf8), STATUT, IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,
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,
NUM_SPECIMENS,
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,
@idm := @idm+1
FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)
WHERE hc.ID != 4
GROUP BY hc.ID
ORDER BY hc.ID ASC;
INSERT INTO tb_coel.coel_collection_botanique
265,7 → 267,8
CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),
CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),
IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', MODE_CLASSEMENT, NULL), NULL)
FROM tb_herbiers.HERBIERS_COLLECTION
FROM tb_herbiers.HERBIERS_COLLECTION
WHERE ID != 4
ORDER BY ID ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne
(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
273,6 → 276,7
SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,
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')
FROM tb_herbiers.HERBIERS_COLLECTION
WHERE ID != 4
ORDER BY ID ASC;
#------------------------------------------------------------------------------------------------------------------------