Subversion Repositories eFlore/Applications.coel

Rev

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

Rev 73 Rev 75
Line 5... Line 5...
5 
 
 5 
 
6 
SET @idm = 761;# Identifiant pour les métadonnées
 6 
SET @idm = 761;# Identifiant pour les métadonnées
7 
SET @idm_max = 99999;# Identifiant pour les métadonnées maximum si déjà des données dans la base
 7 
SET @idm_max = 99999;# Identifiant pour les métadonnées maximum si déjà des données dans la base
8 
#---------------------------------------------------------------------------------------------------------------------#
 8 
#---------------------------------------------------------------------------------------------------------------------#
9 
# Nettoyage des tables
 9 
# Nettoyage des tables
10 
# coel_projet : la table d'historique des lignes
 10 
# coel_projet : la table d''historique des lignes
11 
DELETE FROM tb_coel.coel_projet WHERE cpr_id_projet = 2;
 11 
DELETE FROM tb_coel.coel_projet WHERE cpr_id_projet IN (2,3);
12 
# coel_meta_historique_ligne : la table d'historique des lignes
 12 
# coel_meta_historique_ligne : la table d''historique des lignes
13 
DELETE FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_id_historique_ligne >= @idm AND cmhl_id_historique_ligne < @idm_max;
 13 
DELETE FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_id_historique_ligne >= @idm AND cmhl_id_historique_ligne < @idm_max;
14 
# coel_structure : la table contenant les structures
 14 
# coel_structure : la table contenant les structures
- 
 
 15 
DELETE FROM tb_coel.coel_structure WHERE cs_id_structure IN (SELECT ID_ORG FROM tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG ASC);
- 
 
 16 
# coel_personne_a_relation
- 
 
 17 
DELETE FROM tb_coel.coel_personne_a_relation WHERE cpar_id_projet = '2';
- 
 
 18 
DELETE FROM tb_coel.coel_personne WHERE cp_ce_projet = '3';
- 
 
 19 
DELETE FROM tb_coel.coel_structure_a_personne WHERE csap_id_role = '2026';
- 
 
 20 
DELETE FROM tb_coel.coel_personne WHERE cp_ce_projet = '2';
Line 15... Line 21...
15 
DELETE FROM tb_coel.coel_structure WHERE cs_id_structure IN (SELECT ID_ORG FROM tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG ASC);
 21 
DELETE FROM tb_coel.coel_structure_a_personne WHERE csap_id_role = '2027';
16 
 
 22 
 
17 
#---------------------------------------------------------------------------------------------------------------------#
 23 
#---------------------------------------------------------------------------------------------------------------------#
18 
# EFLORE_PROJET vers coel_projet
 24 
# EFLORE_PROJET vers coel_projet
Line 22... Line 28...
22 
	FROM tb_herbiers.EFLORE_PROJET;
 28 
	FROM tb_herbiers.EFLORE_PROJET;
23 
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)
29 
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)
24 
	VALUES	(@idm := @idm+1, '113', '2', NULL , NOW( ) , 'Ajout du projet IHF', '1', '1', '162.38.234.1');
 30 
	VALUES	(@idm := @idm+1, '113', '2', NULL , NOW( ) , 'Ajout du projet IHF', '1', '1', '162.38.234.1');
25 
# Insertion du projet Utilisateur de l''application COEL - Version 1
31 
# Insertion du projet Utilisateur de l''application COEL - Version 1
26 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_ce_meta)
32 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_ce_meta)
27 
	VALUES 3, 'Utilisateurs', 'U', 'Les utilisateurs de l''application COEL.', NULL, @idm;
 33 
	VALUES (3, 'Utilisateurs', 'U', 'Les utilisateurs de l''application COEL.', NULL, @idm);
28 
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)
34 
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)
29 
	VALUES	(@idm := @idm+1, '113', '2', NULL , NOW( ) , 'Ajout du projet U', '1', '1', '162.38.234.1');
 35 
	VALUES	(@idm := @idm+1, '113', '2', NULL , NOW( ) , 'Ajout du projet U', '1', '1', '162.38.234.1');
Line 30... Line 36...
30 
 
 36 
 
Line 80... Line 86...
80 
	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'
 86 
	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'
81 
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER
87 
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER
82 
	ORDER BY EDP_ID_UTILISATEUR ASC;
 88 
	ORDER BY EDP_ID_UTILISATEUR ASC;
