Subversion Repositories eFlore/Applications.coel

Rev

Rev 98 | Rev 158 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 98 Rev 139
Line 29... Line 29...
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');
Line 30... Line 30...
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_autorisation, cpr_ce_meta)
34 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_mark_autorisation, cpr_ce_meta)
35 
	SELECT 2, CONVERT(EPR_INTITULE_PROJET USING utf8), CONVERT(EPR_ABREVIATION_PROJET USING utf8), CONVERT(EPR_DESCRIPTION_PROJET USING utf8), EPR_LIEN_WEB, 1, @idm
35 
	SELECT 2, CONVERT(EPR_INTITULE_PROJET USING utf8), CONVERT(EPR_ABREVIATION_PROJET USING utf8), CONVERT(EPR_DESCRIPTION_PROJET USING utf8), EPR_LIEN_WEB, 1, @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_autorisation, cpr_ce_meta)
40 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_mark_autorisation, cpr_ce_meta)
41 
	VALUES (3, 'Utilisateurs', 'COEL-U', 'Les utilisateurs de l''application COEL.', NULL, 0, @idm);
 41 
	VALUES (3, 'Utilisateurs', 'COEL-U', 'Les utilisateurs de l''application COEL.', NULL, 0, @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)
Line 43... Line 43...
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');
Line 250... Line 250...
250
250
251 
#------------------------------------------------------------------------------------------------------------------------
 251 
#------------------------------------------------------------------------------------------------------------------------
252 
#HERBIER_COLLECTION vers coel_collection
 252 
#HERBIER_COLLECTION vers coel_collection
253 
INSERT INTO tb_coel.coel_collection
 253 
INSERT INTO tb_coel.coel_collection
254 
	(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description,
254 
	(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description,
255 
	cc_truk_type_depot,
255 
	cc_ce_type_depot,
256 
	cc_ce_specimen_type,
256 
	cc_ce_specimen_type,
257 
	cc_description_specialiste, cc_truk_url,
257 
	cc_description_specialiste, cc_truk_url,
258 
	cc_specimen_type_nbre,
258 
	cc_specimen_type_nbre,
259 
	cc_truk_preservation,
259 
	cc_truk_preservation,
Line 267... Line 267...
267 
		@idm := @idm+1
 267 
		@idm := @idm+1
268 
	FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)
268 
	FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)
269 
	WHERE hc.ID != 4
269 
	WHERE hc.ID != 4
270 
	GROUP BY hc.ID
270 
	GROUP BY hc.ID
271 
	ORDER BY hc.ID ASC;
 271 
	ORDER BY hc.ID ASC;
- 
 
 272 
INSERT INTO tb_coel.coel_meta_historique_ligne
- 
 
 273 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
- 
 
 274 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
- 
 
 275 
	SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,
- 
 
 276 
		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')
- 
 
 277 
	FROM tb_herbiers.HERBIERS_COLLECTION
- 
 
 278 
	WHERE ID != 4
- 
 
 279 
	ORDER BY ID ASC;
- 
 
 280 
#------------------------------------------------------------------------------------------------------------------------
- 
 
 281 
#HERBIER_COLLECTION vers coel_collection_botanique
272 
INSERT INTO tb_coel.coel_collection_botanique
282 
INSERT INTO tb_coel.coel_collection_botanique
273 
	(ccb_id_collection, ccb_truk_unite_base,
283 
	(ccb_id_collection, ccb_truk_unite_base,
274 
	ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type,
284 
	ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type,
275 
	ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
 285 
	ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
276 
	ccb_truk_degradation_specimen,
286 
	ccb_truk_degradation_specimen,
277 
	ccb_truk_degradation_presentation,
 287 
	ccb_truk_degradation_presentation,
278 
	ccb_annotation_classement)
 288 
	ccb_annotation_classement,
- 
 
 289 
	ccb_ce_meta)
279 
	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')))),
290 
	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')))),
280 
		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))))),
291 
		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))))),
281 
		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)))))),
292 
		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)))))),
282 
		PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),
 293 
		PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),
283 
		CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),
 294 
		CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),
284 
		CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),
 295 
		CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),
285 
		IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL)
 296 
		IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL),
- 
 
 297 
		@idm := @idm+1
286 
	FROM tb_herbiers.HERBIERS_COLLECTION
 298 
	FROM tb_herbiers.HERBIERS_COLLECTION
287 
	WHERE ID != 4
299 
	WHERE ID != 4
288 
	ORDER BY ID ASC;
 300 
	ORDER BY ID ASC;
289 
INSERT INTO tb_coel.coel_meta_historique_ligne
301 
INSERT INTO tb_coel.coel_meta_historique_ligne
290 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
302 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
291 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
303 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
292 
	SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,
304 
	SELECT @idm_tmp := @idm_tmp+1, '106', ID, NULL,
293 
		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')
305 
		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')
294 
	FROM tb_herbiers.HERBIERS_COLLECTION
 306 
	FROM tb_herbiers.HERBIERS_COLLECTION
295 
	WHERE ID != 4
307 
	WHERE ID != 4
296 
	ORDER BY ID ASC;
308 
	ORDER BY ID ASC;