1
|
select
|
2
|
c.creation as dri_objidentifier,
|
3
|
r.title as dc_title,
|
4
|
array_agg(DISTINCT lpad('0'||aut.rank,5,'0')||'§§§'||ltrim(replace(per.surname||', '||per.name,'UNKNOWN, ',''), ', ')) as dc_creator,
|
5
|
r.description as dc_description,
|
6
|
r.publisher as dc_publisher,
|
7
|
ARRAY(select trim(s) from unnest(string_to_array(r.keywords, ';')) as s) as dc_subject,
|
8
|
r.language as dc_language,
|
9
|
array_agg(DISTINCT ws.file_url) as dc_identifier,
|
10
|
r.publication_date as "dc_dateAccepted",
|
11
|
extract(year from r.publication_date)::text as oaf_publicationyear,
|
12
|
r.embargo_end_date as oaf_embargoenddate,
|
13
|
extract(year from r.embargo_end_date)::text as oaf_embargoendyear,
|
14
|
r.access_mode as oaf_accessrights,
|
15
|
r.haskind as oaf_haskind,
|
16
|
r.hastype as "dr_CobjCategory",
|
17
|
r.storagedate as oaf_storagedate,
|
18
|
r.createdfrom as temp_createdfrom,
|
19
|
c.creator as action_user,
|
20
|
c.date_of_creation as action_date_of_creation,
|
21
|
c.last_update_date as action_update_date,
|
22
|
|
23
|
array_agg(DISTINCT o.country_of_origin) as oaf_countryaffiliation,
|
24
|
array_agg(DISTINCT o.legal_short_name) as oaf_affiliationname,
|
25
|
array_agg(DISTINCT o.organizationid) as oaf_affiliationid,
|
26
|
array_agg(DISTINCT p.projectid) as temp_projectid,
|
27
|
|
28
|
array_agg(DISTINCT d.datasourceid) as temp_hostedbyid,
|
29
|
array_agg(DISTINCT d.official_name) as temp_hostedbyname,
|
30
|
|
31
|
array_agg(DISTINCT df.datasourceid) as temp_collectedfromid,
|
32
|
array_agg(DISTINCT df.official_name) as temp_collectedfromname
|
33
|
|
34
|
from
|
35
|
creations c
|
36
|
left outer join results r on (r.resultid = c.creation)
|
37
|
left outer join results_projects rp on (r.resultid = rp.result)
|
38
|
left outer join projects p on (p.projectid = rp.project)
|
39
|
left outer join authorships aut on (r.resultid = aut.result)
|
40
|
left outer join persons per on (per.personid = aut.author)
|
41
|
left outer join organizations o on (o.organizationid = aut.affiliation)
|
42
|
left outer join instances i on (r.resultid = i.resource)
|
43
|
left outer join webresources_instances wsi on (wsi.instanceid = i.instanceid)
|
44
|
left outer join webresources ws on (ws.webresourceid = wsi.webresourceid)
|
45
|
left outer join datasources d on (i.hostedby = d.datasourceid)
|
46
|
left outer join datasources df on (i.collectedFrom = df.datasourceid)
|
47
|
where
|
48
|
r.pendingforclaim = false
|
49
|
|
50
|
group by
|
51
|
c.creation, c.creator,
|
52
|
r.resultid, r.title, r.description, r.publisher, r.keywords, r.language,
|
53
|
r.publication_date, r.embargo_end_date, r.access_mode, r.haskind, r.hastype,
|
54
|
r.createdfrom , r.storagedate, c.creator, c.last_update_date, c.date_of_creation
|