Line 83... Line 89...
83 
 
 89 
 
84 
#------------------------------------------------------------------------------------------------------------------------
 90 
#------------------------------------------------------------------------------------------------------------------------
85 
# HERBIER_COORDONNE vers coel_personne_a_relation
 91 
# HERBIERS_COORDONNE vers coel_personne_a_relation
86 
INSERT INTO tb_coel.coel_personne_a_relation
 92 
INSERT INTO tb_coel.coel_personne_a_relation
87 
	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
 93 
	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
88 
	SELECT HC_ID_COORDINATEUR, HC_ID_REDACTEUR, 2, 2025, @idm := @idm+1
 94 
	SELECT HC_ID_COORDINATEUR, HC_ID_REDACTEUR, 2, 2025, @idm := @idm+1
89 
	FROM tb_herbiers.HERBIER_COORDONNE
95 
	FROM tb_herbiers.HERBIERS_COORDONNE
90 
	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
 96 
	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
91 
INSERT INTO tb_coel.coel_meta_historique_ligne
97 
INSERT INTO tb_coel.coel_meta_historique_ligne
92 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
98 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
93 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
 99 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
94 
	SELECT @idm_tmp := @idm_tmp+1, '112', CONCAT(HC_ID_COORDINATEUR, '-', HC_ID_REDACTEUR, '-2-2025'), NULL,
100 
	SELECT @idm_tmp := @idm_tmp+1, '112', CONCAT(HC_ID_COORDINATEUR, '-', HC_ID_REDACTEUR, '-2-2025'), NULL,
95 
		NOW(), CONCAT('Importation des relations de coordination de l''utilisateur #',HC_ID_COORDINATEUR,' de la table HERBIER_COORDONNE.'), '1', '1', '162.38.234.1'
 101 
		NOW(), CONCAT('Importation des relations de coordination de l''utilisateur #',HC_ID_COORDINATEUR,' de la table HERBIER_COORDONNE.'), '1', '1', '162.38.234.1'
96 
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER
102 
	FROM tb_herbiers.HERBIERS_COORDONNE
Line 97... Line 103...
97 
	ORDER BY EDP_ID_UTILISATEUR ASC;
 103 
	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
98 
 
 104 
 
99 
#------------------------------------------------------------------------------------------------------------------------
105 
#------------------------------------------------------------------------------------------------------------------------
100 
#annuaire_tela vers coel_personne
 106 
#annuaire_tela vers coel_personne
Line 126... Line 132...
126 
	FROM tela_prod_v4.annuaire_tela AS AT, tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP
132 
	FROM tela_prod_v4.annuaire_tela AS AT, tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP
127 
	WHERE AT.U_ID = EDP.EDP_ID_UTILISATEUR
133 
	WHERE AT.U_ID = EDP.EDP_ID_UTILISATEUR
128 
	ORDER BY U_ID ASC;
 134 
	ORDER BY U_ID ASC;
Line 129... Line 135...
129 
 
 135 
 
130 
#------------------------------------------------------------------------------------------------------------------------
 136 
#------------------------------------------------------------------------------------------------------------------------
131 
# HERBIER_ADMINISTRER vers coel_structure_a_personne
 137 
# HERBIERS_ADMINISTRER vers coel_structure_a_personne
132 
INSERT INTO tb_coel.coel_structure_a_personne
 138 
INSERT INTO tb_coel.coel_structure_a_personne
133 
	(csap_id_structure, csap_id_personne, csap_id_role, csap_ce_meta)
 139 
	(csap_id_structure, csap_id_personne, csap_id_role, csap_ce_meta)
134 
	SELECT HA_ID_ORG, HA_ID_ANNUAIRE, '2026', @idm := @idm+1
 140 
	SELECT HA_ID_ORG, HA_ID_ANNUAIRE, '2026', @idm := @idm+1
135 
	FROM tb_herbiers.HERBIER_ADMINISTRER
141 
	FROM tb_herbiers.HERBIERS_ADMINISTRER
136 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
 142 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
137 
INSERT INTO tb_coel.coel_meta_historique_ligne
143 
INSERT INTO tb_coel.coel_meta_historique_ligne
138 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
144 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
139 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
 145 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
140 
	SELECT @idm_tmp := @idm_tmp+1, '118', CONCAT(HA_ID_ORG, '-', HA_ID_ANNUAIRE, '-2026'), NULL,
