· 4 years ago · Dec 16, 2020, 10:36 AM
1-- pretraitement
2CREATE OR REPLACE FUNCTION DateNotice()
3RETURNS void AS $$
4DECLARE
5 x text [];
6 i record;
7 d_crea date;
8 d_ana date;
9 d_rev_n date;
10BEGIN
11 -- créer une table temp pour stocker les résultats
12 drop table if exists tmp_date_notice;
13 CREATE TEMPORARY TABLE tmp_date_notice (cote varchar(20),date_creation date, date_analyse date, date_revision date);
14
15 -- traite les ligne et les nettoies
16 FOR i in select c."date_création_notice", c.date_analyse, c."date_révision_notice",c.cote FROM csv_importes as c LOOP
17 IF i."date_création_notice" ~ '(\d{4})*(-)*(\d{2})*(-)*(\d{2})' THEN
18 x := regexp_match(i."date_création_notice", '(\d{4})*(-)*(\d{2})*(-)*(\d{2})');
19 d_crea := (x[1] || x[2] || x[3] || x[4] || x[5])::date;
20 END IF;
21 IF i.date_analyse ~ '(\d{4})*(-)*(\d{2})*(-)*(\d{2})' THEN
22 x := regexp_match(i.date_analyse, '(\d{4})*(-)*(\d{2})*(-)*(\d{2})');
23 d_ana := (x[1] || x[2] || x[3] || x[4] || x[5])::date;
24 END IF;
25 IF i."date_révision_notice" ~ '(\d{4})*(-)*(\d{2})*(-)*(\d{2})' THEN
26 x := regexp_match(i."date_révision_notice", '(\d{4})*(-)*(\d{2})*(-)*(\d{2})');
27 d_rev_n := (x[1] || x[2] || x[3] || x[4] || x[5])::date;
28 END IF;
29 insert into tmp_date_notice values (i.cote,d_crea, d_ana, d_rev_n);
30 END loop;
31END;
32$$ LANGUAGE plpgsql;
33
34SELECT datenotice();
35SELECT * FROM tmp_date_notice;
36
37-- documents
38CREATE OR REPLACE FUNCTION format_nature()
39RETURNS void AS $$
40DECLARE
41 i record;
42 x text[];
43 fm text;
44 nd text;
45BEGIN
46 -- créer une table temp pour stocker les résultats
47 DROP table if exists tmp_format_nature;
48 CREATE TEMPORARY TABLE tmp_format_nature (
49 cote varchar(20),
50 lang_orig Langue_,
51 format varchar(100),
52 notes text,
53 nature_documents varchar(30)
54 );
55
56 FOR i IN select c.cote, c.notes, c.format, c.nature_du_document from csv_importes as c LOOP
57 -- enlève les caractères invisibles
58 i.format := regexp_replace(i.format, 'Ã', 'x', 'g');
59 i.format := regexp_replace(i.format, '[^[:ascii:]]', ' ', 'g');
60 i.format := lower(i.format);
61 i.nature_du_document := regexp_replace(i.nature_du_document, '[^[:ascii:]]', ' ', 'g');
62 i.nature_du_document := lower(i.nature_du_document);
63
64 -- poids
65 IF i.format ~ '(\d{1,3},?\d{0,2}\D*ko)' then
66 x := regexp_match(i.format, '(\d{1,3},?\d{0,}\D*ko)');
67 x := regexp_match(x[1], '(\d{1,3},?\d{0,})\D*(ko)');
68 fm := ' | ' || x[1] || ' ko';
69 i.format := regexp_replace(i.format, '(\d{1,3},?\d{0,2}\D*ko)', '');
70 ELSIF i.format ~ '(\d{1,3},?\d{0,2}\D*mo)' then
71 x := regexp_match(i.format, '(\d{1,3},?\d{0,}\D*ko)');
72 x := regexp_match(x[1], '(\d{1,3},?\d{0,})\D*(ko)');
73 x[1] := regexp_replace(x[1], ',', '.');
74 fm := ' | ' || (x[1]::float * 1024) || ' ko';
75 END IF;
76
77 -- taille
78 IF i.format ~ '(\d{2,4}\D*x\D*\d{2,4})' and i.cote not like 'AS-AA1-01' then
79 x := regexp_match(i.format, '(\d{2,4}\D*\d{2,4})');
80 x := regexp_match(x[1], '(\d{2,4})\D*(\d{2,4})');
81 fm := x[1] || 'x' || x[2] || fm;
82 i.format = regexp_replace(i.format, '(\d{2,4}\D*\d{2,4})', '');
83 ELSIF i.format ~ '(\d{2,4}\D*\d{2,4})' then
84 x := regexp_match(i.format, '(\d{2,4}\D*\d{2,4})');
85 x := regexp_match(x[1], '(\d{2,4})\D*(\d{2,4})');
86 fm := x[1] || 'x' || x[2] || fm;
87 ELSE
88 fm := i.format;
89 END IF;
90
91 IF i.nature_du_document ~ '.*jpe?e?gg?' then
92 nd := 'archivo jpg';
93 ELSIF i.nature_du_document ~ '.*pne?g' then
94 nd := 'archivo png';
95 ELSE
96 nd := i.nature_du_document;
97 END IF;
98
99 i.notes := regexp_replace(i.notes, '[^[:ascii:]]', '', 'g');
100 insert into tmp_format_nature values (i.cote, 'spa', fm, i.notes, nd);
101 fm := '';
102 END LOOP;
103
104END;
105$$ LANGUAGE plpgsql;
106
107select format_nature();
108SELECT * from tmp_format_nature;
109
110TRUNCATE "document";
111INSERT INTO document (cote, lang_orig, format, notes, nature_documents)
112(select cote, 'spa', format, notes, nature_documents from tmp_format_nature);
113
114-- publication
115CREATE OR REPLACE FUNCTION publication()
116RETURNS void AS $$
117DECLARE
118 i record;
119BEGIN
120 FOR i IN select cote, "numéro_de_la_publication", type_de_publication,
121 titre_de_la_publication, directeur_de_la_publication, lieu_de_publication,
122 "publication" from csv_importes LOOP
123 -- pre traite
124 i.publication := regexp_replace(i.publication, '[^[:ascii:]]', ' ', 'g');
125
126 -- insert
127 insert into "publication" (numero, "type", titre, directeur, "publication")
128 values (i."numéro_de_la_publication", i.type_de_publication::type_publication_, i.titre_de_la_publication,
129 i.directeur_de_la_publication, i."publication");
130 update "document"
131 set publication_id = (select max(publication_id) from "publication")
132 where "document".cote = i.cote;
133 END LOOP;
134END;
135$$ LANGUAGE plpgsql;
136
137select publication();
138
139select * from document;
140
141-- Donnee
142CREATE OR REPLACE FUNCTION donnee()
143RETURNS void AS $$
144DECLARE
145 i record;
146BEGIN
147 FOR i IN select Langue, Support,cote from csv_importes LOOP
148
149 -- insert
150 insert into "donnee" (lang, support)
151 values (i.Langue::langue_, i.Support);
152 update "document"
153 set donne_id = (select max(donne_id) from Donnee)
154 where "document".cote = i.cote;
155 END LOOP;
156END;
157$$ LANGUAGE plpgsql;
158select donnee();
159
160-- Genetique
161CREATE OR REPLACE FUNCTION genetique()
162RETURNS void AS $$
163DECLARE
164 i record;
165BEGIN
166 FOR i IN select "État_genetique" ,Relations_genetiques,cote from csv_importes LOOP
167 -- pre traite
168 i.État_genetique := regexp_replace(i.État_genetique, '[^[:ascii:]]', ' ', 'g');
169
170 -- insert
171 insert into Genetique (etat,relation)
172 values (i."État_genetique", i.Relations_genetiques);
173 update "donnee"
174 set genetique_id = (select max(genetique_id) from Genetique)
175 where "donnee".donne_id in (select donne_id
176 from document
177 where document.cote= i.cote);
178 END LOOP;
179END;
180$$ LANGUAGE plpgsql;
181select genetique();
182-- Notice
183CREATE OR REPLACE FUNCTION notice()
184RETURNS void AS $$
185DECLARE
186 i record;
187BEGIN
188 FOR i IN select date_creation ,date_analyse,date_revision,cote from tmp_date_notice LOOP
189
190 -- insert
191 insert into Notice (date_creation,date_analyse,date_revision)
192 values (i.date_creation, i.date_analyse,i.date_revision);
193 update "donnee"
194 set notice_id = (select max(notice_id) from Notice)
195 where donnee.donne_id in (select donne_id
196 from document
197 where document.cote =i.cote);
198 END LOOP;
199END;
200$$ LANGUAGE plpgsql;
201select notice();
202-- Description
203CREATE OR REPLACE FUNCTION Description()
204RETURNS void AS $$
205DECLARE
206 i record;
207BEGIN
208 FOR i IN select Titre ,sous_titre,description,resume,cote from csv_importes LOOP
209
210 -- insert
211 insert into Descriptions (titre, sous_titre,descriptions,resume)
212 values (i.Titre, i.sous_titre,i.description,i.resume);
213 update "donnee"
214 set descriptions_id = (select max(descriptions_id) from Descriptions)
215 where "donnee".donne_id in (select donne_id
216 from document
217 where document.cote= i.cote);
218 END LOOP;
219END;
220$$ LANGUAGE plpgsql;
221select Description();
222
223-- Type
224CREATE OR REPLACE FUNCTION Type()
225RETURNS void AS $$
226DECLARE
227 i record;
228 j record;
229BEGIN
230 FOR i IN select "type", "datatype", cote from csv_importes LOOP
231 -- pre traite
232 i.type := regexp_replace(i.type, '[^[:ascii:]]', '', 'g');
233 if i.type ~ 'Obras, creaciones, producci.*Novelas' then
234 i.type := 'Obras, creaciones, producción – Novelas'::Type_;
235 end if;
236
237 select into j count(t.type) from "type" as t where t."type" = i.type::Type_ and lower(t.datatype) = lower(i.datatype);
238 if j.count = 0 then
239 -- insert
240 insert into Type (type,datatype)
241 values (i.type::type_, i.datatype);
242 update "donnee"
243 set type_id = (select max(type_id) from Type)
244 where "donnee".donne_id in (
245 select donne_id
246 from document
247 where document.cote= i.cote
248 );
249 else
250 update "donnee"
251 set type_id = (select type_id from "type" as t where t."type" = i.type::Type_ and lower(t.datatype) = lower(i.datatype))
252 where "donnee".donne_id in (
253 select donne_id
254 from "document"
255 where document.cote= i.cote
256 );
257 end if;
258 END LOOP;
259END;
260$$ LANGUAGE plpgsql;
261select Type();
262
263-- Info_Juridique
264CREATE OR REPLACE FUNCTION Info_Juridique()
265RETURNS void AS $$
266DECLARE
267 i record;
268BEGIN
269 FOR i IN select droits, Ayants_Droits, cote from csv_importes LOOP
270 -- pre traite
271
272 -- insert
273 insert into Info_Juridique (droits,ayant_droits)
274 values (i.droits, i.Ayants_Droits);
275 update "document"
276 set juridique_id = (select max(juridique_id) from Info_Juridique)
277 where "document".cote = i.cote;
278 END LOOP;
279END;
280$$ LANGUAGE plpgsql;
281select Info_Juridique();
282
283
284-- Localisation
285CREATE OR REPLACE FUNCTION Localisation()
286RETURNS void AS $$
287DECLARE
288 i record;
289BEGIN
290 FOR i IN select pays,region,nom,lat,long,cote from csv_importes LOOP
291 -- pre traite
292 if i.pays ='#FIELD!' then
293 i.pays=NULL;
294 end if;
295 if i.region ='#FIELD!' then
296 i.region=NULL;
297 end if;
298 if i.nom ='#FIELD!' then
299 i.nom=NULL;
300 end if;
301 if i.lat ='#FIELD!' then
302 i.lat=NULL;
303 end if;
304 if i.long ='#FIELD!' then
305 i.long=NULL;
306 end if;
307 i.lat := regexp_replace(i.lat, ',', '.');
308 i.long := regexp_replace(i.long, ',', '.');
309
310 -- insert
311 insert into Localisation (pays, region,nom,latitude,longitude)
312 values (i.pays,i.region,i.nom,i.lat::float,i.long::float);
313 insert into Lettre (lieu_dexpedition) values ((select max(localisation_id) from Localisation));
314 update document
315 set lettre_id = (select max(lettre_id) from Lettre)
316 where document.cote=i.cote;
317 update "publication"
318 set lieu = (select max(localisation_id) from Localisation)
319 where "publication".publication_id in (select publication_id
320 from document
321 where document.cote= i.cote);
322 update "descriptions"
323 set context = (select max(localisation_id) from Localisation)
324 where "descriptions".descriptions_id in (select descriptions_id
325 from donnee
326 where donnee.donne_id in (select donne_id
327 from document
328 where document.cote=i.cote));
329 END LOOP;
330END;
331$$ LANGUAGE plpgsql;
332select Localisation();
333
334-- Auteur
335CREATE OR REPLACE FUNCTION Auteur()
336RETURNS void AS $$
337DECLARE
338 i record;
339 x text[];
340 y text[];
341 z text[];
342 t text[];
343 j text;
344 r int ;
345BEGIN
346 r:=0;
347 FOR i IN select Auteur, Auteur_description,Auteur_revision,Auteur_transcription,cote from csv_importes LOOP
348 if i.Auteur is not null then
349 x:=regexp_split_to_array(i.Auteur,',');
350 y:=regexp_split_to_array(i.Auteur_description,',');
351 z:=regexp_split_to_array(i.Auteur_revision,',');
352 t:=regexp_split_to_array(i.Auteur_transcription,',');
353 -- insert auteur
354 if lower(i.Auteur) not like 'indeterminado' then
355 for j in 1..array_length(x,1) loop
356 if x[j] not in (select auteur from Auteurs) then
357 x[j]:= regexp_replace(x[j], '"', '','g');
358 insert into Auteurs (auteur)
359 values (x[j]);
360 if r not in (select * from Liste_Auteur) then
361 insert into Liste_Auteur (group_id)
362 values (r);
363 end if;
364 insert into Ligne_Auteur (auteurs_id,groupe_id)
365 values ((select max(auteurs_id) from Auteurs),(select max(group_id) from Liste_Auteur));
366 update donnee
367 set auteurs=(select max(group_id) from Liste_Auteur)
368 where donnee.donne_id in (select donne_id
369 from document
370 where document.cote=i.cote);
371 end if;
372 end loop;
373 r:=r+1;
374 end if;
375 -- insert Auteur_description
376 if lower(i.Auteur_description) not like 'indeterminado' then
377 for j in 1..array_length(y,1) loop
378 if y[j] not in (select auteur from Auteurs) then
379 y[j]:= regexp_replace(y[j], '"', '','g');
380 insert into Auteurs (auteur)
381 values (y[j]);
382 if r not in (select * from Liste_Auteur) then
383 insert into Liste_Auteur (group_id)
384 values (r);
385 end if;
386 insert into Ligne_Auteur (auteurs_id,groupe_id)
387 values ((select max(auteurs_id) from Auteurs),(select max(group_id) from Liste_Auteur));
388 update donnee
389 set auteurs=(select max(group_id) from Liste_Auteur)
390 where donnee.donne_id in (select donne_id
391 from document
392 where document.cote=i.cote);
393 end if;
394 end loop;
395 r:=r+1;
396 end if;
397 -- insert Auteur_revision
398 if lower(i.Auteur_revision) not like 'indeterminado' then
399 for j in 1..array_length(z,1) loop
400 if z[j] not in (select auteur from Auteurs) then
401 z[j]:= regexp_replace(z[j], '"', '','g');
402 insert into Auteurs (auteur)
403 values (z[j]);
404 if r not in (select * from Liste_Auteur) then
405 insert into Liste_Auteur (group_id)
406 values (r);
407 end if;
408 insert into Ligne_Auteur (auteurs_id,groupe_id)
409 values ((select max(auteurs_id) from Auteurs),(select max(group_id) from Liste_Auteur));
410 update donnee
411 set auteurs=(select max(group_id) from Liste_Auteur)
412 where donnee.donne_id in (select donne_id
413 from document
414 where document.cote=i.cote);
415 end if;
416 end loop;
417 r:=r+1;
418 end if;
419 -- insert Auteur_transcription
420 if lower(i.Auteur_transcription) not like 'indeterminado' then
421 for j in 1..array_length(t,1) loop
422 if t[j] not in (select auteur from Auteurs) then
423 t[j]:= regexp_replace(t[j], '"', '','g');
424 insert into Auteurs (auteur)
425 values (t[j]);
426 if r not in (select * from Liste_Auteur) then
427 insert into Liste_Auteur (group_id)
428 values (r);
429 end if;
430 insert into Ligne_Auteur (auteurs_id,groupe_id)
431 values ((select max(auteurs_id) from Auteurs),(select max(group_id) from Liste_Auteur));
432 update donnee
433 set auteurs=(select max(group_id) from Liste_Auteur)
434 where donnee.donne_id in (select donne_id
435 from document
436 where document.cote=i.cote);
437 end if;
438 end loop;
439 r:=r+1;
440 end if;
441 end if;
442 END LOOP;
443END;
444$$ LANGUAGE plpgsql;
445select Auteur();
446
447-- editeurs
448CREATE OR REPLACE FUNCTION editeurs()
449RETURNS void AS $$
450DECLARE
451 i record;
452 tmp text;
453 tmps text;
454 tmp3 text;
455 editeurs text;
456 responsables text;
457 responsable_scientifiques text;
458 spectateur text;
459 x text[];
460BEGIN
461 FOR i IN select editeur, cote from csv_importes LOOP
462 -- pre traite
463 i.editeur := lower(i.editeur);
464 IF i.editeur ~ 'responsable del archivo.?:?(.\*)(editor ?:?)(.\*)\(responsable (scientifique|científico).?:?(.*)' THEN
465 x := regexp_match(i.editeur, 'responsable del archivo.?:?(.\*)(editor ?:?)(.\*)\(responsable (scientifique|científico).?:?(.*)');
466 raise notice '%', x;
467 editeurs := x[3];
468 editeurs := regexp_replace(editeurs, '\(', '');
469 editeurs := regexp_replace(editeurs, 'editor:', '');
470 responsable_scientifiques := x[5];
471 responsables := x[1];
472 ELSIF i.editeur ~ 'responsable del archivo.?:?(.*indeterminadoecto)(.*)responsable (scientifique|científico).?:?(.*)' THEN
473 x := regexp_match(i.editeur, 'responsable del archivo.?:?(.*indeterminadoecto)(.*)responsable (scientifique|científico).?:?(.*)');
474 editeurs := 'projecto' || x[2];
475 editeurs := regexp_replace(editeurs, '\(', '');
476 editeurs := regexp_replace(editeurs, 'editor:', '');
477 responsable_scientifiques := x[4];
478 responsables := 'indeterminado';
479 ELSIF i.editeur ~ 'responsable del archivo.?:?(.*),(.*)responsable (scientifique|científico).?:?(.*)' THEN
480 x := regexp_match(i.editeur, 'responsable del archivo.?:?(.*),(.*)responsable (scientifique|científico).?:?(.*)');
481 editeurs := x[2];
482 editeurs := regexp_replace(editeurs, '\(', '');
483 editeurs := regexp_replace(editeurs, 'editor:', '');
484 responsable_scientifiques := x[4];
485 responsables := x[1];
486 ELSIF i.editeur ~ 'responsable del archivo.?:?(.*)( \| )(.*)responsable (scientifique|científico).?:?(.*)' THEN
487 x := regexp_match(i.editeur, 'responsable del archivo.?:?(.*)( \| )(.*)responsable (scientifique|científico).?:?(.*)');
488 editeurs := x[3];
489 editeurs := regexp_replace(editeurs, '\(', '');
490 editeurs := regexp_replace(editeurs, 'editor:', '');
491 responsable_scientifiques := x[5];
492 responsables := x[1];
493 END IF;
494
495 responsables := regexp_replace(responsables, '\|', '');
496 IF responsables LIKE '%editor%' THEN
497 responsables := regexp_replace(responsables, 'editor.*', '');
498 editeurs := 'proyecto espectateur' || editeurs;
499 END IF;
500
501 -- insert
502 insert into Editeurs (responsable,editeur,responsable_scientifique)
503 values (responsables,editeurs,responsable_scientifiques);
504 update Info_juridique
505 set editeur_id = (select max(editeur_id) from Editeurs)
506 where Info_juridique.juridique_id in(select juridique_id
507 from document
508 where document.cote=i.cote);
509 END LOOP;
510END;
511$$ LANGUAGE plpgsql;
512select editeurs();
513
514
515-- drop table temporaire --
516drop table if exists tmp_date_notice;
517drop table if exists tmp_format_nature;
518
519
520select * from descriptions;
521select distinct auteurs_description from descriptions;
522select distinct auteur_description from csv_importes;
523
524-- vide
525select distinct auteur_revision from csv_importes;
526select distinct auteur_transcription from csv_importes;
527
528
529
530