|
1
|
--WITH RECURSIVE fundingtree(id, parent_id, payload, depth)
|
|
2
|
--AS (
|
|
3
|
-- SELECT f.id,
|
|
4
|
-- ff.funding1,
|
|
5
|
-- '{"funding_level_0":{"class":"' || f.semanticclass || '", "id":"' || f.id || '", "description":"' || f.description || '", "name":"' || f.name || '", "parent":{}}}',
|
|
6
|
-- 1
|
|
7
|
-- FROM fundings f LEFT OUTER JOIN funding_funding ff ON f.id = ff.funding1
|
|
8
|
-- WHERE ff.funding2 IS NULL
|
|
9
|
-- UNION
|
|
10
|
-- SELECT f1.id,
|
|
11
|
-- ff.funding2,
|
|
12
|
-- '{"funding_level_'||depth||'":{"class":"' || f1.semanticclass || '", "id":"' || f1.id || '", "description":"' || f1.description || '", "name":"' || f1.name || '", "parent":'|| ft.payload ||'}}',
|
|
13
|
-- ft.depth + 1
|
|
14
|
-- FROM funding_funding ff, fundingtree ft, fundings f2, fundings f1
|
|
15
|
-- WHERE ft.id = ff.funding2 AND f2.id = ft.id AND ff.funding1 = f1.id AND depth <= 10)
|
|
16
|
|
|
17
|
SELECT p.id as projectid,
|
|
18
|
p.code as code,
|
|
19
|
p.websiteurl as websiteurl,
|
|
20
|
p.acronym as acronym,
|
|
21
|
p.title as title,
|
|
22
|
p.startdate as startdate,
|
|
23
|
p.enddate as enddate,
|
|
24
|
p.call_identifier as callidentifier,
|
|
25
|
p.keywords as keywords,
|
|
26
|
p.duration as duration,
|
|
27
|
p.ec_sc39 as ecsc39,
|
|
28
|
p.oa_mandate_for_publications as oamandatepublications,
|
|
29
|
p.ec_article29_3 as ecarticle29_3,
|
|
30
|
p.dateofcollection as dateofcollection,
|
|
31
|
p.inferred as inferred,
|
|
32
|
p.deletedbyinference as deletedbyinference,
|
|
33
|
p.trust as trust,
|
|
34
|
p.inferenceprovenance as inferenceprovenance,
|
|
35
|
dc.id as collectedfromid,
|
|
36
|
dc.officialname as collectedfromname,
|
|
37
|
|
|
38
|
cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name as contracttype,
|
|
39
|
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name as provenanceaction,
|
|
40
|
|
|
41
|
array_agg(DISTINCT i.pid || '###' || idc.code || '@@@' || idc.name || '@@@' || ids.code || '@@@' || ids.name) as pid,
|
|
42
|
array_agg(DISTINCT s.name || '###' || sc.code || '@@@' || sc.name || '@@@' || ss.code || '@@@' || ss.name) as subjects,
|
|
43
|
|
|
44
|
array_agg(fp.path) as fundingtree
|
|
45
|
|
|
46
|
FROM projects p
|
|
47
|
left outer join class cc on (cc.code = p.contracttypeclass)
|
|
48
|
left outer join scheme cs on (cs.code = p.contracttypescheme)
|
|
49
|
|
|
50
|
left outer join class pac on (pac.code = p.provenanceactionclass)
|
|
51
|
left outer join scheme pas on (pas.code = p.provenanceactionscheme)
|
|
52
|
|
|
53
|
left outer join projectpids pp on (pp.project = p.id)
|
|
54
|
left outer join identities i on (i.pid = pp.pid)
|
|
55
|
left outer join class idc on (idc.code = i.issuertypeclass)
|
|
56
|
left outer join scheme ids on (ids.code = i.issuertypescheme)
|
|
57
|
|
|
58
|
left outer join datasources dc on (dc.id = p.collectedfrom)
|
|
59
|
|
|
60
|
left outer join project_fundingpath pf on (pf.project = p.id)
|
|
61
|
left outer join fundingpaths fp on (fp.id = pf.funding)
|
|
62
|
|
|
63
|
left outer join project_subject ps on (ps.project = p.id)
|
|
64
|
left outer join subjects s on (s.id = ps.subject)
|
|
65
|
|
|
66
|
left outer join class sc on (sc.code = s.semanticclass)
|
|
67
|
left outer join scheme ss on (ss.code = s.semanticscheme)
|
|
68
|
|
|
69
|
GROUP BY
|
|
70
|
p.id,
|
|
71
|
p.code,
|
|
72
|
p.websiteurl,
|
|
73
|
p.acronym,
|
|
74
|
p.title,
|
|
75
|
p.startdate,
|
|
76
|
p.enddate,
|
|
77
|
p.call_identifier,
|
|
78
|
p.keywords,
|
|
79
|
p.duration,
|
|
80
|
p.ec_sc39,
|
|
81
|
p.oa_mandate_for_publications,
|
|
82
|
p.ec_article29_3,
|
|
83
|
p.dateofcollection,
|
|
84
|
p.inferred,
|
|
85
|
p.deletedbyinference,
|
|
86
|
p.trust,
|
|
87
|
p.inferenceprovenance,
|
|
88
|
dc.id,
|
|
89
|
dc.officialname,
|
|
90
|
cc.code, cc.name, cs.code, cs.name,
|
|
91
|
pac.code, pac.name, pas.code, pas.name
|
|
92
|
|
|
93
|
|