146 
	SELECT @idm_tmp := @idm_tmp+1, '118', CONCAT(HA_ID_ORG, '-', HA_ID_ANNUAIRE, '-2026'), NULL,
141 
		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'
 147 
		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'
142 
	FROM tb_herbiers.HERBIER_ADMINISTRER
148 
	FROM tb_herbiers.HERBIERS_ADMINISTRER
Line 143... Line 149...
143 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
 149 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
144
150
145
151
146 
#------------------------------------------------------------------------------------------------------------------------
 152 
#------------------------------------------------------------------------------------------------------------------------
147 
#HERBIERS_STAFF vers coel_personne
 153 
#HERBIERS_STAFF vers coel_personne
148 
INSERT INTO tb_coel.coel_personne
 154 
INSERT INTO tb_coel.coel_personne
149 
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_nom, cp_prenom, cp_adresse_01, cp_adresse_02, cp_code_postal, cp_ville, cp_truk_courriel,
 155 
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_nom, cp_prenom, cp_adresse_01, cp_adresse_02, cp_code_postal, cp_ville, cp_truk_courriel,
150 
	cp_truk_telephone, cp_truk_fax, cp_ce_meta)
 156 
	cp_truk_telephone, cp_truk_fax, cp_ce_meta)
151 
	SELECT 100000+ID_STAFF, '2', CONCAT(PRENOM,'', NOM) AS NOM_COMPLET, NOM, PRENOM, ADRESSE1, ADRESSE2, CP, VILLE, LOWER(MAIL),
157 
	SELECT 100000+ID_STAFF, '2', CONCAT(PRENOM,'', NOM) AS NOM_COMPLET, NOM, PRENOM, ADRESSE1, ADRESSE2, CP, VILLE, LOWER(MAIL),
152 
		   IF(TEL != NULL, CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(TEL,'[33]',''))), NULL), CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(FAX,'[33]',''))), @idm := @idm+1
 158 
		   IF(TEL != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(TEL,'[33]',''))), NULL), IF(FAX != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(FAX,'[33]',''))), NULL), @idm := @idm+1
153 
	FROM tb_herbiers.HERBIERS_STAFF
 159 
	FROM tb_herbiers.HERBIERS_STAFF
154 
	ORDER BY ID_STAFF ASC;
 160 
	ORDER BY ID_STAFF ASC;
155 
INSERT INTO tb_coel.coel_metal_historique_ligne
 161 
INSERT INTO tb_coel.coel_meta_historique_ligne
156 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
162 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
157 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
 163 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
158 
	SELECT @idm_tmp := @idm_tmp+1, '111', , NULL, DATE_DERNIERE_MODIF,
 164 
	SELECT @idm_tmp := @idm_tmp+1, '111', ID_STAFF, NULL, DATE_DERNIERE_MODIF,
- 
 
 165 
		CONCAT('Importation de l''utilisateur #', ID_STAFF,'de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
- 
 
 166 
	FROM tb_herbiers.HERBIERS_STAFF
- 
 
 167 
	ORDER BY ID_STAFF ASC;
- 
 
 168 
 
- 
 
 169 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';
159 
		CONCAT('Importation de l''utilisateur #', ID_STAFF,'de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
 170 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ' ', '') WHERE cp_truk_telephone LIKE '% %';
- 
 
 171 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '+33(0)', '') WHERE cp_truk_telephone LIKE '%+33(0)%';
- 
 
 172 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;%', REPLACE(cp_truk_telephone, ';0', '; +33'),cp_truk_telephone);
- 
 
 173 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '+336%', CONCAT('GSM#', cp_truk_telephone), CONCAT('FIX#', cp_truk_telephone));
160 
	FROM tb_herbiers.HERBIERS_STAFF
 174 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%+330%', REPLACE(cp_truk_telephone, '+330', '+33'),cp_truk_telephone);
161 
	ORDER BY ID_STAFF ASC;
 175 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%; +336%', REPLACE(cp_truk_telephone, '; +33', '; GSM#+33'), REPLACE(cp_truk_telephone, '; +33', '; FIX#+33')) WHERE cp_truk_telephone LIKE '%;%';
162 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE (+336%), CONCAT('GSM#', cp_truk_telephone), CONCAT('FIX#', cp_truk_telephone)));
 176 
 
