1
|
SELECT p.id as personid,
|
2
|
p.firstname as firstname,
|
3
|
p.secondnames as secondnames,
|
4
|
CASE WHEN length(p.secondnames) > 0 THEN trim(both from p.secondnames||', '||p.firstname) ELSE p.firstname END as fullname,
|
5
|
p.fax as fax,
|
6
|
p.email as email,
|
7
|
p.phone as phone,
|
8
|
p.dateofcollection as dateofcollection,
|
9
|
p.inferred as inferred,
|
10
|
p.deletedbyinference as deletedbyinference,
|
11
|
p.trust as trust,
|
12
|
p.inferenceprovenance as inferenceprovenance,
|
13
|
dc.id as collectedfromid,
|
14
|
dc.officialname as collectedfromname,
|
15
|
|
16
|
nc.code || '@@@' || nc.name || '@@@' || ns.code || '@@@' || ns.name as nationality,
|
17
|
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name as provenanceaction,
|
18
|
|
19
|
array_agg(DISTINCT i.pid || '###' || idc.code || '@@@' || idc.name || '@@@' || ids.code || '@@@' || ids.name) as pid
|
20
|
|
21
|
FROM persons p
|
22
|
left outer join class nc on (nc.code = p.nationalityclass)
|
23
|
left outer join scheme ns on (ns.code = p.nationalityscheme)
|
24
|
|
25
|
left outer join class pac on (pac.code = p.provenanceactionclass)
|
26
|
left outer join scheme pas on (pas.code = p.provenanceactionscheme)
|
27
|
|
28
|
left outer join datasources dc on (dc.id = p.collectedfrom)
|
29
|
|
30
|
left outer join personpids pp on (pp.person = p.id)
|
31
|
left outer join identities i on (i.pid = pp.pid)
|
32
|
left outer join class idc on (idc.code = i.issuertypeclass)
|
33
|
left outer join scheme ids on (ids.code = i.issuertypescheme)
|
34
|
|
35
|
left outer join project_organization po on (po.contactperson = p.id)
|
36
|
|
37
|
WHERE (p.firstname is not null OR p.secondnames is not null) AND po.resporganization is not null
|
38
|
|
39
|
GROUP BY
|
40
|
p.id,
|
41
|
p.firstname,
|
42
|
p.secondnames,
|
43
|
p.fax,
|
44
|
p.email,
|
45
|
p.phone,
|
46
|
p.dateofcollection,
|
47
|
p.inferred,
|
48
|
p.deletedbyinference,
|
49
|
p.trust,
|
50
|
p.inferenceprovenance,
|
51
|
dc.id,
|
52
|
dc.officialname,
|
53
|
nc.code, nc.name, ns.code, ns.name,
|
54
|
pac.code, pac.name, pas.code, pas.name
|