D-Net

SELECT

	p.id                                                                    AS personid,

	p.firstname                                                             AS firstname,

	p.secondnames                                                           AS secondnames,

	CASE WHEN length(p.secondnames) > 0

		THEN trim(BOTH FROM p.secondnames || ', ' || p.firstname)

	ELSE p.firstname END                                                    AS fullname,

	p.fax                                                                   AS fax,

	p.email                                                                 AS email,

	p.phone                                                                 AS phone,

	p.dateofcollection                                                      AS dateofcollection,

	p.inferred                                                              AS inferred,

	p.deletedbyinference                                                    AS deletedbyinference,

	p.trust                                                                 AS trust,

	p.inferenceprovenance                                                   AS inferenceprovenance,

	dc.id                                                                   AS collectedfromid,

	dc.officialname                                                         AS collectedfromname,

	nc.code || '@@@' || nc.name || '@@@' || ns.code || '@@@' || ns.name     AS nationality,

	pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction,

	array_agg(DISTINCT i.pid || '###' || i.issuertypeclass)                 AS pid

	LEFT OUTER JOIN class nc ON (nc.code = p.nationalityclass)

	LEFT OUTER JOIN scheme ns ON (ns.code = p.nationalityscheme)

	LEFT OUTER JOIN class pac ON (pac.code = p.provenanceactionclass)

	LEFT OUTER JOIN scheme pas ON (pas.code = p.provenanceactionscheme)

	LEFT OUTER JOIN datasources dc ON (dc.id = p.collectedfrom)

	LEFT OUTER JOIN personpids pp ON (pp.person = p.id)

	LEFT OUTER JOIN identities i ON (i.pid = pp.pid)

	LEFT OUTER JOIN project_organization po ON (po.contactperson = p.id)

WHERE (p.firstname IS NOT NULL OR p.secondnames IS NOT NULL) AND po.resporganization IS NOT NULL

	p.id,

	p.firstname,

	p.secondnames,

	p.fax,

	p.email,

	p.phone,

	p.dateofcollection,

	p.inferred,

	p.deletedbyinference,

	p.trust,

	p.inferenceprovenance,

	dc.id,

	dc.officialname,

	nc.code, nc.name, ns.code, ns.name,

	pac.code, pac.name, pas.code, pas.name