- 
 
 177 
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '.', '') WHERE cp_truk_fax LIKE '%.%';
- 
 
 178 
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '+33 (0)', '') WHERE cp_truk_fax LIKE '%+33 (0)%';
Line 163... Line 179...
163 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', ' ') WHERE cp_truk_telephone LIKE '%.%';
 179 
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, ' ', '') WHERE cp_truk_fax LIKE '% %';
164 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '+33 (0)', '') WHERE cp_truk_telephone LIKE '%+33 (0)%';
 180 
UPDATE tb_coel.coel_personne SET cp_truk_fax = IF(cp_truk_fax LIKE '%+330%', REPLACE(cp_truk_fax, '+330', '+33'),cp_truk_fax);
165 
# TODO : faire les fax
 181 
 
166 
 
 182 
 
167 
#------------------------------------------------------------------------------------------------------------------------
 183 
#------------------------------------------------------------------------------------------------------------------------
168 
# HERBIERS_ont_un_staff vers coel_structure_a_personne
 184 
# HERBIERS_ont_un_staff vers coel_structure_a_personne
169 
INSERT INTO tb_coel.coel_structure_a_personne
 185 
INSERT INTO tb_coel.coel_structure_a_personne
170 
	(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)
 186 
	(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)
171 
	SELECT ID_ORG, hous.ID_STAFF, '2027', IF(LOWER(CONTACT) = 'oui', 1, 0) AS CONTACT, IF(FONCTION != '', CONCAT('AUTRE#', FONCTION), NULL) AS FONCTION, @idm := @idm+1
 187 
	SELECT ID_ORG, 100000+hous.ID_STAFF, '2027', IF(LOWER(CONTACT) = 'oui', 1, 0) AS CONTACT, IF(FONCTION != '', CONCAT('AUTRE#', FONCTION), NULL) AS FONCTION, @idm := @idm+1
172 
	FROM tb_herbiers.HERBIERS_ont_un_staff AS hous LEFT JOIN tb_herbiers.HERBIERS_STAFF AS hs ON (hous.ID_STAFF = hs.ID_STAFF)
188 
	FROM tb_herbiers.HERBIERS_ont_un_staff AS hous LEFT JOIN tb_herbiers.HERBIERS_STAFF AS hs ON (hous.ID_STAFF = hs.ID_STAFF)
173 
	WHERE hous.ID_ORG != 0
189 
	WHERE hous.ID_ORG != 0
174 
	ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;
 190 
	ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;
175 
INSERT INTO tb_coel.coel_meta_historique_ligne
191 
INSERT INTO tb_coel.coel_meta_historique_ligne
176 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
192 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
177 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
 193 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
178 
	SELECT @idm_tmp := @idm_tmp+1, '118', CONCAT(ID_ORG, '-', ID_STAFF, '-2027'), NULL,
194 
	SELECT @idm_tmp := @idm_tmp+1, '118', CONCAT(ID_ORG, '-', ID_STAFF, '-2027'), NULL,
- 
 
 195 
		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'
179 
		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'
 196 
	FROM tb_herbiers.HERBIERS_ont_un_staff
- 
 
 197 
	WHERE ID_ORG != 0
- 
 
 198 
	ORDER BY ID_ORG, ID_STAFF ASC;
- 
 
 199
Line 180... Line 200...
180 
	FROM tb_herbiers.HERBIERS_ont_un_staff
 200 
UPDATE tb_coel.coel_structure_a_personne
181 
	WHERE hous.ID_ORG != 0
201 
SET csap_ce_truk_fonction = IF(csap_ce_truk_fonction IS NOT NULL, IF(csap_ce_truk_fonction LIKE '%irecteur%', '2028',
182 
	ORDER BY ID_ORG, ID_STAFF ASC;
202 
	IF(csap_ce_truk_fonction LIKE '%onservateur%', '2029', IF(csap_ce_truk_fonction LIKE '%echnicien%', '2030', csap_ce_truk_fonction))), NULL)
183 
# TODO : ajouter une requête d''update pour mettre à jour la fonction...
 203 
WHERE csap_id_role = '2027'	;
184
204
185
205
186 
#------------------------------------------------------------------------------------------------------------------------
 206 
#------------------------------------------------------------------------------------------------------------------------
- 
 
 207 
