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