Subversion Repositories eFlore/Applications.coel

#------------------------------------------------------------------------------------------------------------------------

#EFLORE_DROIT_POSSEDER vers coel_personne_a_relation

INSERT INTO tb_coel.coel_personne_a_relation

	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)

	SELECT 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))), @idm := @idm+1

	FROM tb_herbiers.EFLORE_DROIT_POSSEDER 

	ORDER BY EDP_ID_UTILISATEUR 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 @idm_tmp := @idm_tmp+1, 112, 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;

#------------------------------------------------------------------------------------------------------------------------

# HERBIER_COORDONNE vers coel_personne_a_relation

INSERT INTO tb_coel.coel_personne_a_relation

	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)

	SELECT HC_ID_COORDINATEUR, HC_ID_REDACTEUR, 2, 2025, @idm := @idm+1

	FROM tb_herbiers.HERBIER_COORDONNE 

	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR 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 @idm_tmp := @idm_tmp+1, 112, CONCAT(HC_ID_COORDINATEUR, '-', HC_ID_REDACTEUR, '-2-2025'), NULL, 

		NOW(), CONCAT('Importation des relations de coordination de l''utilisateur #',HC_ID_COORDINATEUR,' de la table HERBIER_COORDONNE.'), '1', '1', '162.38.234.1'

	FROM tb_herbiers.EFLORE_DROIT_POSSEDER 

	ORDER BY EDP_ID_UTILISATEUR ASC;

#------------------------------------------------------------------------------------------------------------------------	

#annuaire_tela vers 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, 

	 cp_description, 

	 cp_ce_truk_role, 

	 cp_mot_de_passe, cp_login, cp_parametre,  

	 cp_ce_meta)

	SELECT U_ID, 2, CONCAT(U_SURNAME,'',U_NAME) AS NOM_COMPLET, U_SURNAME, U_NAME,

		   U_ADDR1, U_ADDR2, U_STATE, U_ZIP_CODE, U_CITY, CP.CP_Intitule_pays,

		   U_MAIL, U_WEB, 

		   CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE), 

		   IF(EDP_ID_DROIT = '1', 2022, IF(EDP_ID_DROIT = '2', 2023, IF(EDP_ID_DROIT = '3', 2024, 0))),

		   U_PASSWD, U_MAIL, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'), 

		   @idm := @idm+1

	FROM tela_prod_v4.annuaire_tela AS AT, tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP, tela_prod_v4.carto_PAYS AS CP  

	WHERE AT.U_ID = EDP.EDP_ID_UTILISATEUR 

		AND CP.CP_ID_Pays = AT.U_COUNTRY 

	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 @idm_tmp := @idm_tmp+1, '111', U_ID, NULL, U_DATE,

		CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela.'), '1', '1', '162.38.234.1'

	FROM tela_prod_v4.annuaire_tela AS AT, tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP   

	WHERE AT.U_ID = EDP.EDP_ID_UTILISATEUR 

	ORDER BY U_ID ASC;

#------------------------------------------------------------------------------------------------------------------------

# HERBIER_ADMINISTRER vers coel_structure_a_personne

INSERT INTO tb_coel.coel_structure_a_personne

	(csap_id_structure, csap_id_personne, csap_id_role, csap_ce_meta)

	SELECT HA_ID_ORG, HA_ID_ANNUAIRE, '2026', @idm := @idm+1

	FROM tb_herbiers.HERBIER_ADMINISTRER 

	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG 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 @idm_tmp := @idm_tmp+1, '118', CONCAT(HA_ID_ORG, '-', HA_ID_ANNUAIRE, '-2026'), NULL, 

		NOW(), CONCAT('Importation des de l''administrateur #',HA_ID_ANNUAIRE,' de la structure #', HA_ID_ORG,'de la table HERBIER_ADMINISTRER.'), '1', '1', '162.38.234.1'

	FROM tb_herbiers.HERBIER_ADMINISTRER 

	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;

#------------------------------------------------------------------------------------------------------------------------

#HERBIER_COLLECTION vers coel_collection

#INSERT INTO tb_coel.coel_collection

#	(cc_id_collection, cc_ce_projet, cc_ce_mere, cc_truk_code, cc_nom, cc_description, 

#	 cc_truk_url, cc_ce_type, 

#	 cc_truk_conservation_statut, cc_truk_preservation, 

#	 cc_ce_developpement, cc_truk_periode_constitution, cc_truk_couverture_lieu, cc_truk_coordonnee, cc_ce_specimen_type, 

#	 cc_specimen_type_nbre, cc_mark_speciment_type_nbre_precision, cc_ce_specimen_type_classement, cc_expedition_nom,

#	 cc_truk_digital_medium, cc_truk_digital_format, 

#	 

#	 cc_ce_meta)

#		SELECT Id, 2, PARENT_ID, COLLECTION_CODE, NOM_COLLECTION, DESCRIPTION, 

#			   URL, INCLUDED_TYPE, @idm

#		FROM tb_herbiers.HERBIERS_COLLECTION

#		ORDER BY 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) 

#		VALUES (@idm := @idm+1, '101', '2', NULL , NOW( ) , 'Ajout d''une collection', '1', '1', '162.38.234.1');