#HERBIER_COLLECTION vers coel_collection
187 
#HERBIER_COLLECTION vers coel_collection
 208 
INSERT INTO tb_coel.coel_collection
188 
#INSERT INTO tb_coel.coel_collection
 209 
	(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description, cc_truk_type_depot, cc_ce_specimen_type,
- 
 
 210 
	 cc_description_specialiste, cc_truk_url, cc_ce_type,
- 
 
 211 
	 cc_specimen_type_nbre,
- 
 
 212 
    cc_ce_meta)
- 
 
 213 
	SELECT ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",ID) AS GUID, COLLECTION_CODE, NOM_COLLECTION, DESCRIPTION, STATUT, IF(INCLUDED_TYPE = '0', '2236', IF (INCLUDED_TYPE = '1', '2239', IF (INCLUDED_TYPE = '2', '2238', NULL))) ,
189 
#	(cc_id_collection, cc_ce_projet, cc_ce_mere, cc_truk_code, cc_nom, cc_description,
214 
	   CONCAT('Etat documentation : \n',DOC_STATE,'\nCollecteurs : \n',COLLECTEURS), URL, INCLUDED_TYPE,
- 
 
 215 
	   NUM_SPECIMENS,
- 
 
 216 
	   @idm := @idm+1
- 
 
 217 
	FROM tb_herbiers.HERBIERS_COLLECTION
- 
 
 218 
	ORDER BY ID ASC;
- 
 
 219 
INSERT INTO tb_coel.coel_collection_botanique
190 
#	 cc_truk_url, cc_ce_type,
220 
	(ccb_id_collection, ccb_truk_unite_base,
- 
 
 221 
	ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type,
- 
 
 222 
	ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
- 
 
 223 
	ccb_truk_degradation_specimen,
- 
 
 224 
	ccb_truk_degradation_presentation,
- 
 
 225 
	ccb_annotation_classement)
- 
 
 226 
	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')))),
191 
#	 cc_specimen_type_nbre,
 227 
		DATE_DEBUT, IF(DATE_DEBUT_CARAC = '1', '2313', IF (DATE_DEBUT_CARAC = '2', '2314', IF (DATE_DEBUT_CARAC = '3', '2315', IF(DATE_DEBUT_CARAC = '4', '2316' IF (DATE_DEBUT_CARAC = '5', '2317', NULL))))),
192 
#    cc_ce_meta)
 228 
		DATE_FIN, IF( DATE_FIN_CARAC = '1', '2318', IF (DATE_FIN_CARAC = '2', '2319', IF(DATE_FIN_CARAC = '3', '2320' IF (DATE_FIN_CARAC = '4', '2322', IF(DATE_FIN_CARAC = '5', '2321', IF (DATE_FIN_CARAC = '6', '2323', NULL)))))),
193 
#	SELECT Id, 2, PARENT_ID, COLLECTION_CODE, NOM_COLLECTION, DESCRIPTION,
229 
		PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2325', IF(ETAT_CLASSEMENT = 2, '2326', IF(ETAT_CLASSEMENT = 3, '2327', IF(ETAT_CLASSEMENT = 4, '2328', NULL)))),
194 
#	   URL, INCLUDED_TYPE,
230 
		CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2325', IF(ETAT_SPECIMENS = 2, '2326', IF(ETAT_SPECIMENS = 3, '2327', IF(ETAT_SPECIMENS = 4, '2328', NULL)))),
195 
	   count (distinct NUM_SPECIMENS),
 231 
		CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2325', IF(ETAT_PRESENTATION = 2, '2326', IF(ETAT_PRESENTATION = 3, '2327', IF(ETAT_PRESENTATION = 4, '2328', NULL)))),
196 
#	   @idm := @idm+1
 232 
		IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', MODE_CLASSEMENT, NULL), NULL)
197 
#	FROM tb_herbiers.HERBIERS_COLLECTION
 233 
	FROM tb_herbiers.HERBIERS_COLLECTION
198 
#	ORDER BY Id ASC;
 234 
	ORDER BY ID ASC;
199 
#INSERT INTO tb_coel.coel_meta_historique_ligne
235 
INSERT INTO tb_coel.coel_meta_historique_ligne
200 
#	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
236 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
- 
 
 237 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
- 
 
 238 
	SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,
- 
 
 239 
		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');
- 
 
 240 
	FROM tb.herbiers.HERBIERS_COLLECTION